Formuła ogólna
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
Podsumowanie
Aby policzyć komórki, które nie zawierają wielu różnych ciągów, możesz użyć dość złożonej formuły opartej na funkcji MMULT. W pokazanym przykładzie formuła w F5 to:
(=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0)))
gdzie „dane” to nazwany zakres B5: B14, a „wykluczenie” to nazwany zakres D5: D7.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter
Przedmowa
Ten wzór komplikuje wymóg „zawiera”. Jeśli potrzebujesz tylko formuły do zliczania komórek, które nie * są równe * wielu rzeczy, możesz użyć prostszej formuły opartej na funkcji PODAJ.POZYCJĘ. Ponadto, jeśli masz ograniczoną liczbę ciągów do wykluczenia, możesz użyć funkcji LICZ.WARUNKI w następujący sposób:
=COUNTIFS(data,"*pink*",data,"*orange*",data,"*black*")
Jednak w przypadku tego podejścia należy wprowadzić nową parę argumentów zakresu / kryteriów dla każdego wykluczonego ciągu. Z kolei formuła wyjaśniona poniżej może obsługiwać dużą liczbę ciągów znaków, które należy wykluczyć, wprowadzonych bezpośrednio w arkuszu.
Wreszcie ta formuła jest złożona. Daj mi znać, jeśli masz prostszą formułę do zaproponowania :)
Wyjaśnienie
Rdzeniem tej formuły jest ISNUMBER i SEARCH:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Tutaj transponujemy elementy z nazwanego zakresu „wyklucz”, a następnie przekazujemy wynik do SEARCH jako „znajdź tekst”, z „danymi” jako „w tekście”. Funkcja SEARCH zwraca tablicę dwuwymiarową zawierającą wartości PRAWDA i FAŁSZ, 10 wierszy na 3 kolumny, na przykład:
(3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12)
Dla każdej wartości w polu „dane” mamy 3 wyniki (po jednym na wyszukiwany ciąg), które są albo błędami #VALUE, albo liczbami. Liczby reprezentują pozycję znalezionego ciągu tekstowego, a błędy oznaczają ciągi tekstowe, które nie zostały znalezione. Nawiasem mówiąc, funkcja TRANSPOSE jest potrzebna do wygenerowania tablicy 10 x 3 pełnych wyników.
Ta tablica jest wprowadzana do ISNUMBER, aby uzyskać wartości TRUE FALSE, które konwertujemy na jedynki i 0 za pomocą podwójnego operatora ujemnego (-). Rezultatem jest tablica taka:
(1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1)
który trafia do funkcji MMULT jako tablica1. Zgodnie z zasadami mnożenia macierzy liczba kolumn w tablicy1 musi być równa liczbie wierszy w tablicy2. Aby wygenerować array2 , używamy funkcji WIERSZ w następujący sposób:
ROW(exclude)^0
Daje to tablicę 1, 3 wiersze na 1 kolumnę:
(1;1;1)
który przechodzi do MMULT jako tablica2 . Po pomnożeniu tablicy mamy tablicę zwymiarowaną tak, aby pasowała do oryginalnych danych:
(2;1;0;0;1;1;0;0;0;2)
W tej tablicy każda niezerowa liczba reprezentuje wartość, w której znaleziono co najmniej jeden z wykluczonych ciągów. Zera wskazują, że nie znaleziono wykluczonych ciągów. Aby wymusić wszystkie niezerowe wartości na 1, używamy więcej niż zero:
(2;1;0;0;1;1;0;0;0;2)>0
który tworzy kolejną tablicę lub wartości TRUE i FALSE:
(TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)
Naszym ostatecznym celem jest zliczanie tylko wartości tekstowych, w przypadku których nie znaleziono wykluczonych ciągów, więc musimy odwrócić te wartości. Robimy to przez odjęcie tablicy od 1. To jest przykład logiki boolowskiej. Operacja matematyczna automatycznie przekształca wartości PRAWDA i FAŁSZ na 1 i 0, a na koniec mamy tablicę do powrotu do funkcji SUMA:
=SUM((0;0;1;1;0;0;1;1;1;0))
Funkcja SUMA zwraca wynik końcowy równy 5.
