Obecný vzorec
=SUMPRODUCT(COUNTIF(range1,range2))
souhrn
Chcete-li porovnat dva rozsahy a spočítat celkové shody mezi těmito dvěma rozsahy, můžete použít vzorec, který kombinuje funkci COUNTIF s funkcí SUMPRODUCT. V zobrazeném příkladu je vzorec v buňce G7:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Poznámka: tento vzorec se nestará o umístění nebo pořadí položek v každém rozsahu.
Vysvětlení
Předpokládejme, že máte nějaký „hlavní“ seznam a také další seznam, který obsahuje některé stejné položky. Chcete vzorec, který porovnává hodnoty ve 2. seznamu, abyste zjistili, kolik z nich se objeví v prvním seznamu. Nezáleží vám na pořadí, ve kterém jsou položky, chcete jen vědět, kolik položek v seznamu 2 se objeví v seznamu 1.
Funkce COUNTIF spočítá věci v rozsahu, který splňuje vaše kritéria. Normálně byste dali COUNTIF rozsah jako A1: A10 a jednoduchá kritéria jako „> 10“. COUNTIF by pak vrátil počet buněk v A1: A10, které jsou větší než 10.
V tomto případě však dáváme COUNTIF rozsah kritérií. Nepoužíváme žádné logické operátory, což znamená, že COUNTIF zkontroluje ekvivalenci (tj. Chová se, jako kdybychom použili operátor equals (=)).
Protože dáváme COUNTIF rozsah (nazývaný také „pole“), který obsahuje 7 položek, vrátí COUNTIF jako výsledek pole 7 položek. Každá položka v poli výsledků představuje počet. V příkladu toto pole, které COUNTIF vrátí, vypadá takto:
(1; 1; 0; 1; 1; 0; 0)
Nyní jednoduše musíme sečíst položky v tomto poli, což je perfektní práce pro SUMPRODUCT. Funkce SUMPRODUCT je univerzální funkce, která nativně zpracovává pole bez jakékoli speciální syntaxe pole.
Pokud zadáte SUMPRODUCT dvě nebo více polí, dojde k násobení polí dohromady, sečtou výsledky a vrátí toto číslo. V tomto případě dáváme SUMPRODUCT pouze jedno pole, takže jednoduše shrnuje položky v poli a vrátí 4 jako výsledek.
Shoda mezi řádky
Pokud chcete porovnat dva rozsahy nebo sloupce a chcete počítat shody na úrovni řádků (tj. Počítat pouze shody, když se stejná položka objeví na stejné pozici), budete potřebovat jiný vzorec.
