Jak znaleźć pasujące wartości w programie Excel

Masz skoroszyt programu Excel(Excel) zawierający tysiące liczb i słów. Na pewno będą tam wielokrotności tej samej liczby lub słowa. Być może będziesz musiał ich znaleźć. Przyjrzymy się więc kilku sposobom znajdowania pasujących wartości w programie Excel 365(Excel 365) .

Omówimy znajdowanie tych samych słów lub liczb w dwóch różnych arkuszach i dwóch różnych kolumnach. Przyjrzymy się używaniu funkcji DOKŁADNE(EXACT) , PODAJ.POZYCJĘ(MATCH) i WYSZUKAJ.PIONOWO(VLOOKUP) . Niektóre metody, których użyjemy, mogą nie działać w internetowej wersji programu Microsoft Excel , ale wszystkie będą działać w wersji komputerowej. 

Co to jest funkcja Excela?(What’s An Excel Function?)

Jeśli korzystałeś wcześniej z funkcji, przejdź do przodu. 

Funkcja Excela(Excel) jest jak miniaplikacja. Stosuje szereg kroków do wykonania pojedynczego zadania. Najczęściej używane funkcje Excela(Excel) można znaleźć w zakładce Formuły . (Formulas )Tutaj widzimy je skategoryzowane według charakteru funkcji –

  • Autosumowanie
  • Ostatnio używane
  • Budżetowy
  • Logiczny
  • Tekst
  • Data i czas
  • Wyszukiwanie i odniesienie
  • Matematyka i trygonometria
  • Więcej funkcji.

Kategoria Więcej funkcji(More Functions ) zawiera kategorie Statystyka, Inżynieria, Kostka, Informacje, Zgodność i Sieć(Statistical, Engineering, Cube, Information, Compatibility, and Web) .

Dokładna funkcja(The Exact Function)

Zadaniem funkcji Exact jest przejrzenie wierszy dwóch kolumn i znalezienie pasujących wartości w komórkach Excela . (Excel)Dokładny(Exact) znaczy dokładny. Sama funkcja Exact(Exact) rozróżnia wielkość liter. Nowy Jork(New York ) i Nowy Jork(new york ) nie będą pasować.

W poniższym przykładzie znajdują się dwie kolumny tekstu – Tickets i Receipts . Tylko dla 10 zestawów tekstu mogliśmy je porównać, patrząc na nie. Wyobraź sobie, że istnieje 1000 lub więcej wierszy. Wtedy użyjesz funkcji Dokładne(Exact) .

Umieść kursor w komórce C2. Na pasku formuły wprowadź formułę 

=EXACT(E2:E10,F2:F10)

E2:E10 odnosi się do pierwszej kolumny wartości, a F2:F10 do kolumny znajdującej się tuż obok niej. Po naciśnięciu klawisza Enter(Enter) program Excel porówna dwie wartości w każdym wierszu i poinformuje nas, czy jest to zgodność ( True ), czy nie ( False ). Ponieważ użyliśmy zakresów zamiast tylko dwóch komórek, formuła przeniesie się do komórek poniżej i oceni wszystkie pozostałe wiersze.

Ta metoda jest jednak ograniczona. Porównuje tylko dwie komórki znajdujące się w tym samym rzędzie. Na przykład nie będzie porównywać tego, co jest w A2 z B3. Jak to zrobimy? MATCH może pomóc.

Funkcja DOPASUJ(The MATCH Function)

Funkcja PODAJ.POZYCJĘ(MATCH) może nam powiedzieć, gdzie w zakresie komórek znajduje się dopasowanie do określonej wartości.

Załóżmy, że w poniższym przykładzie chcemy dowiedzieć się, w jakim wierszu znajduje się konkretna jednostka magazynowa(SKU) ( Stock Keeping Unit ).

Jeśli chcemy sprawdzić, w jakim wierszu znajduje się AA003 , użyjemy formuły:

=MATCH(J1,E2:E9,0)

J1 odwołuje się do komórki z wartością, którą chcemy dopasować. E2:E9 odnosi się do zakresu wartości, które przeszukujemy. Zero ( 0 ) na końcu formuły informuje program Excel o szukaniu dokładnego dopasowania. Gdybyśmy dopasowywali liczby, moglibyśmy użyć 1 , aby znaleźć coś mniejszego niż nasze zapytanie, lub 2 , aby znaleźć coś większego niż nasze zapytanie.

A co gdybyśmy chcieli poznać cenę AA003 ?

Funkcja WYSZUKAJ.PIONOWO(The VLOOKUP Function)

Litera V w funkcji WYSZUKAJ.PIONOWO(VLOOKUP) oznacza pion. Oznacza to, że może wyszukiwać daną wartość w kolumnie. Może również zwrócić wartość w tym samym wierszu, co znaleziona wartość. 

Jeśli masz subskrypcję usługi Office 365 w kanale (Office 365)miesięcznym(Monthly) , możesz użyć nowszej funkcji XLOOKUP(XLOOKUP) . Jeśli masz tylko półroczną subskrypcję, będzie ona dostępna w lipcu 2020 r(July 2020) .

Wykorzystajmy te same dane inwentaryzacyjne i spróbujmy znaleźć cenę czegoś.

Tam, gdzie wcześniej szukaliśmy wiersza, wprowadź formułę:

=VLOOKUP(J1,E2:G9,3,FALSE)

J1 odwołuje się do komórki z dopasowywaną wartością. E2:G9 to zakres wartości, z którymi pracujemy. Ale WYSZUKAJ.PIONOWO(VLOOKUP) będzie szukać dopasowania tylko w pierwszej kolumnie tego zakresu. 3 odnosi się do trzeciej kolumny od początku zakresu.  

Więc kiedy wpiszemy SKU(SKU) w J1, WYSZUKAJ.PIONOWO(VLOOKUP) znajdzie dopasowanie i pobierze wartość z kolumny komórki 3 powyżej. FAŁSZ(FALSE) informuje program Excel , jakiego rodzaju dopasowania szukamy. FALSE oznacza, że ​​musi to być dokładne dopasowanie, podczas gdy PRAWDA(TRUE) mówi mu, że musi to być bliskie dopasowanie.

Jak znaleźć pasujące wartości w dwóch różnych arkuszach?(How Do I Find Matching Values in Two Different Sheets?)

Każda z powyższych funkcji może działać w dwóch różnych arkuszach, aby znaleźć pasujące wartości w programie Excel . Użyjemy funkcji DOKŁADNIE(EXACT) , aby pokazać, jak to zrobić. Można to zrobić za pomocą prawie każdej funkcji. Nie tylko te, o których tutaj mówiliśmy. Istnieją również inne sposoby łączenia komórek między różnymi arkuszami i skoroszytami(ways to link cells between different sheets and workbooks) .

Pracując na arkuszu Holders , wprowadzamy formułę 

=EXACT(D2:D10,Tickets!E2:E10)

D2:D10 to zakres, który wybraliśmy w arkuszu Posiadacze . (Holders)Po umieszczeniu po nim przecinka możemy kliknąć arkusz Bilety(Tickets) , przeciągnąć i wybrać drugi zakres. 

Zobacz, jak odnosi się do arkusza i zakresu jako Tickets!E2:E10 ? W tym przypadku każdy wiersz jest zgodny, więc wszystkie wyniki są True .

Jak jeszcze mogę korzystać z tych funkcji?(How Else Can I Use These Functions?)

Gdy opanujesz te funkcje dopasowywania i znajdowania rzeczy, możesz zacząć z nimi robić wiele różnych rzeczy. Przyjrzyj się również używaniu razem funkcji INDEX i PODAJ.POZYCJĘ,(using the INDEX and MATCH functions) aby zrobić coś podobnego do funkcji WYSZUKAJ.PIONOWO(VLOOKUP) .

Masz kilka fajnych wskazówek dotyczących używania funkcji programu Excel(Excel) do znajdowania pasujących wartości w programie Excel ? Może pytanie, jak zrobić więcej? Napisz(Drop) do nas w komentarzach poniżej.



About the author

Jestem inżynierem oprogramowania i ekspertem od Windows 10. Mam ponad dwuletnie doświadczenie w pracy ze smartfonami, Windows 10 i Microsoft Edge. Moim głównym celem jest sprawienie, aby Twoje urządzenia działały lepiej i szybciej. Pracowałem przy różnych projektach dla firm takich jak Verizon, IMac, HP, Comcast i wielu innych. Jestem również certyfikowanym instruktorem szkoleń w chmurze Microsoft Azure.



Related posts