Excel“104万行の壁”を越えろ Power Query活用のススメ

Excelには約104万行の行数制限があるが、Power Queryを使えばこの上限を超えられるらしい。本当なのだろうか。ChatGPTで生成したテストデータで挑む。

[村田知己，キーマンズネット]

　多くの社会人が毎日のように使う「Microsoft Excel」（以下、Excel）。使いやすさと豊富な機能が特徴だが、実は「104万8576行」の行数制限がある。しかし、限界があると超えたくなるのが人の常。本稿では、Excelに組み込まれている「Power Query」を使用してこの限界の突破を試みる。

ChatGPTが生成した大量のデータをExcelで読み込んでみた

　Power Queryは、Excelや「Power BI」で利用できる、外部データのインポートや接続、加工、結合などを可能にする機能群だ。どうやら、この機能を使えば“条件付き”でExcelの制限を超えるデータを読み込めるらしい。

テストデータを入手するのも一苦労

　検証を始める前に、まずは104万行以上の検証用データを作らなければならない。そんな大量のデータをどこで手に入れれば……と思い検索してみると、どうやら「ChatGPT」の新機能「Advanced Data Analysis」を使えば、ChatGPT上でPythonを実行してテストデータを生成できるらしい。便利な世の中になったものだ。

　幸い筆者は有料版のChatGPTに加入していたので、早速プロンプトを投げてみる。

ChatGPTに1000万行のデータ生成を頼んでみた

　どうやら“1000万行”は調子に乗りすぎたようだ……。この後、100万行ずつのバッチ処理も頼んでみたが、やはり処理に時間がかかりすぎて断られてしまった。1000万行は諦めて200万行で再チャレンジする。

ChatGPTに200万行で再チャレンジ

　今度はうまくいったようだ。生成したのは架空のWebサービスのログで、ログの記録日時やユーザーID、イベントのタイプ、イベントが検出されたアイテムのID、ユーザーが使用したブラウザが格納された200万行×5列のデータだ。CSVファイルにもかかわらずファイルサイズは150MBあり、テキストエディタで開くのに1分以上かかった。本当にこんな大きいデータをExcelで読み込めるのか……。一抹の不安が残るが、本題の検証に進もう。

いよいよ200万行のデータ読み込みに挑戦

　通常、Excelでデータを開く時はExcelファイルやCSVファイルをダブルクリックしたり、Excelの「ファイル」メニューから「開く」機能を利用したりするが、この方法では約104万行を超えるデータは開けない。

　Power Queryのデータ取得機能を使用すれば、条件付きでこの制限を突破できる。まず、任意のExcelファイルを開き、「データ」メニューからデータを読み込むと、Power Queryのデータ取得機能が起動する。

　筆者が使用する「Excel 2016」の場合、「データ」→「新しいクエリ」→「ファイルから」で任意のファイルを選択すれば、Power Queryでデータを読み込める。

Excel 2016でPower Queryを起動するメニュー

　Excelの行数制限を超えるデータを読み込みたい場合は「読み込み」ボタンの横の下矢印メニューから「読み込み先…」を選び、「接続の作成のみ」を選択して読み込む必要がある。さらに「このデータをデータモデルに追加する」にチェックを入れておくと、読み込んだデータをピボットテーブルの作成などに利用できる。

（左）「読み込み先…」を選び、（右）「接続の作成のみ」を忘れずに選択する

無事200万行のデータを読み込めた

　「接続の作成のみ」を選択すると、データはPower Queryでのみ読み込まれ、Excelのシートには表示されない。実は、Excelの行数制限は「シートに表示できる行数の制限」なので、この方法で読み込めば上限を超えたデータを読み込むことができるというわけだ

データの加工は「Power Queryエディター」を使う必要アリ

　上述したように、Power Queryを使用して約104万行を超えるデータを読み込んだ場合、Excelのシートにデータを表示させられないため、関数等でのデータ加工ができない。そこで使用するのが「Power Queryエディター」だ。

Power Queryエディターのイメージ

　Power Queryエディターでは行／列の削除やフィルター、置換などの基本的な操作から、ExcelであればVLOOKUP関数を使うデータの結合、Excelでは少々面倒なグループ化まで、GUIで簡単に実現できる。加工のプロセスは画面右側に記録され、任意のタイミングに戻ることも可能だ。慣れれば関数で加工するよりも使いやすいので、今回のような大量のデータでなくてもぜひ試してみてほしい。

ピボットテーブルも作成可能

　データを読み込む際に「このデータをデータモデルに追加する」にチェックを入れておくと、読み込んだデータをピボットテーブルの作成などにも利用できる。

　データを読み込んだ状態で「挿入」→「ピボットテーブル」を選択し、「このブックのデータモデルを使用する」にチェックを入れてピボットテーブルを作成する。そうすると、「ピボットテーブルのフィールド」に読み込んだデータが表示されるはず。あとはいつも通り指標を当てはめていくだけだ。

Power Queryで読み込んだデータでピボットテーブルを作ってみた

　以上の操作方法は使用するExcelのバージョンによって異なるため、詳しくはMicrosoftのサポートページを参照してほしい。

　ここまで見てきたように、Excelには約104万行の行数制限があるが、Power Queryを利用すればその制限を超えた行数のデータを読み込める。Microsoftによれば、データモデルの最大サイズは「Power Queryによって制限されない」とのことだが（注1）、クエリの数が増えると読み込みや加工にかなり時間がかかる。使用する端末のスペックに配慮しながら使う必要がありそうだ。

　長い社会人人生。「分析環境も整ってないし教えられる人もいないから取りあえずExcelでやってみて」と大量のデータを渡される日も来るかもしれない。その時は今回紹介した内容を思い出してほしい。

注1：Power Queryの仕様と制限（Microsoft）