Scheinbar einfache Fragen, die sich als kniffelig herausstellen. Tolle Filterfunktionen für Pivot-Tabellen, wenn man sie kennt. Ein kleiner Trick, um einen Krampf im linken Zeigefinger zu vermeiden. All das zeige ich dir in den heutigen Excel-Quickies.
Im diesem Beitrag gibt es Tipps zu folgenden Themen:
- Quickie 115: Wie oft kommt ein bestimmtes Zeichen in einer Zelle vor
- Quickie 116: Mit einem einzigen Datenschnitt mehrere Pivot-Tabellen filtern
- Quickie 117: Schnelles Navigieren bei vielen Arbeitsblättern
Da ist hoffentlich auch für dich wieder etwas dabei.
Excel-Quickie Nr. 115: Wie oft kommt ein bestimmtes Zeichen in einer Zelle vor
Angenommen, du hast einen mehr oder weniger langen Text in einer Zelle und möchtest jetzt wissen, wie oft ein bestimmtes Zeichen in diesem Text vorkommt:
Wie oft kommt das Zeichen vor?
Die vielleicht naheliegend erscheinende Excel-Funktion ZÄHLENWENN hilft hier leider nicht weiter. Denn sie ermittelt die Anzahl der Zellen in einem Bereich, der einem angegebenen Kritierium entspricht. Nicht jedoch innerhalb einer einzelnen Zelle.
Hierfür gibt es keine eigene Funktion, aber ein kleiner Trick mit den Funktionen LÄNGE und WECHSELN bringt die Lösung.
=LÄNGE(A4)-LÄNGE(WECHSELN(A4;"o";""))
Kombination aus LÄNGE und WECHSELN
Die erste LÄNGE-Funktion bestimmt die Länge des Ursprungstextes.
Die WECHSELN-Funktion ersetzt das gesuchte „o“ durch nichts (die beiden leeren doppelte Anführungszeichen). Es löscht also quasi diesen Buchstaben aus dem Text.
Die umschließende LÄNGE-Funktion bestimmt nun die Länge dieses gekürzten Textes und zieht diesen Wert der Ursprungslänge ab. Und liefert damit die Anzahl der o’s.
Achtung:
In dieser Variante kommt es auf die exakte Schreibweise an. Ein groß geschriebenes „O“ wird damit nicht gefunden, wie das folgende Bild zeigt:
Groß- und Kleinschreibung beachten
Wer also unabhängig von Groß- und Kleinschreibung alles finden möchte, muss die Formel um eine zweite WECHSELN-Funktion erweitern:
=LÄNGE(A6)-LÄNGE(WECHSELN(WECHSELN(A6;"o";"");"O";""))
Beide Schreibweisen berücksichtigen
Die innere WECHSEL-Funktion entfernt das kleine „o“, die äußere nimmt das Ergebnis der inneren und entfernt daraus noch das große „O“.
Excel-Quickie Nr. 116: Ein Datenschnitt steuert mehrere Pivot-Tabellen
Jetzt kommt eines meiner Lieblingsthemen in Excel, nämlich die Pivot-Tabellen!
Mit Excel 2010 wurden die sogenannten Datenschnitte eingeführt, mit deren Hilfe man eine Pivot-Tabelle ganz bequem und benutzerfreundlich filtern kann. Wer davon noch nichts gehört hat, sollte zuerst unbedingt diesen Artikel lesen.
Hast du jedoch gewusst, dass man mit einem einzigen Datenschnitt auch mehrere Pivot-Tabellen gleichzeitig filtern kann?
Die einzige Voraussetzung ist, dass alle Pivot-Tabellen auf der gleichen Datenquelle basieren.
Im folgenden Beispiel habe ich eine Verkaufstabelle mit den Umsätzen für drei Jahre. Dazu habe ich drei einfache Pivot-Tabellen angelegt, die jeweils die Umsätze eines Jahres auf Regionen verdichten. Zur besseren Unterscheidung habe ich für die drei Pivots die Namen „U2013“, „U2014“ und „U2015“ vergeben. Das ist nicht zwingend notwendig, aber erleichtert die Arbeit im nächsten Schritt, wie du gleich noch sehen wirst.
Über einen Datenschnitt möchte ich nun alle drei Pivot-Tabellen nach einem bestimmten Produkt filtern können:
Beispieltabelle mit drei Pivot-Tabellen
Dazu muss ich nur den Datenschnitt anklicken, so dass das Menü mit den Datenschnitttools angezeigt wird. Dort gibt es die Schaltfläche „Berichtsverbindungen“:
Berichtsverbindungen öffnen
Im nun erscheinenden Fenster mit den Berichtsverbindungen werden alle Pivot-Tabellen dieser Arbeitsmappe angezeigt. Hier sind es nur drei, aber wenn du mit vielen Pivot-Tabellen arbeitest, erkennst du jetzt den Vorteil, einen aussagefähigen Namen zu vergeben. Standardmäßig ist der Haken nur bei der Pivot-Tabelle gesetzt, für die du den Datenschnitt erstellt hast. Setze jetzt den Haken auch bei den beiden anderen und schließe das Fenster mit OK.
Gewünschte Pivot-Tabellen anklicken
Und jetzt kannst du nach Lust und Laune alle drei Pivots mit diesem einen Datenschnitt filtern:
Ein Datenschnitt für alle Pivots
Excel-Quickie Nr. 117: Schnelles Navigieren bei vielen Arbeitsblättern
Gehörst du zu den Leuten, die sehr viele Arbeitsblätter in ihren Arbeitsmappen nutzen? Ich meine so RICHTIG VIELE?
Dann kennst du vermutlich das Problem: Das endlose Blättern durch die Registerkarten über die beiden kleinen Pfeilsymbole kann ziemlich nervtötend sein und zu einem Krampf im linken Zeigefinger führen. Aber hier kann dir geholfen werden.
Ein Klick mit der linken Maustaste auf eines der beiden Pfeilsymbole bei gleichzeitig gedrückter STRG-Taste blättert bis ans Ende bzw. bis zum Anfang der Arbeitsmappe:
Ein Linksklick bei gedrückter STRG+Taste
Das aktuelle Arbeitsblatt wird dabei jedoch nicht verlassen, man bekommt lediglich die letzten bzw. ersten Blätter angezeigt und kann diese jetzt bei Bedarf auswählen.
Und ein Rechtsklick auf einen der beiden Pfeile öffnet die Liste aller Arbeitsblätter, aus der man dann das gewünschte Auswählen und mit „OK“ direkt anspringen kann:
Alle Blätter zeigen per Rechtsklick
Damit sollte das ewige Scrollen ein Ende haben!
So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
bzgl „Wer also unabhängig von Groß- und Kleinschreibung alles finden möchte, muss die Formel um eine zweite WECHSELN-Funktion erweitern:“
gehts auch einfacher und übersichtlicher: KLEIN() wandelt den Ursprungstext und Kleinbuchstaben um. Sehr praktisch beim Vergleichen von Strings wenn man nicht case sensitiv sein muss oder sein will.
@Tabellenblattnavigation: HAHA, im 2010er Excel gabs dafür noch 4 Pfeile. Im neuen fehlen die beiden obwohl dadurch nichtmal Platz in der Breite gespart wurde – MS Logik für eine klassische Verschlimmbesserung.
Hallo Andi,
guter Tipp mit der KLEIN-Funktion, vielen Dank!
Was die Tabellennavigation angeht: Stimmt, ist mir gar nicht aufgefallen, dass es schon mal 4 Pfeile gab. Weniger ist manchmal tatsächlich weniger…
Schöne Grüße,
Martin
zu Excel-Quickie Nr. 115
Wie so oft, gibt es bei Excel viele Wege, ein Ziel zu erreichen.
Als Freund der Matrix-Formeln habe ich einen alternativen Ansatz zu bieten:
{=SUMME((TEIL(A4;SPALTE(1:1);1)=“o“)*1)}
(wie immer bei Matrix-Formeln werden die Klammern { } nicht eingegeben, sondern die Formeln mit Shift+Strg+Enter abgeschlossen)
Die Verwendung der Matrix-Formel sorgt dafür, dass die Anweisung SPALTE(1:1) einen Vektor mit den Zahlen von 1 bis 16384 liefert und der Ausdruck innerhalb der Summe für jede dieser Zahlen ausgeführt wird.
Die Formel nimmt dabei jeweils ein Zeichen aus der Zeichenkette in A4 und vergleicht dieses auf Gleichheit mit dem Buchstaben „o“. Die Multiplikation mit 1 verwandelt den Wahrheitswert WAHR oder FALSCH in die Zahlen 1 bzw. 0, die dann aufsummiert werden.
Die Suche erfolgt hierbei unabhängig von Groß- und Kleinschreibung.
Der Ansatz ist nicht besser, als der von Martin, sondern soll nur zeigen, wie unterschiedlich ein Problem angegangen werden kann.
Hallo Jochen,
eine sehr coole Anwendung einer Matrix-Formel. Vielen Dank für die tolle Ergänzung nebst Erklärung!
Schöne Grüße,
Martin
Der Plural kann durch das Anhängen eines s erzielt werden, jedoch ist das Apostroph dabei nicht korrekt. Ein Genitiv ist nämlich nicht zu erkennen, bei den Pivots die oben als „Pivot’s“ stehen. Das gilt auch für den Selbstlaut o, dessen Plural os ist und durch ein Apostroph einfach nur falsch dasteht. Alternativ ließe es sich bestimmt durch eine Formulierung mit dem Wort Vokalen gut rüberbringen.
Hallo Sven,
danke für den Hinweis. Jetzt ist mir auch mal wieder der „Deppenapostroph“ durchgerutscht, den ich bei den „Pivot’s“ gleich korrigiert habe 🙂
Bei den „o’s“ lasse ich ihn trotzdem stehen, denn in meinen Augen liest es sich einfach leichter. Richtig hin oder her.
Schöne Grüße,
Martin
Wie immer, vielen Dank für die wertvollen Tipps!
Zum Navigieren durch die Arbeitsblätter nutzte ich bisher die Tastenkombination CTRL+“Page Up“ / „Page Down“.
Doch die rechte Maustaste auf die Pfeile scheint mir sogar noch besser zu sein 😀
Hallo Robert,
die beiden von Dir genannten Kombinationen nutze ich auch häufig. Nur bei Mappen mit sehr vielen Blättern kann das mühsam sein…
Schöne Grüße,
Martin
Gerade der Trick mit dem Zählen gefällt mir sehr gut. Excel bietet immer wieder „Rätsel“ die man lösen muss und diese Lösung finde ich super: einfach mit und ohne o’s zählen.
Und jetzt lese ich mir noch mal die Pivot-Artikel durch. 🙂
Hallo Tim,
ja, Excel ist besser als jedes Rätselheft 🙂
Dann wünsche ich noch viel Spaß mit den Pivot-Tabellen.
Schöne Grüße,
Martin
Guten Morgen Martin,
leider erhalte ich in letzter Zeit die Newsletter nicht mehr, obwohl ich schon länger angemeldet bin. Kannst du mir da helfen?
Viele Grüße Joe
Hallo Martin,
Ein dickes Lob für deine Website! Hab dich vor kurzem entdeckt und find deine Arbeit hier klasse.
Gerade Tipp 117 erspart mir einiges an Zeit 😉
Gruss & Danke
Michi
Hallo Michi,
vielen Dank für das nette Feedback, freut mich sehr, wenn die Tipps weiterhelfen!
Schöne Grüße,
Martin