Pivot Table là công cụ hữu ích trong Excel với nhiều tính năng vượt trội với tốc độ ra kết quả nhanh và dễ dàng thao tác. Chỉ cần nắm trong tay những tính năng cơ bản của Pivot Table, bạn có thể cải thiện hiệu quả công việc của mình một cách đáng kể.
Bài viết này sẽ giúp các bạn hiểu được Pivot Table là gì, chức năng của Pivot Table, cách tạo một Pivot Table cũng như hướng dẫn cách sử dụng Pivot Table để tổng hợp, phân tích, trích xuất dữ liệu, lập bảng thống kê trong Excel 2007, 2010, 2013, 2016, 2019 và 365.
Mục Lục Bài Viết
1. Pivot Table Là Gì? Tại Sao Nên Sử Dụng Pivot Table Trong Phân Tích Dữ Liệu?
Chỉ bằng vào click chuột, chức năng Pivot Table có thể hỗ trợ người dùng tính toán, tóm tắt, phân tích, khai thác ý nghĩa dữ liệu.
Hãy bắt đầu bằng một ví dụ cơ bản nhất cho thấy sức mạnh của Pivot Table: Dưới đây là dữ liệu tổng hợp doanh thu sản phẩm hàng tháng của một công ty trong quý I/2020.
Giả sử bạn đang làm việc cho công ty này, bạn là người báo cáo dữ liệu và đã nhận được một số câu hỏi từ sếp của bạn như sau:
Q1: Doanh thu từng tháng của công ty?
Q2: Khu vực miền Nam bán chạy nhất loại sản phẩm nào (số lượng lớn nhất)?
Q3: Khu vực nào bán được nhiều áo sơ mi nhất?
Q4: Số lượng áo bán được của các khu vực?
Nếu không sử dụng Pivot Table, cách đơn giản nhất mà ai cũng nghĩ đến là dùng các hàm trong Excel.
Bạn sẽ xử lý như nào nếu sếp của bạn đột ngột giao thêm cho bạn một danh sách các câu hỏi khác? – Có thể bạn sẽ phải quay lại bảng dữ liệu để tạo ra những công thức mới mỗi khi có sự thay đổi.
Pivot Table có thể giúp bạn chỉ trong vài thao tác click chuột đơn giản. Bạn có thể xem đáp án của những câu hỏi ở phần 4 bài viết.
Trước khi trả lời những câu hỏi trên, hãy cùng Hoctin.vn tìm hiểu các cách tạo một bảng Pivot Table trong Excel trong phần tiếp theo.
2. Cách Tạo Pivot Table Trong Excel
2.1, Cách Tạo Pivot Table Theo Mẫu Có Sẵn Trong Excel Bằng Chức Năng Recommended PivotTables
Để tạo một bảng Pivot theo mẫu có sẵn trong Excel:
Chọn một ô bất kỳ trong bảng dữ liệu.
Trên thanh công cụ, chọn Insert >> Recommened PivotTables ở mục Table.
Cửa sổ gợi ý hiện lên các mẫu Pivot Table, đến đây bạn chỉ cần lựa chọn bảng phù hợp nhất với nhu cầu của mình:
2.2, Tạo Pivot Table Để Tùy Chỉnh Phân Tích Dữ Liệu
Sử dụng Pivot Table sẵn có theo mẫu nhanh nhưng không cung cấp được nhiều kiểu thông tin và trường dữ liệu như cách tạo bảng tùy chỉnh.
2.2.1, Cách tạo Pivot Table Trống
- Chọn một ô bất kỳ trong bảng dữ liệu. Lưu ý bảng dữ liệu:
- Dữ liệu tổ chức dạng bảng, không có hàng/ cột trống
- Hàng đầu tiên chứa tên trường dữ liệu (tháng/ khu vực/ sản phẩm…)
-
Mỗi cột chỉ được chứa một kiểu dữ liệu (số/ văn bản/ ngày tháng…)
- Trên thanh công cụ, chọn Insert >> PivotTable ở mục Tables.
-
Hộp thoại Create Pivot Table hiện lên: Excel mặc định đặt Pivot Table ở một trang tính mới tại mục New worksheet.
Trường hợp bạn muốn đặt Pivot Table tại một vị trí cụ thể: chọn mục Existing Worksheet >> chọn ô bắt đầu bảng Pivot trong mục Location
- Sau khi ấn OK hoặc phím Enter, bảng Pivot trống được tạo.
2.2.2, Cách Tùy Chỉnh Pivot Table Fields Để Phân Tích Dữ Liệu
Khi bạn click vào một ô bất kỳ trong bảng Pivot, Pivot Table Fields sẽ hiện ra ở phía bên phải giao diện Excel với hai thành phần chính là Fields và Areas:
-
Fields gồm các trường dữ liệu để cấu tạo nên Pivot Table. Các trường dữ liệu được chọn sẽ được bôi đậm và có dấu tick ở ô vuông bên cạnh.
Để tìm nhanh một trường dữ liệu, có thể nhập tên vào ô “Search Box”.
- Areas là nơi kéo các trường dữ liệu vào các vùng sau:
- Vùng Filters: dữ liệu của trường được kéo vào vùng này sẽ được sử dụng làm bộ lọc cho cả bảng Pivot
- Vùng Rows & Columns: hai vùng quyết định các hàng và cột của bảng Pivot chứa các trường nào cho mục đích sắp xếp bảng dữ liệu
- Vùng VALUES: trường nào được kéo vào vùng này sẽ được tổng hợp bới các phép toán sum/ count/ average/ max/ min/ product...
Ví dụ 1: Để tổng hợp doanh thu từng sản phẩm theo tháng, bạn cần kéo các trường hoặc thao tác tick chọn vào các vùng Values, Rows và Columns như sau:
Values: chọn Doanh thu và chọn phép toán SUM
Rows: chọn Sản phẩm
Columns: chọn Tháng (bạn có thể đổi vị trí hai trường ở vùng Rows và Columns, bảng sẽ hiển thị đổi vị trí của hàng và cột).
Kết quả Pivot Tables hiển thị như sau:
Lưu ý quan trọng: Khi có sự bổ sung hay điều chỉnh ở nguồn dữ liệu đầu vào, để cập nhật mới dữ liệu trong Pivot Table: Tại thanh công cụ, chọn tab Analyze >> trong mục Data chọn Refresh hoặc bạn có thể sử dụng tổ hợp phím tắt Alt + F5.
3. Các Ví Dụ Hướng Dẫn Sử Dụng Pivot Tables Để Phân Tích, Tổng Hợp Dữ Liệu
3.1, Thay Đổi Rows và Columns Cho Mục Đích Phân Tích Dữ Liệu
Trường hợp bạn cần tổng hợp doanh thu khu vực theo từng loại sản phẩm, các trường dữ liệu cần sử dụng là “Doanh thu”, “Khu vực”, “Sản phẩm”.
Tiếp theo, hãy hình dung về vị trí của các trường dữ liệu. Ví dụ, bạn muốn các Khu Vực sẽ được đặt ở đầu mỗi hàng, các loại Sản phẩm sẽ được đặt ở đầu mỗi cột.
Cuối cùng, bạn chỉ cần kéo các trường vào các vùng như sau:
Values: kéo cột Doanh thu vào và chọn phép toán SUM
Rows: chọn Khu Vực
Columns: chọn Sản phẩm.
Bạn có thể đổi vị trị Rows và Columns, khi đó bảng Pivot chỉ đơn giản là chuyển đổi hàng sang cột và ngược lại.
3.2, Sử Dụng Filters Trong Pivot Table
Các ví dụ trên đều xử lý các bài toán tổng hợp, phân tích toàn bộ 1 cột. Trường hợp cần phân tích một giá trị trong một cột thì sao?
Vùng Filter sẽ giải quyết giúp bạn câu hỏi này.
Để tổng hợp doanh thu khu vực hàng tháng theo một loại sản phẩm cụ thể, bạn đặt trường Sản phẩm vào Filter để lọc khi cần phân tích một sản phẩm trong trường này:
Filters: chọn Sản phẩm
Values: chọn Doanh thu và chọn phép toán SUM
Rows: chọn Khu Vực
Columns: chọn Tháng.
Tương tự, để giải quyết câu hỏi ở đầu mục 3, bạn muốn biết doanh thu bán hàng khu vực miền Bắc theo sản phẩm từng tháng: Sau khi xác định trường dữ liệu là “Khu vực”, “Sản phẩm”, “Tháng” và “Doanh thu”, kéo/ chọn vùng như sau:
Filters: chọn Khu vực
Values: chọn Doanh thu và chọn phép toán SUM
Rows: chọn Sản phẩm
Columns: chọn Tháng.
Filter Khu vực hiện ra ở đầu bảng Pivot, chọn mũi tên thả xuống, danh sách các giá trị trong cột Khu vực hiện ra: Miền Bắc, Miền Nam, Miền Trung.
Excel mặc định chỉ chọn 1 giá trị. Để chọn từ 2 giá trị trở lên ở Filter, tick Select Multiple Items.
Chọn Miền Bắc, ấn OK, dữ liệu cần tổng hợp và phân tích được thể hiện như sau:
3.3, Ví Dụ Pivot Tables Sử Dụng Từ 2 Trường Dữ Liệu Trong 1 Vùng (Area)
Các ví dụ ở trên đều chỉ kéo 1 trường dữ liệu cho Filter, Hàng và Cột.
Bạn có thắc mắc Pivot table trông sẽ như thế nào nếu 1 vùng chứa nhiều hơn 1 trường dữ liệu?
Thay vì sử dụng Filter, bạn có thể sử dụng dữ liệu lồng nhau bằng cách thêm trường vào các vùng Rows và Columns. Ví dụ dưới đây tổng hợp Doanh thu từng loại Sản phẩm theo Khu vực và Tháng (Sản phẩm >> Khu vực):
Nếu thứ tự thêm trường vào Area thay đổi, dữ liệu cũng thay đổi tương ứng.
Nếu đảo thứ tự của trường Sản phẩm lên phía trên trường Khu Vực trong Rows thì Pivot Table hiển thị tổng hợp Doanh thu từng Khu vực theo Sản phẩm và Tháng (Khu vực >> Sản phẩm):
4. Cách Sử Dụng Pivot Table Nâng Cao Ứng Dụng Giải Bài Tập Excel Ở Mục 1
Sau khi đã tìm hiểu cách sử dụng Pivot Table cơ bản, các bạn hãy cùng Hoctin.vn trả lời những câu hỏi đề ra ở đầu bài viết nhé!
Để tiện theo dõi, hoctin.vn sẽ đặt Pivot Table ở cạnh dữ liệu gốc bằng cách chọn mục Existing Worksheet trong hộp thoại Create Pivot Table.
Q1: Doanh thu từng tháng của công ty?
Nếu đã tìm hiểu các ví dụ trên của hoctin.vn thì trả lời câu hỏi này quá dễ đúng không?
Q2: Khu vực miền Nam bán chạy nhất loại sản phẩm nào (số lượng lớn nhất)?
Sử dụng Filter lọc khu vực Miền Nam, rồi chọn ra sản phẩm bán chạy nhất là Áo Sơ Mi (860 chiếc) so với sản phẩm còn lại là Quần Short (bán được 550 chiếc).
Q3: Khu vực nào bán được nhiều áo sơ mi nhất?
Tương tự câu hỏi 2, để giải bài tập Excel này, bạn chỉ cần Filter Sản phẩm Áo Sơ Mi và chọn Values là hàm SUM cho Số Lượng: bảng Pivot Table sẽ trả về tổng số áo sơ mi đã bán của từng vùng.
Kết quả: Miền Nam là vùng bán được nhiều áo sơ mi nhất.
Q4: Số lượng áo bán được của các khu vực?
Để trả lời câu hỏi này, hoctin.vn vẫn sử dụng vùng Filter của Pivot Table, chỉ có điểm khác với các ví dụ trên ở chỗ, có nhiều hơn 1 sản phẩm là áo nên bạn sẽ cần chọn Select Multiple Items để lọc bảng Pivot cho từ 2 sản phẩm trở lên.
Số lượng áo bán được của các khu vực (áo thun và áo sơ mi) là:
Trên đây là một số cách sử dụng và ứng dụng Pivot Table từ cơ bản đến nâng cao. Hi vọng bài viết của Hoctin.vn đã mang lại cho các bạn các thông tin hữu ích.
Mời các bạn đón đọc bài viết về cách dùng Pivot Table nâng cao: sử dụng Slicer trong PivotTable để lọc dữ liệu, Calculated Field để thêm trường tính toán, tránh bẫy Average và trích xuất dữ liệu từ sheet này sang sheet khác.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:
có thể nào bỏ phần Grand total cột bên phải ngoài cùng không ạ?
Hi bạn, bạn vào tab Design > Grand total > Off for Rows and Columns để bỏ phần Grand total nhé.