Formuła ogólna
(=MAX(IF(ISERROR(SEARCH(H5&"*",files)),0,ROW(files)-ROW(INDEX(files,1,1))+1)))
Podsumowanie
Aby znaleźć pozycję (wiersz) ostatniej wersji pliku w tabeli, możesz użyć formuły opartej na kilku funkcjach programu Excel: MAX, JEŻELI, CZY.BŁĄD, WIERSZ i INDEKS.
W przedstawionym przykładzie formuła w komórce H6 to:
(= MAX (JEŻELI (ISERROR (SEARCH (H5 & "*", pliki)), 0, ROW (pliki) -ROW (INDEKS (pliki, 1,1)) + 1)))
gdzie „pliki” to nazwany zakres C4: C11.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Kontekst
W tym przykładzie mamy kilka wersji plików wyświetlonych w tabeli z datą i nazwą użytkownika. Zwróć uwagę, że nazwy plików są powtarzane, z wyjątkiem kodu dołączonego na końcu w celu oznaczenia wersji („CA”, „CB”, „CC”, „CD” itd.).
Dla danego pliku chcemy zlokalizować pozycję (numer wiersza) dla ostatniej rewizji. Jest to trudny problem, ponieważ kody wersji na końcu nazw plików utrudniają dopasowanie do nazwy pliku. Ponadto, domyślnie formuły dopasowań programu Excel zwracają pierwsze dopasowanie, a nie ostatnie dopasowanie, więc musimy obejść to wyzwanie za pomocą pewnych skomplikowanych technik.
Wyjaśnienie
U podstaw tej formuły budujemy listę numerów wierszy dla danego pliku. Następnie używamy funkcji MAX, aby uzyskać największy numer wiersza, który odpowiada ostatniej wersji (ostatniemu wystąpieniu) tego pliku.
Aby znaleźć wszystkie wystąpienia danego pliku, używamy funkcji SEARCH, skonfigurowanej za pomocą symbolu wieloznacznego gwiazdki (*), aby pasowała do nazwy pliku, ignorując kody wersji. SEARCH zgłosi błąd VALUE, gdy tekst nie zostanie znaleziony, więc zawijamy wyszukiwanie w ISERROR:
ISERROR(SEARCH(H5&"*",files))
Daje to tablicę wartości PRAWDA i FAŁSZ w następujący sposób:
(FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE)
Jest to zagmatwane, ale TRUE oznacza błąd (nie znaleziono tekstu), a FALSE oznacza dopasowanie. Ten wynik tablicy jest wprowadzany do funkcji IF jako test logiczny. Dla value, jeśli TRUE, używamy zera, a dla value, jeśli true, dostarczamy ten kod, który generuje względne numery wierszy dla zakresu, z którym pracujemy:
ROW(files)-ROW(INDEX(files,1,1))+1)
Funkcja JEŻELI zwraca następnie tablicę wartości, taką jak ta:
(1; 0; 3; 4; 0; 0; 7; 0)
Wszystkie liczby oprócz zera reprezentują dopasowania dla „filename1” - tj. Numer wiersza wewnątrz nazwanego zakresu „files”, gdzie pojawia się „filename1”.
Na koniec używamy funkcji MAX, aby uzyskać maksymalną wartość w tej tablicy, która w tym przykładzie wynosi 7.
Użyj INDEKSU z tym numerem wiersza, aby pobrać informacje związane z ostatnią wersją (tj. Pełna nazwa pliku, data, użytkownik itp.).
Bez nazwanego zakresu
Nazwane zakresy ułatwiają i przyspieszają konfigurowanie bardziej złożonej formuły, ponieważ nie trzeba ręcznie wprowadzać adresów komórek. Jednak w tym przypadku używamy dodatkowej funkcji (INDEKS), aby pobrać pierwszą komórkę z nazwanego zakresu „pliki”, co nieco komplikuje sprawę. Bez nazwanego zakresu formuła wygląda następująco:
(=MAX(IF(ISERROR(SEARCH(H5&"*",C4:C11)),0,ROW(C4:C11)-ROW(C4)+1)))
