Vispārēja formula
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Kopsavilkums
Lai tabulā atrastu pēdējās faila pārskatīšanas pozīciju (rindu), varat izmantot formulu, kuras pamatā ir vairākas Excel funkcijas: MAX, IF, ISERROR, ROW un INDEX.
Parādītajā piemērā formula šūnā H6 ir:
(= MAX (IF (ISERROR (MEKLĒT (H5 & "*", faili)), 0, ROW (faili) -ROW (INDEX (faili, 1,1)) + 1)))
kur "faili" ir nosauktais diapazons C4: C11.
Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + shift + enter.
Konteksts
Šajā piemērā mums ir vairākas failu versijas, kas norādītas tabulā ar datumu un lietotāja vārdu. Ņemiet vērā, ka failu nosaukumi tiek atkārtoti, izņemot kodu, kas pievienots beigās, lai attēlotu versiju ("CA", "CB", "CC", "CD" utt.).
Dotam failam mēs vēlamies atrast pēdējās pārskatīšanas pozīciju (rindas numuru). Tā ir sarežģīta problēma, jo failu nosaukumu beigās esošie versiju kodi apgrūtina faila nosaukuma saskaņošanu. Turklāt pēc noklusējuma Excel spēles formulas atgriezīs pirmo, nevis pēdējo spēli, tāpēc mums ir jāizstrādā šis izaicinājums, izmantojot dažas viltīgas metodes.
Paskaidrojums
Šīs formulas pamatā mēs izveidojam norādīto failu rindu numuru sarakstu. Tad mēs izmantojam funkciju MAX, lai iegūtu vislielāko rindas numuru, kas atbilst šī faila pēdējai pārskatīšanai (pēdējam gadījumam).
Lai atrastu visus konkrētā faila gadījumus, mēs izmantojam funkciju MEKLĒŠANA, kas konfigurēta ar zvaigznīti (*) aizstājējzīmi, lai tā atbilstu faila nosaukumam, ignorējot versiju kodus. MEKLĒŠANA radīs VALUE kļūdu, ja teksts netiks atrasts, tāpēc mēs iesaiņojam meklēšanu ISERROR:
ISERROR(SEARCH(H5&"*",files))
Tā rezultātā tiek iegūts šāds TRUE un FALSE vērtību masīvs:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Tas ir mulsinoši, bet TRUE norāda kļūdu (teksts nav atrasts), un FALSE - atbilstība. Šis masīva rezultāts tiek ievadīts IF funkcijā kā loģiskais tests. Vērtībai, ja TRUE, mēs izmantojam nulli, un vērtībai, ja true, mēs piegādājam šo kodu, kas ģenerē relatīvos rindu numurus diapazonam, ar kuru mēs strādājam:
ROW(files)-ROW(INDEX(files,1,1))+1)
Pēc tam funkcija IF atgriež šādu masīvu:
(1; 0; 3; 4; 0; 0; 7; 0)
Visi skaitļi, izņemot nulli, atbilst faila "faila nosaukums1" atbilstībai, ti, rindas numurs nosauktajā diapazonā "faili", kur parādās "faila nosaukums1".
Visbeidzot, mēs izmantojam funkciju MAX, lai iegūtu maksimālo vērtību šajā masīvā, kas šajā piemērā ir 7.
Izmantojiet INDEX ar šīs rindas numuru, lai izgūtu informāciju par pēdējo pārskatīšanu (ti, pilnu faila nosaukumu, datumu, lietotāju utt.).
Bez nosaukta diapazona
Nosauktie diapazoni atvieglo sarežģītas formulas iestatīšanu, jo jums nav jāievada šūnu adreses ar roku. Tomēr šajā gadījumā mēs izmantojam papildu funkciju (INDEX), lai iegūtu nosaukto diapazonu "faili" pirmo šūnu, kas mazliet sarežģī situāciju. Bez nosaukta diapazona formula izskatās šādi:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
