Inhalte
Pivot-Tabellen sind gut für alle – sie rechnen schnell, sind flexibel konfigurierbar und lassen sich bei Bedarf elegant in das Design einwickeln. Aber es gibt auch ein paar Wermutstropfen, insbesondere die fehlende Möglichkeit, eine Zusammenfassung zu erstellen, bei der der Wertebereich keine Zahlen, sondern Text enthalten soll.
Lassen Sie uns versuchen, diese Einschränkung zu umgehen und uns in einer ähnlichen Situation „ein paar Krücken“ einfallen lassen.
Angenommen, unser Unternehmen transportiert seine Produkte in Containern in mehrere Städte in unserem Land und in Kasachstan. Container werden höchstens einmal im Monat verschickt. Jeder Container hat eine alphanumerische Nummer. Als Anfangsdaten gibt es eine Standardtabelle mit Lieferungen, aus der Sie eine Art Zusammenfassung erstellen müssen, um die Anzahl der an jede Stadt und jeden Monat gesendeten Container klar zu sehen:
Lassen Sie uns der Einfachheit halber die Tabelle mit den Anfangsdaten mit dem Befehl im Voraus „intelligent“ machen Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren) und gib ihr einen Namen Die Lieferungen Tab Bauherr (Design). In Zukunft wird dies das Leben vereinfachen, denn. Es ist möglich, den Namen der Tabelle und ihrer Spalten direkt in den Formeln zu verwenden.
Methode 1. Die einfachste – verwenden Sie Power Query
Power Query ist ein superstarkes Tool zum Laden und Transformieren von Daten in Excel. Dieses Add-In ist seit 2016 standardmäßig in Excel integriert. Wenn Sie Excel 2010 oder 2013 haben, können Sie es separat herunterladen und installieren (völlig kostenlos).
Den gesamten Prozess habe ich zur Verdeutlichung Schritt für Schritt im folgenden Video analysiert:
Wenn es nicht möglich ist, Power Query zu verwenden, dann können Sie andere Wege gehen – über eine Pivot-Tabelle oder Formeln.
Methode 2. Hilfszusammenfassung
Fügen wir unserer ursprünglichen Tabelle eine weitere Spalte hinzu, in der wir mit einer einfachen Formel die Nummer jeder Zeile in der Tabelle berechnen:
Offensichtlich wird -1 benötigt, da wir in unserer Tabelle einen einzeiligen Header haben. Wenn sich Ihre Tabelle nicht am Anfang des Blattes befindet, können Sie eine etwas komplexere, aber universellere Formel verwenden, die die Differenz zwischen den Zahlen der aktuellen Zeile und der Tabellenüberschrift berechnet:
Jetzt erstellen wir standardmäßig eine Pivot-Tabelle des gewünschten Typs basierend auf unseren Daten, aber im Wertfeld lassen wir das Feld weg Zeilennummer statt was wir wollen Container:
Da wir nicht mehrere Container in derselben Stadt im selben Monat haben, wird unsere Zusammenfassung tatsächlich nicht die Menge, sondern die Zeilennummern der Container angeben, die wir benötigen.
Außerdem können Sie Gesamt- und Zwischensummen auf der Registerkarte deaktivieren Konstruktor – Allgemeine Summen и Zwischensummen (Design – Gesamtsummen, Zwischensummen) und schalten Sie an gleicher Stelle mit der Schaltfläche die Zusammenfassung auf ein komfortableres Tabellenlayout um Modell melden (Berichtslayout).
Damit sind wir schon auf halbem Weg zum Ergebnis: Wir haben eine Tabelle, in der am Schnittpunkt von Stadt und Monat in der Quelltabelle eine Zeilennummer steht, in der der Containercode steht, den wir brauchen.
Kopieren wir nun die Zusammenfassung (auf dasselbe oder ein anderes Blatt) und fügen sie als Werte ein und geben dann unsere Formel in den Wertebereich ein, wodurch der Containercode anhand der in der Zusammenfassung gefundenen Zeilennummer extrahiert wird:
Funktion IF (WENN), prüft in diesem Fall, ob die nächste Zelle in der Zusammenfassung nicht leer ist. Wenn leer, dann gib eine leere Textzeichenfolge „“ aus, dh lasse die Zelle leer. Wenn nicht leer, dann aus der Spalte extrahieren Container Quelltabelle Die Lieferungen Zelleninhalt nach Zeilennummer mit Funktion INDEX (INDEX).
Vielleicht ist der einzige nicht sehr offensichtliche Punkt hier das Doppelwort Container in der Formel. So eine seltsame Schreibweise:
Lieferungen[[Container]:[Container]]
… wird nur benötigt, um die Spalte zu referenzieren Container war absolut (wie ein Verweis mit $-Zeichen für gewöhnliche „nicht-intelligente“ Tabellen) und rutschte nicht in benachbarte Spalten, wenn wir unsere Formel nach rechts kopierten.
In Zukunft, wenn die Daten in der Quelltabelle geändert werden Die Lieferungen, müssen wir daran denken, unsere Hilfszusammenfassung mit Zeilennummern zu aktualisieren, indem wir mit der rechten Maustaste darauf klicken und den Befehl auswählen Aktualisieren & Speichern (Aktualisierung).
Methode 3. Formeln
Diese Methode erfordert keine Erstellung einer Zwischen-Pivot-Tabelle und keine manuelle Aktualisierung, sondern nutzt die „schwere Waffe“ von Excel – die Funktion SUMMESLIMN (SUMME). Anstatt Zeilennummern in einer Zusammenfassung nachzuschlagen, können Sie sie mit dieser Formel berechnen:
Mit einiger äußerer Sperrigkeit ist dies tatsächlich ein Standardanwendungsfall für die selektive Summationsfunktion SUMMESLIMNA, das die Zeilennummern für die angegebene Stadt und den angegebenen Monat summiert. Da wir nicht mehrere Container in derselben Stadt im selben Monat haben, gibt unsere Funktion tatsächlich nicht den Betrag, sondern die Zeilennummer selbst aus. Und dann die bereits von der vorherigen Methode bekannte Funktion INDEX Sie können auch Containercodes extrahieren:
Natürlich müssen Sie in diesem Fall nicht mehr an die Aktualisierung der Zusammenfassung denken, sondern bei großen Tabellen an die Funktion SUMMESLI kann merklich langsam sein. Dann müssen Sie die automatische Aktualisierung von Formeln deaktivieren oder die erste Methode verwenden – eine Pivot-Tabelle.
Wenn das Aussehen der Zusammenfassung für Ihren Bericht nicht sehr geeignet ist, können Sie Zeilennummern daraus nicht direkt wie wir, sondern über die Funktion in die Abschlusstabelle extrahieren GET.PIVOT.TABLE.DATA (GET.PIVOT.DATEN). Wie das geht, erfahren Sie hier.
- So erstellen Sie einen Bericht mit einer Pivot-Tabelle
- So richten Sie Berechnungen in Pivot-Tabellen ein
- Selektives Zählen mit SUMIFS, COUNTIFS etc.