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
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
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 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
Jetzt können wir INDEX und VERGLEICH kombinieren und den Wert zu einem Datum ausgeben lassen:
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
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
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:
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
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
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
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
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
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
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 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.
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.
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
Hallo Marcel,
danke für den guten Hinweis, die Sortierung ist natürlich tatsächlich wichtig.
Schöne Grüße,
Martin
Hallo Martin,
jederzeit gerne. Auch doppelte Einträge (z.B. 2 mal das gleiche Datum) dürfen nicht vorkommen.
Schöne Grüße
Marcel
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
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
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
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
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
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.
Sorry, sehe gerade, dass es auch um den Anzeigebereich und nicht nur um den Quelldatenbereich geht. Wieder zu schnell gelesen…
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.
Gern geschehen!
Schöne Grüße,
Martin
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.
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
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.
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
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
Hallo Martin,
toller Beitrag und tolle Beschreibung.Funktioniert das auch mit Libre Office ? Bei mir gibt es nur #WERT !!
Der Download geht aber prima mit Libre Office ??
Gruss Peter
Hallo Peter,
zu Libre Office kann ich leider überhaupt nichts sagen, da ich das Programm nicht einsetze.
Schöne Grüße,
Martin
Hallo Martin,
lese Deine Quickies mit großem Interesse, so auch diese Problemstellung bzw. Lösung.
Wäre ich ja nie drauf gekommen.
Habe jedoch eine Frage:
Die Bedingte Formatierung bewirkt ja lediglich, daß die Schriftfarbe weiß wird, so die zellen keine Werte enthalten.
Im Prinzip steht da aber nach wie vor „#NV“, was es einem nicht möglich macht, am Ende der Umsatzspalte (=F29) eine Summe zu ziehen.
Gibt es da noch einen Trick?
Beste Grüße
Jens
Moin Martin,
wollte nur vermelden, daß ich es geschafft habe. Meine Lösung: ich habe die Umsätze lediglich mit einer „einfachen“ Index/Vergleich formel ausgegeben, somit funktioniert auch „wennfehler“, und demzufolge auch die Summe am Ende.
Passt also 🙂
Hallo Martin,
vielen Dank für die hilfreichen Anleitungen!
Vielleicht kannst du mir auf die Sprünge helfen, mit welchen Funktionen ich eine Lösung für folgendes Problem finden könnte.
Meine Werte verteilen sich unregelmäßig über die Zeilen einer Spalte. Ungefähr so wie unten in Spalte H.
Für jeden Wert in Spalte H will ich prüfen, ob er kleiner ist als der vorherige UND der folgendende.
Wenn es keinen Wert gibt, wird nichts geprüft.
Wenn die Bedingung erfüllt ist, dann kopiere ich den Wert in die nächste Spalte I.
Sonst bleibt Spalte I leer.
Kann ich VERGLEICH und INDEX dynamisch kombinieren, um mein Ziel zu erreichen? Irgendeine Idee, in welche Richtung ich am besten suchen könnte? Vermutlich hast du es schon beschrieben, und mir fehlt nur der Suchbegriff.
Vielen Dank im Voraus
Christoph
H I
I33
26,19 I34
I35
I36
23,89 I37 ist Wahr: H37 < H34 UND H37 H45
22,59 I45 erfüllt Bedingung wieder
24,43
Hallo Christoph,
wenn ich es also richtig verstehe, können die einzelnen Werte in Spalte H beliebig weiter auseinander liegen? Ich fürchte, da habe ich auf Anhieb leider auch keinen Tipp für eine Lösung…
Vielleicht hat ja ein anderer Leser eine Idee?
Schöne Grüße,
Martin
Hallo Martin,
ich habe eine kleine Hürde in Sachen Excel. Ich hoffe ich kann es einigermaßen klar beschreiben welche Ausgangsdaten ich erstellt habe und wo die Reise hingehen soll:
Ich hab ein Tabellenblatt, das von einer Kollegin und mir stetig gepflegt wird. Es werden hier Veranstaltungsdaten (Datum, Zeit, Ort, Veranstaltungstitel, Personalmenge, etc.) eingepflegt. Aus diesem Tabellenblatt 1 möchte ich in Tabellenblatt 2 eine gesonderte Übersicht für unser Personal erstellen. Ich habe also zwei Dropdown-Felder „von“ und „bis“ in Bezug auf das Veranstaltungsdatum (aus TB1) in TB2 erstellt. Ich möchte hier einen bestimmten Zeitraum festlegen, wie z. B. eine gesamte Woche: 03.06.-09.06.2019. Anhand dieser Auswahl sollen die darunter stehenden Zellen mit den Daten aus TB1 befüllt werden. Ich konnte per Verweis die gewünschte Ansicht herstellen. Allerdings nur mit dem Suchkriterium aus dem Feld „von“-Datum. Es zeigt mir dann darunter durchaus alle gewünschten Veranstaltungsdaten ab diesem „von“-Datum an, schränkt es aber nicht gemäß dem „bis“-Datum ein. Ich habe diverse Möglichkeiten, Formeln etc. versucht. Ich scheitere aber immer wieder daran, dass ich eben die Suchkriterium als Dropdown erstellt habe. Vielleicht hast du noch einen Tipp für mich welche Formel ich hier hinterlegen kann?
Hallo Sandra,
das hört sich für mich eigentlich ähnlich zum dem an, was ich im Artikel beschrieben habe, nur eben mit mehr Spalten. Hast du dir die Beispieldatei zu diesem Artikel heruntergeladen? Dort werden die Datumsfelder „Von“ und „Bis“ ebenfalls per Dropdown ausgewählt, das funktioniert ohne Probleme.
Voraussetzung ist lediglich, das die Einträge in der Quelltabelle auch nach Datum sortiert sind, wie Marcel oben im ersten Kommentar schon schreibt.
Oder habe ich die Aufgabenstellung falsch verstanden?
Schöne Grüße,
Martin
Hallo Martin,
Ich hab mich die letzten Tage intensiv mit dem Thema beschäftigt. Da ich fast nur mit formatierten Tabellen arbeite, musste ich eine andere Lösung finden.
Dazu habe ich als erste Spalte „ID“ vorangesetzt und eine Formel entwickelt die man nicht in eine Matrixformel wandeln muss.
In der Ausgabe habe ich somit in der Spalte Datum folgende Formel:
=WENNFEHLER(WENN(INDEX(tbl_02[#Daten];VERGLEICH(VERGLEICH(von;tbl_02[Datum])+(ZEILE()-5);tbl_02[ID];0);1)>VERGLEICH(bis;tbl_02[Datum]);““;INDEX(tbl_02[#Daten];VERGLEICH(VERGLEICH(von;tbl_02[Datum])+(ZEILE()-5);tbl_02[ID];0);2));““)
In der Spalte Wert wird einfach die letzte „2“ gegen eine „3“ getauscht und schon hat man alle werte „Datum“ & „Wert“ dynamisch aufgelistet.
Offtopic: Finde deinen Blog sehr gut und hilfreich.
Best regards,
Olli
Hallo Oliver,
danke für dein nettes Feedback und die alternative Lösung. Ich habe sie selbst zwar noch nicht ausprobiert, aber sie sieht sehr interessant aus!
Schöne Grüße,
Martin
Hallo zusammen,
ich habe folgende Anforderung an meine Excel-Tabelle:
Bsp. Ich habe eine Tabelle mit den Spaltenüberschriften: Baden-Wüerttemberg und Bayern.
In den Spalten stehen jeweils 3 Städte aus den jeweiligen Bundesländern.
Wenn ich nun in meinem ersten Auswahlfeld Bundesland Ba-Wü auswähle, werden im Dropdown-Menü des zweiten Auswahlfeldes Städte auch die drei Städte aus Ba-Wü angezeigt. Wähle ich nun hier z.B. Karlsruhe aus und anschließend in meinem Bundesland-Auswahlfeld Bayern, dann steht da Bayern und Karlsruhe.
Meine Anforderung wäre nun, dass wenn das Bundesland geändert wird, das Feld mit der Stadt auf Null gesetzt wird.
Wie mache ich das?
Vielen Dank und Viele Grüße
ts
Hallo Tobias,
das ist ohne VBA-Programmierung nicht möglich. Denkbar ist aber, das Feld mit dem jetzt falschen Eintrag über eine bedingte Formatierung einzufärben (oder die Schrift auf weiß zu stellen). Die Regel dafür wäre ein SVERWEIS, der prüft, ob der eingetragene Wert in der Liste des betreffenden Bundeslandes enthalten ist.
Schöne Grüße,
Martin
Hallo Martin,
super Idee!
Vielen Dank für deine Hilfe und deine schnelle Rückmeldung.
Gruß
Tobias
Hallo zusammen, ich versuche aktuell aus einer liste die ständig aktualisiert wird: Folgendes Schema
1 Category lessor Wert1 wert 2 wert3 …
und einige category kommen öfters vor mit anderen werten.
Nun will ich eine formel aufsetzen die mir wenn der neue eintrag schon in der liste vorkommt mit die werte aller doppelten einträge ausgibt:
Bislang hab ich das =WENN(D16:D17=““;““;VERGLEICH(D16;D2:D15;0))hier gibt er mir nur die Zeile aus in der der doppelte eintrag drin ist, jedoch nur den 1.Doppelten Eintrag aber nicht den 2.Doppelten. Und jetzt würde ich gerne wie gesagt noch die werte für jeden eintrag ausgeben. Hoffe ich hab es einigermaßen verständlich erklärt. Besten Dank
Hallo Marcel,
mir ist zwar noch nicht ganz klar, wo genau diese doppelten Werte auftauchen sollen, aber ich starte mal einen Versuch. Die neue FILTER-Funktion aus Microsoft 365 könnte vielleicht weiterhelfen. Hier ein Beispiel:
Es wird der Wert in Zelle D8 geprüft und dann alle Einträge aus der Tabelle ab Zelle F2 ausgegeben. Wenn der Wert noch nicht in der Tabelle enthalten ist, wird nur ein Strich ausgegeben.
Vielleicht hilft das weiter.
Schöne Grüße,
Martin