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 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.
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.
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.
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 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 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 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
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
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 karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
