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 :
Le format de données ci-dessus est quelque chose que vous attendez en sortie 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.
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 :
(Si vos données sont déjà dans un tableau Excel, commencez à partir de l'étape 6)
- Sélectionnez n'importe quelle cellule de l'ensemble de données.
- Accédez à l'onglet Insertion.
- Cliquez sur l'icône Tableau.
- 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.
- Cliquez sur OK. Cela convertira vos données tabulaires en un tableau Excel.
- Avec n'importe quelle cellule sélectionnée dans le tableau Excel, cliquez sur l'onglet Données.
- Dans le groupe de données Obtenir et transformer, cliquez sur l'icône « À partir de la table/de la plage ».
- 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.
- Dans l'éditeur de requête, cliquez avec le bouton droit sur la colonne Région.
- Cliquez sur l'option « Annuler le pivotement des autres colonnes ». Cela annulera instantanément le pivotement de vos données.
- Remplacez le nom de la colonne « Attribut » par un nom plus significatif, tel que « Mois ».
- 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.
- (Facultatif) Remplacez le nom de votre requête par "Ventes".
- Allez dans l'onglet Accueil (dans l'éditeur de requêtes).
- Cliquez sur Fermer et charger.
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.
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):
Voici les étapes pour actualiser la requête déjà créée et annuler le pivotement de ces données :
- Ajoutez ces nouvelles données à vos données d'origine que vous avez utilisées pour créer la requête.
- É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.
- Accédez à l'onglet Données et cliquez sur Requêtes et connexions. Cela affichera un volet avec toutes les requêtes existantes.
- Cliquez avec le bouton droit sur la requête Ventes dans le volet Requêtes.
- Cliquez sur Actualiser.
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.
