Microsoft 365 (Index+Equiv) imbriqué et inversé

[burger0715]

Bonjour à tous !

Je cherche à exfiltrer des kilomètres dans une BDD qui regroupe toutes les adresses déjà enregistrées. Le problème c'est que je dois trouver une formule qui marche :
En ayant soit le Code Postal Allé et/ou retour
Soit en ayant la ville Allé et/ou retour (Concaténé avec le pays)
Donc : Ville1 + Ville2 OU CP1 + CP2 OU Ville1 + CP2 Ou Ville2 + CP1 et
dans les 2 sens (si dans la base de donnée il y a Vienne-Paris d'enregistré; la formule doit être capable de comprendre que Vienne-Paris = même km que Paris-Vienne).
En sachant que je ne peux pas modifier les fichiers (je peux pas ajouter de case, la formule va être étendu et adapté à toutes les lignes et à d'autres fichiers).
Vous trouverez mes essais et tentatives dans le fichier (qui marche en partie, tout les cas ne sont pas remplis...), notamment avec des index+equiv imbriqué, mais je pense qu'il y a un problème de priorité qui empêche la formule de couvrir tout les cas, et avec des OU mais cela ne fonctionne pas non plus (il m'affiche juste que la condition est VRAI...). Par ailleurs je ne peux malheureusement pas utiliser d'API non plus (même si ce serait bien pratique...).


J'espère que quelqu'un pourra m'aider car j'ai l'impression d'avoir tout essayé ce qui était dans mes compétences, en vous remerciant par avance !
PS : Si je n'ai pas été clair n'hésitez pas à me le dire

 

[job75]

Bonjour burger0715, bienvenue sur XLD,

Je n'ai pas analysé votre formule mais il est évident que sans le pays ou sans le code postal la recherche peut aboutir à plusieurs solutions.

Donc à mon avis ces informations doivent être obligatoires.

Ensuite la formule sera assez simple avec un SOMMEPROD.

A+

 

[burger0715]

Bonjour Job75,

Justement, pour pallier a ce problème la ville est associé au pays (pour éviter par exemple de trouver Vienne en France alors qu'on souhaite la capitale Autrichienne). Les codes postaux sont uniques aux pays, donc il n'y a pas de soucis, la seul limite est s'il y a 2 communes ayant le meme nom dans le meme pays (ce qui existe je sais, mais dans ce cas la on a toujours le CP plus la ville). Et SOMMEPROD additionne les nombres non ? Je n'ai pas besoin d'additionner les km vu qu'ils sont tous écrit dans la base de données, uniquement d'aller les chercher en fonction des informations disponibles.

Sinon, comment feriez vous avec SOMMEPROD, j'ai peut etre mal compris ou je ne vois pas comment faire alors que c'est évident

Merci par avance !

 

[job75]

Voila comment je verrais les choses, avec cette formule en P19 :

=SOMMEPROD(((Feuil1!E$5:E$15=K19)+(Feuil1!I$5:I$15=K19))*((Feuil1!E$5:E$15=O19)+(Feuil1!I$5:I$15=O19));Feuil1!J$5:J$15)

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @burger0715, bonjour @job75
Encore une fois j'arrive un peu tard !
Mais puisque tu as MS365 tu peux essayer cela avec la fonction LET (_BdD étant ta base de données) :
Comme tu précise que tu ne dois pas modifier tes fichiers j'ai fait avec ...

=LET(C_1;INDEX(_BdD;;2);C_2;INDEX(_BdD;;6);V_1;INDEX(_BdD;;4);V_2;INDEX(_BdD;;8);Idx;EQUIV(VRAI;((($I19=C_1)+($I19=C_2)+($K19=V_1)+($K19=V_2))*(($M19=C_1)+($M19=C_2)+($O19=V_1)+($O19=V_2)))>0;0);SIERREUR(INDEX(_BdD;Idx;9);"-"))

Cela vérifie les combinaisons CP1-CP2 CP1-PAYSVILLE2, PAYSVILLE1-CP2, PAYSVILLE1-PAYSVILLE2 dans les deux sens.

Attention ça ne fonctionnera pas avec les versions antérieures (seulement MS365 et OFFICE2021)
Amicalement
Alain

Modif : attention dans ton exemple les CP italiens, tous identiques, conduisent à des erreurs losqu'ils sont renseignés : ils renvoient tous à la première occurrence "Caorso"

 

[burger0715]

Bonjour @AtTheOne et rebonjour @job75

Tout d'abord merci à vous deux pour vos réponses, j'ai préféré utiliser la solution de AtTheOne qui marche super bien et évite de concatener le CP. Par contre, j'ai un problème lorsque je veux "exporter" cette formule dans un autre fichier, elle se met à dysfonctionner (notamment lorsque j'ai uniquement les CP,il m'affiche 0). J'imagine que j'ai mal exporté la formule, mais auriez vous une petite idée de pourquoi ?


Merci d'avance, ça m'a quand meme vraiment débloquer et j'ai appris de nouvelles formules !

 

[AtTheOne]

Re,

, j'ai un problème lorsque je veux "exporter" cette formule dans un autre fichier,

Je pense que c'est à cause du nom défini _BdD qui se réfère à la plage qui contient tes distances entre les villes et qui ne doit pas exister dans le nouveau fichier.

• Essaie de le créer sur celui-ci : sélectionne la plage qui contient les données, et vas dans formule, gestionnaire de noms, nouveau nom nom : _BdD, et valide pour la plage sélectionnée

Sinon, remplace dans la formule _BdD par l'adresse absolue de la plage qui contient les données.
Autre possibilité, les colonnes ne correspondent plus dans le nouveau fichier.
Envoie un extrait du nouveau fichier que l'on puisse vérifier...
Amicalement
Alain

 

[burger0715]

Rebonjour @AtTheOne ,

En effet, c'était peut être un problème de nom, mais aussi de format de cellule ! J'ai donc résolu ce problème, mais je me suis aperçu d'un autre en testant dans tout les sens la formule : Lorsqu'il n'y a pas de CP, et que dans la base de donnée il y a un trou dans les codes postaux (ce qui est le cas dans ma BDD, il y a certaines villes ou je n'ai pas le CP), il affiche par "défaut" les km de la ligne ou il n'y a pas de code postal. Aurais tu une solution pour que la formule montre en "priorité" les km avec les informations qu'elle a (et donc j'imagine qu'elle devra comprendre qu'une cellule vide n'est pas une info)

Je joins le fichier pour que ça soit plus clair, c'est assez dur d'expliquer par écrit

Encore une fois merci d'avance !

 

[AtTheOne]

Bonsoir à toutes & à tous, bonsoir @burger0715
Regarde cette nouvelle version en PJ avec la formule modifiée :

=LET(C_1;INDEX(_BdD;;2);
     C_2;INDEX(_BdD;;6);
     V_1;INDEX(_BdD;;4);
     V_2;INDEX(_BdD;;8);
     Idx;EQUIV(VRAI;(((($I19=C_1)+($I19=C_2))*($I19<>"")+(($K19=V_1)+($K19=V_2))*($K19<>""))*((($M19=C_1)+($M19=C_2))*($M19<>"")+(($O19=V_1)+($O19=V_2))*($O19<>"")))>0;0);
SIERREUR(INDEX(_BdD;Idx;9);"-"))

J'ai ajouté la condition <>"" dans les tests

A bientôt
Amicalement
Alain

 

[burger0715]

Bonjour @AtTheOne
Le Week-end étant passé je viens d'essayer la formule et elle marche parfaitement ! A priori toutes les conditions sont remplis et je n'avais pas pensé à mettre une banale condition comme ça . En tout cas merci infiniment de m'avoir aidé !! Je test la formule en "condition réelle" et je reviens vers toi si elle bug mais il n'y a pas de raison !

Encore une fois merci !