複数のExcelブックを１つのファイルにまとめる時、すべてのExcelブックを開いてコピペを繰り返す必要があるので、大量のデータがある場合は、かなり時間がかかってしまいます。
こんな時は、複数のファイルを１つのフォルダにまとめ、フォルダごとデータを取り込んで１つのデータに結合します。
Excelブックの結合は、エクセルの表の見出しがすべて同じであれば、利用することができます。
この記事では、大量のExcelブックを一括で１つにまとめて取り込む方法を紹介します。
複数のExcelブックをコピペして結合すると時間がかかる
複数のExcelブックを１つにまとめる際、皆さんはどのようにデータを加工していますか？
例えば、下のように、「売上高１月」～「売上高１２月」の１２個のExcelブックを、「売上高2024年」というファイルにまとめる場合などです。
この場合、まず「売上高2024年」という新規ファイルを作成します。
続いて、売上高１月のデータを貼り付け、その下に売上高２月のデータを、さらにその下に・・・といった具合に、１２個のExcelブックを最下行にコピペし続ける必要があります。
確かに、この方法でも１２個のファイルを１つにまとめることができます。
しかし、元データの一部をコピペし忘れたり、見出し行を省く手間がかかるなど、思った以上に時間がかかってしまいます。
手作業で大量に貼り付けると全部貼り付けたか不安になるよね。
複数のExcelブックを結合するための条件
複数のExcelブックをフォルダごと取り込んで結合する際、元データはどんな形でも大丈夫というわけではありません。
表の形には、守るべき２つの条件があります。
まず、１つ目は、表の行見出しが同じ並び順であることです。
下の例のように、B列からE列までの見出しの並びが異なるデータ同士を結合すると、各列に異なる要素のデータが混在してしまいます。
２つ目は、取り込むための元のExcelブックは、１つだけのワークシートにデータが入力されているということです。
下の例のように、「表」シート以外にも、「表２」シートや「まとめ」シートにデータが入力されていると、結合する際、対象外データを取り除くのに手間がかかってしまいます。
なお、データが入力されていないワークシートであれば、取り込みに問題は発生しません。
複数のExcelブックを結合してまとめる方法と手順
それでは、実際に複数のExcelブックをフォルダごと取り込んで結合する方法を紹介します。
今回紹介するサンプルデータとして、以下の１２個のExcelファイルを結合します。
それぞれの各ファイルのデータ内容は下の画像のとおりです。
ファイルには「表」シートだけがあり、表の行見出しの並び順はどれも「地域→販売店舗→商品名→年月→販売数量⇒販売単価⇒販売高」となっています。
１行目とA列にデータ入力がない列と行がありますが、結合時に問題は発生しません。
それでは、下の手順に沿って、複数のExcelファイルを１つに結合していきます。
手順１．全ファイルを１つのフォルダにまとめる
まずは、取り込み用のフォルダを作成し、対象のExcelブックをすべて保管してください。
手順２．フォルダのデータを取得して結合
結合するために新規でExcelファイルを開きます。
データを取り込むため、「データ」タブの「データの取得」ボタンから、「ファイルから」⇒「フォルダから」を選択しクリックします。
「参照」画面が表示されるので、手順１で作成したフォルダを選択して「開く」を押します。
フォルダに保管したExcelファイルがすべて表示されていることを確認して、「データの結合と変換」をクリックします。
「ファイルの結合」画面が表示されるので、「パラメーター１」というフォルダを選択してから、画面右下の「OK」を押します。
そうすると、「PowerQueryエディター」という画面が表示されるので、以降のステップで結合するデータを加工していきます。
手順３．不要な取り込みデータを削除
続いて、「PowerQueryエディター」画面で不要なデータを削除していきます。
まず、取り込んだデータをすべて表示させる必要があります。「Data」列のヘッダーにある⇦⇨ボタンを押して、データを展開させるために「OK」を押してください。
取り込んだすべてのデータが画面に表示されます。
続いて、取り込んだシート名が表示されている「Name」列のフィルターボタンをクリックして、取り込みに関係のない「xlmn._FilterDatabase」という項目のチェックを外します。
手順４．不要な列と行を削除/非表示
今度は、取り込みたくない不要な列を削除します。
「PowerQueryエディター」画面で表示されているデータは、すべてエクセルのセルに表示されることになるので、不要な列は削除しておきます。もちろん、データを取り込んだ後に削除しても問題はありません。
まず、不要な列を「Ctrl」キーを押しながらすべて選択してから、見出しの上で右クリックを押します。
その後、表示された右クリックメニューの中から「列の削除」を押します。
続いて、不要な行を非表示にします。
取り込んだエクセルに空白行がある場合は、下のように、「null」という文字が表示された行が発生するので、この行が存在するか目視で確認してください。
「null」の行がある場合は、ヘッダーのプルダウンリストから、「null」のチェックを外して、対象の行を非表示にしてください。
手順５．見出し行を１行だけ残す
手順４が終わった段階では、それぞれのExcelファイルから取り込んだ見出し行が１２行残っているので、調整していきます。
まず、１行目に見出しが表示されていることを確認してから、「変換」タブの「１行目をヘッダーとして使用」をクリックしてください。
そうすると、１行目の見出し行がPowerQuery画面のヘッダーに変わります。
残りの見出し行は非表示にするために、ヘッダーの任意のプルダウンを使って、見出しの項目のチェックを外して、非表示にします。
手順６．書式(日付など)を調整
次に、各列の書式を変更します。
特に日付列をそのまま取り込むと、値として取り込まれてしますので、日付列に設定する必要があります。
ヘッダーの左についている書式ボタンを押して、「日付」をクリックします。
そうすると、表示形式が値から、日付に変更されます。もちろん、ファイルを結合した後でも書式の変更は可能です。
手順７．閉じて読み込む(完成)
以上で、PowerQuery画面での操作がすべて完了しました。
Excelのトップ画面に結合したデータを表示させるため、「ホーム」タブの「閉じて読み込む」ボタンを押してください。
そうすると、１２のExcelファイルが１つに結合されて読み込まれるので、それぞれのExcelが取り込まれている確認してください。
また、画面右側に「クエリの接続」画面が表示されるのは、この取り込んだデータが「PowerQuery」機能と接続されているためです。
また、取り込んだことにより、自動で「テーブル」機能が設定され、自動でフィルターや配色が設定されます。
この「クエリの接続」と「テーブル」機能を削除したい場合は、次の手順８を行ってください。
⇩テーブル機能とは
手順８．クエリとテーブル機能の解除
「クエリの接続」と「テーブル」機能を削除したい場合は、「テーブルデザイン」タブの「範囲に変換」ボタンをクリックします。
「テーブル」機能を解除すると、セルの色や罫線の色を自在に変更することが出来ます。
以上で、１２個のExcelファイルの結合処理がすべて完了しました。
今回紹介したフォルダごとExcelファイルを取り込む方法は、細かい手順をいくつか行う必要があるので、最低でも４つ以上のファイルを取り込むときに利用することをおすすめします。
複数シートのデータを結合したい場合
今回は複数のExcelファイルを１つに結合する方法を紹介しました。
もし、１つのExcelファイルの複数のシートのデータを１つに結合したい場合は、「統合」機能を使います。
以下の記事で、使い方を詳しく紹介していますので、合わせて参考にしてみてください。
