Splošna formula
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Povzetek
Če želite zgraditi zbirno tabelo s številom seznanjenih elementov, ki se pojavijo na seznamu obstoječih kombinacij, lahko uporabite pomožni stolpec in formulo, ki temelji na funkciji COUNTIFS. V prikazanem primeru je formula v celici H5:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kjer je "pomočnik" imenovani obseg E5: E24.
Opomba: ta formula predvideva, da se elementi ne ponovijo v dani kombinaciji (tj. AAB, EFE niso veljavne kombinacije).
Pojasnilo
Prešteti želimo, kako pogosto se elementi v stolpcih B, C in D pojavljajo skupaj. Na primer, kako pogosto se A pojavi s C, B se pojavi s F, G se pojavi z D itd. To bi se zdelo kot popolna uporaba COUNTIFS, vendar če poskusimo dodati merila, ki iščejo 2 elementa v treh stolpcih, ne bo šlo.
Preprosta rešitev je združitev vseh elementov v eno celico, nato pa za štetje elementov uporabite COUNTIFS z nadomestnim znakom. To naredimo s pomožnim stolpcem (E), ki združuje elemente v stolpcih B, C in D s pomočjo funkcije CONCAT, kot je ta:
=CONCAT(B5:D5)
V starejših različicah Excela lahko uporabite takšno formulo:
=B5&C5&D5
Ker ponovljeni elementi v kombinaciji niso dovoljeni, prvi del formule izključuje ujemajoče se elemente. Če sta elementa enaka, formula vrne vezaj ali pomišljaj kot besedilo:
=IF($G5=H$4,"-"
Če se elementi razlikujejo, se zažene funkcija COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS je konfiguriran za štetje "parov" elementov. Par se šteje šele, ko se element v stolpcu G in ustrezen element iz vrstice 4 pojavita skupaj v celici. Nadomestni znak (*) je združen na obe strani predmeta, da se zagotovi ujemanje ujemanja, ne glede na to, kje je v celici.
