Vispārēja formula
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Kopsavilkums
Lai izveidotu kopsavilkuma tabulu ar to pārī savienoto vienumu skaitu, kas parādās esošo kombināciju sarakstā, varat izmantot palīgkolonnu un formulu, kas balstīta uz funkciju COUNTIFS. Parādītajā piemērā H5 šūnas formula ir:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
kur "palīgs" ir nosauktais diapazons E5: E24.
Piezīme: šī formula pieņem, ka vienumi noteiktā kombinācijā neatkārtojas (piemēram, AAB, EFE nav derīgas kombinācijas).
Paskaidrojums
Mēs vēlamies saskaitīt, cik bieži vienumi kolonnās B, C un D parādās kopā. Piemēram, cik bieži A parādās ar C, B parādās ar F, G parādās ar D utt. Šķiet, ka tas ir ideāls COUNTIFS lietojums, taču, ja mēs mēģinām pievienot kritērijus, meklējot 2 vienumus 3 kolonnās, tas nedarbosies.
Vienkāršs risinājums ir apvienot visus vienumus vienā šūnā, pēc tam vienumu skaitīšanai izmantojiet COUNTIFS ar aizstājējzīmi. Mēs to darām ar palīgkolonnu (E), kas savieno vienumus B, C un D kolonnās, izmantojot CONCAT funkciju šādi:
=CONCAT(B5:D5)
Vecākās Excel versijās varat izmantot šādu formulu:
=B5&C5&D5
Tā kā atkārtoti vienumi nav atļauti kombinācijā, formulas pirmajā daļā tiek izslēgti atbilstošie vienumi. Ja abi vienumi ir vienādi, formula kā tekstu atgriež defisi vai domuzīmi:
=IF($G5=H$4,"-"
Ja vienumi atšķiras, tiek palaista funkcija COUNTIFS:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS ir konfigurēts, lai uzskaitītu vienumu "pārus". Pāris tiek skaitīts tikai tad, kad vienums G slejā un atbilstošais vienums no 4. rindas šūnā parādās kopā. Lai aizstātu saskaņu neatkarīgi no tā, kur tā parādās šūnā, abās vienuma pusēs tiek sasieta aizstājējzīme (*).
