4 chức năng PivotTable nâng cao để phân tích dữ liệu Excel

Bạn không nên đánh giá thấp các pivot table trong Excel. Đó là bởi vì PivotTables của Excel sẽ cung cấp mọi thứ bạn cần để phân tích dữ liệu. Từ việc cô đọng các hàng dữ liệu để tính tổng và nhóm các cột tương tự, pivot table có tất cả mọi thứ.

Tuy nhiên, việc phân tích dữ liệu không hề đơn giản. Vì vậy, để tăng cường dữ liệu của mình, bạn có thể sử dụng một loạt các hàm PivotTable nâng cao trong Excel.

Nếu đã quen thuộc với PivotTable và đang muốn tiến xa hơn với một số hàm nâng cao, bạn có thể xem các hàm PivotTable nâng cao sau đây.

Chuẩn bị tập dữ liệu

Để hiểu các hàm nâng cao, trước tiên bạn phải chuẩn bị tập dữ liệu ban đầu. Để thuận tiện, hãy sử dụng những hàng và cột sau để minh họa các hàm PivotTable nâng cao.

Bạn có thể tải xuống dữ liệu này trực tiếp từ nguồn để thực hành các hàm được liệt kê bên dưới.

• Tải Sample Superstore (Miễn phí)

Cách tạo một pivot table trong Excel

Chọn dữ liệu cho Excel PivotTable của bạn. Để chèn pivot table, hãy bấm vào tab Insert ở trên cùng, tiếp theo là tùy chọn PivotTable.

Chọn tùy chọn From Table/Range.

Vì bạn đã chọn phạm vi dữ liệu trước khi chèn pivot, nên phạm vi dữ liệu được cập nhật tự động trong hộp thoại pivot. Bạn có thể chọn đích của pivot table - đặt nó trên một trang tính hiện có hoặc một trang tính mới.

Sau khi nhấp vào Ok, pivot của bạn đã sẵn sàng để xem xét. Bạn có thể thêm các dạng xem hàng/cột/giá trị cần thiết từ danh sách trường.

Ngoài ra, bạn có thể tự động tạo pivot table trong Excel bằng VBA để tiết kiệm thời gian và công sức.

4 chức năng PivotTable nâng cao để phân tích dữ liệu Excel

1. Slicer

Slicer được giới thiệu như một phần của bộ Microsoft Excel 2010 và có sẵn trong các phiên bản MS Office cao hơn. Microsoft tiếp tục phát hành các hàm Excel mới để giúp người dùng tận dụng tối đa những tác vụ dữ liệu thông thường của họ.

Giống như một số hàm Excel khác, thậm chí slicer còn là một cách tuyệt vời để kiểm soát các pivot của bạn thông qua bộ lọc pivot bên ngoài.

Không giống như bộ lọc pivot tích hợp, bạn có thể tùy chỉnh các bộ lọc và hiển thị nội dung mọi lúc. Người dùng thấy Slicer khá hiệu quả vì bạn có thể lọc nhiều mục và hiển thị chúng cho mọi người xem.

Thêm slicer trong PivotTable

Để thêm slicer vào pivot table, hãy nhấp vào pivot table và điều hướng đến tab PivotTable Analyze, sau đó là tùy chọn Insert Slicer.

Khi nhấp vào nút Slicer, bạn có thể chọn một hoặc nhiều cột để sử dụng làm slicer. Tất cả các tùy chọn bạn chọn được hiển thị trong những hộp lọc riêng biệt bên cạnh pivot để thuận tiện. Các mục của slicer lọc pivot table và hiển thị bất kỳ thứ gì có liên quan.

Slicer mang lại nhiều lợi ích, đặc biệt là khi bạn muốn điều khiển nhiều pivot table đồng thời với các cửa sổ slicer (bộ lọc).

2. Calculated field

Calculated field là một cách tuyệt vời để thêm những phép tính vào pivot table của bạn, điều này rất cần thiết khi làm việc với nhiều cột số, để tính toán nhanh các giá trị mới.

Thay vì phải vật lộn với các tùy chọn PivotTable, bạn có thể tạo (các) cột mới trong pivot table của mình. Bất cứ khi nào bạn thực hiện thay đổi đối với dữ liệu vốn có, các thay đổi sẽ tự động phản ánh trong pivot table. Tuy nhiên, bạn cần phải refresh pivot table trước.

Cách tạo một calculated field trong pivot table

Giả sử bạn muốn tính tổng doanh số cho từng danh mục con. Công thức rõ ràng là lấy tích số của các trường bán hàng và số lượng.

Để thực hiện các phép tính như vậy một cách hiệu quả, bạn có thể tạo một calculated field trong pivot table của mình.

Để tạo một calculated field, hãy nhấp vào pivot table của bạn và điều hướng đến tab PivotTable Analyze. Bấm vào nút Fields, Items, & Sets.

Nhấp vào Calculated Field từ menu drop-down. Trong hộp thoại, bên trong cột Name, hãy chỉ định tiêu đề cột. Tiếp theo, từ danh sách Fields, hãy chọn tên cột bạn muốn sử dụng trong công thức.

Tìm tùy chọn Sales. Bấm vào nút Insert Field. Tùy chọn Sales được tự động thêm vào cột Formula. Tương tự, bạn có thể nhấp vào Quantity, sau đó là nút Insert Field.

Thêm biểu tượng dấu hoa thị (*) giữa hai tên trường trong thanh công thức. Bấm vào nút Add.

Trong pivot table, cột mới được tạo có sẵn.

Tương tự, bạn có thể tạo nhiều calculated field trong pivot table để nâng cao khả năng tính toán của mình.

3. Cách refresh nhiều pivot table

Khi bạn xử lý dữ liệu phức tạp, có khả năng cao bạn sẽ tạo nhiều pivot table đồng thời.

Tùy thuộc vào cấu trúc dữ liệu của bạn, bạn có thể thích đặt một vài pivot trên một trang tính, trong khi nhiều pivot khác có thể nằm trên các trang tính khác trong cùng một workbook.

Có hai cách để bạn có thể làm mới nhiều pivot table trong workbook của mình. Tùy chọn đầu tiên là sử dụng Excel VBA để tự động hóa quá trình refresh.

Ngoài ra, bạn cũng có thể thực hiện việc này theo cách thủ công. Bấm vào bất kỳ pivot table nào và điều hướng đến tab PivotTable Analyze ở menu ribbon trên cùng. Nhấp vào menu drop-down của nút Refresh. Chọn Refresh All từ danh sách.

Bạn sẽ nhận thấy tất cả các pivot table xuất hiện trong workbook ngay sau khi bạn bấm vào nút này. Mọi thay đổi mới sẽ phản ánh trong pivot table.

4. Nhóm các trường liên quan lại với nhau

Bạn thường muốn nhóm các điểm dữ liệu cụ thể để người dùng cuối có thể hiểu cấu trúc dữ liệu tốt hơn. Là một chuyên gia, bạn có thể hiểu rõ dữ liệu và cách nó liên quan đến những danh mục rộng hơn. Ngược lại, người dùng cuối thường có thể khó hiểu các nhóm này trong pivot table.

Để giúp quá trình này trở nên dễ dàng, bạn có thể sử dụng chức năng Group để nhóm các danh mục con lại với nhau và đổi tên nhóm để tạo ra những thông tin chi tiết có ý nghĩa.

Để tạo một nhóm, hãy nhấn phím Ctrl và chọn nhiều mục từ danh sách các mục trong một lần. Nhấp chuột phải trong pivot table và nhấp vào tùy chọn Group.

Excel tạo các nhóm mặc định, bạn có thể chỉnh sửa nhóm này bằng cách chọn nhiều mục.

Khi các nhóm đã sẵn sàng, bạn có thể đổi tên nhóm theo nhu cầu của mình.

Để xóa các nhóm, hãy bấm chuột phải vào pivot table và chọn tùy chọn Ungroup.