Excel 2020: dwanaście korzyści z XLOOKUP - porady dotyczące programu Excel

Nowa funkcja XLOOKUP zostanie wprowadzona do usługi Office 365 od listopada 2019 r. Joe McDaid z zespołu programu Excel zaprojektował XLOOKUP, aby ujednolicić osoby korzystające z funkcji WYSZUKAJ.PIONOWO i osoby korzystające z INDEKSU / PODAJNIKA. W tej sekcji omówimy 12 zalet XLOOKUP:

  1. Dokładne dopasowanie jest ustawieniem domyślnym.
  2. Trzeci argument funkcji WYSZUKAJ.PIONOWO oparty na liczbach całkowitych jest teraz poprawnym odniesieniem.
  3. IFNA jest wbudowana do obsługi brakujących wartości.
  4. XLOOKUP nie ma problemu z przejściem w lewo.
  5. Znajdź następne mniejsze lub następne większe dopasowanie bez sortowania tabeli.
  6. XLOOKUP może wykonać HLOOKUP.
  7. Znajdź ostatnie dopasowanie, wyszukując od dołu.
  8. Symbole wieloznaczne są domyślnie wyłączone, ale można je ponownie włączyć.
  9. Zwróć wszystkie 12 miesięcy w jednej formule.
  10. Może zwrócić odwołanie do komórki, jeśli XLOOKUP znajduje się obok dwukropka, na przykład XLOOKUP (); XLOOKUP ()
  11. Może przeprowadzić dopasowanie dwukierunkowe, takie jak INDEX (, MATCH, MATCH).
  12. Potrafi podsumować wszystkie wyszukiwania w jednej formule, takiej jak funkcja LOOKUP.

Oto składnia: = XLOOKUP (Lookup_Value, Lookup_Array, Results_Array, (if_not_found), (match_mode), (search_mode)).

Korzyść XLOOKUP 1: Domyślne dokładne dopasowanie

99% moich formuł WYSZUKAJ.PIONOWO kończy się na, FAŁSZ lub 0, aby wskazać dokładne dopasowanie. Jeśli zawsze używasz wersji z dopasowaniem ścisłym funkcji WYSZUKAJ.PIONOWO, możesz rozpocząć wyłączanie trybu dopasowania w funkcji XLOOKUP.

Na poniższym rysunku wyszukujesz W25-6 z komórki A4. Chcesz poszukać tego elementu w L8: L35. Gdy zostanie znaleziony, chcesz uzyskać odpowiednią cenę z kolumny N. Nie ma potrzeby określania False jako parametru match_mode, ponieważ XLOOKUP domyślnie odpowiada dokładnemu dopasowaniu.

XLOOKUP wartość w A4. Zajrzyj do L8: L35. Zwróć odpowiednią cenę z N8: N35.

Korzyści XLOOKUP 2: Tablica wyników jest odwołaniem zamiast liczbą całkowitą

Pomyśl o formule WYSZUKAJ.PIONOWO, której używałbyś przed XLOOKUP. Trzecim argumentem byłaby 3, aby wskazać, że chcesz zwrócić trzecią kolumnę. Zawsze istniało niebezpieczeństwo, że nieświadomy współpracownik wstawiłby (lub usunął) kolumnę w Twojej tabeli. Z dodatkową kolumną w tabeli funkcja WYSZUKAJ.PIONOWO zwracająca cenę zaczęłaby zwracać opis. Ponieważ XLOOKUP wskazywał na odwołanie do komórki, formuła przepisuje się, aby nadal wskazywać cenę znajdującą się teraz w kolumnie O.

Stara funkcja WYSZUKAJ.PIONOWO nie powiodłaby się, gdyby ktoś wstawił nową kolumnę do tabeli odnośników. XLOOKUP nadal działa.

XLOOKUP Korzyść 3: IFNA jest wbudowany jako opcjonalny argument

Przerażający błąd # N / D jest zwracany, gdy wartość wyszukiwania nie zostanie znaleziona w tabeli. W przeszłości, aby zastąpić # N / A czymś innym, musiałbyś użyć IFERROR lub IFNA owiniętego wokół WYSZUKAJ.PIONOWO.

Gdy element nie zostanie znaleziony, zwraca # N / A z WYSZUKAJ.PIONOWO lub XLOOKUP…

Dzięki sugestii Rico na moim kanale YouTube, zespół Excela włączył opcjonalny czwarty argument dla if_not_found. Jeśli chcesz zastąpić te błędy # N / A zerem, po prostu dodaj 0 jako czwarty argument. Możesz też użyć tekstu, na przykład „Nie znaleziono wartości”.

Opcjonalny czwarty argument w XLOOKUP to „jeśli nie znaleziono”. Umieść tam 0 lub „Nie znaleziono”.

Korzyści XLOOKUP 4: Nie ma problemu ze spojrzeniem na lewo od pola klucza

WYSZUKAJ.PIONOWO nie może spojrzeć na lewo od pola klucza bez uciekania się do WYSZUKAJ.PIONOWO (A4, WYBIERZ ((1,2), G7: G34, F7: F34), 2, fałsz). Dzięki XLOOKUP nie ma problemu z umieszczeniem tablicy wyników po lewej stronie tablicy wyszukiwania.

W przypadku XLOOKUP nie ma problemu z zwróceniem kategorii z kolumny F podczas wyszukiwania numerów części w kolumnie G. To zawsze była słabość funkcji WYSZUKAJ.PIONOWO: nie może wyglądać w lewo.

XLOOKUP Korzyść 5: Mecz następny mniejszy lub następny większy bez sortowania

WYSZUKAJ.PIONOWO miał opcję wyszukiwania dokładnego dopasowania lub po prostu mniejszej wartości. Możesz pozostawić czwarty argument poza WYSZUKAJ.PIONOWO lub zmienić wartość Fałsz na Prawda. Aby to zadziałało, tabela przeglądowa musiała być posortowana w kolejności rosnącej.

Przykład wersji Przybliżone dopasowanie funkcji WYSZUKAJ.PIONOWO. Każda sprzedaż od 10 do 20 tysięcy otrzymuje premię w wysokości 12 dolarów.

Ale WYSZUKAJ.PIONOWO nie ma możliwości zwrócenia dokładnego dopasowania lub następnego większego elementu. W tym celu musiałeś przełączyć się na używanie MATCH z -1 jako match_mode i trzeba było uważać, aby tablica przeglądowa została posortowana malejąco.

Opcjonalny piąty argument XLOOKUP match_mode może szukać tylko dokładnego dopasowania, równego lub tylko mniejszego, równego lub tylko większego. Zauważ, że wartości w XLOOKUP mają więcej sensu niż w MATCH:

  • -1 znajduje wartość równą lub tylko mniejszą
  • 0 znajdź dokładne dopasowanie
  • 1 znajduje wartość równą lub tylko większą.

Ale najbardziej zdumiewająca część: tabela przeglądowa nie musi być sortowana, a każdy tryb match_mode będzie działać.

Poniżej, match_mode równy -1 znajduje następny mniejszy element.

Piąty argument XLOOKUP to Match_Mode. 0 oznacza dokładne dopasowanie. Ujemny jest używany do dokładnego dopasowania lub następnego mniejszego przedmiotu. Dodatnia 1 oznacza dopasowanie ścisłe lub następny większy element. 2 oznacza dopasowanie symboli wieloznacznych. Aby odzwierciedlić działanie funkcji WYSZUKAJ.PIONOWO z Prawdą w czwartym argumencie, wstaw ujemną wartość jako argument match_mode w XLOOKUP.

Tutaj match_mode równy 1 pozwala znaleźć potrzebny pojazd w zależności od liczby osób w drużynie. Zwróć uwagę, że tabela przeglądowa nie jest posortowana według pasażerów, a nazwa pojazdu znajduje się po lewej stronie klucza.

XLOOKUP może zrobić coś, czego funkcja VLOOKUP nie mogła zrobić: znaleźć dokładne dopasowanie lub po prostu większe. W tym przypadku biuro podróży ma listę rezerwacji. Na podstawie liczby pasażerów tabela wyszukiwania pokazuje, jakiego pojazdu potrzebujesz dla tych osób.

Tabela mówi:

  • Autobus mieści 64 osoby
  • Samochód mieści 4 osoby
  • Motocykl może pomieścić 1 osobę
  • Tour Van mieści 12 osób
  • Van może pomieścić 6 osób.

Jako bonus, dane są sortowane według pojazdu (w starym rozwiązaniu, przy użyciu funkcji MATCH, tabela musiałaby być sortowana malejąco według pojemności. Ponadto: pojazd znajduje się na lewo od pojemności.

XLOOKUP Korzyść 6: Boczne XLOOKUP zastępuje HLOOKUP

Lookup_array i results_array mogą być poziome z XLOOKUP, co ułatwia zastąpienie HLOOKUP.

Tutaj tabela przeglądowa jest pozioma. W przeszłości wymagałoby to HLOOKUP, ale XLOOKUP radzi sobie z tabelą przechodzącą na bok.

Korzyści XLOOKUP 7: Szukaj od dołu ostatniego dopasowania

Mam na YouTube stary film z odpowiedzią na pytanie z brytyjskiej hodowli koni. Mieli flotę pojazdów. Za każdym razem, gdy pojazd przyjeżdżał w celu tankowania lub serwisu, rejestrowali pojazd, datę i przebieg w arkuszu kalkulacyjnym. Chcieli znaleźć najnowszy znany przebieg każdego pojazdu. Chociaż MAXIFS z ery Excel-2017 może rozwiązać ten problem dzisiaj, wiele lat temu rozwiązaniem była tajemna formuła wykorzystująca LOOKUP i wymagająca dzielenia przez zero.

Obecnie opcjonalny szósty argument XLOOKUP pozwala określić, że wyszukiwanie powinno rozpoczynać się od dołu zestawu danych.

Znajdź ostatnie dopasowanie na liście.

Uwaga

Chociaż jest to duża poprawa, pozwala tylko znaleźć pierwsze lub ostatnie dopasowanie. Niektórzy mieli nadzieję, że pozwoli to znaleźć drugie lub trzecie dopasowanie, ale nie jest to celem argumentu search_mode.

Uwaga

Powyższy rysunek pokazuje, że istnieją tryby wyszukiwania korzystające ze starego wyszukiwania binarnego. Joe McDaid odradza ich używanie. Po pierwsze, ulepszony algorytm wyszukiwania z 2018 roku jest na tyle szybki, że nie ma znaczącej korzyści w zakresie szybkości. Po drugie, ryzykujesz, że nieświadomy współpracownik będzie sortował tabelę wyszukiwania i wprowadzał błędne odpowiedzi.

Korzyści XLOOKUP 8: Symbole wieloznaczne są domyślnie „wyłączone”

Większość ludzi nie zdawała sobie sprawy, że funkcja WYSZUKAJ.PIONOWO traktuje gwiazdkę, znak zapytania i tyldę jako znaki wieloznaczne, jak opisano w sekcji „# 51 Użyj symbolu wieloznacznego w WYSZUKAJ.PIONOWO” na stronie 143. W przypadku XLOOKUP symbole wieloznaczne są domyślnie wyłączone. Jeśli chcesz, aby XLOOKUP traktował te znaki jako symbole wieloznaczne, użyj 2 jako Match_Mode.

Bardzo niewiele osób zdało sobie sprawę, że funkcja WYSZUKAJ.PIONOWO traktuje gwiazdki w wartości wyszukiwania jako symbole wieloznaczne. Domyślnie XLOOKUP nie używa symboli wieloznacznych, ale możesz wymusić, aby zachowywał się jak WYSZUKAJ.PIONOWO, jeśli używasz trybu dopasowania 2: dopasowanie znaków wieloznacznych.

Korzyść XLOOKUP 9: Zwróć wszystkie 12 miesięcy w jednej formule!

Jest to naprawdę zaleta Dynamic Arrays, ale jest to mój ulubiony powód, by pokochać XLOOKUP. Gdy musisz zwrócić wszystkie 12 miesięcy w wyszukiwaniu, pojedyncza formuła wprowadzona w B6 z prostokątną tablicą return_array zwróci wiele wyników. Te wyniki rozleją się na sąsiednie komórki.

Na poniższym rysunku pojedyncza formuła wprowadzona w polu B7 zwraca wszystkie 12 odpowiedzi przedstawionych w polu B7: M7.

Pojedynczy XLOOKUP w kolumnie styczeń zwraca liczby od stycznia do grudnia. Odbywa się to przez określenie results_array z 12 kolumnami.

Korzyści XLOOKUP 10: Może zwrócić odwołanie do komórki, jeśli sąsiaduje z okrężnicą

Ten jest złożony, ale piękny. W przeszłości było siedem funkcji, które zmieniałyby się od zwracania wartości komórki do zwracania odwołania do komórki, jeśli funkcja dotykała dwukropka. Na przykład zobacz Używanie A2: INDEX () jako nieulotnego przesunięcia. XLOOKUP to funkcja ósemek oferująca to zachowanie, łącząca WYBIERZ, JEŻELI, IFS, INDEKS, POŚREDNI, PRZESUNIĘCIE i PRZEŁĄCZ.

Rozważ poniższy rysunek. Ktoś wybrał Cherry w E4 i Fig w E5. Potrzebujesz formuły, która zsumuje wszystko od B6 do B9.

Rysunek przedstawia dwie formuły XLOOKUP w dwóch komórkach. Pierwsza zwraca 15 z komórki B6. Drugi powraca 30 z B9. Ale w trzeciej komórce znajduje się formuła, która łączy dwie formuły XLOOKUP dwukropkiem, a następnie zawija ją w funkcję SUMA. Wynikiem jest SUMA z B6: B9, ponieważ XLOOKUP może zwrócić odwołanie do komórki, jeśli funkcja pojawia się obok operatora, takiego jak dwukropek. Aby udowodnić, że to działa, kilka następnych rysunków pokaże tę formułę w oknie dialogowym Oceń wzór.

Na powyższym rysunku widać, że XLOOKUP E4 zwróci 15 z komórki B6. XLOOKUP z E5 zwróci 30 z B9. Jeśli jednak weźmiesz dwie funkcje XLOOKUP z komórek D9 i D10 i umieścisz je razem z dwukropkiem pomiędzy nimi, zachowanie XLOOKUP ulegnie zmianie. Zamiast zwracać 15, pierwszy XLOOKUP zwraca adres komórki B6!

Aby to udowodnić, wybrałem D7 i używam Formulas, Evaluate Formula. Po dwukrotnym naciśnięciu Oceń, następną częścią do obliczenia jest XLOOKUP („Cherry”, A4: A29, B4: B29), jak pokazano tutaj.

To pokazuje okno dialogowe Oceń formułę tuż przed obliczeniem pierwszego XLOOKUP. Ten XLOOKUP pojawia się tuż przed dwukropkiem.

Naciśnij ponownie Oceń i zadziwiająco, formuła XLOOKUP zwraca $ B $ 6 zamiast 15 zapisanych w B6. Dzieje się tak, ponieważ bezpośrednio po tym wzorze XLOOKUP znajduje się dwukropek.

Kliknij Oceń, a pierwsza XLOOKUP zwróci $ B $ 6 zamiast 15.

Naciśnij Oceń jeszcze dwa razy, a tymczasowa formuła będzie miała postać = SUMA (B6: B9).

Po obliczeniu drugiego XLOOKUP formuła tymczasowa to = SUMA (B6: B9).

To niesamowite zachowanie, o którym większość ludzi nie wie. MVP programu Excel Charles Williams powiedział mi, że można go uruchomić za pomocą dowolnego z tych trzech operatorów obok XLOOKUP:

  • Dwukropek
  • Spacja (operator przecięcia)
  • Przecinek (operator Unii)

Korzyści XLOOKUP 11: Dopasowanie dwukierunkowe, takie jak INDEX (, MATCH, MATCH)

Dla wszystkich moich znajomych z funkcji WYSZUKAJ.PIONOWO ludzie z INDEKSU / PODAJANIA czekali, aby sprawdzić, czy XLOOKUP poradzi sobie z dopasowaniem dwukierunkowym. Wspaniała wiadomość: może to zrobić. Zła wiadomość: metodologia jest trochę inna niż oczekiwaliby fani INDEX / MATCH. To może być trochę ponad ich głowami. Ale jestem pewien, że mogą przyjść do tej metody.

W przypadku dopasowania dwukierunkowego chcesz znaleźć wiersz zawierający numer konta A621 pokazany w J3. Tak więc XLOOKUP zaczyna się dość łatwo: = XLOOKUP (J3, A5: A15. Ale potem musisz podać tablicę wyników. Możesz użyć tej samej sztuczki, co w XLOOKUP Korzyść 9: Zwróć wszystkie 12 miesięcy w pojedynczym wzorze powyżej, ale użyj go do zwrócenia wektora pionowego. Wewnętrzny XLOOKUP szuka miesiąca J4 w nagłówkach miesięcy w B4: G4. Tablica_powrotu jest określona jako B5: G15. W rezultacie wewnętrzna XLOOKUP zwraca tablicę taką jak ta pokazana w I10 : I20 poniżej. Ponieważ A621 znajduje się w piątej komórce tablicy lookup_array, a 104 w piątej komórce tablicy wyników, otrzymujesz poprawną odpowiedź ze wzoru. Poniżej J6 pokazuje stary sposób. J7 zwraca nowy sposób.

XLookup J3 na liście kont w A5: A15. Dla tablicy wyników użyj XLOOKUP (J4, B4: G4, B5: G15). W tym wzorze B4: G4 to lista miesięcy. B5: G15 to prostokątna tablica wartości dla wszystkich kont dla wszystkich miesięcy. W innej komórce tylko wewnętrzna XLOOKUP pokazuje, jak zwraca całą kolumnę wartości dla maja.

Korzyść XLOOKUP 12: Zsumuj wszystkie wyszukiwane wartości w jednej formule

Starożytna funkcja LOOKUP oferowała dwie dziwne sztuczki. Po pierwsze, jeśli próbujesz obliczyć łączną kwotę dodatkowego kosztu do naliczenia, możesz poprosić LOOKUP o wyszukanie wszystkich wartości w jednej formule. Na poniższej ilustracji funkcja WYSZUKAJ (C4: C14 wykonuje 11 wyszukiwań. Jednak funkcja WYSZUKAJ nie zapewnia dokładnego dopasowania i wymaga posortowania tabeli przeglądowej.

Wyszukaj 13 wartości i zsumuj je. Kiedyś działało to z LOOKUP, ale działa też z XLOOKUP. Określ wszystkie wartości wyszukiwania C4: C14 jako pierwszy argument. Zawiń XLOOKUP w funkcję SUMA.

Dzięki XLOOKUP możesz określić zakres jako lookup_value, a XLOOKUP zwróci wszystkie odpowiedzi. Korzyścią jest to, że XLOOKUP może wykonywać dokładne dopasowania.

Sztuczka polegająca na używaniu funkcji LOOKUP do sumowania wszystkich wyników wyszukiwania działała tylko z przybliżoną wersją Lookup. Tutaj XLOOKUP dokonuje dokładnego dopasowania wszystkich nazw w L4: L14 i uzyskuje w sumie wszystkie wyniki.

Dodatkowa wskazówka: A co z Twisted LOOKUP?

MVP programu Excel Mike Girvin często pokazuje sztuczkę funkcji WYSZUKAJ, w której Lookup_Vector jest pionowy, a wynik_wektor jest poziomy. XLOOKUP nie obsługuje natywnie tej sztuczki. Ale jeśli trochę oszukasz i zawiniesz wynikową tablicę w funkcji TRANSPOSE, możesz zarządzać pokręconym wyszukiwaniem.

Tutaj tablica przeglądowa jest pionowa, a tablica wyników jest pozioma. Stara funkcja WYSZUKAJ może sobie z tym poradzić, ale aby zrobić to z XLOOKUP, musisz opakować każdą tablicę w TRANSPOSE.

Interesujące artykuły...