Compter les correspondances entre deux colonnes texte en Excel [Résolu]

Signaler
-
Messages postés
23380
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
10 juillet 2020
-
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 ...

Configuration: Windows / Firefox 77.0

6 réponses

Messages postés
12381
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
9 juillet 2020
1 922
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 (???)
Messages postés
12381
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
9 juillet 2020
1 922
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 ...
Messages postés
12381
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
9 juillet 2020
1 922
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 ...
et encore pire :
avec a,e,e,e,e
et a,e,e,e,e = 17 !!!! ????

Je te dis, je comprends pas du tout la logique d'Excel pour arriver à un résultat pareil. Incroyable
Messages postés
12381
Date d'inscription
mercredi 16 janvier 2013
Statut
Membre
Dernière intervention
9 juillet 2020
1 922
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 ;)
Messages postés
23380
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
10 juillet 2020
6 229
Bonjour à tous,

=SOMMEPROD(--(A1:E1=A2:E2)) 

eric
CA FONCTIONNE !!!!!

Alors là, impossible pour moi de comprendre la logique de cette fonction mais ça me donne effectivement le bon résultat à tous les coups

MILLE MERCIS eriiic :D
Messages postés
23380
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
10 juillet 2020
6 229 > Flet
Rien 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
Messages postés
929
Date d'inscription
mardi 20 mai 2003
Statut
Contributeur sécurité
Dernière intervention
9 juillet 2020
104
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,

Pierre (aka Terdef)
Asap Admin - SWI Ambassador - MalwareBytes Expert
Oui, c'est finalement ce que j'ai fait : créer un second tableau "mirroir" avec des fonctions SI et réaliser l'addition.

Ça fonctionne mais ça me semblait une opération tellement simple à faire avec un NB.SI et une formule matricielle ...

Merci pour la réponse ;)