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万行の縛りはありませんので、大量のデータを処理する場合に非常に便利使えるというわけです。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_09h44_51.png)
サンプルデータ
説明用に以下の3つのサンプルファイルを用意しました。
- ひらちんの部屋1.xlsx
- ひらちんの部屋2.xlsx
- ひらちんの部屋P.xlsx
ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxは、都道府県名(name)とDate(日付)、value(値)を持った同じ形のデータです。(意味はありません)
両ファイルとも、見出し1行と100万行のデータが入っています。
これを、ひらちんの部屋P.xlsxファイルでPowerPivotを使って集計したいと思います。
ワークシート上では、2つ足すと200万行超えちゃうので集計出来ませんね。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_09h46_57.png)
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_09h48_11.png)
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_09h47_25.png)
ちなみに、sample_dataフォルダに、ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxは格納されています。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_10h45_05.png)
やり方
では早速やっていきましょう!
流れは超簡単です。次のようになります。
- データモデルに取り込み
- Pivotテーブルを作成
以上
データモデルに取り込み
まずは、データモデル(データを溜めるところ)にデータを取り込みます。
ひらちんの部屋P.xlsxファイルの「データ」タブ>「データの取得」>「ファイルから」>「フォルダーから」と進みます。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_10h49_46.png)
フォルダを選ぶ画面が出てくるので、ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxのファイルが格納されているsample_dataフォルダを選んで「開く」を押してください。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h39_04.png)
次の画像のように、フォルダの中のファイル一覧が表示されますので、下の方の「結合」から、「結合および読み込み先」を選択します。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h42_31.png)
Fileの結合画面が出ます。
左のところのシートを押すと、データのプレビューが表示されます。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h45_44.png)
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h44_08.png)
OKします。
データのインポート画面が出ますので、「接続のみ」と、「このデータをデータモデルに追加する」にチェックを入れてOKします。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h49_06.png)
すると、次の図のような表示が出てきて、しばらく待っていると、読み込まれましたという表示に変わります。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_12h58_37.png)
データを視覚的に見るには、PowerPivotタブの管理に進みます。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h00_39.png)
別画面で、データモデルが表示されます。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h01_29.png)
name_idのフィルタで、空白以外に設定してみましょう。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h02_15.png)
一番下のデータを見ると、きっちり200万行目のデータまであることがわかります。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h03_00.png)
これで、データの読み込みは完了です。
Pivotテーブルを作成
では、Pivotテーブルを作成します。
「挿入」タブ>「ピボットテーブル」ここまでは普通と同じです。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h05_12.png)
分析データの選択時に「このブックのデータモデルを使用する」にチェックを入れてください。これで先程確認した、200万行のデータが、Pivotテーブルのデータになります。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h06_55.png)
OKすると、このようなピボットテーブルが作成されます。
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h08_28.png)
あとは普通と一緒ですね(^^)
集計したい項目をフィールドに入れるだけです!
![](https://hirachin.com/wp/wp-content/uploads/2021/03/2021-03-15_13h09_50.png)
まとめ
ACCESSを使わずに、1048576行以上のデータを集計する方法を紹介しました!
ACCESS嫌やねんってかたもこれで安心ですね(^^)
コメント