Üldine valem
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Kokkuvõte
Kokkuvõtetabeli loomiseks olemasolevate kombinatsioonide loendis kuvatavate seotud üksuste arvuga saate kasutada abimeergu ja funktsiooni COUNTIFS põhinevat valemit. Näidatud näites on lahtris H5 valem:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kus "abistaja" on nimega vahemik E5: E24.
Märkus: see valem eeldab, et üksused ei kordu antud kombinatsioonis (st AAB, EFE pole kehtivad kombinatsioonid).
Selgitus
Tahame kokku lugeda, kui sageli veergudes B, C ja D olevad üksused koos ilmuvad. Näiteks kui sageli ilmub A tähega C, B tähega F, G tähega D jne. See näib olevat COUNTIFSi täiuslik kasutamine, kuid kui proovime lisada kriteeriumid, mis otsivad 2 veergu kolmes veerus, siis see ei toimi.
Lihtne lahendus on kõigi üksuste ühendamine ühes lahtris, seejärel üksuste loendamiseks COUNTFS koos metamärgiga. Teeme seda abiveerguga (E), mis ühendab veergudes B, C ja D olevad elemendid funktsiooni CONCAT abil:
=CONCAT(B5:D5)
Exceli vanemates versioonides saate kasutada sellist valemit:
=B5&C5&D5
Kuna korduvad üksused pole kombinatsioonis lubatud, välistab valemi esimene osa sobivad üksused. Kui need kaks üksust on samad, tagastab valem tekstina sidekriipsu või sidekriipsu:
=IF($G5=H$4,"-"
Kui üksused erinevad, käivitatakse funktsioon COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS on konfigureeritud loendama üksuste "paare". Ainult siis, kui veerus G olev üksus ja 4. rea vastav element ilmuvad lahtris koos, loetakse paar paariks. Metamärk (*) on liidetud üksuse mõlemale küljele, et tagada vaste loendamine lahtris olenemata kohast.
