PivotTable có thể tóm tắt hàng ngàn dòng dữ liệu trong Excel chỉ trong vài giây, thế nhưng nhiều người vẫn lãng phí thời gian lọc dữ liệu. Những mẹo dùng PivotTable của Microsoft Excel dưới đây sẽ giúp công việc xử lý bảng tính của bạn trở nên đơn giản hơn.
Mẹo dùng PivotTable Excel
- Xem dữ liệu nguồn bằng cách nhấp đúp vào giá trị bạn muốn
- Tạo bảng tính riêng cho mỗi danh mục
- Sử dụng chức năng Distinct Count để theo dõi các giá trị duy nhất
- Nhóm các mục liên quan mà không cần thêm cột phụ trợ
- Tính toán tăng trưởng theo tháng mà không cần phải viết công thức
Xem dữ liệu nguồn bằng cách nhấp đúp vào giá trị bạn muốn
Giả sử bạn muốn biết thêm chi tiết về một trong số các giá trị ở PivotTable của mình. Thay vì chuyển đổi giữa các tab gây mất tập trung:
- Tìm và nhấp đúp vào giá trị PivotTable bạn muốn xem.
- Xem lại bảng tính mới được tạo chỉ chứa các hàng nguồn cho giá trị đó.
- Khi quá trình xem xét hoàn tất, nhấp chuột phải vào tab trang tính mới ở cuối cửa sổ và click Delete.
Tạo bảng tính riêng cho mỗi danh mục
Đây là tính năng PivotTable giúp người dùng Excel tiết kiệm thời gian đáng kể. Đầu tiên, hãy thiết lập tự động hóa:
- Kéo trường danh mục bạn muốn tách vào Filters của ngăn trường PivotTable.
- Nhấp vào bên trong PivotTable để hiển thị các công cụ ruy băng theo ngữ cảnh.
- Mở tab PivotTable Analyze.
- Nhấp vào mũi tên thả xuống nhỏ ngay bên cạnh nút Options ở phía bên trái.
- Chọn Show Report Filter Pages từ menu thả xuống theo ngữ cảnh.
Tiếp theo, để tạo các trang tính:
- Kiểm tra xem trường bộ lọc đã chọn trong hộp thoại bật lên có khớp với cột mục tiêu của bạn hay không.
- Nhấp vào OK để chạy tự động tạo trang tính.
- Nhấp chuột qua các tab trang tính mới được tạo để xem từng báo cáo.
- Để xuất một báo cáo cụ thể, nhấp chuột phải vào một tab trang tính, sau đó nhấp vào Move hoặc Copy.
Sử dụng chức năng Distinct Count để theo dõi các giá trị duy nhất
Bằng cách thêm dữ liệu nguồn vào Data Model của Excel khi tạo bảng lần đầu, bạn sẽ mở khóa tùy chọn đếm giá trị khác biệt ẩn, bỏ qua hoàn toàn các mục trùng lặp.
Bắt đầu bằng cách khởi tạo không gian làm việc Model Data của Excel:
- Chọn bảng nguồn thô của bạn và mở tab Insert.
- Nhấp vào PivotTable để mở hộp thoại tạo tiêu chuẩn.
- Chọn vị trí trang tính đích.
- Tích Add this data to the Data Model.
- Nhấp vào OK để tạo PivotTable mới của bạn.
Giờ đây, bạn đã sẵn sàng chuyển bảng tổng hợp sang chế độ đếm riêng biệt:
- Kéo trường nhận dạng vào ô Values.
- Nhấp chuột phải vào số bất kỳ trong cột vừa thêm và chọn Value Field Settings.
- Cuộn xuống danh sách tính toán và nhấp vào Distinct Count.
- Click OK.
Bảng PivotTable sẽ cập nhật ngay lập tức để hiển thị số lượng riêng biệt, nghĩa là mỗi khách hàng chỉ được tính một lần cho mỗi khu vực, bất kể họ đã mua bao nhiêu lần.
Nhóm các mục liên quan mà không cần thêm cột phụ trợ
Thay vì sửa đổi cơ sở dữ liệu chính hoặc tạo cột phụ trợ, bạn có thể xử lý tất cả trong PivotTable.
Dưới đây là cách tạo và dọn dẹp các nhóm tùy chỉnh:
- Giữ phím Ctrl trong khi nhấp vào từng nhãn văn bản riêng lẻ trong các hàng thuộc nhóm tùy chỉnh đầu tiên của bạn.
- Khi các mục đó vẫn được chọn, nhấp chuột phải vào bất kỳ mục nào trong số chúng, sau đó chọn Group.
- Thao tác này ban đầu sẽ làm cho PivotTable trông lộn xộn, vì vậy hãy nhấp chuột phải vào tiêu đề cột PivotTable bên trái và chọn Expand/Collapse > Collapse Entire Field để dọn dẹp mọi thứ.
- Chọn ô chứa nhãn nhóm chung, sau đó ghi đè văn bản hiện có bằng một tên dễ hiểu hơn và nhấn Enter.
Sau khi lặp lại các bước chọn, nhóm và đổi tên cho các mục còn lại:
- Nhấp chuột phải vào tiêu đề trường gốc mới tạo trong lưới.
- Nhấp vào Field Settings.
- Đổi tên trường để phản ánh danh mục mà nó đại diện, sau đó click OK.
Mặc dù việc ghi đè lên các nhãn nhóm riêng lẻ trong bảng PivotTable là hoàn toàn hợp lệ và chỉ ảnh hưởng đến cách các mục đó hiển thị, nhưng tiêu đề trường ở trên cùng đại diện cho chính trường được nhóm bên dưới, chứ không phải một nhãn đơn lẻ, đó là lý do tại sao bạn cần sử dụng Field Settings.
Giờ đây, tất cả các mục liên quan và giá trị số liên kết của chúng được nhóm lại với nhau, vì vậy bạn có thể nhanh chóng tóm tắt các danh mục, phát hiện các xu hướng cấp cao hơn và tạo ra các báo cáo rõ ràng hơn mà không cần phải tự cấu trúc lại dữ liệu nguồn.
Tính toán tăng trưởng theo tháng mà không cần phải viết công thức
Để cấu hình chế độ xem tăng trưởng theo kỳ:
- Kéo số liệu hiệu suất chính của bạn vào ô Values lần thứ hai để nó xuất hiện trùng lặp trong lưới.
- Nhấp chuột phải vào bất kỳ ô nào bên trong cột giá trị được trùng lặp mới đó.
- Di chuột qua Show Values As, sau đó chọn % Difference From.
- Đặt tùy chọn thả xuống Base Field thành trường Month được tạo từ nhóm Date của bạn.
- Đặt tùy chọn thả xuống Base Item thành (previous), sau đó nhấp OK.
- PivotTable Excel hiển thị trường Doanh thu được thêm hai lần trong vùng Giá trị để chuẩn bị cho tính toán so sánh.
Bây giờ PivotTable đã hiển thị thành công sự khác biệt phần trăm theo tháng, bạn có thể nhấp vào tiêu đề của cột giá trị được trùng lặp và đổi tên trực tiếp trong lưới PivotTable.
Thêm dữ liệu mới vào bảng nguồn của bạn, làm mới bảng PivotTable và các phép tính sẽ được cập nhật ngay lập tức mà không làm hỏng cấu trúc của bạn.