In Kombination mit Formeln in der Basistabelle können Pivots ganz schnell noch viel mächtiger werden als sie es schon sind.
Der erste Schritt gehört nicht eigentlich zum Thema Pivots – wir fügen in die Basistabelle Formeln ein. Und anderes Vorbereitendes.
Aber es ist ein häufig vorkommendes Bedürfnis, das sich damit decken lässt.
Also, zurück in die Basistabelle: hier haben markieren wir in der Spalte Sender alles vom ersten Sendernamen bis zum letzten.
Daraus eine Pivottabelle verfertigen, die nur das Feld „Sender“ im Bereich „ZEILEN“ zu enthalten braucht. SChon haben wir eine Liste der Sendernamen, jeder nur einmal. Aber auch garantiert jeder, sofern wir in der Basistabelle alle Zeilen markiert haben. Es könnte ja immerhin sein, das beim letzten oder vorletzten Spot ein neuer Sender auftaucht…
Von hier ab gibt es mehrere Möglichkeiten:
- die Sendernamen aus der kleinen Pivot rauskopieren auf eine neues Arbeitsblatt oder
- die ganze Pivot markieren, kopieren und „Werte einfügen“…
Jedenfalls wollen wir ein Arbeitsblatt mit folgender Hilfstabelle erstellen:
Zuerst noch der Namens-Trick:
Bereichsnamen
Ist ein Berich (oder auch eine einzelne Zelle) markiert, kann man ihr einen Namen geben: diesen einfach in das Feld gleich links oberhalb der Spalte A eingeben. Da steht normalerweise der Standardname der Zelle, also A1 für die erste Zelle usw.
Man kann hier jeden beliebigen Namen verwenden, mit Ausnahme von gültigen Zelladressen (also z.B. A4 oder JAB34567, allerdings ist abhängig von der Excel-Version; ältere kennen nicht so viele Spalten). Ich empfehle daher mindestens 4 Buchstaben oder, falls weniger Buchstaben, einen Unterstrich _ vor den Zahlen, dann ginge auch z.B. A_354. Nur drei Buchstaben, also etwa JAB allein ist auch nicht zulässig, das ist die gültige Bezeichnung für die Spalte JAB (sehr sehr weit rechts). Eine Zahl allein geht auch nicht, ein Name muss mit einem Buchstaben – oder einem Unterstrich _ – anfangen.
In unserem Fall wählen wir „sender_vm“ und tragen das in das Feld oberhalb der Spalte A ein.
Man kann das Ergebnis kontrollieren, indem man eine beliebige Zelle außerhalb des Bereich markiert und dann in der Liste, wo wir grade den Namen eingetragen haben, nach dem vergebenen Namen sucht…
Die Verwendung von Namen für Bereiche oder einzelne Zellen hat viele Vorteile, nicht der geringste davon ist es, dass die Formeln, in denen die Namen verwendet werden, besser lesbar sind: =SUMME(B4:B8) hat keine so große Aussagekraft wie =SUMME(ausgaben). Finde ich jedenfalls. Man muss es nicht übertreiben, aber gewisse Zellen verdienen einen Namen – sie funktionieren dann wie Konstanten machen die Dinge in unübersichtlichen Arbeitsblättern ein Wenig übersichtlicher.
Aber nun weiter mit unserer Pivot-Tabelle:
Damit haben wir unsere Referenztabelle erzeugt, in der wir nachschlagen wollen, welcher Sender zu welchem Vermarkter gehört. Das machen wir mit der Funktion SVERWEIS…
In der Basistabelle eine neue Spalte einfügen, am besten gleich neben der Spalte „Sender“. Überschrift: „Vermarkter“. Die Formel wie folgt eingeben:
Aber Excel wäre nicht von Microsoft, wenn das Wunderding nicht dauernd seine Blödheiten machen würde. Unsere Zelle, in der wir die Formel eingegeben haben, ist nicht mit der richtigen Formatanweisung formatiert worden, sie betrachtet unsere mühsam erstellte Formel einfach als schnöden Text. Daher mit der rechten Maustaste zu „Zellen formatieren“ und dort das Zahlenformat auf „Standard“ stellen.
Und damit nicht genug der Blödheit, Excel kapiert es immer noch nicht: wir müssen erst die Formel nochmals bearbeiten – d.h. in den Bearbeitungsmodus gehen: wenn die Zelle mit der Formel markiert ist, entweder die Taste F2 drücken oder mit der Maus oben in den Formeleditor klicken. Dann einfach ohne weitere Änderung die Taste Enter oder Return drücken.
Erst jetzt kapiert Excel, dass wir hier eine Formel eingeben wollen.
Aber keine Sorge, das liegt in erster Linie an dem Programm, das die ursprüngliche Werbeblockliste ausgegeben hat: dieses Programm hat nämlich die Zellformate auf „Text“ gesetzt. Bei anderen Quellen kann es durchaus sein, dass diese Hürde gar nicht erst auftritt.
Was bedeutet das?
SVERWEIS
SVERWEIS bedeutet „Senkrechter Verweis“, im Gegensatz zum Bruder WVERWEIS („Waagrechter Verweis“).
SVERWEIS erwartet folgende Eingaben:
- einen Text, den wir suchen möchten
- einen Tabellenbereich, wo die Information gesucht werden soll (Matrix)
- die Spalte in der Matrix, die wir als Resultat zurückgegeben erhalten möchten
- einen Steuerwert WAHR/FALSCH, der regelt, ob Excel unscharf suchen darf (das brauchen wir hier garantiert nicht, also immer FALSCH)
In unserer Formel:
- Zelle A4 (da steht der erste Sendername, den wir suchen wollen) – keine $-Zeichen, wir wollen die Formel anschließend nach unten kopieren und sie soll sich die jeweils passende Zelle in derselben Zeile nehmen
- der Namen des Referenzbereichs, den wir vorhin angelegt und benannt haben: „sender_vm“ – hier soll Excel suchen, und zwar in der ersten Spalte
- die Spaltennummer, wo die von uns gesuchte Information zum jeweiligen Sender steht, also 2 (innerhalb der Referenztabelle, egal die wie-vielte Spalte im Arbeitsblatt ist)
- FALSCH (Excel soll keine Näherungs- oder Ausweichwerte liefern, bitte nicht)
Anschließend die Formel in der Spalte nach unten kopieren bis zum letzten Sendernamen. Das geht auch mit Doppelklick auf das winzige Kreuz in der rechten unteren Ecke der Zelle mit der Formel.
Excel geht für den Sendernamen aus jeder Zeile in unsere Referenztabelle und schaut nach, ob der Name in der ersten Spalte irgendwo vorkommt und gibt, wenn ja, den Wert aus Spalte 2 zurück.
Jetzt aber endlich!
Nur noch die Formel runter kopieren…
Leider steht zwischen dem Erfolg und uns noch eine weitere lästige Eigenheit von Excel, die unser Ursprungsprogramm dummerweise verwendet hat:
Man kann in Excel mehrere Zellen zu einer Einheit zusammen fassen, sowohl horizontal als auch vertikal. Der Übeltäter sitzt in der allerletzten Reihe:
Die „Anmerkung“ ist spaltenübergreifend zusammengefasst… Wer Excel ein Wenig kennt, weiß, dass das in diesem Kontext absolut überflüssig ist, jeder hier vorhandene Text würde auch ohne diese Formatierung in voller Länge angezeigt werden.
Behebung des Problems: den ganzen Zellenverbund markieren (er verhält sich sowieso wie eine einzige Zelle), dann im Menü auf Reiter „START“ in der Gruppe „Ausrichtung“ aus dem Untermenü „Verbinden und zentrieren“ die Option „Zellverbund aufheben“ wählen. Dazu muss allerdings der Zellverbund markiert sein.
Tipp: gibt es mehrere solche Zellverbünde in einem Blatt, die bei der Arbeit grade mehr hinderlich als nützlich sind, kann man auch das gesamte Arbeitsblatt markieren und dann „Zellverbund aufheben“ klicken. Das entfernt sie alle auf einen Klick…
Et voilà: nun geht das Kopieren mit Doppelklick auf einmal – und zu jedem Sendernamen erscheint der Vermarkter aus der Referenztabelle. Kann ein Name nicht gefunden werden, gibt Excel #NV()# zurück.
Die Pivottabelle müssen wir allerdings davon in Kenntnis sezten, dass wir eine Spalte ergänzt haben: Menü Reiter „ANALYSIEREN“ in der Gruppe „Daten“ auf „Aktualisieren“ klicken. Dann haben wir unser neues Feld „Vermarkter“ in der Feldliste und können wie gewohnt damit unser Unwesen treiben:
Und im Eiltempo weiter zu: Benutzerdefiniertes Sortieren.
