「取得と変換」を使えば、複数のファイル(CSV)を簡単に1つにまとめることができます
Excel2016から追加された新機能「取得と変換」を使えば、「集計作業」の効率を上げることができます。
- 「取得と変換」を使えば、フォルダー内のデータを結合できます
- 日々の集計ファイルを集めて(結合して)「月」や「年」の集計する
- データの取得と変換は、Excel2016から追加された機能です
- CSVファイルを結合する
- 毎月追加されるファイル(CSV)が自動的に "結合" される
「取得と変換」を使えば、フォルダー内のデータを結合できます
集計の作業で、複数のファイル(CSV)を合わせて(結合して)1つの集計表にする作業はとても手間のかかる作業です。
Excel2016から追加された「取得と変換」を使えば、複数のファイル(CSV)を簡単に結合することができます。
ファイル(CSV)を「1つ1つ切り貼りして1つのファイルにする」のは手間のかかる作業です。
また、"取得と変換" を使えば集計する時の "ルール"「特定の名前のファイル、特定のフィールド(項目)だけを結合する」作業が「クエリとして保存」されます。
同じ作業を繰り返す場合は、「クエリ」と接続して "更新" するだけです。
日々の集計ファイルを集めて(結合して)「月」や「年」の集計する
日々の集計ファイル(CSV)を1つに連結(結合)して、「月」や「年」の「1つの表」を作成する作業は大変です。
しかも、日々(月付)のファイルが追加されるので、切り貼りしていく方法では大変です。
複数の人(部署)が作成したファイル(CSV)を集計する
複数の「人」や「部署」が集計表を作成して、それを1つの集計表にする作業も、ファイル(CSV)の "様式を統一して、1つのフォルダーに保存" するようにすれば多くのファイルでも簡単に "1つに結合" することができます。
会計システムのDATAをエクセルファイルにする
「会計システム」には、会計データを "CSV" として書き出す機能が備わっています。
このファイル(CSV)を使って、月々の変化を分析する会計資料等を作成するためには、月別のファイルを切り貼りして1つの(通月の)ファイルにする作業が必要です。
このような場合も、取得と変換を使えば簡単に「複数のファイルを結合」できるます。
しかも、"次の日" や "次の月" のファイルが追加されても「更新」するだけで、自動的に結合できるようになります。
指定した項目だけを結合することができる
基本的には全てのフィールド(項目)が結合対象になりますが、特定のフィールド(項目)だけを指定して結合対象にすることもできます。
操作(作業)の内容が、「クエリ」として保存される
取得と変換での操作(作業)は、「クエリ」として保存されます。
1度実行した「結合」の作業を、繰り返して行うことができます。
データの取得と変換は、Excel2016から追加された機能です
「取得と変換」は各種データの取り込みや、そのデータの成型・加工などの一連の提携作業を自動化できる機能です。
この機能は、従来「Power Query エディター」という名前で「アドイン」として外部機能の扱いでした。
この機能が、Excel2016から「データの取得と変換」として標準機能として「リボン」から操作できるようになりました。
「取得と変換」で、フォルダー内の「ファイル(ワークシート)」を結合する
「1つのフォルダ」に保存された、 "複数の Excelのファイル" を結合します。
結合するファイルの条件は、3種類
結合できる「ファイル」には、"条件" があります。
1.ファイル内に作成するワークシートは1つだけ
2.「ワークシート名」は同じもの(同一)にする
3.結合したい "ファイル" を1つのフォルダーに入れる
結合するファイルの"ワークシート"の名前は同じものにして、1つだけにします
作成する "ファイル名" にルールはありませんが、ワークシートは「同一の名前」にして「1つだけ」にする。
フォルダー内の全ての「ファイル」を結合します
「取得と変換」は、基本的に外部(Power Query エディター)で動作します。
Excelを立ち上げた状態であれば、カーソル(アクティブセル)はどの位置にあっても問題ありません。
データの取得で、「フォルダーから」を選択
今回は、フォルダー内の "全ての「ファイル」" を結合し、結合の対象は "全てのフィールド" に指定します。
- 「データ」タブを選択
- 「データの取得」コマンドを選択
- プルダウンリストから「ファイルから」を選択
- プルダウンリストから「フォルダーから」を選択
※ 注意点
結合対象のファイルの "ファイル名" についてはルールはありませんが、"ワークシートは1つ" で "ワークシート名は統一(同じ名前に)する" 必要があります。
結合したい "ファイル"が保存したフォルダーを指定します
結合したい "ファイル" が保存されている「フォルダー」を指定します。
- フォルダーのダイアログで「参照」ボタンをクリック
- フォルダーの参照ダイアログでフォルダーを指定します
- 「OK」ボタンをクリック
- フォルダーパスに、"選択したフォルダー" が表示されているのを確認して「OK」ボタンをクリック
"取得と変換のダイアログ" が起動します
"Power Query エディター" が、外部プログラムとして立ち上がり(※ バックグラウンドで)作業が開始されます、結合するデータ量により異なりますが多少時間がかかるので、画面に途中経過の「メッセージ」が表示されます。
- Power Query エディターが立ち上がり、「クエリを評価しています」のメッセージが表示されます
- 次に「接続しています」のメッセージが表示されます
- ダイアログが表示されるので「結合」ボタンをクリックします
- プルダウンリストから「結合及び読み込み」を選択します
「結合」のオプションは3種類あります
今回は2番目の「結合及び読み込み」を選択し、オプションの2番目の「結合及び読み込み」を選択して、フォルダー内の全てのファイルの全てのフィールド(項目)を結合して、新しくワークシートを追加します。
- 結合および編集 ➡ 結合した状態でPower Queryエディタが開く
- 結合および読み込み ➡ 結合した表を新しいワークシートに展開する
- 結合および読み込み先 ➡ 結合した表をどうするかを選択
"Fileの結合"のダイアログが開きます
Fileの結合のダイアログで、結合する「ワークシート」を選択します。
- 統一したワークシート名 "DATA" を選択
- 結合するワークシート "DATA" の内容(一部)が表示されるので確認します
- 「OK」ボタンをクリック
結合された新しい「ワークシート」が作成されます
ファイルを結合した結果は、新しい1つのワークシートになり取得と結合を実行したブック(ファイル)内に追加されます。
※ "新しい ブック" を作成してから「取得と変換」を実行すると分かりやすい
- 「クエリの処理」のメッセージが表示されます
- 「クエリの評価」のメッセージが表示されます
- 新しいワークシート「Sheet2」が追加されます
CSVファイルを結合する
会計ソフトから書き出される形式は "CSV" 形式が多いと思います。
取得と結合の機能は "ブック(ファイル)"だけではなく、"CSVファイル" も結合することができます。
「メモ帳」で開いたCSVファイル
「CSVファイル」は、データが「 , 」(カンマ)、やスペースなどで区切られています。
「データ」タブから「データの取得」を起動
操作方法は "ファイル" を結合する場合と同じ操作です。
- 「データ」タブを選択
- 「データの取得」をクリック
- プルダウンリストから「ファイルから」を選択
- プルダウンリストから「フォルダーから」を選択
結合する "CSV" ファイルを保存している「フォルダー」を選択
ここも操作方法は "「ファイル」を結合する"場合と同じ操作です。
- フォルダーのダイアログで「参照」をクリック
- フォルダーの参照ダイアログで、フォルダーを選択
- 「OK」ボタンをクリック
- フォルダーパスに、選択したフォルダーが表示されているのを確認して「OK」ボタンをクリック
"データの取得と変換ダイアログ" が起動します
ここも操作方法は "「ファイル」を結合する"場合と同じです。
- バックグラウンドでPower Queryエディターが立ち上がり、「クエリを評価しています」のメッセージが表示されます
- 次に「接続しています」のメッセージが表示されます
- 「結合」をクリックします
- 「結合及び読み込み」を選択します
Fileの結合のダイアログが開きます
ここは、"ファイルを結合" する場合と表示される画面が異なります。
Fileの結合のダイアログで、結合する「ワークシート」を選択します。
- Fileの結合のダイアログが表示され、 "DATA" の内容(一部)が表示されるので確認します
- 「OK」ボタンをクリック
結合された新しい「ワークシート」が作成されます
ここも操作方法は "ファイル(CSV)" の場合と同じです。
- 「クエリの処理」のメッセージが表示されます
- 「クエリの評価」のメッセージが表示されます
- 新しいワークシート「Sheet2」が追加されます
毎月追加されるファイル(CSV)が自動的に "結合" される
"取得と更新" の機能で一度フォルダー内のファイルを結合しておけば、新たなファイルをフォルダーに追加して「更新」するだけでファイル(CSV)が結合されます。
フォルダーに"ファイル(CSV)"を追加
「取得と変換」で、ファイルを結合する「クエリ」を作成しておけば、"新しいファイル" を追加しても「更新」することで自動的に追加されます。
「4月分」までが結合されている状態
"取得と結合" の機能で、フォルダー内の「2017年1月」「2017年2月」「2017年3月」「2017年4月」までの4つのファイルが結合されている状態です。
フォルダーに、「2017年5月」のファイル(CSV)を追加して"更新"を実施
"取得と変換" を実行したフォルダーに、新しいファイル「2017年5月」を追加して、「更新」をするだけで追加したファイルも結合されます。
- 「データ」タブを選択
- 「すべて更新」をクリック
このように「取得と変換」の機能を使えば、簡単に複数のファイル(CSV)を結合することができます。
今回は、ワークシートの全てのフィールド(項目)を結合する方法を説明しましたが、次回は特定のフィールド(項目)を選択して結合する方法を説明します。
- 「取得と変換」を使えば、フォルダー内のデータを結合できます
- 日々の集計ファイルを集めて(結合して)「月」や「年」の集計する
- データの取得と変換は、Excel2016から追加された機能です
- CSVファイルを結合する
- 毎月追加されるファイル(CSV)が自動的に "結合" される