Comparaison de listes

[pingouinal]

Bonjour tout le monde,

J'ai un petit problème de formule dans Excel.
Je souhaite comparer deux listes et attribuer une valeur selon certaines conditions.
Je m'explique : j'ai en colonne A 4 noms et la même chose en colonne B.
Si les 4 noms de la colonne A correspondent aux 4 noms de la colonne B mais dans le désordre, je veux attribuer la valeur 1 à la colonne C.
Dans les noms il peut y avoir des doublons, mais pour que la colonne C ait la valeur 1, il faut absolument que les noms de A correspondent à ceux de B et qu'ils ne soient pas dans un ordre parfait (si un ou plusieurs nom est dans le bon ordre, C=1, mais si tous sont dans le bon ordre, C=0).

J'ai réussi à faire celà en énumérant dans une formule SI tous les cas possibles (d'après moi, mais j'en ai sûrement oublié), mais j'arrive à une formule de 968 caractères (un peu moins dans mon exemple mais comme mon fichier final fait plusieurs centaines de lignes, la formule s'allonge) et j'aurai besoin par la suite d'ajouter des choses à cette formule. Je cherche donc à trouver une formule (beaucoup) plus courte qu'un simple SI couplé à des ET et OU, mais j'avoue que je sèche.

Comme je pense ne pas être clair , je vous joins un fichier d'exemple.

D'avance merci à tous ceux qui pourront m'aider.

PS: j'aimerais si possible passer par formule et non par macro, et toujours si possible ne pas ajouter de colonnes intermédiaires pour les formules. ^_^'

 

[hoerwind]

Re : Comparaison de listes

Bonjour,

C4 :

=SI(A4&A5&A6&A7=B4&B5&B6&B7;0;MIN(1;NB.SI(B4:B7;A4)*NB.SI(B4:B7;A5)*NB.SI(B4:B7;A6)*NB.SI(B4:B7;A7)))

formule à copier en C12, C19, C26 et C33

Il y a peut-être plus court.

Pour l'exemple 4, il n'a pas été tenu compte que "Pierre" est mentionné deux fois en colonne A et B, parce que ce n'est pas demandé.
Ce qui implique que si tous les noms de la colonne A se retrouvent en colonne B la formule renvoie 1, ce qui n'est pas le cas si les tous les noms de la colonne B ne se retrouvent pas en colonne A.
Si B26 = André, la formule renvoie 1, mais si A26 = André elle renvoie 0.

Dis nous si c'est ce que tu souhaites obtenir.

 

[pingouinal]

Re : Comparaison de listes

Bonjour hoerwind,

Merci beaucoup pour ta solution, c'est exactement ce que je cherchais.
En plus je connais ces formules et donc je comprends la logique, mais j'étais vraiment loin de penser à faire de la sorte.
J'ai d'autres cas similaires avec des conditions un peu différentes, je vais essayer d'adapter ta logique à ceux-ci également.

Encore merci et bon après-midi.

 

[pingouinal]

Re : Comparaison de listes

Bonjour hoerwind et le forum,

J'ai réussi à adapter la formule donnée plus haut pour une donnée que j'avais oublié, à savoir que la 3e et la 4e places sont la même.
Je m'explique : le fichier en question est en fait un fichier de pronostics pour des podiums. Ceux-ci peuvent avoir pour certaines compétitions 4 places (les sports de combat par exemple) avec deux troisièmes ex-eaquo.

Je vous joins un nouveau fichier avec mes formules complètes car comme la première fois, je pense qu'il est possible de les simplifier largement (et certainement d'accélérer les calculs par la même occasion), mais je ne vois pas comment.
Je mets également dans ce fichier les conditions d'attribution des points pour ne pas surcharger mon post.
J'ai modifier les noms de gens par des noms de pays, pour ajouter la possibilité d'avoir des doublons sur un podium (une même personne ne peux pas être 2 fois sur le podium, mais un même pays peut avoir plusieurs représentants).

Je cherche principalement à simplifier mes formules de calculs pour le podium à 4 (j'ai besoin de 5 formules pour y arriver ), mais si quelqu'un à une version simplifiée de la formule pour le podium à 3 à me proposer, je suis évidemment preneur.

D'avance merci à tous ceux qui pourront m'aider.

tous les

 

[CISCO]

Re : Comparaison de listes

Bonjour à tous, bonjour Hoerwind, bonjour Pingouinal

mais si quelqu'un à une version simplifiée de la formule pour le podium à 3 à me proposer, je suis évidemment preneur.

Deux solutions similaires en pièce jointe, en calcul matriciel, en rouge en D34 et D35. J'ai l'impression que c'est bon. A vérifier...

Pour ce qui est du podium à 4... on verra plus tard.

@ plus

 

[CISCO]

Re : Comparaison de listes

Bonsoir

Que doit on trouver si le pronostic est
France
Allemagne
Suisse
Italie
et que le résultat est
Allemagne
France
Italie
Suisse ?

5 (1 pour chaque pays + 1 car ils sont tous dans le pronostic), il me semble, alors que ta formule donne 7.

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonsoir CISCO,

Le 7 est juste avec le détail ci-dessous :

1 pt pour la France à la mauvaise place
1 pt pour l'Allemagne à la mauvaise place
2 pts pour la Suisse à la bonne place
2 pts pour l'Italie à la bonne place
1 pt pour tous les pays sur le podium (mais pas dans l'ordre complet)

La Suisse et l'Italie sont toutes les deux troisièmes, donc considérées à la bonne place.
Le problème c'est qu'il y a 4 lignes, mais seulement 3 places sur le podium (avec deux 3e ex-aequo), donc si 3=4 ou 4=3, c'est comme si 3=3 ou 4=4.

Merci également pour ta pièce jointe, je vais y jeter un oeil avant de me coucher.

 

[CISCO]

Re : Comparaison de listes

Bonsoir

Bonsoir CISCO,

1 pt pour tous les pays sur le podium (mais pas dans l'ordre complet)

La Suisse et l'Italie sont toutes les deux troisièmes, donc considérées à la bonne place.

J'avais oublié ce "détail". Pas évident, cette histoire...

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonsoir CISCO,

Effectivement, sans ce détail ça aurait été trop simple (enfin pour les habitués du forum, pas pour moi ).

Je viens par contre de trouver une faille dans la formule donnée par hoerwind dans l'exemple suivant :
Pronostic:
1. France
2. Allemagne
3. Allemagne

Réel:
1. France
2. Allemagne
3. Suisse

La formule affiche 1 pt, mais il devrait y avoir 0 car tous les pays ne correspondent pas.
J'ai essayé d'adapter tes fomules, mais j'avoue ne pas bien les comprendre (d'ailleurs, je n'ai pas saisi non plus la différence entre les deux) et donc je bloque dessus et bien sûr toujours sur la simplification des autres...

D'avance merci pour ton aide et pour celle des autres qui se joindraient à ce fil. Mais d'abord bonne nuit à tout le monde...

 

[CISCO]

Re : Comparaison de listes

J'ai essayé d'adapter tes fomules....

J'essayerai de t'expliquer tout cela demain (les deux formules de mon fichier sont presque identiques, l'une avec +LIGNE(A$32), travaillant pa rapport à B$33:B$36, l'autre sans, mais travaillant sur B$1:B$36) because, comme tu le dis

d'abord bonne nuit à tout le monde...

@ plus

 

[CISCO]

Re : Comparaison de listes

Bonsoir

Je vais essayer d'expliquer la différence entre mes deux dernières formules dans mon précédent fichier :
En D34

=SI(OU(ESTNA(EQUIV(A$33:A$36;B$33:B$36;0)));0;(SOMME(ABS(EQUIV(A$33:A$36;[COLOR="Red"][B]B$1:B$36[/B][/COLOR];0)-LIGNE(B$33:B$36)))<>0)*1)

à valider en matriciel

et en D35

=SI(OU(ESTNA(EQUIV(A$33:A$36;B$33:B$36;0)));0;(SOMME(ABS(EQUIV(A$33:A$36;[COLOR="red"][B]B$33:B$36[/B][/COLOR];0)[COLOR="red"][B]+LIGNE(A$32)-[/B][/COLOR]LIGNE(B$33:B$36)))<>0)*1)

à valider en matriciel

Dans la première EQUIV(A$33:A$36;B$1:B$36;0) renvoie la position, par exemple du contenu de A33 dans la plage B1:B33. Autrement dit, cette partie de la formule renvoie le numéro de la ligne où se trouve le contenu de A33 dans la plage B1:B33, à savoir, 33, ou 34, ou 35 ou 36.

Dans la seconde formule EQUIV(A$33:A$36;B$33:B$36;0) renvoie la position, par exemple du contenu de A33 dans la plage B33:B36, à savoir 1, ou 2, ou 3, ou 4. Puis on ajoute LIGNE(A$32), et on obtient 1 + 32 = 33, ou 2 + 32 = 34... On obtient donc le même résultat qu'avec l'autre formule.

La première ne fonctionne bien que si, par exemple, le contenu de A33 ne se trouve pas entre B1 et B32. C'est pour cela que j'ai décalé tes exemples de B1 à B32... Il faut donc adapter tout cela à ton fichier réel.

Ceci dit, ci-joint, un fichier répondant à ta nouvelle demande. Dans le cas du podium à 3 places, je ne trouve pas les mêmes résultats que toi. A toi de voir où est l'erreur... Je ne suis pas vraiment content des formules, trop longues, mais bon, une seule formule suffit à chaque fois... J'ai essayé plein d'autres choses, et à chaque fois, il y a un bug. Grrrrrr

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonsoir CISCO,

Merci beaucoup pour l'explication et pour les formules.
Je vais essayer de les comprendre et de les tester entre ce soir et demain matin, et je ferai un retour demain sur ce fil.

Encore merci de ton aide et bonne soirée.

 

[pingouinal]

Re : Comparaison de listes

Re-bonsoir,

Après un premier test, les formules ont l'air de parfaitement marcher quand les 3 ou 4 pays sont différents. Mais lorsqu'il y a plusieurs fois le même pays dans le podium pronostiqué (celui de gauche), la formule ajoute quasi systématiquement le point de bonus (pour tous les pays présents mais dans le désordre), voire un deuxième point pour un pays qui en a déjà eu.

Voici un exemple :

Pronostic :
1. France
2. Suisse
3. Suisse

Réel :
1. Suisse
2. France
3. Italie

Le score devrait être de 2 pts (1 pour la France mal placée, 1 pour la Suisse mal placée), mais le score affiché est de 4 pts. Si je décrypte bien la formule, le détail est 1 pour la France mal placée, 1 pour la Suisse mal placée, encore 1 pour la deuxième Suisse mal placée et 1 pour tous les pays présents. Bien sûr tous les pays pronostiqués sont présents sur le podium réel, mais la réciproque doit également être vérifiée (je m'aperçois en l'écrivant que je n'étais peut-être pas clair sur ce point depuis le début du post, je m'en excuse)

Lorsqu'il y a plusieurs fois le même pays dans le podium, il faudrait que la formule vérifie si le pays a déjà marqué des points ou non (sachant qu'en plus le même pays peut-être 3 ou 4 fois sur le même podium ), mais je ne connais pas assez les formules MATCH et ISNA (en anglais chez mois, peut-être en français chez toi) pour trouver la solution.
Aurais-tu une idée pour contrer cela?
Dans mon fichier de base j'essayais de le contrer en mettant toutes les possibilités de podiums que j'avais trouvé en condition, mais les formules étaient trop longues et nombreuses, du coup le fichier rame pas mal. De plus j'ai sûrement oublié des probabilités et ne suis donc pas sûr de mes formules.

D'avance merci pour ton aide.

 

[CISCO]

Re : Comparaison de listes

Bonsoir

J'ai une idée pour contrer ce pb (avec NB.SI(....)=1), mais bon, je crois que cela ne sera pas pour ce soir... Tu sais, c'est le genre de truc qui ne devrait prendre que 10 min, et qui, dans la pratique, petite modifiaction après petite modification, test après test, prend 1 h ou plus... Alors, vu l'heure...

@ plus

 

[pingouinal]

Re : Comparaison de listes

Pas de problème CISCO, je te remercie déjà beaucoup pour ton aide d'aujourd'hui.
Je vais profiter de ce temps pour essayer de trouver aussi une réponse par moi-même (même si j'ai des doutes là-dessus ), et aussi pour dormir un peu...