Všeobecný vzorec
=SUMPRODUCT(COUNTIF(range1,range2))
Zhrnutie
Ak chcete porovnať dva rozsahy a spočítať celkové zhody medzi týmito dvoma rozsahmi, môžete použiť vzorec, ktorý kombinuje funkciu COUNTIF s funkciou SUMPRODUCT. V zobrazenom príklade je vzorec v bunke G7:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Poznámka: Tento vzorec sa nestará o umiestnenie alebo poradie položiek v každom rozsahu.
Vysvetlenie
Predpokladajme, že máte nejaký „hlavný“ zoznam a tiež iný zoznam, ktorý obsahuje niektoré rovnaké položky. Chcete vzorec, ktorý porovnáva hodnoty v 2. zozname, aby ste zistili, koľko z nich sa objaví v prvom zozname. Nezáleží vám na poradí, v akom sú položky, len chcete vedieť, koľko položiek v zozname 2 sa nachádza v zozname 1.
Funkcia COUNTIF počíta veci v rozsahu, ktorý spĺňa vaše kritériá. Za normálnych okolností by ste dali COUNTIF rozsah ako A1: A10 a jednoduché kritériá ako „> 10“. COUNTIF by potom vrátil počet buniek v A1: A10, ktorý je väčší ako 10.
V takom prípade však dávame parametrom COUNTIF rozsah kritérií. Nepoužívame žiadne logické operátory, čo znamená, že COUNTIF skontroluje ekvivalenciu (tj. Chová sa, akoby sme použili operátor equals (=)).
Pretože dávame COUNTIF rozsah (nazývaný tiež „pole“), ktorý obsahuje 7 položiek, vráti COUNTIF vo výsledku pole 7 položiek. Každá položka v poli výsledkov predstavuje počet. V príklade toto pole, ktoré vráti COUNTIF, vyzerá takto:
(1; 1; 0; 1; 1; 0; 0)
Teraz jednoducho musíme pridať položky do tohto poľa, čo je pre SUMPRODUCT perfektná práca. Funkcia SUMPRODUCT je všestranná funkcia, ktorá narába s poľami natívne bez akejkoľvek špeciálnej syntaxe poľa.
Ak zadáte SUMPRODUCT dve alebo viac polí, zoskupí sa ich dohromady, výsledky sa sčítajú a dané číslo sa vráti. V takom prípade dáme SUMPRODUCT iba jedno pole, takže jednoducho zhrnie položky v poli a vráti 4 ako výsledok.
Zhoda medzi riadkami
Ak chcete porovnať dva rozsahy alebo stĺpce a chcete počítať zhody na úrovni riadkov (tj počítať iba zhody, keď sa rovnaká položka zobrazuje na rovnakej pozícii), budete potrebovať iný vzorec.
