質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

Q&A

2回答

4591閲覧

複数のファイルの特定のデータを、別ファイルへ転記するマクロについて

sabotenmilk

総合スコア6

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

0グッド

1クリップ

投稿2018/03/17 11:45

編集2022/01/12 10:55

前提・実現したいこと

お世話になります。

下記Aの表をまずエクセルで作り、マクロを使って表Bのように県ごとにまとめたブックを作成します。

表A 【ブック名:企画部門集計用ファイル】【シート名:デフォルトのままです】
|責任組織|勘定科目1|勘定科目2|取引先|入力者1|入力者2|入力者3|入力合計|
|:--|:--:|--:|
|Mビル(東京)|○○部|消耗品費|(株)A商事|||||
|Yビル(東京)|△△部|車両費|(株)B商店|||||
|Jビル(名古屋)|○○部|水道光熱費|(株)D印刷店|||||
|Kビル(名古屋)|□□部|消耗品費|(株)E工場|||||
|Zビル(大阪)|△△部|外注費|(株)Fパワー|||||
|Pビル(大阪)|××部|車両費|(株)G計画|||||

表B
東京のビルのみでまとめたブック 【ブック名:企画部門(東京)】【シート名:デフォルトのままです】
|責任組織|勘定科目1|勘定科目2|取引先|入力者1|入力者2|入力者3|入力合計
|:--|:--:|--:|
|Mビル(東京)|○○部|消耗品費|(株)A商事||||
|Yビル(東京)|△△部|車両費|(株)B商店||||

名古屋のビルのみでまとめたブック 【ブック名:企画部門(名古屋)】【シート名:デフォルトのままです】
|責任組織|勘定科目1|勘定科目2|取引先|入力者1|入力者2|入力者3|入力合計
|:--|:--:|--:|
|Jビル(名古屋)|○○部|水道光熱費|(株)D印刷店||||
|Kビル(名古屋)|□□部|消耗品費|(株)E工場||||

大阪のビルのみでまとめたブック 【ブック名:企画部門(大阪)】【シート名:デフォルトのままです】
|責任組織|勘定科目1|勘定科目2|取引先|入力者1|入力者2|入力者3|入力合計
|:--|:--:|--:|
|Zビル(大阪)|△△部|外注費|(株)Fパワー||||
|Pビル(大阪)|××部|車両費|(株)G計画||||

ここまでが前提です。
県ごとにブックを作成したあと、それぞれの県の担当者へデータを送り、
入力者名・取引先ごとの金額を入力してもらい、
またデータを返してもらいます。

◆返してもらったデータ(例:東京)
※入力担当者は複数いる為、「入力者1・2・3」の欄を担当者名に書きかえてもらい、それぞれ担当の金額を入力してもらっています。
|責任組織|勘定科目1|勘定科目2|取引先|○本さん|△川さん|×井さん|入力合計|
|:--|:--:|--:|
|Mビル(東京)|○○部|消耗品費|(株)A商事|100|200||300|
|Yビル(東京)|△△部|車両費|(株)B商店|150||100|250|

この県ごとのブックの「入力合計」の金額を、下記の表Cのそれぞれの県ごとのセルに転記するようなマクロがあれば教えて頂きたいのです。
各担当者ごとの金額は必要ではなく、あくまで県ごとの各行の合計金額を転記したいため、
「入力合計」欄の金額を、表Cの各県の該当のセルへ転記できればと思います。

表C 【ブック名:企画部門集計用ファイル】【シート名:デフォルトのままです】
|責任組織|勘定科目1|勘定科目2|取引先|東京|名古屋|大阪|入力合計|
|:--|:--:|--:|
|Mビル(東京)|○○部|消耗品費|(株)A商事|||||
|Yビル(東京)|△△部|車両費|(株)B商店|||||
|Jビル(名古屋)|○○部|水道光熱費|(株)D印刷店|||||
|Kビル(名古屋)|□□部|消耗品費|(株)E工場|||||
|Zビル(大阪)|△△部|外注費|(株)Fパワー|||||
|Pビル(大阪)|××部|車両費|(株)G計画|||||

今までは、各担当者よりデータが返ってきたあと、表Aの「入力者1・2・3」を手打ちで県の名前に変え、返してもらったデータの「入力合計」の金額を、各該当の県のセルへコピペして・・というのをひたすら手作業で繰り返していました。。

長文な上に分かり辛くて大変申し訳ございません。
なんとか簡潔に済むようなやり方があればご教授下さいますよう、お願い致します。

発生している問題・エラーメッセージ

エラーメッセージ

該当のソースコード

ソースコード

試したこと

ここに問題に対して試したことを記載してください。

補足情報(FW/ツールのバージョンなど)

ここにより詳細な情報を記載してください。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2018/03/17 12:00

表Cの東京や名古屋などと県ごとのシートと結びつける条件は何ですか?ブック名?シート名?また、具体的なブック名やシート名についても質問文に追記して下さい
sabotenmilk

2018/03/17 13:49

文章が至らず申し訳ありません。「表Cの東京や名古屋などと県ごとのシートと結びつける条件は何ですか?」の意味が分からなかったのですが、県ごとのブック名(すみません、シート名ではありませんでした。。)は全て「○○部門(東京)」などど部門名の後ろに県名が入っている為、表Cに入っている県名とは完全一致はしていないんです。表Cの県名と、県ごとに分かれたブック名は一致していないと難しいのでしょうか?初心者で申し訳ありません。
sazi

2018/03/17 14:26 編集

表Aを表Bに分割している際の条件がデータから識別できないと。例えばMビルとYビルとの合計は東京のセルに入れるというのは何で判断できますか?
sabotenmilk

2018/03/17 16:15

度々申し訳ございません。責任組織名に「○○ビル(東京)」などのように県名が含まれております。本文も修正致しました、申し訳ございません。実は、表Aから表Bへ実行するマクロはわたくしが担当する前から存在しており、マクロを作成した前任者がいない&わたくしが全くのマクロ初心者の為、説明が下手で申し訳ありません。会社のPCからはこちらのサイトが見れないため、思い出しながら投稿させて頂いております。
guest

回答2

0

簡素化のために以下の仮定のもとで実装例を示します。

  • 表B(集計対象の「企画部門」ブック)は表C(集計先の「企画部門集計用ファイル」ブック)と同じフォルダに存在する
  • シート名(デフォルト)を「Sheet1」とする
  • キーとして使用する「責任組織+勘定科目1+勘定科目2+取引先」に重複はない
  • エラーハンドリングは実施していない

VBA

1Sub summarize() 2 3 Dim lngEndRow As Long 4 Dim strPath As String 5 Dim rngData As Range 6 Dim vntData As Variant 7 Dim dicData As Object, I As Long, J As Long, strKey As String 8 9 10 Set dicData = CreateObject("Scripting.Dictionary") 11 12 Application.ScreenUpdating = False 13 14 15 '-- 東京分の読み込み 16 strPath = ThisWorkbook.Path & "\企画部門(東京).xlsx" 17 Workbooks.Open strPath 18 With ActiveWorkbook 19 With .Worksheets("Sheet1") 20 '-- 最終行取得 21 lngEndRow = .Cells(Rows.Count, 1).End(xlUp).Row 22 23 '-- データを配列に取り込み 24 Set rngData = .Range(.Cells(2, 1), .Cells(lngEndRow, 8)) 25 vntData = rngData.Value2 26 Set rngData = Nothing 27 28 '-- 入力金額の合計をDictionary(連想配列)へ取り込み 29 For I = 1 To UBound(vntData) 30 strKey = vntData(I, 1) & "_" & vntData(I, 2) & "_" & vntData(I, 3) & "_" & vntData(I, 4) 31 dicData.Add strKey, vntData(I, 8) 32 Next I 33 End With 34 35 .Close False 36 End With 37 38 39 '-- 名古屋分の読み込み 40 strPath = ThisWorkbook.Path & "\企画部門(名古屋).xlsx" 41 Workbooks.Open strPath 42 With ActiveWorkbook 43 With .Worksheets("Sheet1") 44 '-- 最終行取得 45 lngEndRow = .Cells(Rows.Count, 1).End(xlUp).Row 46 47 '-- データを配列に取り込み 48 Set rngData = .Range(.Cells(2, 1), .Cells(lngEndRow, 8)) 49 vntData = rngData.Value2 50 Set rngData = Nothing 51 52 '-- 入力金額の合計をDictionary(連想配列)へ取り込み 53 For I = 1 To UBound(vntData) 54 strKey = vntData(I, 1) & "_" & vntData(I, 2) & "_" & vntData(I, 3) & "_" & vntData(I, 4) 55 dicData.Add strKey, vntData(I, 8) 56 Next I 57 End With 58 59 .Close False 60 End With 61 62 63 '-- 大阪分の読み込み 64 strPath = ThisWorkbook.Path & "\企画部門(大阪).xlsx" 65 Workbooks.Open strPath 66 With ActiveWorkbook 67 With .Worksheets("Sheet1") 68 '-- 最終行取得 69 lngEndRow = .Cells(Rows.Count, 1).End(xlUp).Row 70 71 '-- データを配列に取り込み 72 Set rngData = .Range(.Cells(2, 1), .Cells(lngEndRow, 8)) 73 vntData = rngData.Value2 74 Set rngData = Nothing 75 76 '-- 入力金額の合計をDictionary(連想配列)へ取り込み 77 For I = 1 To UBound(vntData) 78 strKey = vntData(I, 1) & "_" & vntData(I, 2) & "_" & vntData(I, 3) & "_" & vntData(I, 4) 79 dicData.Add strKey, vntData(I, 8) 80 Next I 81 End With 82 83 .Close False 84 End With 85 86 87 '-- 企画部門集計用ファイルへの集計結果貼り付け 88 With ThisWorkbook 89 With .Worksheets("Sheet1") 90 '-- 見出し行の修正 91 .Range("E1").Value = "東京" 92 .Range("F1").Value = "名古屋" 93 .Range("G1").Value = "大阪" 94 95 '-- 最終行計算 96 lngEndRow = dicData.Count + 1 97 98 '-- データを配列に取り込み 99 Set rngData = .Range(.Cells(2, 1), .Cells(lngEndRow, 7)) 100 vntData = rngData.Value2 101 102 '-- 合計金額を対応箇所へ転記 103 For I = 1 To UBound(vntData) 104 strKey = vntData(I, 1) & "_" & vntData(I, 2) & "_" & vntData(I, 3) & "_" & vntData(I, 4) 105 106 If vntData(I, 1) Like "*東京*" Then 107 J = 5 108 ElseIf vntData(I, 1) Like "*名古屋*" Then 109 J = 6 110 ElseIf vntData(I, 1) Like "*大阪*" Then 111 J = 7 112 End If 113 vntData(I, J) = dicData(strKey) 114 Next I 115 116 '-- 転記したデータをワークシートへ書き戻し 117 rngData.Value = vntData 118 Set rngData = Nothing 119 End With 120 End With 121 122 123 Application.ScreenUpdating = True 124 125 Set dicData = Nothing 126 127End Sub

対象データを配列に取り込んでから処理すると実行速度が速いです。
データ量が多い場合には、何度も検索するよりも連想配列(Dictionary)を使うと処理が簡単になる場合があります。

以上、ご参考になれば幸いです。

投稿2018/04/04 18:26

pi-chan

総合スコア5936

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

pi-chan

2018/04/05 01:38

ちょっと補足です。 今回は分かりやすいようにループを使わずに記載してみましたが、 3つの「企画部門(××).xlsx」を開いてデータを連想配列に取り込む部分は全く同じ処理の繰り返しなので、for~next で繰り返しても良いです。
guest

0

式でも実現できそうですので、手順をまとめてみます。

「企画部門集計用ファイル」、「企画部門(東京)」、「企画部門(名古屋)」、「企画部門(大阪)」がすべて同じフォルダー内にある前提です。

  1. まず「企画部門集計用ファイル」を複製して「企画部門集計用ファイル2」を作り、同じフォルダーに入れます。これが表Cの完成版に相当します。以下の手順は、すべてこの「企画部門集計用ファイル2」に対する操作です。

  2. セル[A2]に次の式を入れます。

=[企画部門集計用ファイル.xlsx]Sheet1!A2
これは、表Aの最初のビルをコピーしてくるだけの式です。

  1. セル[A2]を選んでコピーし、次にSHIFTを押しながらセル[D7]を選んで[A2]〜[D7]を範囲選択し、数式として貼り付けます。

これで表Aのビルから取引先までの一覧がコピーされます。ビルが多くある場合は数に合わせて貼り付け範囲を下へ広げてください。

  1. セル[E2]に次の式を入れます。

=SUMIF('[企画部門(東京).xlsx]Sheet1'!A:A,A2,'[企画部門(東京).xlsx]Sheet1'!H:H)
これは、東京の表のA列に対して[セル]A2のビル名を探し、もし見つかったらH列の入力合計をコピーしてくる式です。仕組みの詳細については「SUMIF」でググってみてください。

  1. セル[E2]を選んでコピーし、セル[E3]以下に数式とし貼り付けます。

ビル数に合わせて貼り付け範囲を下へ広げてください。これで東京のビルの金額が入るはずです。

  1. セル[F2]に次の式を入れます。

=SUMIF('[企画部門(名古屋).xlsx]Sheet1'!A:A,A2,'[企画部門(名古屋).xlsx]Sheet1'!H:H)
手順4の名古屋版です。

  1. セル[F2]を選んでコピーし、セル[F3]以下に数式とし貼り付けます。

これで名古屋のビルの金額が入ります。

  1. セル[G2]に次の式を入れます。

=SUMIF('[企画部門(大阪).xlsx]Sheet1'!A:A,A2,'[企画部門(大阪).xlsx]Sheet1'!H:H)
手順4の大阪版です。

  1. セル[G2]を選んでコピーし、セル[G3]以下に数式とし貼り付けます。

これで大阪のビルの金額が入ります。

手元の環境はWindows7/Excel 2013です。次のようなファイルで確認してみました。
イメージ説明

投稿2018/04/01 01:48

segavvy

総合スコア958

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問