31 Mẹo và thủ thuật bảng tổng hợp Excel nâng cao
Không nghi ngờ gì nữa, bảng tổng hợp là công cụ quan trọng nhất có thể giúp bạn trở thành người dùng Excel nâng cao.
Khi nói đến phân tích dữ liệu, báo cáo nhanh chóng và hiệu quả hoặc trình bày dữ liệu tóm tắt không có gì có thể đánh bại một bảng tổng hợp.
Điều tốt nhất là, nó là mạnh mẽ và linh hoạt.
Ngay cả khi bạn so sánh công thức và bảng tổng hợp, bạn sẽ thấy rằng các bảng tổng hợp dễ sử dụng và quản lý.
Bảng tổng hợp Mẹo và thủ thuật Excel nâng cao
Và ở đây cho bạn, đây là danh sách các mẹo và thủ thuật hữu ích mà bạn có thể học cách làm tốt nhất công cụ tuyệt vời này.
Băt đâu nao…
1. Sử dụng bảng cho dữ liệu nguồn
Trước khi bạn tạo bảng tổng hợp, bạn nên chuyển đổi dữ liệu nguồn của mình thành một bảng. Một bảng mở rộng chính nó bất cứ khi nào bạn thêm dữ liệu mới vào nó.
Điều này sẽ cho phép trục của bạn tự động mở rộng nguồn của nó bằng cách làm mới nó . Vì vậy, không cần phải cập nhật phạm vi nguồn một lần nữa và một lần nữa.
… đây là ba bước đơn giản.
- Chọn toàn bộ dữ liệu của bạn hoặc bất kỳ ô nào.
- Nhấn phím tắt Ctrl + T.
- Nhấp vào OK.
Nó sẽ chuyển đổi dữ liệu của bạn thành một bảng và sau đó bạn có thể chèn một bảng tổng hợp với dữ liệu này.
2. Slicer đơn cho tất cả các bảng tổng hợp
Đôi khi, khi bạn có nhiều bảng tổng hợp, thật khó để kiểm soát tất cả các bảng. Nhưng nếu bạn kết nối một slicer đơn với họ, bạn có thể kiểm soát tất cả các trục không có nỗ lực.
Làm theo các bước sau.
- Trước hết, chèn một slicer.
- Nhấp chuột phải vào slicer và chọn “Report Connections”.
- Từ hộp thoại, chọn tất cả các trục xoay và nhấn OK.
Bây giờ bạn chỉ có thể lọc tất cả các bảng tổng hợp bằng một slicer đơn.
3. Sổ làm việc bên ngoài dưới dạng nguồn
Giả sử bạn muốn tạo một trục từ sổ làm việc nằm trong một thư mục khác và bạn không muốn thêm dữ liệu từ sổ làm việc đó vào trang tính hiện tại của mình.
Vâng, bạn có thể liên kết tệp đó dưới dạng nguồn mà không thêm dữ liệu vào tệp hiện tại.
… đây là các bước.
- Trong hộp thoại tạo bảng tổng hợp, hãy chọn “Use an external data source”
- Sau đó, vào thẻ Connection và nhấp vào “Browse for more”.
- Định vị tệp bạn muốn sử dụng và chọn tệp đó.
- Nhấp vào OK.
- Bây giờ chọn trang tính mà bạn có dữ liệu.
- Nhấp OK (Hai lần).
Bây giờ bạn có một bảng tổng hợp trống với tất cả các tùy chọn trường từ nguồn bên ngoài.
4. Làm mới dữ liệu khi mở tệp
Đây là thiết lập một lần bạn có thể thực hiện để làm cho bảng tổng hợp của bạn làm mới mỗi khi bạn mở tệp của mình.
Với điều này, bạn sẽ nhận được Bảng pivot cập nhật thực sự mỗi lần. Đây là cách bạn có thể làm điều này:
- Nhấp vào bất kỳ ô nào trong bảng tổng hợp.
- Nhấp chuột phải và nhấp vào “Pivot table option”.
- Chuyển đến tab “data” và đánh dấu chọn “Refresh data when opening the file”.
- Nhấp vào OK.
Ghi chú nhanh: Nếu bạn có nhiều bảng tổng hợp với cùng một nguồn dữ liệu thì tất cả các bảng đó cũng sẽ được cập nhật mỗi khi bạn mở tệp.
5. Làm mới dữ liệu sau một khoảng thời gian cụ thể
Nếu bạn muốn tự động cập nhật bảng tổng hợp của mình sau một khoảng thời gian cụ thể thì mẹo này dành cho bạn.
… đây là cách làm.
- Trước hết, trong khi tạo một bảng tổng hợp, trong cửa sổ ““Create Pivot Table”, đánh dấu chọn “Add this data to data model”.
- Sau đó, khi bạn tạo bảng tổng hợp, hãy chọn bất kỳ ô nào và chuyển đến”Analyze Tab”..
- Trong Analyze Tab, Data ➜ Change Data Source ➜ Connection Properties.
- Bây giờ trong “Connection Properties”, trong tab sử dụng, đánh dấu chọn “Refresh Every” và nhập phút.
- Nhấp vào OK.
Bây giờ, sau khoảng thời gian cụ thể mà bạn đã nhập, trục của bạn sẽ tự động được làm mới.
6. Bảo tồn định dạng ô
Trong khi cập nhật bảng tổng hợp, có vấn đề mà chúng tôi phải đối mặt, nó sẽ xóa định dạng tùy chỉnh của bạn.
Giả sử bạn đã đánh dấu một ô có màu và khi bạn làm mới, Excel sẽ xóa màu đó.
Nhưng chúng ta có thể giải quyết vấn đề này bằng cách sử dụng tùy chọn đơn giản này.
- Trước hết, nhấp chuột phải vào bảng tổng hợp của bạn và mở các tùy chọn bảng tổng hợp.
- Bây giờ, trong “Layout & Format”, đánh dấu chọn“Preserve cell formatting on update”
- Cuối cùng, nhấn OK.
Bây giờ bất cứ khi nào bạn làm mới trục của bạn, bạn sẽ không mất định dạng tùy chỉnh của mình.
7. Vô hiệu hóa Auto Width Update
Một điều nữa mà hầu hết mọi người phải đối mặt trên làm mới một bảng tổng hợp là tự động phù hợp với chiều rộng cột nhưng bạn cũng có thể vô hiệu hóa nó.
Sử dụng các bước đơn giản này.
- Trước hết, nhấp chuột phải vào bảng tổng hợp của bạn và mở các tùy chọn bảng tổng hợp.
- Bây giờ, trong “Layout & Format”, đánh dấu chọn “AutoFit Column Width on Update”.
- Nhấp vào OK.
Bây giờ các cột của bạn sẽ vẫn giữ nguyên chiều rộng mỗi khi bạn làm mới trục của mình.
8. Thay thế giá trị lỗi
Đôi khi, khi bạn có lỗi trong dữ liệu nguồn của mình, chúng phản ánh theo cùng một cách trong trục và đây không phải là điều tốt.
Cách tốt hơn là thay thế các lỗi đó bằng một giá trị có ý nghĩa.
Dưới đây là các bước để làm theo:
- Trước hết, nhấp chuột phải vào bảng tổng hợp của bạn và mở các tùy chọn bảng tổng hợp.
- Bây giờ trong “Layout & Format”, đánh dấu “For error value show” và nhập giá trị vào hộp nhập liệu.
- Nhấp vào OK.
Bây giờ cho tất cả các lỗi, bạn sẽ có giá trị bạn đã chỉ định.
9. Thay thế ô trống
Giả sử bạn có trục xoay cho dữ liệu bán hàng và có một số ô trống.
Đối với một người không biết tại sao những ô này trống thì có thể hỏi bạn về điều này. Vì vậy, tốt hơn là thay thế nó bằng một từ có ý nghĩa.
… các bước đơn giản mà bạn cần phải làm theo.
- Trước hết,”right click” trên bảng tổng hợp của bạn và mở các tùy chọn bảng tổng hợp.
- Bây giờ trong “Layout & Format”, đánh dấu chọn “For empty cells show” và nhập giá trị vào hộp nhập.
- Nhấp vào OK.
Bây giờ cho tất cả các lỗi, bạn sẽ có giá trị bạn đã chỉ định.
10. Nhãn mục lặp lại
Khi bạn sử dụng nhiều mục trong bảng tổng hợp, bạn có thể lặp lại các nhãn cho các mục hàng đầu.
Nó giúp dễ dàng hiểu cấu trúc của bảng tổng hợp và dưới đây là các bước để thực hiện:
- Chọn bảng tổng hợp và chuyển đến“Design tab”.
- Trong tab thiết kế, chuyển đến Layout ➜ Report Layout ➜ Repeat All Item Labels
11. thêm một hàng trống sau mỗi mục
Bây giờ, giả sử bạn có một bảng tổng hợp lớn với nhiều mục.
Ở đây bạn có thể chèn một hàng trống sau mỗi mục để không có sự lộn xộn trong trục.
Hãy xem các bước sau để thực hiện:
- Chọn bảng tổng hợp và chuyển đến Design tab.
- Trong tab thiết kế, đi tới Layout -> Blank Rows -> Insert Blank Line after Each Item.
12. Các mục kéo và thả
Khi bạn có một danh sách dài các mục trong trục của mình, bạn có thể sắp xếp tất cả các mục đó theo thứ tự tùy chỉnh bằng cách kéo và thả.
13. Tạo nhiều bảng Pivot từ một
Giả sử bạn đã tạo bảng tổng hợp từ dữ liệu bán hàng khôn ngoan hàng tháng và bạn đã sử dụng sản phẩm làm bộ lọc báo cáo.
Với tùy chọn “Show Report Filter Pages”, bạn có thể tạo nhiều trang tính với bảng tổng hợp cho từng sản phẩm.
Giả sử nếu bạn có 10 sản phẩm trong bộ lọc xoay vòng, bạn có thể tạo 10 trang tính khác nhau chỉ bằng một cú nhấp chuột.
Sử dụng các bước sau:
- Chọn trục của bạn và chuyển đến tab phân tích.
- Trong tab phân tích, chuyển đến Pivot table -> Options -> Show Report Filter Pages
Bây giờ bạn có bốn bảng tổng hợp trong bốn trang tính riêng biệt.
14. Hide/Unhide ẩn Tổng phụ
Khi bạn thêm một bảng tổng hợp với nhiều trường mục, bạn sẽ nhận được tổng phụ cho trường chính.
Nhưng đôi khi không cần hiển thị tổng phụ. Trong tình huống đó, bạn có thể ẩn chúng bằng các bước sau:
- Nhấp vào bảng tổng hợp và chuyển đến tab phân tích.
- Trong tab Analyze tab, Layout -> Subtotals -> Do not show subtotals.
15. Ẩn / Bỏ ẩn Tổng số
Cũng giống như tổng phụ, bạn cũng có thể ẩn và bỏ ẩn các tổng số lớn và dưới đây là các bước đơn giản để thực hiện điều đó.
- Nhấp vào bảng tổng hợp và chuyển đến tab phân tích.
- Trong tab Analyze tab, Layout -> Subtotals -> Do not show subtotals.
16. Ẩn lựa chọn
Giả sử bạn muốn ẩn một (các) hàng cụ thể khỏi trục của mình. Vì vậy, thay vì lọc hàng cụ thể mà bạn có thể sử dụng một cách dễ dàng.
- Trước hết, hãy chọn phần hoặc hàng đó từ trục xoay.
- Sau đó, nhấp chuột phải vào nó.
- Trong menu chuột phải, vào Filter -> Hide Items đã chọn.
17. TẮT GetPivotData
Có một tình huống mà bạn cần phải tham khảo một ô trong một trục.
Tuy nhiên, có thể có một vấn đề bởi vì khi bạn tham chiếu đến một ô trong Excel Excel sẽ tự động sử dụng hàm GetPivotData để tham chiếu.
Điều tốt nhất là, bạn có thể tắt nó và đây là các bước:
- Chuyển đến File Tab -> Options.
- Trong các tùy chọn, đi tới Formulas -> Working with Formulas -> untick “Use GetPivotData functions for PivotTable reference.
18. Lọc 10 giá trị hàng đầu
Bảng tổng hợp là một báo cáo trong chính nó và điều tốt nhất là nó năng động. có rất nhiều tùy chọn để làm tốt nhất.
Lọc 10 giá trị hàng đầu là một trong số chúng. Sử dụng các bước sau:
- Mở bộ lọc từ đầu cột mục.
- Chuyển đến Value Filter -> Top 10.
- Điều này sẽ cung cấp cho bạn một cửa sổ để chọn các tùy chọn để lọc 10 giá trị hàng đầu (Bạn cũng có thể lọc các giá trị dưới cùng).
- Sau đó, bấm OK.
Nó sẽ ngay lập tức lọc chỉ 10 giá trị hàng đầu cho mục mà bạn đã áp dụng bộ lọc.
19. Đánh dấu 10 giá trị hàng đầu
Thay vì lọc, bạn có thể làm nổi bật 10 giá trị hàng đầu từ bảng tổng hợp. Đối với điều này, bạn cần phải sử dụng định dạng có điều kiện.
Các bước dưới đây:
- Chọn bất kỳ ô nào từ cột giá trị trong trục của bạn.
- Chuyển đến tab Home tab -> Styles -> Conditional Formatting.
- Bây giờ trong định dạng có điều kiện, đi đến Top / Bottom Rules -> Top 10 Items.
- Chọn màu từ cửa sổ bạn đã có.
- Và bấm OK.
20. Ngày nhóm trong bảng tổng hợp
Chỉ cần tưởng tượng, bạn muốn tạo bảng tổng hợp khôn ngoan hàng tháng nhưng bạn có ngày trong dữ liệu của mình.
Trong tình huống này, bạn cần thêm cột phụ trong nhiều tháng.
Nhưng cách tốt nhất là tạo ra bằng cách sử dụng các phương thức ngày tháng nhóm trong bảng tổng hợp bằng cách sử dụng phương thức này, bạn không cần thêm cột trợ giúp.
Sử dụng các bước dưới đây:
- Trước hết, bạn cần chèn ngày làm mục hàng trong bảng tổng hợp.
- Nhấp chuột phải vào bảng tổng hợp và chọn “Group…”.
- Chọn “Month” theo từng phần và nhấn OK.
Nó sẽ nhóm tất cả các ngày thành nhiều tháng.
21. Tính toán trong bảng Pivot
Để trở thành người dùng bảng tổng hợp nâng cao, bạn nên tìm hiểu cách tạo trường và mục được tính toán trong bảng tổng hợp.
Giả sử trong bảng tổng hợp dưới đây, bạn cần tạo một dữ liệu mới theo trường nhân với trường dữ liệu hiện tại bằng 10.
Trong trường hợp này, thay vì tạo cột riêng trong bảng tổng hợp, bạn có thể chèn mục được tính toán.
22. Chạy tổng cột trong bảng tổng hợp
Giả sử bạn có bảng bán hàng khôn ngoan trong bảng tổng hợp. Bây giờ, bạn muốn chèn tổng số đang chạy vào bảng tổng hợp của mình để hiển thị mức tăng trưởng doanh số hoàn toàn trong toàn bộ tháng.
Dưới đây là các bước:
- Nhấp chuột phải vào nó và nhấp vào “Value Field Setting”.
- Từ danh sách thả xuống Show Values As”, chọn “Running Total In”.
- Nhấp vào OK.
23. Thêm các cấp bậc trong bảng tổng hợp
Xếp hạng cung cấp cho bạn cách tốt hơn để so sánh mọi thứ với nhau và chèn cột xếp hạng vào bảng tổng hợp, bạn có thể sử dụng các bước sau:
- Trước hết, hãy chèn cùng một trường dữ liệu hai lần vào trục.
- Sau đó cho trường thứ hai, nhấp chuột phải vào trường đó và mở “Value Field Settings”.
- Chuyển đến tab “Show Values as” và chọn “Rank Largest To Smallest”.
- Nhấp vào OK.
24. Tạo phần trăm chia sẻ
Hãy tưởng tượng bạn có bảng tổng hợp để bán sản phẩm thông minh. Và bây giờ, bạn muốn tính tỷ lệ phần trăm của tất cả các sản phẩm trong tổng doanh thu.
Các bước để sử dụng:
- Trước hết, hãy chèn cùng một trường dữ liệu hai lần vào trục.
- Sau đó cho trường thứ hai, nhấp chuột phải vào trường đó và mở “Value Field Settings”.
- Chuyển đến tab “Show Values as” và chọn “% of Grand Total”
- Nhấp vào OK.
25. Chèn một Dòng thời gian
Nếu dữ liệu của bạn có nhiều thông tin về ngày giờ thì bạn có thể chèn bộ lọc dòng thời gian với bảng tổng hợp. Nó sẽ giúp bạn lọc ngày một cách tốt hơn.
Dưới đây là các bước để chèn dòng thời gian với bảng tổng hợp.
- Chọn bất kỳ ô nào trong bảng tổng hợp của bạn.
- Chuyển đến Công cụ Pivot Table Tools ➜ Analyze ➜ Filter ➜ Insert Timeline.
- Từ hộp bật lên, chọn cột ngày và nhấp vào OK.
26. Lấy danh sách các giá trị duy nhất
Nếu bạn có các giá trị trùng lặp trong ngày của mình thì bạn có thể sử dụng bảng tổng hợp để có danh sách các giá trị duy nhất.
- Trước hết, bạn cần phải chèn một bảng tổng hợp và sau đó thêm cột nơi bạn có các giá trị trùng lặp làm trường hàng.
- Sau đó, sao chép trường hàng đó từ trục xoay và dán nó dưới dạng giá trị.
- Bây giờ danh sách bạn có dưới dạng giá trị là danh sách các giá trị duy nhất.
27. Giảm kích thước của báo cáo bảng tổng hợp
Để giảm kích thước của bảng tổng hợp, bạn có thể thử bất kỳ phương pháp nào sau đây.
- Sao chép-dán thay vì tạo từ đầu: Đôi khi bạn cần tạo nhiều hơn một bảng tổng hợp từ cùng một nguồn dữ liệu, cách tốt nhất là sao chép và dán trục đầu tiên và chúng thực hiện các thay đổi trong đó. Bằng cách này, bạn sẽ có một bộ đệm ẩn cho tất cả các bảng tổng hợp cho cùng một nguồn dữ liệu.
- Xóa dữ liệu nguồn: Nếu bạn có bảng tổng hợp và dữ liệu nguồn trong một sổ làm việc, bạn có thể xóa dữ liệu nguồn trước khi gửi tệp đó cho ai đó. Khi bạn tạo một pivot Excel lưu trữ toàn bộ dữ liệu nguồn trong bộ nhớ cache. Và, nếu ai đó cần dữ liệu nguồn, họ có thể nhận được dữ liệu đó bằng cách nhấp vào ô tổng lớn của bảng tổng hợp.
- Trình tìm kiếm bộ nhớ cache trùng lặp : Nếu bạn đã có nhiều bảng tổng hợp trong sổ làm việc của mình, . Và sau đó bạn có thể xóa tất cả các cách trùng lặp và giảm kích thước của sổ làm việc của bạn.
28. Chuyển đổi một bảng tóm tắt thành một dữ liệu thô
Bạn cũng có thể sử dụng bảng tổng hợp để hủy xoay vòng bảng dữ liệu bằng phương pháp này hoặc bằng cách sử dụng các bước bên dưới.
- Trước hết, mở trình hướng dẫn bảng tổng hợp cũ bằng cách sử dụng phím tắt ALT-D P.
- Chọn “Multiple Consolidation Ranges”
- Sau đó, nhấp vào ““Create a single Page field for me”.
- Và sau đó lấy toàn bộ phạm vi dữ liệu và nhấp vào “Add Insert the Pivot Table” vào Trang tính Mới.
- Nhấp vào “”Finish”.
Khi bạn chèn một trục với phương thức này, hãy nhấp vào ô tổng lớn để lấy toàn bộ dữ liệu dưới dạng dữ liệu thô.
29. VBA để tạo bảng tổng hợp
Bạn cũng có thể sử dụng mã macro để tạo bảng tổng hợp . Bạn cần một nhấp chuột duy nhất để khi bạn cần chèn nó.
30. Phím tắt trên Pivot Table hàng đầu
Dưới đây là một số phím tắt quan trọng mà bạn có thể sử dụng tiết kiệm thời gian trong khi làm việc trên các bảng tổng hợp.
- Alt + N + V : Để tạo bảng tổng hợp.
- Alt + Shift + Mũi tên phải : Nhóm các mục bảng tổng hợp được chọn.
- Alt + Shift + Mũi tên trái : Ungroup các mục bảng tổng hợp đã chọn.
- Ctrl + – : Ẩn mục hoặc trường đã chọn.
- Shift + Ctrl + = : Mở cửa sổ trường / mục được tính toán.
- Alt + D, P : Mở trình hướng dẫn bảng tổng hợp cũ.
- Alt + Down Arrow : Mở danh sách trường cho ô hiện hoạt.
Dịch Bông Sara
