Neue Excel-Funktionen im Januar-2016-Update
21. April 2016
Microsoft hat im Januar 2016 ein Update für Office-365-Abonnenten durchgeführt, das den Anwendern von Excel 2016 einige Neuerungen beschert hat. „Formeln und Funktionen für Excel 2016“ war da leider schon im Druck, und die neuen Funktionen konnten nicht mehr beschrieben werden. Ich habe aber noch im Vorwort den Hinweis untergebracht, dass ich eine Beschreibung nachreichen werde. Hier also die Funktionen, die in Excel 2016 ab dem Januar 2016-Update zur Verfügung stehen:
TEXTVERKETTEN()
Syntax: =TEXTVERKETTEN(Trennzeichen;Leer_ignorieren;Text1;[Text2]; … [Textn])
Beschreibung: Die Funktion TEXTVERKETTEN() kombiniert maximal 252 Elemente aus Tabellenblättern und Texte oder Zahlen zu einer Textkette. Im Unterschied zur Funktion VERKETTEN() oder der Textverkettung mit &-Zeichen kann diese Funktion das Trennzeichen zwischen den Elementen vorgeben und Leerzeichen in Bezügen ignorieren. Die Textkette darf nicht mehr als 32.767 Zeichen ergeben, ansonsten wird ein #WERT!-Fehler ausgegeben.
Trennzeichen ist eine Zeichenfolge, die in Anführungszeichen eingegeben wird oder aus einem Bezug auf eine Zelle stammt. Eine Zahl wird als Text behandelt. Mit zwei Anführungszeichen („“) wird bestimmt, dass die Funktion kein Trennzeichen benutzt.
Leer_ignorieren ist WAHR, wenn die Funktion leere Zellen in den Argumenten ignorieren soll, oder FALSCH, wenn leere Zellen verknüpft werden sollen.
Text1 ist das erste Element, das die Textkette abbildet. Es ist erforderlich und kann aus einem Bezug, einer Matrix oder aus Texten oder Zahlen bestehen.
Text2 … Textn sind maximal 251 weitere Elemente, die verkettet werden.
Beispiele:
Die Adressdaten in einer Liste können zu einer Zeile zusammengefasst werden, das Trennzeichen ist ein Leerzeichen, leere Zellen werden ignoriert:
Beispiel 1: Adressdaten verketten
Für den Datenexport wird häufig eine Zusammenfassung der Zellen mit Semikolon als Trennzeichen verlangt. Berechnen Sie die Liste und speichern Sie die Datei als CSV- oder Textdatei:
Beispiel 2: Textkette mit Trennzeichen für Datenexports
Mit der Funktion ZEICHEN(10) lässt sich ein Zeilenumbruch erzeugen (10 ist der Ascii-Wert von „carriage return“ = Wagenvorschub, 13 ist „line feed“ = Zeilenumbruch). In der Textkette als Trennzeichen verwendet, bietet sie ein ideales Werkzeug, um zeilenweise angeordnete Adressdaten für Serienbriefe, Adressaufkleber etc. aufzubereiten. Damit Name und Vorname sowei PLZ und Ort eine Zeile bilden, werden sie in der Textkette nochmals mit dem &-Zeichen verkettet:
Beispiel 3: Textverkettung mit Zeilenumbruch
TEXTKETTE()
Syntax: TEXTKETTE(Text1;[Text2]; … [Textn])
Beschreibung: Diese Funktion ersetzt die Funktion VERKETTEN(), die aber noch in der Funktionsliste beibehalten wird. Es sind maximal 253 Elemente zulässig, die Textkette darf nicht mehr als 32.767 Zeichen ergeben, ansonsten wird ein #WERT!-Fehler ausgegeben.
Text1 ist das erste Element, das verkettet wird. Es ist erforderlich und kann aus einem Bezug, einer Matrix oder aus Texten oder Zahlen bestehen.
Text2 … Textn sind maximal 251 weitere Elemente, die verkettet werden.
Beispiele:
Bezüge und Textelemente werden kombiniert:
Beispiel 4: Textkette aus Namen und Sonderzeichen
Eine Anweisungsfolge aus drei Zeilen wird zu einer Zeile zusammengefasst, für den Zeilenumbruch sorgt die Funktion ZEICHEN(10) (Zeilenumbruch).
Beispiel 5: Textkette mit Zeilenumbruch
WENNS()
Syntax: =WENNS(Logiktest1; Wert_wenn_wahr1;[Logiktest2, Wert_wenn_wahr2]; … [Logiktestn, Wert_wenn_wahrn])
Beschreibung: Die Funktion WENNS() ersetzt die umständliche Kombination mehrerer WENN-Funktionen, wenn mehrere Bedingungen für ein Ergebnis abzuprüfen sind. An Stelle der Einzelbedingungen in einer geschachtelten Formel werden die Bedingungen und die Ergebnisse nacheinander als Argumente angegeben:
Alt: =WENN(Bedingung1;Dann;WENN(Bedingung2;Dann;Wenn(Bedingung3;Dann;Sonst) Neu: =WENNS(Bedingung1;Dann1;Bedingung2;Dann2;Bedingung3;Dann3)
Es können maximal 127 Paare aus Bedingung und Ergebnis in der Funktion angegeben werden.
Logiktest1 ist ein Ausdruck, der WAHR oder FALSCH als Ergebnis haben kann. Logiktest2 ist der zweite Ausdruck, Logiktestn der letzte.
Wert_wenn_wahr1 ist das Ergebnis, wenn die erste Bedingung WAHR ist. Wert_wenn_wahr2 ist das Ergebnis, wenn die zweite Bedingung WAHR ist, Wert_wenn_wahrn ist das Ergebnis, wenn die letzte Bedingung WAHR ist. Wenn keine der Bedingungen WAHR ist, wir der Fehlerwert #NV ausgegeben. Um für diesen Fall ein alternatives Ergebnis zu erhalten, wird die letzte Bedingung so formuliert:
WAHR;Ergebnis
Beispiel:
Die Liste enthält Herkunftsangaben in Spalte B, die Formel prüft alle möglichen Varianten ab und liefert das Ergebnis dazu. Ist die abgeprüfte Zelle leer, sorgt das letzte Argumentepaar dafür, dass „kein Eintrag“ in der Zelle steht (mit „“ bleibt die Zelle leer):
Beispiel 6: WENNS() mit mehreren Bedingungen
MAXWENNS() und MINWENNS()
Syntax: = MAXWENNS(Max_Bereich; Kriterienbereich1; Kriterium1;[Kriterienbereich2;Kriterien2]; … [Kriterienbereichn;Kriteriumn]) = MINWENNS(Min_Bereich; Kriterienbereich1; Kriterium1;[Kriterienbereich2;Kriterien2]; … [Kriterienbereichn;Kriteriumn])
Beschreibung: Die Funktion ermittelt den Maximalwert bzw. Minimalwert aus einer Zahlenreihe unter Verwendung einer oder mehrerer Bedingungen. Es können bis zu 126 Paare aus Kriterienbereich und Kriterium angegeben werden.
Max_Bereich ist der Bereich, aus dem der größte Wert ermittelt wird.
Min_Bereich ist der Bereich, aus dem der kleinste Wert ermittelt wird.
Kriterienbereich1 ist der Bereich, der parallel zum Max_Bereich bzw. Min_Bereich ausgewertet wird.
Kriterium1 ist das Kriterium, das festlegt, welche Zellen im Kriterienbereich1 für die Auswertung verwendet werden. Das kann ein Zellbezug, eine Zahl, ein Text oder ein Ausdruck sein.
[Kriterienbereich2;Kriterien2]; … [Kriterienbereichn;Kriteriumn] sind weitere Paare aus Kriterienbereich und Kriterium, die für die Auswertung herangezogen werden.
Beispiel: Die Risikoliste wurde anhand der aufgetretenen Schadensfälle mit Punkten versehen, in der Spalte daneben wird eine Gewichtung eingetragen. Mit der Funktion MAXWENNS() lässt sich die höchste Punktzahl für jede Gewichtung ermitteln.
Beispiel 7: MAXWENNS() ermittelt Höchstwerte nach Bedingungen
Die Kriterien definieren immer die zu prüfende Menge für die nachfolgende Bedingung. In diesem Beispiel liegt eine Liste mit Reparaturzahlen, Status und Reklamationsgrund vor. Die kleinste Anzahl Reparaturen wird aus den Zeilen ermittelt, die den angegebenen Status in Spalte B und den Reklamationsgrund in Spalte C haben.
Beispiel 8: Kleinsten Wert ermitteln mit mehreren Bedingungen
ERSTERWERT()
Syntax: ERSTERWERT(Ausdruck; Wert1; Ergebnis1;[Standardwert oder Wert2; Ergebnis2];…[Standardwert oder Wertn; Ergebnisn])
Ausdruck ist der Wert, der mit den Argumenten Wert1 bis Wertn verglichen wird.
Wert1 ist der Wert, der mit dem Ausdruck verglichen wird.
Ergebnis1 ist das Ergebnis, das ausgegeben wird, wenn Ausdruck und Wert1 identisch sind.
[Wert2;Ergebnis2] … [Wertn;Ergebnisn] sind weitere Paare mit Wert und Ergebnis, mit denen der Ausdruck verglichen wird.
[Standardwert] ist der Wert, der ausgegeben wird, wenn in keinem Vergleich zwischen dem Ausdruck und den Wert/Ergebnis-Paaren eine Übereinstimmung gefunden wurde.
Beschreibung: Diese Funktion liefert ähnlich der Funktion WAHL() ein Ergebnis, das aus dem Vergleich zwischen dem Ausdruck im ersten Argument und den Paaren aus Wert und Ergebnis ermittelt wird. Im Prinzip hält sie eine Referenzliste, wie sie bei Funktionen wie SVERWEIS() oder VERGLEICH() erforderlich ist, als Argumentfolge vor.
Beispiel:
Um zu einem EU-Kennzeichen das passende Land zu ermitteln, suchen Sie dieses mit der Funktion SVERWEIS() in der ersten Spalte einer Referenzliste und geben das Ergebnis aus einer weiteren Spalte aus. Mit ERSTERWERT() geht das auch ohne Referenzliste. Das letzte Argument wird zum Ergebnis, wenn in D2 kein gültiger Eintrag zu finden ist.
Beispiel 9: Alternative zum Verweis: ERSTERWERT()
Fazit:
Die neuen Funktionen sind sicher nützlich und brauchbar, besonders WENNS() ist eine echte Erleichterung und erspart in Zukunft die lästigen WENN-Schachtelformeln. Dass neue Funktionen in Versionsupdates immer Probleme bereiten, weil die Dateien von Anwendern älterer Office- bzw. Excel-Versionen nicht fehlerfrei benutzt werden können, ist bekannt. Wenn Microsoft aber mit jedem Monatsupdate innerhalb einer Versionsnummer neue Funktionen in die Funktionspalette einbaut, könnte das große Verwirrung schaffen beim Austausch von Excel-Daten mit Mitarbeitern, Kunden und Geschäftspartnern.
Wussten Sie schon …
… dass im Buch „Excel 2016 Formeln und Funktionen“ 120 (!) Praxisbeispiele von „ABC-Analyse“ bis „Zinssatz eines Darlehens berechnen“ zu den ausführlich beschriebenen Funktionen enthalten sind? Diese Praxisbeispiele und natürlich auch die Beispiele zu den neuen Funktionen finden Sie in den Downloaddaten zum Buch:
