Bendroji formulė
=SUMPRODUCT(COUNTIF(range1,range2))
Santrauka
Norėdami palyginti du diapazonus ir suskaičiuoti bendrą dviejų diapazonų atitiktį, galite naudoti formulę, kuri sujungia funkciją COUNTIF su funkcija SUMPRODUCT. Parodytame pavyzdyje formulė langelyje G7 yra:
=SUMPRODUCT(COUNTIF(B5:B11,D5:D11))
Pastaba: šiai formulei nerūpi elementų vieta ar tvarka kiekviename diapazone.
Paaiškinimas
Tarkime, kad turite kažkokį „pagrindinį“ sąrašą, taip pat turite kitą sąrašą, kuriame yra keletas tų pačių elementų. Norite formulės, kuri lygintų 2-ojo sąrašo reikšmes, kad pamatytumėte, kiek jų yra pirmame sąraše. Jums nerūpi prekių eiliškumas, tiesiog norite sužinoti, kiek elementų 2 sąraše rodoma 1 sąraše.
Funkcija COUNTIF skaičiuos jūsų kriterijus atitinkančius diapazono dalykus. Paprastai COUNTIF suteikiate diapazoną, pvz., A1: A10, ir tokius paprastus kriterijus kaip „> 10“. Tada COUNTIF grąžins langelių skaičių A1: A10, kurie yra didesni nei 10.
Tačiau šiuo atveju mes suteikiame COUNTIF kriterijų diapazoną. Mes nenaudojame jokių loginių operatorių, o tai reiškia, kad COUNTIF patikrins lygiavertiškumą (ty elgiasi taip, lyg būtume naudoję operatorių equals (=)).
Kadangi suteikiame COUNTIF diapazoną (dar vadinamą „masyvu“), kuriame yra 7 elementai, COUNTIF grąžins 7 elementų masyvą. Kiekvienas rezultatas masyvo elementas reiškia skaičių. Pavyzdyje šis COUNTIF grąžintas masyvas atrodo taip:
(1; 1; 0; 1; 1; 0; 0)
Dabar mums tiesiog reikia pridėti šio masyvo elementus, o tai yra puikus „SUMPRODUCT“ darbas. Funkcija SUMPRODUCT yra universali funkcija, kuri natūraliai tvarko masyvus be jokios specialios masyvo sintaksės.
Jei suteiksite SUMPRODUCT du ar daugiau masyvų, jis sujungs masyvus, susumuos rezultatus ir grąžins tą skaičių. Tokiu atveju SUMPRODUCT suteikiame tik vieną masyvą, todėl jis paprasčiausiai apibendrina masyvo elementus ir pateikia 4 rezultatą.
Rungtis per eilutes
Jei norite palyginti du diapazonus ar stulpelius ir norite skaičiuoti atitikmenis eilutės lygiu (t. Y. Tik tada, kai tas pats elementas rodomas toje pačioje pozicijoje), jums reikės kitos formulės.
