Yleinen kaava
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Yhteenveto
Voit rakentaa yhteenvetotaulukon pariksi yhdistettyjen kohteiden lukumäärällä, jotka näkyvät olemassa olevien yhdistelmien luettelossa, käyttämällä apusaraketta ja COUNTIFS-funktioon perustuvaa kaavaa. Esitetyssä esimerkissä solun H5 kaava on:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
missä "auttaja" on nimetty alue E5: E24.
Huomaa: tässä kaavassa oletetaan, että kohteet eivät toistu tietyssä yhdistelmässä (ts. AAB, EFE eivät ole kelvollisia yhdistelmiä).
Selitys
Haluamme laskea, kuinka usein sarakkeiden B, C ja D kohteet näkyvät yhdessä. Esimerkiksi kuinka usein A näkyy C: llä, B ilmestyy F: llä, G ilmestyy D: llä ja niin edelleen. Tämä tuntuu täydelliseltä COUNTIFS-toiminnolta, mutta jos yritämme lisätä ehtoja, jotka etsivät kahta kohdetta 3 sarakkeesta, se ei toimi.
Yksinkertainen kiertotapa on liittää kaikki kohteet yhteen soluun ja laskea sitten kohteita COUNTIFS ja jokerimerkki. Teemme sen auttajasarakkeella (E), joka yhdistää sarakkeiden B, C ja D kohteet CONCAT-toiminnolla seuraavasti:
=CONCAT(B5:D5)
Vanhemmissa Excel-versioissa voit käyttää tällaista kaavaa:
=B5&C5&D5
Koska toistuvia kohteita ei sallita yhdistelmässä, kaavan ensimmäinen osa sulkee pois vastaavat kohteet. Jos nämä kaksi kohtaa ovat samat, kaava palauttaa väliviivan tai viivan tekstinä:
=IF($G5=H$4,"-"
Jos kohteet ovat erilaisia, COUNTIFS-toiminto suoritetaan:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS on määritetty laskemaan kohteiden "parit". Pari lasketaan vasta, kun sarakkeen G kohde ja vastaava rivin 4 kohta esiintyvät yhdessä solussa. Jokerimerkki (*) liitetään kohteen molemmille puolille sen varmistamiseksi, että osuma lasketaan riippumatta siitä, missä se näkyy solussa.
