XL 2019 Récupérer des données et les inscrire jour après jour des premières cellules vides de colonne

[Cédric L]

Bonjour,
J'avance à tout petit pas sur un projet que je vous explique ci-après :
Je récupère chaque jour des données par mail au format CSV (il s'agit de vente de produits)
Je transforme se données pour qu'elle soit mise sous forme de tableau dans un premier onglet d'Excel.
Ensuite je voudrais qu'en appuyant sur un bouton, les quantités de produits identiques vendus s'additionnent sur une colonne (en face de leur nom) sur un autre onglet. Exemple : j'ai vendu 5 Orangina à 2€ , je souhaite qu'en face de mon tableau Orangina soit marqué le total de la vente 10 €.
Ca s'est fait. Mais à l'enregistrement suivant je n'arrive pas à décaler d'une case. Je réussi juste à additionner dans la même case.
Je joins mon fichier pour plus de clarté.
Si vous avez la solution je suis preneur.
Merci
Cédric

Voici la partie de mon code qui fonctionne mais qui ne me convient pas entièrement :

Dim Derlig&, Val As Currency
Derlig = Feuil2.Range("A" & Rows.Count).End(xlUp).Row
Derlig1 = Feuil1.Range("C" & Rows.Count).End(xlUp).Row

For j = 1 To Derlig1
For i = 1 To Derlig
If Feuil2.Range("A" & i).Value = Feuil1.Range("C" & j).Value Then
Feuil2.Range("E" & i).Value = Feuil1.Range("E" & j).Value + Feuil2.Range("E" & i).Value

 

[chris]

Bonjour à tous

J'ai cru comprendre que la source de données pouvait aussi être le copier-coller des produits vendu dans la journée.

Non.

La source d'une requête PowerQuery doit rester disponible, pas être écrasée chaque jour.

Donc c'est

• un fichier
• ou un dossier de fichiers
• ou une base de données
• ou éventuellement un site internet

Par conséquent il serait plus logique de récupérer les fichiers csv, s'ils existent.

Par ailleurs, je sais par expérience que le copier coller est la source de multiples problèmes et qu'en faire la base d'un fonctionnement n'est pas la bonne approche.

Enfin, dans ton fonctionnement, si j'ai bien suivi, on copie/colle puis le code va prendre la plage collée et en ajouter les données dans une nouvelle colonne sans contrôle sur la date.
Cela signifie qu'en cas d'erreur (on ne copie colle pas le bon jour ou on a oublié que c'était déjà fait, ou on oublie qu'on a sauté un jour... ) plus rien n'est correct.
Je pense qu'il faudrait à minima récupérer la date présente dans les données pour un contrôle : pas déjà fait et bien le lendemain de la dernière intégration.

 

[Cédric L]

Bonjour à tous

Une autre solution consiste à stocker les csv dans un dossier et les traiter par PowerQuery (intégré à Excel) puis 2 TCD

J'ai bricolé 2 csv supplémentaires dans l'exemple

Modifier le chemin du dossier contenant les csv du mois (cellule en jaune) puis Données, Actualiser tout

Bonjour,
J'ai réussi non sans mal à sortir un fichier CSV de l'application.
Le problème est la mise en forme de celui-ci.
Je vous le met en pièce jointe
Est-ce utilisable à votre avis ?
Bon ben en plus je n'arrive plus à joindre un fichier ! ... Voici le texte :

[["000001", "10-10-02021", "Eau_citronnée", "Boissons", 1.5], ["000001", "10-10-02021", "Coca", "Boissons", 2], ["000001", "10-10-02021", "Magnum_Caramel", "Glaces", 2], ["000001", "10-10-02021", "Magnum_Chocolat_Blanc", "Glaces", 3], ["000001", "10-10-02021", "Crêpe_Nutella", "Crêpes", 3], ["000001", "10-10-02021", "Crêpe_Caramel", "Crêpes", 3], ["000001", "10-10-02021", "1/2_Heure", "Parc", 5], ["000001", "10-10-02021", "1_euro", "Parc", 1], ["000001", "10-10-02021", "1_Journée", "Parasols", 15], ["000001", "10-10-02021", "Après-midi", "Parasols", 10], ["000002", "10-10-02021", "Après-midi", "Parasols", 10], ["000002", "10-10-02021", "1_euro", "Parc", 1], ["000002", "10-10-02021", "Crêpe_Beurre_Sucre", "Crêpes", 2.5], ["000002", "10-10-02021", "Solero_Citron", "Glaces", 2.8], ["000002", "10-10-02021", "Thé", "Boissons", 1.5], ["000002", "10-10-02021", "Chocolat_chaud", "Boissons", 2], ["000002", "10-10-02021", "Galette_2_Ingrédients", "Crêpes", 5]]

 

[chris]

Bonjour

Mets le csv en zip si cela ne passe passe pas ici

Il est a priori en unicode ton fichier, pas de sousci

 

[Cédric L]

Voilà...

 

[chris]

Bonjour

Ton fichier est bien codé en unicode mais il ne respecte pas la logique des csv

Les fins de lignes sont remplacés par des ],
et le début par [ avec un doublement pour début et fin de fichier

Il faudrait revoir ton export

 

[Cédric L]

Je pense que c'est bon.
Pourrais tu m'expliquer comment faire pour Power Query ou m'envoyer des liens de tutos.
Merci beaucoup,
Cédric

 

[chris]

Bonjour

Le fichier modifié pour le format du csv

Modifier le chemin dans la cellule en jaune (B1) et Données, Actualiser tout

J'expliquerai plus tard ou demain, là j'ai une réunion...

 

[chris]

RE
Il y a dans le classeur une tableau nommé Produits de 2 colonnes des Catégories et Produits : on ne mélange pas dans une même colonne

Ce tableau est basculé dans PowerQuery et on y ajoute par formule une colonne Type pour faire émerger Snack pour tout ce qui n'est ni Parc, ni Parasol

Cela permettra, une fois croisée avec les chiffres remontant des csv, de ventiler les TCD par Type et par Catégorie

Pour les csv utiliser Données, Obtenir des données, A partir d'un fichier, A partir d'un dossier
Sélectionner le dossier contenant les CSV du mois.
Dans la fenêtre suivante, dérouler le 1er bouton et choisir Combiner et transformer les données
Puis Origine du fichier Unicode-UTF-8, délimiteur Virgule, puis OK : cela ouvre POwerQuery avec une arborescence de dossiers et requêtes.
Modifier les paramètres : Fichier, Options et paramètres, Options de requête, dans la partie basse Classeur actif, sélectionner Paramètres régionaux, et sélectionner Anglais (états unis)
Il faut modifier la requête Transformer l'exemple de fichier

• sélectionner la colonne 2 et remplacer 02021 par 2021 (clic droit remplacer les valeurs)
• renommer les colonnes en Numéro, Date, Produit, Catégorie, Montant
• typer la colonne Date en date et Montant en nombre décimal ou devise

Passer à la requête qui porte le nom du dossier (Buvette dans mon exemple) :

• supprimer la dernière étape de typage
• supprimer la colonne Source Name

Reprendre la requête Produits :

• la croiser avec Buvette sur les colonnes Catégories et Produits en jointure externe gauche
• puis développer en cliquant sur la double flèche près du titre Buvette et décocher tout sauf Date et Montant

Sortir par Fermer et charger dans, Connexion seule

C'est la requête Produits qu'il faut utiliser pour construire les TCD

NB : sur le fichier joint au #22, je n'avais pas vu que la date avait un 0 devant 2021

 

[Cédric L]

Bonjour,

J'ai ajouté une colonne prix pour pouvoir faire un calcul dessus (Montant / Prix = Quantité de produit vendu)

Je pense avoir réussi les étapes jusqu'au moment ou on passe à la requête "qui porte le nom du dossier (Buvette dans mon exemple)".

J'ai essayé en reprenant le même fichier. Puis j'ai essayé de repartir de zéro en ouvrant une nouvelle feuille Excel.

Si je commence dans une feuille vierge je bloque dès le "Ce tableau est basculé dans PowerQuery et on y ajoute par formule une colonne Type pour faire émerger Snack pour tout ce qui n'est ni Parc, ni Parasol".

Au final on peut le dire... Je bloque :
Le produit fini voulu est une feuille ou est récapitulée jour par jour Les ventes par produit et le tout résumé dans un tableau qui regroupe trois sous ensembles (Snack, Parc et parasol) comme tu l'avais parfaitement fait dans ton exemple.
Je n'arrive pas à le reproduire pour l'instant...

 

[chris]

Bonjour

Si je commence dans une feuille vierge je bloque dès le "Ce tableau est basculé dans PowerQuery et on y ajoute par formule une colonne Type pour faire émerger Snack pour tout ce qui n'est ni Parc, ni Parasol".

Il faut regarder les requêtes du fichier que j'ai posté.

Tu dois partir du classeur Récap Buvette, le tableau doit être nommé Produits au lieu de conserver le nom automatique type Tableau1
Depuis une cellule du tableau Données à partir d'un tableau
Onglet Ajouter une colonne, Colonne personnalisée, nom Type, formule
=if [Catégorie]<>"Parc" and [Catégorie]<>"Parasols" then "Snack" else [Catégorie]

On laisse cette requête en attente puis tu enchaînes avec le reste des explications

 

[Cédric L]

J'ai fait ceci

Bonjour

Il faut regarder les requêtes du fichier que j'ai posté.

Tu dois partir du classeur Récap Buvette, le tableau doit être nommé Produits au lieu de conserver le nom automatique type Tableau1
Depuis une cellule du tableau Données à partir d'un tableau
Onglet Ajouter une colonne, Colonne personnalisée, nom Type, formule
=if [Catégorie]<>"Parc" and [Catégorie]<>"Parasols" then "Snack" else [Catégorie]

On laisse cette requête en attente puis tu enchaînes avec le reste des explications

Ensuite j'ai repris les explications plus haut.
Tout se passe bien jusqu'à ce que je renomme les colonnes de Transformer l'exemple de fichier. Là il me met un point d'exclamation sur le nom du dossier (de la requête) .
J'ai essayé de passer outre cette étape mais c'est juste après que le point d'exclamation apparait et du coup je ne peux pas aller au bout de tes explications.
Ca m'aura au moins permis de comprendre ta démarche.
As tu une idée de mon erreur ?

 

[chris]

Bonjour

Comme ton fichier ne contient pas cette partie, difficile de voir...

A noter que tu n'a pas suivi à la lettre

Tu dois partir du classeur Récap Buvette, le tableau doit être nommé Produits au lieu de conserver le nom automatique type Tableau1

Cela dans Excel (je parle du tableau pas de la requête)

Ni

Sortir par Fermer et charger dans, Connexion seule

...

Tout se passe bien jusqu'à ce que je renomme les colonnes de Transformer l'exemple de fichier

Si tu pars de 0 et fais les étapes décrites dans l'ordre indiqué, il n'y a pas de raiosn que cela ne fonctionne pas

 

[Cédric L]

Il y a dans le classeur une tableau nommé Produits de 2 colonnes des Catégories et Produits : on ne mélange pas dans une même colonne

Ce tableau est basculé dans PowerQuery et on y ajoute par formule une colonne Type pour faire émerger Snack pour tout ce qui n'est ni Parc, ni Parasol

Cela permettra, une fois croisée avec les chiffres remontant des csv, de ventiler les TCD par Type et par Catégorie

Pour les csv utiliser Données, Obtenir des données, A partir d'un fichier, A partir d'un dossier
Sélectionner le dossier contenant les CSV du mois.
Dans la fenêtre suivante, dérouler le 1er bouton et choisir Combiner et transformer les données
Puis Origine du fichier Unicode-UTF-8, délimiteur Virgule, puis OK : cela ouvre POwerQuery avec une arborescence de dossiers et requêtes.
Modifier les paramètres : Fichier, Options et paramètres, Options de requête, dans la partie basse Classeur actif, sélectionner Paramètres régionaux, et sélectionner Anglais (états unis)
Il faut modifier la requête Transformer l'exemple de fichier

• sélectionner la colonne 2 et remplacer 02021 par 2021 (clic droit remplacer les valeurs)

Merci Chris,
Je vois bien que explications sont très clairs et parfaitement détaillées mais je bloque toujours à la même étape. Je suis vraiment reparti de 0. Et je n'ai pris aucune liberté par rapport aux étapes. La seule chose est que la date sors au format date et heure et que du coup il ne le reconnait pas comme date ni comme date/heure dans "modifier le type". Mais l'erreur se passe juste avant cette étape, lorsque je change le nom.
Si je passe cette étape je ne comprend pas la suite pour croiser les données :

• la croiser avec Buvette sur les colonnes Catégories et Produits en jointure externe gauche
• puis développer en cliquant sur la double flèche près du titre Buvette et décocher tout sauf Date et Montant

 

[chris]

RE

Pour renommer les colonnes dans PowrrQuery on change leur Titre en double cliquant dessus pour éditer et modifier.
Est-ce bien ce que tu fais ?

J'ai posté je ne sais combien de dizaines de solutions avec ce type d'explications et c'est le 1ère fois qu'un problème se pose sur le renommage...

 

[Cédric L]

Je dois faire une erreur quelque part.
Je fais deux choses :
- Je reprend encore tout à 0
- Je donne à faire à un copain qui s'y connait en Excel (mais pas en PowerQuery)
Avec ça dès que nous avons tout recommencé ou trouvé l'erreur je reviens ici...
Merci encore,
Cédric