Jak porównać dwie kolumny w programie Excel za pomocą funkcji WYSZUKAJ.PIONOWO

Podczas pracy z dużymi zestawami danych w programie Excel może być konieczne porównanie dwóch kolumn w celu znalezienia podobieństw lub różnic.

WYSZUKAJ.PIONOWO to potężna funkcja, która umożliwia wyszukiwanie pasujących danych między dwiema kolumnami. Funkcja pozwala wyszukać wartości w jednej kolumnie, które pojawiają się w innej.

W tym artykule pokazano, jak porównać dwie kolumny za pomocą funkcji WYSZUKAJ.PIONOWO, aby skutecznie analizować dane.

Spis treści

• Podstawy funkcji Vlookup
  • Funkcja wyszukiwania pionowego
• Przygotowanie danych do WYSZUKAJ.PIONOWO
  • Formatowanie danych
• Jak korzystać z funkcji WYSZUKAJ.PIONOWO, aby porównać dwie kolumny
  • Obsługa innych błędów
• Alternatywy dla funkcji WYSZUKAJ.PIONOWO do porównywania kolumn
  • 1. Korzystanie z funkcji indeksowania i dopasowywania
  • 2. Wykorzystanie XLOOKUP w Excelu
• Pięć wskazówek, jak poprawić wydajność funkcji WYSZUKAJ.PIONOWO
  • 1. Ogranicz zakres wyszukiwania
  • 2. Używaj odwołań bezwzględnych
  • 3. Posortuj swoje dane
  • 4. Użyj podwójnego WYSZUKAJ.PIONOWO
  • 5. Użyj formatowania warunkowego
• Zaawansowane zastosowania WYSZUKAJ.PIONOWO
• Nasze ostatnie słowo

Podstawy funkcji Vlookup

Załóżmy, że masz arkusz kalkulacyjny z dwiema listami elementów w kolumnie A i kolumnie B. Chcesz znaleźć elementy na liście 1, które pojawiają się również na liście 2.

Możesz sobie wyobrazić, że ręczna praca nad listami tysięcy elementów byłaby niezwykle czasochłonna. Na szczęście program Excel przynosi pomoc WYSZUKAJ.PIONOWO!

Termin WYSZUKAJ.PIONOWO oznacza wyszukiwanie w pionie. Funkcja porówna dwie kolumny, znajdzie dopasowania między nimi i zwróci powiązane wartości.

Funkcja wyszukiwania pionowego

Oto podstawowa składnia funkcji WYSZUKAJ.PIONOWO:

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

Oto podział elementów:

• szukana_wartość : wartość, którą chcesz wyszukać w pierwszej kolumnie tablicy_tabeli.

• tablica_tabeli : zakres komórek zawierających dane, które chcesz przeszukać.

• col_index_num : numer kolumny w table_array, z której chcesz zwrócić wartość.

• range_lookup : Opcjonalne. Może to być PRAWDA (dopasowanie przybliżone) lub FAŁSZ (dopasowanie dokładne). Wartość domyślna to PRAWDA.

Przygotowanie danych do WYSZUKAJ.PIONOWO

Przed użyciem funkcji WYSZUKAJ.PIONOWO do porównania dwóch kolumn w programie Excel należy przygotować dane.

Utwórz dwie oddzielne kolumny w arkuszu, w których chcesz porównać wartości. W tym artykule w naszych przykładach użyto kolumny A i kolumny B.

Oto przykładowe dane użyte w tym artykule:

Oto przykładowa tabela z nagłówkiem, który należy dodać do sekcji „Przygotowanie danych do WYSZUKAJ.PIONOWO”:

Lista 1 Lista 2
Pozycja 1 Pozycja 3
Pozycja 2
Pozycja 4 Pozycja 3 Pozycja 1
Pozycja 4 Pozycja 5

Formatowanie danych

Ważne jest, aby dane w obu kolumnach były sformatowane podobnie. Funkcja WYSZUKAJ.PIONOWO uwzględnia wielkość liter, co oznacza, że ​​wielkie i małe litery mają znaczenie.

Błędy dopasowania mogą wystąpić w wyniku końcowym, jeśli formatowanie jest niespójne.

Dobrym pomysłem jest również usunięcie zduplikowanych wartości lub pustych komórek, aby zminimalizować ryzyko błędów.

Aby usunąć zduplikowane wartości:

1. Wybierz kolumnę.

2. Przejdź do karty Dane na górnej wstążce.

3. Kliknij Usuń duplikaty w sekcji Narzędzia danych.

Przycisk może być trochę słabo widoczny. To zdjęcie ci pomoże:

Aby usunąć puste komórki:

1. Wybierz kolumnę.

2. Przejdź do karty Narzędzia główne na górnej wstążce.

3. Rozwiń menu „Znajdź i wybierz”.

4. Wybierz „Przejdź do oferty specjalnej”.

5. Wybierz „Puste” z opcji.

6. Kliknij „Usuń” w sekcji Komórki.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO, aby porównać dwie kolumny

Po przygotowaniu danych możesz napisać formułę WYSZUKAJ.PIONOWO, aby porównać dwie kolumny w programie Excel i zidentyfikować dopasowania. Wykonaj następujące kroki:

1. Wybierz komórkę w nowej kolumnie, w której chcesz wyświetlić wyniki porównania (np. komórka C2).

2. Wpisz następującą formułę: = WYSZUKAJ.PIONOWO(A2; B:B; 1; FAŁSZ))

3. Naciśnij klawisz Enter, aby zastosować formułę.

4. W przypadku pasującej wartości zostanie ona wyświetlona w tym samym wierszu kolumny wyników (np. Kolumna C).

5. Przeciągnij formułę z C2 w dół, aby skopiować ją na tyle komórek, ile potrzebujesz.

Jeśli korzystasz z naszych przykładowych danych, umieść wyniki porównania w trzeciej kolumnie. Formuła znajdzie trzy dopasowania spośród czterech elementów na liście 1.

Zauważ, że Pozycja 2 jest wyświetlana jako #N/D. Program Excel informuje nas, że dopasowanie nie ma zastosowania, tj. nie można go znaleźć.

To jest poprawne i jest użyteczną informacją o brakujących wartościach. Jednak niektórzy użytkownicy programu Excel mogą pomyśleć, że wystąpił problem z danymi lub błąd funkcji.

Dobrą praktyką jest wyświetlanie innego wskazania, że ​​nie znaleziono dopasowania. To może być po prostu puste miejsce.

Aby to zrobić, połącz funkcję WYSZUKAJ.PIONOWO z funkcją JEŻELI:

=JEŻELI.(WYSZUKAJ.PIONOWO(A1; B:B; 1; FAŁSZ); „”)

Funkcja JEŻELI wykrywa błąd #N/D i zastępuje dane wyjściowe pustym miejscem („”). Możesz także użyć funkcji ISNA lub mieć dodatkową logikę z funkcją JEŻELI.

Obsługa innych błędów

Funkcja WYSZUKAJ.PIONOWO może czasami generować inne błędy w wynikach porównania. #REF ! Błąd w programie Excel to prawdziwy problem z danymi.

Zwykle występuje, gdy podany zakres jest nieprawidłowy. Nasz przykład odwołuje się do całej kolumny, ale możesz także użyć pionowych zakresów komórek.

Upewnij się, że zakres wyszukiwania, do którego się odwołujesz, obejmuje wszystkie wartości, które chcesz porównać.

Alternatywy dla funkcji WYSZUKAJ.PIONOWO do porównywania kolumn

Istnieją dwie główne alternatywne funkcje wyszukiwania podczas porównywania dwóch kolumn w programie Excel w celu znalezienia dopasowań.

1. Korzystanie z funkcji indeksowania i dopasowywania

Zamiast korzystać z funkcji WYSZUKAJ.PIONOWO, możesz porównać dwie kolumny w programie Excel, łącząc funkcje INDEKS i PODAJ.POZYCJĘ.

Ta metoda zapewnia bardziej elastyczny sposób wyszukiwania danych i jest szczególnie przydatna podczas pracy z niesąsiadującymi kolumnami lub gdy indeks kolumny może ulec zmianie.

Oto składnia, którą należy umieścić w kolumnie wyników:

=INDEKS(zakres_zwrotu; PODAJ.POZYCJĘ(wyszukiwana_wartość; zakres_przeszukiwania; 0))

• Zakres_zwrotu : zakres komórek zawierających dane, które chcesz zwrócić.

• Lookup_value : wartość, którą chcesz wyszukać w zakresie wyszukiwania.

• Lookup_range : zakres komórek, w którym chcesz znaleźć szukaną wartość.

Korzystając z tych samych danych w poprzednich przykładach, zastępujemy formułę WYSZUKAJ.PIONOWO w następujący sposób:

=(INDEKS(B:B; PODAJ.POZYCJĘ(A2; B:B; 0)))

Funkcja PODAJ.POZYCJĘ zwraca względną pozycję szukanej wartości w zakresie wyszukiwania, a funkcja INDEKS zwraca odpowiednią wartość ze zwracanego zakresu.

Wyniki dla tych samych danych będą takie same, jak w przypadku WYSZUKAJ.PIONOWO, którego użyliśmy wcześniej. To zdjęcie pokazuje używane funkcje:

Możesz również zastąpić błąd #N/D komunikatem niestandardowym. Oto przykład użycia funkcji JEŻELI.BŁĄD.

=JEŻELI.BŁĄD(INDEKS(B:B; PODAJ.POZYCJĘ(A2; B:B; 0)), „Nie znaleziono”)

2. Wykorzystanie XLOOKUP w Excelu

Dla użytkowników programu Excel 365 lub Excel 2019 funkcja XLOOKUP jest alternatywnym testem logicznym do funkcji WYSZUKAJ.PIONOWO w celu znalezienia wspólnych wartości w dwóch kolumnach.

XLOOKUP zapewnia kilka korzyści. Możesz go używać do wyszukiwania danych zarówno w poziomie, jak iw pionie, pracować z niesąsiadującymi kolumnami i określać niestandardowe wartości błędów.

Składnia XLOOKUP to:

=XLOOKUP(szukana_wartość; szukany_zakres; zwracany_zakres; [jeżeli_nie_znaleziono]; [tryb_dopasowania]; [tryb_wyszukiwania])

• szukana_wartość : wartość, którą chcesz wyszukać w zakresie wyszukiwania.

• zakres_przeszukiwania : zakres komórek, w którym chcesz znaleźć szukaną wartość.

• zakres_zwrotu : zakres komórek zawierających dane, które chcesz zwrócić.

• W razie potrzeby dodaj niestandardowe wartości błędów, tryb dopasowania i parametry trybu wyszukiwania.

XLOOKUP znajdzie wyszukiwaną wartość w zakresie wyszukiwania i zwróci odpowiednią wartość z zakresu zwracanego.

Formuła jest szczególnie przydatna, gdy masz dwie listy osadzone w zestawach danych składających się z wielu kolumn. W naszych poprzednich przykładach listy znajdowały się w pierwszej i drugiej kolumnie, ale Twój arkusz może zawierać więcej danych.

Oto przykład z dwiema listami w pierwszej i trzeciej kolumnie arkusza kalkulacyjnego:

Formuła XLOOKUP wygląda następująco:

=XLOOKUP(A2;C:C;C:C,””)

Ten rysunek przedstawia wynik z porównaniem w kolumnie D. Pierwsza wartość jest obecna, ale brakuje drugiej.

Zauważ, że nie mam żadnych dodatkowych formuł błędów, ale brakująca wartość jest wyświetlana jako pusta. To dlatego, że używam czwartego parametru, który jest niestandardową wartością błędu. W tym przypadku jest to pusty ciąg.

Jako bonus pokażę ci, jak używać funkcji XLOOKUP do porównywania wierszy. Jeśli masz dwie listy, które znajdują się w wierszu 1 i wierszu 2, najprostsze użycie formuły wygląda następująco:

=XLOOKUP(B1; 2:2; 2:2; „”)

Ten obraz pokazuje wyniki w dwóch rzędach.

Dzięki ulepszonej funkcjonalności i elastyczności XLOOKUP jest doskonałą alternatywą dla VLOOKUP do porównywania list w Excelu w celu określenia, czy pasują.

Pięć wskazówek, jak poprawić wydajność funkcji WYSZUKAJ.PIONOWO

Optymalizacja funkcji WYSZUKAJ.PIONOWO może pomóc uniknąć długiego czasu oczekiwania i poprawić ogólną szybkość reakcji arkusza programu Excel.

Oto sześć porad i wskazówek, które możesz zastosować, aby poprawić wydajność funkcji WYSZUKAJ.PIONOWO w programie Excel.

1. Ogranicz zakres wyszukiwania

W przykładach użyłem całych kolumn, aby były proste. Jeśli pracujesz z dużą ilością danych, powinieneś tego unikać. Używanie pełnych kolumn może spowolnić proces obliczeń w programie Excel.

Zamiast tego spróbuj użyć dokładnego zakresu wymaganego dla danych (np. A1:A100). Zmniejsza to liczbę komórek, które funkcja WYSZUKAJ.PIONOWO musi ocenić.

2. Używaj odwołań bezwzględnych

Określając zakres danych (np. komórki od B2 do B5) należy używać odwołań bezwzględnych. Dzięki temu formuła jest spójna, a dane istnieją po skopiowaniu ich do wielu komórek.

Oto przykład:

=WYSZUKAJ.PIONOWO(A2; $B$2:$B$5; 1; FAŁSZ)

3. Posortuj swoje dane

Jeśli wiesz, że dane w kolumnie wyszukiwania są posortowane rosnąco, możesz użyć funkcji WYSZUKAJ.PIONOWO z wartością PRAWDA lub 1 dla argumentu „wyszukiwanie_zakresu”.

Spowoduje to, że program Excel wykona dopasowanie przybliżone, które jest szybsze niż dopasowanie dokładne we wszystkich komórkach. Należy jednak zachować ostrożność podczas korzystania z tej opcji, ponieważ nieprawidłowe sortowanie może prowadzić do nieprawidłowych wyników.

4. Użyj podwójnego WYSZUKAJ.PIONOWO

Możesz użyć dwóch funkcji WYSZUKAJ.PIONOWO, aby przyspieszyć proces wyszukiwania w programie Excel.

Pierwsze WYSZUKAJ.PIONOWO określi, czy szukana wartość istnieje, ustawiając „col_index_num” na 1, a „range_lookup” na TRUE.

Jeśli zwraca wartość PRAWDA, drugie WYSZUKAJ.PIONOWO pobiera żądaną wartość z wartością „wyszukiwanie_zakresu” ustawioną na PRAWDA.

5. Użyj formatowania warunkowego

Możesz użyć formatowania warunkowego w arkuszu programu Excel, aby wyróżnić pasujące lub brakujące wartości w określonej kolumnie. Możesz także zastosować kolory do unikalnych wartości. Ułatwi to odczytanie danych.

Menu formatowania warunkowego znajdziesz w grupie stylów na karcie Narzędzia główne.

Zaawansowane zastosowania WYSZUKAJ.PIONOWO

Sparowane próbki T-testy służą do porównania średnich dwóch powiązanych próbek. Ten film przedstawia ich użycie w programie Excel.

Jeśli masz dodatkowe dane lub zmienne, do których musisz się odnieść podczas analizy, możesz użyć funkcji WYSZUKAJ.PIONOWO, aby pobrać niezbędne wartości z innej tabeli lub arkusza.

Możesz na przykład użyć funkcji WYSZUKAJ.PIONOWO, aby pobrać informacje demograficzne lub warunki leczenia dla każdej sparowanej obserwacji w zbiorze danych.

Nasze ostatnie słowo

Do tej pory masz solidną wiedzę na temat korzystania z WYSZUKAJ.PIONOWO. Ta zaawansowana funkcja pozwala szybko identyfikować różnice i dopasowywać wartości między dwiema listami, dzięki czemu analiza danych jest bardziej wydajna i dokładna.

Zobaczyłeś także przykłady innych funkcji wyszukiwania i odwołań, takich jak XLOOKUP, INDEX i MATCH. Dodanie tych elementów do twoich umiejętności jeszcze bardziej wzmocni twoje możliwości analizy danych.

Ćwicz i doskonal swoje umiejętności WYSZUKAJ.PIONOWO, a wkrótce staniesz się ekspertem w porównywaniu kolumn w Excelu, oszczędzając czas i zwiększając produktywność.