Cập nhật nội dung chi tiết về Thành Thạo Pivot Table Trong Excel Thông Qua Ví Dụ Thực Tế 1/2021 mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Video hướng dẫn
Tạo pivot table từ bảng dữ liệu
Khi đã có được dữ liệu, điều đầu tiên chúng ta nên làm là liên kết nó để tạo thành 1 bảng thống nhất. Với thao tác này thì khi chúng ta có sự điều chỉnh thay đổi hay thêm bớt dữ liệu trong tương lai thì pivot table được tạo cũng sẽ tự động được cập nhật theo. Để tạo bảng các bạn ấn vào một ô bất kì trong vùng dữ liệu rồi ở thẻ Insert chọn Table.
Sau khi bảng được tạo mới thì các bạn có thể thay đổi tên của bảng ở mục Table Name ở phía trên bên phải. Điều này giúp chúng ta dễ dàng hơn trong việc phân biệt khi làm việc với nhiều bảng khác nhau.
Để tạo một pivot table từ bảng dữ liệu có sẵn, các bạn chọn một ô bất kì trong vùng dữ liệu, sau đó ở thẻ Insert chọn PivotTable.
Lúc này thì hộp thoại Create PivotTable sẽ hiện ra, các bạn lưu ý chọn đúng bảng chứa dữ liệu cần sử dụng ở mục Table/Range. Ngoài ra, bên dưới các bạn còn có thể tùy chỉnh xem sẽ tạo pivot table ở một sheet mới hay ở một sheet đã có sẵn trước đó.
Sau khi tạo thành công thì một bảng điều khiển sẽ hiện lên từ bên phải tên là PivotTable Fields, đây sẽ là nơi các bạn thao tác chính để tạo ra những báo cáo như mong muốn của mình. Phía bên trên sẽ là tên các cột có trong bảng dữ liệu còn bên dưới gồm bốn ô chính: Filters, Columns, Rows và Values.
Sau khi kéo “Khu Vực” và ô Filters, “Nhà Phân Phối” vào ô Columns, “Sản Phẩm” vào ô Rows và “Doanh Thu” vào ô Values thì chúng ta sẽ được một bảng như bên dưới. Các bạn có thể hiểu đơn giản giá trị trong ô Filters sẽ được dùng để lọc dữ liệu, như ví dụ trên thì khi chúng ta điều chỉnh thành các khu vực khác nhau thì sẽ có được các số liệu thống kê khác nhau. Giá trị của ô Columns và Rows sẽ là các giá trị trong “Nhà Phân Phối” và “Sản Phẩm” lần lượt được biểu diễn theo cột và hàng. Cuối cùng ô Values sẽ chứa giá trị chính, được lọc ra từ Filters (Khu Vực) và tổng hợp theo Columns (Nhà Phân Phối) và Rows (Sản Phẩm).
Mặc định, giá trị của doanh thu sẽ được tính theo hàm SUM, tức là tổng các giá trị. Trong trường hợp các bạn muốn tổng hợp doanh thu theo phần trăm hoặc trung bình cộng thì có thể ấn vào dấu mũi tên bên phải trong ô Values rồi chọn Value Field Settings.
Trong Excel sẽ có sẵn cho các bạn một số lựa chọn cho ô Values như tính tổng cộng, tính số lượng, tính trung bình cộng, tìm ra số lớn nhất, nhỏ nhất,… Các bạn có thể thoải mái lựa chọn ở đây.
Sau khi kéo “Khu Vực” ra khỏi ô Filters và bỏ vào ô Rows thì chúng ta sẽ có được kết quả như bên dưới. Trong ô Rows, giá trị được bỏ vào đầu tiên sẽ có vai trò như là phần tử cha. Như ví dụ bên dưới thì chúng ta có thể nhìn thấy được doanh thu theo khu vực của từng loại sản phẩm, còn nếu kéo “Khu Vực” nằm trên “Sản Phẩm” thì chúng ta sẽ thấy được doanh thu của từng loại sản phẩm theo khu vực.
Mặc định trong pivot table sẽ tính tổng giá trị theo cột và hàng (Subtotals), để ẩn tổng giá trị này, các bạn có thể vào thẻ Design, chọn Subtotals rồi chọn Do Not Show Subtotals.
Tương tự như phía trên thì trong pivot table cũng mặc định hiển thị tổng giá trị của tất cả (Grand Total), để ẩn các bạn cũng vào thẻ Design, chọn Grand Totals rồi chọn Off for Rows and Columns.
Mình có một kinh nghiệm khi sử dụng tính năng pivot table đó là các bạn nên hiển thị báo cáo theo Tabular Form để trở nên trực quan hơn. Để làm điều này, ở thẻ Design các bạn chọn Report Layout rồi chọn Show in Tabular Form.
Sau khi đã tùy chỉnh bố cục của báo cáo, có thể dễ dàng nhận thấy các giá trị “Bánh”, “Kẹo”, “Sữa” chỉ hiển thị trong báo cáo một lần duy nhất ở hàng đầu tiên. Để tùy chỉnh cho hiển thị ở tất cả những ô trống ở các hàng bên dưới, ở thẻ Design chọn Report Layout rồi chọn Repeat All Item Labels.
Các bạn còn có thể sắp xếp giá trị trong các cột theo thứ tự từ nhỏ đến lớn, từ lớn đến nhỏ,.. bằng cách ấn chuột phải vào một ô giá trị bất kì, chọn Sort rồi chọn kiểu sắp xếp theo mong muốn.
Ngoài ra, các bạn còn có thể kéo “Doanh Thu” vào ô Values một lần nữa để tạo ra một cột mới, sau đó ấn chuột phải vào một ô bất kì trong cột đó, chọn Show Values As rồi chọn một kiểu hiển thị khác như tính theo phần trăm, trung bình cộng,…
Tạo pivot table mới từ một pivot table có sẵn
Thay vì phải tạo một pivot table như cách ở phía trên, trong trường hợp các bạn đã có một pivot table trước đó thì có thể tạo mới đơn giản bằng cách chọn pivot table cũ, ấn tổ hợp phím Ctrl + C rồi chọn một ô ở vị trí khác, ấn tổ hợp phím Ctrl + V để dán.
Ngoài cách lọc giá trị bằng ô Filters, các bạn có thể sử dụng một tính năng dễ sử dụng, thao tác nhanh và đẹp hơn là Slicer. Để tạo mới, các bạn ấn chuột phải vào giá trị cần tạo trong bảng PivotTable Fields rồi chọn Add as Slicer.
Tuy nhiên, lúc này slicer vừa tạo chỉ có tác dụng với pivot table được tạo sau. Để slicer tác động được đến nhiều pivot table khác nhau, các bạn ấn chuột phải và slicer rồi chọn Report Connections.
Ở hộp thoại Report Connections vừa hiện ra, các bạn ấn chọn tất cả những pivot table sẽ được tác động khi bạn thay đổi giá trị trên slicer rồi ấn nút OK phía bên dưới để lưu.
Khi các bạn thêm mới, xóa hoặc sửa trên bảng dữ liệu ban đầu, để cập nhật pivot table các bạn ấn chuột phải vào một ô bất kì trong pivot table rồi chọn Refresh, lúc này dữ liệu sẽ ngay lập tức được cập nhật lại. Các bạn lưu ý thao tác này sẽ không có tác dụng nếu các bạn không tạo bảng như ở những bước đầu mình đã hướng dẫn. Chúc các bạn thực hành tốt!
Pivot Table Là Gì? Hướng Dẫn Sử Dụng Thành Thạo Pivot Table Trong Excel
Pivot Table Là Gì? Hướng Dẫn Sử Dụng Thành Thạo Pivot Table Trong Excel
Pivot table là gì trong Excel
Hướng dẫn cách dùng Pivot table trong Excel
Như đã biết, Pivot Table (PT) có ứng dụng phổ biến trong việc xử lý các dữ liệu. Cùng tìm hiểu về cách sử dụng PT trong các trường hợp sau.
Thống kê dữ liệu với Pivot Table
Lệnh Pivot Table giúp bạn thống kế một vùng dữ liệu ban đầu. Vùng dữ liệu này sẽ không cập nhập thêm khi bạn chỉnh sửa hay thêm dữ liệu mới. Hãy làm theo các bước hướng dẫn với minh họa hình ảnh sau.
Lúc này, hộp thoại Create PivotTable sẽ xuất hiện. Bao gồm:
Trong phần Choose the data that you want to analyze, chọn Select a table or range.
Table/ Range là vùng dữ liệu bạn muốn thống kế.
Chọn Use an external data source khi bạn muốn sử dụng các dữ liệu bên ngoài (ít gặp).
Mục Choose where you want the PivotTable report to be placed chọn Existing Worksheet
New Worksheet: Xuất hiện một trang mới
Existing Worksheet: Xuất phần dữ liệu trong trang tính bạn đang thực hiện
Location: Vị trí mà bạn muốn tạo bảng
Sau đó, ấn OK để thực hiện hoàn thành thao tác. Chọn xong hộp thoại này mới tiếp tục lựa các Options của Pivot Table.
Để thay đổi các giao diện cơ bản của vùng dữ liệu Pivot Table, nhấn chuột phải vào vùng dữ liệu. Chọn PivotTable Options. Trong đây sẽ có các loại định dạng phù hợp với yêu cầu của bạn.
Chọn thẻ Display trong hộp thoại PivotTable Options. Chọn Classic PivotTable layout (enables dragging of fields in the grid) để chọn cách hiển thị của bảng. Nhấn Ok để bắt đầu điều chỉnh.
Kéo các vùng dữ liệu tương ứng vào từng vùng trong Pivot Table. Trong đó:
Columns: Cột hiển thị dữ liệu.
Rows: Dòng hiển thị dữ liệu.
Lập báo cáo thống kê với Pivot Table
Pivot Table là gì mà có thể lập cả bảng báo cáo thống kê. TTC sẽ hướng dẫn bạn sử dụng chức năng này. Dữ liệu cho tên mặt hàng, giá bán, ngày bán và bán tai vùng nào. Chúng ta tính tổng tiền bán được của mỗi sản phẩm theo các bước làm như sau:
Thực hiện mở Pivot Table như các bước trên.
Kéo cột giá (Product) vào Row Labels.
Kéo cột số lượng (Amount) vào Values area.
Kéo cột địa điểm bán (Country) vào Report.
Như vậy là bạn đã tính được tổng của 1 dòng theo một nhóm. Kết quả ở trên là tổng tiền theo mỗi loại sản phẩm. Ngoài ra bạn có thể tùy chỉnh đếm tổng số lượng sản phẩm nếu muốn.
Công ty TNHH dịch vụ vi tính Thịnh Tâm uy tín nhất tại Hóc Môn
Trung tâm hỗ trợ Hotline/Zalo: 0919 694 699 Văn phòng : 028 6254 9844
Địa chỉ : 19/1 Nguyễn Ảnh Thủ, Hưng Lân, Bà Điểm, Hóc Môn, TP. Hồ Chí Minh
Website: vitinhttc.com
Học Lập Trình Vba Excel Cấp Tốc Từ Căn Bản Đến Nâng Cao Qua Tình Huống Thực Tế 1/2021
LẬP TRÌNH VBA EXCEL TỪ CĂN BẢN ĐẾN NÂNG CAO – VBA EXCEL FOR BEGINNERS
KHÓA HỌC VBA EXCEL CẤP TỐC QUA 10 TÌNH HUỐNG THỰC TẾ
VBA Excel Cấp Tốc Bài 1 – Lọc Dữ Liệu Nhiều Điều Kiện Với Advanced Filter
VBA Excel Cấp Tốc Bài 2 – In Bảng Cửu Chương
VBA Excel Cấp Tốc Bài 3 – In Ấn Nhanh Hàng Loạt
VBA Excel Cấp Tốc Bài 4 – Xóa Tự Động Hàng Loạt Sheets Không Active
VBA Excel Cấp Tốc Bài 5 – Copy File Từ Một Thư Mục Sang Thư Mục Khác
VBA Excel Cấp Tốc Bài 6 – Hướng Dẫn Sử Dụng Record Macro Trong Excel Từ Cơ Bản Đến Nâng Cao
VBA Excel Cấp Tốc Bài 7 – Cách Gộp Nhiều File Excel Vào Một File Duy Nhất
VBA Excel Cấp Tốc Bài 8 – Cách Tạo Checkbox Hàng Loạt Trong Excel
VBA Excel Cấp Tốc Bài 9 – Tự Tạo Hàm Excel Riêng
PHẦN I. VBA CĂN BẢN TRONG EXCEL
Bài 01. Làm quen VBA EXCEL,tạo và chạy một bản ghi Macro trong VBA EXCEL Bài 02. Cửa sổ soạn thảo và chỉnh sửa Code VBA trong VBA EXCEL
Bài 03. Điều khiển đối tượng Cells, Ranges trong VBA EXCEL
Bài 04. Điều khiển đối tượng Worksheets trong VBA EXCEL
Bài 05. Điều khiển đối tượng Workbook trong VBA EXCEL
Bài 06. Biến và hằng trong VBA EXCEL
Biến trong VBA EXCEL
Hằng trong VBA EXCEL
Bài 07. Vòng lặp For Next, Do While, Do Until, For Each Next trong VBA EXCEL
For Next trong VBA EXCEL
For Each trong VBA EXCEL
Do Until trong VBA EXCEL
Do While trong VBA EXCEL
Bài 08. Cấu trúc điều kiện If Then Else trong VBA EXCEL
Bài 09. Câu lệnh điều kiện Select case trong VBA EXCEL
Bài 10. For Next, For Each Next trong VBA EXCEL và các ví dụ cho người bắt đầu
For Next trong VBA EXCEL
For Each Next trong VBA EXCEL
Các ví dụ ứng dụng trong VBA EXCEL
Bài 11. Hàm tự tạo trong VBA EXCEL Bài 12. Điều khiển hộp thoại MsgBox, InputBox trong VBA EXCEL
MsgBox trong VBA EXCEL
InputBox trong VBA EXCEL
Bài 13. Lỗi và Cách bẫy, xử lý lỗi trong VBA EXCEL
Type Mismatch
Debugger
Compile VBAProject
Run To Cursor
On Error Resume Next
On Error Goto
Bài 14. Cách chạy và gán một Macro cho nút bấm trong VBA EXCEL
Bài 15. Tạo Add-in sử dụng cho EXCEL trong VBA EXCEL
Đọc số thành chữ với các loại bảng mã trong Excel
Hàm tính tổng các ô cùng màu
Hàm tách số trong chuỗi
Bài 16. Lưu và sử dụng một File chứa VBA trong VBA EXCEL
Bài 17. Lập trình sự kiện trong VBA Excel
Selecttion Change, Change trong VBA EXCEL
After Save, Before Save trong VBA EXCEL
Open, Close trong VBA EXCEL
Activate, Deactivate trong VBA EXCEL
Bài 18. Trích lọc tự động với VBA EXCEL
Bài 20. Hàm InStr, InStrRev bổ sung tuyệt vời cho hàm Left, Right trong VBA EXCEL
Bài 21. UserForm và lập trình với các đối tượng của UserForm trong VBA EXCEL
ComboBox trong VBA EXCEL
TextBox trong VBA EXCEL
ListBox trong VBA EXCEL
Button trong VBA EXCEL
Lable trong VBA EXCEL
Tabtrip và MultiePage trong VBA EXCEL
OptionButton trong VBA EXCEL
CheckBox trong VBA EXCEL
Scrool trong VBA EXCEL
SpinButton trong VBA EXCEL
Frame trong VBA EXCEL
Chèn hình trong VBA EXCEL
PHẦN II: VBA NÂNG CAO TRONG EXCEL
Bài 1. Mảng và các thuật toán với mảng trong VBA EXCEL
Khai báo mảng trong VBA EXCEL
a. Tìm hiểu về Dim, Redim, Redim Preseve trong VBA EXCEL
b. Tìm hiểu về kích thước mảng với Resize trong VBA EXCEL
Gán giá trị cho mảng trong VBA EXCEL
a. Gán dữ cho Mảng từ dữ liệu từ một vùng trong VBA EXCEL
b. Sử dụng For Next, For Each gán dữ liệu cho mảng trong VBA EXCEL
Lấy dữ liệu từ mảng trong VBA EXCEL
Mảng một chiều trong VBA EXCEL
Mảng hai chiều trong VBA EXCEL
Mảng động trong VBA EXCEL
Mảng tĩnh trong VBA EXCEL
Các phương thức của mảng trong VBA EXCEL
Bài 2. Dictionnary trong VBA EXCEL- Tổng hợp hàng trăm ngàn dòng chưa đến 1 giây
Thiết lập môi trường cho Dictionnary
Khai báo sớm, khai báo muộn cho Dictionnary
Key và Item trong Dictionnary
Các phương thức của Dictionnary
a. Add
b. Exists
c. Count
d. Remove
Sử dụng vòng lặp For Next duyệt và nặp dữ liệu cho Dictionnary
Sử dụng For Each để duyệt và lấy dữ liệu từ Dictionnary vào Mảng
Bài tập thực hành
a. Tổng hợp dữ liệu b. Lấy dữ liệu duy nhất c. Test tốc độ siêu nhanh khi sử dụng Dictionnary
Bài 3. Colecttion trong VBA EXCEL và bài toán tốc độ nhanh so với Dictionnary
Bài 4. ADO SQL trong VBA EXCEL
Thiết lập môi tường sử dụng ADO
Khai báo sớm và khai báo muộn cho ADO
Thiết lập kết nối
Mở kết nối
Sử dụng các từ khóa SQL
SQL Select
SQL Select Distinct
SQL Where
SQL And, Or, Not
SQL Order By
SQL Insert Into
SQL Null Values
SQL Select Top
SQL Min and Max
SQL Count, Avg, Sum
SQL Like
SQL Wildcards
SQL In
SQL Between
SQL Aliases
SQL Joins
SQL Inner Join
SQL Left Join
SQL Right Join
SQL Full Join
SQL Union
SQL Group By
SQL Having
SQL Exists
SQL Select Into
SQL Insert Into Select
Bài tập Thực hành
Ghi dữ liệu từ Sheet này sang Sheet khác
Đọc dữ liệu từ Workbook đang đóng
Ghi dữ liệu vào Workbook đang đóng
Tổng hợp dữ liệu từ nhiều Sheet vào một Sheet
Tổng hợp dữ liệu từ nhiều File đang đóng
Tổng hợp dữ liệu duy nhất
Bài 5: Kỹ thuật lập trình kết hợp Mảng(Array), Dictionnary, ADO trong tổng hợp dữ liệu
PHẦN III: MỘT SỐ CHUYÊN ĐỀ
Chuyên đề 1. FileSystemObject(FSO) xử lý Driver, File, Folder trong VBA EXCEL
Khởi tạo kích hoạt công cụ FSO
Các phương thức phổ biến a. CreateFolder (dùng để tạo 1 thư mục mới) b. DeleteFolder (dùng để xóa 1 thư mục có sẵn) c. FolderExists (dùng để kiểm tra xem thư mục có tồn tại hay không) d. CopyFolder (dùng để copy 1 folder từ nơi này đến nơi khác) e. GetParentFolderName (dùng để lấy tên thư mục mẹ của 1 Folder hoặc của 1 file) f. GetAbsolutePathName (dùng để lấy tên đường dẫn đầy đủ của 1 file hoặc 1 thư mục) g. GetBaseName (dùng để lấy tên của 1 file không kèm theo phần mở rộng) h. GetExtensionName (dùng để lấy phần mở rộng của 1 file) i. CopyFile (dùng để copy file(s) từ 1 thư mục đến thư mục khác) j. DeleteFile (dùng để xoá 1 file có thuộc tính ẩn hoặc không ẩn) k. FileExists (dùng để kiểm tra sự tồn tại của 1 tập tin) l. CreateTextFile (dùng để xuất dữ liệu thành 1 file dạng txt) m. OpenTextFile (dùng để mở 1 file txt)
Chuyên đề 2: Sử dụng Dir, Path tìm đường dẫn cho File, Folder, ổ đĩa trong VBA EXCEL
Chuyên đề 3: Thêm Nút lệnh vào Menu Ribbon trong VBA EXCEL
PHẦN IV: BÀI TẬP ỨNG DỤNG THỰC TẾ CÔNG VIỆC VỚI VBA EXCEL
Hướng Dẫn Cách Dùng Hàm Mod Trong Excel Thông Qua Ví Dụ
Hàm MOD trong Excel là hàm chia lấy số dư cho ước số và kết quả trả về sẽ cùng dấu với ước số. Về cơ bản thì hàm MOD này không có gì là khó sử dụng cả. Hàm MOD thường được kết hợp với các hàm khác trong Excel để tiện cho việc tính toán. Bài viết này mình sẽ hướng dẫn bạn cách sử dụng hàm MOD trong Excel qua ví dụ cụ thể để bạn biết cách sử dụng cũng như nắm rõ được cú pháp của hàm này.
Hướng dẫn cách dùng hàm MOD trong Excel
Hàm MOD là hàm Excel cơ bản nên có thể sử dụng hàm MOD trên các phiên bản Excel 2007, 2010, 2013 và 2016 trở lên. Ngoài ra, hàm này cũng sử dụng được trên các phiên bản Excel cho Mac, Ipad và trên điện thoại Iphone, Android. Trước khi bắt đầu tìm hiểu về cách dùng hàm MOD thì bạn nên cài đặt Office 2010 hoặc Office 2013 lên máy tính trước để có thể sử dụng Excel và học thực hành theo các bước.
Cú pháp của hàm MOD
Các giá trị trong hàm MOD
Number: Là số bị chia (số cần tìm số dư). Đây là giá trị bắt buộc.
Divisor: Là số chia. Đây là giá trị bắt buộc.
Lưu ý khi dùng hàm MOD
Nếu số chia là 0, thì hàm MOD trả về giá trị lỗi #DIV/0!
Kết quả của hàm MOD trả về cùng dấu với số chia (không quan tâm tới dấu của số bị chia).
Hàm MOD có thể được biểu đạt bằng các số hạng của hàm INT: MOD(n, d) = n – d*INT(n/d)
Ví dụ về hàm MOD
Yêu cầu: Tính số dư sau khi lấy Number chia cho Divisor
Ở đây chúng ta sẽ áp dụng công thức của hàm MOD để tính toán và ta sẽ có công thức cho ô E4 như sau =MOD(C4,D4). Kết quả thu được như sau:
Copy công thức tương ứng xuống các ô bên dưới chúng ta có kết quả như sau:
Bạn có thể thấy:
Kết quả ô E5: Số chia là 0 nên kết quả trả về lỗi #DIV/0!
Kết quả ô E6: Số bị chia là 0 nên kết quả trả về là 0.
Kết quả ô E7: Số chia mang dấu âm nên kết quả mang dấu âm.
Kết quả ô E8: Số bị chia mang dấu âm không ảnh hưởng đến kết quả.
Kết quả ô E9: Số bị chia và số chia mang dấu âm thì kết quả vẫn mang dấu âm cùng dấu với số chia.
Bạn đang đọc nội dung bài viết Thành Thạo Pivot Table Trong Excel Thông Qua Ví Dụ Thực Tế 1/2021 trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!