Tabele przestawne oferują filtr Top 10. To jest super. Jest elastyczny. Ale nienawidzę tego i powiem ci dlaczego.
Oto tabela przestawna przedstawiająca przychody według klientów. Całkowity dochód wynosi 6,7 miliona USD. Zauważ, że największy klient, Roto-Rooter, stanowi 9% całkowitych przychodów.
A co, jeśli mój menedżer jest skupiony jak złota rybka i chce zobaczyć tylko pięciu najlepszych klientów? Aby rozpocząć, otwórz listę rozwijaną w A3 i wybierz Filtry wartości, Top 10.
Niezwykle elastyczne okno dialogowe Filtr górnych 10 umożliwia ustawienie opcji Góra / Dół. Może zrobić 10, 5 lub dowolną inną liczbę. Możesz poprosić o pięć najlepszych pozycji, najlepsze 80% lub wystarczającą liczbę klientów, aby uzyskać 5 milionów dolarów.
Ale tutaj jest problem: wynikowy raport pokazuje pięciu klientów i sumę od tych klientów, a nie sumy wszystkich. Roto-Rooter, który wcześniej stanowił 9% całości, stanowi 23% nowej całości.
Ale najpierw kilka ważnych słów o autofiltrze
Zdaję sobie sprawę, że to pytanie nietypowe. Jeśli chcesz włączyć listy rozwijane Filtr w zwykłym zestawie danych, jak to zrobić? Oto trzy naprawdę popularne sposoby:
- Wybierz jedną komórkę w swoich danych i kliknij ikonę Filtruj na karcie Dane lub naciśnij Ctrl + Shift + L.
- Wybierz wszystkie dane za pomocą Ctrl + * i kliknij ikonę Filtruj na karcie Dane.
- Naciśnij Ctrl + T, aby sformatować dane jako tabelę.
To są trzy naprawdę dobre sposoby. Dopóki znasz którykolwiek z nich, absolutnie nie musisz znać innego sposobu. Ale oto niesamowicie niejasny, ale magiczny sposób na włączenie filtra:
- Przejdź do swojego wiersza nagłówków, a następnie przejdź do skrajnej prawej komórki nagłówka. Przejdź o jedną komórkę w prawo. Z jakiegoś nieznanego powodu, gdy jesteś w tej komórce i klikniesz ikonę Filtruj, program Excel filtruje zestaw danych po lewej stronie. Nie mam pojęcia, dlaczego to działa. Naprawdę nie warto o tym rozmawiać, ponieważ istnieją już trzy naprawdę dobre sposoby włączania list rozwijanych Filtr. Nazywam tę komórkę magiczną komórką.
A teraz wróćmy do tabel przestawnych
Istnieje reguła, która mówi, że nie możesz używać Autofiltru, gdy jesteś w tabeli przestawnej. Zobacz poniżej? Ikona filtru jest wyszarzona, ponieważ wybrałem komórkę w tabeli przestawnej.
Nie wiem, dlaczego Microsoft to szarzeje. To musi być coś wewnętrznego, co mówi, że Autofiltr i tabela przestawna nie mogą współistnieć. Tak więc w zespole programu Excel jest ktoś, kto jest odpowiedzialny za wyszarzanie ikony filtru. Ta osoba nigdy nie słyszała o magicznej komórce. Wybierz komórkę w tabeli przestawnej, a filtr zostanie wyszarzony. Kliknij poza tabelą przestawną, a filtr zostanie ponownie włączony.
Ale poczekaj. A co z magiczną komórką, o której właśnie powiedziałem? Jeśli klikniesz komórkę po prawej stronie ostatniego nagłówka, program Excel zapomni wyszarzać ikonę filtru!
Oczywiście program Excel dodaje listy rozwijane Autofiltru do górnego wiersza tabeli przestawnej. Autofiltr działa inaczej niż filtr tabeli przestawnej. Przejdź do menu rozwijanego Przychody i wybierz Filtry liczb, 10 najlepszych….
W oknie dialogowym Top 10 Autofiltr wybierz 6 najważniejszych elementów. To nie jest literówka… jeśli chcesz mieć pięciu klientów, wybierz 6. Jeśli chcesz mieć 10 klientów, wybierz 11.
W przypadku autofiltru wiersz sumy całkowitej jest największą pozycją w danych. Pięciu największych klientów zajmuje w danych pozycje od 2 do 6.
Uwaga
Najwyraźniej za pomocą tej sztuczki wyrywasz dziurę w strukturze Excela. Jeśli później zmienisz dane bazowe i odświeżysz tabelę przestawną, program Excel nie odświeży filtra, ponieważ o ile Microsoft wie, nie ma możliwości zastosowania filtru do tabeli przestawnej!
Uwaga
Naszym celem jest utrzymanie tego w tajemnicy przed Microsoft, ponieważ jest to całkiem fajna funkcja. Od jakiegoś czasu jest „zepsuty”, więc jest wielu ludzi, którzy mogą już na nim polegać.
Całkowicie legalne rozwiązanie w programie Excel 2013+
Jeśli chcesz, aby tabela przestawna pokazała pięciu największych klientów, ale sumę wszystkich klientów, musisz przenieść swoje dane poza program Excel. Jeśli masz program Excel 2013 lub nowszy działający w systemie Windows, możesz to zrobić w bardzo wygodny sposób. Aby to pokazać, usunąłem oryginalną tabelę przestawną. Wybierz Wstaw, Tabela przestawna. Przed kliknięciem OK zaznacz pole wyboru Dodaj te dane do modelu danych.
Zbuduj tabelę przestawną w normalny sposób. Użyj menu w A3, aby wybrać Filtry wartości, Top 10 i zapytaj o pięciu najlepszych klientów. Po zaznaczeniu jednej komórki w tabeli przestawnej przejdź do karty Projekt na Wstążce i otwórz listę rozwijaną Sumy częściowe. Ostatnim wyborem z listy rozwijanej jest Uwzględnij przefiltrowane elementy w sumie. Zwykle ten wybór jest wyszarzony. Ale ponieważ dane są przechowywane w modelu danych zamiast w normalnej pamięci podręcznej przestawnej, ta opcja jest teraz dostępna.
Wybierz opcję Uwzględnij przefiltrowane pozycje w sumach, a Twoja Suma całkowita zawiera teraz gwiazdkę i sumę wszystkich danych, jak pokazano poniżej.
Ta magiczna sztuczka z komórkami przyszła do mnie pierwotnie od Dana podczas mojego seminarium w Filadelfii i została powtórzona 15 lat później przez innego Dana z mojego seminarium w Cincinnati. Podziękowania dla Miguela Caballero za zasugerowanie tej funkcji.
