InEin weiterer Beitragwurde eine Formel angegeben, die dabei helfen sollte, einen Eintrag zu suchen und einzufügen, wenn das mit dem Eintrag verknüpfte Datum vor dem Datum auf einem anderen Blatt liegt. Die Formel lautete wie folgt:
=IF(
INDEX([LargerSheet!*Range with Dates*],
MATCH(
IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
[SmallerSheet!*Range with Dates]),
[SmallerSheet!*Range with Dates*],0)
)>0,1,"")
Dies funktionierte jedoch nicht ganz wie gewünscht. Ich frage mich, ob mein Problem vielleicht einfach ein wenig anders ist. Im Wesentlichen möchte ich für einen bestimmten Eintrag im SmallerSheet die mit mehreren Objekten im LargerSheet verknüpften Daten überprüfen und das aktuellste Datum aus dem LargerSheet nur dann zurückgeben, wenn das aktuellste Datum < SmallerSheet-Datum ist. Ich denke, dass die obige Formel derzeit prüft, ob das SmallerSheet-Datum aktueller ist als mindestens eines der Daten im LargerSheet, was dazu führt, dass die Aussage immer wahr ist, da jedem SmallerSheet-Eintrag mehrere Einträge im LargerSheet zugeordnet sind, die sehr weit in die Vergangenheit zurückreichen. Ist das eine einfache Lösung?
Antwort1
Vorausgesetzt, Sie können die Daten in Ihrem LargerSheet sortieren, können Sie Ihr Problem folgendermaßen lösen:
Führen Sie zunächst eine benutzerdefinierte Sortierung auf LargerSheet durch; sortieren Sie zuerst nach Name (AZ) und dann nach Datum (ältester bis neuester). Jetzt werden alle Einträge mit gleichem Namen gruppiert und der letzte Eintrag in jeder Gruppe ist das aktuellste Datum für diesen Namen.
--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :
Verwenden Sie dann im kleineren Blatt in einer Spalte neben jedem Namen die folgende Formel (unter der Annahme, dass sich der Name wie im größeren Blatt in Spalte A und das Datum in Spalte B befindet). Entfernen Sie Leerzeichen und Kommentare.
=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.
Dies sollte das aktuellste Datum aus dem größeren Blatt zurückgeben, wenn es vor dem Datum aus dem kleineren Blatt liegt (für jeden Namen), oder das Datum aus dem kleineren Blatt, wenn dies nicht der Fall ist.
Wenn es notwendig ist, weitere Bedingungen für die Datumsauswahl festzulegen, versuchen Sie es mit den logischen Funktionen „UND“ und „ODER“ oder ändern Sie den Test selbst. Die folgende Änderung der Formel gibt beispielsweise das aktuellste Datum aus dem größeren Blatt zurück, solange es innerhalb von -2 Tagen vom Datum im kleineren Blatt abweicht, UND solange der Wert in der Spalte C des größeren Blatts größer als Null ist. Wie Sie sehen, beginnt die Formel unhandlich zu wirken, daher sollten Sie darauf achten, Klammern richtig zu verwenden und die Syntax zu überprüfen. Die Verwendung mehrerer Spalten zum Aufteilen der Formel in Phasen kann hilfreich sein.
=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)
Schließlich können Sie das Problem auch andersherum angehen: Fügen Sie dem größeren Blatt eine Spalte hinzu, die eine Suche nach dem einzelnen Eintrag für den Namen im kleineren Blatt durchführt, führen Sie einen Test durch und geben Sie basierend auf dem Test einen WAHR/FALSCH-Wert zurück.
Antwort2
Ich gebe Ihnen eine Formel, die anders funktioniert als die von Ihnen aufgelistete, da MATCH die erste Funktion zurückgibt.
Bei dieser Formel handelt es sich um eine Arrayformel, sie wird also wie folgt eingegeben:STRG+UMSCHALT+EINGABE.
Dies setzt voraus, dass sich Ihre große Tabelle in Spalte A befindet. Das Datum, das Sie vergleichen möchten, befindet sich in Zelle E2.
=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))
Für jede Zelle in der großen Tabelle wird eine if-Anweisung ausgeführt. Wenn die Zelle kleiner als E2 ist, wird ihr Datum zurückgegeben (das Excel als Zahl speichert), wenn die Zelle größer als E2 ist, wird 0 zurückgegeben. Indem das Maximum aller dieser if-Anweisungen genommen wird, wird das größte Datum zurückgegeben, das kleiner als E2 ist.