Eine spezielle Verkettung von Zellen 17

Ein praktischer Anwendungsfall für das Zusammenfassen von mehreren Zellen
 

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

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

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

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

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

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

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

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.

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

17 Gedanken zu “Eine spezielle Verkettung von Zellen

  • Friedrich

    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.

    • Martin Weiß Autor des Beitrags

      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

  • Hannes

    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

    • Martin Weiß Autor des Beitrags

      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

      • Hannes

        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

          • Martin Weiß Autor des Beitrags

            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

    • Hansi

      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

      • Hannes

        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

  • Irina Batashov

    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

    • Martin Weiß Autor des Beitrags

      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

  • Giovanni

    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

    • Martin Weiß Autor des Beitrags

      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