Disons que vous menez plusieurs projets avec des budgets différents et que vous souhaitez visualiser vos coûts pour chacun d'eux. Autrement dit, à partir de cette table source :
.. obtenez quelque chose comme ceci:
En d'autres termes, vous devez répartir le budget sur les jours de chaque projet et obtenir une version simplifiée du diagramme de Gantt du projet. Faire cela avec vos mains est long et ennuyeux, les macros sont difficiles, mais Power Query pour Excel dans une telle situation montre sa puissance dans toute sa splendeur.
Requête d'alimentation est un module complémentaire de Microsoft qui peut importer des données dans Excel à partir de presque toutes les sources, puis les transformer de différentes manières. Dans Excel 2016, ce complément est déjà intégré par défaut, et pour Excel 2010-2013, il peut être téléchargé à partir du site Web de Microsoft, puis installé sur votre PC.
Tout d'abord, transformons notre table d'origine en une table "intelligente" en choisissant la commande Formater sous forme de tableau languette Accueil (Accueil — Format sous forme de tableau) ou en appuyant sur le raccourci clavier Ctrl+T :
Ensuite, allez à l'onglet Données (si vous avez Excel 2016) ou sur l'onglet Requête d'alimentation (si vous avez Excel 2010-2013 et que vous avez installé Power Query en tant que complément séparé) et cliquez sur le bouton From Table / Range. :
Notre tableau intelligent est chargé dans l'éditeur de requête Power Query, où la première étape consiste à configurer les formats numériques pour chaque colonne à l'aide des listes déroulantes dans l'en-tête du tableau :
Pour calculer le budget par jour, vous devez calculer la durée de chaque projet. Pour cela, sélectionnez (maintenez la touche Ctrl) colonne en premier Finition, et alors Accueil et choisissez une équipe Ajouter une colonne – Date – Soustraire des jours (Ajouter une colonne — Date — Soustraire des jours):
Les nombres résultants sont 1 de moins que nécessaire, car nous sommes censés commencer chaque projet le premier jour le matin et terminer le dernier jour le soir. Par conséquent, sélectionnez la colonne résultante et ajoutez-y une unité à l'aide de la commande Transformer – Standard – Ajouter (Transformer — Standard — Ajouter):
Ajoutons maintenant une colonne où nous calculons le budget par jour. Pour cela, dans l'onglet Ajouter une colonne je ne joue pas Colonne personnalisée (Colonne personnalisée) et dans la fenêtre qui apparaît, entrez le nom du nouveau champ et la formule de calcul, en utilisant les noms des colonnes de la liste :
Maintenant, le moment le plus subtil - nous créons une autre colonne calculée avec une liste de dates du début à la fin avec un pas de 1 jour. Pour ce faire, appuyez à nouveau sur le bouton Colonne personnalisée (Colonne personnalisée) et utilisez le langage Power Query M intégré, appelé Liste.Dates:
Cette fonction a trois arguments :
- date de début – dans notre cas, elle est tirée de la colonne Accueil
- le nombre de dates à générer – dans notre cas, il s'agit du nombre de jours pour chaque projet, que nous avons compté plus haut dans la colonne Soustraction
- pas de temps - défini par la conception #durée(1,0,0,0), ce qui signifie dans le langage M – un jour, zéro heure, zéro minute, zéro seconde.
Après avoir cliqué sur OK nous obtenons une liste (List) de dates, qui peut être étendue à de nouvelles lignes à l'aide du bouton dans l'en-tête du tableau :
… et on obtient :
Il ne reste plus qu'à réduire la table, en utilisant les dates générées comme noms pour les nouvelles colonnes. L'équipe en est responsable. Colonne de détail (Colonne pivotante) languette Convertir (Transformer):
Après avoir cliqué sur OK on obtient un résultat très proche de celui recherché :
Null est, dans ce cas, un analogue d'une cellule vide dans Excel.
Il reste à supprimer les colonnes inutiles et à décharger le tableau résultant à côté des données d'origine avec la commande Fermer et charger – Fermer et charger en… (Fermer et charger — Fermer et charger dans…) languette Accueil (Accueil):
On obtient comme résultat :
Pour plus de beauté, vous pouvez personnaliser l'apparence des tableaux intelligents résultants sur l'onglet Constructeur (Conception) : définissez un style de couleur unique, désactivez les boutons de filtrage, activez les totaux, etc. Accueil — Mise en forme conditionnelle — Échelles de couleurs (Accueil — Mise en forme conditionnelle — Échelles de couleurs):
Et la meilleure partie est qu'à l'avenir, vous pouvez modifier en toute sécurité les anciens ou ajouter de nouveaux projets à la table d'origine, puis mettre à jour la bonne table avec des dates avec le bouton droit de la souris - et Power Query répétera toutes les actions que nous avons faites automatiquement .
Voilà!
- Diagramme de Gantt dans Excel utilisant la mise en forme conditionnelle
- Calendrier des étapes du projet
- Génération de lignes en double avec Power Query