Bendroji formulė
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Santrauka
Norėdami rasti paskutinės failo peržiūros lentelėje vietą (eilutę), galite naudoti formulę, pagrįstą keliomis „Excel“ funkcijomis: MAX, IF, ISERROR, ROW ir INDEX.
Parodytame pavyzdyje formulė langelyje H6 yra:
(= MAX (IF (ISERROR (PAIEŠKA (H5 & "*", failai)), 0, ROW (failai) -ROW (INDEX (failai, 1,1)) + 1)))
kur „failai“ yra pavadintas diapazonas C4: C11.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Kontekstas
Šiame pavyzdyje turime keletą failų versijų, nurodytų lentelėje su data ir vartotojo vardu. Atminkite, kad failų pavadinimai yra kartojami, išskyrus kodą, pridedamą pabaigoje, kuris atspindi versiją („CA“, „CB“, „CC“, „CD“ ir kt.).
Pateiktam failui norime rasti paskutinės peržiūros poziciją (eilutės numerį). Tai kebli problema, nes failų pavadinimų gale esantys versijų kodai apsunkina failo pavadinimo atitikimą. Be to, pagal numatytuosius nustatymus „Excel“ atitikties formulės grąžins pirmąją, o ne paskutinę rungtynes, todėl turime išspręsti šį iššūkį naudodami keletą keblių metodų.
Paaiškinimas
Šios formulės esme sudarome nurodyto failo eilučių skaičių sąrašą. Tada mes naudojame MAX funkciją, kad gautume didžiausią eilutės numerį, kuris atitinka paskutinį to failo pakeitimą (paskutinį kartą).
Norėdami rasti visus nurodyto failo atvejus, mes naudojame funkciją PAIEŠKA, sukonfigūruotą žvaigždute (*) pakaitos simboliu, kad atitiktų failo pavadinimą, nepaisydami versijų kodų. PAIEŠKA sukels VALUE klaidą, kai tekstas nebus rastas, todėl paiešką apvyniojame ISERROR:
ISERROR(SEARCH(H5&"*",files))
Dėl to gaunama tokių TRUE ir FALSE reikšmių masyvas:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Tai painu, bet TRUE reiškia klaidą (tekstas nerastas), o FALSE - atitikmenį. Šis masyvo rezultatas įtraukiamas į IF funkciją kaip loginį testą. Vertei, jei TRUE, mes naudojame nulį, o reikšmei, jei true, pateikiame šį kodą, kuris generuoja santykinius eilučių skaičius diapazonui, su kuriuo dirbame:
ROW(files)-ROW(INDEX(files,1,1))+1)
Tada funkcija IF grąžina tokių reikšmių masyvą:
(1; 0; 3; 4; 0; 0; 7; 0)
Visi skaičiai, išskyrus nulį, reiškia „failo pavadinimas1“ atitikmenis, ty eilutės numeris pavadintame diapazone „failai“, kuriame rodomas „failo vardas1“.
Galiausiai mes naudojame MAX funkciją, kad gautume maksimalią šio masyvo vertę, kuri šiame pavyzdyje yra 7.
Naudokite INDEX su šios eilutės numeriu, kad gautumėte informaciją, susijusią su paskutiniu pakeitimu (ty visą failo pavadinimą, datą, vartotoją ir kt.).
Be pavadinto diapazono
Pavadinti diapazonai leidžia greitai ir lengvai nustatyti sudėtingesnę formulę, nes jums nereikia įvesti langelių adresų rankiniu būdu. Tačiau šiuo atveju mes naudojame papildomą funkciją (INDEX), kad gautume pirmąjį pavadinto diapazono langelį „failai“, o tai šiek tiek apsunkina. Be nurodyto diapazono formulė atrodo taip:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
