XL 2016 Tableau croisé Dynamique pour calcul heures heures

[TRANKILL]

Bonjour,
Je cherche à faire des stats sur une feuille d'heure en fonction des clients et des taches.
Je n'arrive pas créer un TDC qui me donnerait par exemple:
Le nb d'heures par client sur l'année
Le nb d'heures par client sur janvier
Le nb d'heures par tache sur janvier
Le nb d'heure par client et par tache sur janvier

Ca ne me semble pas difficile mais je ne trouve pas la solution.
Pourriez-vous m'aidez svp?

 

[chris]

Bonjour

Un TCD ne peut être réalisé depuis ton planning car tableau à double entrée

Avec 2016, on peut décroiser le tableau par PowerQuery puis effectuer le TCD à partir de la requête PowerQuery

Utiliser Données, Actualiser tout pour mettre à jour

 

[TRANKILL]

Merci beaucoup,
C'est exactement ce que je cherchais à faire.
Comme j'aime bien comprendre et pouvoir reproduire, pourrais-tu m'expliquer comment tu as procédé?
Quelle commande as-tu utiliser dans "récupérer et transformer" ?
Comment ont été créé les champs années date et total heures?
N'hésite pas à faire quelques captures d'écran pour les nuls...
Merci.

 

[chris]

RE

J'ai commencé par nommer la plage Data avec une formule dans le gestionnaire de noms.

Si tu regardes ensuite la requête dans PowerQuery tu vois à droite les actions

• Je suis partie d'une requête vide Obtenir des données, A partir d'autres source puis dans POwerquery on tape dans la barre de formule
  = Excel.CurrentWorkbook(){[Name="Data"]}[Content]
• PQ type alors les données : j'ai supprimé ce typage
• j'ai supprimé la ligne 1 (j'aurais en fait pu définir Data une ligne plus bas...) par Supprimer les lignes dans l'onglet Accueil
• ensuite onglet Transformer, Utiliser la 1ère ligne pour les en-têtes
  PowerQuery type à nouveau les données : supprimer aussi ici (j'ai oublié)
• sélectionner les trois 1ères colonnes puis clic droit Dépivoter les autres colonnes (ou Supprimer le TCD des autres colonnes, selon la version)
  J'ai corrigé ensuite dans la barre de Formule pour avoir Date et Heures et non Attribut et Valeur
• modifier une 1ere fois le type de données : Date en Date/heure et Heures en Nombre entier ou décimal
• modifier ensuite Date en type date (on ne peut convertir directement un texte de type date+heure en Date)
• on sort par Fermer et charger dans, connexion seulement

Puis on construit le TCD. Les dates y sont regroupées Par année et mois

 

[TRANKILL]

Un gros merci!
Ca m'a pris une grosse demi-heure pour comprendre la mécanique...
Grace à toi, j'ai un TCD qui va me permettre de analyses rapides.
Passes de bonnes fêtes et encore Merci,

 

[valentinejade]

RE

J'ai commencé par nommer la plage Data avec une formule dans le gestionnaire de noms.

Si tu regardes ensuite la requête dans PowerQuery tu vois à droite les actions

• Je suis partie d'une requête vide Obtenir des données, A partir d'autres source puis dans POwerquery on tape dans la barre de formule
  = Excel.CurrentWorkbook(){[Name="Data"]}[Content]
• PQ type alors les données : j'ai supprimé ce typage
• j'ai supprimé la ligne 1 (j'aurais en fait pu définir Data une ligne plus bas...) par Supprimer les lignes dans l'onglet Accueil
• ensuite onglet Transformer, Utiliser la 1ère ligne pour les en-têtes
  PowerQuery type à nouveau les données : supprimer aussi ici (j'ai oublié)
• sélectionner les trois 1ères colonnes puis clic droit Dépivoter les autres colonnes (ou Supprimer le TCD des autres colonnes, selon la version)
  J'ai corrigé ensuite dans la barre de Formule pour avoir Date et Heures et non Attribut et Valeur
• modifier une 1ere fois le type de données : Date en Date/heure et Heures en Nombre entier ou décimal
• modifier ensuite Date en type date (on ne peut convertir directement un texte de type date+heure en Date)
• on sort par Fermer et charger dans, connexion seulement

Puis on construit le TCD. Les dates y sont regroupées Par année et mois

Bonsoir,
J'ai exactement la même question sauf que moi je descend le niveau de la catégorie "nulle"
Je me suis arrêtée dans très détaillée liste d'actions précises à réaliser aux termes Powerquery...
J'ai Excel 2016, mais je ne vois même pas où il se trouve sur mon Excel (une honte, je sais, si si si!!!)
Bref, je peux joindre un fichier évidemment si besoin...
Je veux calculer le nombre d'heures par chantier réalisé et par équipe/ salarié sur toute la période que dure le chantier... Facile hein???
Quand je vois le fichier lié, je trouve que ça peut correspondre.
Donc si vous avez le temps de m'aider, ce serait vraiment sympa!
Vous me dites.
Merci beaucoup pour votre attention!
Bonne soirée à tous les !

 

[chris]

Bonjour

Oui poste ton fichier, ce sera plus simple pour t'expliquer la marche à suivre

 

[valentinejade]

Bonjour

Oui poste ton fichier, ce sera plus simple pour t'expliquer la marche à suivre

Bonsoir,
C'est gentil de prendre le temps de regarder!

Voici mon fichier, alors je voudrais faire exactement ce que vous avez fait, je tâtonne donc vous allez voir plusieurs feuilles, mais il faudrait tenir compte de 2020 à imprimer la feuille visible à l'ouverture, avec la feuille nommée liste chantier ( sur laquelle j'avais tenté de faire une SOMMEPRO sans aucune conviction et finalement peu fonctionnelle. )
Pour calculer le nombre d'heures par chantier et par salarié pour indication, mais si deux salariés ou trois sont sur le même chantier, je veux pouvoir les cumuler, l'idée c'est surtout d'arriver à voir combien d'heures je peux imputer par chantier;
Arriver à voir le nombre de repas par restaurant, pour comparer les factures mensuelles des restaurants.
Bref, que de demandes hein!
Merci beaucoup pour votre temps.
Bonne fin de journée.

 

[chris]

RE

Il y a pas mal de bizarreries dans ton onglet 2020- à imprimer

Les durées sont parfois saisies avec une date comme en N5, N13... ce qui fausse le calcul

Il y a des durées sans chantier comme en N10 et des chantiers sans durée comme en Q4

Le fait d'avoir les titres sur 2 lignes nécessite un solution plus compliquée que celle utilisée pour le cas de Trankill car il faut croiser les infos de ces 2 lignes pour reconstituer qui fait quoi quand.
Donc 2 requêtes utilisées pour cela

A noter que ta formule sur l'onglet LISTE CHANTIER n'a pas de sens par rapport à ta disposition.

Je poste la solution mais ne serai pas là demain avant fin après-midi, pour les explications.

 

[valentinejade]

RE

Il y a pas mal de bizarreries dans ton onglet 2020- à imprimer

Les durées sont parfois saisies avec une date comme en N5, N13... ce qui fausse le calcul

Il y a des durées sans chantier comme en N10 et des chantiers sans durée comme en Q4

Le fait d'avoir les titres sur 2 lignes nécessite un solution plus compliquée que celle utilisée pour le cas de Trankill car il faut croiser les infos de ces 2 lignes pour reconstituer qui fait quoi quand.
Donc 2 requêtes utilisées pour cela

A noter que ta formule sur l'onglet LISTE CHANTIER n'a pas de sens par rapport à ta disposition.

Je poste la solution mais ne serai pas là demain avant fin après-midi, pour les explications.

Bonjour, je me suis penchée sur ton message, merci pour ton aide déjà!
J'ai essayé d'homogénéiser les les cellules, pour éviter les bizarrerie!
J'ai essayé de compléter les 7 ou 8 premières lignes comme ça on a maintenant une base "saine" de travail avec de vrais chantiers et de vrais restaurant et de vrais heures.
Et j'ai modifié ma cellule somme des heures qui n'était pas bonne à cause du format que j'ai mis maintenant spécifique.
J'ai vu également que ma MFC grisée et rouge pour les périodes congés, et fermeture entreprise ne fonctionnait plus, à cause des salariés je suppose? A suivre!
Concernant les deux lignes dont tu me parles il s'agit du nom du salarié et du chantier/restaurant/ temps?
Comment pourrais je faire pour simplifier si tu as une idée bien entendu je suis preneuse. Je ne voyais pas.
Concernant ma formule pour le décompte des heures dans le tableau elle est complétement fausse, je me suis inspirée des tutos que j'ai trouvé, en essayant de reproduire mais c'est archi faux, faut que je me penche sur cette formule SOMMEPROD.
Merci beaucoup pour tes explications, bien entendu je suis preneuse pour essayer de solutionner tout ça.
Je te remets le fichier aéré et modifié en fonction de tes remarques (j'espère que ce ne sera pas une perte de temps pour toi!)
Merci par avance pour ton aide.
Je suis dispo quand tu peux.
A très bientot.


Voici mon lien de téléchargement.

 

[chris]

Bonjour

Le budget heures n'est pas très clair : c'est en jours ou en heures ?

 

[valentinejade]

Bonjour

Le budget heures n'est pas très clair : c'est en jours ou en heures ?

Désolée, je n'avais pas vu ta réponse plus tôt.
Le budget d'heures je le remplis en fonction du devis que l'on a. Donc en clair on me donne ce chiffre.

 

[chris]

RE

Ma question est : quand tu indiques 15 c'est 15H ou 15 jours ?

Car là tu dépasses quasi partout si c'est en heures d'où mon interrogation...

 

[valentinejade]

RE

Ma question est : quand tu indiques 15 c'est 15H ou 15 jours ?

Car là tu dépasses quasi partout si c'est en heures d'où mon interrogation...

C'est bien en heures que je le veux parce que la pluspart des chantiers ne dépasseront pas les 500 heures. Ca ne te semble pas bon?

 

[valentinejade]

Bonjour,
C'est trop complexe tu penses ce que j'essaie de faire à cause des doubles lignes dont tu m'as parlé au début?
Peut être que je peux essayer d'organiser différemment mon tableau?
Merci pour ton attention.
Bonne journée!