Comment annuler le pivotement des données dans Excel à l’aide de Power Query (aka Get & Transform)

Regarder la vidéo – Le moyen le plus rapide d’annuler le pivotement des données dans Excel

Les tableaux croisés dynamiques sont parfaits lorsque vous souhaitez analyser une énorme quantité de données en quelques secondes. Il vous permet également de créer rapidement différentes vues de données par simple glisser-déposer.

Et pour créer un tableau croisé dynamique, vous devez disposer des données dans un format de tableau croisé dynamique spécifique.

Dans de nombreux cas, vous obtiendrez probablement les données dans des formats qui ne sont pas prêts pour le tableau croisé dynamique.

C’est souvent le cas lorsque quelqu’un collecte manuellement des données et crée un format plus lisible par les humains (pas les tableaux croisés dynamiques).

Quelque chose comme indiqué ci-dessous :

Annuler le pivot des données dans Excel à l'aide de Power Query - Ensemble de données

Le format de données ci-dessus est quelque chose que vous attendez comme résultat d’une analyse de tableau croisé dynamique.

Maintenant, que se passe-t-il si vous souhaitez analyser ces mêmes données et voir quelles ont été les ventes totales par région ou par mois.

Bien que cela puisse être facilement fait à l’aide de tableaux croisés dynamiques, vous ne pouvez malheureusement pas alimenter les données ci-dessus dans un tableau croisé dynamique.

Vous devez donc annuler le pivotement des données et les rendre conviviales pour le tableau croisé dynamique.

Résultat lorsque vous annulez le pivot des données à l'aide de la transformation Power Query

Bien qu’il existe plusieurs façons de le faire à l’aide d’une formule Excel ou de VBA, Power Query (Get & Transform in Excel 2016) est le meilleur outil pour annuler le pivotement des données.

Annuler le pivot des données à l’aide de Power Query

Voici les étapes pour annuler le pivotement des données à l’aide de Power Query :

Lis  Comment supprimer une ligne entière dans Excel à l'aide de VBA (exemples)

(Si vos données sont déjà dans un tableau Excel, commencez à partir de l’étape 6)

  1. Sélectionnez n’importe quelle cellule de l’ensemble de données.
  2. Accédez à l’onglet Insertion.Insérer un onglet dans le ruban
  3. Cliquez sur l’icône Tableau.Cliquez sur l'icône Tableau pour créer un tableau Excel
  4. Dans la boîte de dialogue « Créer une table », assurez-vous que la plage est correcte. Vous pouvez modifier la plage si nécessaire.
  5. Cliquez sur OK. Cela convertira vos données tabulaires en un tableau Excel.Boîte de dialogue Créer un tableau dans Excel
  6. Avec n’importe quelle cellule sélectionnée dans le tableau Excel, cliquez sur l’onglet Données.Onglet Données dans le ruban
  7. Dans le groupe de données Obtenir et transformer, cliquez sur l’icône « À partir de la table/de la plage ».cliquez sur à partir de la plage de la table pour créer une requête de puissance pour annuler le pivot des données
  8. Dans la boîte de dialogue Créer une table qui s’ouvre (si elle s’ouvre), cliquez sur OK. Cela ouvrira l’éditeur de requête en utilisant les données du tableau Excel.
  9. Dans l’éditeur de requête, cliquez avec le bouton droit sur la colonne Région.Cliquez avec le bouton droit sur la colonne Région Power Query pour annuler le pivotement des données
  10. Cliquez sur l’option « Annuler le pivotement des autres colonnes ». Cela annulera instantanément le pivotement de vos données.Cliquez sur Annuler le pivot d'autres colonnes
  11. Remplacez le nom de la colonne « Attribut » par un nom plus significatif, tel que « Mois ».
  12. Une fois que vous avez les données non pivotées, il est recommandé de s’assurer que les types de données sont tous corrects. Dans cet exemple, cliquez sur une cellule pour chaque colonne et voyez le type de données dans l’onglet Transformer. Si nécessaire, vous pouvez également modifier le type de données.Type de données dans l'onglet Transformer
  13. (Facultatif) Remplacez le nom de votre requête par “Ventes”.Changer le nom de la requête
  14. Allez dans l’onglet Accueil (dans l’éditeur de requêtes).Onglet Accueil dans Power Query Editor
  15. Cliquez sur Fermer et charger.Fermer et charger Power Query - Charger les données Unpivot dans Excel

Les étapes ci-dessus annuleraient le pivotement de votre ensemble de données à l’aide de Power Query et seraient réintroduits dans Excel sous forme de tableau dans une nouvelle feuille de calcul.

Lis  Ajuster la valeur maximale de la barre de défilement en fonction d'une valeur de cellule dans Excel

Vous pouvez désormais utiliser ces données pour créer différentes vues à l’aide d’un tableau croisé dynamique. Par exemple, vous pouvez vérifier la valeur totale des ventes par mois ou par région.

Actualisation de la requête lorsque de nouvelles données sont ajoutées

Tout cela fonctionne bien.

Mais que se passe-t-il lorsque de nouvelles données sont ajoutées à notre ensemble de données d’origine.

Disons que vous obtenez des données pour juillet qui sont dans le même format que celui avec lequel nous avons commencé.

Dois-je répéter toutes les étapes pour inclure ces données dans mon ensemble de données non pivoté ?

La réponse est non.

Et c’est ce qui est génial avec Power Query. Vous pouvez continuer à ajouter de nouvelles données (ou modifier des données existantes) et Power Query les mettra à jour instantanément dès que vous les actualisez.

Laisse moi te montrer comment.

Supposons ci-dessous le nouvel ensemble de données que je reçois (qui contient des données supplémentaires pour juillet):

Données de juillet à annuler le pivotement dans Excel

Voici les étapes pour actualiser la requête déjà créée et annuler le pivotement de ces données :

  1. Ajoutez ces nouvelles données à vos données d’origine que vous avez utilisées pour créer la requête.
  2. Étant donné que vous ajoutez des données à la colonne adjacente d’un tableau Excel, le tableau Excel se développera pour y inclure ces données. Si ce n’est pas le cas, faites-le manuellement en faisant glisser la petite icône « L » inversée en bas à droite du tableau Excel.
  3. Accédez à l’onglet Données et cliquez sur Requêtes et connexions. Cela affichera un volet avec toutes les requêtes existantes.Requêtes et connexions dans le ruban
  4. Cliquez avec le bouton droit sur la requête Ventes dans le volet Requêtes.Volet de requêtes qui regroupe toutes les requêtes au même endroit
  5. Cliquez sur Actualiser.Actualiser Power Query pour annuler le pivotement des données dans Excel
Lis  Comment se classer dans les groupes dans Excel

C’est ça! Vos nouvelles données sont instantanément non pivotées et ajoutées aux données existantes.

Vous remarquerez que le nombre de lignes affichées dans la requête est mis à jour pour vous montrer les nouveaux nombres. Dans cet exemple, il était de 24 avant l’actualisation et est devenu 28 après l’actualisation.

Cela signifie également que si vous avez créé des tableaux croisés dynamiques à l’aide des données que vous avez obtenues de Power Query, ces tableaux croisés dynamiques seront également actualisés pour vous montrer les résultats mis à jour.

Vous aimerez peut-être aussi les didacticiels Excel suivants :

Pour protéger votre fils, votre mari ou votre espion, vous pouvez télécharger cette application

X