Journal comptable banque / recherche un libellé dans plusieurs lignes
Bonjour,
Par mon métier en cabinet d'expertise-comptable, je suis souvent amené à alimenter la comptabilité à partir d'une extraction des lignes bancaires.
J'aurais besoin d'automatiser davatange mon export afin d'alimenter le n° de compte à utiliser (les deux cellules en jaune sur ma PJ). L'idée est de renvoyer à la feuille TABLE, et voici ce que j'aimerai traduire :
1) pour la première cellule jaune : si tu trouves "GRENKE" dans les lignes portant le n° 1 (dernière colonne J) > alors tu renvois 0GRENKE comme c'est indiqué sur la feuille TABLE
2) pour le second exemple : si tu trouves "REMISE CB" dans les lignes portant le n° 2 > alors tu renvois 5112000
etc.
Si possible j'aimerai aussi que le critère "ligne portant le n° 1", "ligne portant le n°2" etc , soit automatisés.
Mais je bloque pour la détermination des formules. Pourriez-vous m'aider s'il vous plait ?
Merci par avance
Cordialement,
Bonjour
L'extraction est mal chargée : l'encodage du fichier n'est pas pris en compte
Ce type de traitement est à faire avec PowerQuery intégré à Excel...
Bonjour et merci pour votre réponse.
Je n'ai absolument aucune connaissance en PowerQuery... N'est il vraiment pas possible de passer par des formules ?
Le fichier n'est pas accessible ? Je peux le reposer si besoin
cordialement
RE
Il faut poster le fichier extrait (probablement un csv) , pas un Excel
Si tu dois le modifier pour des raisons de confidentialité, utilise le bloc note car Excel ne sait pas l'ouvrir correctement et en altère le contenu...
Je ne comprends pas car le fichier est bien en xls chez moi, et je parviens à l'ouvrir. J'essaie de vous le remettre à nouveau ?
Cordialement,
RE
Vous voyez bien qu'il y a des caractères anormaux en ligne 1 : cela montre que le fichier n'est pas ouvert avec le bon encodage.
Certaines banques mettent une extension xls mais exportent en réalité des csv.
Avez-vous essayé de l'ouvrir avec le bloc notes ? Si c'est bien un format réel csv il sera lisible dans le bloc notes et son encodage sera indiqué dans l'angle en bas à droite de la fenêtre.
Effectivement il y'a des caractères anormaux en ligne 1 et j'ai compris vos remarques (je ne connaissais pas ces notions dsl)..
J'ai tenté de re-télécharger un export du site de la Société générale, et voici un aperçu en PJ. En l'ouvrant avec le bloc note, il n'y a plus de caractères anormaux (donc si je comprends bien c'est un "bon" csv). Les informations en bas à droit du bloc note sont : Ln1, Col 1, Unix (LF) , UTF-8 ??
En le convertissant en xls comme je l'avais fait, le fichier me parait exploitable pour être traité de la façon dont j'en ai besoin & ainsi répondre à ma problématique ? En quoi cela pourrait me gêner, d'autant plus que cette première ligne est vouée à être supprimée dans la suite de mes retraitements?
Merci
RE
Et pour les 2 premières opérations on doit trouver quoi et mettre quel compte car il n'y a ni GRENKE ni REMISE CB pour ces 2 opérations (il y a 3 opérations dans ce csv.)
Les éléments à trouver sont dans mon premier fichier auquel j'ai déjà apporté des modifications par rapport au csv exporté
RE
Je peux travailler à partir d'un CSV pas de ton Excel : un mausavis import peut avoir des effets aussi sur le libellé de l'opération.
On n'ouvre pas un csv depuis Excel (sauf éventuellement si on vit aux états unis et que ne manipule que des fichiers américains), on les importe.
En PowerQuery on peut obtenir cela (j'ai fait une table de conversion bidon avec tes 2 demandes et deux inventés par rapport au csv transmis)
On ne me l'avait jamais appris. C'est intéressant a savoir. Du coup pourriez vous me donner la marche à suivre pour importer correctement un csv?
Une fois cela fait, pourrai vous renvoyer le fichier même si je ne pense pas que cela soit nécessaire !
Le résultat obtenu semble ressembler a ce que je cherche ! Mais alors pour duppliquer... c'est accessible pour un novice ?
RE
Le principe est le suivant : onglet Données, A partir d'un fichier texte/csv
Sélectionner le fichier, Importer, Transformer les données : on se retrouve dans PowerQuery et on peut agir sur les données.
Dans le classeur ci-joint : j'ai crée un onglet Technique avec
- un tableau de conversion (qui est chargé dans PowerQuery)
- une cellule nommée contenant le chemin du csv à traiter (en jaune)
La requête CSV
- charge le fichier donc le nom est dans la cellule nommée,
- en supprime les 6 lignes d'en-tête,
- promeut la suivante comme en-têtes,
- type les données,
- ajoute un index, un sous-index pour repérer chaque opération,
- regroupe par Opération pour garder les colonnes utiles sur une seule et même ligne y compris le libellé
- cherche les mots clé dans le libellé et récupère le Compte
- numérote ces lignes d'opérations
J'ai vu dans l'exemple Excel que vous avez ajouté des libellés : il faudrait le faire dans une colonne ajoutée à la requête.
Selon la norme une table est composé d'enregistrements d'une ligne...
Pour que la cellule nommée soit correctement chargée il faut modifier un paramètres PowerQuery :
Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité
Une fois cela fait, modifier le chemin dans la cellule en jaune, modifier/compléter la table de correspondance : il ne reste qu'à actualiser.
Cela marchera pour tout fichier extrait de même structure.
Bonjour,
Merci pour le travail effectué pour moi. J'ai l'impression que cela va être trop compliqué pour moi et je vais me voir contrainte d'abandonner l'idée me semble-t-il ...
Je suis bloquée à "Sélectionner le fichier, Importer, Transformer les données : on se retrouve dans PowerQuery et on peut agir sur les données." où je ne sais pas quelle suite je dois donner...
Bonjour
Dans un premier temps utilise mon fichier.
A part :
- modifier dans PowerQuery le paramètre comme indiqué :
Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité - modifier dans Excel la table de conversion et le chemin d'accès
tu n'as rien à faire d'autre qu'actualiser (Données Actualiser tout) ...
Teste déjà cela et si tu dois ajouter une colonne, reviens pour qu'on t'explique...
Sinon :
onglet Données, A partir d'un fichier texte/csv
Sélectionner le fichier, Importer , Charger et tu auras le fichier correctement décodé dans Excel mais avec les mêmes problèmes de formule décrits dans ton premier post... car les formules pour traiter tous les cas ne sont pas la bonne méthode...
En comptabilité il est important de pouvoir industrialiser mais de façon juste en limitant au maximum le risque d'erreur ...
Merci encore, mais comment je fais pour ouvrir PowerQuery ?
Merci !
Premières questions ou pistes d'amélioration qui me viennent :
=> est-il possible d'attribuer un n° de compte par défaut (471) à toute les opérations qu'il n'aurait pas retrouvé dans la table de correspondance ? Pour avoir l'exhaustivité des opérations du relevé bancaire ?
=> j'aimerai que les colonnes débit & crédit soient en positif. Est-ce correct d'appliquer : multiplier par -1 ? Y'a t'il une meilleure manipulation ?
j'ai beaucoup de travail pour arriver à continuer à développer et comprendre la base que vous m'avez envoyée, mais merci beaucoup cela attise ma curiosité...
Deuxième question, pourriez-vous m'expliquer comment vous avez paramétré dans le requête que les 3 lignes liées à la même opérations sur le CSV ressortent en une seule ligne ?
RE
Je vais répondre dans le désordre mais c'est plus logique par rapport à mes modifications
A noter que les étapes de la requêtes sont listées à droite.
Le détail d'une étape sélectionnée est visible dans la barre de formule et en cliquant sur le petit rouage de la ligne d'étape
=>pourriez-vous m'expliquer comment vous avez paramétré dans le requête que les 3 lignes liées à la même opérations sur le CSV ressortent en une seule ligne ?
Ce sont les étapes nommées
- Lignes groupées1 (on regroupe par Date, Date de valeur et Idx) et on récupère par calcul Débit, Crédit, Devise, Libellé interbancaire, plus la table.
- Personnalisée ajoutée3 qui ajoute une formule pour récupérer le champ Nature de l'opération dans la table
- Valeurs extraites qui en extrait les n lignes et les met bout à bout
=> j'aimerai que les colonnes débit & crédit soient en positif. Est-ce correct d'appliquer : multiplier par -1 ? Y'a t'il une meilleure manipulation ?
J'ai effectivement ajouté *-1 à l'étape Lignes groupées1 (on le fait dans la barre de formule après le regroupement détaillé dans la fenêtre de regroupement)
=> est-il possible d'attribuer un n° de compte par défaut (471) à toute les opérations qu'il n'aurait pas retrouvé dans la table de correspondance ? Pour avoir l'exhaustivité des opérations du relevé bancaire ?
C'est nettement plus compliqué.
Pour que tu comprennes mieux j'ai coupé la requête CSV en 3
CSV0 qui s'arrête après l'étape Valeurs extraites et la suppression de la colonne Tabl. On a donc les 3 lignes d'opérations.
CSV_Comptes qui effectue la suite jusqu'à associer le compte pour les opérations où on peut le faire (j'ai retiré une ligne de la table de conversion pour avoir un cas sans compte). Reste donc 2 opérations dont le compte est identifié.
CSV qui
- croise CSV0 et CSV_Comptes pour récupérer les lignes sans compte identifié,
- leur associe le compte 471,
- combine CSV0 et la requête en cours pour avoir toutes les lignes
- tri par Idx, indexe, et supprime Idx
- ordonne les colonnes
Un immense merci je vais essayer de descendre ces 3 requêtes pour comprendre. Je reviens vers vous.
Dans l'attente, est-ce normal qu'à partir de l'étape "Etat0" je ne vois plus le résultat de la requête en affichage ? Je ne parviens à vous mettre de capture mais ci-après le résultat affiché :
Expression.Error : Désolé... Nous n'avons pas trouvé la colonne « Nature de l'opération » de la table.
Détails :
Nature de l'opération
RE
Si tu as ce message c'est que la colonne porte un autre nom dans le csv source ou que le nombre de lignes d'en-tête n'est pas le même que dans ton csv exemple.Si c'est le cas, on est mal. Le second cas est gérable (il faudrait modifier la requête pour chercher le mot Date et ainsi déterminer la 1ère ligne de données) mais pour les titres de colonnes si c'est aléatoire...