Nếu bạn đang làm việc với các file dữ liệu lớn trong Excel, Pivot Table sẽ thực sự hữu ích với bạn. Nó như một cách nhanh chóng để tạo bản tóm tắt mang tính tương tác từ nhiều bản ghi, nó có thể tự động sắp xếp và lọc các tập hợp con dữ liệu khác nhau, đếm tổng, tính trung bình cũng như tạo các bảng so sánh chéo.
Một lợi ích khác của việc sử dụng Pivot Tables 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.
Trong hướng dẫn này, bạn sẽ tìm hiểu PivotTable là gì, cách tạo và sử dụng Bảng Pivot trong tất cả các phiên bản Excel
Pivot Table trong Excel là gì?
Pivot Table là một công cụ giúp khám phá và tóm tắt lượng lớn dữ liệu, phân tích các thông số có liên quan và trình bày các báo cáo tóm tắt được thiết kế nhằm:
- Trình bày lượng lớn dữ liệu theo cách thân thiện với người dùng.
- Tóm tắt dữ liệu theo từng danh mục
- Lọc, nhóm, sắp xếp và định dạng có điều kiện các tập hợp con dữ liệu khác nhau để bạn có thể tập trung vào thông tin phù hợp nhất.
- Xoay hàng thành cột hoặc cột thành hàng (gọi là pivoting) để xem các bản tóm tắt khác nhau của dữ liệu nguồn.
- Tổng hợp và tính tổng các dữ liệu số trong bảng tính.
Ví dụ: bạn có thể có rất nhiều mục nằm trong bảng tính với số liệu bán hàng của các cửa hàng theo từng khu vực như sau:
Cách khả thi nhất để tính tổng danh sách dài các số này theo một hoặc nhiều điều kiện là sử dụng các công thức như SUMIF và SUMIFS. Tuy nhiên, nếu bạn muốn so sánh một số thông tin về từng người bán, thì sử dụng Pivot Table là cách hiệu quả nhất. Chỉ trong vài cú click chuột, bạn có thể có được bảng tóm tắt linh hoạt và có thể tùy chỉnh dễ dàng, tổng hợp các số theo bất kỳ trường nào bạn muốn.
Ảnh trên chỉ thể hiện một vài trong số rất nhiều bố cục bạn có thể tạo ra. Các bước bên dưới sẽ cho bạn thấy cách có thể nhanh chóng tạo Pivot Table của riêng mình trong tất cả các phiên bản Excel.
Mẹo: Người dùng Excel365 cũng có thể nhóm và tóm tắt dữ liệu bằng công thức PIVOTBY
Cách tạo Pivot Table trong Excel
Bạn có thể tạo một Pivot Table của riêng mình chỉ trong vài bước đơn giản sau đây:
1. Chuẩn bị nguồn dữ liệu
Trước khi tạo Pivot Table, 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 đổi dữ liệu của bạn thành dạng Excel Tablel. Để thực hiện việc này, hãy chọn tất cả dữ liệu, sau đó nhấn vào tab Insert và nhấp vào Table.
Việc sử dụng Excel Tablel sẽ giúp bảng của bạn có thể tự động mở rộng và thu nhỏ khi bạn thêm hoặc xóa các mục nhập, do đó, bạn sẽ không phải lo lắng rằng Pivot Table của mình thiếu dữ liệu mới nhất.
Mẹo:
- Đảm bảo bảng dữ liệu nguồn của bạn không chứa hàng hoặc cột trống.
- Bạn có thể đặt tên cho bảng dữ liệu nguồn bằng cách chuyển sang tab Table Design và nhập tên vào ô Table Name ở góc trên bên trái của trang tính.
2.Tạo Pivot Table
Chọn bất kỳ ô nào trong bảng dữ liệu nguồn, sau đó nhấn vào tab Insert > Tables group > PivotTable.
Thao tác này sẽ mở cửa sổ tạo PivotTable. kiểm tra lại xem phạm vi ô được chọn đã chính xác chưa. Sau đó chọn vị trí đặt Pivot Table của bạn:
- New Worksheet: tùy chọn này sẽ đặt bảng trong một worksheet mới tại vị trí A1.
- Existing Worksheet: tùy chọn này sẽ đặt bảng tại bảng tính hiện tại. Tại ô Location, bạn có thể chọn vị trí đặt bảng.
Nhấn OK để tạo một Pivot Table trống tại vị trí đã chỉ định:
3. Sắp xếp bố cục của Pivot Table
Khu vực nơi bạn làm việc với các trường trong báo cáo tóm tắt của mình được gọi là PivotTable Field List. Nó nằm ở phần bên phải của trang tính và được chia thành 2 phần:
- Phần Fields chứa tên của các trường mà bạn có thể thêm vào bảng của mình. Tên được phân loại tương ứng với tên cột của bảng dữ liệu nguồn của bạn.
- Phần Layout chứa các vùng Filter, Column , Row và Values . Tại đây bạn có thể sắp xếp và sắp xếp lại các trường trong bảng của mình.
Những thay đổi bạn thực hiện trong PivotTable Field List sẽ ngay lập tức được phản ánh vào bảng của bạn.
Cách thêm trường trong Pivot Table
Để thêm một trường vào phần Layout , bạn hãy tích chọn hộp kiểm bên cạnh tên trường trong phần Field.
Mặc định, Microsoft Excel thêm các trường vào phần Layout theo cách sau:
- Các trường không phải số được thêm vào vùng Row;
- Các trường số được thêm vào vùng Values;
- Online Analytical Processing (OLAP) được thêm vào khu vực Column.
Cách xóa các trường khỏi Pivot Table
Để xóa một trường nhất định, bạn làm như sau:
- Cách 1: Bỏ chọn hộp kiểm trong phần Field của PivotTable Field List.
- Cách 2: Nhấn phải chuột vào trường trong Pivot Table, sau đó chọn Remove Field_Name.
Cách sắp xếp các trường trong Pivot Table
Bạn có thể sắp xếp các trường trong phần Layout theo ba cách:
- Kéo và thả các trường giữa 4 vùng của phần Layout bằng chuột. Ngoài ra, hãy nhấp và giữ tên trường trong phần Field, sau đó kéo tên trường đó vào một khu vực trong phần Layout – thao tác này sẽ xóa trường khỏi khu vực hiện tại trong phần Layout và đặt trường đó vào khu vực mới.
- Bấm chuột phải vào tên trường trong phần Field, sau đó chọn khu vực bạn muốn thêm trường đó:
- Bấm vào trường trong phần Layout để chọn nó. Thao tác này sẽ hiển thị các tùy chọn có sẵn cho trường cụ thể đó, bao gồm cả di chuyển.
Chọn hàm tính toán cho mục Values
Theo mặc định, Microsoft Excel áp dụng hàm Sum cho các trường giá trị số được đặt trong vùng Values. Ngược lại, đối với dữ liệu không phải số (chẳng hạn như văn bản, ngày tháng hoặc giá trị Boolean), hàm Count sẽ tự động được áp dụng.
Tuy nhiên, bạn có thể tự do lựa chọn một chức năng khác tùy theo nhu cầu của mình. Để thực hiện việc này, nhấp chuột phải vào trường giá trị bạn muốn sửa đổi, chọn Summarize Values By, sau đó chọn hàm mong muốn từ các tùy chọn được cung cấp.
Để có thêm các chức năng khác, hãy nhấp vào Summarize Values By > More Options….
Hiển thị cách tính toán khác trong trường Value
Excel Pivot Tables cung cấp thêm một tính năng hữu ích cho phép bạn trình bày giá trị theo nhiều cách khác nhau, ví dụ: hiển thị tổng dưới dạng phần trăm hoặc xếp 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 bạn có thể xem tại đây
Tính năng này được gọi là Show Values As và có thể truy cập được bằng cách bấm chuột phải vào trường trong bảng với 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.
Làm việc với PivotTable Field List
PivotTable Field List là công cụ chính mà bạn sử dụng để sắp xếp PivotTable theo cách bạn muốn. Để làm cho công việc của bạn với các trường trở nên dễ dàng và thoải mái hơn, bạn có thể tùy chỉnh List theo ý thích của mình.
Thay đổi chế độ xem của Field List
Nếu bạn muốn thay đổi cách hiển thị các phần trong Field List, hãy nhấp vào nút Công cụ (bánh răng) và chọn bố cục ưa thích của bạn.
Đóng và mở PivotTable pane
Đóng PivotTable Field List rất dễ dàng bằng cách nhấn vào nút Close (X) ở góc trên bên phải.
Để hiển thị lại PivotTable Field List, hãy bấm chuột phải vào bất kỳ vị trí nào trong bảng, sau đó chọn Show Field List.
Bạn cũng có thể nhấn vào nút Field List trên thanh Ribbon để hiển thị lại.
Cách làm mới Pivot Table trên Excel
Mặc dù Pivot Table report được kết nối với dữ liệu nguồn của bạn nhưng bạn có thể ngạc nhiên khi biết rằng Excel không tự động làm mới dữ liệu đó. Bạn có thể nhận bất kỳ cập nhật dữ liệu nào bằng cách thực hiện thao tác làm mới theo cách thủ công hoặc để nó tự động làm mới khi bạn mở sổ làm việc.
Làm mới dữ liệu Pivot Table thủ công
- Nhấn vào bất cứ đâu trong bảng.
- Trên thẻ Analyze (Thẻ Options ở các ver cũ), Nhấn vào nút Refresh, hoặc nhấn ALT+F5.
- Ngoài ra, bạn có thể nhấp chuột phải vào bảng và chọn Refresh từ menu ngữ cảnh
Để làm mới tất cả các Pivot Table trong sổ làm việc của bạn, hãy bấm vào mũi tên dưới nút Refresh, rồi chọn Refresh All.
Chú ý: Nếu định dạng của Pivot Table bị thay đổi sau khi làm mới, hãy đảm bảo các tùy chọn “Autofit column width on update” và “Preserve cell formatting on update” được chọn. Để kiểm tra, hãy nhấp vào tab Analyze > PivotTable > Options. Chuyển sang tab Layout & Format và bạn sẽ tìm thấy các hộp kiểm này ở đó.
Sau khi làm mới, bạn vẫn có thể xem lại trạng thái hoặc hủy trạng thái đó nếu bạn muốn. Chỉ cần bấm vào mũi tên dưới nút Refresh, sau đó bấm Refresh Status hoặc Cancel Refresh.
Tự động làm mới Pivot Table
Để tự động làm mới Pivot Table, bạn làm như sau:
- Trên thẻ Analyze, trong nhóm PivotTable, nhấn vào Options
- Trong hộp thoại PivotTable Option, di chuyển tới thẻ Data, và tích chọn Refresh data when opening the file.
Cách di chuyển Pivot Table
Nếu bạn muốn di chuyển bảng của mình sang một workbook mới, hoặc một khu vực khác trong trang tính hiện tại, hãy chuyển tới tab Analyze (tab Options trong Excel 2010 trở về trước) và nhấp vào nút Move PivotTable trong nhóm Actions. Chọn một điểm đến mới và nhấp vào OK.
Cách xóa Excel Pivot Table
Nếu bạn không còn cần sử dụng Pivot Table nữa, bạn có thể xóa nó bằng một số cách sau:
- Nếu Pivot Table của bạn nằm trong một trang tính riêng biệt, bạn chỉ cần xóa trang tính đó.
- Nếu bảng của bạn nằm cùng với một số dữ liệu khác trên một trang tính, hãy bôi đen toàn bộ Pivot Table và nhấn phím Delete.
- Bấm vào bất kỳ vị trí nào trong Pivot Table mà bạn muốn xóa, đi tới tab Analyze (tab Options trong Excel 2010 trở về trước) > nhóm Actions, bấm vào mũi tên nhỏ bên dưới nút Select , chọn Entire PivotTable, rồi nhấn Delete.
Hy vọng rằng hướng dẫn về Pivot Table này sẽ giúp ích được cho bạn.