Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい

Question

よろしくお願いします。

Excelでアンケート集計を行なっています。
同じフォルダ内に様式が同じExcelファイルが百数十あります。

画像のように、1つのファイルの中に複数のシートが有り、【記入表】というシートだけ1つのファイルに集約したいのです。

その集約後、2行目、3行目の赤枠で囲っている回答部分を1つのシートに集約、集計等行なっていきたいと思っています。
これは可能でしょうか。

①各部署が回答してきたファイルを同一のフォルダ内に保存
②マクロ（マクロ以外でやり方があれば教えてください）でファイル内の【記入表】（名前は例です、全ての部署に同じ名前で配布）シートのみを別ファイルに集約

③集約したファイル内で集計シートを作り、各部署からの回答部分を集計できるように貼り付け

ということをしたいです。今までは手作業でコピーをしていました。

同じフォルダ内の複数ファイルを1つのファイルにまとめる、というマクロはみつけました。
（ただし、マクロの内容までは理解出来ず、コピペで使おうと思っています。）

ファイル内の特定のシートのみを集約する、ということは可能でしょうか。
シート名は全て【記入表】という名前になっています。（一意のシート名ではない）
また、集約後、回答部分の行を1つのシートに追加していく形で集約することは可能でしょうか。

自分の技術力のなさに辟易してしまいます。
どうぞお知恵を貸して頂ければとおもいます。

tatsumaru77 · Accepted Answer

以下のマクロを標準モジュールに登録してください。
回答一覧の3行目以降から設定します。
回答一覧の2行目の見出しはあなたが設定しておいてください。
不明点があれば、補足してください。

Option Explicit

Public Sub アンケート集計()
    Const Folder As String = "D:\goo\data7"     'excelファイル格納フォルダ
    Dim fname   As String   'ファイル名
    Dim wb2 As Workbook     '記入表のブック
    Dim ws1 As Worksheet    '回答一覧
    Dim ws2 As Worksheet    '記入表
    Dim maxrow As Long      '回答一覧の最大行
    Dim row1 As Long        '回答一覧の処理行
    Set ws1 = Worksheets("回答一覧")
    maxrow = ws1.Cells(Rows.Count, "C").End(xlUp).Row    'C列の最大行取得
    If maxrow < 2 Then maxrow = 2       '2行未満なら2行に修正
    row1 = maxrow + 1                   'maxrowの次の行から書き込み
    fname = Dir(Folder & "\*.xlsx")      '指定フォルダ内の*.xlsxを取得
    If fname = "" Then
        MsgBox (Folder & "内に.xlsxなし")
        Exit Sub
    End If
    Do While fname <> ""
        '該当ファイルをオープン
        Set wb2 = Workbooks.Open(Folder & "\" & fname)
        Set ws2 = wb2.Worksheets("記入表")
        '記入表から回答一覧へ６セル分まとめて転記
        ws1.Cells(row1, "C").Resize(, 6).Value = ws2.Cells(3, "C").Resize(, 6).Value
        wb2.Close SaveChanges:=False
        '回答一覧の処理行に1加算
        row1 = row1 + 1
        '次のexcelファイルを取得
        fname = Dir()
    Loop
    MsgBox ("処理完了")
End Sub

fujillin · Answer

No4です

どうせ利用なさらないでしょうから、補足する必要もないとは思いましたが・・・

＞C:\Users\XXX\のぶぶんが異様に長くなること、～～
業務を効率化しようとしているのではないかと思いますが、関数に限らず通常のシートの作業であれ、マクロであれ、効率よく考えることに関しては同じようなものです。

長いパスを全セルに記入しようとなどとする前に、どこかのセルに（例えばAセル）に、共通のパスを入力しておけば、後はそれを参照すれば済むという方法を思いつくはず。
どうせ文字列でパスを作成するのですから
　A1 & ブック名 & "hoge"
的なことを考えれば済む話ではないのかなぁ・・

まぁ、一覧も無いようですし、使えない案でしょうから、どうでもよいのですけれど・・

tatsumaru77 · Answer

今後、マクロを勉強するということですので、以下何点か、追記します。
１．インデントについて
このサイトにアップするとインデントが崩れます。
マクロはインデントがきちんとしていないと、非常に読みづらくなります。
下記にインデントをきちんとしたマクロがアップしてあります。（内容は同じです）
こちらを使用したほうが読みやすいです。
https://ideone.com/dbG4u4

２．参考書について
excel VBA できる大事典　を私は使用しています。
https://book.impress.co.jp/books/1120101091

これに大体の機能は網羅されています。
これば、難しければ、もっと優しい入門書から始めればよいかと思います。
書店で手に取って、自分にあいそうなものを見つけてください。
参考書は1冊で済むと思わずに３～４冊買うつもりでいた方が良いです。
そうすれば、そのうちの一冊は、非常にあなたにあったものが見つかるかと。
いずれにせよ、参考書は会社の経費で落とすようにしてください。
（本当に気に入ったものは自分で購入しても良いかもしれませんが・・・）
会社の経費が落ちないときは、図書館を利用する方法もあります。

３．参考になるサイトについて
office TANAKAが有名です。
私も利用しています。
但し、入門時は、もっと優しいサイトの方がよいかもしれません。
http://officetanaka.net/excel/vba/

tatsumaru77 · Answer

No5です。
追伸：
Const Folder As String = "D:\goo\data7"
は、excelのブックを格納しているフォルダ名です。
上記はこちらで試験したときのフォルダ名です。
あなたの環境にあわせて適切に設定してください。

fujillin · Answer

No1です。

＞集計の際のINDIRECT関数の使い方についてもう少しお教えいただけないでしょうか。
単純な「別ブックからの1行の参照でしょうから、通常なら
　='C:\Users\XXX\[対象ブック.xls]記入表'!C$3
のような要領で参照可能と思います。
このブック名の部分を入替えたいのでしょうから、参照先を文字列で作成し、INDIRECT関数で参照すればよいという意味です。
その際に、入替えるのにブックの一覧があると便利です。
あるいは、連番のような規則性のあるブック名であれば、一覧などなくても作成可能ですね。

＞（一般的な関数の構造などは自分でもちろん調べます。）
はい。　一般的な関数ですので、調べてみてください。

ただし、INDIRECT関数の場合は、参照先のブックが閉じた状態だと参照できずに
　#REF!
のエラー表示になります。
ですので、一旦は、対象ブックを開いてあげる必要があります。
（関数設定後にブックを開けば、値が表示されます）
その後、参照した値を保持するために、全体を「コピー」-「値をペースト」で固定値化すれば良いのでは、と言う意味の回答です。

でも、いろいろ調べたり試行錯誤している時間を考えれば、No1でも述べたように「コピペ」でこなした方が、結局、トータルの時間は早いと思います。

tatsumaru77 · Answer

>（関数だけでは私が行ないたいことは出来ないですよね）
できますが、現実的ではありません。
関数式でやるなら以下のようになります。
回答一覧シートのC3へ
c:\sample\回答1.xlsxの記入表のC3の内容を表示するには
C3へ
='c:\sample\[回答1.xlsx]記入表'!C$3
と記入します。
同様に
D3～H3は
='c:\sample\[回答1.xlsx]記入表'!D$3
・・・
='c:\sample\[回答1.xlsx]記入表'!H$3
と記入します。
これで1行目が完成です。
２行目は、回答2.xlsxの内容を表示するなら
C4へ
='c:\sample\[回答2.xlsx]記入表'!C$3
と表示します。

これを百数十個のファイルの数分設定します。
したがって、これらの設定だけで相当の手間がかかります。
次回は、そのあとへ追記するので、その分の手間も次回発生します。
それならば、今まで通り各ファイルの内容をコピペしたほうが速いでしょう。

関数式で効果があるのは以下のようなケースです。
同じブック内の同じシートの参照の場合
同じブック内の他のシートの参照の場合
他のブックの場合は、参照項目が少ない場合。（参照用のブックが少ないこと）

よって、マクロの方が現実的かと思います。
次の回答では、具体的なマクロの回答をいたします。

tatsumaru77 · Answer

補足要求です。
１．集約ファイルの集計シートのレイアウトは、添付図であってますか。

２．集約ファイルの集計シートのシート名は何でしょうか。

３．集約ファイルにマクロを格納する前提で良いですか。
（集約ファイルの拡張子は.xlsmになります）

４．同じフォルダ内の様式が同じExcelファイルの拡張子は.xlsxで間違いないでしょうか。

５．今回集計が終わった後、次回の集計を行う場合は、
集計シートの最後の行の後ろへ追記して行くのでしょうか。
それとも、以前のぶんをクリアして、集計シートの３行目から書き込んで行くのでしょうか。

fujillin · Answer

こんにちは

とにかく一つのシートにまとめてしまえばあとは何とかなるのでしょうから・・

各ファイル名の一覧などがあるのなら、INDIRECT関数で参照式を設定しておいて、ブックをまとめて開き、値をペーストで固定値化するのが簡単かも知れません。
ただし、シート名が異なると面倒です。
＞シート名は全て【記入表】という名前になっています。（一意のシート名ではない）
どっちなんでしょうね？
一意のシート名のように読めますけれど・・・？

もちろん、マクロでも可能ですけれど、（失礼ながら、すぐに作成できる技量をお持ちというわけでもなさそうですので）学習して、作成して、テストして・・という時間を考えれば、200件程度なら手作業でコピペしてしまう方が遥かに速いと思います。

ご質問とは、方法はまったく変わってしまいますが・・・
Googleのアンケートフォームを使えば、集計は簡単にスプレッドシートにまとめることができたように思います。
ご参考までに。
https://www.makeleaps.jp/resources/articles/how-to-use-google-form/

Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい

以下のマクロを標準モジュールに登録してください。

No4です

今後、マクロを勉強するということですので、以下何点か、追記します。

No5です。

No1です。

>（関数だけでは私が行ないたいことは出来ないですよね）

補足要求です。

こんにちは

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング