フォルダ内のデータを結合する
任意のフォルダ内に、複数のデータが存在していたとします。それらのデータを1つのリストに結合(合体)させてみましょう。まずは、次のようなケースで解説します。
上図のように、任意のフォルダ(ここではC:\Work\とします)に、複数のCSVファイルがあります。言わなくても分かると思いますが、それぞれのCSVには、それぞれ月別のデータが入力されています。基幹システムやサーバーから毎月提供されるような感じです。
こうした複数のデータを結合するのって、実務では"あるある"でしょう。毎月これを業務として(手作業で)やっている人も多いはずです。「取得と変換」を使うと、もうそんな苦労は必要なくなるんですよ。では、やってみましょう。
[データ]タブ[データの取得]をクリックして[フォルダーから]を選択します。
フォルダを選択するダイアログボックスが表示されますので、今回CSVファイルが保存されているフォルダを[参照]ボタンで選択します。ちなみに、あらかじめフォルダのパスをコピーしておいて、それを貼り付けるのが簡単です。
[OK]ボタンをクリックすると、フォルダ内に存在するすべてのファイルが表示されます。特定のファイルだけ扱うことも可能ですが、まずはすべてのCSVファイルを結合してみます。
[編集]ボタンをクリックすると、この状態でPower Queryエディタが開きますが、そこから手動で複数のCSVファイルを結合させるのは難しいので、クリックしない方がいいです。また[読み込み]ボタンも、この状態がワークシートに代入されるだけで意味がないですから、クリックしない方がいいです。使うのは左端の[結合]ボタンです。ここには3つの選択肢が用意されています。
どれを選択しても、一度確認画面が表示されるのですが、確認画面の後が次のように変わります。
- 結合および編集 → 結合した状態でPower Queryエディタが開きます
- 結合および読み込み → 結合した表を新しいワークシートに展開します
- 結合および読み込み先 → 結合した表をどうするか選択できます
列を削除するなど、結合した表に対して何かの編集をしたいのでしたら「結合および編集」でPower Queryエディタを開くといいでしょう。もう、いきなり読み込んでいいのなら「結合および読み込み」が簡単です。一度接続のみを作って、さらに何か別のことをしたいのでしたら3番目の「結合および読み込み先」を選択します。今回は2番目の「結合および読み込み」で、いきなりワークシートに読み込みます。
[結合および読み込み]ボタンをクリックすると、「こんな感じだけどいいかな？」みたいに、CSVの内容が表示されます。まぁ、特別な場合を除いて、ここは普通に[OK]ボタンをクリックでしょう。
実行すると、フォルダ内のCSVファイルが結合されて新しいワークシートに展開されます。
もちろん、すべてのデータが結合されています。
試しに、元のCSVを編集してみましょう。
[更新]ボタンをクリックするか、Alt+[F5]キーを押すと反映されます。ここに数式などは使われていないことに留意してください。
さて、当たり前ですが、実務では翌月に新しいCSVファイルが提供されます。それを同じフォルダに入れます。ただ保存するだけです。
はい、データも追加されました。
簡単ですね～驚きですね～これを毎月やっていた人、絶対にいますよね～もう、そんな作業しなくていいんですよ。
結合するデータを編集する
ここまでは、最も簡単な操作を紹介しましたけど、たとえば「左端のファイル名はいらないんだよね～」みたいなことは多いですよね。なので、編集する手順をご紹介します。といっても簡単なんですけど。
今読み込んでいるクエリを編集します。やり方は3つありますので、お好きな方法でどうぞ。クエリにマウスポインタを合わせるとプレビュー画面が表示されます。その下に[編集]ボタンがあるので、それをクリックします。
あるいは、クエリを右クリックして表示されるメニューから[編集]を選択してもいいです。
最も簡単な方法は、クエリをダブルクリックします。いずれの操作でも、そのクエリがPower Queryエディタで表示されます。
削除したい列を選択して[列の削除]ボタンをクリックします。
列を入れ替えたいなら、そうしてください。
[閉じて読み込む]ボタンをクリックすれば反映されます。
特定のファイルだけ結合する
フォルダの中に、結合したいファイルだけが存在しているのならいいですが、結合したくないファイルが保存されている場合もあるでしょうね。
ここでは「2019」で始まるファイルだけを結合してみます。まずは、上記の手順でフォルダを指定してください。
ファイルの一覧が表示されるので、今度は[結合および編集]を選択します。
Power Queryエディタが開きます。
右下にある「適用したステップ」で一番上の「ソース」を選択します。
この「適用したステップ」とは、指定されたファイルなどに対して「取得と変換」機能が、順番に処理をした内容です。「ソース」を選択すると、一番最初に読み込んだファイルの一覧が表示されます。ファイル名が表示されている2列目の「Name」右にある▼ボタンをクリックします。Excelで使い慣れたオートフィルタだと思えばいいです。
オートフィルタみたいなリストが表示されるので、オートフィルタと同じ要領で[テキストフィルター]を選択し[指定の値で始まる]をクリックします。
すると「ステップの挿入」を確認するメッセージが表示されます。ステップとは、上に書いたように、「取得と変換」がデータに対して順番に行った操作です。上流のステップを変更することで、下流のステップに影響が出る場合もあります。なので、その確認です。今回はもちろん[挿入]ボタンをクリックします。実行すると「行のフィルター」画面が表示されます。これも、オートフィルタと同じに考えればいいでしょう。
今回は「2019で始まる」という指定にしました。「適用したステップ」の一番下「変更された型」をクリックします。この「変更された型」が何を意味するかは、あまり考えない方がいいでしょう。
ちなみにですけど、CSVの中で項目の順番が異なっていても、タイトルが同じだったら結合できます。
ブック同士も結合できる
ここまでは複数のCSVファイルで話を進めてきましたが、CSVだけでなく、複数のブックを結合することも可能です。
ちなみに、それぞれのブックはこんな感じになっています。テーブルだけではなく、普通のワークシートでもOKです。
CSVのときと同じように、フォルダを指定します。Excelが複数のファイル名(ブック名)を表示しますので、[結合および読み込み]を実行します。CSVのときは、CSVの内容が表示されました。ブックの場合は、次のような画面が表示されます。
ここで、読み込むシート名またはテーブル名を指定します。CSVと同じ手順で結合できます。
注意しなければいけないのは、読み込むシート名が統一されていなければならないということです。たとえば、1つだけ次のようにシート名が異なっていると、エラーになって結合はできません。
ブック内のテーブルを結合するときも、次のように結合したいテーブルを選択するのですが、
やはり、テーブル名が統一されていないとエラーになります。このへんは、もしかすると何か手があるかもしれません。もし回避する方法が分かったら追記します。