Oftmals kommt es vor, dass Vor- und Nachnamen von Personen in einer Zelle stehen. Manchmal ist es aber besser, Vor- und Nachnamen in separaten Spalten abzubilden.
Die beispielhaften Namen sollen nun auf die Zellen D3:E7 aufgeteilt werden.
Dazu markierst Du B3:B7 und klickst in der Multifunktionsleiste auf Daten und dann auf Text in Spalten. Auf dem Bildschirm erscheint diese Anzeige:
Wähle „Weiter“ und klicke dann als Trennzeichen „Leerzeichen“ an.
Wähle wieder „Weiter“, gebe als Zielbereich $D$3 ein und klicke auf „Fertigstellen“.
Im Bereich D3:E7 werden jetzt Vor- und Nachnamen getrennt dargestellt.
In Excel gibt es mittlerweile drei Funktionen, um den Inhalt zweier oder mehrerer Zellen in einer weiteren Zelle zu verbinden.
Die eine ist die Funktion VERKETTEN (engl. CONCATENATE), die es seit Excel 2003 gibt. Hierzu findest Du im Blog auch einen früheren Beitrag: „Zellinhalte in Excel verketten“.
Die Funktion dient in diesem Beitrag dem Vergleich mit den neuen Funktionen.
Die anderen sind die Funktionen TEXTKETTE (engl. CONCAT), verfügbar seit Excel 2019 und TEXTVERKETTEN (engl. TEXTJOIN), ebenfalls seit Excel 2019 verfügbar. Der Beitrag erklärt die drei Funktionen und zeigt Unterschiede auf.
1. Die Funktion VERKETTEN
Die Syntax für die VERKETTEN-Funktion lautet: =VERKETTEN(Text1;Text2,…)
Alternativ kann eine Verkettung auch mit dem Operator & herbeigeführt werden: =Text1&Text2
Text1 (erforderlich) ist das erste Element, das kombiniert werden soll. Es kann Text, wie Wörter oder Zahlen, von Anführungszeichen umgebene Leerzeichen oder Zellverweise auf den Speicherort von Daten in einem Arbeitsblatt sein.
Text2;Text3;…;Text255 (optional bis zu 255 Texteinträge) sind die anderen zu kombinierenden Einträge. Diese können der Funktion VERKETTEN bis zu maximal 8.192 Zeichen einschließlich Leerzeichen hinzugefügt werden. Jeder Eintrag muss durch ein Semikolon getrennt sein.
Verkettet werden können sowohl Texteinträge als auch Zahlen. Das Ergebnis einer Zahlenverkettung wird allerdings als Text gezeigt und kann nicht für mathematische Operationen verwendet werden.
Es ist aber wie gesagt möglich, unterschiedliche Formen miteinander zu kombinieren:
Text: Text schreiben Sie in Excel mit Anführungszeichen. So erkennt das Programm, wie es die Information zu verarbeiten hat.
Zahlen: Zahlen können Sie direkt in die Formel eintragen. Diese können, aber müssen Sie nicht in Anführungszeichen setzen.
Zellbezüge: Möchten Sie den Inhalt einer Zelle an die Funktion übergeben, tragen Sie den Zellbezug direkt als Argument in die Formel ein. Sie können den Bezug entweder relativ (ohne Markierung) oder absolut (mit Dollarzeichen als Marker) einfügen.
Funktionen: Sie haben außerdem die Möglichkeit, andere Funktionen in die VERKETTEN-Funktion einzufügen. Der Output dieser Funktionen wird dann mit den anderen Elementen verkettet.
1.1 Texte und Zellbezüge verketten
a) Die Worte „Laub“ und „Wald“ sollen verkettet werden. Die nachfolgende Abbildung zeigt drei Möglichkeiten dazu.
In Zeile 3 werden die Argumente als Text in Anführungszeichen eingegeben.
In Zeile 4 wird mit Bezügen gearbeitet.
Zeile 5 schließlich zeigt die Verwendung des Operators &.
b) Die nächsten Beispiele sind ebenfalls in den unter a) gezeigten Möglichkeiten darstellbar, hier wird nur die Arbeit mit Bezügen gezeigt.
In Zeile 3 werden Vor- und Nachnamen verkettet, zwischen den Argumenten wird ein Leerzeichen eingefügt.
In Zeile 4 werden die Argumente mit dem in Firmennamen üblichen „&“ verkettet. In Zeile 5 werden drei Argumente mit Leerzeichen und dem „&“ verkettet.
1.2 Zahlen verketten
Die Abbildung zeigt drei Beispiele:
In Zeile 3 werden zwei Zahlen verkettet. Beachte, das Ergebnis ist eine Zeichenfolge im Textformat. Das Textformat ist auch an der linksbündigen Ausrichtung ersichtlich.
In Zeile 4 wurde diese (Text)-Zeichenfolge mit 1 multipliziert. Dadurch wird die Zeichenfolge zur Zahl, was auch an der rechtsbündigen Ausrichtung ersichtlich ist.
In Zeile 5 wird eine Zahl mit einem Text kombiniert. Als Ergebnis erhältst Du eine Geschwindigkeitsangabe.
1.3 Andere Funktionen in VERKETTEN einfügen
Hierzu stelle ich Dir zwei Beispiele vor:
In Zeile 3 ist Text2 kleingeschrieben. Da es sich um einen Nachnamen handelt, willst Du den Text in der Verkettung großgeschrieben sehen. Du verwendest zunächst ein Leerzeichen zwischen Vor- und Nachnamen und setzt vor Text2 die Funktion GROSS2. Damit erreichst Du, dass „bartels“ großgeschrieben wird.
In Zeile 4 ist Text2 großgeschrieben. Da beide Argumente ohne Leerzeichen verkettet werden sollen, setzt Du vor Text2 die Funktion KLEIN. Der Output von KLEIN wird dann mit Text1 verkettet.
Die Syntax für die TEXTKETTE-Funktion lautet: =TEXTKETTE(Text1;Text2,…)
TEXTKETTE ersetzt die ältere Funktion VERKETTEN (siehe Abschn. 1), die aber weiterhin nutzbar bleibt.
Text1 (erforderlich) ist der erste Textwert, Zellbezug oder Bereich. Das zu verkettende Textelement. Eine Zeichenfolge oder ein Array von Zeichenfolgen, wie z. B. ein Zellbereich.
Text2(optional) ist der zweite Textwert, Zellbezug oder Bereich mit weiteren zu verkettenden Textelementen. Für die Textelemente sind maximal 253 Textargumente zulässig. Dabei kann es sich jeweils um eine Zeichenfolge oder ein Array von Zeichenfolgen, wie z. B. um einen Zellbereich, handeln.
Text3 usw. sind optional.
Die Funktion TEXTKETTE führt Text aus Zellen oder Zellbereichen zusammen, wobei keine Trennzeichen oder leere Zellinhalte gesondert berücksichtigt werden. Dazu übergibst Du Text, Zellbezüge sowie Zellbereiche an die Funktion TEXTKETTE welche Du in einer neuen Zelle zusammenfassen möchtest.
2.1 Texte, Zahlen und Zellbezüge verketten
Die Beispiele zeigen, dass sich eine Verkettung von Texten, Zahlen und Zellbezügen mit der Funktion TEXTKETTE genauso verhält wie mit der alten Funktion VERKETTEN.
In den Zeilen 3 und 4 werden Text1 und Text2 als Worte in die Formel eingetragen. Beide Ergebnisse sind nicht korrekt, richtig wäre z.B. Zeile 5.
Zeile 7 zeigt die Verkettung von drei Zahlen. Die Zahlen wurden in die Formel eingetragen. Das Ergebnis ist eine Textzeichenfolge.
In Zeile 8 wurden Bezüge in die Formel eingetragen, das Ergebnis ist eine Textzeichenfolge.
In Zeile 9 wurde zu dem Verkettungsergebnis eine Zahl addiert, das Ergebnis ist nun eine Zahl.
Zeile 11 zeigt das Erstellen einer Textkette über Bezüge.
In Zeile 12 enthält C nichts. Ohne die Ergänzung „ „ in der Formel würde das Ergebnis „Haus-Nr.“ lauten. Die Funktion TEXTKETTE berücksichtigt keine Leerzellen.
Die Zeilen und zeigen die Verkettung von Vor- und Nachname ohne und mit Leerzeichen in der Formel.
2.2 Einfache Bereiche verketten
Die Fähigkeit, z.B. einen Zellbereich mit einer Formel zu verketten, ist erst mit der Funktion TEXTKETTE gegeben. Mit VERKETTEN war das nicht möglich.
Zeile 3 zeigt ein einfaches Beispiel. Drei Zellinhalte werden als Argument B:D in die Formel eingegeben.
In Zeile 4 wird noch einmal ein vorheriges Beispiel aufgegriffen. Die Zelle C5 enthält hier ein Leerzeichen. B:D kann deshalb als Argument eingegeben werden. Das Ergebnis lautet korrekt „Haus-Nr. 16“.
2.3 Mehrspaltige Bereiche verketten
Auch das war mit der alten Funktion VERKETTEN nicht machbar. TEXTKETTE kann aus mehreren Zeilen und Spalten bestehende Bereiche verketten.
Text1 und Text2 umfassen jeweils vier Zeichenfolgen.
In D3 werden die Bereiche B3:B6 sowie C3:C6 durch Semikolon getrennt als Argumente eingegeben.
In D5 wird der Bereich B6:C6 gesamt als Argument gewählt.
Die Funktion „TEXTVERKETTEN“ geht über die beiden vorangegangenen Funktionen hinaus.
Sie kombiniert den Text aus mehreren Bereichen und/oder Zeichenfolgen und fügt zwischen jedem zu kombinierenden Textwert ein anzugebendes Trennzeichen ein. Wenn das Trennzeichen eine leere Textzeichenfolge ist, verkettet diese Funktion effektiv die Bereiche.
Trennzeichen (erforderlich) ist eine Textzeichenfolge, entweder leer oder mindestens ein Zeichen in doppelten Anführungszeichen oder ein Bezug auf eine gültige Textzeichenfolge. Eine eingegebene Zahl wird als Text behandelt. Das können irgendein einzelnes Zeichen, eine Zahl oder Buchstaben sein oder auch ein Wort oder Text.
Leer_ignorieren (erforderlich), wenn es WAHR ist, werden leere Zellen ignoriert, wenn es FALSCH ist, werden leere Zellen einbezogen.
Text1 (erforderlich) ist das zu verkettende Textelement. Eine Textzeichenfolge oder ein Array von Zeichenfolgen, z. B. ein Zellbereich. Text2 (optional) sind weitere zu verkettende Textelemente. Für die Textelemente sind maximal 252 Textargumente zulässig, einschließlich Text1. Dabei kann es sich jeweils um eine Textzeichenfolge oder ein Array von Zeichenfolgen (z. B. um einen Zellbereich) handeln.
3.1 Aufzählungen
In Zeile 3 wurde als Trennzeichen Komma und Leerzeichen gewählt. Mit dem Argument WAHR wird verhindert, dass die leere Zelle D3 mit in die Aufzählung genommen wird. In G3 ist die Aufzählung zu sehen.
In Zeile 6 wird der Bereich B6:B8 verkettet. Die Leerzellen in C6:E8 werden durch das Argument WAHR ignoriert.
Als Trennzeichen wurde Leerzeichen / Bindestrich / Leerzeichen verwendet.
3.2 Text und Währungsangaben verketten
Was wurde hier verkettet? Da sind Vorname, Name, Ort und ein Betrag. Das Währungszeichen „€“ ist nicht in den Texten vorhanden und wurde deshalb als weiteres Textelement in die Formel aufgenommen.
3.3 Verkettung mit verschiedenen Trennzeichen
In diesem Beispiel werden zwei verschiedene Trennzeichen benötigt, ein Leerzeichen sowie Komma und Leerzeichen.
Dazu wurde die Hilfstabelle B10:C10 angelegt. B10 enthält ein Leerzeichen, C10 enthält Komma und Leerzeichen.
Dieser Bereich wird als Argument Trennzeichen in die Formel eingegeben.
3.4 Verkettung verschiedener Inhalte
In diesem Beispiel enthalten Zellen unterschiedlich viele Wörter, Währungsangaben fehlen oder Leerzellen werden berücksichtigt.
In C2:C7 werden Betrag und Währungssymbol manuell dazu getragen.
In C4, D3 und C10 sind jeweils zwei Wörter in einer Zelle enthalten.
In C12 gibt es keinen Eintrag. Die Formel bezieht die Leerzelle mit ein, da als Argument FALSCH angegeben ist. Im Ergebnis sind zwischen „sind“ und „11° C“ jetzt zwei Leerzeichen vorhanden.
4. Fazit
TEXTKETTE und TEXTVERKETTEN sind schon komfortabler als die alte Funktion VERKETTEN.
Dennoch sind alle Beispiele des Beitrages auch noch mit VERKETTEN lösbar.
Und mehr sollte der Beitrag nicht zeigen. Über diesen Link kannst Du die Beispieldatei herunterladen:
Die Funktion SVERWEIS ist allgemein sehr bekannt und wird durch Excel-User vielfach für Abfragen genutzt.
Ich wurde schon einige Male gefragt, ob das Argument „Suchkriterium“ nur über einen identisch in der Matrix vorkommenden Wert bzw. als Bezug darauf oder auch über andere Funktionen bestimmt werden kann.
Der Beitrag zeigt Dir Möglichkeiten auf. Inwieweit diese praktisch für Dich relevant sein können, wirst Du selbst entscheiden. Hier war nur wichtig zu zeigen, wie es gehen kann.
Als aufmerksamer Leser hast Du sicherlich sofort festgestellt, dass es diese Funktion bei Excel gar nicht gibt. Bekannt sind die Funktionen ANZAHL, ANZAHL2, ZÄHLENWENN und ZÄHLENWENNS. ANZAHLWENN gibt es eben nicht.
Das Gute an der Excel-Funktion BEREICH.VERSCHIEBEN ist, dass sie, ausgehend von einer festen Zelle auf Deinem Arbeitsblatt, in alle Richtungen operieren kann, nach oben, nach unten, nach links oder nach rechts.
Diese Eigenschaft soll bei der Berechnung eines gleitenden Durchschnitts genutzt werden.
Eine Möglichkeit zeigt dieser Beitrag.
1. Die Wertetabelle
Angenommen, in einem Unternehmen wurden die Produktionsmengen von 6 Artikeln für den Zeitraum von Januar 2020 bis jetzt, Februar 2021, in einer Tabelle festgehalten, hier ein Auszug:
2. Die Einstellungen zur Auswahl
Für die Auswahl des Artikels, des aktuellen Monats und der Anzahl der Perioden, über die ein gleitender Durchschnitt berechnen werden kann, wurden über die Datenüberprüfung Listen angelegt.
Unter „akt. Monat“ ist hier der Monat zu verstehen, bis zu dem der gleitende Durchschnitt über die ausgewählte Anzahl der Perioden berechnet werden soll.
So bedeutet „6“ im Beispiel, dass der Durchschnitt für den Artikel „ZB 87 bn“ von Januar bis Juni 2020 zu rechnen ist.
Für die Auswahl der Perioden stehen 3, 6, 9 und 12 Monate zur Verfügung.
3. Die Berechnungsformel
Die Funktion BEREICH.VERSCHIEBEN hat folgende Syntax:
Du willst zuerst aber wissen, ob es links vom aktiven Monat (im Beispiel Jun 20) weitere 5 (6-1) Monate (im Beispiel) für die Durchschnittsberechnung gibt. Das kannst Du mit dieser Formel prüfen:
=VERGLEICH(EDATUM(C14;-C15+1);B4:O4;0))
Als Ergebnis erhältst Du die 1 (Jan 20). Nun prüfts Du noch, ob das Ergebnis eine Zahl ist, dazu setzt Du noch die Funktion ISTZAHL davor:
=ISTZAHL(VERGLEICH(EDATUM(C14;-C15+1);B4:O4;0))
Ergebnis ist WAHR.
Mit einer WENN-Abfrage machst Du weiter. Wenn die vorherige Berechnung WAHR ergibt, kann der Mittelwert über die mit Perioden vorgegebene Anzahl der Monate ermittelt werden.
Darin enthalten ist die Funktion ADRESSE. Diese liefert Dir als Bezug $G$4. Das ist genau die Zelle, in der „Jun 20“ in Zeile 4 steht.
Das Argument Zeile in BEREICH.VERSCHIEBEN berechnest Du mit der Formel
=VERGLEICH($C$13;A5:A10;0)
Der gesuchte Artikel aus C13 steht in Zeile 3 der Liste A5:A10.
Das Argument Spalte in BEREICH.VERSCHIEBEN besetzt Du mit 0, Du bleibst in der Spalte des Bezuges.
Für das Argument Höhe nimmst Du eine 1, Du bleibst in der Zeile.
Für das Argument Breite trägst Du
-C15
ein. Inklusive Jun 20 sollen es 6 Zellen sein, die in die Mittelwertberechnung einbezogen werden.
Letztlich bleibt noch die Sonst-Anweisung in der WENN-Abfrage. Sollte als die ISTZAHL-Formel FALSCH ergeben, soll in Zelle C17 die Mitteilung „Monate nicht verfügbar“ angezeigt werden.
4. Zusammenfassung
Die beschrieben Methode ermöglicht es Dir, einen Artikel und einen Ausgangsmonat auszuwählen und davon ausgehend über die gewählten Perioden einen Mittelwert zu berechnen.
Wählst Du in C14 den nächsten Monat nach rechts, gleitet der Mittelwert ebenfalls.
Wichtig ist, dass sich die Berechnungen des Solver auf nur eine Zielzelle beziehen. Diese muss eine Formel enthalten, die mit Vorgängerwerten ein gewünschtes Ergebnis errechnet.
Dies geschieht, indem ein exakter Wert oder ein Minimum oder ein Maximum als Ziel definiert wird. Der Solver arbeitet mit einer Gruppe von Zellen, die Du als „veränderbare Zellen“ bestimmen musst.
Die Berechnungen können Nebenbedingungen oder Einschränkungen unterliegen, die ebenfalls vorher festzulegen sind.
Letztlich passt der Solver darauf aufbauend die Werte in den veränderbaren Zellen so an, dass das Ziel erreicht und die Nebenbedingungen eingehalten werden.
Mit welchen Methoden der Solver im Einzelnen arbeitet, zeigt dieser Blogbeitrag.
Du hast sicherlich schon einmal in einer Liste nach dem Minimum oder dem Maximum unter Bedingungen gesucht.
In den Excelversionen bis 2016 musstest Du Dich damit begnügen, die Funktionen MIN bzw. MAX z.B. mit der WENN-Funktion zu kombinieren.
Seit Office 2019 bzw. mit einem Microsoft 365-Abonnement stehen Dir die neuen Funktionen MINWENNS und MAXWENNS zur Verfügung. Dieser Beitrag führt Dich in diese Funktionen ein.
Mitunter hast Du vielleicht den Wunsch, bei einer bedingten Formatierung mehrere Bedingungen zu berücksichtigen.
Das kann sicherlich in verschiedener Art und Weise erfolgen, dieser Beitrag will Dir eine Variante unter Verwendung der logischen Funktionen UND bzw. ODER zeigen.
