Formuła ogólna
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Podsumowanie
W przypadku dużych zestawów danych WYSZUKAJ.PIONOWO z dopasowaniem ścisłym może być boleśnie powolne, ale funkcja WYSZUKAJ.PIONOWO błyskawicznie się rozjaśnia, używając dwóch WYSZUKAJ.PIONOWO, jak wyjaśniono poniżej.
Uwagi:
- Jeśli masz mniejszy zestaw danych, takie podejście jest przesadą. Używaj go tylko z dużymi zestawami danych, gdy liczy się prędkość.
- Aby ta sztuczka zadziałała, musisz posortować dane według wartości wyszukiwania.
- W tym przykładzie zastosowano nazwane zakresy. Jeśli nie chcesz używać nazwanych zakresów, użyj zamiast tego odwołań bezwzględnych.
WYSZUKAJ.PIONOWO z dopasowaniem ścisłym jest powolne
Gdy używasz funkcji WYSZUKAJ.PIONOWO w „trybie dopasowania ścisłego” dla dużego zestawu danych, może to naprawdę spowolnić czas obliczeń w arkuszu. Przy, powiedzmy, 50 000 lub 100 000 rekordów obliczenia mogą zająć kilka minut.
Dokładne dopasowanie ustawia się, podając FALSE lub zero jako czwarty argument:
=VLOOKUP(val,data,col,FALSE)
Powodem, dla którego funkcja WYSZUKAJ.PIONOWO w tym trybie jest powolna, jest to, że musi on sprawdzać każdy pojedynczy rekord w zestawie danych, aż do znalezienia dopasowania. Nazywa się to czasem przeszukiwaniem liniowym.
Funkcja VLOOKUP z przybliżonym dopasowaniem jest bardzo szybka
W trybie przybliżonego dopasowania funkcja WYSZUKAJ.PIONOWO jest niezwykle szybka. Aby użyć funkcji WYSZUKAJ.PIONOWO z przybliżonym dopasowaniem, musisz posortować dane według pierwszej kolumny (kolumny odnośnika), a następnie określić wartość TRUE dla czwartego argumentu:
=VLOOKUP(val,data,col,TRUE)
(WYSZUKAJ.PIONOWO domyślnie jest ustawione na prawda, co jest przerażającą wartością domyślną, ale to już inna historia).
Przy bardzo dużych zestawach danych zmiana na przybliżone dopasowanie WYSZUKAJ.PIONOWO może oznaczać dramatyczny wzrost prędkości.
Więc, bez myślenia, prawda? Po prostu posortuj dane, użyj przybliżonego dopasowania i gotowe.
Nie tak szybko (heh).
Problem z WYSZUKAJ.PIONOWO w trybie „przybliżonego dopasowania” jest następujący: WYSZUKAJ.PIONOWO nie wyświetli błędu, jeśli wartość wyszukiwania nie istnieje. Co gorsza, wynik może wyglądać zupełnie normalnie, nawet jeśli jest całkowicie błędny (patrz przykłady). Nie jest to coś, co chcesz wyjaśnić swojemu szefowi.
Rozwiązaniem jest dwukrotne użycie funkcji WYSZUKAJ.PIONOWO, za każdym razem w trybie przybliżonego dopasowania:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Wyjaśnienie
Pierwsze wystąpienie funkcji WYSZUKAJ.PIONOWO po prostu wyszukuje wartość wyszukiwania ( identyfikator w tym przykładzie):
=IF(VLOOKUP(id,data,1,TRUE)=id
i zwraca wartość TRUE tylko wtedy, gdy zostanie znaleziona wartość wyszukiwania. W takim przypadku
formuła ponownie uruchamia funkcję WYSZUKAJ.PIONOWO w trybie przybliżonego dopasowania, aby pobrać wartość z tej tabeli:
VLOOKUP(id,data,col,TRUE)
Nie ma niebezpieczeństwa braku wartości wyszukiwania, ponieważ pierwsza część formuły została już sprawdzona, aby upewnić się, że tam jest.
Jeśli wartość wyszukiwania nie zostanie znaleziona, zostanie uruchomiona część funkcji JEŻELI „wartość, jeśli FAŁSZ” i możesz zwrócić dowolną wartość. W tym przykładzie używamy NA (), zwracamy błąd # N / A, ale możesz również zwrócić komunikat typu „Missing” lub „Not found”.
Pamiętaj: aby ta sztuczka zadziałała, musisz posortować dane według wartości wyszukiwania.
