Discussion :

[alain16]

Bonjour,

J'ai des formules en colonne A et je souhaiterais extraire chaque terme dans les colonnes à coté.
Comment faire?
Merci par avance

[thekat]

Bonjour,

En VBA ou par formule ?

Code :

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

T = Range("A1").FormulaR1C1Local

T renvoie "=40+20-10,5-26" par exemple ...

[BENNASR]

TRÈS IMPORTANT : à tester sur une copie de ton fichier pour munir contre tous risques de pertes de données

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub test()
    With Sheets("Feuil2")
    .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Replace What:="+", Replacement:="|+", LookAt:=xlPart
    .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Replace What:="-", Replacement:="|-", LookAt:=xlPart
    For L = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
    .Cells(L, 1).TextToColumns Destination:=Range("B" & L), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Next L
    End With
End Sub

[alain16]

Merci BENNASR, mais ça ne fonctionne pas
j'essaye de chercher pourquoi
Cordialement

[BENNASR]

ça fonctionne chez moi
vérifier le nom de l'onglet, la position de tes lignes...

[alain16]

Merci BENNASR mais je crois qu'on ne s'est pas bien compris
Dans les cellules
AI: il y a 11 soit le résultat de la formule =15+12-16
et en
A2: il y a 27 soit le résultat de la formule =32+16+25-46

sur mon exemple A1= 108,3 soit la résultat de la formule =10+45,3-34+87

J'ai montré les formules sous forme de texte pour la compréhension , mais çà n'a pas marché...

Désolé et merci

[BENNASR]

je bricole ça :
(tjrs à tester avec prudence sur une copie

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Sub test()
Dim F As Worksheet
Dim derlig As Long
Set F = Sheets("Feuil1")
derlig = F.Range("A" & Rows.Count).End(xlUp).Row
    F.Cells(2, "B").FormulaR1C1 = "=f(RC[-1])"
    F.Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & derlig), Type:=xlFillDefault
    Selection.Copy
    F.Range("B2:B" & derlig).Copy
    F.Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
 
    F.Range("B2:B" & F.Range("B" & Rows.Count).End(xlUp).Row).Replace What:="+", Replacement:="|+", LookAt:=xlPart
    F.Range("B2:B" & F.Range("B" & Rows.Count).End(xlUp).Row).Replace What:="-", Replacement:="|-", LookAt:=xlPart
    For L = 2 To F.Cells(Rows.Count, 1).End(xlUp).Row
    F.Cells(L, 2).TextToColumns Destination:=Range("C" & L), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
     Next L
 
End Sub
Public Function F(Cellule)
Application.Volatile
F = Cellule.FormulaLocal
End Function

[Raccourcix]

Bonjour

une proposition par formule matricielle

Code :

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

=TRANSPOSE(FILTRE.XML("<A><B>"&SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(FORMULETEXTE(A1);"-";"|-");"=";"");"|";"</B><B>");"+";"</B><B>")&"</B></A>";"//B"))

FORMULETEXTE pour obtenir le contenu de la formule
4 SUBSTITUE pour remplacer = par rien, - par |-, puis + et | par </B><B> pour le FILTRE.XML
puis un FILTRE.XML pour "couper" le texte de la formule avec les + et les |
enfin TRANSPOSE pour mettre en ligne



Ca fonctionne avec Excel 365, à tester avec 2016

Stéphane

[alain16]

Merci beaucoup BENNASR
çà fonctionne mais la création de la colonne B avec les formules m'est inutile
Cordialement

Merci Stéphane
Je n'arrive pas à obtenir le même resultat
Je rentre la formule en C1
Je valide avec Ctrl+Maj+Entrée et j'obtiens une formule matricielle. J'ai le 1er résultat correct en C mais pas dans les autres colonnes.
Je manipule probablement mal les formules matricielles.
Cordialement

[thekat]

sur le principe énoncé plus haut :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
Sub decompositionTermesFormule()
 
T = Range("A1").FormulaR1C1Local
 
'supprime les valeurs décomposées
 Range("B1:E1").Select
  Selection.ClearContents
    Range("A1").Select
 
'nb de caractères de la fomule
lg = Len(T)
 
'init variables
textt = ""
 cpt = 1
 
'boucle de traitement des caractères de la formule
For i = 2 To lg + 1
 
C = Mid(T, i, 1)
 
 
  Select Case C
 
    Case 0 To 9
 
     'récupération des groupes de chiffres
      textt = textt + C
 
    Case Is = ",", "."
      'chiffre décimal
      textt = textt + C
 
    Case Is = "-"
 
      'chiffre négatif
 
        Select Case textt
 
          Case Is = ""
           'mémorisation du xxxxxx
 
            textt = textt + C
 
 
          Case Else
 
            cpt = cpt + 1
 
             ActiveSheet.Cells(1, cpt) = textt
 
            'mémorisation du signe négatif
              textt = C
 
 
        End Select
 
 
 
 
    Case Else
     'caractère autre que chiffre et symbole décimal
 
       cpt = cpt + 1
 
       ActiveSheet.Cells(1, cpt) = textt
 
       textt = ""
 
  End Select
 
'gère le traitement du dernier chiffre
If i = lg + 1 And textt <> "" Then ActiveSheet.Cells(1, cpt + 1) = textt
 
Next i
 
 
End Sub

reste à boucler sur le nombre de lignes à traiter

[Pierre Fauconnier]

Salut.

La solution de Stéphane ( au passage) ne fonctionne pas en 2016, car cette version ne connaît pas les plages dynamiques. La formule matricielle devrait être étendue sur le nombre de cellules correspondant au nombre de termes, ce qui en limite considérablement la portée, et elle devra être transformée (si c'est possible) pour récupérer chaque terme, car en l'état, elle renvoie le premier sur toutes les cellules.

Attention qu'elle est limitée, telle que rédigée, aux additions et soustractions (je ne sais pas si les exemples initiaux étaient représentatifs de cette limitation).

[Pierre Fauconnier]

On pourrait passer par une expression rationnelle.

Le code suivant considère les 4 opérations de base et place les membres avec l'opérateur qui les précède à droite de la cellule active.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test()
  Dim RegEx As Object
  Dim Matches As Object
  Dim i As Long
 
  Set RegEx = CreateObject("VBScript.RegExp")
  With RegEx
    .Pattern = "([+*/-]?\d+)"
    .Global = True
    Set Matches = .Execute(ActiveCell.Formula)
  End With
  For i = 1 To Matches.Count
    ActiveCell(1, i + 1) = Matches(i - 1)
  Next
End Sub

Explications.

Le modèle est le suivant: ([+*/-]?\d+) et la RegExp effectue une recherche globale et parcourt donc toute la chaine. Sans cela, elle s'arrête à la première occurrence rencontrée.

• Les parenthèses englobent le motif pour extraire chaque sous-chaine qui correspond au motif;
• Les crochets signifient que l'on recherche l'un des caractères précisés;
• Le ? derrière le crochet fermant précise que le caractère doit être présent 0 ou 1 fois. Cela permet de récupérer le premier membre lorsqu'il est positif (et donc sans + devant);
• \d précise n'importe quel chiffre;
• + précise que le chiffre doit être présent 1 ou plusieurs fois.

Si l'on traite des valeurs décimales, on devra adapter le pattern pour qu'il reprenne les nombres entiers et les nombres décimaux => ([+*/-]?\d+(?:\.?\d+)+)

La partie (?:\.?\d?)+ se lit ainsi:

• \.? signifie que l'on recherche le . (c'est un caractère spécial donc on l'échappe) 0 ou 1 fois
• \d+ signifie que l'on cherche un chiffre 1 ou plusieurs fois
• Le + final signifie que l'on cherche ce groupe 1 ou plusieurs fois
• ?: au début du groupe signifie que ce groupe n'est pas capturant, autrement dit qu'il ne se retrouvera pas dans les matches de la regexp

Les expressions rationnelles sont une manière élégante de séquencer un texte répondant à une syntaxe précise en évitant de construire la logique de la syntaxe par code. On remarque ici qu'il suffit de modifier le pattern pour séquencer différemment, sans toucher au reste du code...

[alain16]

Envoyé par thekat

Merci la Macro fonctionne trés bien
Cordialement

[alain16]

Pierre Fauconnier

Merci pour ces informations très détaillées et du temps pris pour expliquer mais j'ai une petite question
J'ai modifié le Pattern comme suit
J'ai besoin de traiter les décimaux donc j'ai pris:
([+*/-]?\d+(?:\.?\d+)+)
Je n'ai pas besoin de rajouter un + devant un nombre naturellement positif donc j'ai modifié ainsi
([*/-]?\d+(?:\.?\d+)+)
Je me suis aperçu ( sans bien comprendre pourquoi ) que lorsque le dernier nombre est à 1 chiffre à la fin,il est oublié
exemple:



en modifiant le Pattern comme suit ça fonctionne
([*/-]?(?:\.?\d+)+)

Donc tout va bien ..... sauf qu'à la sortie les chiffres décimaux sont affectés d'un point plutôt que d'une virgule
Ils ne sont donc pas reconnus comme des nombres
Bien sûr on peut rajouter du code pour remplacer le . par une virgule ( ou paramétrer Excel), mais pour rester dans l'élégance des expressions rationnelles ( que je ne connais pas bien) peut on les modifier pour obtenir le résultat souhaité?

Merci par avance et cordialement

[Pierre Fauconnier]

On pourrait traduire en nombre en multipliant chaque groupe trouvé par 1 (ne fonctionne alors que pour les additions et soustractions)...

Pour le remplacement des . par des ,, le plus simple selon moi de passer la chaine corrigée à la regexp. Il faut alors modifier le pattern pour ne plus retenir le point avec \., mais la virgule avec le simple caractère , => ([*/-]?(?:,?\d+)+)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test()
  Dim RegEx As Object
  Dim Matches As Object
  Dim i As Long
 
  Set RegEx = CreateObject("VBScript.RegExp")
  With RegEx
    .Pattern = "([*/-]?(?:,?\d+)+)"
    .Global = True
    Set Matches = .Execute(Replace(ActiveCell.Formula, ".", ",")) ' . vers ,
  End With
  For i = 1 To Matches.Count
    ActiveCell(1, i + 1) = Matches(i - 1) * 1 ' conversion en numérique
  Next
End Sub

[...]mais pour rester dans l'élégance des expressions rationnelles ( que je ne connais pas bien)[...]

Moi non plus ( et de plus, ton pattern est plus synthétique que le mien ), surtout que la bibliothèque utilisée (VBScript) les comprend à sa sauce (ce n'est pas du Microsoft pour rien) et que certaines spécificités sont oubliées ou modifiées... Les tutos sont rares et il faut pratiquer et encore pratiquer pour ancrer certains automatismes...

[alain16]

Merci beaucoup
çà fonctionne impeccable

Moi non plus

Mais si, mais si