Bảng tổng hợp Bộ lọc Top 10 cung cấp tổng số các hàng hiển thị
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.
Đ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.
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.
- 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, 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 Phép thuật.
Và bây giờ, Quay lại Pivot Table…
Vì vậy, có một quy tắc nói rằng bạn không thể sử dụng Bộ lọc tự động 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 chưa bao giờ thực sự xem xét tại sao Microsoft lại giải quyết vấn đề này. Nó phải là một cái gì đó nội bộ cho biết AutoFilter và Pivot Table 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ề Magic cell. 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 Phép thuật mà tôi vừa nói với bạn? 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à Bộ lọc tự động hoạt động khác với bộ lọc bảng tổng hợp. Chuyển đến menu thả xuống Doanh thu và chọn Bộ lọc số, 10…
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ó năm 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, bởi vì nó 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 bên ngoài Excel. Nếu bạn có Excel 2013 hoặc 2016, 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 có nội dung 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ở menu 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 số của tất cả dữ liệu.
Bí quyết này ban đầu đến với tôi từ Dan trong cuộc hội thảo của tôi ở Philadelphia. Cảm ơn Miguel Caballero đã đề xuất tính năng này.
Xem video
- Bảng tổng hợp Bộ lọc Top 10 cung cấp tổng số các hàng hiển thị
- Bao gồm các mục đã lọc trong Tổng số là màu xám
- Cách kỳ lạ để gọi Bộ lọc dữ liệu từ ô ma thuật
- Bộ lọc dữ liệu không được phép trong bảng tổng hợp
- Excel không thể làm xám Bộ lọc Dữ liệu khỏi ô ma thuật
- Yêu cầu top 6 để nhận được top 5 cộng với Grand Total
- Hữu ích để lọc theo một mục tổng hợp cụ thể
- Excel 2013 hoặc mới hơn: Cách khác để nhận Tổng số đúng
- Gửi dữ liệu của bạn thông qua Mô hình dữ liệu
- Bao gồm các mục đã lọc trong Tổng số sẽ có sẵn
- Nhận Tổng cộng với dấu hoa thị
- Tôi đã học mẹo này hơn 10 năm trước từ Dan ở Philadelphia
Bản ghi video
Học Excel cho Podcast, Tập 1999 - Pivot Table True Top Five
Tôi đang podcasting toàn bộ cuốn sách này. Có một danh sách phát, hãy nhấp vào Tôi ở góc trên cùng bên phải để theo dõi danh sách phát đó. Chào mừng bạn trở lại netcast. Tôi là Bill Jelen.
Được rồi, vì vậy chúng tôi sẽ tạo Bảng tổng hợp và chúng tôi muốn hiển thị, không phải tất cả khách hàng, mà chỉ năm khách hàng hàng đầu. INSERT, Pivot Table. Được rồi, tôi sẽ đặt Khách hàng xuống phía bên trái và Doanh thu. Được rồi, đây là toàn bộ danh sách khách hàng của chúng tôi được ghi nhận là 6,7 triệu đô la. Excel, giúp bạn dễ dàng thực hiện năm đầu. Đi vào Nhãn hàng, Bộ lọc giá trị, top 10. Không nhất thiết phải đứng đầu. Nó có thể là trên cùng hoặc dưới cùng. Không cần phải là năm. Nó có thể là hai mươi, bốn mươi, nó có thể là bất cứ điều gì. Tám mươi phần trăm hàng đầu, hãy cung cấp cho tôi đủ hồ sơ để đạt được ba triệu đô la hoặc bốn triệu đô la, nhưng chúng ta bắt đầu. Năm mục hàng đầu. Bây giờ hãy nhớ đến 6,7 triệu đô la, hãy nhấp vào OK và vấn đề lớn của tôi ở đây, đó là, tổng số tiền đó không phải là 6,7 triệu. Khi tôi đưa cái này cho Phó Giám đốc bán hàng, anh ấy sẽ phát hoảng, nói rằng, chờ một chút,Tôi biết tôi đã làm hơn 3,3 triệu đô la. Đúng vậy, chúng tôi sẽ hoàn tác, hoàn tác điều đó và quay lại dữ liệu ban đầu.
Bây giờ, thủ thuật tiếp theo này tôi đã học được trong một trong những Hội thảo Power Excel của tôi ở Philadelphia. Một anh chàng tên Dan ở hàng hai, cho tôi xem cái này. Cách đây hơn mười năm, anh ấy đã chỉ cho tôi thủ thuật này, và đầu tiên chúng ta phải nói về Bộ lọc. Vì vậy, thông thường, nếu bạn định sử dụng Bộ lọc thông thường, Bộ lọc này tại đây, bạn chọn bất kỳ ô nào trong tập dữ liệu của mình và nhấp vào Biểu tượng Bộ lọc hoặc một số người chọn toàn bộ Tập dữ liệu, CONTROL * và nhấp vào Biểu tượng Bộ lọc, nhưng có một cách thứ ba. Một cách mà không ai quan tâm đến. Nếu bạn đi đến Ô tiêu đề cuối cùng, trong trường hợp của tôi, đó là Chi phí ở L1 và chuyển sang một ô bên phải. Tôi gọi đây là ô ma thuật, tôi không biết tại sao nhưng không hiểu vì lý do gì, từ ô này, tôi có thể lọc Tập dữ liệu liền kề. Được rồi, nó giống như một cách kỳ lạ và không ai quan tâm đến điều này.
Đúng vậy, bởi vì có hai cách thực sự tốt khác để gọi Bộ lọc, không ai cần biết về ô ma thuật, nhưng đây là vấn đề, hãy xem bên trong Bảng xoay vòng, nó chuyển sang màu xám. Bạn không được phép sử dụng các Bộ lọc đó. Nó trái với các quy tắc. Bây giờ, nếu tôi bước ra đây, tôi rất hoan nghênh việc sử dụng Bộ lọc nhưng bên trong chúng bị loại. Tôi không biết ai là người phát ra thứ này, nhưng họ chưa bao giờ nghe tôi nói về tế bào ma thuật, bởi vì nếu tôi đi đến Ô tiêu đề cuối cùng và đi sang phải một ô, hãy nhìn vào đó, họ quên bôi xám Bộ lọc và bây giờ tôi vừa thêm Bộ lọc Tự động cũ vào Bảng Pivot. Vì vậy, tôi đến đây, đi tới Bộ lọc số, nó khác với Bộ lọc giá trị. Nó vẫn được gọi là Top Ten. Hơi khác một chút, tôi sẽ yêu cầu năm người đứng đầu, không có sáu người đứng đầu.Sáu hàng đầu vì đối với Bộ lọc này, Tổng Tổng chỉ là một hàng khác, và Tổng Tổng là mục lớn nhất và sau đó khi được yêu cầu cho các mục từ 2 đến 6, tôi nhận được năm mục hàng đầu.
Được rồi, vậy là xong. Một bản hack Bộ lọc thú vị, cung cấp cho chúng tôi năm mục hàng đầu và tổng số thực sự của tất cả mọi người. Được rồi, một vài điều. Đừng quên về tế bào ma thuật. Được rồi, không có cách nào để tắt Bộ lọc này, trừ khi bạn quay lại phòng giam ma thuật. Được rồi, vì vậy bạn cần nhớ ô ma thuật. Ngoài ra, nếu bạn thay đổi dữ liệu cơ bản và bạn làm mới Bảng Pivot, chúng sẽ không làm mới Bộ lọc vì theo như Microsoft biết, bạn không được phép có Bộ lọc.
Điều này hữu ích cho những thứ khác. Đôi khi chúng tôi có sản phẩm vượt qua hàng đầu. Hãy vào đây ở dạng bảng. Không cần thiết, tôi chỉ muốn lấy các tiêu đề thực sự. Gizmo, Widget, Gadgets, Doodad. Được rồi và có thể bạn là người quản lý Doodads và bạn chỉ cần xem những khách hàng có giá trị cụ thể và Doodads. Vì vậy, tôi đi đến ô ma thuật, bật Bộ lọc và sau đó trong Doodads, tôi có thể yêu cầu các mục lớn hơn 0. Bấm OK. Được rồi, loại Lọc đó sẽ không thể thực hiện được trên Bảng Pivot thông thường, nhưng có thể sử dụng ô ma thuật.
Được rồi, bây giờ chúng ta hãy hoàn tác danh sách. Hãy tắt Bộ lọc này và loại bỏ Bảng tổng hợp và nếu bạn đang sử dụng Excel 2013 hoặc mới, tôi sẽ chỉ cho bạn một cách hoàn toàn hợp pháp để có được tổng số chính xác ở phía dưới. Chèn Pivot Table, xuống đây ở phía dưới, bắt đầu trong Excel 2013, hộp này rất vô hại, nghe có vẻ không thú vị lắm, hãy thêm dữ liệu này vào Mô hình Dữ liệu. Điều đó sẽ gửi dữ liệu, đằng sau hậu trường, tới Power Pivot Engine. Xây dựng cùng một báo cáo. Khách hàng xuống phía bên tay trái. Doanh thu ở trung tâm của Bảng tổng hợp. Sau đó, chuyển đến Bộ lọc thông thường, Bộ lọc giá trị hàng đầu 10. Yêu cầu 5 bộ lọc hàng đầu. Lưu ý một lần nữa, chúng tôi có 6,7 triệu đô la sau khi tôi làm điều này, 3,3 triệu đô la nhưng đây là sự khác biệt. Khi tôi chuyển đến Tab thiết kế, trong Tổng số phụ, tính năng này được gọi là Bao gồm các mục đã lọc trong Tổng số,không còn bị xám. Tại một Pivot Table thông thường không có sẵn. Chúng tôi nhận được một dấu hoa thị nhỏ ở đó và đó là tổng thể của mọi thứ. Được rồi, tất nhiên bây giờ chỉ hoạt động trong Excel 2013 hoặc mới hơn.
Được rồi, tôi sẽ mất sáu tuần để đưa toàn bộ cuốn sách này lên YouTube. Có rất nhiều mẹo hay ở đây. Những mẹo có thể bắt đầu giúp bạn tiết kiệm thời gian ngay lập tức. Mua toàn bộ cuốn sách ngay bây giờ và bạn sẽ có quyền truy cập vào tất cả 40, Đó thực sự là hơn 40 mẹo. Các phím tắt trong Excel. Tất cả các loại công cụ tuyệt vời trong cuốn sách này.
Được rồi, tóm tắt lại. Vì vậy, khi chúng tôi thực hiện Bộ lọc 10 đầu Pivot Table, nó cung cấp cho chúng tôi tổng số nhưng chỉ những hàng hiển thị, không phải những thứ mà nó đã lọc ra. Đúng vậy nếu chúng ta chuyển đến tab thứ hai và tìm Tổng phụ, Mục đã lọc và Tổng, nó sẽ chuyển sang màu xám, nhưng có một cách kỳ lạ để gọi Bộ lọc Dữ liệu Cũ từ ô ma thuật. Ô tiêu đề cuối cùng, chuyển sang một ô bên phải, bạn không thể sử dụng Bộ lọc và Bảng tổng hợp, nhưng nếu bạn đi đến ô ma thuật, họ quên tô xám. Bây giờ trong Bộ lọc số, bạn yêu cầu sáu đầu để lấy năm đầu, cộng với tổng tổng. Cũng hữu ích để lọc đến một Mục Pivot cụ thể: Doodads, bất kỳ thứ gì có lớn hơn 0 trong Doodads hoặc 5 Doodads hàng đầu. Excel 2013 hoặc mới hơn, có một cách khác để lấy Tổng số Đúng.Chọn hộp đó cho Mô hình Dữ liệu và sau đó bao gồm các Mục được Lọc trong Tổng số sẽ có sẵn. Bạn nhận được tổng số bằng dấu hoa thị. Và cảm ơn Dan ở Philadelphia, người đã chỉ cho tôi tại một trong những Hội thảo Power Excel của tôi, hơn mười năm trước, và cho tôi mẹo nhỏ tuyệt vời này. Một cách để Bộ lọc lẻn qua Tường của Club Pivot Table. Họ thường không cho phép Bộ lọc Tự động đó.
Này, tôi muốn cảm ơn bạn đã ghé qua. Chúng tôi sẽ gặp lại bạn vào lần sau, cho một netcast khác từ MRExcel.
Tải tập tin
Tải xuống tệp mẫu tại đây: Podcast1999.xlsx
