Generisk formel
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Resumé
For at finde placeringen (række) for den sidste filrevision i en tabel kan du bruge en formel baseret på flere Excel-funktioner: MAX, IF, ISERROR, ROW og INDEX.
I det viste eksempel er formlen i celle H6:
(= MAKS (HVIS (FEJL (SØG (H5 & "*", filer)), 0, RÆDE (filer) -ROW (INDEKS (filer, 1,1)) + 1)))
hvor "filer" er det navngivne område C4: C11.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Sammenhæng
I dette eksempel har vi et antal filversioner opført i en tabel med en dato og et brugernavn. Bemærk, at filnavne gentages bortset fra den kode, der er tilføjet i slutningen for at repræsentere version ("CA", "CB", "CC", "CD" osv.).
For en given fil ønsker vi at finde positionen (række nummer) til den sidste revision. Dette er et vanskeligt problem, fordi versionskoderne i slutningen af filnavnene gør det sværere at matche på filnavnet. Som standard returnerer Excel-matchformler det første match, ikke det sidste match, så vi er nødt til at omgå den udfordring med nogle vanskelige teknikker.
Forklaring
Kernen i denne formel bygger vi en liste over rækkenumre for en given fil. Derefter bruger vi MAX-funktionen til at få det største række nummer, der svarer til den sidste revision (sidste forekomst) af den fil.
For at finde alle forekomster af en given fil bruger vi SEARCH-funktionen, konfigureret med asterisk (*) jokertegnet til at matche filnavnet, idet vi ignorerer versionskoderne. SØGNING kaster en VALUE fejl, når teksten ikke findes, så vi indpakker søgningen i FEJL:
ISERROR(SEARCH(H5&"*",files))
Dette resulterer i en række SANDE og FALSKE værdier som denne:
(FALSK; SAND; FALSK; FALSK; SAND; SAND; FALSK; SAND)
Det er forvirrende, men SAND repræsenterer en fejl (tekst ikke fundet), og FALSK repræsenterer et match. Dette array-resultat føres ind i IF-funktionen som den logiske test. For værdi, hvis SAND, bruger vi nul, og for værdi, hvis sand, leverer vi denne kode, der genererer relative rækkenumre for det område, vi arbejder med:
ROW(files)-ROW(INDEX(files,1,1))+1)
IF-funktionen returnerer derefter en række værdier som denne:
(1; 0; 3; 4; 0; 0; 7; 0)
Alle tal undtagen nul repræsenterer matches for "filnavn1" - dvs. række nummer inden for det navngivne interval "filer", hvor "filnavn1" vises.
Endelig bruger vi MAX-funktionen til at få den maksimale værdi i dette array, som er 7 i dette eksempel.
Brug INDEX med dette række nummer til at hente oplysninger relateret til den sidste revision (dvs. fuldt filnavn, dato, bruger osv.).
Uden navngivet rækkevidde
Navngivne intervaller gør det hurtigt og nemt at oprette en mere kompleks formel, da du ikke behøver at indtaste celleadresser manuelt. I dette tilfælde bruger vi dog en ekstra funktion (INDEX) til at hente den første celle i de navngivne rækkevidde "filer", hvilket komplicerer tingene lidt. Uden det navngivne interval ser formlen sådan ud:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
