Filtr 10 pierwszych pozycji tabeli przestawnej podaje sumę widocznych wierszy
Tabele przestawne oferują filtr Top 10. To jest super. Jest elastyczny. Ale nienawidzę tego i powiem ci dlaczego.
Oto tabela przestawna pokazująca przychody według klientów. Całkowity dochód wynosi 6,7 miliona USD.
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.
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.
- 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, 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 wracając do tabel przestawnych…
Tak więc istnieje reguła, która mówi, że nie możesz używać Autofiltrów, gdy jesteś w tabeli przestawnej. Zobacz poniżej? Ikona filtru jest wyszarzona, ponieważ wybrałem komórkę w tabeli przestawnej.
Nigdy nie zastanawiałem się, 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 ci mówił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ż filtry tabeli przestawnej. Przejdź do menu Przychody i wybierz Filtry liczb, 10 pierwszych…
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ą, 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 Microsoftem, 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ść dane poza program Excel. Jeśli masz program Excel 2013 lub 2016, 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 z napisem 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 sumie, a Twoja suma końcowa zawiera teraz gwiazdkę i sumę wszystkich danych.
Ta sztuczka przyszła mi do głowy od Dana podczas mojego seminarium w Filadelfii. Podziękowania dla Miguela Caballero za zasugerowanie tej funkcji.
Obejrzyj wideo
- Filtr 10 pierwszych pozycji tabeli przestawnej podaje sumę widocznych wierszy
- Uwzględnij przefiltrowane elementy w sumach jest wyszarzona
- Dziwny sposób wywołania filtru danych z magicznej komórki
- Filtry danych nie są dozwolone w tabelach przestawnych
- Program Excel nie wyszarzał filtru danych z magicznej komórki
- Poproś o pierwszą szóstkę, aby otrzymać pierwszą piątkę oraz sumę całkowitą
- Przydatne do filtrowania według określonego elementu przestawnego
- Excel 2013 lub nowszy: inny sposób na uzyskanie prawdziwej sumy
- Wyślij swoje dane za pośrednictwem modelu danych
- Opcja Uwzględnij przefiltrowane elementy w sumach będzie dostępna
- Uzyskaj sumę z gwiazdką
- Nauczyłem się tej sztuczki 10+ lat temu od Dana w Filadelfii
Transkrypcja wideo
Learn Excel for Podcast, Episode 1999 - Pivot Table True Top Five
Podcastuję całą tę książkę. Jest lista odtwarzania, kliknij I w prawym górnym rogu, aby śledzić tę listę. Witamy ponownie w netcast. Jestem Bill Jelen.
W porządku, więc utworzymy tabelę przestawną i chcemy pokazać nie wszystkich klientów, ale tylko pięciu najlepszych klientów. WSTAW, tabela przestawna. OK, umieszczę klienta po lewej stronie i przychód. W porządku, więc oto cała nasza lista klientów odnotowana jako 6,7 miliona dolarów. Excel, ułatwia zrobienie pierwszej piątki. Przejdź do etykiet wierszy, filtrów wartości, u góry 10. Nie musi być na górze. Może być na górze lub na dole. Nie musi być pięć. Może być dwadzieścia, czterdzieści, cokolwiek. Najwyższe osiemdziesiąt procent, daj mi wystarczająco dużo rekordów, aby uzyskać trzy lub cztery miliony dolarów, ale zaczynamy. Pięć najlepszych pozycji. Zapamiętaj teraz 6,7 miliona dolarów, kliknij OK i mój wielki problem polega na tym, że ta suma nie jest 6,7 miliona. Kiedy przekażę to wiceprezesowi ds. Sprzedaży, on oszaleje, mówiąc: poczekaj chwilę,Wiem, że zarobiłem ponad 3,3 miliona dolarów. Tak, więc zamierzamy cofnąć, cofnąć to i wrócić do oryginalnych danych.
Teraz następnej sztuczki nauczyłem się podczas jednego z moich seminariów Power Excel w Filadelfii. Facet o imieniu Dan w drugim rzędzie pokazał mi to. Pokazał mi tę sztuczkę ponad dziesięć lat temu i najpierw musimy porozmawiać o filtrach. Tak więc normalnie, jeśli zamierzasz używać zwykłego filtru, tego filtru tutaj, wybierasz dowolną komórkę w zestawie danych i klikasz ikonę filtru lub niektóre osoby wybierają cały zestaw danych, KONTROLA * i klikają ikonę filtru, ale jest trzeci sposób. Sposób, na którym nikogo to nie obchodzi. Jeśli przejdziesz do ostatniej komórki nagłówka, w moim przypadku, będzie to Koszt w L1 i przejdź o jedną komórkę w prawo. Nazywam to magiczną komórką, nie mam pojęcia dlaczego, ale z jakiegoś nieznanego powodu mogę z tej komórki przefiltrować sąsiedni zbiór danych. W porządku, to dziwny sposób i nikogo to nie obchodzi.
Zgadza się, ponieważ istnieją dwa inne naprawdę dobre sposoby wywołania filtru, nikt nie musi wiedzieć o magicznej komórce, ale o to chodzi, zobacz wnętrze tabeli przestawnej, jest wyszarzone. Nie możesz używać tych filtrów. To niezgodne z zasadami. Teraz, jeśli tu wyjdę, jestem więcej niż mile widziany, aby użyć filtra, ale w środku oceniają. Nie wiem, kim jest osoba, która to szarzeje, ale nigdy nie słyszeli mojej krótkiej rozmowy o magicznej celi, ponieważ jeśli pójdę do ostatniej celi głównej i przejdę o jedną komórkę w prawo, spójrz na to, zapominają o wyszarzeniu filtra, a teraz właśnie dodałem stare filtry automatyczne do tabeli przestawnej. Więc przychodzę tutaj, przechodzę do filtrów liczbowych, to coś innego niż filtry wartości. Nadal nazywa się Top Ten. Nieco inaczej, poproszę o pierwszą piątkę, nie o pierwszą szóstkę.Pierwsza szóstka, ponieważ dla tego filtru Suma całkowita to tylko kolejny wiersz, a Suma całkowita to największa pozycja, a następnie, gdy zostaniesz zapytany o pozycje od 2 do 6, otrzymam pięć pierwszych pozycji.
W porządku, więc jesteśmy. Fajny hack filtrujący, który daje nam pięć najlepszych pozycji i prawdziwą liczbę wszystkich. W porządku, kilka rzeczy. Nie zapomnij o magicznej komórce. W porządku, nie ma sposobu, aby wyłączyć ten filtr, chyba że wrócisz do magicznej komórki. W porządku, więc musisz pamiętać o magicznej komórce. Ponadto, jeśli zmienisz dane źródłowe i odświeżysz tabelę przestawną, nie będą one odświeżać filtru, ponieważ zgodnie z wiedzą firmy Microsoft nie możesz mieć filtru.
Jest to przydatne do innych rzeczy. Czasami mamy produkty na szczycie. Przejdźmy tutaj w formie tabelarycznej. Nie jest to konieczne, po prostu lubię uzyskiwać prawdziwe nagłówki. Gizmo, Widget, Gadgets, Doodads. W porządku, a może jesteś menedżerem Doodads i musisz zobaczyć tylko klientów, którzy mieli określoną wartość, i Doodads. Więc idę do magicznej celi, włączam Filtr, a następnie pod Doodads mogę poprosić o przedmioty większe od zera. Kliknij OK. W porządku, ten typ filtrowania nie byłby możliwy w zwykłej tabeli przestawnej, ale jest możliwy przy użyciu magicznej komórki.
W porządku, teraz cofnijmy listę. Wyłączmy ten filtr i usuńmy tabelę przestawną, a jeśli korzystasz z programu Excel 2013 lub nowego, pokażę Ci całkowicie legalny sposób uzyskania prawidłowej sumy na dole. Wstaw tabelę przestawną, tutaj na dole, zaczynając od programu Excel 2013, to bardzo nieszkodliwe pudełko, nie brzmi zbyt ekscytująco, dodaj te dane do modelu danych. To przesyła dane za kulisami do silnika Power Pivot. Zbuduj dokładnie ten sam raport. Klienci po lewej stronie. Przychody w sercu tabeli przestawnej. Następnie przejdź do zwykłych filtrów, najlepszych filtrów wartości 10. Zapytaj o pięć najlepszych. Zauważ, że po tym, jak to zrobię, mamy 6,7 miliona dolarów, 3,3 miliona dolarów, ale tutaj jest różnica. Kiedy przechodzę do karty Projekt, w sekcji Sumy częściowe, ta funkcja o nazwie Uwzględnij filtrowane elementy w podsumach,nie jest już wyszarzona. W zwykłej tabeli przestawnej nie jest dostępna. Dostajemy tam małą gwiazdkę i jest to suma wszystkiego. W porządku, teraz oczywiście działa tylko w programie Excel 2013 lub nowszym.
W porządku, zajmie mi sześć tygodni, zanim cała książka pojawi się na YouTube. Jest tu tak wiele dobrych wskazówek. Wskazówki, które mogą od razu zacząć oszczędzać czas. Kup całą książkę już teraz, a będziesz mieć dostęp do wszystkich 40. To właściwie dużo ponad 40 wskazówek. Klawisze skrótów programu Excel. Wiele wspaniałych rzeczy w tej książce.
W porządku, podsumuj. Więc kiedy robimy filtr 10 pierwszych w tabeli przestawnej, daje nam sumę, ale tylko widoczne wiersze, a nie elementy, które zostały odfiltrowane. Tak, jeśli przejdziemy do drugiej karty i poszukamy sum częściowych, elementów filtrowanych i sum, jest ona wyszarzona, ale istnieje dziwny sposób wywołania starego filtru danych z magicznej komórki. Ostatnia komórka nagłówka, przejdź o jedną komórkę w prawo, nie możesz użyć filtrów i tabel przestawnych, ale jeśli przejdziesz do magicznej komórki, zapomnisz o jej wyszarzeniu. Teraz w filtrze liczb pytasz o pierwszą szóstkę, aby uzyskać pierwszą piątkę i sumę końcową. Przydatne również do filtrowania według konkretnego elementu bazowego: Doodads, wszystkiego, co miało więcej niż 0 w Doodads lub 5 najlepszych Doodads. Excel 2013 lub nowszy, istnieje inny sposób uzyskania rzeczywistej sumy.Zaznacz to pole dla modelu danych, a następnie uwzględnij przefiltrowane elementy w sumie będą dostępne. Wynik otrzymujesz z gwiazdką. I dziękuję Danowi z Filadelfii, który pokazał mnie na jednym z moich seminariów Power Excel ponad dziesięć lat temu i dał mi tę wielką sztuczkę. Sposób na przedostanie się filtra przez ścianę tabeli przestawnej klubu. Zwykle nie zezwalają na ten automatyczny filtr.
Hej, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem w kolejnym netcastie z MRExcel.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast1999.xlsx
