Power QueryとPower Pivotで1048576行の壁を打ち破るぞ！
「104万行」、正確には1048576行。
これは、EXCELの1シートに入力できる最大行数です。
以前に下記の記事で、この最大行数を超えてしまった場合の対処方法として、ACCESSと連携させる方法を紹介させてもらいました。
でも、世の中には「とにかくACCESSは使いたくない」「EXCELだけでなんとかしたい！」という人が沢山いることは事実です。
ACCESSという文字を見たところで、ページを閉じてしまった人もいるでしょう。
今回は、そんなひとのために、EXCELだけで104万行の壁を突破する方法を紹介したいと思います！
Power QueryとPower Pivot
Power Query(パワークエリ)とPower Pivot(パワーピボット)、EXCELのこの機能を使うことで、104万行の壁を突破したいと思います。
名前からしていかにも強そうですね(^^)
通常EXCELでは、シート上にデータを入力・記録して、様々な関数やピボットテーブル・グラフなどを利用しますが、Power Pivotの機能を使うことで、ワークシートでは無い領域（データモデル）にデータを記録出来るようになります。
そして、そのワークシートでは無い領域では、104万行の縛りはありませんので、大量のデータを処理する場合に非常に便利使えるというわけです。
サンプルデータ
説明用に以下の３つのサンプルファイルを用意しました。
- ひらちんの部屋１.xlsx
- ひらちんの部屋２.xlsx
- ひらちんの部屋P.xlsx
ひらちんの部屋１.xlsxとひらちんの部屋２.xlsxは、都道府県名(name)とDate(日付)、value(値)を持った同じ形のデータです。（意味はありません）
両ファイルとも、見出し１行と100万行のデータが入っています。
これを、ひらちんの部屋P.xlsxファイルでPowerPivotを使って集計したいと思います。
ワークシート上では、2つ足すと200万行超えちゃうので集計出来ませんね。
ちなみに、sample_dataフォルダに、ひらちんの部屋１.xlsxとひらちんの部屋２.xlsxは格納されています。
やり方
では早速やっていきましょう！
流れは超簡単です。次のようになります。
- データモデルに取り込み
- Pivotテーブルを作成
以上
データモデルに取り込み
まずは、データモデル（データを溜めるところ）にデータを取り込みます。
ひらちんの部屋P.xlsxファイルの「データ」タブ＞「データの取得」＞「ファイルから」＞「フォルダーから」と進みます。
フォルダを選ぶ画面が出てくるので、ひらちんの部屋１.xlsxとひらちんの部屋２.xlsxのファイルが格納されているsample_dataフォルダを選んで「開く」を押してください。
次の画像のように、フォルダの中のファイル一覧が表示されますので、下の方の「結合」から、「結合および読み込み先」を選択します。
Fileの結合画面が出ます。
左のところのシートを押すと、データのプレビューが表示されます。
OKします。
データのインポート画面が出ますので、「接続のみ」と、「このデータをデータモデルに追加する」にチェックを入れてOKします。
すると、次の図のような表示が出てきて、しばらく待っていると、読み込まれましたという表示に変わります。
データを視覚的に見るには、PowerPivotタブの管理に進みます。
別画面で、データモデルが表示されます。
name_idのフィルタで、空白以外に設定してみましょう。
一番下のデータを見ると、きっちり200万行目のデータまであることがわかります。
これで、データの読み込みは完了です。
Pivotテーブルを作成
では、Pivotテーブルを作成します。
「挿入」タブ＞「ピボットテーブル」ここまでは普通と同じです。
分析データの選択時に「このブックのデータモデルを使用する」にチェックを入れてください。これで先程確認した、200万行のデータが、Pivotテーブルのデータになります。
OKすると、このようなピボットテーブルが作成されます。
あとは普通と一緒ですね(^^)
集計したい項目をフィールドに入れるだけです！
まとめ
ACCESSを使わずに、1048576行以上のデータを集計する方法を紹介しました！
ACCESS嫌やねんってかたもこれで安心ですね(^^)
コメント