【EXCEL | ノート】Power QueryとPower Pivotで1048576行の壁を打ち破るぞ!

スポンサーリンク

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万行の縛りはありませんので、大量のデータを処理する場合に非常に便利使えるというわけです。

Power Pivot タブの場所

Microsoft(マイクロソフト)本家の説明はこちら

https://support.microsoft.com/ja-jp/office/power-pivot-概要と学習-f9001958-7901-4caa-ad80-028a6d2432ed

サンプルデータ

説明用に以下の3つのサンプルファイルを用意しました。

  • ひらちんの部屋1.xlsx
  • ひらちんの部屋2.xlsx
  • ひらちんの部屋P.xlsx

ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxは、都道府県名(name)とDate(日付)、value(値)を持った同じ形のデータです。(意味はありません)

両ファイルとも、見出し1行と100万行のデータが入っています。

これを、ひらちんの部屋P.xlsxファイルでPowerPivotを使って集計したいと思います。

ワークシート上では、2つ足すと200万行超えちゃうので集計出来ませんね。

ひらちんの部屋1.xlsx

ひらちんの部屋2.xlsx

ひらちんの部屋P.xlsx

ちなみに、sample_dataフォルダに、ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxは格納されています。

やり方

では早速やっていきましょう!

流れは超簡単です。次のようになります。

  1. データモデルに取り込み
  2. Pivotテーブルを作成

以上

1.データモデルに取り込み

まずは、データモデル(データを溜めるところ)にデータを取り込みます。

ひらちんの部屋P.xlsxファイルの「データ」タブ>「データの取得」>「ファイルから」>「フォルダーから」と進みます。

フォルダを選ぶ画面が出てくるので、ひらちんの部屋1.xlsxとひらちんの部屋2.xlsxのファイルが格納されているsample_dataフォルダを選んで「開く」を押してください。

次の画像のように、フォルダの中のファイル一覧が表示されますので、下の方の「結合」から、「結合および読み込み先」を選択します。

Fileの結合画面が出ます。

左のところのシートを押すと、データのプレビューが表示されます。

OKします。

データのインポート画面が出ますので、「接続のみ」と、「このデータをデータモデルに追加する」にチェックを入れてOKします。

すると、次の図のような表示が出てきて、しばらく待っていると、読み込まれましたという表示に変わります。

データを視覚的に見るには、PowerPivotタブの管理に進みます。

別画面で、データモデルが表示されます。

name_idのフィルタで、空白以外に設定してみましょう。

一番下のデータを見ると、きっちり200万行目のデータまであることがわかります。

これで、データの読み込みは完了です。

2.Pivotテーブルを作成

では、Pivotテーブルを作成します。

「挿入」タブ>「ピボットテーブル」ここまでは普通と同じです。

分析データの選択時に「このブックのデータモデルを使用する」にチェックを入れてください。これで先程確認した、200万行のデータが、Pivotテーブルのデータになります。

OKすると、このようなピボットテーブルが作成されます。

あとは普通と一緒ですね(^^)

集計したい項目をフィールドに入れるだけです!

まとめ

ACCESSを使わずに、1048576行以上のデータを集計する方法を紹介しました!

ACCESS嫌やねんってかたもこれで安心ですね(^^)