Menu

Excel comprendre une fonction imbriquée. [Résolu]

Messages postés
47
Date d'inscription
lundi 3 mars 2008
Dernière intervention
10 novembre 2018
- - Dernière réponse : Alain 06
Messages postés
47
Date d'inscription
lundi 3 mars 2008
Dernière intervention
10 novembre 2018
- 12 oct. 2018 à 20:15
Bonjour, à tous.

Pourriez-vous m’éclairer sur cette formule, j’ai fait des recherches sur les différentes fonctions imbriquées, mais je sèche lamentablement.
Dans la ligne ci-dessous le résultat correspond au premier nombre du code postal.
J’aimerai comprendre qui fait quoi, le pourquoi et le comment écrire une telle fonction,
résultat retourné : 35.

Pour la petite histoire d’autres formules partent de ce résultat pour extraire des informations, tel que les deux premiers nombres du code postal ou le NOM de la ville, par exemple.
Et en dessous ce que j'ai trouvé.

57 RUE DE PONTOISE ETG 1 - DROITE 93240 STAINS
=SOMMEPROD(ESTNUM(STXT(SUBSTITUE(A3;" ";"|");LIGNE($1:$99);5)*1)*LIGNE($1:$99))

La fonction Excel SOMMEPROD est un outil puissant et très intéressant d’Excel.
La syntaxe de la formule ici sera :
=SOMMEPROD( (Critère 1)*(Critère2)*...*(Somme de la plage voulue))
La définition de l’aide de votre tableur favori est la suivante:
« Multiplie les valeurs correspondantes des matrices spécifiées et calcule la somme de ces produits. »

ESTNUM(): vérifier si une cellule renvoie un nombre
La fonction ESTNUM() renvoie vrai si l’argument valeur représente un nombre. ESTNUM() appartient aux fonctions d’information d'Excel.
La fonction ESTNUM suit la syntaxe suivante :
=ESTNUM(argument1) où : argument1 est la cellule qui contient la valeur à vérifier

STXT() - Extraire une chaîne de caractères au sein d'une autre
STXT(texte; numéro de départ; nombre de caractères)
• texte représente le texte ou la référence à une cellule contenant le texte à extraire,
• numéro de départ est la position dans le texte du premier caractère à extraire,
• nombre de caractères indique le nombre de caractères à extraire.

SUBSTITUE() - Remplacer une chaîne de texte par une autre
SUBSTITUE(texte; ancien texte; nouveau texte; position)
• texte
• représente le texte ou la référence à une cellule cellule dont on veut remplacer certains caractères,
• ancien texte est le texte à remplacer,
• nouveau texte est le texte qui doit remplacer l'ancien,
• position est un argument facultatif, il indique quelle occurrence de l'ancien texte on souhaite remplacer par
• le nouveau texte. Si cet argument n'est pas indiqué, toutes les occurrences de l'ancien texte sont remplacées par le nouveau texte, sinon seule l'occurrence indiquée est remplacée.
• Les arguments de la fonction peuvent être des références à des cellules, des formules, ou du texte saisi. En cas de saisie, chiffres ou textes, les arguments doivent être notés entre guillemets.
• La fonction SUBSTITUE() est sensible à la casse :

=LIGNE (référence )
La fonction ligne est similaire à la fonction colonne; la seule différence étant qu’elle renvoie le numéro de ligne d’une référence plutôt que le numéro de colonne.
Cette fonction est également assez simple et ne comporte qu’un seul argument.
Sa structure est la suivante : =LIGNE (référence )
Où "référence" est la cellule de laquelle on cherche le numéro de ligne.
Ici, contrairement à la fonction colonne, le résultat de la fonction donnera le numéro de ligne réel indiqué à gauche sur les numéros de lignes.



Afficher la suite 

Votre réponse

4 réponses

Meilleure réponse
Messages postés
1849
Date d'inscription
lundi 6 mai 2013
Dernière intervention
14 décembre 2018
1
Merci
Bonjour,
Pourquoi 35? c'est la position qu'occupe le premier caractère du code postal dans l'adresse complète.

Comment fonctionne la formule? Pour bien la comprendre, il faut la décortiquer.
1)- SUBSTITUE(A3;" ";"|"), remplace tous les "espaces" par des "|", ceci à son importance pour la suite.

2)-STXT(SUBSTITUE(A4;" ";"|");LIGNE($1:$99);5) extrait les 5 caractères, à partir d'un numéro de départ donné, ici , ce N° de départ est remplacé par LIGNE($1:$99), Cela veut dire que SOMMEPROD va tester un groupe de 5 caractères en décalant le premier caractère de l'adresse complète en remplacement le N° de départ par 1 puis 2 puis 3 jusqu'à 99.

Pour bien comprendre le raisonnement, écrivez la formule suivante
=STXT(SUBSTITUE(A3;" ";"|");LIGNE($1:$99);5),
et remplacez successivement la fonction LIGNE($1:$99) par 1, puis 2 puis 3 etc et observez.

3)- ESTNUM(STXT(SUBSTITUE(A3;" ";"|");LIGNE($1:$99);5)*1), le résultat précédent étant du texte(Stxt), on le multiplie par 1 pour le convertir en numérique. Si le résultat obtenu après multiplication est toujours du texte , alors la fonction ESTNUM renvoie "FAUX" sinon "VRAI".

4)- =SOMMEPROD(ESTNUM(STXT(SUBSTITUE(A3;" ";"|");LIGNE($1:$99);5)*1)*LIGNE($1:$99)) , SOMMEPROD va faire un balayage de 1 à 99 caractères et si ESTNUM renvoie "VRAI", c'est qu'il à trouvé une partie du texte qui est entièrement numérique, il s'agit donc du code postal. Sommeprod renvoie la position du premier caractère du code postal, dans le cas présent , il est en 35ème position.

Voilà, j'espère avoir pu vous aider.

Cdlt

Dire « Merci » 1

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez CommentCaMarche

CCM a aidé 35283 internautes ce mois-ci

Commenter la réponse de Frenchie83
Messages postés
1849
Date d'inscription
lundi 6 mai 2013
Dernière intervention
14 décembre 2018
1
Merci
Bonsoir,
SUBSTITUE(A3;" ";"|"),quelle est l'importance de cette barre verticale à la place des espaces, il pourrait tout aussi bien y avoir un autre signe, non, et pourquoi remplacer les espaces le comptage est plus facile?
Mettre un autre signe autre qu'une barre verticale, OUI à condition que ce soit un signe jamais utilisé dans une adresse, par exemple: £, µ, §,....
Pourquoi remplacer les espaces: tout simplement parce que ce que vous voyez et croyez être un espace(code 32) n'en n'est pas forcément un, surtout si l'adresse est récupérée sur une autre application, l'espace est souvent un caractère insécable (code160)
Pour comprendre, dans une cellule écrivez:
=REPT(CAR(32);10)&"A" en A1
en dessous, écrivez:
=REPT(CAR(160);10)&"A" en A2
en apparence les 2 cellules sont identiques
A côté des 2 cellules précédentes écrivez ceci
=SUPPRESPACE(A1) en B1
=SUPPRESPACE(A2) en B2
Voyez-vous mieux la différence? Voilà pourquoi il est nécessaire de remplacer les espaces.

Voilà
Cdlt

Dire « Merci » 1

Heureux de vous avoir aidé ! Vous nous appréciez ? Donnez votre avis sur nous ! Evaluez CommentCaMarche

CCM a aidé 35283 internautes ce mois-ci

Commenter la réponse de Frenchie83
Messages postés
47
Date d'inscription
lundi 3 mars 2008
Dernière intervention
10 novembre 2018
0
Merci
Bonjour voisin.
Un grand merci pour votre réponse, mon blocage venait de la fonction ligne et de la recherche de 5 caractères successifs, car sur quelques lignes figure le N° de la voie.
SUBSTITUE(A3;" ";"|"), quelle est l'importance de cette barre verticale à la place des espaces, il pourrait tout aussi bien y avoir un autre signe, non, et pourquoi remplacer les espaces le comptage est plus facile?
Je remarque qu'il me reste beaucoup de chemin à faire pour comprendre Excel.
Encore un grand MERCI.
Cordialement.
Commenter la réponse de Alain 06
Messages postés
47
Date d'inscription
lundi 3 mars 2008
Dernière intervention
10 novembre 2018
0
Merci
Bonsoir et encore un très grand MERCI pour votre réponse et du temps que vous m'avez consacré.
Cordialement.
Commenter la réponse de Alain 06