Wszystkie sekrety funkcji WYSZUKAJ.PIONOWO programu Excel do wyszukiwania danych w tabeli i wyodrębniania ich do innej

2024 Autor: Malcolm Clapton | [email protected]. Ostatnio zmodyfikowany: 2023-12-17 04:07

Po przeczytaniu artykułu dowiesz się nie tylko, jak znaleźć dane w arkuszu Excel i wyodrębnić je do innego, ale także techniki, które można wykorzystać w połączeniu z funkcją WYSZUKAJ. PIONOWO.

Podczas pracy w Excelu bardzo często zachodzi potrzeba wyszukania danych w jednej tabeli i wyodrębnienia ich do innej. Jeśli nadal nie wiesz, jak to zrobić, to po przeczytaniu artykułu dowiesz się nie tylko, jak to zrobić, ale także dowiesz się, w jakich warunkach możesz wycisnąć z systemu maksymalną wydajność. Uwzględniono większość bardzo skutecznych technik, które powinny być używane w połączeniu z funkcją WYSZUKAJ. PIONOWO.

Nawet jeśli korzystasz z funkcji WYSZUKAJ. PIONOWO od lat, to z dużym prawdopodobieństwem ten artykuł przyda Ci się i nie pozostawi Cię obojętnym. Na przykład, będąc informatykiem, a potem szefem IT, używam VLOOKUP od 15 lat, ale z wszystkimi niuansami poradziłem sobie dopiero teraz, kiedy zacząłem uczyć ludzi Excela na zasadach zawodowych.

WYSZUKAJ. PIONOWO to skrót od vpionowy NSkontrola. Podobnie, WYSZUKAJ. PIONOWO - WYSZUKAJ PIONOWO. Już sama nazwa funkcji podpowiada nam, że przeszukuje ona wiersze tabeli (w pionie - iterowanie po wierszach i ustalanie kolumny), a nie w kolumnach (poziomo - iteracja po kolumnach i ustalanie wiersza). Należy zauważyć, że WYSZUKAJ. PIONOWO ma siostrę - brzydkie kaczątko, które nigdy nie zostanie łabędziem - jest to funkcja WYSZUKAJ. POZIOMO. WYSZUKAJ. POZIOMO, w przeciwieństwie do WYSZUKAJ. PIONOWO, wykonuje wyszukiwania poziome, ale koncepcja programu Excel (i rzeczywiście koncepcja organizacji danych) sugeruje, że tabele mają mniej kolumn i znacznie więcej wierszy. Dlatego musimy wielokrotnie częściej przeszukiwać wiersze niż kolumny. Jeśli zbyt często używasz funkcji HLO w programie Excel, prawdopodobnie nie zrozumiałeś czegoś w tym życiu.

Składnia

Funkcja WYSZUKAJ. PIONOWO ma cztery parametry:

= WYSZUKAJ. PIONOWO (;; [;]), tutaj:

- pożądana wartość (rzadko) lub odwołanie do komórki zawierającej pożądaną wartość (w zdecydowanej większości przypadków);

- odwołanie do zakresu komórek (tablica dwuwymiarowa), w kolumnie PIERWSZEJ (!), w której będzie wyszukiwana wartość parametru;

- numer kolumny w zakresie, z którego zostanie zwrócona wartość;

- to bardzo ważny parametr, który odpowiada na pytanie, czy pierwsza kolumna zakresu jest posortowana rosnąco. Jeśli tablica jest posortowana, podajemy wartość TRUE lub 1, w przeciwnym razie FALSE lub 0. Jeśli ten parametr zostanie pominięty, domyślnie przyjmuje wartość 1.

Założę się, że wielu z tych, którzy znają funkcję WYSZUKAJ. PIONOWO jako niestabilną, po przeczytaniu opisu czwartego parametru mogą czuć się nieswojo, ponieważ są przyzwyczajeni do widzenia go w nieco innej formie: zwykle mówią o dokładnym dopasowaniu, gdy wyszukiwanie (FAŁSZ lub 0) lub skanowanie zakresu (PRAWDA lub 1).

Teraz musisz się wysilić i przeczytać następny akapit kilka razy, aż poczujesz znaczenie tego, co zostało powiedziane do końca. Każde słowo jest tam ważne. Przykłady pomogą ci to rozgryźć.

Jak dokładnie działa formuła WYSZUKAJ. PIONOWO?

• Formuła typu I. Jeśli ostatni parametr zostanie pominięty lub określony jako równy 1, funkcja WYSZUKAJ. PIONOWO zakłada, że pierwsza kolumna jest posortowana w porządku rosnącym, więc wyszukiwanie zatrzymuje się na wierszu, który bezpośrednio poprzedza wiersz zawierający wartość większą niż pożądana … Jeśli nie zostanie znaleziony taki ciąg, zwracany jest ostatni wiersz zakresu.

  

• Formuła II. Jeśli ostatni parametr jest określony jako 0, funkcja WYSZUKAJ. PIONOWO skanuje kolejno pierwszą kolumnę tablicy i natychmiast zatrzymuje wyszukiwanie po znalezieniu pierwszego dokładnego dopasowania z parametrem, w przeciwnym razie kod błędu # N / A (# N / A) jest zwracany.

  

Przepływy pracy z formułami

VPR typu I

VPR typu II

Następstwa dla formuł postaci I

1. Formuły mogą służyć do dystrybucji wartości w zakresach.
2. Jeżeli pierwsza kolumna zawiera zduplikowane wartości i jest poprawnie posortowana, to zostanie zwrócony ostatni z wierszy ze zduplikowanymi wartościami.
3. Jeśli szukasz wartości, która jest oczywiście większa niż może zawierać pierwsza kolumna, możesz łatwo znaleźć ostatni wiersz tabeli, co może być całkiem wartościowe.
4. Ten widok zwróci błąd # N / A tylko wtedy, gdy nie znajdzie wartości mniejszej lub równej żądanej.
5. Trudno zrozumieć, że formuła zwraca nieprawidłowe wartości, jeśli tablica nie jest posortowana.

Następstwa dla formuł typu II

Jeśli żądana wartość występuje wielokrotnie w pierwszej kolumnie tablicy, formuła wybierze pierwszy wiersz do późniejszego pobrania danych.

Wydajność WYSZUKAJ. PIONOWO

Dotarłeś do kulminacyjnego punktu artykułu. Wydawałoby się, cóż, jaka jest różnica, jeśli jako ostatni parametr określę zero lub jeden? Zasadniczo wszyscy wskazują oczywiście zero, ponieważ jest to całkiem praktyczne: nie musisz się martwić o sortowanie pierwszej kolumny tablicy, możesz od razu zobaczyć, czy wartość została znaleziona, czy nie. Ale jeśli masz na arkuszu kilka tysięcy formuł WYSZUKAJ. PIONOWO, zauważysz, że WYSZUKAJ. PIONOWO II działa wolno. Jednocześnie zwykle każdy zaczyna myśleć:

• Potrzebuję mocniejszego komputera;
• Potrzebuję szybszej formuły, na przykład wiele osób wie o INDEX + MATCH, który jest podobno szybszy o marne 5-10%.

I niewiele osób myśli, że jak tylko zaczniesz używać funkcji WYSZUKAJ. PIONOWO typu I i upewnisz się, że pierwsza kolumna jest posortowana w jakikolwiek sposób, szybkość funkcji WYSZUKAJ. PIONOWO wzrośnie 57 razy. Piszę słowami - PIĘĆDZIESIĄT SIEDEM RAZY! Nie 57%, ale 5700%. Sprawdziłem ten fakt dość rzetelnie.

Sekret tak szybkiej pracy tkwi w tym, że na posortowanej tablicy można zastosować niezwykle wydajny algorytm wyszukiwania, który nazywa się wyszukiwaniem binarnym (metoda o połowę, metoda dychotomii). Zatem WYSZUKAJ. PIONOWO typu I stosuje go, a WYSZUKAJ. PIONOWO typu II wyszukuje w ogóle bez żadnej optymalizacji. To samo dotyczy funkcji PODAJ. POZYCJĘ, która zawiera podobny parametr, oraz funkcji WYSZUKAJ, która działa tylko na posortowanych tablicach i jest dołączona do programu Excel w celu zapewnienia zgodności z programem Lotus 1-2-3.

Wady formuły

Wady funkcji WYSZUKAJ. PIONOWO są oczywiste: po pierwsze przeszukuje tylko pierwszą kolumnę określonej tablicy, a po drugie tylko po prawej stronie tej kolumny. A jak rozumiesz, może się zdarzyć, że kolumna zawierająca niezbędne informacje będzie znajdować się po lewej stronie kolumny, w której będziemy szukać. Wspomniana już kombinacja formuł INDEX + MATCH jest pozbawiona tej wady, co sprawia, że jest to najbardziej elastyczne rozwiązanie do wydobywania danych z tabel w porównaniu z VLOOKUP (VLOOKUP).

Niektóre aspekty stosowania formuły w prawdziwym życiu

Wyszukiwanie zakresu

Klasyczną ilustracją wyszukiwania asortymentu jest zadanie określenia rabatu według wielkości zamówienia.

Wyszukaj ciągi tekstowe

Oczywiście funkcja WYSZUKAJ. PIONOWO szuka nie tylko liczb, ale także tekstu. Należy pamiętać, że formuła nie rozróżnia przypadku znaków. Jeśli używasz symboli wieloznacznych, możesz zorganizować wyszukiwanie rozmyte. Istnieją dwa symbole wieloznaczne: „?” - zastępuje dowolny znak w ciągu tekstowym, "*" - zastępuje dowolną liczbę dowolnych znaków.

Przestrzenie walki

Często pojawia się pytanie, jak rozwiązać problem dodatkowych przestrzeni podczas wyszukiwania. Jeśli nadal można wyczyścić tabelę przeglądową, pierwszy parametr formuły WYSZUKAJ. PIONOWO nie zawsze zależy od Ciebie. Dlatego jeśli istnieje ryzyko zatkania komórek dodatkowymi przestrzeniami, możesz użyć funkcji TRIM, aby je wyczyścić.

Inny format danych

Jeśli pierwszy parametr funkcji WYSZUKAJ. PIONOWO odnosi się do komórki zawierającej liczbę, ale przechowywanej w komórce jako tekst, a pierwsza kolumna tablicy zawiera liczby w poprawnym formacie, wyszukiwanie nie powiedzie się. Możliwa jest również sytuacja odwrotna. Problem można łatwo rozwiązać, tłumacząc parametr 1 na wymagany format:

= WYSZUKAJ. PIONOWO (−− D7; Produkty! $ A $ 2: $ C $ 5; 3; 0) - jeśli D7 zawiera tekst, a tabela zawiera liczby;

= WYSZUKAJ. PIONOWO (D7 i ""); Produkty!$A$2:$C$5; 3; 0) - i odwrotnie.

Przy okazji możesz przetłumaczyć tekst na liczbę na kilka sposobów na raz, wybierz:

• Podwójna negacja -D7.
• Mnożenie przez jeden D7 * 1.
• Zerowy dodatek D7 + 0.
• Podniesienie do pierwszej potęgi D7 ^ 1.

Konwersja liczby na tekst odbywa się poprzez konkatenację z pustym ciągiem, co wymusza konwersję typu danych w programie Excel.

Jak stłumić # N / A

Jest to bardzo wygodne w przypadku funkcji JEŻELI. BŁĄD.

Na przykład: = JEŻELI. BŁĄD (WYSZUKAJ. PIONOWO (D7; Produkty! $ A $ 2: $ C $ 5; 3; 0; "").

Jeśli WYSZUKAJ. PIONOWO zwróci kod błędu # N/A, to IFERROR przechwyci go i podstawi parametr 2 (w tym przypadku pusty ciąg), a jeśli nie wystąpi żaden błąd, to ta funkcja będzie udawać, że w ogóle nie istnieje, ale istnieje tylko WYSZUKAJ. PIONOWO, które zwróciły normalny wynik.

Szyk

Często zapominają, aby odwołanie do tablicy było absolutne, a podczas rozciągania tablicy „pływa”. Pamiętaj, aby zamiast A2:C5 użyć $A$2:$C5$.

Dobrym pomysłem jest umieszczenie tablicy referencyjnej na osobnym arkuszu skoroszytu. Nie wchodzi pod stopy i będzie bezpieczniej.

Jeszcze lepszym pomysłem byłoby zadeklarowanie tej tablicy jako nazwanego zakresu.

Wielu użytkowników, określając tablicę, używa konstrukcji takiej jak A:C, określając całe kolumny. Takie podejście ma prawo istnieć, ponieważ nie musisz śledzić faktu, że twoja tablica zawiera wszystkie wymagane ciągi. Jeśli dodasz wiersze do arkusza z oryginalną tablicą, zakres określony jako A: C nie musi być dostosowywany. Oczywiście ta konstrukcja składniowa zmusza program Excel do wykonania nieco więcej pracy niż dokładne określenie zakresu, ale ten narzut można pominąć. Mówimy o setnych części sekundy.

Cóż, na granicy geniuszu - ułożyć tablicę w formie.

Użycie funkcji KOLUMNA do określenia kolumny do wyodrębnienia

Jeśli tabela, do której pobierasz dane za pomocą funkcji WYSZUKAJ. PIONOWO, ma taką samą strukturę jak tabela przeglądowa, ale zawiera po prostu mniej wierszy, możesz użyć funkcji COLUMN() w funkcji WYSZUKAJ. PIONOWO, aby automatycznie obliczyć liczby kolumn do pobrania. W takim przypadku wszystkie formuły WYSZUKAJ. PIONOWO będą takie same (dostosowane dla pierwszego parametru, który zmienia się automatycznie)! Zauważ, że pierwszy parametr ma bezwzględną współrzędną kolumny.

Tworzenie klucza złożonego za pomocą & „|” &

Jeśli zajdzie konieczność jednoczesnego przeszukiwania kilku kolumn, konieczne jest utworzenie klucza złożonego do wyszukiwania. Gdyby wartość zwracana nie była tekstowa (jak w przypadku pola „Kod”), ale liczbowa, wówczas odpowiednia byłaby wygodniejsza formuła SUMIFS, a klucz kolumny złożonej nie byłby w ogóle wymagany.

To jest mój pierwszy artykuł dla Lifehackera. Jeśli Ci się spodobało to zapraszam do odwiedzin, a także chętnie przeczytam w komentarzach o Twoich tajnikach korzystania z funkcji WYSZUKAJ. PIONOWO i tym podobnych. Dziękuję.:)