Hướng dẫn sử dụng Pivot Table cơ bản & nâng cao mới nhất

Pivot table là một trong những tính năng hữu dụng và mạnh mẽ nhất của Excel dùng để tổng hợp, cho phép người dùng trích lọc, phân tích và tổng hợp dữ liệu từ bảng dữ liệu lớn dễ dàng và nhanh chóng. Kết quả của PivotTable có thể tiếp tục được sử dụng để tính toán ra các chỉ tiêu mới nhằm phục vụ các mục đích khác nhau.

Với Pivot table, người dùng có thể nhóm dữ liệu, tính toán giá trị trung bình, tổng, trung vị và các hàm khác của một tập dữ liệu. Nó cũng cho phép người dùng xoay (pivot) các thông tin trên hàng và cột để tạo ra các báo cáo có cấu trúc khác nhau, giúp người dùng nhanh chóng tìm thấy mẫu thông tin và các định dạng hiển thị phù hợp. Pivot table là một công cụ hữu ích cho việc phân tích và tổng hợp dữ liệu trong các báo cáo tài chính, thống kê, quản lý dự án, v.v.

Trong bài viết này, mình sẽ hướng dẫn các bạn sử dụng pivot table làm báo cáo thống kê doanh thu theo nhóm hàng, khu vực và tháng và các báo cáo nâng cao khác

Pivot table là gì?

Pivot Table là một tính năng trong các phần mềm bảng tính như Microsoft Excel, Google Sheets, hay Apple Numbers, cho phép người dùng tổng hợp và phân tích dữ liệu một cách dễ dàng và nhanh chóng. Nó cho phép người dùng thay đổi cách hiển thị dữ liệu, sắp xếp lại các giá trị, thêm bớt các chi tiết và tính toán tổng hợp các giá trị dữ liệu.

Pivot Table thường được sử dụng để phân tích dữ liệu trong các bảng tính lớn, giúp người dùng tìm kiếm các thông tin quan trọng và hiểu rõ hơn về dữ liệu đang xử lý. Các ứng dụng của Pivot Table có thể đa dạng, từ phân tích tài chính, định lượng doanh số, đánh giá kết quả kinh doanh đến phân tích dữ liệu về khách hàng, sản phẩm hoặc dịch vụ.

Tuy nhiên, để sử dụng Pivot Table một cách hiệu quả, người dùng cần có kiến thức về cách thực hiện tạo và sử dụng Pivot Table, cũng như các lưu ý và kinh nghiệm khi sử dụng tính năng này.

Các tính năng và lợi ích của Pivot Table

Pivot Table là một công cụ hữu ích giúp người dùng quản lý và phân tích dữ liệu một cách chính xác và hiệu quả. Các tính năng và lợi ích của Pivot Table bao gồm:

1. Tổng hợp và phân tích dữ liệu nhanh chóng: Pivot Table cho phép người dùng thực hiện các phép tính toán và tổng hợp dữ liệu một cách nhanh chóng, giúp tiết kiệm thời gian và tăng hiệu quả công việc.
2. Khả năng lọc và sắp xếp dữ liệu linh hoạt: Người dùng có thể lọc và sắp xếp dữ liệu theo nhiều tiêu chí khác nhau để tìm kiếm các thông tin quan trọng, từ đó đưa ra các quyết định và kế hoạch phù hợp.
3. Cập nhật dữ liệu tự động và dễ dàng: Pivot Table có tính năng tự động cập nhật dữ liệu khi có sự thay đổi, người dùng chỉ cần làm mới bảng Pivot Table để dữ liệu được cập nhật và hiển thị chính xác.
4. Tính linh hoạt và tùy chỉnh cao: Pivot Table cho phép người dùng tùy chỉnh các hiển thị dữ liệu, thêm bớt các chi tiết và tính toán tổng hợp các giá trị dữ liệu theo nhu cầu sử dụng.
5. Khả năng phân tích dữ liệu chi tiết: Pivot Table cho phép người dùng phân tích dữ liệu chi tiết đến mức độ cao, từ đó tìm ra các mẫu và xu hướng trong dữ liệu, đưa ra những quyết định và hành động phù hợp.

Các lưu ý khi sử dụng Pivot Table

Khi sử dụng Pivot Table, người dùng cần lưu ý những điểm sau:

1. Chuẩn bị dữ liệu: Trước khi tạo Pivot Table, người dùng cần chắc chắn rằng dữ liệu đã được sắp xếp và định dạng đúng cách để Pivot Table có thể hiển thị chính xác.
2. Lựa chọn đúng loại Pivot Table: Trong các phần mềm bảng tính, có nhiều loại Pivot Table khác nhau, người dùng cần lựa chọn loại phù hợp với nhu cầu sử dụng và dữ liệu đang xử lý.
3. Tạo các nhóm dữ liệu: Pivot Table cho phép người dùng tạo các nhóm dữ liệu để phân tích và tổng hợp dữ liệu một cách hiệu quả hơn. Người dùng cần sử dụng tính năng này một cách linh hoạt để tạo ra các nhóm dữ liệu phù hợp với mục đích sử dụng.
4. Sử dụng các tính năng tổng hợp dữ liệu: Pivot Table có nhiều tính năng tổng hợp dữ liệu như tính toán trung bình, tổng, phần trăm, … Người dùng cần sử dụng các tính năng này một cách chính xác và phù hợp với mục đích sử dụng.
5. Lưu ý đến định dạng và hiển thị dữ liệu: Pivot Table cho phép người dùng tùy chỉnh định dạng và hiển thị dữ liệu. Tuy nhiên, người dùng cần lưu ý đến việc không nên quá tùy chỉnh khiến cho Pivot Table trở nên khó đọc và hiểu.
6. Cập nhật dữ liệu định kỳ: Pivot Table cần được cập nhật dữ liệu định kỳ để đảm bảo dữ liệu được hiển thị chính xác và phù hợp với thực tế.

Tóm lại, để sử dụng Pivot Table một cách hiệu quả, người dùng cần lưu ý đến việc chuẩn bị dữ liệu, lựa chọn loại Pivot Table phù hợp, tạo các nhóm dữ liệu, sử dụng các tính năng tổng hợp dữ liệu một cách chính xác, và cập nhật dữ liệu định kỳ.

Hướng dẫn sử dụng pivottable cơ bản

Với các tính năng pivot table cơ bản thì bạn có thể tổng hợp, trích xuất các số liệu để làm báo cáo và phân tích. Mặc dù nói là cơ bản nhưng nó cũng đã đáp ứng đến 80%, 90% nhu cầu công việc của bạn rồi

– Đầu tiên, bạn cần có bảng dữ liệu như sau:

– Bạn chọn toàn bộ dữ liệu từ B3:G15, sau đó nhấp vào biểu tượng PivotTable như hình bên dưới:

– Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.

Bảng pivottable hiện ra. Đầu tiên để tính tổng số tiền bán được của mỗi nhóm sản phẩm, thì bạn làm theo các bước

– Kéo cột Nhóm SP vào vùng Row Labels

– Kéo cột Tháng vào vùng Column Labels

– Kéo cột Số Tiền vào vùng Values area

– Kéo cột Khu Vực vào vùng Report Filter

– Và đây là kết quả sau khi hoàn thành các bước trên.

– Nếu bạn muốn lọc doanh thu theo 1 khu vực nào đó thì bạn chọn như hình bên dưới:

Download file mẫu PivotTable: PivotTable

Ngoài những tính năng cơ bản và phổ biến bên trên thì pivot table còn nhiều tính năng nâng cao rất hay. Nếu bạn có nhu cầu phân tích nâng cao thì xem thêm phần hướng dẫn sử dụng pivottable nâng cao dưới đây nhé

Hướng dẫn sử dụng pivottable nâng cao

Ở phần nâng cao hơn thì bạn có thể trích lọc dữ liệu chuyên sâu hơn, thực hiện tính toán, trang trí các bảng pivot table hoặc vẽ đồ thị pivot chart…

1. Sử dụng Slicer để lọc dữ liệu với Pivottable

Slicer là một công cụ mới xuất hiện từ Excel 2010 cung cấp các nút mà bạn có thể bấm vào để lọc dữ liệu bảng hoặc dữ liệu PivotTable. Ngoài việc lọc nhanh, slicer còn thể hiện trạng thái lọc hiện tại, giúp bạn dễ dàng hiểu nội dung chính xác được hiển thị trong PivotTable đã lọc.

Để có thể kích hoạt chức năng này, bạn có thể làm như sau:
Bước 1: Chọn 1 PivotTable bạn muốn áp dụng -> chọn thẻ Insert của thanh Ribbon -> Chọn Slicer.

Bước 2: Từ đây, bạn có thể chọn 1 trường mà bạn muốn thể hiện các dữ liệu để tiến hành filter sau này. Bạn có thể chọn 1 hoặc nhiều trường cùng một lúc. Giả sử trong hình dưới đây tôi chọn trường Department và Status. Và tôi sẽ có kết quả như sau:

Bạn có thể thấy là tất cả giá trị của trường Department và Status sẽ hiện lên rõ nét cho bạn. Và khi bạn cần lọc gì, bạn chỉ cần bấm nút tương ứng thay vì chọn Filter như cách truyền thống.

2. Sử dụng Calculated Field để thêm trường tính toán trong pivot table

Calculated Field là một chức năng vô cùng hay khác của PivotTable. Nó giúp bạn có thể thực hiện tính toán những số liệu khác dựa vào các trường mà bạn đã có sẵn. Ví dụ, nếu bạn có trường Ngày công và Lương, thay vì bạn kéo thành 2 cột Ngày công và Lương trong PivotTable để rồi sau đó tính toán bằng tay theo kiểu =Lương*Ngày công, bạn có thể sử dụng Calculated Field để trực tiếp tạo một field mới theo đúng công thức mà bạn cần.

Bạn có thể thực hiện bằng cách chọn vào PivotTable, sau đó chọn thẻ Options trên thanh Ribbon -> chọn Fields, Items & Sets -> Calculated Field.

Một hộp thoại sẽ hiện lên và bạn gõ tên field cùng công thức tương ứng -> chọn OK.

Ưu điểm của cách làm này chính là việc bạn tạo mới được 1 field và nó sẽ cùng với bạn tạo nên những báo cáo tùy chỉnh kéo thả. Bởi vì, nếu bạn tính toán bằng tay dựa trên công thức ở ngoài PivotTable, khi PivotTable bạn thay đổi khi kéo thả, chắc chắn công thức tay này sẽ không còn đúng nữa.

Ngoài ra, một số vấn đề khác cũng cần lưu ý như bẫy Average trong PivotTable mà bạn sẽ dễ mắc phải nếu không sử dụng Calculated Field này.

3. Dùng Conditional Formatting cho PivotTable để định dạng dữ liệu theo điều kiện

Ở chế độ tính toán bình thường thì mình nghĩ rất nhiều bạn biết sử dụng Conditional Formatting để định dạng dữ liệu theo điều kiện. Một điều khá hay là cho dù bạn đang xài PivotTable, Conditional Formatting vẫn tỏ ra sử dụng được như bình thường. Bạn chỉ cần vào thẻ Home trên thanh Ribbon -> chọn Conditional Formatting và thực hiện tất cả như bạn làm với ô, bạn sẽ có kết quả như bạn mong muốn.

Chẳng hạn như ví dụ của tôi, tôi chọn Conditional Formatting là Data Bars để xem tỷ lệ về dữ liệu của những người có trong danh sách.

Ưu điểm của vấn đề này chính là việc Conditional Formatting sẽ theo sát PivotTable của bạn, nghĩa là kể cả khi bạn kéo thả, Conditional Formatting sẽ chạy theo đúng với field mà bạn đã thiết lập từ trước và bạn sẽ không phải tốn công để chỉnh sửa lại điều kiện như phải làm với ô.

4. Chuyển đổi dữ liệu của bạn thành dạng %

Với bảng dữ liệu như bên trên, bây giờ bạn muốn thay đổi nó thành định dạng % tích lũy tăng dần để vẽ biểu đồ Pareto thì bạn phải làm như thế nào? Chắc chắn là bạn không nên tính toán bằng tay ra tỷ lệ % rồi sau đó cộng dồn lại.

Chúng ta có thể làm theo một cách khác như sau: Bấm vào giá trị mà bạn cần chuyển đổi ngay trên vùng Values của PivotTable -> chọn Value Field Settings.

Một cửa sổ sẽ hiện lên, và bạn chọn qua thẻ Show Values As. Ngay tại đây, bạn sẽ có rất nhiều lựa chọn tùy theo vào mục đích bạn muốn biến dữ liệu ra sao. Với tôi, nếu tôi muốn tùy biến thành % tích lũy, tôi có thể chọn vào % Running Total In -> OK.

Và dữ liệu của bạn sẽ biến chuyển thành % tích lũy ngay lập tức.

5/ Group (nhóm) các dữ liệu

Bạn có nhiều dữ liệu rời rạc chẳng hạn như các tháng với nhau, và bạn muốn tính toán theo từng quý chẳng hạn, vậy bạn sẽ làm thế nào? Chúng ta sẽ có vài cách như sau:
Cách 1:  Trình diễn hết 12 tháng cùng dữ liệu tương ứng, sau đó tính tổng bằng hàm SUM bên ngoài để lấy giá trị tổng.
Cách 2: Hoặc bạn chuyển qua dữ liệu gốc và thêm một cột Quý để PivotTable có thêm Quý nhằm giúp bạn kéo thả dễ dàng hơn.
Cách 3: Sử dụng chức năng Group của PivotTable

Chúng ta có thể phân định cách nào tốt hơn qua ví dụ rời rạc hơn nữa như bài toán, tìm trung bình của 3 tháng 1, 5 và 7. Bạn có thể thấy bài toán này không có quy luật như bài toán Quý kể trên.

Và bạn cũng sẽ dễ dàng nhận thấy, cách 1 chỉ mang tính chất mì ăn liền giải quyết ngay tại chỗ, nhưng nó không thể theo bạn trong việc chuyển đổi bằng kéo thả vốn dĩ là đặc trưng của PivotTable. Bên cạnh đó, trong trường hợp tính kết quả tháng 1, 5 và 7, sử dụng công thức rất cập rập và khó khăn.

Cách 2 cũng không tốt hơn vì không có quy luật nào tồn tại ở đây thì sao bạn có thể điền dữ liệu cho một cột mới?

Cách 3 là ổn thỏa hơn cả. Quả thật, chức năng Group của PivotTable rất tốt, nó giúp bạn group lại các dữ liệu rời rạc mà bạn cho rằng nó liên quan để có thể có được kết quả tổng, trung bình,… từ nó một cách dễ dàng mà không cần phải tốn sức sử dụng bất kỳ phương pháp làm tay nào cả.

Hướng dẫn sử dụng Pivot chart

Pivot Chart và Pivot Table là 2 công cụ của Excel có mối quan hệ mật thiết và có vai trò quan trọng trong việc phân tích, thống kê dữ liệu. Pivot Chart chính là một cách thể hiện khác trực quạn hơn của Pivot Table. Để tạo Pivot Chart từ Pivot Table, bạn vui long xem hướng dẫn ở đây: Sử dụng Pivot chart tạo biều đồ tự động theo tùy chọn người dùng