Data Analysis with Excel: Pivot Tables

Trong bài viết này chúng ta sẽ tìm hiểu tất tần tật chủ đề Pivot Tables trong Excel nhé!

Note: Bạn có thể kéo xuống dưới cùng để download tài liệu của bài viết này.

Pivot Tables

Pivot Tables là một công cụ mạnh mẽ để tính toán, tóm tắt và phân tích dữ liệu trong Excel, cho phép bạn xem so sánh, mẫu hình và xu hướng trong dữ liệu của mình.

Chúng ta sẽ có tập dữ  bao gồm 213 records và 6 fields gồm Order ID, Product, Category, Amount, Date và Country.

Insert a Pivot Table

Để chèn một Pivot Table, bạn hãy thực hiện các bước sau.

1. Click vào ô đơn bất kỳ trong tập dữ liệu.

2. Trên tab , trong nhóm Tables, click PivotTable.

Sẽ có hộp thoại sau xuất hiện. Lúc này, Excel sẽ tự động chọn dữ liệu cho bạn, vị trí mặc định cho một Pivot Table mới sẽ là New Worksheet.

3. Click OK.

Drag fields

Pivot Table Fields pane xuất hiện. Nếu bạn muốn biết tổng số tiền của sản phẩm nào, thì bạn hãy kéo các fields đó vào các khu vực tương ứng.

1. Product field đến Rows area.

2. Amount field đến Values area.

3. Country field đến Filters area.

Bạn có thể tìm thấy pivot table dưới đây. Chuối sẽ là mặt hàng xuất khẩu chính trong ví dụ này.

Sort

Hãy thực hiện các bước sau nếu bạn muốn sắp xếp  Chuối đứng đầu danh sách.

1. Click vào bất kỳ ô nào bên trong Sum of Amount column.

2. Click chuột phải và click on Sort, Sort Largest đến Smallest.

Kết quả:

Filter

Chúng ta sẽ thêm Country field vào Filters area, nên lúc này, bạn có thể lọc pivot table theo Country. Ví dụ, chúng ta có thể xem sản phẩm nào được xuất khẩu nhiều nhất sang Pháp?

1. Nhấp vào filter drop-down và chọn France.

Kết quả, Táo là sản phẩm chính mà chúng ta xuất khẩu sang Pháp.

Lưu ý: bạn có thể sử dụng standard filter (Triangle bên cạnh Row Labels) để chỉ hiển thị số lượng sản phẩm cụ thể.

Change Summary Calculation

Theo mặc định, Excel sẽ tóm tắt dữ liệu của bạn bằng cách tính tổng hoặc đếm các mục. Để thay đổi theo kiểu tính toán mà bạn muốn sử dụng, hãy thực hiện các bước sau.

1. Click vào bất kỳ ô nào bên trong cột Sum of Amount.

2. Click chuột phải và click  Value Field Settings.

3. Chọn kiểu tính toán bạn muốn sử dụng. Ví dụ: click Count.

4. Nhấp vào OK.

Cho ra kết quả, trong 28 đơn đặt hàng đến Pháp thì có 16 đơn đặt hàng là Táo.

Two-dimensional Pivot Table

Nếu bạn kéo một field vào Rows area và Columns area, bạn có thể tạo two-dimensional pivot table. Đầu tiên, bạn hãy chèn pivot table. Tiếp theo, để nhận tổng số tiền được xuất sang từng quốc gia, của từng sản phẩm, hãy kéo các fields sau vào các khu vực khác nhau.

1. Country field đến Rows area.

2. Product field vào vùng Columns area.

3. Amount field đến Values area.

4. Category field đến Filters area.

Bạn có thể thấy two-dimensional pivot table dưới đây.

Để có thể dễ dàng so sánh những con số này, bạn hãy tạo pivot chart và áp dụng filter. Có thể đây là một bước quá xa đối với bạn ở giai đoạn này, nhưng nó cho bạn thấy một trong nhiều tính năng powerful pivot table khác mà Excel phải cung cấp.

Group Pivot Table Items

Ví dụ này sẽ hướng dẫn bạn cách nhóm các mục trong pivot table. Chúng ta sẽ cùng tìm hiểu cách nhóm các sản phẩm và cách nhóm ngày theo quý.

Dưới đây bạn có thể tìm thấy pivot table. Nếu chưa biết cách tạo, bạn có thể lướt lên trên để xem lại cách tạo nha!

Group Products

Product field sẽ chứa 7 mục gồm Táo, Chuối, Đậu, Bông cải xanh, Cà rốt, Xoài và Cam.

Để tạo thành hai nhóm, bạn hãy thực hiện các bước sau.

1. Trong bảng tổng hợp, chọn Táo và Chuối.

2. Click chuột phải và click on Group.

3. Trong pivot table, chọn Đậu, Bông cải xanh, Cà rốt, Xoài và Cam.

4. Click chuột phải và click on Group.

Kết quả:

Lưu ý: để thay đổi tên của một nhóm (Nhóm 1 hoặc Nhóm 2), hãy chọn tên và chỉnh sửa tên trong formula bar. Để hủy nhóm, chọn group, click chuột phải và click on Ungroup.

5. Để thu gọn các nhóm lại bạn hãy click vào dấu trừ.

Kết luận: Táo và Chuối (Nhóm 1) có tổng số sản phẩm cao hơn tất cả các sản phẩm còn lại (Nhóm 2).

Group Dates

Để tạo pivot table bên dưới, thay vì Product field, hãy thêm Date field vào Rows area. Date field chứa nhiều mục. 6-Jan, 7-Jan, 8-Jan, 10-Jan, 11-Jan,...

Để nhóm các ngày này theo quý, bạn hãy thực hiện các bước sau.

1. Nhấp vào bất kỳ ô nào bên trong cột có ngày tháng.

2. Click chuột phải và click on Group.

3. Chọn Quarters và click OK.

Kết quả:

Kết luận: Quý 2 là quý tốt nhất.

Multi-level Pivot Table

Bạn hoàn toàn có thể kéo nhiều field vào một vùng trong pivot table. Chúng ta sẽ xem xét một ví dụ về multiple row fields, multiple value fields và multiple report filter fields.

Hãy nhớ rằng, tập dữ liệu của chúng ta có 213 records và 6 fields gồm Order ID, Product, Category, Amount, Date và Country.

Multiple Row Fields

Đầu tiên, bạn hãy chèn một pivot table. Tiếp đến, kéo các fields sau vào các khu vực khác nhau.

1. Category field và Country field vào Rows area.

2. Amount field cho vùng Values area.

Bạn có thể tìm thấy multi-level pivot table dưới đây.

Multiple Value Fields

Đầu tiên, bạn hãy chèn một pivot table. Tiếp theo, bạn sẽ kéo các fields sau vào các khu vực khác nhau.

1. Country field thành Rows area.

2. Amount field thành Values area (2x).

Lưu ý: nếu bạn kéo Amount field vào Values area lần thứ hai, Excel cũng sẽ tự động điền Columns area.

Pivot table:

3. Tiếp theo, bạn click vào bất kỳ ô nào bên trong Sum of Amount2 column.

4. Click chuột phải và click vào Value Field Settings.

5. Nhập Percentage cho Custom Name.

6. Trên tab Show Values As, chọn % of Grand Total.

7. Click OK.

Kết quả:

Multiple Report Filter Fields

Đầu tiên, bạn hãy chèn một pivot table. Tiếp theo, bạn sẽ kéo các fields sau vào các khu vực khác nhau.

1. Order ID đến Rows area.

2. Amount field cho Values area.

3. Country field and Product field vào Filters area.

4. Tiếp theo, chọn United Kingdom từ filter drop-down đầu tiên và Bông cải xanh từ filter drop-down thứ hai.

Bảng pivot table sẽ hiển thị tất cả các đơn hàng có liên quan đến "Bông cải xanh" được đặt hàng đến United Kingdom.

Frequency Distribution

Bạn có thể sử dụng pivot tables để dễ dàng tạo frequency distribution trong Excel. Bên cạnh đó, bạn cũng có thể sử dụng Analysis Toolpak để tạo histogram (biểu đồ).

Chúng ta sẽ có tập dữ  bao gồm 213 records và 6 fields gồm Order ID, Product, Category, Amount, Date và Country.

Đầu tiên, bạn hãy chèn một pivot table. Tiếp theo, bạn sẽ kéo các fields sau vào các khu vực khác nhau.

1. Amount field vào Rows area.

2. Amount field (or any other field) vào Values area.

3. Click vào bất kỳ ô nào bên trong cột Sum of Amount.

4. Click chuột phải và click vào Value Field Settings.

5. Chọn Count và click OK.

6. Tiếp theo, bạn click vào ô bất kỳ bên trong cột có Row Labels.

7. Click chuột phải và click vào Group.

8. Nhập 1 cho Starting at, 10000 cho Ending at, và 1000 cho By.

9. Click OK.

Kết quả:

Để dễ dàng so sánh những con số này, bạn hãy tạo pivot chart.

10. Click vào bất kỳ ô nào bên trong pivot table.

11. Trên tab PivotTable Analyze, trong nhóm Tools, click PivotChart.

Hộp thoại Insert Chart xuất hiện.

12. Click OK.

Kết quả:

Pivot Chart

Pivot Chart là biểu diễn trực quan của pivot table trong Excel. Pivot Chart và pivot table được kết nối với nhau. Dưới đây, bạn có thể tìm thấy two-dimensional pivot table.

Insert Pivot Chart

Để chèn pivot chart, bạn hãy thực hiện các bước sau.

1. Click vào bất kỳ ô nào bên trong pivot table.

2. Trên tab PivotTable Analyze, trong nhóm Tools, click PivotChart.

Hộp thoại Insert Chart xuất hiện.

3. Click OK.

Bạn có thể tìm thấy pivot chart dưới đây. Pivot chart này sẽ tạo ấn tượng và khiến sếp bạn ngạc nhiên đấy!

Lưu ý: bất kỳ thay đổi nào bạn thực hiện đối với pivot chart sẽ được phản ánh ngay lập tức trong pivot table và ngược lại.

Filter Pivot Chart

Để Filter Pivot Chart này, bạn hãy thực hiện các bước sau.

1. Sử dụng các standard filters (Triangles bên cạnh Product và Country) Ví dụ: sử dụng Country filter để chỉ hiển thị tổng số lượng từng sản phẩm được xuất khẩu sang Hoa Kỳ.

2. Xóa Country filter.

3. Bởi vì chúng ta đã thêm Category field vào Filters area bằng Category. Ví dụ: sử dụng Category filter để hiển thị các loại rau được xuất khẩu sang từng quốc gia.

Change Pivot Chart Type

Bạn có thể thay đổi sang một loại pivot chart khác bất kỳ lúc nào bằng cách.

1. Chọn biểu đồ.

2. Trên tab Design, trong nhóm Type, click Change Chart Type.

3. Chọn Pie.

4. Click OK.

Kết quả:

Lưu ý: Pie charts luôn sử dụng data series (trong trường hợp này là Beans). Để có được pivot chart của một quốc gia, hãy hoán đổi data qua axis. Đầu tiên, bạn hãy chọn biểu đồ. Tiếp theo, trên tab Design, trong nhóm Data, click Switch Row/Column.

Slicers

Sử dụng slicers trong Excel để filter pivot tables một cách nhanh chóng và dễ dàng. Bạn có thể kết nối nhiều slicers với nhiều pivot tables để tạo ra các báo cáo tuyệt vời.

Dưới đây bạn có thể tìm thấy pivot table.

Để chèn một slicer, bạn hãy thực hiện các bước sau.

1. Click vào bất kỳ ô nào bên trong pivot table.

2. Trên tab PivotTable Analyze, trong nhóm Filter, click Insert Slicer.

3. Kiểm tra Country và click OK.

4. Click United States để tìm hiểu xem chúng ta xuất khẩu sản phẩm nào nhiều nhất sang United States.

Kết luận: Chuối là mặt hàng mà chúng ta xuất khẩu nhiều nhất sang Hoa Kỳ. Filter báo cáo (ô B1) thay đổi thành United States.

5. Click vào bất kỳ ô nào bên trong pivot table.

6. Trên tab PivotTable Analyze, trong nhóm Filter, click Insert Slicer.

7. Kiểm tra Product và click OK.

8. Chọn slicer.

9. Trên tab Slicer, trong nhóm Slicer Styles, click a slicer style.

10. Sử dụng second slicer. Click vào nút Multi-Select để chọn nhiều sản phẩm.

Lưu ý: thay vì sử dụng nút Multi-Select, hãy giữ CTRL để chọn nhiều mục.

11. Chèn pivot table thứ hai.

Để kết nối cả hai slicers với pivot table này, bạn hãy thực hiện các bước sau.

12. Chọn slicer đầu tiên.

13. Trên tab Slicer, trong nhóm Slicer, click Report Connections.

14. Chọn pivot table thứ hai và click OK.

15.  Lặp lại các bước 12-14 cho slicer thứ hai.

16. Sử dụng cả hai slicers.

Kết luận: số lượng táo xuất khẩu sang Canada bằng $ 24,867 (6 đơn hàng) và tổng lượng cam xuất khẩu sang Canada bằng $ 19,929 (3 đơn hàng).

17. Click vào biểu tượng ở góc trên bên phải của slicer để xóa filter.

Update Pivot Table

Những thay đổi mà bạn thực hiện đối với tập dữ liệu sẽ không được pivot table chọn tự động. Bạn cần làm mới pivot table hoặc thay đổi nguồn dữ liệu để cập nhập pivot table với các thay đổi được áp dụng.

Refresh

Nếu bạn muốn thay đổi text hoặc numbers bất kỳ trong tập dữ liệu của mình, bạn cần làm mới pivot table.

1. Click vào bất kỳ ô nào bên trong pivot table.

2. Click chuột phải và click on Refresh.

Change Data Source

Nếu bạn muốn thay đổi kích thước tập dữ liệu bằng cách thêm hoặc xóa hàng / cột, bạn cần cập nhật dữ liệu nguồn cho pivot table.

1. Click vào bất kỳ ô nào bên trong pivot table.

2. Trên tab PivotTable Analyze, trong nhóm Data, click Change Data Source.

Bí kíp: Bạn hãy thay đổi tập dữ liệu thành một table trước khi bạn chèn một pivot table. Bằng cách này, nguồn dữ liệu của bạn sẽ được cập nhật tự động khi bạn thêm hoặc xóa hàng / cột. Điều này sẽ giúp bạn tiết kiệm được thời gian.

Calculated Field/Item

Ví dụ này sẽ hướng dẫn bạn cách chèn calculated field hoặc calculated trong pivot table. Dưới đây bạn có thể tìm thấy pivot table.

Calculated Field

Một calculated field sử dụng các giá trị từ một field khác. Để chèn calculated field, bạn hãy thực hiện các bước sau.

1. Click vào bất kỳ ô nào bên trong pivot table.

2. Trên tab PivotTable Analyze, trong nhóm Calculations, click Fields, Items & Sets.

3. Click Calculated Field.

Hộp thoại Insert Calculated Field xuất hiện.

4. Nhập Tax cho Name.

5. Nhập công thức = IF(Amount>100000, 3%*Amount, 0)

6. Click Add.

Lưu ý: Sử dụng nút Insert Field để nhanh chóng chèn các fields khi bạn nhập công thức. Để xóa một calculated field, bạn hãy chọn fields và click Delete (under Add).

7. Click OK.

Excel sẽ tự động thêm Tax field vào Values area.

Kết quả:

Calculated Item

Một calculated item sử dụng các giá trị từ các mục khác. Để chèn calculated, bạn hãy thực hiện các bước sau.

1. Click vào bất kỳ Country nào có trong pivot table.

2. Trên tab PivotTable Analyze, trong nhóm Calculations, hclick Fields, Items & Sets.

3. Click Calculated Item.

Hộp thoại Insert Calculated Item xuất hiện.

4. Nhập Oceania cho Name.

5. Nhập công thức =3%*(Australia+'New Zealand')

6. Click Add.

Lưu ý: sử dụng nút Insert Item để nhanh chóng chèn các mục khi bạn nhập công thức. Để xóa một calculated item, bạn hãy chọn mục đó và click Delete (under Add).

7. Lặp lại các bước tương tự từ 4 đến 6 đối với North America (Canada and United States) và Europe (France, Germany và United Kingdom) với thuế suất lần lượt là 4% và 5%.

8. Click OK.

Kết quả:

Lưu ý: Chúng ta đã tạo hai nhóm (Sales and Taxes).

GetPivotData

Để nhập nhanh hàm GETPIVOTDATA trong Excel, bạn hãy nhập dấu bằng (=) và nhấp vào một ô trong pivot table. Hàm GETPIVOTDATA có thể khá hữu ích cho bạn.

1. Đầu tiên, bạn hãy chọn ô B14 bên dưới và nhập = D7 (không nhấp vào ô D7 trong pivot table) để tham chiếu lượng Đậu xuất khẩu sang Pháp.

2. Sử dụng filter để chỉ hiển thị số lượng rau được xuất khẩu sang mỗi quốc gia.

Lưu ý: ô B14 hiện tham chiếu lượng cà rốt xuất khẩu sang Pháp, không phải lượng đậu, GETPIVOTDATA sẽ giúp bạn.

3. Tháo filter. Chọn lại ô B14, nhập dấu bằng (=) và nhấp vào ô D7 trong pivot table.

Lưu ý: Excel sẽ tự động chèn hàm GETPIVOTDATA được hiển thị ở trên.

4. Một lần nữa, bạn hãy sử dụng filter để chỉ hiển thị số lượng rau được xuất khẩu sang mỗi quốc gia.

Lưu ý: hàm GETPIVOTDATA sẽ cho ra kết quả chính xác về số lượng Đậu xuất khẩu sang Pháp.

5. Hàm GETPIVOTDATA chỉ có thể trả về dữ liệu hiển thị. Ví dụ: sử dụng filter để hiển thị số lượng trái cây được xuất khẩu sang mỗi quốc gia.

Lưu ý: hàm GETPIVOTDATA trả về #REF! error vì giá trị 680 (Đậu xuất khẩu sang Pháp) không hiển thị.

6. Hàm GETPIVOTDATA động bên dưới trả về số lượng Xoài xuất khẩu sang Canada.

Lưu ý: hàm GETPIVOTDATA này có 6 arguments (data field, một tham chiếu đến bất kỳ ô nào bên trong pivot table và 2 field/item pairs). Tạo drop-down list trong ô B14 và ô B15 để nhanh chóng chọn mục đầu tiên và mục thứ hai (xem tệp Excel có thể tải xuống).

7. Hàm GETPIVOTDATA bên dưới có 4 arguments (data field, một tham chiếu đến bất kỳ ô nào bên trong pivot table và 1 field/item pairs) và trả về tổng số tiền đã xuất sang USA.

8. Nếu tổng số tiền được xuất khẩu sang USA có sự thay đổi (ví dụ: bằng cách sử dụng filter), giá trị được trả về bởi hàm GETPIVOTDATA cũng sẽ có sự thay đổi.

Nếu bạn không muốn Excel tự động chèn hàm GETPIVOTDATA, bạn có thể tắt tính năng này bằng cách.

9. Click vào bất kỳ ô nào bên trong pivot table.

10. Trên tab PivotTable Analyze, trong nhóm PivotTable, hclick the drop-down bên cạnh Options và bỏ chọn Generate GetPivotData.

Tài liệu của series

Để chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé!

Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab!

Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp thì bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây.

Bài viết tiếp theo cùng series:

• Data Analysis with Excel: Tables