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

[SPEA]

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.

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

 

[JHA]

Bonjour à tous,

Sans le fichier exemple et sans boule de cristal, c'est compliqué. A tout hasard

=JOUR(FIN.MOIS(DATE;0))

JHA

 

[SPEA]

Ci-joint le fichier.

 

[chris]

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 :

=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]

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 :

=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.

 

[JHA]

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

 

[SPEA]

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).

 

[JHA]

Bonjour à tous,

Un essai en ajoutant une colonne dans le tableau "Tab_Titioro"

JHA

 

[chris]

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

 

[SPEA]

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]

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

 

[SPEA]

OK merci Chris pour toutes ces précisions.

Cdlt.

 

[David Aubert]

Test reponse