Thành Thạo Pivot Table Trong Excel Thông Qua Ví Dụ Thực Tế

Video hướng dẫn

Tạo pivot table từ bảng dữ liệu

Khi đã có được dữ liệu, điều đầu tiên chúng ta nên làm là liên kết nó để tạo thành 1 bảng thống nhất. Với thao tác này thì khi chúng ta có sự điều chỉnh thay đổi hay thêm bớt dữ liệu trong tương lai thì pivot table được tạo cũng sẽ tự động được cập nhật theo. Để tạo bảng các bạn ấn vào một ô bất kì trong vùng dữ liệu rồi ở thẻ Insert chọn Table.

Sau khi bảng được tạo mới thì các bạn có thể thay đổi tên của bảng ở mục Table Name ở phía trên bên phải. Điều này giúp chúng ta dễ dàng hơn trong việc phân biệt khi làm việc với nhiều bảng khác nhau.

Để tạo một pivot table từ bảng dữ liệu có sẵn, các bạn chọn một ô bất kì trong vùng dữ liệu, sau đó ở thẻ Insert chọn PivotTable.

Lúc này thì hộp thoại Create PivotTable sẽ hiện ra, các bạn lưu ý chọn đúng bảng chứa dữ liệu cần sử dụng ở mục Table/Range. Ngoài ra, bên dưới các bạn còn có thể tùy chỉnh xem sẽ tạo pivot table ở một sheet mới hay ở một sheet đã có sẵn trước đó.

Sau khi tạo thành công thì một bảng điều khiển sẽ hiện lên từ bên phải tên là PivotTable Fields, đây sẽ là nơi các bạn thao tác chính để tạo ra những báo cáo như mong muốn của mình. Phía bên trên sẽ là tên các cột có trong bảng dữ liệu còn bên dưới gồm bốn ô chính: Filters, Columns, Rows và Values.

Sau khi kéo “Khu Vực” và ô Filters, “Nhà Phân Phối” vào ô Columns, “Sản Phẩm” vào ô Rows và “Doanh Thu” vào ô Values thì chúng ta sẽ được một bảng như bên dưới. Các bạn có thể hiểu đơn giản giá trị trong ô Filters sẽ được dùng để lọc dữ liệu, như ví dụ trên thì khi chúng ta điều chỉnh thành các khu vực khác nhau thì sẽ có được các số liệu thống kê khác nhau. Giá trị của ô Columns và Rows sẽ là các giá trị trong “Nhà Phân Phối” và “Sản Phẩm” lần lượt được biểu diễn theo cột và hàng. Cuối cùng ô Values sẽ chứa giá trị chính, được lọc ra từ Filters (Khu Vực) và tổng hợp theo Columns (Nhà Phân Phối) và Rows (Sản Phẩm).

Mặc định, giá trị của doanh thu sẽ được tính theo hàm SUM, tức là tổng các giá trị. Trong trường hợp các bạn muốn tổng hợp doanh thu theo phần trăm hoặc trung bình cộng thì có thể ấn vào dấu mũi tên bên phải trong ô Values rồi chọn Value Field Settings.

Trong Excel sẽ có sẵn cho các bạn một số lựa chọn cho ô Values như tính tổng cộng, tính số lượng, tính trung bình cộng, tìm ra số lớn nhất, nhỏ nhất,… Các bạn có thể thoải mái lựa chọn ở đây.

Sau khi kéo “Khu Vực” ra khỏi ô Filters và bỏ vào ô Rows thì chúng ta sẽ có được kết quả như bên dưới. Trong ô Rows, giá trị được bỏ vào đầu tiên sẽ có vai trò như là phần tử cha. Như ví dụ bên dưới thì chúng ta có thể nhìn thấy được doanh thu theo khu vực của từng loại sản phẩm, còn nếu kéo “Khu Vực” nằm trên “Sản Phẩm” thì chúng ta sẽ thấy được doanh thu của từng loại sản phẩm theo khu vực.

Mặc định trong pivot table sẽ tính tổng giá trị theo cột và hàng (Subtotals), để ẩn tổng giá trị này, các bạn có thể vào thẻ Design, chọn Subtotals rồi chọn Do Not Show Subtotals. 

Tương tự như phía trên thì trong pivot table cũng mặc định hiển thị tổng giá trị của tất cả (Grand Total), để ẩn các bạn cũng vào thẻ Design, chọn Grand Totals rồi chọn Off for Rows and Columns.

Mình có một kinh nghiệm khi sử dụng tính năng pivot table đó là các bạn nên hiển thị báo cáo theo Tabular Form để trở nên trực quan hơn. Để làm điều này, ở thẻ Design các bạn chọn Report Layout rồi chọn Show in Tabular Form.

Sau khi đã tùy chỉnh bố cục của báo cáo, có thể dễ dàng nhận thấy các giá trị “Bánh”, “Kẹo”, “Sữa” chỉ hiển thị trong báo cáo một lần duy nhất ở hàng đầu tiên. Để tùy chỉnh cho hiển thị ở tất cả những ô trống ở các hàng bên dưới, ở thẻ Design chọn Report Layout rồi chọn Repeat All Item Labels.

Các bạn còn có thể sắp xếp giá trị trong các cột theo thứ tự từ nhỏ đến lớn, từ lớn đến nhỏ,.. bằng cách ấn chuột phải vào một ô giá trị bất kì, chọn Sort rồi chọn kiểu sắp xếp theo mong muốn. 

Ngoài ra, các bạn còn có thể kéo “Doanh Thu” vào ô Values một lần nữa để tạo ra một cột mới, sau đó ấn chuột phải vào một ô bất kì trong cột đó, chọn Show Values As rồi chọn một kiểu hiển thị khác như tính theo phần trăm, trung bình cộng,…

Tạo pivot table mới từ một pivot table có sẵn

Thay vì phải tạo một pivot table như cách ở phía trên, trong trường hợp các bạn đã có một pivot table trước đó thì có thể tạo mới đơn giản bằng cách chọn pivot table cũ, ấn tổ hợp phím Ctrl + C rồi chọn một ô ở vị trí khác, ấn tổ hợp phím Ctrl + V để dán.

Ngoài cách lọc giá trị bằng ô Filters, các bạn có thể sử dụng một tính năng dễ sử dụng, thao tác nhanh và đẹp hơn là Slicer. Để tạo mới, các bạn ấn chuột phải vào giá trị cần tạo trong bảng PivotTable Fields rồi chọn Add as Slicer.

Tuy nhiên, lúc này slicer vừa tạo chỉ có tác dụng với pivot table được tạo sau. Để slicer tác động được đến nhiều pivot table khác nhau, các bạn ấn chuột phải và slicer rồi chọn Report Connections.

Ở hộp thoại Report Connections vừa hiện ra, các bạn ấn chọn tất cả những pivot table sẽ được tác động khi bạn thay đổi giá trị trên slicer rồi ấn nút OK phía bên dưới để lưu. 

Khi các bạn thêm mới, xóa hoặc sửa trên bảng dữ liệu ban đầu, để cập nhật pivot table các bạn ấn chuột phải vào một ô bất kì trong pivot table rồi chọn Refresh, lúc này dữ liệu sẽ ngay lập tức được cập nhật lại. Các bạn lưu ý thao tác này sẽ không có tác dụng nếu các bạn không tạo bảng như ở những bước đầu mình đã hướng dẫn. Chúc các bạn thực hành tốt!