Autres Code Excel VBA modification

[bthw33]

Bonjour,

Novice en Excel j'ai récupérer un fichier sur le forum que je trouve magique !

J'ai un à peine retravailler celui-ci car il est presque parfait.

Je bloque sur un point, quand je clique sur les points rouges les chiffres apparaissent, mais moi je souhaite que les chiffres soit directement inscrit à coté des points rouges comment faire ?

Merci d'avance, et merci au créateur du fichier.

 

[AtTheOne]

Re,
En regardant ton fichier je me suis rendu compte que ce n'était pas tout à fait l'attendu.
J'ai créé deux noms :

_TCTIERS	=DECALER(Liste!$A$1;1;0;NBVAL(Liste!$A:$A)-1;1)
_TCVILL	=DECALER(Liste!$B$1;1;0;NBVAL(Liste!$B:$B)-1;1)

Et la formule dans la colonne F de ta feuille "DATA" devient :

=NB.SI.ENS(_TCTIERS;$A2;_TCVILL;"*"&DROITE(B2;4))

Je pense que ça correspond plus à ta demande (Attention à l'orthographe des noms de ville dans ton fichier il y a par exemple ANDERNOS-LES-BAINS d'un coté et ANDERNOS LES BAINS de l'autre)
Amicalement
Alain

 

[bthw33]

Bonjour,

Merci c'est pas mal !
Mais du coup si la ville n'est pas tapée comme à l'identique cela ne fonctionne pas.
Peut-on ce basé sur un nombre de caractères minimales identiques par exemple 5 caractères d'affilés identique pour valider?
Exemple pour ANDERNOS-LES-BAINS : si je met le bon code postal, mais que dans la ville je met seulement Andernos ou bien bains cela pourrait valider?
Je ne sais pas si je suis très clair?

Merci d'avance,

 

[AtTheOne]

Re,

Mais du coup si la ville n'est pas tapée comme à l'identique cela ne fonctionne pas.
Peut-on ce basé sur un nombre de caractères minimales identiques par exemple 5 caractères d'affilés identique pour valider?

Si : La formule donnée :=NB.SI.ENS(_TCTIERS;$A2;_TCVILL;"*"&DROITE(B2;4)), vérifie que les villes de la feuille Liste se terminent par les 4 caractères de droite de la cellule B2.
Pour ANDERNOS-LES-BAINS on vérifie Que les villes dans la feuille Liste se terminent par AINS, c'est ce que signifie "*"&DROITE(B2;4).
Mais ça ne fonctionnerait pas si l'on prenait les 6 caractères de droite car "*&DROITE(B2;6) vérifierait que la ville dans la feuille liste se termine par les 6 caractères "-BAINS" or on a " BAINS" et là cela ne correspond pas.

Amicalement
Alain

 

[bthw33]

Re,

Effectivement je comprends le principe, je n'avais pas vu ton deuxième message je vais tester.
Et peut-on mettre une condition par exemple soit les 4 caractères de droite ou les 4 caractères de gauche?

Merci d'avance,

 

[AtTheOne]

Re re !

peut-on mettre une condition par exemple soit les 4 caractères de droite ou les 4 caractères de gauche?

Oui mais on doit changer de fonction et utiliser SOMMEPROD.

Avant tout j'ai créé un autre nom pour contenir le nombre de caractères vérifiés à gauche ou à droite (_NbV) on peut modifier sa valeur dans la feuille Table.

Voici la liste des noms du classeur, il y avait des noms pré-existants :

Nombre de caractères à vérifier (voir feuille Tables)	_NbV	=Tables!$A$2
Liste des CP de la feuille Liste	_TCTIERS	=DECALER(Liste!$A$1;1;0;NBVAL(Liste!$A:$A)-1;1)
Liste des villes de la feuille Liste	_TCVILL	=DECALER(Liste!$B$1;1;0;NBVAL(Liste!$B:$B)-1;1)
Plage contenant les noms de zone feuille DATA	_Zone	=DECALER(Data!$G$1;1;0;NBVAL(villes);1)
Tableau contenant la correspondance Zone-Couleur feuille Tables	_LoCouleurs	(Tableau Structuré)
Liste pour le choix de la zone dans la feuille Data (Validation de données)	_Chx_Zone	=DECALER(_LoCouleurs;0;1;;1)
Pré-Existant liste des villes feuille Data	villes	=DECALER(Data!$B$1;1;;NBVAL(Data!$B:$B)-1)
Pré-Existant liste des CP feuille DATA	cp	=DECALER(villes;;-1)
Pré-Existant Liste existant en colonne W feuille Data	Liste	=Data!$W$2:$W$28

La formule NB.SI.ENS(...) en colonne F de la feuille Data devient :

=SOMMEPROD((_TCTIERS=TEXTE($A2;"00000"))*
          (((DROITE(_TCVILL;_NbV)=DROITE($B2;_NbV))+(GAUCHE(_TCVILL;_NbV)=GAUCHE($B2;_NbV)))>0))

Cela mérite une explication :

• La multiplication équivaut à un ET logique, l'addition à un OU.
• (_TCTIERS=TEXTE($A2;"00000")) je convertis $A2 en texte car dans ta feuille Liste les CP sont sous forme de Texte (ce qui est courant, voir la Corse ou les 9 premiers départements)
  On vérifie si chaque ligne de _TCTIERS correspond à la cellule A de la ligne courante, cela va donner un tableau de VRAI et de FAUX convertis lors de la multiplication en un tableau de 1 et de 0.
• (((DROITE(_TCVILL;_NbV)=DROITE($B2;_NbV))+(GAUCHE(_TCVILL;_NbV)=GAUCHE($B2;_NbV))))>0) On regarde si on a une égalité à gauche ou à droite entre les _NbV caractères de _TCVILL et la Cellule B de la ligne courante,
  le >0 vérifie qu'au moins une des 2 conditions est vérifiée. On obtient un tableau de VRAI et de FAUX converti lors de la multiplication en un tableau de 1 et de 0.
• On multiplie ces deux tableaux membre à membre et on en fait la somme (par SOMMEPROD)

Le résultat est le nombre de lignes de ta feuille liste pour lesquelles on à une correspondance exacte des codes postaux et une correspondance à droite ou/et à gauche des _NbV caractères des noms de ville.

J'en profite pour te transmettre une version avec l'utilisation du nom des zones.
Si tu dois ajouter une zone il suffit de le faire dans la feuille Tables, en commençant par la couleur puis en ajoutant le nom de la zone (il suffit de taper le nom de la nouvelle zone juste sous le tableau, il s'étend automatiquement).
Une macro prend en charge l'affichage du N° de la couleur.
Ensuite tu affectes le nom de zone à tes villes de la feuille DATA, une macro prend en charge la mise en couleur du nom de la ville.
Cela évite les erreurs de couleur (précédemment j'en ai corrigé 2, je m'en suis aperçu car le repère de 2 zones n'était pas centré sur la surface attendue et en plus, il y avait également 2 erreurs dans des nuances de vert)
Je pense que ce n'est pas plus long que de modifier ou d'affecter une nouvelle couleur.
Mais c'est à toi de voir.

Amicalement
Alain

 

[bthw33]

Merci pour toutes les explications ! cela permet de comprendre.
Désolé de vous faire travailler pour rien à chaque fois... Mais mon problème toujours pas résolu..
Car pour toutes les villes ou il y a médoc cela ne fonctionne pas correctement..
Je ne vois vraiment pas de solution pour ce problème..
Merci encore pour vos propositions et explications.

 

[AtTheOne]

Bonsoir à toutes & à tous, bonsoir @bthw33

Car pour toutes les villes ou il y a médoc cela ne fonctionne pas correctement..

Ennuyeux ce cru !
Bon on peut passer le nombre de caractères à 8, mais cela risque de rejeter les noms de ville courts contenant un espace ...
J'ai opté pour une normalisation des noms de ville contenus dans CTVILL :
Remplacer "ST " par "SAINT-" et "STE " par "SAINTE-" et remplacer les espaces au-delà du 4ème caractère par des "-" (au-delà du 4ème pour préserver l'espace qui suit les articles en début de nom).
Ensuite on fait la comparaison entre le contenu complet de la cellule B et ces noms de ville normalisés.
Pour rendre plus lisible la formule dans la feuille Date j'ai encore ajouté un Nom défini (Oui j'aime ça !) :

Noms de ville normalisés

_TCVILL_Norm

=SI(GAUCHE(_TCVILL;3)="ST ";"SAINT-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-3);" ";"-"); SI(GAUCHE(_TCVILL;4)="STE ";"SAINTE-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-"); GAUCHE(_TCVILL;4)&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-")))

Voilà, la formule en F2 devient :
=SOMMEPROD((_TCTIERS=TEXTE($A2;"00000"))*(_TCVILL_Norm=$B2))

Amicalement
Alain
PS s'il y a d'autres règles que je n'ai pas vu pour la normalisation, fais moi signe

 

[bthw33]

Re,

C'est presque bon sur tout !

Ce qui n'a pas marché :

33260	CAZAUX
33260	CAZAUX

33970	CAP FERRET
33970	CAP FERRET
33970	CAP FERRET

Mais c'est deux ville c'est normal elles ne sont pas sur la carte donc je vais tenter de les rajouter sur la carte.

33127	ST JEAN D ILLAC
33127	ST JEAN D ILLAC

33260	LA TESTE
33340	LESPARRE
33340	LESPARRE
33340	LESPARRE
33340	ST GERMAIN D ESTEUIL
33590	GRAYAN ET L HOPITAL
33680	LACANAU OCEAN

Et la les 6 villes suivantes ne fonctionne pas...
Histoire de ' pour certaines, et pour d'autre mot en plus ou en moins...
C'est possible d'avoir un endroit ou on voit ce qui n'a pas pu être trouvé?

Merci d'avance,

 

[bthw33]

Je n'arrive pas a trouver ton plan de normalisation ? (pour pouvoir analyser et essayer de comprendre ahah)
Quand je rajoute des données dans la feuille liste, cela ne fonctionne pas, j'ai bien vu que tu avais convertis les nombres en texte, j'ai essayé de les convertir aussi en texte mais que cela ne fonctionne pas... Qu'est que je fais de mal?

Merci d'avance,

 

[AtTheOne]

Bonne Nuit !!
Dans la version jointe le problème des apostrophes est résolu en modifiant le nom _TCVILL_Norm :

_TCVILL_Norm

=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SI(GAUCHE(_TCVILL;3)="ST ";"SAINT-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-3);" ";"-");SI(GAUCHE(_TCVILL;4)="STE ";"SAINTE-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-");GAUCHE(_TCVILL;4)&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-")));"-D-";"-D'");"-L-";"-L'");"D ";"D'");"L ";"L'")

Il s'agit de remplacer les "L " et les "D " par des L' et D' (en tête) et les "-L " et les "-D "par des -L' et des -D' en milieu de nom.


Restent les noms qui ne correspondent pas :

TCTIER	TCVILL	Nom approchant
33970	CAP FERRET	Aucun
33260	CAZAUX	Aucun
33260	LA TESTE	LA TESTE-DE-BUCH
33680	LACANAU OCEAN	LACANAU
33340	LESPARRE	LESPARRE-MEDOC
33115	PYLA SUR MER	Aucun

LA TESTE, LACANAU OCEAN, ET LESPARRE pourraient matcher si de nouveau on ne considérait que les 5 premiers caractères

J'ai mis un format conditionnel dans le tableau de la feuille Liste pour faire apparaître les noms qui ne matchent pas. C'est couteux en temps de calcul, supprime le dès que tu n'en auras plus besoin.

Par curiosité, où te procures-tu les coordonnées des communes ?

Amicalement
Alain

MODIF : J'étais vraiment fatigué hier, j'ai oublié de cliquer sur la case Répondre ! Bon du coup je prend en compte ton dernier post dans un instant.

 

[bthw33]

ah super merci, mais je ne trouve pas le normage ?
merci d'avance

 

[AtTheOne]

Bonsoir à toutes et à tous, bonsoir @bthw33

Quand je rajoute des données dans la feuille liste, cela ne fonctionne pas, j'ai bien vu que tu avais convertis les nombres en texte, j'ai essayé de les convertir aussi en texte mais que cela ne fonctionne pas... Qu'est que je fais de mal?

En fait dans la feuille "Data" les codes postaux sont sous forme de nombre (choisis l'alignement standard, ils sont cadrés à droite)
Dans la feuille Liste les codes postaux sont enregistrés sous forme de chaînes de caractères (choisis alignement standard, ils sont cadrés à gauche). Moi ça ne me choque pas car ils y a des CP alphanumériques pour la corse, et pour avoir les 01xxx etc il faut soit jouer avec un format de nombre "00000" ou les avoir sous forme de texte.

J'ai pris le parti de ne pas modifier tes données, ne sachant pas comment tu les récupères.
Mais pour la comparaison il faut, soit que les 2 soient des textes, soit que les deux soient des nombres. Vu les formules utilisées j'ai opté pour les textes.
Donc si tu tu saisis manuellement des CP dans ta feuille liste avant de le saisir, choisis le format "Texte" ("@"),
Excel te met un petit triangle vert en haut à gauche de la cellule avec l'indication "Nombre stocké sous forme de texte".

Maintenant si tu ne ré-importes pas tes données d'une source extérieure, tu peux les convertir en nombres facilement : Sélectionne les textes à convertir, tu as un petit panneau "Danger", clique dessus et choisis convertir en nombre :

Si tu fais cela, il faudra changer la formule qui convertit les CP de la feuille Data en texte et remplacer TEXTE($A2;"00000") par $A2.

Voilà pour CP.

Je n'arrive pas a trouver ton plan de normalisation ?

Là j'ai un peu analysé tes deux listes de noms de ville
Dans la liste "Data"

• Il n'y a pas d'espace excepté après un article situé en tête de nom (LE QUELQUE-CHOSE-LES-DEUX-RIVIERES) donc il faut conserver les "LE ", "LA ", "LES " en tête de nom (je ne pense pas que l'on ait des articles indéfinis, auquel cas il faudrait aviser)
• Les mots SAINT ET SAINTE apparaissent en toutes lettres.
• Les Apostrophes existent

Dans la liste "Liste"

• Il y a des espaces entre les mots
• Les abréviations ST et STE sont utilisées à la place de SAINT et SAINTE
• Les apostrophes sont remplacées par des espaces.

Dans un premier temps je traite les "ST " en tête que je remplace par "SAINT-" et je complète en remplaçant dans le reste du nom tous les espace par des "-"
SI(GAUCHE(_TCVILL;3)="ST ";"SAINT-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-3);" ";"-");

Si les 3 premières lettres sont différentes de "ST " je traite les "STE " de tête que je remplace par "SAINTE-" et que je complète en remplaçant dans le reste du nom les espaces par des "-".
SI(GAUCHE(_TCVILL;4)="STE ";"SAINTE-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-");

Si je ne suis pas dans un des 2 cas précédents, je remplace à partir du 5 ème caractères tous espaces par des "-" :
GAUCHE(_TCVILL;4)&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-")
(le GAUCHE(_TCVILL;4) se justifie car je veux garder les "LES " en début de nom)

A partir de ce nom transformé je remplace les "-D-" par des "-D'" (cas des D apostrophe en milieu de nom)
je remplace les "-L-" par des "-L'" (cas des L apostrophe en milieu de nom)
je remplace les "D " par des "D'" (cas des D apostrophe en début de nom)
je remplace les "L " par des "L'" (cas des L apostrophe en début de nom)

Voilà, en écrivant cela je me rends compte que je ne traite pas les ST et STE en milieu de nom !!!
Je pense qu'il faut peut-être faire l'inverse (dé-Normer les noms de la feuille Data)

Mais ré-importes-tu souvent des données, car la transformation serait plus simple par macro après chaque importation.

MODIF : J'étais vraiment fatigué hier, j'ai oublié de cliquer sur la case Répondre ! Bon du coup je prend en compte ton dernier post dans un instant.

mais je ne trouve pas le normage ?

Bon je ne pédale pas très vite, le post #25 date en fait d'hier (je me suis déconnecté en oubliant de le poster
Et ce post est en gestation depuis 17h15 mais j'ai fait une longue interruption pour d'autres activités entre temps. Alors un peu de patience STP !

Amicalement
Alain

 

[bthw33]

Re,
Oh la loin de moi l'idée que tu ne vas pas assez vite ou quoi que ce soit !
C'est déjà super sympas de m'expliquer.
Mais enfaite je ne comprends pas ton normage, fin je comprends ce que tu fais, mais dans le fichier je ne le retrouves nul pars c'est ça que je ne comprends pas?
Oui enfaite le but c'est que le fichier soit vivant, très souvent des données vont être mise dans la feuille liste, c'est pour sa que j'essaye de trouver ton normage dans le fichier pour essayer de tester et bien comprendre ahah

Merci encore

 

[AtTheOne]

Re,
Il est dans le nom "_TCVILL_Norm" qui contient la formule (voir gestionnaire de noms)

Pour rendre plus lisible la formule dans la feuille Date j'ai encore ajouté un Nom défini (Oui j'aime ça !) :

Noms de ville normalisés

_TCVILL_Norm

=SI(GAUCHE(_TCVILL;3)="ST ";"SAINT-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-3);" ";"-"); SI(GAUCHE(_TCVILL;4)="STE ";"SAINTE-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-"); GAUCHE(_TCVILL;4)&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-")))

dans la version jointe le problème des apostrophes est résolu en modifiant le nom _TCVILL_Norm :

_TCVILL_Norm

=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SI(GAUCHE(_TCVILL;3)="ST ";"SAINT-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-3);" ";"-");SI(GAUCHE(_TCVILL;4)="STE ";"SAINTE-"&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-");GAUCHE(_TCVILL;4)&SUBSTITUE(DROITE(_TCVILL;NBCAR(_TCVILL)-4);" ";"-")));"-D-";"-D'");"-L-";"-L'");"D ";"D'");"L ";"L'")

Amicalement Alain

 

[bthw33]

Bonjour,
Je viens de relire tes messages, merci encore !!!
Peut-tu me joindre le fichier stp? pour que je puisse avoir comme dans un tes dernier message :
J'ai mis un format conditionnel dans le tableau de la feuille Liste pour faire apparaître les noms qui ne matchent pas. C'est couteux en temps de calcul, supprime le dès que tu n'en auras plus besoin.
merci d'avance