Einen dynamischen Datenbereich ausgeben 17

Durch geschickten Formeleinsatz lassen sich Datenbereiche dynamisch ausgeben
 

Beim Umgang mit größeren Datenmengen stellt sich oft das Problem, dass man nur einen bestimmten Teil der Daten für eine Auswertung benötigt. Excel bietet dazu verschiedene praktische Hilfsmittel, wie z.B. die Filterfunktionen.

Wenn die Quelltabelle Datumswerte enthält und ein bestimmter Datumsbereich dynamisch an einer anderen Stelle ausgegeben werden soll, muss man jedoch auf andere Lösungen zurückgreifen. Eine davon stelle ich heute vor.

Und so geht’s:

Die Aufgabenstellung

Nehmen wir an, wir haben eine Tabelle, in der für jeden einzelnen Tag ein Wert erfasst wird (z.B. der Tagesumsatz). Diese Tabelle wird im Laufe der Zeit natürlich sehr lang.

Nun soll dem Anwender über zwei Datumsfelder die Möglichkeit gegeben werden, nur die Werte innerhalb des angegebenen Datumsbereichs in einer separaten Liste auszugeben, so wie im folgenden Bild:

Ein dynamischer Datenbereich

Ein dynamischer Datenbereich

Wie bekommt man so etwas hin?

Ein wenig Komfort

Es ist immer eine gute Idee, den Bediener ein wenig an der Hand zu nehmen und etwas Komfort bei der Eingabe zu bieten. Daher gestalten wir die beiden Datumsfelder als Dropdown-Felder. Gleichzeitig verhindert man dadurch Fehleingaben. Es sollen also nur Datumsangaben erlaubt sein, die auch tatsächlich in unserer Quelltabelle existieren.

Dazu markieren wir die beiden Eingabefelder (1) und klicken in der Registerkarte “Daten” die Schaltfläche “Datenüberprüfung” (2). Im Dialogfenster wählen wir im Feld “Zulassen” die Option “Liste” (3) und geben als Quelle den Bereich mit den Datumswerten an (4):

Datenüberprüfung einrichten

Datenüberprüfung einrichten

INDEX und VERGLEICH

Um unsere Auswahltabelle mit Leben zu füllen, kommen zwei sehr mächtige Funktionen zum Einsatz: INDEX und VERGLEICH. Dieses Duo stellt jeden SVERWEIS in den Schatten, wie du in diesem Artikel nachlesen kannst.

Die INDEX-Funktion gibt den Wert in einer Matrix zurück, der an einer bestimmten Position steht. Diese Position wird durch einen Index bestimmt. Folgendes Beispiel gibt den 5. Wert in Spalte B zurück:

Die INDEX-Funktion

Die INDEX-Funktion

Die VERGLEICH-Funktion hingegen durchsucht eine Matrix nach einem bestimmten Wert und liefert dann dessen Position in der Tabelle zurück. Im diesem Beispiel suche ich nach Datum in F2 und lasse mir dessen Position in der Spalte A zurückliefern:

Die VERGLEICH-Funktion

Die VERGLEICH-Funktion

Jetzt können wir INDEX und VERGLEICH kombinieren und den Wert zu einem Datum ausgeben lassen:

INDEX und VERGLEICH in Kombination

INDEX und VERGLEICH in Kombination

Der Einfachheit halber verwenden wir die gleiche Kombination, um das Datum auszugeben. Ich habe die beiden Formeln jetzt in die Ausgabetabelle übertragen und damit haben wir schon mal den Anfangswert in unserem Datumsbereich ermittelt:

Das erste Ergebnis

Das erste Ergebnis

Da wir aber einen ganzen Wertebereich ausgeben (von Datum / bis Datum), müssen wir die Formel etwas erweitern. Von-Bis-Bereiche in Excel werden immer durch einen Doppelpunkt getrennt. Wir können also unsere Formel einfach kopieren und müssen nur den Bezug auf das Enddatum von F2 auf F3 ändern:

Einen Bereich (von...bis...) festlegen

Einen Bereich (von…bis…) festlegen

Analog dazu passen wir die Formel für die Werte in Spalte F an:
=INDEX($B$2:$B$366;VERGLEICH($F$2;$A$2:$A$366;0)):INDEX($B$2:$B$366;VERGLEICH($F$3;$A$2:$A$366;0))

Wichtig:
Da wir die Formeln nach unten kopieren, müssen die Verweise auf die beiden Datumsfelder absolut sein, also $F$2 und $F$3.

Bis auf ein paar unschöne #WERT-Fehler sieht das Ergebnis auf den ersten Blick gar nicht so schlecht aus:

Das Ergebnis scheint zu passen...

Des Ergebnis scheint zu passen…

Leider trügt dieser erste Blick! Sobald wir ein wenig mit den Datumsfeldern herumspielen, wird das Dilemma sichtbar:

Schwachpunkte in der Lösung

Schwachpunkte in der Lösung

Die Werte “rutschen” in der Tabelle nach unten und werden schließlich gar nicht mehr angezeigt. Ein ähnliches Problem gibt es, wenn ich Datumswerte vor dem 05.01. anzeigen möchte:

Schwachpunkte in der Lösung

Schwachpunkte in der Lösung

Die Lösung ist aber einfach. Wir müssen diese “normalen” Formeln nur in Matrix-Formeln umwandeln. Dazu markiere ich erst den Bereich mit den Datumsangaben, also E6:E28 und schalte mit der Funktionstaste F2 in den Bearbeitungsmodus. Dann wird die Formel mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen:

Umwandlung in Array-Formeln

Umwandlung in Array-Formeln

Ganz wichtig:
Es muss wirklich zuerst der gesamte Bereich markiert worden sein, bevor die Array-Formeln erzeugt werden, sonst kommt es zu falschen Ergebnissen.

Als Ergebnis werden alle markierten Zellen in Array-Formeln umgewandelt, wie man an den geschweiften Klammern erkennen kann. Das gleiche Spiel wiederholen wir für die Wertespalte F6:F28 und bekommen damit deutlich bessere Ergebnisse:

Das Ergebnis stimmt jetzt

Das Ergebnis stimmt jetzt

Der letzte Feinschliff

Ein paar Tests zeigen, dass die Ergebnisse offensichtlich passen. Bleiben nur noch die unschönen #NV-Einträge in den restlichen Zellen. Leider funktioniert hier die für solche Zwecke geeignete WENNFEHLER-Funktion nicht. Die Array-Funktionen machen ihr hier offensichtlich Probleme:

WENNFEHLER hilft nicht weiter

WENNFEHLER hilft nicht weiter

Aber es bleibt noch die Möglichkeit, die Fehlerwerte über eine bedingte Formatierung auszublenden. Markieren wir dazu den Bereich E6:F28 und legen eine neue Formatierungsregel an:
Start | Bedingte Formatierung | Neue Formatierungsregel.

Formatierungsregel einrichten

Formatierungsregel einrichten

Als Regeltyp wählen wir die unterste Option “Formel zur Ermittlung der zu formatierenden Zellen verwenden” aus und geben folgende Formel ein:
=ISTFEHLER(E6)
Als Formatierung legen wir die weiße Schriftfarbe fest. Und schon sieht unsere Ausgabeliste richtig gut aus:

Die fertige Tabelle

Die fertige Tabelle

Die fertige Datei kannst du dir übrigens hier herunterladen.

Vielleicht fallen dir ja noch ein paar andere Anwendungsmöglichkeiten oder Lösungen zu diesem Problem ein. Dann lass es uns unten in den Kommentaren wissen.

 

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 “Einen dynamischen Datenbereich ausgeben

  • Marcel

    Hallo Martin,

    wichtig zu erwähnen wäre noch, dass die Quelltabelle nach dem Datum sortiert sein muss. Ansonsten ist es ein ausgezeichnetes Beispiel.

    Schöne Grüße
    Marcel

    • Martin Weiß Autor des Beitrags

      Hallo Marcel,

      danke für den guten Hinweis, die Sortierung ist natürlich tatsächlich wichtig.

      Schöne Grüße,
      Martin

  • Andi

    Hallo Martin,
    da musste ich ein wenig schmunzeln: “Die Lösung ist aber einfach” gefolgt von “nur in Matrix-Formeln umwandeln.” 😉
    Ich hätte (wie fast immer) eine Pivtottabelle auf das Ganze losgelassen, da ich mit der Matrix noch immer auf Kriegsfuß stehe.
    Vorteil Pivot: Formatierung/Länge der Tabelle automatisch, Quelldatensortierung egal, Filter bereits integriert, fast unendliche Möglichkeiten
    Nachteil Pivot: Datum in jede Zelle ist einfacher, Formeln besser nutzbar als bei Pivots

    • Martin Weiß Autor des Beitrags

      Hallo Andi,

      wenn ich geschrieben hätte, die Lösung ist schwierig, hätte ich die Leser nur abgeschreckt 🙂
      Und letztendlich ist es ja auch nur eine Tastenkombination.
      Aber ich bin grundsätzlich auch ein Freund von Pivot-Tabellen und es zeigt wieder einmal, dass viele Wege nach Rom führen.

      Schöne Grüße,
      Martin

      • Andi

        Hallo,
        Matrixformeln abtippen und eingeben ist nicht das Problem, sondern zu erkennen, wie man die Matrixformel selbst für das jeweilige Problem gestalten muss bzw überhaupt zu erkennen, dass eine Matrixformel das Problem lösen könnte.

        LG

  • Bastian Burger

    Hallo Martin
    Toll – ich wusste nicht, dass Index auch als Bereich mit Doppelpunkt verwendet werden kann.
    Schade, dass sich der Anzeigebereich nicht dynamisch an die Grösse der Quelldaten anpasst. Was, wenn ich mehr als 366 Tage habe und mehr als 22 Werte anzeigen möchte? Bzw. gar nicht genau weiss, wieviele Werte zwischen den zwei Randbednigungen (Daten) liegen? Das Problem wird von Andy ja schon mit Pivot angetönt, aber gibts hier eine “Index-Matrix”-Lösung?

    Liebe Grüsse,
    Bastian

    • Martin Weiß Autor des Beitrags

      Hallo Bastian,

      der Anzeigebereich lässt sich mit der Formellösung leider nicht dynamisch anpassen. Hier muss man wirklich entsprechend der tatsächlichen Gegebenheiten den Bereich etwas großzügig vorbestimmen. Da ist eine Pivot-Tabelle sicherlich eine gute Alternative.

      Schöne Grüße,
      Martin

      • Tim

        Vielleicht ist das doch möglich. Wenn ich erst die Position des größten Wertes in der ersten Spalte feststelle, kann ich doch diese Position als dynamischen Wert in das Fenster für das Dropdown-Formular eintragen. Das wäre dann bei mir:
        =INDIREKT(“$A$2:$A$”&VERGLEICH(MAX(A:A);A:A;0))
        Er nimmt dann immer Spalte A bis zum größten Wert, den er findet. Mit “Indirekt” sollte man dann auch beim Index immer die Matrix anpassen können.

  • Walter Niebling

    Ich finde die Beschreibung und Anleitung super, selbst ich, der noch nicht so gut mit dem Programm zurecht kommt, kann das Beispiel nachvollziehen. Prima danke.

  • Heinz Leonhart

    Hallo Martin.
    Auch ich bin kein Excel Profi, aber durch deine Beiträge habe ich die Hoffnung irgendwann einer zu werden. Tolles Beispiel und toll erklärt.
    Kommt auch zum richtigen Augenblick, da ich an so einer Jahresübersicht mit selktivem Datumsbereich arbeite.
    Wofür ich noch eine Lösung suche, wenn in Spalte “B” kein Wert eingetragen ist, will ich die Zeile nicht angezeigt bekommen.
    Einige Kommentare verweisen auf Pivot, damit habe ich noch gar nicht gearbeitet, aber könnte man es mit Pivot lösen?
    Liebe Grüße und Danke für die Anregungen.
    Gruß Heinz.

    • Martin Weiß Autor des Beitrags

      Hallo Heinz,

      ja, eine Pivot-Tabelle wären dafür definitiv gut geeignet. Markiere die Quelltabelle und erstelle eine Pivot-Tabelle (Menü Einfügen | PivotTable)
      In den Zeilenbereich ziehst Du das Feld “Datum”, in den Wertebereich das Feld “Wert” (oder wie immer das Feld bei Dir heißt). Innerhalb der Pivot-Tabelle kannst Du dann nach dem gewünschten Datumsbereich filtern.

      Schöne Grüße,
      Martin

  • Silke Reinhold

    Hallo Martin,
    das sieht ja wirklich super aus und könnte für mich sehr nützlich sein.
    (bin übrigens immer wieder begeistert von Deinen kleinen Schulungen)
    Nur leider funktioniert es bei mir nicht (Excel 2016)
    Bis zum ersten (Ausgabe) -Datum ist die MATRIX-Formel toll. Ein Kopieren nach unten bewirkt leider nur das vollständige Kopieren des Ergebnisses. 🙁 Die Formel an sich ändert sich ja nicht. Also ist das Eingabe – ENDE- Datum (F3) bei mir ohne Funktion!?
    Was mache ich denn falsch?
    Die Formel ist doch aber richtig!? Einziger Unterschied bei mir: ich habe hier eine “intelligente Tabelle”. Das sollte doch aber nicht das Problem sein.
    =INDEX(Tabelle1[Datum];VERGLEICH($F$2;Tabelle1[Datum];0)):INDEX(Tabelle1[Datum];VERGLEICH($F$3;Tabelle1[Datum];0))

    Es wäre so cool, wenn das bei mir funktionieren würde.

    Viele Grüße
    Silke.

    • Martin Weiß Autor des Beitrags

      Hallo Silke,

      könnte es vielleicht sein, dass Du diesen kleinen Hinweis im Text überlesen hast:

      “Ganz wichtig:
      Es muss wirklich zuerst der gesamte Bereich markiert worden sein, bevor die Array-Formeln erzeugt werden, sonst kommt es zu falschen Ergebnissen.”

      Nur so eine Vermutung… 🙂

      Schöne Grüße,
      Martin

      • Silke Reinhold

        Tausend Dank, Martin!
        Das klappt jetzt, verstehe es aber nicht wirklich so richtig.
        Ich hatte den Hinweis zwar nicht überlesen, aber ignoriert. In allen Zellen waren ja die geschweiften Klammern drin …
        dann war das für mich logisch, dass es funktionieren muss.
        Aber offenbar sage ich diesem Ausgabebereich mit dem vorherigen Markieren ALLER Zellen, dass so alles zusammenhängt.
        Das ist echt ein Excel-Geheimnis. Muss ich mir zu Nutze machen 🙂 DANKE, DANKE, …
        Viele Grüße.
        Silke