Bảng tổng hợp cung cấp bộ lọc 10 hàng đầu. Nó là mát mẻ. Nó là linh hoạt. Nhưng tôi ghét nó, và tôi sẽ cho bạn biết tại sao.
Đây là bảng tổng hợp hiển thị doanh thu theo khách hàng. Tổng doanh thu là 6,7 triệu đô la. Lưu ý rằng khách hàng lớn nhất, Roto-Rooter, chiếm 9% tổng doanh thu.
Điều gì sẽ xảy ra nếu người quản lý của tôi có tầm chú ý như một con cá vàng và chỉ muốn nhìn thấy năm khách hàng hàng đầu? Để bắt đầu, hãy mở menu thả xuống trong A3 và chọn Bộ lọc giá trị, Top 10.
Hộp thoại Top 10 Filter siêu linh hoạt cho phép Top / Bottom. Nó có thể làm 10, 5 hoặc bất kỳ số nào khác. Bạn có thể yêu cầu năm mặt hàng hàng đầu, 80% hàng đầu hoặc đủ khách hàng để đạt được 5 triệu đô la.
Nhưng đây là vấn đề: Báo cáo kết quả hiển thị năm khách hàng và tổng số từ những khách hàng đó thay vì tổng số từ tất cả mọi người. Roto-Rooter, người trước đây là 9% trong tổng số thì nay là 23% trong tổng số mới.
Nhưng trước tiên, một vài từ quan trọng về AutoFilter
Tôi nhận ra rằng đây có vẻ như là một câu hỏi khó hiểu. Nếu bạn muốn bật menu thả xuống Bộ lọc trên một tập dữ liệu thông thường, bạn phải làm như thế nào? Dưới đây là ba cách thực sự phổ biến:
- Chọn một ô trong dữ liệu của bạn và nhấp vào biểu tượng Bộ lọc trên tab Dữ liệu hoặc nhấn Ctrl + Shift + L.
- Chọn tất cả dữ liệu của bạn bằng Ctrl + * và nhấp vào biểu tượng Bộ lọc trên tab Dữ liệu.
- Nhấn Ctrl + T để định dạng dữ liệu dưới dạng bảng.
Đây là ba cách thực sự tốt. Miễn là bạn biết bất kỳ ai trong số họ, hoàn toàn không cần biết cách khác. Nhưng đây là một cách cực kỳ bí ẩn nhưng kỳ diệu để bật bộ lọc:
- Chuyển đến hàng tiêu đề của bạn và sau đó chuyển đến ô tiêu đề ngoài cùng bên phải. Di chuyển một ô sang bên phải. Vì một số lý do không xác định, khi bạn ở trong ô này và nhấp vào biểu tượng Bộ lọc, Excel sẽ lọc tập dữ liệu ở bên trái của bạn. Tôi không biết tại sao nó hoạt động. Nó thực sự không đáng nói vì đã có ba cách thực sự tốt để bật menu thả xuống Bộ lọc. Tôi gọi phòng giam này là phòng giam ma thuật.
Và bây giờ, quay lại bảng tổng hợp
Có một quy tắc nói rằng bạn không thể sử dụng Tự động lọc khi bạn đang ở trong bảng tổng hợp. Xem bên dưới? Biểu tượng Bộ lọc chuyển sang màu xám vì tôi đã chọn một ô trong bảng tổng hợp.
Tôi không biết tại sao Microsoft lại giải quyết vấn đề này. Nó phải là một cái gì đó bên trong nói rằng AutoFilter và một bảng tổng hợp không thể cùng tồn tại. Vì vậy, có một người nào đó trong nhóm Excel phụ trách việc làm xám biểu tượng Bộ lọc. Người đó chưa bao giờ nghe nói về tế bào ma thuật. Chọn một ô trong bảng tổng hợp và Bộ lọc chuyển sang màu xám. Nhấp vào bên ngoài bảng tổng hợp và Bộ lọc được bật lại.
Nhưng đợi đã. Còn về tế bào ma thuật mà tôi vừa kể cho bạn nghe thì sao? Nếu bạn bấm vào ô ở bên phải của tiêu đề cuối cùng, Excel sẽ quên tô xám biểu tượng Bộ lọc!
Chắc chắn rồi, Excel thêm trình đơn thả xuống Bộ lọc Tự động vào hàng trên cùng của bảng tổng hợp của bạn. Và AutoFilter hoạt động khác với một bộ lọc bảng xoay. Chuyển đến menu thả xuống Doanh thu và chọn Bộ lọc số, 10 đầu….
Trong hộp thoại Top 10 AutoFilter, chọn Top 6 Item. Đó không phải là lỗi đánh máy… nếu bạn muốn có 5 khách hàng, hãy chọn 6. Nếu bạn muốn 10 khách hàng, hãy chọn 11.
Đối với AutoFilter, hàng tổng cộng là mục lớn nhất trong dữ liệu. Năm khách hàng hàng đầu đang chiếm giữ các vị trí từ 2 đến 6 trong dữ liệu.
Thận trọng
Rõ ràng, bạn đang làm rách một lỗ hổng trong kết cấu Excel với thủ thuật này. Nếu sau đó bạn thay đổi dữ liệu cơ bản và làm mới bảng tổng hợp của mình, Excel sẽ không làm mới bộ lọc vì theo như Microsoft biết, không có cách nào để áp dụng bộ lọc cho bảng tổng hợp!
Ghi chú
Mục tiêu của chúng tôi là giữ bí mật điều này với Microsoft vì đây là một tính năng khá thú vị. Nó đã bị "hỏng" một thời gian, vì vậy có rất nhiều người có thể đang dựa vào nó.
Một giải pháp hoàn toàn hợp pháp trong Excel 2013+
Nếu bạn muốn một bảng tổng hợp hiển thị cho bạn năm khách hàng hàng đầu nhưng tổng số từ tất cả các khách hàng, bạn phải di chuyển dữ liệu của mình ra ngoài Excel. Nếu bạn có Excel 2013 hoặc phiên bản mới hơn đang chạy trong Windows, có một cách rất tiện lợi để thực hiện việc này. Để cho bạn thấy điều này, tôi đã xóa bảng tổng hợp ban đầu. Chọn Chèn, Bảng tổng hợp. Trước khi bấm OK, hãy chọn hộp kiểm Thêm dữ liệu này vào mô hình dữ liệu.
Xây dựng bảng tổng hợp của bạn như bình thường. Sử dụng menu thả xuống trong A3 để chọn Bộ lọc giá trị, 10 khách hàng hàng đầu và yêu cầu năm khách hàng hàng đầu. Với một ô trong bảng tổng hợp được chọn, hãy chuyển đến tab Thiết kế trong ruy-băng và mở danh sách Tổng số phụ thả xuống. Lựa chọn cuối cùng trong menu thả xuống là Bao gồm các mục đã lọc trong Tổng số. Thông thường, lựa chọn này có màu xám. Nhưng vì dữ liệu được lưu trữ trong Mô hình Dữ liệu thay vì một bộ đệm tổng hợp bình thường, tùy chọn này hiện khả dụng.
Chọn tùy chọn Bao gồm các mục đã lọc trong Tổng số và Tổng số tiền của bạn bây giờ bao gồm dấu hoa thị và tổng của tất cả dữ liệu, như được hiển thị bên dưới.
Thủ thuật tế bào ma thuật này ban đầu đến với tôi từ Dan trong cuộc hội thảo của tôi ở Philadelphia và được lặp lại 15 năm sau bởi một Dan khác với hội thảo của tôi ở Cincinnati. Cảm ơn Miguel Caballero đã đề xuất tính năng này.
