Formuła ogólna
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
Podsumowanie
Aby policzyć komórki nie równe żadnej z wielu rzeczy, możesz użyć formuły opartej na funkcjach PODAJ.POZYCJĘ, ISNA i SUMPRODUCT. W przedstawionym przykładzie formuła w komórce F5 to:
=SUMPRODUCT(--(ISNA(MATCH(data,exclude,0))))
gdzie „dane” to nazwany zakres B5: B16, a „wykluczenie” to nazwany zakres D5: D7.
Wyjaśnienie
Najpierw mały kontekst. Zwykle, jeśli masz tylko kilka rzeczy, których nie chcesz liczyć, możesz użyć funkcji COUNTIFS w następujący sposób:
=COUNTIFS(range,"apple",range,"orange")
Ale to nie skaluje się zbyt dobrze, jeśli masz listę wielu rzeczy, ponieważ będziesz musiał dodać dodatkową parę zakres / kryteria dla każdej rzeczy, której nie chcesz liczyć. Dużo łatwiej byłoby zbudować listę i przekazać odniesienie do tej listy jako część kryteriów. Dokładnie to robi wzór na tej stronie.
Zasadniczo ta formuła wykorzystuje funkcję PODAJ.POZYCJĘ, aby znaleźć komórki różniące się od „a”, „b” lub „c” z następującym wyrażeniem:
MATCH(data,exclude,0)
Zwróć uwagę, że wartość wyszukiwania i tablica wyszukiwania są „odwrócone” od normalnej konfiguracji - wszystkie wartości z nazwanego zakresu „dane” podajemy jako wartości wyszukiwania i podajemy wszystkie wartości, które chcemy wykluczyć w nazwanym zakresie „wyklucz”. Ponieważ dajemy MATCH więcej niż jedną wartość wyszukiwania, otrzymujemy więcej niż jeden wynik w tablicy takiej jak ta:
(1;2;3;#N/A;#N/A;#N/A;1;2;3;#N/A;1)
Zasadniczo funkcja PODAJ.POZYCJĘ podaje nam pozycję pasujących wartości jako liczbę i zwraca # N / A dla wszystkich innych wartości.
Wyniki # N / A są tymi, które nas interesują, ponieważ reprezentują wartości różne od „a”, „b” lub „c”. W związku z tym używamy ISNA, aby wymusić na tych wartościach TRUE, a liczby na FALSE:
ISNA(MATCH(data,exclude,0)
Następnie używamy podwójnego ujemnego wyniku, aby wymusić TRUE na 1 i FALSE na zero. Wynikowa tablica wewnątrz SUMPRODUCT wygląda następująco:
=SUMPRODUCT((0;0;0;1;1;1;0;0;0;1;0))
Mając tylko jedną tablicę do przetworzenia, SUMPRODUCT sumuje i zwraca wynik końcowy, 4.
Uwaga: Użycie SUMPRODUCT zamiast SUM pozwala uniknąć konieczności użycia kombinacji Ctrl + Shift + Enter.
Licznik minus dopasowanie
Innym sposobem zliczania komórek, które nie są równe żadnej z kilku rzeczy, jest policzenie wszystkich wartości i odjęcie dopasowań. Możesz to zrobić za pomocą następującego wzoru:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude))
W tym przypadku COUNTA zwraca liczbę wszystkich niepustych komórek. Funkcja LICZ.JEŻELI, biorąc pod uwagę nazwany zakres „wyklucz”, zwróci trzy liczniki, po jednej dla każdego elementu na liście. SUMPRODUCT sumuje sumę, a ta liczba jest odejmowana od liczby wszystkich niepustych komórek. Ostatecznym wynikiem jest liczba komórek, które nie są równe wartościom w polu „wyklucz”.
Literał zawiera logikę typu
Formuła na tej stronie liczy się z logiką „równa się”. Jeśli chcesz policzyć komórki, które nie zawierają wielu ciągów, gdzie zawiera oznacza, że ciąg może pojawić się w dowolnym miejscu komórki, potrzebujesz bardziej złożonej formuły.
