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）
Copyright © ITmedia, Inc. All Rights Reserved.
編集部コラム
この記事の著者
関連記事
こんなメディアも見られています
キーマンズネットに関連する情報をお探しであれば、こちらのメディアもお役に立てるかもしれません。
SpecialPR
アクセスランキング
-
1
Windowsを使い続けるのは正解か？ 「Linux化」というモダナイズのもう一つの選択肢
-
2
「Gemini Notebook」で利用者10倍 シニア社員をAIヘビーユーザーにした首都高の考え
-
3
スクエニ開発陣が「ドラゴンクエストX」の世界観を守るのに「Gemini」が必要だったワケ
-
4
「新しいOutlook」って使ってる？ 移行で変わるメール誤送信対策
-
5
できる人ほど教える時間がない AIは暗黙知の夢を見るか
-
6
企業買収を20年以上続ける「Infor」 9つのERPを製品表でチェック
-
7
舞鶴市「脱Windows」で6億円削減 1人1日82分を時短したGoogleツールの使い方
-
8
SCS評価制度、我が社はどこに気を付けたらいいの？ 乗り遅れないためのポイント解説
-
9
製造業の73％が「生成AIに仕事を頼みたいのに頼めない」理由とは
キーマンズネット SNS
@Keymansをフォロー
インフォメーション
注目情報をチェック
キーマンズネットをフォロー