複数シートの平均を算出し平均表を作成するマクロを教えてください

Question

こんにちは！
ご教授いただきたく質問させていただきます。
それぞれ個人のデータが入っている個人表（２行７列）が載っているシートが複数枚あります。その平均を同じシートの個人表の下に作成してある同じ形式の平均表（２行７列）と個人シート（複数枚）と同じつくりにしてある集計シートの平均表に入れたいのです。
今現在は平均表の左上のセル【個人シート（全員分）と集計シートを選択】して個人表の左上セルを串刺し、平均を出しオートフィルで平均表を作成しています。自分一人で集計する時は問題ないのですが他多数の人が行う際に関数入力間違いや参照間違いの発生が危惧されます。マクロでできればそのようなミスが起こらないと思いますが、マクロを作ることが出来ません。ご教授お願いします。

tatsu99 · Accepted Answer

以下のマクロを標準モジュールに登録してください。
集計表より右側のシートは、関知しません。
集計表の平均の表が、各個人の平均の表にコピーされます。
集計表の平均の表の書式設定を適切に行ってください。それらも含めて、各個人のシートに反映されます。
--------------------------------------------------------------
Option Explicit
Public Sub 平均()
    Const tName As String = "集計表"
    Dim row, col, i As Long
    Dim ws, wt As Worksheet
    Dim sheet_last, sheet_count As Long
    Dim val As Variant
    Set wt = Worksheets(tName)
    sheet_last = 0
    sheet_count = 0
    For i = 2 To Worksheets.Count
        If Worksheets(i).Name = tName Then
            sheet_last = i - 1
            Exit For
        End If
        sheet_count = sheet_count + 1
    Next
    If sheet_count = 0 Then
        MsgBox ("シート不足")
        Exit Sub
    End If
    '集計シートクリア
    wt.Range("B5:I6").Value = ""
    wt.Range("B8:I9").Value = ""
    '全ての個人シートを加算
    For i = 2 To sheet_last
        Worksheets(i).Activate
        For row = 5 To 6
            For col = 2 To 9
                wt.Cells(row, col) = wt.Cells(row, col) + Cells(row, col).Value
            Next col
        Next row
    Next i
    '平均を求める
    For row = 5 To 6
        For col = 2 To 9
            wt.Cells(row + 3, col).Value = wt.Cells(row, col).Value / sheet_count
        Next col
    Next row
    '全ての個人シートへ平均をコピー
    For i = 2 To sheet_last
        Worksheets(i).Activate
        wt.Range("B8:I9").Copy Range("B8:I9")
    Next i
    MsgBox ("完了")
End Sub
-------------------------------------------

tatsu99 · Answer

>１．集計用シートは個人シートの右になりますが、今現在その右にその集計シートのデータを参照する表（グラフ等も】のシートが２シートつけてありますが、そのシートがある事が問題ならば集計シートにまとめることは可能です。

では、集計用シートのシート名を提示ください。シート名が、提示されたXXXになった時点で、個人シートが終わったと判断します。その為には、XXXの具体的な値を提示してください。

＞６．空欄はありません。すべて数値（時間も）です。
B5：I6の中に、時間も含まれているのですか。
マクロでは、どこが時間なのかわからないので、単純に数値を加算し、その平均をとりますがそれで良いですか。

tatsu99 · Answer

補足要求です。
１．集計用シートは、一番右側のシートであってますか。
２．個人用シートは、左から2番目のシートから開始し、集計用シートの手前で終了ですか。
３．集計用シートのB5:I6は、個人シートの合計値が設定されれば良いのですか。
４．集計用シートのB8:I9は、B5:I6の平均が設定されれば良いですか。
５．個人用シートのB8:I9は、集計用シートのB8:I9と同じ内容になりますか。
６．個人用シートのB5:I6は、空欄（データなし）もありますか。また、空欄があった場合は、それはデータ件数に含めませんが、それで良いですか。
例えば、個人シートが１０枚あって、個人１のB8＝１０、個人２のB8=２０、他の個人のB8＝空欄の場合、
合計は３０ですが、平均は３０÷２＝１５になります。（３０÷１０＝３ではありません）

CoalTar · Answer

名簿用シートの直後(一つ右)に「ここから」シートを挿入
集計用シートの直前(一つ左)に「ここまで」シートを挿入
串刺し計算は =SUM(ここから:ここまで!B4) のようにしておく
ただし、「ここから」シートと「ここまで」シートの位置が逆転すると数式が壊れます

fujillin · Answer

こんにちは

内容がよく理解できていない上に、直接の回答ではありませんが・・・

＞マクロを作ることが出来ません。
＞他多数の人が行う際に関数入力間違いや参照間違いの発生が危惧されます。
マクロでもできるでしょうけれど、メンテも何もできなくなるだけではないでしょうか？

同じ計算をさせるだけだのように思われますので、ひな形のブックなりシートを作成しておいてそれを使い回せば良いだけのように思えますが？
計算式を入れてある部分を上書きで壊されないようにしたければ、シートの保護などでそのセルを保護しておけば良いでしょう。

その時々で違う計算をさせたいと言う場合は、マクロでも対応できるのかどうか不明です。（規則性があれば可能だと思いますが）

いずれにしろ、ご質問の情報の内容だけでは、回答者にマクロを作成することはできませんね。（情報が不足しすぎ、というより、ほとんどありませんので）

複数シートの平均を算出し平均表を作成するマクロを教えてください

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

補足要求です。

名簿用シートの直後(一つ右)に「ここから」シートを挿入

こんにちは

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

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

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

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

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