Tableau croisé dynamique champ calculé

Le 02/12/2020 à 18:52

Bonjour à tous,

Je sollicite votre aide pour la première fois, voici ma problématique:

Dans le cadre de la mise en place d'indicateurs, je dois exploiter une base de données de plusieurs milliers de lignes que j'alimenterais chaque mois. Chaque ligne correspond à une commande et est rattachée à une immatriculation de véhicule et une date (voir onglet "données") Dans l'onglet

46tcd-test.xlsx (18.64 Ko)

"Tcd" en exemple, je souhaite insérer un champ dans le Tcd afin de faire la moyenne du chiffre d'affaire journalier réalisé pour chaque immatriculations...

J'ai fait plusieurs tentatives par le biais du champ calculé, mais je ne parviens pas a trouver la bonne combinaison, et là je bloque!!

Merci d'avance pour vos retours.

Bonne soirée

Le 02/12/2020 à 20:09

Bonjour

Tu ne peux pas pour plusieurs raisons : un champs calculé se base par défaut sur la somme

Ce que tu demandes est un calcul sur un résultat et non sur les données initiales, ce qui n'existe pas dans le TCD.

Il ne peut calculer qu'un moyenne pondérée par facture.

une proposition par PowerQuery + MFC appliquée au TCD réalisé à partir de la requête

76tcd-moyenne-pq.xlsx (29.97 Ko)
Le 03/12/2020 à 08:20

Bonjour Chris,

Merci pour ton aide, ta solution semble répondre à mon besoin mais elle dépasse légèrement mes compétences Excel :). Peux tu m'expliquer le chemin pour parvenir à ce résultat ???

Dans mon cas n'est-il pas plus simple de re-traiter la base de données pour afficher le total de chiffre d'affaire pour chaque immatriculation par jours??

Merci

Le 03/12/2020 à 09:08

Dans une autre colonne j'ai essayé la formule suivante :

=SI([@[IMMAT_TRACTEUR]]<>J3;[@[Mt Transport]];"")

Le problème c'est qu'elle ne fonctionne que lorsque les données sont triées par date et à condition de ne pas avoir 2 dates différentes à suivre avec la même immatriculation..

Le 03/12/2020 à 11:50

Bonjour,

Il existe une autre solution à votre problème. Une solution un peu longue mais universelle (réponds à toutes les situations semblable) et facilement "industrialisable" (la solution traitera automatiquement l'ensemble du classeur).

Voici les différentes étapes de la solution que vous pouvez suivre (voir classeur en pièce jointe):

--1:

-Créez la colonne "LES IMMATRICULATIONS SONT-ELLES INDENTIQUES D'UNE LIGNE A L'AUTRE ?

" en M (Laissons la colonne L vide pour le momment).

-Rentrez la formule "=SI(J2=J3;"VRAI";"FAUX")" en M2

-Remarque: l'objectif de cette formule est de d'indiquer à excel la frontière entre une immatriculation et une autre.

--2:

-Créez la colonne "LES DATES DE LIVRAISONS SONT-ELLES INDENTIQUES D'UNE LIGNE A L'AUTRE ?" en N

-Rentrez la formule "=SI(I2=I3;"VRAI";"FAUX")" en N2

-Remarque: l'objectif de cette formule est d'indiquer à excel la frontière entre une date de livraison et une autre.

--3:

-Créez la colonne "SOMME PROGRESSIVE DU CHIFFRE D'AFFAIRE PAR IMMATRICULATION ET PAR JOURS" en O

-Rentrez la formule "=SI(OU(M1="FAUX";L1="FAUX");K2;SI(ESTTEXTE(N1);K2;SI(OU(ESTTEXTE(N1);ESTNUM(N1));SOMME(K2;N1);"")))" en O2

-Remarque: l'objectif de cette formule est de progressivement additionner les chiffres d'affaires pour une immatriculation et un jour donné.

--4:

-Créez la colonne "SOMME TOTALE CA PAR IMMATRICULATION ET PAR JOUR" en P

-Rentrez la formule "=SI(OU(M2="FAUX";L2="FAUX");N2;"")" en P2

-Remarque: l'objectif de cette formule est de déterminer le chiffre d'affaires effectuer par une immatriculation pour un jour donné.

--5:

-Créez la colonne "OUTILS CALCUL MOYENNE DE CA PAR IMMATRICULATION ET PAR JOUR" en Q

-Rentrez la formule "=SI(ESTTEXTE(P1);1;SI(ET(ESTNUM(P1);ESTNUM(N2);ESTNUM(O2));O2/(P1+1);SI(ESTNUM(O1);1;SI(ET(ESTNUM(P1);ESTNUM(N2));(1+P1);SI(ET(ESTNUM(P1);ESTNUM(N2));1)))))" en Q2

-Remarque: l'objectif de cette formule est de calculer le CA moyen d'une immatriculation pour un jour donné.

--6:

-Créez la colonne "MOYENNE DE CA PAR IMMATRICULATION ET PAR JOUR" en L

-Rentrer la formule "=SI(ESTNUM(O2);P2;"")" en L2

-Remarque: l'objectif de cette formule est de supprimer les numéros 1 de la colonne P qui créent des erreurs dans le tcd.

--7:

-étirez l'ensemble des formules des nouvelles colonnes de la ligne 2 à la ligne 9

--8: intégrez la colonne L au tcd

--9: sélectionnez le champ "MOYENNE DE CA PAR IMMATRICULATION ET PAR JOUR"

--10: Mettez le champ MOYEN DE CA PAR IMMATRICULATION ET PAR JOUR" dans la zone "Valeurs" avec "somme" comme valeurs

--11: Vous constaterez que le résultat est correct et automatisable pour l'ensemble du tableau

--12: pour peaufiner, vous pouvez masquer les colonnes indésirables (mais il ne faut pas les supprimer ni les modifier !) en suivant un tuto sur internet.

En espérant que cela pourra vous être utile,

Bonne journée !

18tcd-test.xlsx (23.87 Ko)
Le 03/12/2020 à 13:55

Bonjour à tous

Une autre solution simple utilisant le modèle de données PowerPivot

A la construction du TCD, dans la fenêtre Créer un tableau croisé dynamique, cocher tout en bas "Ajouter ces données au modèle de données"

Construire le TCD puis, selon ta version

  • soit dans l'onglet POwerPIvot (version Pro plus)
  • soit pour les autres versions, dans le volet de construction du TCD à droite, clic droit dans le liste des champs,

Nouvelle Mesure :

  • nom : Moyenne / Jour
  • formule : =[Somme de Mt Transport]/DISTINCTCOUNT([Date Liv])

Utiliser la même MFC que pour l'exemple PowerQuery

51tcd-moyenne-ppvt.xlsx (202.04 Ko)
Le 03/12/2020 à 14:16
78chris a écrit :

Bonjour à tous

Une autre solution simple utilisant le modèle de données PowerPivot

A la construction du TCD, dans la fenêtre Créer un tableau croisé dynamique, cocher tout en bas "Ajouter ces données au modèle de données"

Construire le TCD puis, selon ta version

  • soit dans l'onglet POwerPIvot (version Pro plus)
  • soit pour les autres versions, dans le volet de construction du TCD à droite, clic droit dans le liste des champs,

Nouvelle Mesure :

  • nom : Moyenne / Jour
  • formule : =[Somme de Mt Transport]/DISTINCTCOUNT([Date Liv])

Utiliser la même MFC que pour l'exemple PowerQuery

51tcd-moyenne-ppvt.xlsx (202.04 Ko)

Sinon pour PowerQuery

  1. A partir d'une cellule du tableau source nommé Données : Données, à partir d'un tableau. Ce qui ouvre PowerQuery
    On voit à gauche une requête portant le nom du tableau Données, au centre les données, à droite les étapes de la requête
  2. Sélectionner les colonnes Agence, IMMAT_TRACTEUR, Date Liv, Mt Transport, clic droit, Supprimer les autres colonnes
  3. Sélectionner les colonnes Agence, IMMAT_TRACTEUR, Date Liv puis Transformer, Regrouper par : opération Somme, colonne Mt Transport, nom Mt Transport
  4. Accueil, Nouvelle Source, Autres Sources, Requête vide : dans la barre de Formule taper
    =Données
    puis renommer la requête en Moyenne
  5. Sélectionner les colonnes Agence, IMMAT_TRACTEUR puis Transformer, Regrouper par :
    opération Moyenne, colonne Mt Transport, nom Mt Moyen
    opération Compter les lignes, nom NB
  6. Ajouter une colonne, Colonne personnalisée : nom : Mtt Moyen /J, formule :
    [Mt Moyen]/[NB]
  7. Supprimer les colonnes Mt Moyen, NB
  8. Accueil, Fusionner les requêtes : en seconde requête choisir Données, sélectionner les colonnes Agence, IMMAT_TRACTEUR de chaque requête, Jointure Interne
  9. Cliquer sur la double flèche près du titre Données : décocher tout sauf Date Liv, Mt Transport
  10. Sortir par Fermer et charger dans, TCD
    (si cela existe déjà dans 2019) sinon Connexion uniquement et construire le TCD à partir de la requête Moyenne
Le 03/12/2020 à 15:05

Super !! Deux approches différentes mais qui fonctionnent parfaitement...

Merci infiniment pour vos réponses ainsi que pour le temps consacré!! :)

Le 03/12/2020 à 16:18

RE

Tu en as même 3 en tout :

  1. Formules
  2. PowerQuery
  3. PowerPivot
Le 04/12/2020 à 14:37

Bonjour à tous!!

Chris j'ai reproduit ta méthode avec le powerpivot, cela fonctionne à un détail près, je n'arrive pas modifier le total général (je souhaite faire la moyenne de la plage et non la somme...)

Et enfin dernière chose, je souhaite calculer dans mon tableau le nombre de jours d'exploitation qui peut se définir par le nombre de valeur (voir PJ)

Bon voilà, j'espère ne pas trop abusé, dernière question pour ma culture perso, quelle est la différence fondamentale entre Powerquery et Powerpivot, il semble que tout 2 permettent un retraitement de la donnée ??

Merci d'avance

5tdc-test2.zip (691.83 Ko)
Le 04/12/2020 à 15:37

Bonjour

Ce sont des calculs différents : un total existe pour chaque calcul

37tcd-moyenne-ppvt2.xlsx (201.15 Ko)

PowerPivot transforme les données en base cube et offre les possibilités des bases cubes uniquement pour réaliser des TCD

PowerQuery est un requêteur fonctionnant sur le principe des bases relationnelles et pouvant retraiter les données de façons très diverses sans forcément que le résultat soit utilisé par un TCD

Ils sont complémentaires plus qu'équivalents

Le 04/12/2020 à 18:30

Dans l'autre exemple ci-dessus, légèrement différent, j'utilise la formule [Somme de Mt Transport]/DISTINCTCOUNT([Date Liv]) qui fonctionne parfaitement sauf pour mon total général qui fait la somme des montant plutôt que d'en faire la moyenne..

Ok merci pour les infos, je vais creuser le sujet..

Le 04/12/2020 à 19:55

RE

Pour ton second TCD le total à droite correspond bien à la somme des Montant de la ligne / le nombre de valeurs de la ligne
pour la 1ère 205/3 doit 68,33

Pour le total en bas de la colonne il n'y a qu'une date par jour dont division de la somme par 1 et en total général
661454 / 29 dates soit 2208,76

Le TCD trouve 29 dates en tout et la formule est basée sur le nombre de dates distinctes donc c'est juste
Le 07/12/2020 à 14:07

Bonjour Chris,

Merci pour ton aide..

Rechercher des sujets similaires à "tableau croise dynamique champ calcule"