Znajdź brakujące wartości w Excelu

Anonim

W tym artykule dowiemy się, jak wyszukać brakujące wartości w tabeli za pomocą formuły w programie Excel.

Na przykład mamy listę wartości i potrzebujemy potwierdzenia, gdzie znajduje się wartość na liście. W tym celu rozważymy sytuację i zaproponujemy kilka formuł, aby zrobić to samo. Będziemy używać różnych operacji funkcji w zależności od łatwości działania tablicy table_array.

W użyciu będzie następująca funkcja. Więc dowiedz się trochę o nich przed użyciem.

  1. JEŚLI funkcja
  2. LICZ.JEŻELI funkcja
  3. funkcja ISNA
  4. Funkcja WYSZUKAJ.PIONOWO
  5. Funkcja PODAJ.POZYCJĘ

Skonstruujemy z tego formułę. Najpierw szukana wartość jest przeszukiwana w konkretnej kolumnie tablicy tabeli. Wtedy dopasowane wartości dadzą nam potwierdzenie za pomocą funkcji JEŻELI. Funkcja JEŻELI zwraca potwierdzenie przy użyciu wartości „Czy istnieje” i „Brak”.

Pierwsza metoda: Użycie funkcji LICZ.JEŻELI i JEŻELI

Użyj ogólnej formuły

= JEŻELI ( LICZ.JEŻELI ( lista , wartość_komórki ), "Jest tam" , "Brak" )

Wyjaśnienie:

  • Funkcja LICZ.JEŻELI przechowuje liczbę wartości_komórki na liście i zwraca liczbę do funkcji JEŻELI.
  • Funkcja JEŻELI uważa 0 za FAŁSZ, a każdą inną liczbę całkowitą inną niż 0 za PRAWDĘ.
  • Funkcja JEŻELI zwraca "Is there" jako wartość, jeśli prawda i "Brak" jako wartość, jeśli False.

Przykład:

Przejdźmy przez to, używając wzoru w przykładzie.

Tutaj mamy tabelę i musimy z niej wydobyć informacje.

Tutaj potrzebujemy potwierdzenia przez listę ID. Dlatego używamy nazwanego zakresu dla listy identyfikatorów. NS nazwany zakres użycia dla C2:C14.

Więc użyjemy wzoru, aby uzyskać całkowitą kwotę

= JEŻELI ( LICZ.JEŻELI ( ID , G4 ) , "Jest tam" , "Brak" )

Wyjaśnienie wzoru:

  1. Funkcja LICZ.JEŻELI zachowuje na liście liczbę o identyfikatorze 900 i zwraca liczbę do funkcji JEŻELI.
  2. Funkcja JEŻELI uważa 0 za FAŁSZ, a każdą inną liczbę całkowitą inną niż 0 za PRAWDA
  3. Funkcja JEŻELI zwraca "Is there" jako wartość, jeśli prawda i "Brak" jako wartość, jeśli False.

W tym przypadku tablice argumentów funkcji są podawane jako odwołanie do komórki.

Jak widać, formuła zwraca wartości dla numeru ID 807 i 953. Ale zwraca wartość Missing dla numeru ID 900.

Druga metoda: Korzystanie z funkcji ISNA i WYSZUKAJ.PIONOWO.

Składnia formuły:

= JEŻELI ( ISNA ( WYSZUKAJ.PIONOWO ( wartość_komórki , lista , 1 , 0 )), "Brak" , "Jest tam" )

Wyjaśnienie wzoru:

  1. Funkcja WYSZUKAJ.PIONOWO wyszukuje wartość komórki w pierwszej kolumnie listy tablica_tabela. Funkcja zwraca wartość, jeśli zostanie znaleziona, w przeciwnym razie zwraca błąd #N/D.
  2. Funkcja ISNA przechwytuje błąd #N/D i zwraca TRUE, jeśli istnieje błąd #N/D lub zwraca FALSE.
  3. Funkcja JEŻELI zwraca „Istnieje” jako wartość, jeśli FAŁSZ i „Brak” jako wartość, jeśli PRAWDA.

Jak widać na powyższym zdjęciu. Formuła zwraca „Is There” dla dopasowanych identyfikatorów 807 i 953. Ale zwraca „Brak” dla niedopasowanych identyfikatorów 900.

Trzecia metoda: Wykorzystanie funkcji ISNA & MATCH.

Składnia formuły:

= JEŻELI ( ISNA ( DOPASUJ ( wartość_komórki , lista , 0 ) ), "Brak" , "Jest" )

Wyjaśnienie wzoru:

  1. Funkcja PODAJ.POZYCJĘ wyszukuje wartość komórki na liście tablica_tabela. Funkcja zwraca wartość, jeśli zostanie znaleziona, lub zwraca błąd #N/D.
  2. Funkcja ISNA przechwytuje błąd #N/D i zwraca TRUE, jeśli istnieje błąd #N/D lub zwraca FALSE.
  3. Funkcja JEŻELI zwraca „Istnieje” jako wartość, jeśli FAŁSZ i „Brak” jako wartość, jeśli PRAWDA.

Jak widać na powyższym zdjęciu. Formuła zwraca „Is There” dla dopasowanych identyfikatorów 807 i 953. Ale zwraca „Brak” dla niedopasowanych identyfikatorów 900.

Powyższe wyjaśniono 3 przykłady, aby znaleźć brakujące wartości na liście w programie Excel. Wszystkie trzy formuły działają dobrze, ale jest kilka punktów, na które należy zwrócić uwagę.

Uwagi:

  1. Funkcja WYSZUKAJ.PIONOWO nie wyszukuje w lewo w table_array.
  2. Funkcja LICZ.JEŻELI obsługuje symbole wieloznaczne ( * , ? ), który pomaga w wydobywaniu wartości z fraz.
  3. Wartości nieliczbowe należy podawać w cudzysłowach ("wartość") lub użyj referencji_komórki…
  4. Zobacz całą listę, ponieważ funkcja zwraca wartości wszędzie tam, gdzie pasuje.
  5. Argument tablicowy funkcji może być podany jako odwołanie do komórki lub nazwane zakresy.
  6. Możesz dostosować te formuły zgodnie z wymaganiami, korzystając z innej funkcji programu Excel.
  7. Funkcja zwraca sumę wartości spełniających wszystkie warunki.

Mam nadzieję, że rozumiesz, jak znaleźć brakujące wartości w programie Excel. Zapoznaj się z dodatkowymi artykułami na temat formuł funkcji programu Excel tutaj. Prosimy o przesłanie zapytania lub opinii dotyczącej powyższego artykułu. Pomożemy Ci.

INDEKS-DOPASOWANIE w Excelu

WYSZUKAJ.PIONOWO Wiele wartości

WYSZUKAJ.PIONOWO z dynamicznym indeksem Col

Częściowe dopasowanie z funkcją WYSZUKAJ.PIONOWO

Przeglądaj według daty w Excelu

17 rzeczy o Excelu WYSZUKAJ.PIONOWO

popularne artykuły

50 skrótów Excela, aby zwiększyć produktywność

Edytuj listę rozwijaną

Absolutne odniesienie w Excelu

Jeśli z formatowaniem warunkowym

Jeśli z symbolami wieloznacznymi

Przeglądaj według daty