Heute gibt’s wieder ein paar Tipps & Tricks aus meiner Excel-Quickies-Reihe. Kein großer Schnickschnack, sondern leicht umsetzbare Kniffe, die das Anwenderleben vielleicht ein bisschen leichter machen.
In diesem Beitrag zeige ich dir Tipps zu folgenden Themen:
- Quickie 196: Aufzählungszeichen per Zahlenformat
- Quickie 197: Zellen zählen, die mit einer Zahl beginnen
- Quickie 198: Datenschnitte in geschützten Blättern bedienbar lassen
Da ist sicher auch für dich etwas dabei.
Excel-Quickie Nr. 196: Aufzählungszeichen per Zahlenformat
Auch wenn Excel eigentlich eine Tabellenkalkulation ist, wird sie gerne für andere Dinge missbraucht. Wie in diesem Fall, in dem einer Liste mit Agenda-Punkten Aufzählungszeichen vorangestellt werden sollen:
Eine Liste soll Aufzählungspunkte erhalten
Damit dies automatisch passiert und nicht jede Zelle einzeln geändert werden muss, kann man folgendes benutzerdefinierte Zahlenformat erstellen:
• Standard;• Standard;• Standard;• Standard
Benutzerdefiniertes Zahlenformat einrichten
Die Punkte kann man vorher entweder aus eine Zeichensatz heraus kopieren oder über die Tastenkombination Windows+Punkt eine Liste von Smileys, Symbolen und Sonderzeichen aufrufen und dort das gewünschte Zeichen auswählen.
Die Aufzählung funktioniert
Excel-Quickie Nr. 197: Zellen zählen, die mit einer Zahl beginnen
Dieser Tipp stammt von Adrian Stöckl, einem treuen Leser dieses Blogs (danke, Adrian!).
Er hatte aus irgendeinem Grund die Aufgabe, die Anzahl der Zellen in einem Bereich zu ermitteln, die mit einer Zahl beginnen. Gemeinerweise enthielt seine Liste jedoch nicht entweder nur reine Texte oder nur reine Zahlen, sondern auch alphanumerische Werte, die mit einer Zahl beginnen:
Die zu zählenden Daten
Eine einfache Lösung mit den Funktionen ANZAHL2 und ANZAHL fällt somit aus. Adrian hat aber folgende clevere Alternative gefunden:
=SUMMENPRODUKT(ISTZAHL(LINKS(B4:B9;1)*1)*1)
Wie funktioniert diese Formel genau?
Die innere LINKS-Funktion dürfte klar sein, hier wird einfach die erste Position der jeweiligen Zelle betrachtet
LINKS(B4:B9;1)
Achtung:
Normalerweise kann die LINKS-Funktion nur eine einzelne Zelle verarbeiten. Die alles umschließende SUMMENPRODUKT-Funktion erlaubt hier aber eine Ausnahme, daher kann ein Bereich wie B4:B9 angegeben werden (in Microsoft 365 geht das übrigens auch ohne SUMMENPRODUKT).
Jetzt kommt ein kleiner Kniff: Die gelieferte erste Position wird mit 1 multipliziert. Wenn es sich um einen Text handelt, ergibt das einen #WERT!-Fehler, bei Zahlen wird eine Zahl zurückgeliefert:
LINKS(B4:B9;1)*1
In der umschließenden ISTZAHL-Funktion wird nun geprüft, ob es sich um eine Zahl gehandelt hat:
ISTZAHL(LINKS(B4:B9;1)*1)
Das Ergebnis dieser Berechnung ist eine Liste aus WAHR oder FALSCH-Werten.
Nun kommt der letzte Schritt: Die SUMMENPRODUKT-Funktion. Sie multipliziert üblicherweise zwei Bereiche zeilenweise und addiert dann die einzelnen Produkte.
=SUMMENPRODUKT(ISTZAHL(LINKS(B4:B9;1)*1)*1)
Wer diese Funktion noch nicht kennt, kann hier mal tiefer reinschnuppern:
SUMMENPRODUKT: Theorie und Praxis (Teil 1)
SUMMENPRODUKT: Theorie und Praxis (Teil 2)
In unserem Beispiel werden durch SUMMENPRODUKT die WAHR und FALSCH-Werte mit 1 multipliziert und dann summiert. Da WAHR dem Wert 1 und FALSCH dem Wert 0 entspricht, ergibt das 0 + 0 + 1 + 1 + 1 + 1 = 4
Ein etwas kniffelige Formel bringt die Lösung
Und somit haben wir das gewünschte Ergebnis!
Bonus-Tipp:
Wer Microsoft 365 im Einsatz hat oder keine Angst vor Matrixformeln hat, kann auch folgende Alternativen verwenden:
M365:
=ANZAHL(LINKS(B4:B9;1)*1)
Nicht-M365-Versionen, aber Eingabe als Matrix-Formel:
{=ANZAHL(LINKS(B4:B9;1)*1)}
Das heißt, die Eingabe muss dann zwingend mit Strg+Umschalt+Eingabetaste abgeschlossen werden.
Excel-Quickie Nr. 198: Datenschnitte in geschützten Blättern bedienbar lassen
Dieser Tipp ist für alle, die gerne den Blattschutz aktivieren möchten aber dann feststellen müssen, dass sich damit auch keine Datenschnitte mehr bedienen lassen (danke, Rene, für diese Anregung!)
Blattschutzoptionen sollen das Filtern ermöglichen
Mit den gesetzten Einstellungen lassen sich zwar die Autofilter direkt innerhalb der Tabelle nutzen:
Autofilter bleibt zwar möglich, Datenschnitt ist gesperrt
Versucht man aber, den Filter über den Datenschnitt zu verändern, passiert leider nichts – der Datenschnitt bleibt gesperrt. Das Problem lässt sich aber einfach lösen:
Bevor der Blattschutz aktiviert wird, muss der Datenschnitt mit einem Rechtsklick markiert und dann das Menü „Größe und Eigenschaften…“ aufgerufen werden (alternativ geht auch die Tastenkombination Strg+1). In dem Arbeitsbereich rechts wird dann unter „Eigenschaft“ einfach das Häkchen vor „Gesperrt“ entfernt:
Datenschnitt-Eigenschaften anpassen
Wenn jetzt der Blattschutz aktiviert wird, bleibt der Datenschnitt trotzdem bedienbar.
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.
Mir erschließt sich zwar nicht ganz die Logik, warum man 3x „• Standard“ im Format eingeben muss, aber es klappt, also sehr schön 😉
Beim Thema Datenschnitte hätte ich noch eine Ergänzung:
Wenn man den Datenschnitt entsperrt hat, wie auf dem Bild beschrieben, dann kann man etwas weiter oben unter „Position und Layout“ noch einen Haken setzen bei „Größenanpassung und Verschieben deaktivieren“.
Jetzt kann der Datenschnitt auch nicht einfach verschoben werden und wird im Prinzip so behandelt wie andere Bilder/Objekte im Tabellenblatt die gesperrt sind, bleibt dabei aber nutzbar.
Hallo Sebastian,
ein benutzerdefiniertes Zahlenformat kann aus maximal 4 Abschnitten bestehen: Positive Zahlen, negative Zahlen, Nullwerte und Text.
Mit dem „Standard“ soll nur sichergestellt werden, dass jeder dieser 4 Gruppen im Standardformat dargestellt wird.
Und Danke für die sehr hilfreiche Ergänzung zum Datenschnitt, diese Einstellung ist auf jeden Fall empfehlenswert.
Schöne Grüße,
Martin