Funkcja WYSZUKAJ.PIONOWO jest dostępna wyłącznie dla wewnętrznego programu pakietu Office 365. Funkcja WYSZUKAJ ma wiele funkcji, które przezwyciężają wiele słabości funkcji WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO, ale niestety na razie nie jest dla nas dostępna. Ale nie martw się, możemy utworzyć funkcję XLOOKUP, która działa dokładnie tak samo, jak nadchodząca funkcja XLOOKUP MS Excel. Dodamy do niego funkcjonalności po kolei.
Kod VBA funkcji XLOOKUP
Poniższa funkcja wyszukiwania UDF rozwiąże wiele problemów. Skopiuj go lub pobierz dodatek xl poniżej pliku poniżej.
Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) Funkcja zakończenia
Wyjaśnienie:
Powyższy kod jest tylko podstawowym INDEX-MATCH używanym w VBA. Upraszcza to wiele rzeczy, z którymi boryka się nowy użytkownik. Jeśli rozwiązuje złożoność funkcji INDEKS-PODZIELANIE i używa tylko trzech argumentów. Możesz go skopiować do pliku Excela lub pobrać plik .xlam poniżej i zainstalować go jako dodatek Excel. Jeśli nie wiesz, jak stworzyć i używać dodatku, kliknij tutaj, to ci pomoże.
Dodatek XLOOKUP
zobaczmy, jak to działa w arkuszu programu Excel.
Składnia XLOOKUP
| =XWYSZUKAJ(wyszukiwana_wartość;wyszukiwana_tablica;wyszukiwana_tablica) |
szukana_wartość: To jest wartość, którą chcesz wyszukać w przeszukiwana_tablica.
szukana_tablica: Jest to zakres jednowymiarowy, w którym chcesz przeszukiwać szukana_wartość.
tablica_wyników: Jest to również zakres jednowymiarowy. Jest to zakres, z którego chcesz pobrać wartość.
Zobaczmy, jak działa ta funkcja XLOOKUP.
Przykłady XWYSZUKAJ:
Tutaj mam tabelę danych w programie Excel. Przyjrzyjmy się niektórym funkcjom przy użyciu tej tabeli danych.
Funkcjonalność 1. Dokładny Wyszukaj po lewej i prawej stronie wartości wyszukiwania.
Jak wiemy, funkcja WYSZUKAJ.PIONOWO programu Excel nie może pobrać wartości z lewej strony wartości wyszukiwania. W tym celu musisz użyć złożonej kombinacji INDEX-MATCH. Ale już nie.
Zakładając, że musimy pobrać wszystkie informacje dostępne w tabeli niektórych numerów rolek. W takim przypadku będziesz musiał również pobrać region, który znajduje się po lewej stronie kolumny numeru rolki.
Napisz tę formułę, I2:
| = X WYSZUKAJ (H2, $ B 2: $ B 14 $, $ A 2 $: 14 $ A) |
Otrzymujemy wynik północny dla rolki numer 112. Skopiuj lub przeciągnij w dół formułę w poniższych komórkach, aby wypełnić je odpowiednimi regionami.
Jak to działa?
Mechanizm jest prosty. Ta funkcja wyszukuje szukana_wartość w lookup_array i zwraca indeks pierwszego dokładnego dopasowania. Następnie używa tego indeksu, aby pobrać wartość z tablica_wyników. Ta funkcja działa doskonale z nazwanymi zakresami.
Podobnie użyj tej formuły, aby pobrać wartość z każdej kolumny.
Funkcjonalność 2. Dokładna Poziomy Wyszukaj powyżej i poniżej wartości wyszukiwania.
XLOOKUP działa również jako dokładna funkcja WYSZUKAJ.POZIOMO. Funkcja WYSZUKAJ.POZIOMO ma takie samo ograniczenie jak WYSZUKAJ.PIONOWO. Nie może pobrać wartości powyżej wartości wyszukiwania. Ale XLOOKUP nie tylko działa jako WYSZUKAJ.POZIOMO, ale także przezwycięża tę słabość. Zobaczmy jak.
Hipotetycznie, jeśli chcesz porównać dwa rekordy. Rekord wyszukiwania, który już masz. Rekord, z którym chcesz porównać, znajduje się powyżej zakresu wyszukiwania. W takim przypadku użyj tej formuły.
| =XWYSZUKAJ(H7,$A$9:$E$9,$A$2:$E$2) |
przeciągnij formułę i masz cały rekord porównywania wiersza.
Funkcjonalność 3. Nie ma potrzeby podawania numeru kolumny i domyślnego dokładnego dopasowania.
Kiedy używasz funkcji WYSZUKAJ.PIONOWO, musisz podać numer kolumny, z której chcesz pobrać wartości. W tym celu musisz policzyć kolumny lub skorzystać z kilku sztuczek, skorzystać z pomocy innych funkcji. Dzięki tej funkcji wyszukiwania UDF XLOOKUP nie musisz tego robić.
Jeśli używasz funkcji WYSZUKAJ.PIONOWO do pobierania wartości z jednej kolumny lub sprawdzania, czy wartość istnieje w kolumnie, jest to najlepsze rozwiązanie według mnie.
Funkcjonalność 4. Zastępuje funkcję INDEX-MATCH, VLOOKUP, HLOOKUP
W przypadku prostych zadań nasza funkcja XLOOKUP zastępuje wyżej wymienione funkcje.
Ograniczenia XLOOKUP:
Jeśli chodzi o złożone formuły, takie jak WYSZUKAJ.PIONOWO z dynamicznym indeksem kolumn, w których WYSZUKAJ.PIONOWO identyfikujemy kolumnę wyszukiwania z nagłówkami, ta funkcja WYSZUKAJ.PIONOWO nie powiedzie się.
Innym ograniczeniem jest to, że jeśli musisz wyszukać wiele losowych kolumn lub wierszy z tabeli, ta funkcja będzie bezużyteczna, ponieważ będziesz musiał wielokrotnie pisać tę formułę. Można to obejść, używając nazwanych zakresów.
Na razie nie dodaliśmy przybliżonej funkcjonalności, więc oczywiście nie możesz uzyskać przybliżonego dopasowania. Dodamy to za wcześnie.
Jeśli funkcja XLOOKUP nie znajdzie wartości wyszukiwania, zwróci błąd #VALUE, a nie #N/A.
Więc tak, w ten sposób używasz XLOOKUP do pobierania, wyszukiwania i sprawdzania wartości w tabelach programu Excel. Tej funkcji zdefiniowanej przez użytkownika można użyć do bezproblemowego wyszukiwania po lewej lub powyżej wartości wyszukiwania. Jeśli nadal masz jakiekolwiek wątpliwości lub jakiekolwiek szczególne wymagania związane z tą funkcją lub zapytaniem dotyczącym programu EXCEL 2010/2013/2016/2019/365 lub VBA, zapytaj o to w sekcji komentarzy poniżej. Na pewno otrzymasz odpowiedź.
Utwórz funkcję VBA, aby zwrócić tablicę | Aby zwrócić tablicę z funkcji zdefiniowanej przez użytkownika, musimy ją zadeklarować, gdy nazywamy UDF.
Tablice w programie Excel Formuła|Dowiedz się, jakie tablice znajdują się w programie Excel.
Jak utworzyć funkcję zdefiniowaną przez użytkownika za pomocą VBA | Dowiedz się, jak tworzyć funkcje zdefiniowane przez użytkownika w programie Excel
Korzystanie z funkcji zdefiniowanej przez użytkownika (UDF) z innego skoroszytu przy użyciu VBA w programie Microsoft Excel | Użyj funkcji zdefiniowanej przez użytkownika w innym skoroszycie programu Excel
Zwróć wartości błędów z funkcji zdefiniowanych przez użytkownika za pomocą VBA w programie Microsoft Excel | Dowiedz się, jak zwrócić wartości błędów z funkcji zdefiniowanej przez użytkownika
Popularne artykuły:
Podziel arkusz Excela na wiele plików na podstawie kolumny za pomocą VBA | Ten kod VBA dzieli arkusz Excela na podstawie unikalnych wartości w określonej kolumnie. Pobierz plik roboczy.
Wyłącz komunikaty ostrzegawcze za pomocą VBA w programie Microsoft Excel 2016 | Aby wyłączyć komunikaty ostrzegawcze, które przerywają działający kod VBA, używamy klasy Application.
Dodaj i zapisz nowy skoroszyt za pomocą VBA w programie Microsoft Excel 2016 | Do dodawania i zapisywania skoroszytów za pomocą VBA używamy klasy Workbooks. Workbooks.Add łatwo dodaje nowy skoroszyt, jednak…