Formule générique
=COUNTIFS(range,"*"&$item1&"*",range,"*"&item2&"*")
Sommaire
Pour créer un tableau récapitulatif avec un nombre d'éléments appariés qui apparaissent dans une liste de combinaisons existantes, vous pouvez utiliser une colonne d'aide et une formule basée sur la fonction COUNTIFS. Dans l'exemple illustré, la formule de la cellule H5 est:
=IF($G5=H$4,"-",COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*"))
où "helper" est la plage nommée E5: E24.
Remarque: cette formule suppose que les éléments ne se répètent pas dans une combinaison donnée (c.-à-d. AAB, EFE ne sont pas des combinaisons valides).
Explication
Nous voulons compter la fréquence à laquelle les éléments des colonnes B, C et D apparaissent ensemble. Par exemple, à quelle fréquence A apparaît avec C, B apparaît avec F, G apparaît avec D, et ainsi de suite. Cela semble être une utilisation parfaite de COUNTIFS, mais si nous essayons d'ajouter des critères à la recherche de 2 éléments sur 3 colonnes, cela ne fonctionnera pas.
Une solution de contournement simple consiste à joindre tous les éléments ensemble dans une seule cellule, puis à utiliser COUNTIFS avec un caractère générique pour compter les éléments. Nous faisons cela avec une colonne d'assistance (E) qui joint les éléments des colonnes B, C et D en utilisant la fonction CONCAT comme ceci:
=CONCAT(B5:D5)
Dans les anciennes versions d'Excel, vous pouvez utiliser une formule comme celle-ci:
=B5&C5&D5
Étant donné que les éléments répétés ne sont pas autorisés dans une combinaison, la première partie de la formule exclut les éléments correspondants. Si les deux éléments sont identiques, la formule renvoie un tiret ou un tiret sous forme de texte:
=IF($G5=H$4,"-"
Si les éléments sont différents, une fonction COUNTIFS est exécutée:
COUNTIFS(helper,"*"&$G5&"*",helper,"*"&H$4&"*")
COUNTIFS est configuré pour compter les "paires" d'éléments. La paire n'est comptée que lorsque l'élément de la colonne G et l'élément correspondant de la ligne 4 apparaissent ensemble dans une cellule. Un caractère générique (*) est concaténé aux deux côtés de l'élément pour garantir qu'une correspondance sera comptée, peu importe où elle apparaît dans la cellule.
