Compter les correspondances entre deux colonnes texte en Excel
Résolu
Bonjour,
Je tente de réaliser une opération en apparence simple mais qui me fait m'arracher les cheveux (et le clavier à force d'arpenter les différentes forums et tutoriels) !
Dans ma feuille, j'ai une première ligne de réponses A1:E1 (ex: VRAI, FAUX, etc. ou a,b,c, etc.) et une seconde ligne de corrigé A2:E2 (ex:VRAI, FAUX, etc. ou a,b,c, etc.)
Je voudrais compter le nombre de cellules sur la ligne 1 qui correspondent en vis-à-vis à la ligne 2.
Exemples :
Ligne 1 = VRAI, FAUX, FAUX, VRAI, FAUX
Ligne 2 = VRAI, FAUX, FAUX, FAUX, VRAI
Résultat souhaité = 3 (les 3 premières colonnes correspondent)
Ligne 1 = A,B,C,D,E
Ligne 2 = A,X,C,E,D
Résultat souhaité = 2 (seuls A et C correspondent)
Comme je compare des ensembles avec des ensembles, j'ai immédiatement pensé à une formule matricielle, mais impossible de la faire comprendre à Excel :
J'ai essayé {=NB.SI(A1:E1;A2;E2)}
J'ai essayé {=NB.SI.ENS(A1:E1;A2:E2)}
J'ai essayé la fonction SOMMEPROD, la fonction EQUIV,
Rien à faire, aucune ne veux m'effectuer le comptage et me fournir un résultat correct.
Je sais que je pourrais créer un deuxième tableau en parallèle avec une série de fonctions SI qui me calculeraient 1 à chaque correspondance et ensuite réaliser la SOMME mais je me demandais s'il n'existait pas une formule directement prévue pour ce cas.
Quelqu'un aurait-il une idée qui me permettrait de résoudre ce problème ?
Merci d'avance pour votre aide, là je cale ...
Je tente de réaliser une opération en apparence simple mais qui me fait m'arracher les cheveux (et le clavier à force d'arpenter les différentes forums et tutoriels) !
Dans ma feuille, j'ai une première ligne de réponses A1:E1 (ex: VRAI, FAUX, etc. ou a,b,c, etc.) et une seconde ligne de corrigé A2:E2 (ex:VRAI, FAUX, etc. ou a,b,c, etc.)
Je voudrais compter le nombre de cellules sur la ligne 1 qui correspondent en vis-à-vis à la ligne 2.
Exemples :
Ligne 1 = VRAI, FAUX, FAUX, VRAI, FAUX
Ligne 2 = VRAI, FAUX, FAUX, FAUX, VRAI
Résultat souhaité = 3 (les 3 premières colonnes correspondent)
Ligne 1 = A,B,C,D,E
Ligne 2 = A,X,C,E,D
Résultat souhaité = 2 (seuls A et C correspondent)
Comme je compare des ensembles avec des ensembles, j'ai immédiatement pensé à une formule matricielle, mais impossible de la faire comprendre à Excel :
J'ai essayé {=NB.SI(A1:E1;A2;E2)}
J'ai essayé {=NB.SI.ENS(A1:E1;A2:E2)}
J'ai essayé la fonction SOMMEPROD, la fonction EQUIV,
Rien à faire, aucune ne veux m'effectuer le comptage et me fournir un résultat correct.
Je sais que je pourrais créer un deuxième tableau en parallèle avec une série de fonctions SI qui me calculeraient 1 à chaque correspondance et ensuite réaliser la SOMME mais je me demandais s'il n'existait pas une formule directement prévue pour ce cas.
Quelqu'un aurait-il une idée qui me permettrait de résoudre ce problème ?
Merci d'avance pour votre aide, là je cale ...
Configuration: Windows / Firefox 77.0
6 réponses
-
Bonjour Flet
=SOMMEPROD(NB.SI(A1:E1;A2:E2))
Cdlmnt
Via
-
Bonjour Via,
Merci pour ta réponse rapide, mais je viens d'essayer et je n'obtiens pas le bon résultat :
avec :
a,b,c,d,e
et
a,a,b,c,e
la formule me renvoie le résultat 5 (en formule classique et également en formule matricielle) et quand je recopie exactement les mêmes valeurs sur les deux lignes, le résultat est 7 (???)
-
-
Exact
Je suis allé un peu vite !
=SOMMEPROD(NB.SI(A1:E1;A2:E2)*(A1:E1=A2:E2)) devrait mieux aller
-
toujours pas ... les résultats sont un peu différents mais jamais le bon comptage
avec : a,b,c,d,e et a,b,c,d,e =5 (jusque là, tout va bien :D)
mais : a,b,c,d,e et a,e,e,e,e = 5
par contre : a,b,c,d,e et a,x,c,d,y = 3
franchement, je ne comprends pas toute la logique d'Excel quelques fois. Je comprends ta formule (qui me semble effectivement correcte) mais on dirait qu'il me sort des chiffres d'un chapeau une fois sur deux ...
-
-
Chez moi a,b,c,d,e et a,e,e,e,e = 2 avec ma formule !
-
oops, pardon, je me suis trompé dans l'illustration
avec a,b,c,d,e et a,e,e,e,e = 2 effectivement
mais avec le contraire a,e,e,e,e et a,b,c,d,e = 5 ???
En soi, si la formule compare les correspondances des deux lignes, la réponse devrait être la même quelque soit l'ordre dans lequel on indique l'une et l'autre. Et là, ça part en c...acahuète avec une simple inversion.
Vraiment étrange ... -
-
-
Moi aussi il y a quelque chose qui m"échappe :
Alors passons par une fonction personnalisée à mettre dans un module de l'éditeur VBA
Function denomb(plage As Range) For Each c In plage If c = Cells(c.Row + 1, c.Column) Then tot = tot + 1 Next denomb = tot End Function
S'utilise selon la syntaxe = denomb(plage 1ere ligne) par ex ici = denomb(A1:E2) et va comparer avec la ligne en dessous
Cdlmnt
Via-
Oui, je voulais éviter d'en arriver à VBA mais bon on dirait que j'y serai condamné !!!
En attendant, j'ai créé un tableau "mirroir" où j'ai utilisé des fonctions SI en pagaille pour traduire les correspondances en 1 ou 0 et faire l'addition.
C'est mieux que rien et ça fait le boulot.
Merci beaucoup pour ton aide ;)
-
-
ContributeurBonjour à tous,
=SOMMEPROD(--(A1:E1=A2:E2))
eric
-
-
Contributeur@FletRien de mystérieux.
(A1:E1=A2:E2)
te retourne une matrice des résultats des tests =>({VRAI\VRAI\VRAI\FAUX\FAUX})--
: le 1er - te converti les booléens en numérique, le 2nd te rétablit le signe =>{1\1\1\0\0}
et un sommeprod() pour les compter.
eric
-
-
Contributeur sécuritéBonjour,
Et un truc aussi bête que :
En A3 =SI(A1=A2;1;0)
En B3 =SI(B1=B2;1;0)
En C3 =SI(C1=C2;1;0)
En D3 =SI(D1=D2;1;0)
En E3 =SI(E1=E2;1;0)
Et, quelque part, =SOMME(A3:E3)
C'est bête, mais ça fonctionne à tous les coups sans s'arracher les cheveux (ni le clavier).
Comme cela ressemble à un QCM, le nombre de questions et connu d'avance, donc on peut aussi faire le décompte des erreurs au lieu du décompte des bonnes réponses (ou faire les deux) et jouer sur ces résultats pour écrire des trucs comme :
5 fautes sur 5 ! Vous êtes viré ! ;)
5 bonnes réponses ! Passez au cours supérieur !
Etc.
Cordialement,
