Convertir la matrice en tableau à 3 colonnes ('reverse pivot', 'unpivot', 'flatten', 'normalize'))

je dois convertir la matrice Excel FIRST dans le tableau LATER :

PREMIÈRE : 

    P1  P2  P3  P4
F1  X
F2  X   X
F3      X       X
F4      X   X

plus tard : 

F   P   VALUE
F1  P1  X
F1  P2
F1  P3
F1  P4
F2  P1  X
F2  P2  X
F2  P3
F2  P4
F3  P1
F3  P2  X
F3  P3
F3  P4  X
F4  P1
F4  P2  X
F4  P3  X
F4  P4
27
demandé sur pnuts 2013-12-12 15:11:20

2 réponses

à "reverse pivot", "unpivot" ou "flatten":

  1. Pour Excel 2003: Activer une cellule de votre tableau de synthèse et choisissez Données - Rapport de tableau croisé dynamique:

    SO20541905 first example

pour les versions ultérieures, accédez au Magicien avec Alt + D , P .

pour Excel pour Mac 2011, c'est ⌘ + Alt + P ( voir ici ).

  1. sélectionnez multiple consolidation ranges et cliquez sur suivant .

    SO20541905 second example

  2. dans "Étape 2a de 3", Choisissez je vais créer les champs de la page et cliquez sur .

    SO20541905 third example

  3. dans" Étape 2b de 3 "spécifiez la plage de votre tableau sommaire dans le champ Plage (A1:E5 pour les données de l'échantillon) et cliquez sur ajouter , puis suivant .

    SO20541905 fourth example

  4. dans" Étape 3 de 3", Sélectionnez un emplacement pour la table de pivotement (la feuille existante devrait servir, car le PT n'est requis que temporairement):

    SO20541905 fifth example

  5. , Cliquez sur Terminer pour créer le tableau croisé dynamique:

    SO20541905 sixth example

  6. forer vers le bas (c'est-à-dire double-cliquer) sur l'intersect des grands totaux (ici la cellule V7 ou 7 ):

    SO20541905 seventh example

  7. le PT peut maintenant être supprimé.

  8. la Table résultante peut être convertie en un tableau conventionnel de cellules en sélectionnant Table dans le Menu rapide (clic droit dans le tableau) et convertir à la gamme .

il y a une vidéo sur même sujet à lancer Excel que je considère excellente qualité.

49
répondu pnuts 2015-10-15 22:41:33

une autre façon de déballer des données sans utiliser VBA est avec PowerQuery, un add-in Gratuit pour Excel 2010 et plus, disponible ici: http://www.microsoft.com/en-us/download/details.aspx?id=39379

installer et activer L'add-in de requête de puissance. Puis suivez ces étapes:

ajouter une étiquette de colonne à votre source de données et la transformer en une table Excel via Insert > Table ou Ctrl - T .

enter image description here

sélectionnez n'importe quelle cellule dans la table et sur le ruban de requête D'alimentation cliquez "De La Table".

enter image description here

ceci ouvrira la table dans la fenêtre de L'éditeur de requête de puissance.

enter image description here

, Cliquez sur l'en-tête de colonne de la première colonne pour la sélectionner. Puis, sur le transformer ruban, cliquez sur le colonnes Unpivot menu déroulant et sélectionnez colonnes UNPIVOT autres .

pour les versions de Power Query qui n'ont pas la commande Unpivot autres colonnes , sélectionnez Toutes les colonnes sauf la première (en utilisant Shift-click sur les en-têtes de colonne) et utilisez la commande Unpivot .

enter image description here

le résultat est une table plate. Cliquez sur fermer et charger sur le ruban intérieur et les données seront chargées sur une nouvelle feuille Excel.

enter image description here

maintenant à la bonne partie. Ajoutez quelques données à votre table source, par exemple

enter image description here

cliquez sur la feuille avec la table de résultat de requête de puissance et sur le ruban de données cliquez Rafraîchir tous . Vous verrez quelque chose comme:

enter image description here

Power Query n'est pas une simple transformation. Il est reproductible et peut être relié à des données qui changent de façon dynamique.

19
répondu teylyn 2014-12-27 21:10:28