Formuła ogólna
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
Podsumowanie
Aby podsumować, czy komórki zawierają jeden lub inny ciąg tekstowy (tj. Zawierają słowa „kot” lub „szczur”), można użyć funkcji SUMPRODUCT razem z opcją ISNUMBER + SEARCH lub FIND. W przedstawionym przykładzie formuła w komórce F5 to:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
która zwraca sumę wartości w C4: C8, gdy komórki w B4: B8 zawierają „kot” lub „szczur”.
Wyjaśnienie
Sumując komórki za pomocą kryteriów „LUB”, należy uważać, aby nie liczyć podwójnie, gdy istnieje możliwość, że oba kryteria zwrócą prawdę. W przedstawionym przykładzie chcemy zsumować wartości w kolumnie C, gdy komórki w kolumnie B zawierają „kot” lub „szczur”. Nie możemy używać funkcji SUMA.JEŻELI z dwoma kryteriami, ponieważ SUMA.WARUNKÓW jest oparta na logice AND. A jeśli spróbujemy użyć dwóch SUMA.WARUNKÓW (tj. SUMA.WARUNKÓW + SUMA.WARUNKÓW), policzymy podwójnie, ponieważ istnieją komórki zawierające zarówno słowo „kot”, jak i „szczur”.
Zamiast tego używamy następującego wzoru:
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)
Ten rdzeń tej formuły jest oparty na formule wyjaśnionej tutaj, która lokalizuje tekst w komórce z ISNUMBER i SEARCH:
ISNUMBER(SEARCH("text",range)
Po podaniu zakresu komórek ten fragment kodu zwróci tablicę wartości PRAWDA / FAŁSZ, po jednej wartości dla każdej komórki zakresu. W tym wzorze użyjemy tego fragmentu dwukrotnie, raz dla słowa „kot” i raz dla słowa „szczur”, więc otrzymamy dwie tablice. W tym momencie mamy:
=SUMPRODUCT(--(((TRUE;FALSE;TRUE;FALSE;FALSE)+ (TRUE;FALSE;TRUE;TRUE;FALSE))>0),C4:C8)
Następnie dodajemy te tablice razem, ponieważ dodawanie jest używane w algebrze boolowskiej dla logiki OR. Operacja matematyczna automatycznie przekształca wartości PRAWDA i FAŁSZ na 1 i 0, więc otrzymujemy tablicę poniżej:
=SUMPRODUCT(--(((2;0;2;1;0))>0),C4:C8)
Każda liczba w tej tablicy jest wynikiem dodania razem wartości PRAWDA i FAŁSZ w dwóch oryginalnych tablicach. W pokazanym przykładzie tablica wygląda następująco:
(2;0;2;1;0)
Musimy dodać te liczby, ale nie chcemy liczyć podwójnie. Musimy więc upewnić się, że każda wartość większa niż zero jest policzona tylko raz. Aby to zrobić, wymuszamy na wszystkich wartościach TRUE lub FALSE, sprawdzając tablicę z "> 0". Zwraca TRUE / FALSE:
=SUMPRODUCT(--((TRUE;FALSE;TRUE;TRUE;FALSE)),C4:C8)
Które następnie przeliczamy na 1/0 za pomocą podwójnego ujemnego (-):
=SUMPRODUCT((1;0;1;1;0),C4:C8)
i w końcu:
=SUMPRODUCT((1;0;1;1;0),(20;15;30;20;10))
SUMPRODUCT mnoży razem odpowiadające elementy z dwóch tablic i sumuje wynik, zwracając 70.
Opcja uwzględniająca wielkość liter
Funkcja SEARCH ignoruje wielkość liter. Jeśli potrzebujesz wrażliwej opcji, zastąp SZUKAJ funkcją ZNAJDŹ.
