Bendroji formulė
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Santrauka
Norėdami sukurti suvestinę lentelę su suporuotų elementų skaičiumi, kurie rodomi esamų derinių sąraše, galite naudoti pagalbinį stulpelį ir formulę, pagrįstą funkcija COUNTIFS. Parodytame pavyzdyje formulė langelyje H5 yra:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kur „pagalbininkas“ yra įvardytas diapazonas E5: E24.
Pastaba: pagal šią formulę daroma prielaida, kad elementai nekartoja nurodytoje kombinacijoje (ty AAB, EFE nėra galiojančios kombinacijos).
Paaiškinimas
Norime suskaičiuoti, kaip dažnai elementai B, C ir D stulpeliuose rodomi kartu. Pavyzdžiui, kaip dažnai pasirodo A su C, B su F, G su D ir t. T. Atrodytų, tai puikus „COUNTIFS“ naudojimas, bet jei bandysime pridėti kriterijus, ieškodami 2 elementų 3 stulpeliuose, tai neveiks.
Paprastas sprendimas yra sujungti visus elementus į vieną langelį, tada elementams suskaičiuoti naudokite COUNTIFS su pakaitos simboliu. Tai darome su pagalbiniu stulpeliu (E), kuris sujungia elementus B, C ir D stulpeliuose, naudodamas tokią funkciją CONCAT:
=CONCAT(B5:D5)
Senesnėse „Excel“ versijose galite naudoti tokią formulę:
=B5&C5&D5
Kadangi kartoti elementai neleidžiami kartu, pirmoje formulės dalyje neatsižvelgiama į atitinkamus elementus. Jei abu elementai yra vienodi, formulė pateikia brūkšnį arba brūkšnį kaip tekstą:
=IF($G5=H$4,"-"
Jei elementai skiriasi, vykdoma funkcija COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS sukonfigūruotas skaičiuoti elementų „poras“. Pora skaičiuojama tik tada, kai langelio G stulpelyje esantis elementas ir atitinkamas elementas iš 4 eilutės yra kartu. Pakaitos simbolis (*) yra sujungtas į abi elemento puses, kad atitikimas būtų skaičiuojamas, nesvarbu, kur jis rodomas langelyje.
