すでに作成されているデータ表を「行と列を入れ替えた表」に変換したいケースもあるだろう。このような場合にもパワークエリが活用できる。今回は、行と列の関係を逆にしてくれる「入れ替え」の使い方を紹介していこう。前回の連載で紹介した「フィル」にも応用できるので、その仕組みをよく学んでおくとよい。

  • 行と列の入れ替え、フィルの応用

行と列の関係を逆にする「入れ替え」

今回は、データ表の「行」と「列」の関係を逆にする「入れ替え」コマンドの使い方を紹介していこう。通常のExcelには用意されていない、パワークエリならではの機能といえるので、この機会に使い方を覚えておくとよい。

以下の図は、ある企業における「地域別の出荷量」を集計したものだ。この表の縦／横を入れ替えて、「地域」を左右に、「年」を上下に並べたい場合、どうすればよいだろうか？　いちいちデータを入力しなおすのは面倒な作業になるし、入力ミスを犯してしまう危険性もある。

  • 取得元のデータ表

このような場合に活用できるのが、パワークエリの「入れ替え」コマンドだ。その使い方を紹介していこう。まずは、データ表の範囲を「Power Query エディター」に取得する。続いて、「変換」タブにある「入れ替え」をクリックする。

  • 「入れ替え」の実行

すると、「行」と「列」を入れ替えた形にデータ表を変換することができる。このように、クリックひとつで行と列の関係を逆にできる機能が「入れ替え」となる。

  • 行と列を入れ替えたデータ表（失敗例）

ただし、上図をよく見ると、変換前に「列名」となっていた部分が欠落していることに気付くと思う。このため、「出荷地域」や「2016年」、「2017年」……、といった文字が抜け落ちたデータ表になってしまう。

この問題を解決するには、「列名」の部分もデータとして扱うように操作してから「入れ替え」コマンドを実行する必要がある。「転置されたテーブル」のステップを削除し、もういちど最初から作業をやり直してみよう。

最初に、「1行目をヘッダーとして使用」の▼をクリックし、「ヘッダーを1行目として使用」を選択する。

  • ヘッダーを1行目として使用

すると、「列名」の部分が降格され、データとして扱われるようになる。この状態にしてから「入れ替え」をクリックする。

  • 「入れ替え」の実行

このように操作すると、もともと「列名」だった部分を含めて、データ表の縦／横を入れ替えることが可能となる。その後、「1行目をヘッダーとして使用」をクリックし、1行目のデータを「列名」に昇格させる。

  • 1行目をヘッダーとして使用

以上が、データ表の「行」と「列」を入れ替えるときの基本的な操作手順となる。

なお、1行目のデータを「列名」に昇格させると、各列のデータ型が自動的に再判別されることにも注意しておく必要がある。今回の例の場合、「2016年」や「2017年」などのデータが「日付」型のデータと判別されてしまったため、「2016/1/1」や「2017/1/1」といった表記に変更されている。これを元に戻すには、データ型を「テキスト」に修正してあげる必要がある。

  • データ型の変更

このデータ表をExcelに出力した例も紹介しておこう。左右に「地域」、上下に「年」が並ぶデータ表（テーブル）が作成されていることを確認できるだろう。

  • Excelに出力したデータ表

出力されたデータ表を「テーブル」ではなく、「通常の表」として扱いたい場合もあるだろう。この場合は「テーブル デザイン」タブにある「範囲に変換」をクリックすればよい。これでテーブルを解除できる。

  • テーブルの解除（範囲に変換）

あとは、書式を自由に指定しなおすだけ。これで元のデータ表と同じような書式に仕上げることができる。

  • 表の書式を指定

なお、いちどパワークエリを経由すると、数式や関数の処理結果が「通常のデータ」として扱われることにも注意しなければならない。上図に示した例の場合、「合計」の値は関数SUMではなく、単なる数値データとして扱われている。

これはパワークエリの仕様によるものだ。数式や関数を含むデータ表を「Power Query エディター」に取得すると、数式や関数は無視され、その処理結果だけが数値データ（または文字データ）として読み込まれる仕組みになっている。当然ながら、Excelに出力したデータ表にも数式や関数は存在しなくなる。

このため、出力したデータ表の数値を書き換えても「合計」の値は何も変化しない。「合計」を自動的に再計算させるには、「自分で関数SUMを入力しなおす」などの対策が必要だ。後に大きなミスに発展してしまう可能性もあるので、勘違いしないように注意しておく必要があるだろう。

「フィル」で横方向にデータを補完する

前回の連載で紹介した「フィル」を使用する際にも、「入れ替え」コマンドが役に立つケースがある。続いては、以下の図に示したデータ表をパワークエリで処理する場合を考えてみよう。

  • セルを横方向に結合したデータ表

このデータ表には「横方向に結合したセル」が含まれている。この状態のまま「Power Query エディター」にデータを取得すると、“先頭セル”以外はnull（空白）としてデータが取得される。

  • 「Power Query エディター」に取得されたデータ

これらのnullは“適切なデータ”に補完しておくのが基本といえるが、残念ながら「フィル」コマンドには横方向にデータを補完する機能が用意されていない。

このような場合は、先ほど示した手順でデータ表の「行」と「列」を入れ替えてあげると、「フィル」コマンドを利用できるようになる。単に「入れ替え」を行うだけでなく、ヘッダーの降格／昇格といった作業も必要になるので、忘れないように注意すること。

  • 行と列を入れ替えたデータ表

「行」と「列」を入れ替えたら、データを補完する列を選択して「フィル」→「下へ」を実行する。これでnullを“すぐ上にあるデータ”（元々は左にあったデータ）で補完することができる。

  • 「フィル」→「下へ」でデータを補完

  • フィルにより自動補完されたデータ

最後に、もういちど「行」と「列」を入れ替えて、データ表を元の形に戻してあげる。これで横方向のデータ補完を実現できる。なお、この際にもヘッダーの降格／昇格が必要となる。忘れないように注意しておこう。

  • 行と列を元に戻したデータ表

「フィル」で縦と横の両方にデータを補完する

さらに応用的な例として、以下の図に示したデータ表をパワークエリで処理する場合を考えてみよう。今度の例には「横方向」だけでなく、「縦方向」や「縦横両方」に結合したセルが含まれている。

  • セルを縦・横に結合したデータ表

このデータ表を「Power Query エディター」に取得すると、以下の図のようになる。“先頭セル”以外はnull（空白）としてデータが取得されることを確認できるだろう。

  • 「Power Query エディター」に取得されたデータ

これらのnullを補完するには、縦方向と横方向の両方で「フィル」を実行しなければならない。ということで、まずは縦方向のフィルを実行してみよう。「Ctrl」＋「A」キーを押して、すべての列を選択し、「フィル」→「下へ」を実行する。すると、以下の図のような結果になる。

  • フィルにより自動補完されたデータ（失敗例）

「新宿」と「池袋」の部分は適切にデータが補完されているが、それ以外の部分は“不適切なデータ”が補完されてしまう。よって、この方法は失敗となる。「下方向へコピー済み」のステップを削除して、最初の状態に戻しておこう。

縦と横の両方にデータを補完するときは、その作業順にも注意しなければならない。今回の例の場合、先に横方向のデータ補完をするのが正解だ。よって、データ表の「行」と「列」を入れ替えてから「フィル」を実行する必要がある。

  • 行と列を入れ替えてから「フィル」を実行

その後、もういちど「行」と「列」を入れ替えて元の状態に戻すと、以下の図のような結果が得られる。

  • 行と列を元に戻したデータ表

これで先の失敗を回避できるようになる。あとは、そのまま「フィル」→「下へ」を実行して、nullの部分を補完すればよい。

  • 「フィル」→「下へ」でデータを補完

すると、以下の図のような結果になり、セルが結合されていた部分を“同じ値”で埋めることが可能となる。

  • フィルにより自動補完されたデータ

このように縦・横の両方で「フィル」を実行するときは、その作業順にも配慮しておく必要がある。ただし、どの順番が正解になるかは状況により変化する。「縦方向のデータ補完」を先に行うのが正解のケースもあれば、今回の例のように「横方向のデータ補完」を先に行わなければならないケースもある。さらには、「どちらを先に行っても上手くいかない……」というケースもある。

適切に対応するには状況を正しく見極める必要があるが、そのロジックは意外と複雑で「よくわからない……」という方もいるだろう。このような場合は、事前にセルの結合を解除して、手作業で空白セルを埋めてから「Power Query エディター」にデータを取得したほうが確実かもしれない。

「セルの結合」が含まれるデータ表は、何十件ものデータがある“大きな表”ではなく、データが数件程度の“小さな表”である場合が多い。であれば、複雑なロジックを考えるよりも、手作業でデータを補完した方が短時間で作業を終えられるかもしれない。こういった考え方も含めて、状況に応じた対処方法をとる必要がある。頻繁に生じる問題ではないが、参考にして頂ければ幸いだ。