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. ^_^'

 

[pingouinal]

Re : Comparaison de listes

Re

Qu'en à

SI(OU(ET(C7=E6;C6<>E6);C7=E7);2;SI(ESTNA(MATCH( C7;E4:E7;0));0;1))...


je ne pense pas qu'elle soit exhausive : par exemple, qu'est-ce que cela donne si Suisse est sur la ligne 5 ?

@ plus

Tu veux dire Suisse en E5?
Je viens de faire un test et ça paraît correct.
Tu aurais un exemple du podium qui poserait problème selon toi?

 

[CISCO]

Re : Comparaison de listes

Bonjour

Pronostic
France
Suisse
Allemagne
Suisse

Réel
Allemagne
Suisse
Suisse
France


Les formules donnent 7 (en F4 aussi), et il me semble que l'on devrait avoir 6.

@ plus

 

[pingouinal]

Re : Comparaison de listes

Tu as effectivement raison : un tel podium devrait donner 6 pts.
Nous revoilà donc au point de départ pour les deux formules (podiums à 3 et à4)...

 

[pingouinal]

Re : Comparaison de listes

J'ai revérifié CISCO et en fait les 7 pts sont justes avec le décompte suivant :

1 pt pour la France pronostiquée 1ère et qui est 3e
2 pts pour la Suisse pronostiquée 2e et qui est 2e
1 pt pour l'Allemagne pronostiquée 3e et qui est 1ère
2 pts pour la Suisse pronostiquée 3e et qui est 3e
1 pt pour avoir les bons pays en bonne quantité sur le podium mais dans le désordre

Le problème vient du fait que les 3e et 4e places sont en fait des 3e places ex-aequo.

Ca me rassure, on avance mais j'en arrive à m'embrouillé dans les décomptes alors que c'est moi qui ai fait le barême... 😀

 

[CISCO]

Re : Comparaison de listes

RE

J'ai revérifié CISCO et en fait les 7 pts sont justes avec le décompte suivant :

Le problème vient du fait que les 3e et 4e places sont en fait des 3e places ex-aequo.

Effectivement, j'avai encore oublié cette possibilité.

@plus

 

[CISCO]

Re : Comparaison de listes

Rebonjour

Une autre possibilité en I13 pour le podium à 3. Est-ce bon ?

@ plus

P.S : Et non, ce n'est pas bon. Exemple de situation qui ne donne pas le bon résultat :
Pronostic
Suisse
France
Suisse

Réel
France
Suisse
Italie


Car les 2 "Suisse" sont comptés pour 1 à chaque fois, soit 2 au total, alors qu'il faudrait n'en prendre qu'un en compte...


Grrrr. Va falloir trouver autre chose.

 

[CISCO]

Re : Comparaison de listes

Bonsoir


En pièce jointe, une autre méthode, plus rigoureuse à mon avis.

Je vérifierai tout cela davantage demain.

@ plus

P.S : En I5, pour le podium à 4, j'ai nommé certaines parties de la formule car excel n'acceptait pas la formule proposée à cause du nombre de SI.
En pratique, on peut d'ailleurs faire avec un seul nom, en faisant faire quelques cacluls inutiles à excel. Si tu constates que cela te donne les bons résultats, je ferai cette modif, histoire de simplifier un peu la formule.
Si tu as du mal à comprendre la formule, j'essayerai de mettre une "petite" explication sur le fil.
Si je trouve plus court, je te fais signe... Bon dimanche.

 

[CISCO]

Re : Comparaison de listes

Bonjour à tous

Pingouinal, comme tu n'en as pas parlé, les formules que j'ai mis dans mes précédents fichiers ne tiennennt pas compte des cas suivants : 1 et 2 ex-aequo, 2 et 3 ex-aequo. J'espère que cela ne pose pas de problème...

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonjour CISCO et les autres qui suivent ce fil,

Merci beaucoup pour tes formules, je vais les tester en fin d'après-midi et te tiendrait au courant des résultats.
En ce qui concerne les cas que tu cites (1er et 2e ex-aequo, 2e et 3e ex-aequo), ça n'est pas un problème. Si un tel cas devait arriver, je calculerais les points manuellement pour chaque participant (les cas d'égalité étant assez rares donc je peux me permettre de le faire à la main).

Encore merci pour ton aide, je ferai un retour dessus en fin de journée.

Edit : après un premier test, je bloque sur le cas suivant :

Pronostic:
1. France
2. Allemagne
3. Suisse
4. Allemagne

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

Je devrais avoir 5 pts selon le decompte ci-dessous et la formule affiche 6.
1 pt pour la France mal placée
2 pts pour la Suisse 3e
2 pts pour l'Allemagne 3e

 

[CISCO]

Re : Comparaison de listes

Bonsoir à tous

Ouf, ce n'est pas le principe des tests sur C4, C5, C6 et C7 séparement qui est en cause... Ouf.

C'est la partie

SI(ESTNUM(SOMME(EQUIV(C4:C7;E4:E7;0);EQUIV(E4:E7;C4:C7;0)));1;0)

(pour voir si tous les termes dans la plage C4:C7 se trouvent dans la plage E4:E7, et réciproquement) qui ne convient pas, toujours pour la même raison, à savoir le fait que la fonction Equiv indique la position du terme recherché, pas si on trouve ce terme 1 fois, deux fois...

Pour résoudre ce problème, j'ai de nouveau utilisé les fonctions SUBSTITUE et CONCATENER, et apparemment, cela fonctionne bien avec

SI(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(E4&E5&E6&E7;C4;"";1);C5;"";1);C6;"";1);C7;"";1)=""

@ plus

P.S : Le calcul n'est alors plus matriciel puisqu'on n'utilise plus la partie en SOMME(EQUIV(C4:C7;E4:E7....

 

[pingouinal]

Re : Comparaison de listes

Bonsoir CISCO,

Merci pour cette solution rapide.
Je vais tester ça tranquillement et j'espère pouvoir venir te dire que cette fois tout est bon.

Par contre j'ai une petite question : à quoi font référence les parties nommées dans la cellule (sansidentiqueor par exemple)? J'ai essayé de trouvé une partie du fichier nommée ainsi (comme dans une formule recherchev), mais impossible de la trouver.

D'avance merci.

 

[CISCO]

Re : Comparaison de listes

Rebonsoir

En réalité, la formule en I5 est :
=SI(C4=E4;3;SI(ESTNUM(CHERCHE(C4;SI(C5<>E5;E5;"")&SI(ET(C6<>E6;C7<>E6);E6;"")&SI(ET(C7<>E7;C6<>E7);E7;""))));1;0))

+SI(C5=E5;2;SI(ESTNUM(CHERCHE(C5;SUBSTITUE(SI(C4<>E4;E4;"")&SI(ET(C6<>E6;C7<>E6);SI(C4<>E4;C4;"");"";1)));1;0))

+SI(OU(C6=E6;C6=E7);2;SI(ESTNUM(CHERCHE(C6;SUBSTITUE(SUBSTITUE(SI(C4<>E4;E4;"")&SI(C5<>E5;E5;"")&SI(ET(C7<>E7;C6<>E7);E7;"");SI(C4<>E4;C4;"");"";1);SI(C5<>E5;C5;"");"";1)));1;0))

+SI(OU(C7=E7;C7=E6);2;SI(ESTNUM(CHERCHE(C7;SUBSTITUE(SUBSTITUE(SUBSTITUE(SI(C4<>E4;E4;"")&SI(C5<>E5;E5;"")&SI(ET(C6<>E6;C7<>E6);E6;"");SI(C4<>E4;C4;"");"";1);SI(C5<>E5;C5;"");"";1);SI(C6<>E6;C6;"");"";1)));1;0))

+SI(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(E4&E5&E6&E7;C4;"";1);C5;"";1);C6;"";1);C7;"";1)="";1;0)

+SI(OU(C4&C5&C6&C7=E4&E5&E6&E7;C4&C5&C6&C7=E4&E5&E7&E6);5;0)

sans calcul matriciel

mais comme il y a trop d'imbrications, excel ne peut pas calculer le résultat de cette expression.

Pour contourner ce problème, j'ai donné un nom aux parties en couleur.
Pour voir la bonne formule utilisée pour définir sansidentiqueor et les autres, il faut avoir auparavant sélectionné la cellule I5, puis -->insertion-->définir un nom me semble t'il (travaillant sur excel 2007, je ne me souviens plus très bien des menus d'excel 2003)... Tu verras alors les parties en couleur ci-dessus avec des sheet1!, sheet2! .... en plus.
Si tu veux ne pas avoir de problème par rapport à ces noms, pour pouvoir les utiliser ailleurs qu'en I5, il faudra certainement ajouter des $ dans ces définitions.

D'autre part, comme dit dans un de mes précédents posts, on peut utiliser un seul nom pour simplifier un peu l'écriture de la formule. Cf fichier ci-joint avec sansidentique défini par
=SI(C4<>E4;E4;"")&SI(C5<>E5;E5;"")&SI(ET(C6<>E6;C7<>E6);E6;"")&SI(ET(C7<>E7;C6<>E7);E7;"")
et qui concatene les résultats sans les lignes contenant les mêmes noms en colonne C et en colonne E.
Cette dernière méthode fait faire un ou des calculs pour rien à excel.

Remarque 1 : Cette gargantuesque formule ne donnera pas le bon résultat si tu as des noms imbriqués les uns dans les autres, du style France et Ile de France, ou encore Bretagne et Grande Bretagne...

Remarque 2 : Ici aussi le calcul matriciel n'est plus nécessaire puisqu'on n'utilise plus la partie SOMME(EQUIV(C4:C7;E4:E7...

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonsoir CISCO,

Merci pour cette explication. J'apprends donc qu'il est possible d'inclure une formule en nommant une plage de cellule.

Par contre j'ai de nouveau une question : j'ai besoin de copier cette formule plusieurs fois (puisque plusieurs podiums). J'ai donc fait un simple copier de la zone B4:I7 et l'ai collé en B20:I24. Je n'ai pas touché à autre chose, le nom "sansidentique" correspond donc toujours à une plage entre les lignes 4 et 7. Cependant, la formule copiée-collée à l'air de marcher complètement.
Ma question est donc : est-ce normal que la formule marche alors que "sansidentique" fait référence à une plage qui concerne un autre podium?
Ou dois-je renommé la nouvelle plage de cellule (concernant le nouveau podium) avec un autre nom et mettre à jour la nouvelle formule (avec le nouveau nom de plage)?

Pour ce qui est des problèmes de noms, effectivement c'est un souci dans quelques cas, mais seul le pronostic de la Russie et la victoire de la Biélorussie me paraît embêtant (les autres pays potentiellement "à problème" ayant peu de chances d'être pronostiqués). Si tu as une solution rapide, je suis preneur (histoire d'avoir un fichier complètement propre). Sinon pas de souci, je renommerai la Russie en Fédération de Russie et le tour sera joué. 😀

D'avance merci.

 

[CISCO]

Re : Comparaison de listes

Bonsoir CISCO,

...Je n'ai pas touché à autre chose, le nom "sansidentique" correspond donc toujours à une plage entre les lignes 4 et 7. Cependant, la formule copiée-collée à l'air de marcher complètement...

Comme je n'ai pas mis de $ dans la définition du nom sansidentique, on a une définition relative. En I5, cette définition utilise certaines données de la plage B4:E7. En I21, elle utilise certaines données de la plage B20:E23. Donc, c'est normal que cela donne la bonne réponse...

Par contre, comme il s'agit d'une définition relative, elle évolue en fonction de l'endroit où on l'utilise. Autrement dit, telle que, il faut que la formule soit toujours positionnée exactement comme I5 par rapport à B4:E7.

On peut très bien modifié cela (Cf un exemple en pièce jointe).

Malheureusement, en faisant ce nouvel exemple, j'ai trouvé une situation non prévue par moi, et où la formule donne un mauvais résultat, à savoir :
Pronostic
Grèce
France
Suisse
Suisse

réel
Grèce
France
Allemagne
Suisse

Combien de point cela devrait-il donner ?

A toi de me dire... Si besoin est, je ferai la modif en conséquence... J'ai déja ma petite idée sur le sujet...

Si tu as une solution rapide, je suis preneur (histoire d'avoir un fichier complètement propre). Sinon pas de souci, je renommerai la Russie en Fédération de Russie et le tour sera joué. 😀

D'avance merci.

Je n'ai pas de solution rapide... J'en vois au moins une, mais malheureusement, elle rallonge encore beaucoup la formule...

@ plus

 

[pingouinal]

Re : Comparaison de listes

Je me rends compte que ma question était peut-être un peu bête.
Ce qui m'a en fait étonné était de voir qu'il n'y avait qu'un nom "sansidentique" et que celui-ci prenait les valeurs des lignes souhaitées en fonction du podium (lignes) choisi. J'étais en fait persuadé qu'un nom était associé à une plage de cellule fixe et que si l'on voulait utiliser des cellules quelques lignes plus bas, il fallait enregistrer un autre nom, d'où mon étonnement. Cela dit, tant mieux... 😀

Pour ce qui est du podium que tu évoques, il doit donner 7 points :
3 pour la Grèce, 2 pour la France et 2 pour la Suisse.
Mais la formule affiche 9 pts.

Concernant les noms de pays, penses-tu que le fait de rallonger la formule va ralentir les calculs? Comme le fichier final va en comporter énormément et qu'avec ma première version des formules (qui n'était pas juste), ça ramait déjà beaucoup, un des buts était de ne pas allonger cette durée des calculs (et bien sûr de trouver les formules qui donnent le bon résultat 😛).
Si cela complexifie trop le fichier, je feintera en changeant le nom de certains pays comme expliqué dans mon précédent post. Mais si la longueur de la formule n'a pas d'impact réel sur les temps de calculs, alors je suis preneur de toute solution...

Encore merci pour ton aide.