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 anzeigt. Der Gesamtumsatz beträgt 6,7 Millionen US-Dollar. Beachten Sie, dass der größte Kunde, Roto-Rooter, 9% des Gesamtumsatzes ausmacht.
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. Roto-Rooter, der zuvor 9% der Gesamtzahl ausmachte, macht 23% der neuen Summe aus.
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 das Symbol Filter auf der Registerkarte Daten oder drücken Sie Strg + Umschalt + L.
- 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 dann 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 eine Regel, die besagt, dass Sie 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 weiß nicht, warum Microsoft dies ausgraut. 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 magischen Zelle 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 AutoFilter funktioniert anders als ein 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 mit den fünf wichtigsten Kunden, aber der Summe aller Kunden, wünschen, müssen Sie Ihre Daten außerhalb von Excel verschieben. Wenn Sie Excel 2013 oder höher unter Windows ausführen, 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. Aktivieren Sie vor dem Klicken auf OK 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. Wenn eine Zelle in der Pivot-Tabelle ausgewählt ist, wechseln Sie in der Multifunktionsleiste zur Registerkarte Entwurf 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 Summen einbeziehen. Ihre Gesamtsumme enthält jetzt ein Sternchen und die Summe aller Daten, wie unten gezeigt.
Dieser Zaubertrick kam ursprünglich von Dan in meinem Seminar in Philadelphia und wurde 15 Jahre später von einem anderen Dan als meinem Seminar in Cincinnati wiederholt. Vielen Dank an Miguel Caballero für den Vorschlag dieser Funktion.
