Discussion :

[lcoulon]

Bonjour,

J'ai besoin d'insérer dans un tableau des coordonnées de personnes.

Le classeur est plutôt simple,comporte 2 feuilles. ( voir fichier demo ci joint )

L'une ou je rentre les coordonnées et l'autre est une base de donnée qui contient les codes postaux, villes , départements et régions de France.

Je voudrai que lorsque je rentre un code postal : la ville , le département et la région associée soit automatiquement mis à jour
Il faudrait aussi qu'a chaque nouvelle ligne entrée, la formule INDEX et EQUIV soit correctement reportée sur la nouvelle ligne et ça je ne parviens à copier correctement la formule, Excel incrémente A2 et 1 à chaque fois ce qui fausse les résultats ...

Par exemple, pour recherche de la ville :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX('BD France'!A2:E36743;EQUIV($D2;'BD France'!A2:A36743;0);2)

--

Ensuite, lorsque la 1ere partie ci dessus fonctionnera je voudrai aller un peu plus loin :

Comment dois je procéder pour dès lors qu'un élément est entré qui peut être soit le CP OU BIEN la ville, cela rempli automatiquement les champs en relation ?
Ex :
je rentre le CP -> la ville + le département + région sont mis à jour.
je rentre la ville -> le CP + le département + région sont mis a jour.

En fait Excel laisse le choix d'entrer soit le CP OU bien la Ville et met à jour les champs manquants en fonction.

Merci beaucoup pour votre aide,

[halaster08]

Bonjour,

Excel incrémente A2 et 1 à chaque fois ce qui fausse les résultats ...

Utilise les dollars

Par exemple, pour recherche de la ville :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX('BD France'!$A$2:$E$36743;EQUIV($D2;'BD France'!$A$2:$A$36743;0);2)

[Menhir]

Par exemple, pour recherche de la ville :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDEX('BD France'!A2:E36743;EQUIV($D2;'BD France'!A2:A36743;0);2)

Mets des $ dans les références de la zone indexée et la zone de recherche pour les figer.
Sinon elles vont "glisser" en fonction de la ligne où la formule est enregistrée.

[lcoulon]

En ajoutant des $, cela fige les plages souhaitée, merci beaucoup.

Par contre je ne comprends pas pourquoi pour certains codes postaux, le resultat reste N/A alors que la formule est identique à celles ou le resultat est bien celui attendu.

Voici l'exemple qui montre cela.

[halaster08]

Concernant les fichiers joints : fichiers-joints-discussions/

Es tu sur que le code postal en question est bien dans ta liste ?
Es tu sur qu'il n'y a pas d'espace ou d'autres caractères en trop dans ton code postal ?
Si tu essaye seulement la partie equiv de ta formule quel est le résultat ?

[Menhir]

Par contre je ne comprends pas pourquoi pour certains codes postaux, le resultat reste N/A alors que la formule est identique à celles ou le resultat est bien celui attendu.

Parce que Excel ne trouve pas de correspondance.

Est-ce que tes cellules de CP (le critère aussi bien que la plage de recherche) sont au format texte ?
Est-ce qu'il n'y a pas des espaces en trop dans le code CP (en début, en fin ou entre département et commune) ?

Pour info, il serait plus simple de passer par RECHERCHEV que par INDEX+EQUIV :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=RECHERCHEV($D2;'BD France'!$A$2:$B$36743;2;FAUX)

[Pierre Fauconnier]

Salut.

En ajoutant des $, cela fige les plages souhaitée, merci beaucoup.

Tu aurais intérêt à utiliser des tables de données. Cela éviterait l'omission des dollars et te permettrait d'avoir une zone qui serait adaptée lors de l'ajout ou de la suppression de données dans la feuille BD France, puisque ta plage est figée de A2 à E36743.


De plus, tu utilises INDEX d'une façon impropre en l'utilisant sur ta plage entière et pas uniquement sur la colonne B, car tu y glisses une constante.

=INDEX('BD France'!A2:E36743;EQUIV($D2;'BD France'!A2:A36743;0);2)

Si jamais tes colonnes sont déplacées dans DB France, la colonne des localités ne sera plus la 2 et tes INDEX te renverront de mauvaises données ou des erreurs. Perso, je travaillerais donc avec un Index/Equiv par colonne à restituer. Tu éviteras ces erreurs alors qu'ici, tu utilises INDEX/EQUIV avec la même contrainte que RECHERCHEV, à savoir un indice de colonne en constante.

J'utiliserais donc:

INDEX('BD France'!B2:B36743;EQUIV($D2;'BD France'!A2:A36743;0))

(sans ;2 à la fin).

Note pour plus tard: Toujours utiliser INDEX/EQUIV et plus jamais RECHERCHEV (voir mon billet de blog à ce sujet, et notamment le point 1 qui parle de la constante pour l'indice de colonne). RECHERCHEV est à bannir d'Excel. Cette fonction est bien trop contraignante et source de problèmes, alors que la combinaison INDEX-EQUIV est aussi simple à utiliser et permet plus de choses en toute sécurité.

En ajoutant des $, cela fige les plages souhaitée, merci beaucoup.

Par contre je ne comprends pas pourquoi pour certains codes postaux, le resultat reste N/A alors que la formule est identique à celles ou le resultat est bien celui attendu.

Voici l'exemple qui montre cela.

Dans DB France, les CP sont en texte. Tu dois donc les saisir en texte dans Fichier Clients. C'est pour cela que certains CP ne sont pas trouvés: ils sont en format numérique, qui plus est "spécial code postal" (une horreur sans nom de Microsoft).

Attention également au format de la colonne D de Fichier Clients... Le format spécial Code postal inventé par Microsoft est une connerie de la pire espèce. Il ajoute visuellement un 0 devant les codes postaux des neufs premiers départements. Si, dans DB France, tes codes postaux sont du texte avec cinq chiffres, tu auras forcément des #N/A pour ces départements.

Il ne faut jamais saisir les codes postaux comme des valeurs numériques, même et surtout pas avec ce format "spécial" merdique, mais toujours dans des cellules formatées en TEXTE. (On ne met en numérique que des valeurs sur lesquelles on est susceptible de réaliser des opérations arithmétiques ou mathématiques).

[lcoulon]

Merci à vous,
A présent, je fais face à un nouveau probleme :

Plusieurs villes en France ont un code postal commun, par exemple pour le code postal : 01090 cela peut correspondre à ces 3 communes :

01090 LURCY
01090 MONTCEAUX
01090 MONTMERLE-SUR-SAONE

Comment dois je faire apparaitre dans la cellule VILLE, une liste déroulante qui offrirait la possibilité à l'utilisateur de choisir parmis les villes répondant au meme code postal ?

Je pense que la formule de recherche avec INDEX et EQUIV devient obsolete puisqu'il faut gérer l'eventualité que plusieurs choix de villes sont alors possibles.

Cette formule se contente d'afficher la 1ere ville correspondant au code postal entré :
=INDEX('BD France'!$B$2:$B$37000;EQUIVMATCH($D2;'BD France'!$A$2:$A$37000;0))

[Pierre Fauconnier]

Soit tu utilise INDEX-EQUIV et c'est effectivement la première qui apparaît, soit tu places une liste déroulante, mais tu devras alors saisir chaque localité à la main et bénéficier d'une liste déroulante qui, dans la plupart des cas, te renseignera une seule ligne de choix.

Excel n'étant pas fait pour gérer des données, tu n'auras que des solutions bancales pour la situation que tu rencontres (sauf à programmer en VBA, mais c'est un autre débat et un autre sujet...).

[Menhir]

Dans la mesure où la validation de données n'accepte pas les formules matricielles pour les références des validations par liste, il n'est pas possible de faire ce que tu souhaites par des moyens conventionnels.
Même en passant par du VBA, ça doit être complexe et lourd.

Comme Pierre, je pense que tu utilises un couteau pour visser une cruciforme.
Une telle application est manifestement une base de données et devrait être gérée comme telle avec un SGBD.

[cb_60]

Bonsoir
Un exemple en fonction de ton fichier, la colonne A de "bdFrance" doit être toujours du même format (code postal) que j'ai multiplié par 1 et dans ta colonne D de "fichierClient" ce doit être le même format,
Ensuite dans la colonne E, il y a une liste déroulante, permettant de choisir la ville par rapport au code postal, cette liste déroulante ne correspond qu'a ce code postal.
A tester, ensuite tirer les formules des colonnes E,F,G,H

[Pierre Fauconnier]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Un exemple en fonction de ton fichier, la colonne A de "bdFrance" doit être toujours du même format (code postal) que j'ai multiplié par 1

ON NE PEUT PAS MULTIPLIER DES CODES POSTAUX PAR 1!!

Des codes postaux, ça ne se multiplie pas!! Vous perdrez les 0 des CP des villes des neuf premiers départements. il est de loin préférable d'unifier les formats dans l'autre sens, c'est-à-dire de mettre tout en texte, dans la table de données ET dans la feuille de saisie. Et pour corriger ce problème du zéro qui s'en va, vous utilisez un truc plus que merdique de MS, ce format spécial "code postal"... Ce n'est pas une solution, c'est juste de la merde, ce truc proposé par Microsoft.

Outre que ça ne règle en rien le fait qu'on voudrait choisir une valeur dans une liste déroulante et en même temps avoir une formule dans la même cellule.

Le problème, en outre, ce n'est pas de faire une liste en cascade, c'est que soit on a une liste en cascade pour tout code postal choisi, alors que la majorité des codes postaux sont unis à une seule ville, soit qu'on a INDEX-EQUIV et donc on tombe, dans les moins nombreux cas de villes différentes avec cp identiques, sur la première ville rencontrée.

Désolé, CB_60, ce n'est pas contre toi que j'en ai, bien entendu...

[cb_60]

Bonsoir
" Désolé, CB_60, ce n'est pas contre toi que j'en ai, bien entendu... ", il ni a pas de soucis..
J'ai bien multiplié les code postaux par 1, pour moi ça a fonctionné, et ma liste déroulante fonctionne aussi.

[Pierre Fauconnier]

Je ne dis pas que ça ne fonctionne pas, je dis que c'est une aberration de multiplier les codes postaux par 1 puis de camoufler la perte du zéro à gauche par un ajout visuel.

Ca va compliquer toutes les formules futures d'une part, et ça va renvoyer des résultats erronés dans d'autres cas.

Regarde l'illustration suivante:



En D1 et D2, la valeur numérique 1100 avec le format spécial code postal France (aligné à gauche sinon c'est pas drôle).
NB.SI.ENS renvoie 0 pour E1 car il n'y a pas de CP égal à 1100.
NB.SI.ENS renvoie 2 (les deux villes du département 11 qui ont 1100 comme début de CP), alors que tu t'attendrais à ce qu'il renvoie 7 pour les sept villes en vert (moi, en tout cas, je m'attendrais à cela sur base de ce que je vois). Et il renvoie 2 car il y a bien deux villes dont le CP commence par 1100.

Si je veux compter les villes d'un département (ici, j'ai pris 1 et 11 en exemple), je serais tenté d'utiliser la formule =NB.SI.ENS(A2:A36743;"01*") pour l'Ain et =NB.SI.ENS(A2:A36743;"11*") pour l'Aude, mais ça va foirer (normal, en A, je pense avoir des suites de 5 chiffres qui forment des chaines de caractères) mais j'ai des nombres! Du coup, ma formule simple ne focntionne pas...

A la place, avec ce format de merde, je vais devoir rédiger une formule à la mord-moi-le-noeud... =NB.SI.ENS($A$2:$A$36743;">=" & H1 &"000";$A$2:$A$36743;"<" & H1+1 &"000")



Ton classeur fonctionnerait tout aussi bien (et ta liste déroulante basée sur DECALER aussi) en laissant tout en texte dans la colonne A de BD France, et en forçant le format de la cellule de saisie du cp en texte dans la colonne D du fichier client, et ce serait bien plus logique.

Imagine que par la suite, les données du fichier client soient injectées dans une base de données autre qu'Excel (le seul qui propose ce format spécial merdique)... Les valeurs injectées seront des valeurs numériques et donc, les CP des neufs premiers départements seront erronés.

Dernière chose, qui n'a rien à voir avec le format des cellules, mais il faut bien préciser à l'utilisateur que pour que la liste déroulante fonctionne bien, il est impératif que la DB France soit triée au moins sur la première colonne et que si le tri est effectué sur plusieurs colonnes, la colonne A doit être la première du tri.

Dernière chose que je répète encore une fois: Il est préférable de travailler avec des bases de données qui permettent aux formules de s'adapter à de nouvelles saisies.