Formuła ogólna
=SUMPRODUCT(COUNTIF(range1,range2))
Podsumowanie
Aby porównać dwa zakresy i policzyć łączną liczbę dopasowań między dwoma zakresami, możesz użyć formuły, która łączy funkcję LICZ.JEŻELI z funkcją SUMA.WROTU. W przedstawionym przykładzie formuła w komórce G7 to:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Uwaga: ta formuła nie dotyczy lokalizacji ani kolejności elementów w każdym zakresie.
Wyjaśnienie
Załóżmy, że masz jakąś listę „główną”, a także inną listę zawierającą takie same elementy. Potrzebujesz formuły porównującej wartości z drugiej listy, aby zobaczyć, ile z nich pojawia się na pierwszej liście. Nie obchodzi Cię kolejność pozycji, po prostu chcesz wiedzieć, ile pozycji z listy 2 pojawia się na liście 1.
Funkcja LICZ.JEŻELI policzy elementy w zakresie spełniającym Twoje kryteria. Zwykle podajesz LICZ.JEŻELI zakres taki jak A1: A10 i proste kryteria, takie jak „> 10”. LICZ.JEŻELI zwróci wtedy liczbę komórek w A1: A10, które są większe niż 10.
W tym przypadku jednak podajemy LICZ.JEŻELI zakres dla kryteriów. Nie używamy żadnych operatorów logicznych, co oznacza, że LICZ.JEŻELI sprawdzi równoważność (tj. Zachowuje się tak, jakbyśmy użyli operatora równości (=)).
Ponieważ podajemy LICZ.JEŻELI zakres (nazywany również „tablicą”) zawierający 7 elementów, LICZ.JEŻELI zwróci jako wynik tablicę składającą się z 7 elementów. Każdy element w tablicy wyników reprezentuje liczbę. W tym przykładzie tablica zwracana przez LICZ.JEŻELI wygląda następująco:
(1; 1; 0; 1; 1; 0; 0)
Teraz musimy po prostu zsumować elementy w tej tablicy, co jest idealnym zadaniem dla SUMPRODUCT. Funkcja SUMPRODUCT to wszechstronna funkcja, która obsługuje tablice natywnie bez żadnej specjalnej składni tablicowej.
Jeśli podasz SUMPRODUCT dwie lub więcej tablic, pomnoży je razem, zsumuje wyniki i zwróci tę liczbę. W tym przypadku dajemy SUMPRODUCT tylko jedną tablicę, więc po prostu sumuje pozycje w tablicy i zwraca 4 jako wynik.
Dopasuj w rzędach
Jeśli chcesz porównać dwa zakresy lub kolumny i zliczać dopasowania na poziomie wiersza (tj. Liczyć tylko dopasowania, gdy ten sam element znajduje się na tej samej pozycji), potrzebujesz innej formuły.
