Der heutige Artikel ist einem Leser zu verdanken, der folgendes Problem hatte:
In einer Exceltabelle wird für jeden Mitarbeiter ein Kreuzchen gesetzt, wenn dieser Mitarbeiter am betreffenden Tag Urlaub hat. Du kennst vermutlich diese Art von Urlaubsübersichten.
Nun soll aber zusätzlich in einer Zusammenfassung eine Liste nur dieser genommenen Urlaubstage angezeigt werden.
Ich fand diese Aufgabenstellung sehr interessant und möchte dir meine Lösung im folgenden Beitrag zeigen.
Und so geht’s:
Die Urlaubstabelle
Die Ausgangslage ist eine ganz normale Urlaubstabelle, wie sie so oder in ähnlicher Form häufig in Excel geführt wird:
Ein einfache Urlaubstabelle
In der betreffenden Zelle wird ein Kreuzchen gesetzt (oder ein „U“ für Urlaub), wenn dieser Mitarbeiter an dem jeweiligen Tag Urlaub hat.
Schön wäre es nun, wenn am Ende der Tabelle gleich noch eine kurze Zusammenfassung mit allen genommenen Urlaubstagen ausgegeben wird, etwa so:
Aufgabe: Die Tabelle in einer Zelle zusammenfassen
Nun bietet Excel bekanntlich ja eine Reihe Verkettungsfunktionen, mit der sich die Inhalte von verschiedenen Zellen zusammenfassen lassen:
- &
- VERKETTEN
- TEXTKETTE (in Office 365 / Excel 2019)
- TEXTVERKETTEN (in Office 365 / Excel 2019)
Tatsächlich verwendet meine Lösung die TEXTVERKETTEN-Funktion. Hier die allgemeine Syntax:
=TEXTVERKETTEN(Trennzeichen;Leer_ignorieren;Text1;Text2;...)
Damit ließen sich alle Datumswerte meiner Beispieltabelle mit Komma getrennt ein einer Zelle zusammenfassen:
=TEXTVERKETTEN(",";WAHR;$B$1:$L$1)
Erster Schritt mit der TEXTVERKETTEN-Funktion
Der zweite Parameter WAHR soll bewirken, dass leere Zellen ignoriert werden. Man erkennt jedoch schnell, dass es mit dieser Formel zwei Probleme gibt:
- Die Datumswerte werden als normale Zahlen dargestellt
- Es werden ALLE Datumswerte in Zeile 1 gelistet und nicht nur die, bei denen darunter ein „u“ eingetragen ist
Naheliegend ist es daher, über die WENN-Funktion die Einträge in der Mitarbeiterzeile zu überprüfen. Wenn ein „u“ eingetragen ist, soll der Wert aus Zeile 1 ausgegeben werden, wenn nicht, dann eben nicht. Also etwa so:
=TEXTVERKETTEN(",";WAHR;WENN(B2:L2="u";$B$1:$L$1;""))
Leider reicht das offensichtlich noch nicht, denn das Ergebnis bleibt unverändert:
Kombination mit der WENN-Funktion
Oder schlimmer, es wird plötzlich gar nichts mehr angezeigt, falls nämlich die erste Zelle leer ist:
Das Ergebnis passt leider nicht
Der Grund dafür ist einfach: Wir haben versucht, der WENN-Funktion für die Prüfung einen ganzen Tabellenbereich („Array“) unterzujubeln. Damit kann eine „normale“ WENN-Funktion nichts anfangen. Genauer gesagt wird nur die erste Zelle des angegebenen Bereichs geprüft, in diesem Fall also B1. Und wenn diese Zelle leer ist, dann wird eben überhaupt nichts ausgegeben.
Die Lösung ist jedoch einfach.
Eine Array-Formel hilft weiter
Wir müssen die verwendete Formel nur als Array-Formel eingeben. Das heißt, sie muss mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen werden, und schon sieht es etwas besser aus:
Eingabe als Array-Formel
Dass es sich um eine Array-Formel handelt, erkennt man an den automatisch eingefügten geschweiften Klammern in der Bearbeitungszeile.
Und jetzt werden nur noch 5 Datumswerte angezeigt, was auch der Anzahl der U’s in Zeile entspricht.
Fehlt nur noch die Darstellung in einem vernünftigen Datumsformat. Dazu erweitern wir die Formel noch um die TEXT-Funktion:
{=TEXTVERKETTEN(", ";WAHR;WENN($B2:$L2="u";TEXT($B$1:$L$1;"TT.MM.");""))}
(bitte auch hier nicht vergessen, die Eingabe wieder mit Strg+Umschalt+Eingabe abzuschließen!)
Und jetzt sieht unsere Zusammenfassung endlich so aus wie sie soll:
Datumswerte im richtigen Format darstellen
Das Ganze funktioniert selbstverständlich auch dann, wenn deine Urlaubsliste vertikal aufgebaut ist.
Einziger Wermutstropfen:
Die TEXTVERKETTEN-Funktion gibt es nur in Office 365 oder in der aktuellen Version Excel 2019.
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.
Wie immer ein schöner Artikel, diesmal allerdings schon sehr speziell, gerade durch die Beschränkung auf Office 365 und 2019.
Aber gerade die mitbenötigten Funktionen zum Datum sind sehr interessant und sicher immer wieder nützlich.
Danke!
PS:
Der s-Plural wird nicht mit einem Apostroph gebildet, also Us.
Hallo Friedrich,
danke für das Feedback, gern geschehen.
Schöne Grüße,
Martin
Hallo Martin,
wäre das Ergebnis nicht einfacher mit einer Index-Abfrage zu erreichen gewesen?
Viele Grüße, Rolf
Hallo Rolf,
ich wüsste jetzt nicht, wie man das mit der INDEX-Funktion hinbekommen würde, ohne jeden einzelnen Tag ansprechen zu müssen. Wie sieht denn Deine Lösung aus?
Schöne Grüße,
Martin
An genau so einer Lösung basteln wir grad. Aber mit excel 2013/2016.
Wie würde denn die Lösung hierzu aussehen?
Hallo,
mit dieser Funktion geht´s auch ab Version 2010, ohne Verketten in jeder Zelle ein Datum!
=WENNFEHLER(AGGREGAT(15;6;$B$1:$L$1/–($B2:$L2=“u“);SPALTE(C2)-SPALTE($B$1));““)
Die Funktion nach rechts ausfüllen auf die Anzahl der Zellen möglicher Werte (Also meistens 30 bei einer Jahrestabelle)
Kurze Erläuterung:
Die Werte in der Aggregatfunktion – welche die Fehlerwerte ingoriert (2. Argument bei Aggregat = 6) – werden jeweils durch den Wahrheitswert Bereich=“u“ geteilt und ergeben somit für jedes Datum ohne U eine Division durch Null und somit einen Fehlerwert, welcher dann ignoriert wird.
Hinweis 1:
Die Wahrheitswerte werden durch die Klammersetzung mit den vorangestellten 2 Minuszeichen in eine Zahl umgewandelt (WAHR = 1; FALSCH = 0).
Hinweis 2:
Durch Spalte(C2) relativ! abzüglich Spalte($B$2) absolut! wird immer das letzte Argument von der Funktion KKLEINSTE (1. Argument bei Aggregat=15) in einer Zahlenfolge dargestellt.
Ich hoffe, dass meine Erläuterungen verständlich sind und wünsche viel Spass beim ausprobieren.
Freundliche Grüße aus Gronau
Hannes
Hallo Hannes,
vielen Dank für diese ebenfalls interessante Lösung, da wäre ich nie darauf gekommen. Damit werden die Datumswerte zwar nicht in einer einzelnen Zelle zusammengefasst, aber man bekommt zumindest eine durchgängige Reihe.
Man müsste dann vielleicht noch über eine WENN-Funktion prüfen, ob das angezeigte Datum schon in der vorhergehenden Spalte vorhanden war. Denn ansonsten wird bis zum Schluss immer der jeweils letzte Eintrag angezeigt, wenn man die Formel nach rechts auffüllt.
Schöne Grüße,
Martin
Hallo Martin,
das kann eigentlich nicht sein. Denn wenn in der vorgegebenen Tabelle jedes Datum nur 1 x vorhanden ist und durch die fortlaufende KKLEINSTE() – Funktion (Spalte(S;Z)-Spalte($S;$Z-1) der n-kleinste Wert fortlaufend ausgelesen wird, kann eigentlich jedes Datum höchstens 1 x auftreten. Da in dem Ursprungs-Array ja jedes Datum nur 1x aufgelistet ist. Das wäre natürlich Bedingung, also eine chronologische Kalenderreihenfolge beim angegebenen Array. Also in meinem Test funktionierts.
Die Datumswerte die mit einem „U“ versehen sind werden chronologisch aufgeführt. Achte mal auf die relativ/absolut Angaben in der Spalte()-Funktion.
Vielleicht versehe ich die Problematik aber auch nicht.
Freundliche Grüße
Hannes
Korrektur:
Es muss Spalte(S;Z)-Spalte($S-1;$Z) heißen.
Hallo Hannes,
ich nehme alles zurück und behaupte das Gegenteil 🙂
Du hattest natürlich recht, die Formel funktioniert. Man muss sie nur richtig eintippen…
Danke nochmal,
Martin
Hallo Hannes,
vielen Dank für die tolle Formel. Sie funktioniert super wenn man nur nach einem Argument (z.B. U) sucht. Könnte man die Formel auch so anpassen das sie nach drei Kriterien (G;K;S) sucht? (G=gemehmigter Urlaub; K=Krank; S=Schule/Seminar)
Hier die Formel wie sie in meiner Excelzelle erfasst ist:
=WENNFEHLER(AGGREGAT(15;6;Planer!$C$30:$C$395/–(Planer!$P$30:$P$395=“G“);ZEILE(Planer!C34)-ZEILE(Planer!$C$30));“-„)
Leider hab ich keinen Plan, wie ich hier eine Anpassung auf die drei Argumente vornehmen könnte.
Vielen Dank schon mal im voraus.
Hansi
Hallo Hansi,
habe erst jetzt deinen Kommentar gelesen.
Versuch mal den Teil „–(Planer!$P$30:$P$395=“G“)“ zu ersetzen durch „–(Oder(Planer!$P$30:$P$395=“G“;Planer!$P$30:$P$395=“K“;Planer!$P$30:$P$395=“S“))“.
Sollte dann funzen.
Freundliche Grüße und ein schönes Wochenende!
Hannes
Hallo Martin,
Ihre Seite und Ihre Tipps sind immer hilfreich. Aber ich habe das anderen Problem, Personalkosten für Unternehmer. Die Tabelle ist sehe beweglich, die Mitarbeiter wurde jeden Monat entwider eingestellt oder gekündigt. Wie kann ich eine konsolidierte Tabelle für das ganzen Jahr bekommen. Ich habe probiert über die Konsolidierung , über Pivotabelle. Es ist nicht gelungen. Leider habe ich keine Lösung , eventuell hast du eine Lösung, es wäre prima.
Danke
Gruß
Irina
Hallo Irina,
die Frage hat eigentlich nichts mit diesem Artikel zu tun und sie lässt sich aufgrund der relativ allgemeinen Angaben auch schwer beantworten. Das hängt alles von den genauen Anforderungen, vom tatsächlichen Aufbau der Tabelle(n) und von der Art und Weise ab, wie die Daten gepflegt werden. Tut mir sehr leid, wenn ich daher an dieser Stelle nicht weiterhelfen kann.
Schöne Grüße,
Martin
Hallo
Hier bietet sich wohl eher eine Datenbank an.
Mfg gerhard
Hallo Zusammen. Ich habe verschiedene Excel Tabellen mit verschiedenen Anzahl von Spalten. Frage, wie kann ich aus diesen eine einzige machen, ohne Datenverlust? Danke für Eure Hilfe
Hallo Giovanni,
um die Frage beantworten zu können, müssten wir wissen, wie deine Tabellen aussehen und wie genau du sie zusammenführen möchtest. Sollen alle Tabellen untereinander gefügt und sozusagen eine einzige lange Tabelle ergeben oder sollen nur bestimmte Spalten nebeneinander dargestellt werden?
Schöne Grüße,
Martin
Hallo Martin.
Vielen dank für deinen aufschlussreichen Artikel, der mir dabei geholfen hat unsere Urlaubstabelle etwas zu pimpen und die Zusammenhänge besser zu verstehen.
Zur Zeit bin ich gerade dabei hiermit ein etwas anderes Problem lösen zu wollen.
Ich versuche als Text die Zeilennummer in denen sich die „U“s befinden wiederzugeben.
Mein Versuch sieht zur Zeit so aus:
{=TEXTVERKETTEN(“ / „;WAHR;WENN(B:B=A1;TEXT(ZEILE(INDEX(B:B;VERGLEICH(A1;B:B;0)*1));“0″);““))}
In Zelle A1 wird ein Begriff (z.B. Allgemeines Bla bla) eingetragen und in Spalte B sind Hunderte Begriffe.
Nun kann es vorkommen, dass der Begriff „Allgemeines Bla Bla“ mehrfach in Spalte B vorhanden ist, z.B. in Zeile 1, 2 und 3).
Als Ergebnis hätte ich jetzt eigentlich gerne „1 / 2 / 3“. Es werden auch 3 Treffer gefunden, allerdings wird immer wieder der erste angezeigt, also „1 / 1 / 1“.
Hättest du eine Idee wie ich mein Problem doch noch lösen kann?
Vielen Dank für Ratschläge und schöne Grüße
Sebastian
Hallo Sebastian,
versuch’s mal hiermit:
{=TEXTVERKETTEN(“ / „;WAHR;WENN(B:B=A1;ZEILE(B:B);““))}
Schöne Grüße,
Martin
Ich glaub ich hab da ein wenig umständlich gedacht.
Vielen Dank für die schnelle Hilfestellung.
Die Formel funktioniert reibungslos.
Hallo Martin,
wenn ich dieses Array abschließe zeigt er mir nur den ersten Wert der Tabelle an, es sind (aktuell) mindestens 2 eingetragen.
{ =TEXTVERKETTEN(„, „;WAHR;WENN(TabelleSpeda[Spalte14]=1;TabelleSpeda[Spalte17];““)) }
hab ich da einen Tippfehler, oder denk ich falsch?
Für einen Rat wäre ich echt dankbar…
Hallo Martin,
mit ein wenig nachdenken konnte ich meinen Denkfehler finden :-D…
die „Wennabfrage“ kann ich mir schenken, da die Bedingung ja vorher schon erfüllt ist :-)).
Trotzdem schön, dass Du Dir die Mühe gemacht hast!
Gruß Chico
Hallo Chico,
freut mich, dass du das Problem noch selbst lösen konntest 🙂
Schöne Grüße,
Martin
Hallo Martin!
Danke für den guten Beitrag, ich steht nur gerade vor dem Problem, dass ich 2 Bedingungen habe, die erfüllt werden müssen, damit sich der Text verketten soll.
Ich hätte die Formel derzeit so aufgebaut: =TEXTVERKETTEN(„, „;WAHR;WENN(UND(I139:I183<10;E141:E183="Status Quo");A141:A183;"")), aber hier erhalte ich leider gar kein Ergebnis mehr. Mit nur einer Bedingung funktioniert es ausgezeichnet.
Vielleicht hat ja jemand eine Idee wie man es lösen könnte.
Danke
Hallo Eva,
versuch’s mal hiermit:
=TEXTVERKETTEN(", ";WAHR;WENN((I139:I183<10)*(E139:E183="Status Quo");A139:A183;""))Warum die UND-Funktion hier Zicken macht, kann ich dir leider auch nicht erklären.
Hinweis:
Achte auch darauf, dass alle Bezüge gleich groß sind. In deiner Formel beziehst du dich einmal auf Zeile 139:183 und dann auf 141:183
Schöne Grüße,
Martin
Moin Eva und Martin,
ich gehe davon aus, dass die UND – Funktion deswegen zicken macht, weil dafür alle Bedingungen Wahr sein müssen.
Das ist aber nur dann der Fall, wenn I139:183<10 immer Wahr und E139:E183="Status Quo" immer Wahr ergibt.
UND scheint hier nicht "Zeilenweise" vorzugehen sondern alle Ergebnisse zu betrachten. Wenn also nur eins davon Falsch ergibt, ist es für die UND – Funktion im Gesamten Falsch.
Mfg Marcel
Servus Marcel,
ja, das klingt nach einer plausiblen Erklärung. Vielen Dank für den Hinweis!
Schöne Grüße,
Marcel
Hallo Martin, hab folgendes Problem.
Ich habe über Zellen formatieren die benutzerdefinierte Einstellung MM/JJ vorgenommen. Somit wird jedes Datum, das dort eingetragen wird gleich auf das Format geändert. Soweit so gut. Nun benötige ich TEXTVERKETTEN um die Inhalte der Zellen, in meinem Fall, getrennt mit einem Komma, in einer anderen Zelle aufgelistet zu bekommen. Das klappt auch soweit. Nur stellt Excel jetzt die Inhalte nicht im Format MM/JJ dar, sonder in Zahlen und vor allem, das ist wichtig, darf am Ende kein Komma stehen, wenn in der nächsten Zelle kein Inhalt ist. Wie bekomme ich die Zelleninhalte in der Zelle mit der TEXTVERKETTUNG mit dem Format MM/JJ angezeigt, ohne die Kommas der leeren Folgezellen angezeigt zu bekommen? Hoffe du kannst mir hier weiterhelfen. Danke vorab. VG Harry
Hallo Harry,
du musst zusätzlich mit der TEXT-Funktion arbeiten. Also beispielsweise =TEXT(B4;“MM/JJ“), um das Datum im gewünschten Format darzustellen.
Wenn du mit TEXTVERKETTUNG beispielsweise zwei Datumsfelder (A1, B1) verketten möchtest, dann würde das so aussehen:
=TEXTVERKETTEN(„;“;WAHR;TEXT(A1;“MM/JJ“);TEXT(B1;“MM/JJ“))
Schöne Grüße,
Martin
Vielen Dank für diese Formel-Idee – im Prinzip genau das, was ich gesucht habe (Bedingung in Verketten-Funktion). Allerdings hat deine Formel in meinem Fall einen Fehler produziert (#WERT), den ich aber nicht genau nachvollziehen konnte, warum er produziert wurde (icg vermute Leerstelen innerhalb der Tabelle). Auf der Suche nach Abhilfe bin ich auf die FILTER-Funktion aufmerksam geworden, die m.E. die Unzulänglichkeiten der Wenn-Bedingung und der Notwendigkeit einer Array-Formel umgeht – im Beispielfall müsste das dann lauten:
=TEXTVERKETTEN(„, „;WAHR;FILTER(TEXT($B$1:$L$1;“TT.MM.“);$B2:$L2=“u“;))
Viele Grüße, Andreas
P.S.: Danke für die immer wertvollen Tipps! Haben mir schon häufiger weitergeholfen
Hallo Andreas,
vielen Dank für die schöne Alternative mit der FILTER-Funktion. Allerdings ist diese Funktion erst nur in M365 enthalten und funktioniert damit mit Excel 2019 noch nicht. Das ist auch der Grund, warum hier keine explizite Array-Formel mehr eingegeben werden muss. Excel aus Microsoft 365 kann automatisch mit Arrays umgehen.
Trotzdem ein sehr gute Lösung.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Excel Artikel. Sehr aufschlußreich.
Ich habe diese genommen um folgendes zu machen:
Prüfe in einem Bestellblatt nach, welche Kunden „D2“ hat und liste diese in einer Zelle mit Komma getrennt auf.
Passt sehr gut.
Jetzt würde ich aber gerne die Liste ohne doppelten Kundennamen erstellen. Sprich „Liste aber Kunden nur einmalig in der Liste auf“.
Da scheitere ich gerade. Eine Idee?
Hallo Martin, war, glaube ich, schneller 🙂
Lösung gefunden.
Falls es jemand benötigt, hier mein Lösungsansatz
=TEXTVERKETTEN(„, „;WAHR;SORTIEREN(EINDEUTIG(FILTER(M2:M417;N2:N417=D2))))
Somit das ganze auch noch alphabetisch sortiert
Tja lieber Martin,
komme wohl ohne deine Hilfe doch nicht hin. 🙂
Diese Formel listet mir alle Kunden auf die von einer bestimmten Person (D2) betreut werden:
=TEXTVERKETTEN(„, „;WAHR;SORTIEREN(EINDEUTIG(FILTER(M2:M417;N2:N417=D2))))
und liefert mir dann:
Kunde A, Kunde B
Nun möchte ich das hinter jedem Kunden auch die Anzahl „gekauft“ in Klammern dahinter steht.
Hiermit:
=TEXTVERKETTEN(„, „;WAHR;SORTIEREN(EINDEUTIG(FILTER(M2:M417;N2:N417=D2)))&“
(„&WENNFEHLER(ZÄHLENWENNS(C2:C417;“gekauft“;N2:N417;A7);0)&“)“)
Erhalte ich dieses falsche/positive Ergebnis:
Kunde A (2), Kunde B (2)
Positiv, da ich es ja abfrage.
Falsch, da lediglich „Kunde A“ 2 mal gekauft hat, Kunde B gar nicht. Somit müsste als Ergebnis
Kunde A (2), Kunde B (0)
rauskommen.
Was bedenke ich nicht bzw. mache ich falsch?
Gruß
Anton
Hallo Anton,
deine ZÄHLENWENNS-Funktion immer die Gesamtzahl aller Verkäufe, denn es ist dort ja kein Kriterium angegeben, dass auch die Kunden berücksichtigen würde.
Dann verweist du in der Formel einmal auf D2 und einmal auf A7, was mir auch nicht ganz klar ist. Ich gehe in meinem Vorschlag davon aus, dass der Betreuer „D2″ heißt (deshalb in Anführungszeichen):
=SORTIEREN(EINDEUTIG(FILTER(M2:M417;N2:N417=“D2″))&“ („&ZÄHLENWENNS(C2:C417;“gekauft“;N2:N417;“D2″;M2:M417;EINDEUTIG(FILTER(M2:M417;N2:N417=“D2″)))&“)“)
Sollte es jedoch so sein, dass in Zelle A7 der Name des Kundenbetreuers steht, dann wäre mein Vorschlag eben:
=SORTIEREN(EINDEUTIG(FILTER(M2:M417;N2:N417=A7))&“ („&ZÄHLENWENNS(C2:C417;“gekauft“;N2:N417;A7;M2:M417;EINDEUTIG(FILTER(M2:M417;N2:N417=A7)))&“)“)
Schöne Grüße,
Martin
Hallo Martin,
Besten dank und entschuldige die Verwirrung.
In D2 steht normalerweise der Kundenbetreuer, zum Testen hatte ich es auf a7 umgestellt.
Somit werde ich die zweite variante versuchen.
Mir war klar, das ich erneut die doppelten beim Zählenwenns als Kriterium angeben muss, war mir aber nich klar wo und wie.
Jetzt wo ich seine Lösung sehe wird es klar.
Ein Tolles Wochenende wünscht
Anton