Note: The other languages of the website are Google-translated. Back to English

Znajdź brakujące wartości

Istnieją przypadki, w których trzeba porównać dwie listy, aby sprawdzić, czy wartość listy A istnieje na liście B w programie Excel. Na przykład masz listę produktów i chcesz sprawdzić, czy produkty z Twojej listy istnieją na liście produktów dostarczonej przez Twojego dostawcę. Aby wykonać to zadanie, poniżej wymieniliśmy trzy sposoby poniżej, możesz wybrać ten, który Ci się podoba.

znajdź brakujące wartości 1

Znajdź brakujące wartości za pomocą MATCH, ISNA i IF
Znajdź brakujące wartości za pomocą funkcji WYSZUKAJ.PIONOWO, ISNA i IF
Znajdź brakujące wartości za pomocą funkcji LICZ.JEŻELI i JEŻELI


Znajdź brakujące wartości za pomocą MATCH, ISNA i IF

Aby dowiedzieć się jeśli wszystkie produkty z Twojej listy znajdują się na liście Twojego dostawcy jak pokazano na powyższym zrzucie ekranu, możesz najpierw użyć funkcji MATCH, aby pobrać pozycję produktu z Twojej listy (wartość listy A) na liście dostawców (lista B). MATCH zwróci błąd #N/D, gdy produkt nie zostanie znaleziony. Następnie możesz przesłać wynik do ISNA, aby przekonwertować błędy #N/D na PRAWDA, co oznacza, że ​​brakuje tych produktów. Funkcja JEŻELI zwróci wtedy oczekiwany wynik.

Ogólna składnia

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Uwaga: Możesz zmienić „Brakujące”, „Znalezione” na dowolne wartości według potrzeb.

  • szukana_wartość: Wartość MATCH użyta do pobrania jego pozycji, jeśli istnieje w zakres_wyszukiwania lub błąd #N/D, jeśli nie. Tutaj odnosi się do produktów z Twojej listy.
  • zakres_wyszukiwania: Zakres komórek do porównania z lookup_value. Tutaj odwołuje się do listy produktów dostawcy.

Aby dowiedzieć się jeśli wszystkie produkty z Twojej listy znajdują się na liście Twojego dostawcy, skopiuj lub wprowadź poniższą formułę w komórce H6 i naciśnij Wchodzę aby uzyskać wynik:

=JEŻELI(CZYN.NA(ZAPASUJ()30002,6 B $: 10 B $,0)), „Brak”, „Znaleziono”)

Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:

=JEŻELI(CZYN.NA(ZAPASUJ()G6,6 B $: 10 B $,0)), „Brak”, „Znaleziono”)

√ Uwaga: Znaki dolara ($) powyżej oznaczają odniesienia bezwzględne, co oznacza zakres_wyszukiwania w formule nie zmieni się po przeniesieniu lub skopiowaniu formuły do ​​innych komórek. Jednak nie ma żadnych znaków dolara dodanych do lookup_value ponieważ chcesz, aby był dynamiczny. Po wprowadzeniu formuły przeciągnij uchwyt wypełniania w dół, aby zastosować formułę do poniższych komórek.

znajdź brakujące wartości 2

Wyjaśnienie formuły

Tutaj używamy poniższej formuły jako przykładu:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

  • DOPASUJ (G8, $ B 6 $: B $ 10,0, XNUMX): Typ dopasowania 0 wymusza, aby funkcja MATCH zwróciła wartość liczbową wskazującą pozycję pierwszego dopasowania 3004, wartość w komórce G8, w tablicy 6 B $: 10 B $. Jednak w tym przypadku funkcja MATCH nie może znaleźć wartości w tablicy wyszukiwania, więc zwróci # N / A Błąd.
  • ISNA(DOPASUJ (G8, $ B 6 $: B $ 10,0, XNUMX)) = ISNA(# N / A): ISNA stara się ustalić, czy wartość jest błędem „#N/A”, czy nie. Jeśli tak, funkcja zwróci TURE; Jeśli wartość jest inna niż błąd „#N/A”, zwróci FALSE. Tak więc ta formuła ISNA powróci TURA.
  • GDYBY(ISNA(DOPASUJ (G8, $ B 6 $: B $ 10,0, XNUMX)), „Brak”, „Znaleziono”) = JEŻELI(TRUE, „Brak”, „Znaleziono”): Funkcja JEŻELI zwróci wartość Missing, jeśli porównanie dokonane przez ISNA i MATCH ma wartość TRUE, w przeciwnym razie zwróci wartość Found. Tak więc formuła wróci brakujący.

Znajdź brakujące wartości za pomocą funkcji WYSZUKAJ.PIONOWO, ISNA i IF

Aby dowiedzieć się, czy wszystkie produkty z twojej listy istnieją na liście twojego dostawcy, możesz zastąpić powyższą funkcję PODAJ.POZYCJĘ funkcją WYSZUKAJ.PIONOWO, ponieważ działa ona tak samo jak PODAJ.POZYCJĘ, że zwróci błąd #N/A, jeśli wartość nie istnieje w inną listę lub mówimy, że jej brakuje.

Ogólna składnia

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Uwaga: Możesz zmienić „Brakujące”, „Znalezione” na dowolne wartości według potrzeb.

  • szukana_wartość: Wartość WYSZUKAJ.PIONOWO użyta do pobrania swojej pozycji, jeśli istnieje w zakres_wyszukiwania lub błąd #N/D, jeśli nie. Tutaj odnosi się do produktów z Twojej listy.
  • zakres_wyszukiwania: Zakres komórek do porównania z lookup_value. Tutaj odwołuje się do listy produktów dostawcy.

Aby dowiedzieć się, czy wszystkie produkty z Twojej listy istnieją na liście Twojego dostawcy, skopiuj lub wprowadź poniższą formułę w komórce H6 i naciśnij Wchodzę aby uzyskać wynik:

=JEŻELI(CZY.NA(WYSZUKAJ.PIONOWO()30002,6 B $: 10 B $,1,FAŁSZ))), „Brak”, „Znaleziono”)

Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:

=JEŻELI(CZY.NA(WYSZUKAJ.PIONOWO()G6,6 B $: 10 B $,1,FAŁSZ))), „Brak”, „Znaleziono”)

√ Uwaga: Znaki dolara ($) powyżej oznaczają odniesienia bezwzględne, co oznacza zakres_wyszukiwania w formule nie zmieni się po przeniesieniu lub skopiowaniu formuły do ​​innych komórek. Jednak nie ma żadnych znaków dolara dodanych do lookup_value ponieważ chcesz, aby był dynamiczny. Po wprowadzeniu formuły przeciągnij uchwyt wypełniania w dół, aby zastosować formułę do poniższych komórek.

znajdź brakujące wartości 3

Wyjaśnienie formuły

Tutaj używamy poniższej formuły jako przykładu:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

  • WYSZUKAJ.PIONOWO(G8, $ B 6 $: $ B 10,1, XNUMX, FAŁSZ): Wyszukiwanie_zakresu FAŁSZYWY zmusza funkcję WYSZUKAJ.PIONOWO do wyszukania i zwrócenia wartości, która dokładnie pasuje 3004, wartość w komórce G8. Jeśli szukana_wartość 3004 istnieje w 1pierwsza kolumna tablicy 6 B $: 10 B $, WYSZUKAJ.PIONOWO zwróci tę wartość; W przeciwnym razie zwróci wartość błędu #N/D. Tutaj 3004 nie istnieje w tablicy, więc wynik byłby # N / A.
  • ISNA(WYSZUKAJ.PIONOWO(G8, $ B 6 $: $ B 10,1, XNUMX, FAŁSZ)) = ISNA(# N / A): ISNA stara się ustalić, czy wartość jest błędem „#N/A”, czy nie. Jeśli tak, funkcja zwróci TURE; Jeśli wartość jest inna niż błąd „#N/A”, zwróci FALSE. Tak więc ta formuła ISNA powróci TURA.
  • GDYBY(ISNA(WYSZUKAJ.PIONOWO(G8, $ B 6 $: $ B 10,1, XNUMX, FAŁSZ)), „Brak”, „Znaleziono”) = JEŻELI(TRUE, „Brak”, „Znaleziono”): Funkcja JEŻELI zwróci Brak, jeśli porównanie wykonane przez ISNA i WYSZUKAJ.PIONOWO ma wartość PRAWDA, w przeciwnym razie zwróci Znaleziono. Tak więc formuła wróci brakujący.

Znajdź brakujące wartości za pomocą funkcji LICZ.JEŻELI i JEŻELI

Aby dowiedzieć się, czy wszystkie produkty z Twojej listy znajdują się na liście dostawcy, możesz użyć prostszej formuły z funkcjami LICZ.JEŻELI i JEŻELI. Formuła wykorzystuje fakt, że Excel oceni dowolną liczbę z wyjątkiem zera (0) jako PRAWDA. Jeśli więc wartość istnieje na innej liście, funkcja LICZ.JEŻELI zwróci liczbę jej wystąpień na tej liście, a następnie JEŻELI przyjmie liczbę jako TURE; Jeśli wartość nie istnieje na liście, funkcja LICZ.JEŻELI zwróci 0, a JEŻELI przyjmie ją jako FAŁSZ.

Ogólna składnia

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Uwaga: Możesz zmienić „Znaleziono”, „Brakujące” na dowolne wartości.

  • zakres_wyszukiwania: Zakres komórek do porównania z lookup_value. Tutaj odwołuje się do listy produktów dostawcy.
  • szukana_wartość: Wartość LICZ.JEŻELI użyta do zwrócenia liczby wystąpień w zakres_wyszukiwania. Tutaj odnosi się do produktów z Twojej listy.

Aby dowiedzieć się, czy wszystkie produkty z Twojej listy istnieją na liście Twojego dostawcy, skopiuj lub wprowadź poniższą formułę w komórce H6 i naciśnij Wchodzę aby uzyskać wynik:

=JEŻELI(LICZ.JEŻELI(6 B $: 10 B $,30002), "Znaleziono", "Brak")

Możesz też użyć odwołania do komórki, aby uczynić formułę dynamiczną:

=JEŻELI(LICZ.JEŻELI(6 B $: 10 B $,G6), "Znaleziono", "Brak")

√ Uwaga: Znaki dolara ($) powyżej oznaczają odniesienia bezwzględne, co oznacza zakres_wyszukiwania w formule nie zmieni się po przeniesieniu lub skopiowaniu formuły do ​​innych komórek. Jednak nie ma żadnych znaków dolara dodanych do lookup_value ponieważ chcesz, aby był dynamiczny. Po wprowadzeniu formuły przeciągnij uchwyt wypełniania w dół, aby zastosować formułę do poniższych komórek.

znajdź brakujące wartości 4

Wyjaśnienie formuły

Tutaj używamy poniższej formuły jako przykładu:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

  • LICZ.JEŻELI($ B$6:$B$10,G8): Funkcja LICZ.JEŻELI zlicza, ile razy robi 3004, wartość w komórce G8 pojawia się w tablicy 6 B $: 10 B $. Najwyraźniej 3004 nie istnieje w tablicy, więc wynik byłby 0.
  • GDYBY(LICZ.JEŻELI($ B$6:$B$10,G8),"Znaleziono", "Brak") = JEŻELI(0, "Znaleziono", "Brak"): Funkcja JEŻELI oceni 0 jako FAŁSZ. Tak więc formuła wróci brakujący, wartość do zwrócenia, gdy pierwsze rozszerzenie ma wartość FALSE.

Powiązane funkcje

Funkcja Excel IF

Funkcja JEŻELI jest jedną z najprostszych i najbardziej przydatnych funkcji w skoroszycie programu Excel. Wykonuje prosty test logiczny, który zależy od wyniku porównania i zwraca jedną wartość, jeśli wynik jest PRAWDA, lub inną wartość, jeśli wynik jest FAŁSZ.

Funkcja Excel MATCH

Funkcja Excel PODAJ.POZYCJĘ wyszukuje określoną wartość w zakresie komórek i zwraca względną pozycję wartości.

Funkcja WYSZUKAJ.PIONOWO w Excelu

Funkcja WYSZUKAJ.PIONOWO programu Excel wyszukuje wartość, dopasowując ją do pierwszej kolumny tabeli i zwraca odpowiednią wartość z określonej kolumny w tym samym wierszu.

Funkcja LICZ.JEŻELI Excel

Funkcja LICZ.JEŻELI to funkcja statystyczna w programie Excel, która służy do zliczania liczby komórek spełniających kryterium. Obsługuje operatory logiczne (<>, =, > i <) oraz symbole wieloznaczne (? i *) do częściowego dopasowania.


Powiązane formuły

Wyszukaj wartość zawierającą określony tekst za pomocą symboli wieloznacznych

Aby znaleźć pierwsze dopasowanie, które zawiera określony ciąg tekstowy w zakresie w programie Excel, możesz użyć formuły INDEKS i PODAJ.POZYCJĘ ze znakami wieloznacznymi — gwiazdką (*) i znakiem zapytania (?).

Częściowe dopasowanie za pomocą funkcji WYSZUKAJ.PIONOWO

Czasami potrzebujesz programu Excel do pobierania danych na podstawie częściowych informacji. Aby rozwiązać problem, możesz użyć formuły WYSZUKAJ.PIONOWO wraz ze znakami wieloznacznymi - gwiazdką (*) i znakiem zapytania (?).

Przybliżone dopasowanie z INDEX i MATCH

Czasami musimy znaleźć przybliżone dopasowania w programie Excel, aby ocenić wydajność pracowników, ocenić wyniki uczniów, obliczyć opłatę pocztową na podstawie wagi itp. W tym samouczku omówimy, jak używać funkcji INDEKS i DOPASUJ do pobierania wyniki, których potrzebujemy.

Wyszukaj najbliższą wartość dopasowania z wieloma kryteriami

W niektórych przypadkach może być konieczne wyszukanie najbliższej lub przybliżonej wartości dopasowania na podstawie więcej niż jednego kryterium. Dzięki połączeniu funkcji INDEKS, DOPASUJ i JEŻELI możesz szybko wykonać to w programie Excel.


Najlepsze narzędzia biurowe

Kutools dla programu Excel - pomaga wyróżnić się z tłumu

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)

  • Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
  • Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
  • Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
  • Wprowadza wydajne zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL