Monsieur Excel
Pour tout savoir faire sur Excel !

16 septembre 2013

Isolement de partie numérique

Nous recevons un fichier CSV contenant une liste de textes (20 caractères au maximum) suivis d’une valeur, comme dans la colonne A dans la copie d’écran ci-dessous.

  
L’objectif est d’isoler les textes et les valeurs dans deux colonnes différentes.

La formule de B1, proposée par un internaute, était la suivante :
=stxt(A1;equiv(vrai;estnum(1*stxt(A1;ligne($1:$20);1));0);nbval(1*stxt(A1;ligne($1:$20);1)))

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Décortiquons cette formule :

stxt(A1;ligne($1:$20);1) est évalué en {"A";"v";"i";"o";"n";"2";"3";"4";"5";",";"6";"7";"";"";"";"";"";"";"";""}

ESTNUM(1*{"A";"v";"i";"o";"n";"2";"3";"4";"5";",";"6";"7";"";"";"";"";"";"";"";""}) est évalué en : {FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;VRAI;
FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX}

equiv(vrai;{FAUX;FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;FAUX;VRAI;VRAI;
FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX;FAUX};0) est évalué en 6, c’est-à-dire la position du premier chiffre.

Et enfin, nbval(1*stxt(A1;ligne($1:$20);1)) est évalué en 20, ce qui nous donne enfin =stxt(A1;6;20) avec le résultat espéré.

Remarque – Une fois de plus, nous constatons que les formules matricielles permettent d’obtenir en une formule unique un traitement algorithmique.

La dernière étape est un peu lourde, et l’on peut écrire la formule globale suivante, qui est plus courte :
=1*droite(A1;nbcar(A1)-equiv(vrai;estnum(1*stxt(A1;ligne($1:$20);1));0)+1)

La formule de C1 ne pose pas de problème : =gauche(A1;nbcar(A1)-nbcar(B1))

rédigé par Hervé Thiriez | 4 commentaire(s)

4 Commentaire(s):

  • Bonjour,

    Remarque 1 : Le nombre de caractère étant limité à 20 dans votre exemple, ne pourrions-nous pas simplifier la formule en remplaçant directement le dernier membre NBVAL(1*STXT(A1;LIGNE($1:$20);1)) par 20.

    Remarque 2 : Les nombres de la colonne B sont extraits en texte. Nous pourrions les avoir directement en nombres en ajoutant la fonction CNUM.
    CNUM(STXT(A1;EQUIV(VRAI;ESTNUM(1*STXT(A1;LIGNE($1:$20);1));0);20))

    By Anonymous Gonzague, sur 11:00 AM  

  • J'ai essayé un traitement en masse (500 000 lignes !) avec les 2 formules proposées. Lorsqu'on change une valeur de la colonne A, le recalcul prend 43s sur ma machine.

    J'ai essayé une solution "bestiale" :
    - en A1, j'ai imbriqué 11 fonctions SUBSTITUE (les 10 chiffres+la virgule), tout ceci SANS notation matricielle.
    =SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A1;"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"");",";"")
    - en B1 j'ai mis
    =CNUM(SUBSTITUE(A1;B1;""))
    Et là lorsque je change une valeur en A1, la mise à jour est instantanée.

    By Anonymous cduigou, sur 1:06 PM  

  • Excusez-moi, dans mon post précédent il fallait lire :
    - en B1, j'ai imbriqué... (au lieu de en A1)

    - en C1 j'ai mis... (au lieu de en B1 j'ai mis)

    By Anonymous cduigou, sur 2:30 PM  

  • Certes, quand on prend en compte le temps de calcul, des formules lourdes se calculent parfois bien plus vite que d'autres formules bien plus légères.

    En règle générale, les formules matricielles prennent plus de temps de calcul que les formules normales.

    La façon la plus lourde, en temps de calcul, est de passer par des tables d'hypothèses.

    Ceci dit, on peut, avec des formules matricielles ou des tables d'hypothèses, résoudre des problèmes qui ne trouveraient pas d'autre solution...

    By Blogger Hervé Thiriez, sur 11:27 AM  

Enregistrer un commentaire

<< Accueil