この記事では、複数のエクセルブックに保存されているデータを1つに結合するためのPower Queryの使い方を紹介します。加えて、結合されたエクセルブックを再度分割するためのVBAコードについても紹介します。
この記事を読むべき人
本店に勤めており、各支店からの予算要望の受信と配分額の回答を下記のステップで行っている人は、この記事を読むべき人です。
- 各エクセルブックのデータをカットアンドペーストで1つに結合
- 要望額に対する配分額を入力
- 支店に返信するため、結合したエクセルブックを支店の数だけコピーして、他の支店のデータを手作業で消去し分割
2016年までは、データを結合するためにVBAで難しいコードを書く必要がありました。ところが、Power Queryが2016年に登場し、データ結合するためのVBAは必要なくなりました。
VBAに比べてPower Queryが優れている点は、操作が簡単なことです。
それでは、実際に操作方法を紹介しますので、まずは作業の前提条件とサンプルデータをご一読ください。
作業の前提条件とサンプルファイル
前提条件
今回紹介する作業の前提条件は下記のとおりです。
- すべてのエクセルブックは１つのフォルダに保存
- データが入力されているシート名は、すべて同じ
サンプルファイル
ここからダウンロードしてください。
DOWNLOAD
サンプルファイルを解凍すると、フォルダ「test」内に、「01_A支店.xlsx」「02_B支店.xlsx」「03_C支店.xlsx」というエクセルブックがあります。
すべてのブックには「data」という名前がついたシートがあります。
各エクセルブックには下記の図のとおりデータが入力されています。
チュートリアル
全体の概要
本店に勤めているあなたは、各支店から送られてきた予算要望のエクセルブックを結合して、配分額を入力した後、支店ごとにエクセルブックを分割したいと考えています。
これを実現するため、以下の３つの作業を実際の操作画面で紹介します。
- 結合
- 配分額の入力
- 分割
Power Queryによる結合
まずは、複数のエクセルブックに保存されているデータをPower Queryで結合してみましょう。
エクセルブックを新規作成し、タブ「データ」の左端「データの取得」をクリックします。
続いて、「ファイルから」、「フォルダーから」をクリックします。
開いたウィンドウの下にある「結合」ボタンのプルダウンから「結合および読み込み」をクリックします。
開いたウィンドウの左にある「data」をクリックし、右下「OK」をクリックします。
処理が完了すると、図のとおり3つのエクセルブックのデータが結合されたテーブルが表示されます。
操作方法さえ覚えれば、カットアンドペーストで結合するより簡単です。
配分額の入力
結合したデータは「テーブル」になので、データを追加は、テーブル端に入力するだけ。
E1に文字を入力すると、E10までのセルがテーブルに追加され、色が変化します。
E2からE10のセルに配分する金額を入力します。
次に、各支店に返信するため、エクセルブックを分割してみましょう。
VBAによる分割
私が調べた限り、エクセルブックの分割はVBAでしかできません。
エクセルでAtl + F11を押せば、VBAのウィンドウが開きます。
キーボードでAtl + F11を押し、VBAのウィンドウが開いたら、メニュー「挿入」の「標準モジュール」をクリックします。
標準モジュールが追加されるので、下記のコードをコピペします。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
| Sub Split_Sht()
myArr = Cells(1, 1).CurrentRegion
maxRow = UBound(myArr)
Set myDic = CreateObject("Scripting.Dictionary")
For i = 2 To maxRow
If Not myDic.Exists(myArr(i, 1)) Then
myDic.Add myArr(i, 1), Null
End If
Next i
mySourceName = myDic.Keys
Worksheets(1).Cells(1, 1).Activate
Worksheets(1).ListObjects(1).TableStyle = ""
Dim myPath As String
MsgBox "保存先フォルダを選んでください。", vbExclamation
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = ThisWorkbook.Path & "\"
If .Show = True Then
myPath = .SelectedItems(1)
Else
MsgBox "処理がキャンセルされました。", vbExclamation
Exit Sub
End If
End With
For Each c In mySourceName
Worksheets(1).Activate
With ActiveSheet.Range("A1")
.AutoFilter Field:=1, Criteria1:=c
.CurrentRegion.Copy
End With
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
Selection.PasteSpecial Paste:=xlPasteColumnWidths
ActiveSheet.Paste
Columns("A:A").Delete
ws.Name = "data"
ActiveSheet.Move
ActiveSheet.Cells(1, 1).Select
ActiveWorkbook.SaveAs myPath & c
ActiveWorkbook.Close
Next
With Worksheets(1)
.Activate
.ShowAllData
End With
Set myDic = Nothing
End Sub
|
ペーストしたら、右上のxをクリックして、VBAウィンドウを閉じます。
エクセルのウィンドウに戻って、タブ「表示」の右端にある「マクロ」をクリックします。
マクロのウィンドウが開いたら、右上の「実行」をクリックします。
表示されるダイアログに沿って、分割したブックを保存するためのフォルダを選んでください。
保存先に選んだフォルダを開いてみてください。
エクセルブックが作成されていますので、試しに「01_A支店.xlsx」を開いてみましょう。
D列に「配分額」が追加された状態になっています。
これで、各支店に配分額を通知するエクセルブックが完成しました。
あとがき
この方法は、結合と分割を繰り返すことができます。
今回の記事では、エクセルブックの結合と分割について基本的な方法だけ紹介させていただきました。
下記の記事では、シート名が同じでない場合の対応方法などを紹介していますので、こちらも参考にしてください。
Power QueryでExcelを結合した時のエラー対策 -原因はシート名-
パワークエリでエクセルブックの結合する際の前提条件の1つに「シート名がすべて同じ」があります。この記事では、シート名が異なっている場合のエラーの概要と、対処方法について紹介します。
