プロが教える店舗&オフィスのセキュリティ対策術

よろしくお願いします。

Excelでアンケート集計を行なっています。
同じフォルダ内に様式が同じExcelファイルが百数十あります。

画像のように、1つのファイルの中に複数のシートが有り、【記入表】というシートだけ1つのファイルに集約したいのです。

その集約後、2行目、3行目の赤枠で囲っている回答部分を1つのシートに集約、集計等行なっていきたいと思っています。
これは可能でしょうか。

①各部署が回答してきたファイルを同一のフォルダ内に保存
②マクロ(マクロ以外でやり方があれば教えてください)でファイル内の【記入表】(名前は例です、全ての部署に同じ名前で配布)シートのみを別ファイルに集約

③集約したファイル内で集計シートを作り、各部署からの回答部分を集計できるように貼り付け

ということをしたいです。今までは手作業でコピーをしていました。

同じフォルダ内の複数ファイルを1つのファイルにまとめる、というマクロはみつけました。
(ただし、マクロの内容までは理解出来ず、コピペで使おうと思っています。)

ファイル内の特定のシートのみを集約する、ということは可能でしょうか。
シート名は全て【記入表】という名前になっています。(一意のシート名ではない)
また、集約後、回答部分の行を1つのシートに追加していく形で集約することは可能でしょうか。

自分の技術力のなさに辟易してしまいます。
どうぞお知恵を貸して頂ければとおもいます。

「Excel、同じフォルダ内のExcelフ」の質問画像

A 回答 (8件)

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


回答一覧の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("記入表")
'記入表から回答一覧へ6セル分まとめて転記
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
    • good
    • 0
この回答へのお礼

回答ありがとうございます。

教えて頂いたマクロで動かすことが出来ました。

大変助かりました。少しずつでも勉強していきたいと思います。

お礼日時:2022/09/13 05:31

No4です



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

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

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

まぁ、一覧も無いようですし、使えない案でしょうから、どうでもよいのですけれど・・
    • good
    • 0
この回答へのお礼

度々の回答ありがとうございます。

今回のことに限らず、他のことでも応用できそうで、
大変参考になりました。

お礼日時:2022/09/09 08:36

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


1.インデントについて
このサイトにアップするとインデントが崩れます。
マクロはインデントがきちんとしていないと、非常に読みづらくなります。
下記にインデントをきちんとしたマクロがアップしてあります。(内容は同じです)
こちらを使用したほうが読みやすいです。
https://ideone.com/dbG4u4


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

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


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

No5、No6 と回答ありがとうございました。
まず、自分で動かして見たいと思います。

不明な点があれば、No5の回答のお礼欄に書かせて頂きます。
まずは御礼まで。
ありがとうございました。

お礼日時:2022/09/09 08:49

No5です。


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

回答ありがとうございます。

お礼日時:2022/09/13 05:30

No1です。



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

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

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


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

回答ありがとうございます。
回答されてきた百近くのブックを開いて、集計シートにINDIRECT関数を入力する、というのは仰るとおり、現実的ではないかもしれないと思いました。それであればコピペの方が早いかと。

特に私の技術力ではそうなってしまいそうです。。。

'C:\Users\XXX\[対象ブック.xls]記入表'!C$3

ですが、サーバ上に保存したファイルとなり、C:\Users\XXX\のぶぶんが異様に長くなること、対象ブック名も各部署が変更して回答してくるので、規則性がない可能性があるので、INDIRECT関数に入力する上記のようなパスを用意するのも一苦労となりそうです。
(百数十と書きましたが、場合によっては300、400くらいになりそうです)

コピペの方が効率的かもしれないです。。。。

お礼日時:2022/09/07 18:23

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


できますが、現実的ではありません。
関数式でやるなら以下のようになります。
回答一覧シートの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行目は、回答2.xlsxの内容を表示するなら
C4へ
='c:\sample\[回答2.xlsx]記入表'!C$3
と表示します。

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

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

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

回答ありがとうございます。


何度も回答頂き大変ありがたいです。

マクロ、少しずつでも勉強したいです。

引き続き何卒よろしくお願いいたします。

お礼日時:2022/09/07 18:25

補足要求です。


1.集約ファイルの集計シートのレイアウトは、添付図であってますか。

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

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

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

5.今回集計が終わった後、次回の集計を行う場合は、
集計シートの最後の行の後ろへ追記して行くのでしょうか。
それとも、以前のぶんをクリアして、集計シートの3行目から書き込んで行くのでしょうか。
「Excel、同じフォルダ内のExcelフ」の回答画像2
    • good
    • 0
この回答へのお礼

回答ありがとうございます。

1.集約ファイルの集計シートのレイアウトは、添付図であってますか。

→はい、合っています。

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

→決まっていませんが【回答一覧】などにしたいと思います。

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

→はい、マクロを格納する前提です。
(関数だけでは私が行ないたいことは出来ないですよね)

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

→はい、各部署に配布し、回答し手頂くファイルも.xlsにします。

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

→シートの最後の行の後ろへ追記していくことを考えています。


よろしくお願いします。

お礼日時:2022/09/07 16:34

こんにちは



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

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

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


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

回答ありがとうございます。
Excelのマクロ、VBAは全く使ったことがなく、書かれているように、技術力を持ち合わせておりません。。。

また、今回はExcelの使用が前提になっています。。。
(Gpoogleは使えない)

シート名は【記入表】に統一されています。(全てのファイル内に【記入表】というシートが有り、重複してしまうと言うことで記載しました)

A部署の回答ファイル内の記入用シート
B部署の回答ファイル内の記入用シート

をINDIRECT関数を使えば1つのファイルのシートに集約できるのでしょうか。

各ファイル名の一覧がないと行けないのですね。。。それを作成する必要はありますね。

集計の際のINDIRECT関数の使い方についてもう少しお教えいただけないでしょうか。
(一般的な関数の構造などは自分でもちろん調べます。)

お礼日時:2022/09/07 16:07

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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


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

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