列のピボット解除
上図のような縦横の表、よくみなさん作りますよね。ただし、これは「出力」用です。見るための表です。ですから、これをいきなり作ってはいけません。最終的にこれを作るにしても、まずは「入力」用の"元データ"を作ります。元データはデータベース形式になっていなければいけません。元データがあれば、そこに対してさまざまな編集や計算や加工などの「計算」を行えます。Excelはそのように、「入力」→「計算」→「出力」の流れで考えるのが正しい使い方です。この流れを無視すると、とたんにExcelは牙をむきます。難易度が恐ろしく高まります。そして多くの方は、その状態で何とかしようとします。正しい使い方をすれば簡単なのに。さて、上図のような表をいきなり渡されて、ここから何かの処理を進めようとしても難しいでしょう。まずは、この表を作るための"元データ"である、下図のような表が欲しいです。
これ「取得と変換」を使うとできます。それが「列のピボット解除」です。ただし、最初にお伝えしておきます。凝ったことはできません。「列のピボット解除」は、どんなケースにも柔軟に対応できる魔法のテクニックではありません。ごく限られたケースにのみ使えるものだと思ってください。
さっそくやってみましょう。まず最初にお見せしたような縦横の表をPower Queryエディタで読み込むのですが、そのときには"テーブル形式"に変換されます。なので、先に「A列のタイトル」を入力しておくといいです。
これ、後で処理することもできますけど、先にセルへ入力しといた方が楽です。この状態で、表の中にアクティブセルを置き、[データ]タブの[テーブルまたは範囲から]をクリックします。
実行すると、この表がテーブル形式に変換されて、Power Queryエディタに読み込まれます。
さて、Power Queryエディタで読み込まれると、標準では上図のように、[名前]列が選択された状態になっています。ここ重要ですから、しっかり認識してください。選択されている[名前]列だけを見ると、ここはすでに表形式になっています。つまり、この[名前]列は"ピボット解除"しないところです。"ピボット解除"したいのは、それ以外の[A]列と[B]列と[C]列です。言ってる意味分かりますか？欲しいのは下図のような結果です。
Power Queryエディタで読み込んだ状態の[名前]列って、すでに下方向(行方向)に並んでいますよね。
対して、[A]列と[B]列と[C]列は現在、右方向(列方向)に並んでいます。これらを"ピボット解除"して、下方向(行方向)に変換したいんです。
さあ、よろしいですか。ここからは重要ですから、慎重に操作してくださいね。まず「[名前]列が選択されている」状態で、Power Queryエディタの[変換]タブを開き、[その他の列のピボット解除]をクリックします。
「その他の列～」というのは「(今選択されている列ではない)その他の列～」という意味です。つまり、ここでは[A]列と[B]列と[C]列のことです。実行すると、次のようになります。
素晴らしい！みなさん、ここは拍手するところです。(^o^)//パチパチ
タイトルが[属性]とか[値]になっていますので、ここは好きに変更しましょう。列のタイトルを変更するには、変更したいタイトルの列を選択して[名前の変更]をクリックします。
あるいは、変更したいタイトルを選択して、[F2]キーを押すと変更できます。お好きな方法で、お好きなタイトルに変更してください。
完成です。あとはこれを、シート上に読み込みます。このへんは、もう何度も解説していますから分かりますよね。
もちろん、元の表に新しいデータを追加すると対応します。
それだけではありません。列方向に新しい項目が増えても、ちゃんと対応します。
ここがポイントです！いま、列方向に新しい「D」を追加しました。すると、クエリの結果が望むようになりました。これは「列のピボット解除」をするときに「その他の列のピボット解除」を行ったからです。
もう一度、Power Queryエディタで「列のピボット解除」を実行したときのことを思い出してみましょう。どの列に対して"ピボット解除"を行ったのか。
今回指定したのは「[名前]列を除くすべての列」です。[名前]列でなければ、その列はピボットが解除されます。もちろん、今後登場するかもしれない新しい列であっても、それは「[名前]列ではない」のですから解除の対象になります。
もし、[A]列と[B]列と[C]列だけを選択して[選択した列のみをピボット解除]を実行すると、このときは同じ結果になります。しかし、今後もし新しい列が登場しても、その列は[A]列と[B]列と[C]列ではありませんから、解除の対象になりません。
試しに、[A]列と[B]列と[C]列だけを選択して[選択した列のみをピボット解除]を実行してみましょう。この時点では同じ結果になります。
しかし、新しいデータを追加したとき、望む結果にはなりません。
「選択した列だけ」を解除するのか、あるいは「選択した列ではない列すべて」を解除するのかによって動作が異なります。望む結果をイメージして、使い分けてください。