4 Funkcje wyszukiwania Excel do efektywnego wyszukiwania arkuszy kalkulacyjnych

Przeszukiwanie arkusza kalkulacyjnego Microsoft Excel jest przez większość czasu dość łatwe. Jeśli nie możesz po prostu przejrzeć wierszy i kolumn, możesz użyć Ctrl + F, aby go wyszukać. Jeśli pracujesz z naprawdę dużym arkuszem kalkulacyjnym, możesz zaoszczędzić dużo czasu na skorzystaniu z jednej z tych czterech funkcji wyszukiwania.

Gdy wiesz, jak wyszukiwać w Excelu za pomocą odnośnika, nie będzie miało znaczenia, jak duże będą Twoje arkusze kalkulacyjne, zawsze będziesz w stanie znaleźć coś w Excelu!

1. Funkcja WYSZUKAJ.PIONOWO

Ta funkcja pozwala określić kolumnę i wartość i zwróci wartość z odpowiedniego wiersza innej kolumny (jeśli to nie ma sensu, za chwilę stanie się jasne). Dwa przykłady, w których możesz to zrobić, to wyszukiwanie nazwiska pracownika według jego numeru pracownika lub znalezienie numeru telefonu przez podanie nazwiska.

Oto składnia funkcji:

= WYSZUKAJ.PIONOWO ([wartość_wyszukiwania], [tablica_tabeli], [numer_indeksu_kolumny], [przegląd_zakresu])

• [wartość_wyszukiwania] to informacja, którą już masz. Na przykład, jeśli chcesz wiedzieć, w jakim stanie znajduje się miasto, będzie to nazwa miasta.
• [tablica_tablicy] pozwala określić komórki, w których funkcja będzie szukać wartości zwracanych i zwracanych. Wybierając zakres, upewnij się, że pierwsza kolumna zawarta w tablicy to ta, która będzie zawierać wartość wyszukiwania!
• [col_index_num] jest numerem kolumny zawierającej wartość zwracaną.
• [Zakres wyszukiwania] jest opcjonalnym argumentem i przyjmuje 1 lub 0. W przypadku wprowadzenia 1 lub pominięcia tego argumentu funkcja szuka wprowadzonej wartości lub następnej najniższej liczby. Na poniższym obrazku WYSZUKAJ.PIONOWO szukający wyniku SAT 652 zwróci 646, ponieważ jest to najbliższa liczba na liście, która jest mniejsza niż 652, a [zakres_wyszukania] domyślnie wynosi 1.

Zobaczmy, jak możesz tego użyć. Ten arkusz kalkulacyjny zawiera numery identyfikacyjne, imiona i nazwiska, miasto, stan i wyniki SAT. Powiedzmy, że chcesz znaleźć wynik SAT osoby o nazwisku “Zimy.” WYSZUKAJ.PIONOWO ułatwia. Oto wzór, którego użyjesz:

= WYSZUKAJ.PIONOWO („Winters”, C2: F101, 4, 0)

Ponieważ wyniki SAT są czwartą kolumną od kolumny z nazwiskiem, 4 jest argumentem indeksu kolumny. Pamiętaj, że gdy szukasz tekstu, dobrym pomysłem jest ustawienie [zakres_wyszukiwania] na 0. Bez tego możesz uzyskać złe wyniki.

Oto wynik:

Zwrócił 651, wynik SAT należący do ucznia o imieniu Kennedy Winters, który jest w rzędzie 92 (wyświetlany na wstawce powyżej). Przewinięcie w poszukiwaniu nazwy zajęłoby znacznie więcej czasu niż szybkie wpisanie składni!

Uwagi na temat WYSZUKAJ.PIONOWO

Podczas korzystania z funkcji WYSZUKAJ.PIONOWO warto pamiętać o kilku rzeczach. Upewnij się, że pierwsza kolumna w Twoim zakresie to ta, która zawiera wartość wyszukiwania. Jeśli nie ma go w pierwszej kolumnie, funkcja zwróci nieprawidłowe wyniki. Jeśli Twoje kolumny są dobrze zorganizowane, nie powinno to stanowić problemu.

Pamiętaj również, że funkcja WYSZUKAJ.PIONOWO zwróci tylko jedną wartość. Jeśli użyłeś “Gruzja” jako wartość wyszukiwania zwróciłoby wynik pierwszego studenta z Gruzji i nie dałoby żadnych wskazówek, że w rzeczywistości jest dwóch studentów z Gruzji.

2. Funkcja WYSZUKAJ.PIONOWO

Gdy WYSZUKAJ.PIONOWO znajduje odpowiednie wartości w innej kolumnie, WYSZUKAJ.PIONOWO znajduje odpowiednie wartości w innym wierszu. Ponieważ zwykle najłatwiej jest skanować nagłówki kolumn, dopóki nie znajdziesz właściwego, i użyj filtru, aby znaleźć to, czego szukasz, WYSZUKAJ.PIONOWO najlepiej jest używać, gdy masz naprawdę duże arkusze kalkulacyjne lub pracujesz z wartościami uporządkowanymi według czasu.

Oto składnia funkcji:

= WYSZUKAJ.PIONOWO ([wartość_wyszukiwania], [tablica_tabeli], [numer_indeksu wierszy], [przegląd_zakresu])

• [wartość_wyszukiwania] to wartość, którą znasz i chcesz znaleźć odpowiednią wartość dla.
• [tablica_tablicy] to komórki, w których chcesz wyszukiwać.
• [row_index_num] określa wiersz, z którego będzie zwracana wartość.
• [Zakres wyszukiwania] jest taki sam, jak w WYSZUKAJ.PIONOWO, pozostaw puste, aby uzyskać najbliższą wartość, jeśli to możliwe, lub wprowadź 0, aby wyszukać tylko dokładne dopasowania.

Ten arkusz kalkulacyjny zawiera wiersz dla każdego stanu, wraz z wynikiem SAT w latach 2000-2014. Możesz użyć WYSZUKAJ.PIONOWO, aby znaleźć średni wynik w Minnesocie w 2013 roku. Oto jak to zrobimy:

= WYSZUKAJ.PIONOWO (2013, A1: P51, 24)

Jak widać na poniższym obrazku, wynik jest zwracany:

Minnesotanie osiągnęli średnio 1014 punktów w 2013 roku. Pamiętaj, że 2013 nie jest w cudzysłowie, ponieważ jest liczbą, a nie łańcuchem. Ponadto 24 pochodzi z Minnesoty w 24 rzędzie.

Uwagi na temat WYSZUKAJ.PIONOWO

Podobnie jak w przypadku WYSZUKAJ.PIONOWO, wartość wyszukiwania musi znajdować się w pierwszym wierszu tablicy tabel. Rzadko jest to problem z WYSZUKAJ.PIONOWO, ponieważ zwykle używasz tytułu kolumny jako wartości wyszukiwania. WYSZUKAJ.PIONOWO zwraca również tylko jedną wartość.

3-4. Funkcje INDEX i MATCH

INDEX i MATCH to dwie różne funkcje, ale gdy są używane razem, mogą znacznie przyspieszyć wyszukiwanie w dużym arkuszu kalkulacyjnym. Obie funkcje mają wady, ale łącząc je, wykorzystamy mocne strony obu.

Najpierw jednak składnia obu funkcji:

= INDEKS ([tablica], [numer_wiersza], [numer_kolumny])

• [szyk] to tablica, w której będziesz szukać.
• [Numer wiersza] i [numer_kolumny] mogą być użyte do zawężenia wyszukiwania (przyjrzymy się temu za chwilę).

= MATCH ([wartość_wyszukiwania], [tablica_wyszukiwania], [typ_pasowania])

• [wartość_wyszukiwania] to wyszukiwane hasło, które może być ciągiem lub liczbą.
• [lookup_array] to tablica, w której program Microsoft Excel będzie szukał wyszukiwanego terminu.
• [typ_typu] jest opcjonalnym argumentem, który może wynosić 1, 0 lub -1. 1 zwróci największą wartość, która jest mniejsza lub równa wyszukiwanemu terminowi. 0 zwróci tylko twój dokładny termin, a -1 zwróci najmniejszą wartość, która jest większa lub równa wyszukiwanemu terminowi.

Może nie być jasne, w jaki sposób będziemy korzystać z tych dwóch funkcji razem, więc opiszę to tutaj. PODAJNIK przyjmuje wyszukiwane hasło i zwraca odwołanie do komórki. Na poniższym obrazku widać, że podczas wyszukiwania wartości 646 w kolumnie F funkcja PODAJNIK zwraca 4.

Z drugiej strony INDEKS robi coś przeciwnego: pobiera odwołanie do komórki i zwraca w nim wartość. Widać tutaj, że gdy zostanie poproszony o zwrócenie szóstej komórki kolumny Miasto, zwraca INDEX “Zamocowanie,” wartość z wiersza 6.

To, co zrobimy, to połączenie tych dwóch elementów, aby funkcja MATCH zwraca odwołanie do komórki, a INDEKS używa tego odwołania do wyszukania wartości w komórce. Załóżmy, że pamiętasz, że był uczeń o nazwisku Waters i chcesz zobaczyć, jaki był wynik tego ucznia. Oto wzór, którego użyjemy:

= INDEKS (F: F, MATCH („Wody”, C: C, 0))

Zauważysz, że typ dopasowania jest tutaj ustawiony na 0. Kiedy szukasz łańcucha, tego właśnie chcesz użyć. Oto, co otrzymujemy po uruchomieniu tej funkcji:

Jak widać na początku, Owen Waters zdobył 1720, liczbę, która pojawia się po uruchomieniu funkcji. Może to nie wydawać się szczególnie przydatne, gdy możesz po prostu spojrzeć na kilka kolumn, ale wyobraź sobie, ile czasu byś zaoszczędził, gdybyś musiał to zrobić 50 razy na dużym arkuszu kalkulacyjnym bazy danych Excel vs. Dostęp - czy arkusz kalkulacyjny może zastąpić bazę danych? Excel vs. Dostęp - czy arkusz kalkulacyjny może zastąpić bazę danych? Jakiego narzędzia należy użyć do zarządzania danymi? Access i Excel oferują zarówno filtrowanie danych, sortowanie, jak i zapytania. Pokażemy Ci, który najlepiej pasuje do twoich potrzeb. który zawierał kilkaset kolumn!

Rozpocznij wyszukiwanie w programie Excel

Microsoft Excel ma wiele niezwykle potężnych funkcji do manipulowania danymi, a cztery wymienione powyżej tylko zarysowują powierzchnię. Nauczenie się ich używania znacznie ułatwi ci życie.

Jeśli naprawdę chcesz opanować program Microsoft Excel, możesz naprawdę skorzystać z zachowania Ściągawka Essential Excel Ściągawka Essential Microsoft Excel Formuły i Ściągawka Ściągawka Essential Microsoft Excel Ściągnij ten Ściągawka formuł Excel, aby przejść przez skrót ulubiony program do obsługi arkuszy kalkulacyjnych na świecie. pod ręką!

Zdjęcie kredytowe: Cico / Shutterstock