XL 2016 Calculer nombre de jours du mois dans un tableau croisé dynamique

SPEA

XLDnaute Occasionnel
Bonjour,

je cherche à calculer le nombre de jours du mois correspondant dans un TCD, pour cela j'ai inséré un champs calculé mais la formule ne fonctionne pas.

Sans titre.png


J'ai un champs Date qui contient la date et un champs Années qui contient l'année.
 

chris

XLDnaute Barbatruc
Bonjour

Sachant qu'un champ calculé de TCD se base sur la somme du champ, ce n'est pas possible

En plus ANNEE(Années) n'a pas de sens puisque Années en déjà une Année (en texte dans un TCD)

Soit tu ajoutes une colonne formulée au Tableau source :
VB:
=FIN.MOIS([@Date];0)-FIN.MOIS([@Date];-1)
et tu utilises ce champ en Min ou Max, soit tu l'ajoutes via une requête PowerQuery (intégré à Excel)

A noter que la formule correspondant au nom Date de ton classeur devrait être
=Tab_Titioro[Date]
DECALER étant inutile dans les tableaux structurés
 

SPEA

XLDnaute Occasionnel
Bonjour

Sachant qu'un champ calculé de TCD se base sur la somme du champ, ce n'est pas possible

En plus ANNEE(Années) n'a pas de sens puisque Années en déjà une Année (en texte dans un TCD)

Soit tu ajoutes une colonne formulée au Tableau source :
VB:
=FIN.MOIS([@Date];0)-FIN.MOIS([@Date];-1)
et tu utilises ce champ en Min ou Max, soit tu l'ajoutes via une requête PowerQuery (intégré à Excel)

A noter que la formule correspondant au nom Date de ton classeur devrait être
=Tab_Titioro[Date]
DECALER étant inutile dans les tableaux structurés
Bonjour,

Ne connaissant pas les requêtes j'ai ajouté une colonne au tableau source comme préconisé, j'ai pu obtenir les nombres de jours de chaque mois dans le TCD. Maintenant je suis bloqué pour calculer les heures par mois, je pensais créer un champs calculé et multiplier le nombre de jours par 24 mais je ne peux pas insérer le champs du nbre de jours par mois.

La finalité recherchée, connaissant le temps de marche de l'appareil dans le champs colonne B, est de calculer le % de temps de marche pour chaque mois d'où la nécessité de calculer le nombre d'heures total de chaque mois. S'il y a une méthode plus simple que celle que j'essaie je suis preneur.
 

Pièces jointes

  • test2.xlsx
    104.2 KB · Affichages: 7

JHA

XLDnaute Barbatruc
Bonjour à tous,

Tu ne peux pas insérer le champ "nbre de jours par mois" car il a été renommé, à l'origine c'est "Fin du mois".

J'ai mis une formule dans le TCD, à adapter suivant tes besoins.

JHA
 

Pièces jointes

  • test2.xlsx
    104.5 KB · Affichages: 5

SPEA

XLDnaute Occasionnel
Bonjour JHA,

Le résultat n'est pas cohérent, par exemple pour le mois de janvier 2019 on a 31 jours x 24h donc 744 heures pour un temps de marche de 570 heures soit 76% de temps de marche.

Si j'insère le champs "Fin de mois" il fait la somme des jours du champs (par exemple 120 pour sept 2019), alors que j'ai besoin du Nbre de jours du mois (30 pour sept 2019).
 

chris

XLDnaute Barbatruc
Bonjour à tous

Solution PowerQuery

J'ai gardé un TCD basé sur la requête pour les totaux annuels et les possibilités de filtre (reste à ajouter les segments) mais sinon on pourrait directement afficher le résultat de la requête et lui ajouter des segments
 

Pièces jointes

  • Sel_javel_PQ.xlsx
    116.7 KB · Affichages: 10

SPEA

XLDnaute Occasionnel
Bonjour à tous,

Merci pour votre aide, 2 solutions différentes. Celle avec PowerQuery parait plus simple car elle ne nécessite pas d'ajouter des colonnes intermédiaires dans le tableau source, par contre n'ayant jamais utilisé cet outils je n'ai pas compris comment fonctionne la requête.
 

chris

XLDnaute Barbatruc
RE

Si tu ouvres PowerQuery : Données, Connexions puis dans le volet à droite, clic droit sur Tab_Titioro , Modifier
Cela ouvre PowerQuery et à droite tu vois toutes les étapes de la requête :
  • Source et Type modifié sont automatiques quand à partir de la source, tu utilises Données, A partir d'un tableau
  • On sélectionne ensuite les colonnes à conserver : Date, Temps marche, Energie consommée, Sacs de sels et clic droit, supprimer les autres colonnes
  • A partir de la colonne Date : Ajouter une colonne, Date, Année, Année
  • A partir de la colonne Date : Ajouter une colonne, Date, Mois, Nom du mois
  • Ensuite on ajoute les colonnes calculées :
    • Ajouter une colonne, Colonne personnalisée : NB Jours, formule
      Date.DaysInMonth([Date])
    • Ajouter une colonne, Colonne personnalisée : Conso sel (Kg), formule
      [Sacs de sel]*25
    • Ajouter une colonne, Colonne personnalisée : Volume Javel produite, formule
      [#"Temps marche (h)"]*82
    • Ajouter une colonne, Colonne personnalisée : Heures/Mois, formule
      [NB Jours]*24
  • Sélectionner les colonnes Année, Nom du mois, Nb Jours, Heures/Mois,
    Transformer, Regrouper par
    effectuer dans le bas les sommes des colonnes Temps marche, Energie consommée, Sacs de sels, Conso sel (Kg), Volume Javel produite
  • Ajouter une colonne, Colonne personnalisée : Ratio, formule
    [#"Temps marche (h)"]/[#"Heures/Mois"]
  • Sortir par Fermer et Charger Dans : connexion seulement si on veut faire un TCD, Tableau sinon
Dans Excel construire le TCD sur Données Externes et choisir Tab_Titioro
 
Dernière édition:

Discussions similaires