Recherches formules pour extraire des termes d'une cellule

Bonsoir,

Pas beaucoup de temps car il est tard, je verrai demain soir.
Quand tu parles de date c'est seulement une année pas une date, on est d'accord ?

Il apparaît que les cellules en colonne A ( « Identifiant ») ne sont pas toutes sous le même type du fait des noms de feuilles qui possèdent souvent des « _ » entre les mots

là il n'y a que toi qui te comprend...

En ce qui concerne les échelles, certaines sont en M (million)

comment les comprendre ?
5M2=5200000 et 1M750=1750000 ?

Je remarque que des lignes A 108 à A 123, j’ai une série portant des numéros de feuille allant de 01 à 16.

Comment devrais-je faire pour les extraire si je voulais créer une colonne spécifique avec ces n°

une fonction ne voit pas ses voisines. On pourrait traiter ligne à ligne et extraire si la fin de chaine est numérique mais ça voudrait dire que CANARIES_6203 et ZARAGOZA-F4 seraient traitées à l'identique, pas sûr que ça t'intéresse.
Faire un sub qui traite la feuille plutôt que des fonctions permettrait plus de possibilités.
Le pb c'est que je pars en vacances en fin de semaine et je vais avoir d'autres choses à faire.
J'ai vu qu'il y avait des amateurs (=qui aiment, ils sont assez pro ) de PQ, ils pourront te concocter quelque chose aussi.

syntaxe 2
Je ne peux que répéter, je ne vois pas comment expliquer autrement.
Si tu veux 2 termes tu sélectionnes 2 cellules, tu écris la formule que tu valides en matricielle.
mais si tu n'y arrives pas ne te prend pas la tête, c'était juste un plus pas du tout indispensable ici.
eric

Bonjour,

Merci beaucoup pour votre réponse.

- Quand tu parles de date c'est seulement une année pas une date, on est d'accord ? Oui effectivement, c’est une année. Une date c’est plutôt 01/01/2000 ou 01-01-2000. Désolé, je ne m'exprime pas comme il faut.

Vous demandez des explications sur ma phrase " Il apparaît que les cellules en colonne A ( « Identifiant ») ne sont pas toutes sous le même type du fait des noms de feuilles qui possèdent souvent des « _ » entre les mot"

Je m’explique : les fichiers (identifiants, en colonne A) ne sont pas nommés de façon standardisée.

Le plus souvent, le nom de fichier est composée de :

1 - thème du document (ADM ; ECO ; POLIT ; URBA ….)

puis un « _ » faisant office de séparateur

2 - nom de feuille (le titre du document) ; lequel peut-être écrit de différentes façons :

ex : GRENADE-CORDOUE-JAEN (des « - » entre les mots composant de nom de feuille) ou bien

CASTELLON_DE_LA_PLANA (là, ce sont des « _ » qui se trouvent entre les mots composant le nom)

Cette dernière façon de procéder se rencontre moins dans ce qui a été nommé récemment mais il y a tout ce qui a été fait par le passé.

puis un « _ » faisant office de séparateur

3 - un nombre (qui représente une échelle exprimée généralement en K mais aussi en M)

A ce sujet, oui : 5M2=5200000 et 1M750=1750000 ; par conséquent 700K=700000

puis un « _ » faisant office de séparateur

4 - une année

Pour autant, il y a des exceptions à cette généralité.

Par exemple pour les lignes A 108 à A 123, il y a un élément en plus, ici un nombre qui signifie un numéro de feuille ; les noms de feuille étant par ailleurs identiques. Je suis conscient que l’extraction de ces nombres dans une colonne spécifique nécessite une formule complexe. Ne vous en faites pas ne la cherchez pas.

Vous évoquez Power Query. C’est effectivement un moyen et d’ailleurs j’insiste ici sur le fait que je ne refuse pas du tout l’aide de JFL . C’est surtout que je ne dispose pas à ma guise de l’ordinateur équipé d’Excel. De plus, je me demande si je serai autorisé à installer le fichier qui me sera indispensable (car j'utilise Excel 2010) sur un ordinateur dont je ne suis pas administrateur (ni la personne qui m’en donne occasionnellement l’accès). C’est un point que je dois d’abord vérifier.

Pour terminer : concernant la syntaxe 2 : je vais donc reprendre tout ça quand je serai devant Excel.

Encore un grand merci pour votre aide. Ne vous en faites pas pour les points qui demandent des formules complexes et donc du temps. Vous m’avez grandement facilité la tâche.

Bonne journée,

Thierry

Re,

Suite à mon message de ce matin qui vous apportait mes réponses à vos questions - voir plus haut - je reviens ici pour dire que j'ai eu accès ce matin à Excel.

Je me suis focalisé sur la syntaxe 2 et sans doute suis-je trop novice, je ne suis toujours pas arrivé à la faire fonctionner.

Voilà mes constatations et remarques sur ce sujet :

Donc, en colonne D je peux avoir ce que j’appelle « l’addition des éléments contenus en colonnes B et C ».

Si les colonnes B et C contiennent respectivement ARAGON et 700000 je devrais donc pouvoir obtenir en colonne D : ARAGON 700000 c’est bien cela ?

Je dois oublier de faire une action ou bien je m’y prends mal. J’ai essayé plusieurs possibilités et j’ai toujours uniquement, en colonne D l’élément de la colonne B.

Je me suis dis que peut-être est-ce dû au fait que l élément de la colonne C est un nombre. Alors j’ai créé une nouvelle colonne et j’ai mis un élément sous forme de mot. Cela ne change rien.

Pour sélectionner les cellules à prendre en compte, je place d’abord la souris sur B2, je la fais glisser vers C2, les deux cellules sont entourées d’un trait noir épais

et la C2 a un fond bleu ;

Il faut que j’écrive la formule =DECOUP(A2) dans la colonne « de destination », donc D2 ? C’est bien cela ? Mais alors B2 et C2 ne sont plus sélectionnées. Désolé je ne suis pas pro du tout.

Donc j’ai recommencé en sélectionnant B2,C2 et D2 et j’ai saisi la formule =DECOUP(A2) puis fait maj+ctrl+entrée ; et là j’obtinen l’année ! (qui se trouve en colonne E) et ensuite excel est bloqué !

Encore une fois, je ne dois pas être doué pour tout ça, surtout quand ça devient « pointu ».

Précision : les contenus des cellules B2 et C2 sont ont été convertis en valeurs.

J’ai l’impression d’être vraiment novice. J’aimerais bien comprendre le fonctionnement pour activer cette syntaxe 2.

D’avance, merci

Thierry

Bonjour,

CASTELLON_DE_LA_PLANA (là, ce sont des « _ » qui se trouvent entre les mots composant le nom)

là on ne peut rien faire, il faut normaliser ces noms auparavant en remplaçant les _

Matricielles.
Tu as 2 types de matricielles sur excel 
Celles qui travaillent avec une matrice (plage) en paramètre, les plus utilisées.
Celles qui ont besoin d'une plage pour retourner plusieurs résultats comme ici.
Comme dit elle n'est pas du tout indispensable ici. Prend ça à titre de curiosité et ne perd pas trop de temps dessus, peu de gens connaissent.

Sur ton dernier fichier sélectionne H2:I2, dans la barre de formule tu saisis =DECOUP(A2) que tu valides avec Shift.Ctrl+Entrée (les 3 touches en même temps).
Si la validation est correcte la formule s'encadre de { } pour indiquer que c'est une matricielle.
Tu obtiens 2 réponses sur une seule formule (comme la fonction excel DROITEREG() dont tu peut regarder l'aide) :

eric

suite...

ajouté :
- la fonction retourne des vrais nombres pour échelle et date.
la prochaine fois évite de mettre un alignement à gauche.
ça m'évitera de perdre du temps à chercher des erreurs qui n'existent pas. Sur excel les textes s'alignent à gauche et les nombres à droite...

- num feuille :
si la chaine après le dernier "_" du titre feuille peut être converti en numérique il est supprimé du titre feuille (il fallait le laisser ?) et peut être extrait en demandant le terme 0
Possible anomalie sur CANARIES_6203 par exemple
(cette fonctionnalité est incompatible avec la syntaxe matricielle que je ne vais pas faire évoluer pour ça, ça ne vaut pas le coup)

Reste à traiter 11K7 etc que je viens de voir.
= 11700 ?
eric

bonjour Thierry, Eriic, le fil,

on peut aussi faire tout dans une macro et envoyer le résultat vers une plage, cela demande moins de préparation

Sub Thierry2000()

     a = Range("A1").CurrentRegion.Columns(1).Value     '1ere colonne
     ReDim res(1 To UBound(a), 1 To 5)     'dimensioner array de sortie avec 5 éléments par donnée

     For i = 2 To UBound(a)     'boucle les données
          sp = Split(a(i, 1), "_")     'séparer sur "_"
          If UBound(sp) > 2 Then     'au minimum 3 morceaux
               res(i, 5) = sp(UBound(sp))     'dernier morceau

               s = sp(UBound(sp) - 1)     'avant-dernier morceau
               m = InStr(1, s, "M")     'Y-a-t-il un "M" dedans ?
               res(i, 3) = CDbl(Replace(Replace(s, "M", ",", , , vbTextCompare), "K", ",", , , vbTextCompare) & "0") * IIf(m > 0, 1000000#, 1000#)

               s = ""
               b = IsNumeric(sp(UBound(sp) - 2))     'le 3eme dernier est numerique ?
               If b Then res(i, 2) = sp(UBound(sp) - 2)     'ce chiffre
               For j = 1 To UBound(sp) - 2 + b     'reste des morceaux à partir du 2ieme
                    s = s & "_" & sp(j)
               Next
               res(i, 1) = Mid(s, 2)
          End If
     Next

     With Range("H1").Resize(UBound(res, 2))     'plage de sortie
          .EntireColumn.ClearContents     'effacer
          .Resize(UBound(res), UBound(res, 2)).Value = res     'écrire le résultat
          .Columns(3).NumberFormat = "#,##0;[Red]#,##0"
          .EntireColumn.AutoFit
     End With

End Sub

Bonjour,

oui, ça a été proposé précédemment :

Faire un sub qui traite la feuille plutôt que des fonctions permettrait plus de possibilités.

eric

@Eriic,

excuses, je n'ai pas suivi tout le fil.

Alors, même méthode et le résultat comme un matricielle ou des valeurs individuels.

Bonjour à tous !

Proposition Power Query amendée :

Bonjour à tous,

Eriiic, BsAlv et JFL, je vous remercie beaucoup pour vos recherches et vos messages.

J'ai eu accès à Excel ce matin, pour un temps limité et j'en suis resté à l'utilisation des fonctions VBA à l'aide des informations fournies par Eriiic. J'avais commencé avec cette façon et en raison des contraintes d'utilisation de l'ordi, j'ai continué là-dessus. J'ai bien vu que les codes figurant dans le module ont été modifiés et j'ai encore une fois pris conscience que tout ce langage est difficilement compréhensible pour un novice.

Le résultat me convient parfaitement. De toute façon, je sais que le fait de nommer différemment le fichier initial peut remettre en cause le résultat obtenu dans les colonnes. Quoiqu'il en soit j'ai une base solide pour effectuer ce que je veux faire.

Je n'oublie pas non plus BsAlv qui m'a fourni les infos d'une méthode identique permettant d'obtenir les résultats comme un matricielle ou des valeurs individuels.

Faute de temps et compte tenu du fait que je suis très prudent pour installer les fichiers nécessaires sur Excel 2010 pour utiliser Power Query - ce serait nettement mieux d'avoir moi-même Excel - je n'ai pas encore évoqué cette nécessité avec la personne qui me donne accès à Excel. Cela viendra sans aucun doute et je ne manquerai pas de tester les possibilités offertes par Power Query, et ce grâce à JFL.

Je suis bluffé par votre implication et vos compétences, chacun dans sa manière d'aborder les requêtes sous Excel.

J'ai vu aussi sur ce site, la palette infinie des questions posées, bien souvent très pointues et je reviendrai ici régulièrement car je peux y trouver de bonnes idées.

Pour finir sur une note souriante, grâce à vous, j'ai appris qu'il y avait une différence entre une année et une date. 1950 est bien une année dans une suite de caractères, pas une date. C'est là que je vois que j'ai du travail si je veux me perfectionner sur Excel.

Encore merci à tous et qui sait, peut-être à bientôt pour une nouvelle demande,

Thierry

Bonsoir à tous,

Je vous remercie de ce retour...

Et... bon courage pour la suite !