Generička formula
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Sažetak
Da biste izgradili tablicu sažetka s brojem uparenih stavki koje se pojavljuju na popisu postojećih kombinacija, možete upotrijebiti pomoćni stupac i formulu koja se temelji na funkciji COUNTIFS. U prikazanom primjeru formula u stanici H5 je:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
gdje je "pomagač" imenovani raspon E5: E24.
Napomena: ova formula pretpostavlja da se stavke ne ponavljaju u datoj kombinaciji (tj. AAB, EFE nisu važeće kombinacije).
Obrazloženje
Želimo izbrojati koliko se često stavke u stupcima B, C i D pojavljuju zajedno. Na primjer, koliko se često A pojavljuje s C, B pojavljuje sa F, G se pojavljuje s D itd. To bi se činilo kao savršeno korištenje COUNTIFS-a, ali ako pokušamo dodati kriterije koji traže 2 stavke u 3 stupca, to neće uspjeti.
Jednostavno je rješenje spojiti sve stavke u jednu ćeliju, a zatim brojiti stavke pomoću COUNTIFS-a sa zamjenskim znakom. To radimo s pomoćnim stupcem (E) koji spaja stavke u stupcima B, C i D pomoću funkcije CONCAT poput ove:
=CONCAT(B5:D5)
U starijim verzijama Excela možete koristiti formulu poput ove:
=B5&C5&D5
Budući da ponovljene stavke nisu dopuštene u kombinaciji, prvi dio formule isključuje odgovarajuće stavke. Ako su dvije stavke iste, formula vraća crticu ili crticu kao tekst:
=IF($G5=H$4,"-"
Ako su stavke različite, pokreće se funkcija COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS je konfiguriran za brojanje "parova" stavki. Par se broji samo kada se stavka u stupcu G i odgovarajuća stavka iz retka 4 pojave zajedno u ćeliji. Zamjenski znak (*) spojen je na obje strane stavke kako bi se osiguralo da se podudaranje broji bez obzira na to gdje se nalazi u ćeliji.
