Üldine valem
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Kokkuvõte
Viimase failiversiooni tabelis tabeli (rea) asukoha leidmiseks võite kasutada valemit, mis põhineb mitmel Exceli funktsioonil: MAX, IF, ISERROR, ROW ja INDEX.
Näidatud näites on lahtris H6 valem:
(= MAX (KUI (ISERROR (OTSI (H5 & "*", failid)), 0, RIDA (failid) -ROW (INDEX (failid, 1,1)) + 1)))
kus "failid" on nimega vahemik C4: C11.
Märkus: see on massiivi valem ja see tuleb sisestada juhtklahviga + shift + enter.
Sisu
Selles näites on meil tabelis loetletud arv failiversioone koos kuupäeva ja kasutajanimega. Pange tähele, et failinimesid korratakse, välja arvatud lõpus lisatud kood, mis tähistab versiooni ("CA", "CB", "CC", "CD" jne).
Antud faili puhul tahame leida viimase redaktsiooni asukoha (rea numbri). See on keeruline probleem, sest failinimede lõpus olevad versioonikoodid muudavad failinime sobitamise keerulisemaks. Samuti tagastavad Exceli vastevalemid vaikimisi esimese, mitte viimase vaste, seega peame selle väljakutse mõne keeruka tehnikaga ümber töötama.
Selgitus
Selle valemi keskmes koostame loendi antud faili ridade numbritest. Seejärel kasutame funktsiooni MAX, et saada suurim rea number, mis vastab selle faili viimasele versioonile (viimane kordus).
Antud faili kõigi esinemiste leidmiseks kasutame funktsiooni SEARCH, mis on konfigureeritud tärniga (*) metamärgiga, et see vastaks failinimele, eirates versioonikoode. OTSING loob vea VALUE, kui teksti ei leita, seega mässime otsingu ISERROR-i:
ISERROR(SEARCH(H5&"*",files))
Selle tulemuseks on massiivi TRUE ja FALSE sellised väärtused:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
See on segane, kuid TRUE tähistab viga (teksti ei leitud) ja FALSE tähistab vastet. See massiivi tulemus sisestatakse IF-funktsiooni loogilise testina. Väärtuseks, kui TRUE, kasutame nulli ja väärtuseks, kui see on tõene, esitame selle koodi, mis genereerib suhtelise rea numbrid vahemikule, millega töötame:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funktsioon IF tagastab seejärel järgmiste väärtuste massiivi:
(1; 0; 3; 4; 0; 0; 7; 0)
Kõik numbrid, välja arvatud null, tähistavad faili "failinimi1" vasteid - st rea numbrit nimega vahemikus "failid", kus kuvatakse "failinimi1".
Lõpuks kasutame selle massiivi maksimaalse väärtuse saamiseks funktsiooni MAX, mis on selles näites 7.
Kasutage selle rea numbriga INDEX-i, et hankida viimase redaktsiooniga seotud teave (nt faili täielik nimi, kuupäev, kasutaja jne).
Ilma nimetatud vahemikuta
Nimega vahemikud muudavad keerukama valemi seadistamise kiireks ja lihtsaks, kuna te ei pea lahtriaadresse käsitsi sisestama. Kuid sel juhul kasutame nimega vahemiku esimese lahtri "failid" saamiseks lisafunktsiooni (INDEX), mis muudab asja veidi keerulisemaks. Ilma nimetatud vahemikuta näeb valem välja selline:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
