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, salut les autres,

Je réponds à ta question posée en MP.

Sous la question initiale (#1) il était demandé :
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

Comme tu ne précisais rien concernant les noms de B par rapport à ceux de A, j'en ai fait la remarque sous ma réponse (#2) :
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.
Cette remarque est restée sans réaction.

Maintenant, d'après ce que j'ai compris, tu souhaites que ce qui est valable pour A le soit aussi pour B.

Rien de plus facile, il suffit d'appliquer sur la colonne B les quatre tests (NB.SI) existants sur la colonne A sous la formule proposée initialement.
Vois la pièce jointe et dis nous si c'est cela ce que tu souhaites.

 

[CISCO]

Re : Comparaison de listes

Bonjour

Vraiment pas évident cette histoire...

Que devrait donner la situation suivante :
Pronostic
France
Suisse
Suisse

Réel
Suisse
France
Suisse

@ plus

 

[pingouinal]

Re : Comparaison de listes

Bonjour hoerwind,

Merci pour ta réponse.
Je me rends compte que j'ai effectivement laissé sans réponse ta précédente remarque car je n'avais pas réagi que cela avait une influence sur les points que je souhaitais (je ne regardais mon podium que dans un sens).
Je pense que je me suis mal exprimé au début et vous ai par conséquent sûrement lancé sur de mauvaises pistes. Pour cette formule, il faut donc que les noms des colonnes A et B soient exactement les mêmes (en quantités égales, c'est le point sur lequel je bute), mais pas dans un ordre complet.

J'ai regardé le fichier joint, mais je recontre un problème avec la configuration suivante :

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

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

La formule m'affiche 1 point alors que les deux colonnes n'ont pas excatement les mêmes listes (mêmes pays mais 2x France en pronostic alors que 2x Suisse en réel).

Encore désole si je n'ai pas été très clair dans mes attentes au début, et d'avance merci pour ton aide et celle des autres.

 

[pingouinal]

Re : Comparaison de listes

Bonjour

Vraiment pas évident cette histoire...

Que devrait donner la situation suivante :
Pronostic
France
Suisse
Suisse

Réel
Suisse
France
Suisse

@ plus

Bonjour CISCO,

Ca me rassure un peu de ne pas être le seul à galérer sur ces formules... ^_^'

Pour le podium que tu proposes, il devrait y avoir 5 points :
1 pt pour la Suisse première mais pronostiquée deuxième
1 pt pour la France deuxième mais pronostiquée première
2 pts pour la Suisse troisième et pronostiquée troisième
1 pt pour tous les pays présents mais dans le désordre

 

[hoerwind]

Re : Comparaison de listes

Salut CISCO,

Si toi aussi tu t'y mets !

A mon avis 1, car tous les noms de "Pronostic" se retrouvent sous "Réel" et dans un ordre différent, et vice-versa (les noms de "Réel" sous "Pronostic").
Mais il faut, sous la formule, limiter le nombre de lignes à 3, car en prenant 4 lignes (une vide), la formule renvoie 0.

Il est à espérer que notre ami ne nous demande pas maintenant que la formule s'adapte automatiquement aux nombres de lignes du tableau qu'il souhaite tester.

 

[hoerwind]

Re : Comparaison de listes

Re,

Je viens de prendre connaissance de ta réponse #18.

La formule m'affiche 1 point alors que les deux colonnes n'ont pas excatement les mêmes listes (mêmes pays mais 2x France en pronostic alors que 2x Suisse en réel)

Peut-être ai-je mal lu, mais où as-tu précisé cela sous ta question initiale (#1) ?

 

[pingouinal]

Re : Comparaison de listes

Re,

Je viens de prendre connaissance de ta réponse #18.

La formule m'affiche 1 point alors que les deux colonnes n'ont pas excatement les mêmes listes (mêmes pays mais 2x France en pronostic alors que 2x Suisse en réel)

Peut-être ai-je mal lu, mais où as-tu précisé cela sous ta question initiale (#1) ?

Re-bonjour hoerwind,

Comme je l'ai dit dans cette même réponse #18, je n'avais pas précisé cela dans ma requête initiale et je m'en excuse.
C'était clair dans ma tête (comme je butais sur ces formules depuis des jours), mais je l'ai mal exprimé. En disant tous les pays de A doivent être en B, je pensais dans les mêmes quantités, mais en le relisant je me rends compte que ce n'était absolument pas clair. Encore désolé pour cela.

 

[hoerwind]

Re : Comparaison de listes

Re,

Ce n'est pas grave, et j'espère pour toi que quelqu'un te trouvera une solution.

 

[CISCO]

Re : Comparaison de listes

Bonjour

Un essai en pièce jointe, avec une nouvelle formule uniquement en I13, pour le cas podium à 3 places... Je ne sais pas si cela fonctionne... C'est un peu une essai au pif... Autrement dit, il faut vérifier très, très sérieusement... Pour la formule dans I4, on verra plus tard !!!

D'autre part, suite aux posts d'Hoerwind (bonjour au passage), j'aimerai savoir où tu en es avec ta première question, celle permettant d'obtenir des 0 ou des 1 en fonction de la situation... Ma proposition dans mon premier fichier te va t'elle, où présente t'elle aussi un problème...?

@ plus

 

[pingouinal]

Re : Comparaison de listes

Re-bonjour CISCO,

tout d'abord merci pour ce nouveau fichier.
Je viens de tester rapidement la formule du podium à 3 et apparemment ça fonctionne très bien (le problème rencontré hier soir, à savoir plusieurs fois le même pays a l'air résolu).

Pour ce qui est de la formule qui ajoute un point au podium à 4, elle a l'air bonne. Mais il y a un autre problème dans l'exemple suivant :

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

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

Le total devrait être de 7 pts (1 pour la France, 2 pour l'Allemagne, 2 pour la Suisse, 1 pour la 2e Suisse et 1 pour tous les pays) et la formule donne 8 pts (si je la comprends bien : 1 pour la France, 2 pour l'Allemagne, 2 pour la Suisse, 2 pour la 2e Suisse et 1 pour tous les pays).

J'ai tenté la modification suivante pour compter les points de la 4e place, mais j'aimerais ton avis dessus car je ne suis pas sûr qu'elle soit bonne (et surtout exhaustive).

Formule de base :
...SI(OU(C7=E6;C7=E7);2;SI(ESTNA(MATCH(C7;E4:E7;0));0;1))...
Formule modifiée :
...SI(OU(ET(C7=E6;C6<>E6);C7=E7);2;SI(ESTNA(MATCH(C7;E4:E7;0));0;1))...

Et bien sûr l'inverse pour calculer les points de la 3e place (à savoir C7<>E6).

Il me vient aussi deux questions à l'esprit :

1. J'ai l'impression que la formule à 4 donne le même résultat en matriciel et en normal (avec et sans les {}). Il y a sûrement quelque chose qui m'échappe, mais pourquoi sont-ils là?

2. A priori (même si je vais encore tester beaucoup ces deux formules), celle du podium à 4 marche (notamment en ce qui concerne le point si tous les pays sont représentés en quantité correcte) en étant plus concise que celle du podium à 3 alors que selon moi elle devrait être plus complexe. Comment se fait-il qu'il y ait une telle différence entre les deux?

Je precise bien sûr que ces deux questions ne sont absolument pas des critiques, mais j'essaie juste de comprendre la logique derrière.

D'avance merci pour ta réponse.

 

[CISCO]

Re : Comparaison de listes

Bonjour à tous, bonjour Pingouinal

Perso, je ne crois pas trop à la validité de la formule dans la cellule I13. Je ne sais si tu as repéré, mais elle est constituée comme suit :
* un test pour décider si on utilise les partie A ou B ci-dessous
* partie A : travail en comparant le contenu de la plage utile pronostic C13:C15 à la plage réelle E13:E15 (presque comme dans mon premier fichier exmple(1) 4)
* partie B : travail en comparant le contenu de la plage utile réelle E13:E15 à la plage pronostic C13:C15

Par curiosité, la seconde partie ressemblant à la première, je l'avai crée dans une cellule. Constatant qu'elle semblait donner le bon résultat lorsque l'autre ne le donnait pas, j'ai assemblé les deux...

Si on ne trouve rien d'autre, de plus logique, si tous tes tests sont positifs, il ne te restera plus qu'à faire avec... Mais bon, je ne trouve pas cela très rigoureux... A voir donc.

Pour ce qui est de la longueur des formules en I4 et I13, je vais vois cela de ce pas. Je n'ai pas fini le travail sur celle en I4. Ceci explique peut être cela.

@ plus

 

[CISCO]

Re : Comparaison de listes

Bonjour

Re-bonjour CISCO,

1. J'ai l'impression que la formule à 4 donne le même résultat en matriciel et en normal

Normalement, il devrait y avoir, parfois, une différence, car la partie SOMME(EQUIV(C4:C7;E4:E7;0);EQUIV(E4:E7;C4:C7;0)) ne donne pas le même résultat dans tous les cas :

* sans caclul matriciel, c'est la même chose que
SOMME(EQUIV(C4;E4:E7;0);EQUIV(E4;C4:C7;0)), autrement dit, excel ne recherche que la position du contenu de C4 dans E4:E7 et de celui de E4 dans C4:C7

avec le calcul matriciel, c'est la même chose que
SOMME(EQUIV(C4;E4:E7;0);EQUIV(E4;C4:C7;0);EQUIV(C5;E4:E7;0);EQUIV(E5;C4:C7;0);EQUIV(C6;E4:E7;0);EQUIV(E6;C4:C7;0);EQUIV(C7;E4:E7;0);EQUIV(E7;C4:C7;0))

Bien sûr, puisque ce qui nous intéresse, ce n'est pas le résultat de cette somme, mais savoir si c'est un nombre (autrement dit tous les contenus de C4 à C7 sont dans E4 à E7, et réciproquement, tous les contenus de E4 à E7 sont dans la plage C4 à C7), ou un #NA (un des contenus manque et donne #NA, donc une somme en #NA), très souvent, cela donne le même résultat final. Mais lorsque le contenu manquant n'est pas en premier, ces deux situations, calcul normal, et calcul matriciel, ne devraient pas donner le même résultat.

@ plus

 

[CISCO]

Re : Comparaison de listes

Re

....
Je precise bien sûr que ces deux questions ne sont absolument pas des critiques, mais j'essaie juste de comprendre la logique derrière.
...

Il ni y a pas de problème. Je comprend que tu te poses des questions.... et il est normal, et même je préfère que tu essayes de comprendre les formules que je te proposes.

@ plus

 

[pingouinal]

Re : Comparaison de listes

Tu as raison sur la formule en I13 CISCO, les premiers essais paraissaient très bons, mais je viens de trouver une faille.

Pronostic :

1. France
2. Suisse
3. Italie

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

Score espéré : 2 pts (France 2e au lieu de 1er, et Suisse 1er au lieu de 2e).
Score affiché : 1 pt.
Par contre, j'avoue que je suis encore en train d'essayer de décortiquer la fameuse formule, et donc je ne vois pas d'où vient le problème.

Merci pour tes explications pour la formule matricielle. Effectivement, mieux vaut garder les {}.
Je vais continuer mes tests sur cette formule pour confirmer qu'elle fonctionne, mais pour le moment c'est bien parti.

 

[CISCO]

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