Microsoft 365 Obtenir le Détail d'un # compte

Fiat3sg@hotmail.com

XLDnaute Nouveau
Comment puis-je obtenir le Détail d'un # compte avec toutes les transactions de l'année ou d'une période déterminée?

Dans la base de données onglet "Exercice" le # compte peut se retrouver dans plusieurs colonnes CompteGL1, CompteGL2,..., CompteGL6.

La sélection du compte doit se faire à partir de la liste de comptes dans l'onglet "Balance de vérification" A10:A80.

Le montant à retourner se trouve dans les champs Montant1, Montant2,..., Montant6.

De plus un # compte peut se retrouver 2 fois sur la même ligne puisque attribuer à des projets différents.

Exemple :

Détail du compte :5510 Conception des outils web
DateNomDescriptionMontant
2019-01-02NamecheapRenouvellement domaine Missisquoinord.com 18,54 $
2019-01-04Envato MarketExtension adresse autocomplete -
2019-02-05Lucie Hébert - ConsultantePremier versement - mandat AMO 1 269,43
2019-02-20Renée DonaldsonTraduction Circuit -
2019-03-04Cha-Cha CommunicationsSite web et cartes - 1er versement 1 612,31
2019-04-08OndagoFacture #1014 201,22
Total X XXX,XX
Peut-on le faire avec un TCD (Tableau croisée dynamique)?
 

Pièces jointes

  • AMO - Exercice 2019 Rév ^LN4 - Infos.xlsx
    74.7 KB · Affichages: 13
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,
Hello @Staple1600 :)

Dans le fichier joint, une solution par requête PowerQuery sur la feuille 'PowerQuery'.
Choix du compte sur liste de validation en D1
L'actualisation de la requête est faite par macro dans le code de la feuille.

Cordialement

[Edition] nouvelle version du fichier au 18/10/2019 11:20
 

Pièces jointes

  • PowerQuery AMO - Exercice 2019.xlsm
    104.5 KB · Affichages: 8
Dernière édition:

Fiat3sg@hotmail.com

XLDnaute Nouveau
Bonjour,
Hello @Staple1600 :)

Dans le fichier joint, une solution par requête PowerQuery sur la feuille 'PowerQuery'.
Choix du compte sur liste de validation en D1
L'actualisation de la requête est faite par macro dans le code de la feuille.

Cordialement

[Edition] nouvelle version du fichier au 18/10/2019 11:20
Merci beaucoup pour votre aide. Cela fonctionne très bien.
J'aimerais en connaître le fonctionnement. Pourriez-vous m'expliquer comment vous l'avez bâti ou me référer un tutoriel de formation?
 

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

Nous n'avons pas les mêmes versions d'excel et j'ai bien peur que powerquery ait évolué depuis la version addin 2010. Quoiqu'il en soit, la démarche générale et de décrire à travers différentes étapes, comment manipuler les données. Je ne connais pas de Tuto sur le sujet, même pour votre exemple j'ai du me débrouiller avec la doc miséreuse de MS https://docs.microsoft.com/fr-fr/powerquery-m/index (le chapître sur les fonctions M).

En sachant que la requête nommé 'prmCompteRecherché' ne fait que retourner la valeur de la cellule nommée 'Compte.Recherché' (D1),
je vais vous décrire les étapes de la requête 'DétailsCompte':

1 - la source : Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content] Ramène toutes les données du tableau "Tableau1"

2 - établir une liste des noms de colonnes des 'CompteG' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"CompteG"))

3 - établir une liste des colonnes 'Montant?' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Montant"))

4 - sélectionner les colonnes du tableau 'Tri' à 'Description' plus celles des deux listes précédentes : Table.SelectColumns(Source,List.Combine({{"Tri", "Date", "Nom", "Description"},lstColsComptes,lstColsMontants}))

5 - sélectionner les lignes dont au moins une colonne est égale au paramètre 'prmCompteRecherché' : = Table.SelectRows(SelectionColonnes, each List.Contains(Record.FieldValues(Record.SelectFields(_,lstColsComptes)),prmCompteCherché))

6 - ajouter une colonnes 'Montants' chargée de sommer les valeurs des colonnes idoines. Table.AddColumn(SelectionLignes, "Montants", each fnSommer(_))

Pour cette étape j'ai créé à l'intérieur de la requête une fonction nommer 'fnSommer' qui reçoit en paramètre une ligne d'enregistrement.
La fonction transforme la ligne d'enregistrement en Table, établit une liste des noms de CompteG qui ont pour valeur le compte recherché.
A partir de cette liste, par remplacement de 'CompteGL' par 'Montant' est établit une liste des lignes à sommer.

Cordialement
 
Dernière édition:

Staple1600

XLDnaute Barbatruc
Re

Roblochon
C'est pas grave au moins la lecture de ce fil m'aura au travers de tes réponses obliger à me tourner de plus en plus vers PowerQuery. ;)
Puisque tu parles d'avions
Avions-tu point remarqué que le pseudo du demandeur est un email...?
J'attends le petit Bonjour qui va bien pour me fendre de l'amical conseil relatif au spam...
Ou je me focalise vers la Puissante Requête ? ;)
 

Fiat3sg@hotmail.com

XLDnaute Nouveau
Re,

Nous n'avons pas les mêmes versions d'excel et j'ai bien peur que powerquery ait évolué depuis la version addin 2010. Quoiqu'il en soit, la démarche générale et de décrire à travers différentes étapes, comment manipuler les données. Je ne connais pas de Tuto sur le sujet, même pour votre exemple j'ai du me débrouiller avec la doc miséreuse de MS https://docs.microsoft.com/fr-fr/powerquery-m/index (le chapître sur les fonctions M).

En sachant que la requête nommé 'prmCompteRecherché' ne fait que retourner la valeur de la cellule nommée 'Compte.Recherché' (D1),
je vais vous décrire les étapes de la requête 'DétailsCompte':

1 - la source : Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content] Ramène toutes les données du tableau "Tableau1"

2 - établir une liste des noms de colonnes des 'CompteG' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"CompteG"))

3 - établir une liste des colonnes 'Montant?' : List.Select(Table.ColumnNames(Source),each Text.StartsWith(_,"Montant"))

4 - sélectionner les colonnes du tableau 'Tri' à 'Description' plus celles des deux listes précédentes : Table.SelectColumns(Source,List.Combine({{"Tri", "Date", "Nom", "Description"},lstColsComptes,lstColsMontants}))

5 - sélectionner les lignes dont au moins une colonne est égale au paramètre 'prmCompteRecherché' : = Table.SelectRows(SelectionColonnes, each List.Contains(Record.FieldValues(Record.SelectFields(_,lstColsComptes)),prmCompteCherché))

6 - ajouter une colonnes 'Montants' chargée de sommer les valeurs des colonnes idoines. Table.AddColumn(SelectionLignes, "Montants", each fnSommer(_))

Pour cette étape j'ai créé à l'intérieur de la requête une fonction nommer 'fnSommer' qui reçoit en paramètre une ligne d'enregistrement.
La fonction transforme la ligne d'enregistrement en Table, établit une liste des noms de CompteG qui ont pour valeur le compte recherché.
A partir de cette liste, par remplacement de 'CompteGL' par 'Montant' est établit une liste des lignes à sommer.

Cordialement
Désolé mais je ne comprends pas comment le refaire moi-même. J'ai des bases en PowerQuery et PowerPivot mais ce ne semble pas suffisant. Faut-il créer ces lignes de macro ou cela se fait par une requête?
 

Hasco

XLDnaute Barbatruc
Repose en paix
Bonjour,

Où voyez-vous des lignes de macro? Ce n'est que la description de la requête que je vous ai faite.
Je ne sais pas quoi vous dire en plus.
Pour interpréter les lignes de requête, vous pouvez vous aider de l'aide MS: M Formules

Eventuellement, sur un point précis revenez avec un fichier exemple contenant votre requête.

Bonne journée
 

Fiat3sg@hotmail.com

XLDnaute Nouveau
OK mais comment exécuter ces requêtes avec PowerQuery? Pourriez-vous me décrire les étapes? ex. :
1571924056472.png

Comment réaliser chacune de ces étapes par ex. pour "Lignes filtrées" : va t-on dans 1- Éditeur Power Query 2- Accueil / Trier / ... OU Transformer / ...

Lequel permet d'obtenir cette fonction : = Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL2] = prmCompteCherché) or ([CompteGL3] = prmCompteCherché) or ([CompteGL4] = prmCompteCherché) or ([CompteGL5] = prmCompteCherché) or ([CompteGL6] = prmCompteCherché))


Ex. 2

1571925339853.png


Lequel donne :
1571925375716.png


De plus, comment cela apparaît-il?
1571925421543.png


J'espère que ce n'est pas trop vous demander?

J'apprécie beaucoup votre aide.
 
Dernière édition:

Hasco

XLDnaute Barbatruc
Repose en paix
Re,

Ben c'est à dire que vous faire une formation sur PQ sort totalement du cadre du forum.
Pour le filtre, Sélectionnez l'étape 'Colonnes supprimées'
A droite de l'étiquette de la colonne 'CompteGL1' vous avez un icône de filtre, cliquer et sélectionnez un item:
1571926327016.png

Vous valider et vous obtenez l'étape 'Lignes filtrées' avec dans la barre de formule:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = "5510 Conception des outils web"))
Dans la barre de formule, vous remplacez "5510 Conception des outils web" (guillemets compris) = prmCompteCherché (sans guillemet)
ce qui vous donnera:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché))
Vous copiez ([CompteGL1] = prmCompteCherché)
1 - Vous tapez une espace et 'or' et une espace après la parenthèse fermante de prmCompteCherché)
2 - Puis vous coller (CTRL+V) Pour Obtenir ça:
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL1] = prmCompteCherché))
3 - Vous changez le deuxième 'CompteGL1' par 'CompteGL2' et vous recommencez de 1 à 3 jusqu'à obtenir
= Table.SelectRows(#"Colonnes supprimées", each ([CompteGL1] = prmCompteCherché) or ([CompteGL1] = prmCompteCherché) or ([CompteGL2] = prmCompteCherché) or ([CompteGL3] = prmCompteCherché) or ([CompteGL4] = prmCompteCherché) or ([CompteGL5] = prmCompteCherché))
et valider
Faites attention aux parenthèses.

Dans le post 5 je vous ai donné le lien vers la doc du langage M. servez-vous en.

Bon apprentissage
 

Pièces jointes

  • 1571926223410.png
    1571926223410.png
    78.4 KB · Affichages: 14

Statistiques des forums

Discussions
311 735
Messages
2 082 024
Membres
101 873
dernier inscrit
excellllll