Pole, które musiała przeszukać, zawierało zmienną liczbę znaków jako prefiks, po którym następował numer czeku, a następnie kończyło się jeszcze większą liczbą znaków. Większość ludzi nie zdaje sobie sprawy, że VLOOKUP obsługuje symbole wieloznaczne. W rzeczywistości, zespół Excela w firmie Microsoft uważa, że symbole wieloznaczne częściej trafiają do VLOOKUP przez przypadek niż przez osoby, które używają ich celowo. Ponieważ większość ludzi nie wie, że VLOOKUP będzie obsługiwał symbole wieloznaczne, bardziej prawdopodobne wydaje się, że ludzie wykonają VLOOKUP, w którym wartość wyszukiwania będzie zawierała znak, który nie był przeznaczony do reprezentowania symbolu wieloznacznego.

UŻYWANIE ZNACZNIKÓW

Standardowe symbole wieloznaczne to gwiazdka (*) oznaczająca dowolną liczbę znaków oraz znak zapytania (?) oznaczający dowolny jeden znak. A co jeśli musisz szukać gwiazdki lub znaku zapytania? Użyj ~* do szukania gwiazdki lub ~? do szukania znaku zapytania. Użyj ~~, aby wyszukać tyldę.

Użycie tych trzech znaków jest szczególnie trudne, jeśli masz setki VLOOKUPów, a wartości zawierają już znaki wieloznaczne. Próba konwersji wartości w locie przy użyciu zagnieżdżonych funkcji SUBSTITUTE staje się nieporęczna. Wyobraź sobie =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, „~”,”~~”), „*”,”~*”),”?”,”~?”). W rzeczywistości byłoby lepiej, gdyby Microsoft zaoferował opcję, która wyłączyła zachowanie wieloznaczne dla konkretnego arkusza lub skoroszytu.

Wracając do pierwotnego problemu. Musiałbyś wyszukać *apple*, aby znaleźć słowo „apple” wewnątrz dowolnego tekstu w pierwszej kolumnie tabeli wyszukującej. Ale ponieważ komórka A2 na rysunku 1 zawiera już słowo „jabłko”, formuła zaczyna się od =VLOOKUP(„*”&A2&”*”. W tym przypadku używasz znaku konkatenacji (&) do zawijania wartości w komórce A2 z gwiazdką po każdej stronie. Alternatywnie, możesz użyć =VLOOKUP(CONCATENATE(„*”,A2, „*”), aby zawinąć słowo „jabłko” w gwiazdki.

Formuła =VLOOKUP(„*”&A2&”*”,$D$2:$E$6,2,FALSE) poprawnie wyszukuje 956$ z komórki E5.

Zachowaj szczególną ostrożność przy przeglądaniu wyników wyszukiwania wieloznacznego. Co by było, gdybyś szukał zarówno „apple”, jak i „crabapple”? Wyszukiwanie dla crabapple byłoby poprawne, ale ponieważ VLOOKUP zwraca pierwsze dopasowanie w tabeli wyszukiwania, VLOOKUP dla *apple* zwróciłby wartość dla crabapple, jeśli zostałby znaleziony przed apple w tabeli wyszukiwania.

ZNAJDOWANIE I SUMOWANIE WSZYSTKICH DOPASOWAŃ

Podczas pracy nad przykładem na seminarium dotyczącym Excela, ktoś inny zapytał o sytuację, w której może istnieć wiele dopasowań i chciałbyś znaleźć i zsumować wszystkie pasujące wyniki.

Ponieważ symbol wieloznaczny *apple* działał tak dobrze z VLOOKUP, stworzyłem zestaw danych, w którym mogliśmy przetestować, czy funkcja SUMIFS również pozwoli na stosowanie symboli wieloznacznych. W funkcji SUMIFS pokazanej poniżej, formuła mówi, aby zsumować wartości w kolumnie E, jeśli odpowiadająca im wartość w kolumnie D zawiera *Apple*. Ta formuła działa równie dobrze.

IMPROVING WILDCARD HANDLING

Czy jesteś zaskoczony, że VLOOKUP może obsługiwać symbole wieloznaczne? Czy kiedykolwiek robiłeś VLOOKUP, w którym wartość wyszukiwania może zawierać gwiazdkę lub znak zapytania jako część normalnego tekstu i nie jest przeznaczona do użycia jako symbol wieloznaczny?

Jeśli odpowiedź na którekolwiek z tych pytań jest twierdząca, to prawdopodobnie miałoby sens dla Ciebie, aby Microsoft zaoferował nową, ulepszoną wersję VLOOKUP, która domyślnie ignoruje symbole wieloznaczne. Na końcu tej nowej funkcji mógłby znajdować się dodatkowy opcjonalny argument, który pozwoliłby Ci powiedzieć Excelowi, aby używał symboli wieloznacznych. Gdybyś pominął ten argument, Excel nie używałby żadnych symboli wieloznacznych. Można by łatwo wyszukać „142*154”, „Dlaczego?” lub „e*Trade”, nie martwiąc się o to, że wartość taka jak „Electrical Trade” będzie fałszywie dopasowana.

FASTER VLOOKUPS ARE COMING

Microsoft testował nową, szybszą wersję funkcji VLOOKUP w kanale Insider Monthly w kompilacjach Office 365 przez ostatnie kilka miesięcy. Funkcja jest często 10 razy szybsza niż stary VLOOKUP, szczególnie gdy istnieją tysiące lookups przeciwko tej samej tabeli.

Nowa logika powinna być toczona do wszystkich subskrybentów Office 365 do końca 2019 roku. Jeśli twoje VLOOKUPy zajmują obecnie mniej niż sekundę, możesz nie zauważyć zmiany. Ale jeśli masz masywny arkusz, którego obliczenie zajmuje 40 sekund, spodziewaj się, że ten czas przeliczania spadnie do około czterech sekund lub mniej.

.