エクセルの各シート間にまたがる統計処理について質問です

質問

エクセルの各シート間にまたがる統計処理について質問です

エクセルの各シート間にまたがる統計処理について質問です

　例えばsheet1～sheet20までのｃ６セルのうち、一番大きい数字を統計処理用シートのA２に、一番大きな数字のsheetの A1（subject ID)を統計処理用シートのB2に、大きな数字のsheetのB2(subject NAME)を統計処理用シートのC2に代入するといった作業はどんな関数を用いればいいのでしょうか？

　教えてください。お願いします

回答No.4

MackyNo1

>MackyNo1さんに教えていただいたやり方ではシート１～２０まででしたが、２０のとこを「一番後ろにあるシート」にするにはどんな関数（？）を使えばいいんでしょうか？たぶんこれからシートの枚数が膨らんでくるので、シート番号指定だといちいち数式書き直さないといけないですよね・・・

基本的に関数で集計する場合は、集計対象が変更になれば当然のことですが以下のように数式を変更する必要があります。

=MAX(最初のシート名:最後のシート名!C6)

この場合でも、最大値については、上記のように最初のシート名と最後のシート名で串刺し集計ができますが、参考までに提示した各シートのA1セルを参照する数式では、シート名が「Sheet1」のようにSheet+数字になっているケース以外には適用できません。

もし、各シートのC6セルやA1セルの値でデータ処理したいなら、別シートに各シートのデータを引っ張ってきて、このデータ範囲を数式処理をする方が簡単です。

ちなみに、この場合でもシート名が数字で規則的になっているなら、以下のようなINDIRECT関数が利用できます（例えばSheet2以下のA1セルを表示する場合）。

=INDIRECT("Sheet"&ROW(A2)&"!A1")

回答No.3

tom04

No.2です！
ある程度希望に近い形になったみたいでよかったですぅ～！

＞C3にサブジェクト名を・・・
とありましたが、質問では「C2」に代入となっていましたので、Offset部分をあのようにしてみました。
C3セルに表示したいのであれば、前回のコード内の

.Offset(, 2) = Worksheets(k).Range("B2")
の行を
.Offset(1, 2) = Worksheets(k).Range("B2")
に変更してみてください。
C3セルに表示されると思います。

それから
＞最大値と最小値を１～１０位まで抜き出したり・・・
とありますので、
前回のコードの最終行
ws1.Columns(1).Delete (xlToLeft)
を削除して一度マクロを実行してみてください。
作業用の列としてA列を挿入し、A列に各SheetのC6の値を表示させ、
その後の処理をして最後にA列を削除するコードにしていましたので
A列のデータを残したままにすれば最大値と最小値の　1～10位までの抜き取りも
Large関数・Small関数を使用すれば可能だと思います。

尚、余計なお世話かもしれませんが、
A列に表示されたデータが各SheetのC6のデータになっていて、
A列の行番号が各SheetのSheet番号（何番目のSheetか）になるようにしていました。

具体的にどのセルに順位を表示させれば良いのか判らないので
この程度しか書けません。ごめんなさい。

お役に立てばよいのですが・・・m(__)m

回答No.2

tom04

こんにちは！
VBAで無理矢理って感じの方法になってしまいますが・・・

統計処理用シートをSheet1として、20SheetはSheet2以降にあるとしています。

Altキーを押しながらF11キーを押してみてください。
VBE画面が出ますので、↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test()
Dim i, j, k As Long
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet1")
j = ws1.Cells(Rows.Count, 1).End(xlUp).Row
ws1.Columns(1).Insert
For i = 2 To Worksheets.Count
ws1.Cells(i, 1) = Worksheets(i).Range("C6")
Next i
k = WorksheetFunction.Match(WorksheetFunction.Max(ws1.Range("A:A")), _
ws1.Range("A:A"), False)
With ws1.Range("B2")
.Value = Worksheets(k).Range("C6")
.Offset(, 1) = Worksheets(k).Range("A1")
.Offset(, 2) = Worksheets(k).Range("B2")
End With
ws1.Columns(1).Delete (xlToLeft)
End Sub

尚、同じ最大値が複数Sheetにある場合は最初のSheetのデータが表示されると思います。
そして、一旦マクロを実行すると元に戻せませんので、
できれば別Bookにコピーしてマクロを試してみてください。

以上、参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m

お礼

統計処理用シートのA2セルに各シート同一セル上の最大値を求めるのと、B2セルに最大値を出したシートのサブジェクトIDを出すことができました！C3にサブジェクト名を出すことはできませんでしたが、VBAの記述をいじりながらチャレンジしてみます。
　あと、最大値と最小値を１～１０位まで抜き出したりとかもやってみます。

　学校でグループワークの実験レポートを作成してるんですが、「お前パソコン詳しいからエクセル係な」って無茶振りされて（笑）、困ってたので本当に助かりました。

ありがとうございました。

回答No.1

MackyNo1

多数のシートの同じセルに入力されている数字の最大値を求めることは以下のような関数で可能ですが、その最大値の含むシートのセルの値を求める関数はありません。

A2セル
=MAX(Sheet1:Sheet20!C6)

少し工夫するなら、例えば、B2セルに以下のような関数を入力しておき、数式バー上で、「INDIRECT("Sheet"&ROW(A1:A20)&"!A1")」と「INDIRECT("Sheet"&ROW(A1:A20)&"!C6")」の部分をそれぞれドラッグして選択し、F9キーを押して配列定数にすれば、最大値の含むシートのA1セルの値を表示できます。

B2セル
=INDEX(INDIRECT("Sheet"&ROW(A1:A20)&"!A1"),MATCH(A2,INDIRECT("Sheet"&ROW(A1:A20)&"!C6"),))

お礼

ありがとうございました。さっそくやってみました。

　今回の処理では、シート２以降サブジェクトごとの主に数字データを入力していき、シート１を統計処理用にして各シートの同一セルの数字を参照してサブジェクト順に並べたりということをしたいんです。
　MackyNo1さんに教えていただいたやり方ではシート１～２０まででしたが、２０のとこを「一番後ろにあるシート」にするにはどんな関数（？）を使えばいいんでしょうか？たぶんこれからシートの枚数が膨らんでくるので、シート番号指定だといちいち数式書き直さないといけないですよね・・・

　ほんとは自分で調べないといけないんだろうけど、ちょっとせっぱつまってて困ってます。

　教えていただけると嬉しいです。