Generische Formel
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Zusammenfassung
Um die Position (Zeile) der letzten Dateirevision in einer Tabelle zu ermitteln, können Sie eine Formel verwenden, die auf mehreren Excel-Funktionen basiert: MAX, IF, ISERROR, ROW und INDEX.
In dem gezeigten Beispiel lautet die Formel in Zelle H6:
(= MAX (IF (ISERROR (SEARCH (H5 & "*", Dateien)), 0, ROW (Dateien) -ROW (INDEX (Dateien, 1,1)) + 1)))
Dabei ist "Dateien" der benannte Bereich C4: C11.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabe eingegeben werden.
Kontext
In diesem Beispiel sind einige Dateiversionen in einer Tabelle mit Datum und Benutzername aufgeführt. Beachten Sie, dass Dateinamen wiederholt werden, mit Ausnahme des Codes, der am Ende zur Darstellung der Version angehängt wird ("CA", "CB", "CC", "CD" usw.).
Für eine bestimmte Datei möchten wir die Position (Zeilennummer) für die letzte Revision suchen. Dies ist ein heikles Problem, da die Versionscodes am Ende der Dateinamen die Übereinstimmung mit dem Dateinamen erschweren. Außerdem geben Excel-Übereinstimmungsformeln standardmäßig die erste Übereinstimmung zurück, nicht die letzte Übereinstimmung. Daher müssen wir diese Herausforderung mit einigen kniffligen Techniken umgehen.
Erläuterung
Im Kern dieser Formel erstellen wir eine Liste mit Zeilennummern für eine bestimmte Datei. Dann verwenden wir die MAX-Funktion, um die größte Zeilennummer zu erhalten, die der letzten Revision (dem letzten Vorkommen) dieser Datei entspricht.
Um alle Vorkommen einer bestimmten Datei zu finden, verwenden wir die SEARCH-Funktion, die mit dem Sternchen (*) konfiguriert ist, um mit dem Dateinamen übereinzustimmen, wobei die Versionscodes ignoriert werden. SEARCH löst einen VALUE-Fehler aus, wenn kein Text gefunden wird. Daher schließen wir die Suche in ISERROR ein:
ISERROR(SEARCH(H5&"*",files))
Dies führt zu einem Array von TRUE- und FALSE-Werten wie folgt:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Es ist verwirrend, aber TRUE steht für einen Fehler (Text nicht gefunden) und FALSE für eine Übereinstimmung. Dieses Array-Ergebnis wird als logischer Test in die IF-Funktion eingespeist. Für den Wert TRUE verwenden wir Null, und für den Wert true geben wir diesen Code an, der relative Zeilennummern für den Bereich generiert, mit dem wir arbeiten:
ROW(files)-ROW(INDEX(files,1,1))+1)
Die IF-Funktion gibt dann ein Array von Werten wie folgt zurück:
(1; 0; 3; 4; 0; 0; 7; 0)
Alle Zahlen außer Null stellen Übereinstimmungen für "Dateiname1" dar - dh die Zeilennummer innerhalb des benannten Bereichs "Dateien", in denen "Dateiname1" angezeigt wird.
Schließlich verwenden wir die MAX-Funktion, um den Maximalwert in diesem Array zu erhalten, der in diesem Beispiel 7 beträgt.
Verwenden Sie INDEX mit dieser Zeilennummer, um Informationen zur letzten Revision abzurufen (z. B. vollständiger Dateiname, Datum, Benutzer usw.).
Ohne benannten Bereich
Mit benannten Bereichen können Sie schnell und einfach eine komplexere Formel einrichten, da Sie die Zelladressen nicht manuell eingeben müssen. In diesem Fall verwenden wir jedoch eine zusätzliche Funktion (INDEX), um die erste Zelle des genannten Bereichs "files" abzurufen, was die Sache etwas kompliziert macht. Ohne den genannten Bereich sieht die Formel folgendermaßen aus:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
