Bài Tập Excel Pivot Table: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Giải Bài Toán Vận Tải Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Giới thiệu chung về Pivot Table

Pivot Table (bảng tổng hợp) là một trong những công cụ mạnh mẽ nhất của Microsoft Excel, giúp người dùng nhanh chóng tổng hợp, phân tích và trình bày dữ liệu một cách trực quan. Thay vì phải viết công thức phức tạp hoặc thực hiện các bước lọc thủ công, bạn chỉ cần kéo thả các trường dữ liệu vào các khu vực khác nhau của Pivot Table để nhận được những báo cáo tóm tắt ngay lập tức. Đối với những người mới bắt đầu, Pivot Table có thể gây cảm giác khó khăn, nhưng với các bài tập thực tế, bạn sẽ nhanh chóng làm chủ công cụ này.

Trong bài viết này, chúng ta sẽ đi qua:

1. Cấu trúc cơ bản của Pivot Table
2. Các bước tạo Pivot Table từ dữ liệu thô
3. Bài tập thực hành từ cơ bản đến nâng cao
4. Mẹo và thủ thuật tối ưu hoá Pivot Table
5. Giải đáp một số câu hỏi thường gặp

Mục tiêu cuối cùng là giúp bạn không chỉ hiểu lý thuyết mà còn có thể áp dụng ngay vào công việc thực tế, từ báo cáo doanh thu, phân tích khách hàng, đến quản lý tồn kho.

Có thể bạn quan tâm: Bài Toán Solver Trong Excel: Khám Phá, Ứng Dụng Và Các Bước Thực Hiện Chi Tiết

1. Cấu trúc cơ bản của Pivot Table

Pivot Table được chia thành bốn khu vực chính:

Khi bạn kéo một trường dữ liệu vào khu vực Rows, Excel sẽ tự động nhóm các giá trị duy nhất của trường đó và tạo các hàng tương ứng. Tương tự, khi kéo vào Columns, các giá trị duy nhất sẽ được tạo thành các cột. Các trường kéo vào Values sẽ được tính toán theo hàm mặc định (thường là SUM) hoặc hàm bạn chọn. Cuối cùng, Filters giúp bạn hiển thị một phần dữ liệu mà bạn quan tâm.

Có thể bạn quan tâm: Bài Test Excel Tuyển Dụng: Hướng Dẫn Chi Tiết, Mẫu Câu Hỏi Và Cách Đánh Giá Ứng Viên

2. Các bước tạo Pivot Table từ dữ liệu thô

Bước 1: Chuẩn bị dữ liệu

• Dữ liệu dạng bảng: Đảm bảo dữ liệu của bạn được tổ chức dưới dạng bảng (có tiêu đề cột, không có hàng trống).
• Không có dữ liệu trùng lặp tiêu đề: Nếu có các cột có cùng tên, Excel sẽ tự động đổi tên (ví dụ: Sales và Sales2).
• Kiểm tra kiểu dữ liệu: Các cột số (số lượng, doanh thu) phải ở dạng Number; các cột ngày tháng phải ở dạng Date.

Lưu ý: Nếu dữ liệu của bạn thường xuyên thay đổi (thêm, xóa, cập nhật), hãy chuyển nó thành Table (Ctrl + T) để Pivot Table tự động mở rộng.

Bước 2: Chèn Pivot Table

1. Chọn bất kỳ ô nào trong bảng dữ liệu.
2. Vào Insert → PivotTable.
3. Trong hộp thoại, chọn New Worksheet hoặc Existing Worksheet (đặt vị trí tùy ý).
4. Nhấn OK.

Bước 3: Xây dựng bố cục

• Kéo trường Category (hoặc bất kỳ trường nào) vào khu vực Rows.
• Kéo trường Region vào Columns.
• Kéo trường Sales vào Values (Excel sẽ mặc định tính Sum).
• Nếu muốn lọc theo năm, kéo trường Year vào Filters.

Bước 4: Định dạng và tùy chỉnh

• Thay đổi hàm tính: Nhấp chuột phải vào trường trong Values → Value Field Settings → chọn Average, Count, Max, …
• Hiển thị số thập phân: Click vào Number Format trong cùng hộp thoại.
• Thêm tổng cộng: Trong Design → Grand Totals, bật/tắt tổng cộng cho Rows và Columns.
• Thêm Subtotals: Design → Subtotals, chọn vị trí và kiểu hiển thị.

3. Bài tập thực hành từ cơ bản đến nâng cao

Bài tập 1: Tổng doanh thu theo khu vực và tháng (Cơ bản)

Dữ liệu mẫu: Bảng SalesData gồm các cột: OrderID, Date, Region, Product, Quantity, UnitPrice.

Mục tiêu: Xây dựng Pivot Table hiển thị tổng doanh thu (Quantity UnitPrice) cho mỗi khu vực (Region) theo từng tháng.

Các bước:

1. Thêm cột tính doanh thu trong bảng gốc:
2. Vào Table Tools → Design → Add Column và nhập công thức =QuantityUnitPrice, đặt tên là Revenue.
3. Chọn bất kỳ ô nào trong SalesData → Insert → PivotTable → New Worksheet.
4. Kéo Region vào Rows.
5. Kéo Date vào Columns, sau đó click chuột phải vào một ngày → Group → chọn Months và Years (để có cả năm).
6. Kéo Revenue vào Values, để hàm mặc định là Sum.
7. Định dạng cột Values thành tiền tệ (Currency).

Kết quả: Bảng tổng hợp hiển thị doanh thu mỗi tháng cho từng khu vực, kèm tổng cộng năm.

Bài tập 2: Đếm số đơn hàng theo loại sản phẩm và kênh bán (Trung cấp)

Dữ liệu mẫu: Bảng Orders gồm: OrderID, ProductCategory, SalesChannel, OrderDate, CustomerID.

Mục tiêu: Đếm số lượng đơn hàng (không tính trùng) cho mỗi loại sản phẩm (ProductCategory) trên từng kênh bán (SalesChannel).

Các bước:

1. Chèn Pivot Table từ bảng Orders.
2. Kéo ProductCategory vào Rows.
3. Kéo SalesChannel vào Columns.
4. Kéo OrderID vào Values → Value Field Settings → chọn Count (đếm số lần xuất hiện).
5. Để loại bỏ trùng lặp (nếu một đơn hàng có nhiều dòng), bạn có thể tạo Calculated Field hoặc Distinct Count (có trong Excel 2026 trở lên):
6. Vào PivotTable Analyze → Fields, Items & Sets → Calculated Field → đặt công thức =1 và đặt tên CountDistinct.
7. Hoặc trong Value Field Settings, chọn Distinct Count (nếu có).

Kết quả: Bảng hiển thị số đơn hàng duy nhất cho mỗi kết hợp ProductCategory – SalesChannel.

Bài tập 3: Phân tích lợi nhuận theo nhân viên bán hàng và quý (Nâng cao)

Dữ liệu mẫu: Bảng SalesTeam gồm: SaleID, SalesRep, Date, Product, Quantity, UnitPrice, CostPerUnit.

Mục tiêu: Tính lợi nhuận ((UnitPrice - CostPerUnit) Quantity) cho mỗi nhân viên bán hàng (SalesRep) theo quý (Q1, Q2, Q3, Q4). Đồng thời, hiển thị tỷ lệ lợi nhuận so với doanh thu.

Các bước chi tiết:

1. Thêm cột tính lợi nhuận:

2. Trong bảng SalesTeam, tạo cột Profit với công thức =(QuantityUnitPrice) - (QuantityCostPerUnit).

3. Tạo Pivot Table:

4. Chọn bảng → Insert → PivotTable → New Worksheet.

5. Bố trí trường:

6. SalesRep → Rows.
7. Date → Columns, rồi Group → chọn Quarters và Years.
8. Profit → Values (Sum).

9. Revenue (tạo cột Revenue = QuantityUnitPrice nếu chưa có) → Values (Sum).

10. Tính tỷ lệ lợi nhuận:

11. Click PivotTable Analyze → Fields, Items & Sets → Calculated Field.
12. Đặt tên ProfitMargin, công thức: =Profit/Revenue.

13. Kéo ProfitMargin vào Values, đổi Number Format thành Percentage với 2 chữ số thập phân.

14. Định dạng hiển thị:

15. Bật Grand Totals cho Rows và Columns.
16. Sử dụng Conditional Formatting → Data Bars cho cột ProfitMargin để dễ so sánh.

Kết quả: Bảng tổng hợp cho thấy lợi nhuận và tỷ lệ lợi nhuận của từng nhân viên theo quý, kèm tổng cộng năm.

Bài tập 4: Sử dụng Slicers và Timeline để tạo báo cáo tương tác (Nâng cao)

Dữ liệu mẫu: Bảng RetailSales gồm: TransactionID, Store, Date, Category, Amount.

Mục tiêu: Xây dựng Pivot Table với các Slicer cho Store và Timeline cho Date để người dùng có thể lọc nhanh theo cửa hàng và khoảng thời gian.

Các bước:

1. Tạo Pivot Table từ RetailSales.
2. Kéo Category vào Rows, Amount vào Values (Sum).
3. Kéo Date vào Columns, nhóm theo Months và Years.
4. Chèn Slicer:
5. Chọn Pivot Table → Insert → Slicer → chọn Store.
6. Đặt slicer ở vị trí thuận tiện, thay đổi màu sắc, kích thước.
7. Chèn Timeline:
8. Chọn Pivot Table → Insert → Timeline → chọn Date.
9. Timeline cho phép kéo thả để chọn tháng, quý hoặc năm.

Kết quả: Báo cáo tương tác, người dùng chỉ cần click vào slicer hoặc kéo timeline để xem dữ liệu của một cửa hàng hoặc khoảng thời gian nhất định.

Có thể bạn quan tâm: Bài Tập Các Hàm Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Bài tập 5: Pivot Table kết hợp với Power Query để làm sạch dữ liệu (Chuyên sâu)

Kịch bản: Bạn nhận được file CSV từ nhà cung cấp, trong đó có:

• Dòng tiêu đề lặp lại mỗi 1000 bản ghi.
• Cột Price chứa ký tự $ và dấu phẩy (ví dụ: $1,200.50).
• Một số bản ghi có ngày tháng ở định dạng dd-mm-yyyy, còn một số khác là mm/dd/yyyy.

Mục tiêu: Dùng Power Query để làm sạch dữ liệu, sau đó tạo Pivot Table tổng hợp doanh thu theo năm và nhà cung cấp.

Các bước:

1. Import dữ liệu bằng Power Query:
2. Data → Get Data → From Text/CSV → Chọn file CSV.

3. Trong Power Query Editor, dùng Remove Top Rows để loại bỏ các dòng tiêu đề lặp lại (có thể dùng Keep Rows → Keep Range of Rows).

4. Làm sạch cột Price:

5. Chọn cột Price → Transform → Replace Values → thay $ bằng rỗng.
6. Tiếp tục Replace Values → thay , bằng rỗng.

7. Đổi kiểu dữ liệu sang Decimal Number.

8. Chuẩn hoá ngày tháng:

9. Thêm cột tùy chỉnh: = Date.FromText(Date) – Power Query sẽ tự nhận dạng định dạng.

10. Nếu có lỗi, dùng Conditional Column để kiểm tra định dạng bằng Text.Contains và chuyển đổi phù hợp.

11. Load dữ liệu vào Excel:

12. Home → Close & Load To… → chọn Table trong một sheet mới.

13. Tạo Pivot Table:

14. Chọn bảng đã Load → Insert → PivotTable.
15. Supplier → Rows.
16. Date (được nhóm thành Years) → Columns.
17. Price → Values (Sum).

Kết quả: Dữ liệu đã sạch, Pivot Table cho thấy doanh thu hàng năm theo từng nhà cung cấp, sẵn sàng cho quyết định mua hàng.

4. Mẹo và thủ thuật tối ưu hoá Pivot Table

4.1. Sử dụng GetPivotData để tạo báo cáo tùy chỉnh

Giả sử bạn có Pivot Table PivotTable1 tổng hợp doanh thu theo khu vực và năm. Để lấy doanh thu của khu vực “Hanoi” năm 2026, bạn dùng:

=GETPIVOTDATA("Revenue", $A$3, "Region", "Hanoi", "Year", 2026)

Công thức này sẽ tự động cập nhật khi Pivot Table thay đổi, tránh việc phải sao chép/đánh giá lại số liệu.

4.2. Khi nào nên dùng Power Pivot vs Pivot Table truyền thống?

• Power Pivot: Khi bạn cần liên kết nhiều bảng (ví dụ: bảng Orders, Customers, Products) và thực hiện các phép tính DAX phức tạp (ví dụ: Year‑to‑Date, Moving Average).
• Pivot Table truyền thống: Khi dữ liệu nằm trong một bảng duy nhất, không yêu cầu mô hình quan hệ, và muốn nhanh chóng tạo báo cáo.

5. Câu hỏi thường gặp (FAQ)

5.1. Pivot Table không hiển thị tất cả các giá trị trong cột

Nguyên nhân thường gặp:
– Dữ liệu không được sắp xếp thành bảng (có hàng trống).
– Có giá trị blank (rỗng) trong cột được dùng làm Rows/Columns.

Giải pháp:
– Đảm bảo không có hàng trống, hoặc chuyển dữ liệu thành Table.
– Trong PivotTable Options → Layout & Format, bật Show items with no data.

5.2. Khi cập nhật dữ liệu, Pivot Table không tự động refresh

• Đánh dấu Refresh data when opening the file trong PivotTable Options.
• Hoặc viết macro VBA:
  vba Private Sub Workbook_Open() Dim pt As PivotTable For Each pt In ThisWorkbook.Worksheets("Sheet1").PivotTables pt.RefreshTable Next pt End Sub

5.3. Lỗi “#REF!” khi dùng GETPIVOTDATA

• Kiểm tra rằng tham chiếu ô (ví dụ $A$3) là ô đầu tiên của Pivot Table.
• Đảm bảo các trường và giá trị trong công thức tồn tại trong Pivot Table hiện tại.

5.4. Cách tính “Distinct Count” trong Excel 2026

• Excel 2026 không hỗ trợ Distinct Count trong Pivot Table mặc định.
• Giải pháp:
• Chuyển dữ liệu sang Power Pivot (cài add‑in).
• Hoặc tạo cột phụ =IF(COUNTIFS(Range,CurrentValue)=1,1,0) và tổng hợp cột này.

5.5. Pivot Table chậm khi dữ liệu lớn (hơn 100k bản ghi)

• Sử dụng Data Model (Power Pivot) thay vì Pivot Table truyền thống.
• Tối ưu cache: Vào PivotTable Options → Data → Number of items to retain per field → chọn Automatic hoặc None nếu không cần giữ lịch sử.
• Tắt tính năng “Enable background refresh” để giảm tải CPU khi không cần cập nhật liên tục.

6. Kết luận

Pivot Table là một công cụ không thể thiếu trong bộ công cụ Excel của bất kỳ chuyên viên phân tích dữ liệu nào. Từ việc tạo báo cáo doanh thu đơn giản đến phân tích lợi nhuận phức tạp, từ việc sử dụng slicer để tạo báo cáo tương tác đến việc tích hợp Power Query và Power Pivot để làm sạch và liên kết dữ liệu đa nguồn, Pivot Table luôn mang lại sự linh hoạt và hiệu quả.

Qua các bài tập thực hành từ cơ bản tới nâng cao trong bài viết này, hy vọng bạn đã nắm được:

• Cách chuẩn bị dữ liệu chuẩn cho Pivot Table.
• Các bước thiết lập và tùy chỉnh Pivot Table một cách nhanh chóng.
• Các kỹ thuật nâng cao như Calculated Field, Slicer, Timeline, và tích hợp Power Query.
• Những mẹo tối ưu hoá giúp Pivot Table chạy mượt mà ngay cả với dữ liệu lớn.

Hãy áp dụng ngay các bài tập này vào công việc thực tế của bạn. Khi đã thành thạo, bạn sẽ nhanh chóng tạo ra các báo cáo chuyên nghiệp, giảm thời gian xử lý dữ liệu và đưa ra quyết định kinh doanh dựa trên số liệu một cách chính xác hơn.

Chúc bạn thành công và luôn khám phá thêm những tiềm năng mới của Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.