Excel 2016: voici comment mettre de l’ordre dans vos fichiers Excel à l’aide de Power Query

Pour de nombreux employés de la Confédération, Microsoft Excel est un instrument de travail indispensable. Mais les analyses complexes entraînent fréquemment des complications inattendues. C’est pourquoi «Eisbrecher» vous présente quelques astuces pour pouvoir, à l’avenir, travailler encore plus rapidement et efficacement avec Excel.

Des requêtes simples comme bonjour

En avez-vous assez des copier-coller sans fin? Des valeurs énigmatiques apparaissent-elles quand vous utilisez des données stockées de manière temporaire? Devez-vous passer par les mêmes étapes à chaque fois que de nouvelles données sont disponibles? Grâce aux requêtes dans Excel, vous pouvez diminuer votre charge de travail en toute simplicité, sans macros fastidieuses. En effet, Power Query vous permet – surtout quand les données conservent le même format – une automatisation rapide et intuitive de plusieurs étapes de travail. 

Dans l’exemple suivant, vous calculez les dépenses pour une sortie de groupe et essayez de déterminer le montant payé par chaque participant, et donc de savoir qui doit de l’argent à qui. Pour cela, enregistrez votre fichier Excel avec les données (image 1) et ouvrez un nouveau fichier Excel.

Dans l’onglet «Données», cliquez sur «Nouvelle requête». Vous avez ici plusieurs possibilités de requête, pour notre exemple, sélectionnez «À partir d’un fichier» puis «À partir d’un classeur» (image 2).

Dans la nouvelle fenêtre, vous pouvez maintenant naviguer jusqu’au fichier enregistré plus tôt, qui comporte vos données. Une fois dans la fenêtre du «Navigateur», sélectionnez la bonne feuille de travail dans la colonne de gauche en appuyant sur le bouton droit de votre souris, puis cliquez sur «Modifier». Vous êtes maintenant dans l’Éditeur Power Query. 

L’éditeur Power Query est un outil très utile. Vous pouvez par exemple, dans la colonne de droite, définir le nom de la requête. Dans l’exemple, nommez-la «dépenses». Plus important encore, vous pouvez y définir les étapes de travail qui seront exécutées automatiquement lors de chaque actualisation des données. 

Dans l’exemple, vous calculez les dépenses effectuées par chaque participant pendant une sortie de groupe. Vous devez d’abord harmoniser la dernière colonne «montant versé». Excel sauvegarde les valeurs de cette colonne en tant que texte car il a reconnu le mot «aucun». Dans un premier temps, remplacez «aucun» par un champ vide. Pour ce faire, sélectionnez la colonne et, dans l’onglet «Transformer», cliquez sur le bouton «Remplacer les valeurs» (image 3). Si vous laissez le champ «Remplacer par» vide, Excel supprime la valeur de cellule concernée. Une fois toutes les valeurs posant problème corrigées, vous pouvez sélectionner «nombre décimal» comme type de données. La colonne ne comportant plus de mots, elle peut maintenant être convertie en nombre. 

Afin que les apprentis ne doivent pas mettre la main à la poche, créez une colonne qui indique si un participant a moins de 18 ans. Pour ce faire, dans l’onglet «Ajouter une colonne», sélectionnez «Colonne conditionnelle». Saisissez la limite d’âge et les nouvelles valeurs de la nouvelle colonne (image 5).

Si vous êtes satisfait de la manipulation, vous pouvez récupérer votre source de données dans votre feuille Excel en cliquant sur «Fermer et charger». 

Dans l’exemple, de nouvelles personnes ont été ajoutées et la fiche de données est disponible sous sa forme actualisée.

Dans votre nouveau fichier Excel, allez sous «Données», puis «Actualiser tout». Excel ayant déjà sauvegardé automatiquement chacune de vos étapes de calcul, la colonne «montant versé» sera automatiquement modifiée puis la colonne «apprentis?» sera recalculée. Vous pouvez ainsi, en un clic, venir à bout de beaucoup de petites étapes de travail répétitives et mettre de l’ordre dans vos fichiers.

En finir avec la soupe aux lettres: des références structurées

Ce processus produit un effet secondaire plaisant: Power Query permet un formatage automatique des tableaux. Vous pouvez aussi sélectionner vos données dans l’onglet «Accueil» et ensuite manuellement sous «Mettre sous forme de tableau» (image 6), ce qui permet d’utiliser les références structurées. Vous améliorez ainsi la lisibilité des références (par exemple dans les formules) et toute modification des données ou des colonnes vous dérangera moins. Les références structurées sont donc le complément idéal à Power Query. Au lieu d’utiliser les lettres des colonnes respectives, les références structurées fonctionnent en principe d’après la structure «Nom du tableau[nom de la colonne]». 

Ouvrez par exemple une nouvelle feuille de calcul. Pour effectuer le calcul, vous avez besoin de trois chiffres: la somme totale, le nombre d’adultes et le montant par personne. Cela fonctionne mieux sous forme de tableau.

Image 6: Vous pouvez mettre vos données sous forme de tableau dans l’onglet «Accueil».

La somme totale est calculée comme le montant total de toutes les dépenses effectuées. Pour ce faire, accédez au champ correspondant et utilisez la formule =SOMME(Données[einbezahlt]) pour calculer la formule de la somme au moyen d’une référence structurée.

Image 7

De la même manière, vous pouvez calculer le nombre d’adultes. Pour ce faire, cliquez à nouveau sur le champ correspondant et écrivez la formule =NB.SI(Ausgaben[Lernende?];’Nein’) 

 

Ensuite vient le calcul des dépenses par personne, ce qui correspond au résultat de la division des dépenses totales par le nombre d’adultes. Pour déterminer les dépenses par personne, insérez une nouvelle colonne à côté des données.

Pour connaître le nom de votre tableau, vous pouvez sélectionner les données et le nom de votre tableau s’affichera en haut à gauche:

Image 8

En copiant les formules des colonnes, vous avez calculé combien chaque personne a versé et combien elle doit récupérer. 

Cet exemple ne montre qu’un petit aperçu des possibilités de Power Query et des références structurées. Grâce à une programmation intuitive des étapes de travail, vous pouvez automatiser rapidement et simplement des tâches laborieuses. En outre, vous bénéficiez toujours d’un degré élevé de transparence de ce qu’il se passe en arrière-plan. 

Power Query et les références structurées sont des aides indispensables dans l’utilisation quotidienne d’Excel qui vous permettent de mieux exploiter vos données. Les données de cet exemple sont jointes ci-dessous. Faites un essai!


Texte: Oliver Prinzing 


https://www.bit.admin.ch/content/bit/fr/home/dokumentation/kundenzeitschrift-eisbrecher/eisbrecher-archiv/kundenzeitschrift-eisbrecher-ausgabe-75/tipps-excel.html