Hoctin.vn đã có bài viết hướng dẫn sử dụng Pivot Table đơn giản và chi tiết: Pivot Table là gì, chức năng và cách tạo một Pivot Table cũng như cách sử dụng nó để tổng hợp, phân tích, lập bảng thống kê trong Excel 2007, 2010, 2013, 2016, 2019 và 365.
Ở bài viết này, hoctin.vn sẽ tiếp tục giới thiệu với các bạn cách sử dụng Pivot Table nâng cao trong Excel với Slicer, Calculated Field, tránh bẫy Average và trích xuất dữ liệu từ sheet này sang sheet khác.
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. Hoctin.vn sẽ sử dụng bộ dữ liệu này để áp dụng cách dùng Pivot Table nâng cao.
Mục Lục Bài Viết
1, Sử Dụng Slicer Để Lọc Dữ Liệu Thay Thế Cho Filters Trong Pivottable
Slicer là một công cụ giúp bạn lọc dữ liệu bảng hoặc dữ liệu Pivot Table.
Các nút của Slicer giúp lọc nhanh, và hiển thị trạng thái lọc hiện tại, giúp bạn dễ dàng hiểu kết quả PivotTable đã lọc từ cách lọc các trường dữ liệu nào.
Tạo Slicer để lọc dữ liệu:
Bước 1: Bấm vào vị trí bất kỳ trong PivotTable >> chọn thẻ Insert của thanh Ribbon >> Chọn Slicer.
Hộp thoại Insert Slicers hiện lên:
Bước 2: Bạn tick/ chọn các trường mà bạn muốn Filter/ lọc >> ấn OK.
Hoctin.vn chọn các trường Tháng, Khu Vực và Sản Phẩm. Slicers hiển thị tất cả các giá trị của 3 trường dữ liệu này như sau:
Bước 3: Thực hiện lọc dữ liệu với Slicers. Chọn các giá trị bạn muốn lọc, bấm nút tương ứng trên các vùng Slicers thay vì chọn Filter như cách truyền thống.
Ở ví dụ này, hoctin.vn đã chọn lọc dữ liệu Miền Nam trong trường Khu vực. Pivot table tự động hiển thị dữ liệu sản phẩm bán ở Miền Nam. Nhờ Slicer, người dùng có thể dễ dàng thấy trạng thái lọc hiện tại của bảng Pivot.
Để xóa Filter trong Slicers, chọn biểu tượng “x” ở góc bảng đã lọc.
2, Sử Dụng Pivot Table Nâng Cao: Cách Trích Xuất Dữ Liệu Từ Sheet Này Sang Sheet Khác
Pivot Table không chỉ sử dụng để tính toán, phân tích mà còn có thể ứng dụng trong việc lấy dữ liệu từ bảng này sang bảng khác, rút trích dữ liệu có điều kiện sang sheet khác trong Excel.
Ví dụ: Từ bảng dữ liệu đề bài, bạn cần lọc riêng thông tin bán hàng của 1 sản phẩm là Quần Short sang 1 sheet mới.
Thay vì sử dụng chức năng Filter của Excel để lọc dữ liệu rồi copy sang một trang tính Excel khác, bạn có thể sử dụng Pivot Table. Ưu điểm của cách này là thao tác nhanh và không phải thao tác nhiều lần nếu như yêu cầu trích xuất dữ liệu có thay đổi.
Trong bảng Pivottable, nhấn đúp chuột vào dữ liệu bạn muốn trích xuất, ở ví dụ này, hoctin.vn cần xuất dữ liệu toàn bộ các tháng của sản phẩm Quần Short nên chọn ô E8 là tổng doanh thu các tháng của Quần Short:
Sau khi nhấn đúp chuột vào Values của dữ liệu cần trích xuất, trang tính mới được Excel tạo ra chứa toàn bộ thông tin của trường Quần Short:
3. Sử Dụng Calculated Field Của Pivot Table Để Thêm Trường Tính Toán Mà Không Cần Dùng Cách Thêm Cột Trong Excel
Calculated Field trong Pivot Table nâng cao giúp bạn có thể thực hiện tính toán giữa các trường và tạo nên một trường dữ liệu mới mà không cần thay đổi bảng số liệu gốc.
Ví dụ, nếu bạn có trường Số lượng và Đơn giá, thay vì bạn kéo thành nhiều cột trong Pivot Table và sau đó thực hiện tính toán bằng phép nhân Doanh thu = Số lượng * Đơn giá, bạn có thể sử dụng Calculated Field để tạo một trường mới mà không cần phải thêm cột vào bảng dữ liệu ban đầu.
Để sử dụng Calculated Field của Pivot Table Nâng Cao Trong Excel, bấm vào vị trí bất kỳ trong PivotTable >> chọn thẻ Analyze của thanh Ribbon >> Chọn Fields, Items, & Sets >> Chọn Calculated Field.
Hộp thoại Insert Calculated Field hiện ra. Bạn đặt tên cho field mới, ví dụ “SL*DG” rồi chọn công thức tương ứng bằng cách chọn trường và thực hiện phép toán ví dụ = ‘Số Lượng’* ‘Đơn giá’ >> chọn OK.
Lưu ý để đặt công thức ở ô Formula, cần chọn các trường cấu thành nên công thức, ấn Insert Field rồi thêm các phép toán giữa các trường.
Trường mới, SL*DG được tạo. Giờ đây bạn có thể tùy ý kéo thả trường vào các Area cho mục đích phân tích dữ liệu của mình.
Ưu điểm của cách làm này là khi bạn kéo thả, thay đổi trường trong Pivottable, thì việc tạo mới một field giúp kết quả luôn đảm bảo chính xác, còn nếu bạn thực hiện phép tính, sử dụng công thức ở ngoài trên một Pivot Table có sẵn, thì kết quả có thể không còn đúng nữa.
Sử dụng Calculated Field giúp người dùng tránh được Bẫy Avetage trong Pivot Table.
4, Pivot Table Nâng Cao: Cách Tránh Bẫy Average
Bẫy Average trong Pivot Table là gì?
Đã bao giờ bạn dùng hàm AVERAGE của PivotTable nhưng kết quả của bạn và kết quả tính lại bằng tay lại khác nhau hay không?
Khi sử dụng PivotTable, chúng ta rất dễ bị đánh lừa vì nghĩ rằng hàm Average của PivotTable trong Values sẽ tính theo công thức trung bình mặc định cho các giá trị hiển thị trên bảng Pivot tại thời điểm.
Một ví dụ đơn giản để bạn hình dung được Bẫy Average: cho bảng dữ liệu chi phí lương, số nhân viên của 3 phòng Sales, Accounting, Production trong quý 1/2020.
Để phân tích dữ liệu tiền lương trung bình đầu người, một nhân viên đã thêm cột “Trung bình lương” trong bảng dữ liệu gốc và sử dụng Pivot table để phân tích theo tháng và trung bình cả quý.
Kết quả trung bình quý theo Pivot table là dòng Grand Total khác với kết quả tính tay ở ô D15. Nếu kiểm tra thử các kết quả sử dụng Average của Pivottable cho các tháng cũng ra sai khác tương tự.
Nguyên nhân xảy ra bẫy Average là do số phần tử trong phép tính trung bình cộng. Phép tính đúng là tổng chi phí lương chia cho tổng số nhân viên, tuy nhiên hàm Average của Pivot Table sẽ tính số trung bình của cột Trung bình lương dẫn đến khác biệt trong kết quả.
Để tránh bẫy Average, có thể dùng Calculated Field đã được nhắc đến ở mục 3.
Hoctin.vn đã thêm trường TB lương = Chi phí lương / Số nhân viên.
Dòng Grand Total của phép Sum trường TB lương chính là kết quả cần tìm:
Trên đây là các ví dụ và ứng dụng hay của Pivot Table nâng cao trong Excel. 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.
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: