Der Top 10-Filter der Pivot-Tabelle gibt insgesamt die sichtbaren Zeilen an
Pivot-Tabellen bieten einen Top-10-Filter. Es ist cool. Es ist flexibel. Aber ich hasse es und ich werde dir sagen warum.
Hier ist eine Pivot-Tabelle, die den Umsatz nach Kunden zeigt. Der Gesamtumsatz beträgt 6,7 Millionen US-Dollar.
Was ist, wenn mein Manager die Aufmerksamkeitsspanne eines Goldfisches hat und nur die fünf besten Kunden sehen möchte?
Öffnen Sie zunächst die Dropdown-Liste in A3 und wählen Sie Wertfilter, Top 10.
Der superflexible Dialog Top 10 Filter ermöglicht Top / Bottom. Es kann 10, 5 oder eine beliebige andere Zahl ausführen. Sie können nach den fünf besten Artikeln, den besten 80% oder nach genügend Kunden fragen, um 5 Millionen US-Dollar zu erreichen.
Aber hier ist das Problem: Der resultierende Bericht zeigt fünf Kunden und die Summe von diesen Kunden anstelle der Summen von allen.
Aber zuerst ein paar wichtige Worte zu AutoFilter
Mir ist klar, dass dies eine Frage von der Wand zu sein scheint. Wie gehen Sie vor, wenn Sie die Filter-Dropdowns für einen regulären Datensatz aktivieren möchten? Hier sind drei wirklich gebräuchliche Wege:
- Wählen Sie eine Zelle in Ihren Daten aus und klicken Sie auf der Registerkarte Daten auf das Symbol Filter.
- Wählen Sie alle Ihre Daten mit Strg + * aus und klicken Sie auf der Registerkarte Daten auf das Symbol Filter.
- Drücken Sie Strg + T, um die Daten als Tabelle zu formatieren.
Das sind drei wirklich gute Wege. Solange Sie einen von ihnen kennen, müssen Sie absolut keinen anderen Weg kennen. Aber hier ist eine unglaublich dunkle, aber magische Art, den Filter einzuschalten:
- Gehen Sie zu Ihrer Kopfzeile und zur Zelle ganz rechts. Bewegen Sie eine Zelle nach rechts. Wenn Sie sich in dieser Zelle befinden und auf das Symbol Filter klicken, filtert Excel aus einem unbekannten Grund den Datensatz links von Ihnen. Ich habe keine Ahnung, warum das funktioniert. Es lohnt sich wirklich nicht darüber zu sprechen, da es bereits drei wirklich gute Möglichkeiten gibt, die Filter-Dropdowns zu aktivieren. Ich nenne diese Zelle die magische Zelle.
Und jetzt zurück zu den Pivot-Tabellen…
Es gibt also eine Regel, die besagt, dass Sie die AutoFilter nicht verwenden können, wenn Sie sich in einer Pivot-Tabelle befinden. Siehe unten? Das Filtersymbol ist ausgegraut, da ich eine Zelle in der Pivot-Tabelle ausgewählt habe.
Ich habe nie wirklich darüber nachgedacht, warum Microsoft dies ausblendet. Es muss etwas Internes sein, das besagt, dass AutoFilter und eine Pivot-Tabelle nicht koexistieren können. Es gibt also jemanden im Excel-Team, der für das Ausgrauen des Filtersymbols verantwortlich ist. Diese Person hat noch nie von der Zauberzelle gehört. Wählen Sie eine Zelle in der Pivot-Tabelle aus, und der Filter wird ausgegraut. Klicken Sie außerhalb der Pivot-Tabelle, und Filter wird wieder aktiviert.
Aber warte. Was ist mit der magischen Zelle, von der ich dir gerade erzählt habe? Wenn Sie in die Zelle rechts neben der letzten Überschrift klicken, vergisst Excel, das Filtersymbol auszublenden!
Natürlich fügt Excel der oberen Zeile Ihrer Pivot-Tabelle AutoFilter-Dropdowns hinzu. Und der AutoFilter arbeitet anders als Pivot-Tabellenfilter. Gehen Sie zur Dropdown-Liste Einnahmen und wählen Sie Zahlenfilter, Top 10…
Wählen Sie im Dialogfeld "Top 10 AutoFilter" die Option "Top 6 Elemente". Das ist kein Tippfehler…. Wenn Sie fünf Kunden möchten, wählen Sie 6. Wenn Sie 10 Kunden möchten, wählen Sie 11.
Für AutoFilter ist die Gesamtsummenzeile das größte Element in den Daten. Die fünf größten Kunden belegen die Positionen 2 bis 6 in den Daten.
Vorsicht
Mit diesem Trick reißen Sie eindeutig ein Loch in die Struktur von Excel. Wenn Sie später die zugrunde liegenden Daten ändern und Ihre Pivot-Tabelle aktualisieren, aktualisiert Excel den Filter nicht, da es nach Microsoft keine Möglichkeit gibt, einen Filter auf eine Pivot-Tabelle anzuwenden!
Hinweis
Unser Ziel ist es, dies vor Microsoft geheim zu halten, da es eine ziemlich coole Funktion ist. Es ist seit geraumer Zeit „kaputt“, daher gibt es viele Leute, die sich inzwischen vielleicht darauf verlassen.
Eine vollständig legale Lösung in Excel 2013+Wenn Sie eine Pivot-Tabelle wünschen, in der die fünf wichtigsten Kunden, aber die Summe aller Kunden angezeigt werden, müssen Sie Ihre Daten außerhalb von Excel verschieben. Wenn Sie Excel 2013 oder 2016 haben, gibt es eine sehr bequeme Möglichkeit, dies zu tun. Um Ihnen dies zu zeigen, habe ich die ursprüngliche Pivot-Tabelle gelöscht. Wählen Sie Einfügen, Pivot-Tabelle. Bevor Sie auf OK klicken, aktivieren Sie das Kontrollkästchen Diese Daten zum Datenmodell hinzufügen.
Erstellen Sie Ihre Pivot-Tabelle wie gewohnt. Verwenden Sie die Dropdown-Liste in A3, um Wertefilter, Top 10 auszuwählen und nach den fünf besten Kunden zu fragen. Gehen Sie mit einer ausgewählten Zelle in der Pivot-Tabelle zur Registerkarte Design in der Multifunktionsleiste und öffnen Sie die Dropdown-Liste Zwischensummen. Die letzte Auswahl in der Dropdown-Liste lautet "Gefilterte Elemente in Summen einbeziehen". Normalerweise ist diese Auswahl ausgegraut. Da die Daten jedoch im Datenmodell anstelle eines normalen Pivot-Cache gespeichert sind, ist diese Option jetzt verfügbar.
Wählen Sie die Option Gefilterte Elemente in Gesamtsumme einbeziehen. Ihre Gesamtsumme enthält jetzt ein Sternchen und die Gesamtsumme aller Daten.
Dieser Trick kam ursprünglich von Dan in meinem Seminar in Philadelphia. Vielen Dank an Miguel Caballero für den Vorschlag dieser Funktion.
Schau Video
- Der Top 10-Filter der Pivot-Tabelle gibt insgesamt die sichtbaren Zeilen an
- Gefilterte Elemente in Summen aufnehmen ist ausgegraut
- Seltsame Möglichkeit, den Datenfilter aus der magischen Zelle aufzurufen
- Datenfilter sind in Pivot-Tabellen nicht zulässig
- Excel kann den Datenfilter in der magischen Zelle nicht ausgrauen
- Fragen Sie nach den Top 6, um die Top 5 plus Gesamtsumme zu erhalten
- Nützlich zum Filtern nach einem bestimmten Pivot-Element
- Excel 2013 oder neuer: Anderer Weg, um die wahre Summe zu erhalten
- Senden Sie Ihre Daten über das Datenmodell
- Gefilterte Elemente in Summen einbeziehen ist verfügbar
- Holen Sie sich Total mit Sternchen
- Ich habe diesen Trick vor mehr als 10 Jahren von Dan in Philadelphia gelernt
Video-Transkript
Lernen Sie Excel für Podcast, Episode 1999 - Pivot Table True Top Five
Ich podcaste das ganze Buch. Es gibt eine Wiedergabeliste. Klicken Sie oben rechts auf das I, um dieser Wiedergabeliste zu folgen. Willkommen zurück im Netcast. Ich bin Bill Jelen.
Also gut, wir werden eine Pivot-Tabelle erstellen und nicht alle Kunden, sondern nur die fünf besten Kunden zeigen. INSERT, Pivot-Tabelle. Okay, ich lege den Kunden auf die linke Seite und den Umsatz. Okay, hier ist unsere gesamte Kundenliste mit 6,7 Millionen Dollar. Excel macht es einfach, die Top 5 zu erreichen. Gehen Sie zu Zeilenbeschriftungen, Wertfilter, Top 10. Muss nicht Top sein. Es kann oben oder unten sein. Muss nicht fünf sein. Es kann zwanzig, vierzig sein, es kann was auch immer sein. Top achtzig Prozent, gib mir genug Rekorde, um drei Millionen Dollar oder vier Millionen Dollar zu erreichen, aber los geht's. Top fünf Artikel. Denken Sie jetzt an 6,7 Millionen Dollar, klicken Sie auf OK und mein großes Problem hier ist, dass diese Gesamtsumme nicht die 6,7 Millionen ist. Wenn ich dies dem Verkaufsleiter gebe, wird er ausflippen und sagen: Warte eine Sekunde,Ich weiß, dass ich mehr als 3,3 Millionen Dollar gemacht habe. Richtig, also werden wir das rückgängig machen, rückgängig machen und zu den ursprünglichen Daten zurückkehren.
Diesen nächsten Trick habe ich während eines meiner Power Excel-Seminare in Philadelphia gelernt. Ein Typ namens Dan in der zweiten Reihe hat mir das gezeigt. Vor mehr als zehn Jahren hat er mir diesen Trick gezeigt, und zuerst müssen wir über die Filter sprechen. Wenn Sie also den regulären Filter, diesen Filter hier, verwenden, wählen Sie normalerweise eine beliebige Zelle in Ihrem Datensatz aus und klicken auf das Filtersymbol, oder einige Benutzer wählen den gesamten Datensatz, CONTROL * und klicken auf das Filtersymbol. aber es gibt einen dritten Weg. Ein Weg, den niemand interessiert. Wenn Sie in meinem Fall zur allerletzten Überschriftenzelle gehen, sind das die Kosten in L1, und gehen Sie eine Zelle nach rechts. Ich nenne dies die magische Zelle. Ich habe keine Ahnung, warum, aber aus einem unbekannten Grund kann ich aus dieser Zelle den benachbarten Datensatz filtern. Okay, es ist wie ein seltsamer Weg und niemand kümmert sich darum.
Richtig, da es zwei andere wirklich gute Möglichkeiten gibt, einen Filter aufzurufen, muss niemand etwas über die magische Zelle wissen, aber hier ist die Sache, siehe in einer Pivot-Tabelle, sie ist ausgegraut. Sie dürfen diese Filter nicht verwenden. Es ist gegen die Regeln. Wenn ich hier rauskomme, kann ich den Filter gerne verwenden, aber innen werden sie bewertet. Ich weiß nicht, wer die Person ist, die das ausgraut, aber sie haben noch nie mein kleines Gerede über die magische Zelle gehört, denn wenn ich zur allerletzten Überschriftenzelle gehe und eine Zelle nach rechts gehe, schau dir das an. Sie vergessen, den Filter auszublenden, und jetzt habe ich gerade die alten automatischen Filter zur Pivot-Tabelle hinzugefügt. Also komme ich hierher, gehe zu Zahlenfiltern, das ist etwas anderes als Wertfilter. Es heißt immer noch Top Ten. Etwas anders werde ich nach den Top 5 fragen, nicht nach den Top 6.Die Top 6, da für diesen Filter die Gesamtsumme nur eine weitere Zeile ist und die Gesamtsumme der größte Artikel ist. Wenn ich dann nach den Artikeln 2 bis 6 gefragt werde, erhalte ich die Top 5.
Okay, also da sind wir. Ein cooler Filter-Hack, der uns die fünf besten Items und die wahre Summe aller gibt. Okay, jetzt ein paar Dinge. Vergiss die magische Zelle nicht. Okay, es gibt keine Möglichkeit, diesen Filter auszuschalten, es sei denn, Sie kehren zur magischen Zelle zurück. Also gut, du musst dich an die magische Zelle erinnern. Wenn Sie die zugrunde liegenden Daten ändern und die Pivot-Tabelle aktualisieren, wird der Filter nicht aktualisiert, da Sie nach Microsoft keinen Filter haben dürfen.
Dies ist nützlich für andere Dinge. Manchmal haben wir Produkte, die über die Spitze gehen. Lassen Sie uns hier in tabellarischer Form gehen. Nicht notwendig, ich möchte nur echte Überschriften bekommen. Gizmo, Widget, Gadgets, Doodads. Okay und vielleicht sind Sie der Manager von Doodads und Sie müssen nur die Kunden sehen, die einen bestimmten Wert hatten, und Doodads. Also gehe ich in die magische Zelle, schalte den Filter ein und kann dann unter Doodads nach Gegenständen fragen, die größer als Null sind. OK klicken. In Ordnung, diese Art der Filterung wäre in einer normalen Pivot-Tabelle nicht möglich, aber es ist möglich, die magische Zelle zu verwenden.
Okay, jetzt machen wir die Liste rückgängig. Lassen Sie uns diesen Filter deaktivieren und die Pivot-Tabelle entfernen. Wenn Sie in Excel 2013 oder neu sind, zeige ich Ihnen einen völlig legalen Weg, um unten die richtige Summe zu erhalten. Fügen Sie die Pivot-Tabelle hier unten ein. Ab Excel 2013 klingt diese sehr harmlose Box nicht sehr aufregend. Fügen Sie diese Daten zum Datenmodell hinzu. Dadurch werden die Daten hinter den Kulissen an die Power Pivot Engine gesendet. Erstellen Sie genau den gleichen Bericht. Kunden auf der linken Seite. Einnahmen im Herzen der Pivot-Tabelle. Gehen Sie dann zu den regulären Filtern, den Wertfiltern Top 10. Fragen Sie nach den Top 5. Beachten Sie noch einmal, dass wir 6,7 Millionen Dollar haben, nachdem ich das getan habe, 3,3 Millionen Dollar, aber hier ist der Unterschied. Wenn ich unter Zwischensummen zur Registerkarte "Design" gehe, wird diese Funktion mit dem Namen "Gefilterte Elemente in Summen einschließen" bezeichnet.ist nicht mehr ausgegraut. Bei einem regulären Pivot ist Table nicht verfügbar. Wir bekommen dort ein kleines Sternchen und es ist die Summe von allem. Okay, jetzt funktioniert das natürlich nur in Excel 2013 oder neuer.
Okay, es wird sechs Wochen dauern, bis ich das ganze Buch hier auf YouTube veröffentlicht habe. Hier gibt es so viele gute Tipps. Tipps, mit denen Sie sofort Zeit sparen können. Kaufen Sie jetzt das gesamte Buch und Sie haben Zugriff auf alle 40, es sind tatsächlich viel mehr als 40 Tipps. Excel-Tastenkombinationen. Alle Arten von großartigen Sachen in diesem Buch.
Okay, rekapitulieren Sie. Wenn wir also einen Pivot Table Top 10-Filter erstellen, erhalten wir die Summe, aber nur die sichtbaren Zeilen, nicht das Material, das herausgefiltert wurde. Ja, wenn wir zur zweiten Registerkarte gehen und nach Zwischensummen, gefilterten Elementen und Summen suchen, ist diese ausgegraut, aber es gibt eine seltsame Möglichkeit, den alten Datenfilter aus der magischen Zelle aufzurufen. In der allerletzten Überschriftenzelle gehen Sie eine Zelle nach rechts. Sie können keine Filter und Pivot-Tabellen verwenden, aber wenn Sie in die magische Zelle gehen, vergessen sie, sie auszublenden. Jetzt fragen Sie im Zahlenfilter nach den Top Sechs, um die Top Fünf zuzüglich der Gesamtsumme zu erhalten. Auch nützlich zum Filtern nach einem bestimmten Pivot-Element: Doodads, alles, was in Doodads oder Top-5-Doodads größer als 0 war. In Excel 2013 oder neuer gibt es eine andere Möglichkeit, die True Total zu erhalten.Aktivieren Sie dieses Kontrollkästchen für das Datenmodell und schließen Sie dann gefilterte Elemente in Summen ein. Sie erhalten die Summe mit einem Sternchen. Und danke an Dan in Philadelphia, der mich vor mehr als zehn Jahren bei einem meiner Power Excel-Seminare gezeigt und mir diesen tollen kleinen Trick gegeben hat. Eine Möglichkeit für den Filter, sich durch die Club Pivot-Tischwand zu schleichen. Normalerweise erlauben sie diesen automatischen Filter nicht.
Hey, ich möchte dir dafür danken, dass du vorbeigekommen bist. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von MRExcel.
Download-Datei
Laden Sie die Beispieldatei hier herunter: Podcast1999.xlsx
