Formuła ogólna
=IF(COUNTIF(list,value),"OK","Missing")
Podsumowanie
Aby zidentyfikować wartości na jednej liście, których brakuje na innej liście, możesz użyć prostej formuły opartej na funkcji LICZ.JEŻELI z funkcją JEŻELI. W przedstawionym przykładzie formuła w G6 to:
=IF(COUNTIF(list,F6),"OK","Missing")
gdzie „lista” to nazwany zakres B6: B11.
Wyjaśnienie
Funkcja LICZ.JEŻELI zlicza komórki spełniające kryteria i zwraca liczbę znalezionych wystąpień. Jeśli żadna komórka nie spełnia kryteriów, LICZ.JEŻELI zwraca zero. Możesz użyć zachowania bezpośrednio w instrukcji IF, aby zaznaczyć wartości, które mają zero (tj. Wartości, których brakuje). W przedstawionym przykładzie formuła w G6 to:
=IF(COUNTIF(list,F6),"OK","Missing")
gdzie „lista” to nazwany zakres, który odpowiada zakresowi B6: B11.
Funkcja JEŻELI wymaga testu logicznego, aby zwrócić PRAWDA lub FAŁSZ. W takim przypadku funkcja LICZ.JEŻELI wykonuje test logiczny. Jeśli wartość znajduje się na liście , LICZ.JEŻELI zwraca liczbę bezpośrednio do funkcji JEŻELI. Ten wynik może być dowolną liczbą… 1, 2, 3 itd.
Funkcja JEŻELI oceni dowolną liczbę jako PRAWDA, powodując, że funkcja JEŻELI zwróci „OK”. Jeśli wartość nie zostanie znaleziona na liście , LICZ.JEŻELI zwraca zero (0), co daje FALSE, a JEŻELI zwraca „Brak”.
Alternatywa z MATCH
Możesz również sprawdzić brakujące wartości za pomocą funkcji PODAJ.POZYCJĘ. PODAJ.POZYCJĘ znajduje pozycję elementu na liście i zwróci błąd # N / D, jeśli wartość nie zostanie znaleziona. Możesz użyć tego zachowania do zbudowania formuły, która zwraca „Brak” lub „OK”, testując wynik PODAJ.POZYCJĘ za pomocą funkcji ISNA. ISNA zwraca TRUE tylko wtedy, gdy otrzyma błąd # N / A.
Aby użyć funkcji MATCH, jak pokazano w powyższym przykładzie, formuła jest następująca:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Zwróć uwagę, że MATCH musi być skonfigurowane pod kątem dopasowania ścisłego. Aby to zrobić, upewnij się, że trzeci argument ma wartość zero lub FALSE.
Alternatywa z WYSZUKAJ.PIONOWO
Ponieważ funkcja WYSZUKAJ.PIONOWO zwraca również błąd # N / D, gdy wartość nie jest okrągła, możesz utworzyć formułę za pomocą funkcji WYSZUKAJ.PIONOWO, która działa tak samo, jak opcja PODAJ.POZYCJĘ. Podobnie jak w przypadku MATCH, musisz skonfigurować funkcję WYSZUKAJ.PIONOWO, aby używać dopasowania ścisłego, a następnie przetestować wynik za pomocą ISNA. Zauważ również, że podajemy WYSZUKAJ.PIONOWO tylko jedną kolumnę (kolumnę B) dla tablicy tabeli.
