4 funkcje wyszukiwania Excel do wydajnego wyszukiwania arkuszy kalkulacyjnych

4 funkcje wyszukiwania Excel do wydajnego wyszukiwania arkuszy kalkulacyjnych

Przez większość czasu wyszukuję a Microsoft Excel arkusz kalkulacyjny jest całkiem prosty. 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, korzystając z jednej z tych czterech funkcji wyszukiwania.





Gdy już wiesz, jak wyszukiwać w Excelu za pomocą wyszukiwania, nie ma znaczenia, jak duże są Twoje arkusze kalkulacyjne, zawsze będziesz mógł coś znaleźć w Excelu!



1. Funkcja WYSZUKAJ.PIONOWO

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





Oto składnia funkcji:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [wyszukiwana_wartość] to informacja, którą już posiadasz. Na przykład, jeśli chcesz wiedzieć, w jakim stanie znajduje się miasto, będzie to nazwa miasta.
  • [tablica_tabela] pozwala określić komórki, w których funkcja będzie szukać wartości wyszukiwania i zwracanych. Wybierając zakres, upewnij się, że pierwsza kolumna zawarta w Twojej tablicy to ta, która będzie zawierać Twoją wartość wyszukiwania!
  • [col_index_num] to numer kolumny zawierającej zwracaną wartość.
  • [Zakres wyszukiwania] jest argumentem opcjonalnym i przyjmuje 1 lub 0. Jeśli wprowadzisz 1 lub pominiesz ten argument, funkcja szuka wprowadzonej wartości lub następnej najniższej liczby. Tak więc na poniższym obrazku funkcja WYSZUKAJ.PIONOWO szukająca wyniku SAT 652 zwróci 646, ponieważ jest to najbliższa liczba na liście, która jest mniejsza niż 652, a [range_lookup] domyślnie wynosi 1.

Przyjrzyjmy się, jak możesz tego użyć. Ten arkusz kalkulacyjny zawiera numery identyfikacyjne, imiona i nazwiska, miasto, stan i wyniki SAT. Załóżmy, że chcesz znaleźć wynik SAT osoby o nazwisku „Winters”. WYSZUKAJ.PIONOWO ułatwia. Oto formuła, której użyjesz:



=VLOOKUP('Winters', C2:F101, 4, 0)

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

Oto wynik:



Zwrócił 651, wynik SAT należący do studenta Kennedy'ego Wintersa, który jest w rzędzie 92 (wyświetlony we wstawce powyżej). Przewijanie w poszukiwaniu nazwy zajęłoby znacznie więcej czasu niż szybkie wpisanie składni!

Uwagi dotyczące funkcji WYSZUKAJ.PIONOWO

Podczas korzystania z funkcji WYSZUKAJ.PIONOWO warto pamiętać o kilku rzeczach. Upewnij się, że pierwsza kolumna w 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.



Należy również pamiętać, że funkcja WYSZUKAJ.PIONOWO zwróci tylko jedną wartość. Gdybyś użył „Georgia” jako wartości wyszukiwania, zwróciłby wynik pierwszego ucznia z Gruzji i nie wskazałby, że w rzeczywistości jest dwóch uczniów z Gruzji.

2. Funkcja WYSZUKAJ.POZIOMO

Gdzie WYSZUKAJ.PIONOWO znajduje odpowiadające wartości w innej kolumnie, WYSZUKAJ.POZIOMO znajduje odpowiadające wartości w innym wierszu. Ponieważ zwykle najłatwiej jest przeglądać nagłówki kolumn, aż znajdziesz właściwy i użyć filtra, aby znaleźć to, czego szukasz, WYSZUKAJ.POZIOMO najlepiej sprawdza się, gdy masz naprawdę duże arkusze kalkulacyjne lub pracujesz z wartościami uporządkowanymi według czasu .

Oto składnia funkcji:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [wyszukiwana_wartość] to wartość, którą znasz i chcesz znaleźć odpowiednią wartość.
  • [tablica_tabela] to komórki, w których chcesz przeszukać.
  • [numer_indeksu_wiersza] określa wiersz, z którego będzie pochodzić zwracana wartość.
  • [Zakres wyszukiwania] jest takie samo jak w funkcji WYSZUKAJ.PIONOWO, pozostaw puste, aby uzyskać najbliższą wartość, jeśli to możliwe, lub wprowadź 0, aby szukać tylko dokładnych dopasowań.

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

=HLOOKUP(2013, A1:P51, 24)

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

Minnesotańczycy uzyskali w 2013 roku średnio 1014 punktów. Zauważ, że 2013 nie jest ujęty w cudzysłów, ponieważ jest liczbą, a nie ciągiem. Ponadto 24 pochodzi z Minnesoty, która jest w 24. rzędzie.

Oto jak obliczyć średnią ważoną w Excelu .

Uwagi dotyczące funkcji WYSZUKAJ.POZIOMO

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

3-4. Funkcje INDEKS i PODAJ.POZYCJĘ

INDEKS i PODAJ.POZYCJĘ to dwie różne funkcje, ale gdy są używane razem, mogą znacznie przyspieszyć przeszukiwanie dużego arkusza kalkulacyjnego. Obie funkcje mają wady, ale łącząc je, zbudujemy mocne strony obu.

Najpierw jednak składnia obu funkcji:

=INDEX([array], [row_number], [column_number])
  • [szyk] to tablica, w której będziesz przeszukiwać.
  • [Numer wiersza] oraz [numer_kolumny] można użyć do zawężenia wyszukiwania (przyjrzymy się temu za chwilę).
=MATCH([lookup_value], [lookup_array], [match_type])
  • [wyszukiwana_wartość] to wyszukiwane hasło, które może być ciągiem lub liczbą.
  • [wyszukiwana_tablica] to tablica, w której program Microsoft Excel będzie szukał wyszukiwanego terminu.
  • [typ_dopasowania] jest opcjonalnym argumentem, który może mieć wartość 1, 0 lub -1. 1 zwróci największą wartość, która jest mniejsza lub równa wyszukiwanemu hasłu. 0 zwróci tylko 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 zamierzamy używać tych dwóch funkcji razem, więc przedstawię to tutaj. PODAJ.POZYCJĘ przyjmuje wyszukiwany termin i zwraca odwołanie do komórki. Na poniższym obrazku widać, że podczas wyszukiwania wartości 646 w kolumnie F funkcja MATCH zwraca 4.

Z drugiej strony INDEX działa odwrotnie: pobiera odwołanie do komórki i zwraca w nim wartość. Widać tutaj, że gdy zostanie poproszony o zwrócenie szóstej komórki w kolumnie Miasto, INDEKS zwraca „Zakotwiczenie”, wartość z wiersza 6.

Zamierzamy połączyć te dwa elementy, tak aby funkcja PODAJ.POZYCJĘ zwracała odwołanie do komórki, a INDEKS użyje tego odwołania do wyszukania wartości w komórce. Powiedzmy, że pamiętasz, że był uczeń o nazwisku Waters i chcesz zobaczyć, jaki był jego wynik. Oto formuła, której użyjemy:

jak pobrać stronę internetową w trybie offline
=INDEX(F:F, MATCH('Waters', C:C, 0))

Zauważysz, że typ dopasowania jest tutaj ustawiony na 0. Kiedy szukasz sznurka, właśnie tego będziesz chciał użyć. Oto, co otrzymujemy, gdy uruchamiamy tę funkcję:

Jak widać na wstawce, Owen Waters zdobył 1720, czyli liczbę, która pojawia się po uruchomieniu funkcji. Może to nie wydawać się zbyt przydatne, gdy możesz po prostu przejrzeć kilka kolumn, ale wyobraź sobie, ile czasu zaoszczędziłbyś, gdybyś musiał to zrobić 50 razy na duży arkusz kalkulacyjny bazy danych który zawierał kilkaset kolumn!

Niech rozpocznie się wyszukiwanie w Excelu

Microsoft Excel ma wiele niezwykle rozbudowane funkcje do manipulowania danymi, a cztery wymienione powyżej po prostu zarysowują powierzchnię. Nauka korzystania z nich znacznie ułatwi Ci życie.

Jeśli naprawdę chcesz opanować program Microsoft Excel, możesz naprawdę skorzystać z posiadania ściągawki Essential Excel pod ręką!

Źródło obrazu: Cico/ Shutterstock

Udział Udział Ćwierkać E-mail Canon kontra Nikon: która marka aparatu jest lepsza?

Canon i Nikon to dwie największe marki w branży aparatów fotograficznych. Ale która marka oferuje lepszą gamę aparatów i obiektywów?

Czytaj dalej Powiązane tematy
  • Wydajność
  • Arkusz
  • Microsoft Excel
  • Sztuczki wyszukiwania
O autorze Ian Buckley(216 opublikowanych artykułów)

Ian Buckley jest niezależnym dziennikarzem, muzykiem, performerem i producentem wideo mieszkającym w Berlinie w Niemczech. Kiedy nie pisze ani nie występuje na scenie, majstruje przy elektronice DIY lub kodowaniu w nadziei, że zostanie szalonym naukowcem.

Więcej od Iana Buckleya

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować