Thông tin về cách sử dụng pivot table trong excel bạn cần biết

Xem nhanh

• Bảng Pivot trong Excel - Công cụ tóm tắt và khám phá dữ liệu lớn
• Tạo Bảng Pivot trong Excel - Dễ dàng hơn bạn nghĩ!
• Làm việc với Danh sách Trường Bảng Pivot
• 1. Chuẩn bị dữ liệu nguồn
• 2. Tạo Bảng Pivot
• 3. Sắp xếp bố cục báo cáo Bảng Pivot
• 4. Chọn hàm cho trường Giá trị (tùy chọn)
• 5. Hiển thị các cách tính khác nhau trong trường giá trị (tùy chọn)
• Thay đổi cách hiển thị:
• Đóng và mở Bảng Pivot:
• Để hiển thị lại Bảng Pivot:
• Thêm trường vào Bảng Pivot:
• Xóa trường khỏi Bảng Pivot:
• Sắp xếp các trường Bảng Pivot:

Nếu bạn đang làm việc với các bộ dữ liệu lớn trong Excel, Pivot Table rất hữu ích để tạo ra một bản tóm tắt tương tác một cách nhanh chóng từ nhiều bản ghi. Trong số các tính năng khác, nó có thể tự động sắp xếp và lọc các tập hợp con khác nhau của dữ liệu, đếm tổng số, tính trung bình cũng như tạo bảng chéo. Một lợi ích khác của việc sử dụng Pivot Table là bạn có thể thiết lập và thay đổi cấu trúc của bảng tóm tắt chỉ bằng cách kéo và thả các cột của bảng nguồn. Tính năng xoay hoặc xoay này đã tạo nên tên gọi của nó.

Bảng Pivot trong Excel - Công cụ tóm tắt và khám phá dữ liệu lớn

Bảng Pivot trong Excel là một công cụ mạnh mẽ giúp bạn khám phá, tóm tắt và phân tích khối lượng lớn dữ liệu, từ đó lập báo cáo tổng hợp với những chức năng chính:

Hiển thị dữ liệu trực quan: Biến khối lượng dữ liệu khổng lồ thành các bảng dễ nhìn, dễ hiểu.

Tổng hợp theo nhóm: Gom dữ liệu theo các nhóm và nhóm con, thuận tiện cho việc so sánh.

Lọc, nhóm, sắp xếp và định dạng theo điều kiện: Tập trung vào thông tin quan trọng nhất, loại bỏ những phần không cần thiết.

Xoay dữ liệu: Chuyển hàng thành cột, cột thành hàng, giúp bạn có những góc nhìn khác nhau về dữ liệu.

**Tính toán tổng: Tính tổng cộng, trung bình và các phép toán khác cho dữ liệu số trong bảng.

Mở rộng/Thu gọn bậc dữ liệu: Xem chi tiết từng phần nhỏ hoặc tổng quan toàn bộ dữ liệu.

Trình bày dữ liệu: Tạo báo cáo trực tuyến hoặc in ấn đẹp mắt, dễ hiểu.

Ví dụ: Bạn có một bảng với hàng trăm dòng dữ liệu về doanh số của các đại lý địa phương.

Thông thường, bạn có thể dùng hàm SUMIF hoặc SUMIFS để tính tổng theo điều kiện. Tuy nhiên, nếu bạn muốn so sánh nhiều yếu tố khác nhau liên quan đến doanh số, dùng Bảng Pivot sẽ hiệu quả hơn nhiều.

Chỉ với vài cú click chuột, bạn có thể tạo ra một bảng tóm tắt linh hoạt, dễ tùy chỉnh, tính tổng dữ liệu theo bất kỳ trường nào bạn muốn.

Những hình ảnh phía trên chỉ là một vài ví dụ về các kiểu bố cục của Bảng Pivot. Các bước hướng dẫn phía dưới sẽ cho bạn thấy cách nhanh chóng tạo Bảng Pivot trên mọi phiên bản Excel.

Tạo Bảng Pivot trong Excel - Dễ dàng hơn bạn nghĩ!

Nhiều người cho rằng tạo Bảng Pivot trong Excel rắc rối và mất thời gian. Nhưng điều này không đúng! Microsoft đã cải tiến công nghệ này trong nhiều năm, và ở các phiên bản Excel hiện đại, việc tạo báo cáo tóm tắt rất dễ dàng và nhanh chóng. Thực tế, bạn có thể xây dựng bảng tóm tắt của riêng mình chỉ trong vài phút. Và đây là cách thực hiện:

1. Chuẩn bị dữ liệu nguồn

Trước khi tạo báo cáo tóm tắt, hãy sắp xếp dữ liệu của bạn thành các hàng và cột, sau đó chuyển vùng dữ liệu thành một Bảng Excel. Để thực hiện việc này, chọn toàn bộ dữ liệu, chuyển sang tab "Insert" và nhấp vào "Table".

Sử dụng Bảng Excel cho dữ liệu nguồn mang lại một lợi ích rất lớn - vùng dữ liệu của bạn trở nên "động". Trong ngữ cảnh này, vùng động có nghĩa là bảng của bạn sẽ tự động mở rộng và thu hẹp khi bạn thêm hoặc xóa các mục, vì vậy bạn không cần lo lắng Bảng Pivot của mình thiếu dữ liệu mới nhất.

Mẹo hay:

• Thêm các tiêu đề duy nhất, dễ hiểu cho các cột, chúng sẽ trở thành tên trường sau này.
• Đảm bảo bảng nguồn của bạn không có hàng hoặc cột trống, và không có tổng phụ.
• Để dễ dàng quản lý bảng hơn, bạn có thể đặt tên cho bảng nguồn bằng cách chuyển sang tab "Design" và nhập tên vào ô "Table Name" ở góc trên bên phải của trang tính.

2. Tạo Bảng Pivot

• Chọn dữ liệu nguồn: Chọn bất kỳ ô nào trong bảng dữ liệu nguồn của bạn.
• Vào tab Insert: Nhấp vào tab "Insert" trên thanh công cụ Excel.
• Nhấp vào nút PivotTable: Trong nhóm "Tables", nhấp vào nút "PivotTable".
• Chọn vị trí cho bảng: Trong cửa sổ "Create PivotTable", bạn sẽ có hai lựa chọn:
  • New Worksheet: Tạo bảng mới trên một trang tính riêng biệt.
  • Existing Worksheet: Đặt bảng vào một vị trí cụ thể trên trang tính hiện có.
• Nhấp vào OK: Sau khi chọn vị trí, nhấp vào nút "OK" để tạo bảng Pivot. Bạn sẽ thấy một bảng trống xuất hiện ở vị trí đã chọn.

Mẹo hay:

• Đặt bảng riêng: Thông thường, nên đặt bảng Pivot trên một trang tính riêng biệt, đặc biệt là đối với người mới bắt đầu.
• Dữ liệu từ nguồn khác: Nếu bạn muốn tạo bảng từ dữ liệu trong một trang tính hoặc bảng tính khác, hãy nhập tên bảng tính và trang tính theo cú pháp [tên_bảng_tính]tên_trang_tính!vùng_dữ_liệu, ví dụ: [Book1.xlsx]Sheet1!$A$1:$E$20.
• Tạo bảng và biểu đồ cùng lúc: Bạn có thể tạo bảng Pivot và biểu đồ Pivot cùng lúc bằng cách vào tab "Insert", nhấp vào mũi tên bên dưới nút "Pivot Chart" và chọn "Pivot Chart & Pivot Table".

3. Sắp xếp bố cục báo cáo Bảng Pivot

Khu vực để bạn làm việc với các trường trong báo cáo tóm tắt được gọi là Danh sách Trường PivotTable. Nó nằm ở phía bên phải của trang tính và được chia thành hai phần:

• Phần Trường (Field Section): Chứa tên của các trường mà bạn có thể thêm vào bảng. Tên trường tương ứng với tên cột trong bảng nguồn của bạn.
• Phần Bố cục (Layout Section): Gồm các khu vực: Báo cáo Bộ lọc (Report Filter), Nhãn Cột (Column Labels), Nhãn Hàng (Row Labels) và Giá trị (Values). Tại đây, bạn có thể sắp xếp và bố trí các trường trong bảng.

Mọi thay đổi bạn thực hiện trong Danh sách Trường PivotTable sẽ được phản ánh ngay lập tức vào bảng của bạn.

Thêm trường vào Bảng Pivot:

Để thêm trường vào Phần Bố cục, hãy chọn hộp kiểm bên cạnh tên trường trong Phần Trường.

Theo mặc định, Excel sẽ thêm các trường vào Phần Bố cục theo cách sau:

• Trường không phải số được thêm vào khu vực Nhãn Hàng.
• Trường số được thêm vào khu vực Giá trị.
• Thứ bậc ngày và giờ OLAP được thêm vào khu vực Nhãn Cột.

Xóa trường khỏi Bảng Pivot:

Để xóa một trường, bạn có thể:

• Bỏ chọn hộp kiểm bên cạnh tên trường trong Phần Trường của khung PivotTable.
• Nhấp chuột phải vào trường trong Bảng Pivot và sau đó nhấp vào "Xóa Tên_Trường".

Sắp xếp các trường Bảng Pivot:

Bạn có thể sắp xếp các trường trong Phần Bố cục theo ba cách:

• Kéo và thả: Kéo và thả các trường giữa 4 khu vực của Phần Bố cục bằng chuột.
• Nhấp chuột phải: Nhấp chuột phải vào tên trường trong Phần Trường và sau đó chọn khu vực mà bạn muốn thêm trường vào.
• Nhấp vào trường: Nhấp vào trường trong Phần Bố cục để chọn nó. Thao tác này cũng sẽ hiển thị các tùy chọn có sẵn cho trường cụ thể đó.

4. Chọn hàm cho trường Giá trị (tùy chọn)

Theo mặc định, Excel sử dụng hàm SUM (tổng) cho các trường giá trị số mà bạn đặt trong khu vực Giá trị (Values) của Danh sách Trường (Field List). Khi bạn đặt dữ liệu không phải số (văn bản, ngày tháng hoặc Boolean) hoặc giá trị trống trong khu vực Giá trị, hàm COUNT (đếm) sẽ được áp dụng.

Tuy nhiên, bạn có thể chọn một hàm tổng hợp khác nếu muốn. Trong Excel 2013 trở lên, hãy nhấp chuột phải vào trường giá trị bạn muốn thay đổi, nhấp vào "Summarize Values By" (Tính tổng giá trị theo) và chọn hàm tổng hợp bạn muốn.

Trong Excel 2010 trở xuống, tùy chọn "Summarize Values By" cũng có sẵn trên thanh công cụ Ribbon - ở tab "Options", trong nhóm "Calculations".

Dưới đây là ví dụ về Bảng Pivot với hàm AVERAGE (trung bình):

Các hàm tổng hợp phổ biến:

• Sum: Tính tổng các giá trị.
• Count: Đếm số lượng giá trị không trống (hoạt động như hàm COUNTA).
• Average: Tính trung bình các giá trị.
• Max: Tìm giá trị lớn nhất.
• Min: Tìm giá trị nhỏ nhất.
• Product: Tính tích các giá trị.

Để xem các hàm cụ thể hơn, hãy nhấp vào "Summarize Values By" > "More Options…". Bạn có thể tìm thấy danh sách đầy đủ các hàm tổng hợp có sẵn và mô tả chi tiết của chúng tại đây.

5. Hiển thị các cách tính khác nhau trong trường giá trị (tùy chọn)

Bảng Pivot trong Excel cung cấp một tính năng hữu ích khác cho phép bạn trình bày các giá trị theo nhiều cách khác nhau, chẳng hạn như hiển thị tổng số dưới dạng phần trăm hoặc xếp hạng các giá trị từ nhỏ nhất đến lớn nhất và ngược lại. Danh sách đầy đủ các tùy chọn tính toán có sẵn tại đây.

Tính năng này được gọi là "Hiển thị Giá trị Dưới dạng" (Show Values As) và có thể truy cập bằng cách nhấp chuột phải vào trường trong bảng trong Excel 2013 trở lên. Trong Excel 2010 trở xuống, bạn cũng có thể tìm thấy tùy chọn này trên tab "Options", trong nhóm "Calculations".

Mẹo: 

Tính năng "Hiển thị Giá trị Dưới dạng" có thể đặc biệt hữu ích nếu bạn thêm cùng một trường nhiều lần và hiển thị, ví dụ: tổng doanh số và doanh số dưới dạng phần trăm của tổng số cùng một lúc. Dưới đây là một ví dụ về bảng như vậy:

Vậy là bạn đã biết cách tạo Bảng Pivot trong Excel. Bây giờ, hãy dành thời gian thử nghiệm với các trường để chọn bố cục phù hợp nhất với bộ dữ liệu của bạn.

Làm việc với Danh sách Trường Bảng Pivot

Danh sách Trường Bảng Pivot, thường được gọi tắt là Bảng Pivot, là công cụ chính để bạn sắp xếp và tùy chỉnh báo cáo tóm tắt theo ý muốn. Để làm việc hiệu quả hơn, bạn có thể tùy chỉnh giao diện của Bảng Pivot.

Thay đổi cách hiển thị:

Nếu bạn muốn thay đổi cách hiển thị các phần trong Bảng Pivot, hãy nhấp vào nút "Tools" (Công cụ) và chọn bố cục ưa thích. Bạn cũng có thể thay đổi kích thước ngang của Bảng Pivot bằng cách kéo thanh ngăn cách giữa Bảng Pivot và trang tính.

Đóng và mở Bảng Pivot:

Để đóng Bảng Pivot, bạn chỉ cần nhấp vào nút "Close" (X) ở góc trên bên phải. Tuy nhiên, mở lại Bảng Pivot không đơn giản như vậy.

Để hiển thị lại Bảng Pivot:

Nhấp chuột phải vào bất kỳ vị trí nào trong bảng, rồi chọn "Show Field List" (Hiển thị danh sách trường) từ menu ngữ cảnh.

Hoặc, nhấp vào nút "Field List" trên thanh ribbon trong tab "Analyze/Options", nhóm "Show".

Với việc học cách sử dụng pivot table trong excel bạn có thể tạo ra các dữ liệu báo cáo hấp dẫn, trực quan và chi tiết nhất. Bạn cần tập luyện thường xuyên để có thể sử dụng thành thạo công cụ này cho công việc.