☰

Indizieren eines Datumsbereichs, wenn das Datum größer als ein anderes Datum ist und innerhalb von n Tagen nach diesem liegt

tag-icon • tag-icon microsoft-excel worksheet-function
Indizieren eines Datumsbereichs, wenn das Datum größer als ein anderes Datum ist und innerhalb von n Tagen nach diesem liegt

In einemvorherigen PostIch habe gefragt, wie man das aktuellste Datum findet, das mit einer bestimmten Entität verknüpft ist, und wie man diesen Wert in Sheet2 zurückbekommt, solange er größer als das verknüpfte Datum in Sheet2 ist. Mir ist jedoch klar geworden, dass das eigentliche Problem nicht darin besteht, das aktuellste Datum zurückzugeben, wenn die Bedingung erfüllt ist, sondern das Datum zurückzugeben, wenn es größer als und innerhalb von 2 Tagen (oder im Allgemeinen n Tagen) des Referenzdatums liegt.

Die zuvor ermittelte Formel lautete wie folgt:

=IF(
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)                            //"Else" return SmallerSheet date.

Gibt es keine Möglichkeit, eine weitere Bedingung für INDIRECT() hinzuzufügen, sodass nicht nur abgefragt wird, ob es ist, < $B1sondern auch, ob die Distanz zum Datum beträgt <2?

Ich habe die folgende Formel ohne Erfolg ausprobiert:

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

Ein Musterblatt finden SieHier.

Antwort1

Ich musste Ihre Fragenreihe noch einmal durchgehen, um zu sehen, wo das Problem liegt, aber (mit nur geringfügigen Änderungen)@Hannu's Formelist ziemlich richtig. Das Problem, das Sie bei der Implementierung haben, ist, dass Ihr Tabellenlayout nicht mit dem übereinstimmt, was von @jbmorch in ihremAntwort auf Ihre vorherige Frage.

Das Layout wurde in der Antwort von @jbmorch sehr klar angegeben und ist aus mehreren Gründen wichtig:

  • Sie haben in Ihrer Frage nicht viele Details zu Ihrem Tabellenlayout angegeben, außer den Teilen Ihrer Pseudoformel, die Blattnamen enthielten. Daher mussten @jbmorch und andere spekulieren und ihre eigenen Layouts erstellen, um eine geeignete Formel zusammenzustellen.
  • Wenn Sie die Spalten in Ihrem Blatt nicht wie in der Antwort angegeben anordnen, sind die Verweise auf die Spalten im Blatt nicht korrekt, sofern Sie sie nicht anpassen.
  • Die Sortierreihenfolge auf LargerSheet ist aufgrund der Funktionsweise von MATCH wichtig. Wenn die Zeilen nicht wie in der Antwort angegeben sortiert sind, liefert MATCH keine genauen Ergebnisse.
  • Die Antwort von @jbmorch ging außerdem davon aus (da keine anderen Informationen bereitgestellt wurden), dass Ihre Daten in Zeile 1 begannen. Daher wurde ihre Antwort für eine solche Tabelle geschrieben und führt zu fehlerhaften Ergebnissen, wenn sie nicht an das tatsächliche Layout angepasst wird.

Beachten Sie außerdem noch einige weitere mögliche Probleme:

  • Sie müssen sicherstellen, dass alle Datums-/Zeiteinträge tatsächlich als Datum und Uhrzeit formatiert sind, da Excel sonst keine richtigen Vergleiche durchführen kann. Dies lässt sich in den Formatierungsoptionen unter den Zelleneigenschaften und auch durch mathematische Berechnungen für die Zelle überprüfen. (Beispiel: Wenn A2 enthält 1/7/2003und B2 ist =A2+2, sollte der Wert für B2 zu ergeben 1/9/2003.)
  • Ihr Beispielblatt enthält nur wenige Einträge in Blatt1, die tatsächlich den Kriterien entsprechen, nach denen Sie suchen, um einen Wert aus LargerSheet zurückzugeben. Dies macht die Fehlersuche etwas schwierig. Tatsächlich war der einzige übereinstimmende Eintrag, den ich gefunden habe, Zeile 9. (Das Datum für DAILY, JIM in dieser Zeile in Blatt1 lag tatsächlich innerhalb von zwei Tagen nach dem letzten Datum für ihn in LargerSheet.)

Hier ist die Formel, die Sie brauchen. Legen Sie sie in C2 auf Blatt1 ab und kopieren Sie sie nach unten:

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

Achten Sie auch hier wieder darauf, dass Ihr Blatt formatiert istgenauwie unten, sonst funktioniert die Formel ohne Anpassungen nicht.

  • Alle Terminemussals Datumsangaben formatiert werden – nicht als Text oder Zahlen.
  • Beide BlättermussTragen Sie Namen in Spalte A und Daten in Spalte B ein, wobei die eigentlichen Daten in Zeile 2 beginnen.
  • GrößeresBlattmusssowohl nach Name (aufsteigend) als auch nach Datum (aufsteigend) sortiert werden, mit festgelegter Sortierprioritätin dieser Reihenfolge.

Ein weiterer Punkt, den Sie beachten sollten, ist der Unterschied zwischen „innerhalb von 2 Tagen“ und „innerhalb von 48 Stunden“ – diese Formel verwendet Letzteres. Das heißt, wenn eine Zeit 5/6/2012 03:00:00auf Blatt1 steht und der entsprechende Wert auf größerem Blatt, 5/8/2012 03:00:01dann wird die IF-Anweisung als FALSE ausgewertet und gibt den Wert von Blatt1 zurück, statt den von größerem Blatt. Es wären erhebliche Formeländerungen erforderlich, um zu berücksichtigen, ob Sie „jederzeit in der Zukunft innerhalb der nächsten zwei Tage“ statt „jederzeit innerhalb von 48 Stunden“ abgleichen möchten.

Da die IF-Anweisung außerdem ein exklusives Größer-als-Zeichen ( >) anstelle eines Größer-als-oder-gleich-Zeichens verwendet, wird sie bei genauen Übereinstimmungen als FALSE ausgewertet. Wenn Sie möchten, dass sie bei genauen Übereinstimmungen als TRUE ausgewertet wird, ersetzen Sie sie >durch >=.

Antwort2

HINWEIS: Ich schaue mir gerade Ihr IF() an und ändere die Bedingungsanweisung.
Wenn das bei Ihnen nicht funktioniert, gibt es Ihnen vielleicht zumindest eine Idee, wie Sie es schreiben/ändern können.

=WENN(
     AND(INDIRECT("LargerSheet!$B"& //Zelle beginnt mit "$B" und endet mit
     MATCH($A1,LargerSheet!$A:$A,1) //Zeile mit dem letzten Datum für den Namen.
     )<$B1, //Vergleiche mit SmallerSheet-Datum
     INDIREKT("GrößeresBlatt!$B"& //Zelle beginnt mit "$B" und endet mit
     MATCH($A1,LargerSheet!$A:$A,1) //Zeile mit dem letzten Datum für den Namen.
     )<($B1+2)), //Vergleiche mit SmallerSheet (Datum-2)
     INDIREKT("GrößeresTabellenblatt!$B"& //"Dann" returniere das Datum für das größereTabellenblatt,
     MATCH($A1,GrößeresBlatt!$A:$A,1)
     ),$B1)

... die wichtige Ergänzung ist AND( condition1, condition2 )zwischen IF(und dem ersten ,für das IF.

verwandte Informationen