Formuła ogólna
=LOOKUP(2,1/(ISNUMBER(FIND(filename,range))),range)
Podsumowanie
Aby wyszukać najnowszą wersję pliku na liście, możesz użyć formuły opartej na funkcji WYSZUKAJ wraz z funkcjami CZY.LICZBA i ZNAJDŹ. W przedstawionym przykładzie formuła w komórce G7 to:
=LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files)
gdzie „pliki” to nazwany zakres B5: B11.
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 licznikiem na końcu jako numer wersji - 001, 002, 003 itd.
Biorąc pod uwagę nazwę pliku, chcemy pobrać nazwę ostatniej lub najnowszej wersji. Istnieją dwa wyzwania:
- Wyzwaniem jest to, że kody wersji na końcu nazw plików utrudniają dopasowanie nazwy pliku.
- Domyślnie formuły dopasowania programu Excel zwracają pierwsze dopasowanie, a nie ostatnie dopasowanie.
Aby sprostać tym wyzwaniom, musimy użyć pewnych skomplikowanych technik.
Wyjaśnienie
Ta formuła używa funkcji WYSZUKAJ, aby znaleźć i pobrać ostatnią pasującą nazwę pliku. Wartość wyszukiwania wynosi 2, a lookup_vector jest tworzony w ten sposób:
1/(ISNUMBER(FIND(G6,files)))
Wewnątrz tego fragmentu funkcja ZNAJDŹ szuka wartości w G6 w nazwanych „plikach” zakresu (B5: B11). Rezultatem jest tablica taka:
(1;#VALUE!;1;1;#VALUE!;#VALUE!;1)
W tym przypadku liczba 1 oznacza dopasowanie, a błąd #ARG oznacza niezgodną nazwę pliku. Ta tablica trafia do funkcji ISNUMBER i wygląda następująco:
(TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Wartości błędów są teraz FAŁSZ, a liczba 1 jest teraz PRAWDA. To rozwiązuje wyzwanie nr 1, mamy teraz tablicę, która jasno pokazuje, które pliki na liście zawierają nazwę pliku będącego przedmiotem zainteresowania.
Następnie tablica jest używana jako mianownik z 1 jako licznikiem. Wynik wygląda następująco:
(1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;1)
która przechodzi do LOOKUP jako lookup_vector. To trudne rozwiązanie dla wyzwania # 2. Funkcja WYSZUKAJ działa tylko w trybie przybliżonego dopasowania i automatycznie ignoruje wartości błędów. Oznacza to, że z 2 jako wartością wyszukiwania, WYSZUKAJ.PIONOWO spróbuje znaleźć 2, zakończy się niepowodzeniem i cofnie się do poprzedniej liczby (w tym przypadku dopasowując ostatnią 1 na pozycji 7). Wreszcie, LOOKUP używa 7 jako indeksu do pobrania siódmego pliku na liście plików.
Obsługa pustych wyszukiwań
Co dziwne, funkcja ZNAJDŹ zwraca 1, jeśli wartość wyszukiwania jest pustym ciągiem („”). Aby ustrzec się przed fałszywym dopasowaniem, możesz zawinąć formułę w JEŻELI i przetestować pod kątem pustego wyszukiwania:
=IF(G6"",LOOKUP(2,1/(ISNUMBER(FIND(G6,files))),files),"")
