Problèmes de formats de cellules (je pense)
Bonjour,
j'ai fait un fichier avec deux onglets :
- un onglet "Import CRCA" : l'import de données provenant d'une banque (mal fichues au départ que je dois mettre dans des formats utilisables)
- un onglet "Données rangées" : avec les données dans le bon format et placés dans les bonnes colonnes (texte pour tout sauf H:I:J qui deviendront des formats monétaires)
J'ai deux macros accessibles depuis le premier onglet.
Si je lance la première macro (carré bleu avec "Transpose") les données se trient mais on voit que la cellule A2 n'est pas dans un format "texte" comme les autres de la colonne A.
Puis si je lance la macro (carré bleu avec "Vers Données rangées") les données se positionnent dans l'onglet "Données rangées", mais il y a deux problème issus je pense d'un mauvais format dans les deux cas :
- en colonne A, les remplacements de "septembre" par "/12/" (par exemple) ne se font pas.
- en colonne H, la cellule H2 détecte un défaut de format de J2.
Comment "obliger" les données de la colonne A et de la colonne D du premier onglet à prendre des formats "Texte" uniformes qui seront traités
de façon identique avec les macros ?
Columns("A:A").Select
Selection.NumberFormat = "@"
ou
Columns("A:A").Select
Selection.NumberFormat = "[$-fr-FR]d mmmm yyyy;@"
Ne semblent pas fonctionner.
Merci.
Christophe
Bonjour,
C'est quoi que tu as importé du xls, du csv.
Ya aucune référence de liaison donc on peut supposer que tu as importer ça à la va comme jte pousse ?
Ya surement une meilleure méthode d'importation.
Tu as essayé Données > Nouvelle requête > A partir d'un fichier... A partir de là il faut te lancer dans l'aventure !
A+
Bonjour à tous
Ne pas confondre Format qui ne fait qu'habiller et Type de donnée : l'habit ne fait pas le moine
Cela fait des années qu'on utilise PowerQuery, intégré à Excel, pour importer et traiter ce type de fichiers
En partant de ton Excel on obtient cela assez facilement
mais en général il est plus efficace de partir directement du fichier, csv la plupart du temps
Re-bonjour,
Bon, j'ai tourné en rond, je reviens vers vous.
Galopin et 78chris, je suppose que vous me parlez tous les deux de Querry. Je ne connaissais pas mais j'ai regardé des tutos et ai fait des essais.
En fait, j'arrive au même problème, querry me trouve des erreurs sur certaines cellules. Je pense que c'est surtout à cause de celles dont le nombre au format texte est supérieur à 999 car à partir de 1 000 il y a un espace dans l'expression.
Si vous voulez tester mon pb que j'essaie de régler en VBA :
- mettez vous sur l'onglet import_crca
- lancez la macro "transpose" qui récupère les données de la colonne A et qui après les avoir transposé, efface les lignes vides (on voit dans la colonne F que le résultat multiplié par 1 n'est pas bon). (Nota : les valeurs de la colonne A sont issues d'un fichier Excel exporté à partir du site de la banque)
Ce sont les valeurs en colonne "D" qui m'ennuient. Ce sont des formats nombres au format TEXTE.
- lancez la macro "Test" qui est un essai pour enlever les espaces de ces nombres. En fait, j'ai adapté la macro Sub Mat() qui supprime les espaces d'un texte pour l'intégrer dans ma macro sub test() mais là malheureusement, les espaces ne sont pas enlevés, ils résistent.
J'ai donc toujours ce problème récurrent d'espace qui m'empêche d'avancer.
Un coup de main ne serait pas de refus, je bloque !
merci d'avance
Christophe
Bonjour à tous
Galopin et 78chris, je suppose que vous me parlez tous les deux de Querry. Je ne connaissais pas mais j'ai regardé des tutos et ai fait des essais.En fait, j'arrive au même problème, querry me trouve des erreurs sur certaines cellules.
Ce qu'on attend c'est le fichier source (l'export)
Mon tableau montre que cela fonctionne il me semble...
Il faut juste apprendre un minimum à l'utiliser mais tu sembles focalisé sur VBA... qui ne marche pas mieux dans tes essais...
Bonjour à tous,
Chris,
Je me suis "cassé" les dents pour pivoter les lignes sur 4 colonnes. J'ai voulu reproduire la formule
=INDIRECT("A"&LIGNE()*4-9)Comme dans ce vidage d'écran, mais je n'ai pas su faire l'équivalent sur Power Query.
Comment as-tu fait ?
Quelles étapes as-tu utilisées ?
J'ai essayé d'utiliser la méthode Fonction personnalisée en pensant naïvement que je pourrais coller ma fonction mais que nenni...
Bonjour,
Autre proposition en VBA, (maîtrise pas encore POWER QUERY).
Le problème du séparateur de milliers est un espace (code 32) donc pas un caractère insécable et pourtant on n'arrive pas à le virer.
Alors pour contourner ce problème, j'ai fait une copie manuelle de ce caractère que j'ai copié en cellule Z1 de la feuille "Données_rangées".
Partant de là, la substitution fonctionne.
Cdlt
Bonjour
Chris,Je me suis "cassé" les dents pour pivoter les lignes sur 4 colonnes.
Quelles étapes as-tu utilisées ?
Plage nommée Data avec une formule DECALER
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Personnalisé1 = Table.FromRows(List.Split(Source[Column1],4), {"Date", "Opération", "Nom","Montant"}),
#"Type modifié" = Table.TransformColumnTypes(Personnalisé1,{{"Date", type date}, {"Opération", type text}, {"Nom", type text}, {"Montant", type text}}),
#"Valeur remplacée" = Table.ReplaceValue(#"Type modifié","€","",Replacer.ReplaceText,{"Montant"}),
#"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée"," ","",Replacer.ReplaceText,{"Montant"}),
#"Valeur remplacée2" = Table.ReplaceValue(#"Valeur remplacée1"," ","",Replacer.ReplaceText,{"Montant"}),
#"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée2",{{"Montant", Currency.Type}}),
#"Lignes triées" = Table.Sort(#"Type modifié1",{{"Date", Order.Ascending}})
in
#"Lignes triées"Le pivotage est simple, c'est les remplacements de caractères les étapes en plus
On pourrait faire une liste des caractères parasites et faire moins d'étapes...
Merci Chris,
Je vais essayer de reproduire tes étapes (et de les comprendre)
xorsankukai : les valeur supérieures à 999€ ne sortent pas.
Merci.
78chris : Le fichier source, c'est le relevé de compte d'un proche. C'est ennuyeux.
Par contre avec ta réponse, (plus bas) je vois que j'ai du chemin à faire pour utiliser Querry.
Arturo83 : ça sent bon ça...
merci.
RE
Par contre avec ta réponse, (plus bas) je vois que j'ai du chemin à faire pour utiliser Querry.
Apparemment pas plus qu'en VBA...
Bonjour tout le monde,
xorsankukai : les valeur supérieures à 999€ ne sortent pas.
Cordialement,
Merci pour vos réponses, j'ai pu avancer et suis maintenant sur la bonne voie.
Merci
Christophe
J'ai encore un petit bug...
xorsankukai : ta macro bloque avec les valeurs du fichier joint sur la ligne :
ntb(k + 1, 4) = IIf(CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) < 0, CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))), "")
Le message d'erreur est "incompatibilité de type" pourtant ntb est déclaré comme "variant".
A quoi cela est-il dû ?
Merci
Christophe
RE à tous
Par contre avec ta réponse, (plus bas) je vois que j'ai du chemin à faire pour utiliser Querry.
Depuis dimanche cela fonctionne en PQ il me semble et c'est une solution hyper simple...
J'ai encore un petit bug...
xorsankukai : ta macro bloque avec les valeurs du fichier joint sur la ligne...
Merci Chris de m'avoir "obligé" à chercher dans cette voie. J'ai fini par y arriver.
Mais je ne suis pas d'accord avec toi, ce n'est pas hyper simple à celui qui ne connait pas PQ. D'ailleurs, sans ta méthode donnée plus haut je n'y serais jamais arrivé.
J'ai bien vu pour la "plage data", mais pas pour la "formule décaler"
Je ne coche pas le sujet comme résolu, car si xorsankukai repasse dans le secteur et qu'il souhaite répondre à ma question, je lui en serait reconnaissant, sa solution VBA me plaisait bien.
En attendant merci à tous et bon WE.
re,
ce chr(160) cause des problèmes dans la macro à xorsankukai, avec un petit modif :
Sub transposition()
Dim tb, ntb()
Dim i, k, j
Dim crédit, débit 'facultatif
With Sheets("import_CRCA")
tb = .Range("A1").CurrentRegion
k = 0
ReDim ntb(1 To UBound(tb, 1), 1 To 5)
For i = 2 To UBound(tb, 1) Step 4
If tb(i, 1) <> "" Then
ntb(k + 1, 1) = Trim(tb(i, 1))
ntb(k + 1, 2) = Trim(tb(i + 1, 1))
ntb(k + 1, 3) = Trim(tb(i + 2, 1))
s = tb(i + 3, 1)
tb(i + 3, 1) = ""
For j = 1 To Len(s)
X = Mid(s, j, 1)
Select Case Asc(X)
Case 43 To 57: tb(i + 3, 1) = tb(i + 3, 1) & X 'éviter ce chr(160) avec tous les moyens
End Select
Next
ntb(k + 1, 4) = IIf(CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) < 0, CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))), "")
If CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) < 0 Then débit = débit + CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) 'facultatif
ntb(k + 1, 5) = IIf(CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) >= 0, CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))), "")
If CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) >= 0 Then crédit = crédit + CDbl(Trim(Replace(tb(i + 3, 1), Chr(160), ""))) 'facultatif
k = k + 1
End If
Next i
If k > 0 Then
.Range("A1").CurrentRegion.Offset(1, 0).ClearContents
.Range("A2").Resize(k, 5) = ntb
End If
Erase tb: Erase ntb
End With
MsgBox "Nombre d'opération(s) : " & k & Chr(10) & "Débit total : " & débit & "€" & Chr(10) & "Crédit total : " & crédit & "€" 'facultatif
End SubBonjour
Merci Chris de m'avoir "obligé" à chercher dans cette voie. J'ai fini par y arriver.
Mais je ne suis pas d'accord avec toi, ce n'est pas hyper simple à celui qui ne connait pas PQ. D'ailleurs, sans ta méthode donnée plus haut je n'y serais jamais arrivé.
J'ai bien vu pour la "plage data", mais pas pour la "formule décaler"
Comme tu boudais cette solution, j'ai donné le code à Eric mais je pouvais, si demandé, expliquer comme le faire manuellement en seulement quelques manips simples...
Concernant les formules nommés dans Excel, cela n'est pas lié à PowerQuery mais existe depuis la nuit des temps Excel.
Il suffit d'ouvrir le gestionnaire de noms pour voir la formule qui utilise la fonction DECALER, une grande classique d'Excel...
Elle permet de définir des plages de taille évolutive (ce qui depuis 20 ans est remplacé par les tableaux structurés...) ou des plages glissantes.