Generieke formule
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Samenvatting
Om de positie (rij) van de laatste bestandsrevisie in een tabel te vinden, kunt u een formule gebruiken die is gebaseerd op verschillende Excel-functies: MAX, IF, ISFOUT, RIJ en INDEX.
In het getoonde voorbeeld is de formule in cel H6:
(= MAX (ALS (ISFOUT (SEARCH (H5 & "*", bestanden)), 0, RIJ (bestanden) -ROW (INDEX (bestanden, 1,1)) + 1)))
waarbij "bestanden" het benoemde bereik C4: C11 is.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Context
In dit voorbeeld hebben we een aantal bestandsversies vermeld in een tabel met een datum en gebruikersnaam. Merk op dat bestandsnamen worden herhaald, behalve de code die aan het einde is toegevoegd om de versie weer te geven ("CA", "CB", "CC", "CD", enz.).
Voor een bepaald bestand willen we de positie (rijnummer) voor de laatste revisie lokaliseren. Dit is een lastig probleem, omdat de versiecodes aan het einde van de bestandsnamen het moeilijker maken om overeen te komen met de bestandsnaam. Standaard retourneren Excel-matchformules ook de eerste match, niet de laatste match, dus we moeten die uitdaging omzeilen met een aantal lastige technieken.
Uitleg
In de kern van deze formule bouwen we een lijst met rijnummers voor een bepaald bestand. Vervolgens gebruiken we de MAX-functie om het grootste rijnummer te krijgen, dat overeenkomt met de laatste revisie (laatste instantie) van dat bestand.
Om alle exemplaren van een bepaald bestand te vinden, gebruiken we de SEARCH-functie, geconfigureerd met het asterisk (*) jokerteken dat overeenkomt met de bestandsnaam, waarbij we de versiecodes negeren. SEARCH genereert een VALUE-fout als er geen tekst wordt gevonden, dus we plaatsen de zoekopdracht in ISERROR:
ISERROR(SEARCH(H5&"*",files))
Dit resulteert in een reeks TRUE en FALSE waarden als volgt:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Het is verwarrend, maar TRUE staat voor een fout (tekst niet gevonden) en FALSE staat voor een overeenkomst. Dit matrixresultaat wordt in de ALS-functie ingevoerd als de logische test. Voor waarde als WAAR, gebruiken we nul, en voor waarde als waar, we leveren deze code, die relatieve rijnummers genereert voor het bereik waarmee we werken:
ROW(files)-ROW(INDEX(files,1,1))+1)
De ALS-functie retourneert vervolgens een reeks waarden zoals deze:
(1; 0; 3; 4; 0; 0; 7; 0)
Alle nummers behalve nul vertegenwoordigen overeenkomsten voor "filename1" - dat wil zeggen het rijnummer binnen de genoemde reeks "files" waar "filename1" verschijnt.
Ten slotte gebruiken we de MAX-functie om de maximale waarde in deze array te krijgen, die in dit voorbeeld 7 is.
Gebruik INDEX met dit rijnummer om informatie op te halen met betrekking tot de laatste revisie (dwz volledige bestandsnaam, datum, gebruiker, enz.).
Zonder benoemd bereik
Benoemde bereiken maken het snel en eenvoudig om een complexere formule in te stellen, aangezien u celadressen niet met de hand hoeft in te voeren. In dit geval gebruiken we echter een extra functie (INDEX) om de eerste cel van de genoemde reeks "bestanden" te krijgen, wat de zaken een beetje ingewikkelder maakt. Zonder het benoemde bereik ziet de formule er als volgt uit:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
