WYSZUKAJ.PIONOWO między dwoma skoroszytami: łatwy przewodnik krok po kroku

Jeśli pracujesz z wieloma skoroszytami w programie Excel, może być konieczne użycie funkcji WYSZUKAJ.PIONOWO w celu pobrania danych z jednego skoroszytu do drugiego.

Funkcja WYSZUKAJ.PIONOWO wyszukuje określoną wartość w tabeli w osobnym skoroszycie i zwraca odpowiadającą jej wartość z tego samego wiersza. Składnia musi zawierać w pełni kwalifikowane odwołanie do nazwy drugiego skoroszytu i arkusza.

Właściwe odniesienie może być trudne, gdy używasz tej funkcji po raz pierwszy. W tym artykule użyto przykładowych arkuszy kalkulacyjnych, aby dokładnie pokazać, jak wprowadzać formuły.

Nauczysz się również kilku ulepszeń funkcji WYSZUKAJ.PIONOWO i kilku alternatyw dla tej funkcji.

Zacznijmy!

Spis treści

• Co to jest WYSZUKAJ.PIONOWO?
• Jak korzystać z funkcji WYSZUKAJ.PIONOWO między dwoma skoroszytami
  • Przykładowe dane
  • Krok 1: Pozostaw oba skoroszyty otwarte
  • Krok 2: Rozpocznij wprowadzanie formuły WYSZUKAJ.PIONOWO
  • Krok 3: Jak odwoływać się do drugiego skoroszytu
• 3 typowe błędy WYSZUKAJ.PIONOWO i sposoby ich naprawy
  • 1. #Nie dotyczy Błąd
  • 2. #Odnośnik! Błąd numeru kolumny
  • 3. Błędy cudzysłowów i nawiasów kwadratowych
• 4 wskazówki dotyczące zarządzania różnymi skoroszytami
• Jak automatycznie aktualizować dane
• Zaawansowane zastosowania funkcji WYSZUKAJ.PIONOWO
• 2 główne alternatywy dla WYSZUKAJ.PIONOWO
  • 1. DOPASOWANIE INDEKSOWE
  • 2. XWYSZUKAJ
• Jak korzystać z funkcji WYSZUKAJ.PIONOWO w wielu arkuszach
  • Krok 1: Zidentyfikuj komponenty
  • Krok 2: Skonfiguruj swoje dane
  • Krok 3: Użyj funkcji WYSZUKAJ.PIONOWO
  • (Opcjonalnie) Krok 4: Rozwiąż problemy z błędami #N/D
• Końcowe przemyślenia

Co to jest WYSZUKAJ.PIONOWO?

Załóżmy, że masz skoroszyt sprzedaży produktów i osobny skoroszyt z cenami każdego produktu. Chcesz uzyskać dostęp do cen z drugiego skoroszytu i użyć ich w obliczeniach w pierwszym skoroszycie.

WYSZUKAJ.PIONOWO to jedno rozwiązanie tego zadania. Termin oznacza „Wyszukiwanie pionowe . “

Jest to wbudowana funkcja, która pozwala wyszukać określoną wartość w kolumnie i zwrócić odpowiednią wartość w innej kolumnie na podstawie lokalizacji.

Może być używany z:

• pojedynczy arkusz kalkulacyjny

• wiele arkuszy w tym samym skoroszycie

• dwa skoroszyty programu Excel

Jeśli nie masz dodatkowej złożoności wielu skoroszytów, zapoznaj się z tym samouczkiem dotyczącym porównywania dwóch kolumn w programie Excel przy użyciu WYSZUKAJ.PIONOWO .

Jak korzystać z funkcji WYSZUKAJ.PIONOWO między dwoma skoroszytami

Funkcja WYSZUKAJ.PIONOWO jest szczególnie przydatna, gdy trzeba wyszukać dane w wielu arkuszach lub skoroszytach.

Aby to zrobić, wykonaj trzy główne kroki:

1. Otwórz oba skoroszyty.

2. Wprowadź formułę WYSZUKAJ.PIONOWO w pierwszym skoroszycie programu Excel.

3. Podaj w pełni kwalifikowane odwołanie do drugiego skoroszytu.

Przykładowe dane

Ten prosty samouczek obejmuje przykłady oparte na przykładowych danych. Aby kontynuować, utwórz nowy skoroszyt o nazwie „Sprzedaż” i wprowadź te dane w kolumnach A i B:

• jabłka, 40

• Pomarańcze, 30

• Gruszki, 70

• banany, 20

Dodaj trzecią kolumnę do przechowywania informacji o cenie:

Utwórz drugi skoroszyt programu Excel o nazwie „Produkt” i zmień nazwę Arkusz1 na „Ceny”. Wprowadź te dane w kolumnie A i kolumnie B:

• Jabłka, 1,50

• Pomarańcze, 2.30

• Gruszki, 3,60

• Banany, 2.10

Dane wyglądają następująco:

Aby obliczyć przychód ze sprzedaży w pierwszym arkuszu kalkulacyjnym, musimy sprawdzić cenę w drugim arkuszu kalkulacyjnym. VLOOKUP na ratunek!

Krok 1: Pozostaw oba skoroszyty otwarte

Oba skoroszyty muszą być otwarte podczas używania formuły WYSZUKAJ.PIONOWO między dwoma skoroszytami. Jeśli drugi skoroszyt zostanie zamknięty, funkcja zwróci błąd.

Możesz otworzyć je osobno lub użyć polecenia „Otwórz” w programie Excel, aby otworzyć je oba jednocześnie. Istnieją dwa sposoby otwierania obu skoroszytów:

• Przejdź do zakładki Plik i wybierz „Otwórz”.

• Użyj skrótu klawiaturowego „Ctrl + O” .

Po otwarciu obu skoroszytów możesz przełączać się między nimi, klikając odpowiednią kartę u dołu okna programu Excel.

Krok 2: Rozpocznij wprowadzanie formuły WYSZUKAJ.PIONOWO

Oto podstawowa składnia funkcji WYSZUKAJ.PIONOWO:

=WYSZUKAJ.PIONOWO(wyszukiwana_wartość; tablica_tabeli; numer_indeksu_kolumny; [przeszukiwany_zakres])

• lookup_value : Wartość, którą chcesz wyszukać w innym skoroszycie.

• tablica_tabeli : zakres komórek w innym skoroszycie zawierający dane, które chcesz pobrać.

• col_index_num : Numer kolumny w zakresie zawierającym dane, które chcesz pobrać.

• range_lookup : Ten ostatni parametr jest opcjonalny. Jeśli ustawisz ją na PRAWDA lub ją pominiesz, funkcja WYSZUKAJ.PIONOWO wyszuka przybliżone dopasowanie. Jeśli ustawisz FAŁSZ, VLOOKUP będzie szukać tylko dokładnego dopasowania.

Załóżmy, że chcesz sprawdzić cenę jabłek w drugim skoroszycie naszych przykładowych danych. Oto jak rozkłada się formuła:

• Lookup_value znajduje się w komórce A2 pierwszego skoroszytu.

• Tablica tabeli znajduje się w zakresie komórek A1:B5 drugiego skoroszytu.

• Col_index_num to druga kolumna zawierająca cenę.

Najbardziej skomplikowaną częścią formuły WYSZUKAJ.PIONOWO jest prawidłowe odniesienie do drugiego skoroszytu.

Krok 3: Jak odwoływać się do drugiego skoroszytu

Aby wykonać formułę WYSZUKAJ.PIONOWO dla dwóch skoroszytów, musisz użyć w pełni kwalifikowanego odwołania, które zawiera te szczegóły z drugiego skoroszytu:

• nazwa skoroszytu

• nazwa arkusza

• zakres komórek

Dzięki temu program Excel dokładnie wie, gdzie znaleźć dane, których szukasz. W pełni kwalifikowane odwołanie jest używane dla argumentu tablica_tabeli funkcji WYSZUKAJ.PIONOWO.

Oto wartości dla przykładowych danych:

• nazwa skoroszytu: Product.xlsx

• nazwa arkusza: Ceny

• zakres komórek: $A1:$B5

Aby wyszukać cenę pierwszej pozycji, wprowadź następującą formułę w komórce C2 pierwszego skoroszytu:

=WYSZUKAJ.PIONOWO(A2; [Produkt.xlsx]Ceny!$A$1:$B$5; 2; FAŁSZ)

Ten rysunek przedstawia działanie funkcji na pasku formuły dla pierwszej komórki:

Zwróć uwagę, że nazwa skoroszytu jest ujęta w nawiasy kwadratowe, a po nazwie arkusza następuje wykrzyknik.

Jeśli nazwa pliku programu Excel zawiera spację lub znaki specjalne, cały skoroszyt i arkusz należy ująć w pojedyncze cudzysłowy.

Formuła używa wartości bezwzględnych dla zakresu komórek (prefiks $). Pozwala to skopiować formułę do pozostałych wierszy, klikając uchwyt wypełniania.

Oto wyniki WYSZUKAJ.PIONOWO:

I to wszystko! Teraz wiesz, jak efektywnie korzystać z funkcji WYSZUKAJ.PIONOWO. Aby pomóc Ci w przypadku napotkania jakichkolwiek błędów, w następnej sekcji zebraliśmy trzy najczęstsze błędy WYSZUKAJ.PIONOWO.

3 typowe błędy WYSZUKAJ.PIONOWO i sposoby ich naprawy

Teraz, gdy nauczyłeś się korzystać z WYSZUKAJ.PIONOWO z innego skoroszytu, możesz napotkać kilka typowych błędów.

Oto trzy najczęstsze błędy i sposoby ich naprawy.

1. #Nie dotyczy Błąd

Błąd #N/D występuje, gdy wyszukiwana wartość nie zostanie znaleziona w tablicy tabeli. Aby naprawić ten błąd, możesz spróbować wykonać następujące czynności:

• Sprawdź pisownię szukanej wartości.

• Upewnij się, że wyszukiwana wartość znajduje się w pierwszej kolumnie tablicy tabeli.

• Sprawdź, czy wyszukiwana wartość nie jest sformatowana jako tekst w dwóch arkuszach.

• Upewnij się, że argument range_lookup jest ustawiony na FAŁSZ, aby znaleźć dokładne dopasowanie.

Powyższy przykład nie znalazł pasującej wartości dla wiśni w drugim skoroszycie.

2. #Odnośnik! Błąd numeru kolumny

# Ref! Błąd występuje, gdy argument col_index_num jest większy niż liczba kolumn w tablicy tabeli.

Aby naprawić ten błąd, sprawdź, czy argument col_index_num jest mniejszy lub równy liczbie kolumn w tabeli źródłowej.

Na poniższym obrazku col_index_num został ustawiony na 3, ale w zakresie odniesienia są tylko dwie kolumny.

3. Błędy cudzysłowów i nawiasów kwadratowych

Błąd cudzysłowów i nawiasów kwadratowych występuje, gdy wykonujesz WYSZUKAJ.PIONOWO z wartością zawierającą cudzysłowy lub nawiasy kwadratowe.

Aby naprawić ten błąd, możesz spróbować wykonać następujące czynności:

• Użyj podwójnych cudzysłowów wokół wartości wyszukiwania.

• Użyj funkcji CHAR(34) , aby wstawić podwójne cudzysłowy.

• Użyj funkcji JEŻELI.BŁĄD, aby zwrócić pustą komórkę, jeśli wyszukiwana wartość zawiera cudzysłowy lub nawiasy kwadratowe.

Gdy już zapoznasz się z tymi błędami, naprawienie ich jest proste! Aby jeszcze bardziej pomóc Ci zostać profesjonalistą VLOOKUP, w następnej sekcji przedstawiliśmy cztery wskazówki dotyczące zarządzania różnymi skoroszytami.

4 wskazówki dotyczące zarządzania różnymi skoroszytami

Podczas pracy z wieloma skoroszytami ważne jest, aby śledzić, który skoroszyt zawiera dane.

Oto nasze cztery najlepsze wskazówki, jak zachować zdrowie psychiczne:

1. Używaj konwencji nazewnictwa, aby łatwiej zapamiętać, które arkusze kalkulacyjne programu Excel zawierają dane.

2. Używaj w formułach odwołań bezwzględnych, aby mieć pewność, że zakres wyszukiwania odwołuje się do właściwych komórek, nawet jeśli przeniesiesz lub skopiujesz formułę do innej lokalizacji lub arkusza programu Excel.

3. Użyj funkcji JEŻELI.BŁĄD, aby obsłużyć wszelkie błędy #N/D, które mogą wystąpić, jeśli wyszukiwane wartości nie zostaną znalezione w danych źródłowych.

4. Rozważ zablokowanie komórek w skoroszycie referencyjnym, aby nie można ich było zastąpić.

Jak automatycznie aktualizować dane

Podczas pracy z wieloma plikami programu Excel ręczne aktualizowanie danych może być czasochłonne .

Jeśli chcesz, aby formuła WYSZUKAJ.PIONOWO aktualizowała się automatycznie po dodaniu lub usunięciu danych z innych skoroszytów, możesz użyć funkcji POŚREDNIE.

Oto jak:

• W formule WYSZUKAJ.PIONOWO zastąp argument tablica tabeli funkcją ADR.POŚR.

• W funkcji POŚREDNI użyj odwołania do komórki zawierającej tablicę tabel w innym skoroszycie.

Aby to osiągnąć za pomocą naszych przykładowych skoroszytów, wykonaj następujące kroki.

1. Użyj jednej komórki w kolumnie D, aby wprowadzić odwołanie do arkusza „Ceny” skoroszytu „Produkt” i zakresu komórek A1:B5 w następujący sposób: [Produkt.xlsx]Prices!A:B

2. W komórce C2 skoroszytu „Sprzedaż” użyj następującej formuły: = WYSZUKAJ.PIONOWO(A2; POŚR($D$1); 2; FAŁSZ)

3. Skopiuj formułę w dół (odwołania bezwzględne zapewniają zachowanie komórki z arkuszem kalkulacyjnym).

Te działania zapewniają, że zmiany w oryginalnym arkuszu zostaną automatycznie zaktualizowane w połączonych komórkach.

Ten rysunek przedstawia funkcję POŚREDNI połączoną z funkcją WYSZUKAJ.PIONOWO:

Zwróć uwagę, że w tym przykładzie użyto zakresu „A:B”, aby odwołać się do dwóch kolumn. Oznacza to, że po dodaniu kolejnych produktów i cen formuła będzie nadal działać w bieżącym skoroszycie.

Teraz, gdy już poznałeś możliwości WYSZUKAJ.PIONOWO, przyjrzyjmy się niektórym zaawansowanym zastosowaniom tej funkcji w tej sekcji!

Zaawansowane zastosowania funkcji WYSZUKAJ.PIONOWO

Przykłady w tym artykule zwracają jedno dopasowanie dla każdej wartości wyszukiwania. Jeśli chcesz pracować z wieloma dopasowaniami z innego skoroszytu, możesz zamiast tego użyć formuły tablicowej.

Funkcję WYSZUKAJ.PIONOWO można również łączyć z innymi funkcjami. Na przykład możesz osadzić go w funkcji JEŻELI w celu przeprowadzenia dodatkowej złożonej analizy.

Złożona analiza obejmuje techniki, takie jak testy chi-kwadrat i testy t dla par próbek. Oto przykład użycia testu chi-kwadrat w programie Excel:

Zaawansowani użytkownicy korzystają z WYSZUKAJ.PIONOWO do porównywania zestawów danych i identyfikowania niezgodności. Inne sposoby znajdziesz w tym artykule na temat znajdowania rozbieżności w Excelu .

Ale co, jeśli z jakiegokolwiek powodu nie chcesz używać WYSZUKAJ.PIONOWO? Masz szczęście! Istnieje kilka alternatyw, które omówimy w następnej sekcji.

2 główne alternatywy dla WYSZUKAJ.PIONOWO

Jeśli chodzi o wyszukiwanie wartości w różnych arkuszach, funkcja WYSZUKAJ.PIONOWO jest często podstawową funkcją. Istnieje jednak kilka alternatyw, które mogą być przydatne w określonych sytuacjach.

Dwa najczęstsze to:

• DOPASOWANIE INDEKSOWE

• XWYSZUKAJ

1. DOPASOWANIE INDEKSOWE

INDEKS i PODAJ.POZYCJĘ to potężna kombinacja dwóch funkcji, których można użyć do wyszukiwania wartości w tabeli. W przeciwieństwie do funkcji WYSZUKAJ.PIONOWO, która wymaga, aby wyszukiwana wartość znajdowała się w skrajnej lewej kolumnie tabeli, funkcja PODAJ.POZYCJĘ INDEKSU może wyszukiwać wartości w dowolnej kolumnie.

Oto jak to działa:

1. Funkcja INDEKS zwraca wartość z określonego zakresu na podstawie numeru wiersza i kolumny.

2. Funkcja PODAJ.POZYCJĘ zwraca pozycję szukanej wartości w określonym zakresie.

Aby użyć funkcji PODAJ.POZYCJĘ INDEKSOWĄ, musisz określić zakres, który chcesz przeszukać, wiersz, w którym chcesz znaleźć szukaną wartość, oraz kolumnę, w której chcesz zwrócić wynik.

Oto przykładowa formuła:

=INDEKS(ZakresTabeli;POZYCJA.POZYCJI(WartośćWyszukiwana;ZakresWyszukiwania;0);IndeksKolumny)

Aby użyć tego z przykładowymi arkuszami kalkulacyjnymi, oto formuła:

=INDEKS([Produkt.xlsx]Ceny!$B$1:$B$5; PODAJ.POZYCJĘ(A2; [Produkt.xlsx]Ceny!$A$1:$A$5; 0))

To rozkłada się jako:

• Funkcja PODAJ.POZYCJĘ wyszukuje wartość w komórce A2 (nazwa produktu) w zakresie A1:A5 w arkuszu „Ceny” skoroszytu „Produkt”.

• Funkcja INDEKS zwraca wartość z zakresu B1:B5 w arkuszu „Ceny” skoroszytu „Produkt” na podstawie numeru wiersza podanego przez funkcję PODAJ.POZYCJĘ.

To zdjęcie pokazuje formułę w akcji:

2. XWYSZUKAJ

XLOOKUP to nowsza funkcja wprowadzona w programie Excel 365 . Jest podobny do WYSZUKAJ.PIONOWO, ale z kilkoma dodatkowymi funkcjami.

Jedną z największych zalet XLOOKUP jest to, że może wyszukiwać wartości po lewej stronie kolumny wyszukiwania.

Oto jak to działa:

1. Funkcja XLOOKUP zwraca wartość z określonego zakresu na podstawie wartości wyszukiwania.

2. Możesz określić, czy chcesz dokładne dopasowanie, czy przybliżone dopasowanie.

3. Możesz także określić, co ma zostać zwrócone, jeśli nie zostanie znalezione dopasowanie.

Aby skorzystać z XLOOKUP, musisz określić szukaną wartość, zakres, który chcesz przeszukać, oraz kolumnę, w której chcesz zwrócić wynik.

Oto przykładowa formuła:

=XLOOKUP(LookupValue,LookupRange,ReturnRange,"Nie znaleziono",0)

Aby użyć tego z przykładowymi arkuszami kalkulacyjnymi, oto formuła:

=XLOOKUP(A2, [Produkt.xlsx]Ceny!$A$1:$A$5, [Produkt.xlsx]Ceny!$B$1:$B$5)

To rozkłada się jako:

• A2: Wartość, którą chcesz wyszukać (nazwa produktu).

• [Product.xlsx]Ceny!$A$1:$A$5: Zakres, w którym chcesz wyszukać wartość wyszukiwania (nazwy produktów w arkuszu „Ceny” skoroszytu „Produkt”).

• [Product.xlsx]Prices!$B$1:$B$5: Zakres zawierający zwracane wartości (ceny w arkuszu „Ceny” skoroszytu „Produkt”).

Oto formuła w działaniu:

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w wielu arkuszach

Jeśli musisz wykonać funkcję WYSZUKAJ.PIONOWO między dwoma skoroszytami, może być konieczne użycie funkcji WYSZUKAJ.PIONOWO na wielu arkuszach.

Na szczęście proces ten jest bardzo podobny do wykonywania WYSZUKAJ.PIONOWO między dwoma skoroszytami.

Istnieją cztery kroki.

Krok 1: Zidentyfikuj komponenty

Zamiast uwzględniać tablicę tabel, tak jak w przypadku jednego arkusza, należy dołączyć również nazwę arkusza.

Na przykład, jeśli chcesz wyszukać wartość z zakresu A2:B6 w arkuszu Jan w skoroszycie Sales_reports.xlsx, Twoja formuła będzie wyglądać następująco:

=WYSZUKAJ.PIONOWO(A2;[Sales_raporty.xlsx]styczeń!$A$2:$B$6;2;FAŁSZ)

Krok 2: Skonfiguruj swoje dane

Upewnij się, że Twoje dane są zorganizowane w sposób ułatwiający odwoływanie się do nich w wielu arkuszach.

Możesz rozważyć utworzenie różnych kart dla określonych danych referencyjnych.

Krok 3: Użyj funkcji WYSZUKAJ.PIONOWO

Po skonfigurowaniu danych możesz użyć funkcji WYSZUKAJ.PIONOWO, aby wyszukiwać i pobierać dane z innych arkuszy.

Pamiętaj tylko, aby podać nazwę arkusza w formule, jak pokazano powyżej.

(Opcjonalnie) Krok 4: Rozwiąż problemy z błędami #N/D

Jeśli podczas korzystania z funkcji WYSZUKAJ.PIONOWO w wielu arkuszach napotkasz błędy #N/D, dokładnie sprawdź swoją formułę, aby upewnić się, że wszystkie niezbędne arkusze i zakresy zostały prawidłowo odniesione.

Możesz także chcieć sprawdzić, czy przeprowadzasz dokładne dopasowanie, a nie wyszukiwanie zakresu.

Końcowe przemyślenia

Wiesz już, jak używać funkcji WYSZUKAJ.PIONOWO do wyszukiwania i pobierania danych w dwóch arkuszach kalkulacyjnych. Ta wszechstronna funkcja ułatwia wyszukiwanie wartości w jednym skoroszycie na podstawie odpowiednich wartości w innym.

Możliwości funkcji WYSZUKAJ.PIONOWO znacznie wykraczają poza pojedyncze arkusze robocze, obejmując różne skoroszyty w celu łączenia i analizowania danych.

Opanowanie tej funkcji jest znaczącym krokiem w kierunku biegłości w Excelu, ponieważ otwiera nowe możliwości zarządzania danymi i ich interpretacji.

Dzięki włączeniu funkcji, takich jak POŚREDNIE, INDEX-MATCH lub XLOOKUP, możesz jeszcze bardziej zwiększyć elastyczność i skuteczność wyszukiwania danych.

Kontynuuj ćwiczenie i eksploruj ogromne możliwości programu Excel; to tylko wierzchołek góry lodowej. Udanego polowania na dane!