こんな表で一気に平均値出せますか？【エクセル】

こんな表で一気に平均値出せますか？【エクセル】

例えば、1行目から1万行目まで100人の人のあるデータが100行ずつ入っているとします。
A列＝人名、B列＝点数とします。
A列の1行目から100行目までを仮に【No.1】として、B列の1行目から100行目までランダムに点数が入っています。そして、この【No.1】の平均点を出したくて、C列の1行目に=AVERAGE（B1:B100）として、平均点を出します。
次に、【No.2】として101行目から200行目までのデータの平均点をC列の101行目に出します。
用は、このように【No.1】～【No.100】のそれぞれの平均点を出したいのですが、簡単にできる方法はありますか？
オートフィルタで【No.1】～【No.100】まで、それぞれ抽出して、式を入れればいいのですが、何しろ数が多いので、もっと簡単にできる方法があるか聞いてみました。
実際のデータは、100人以上いるし、データもそれぞれ100ぴったりではありません。

分かりにくい説明かもしれませんが、よろしくお願いします。

No.7 ベストアンサー 回答者： giougio 回答日時： 2010/07/21 11:46

いや、ここはすごく勉強になります。

僕は古いバージョンのExcelを使って、
ずっとネットのみの独学で勉強してたので、
AVERAGEIFなんて関数や、
ピボットテーブルなんて方法を知りませんでした……
ありがとうございます。

と、お礼投稿ついでに、古いVer.でAVERAGEIFっぽい方法を考えてみました。
※データの最初の名前のみ、IF関数でエラーになってしまいます

//////////////////////////////////////////////////////////////

A列、二番目の名前が5行目から始まっているとして、

=IF(A5=A4,"",SUMIF(A:A,A5,B:B)/COUNTIF(A:A,A5))

をC列に入れて、一番下までオートフィルでコピーします。
その後、C列をオートフィルターで空白を抽出、Deleteします。

※もし、A列に同じ名前がありそうな場合
1.D1に「1」を入力します。
2.D2に「=IF(A1=A2,C1,C1+1)」を入力します。
3.D2をオートフィルでデータの一番下までコピーします。
4.上記関数「=IF(A5=A4,"",SUMIF(A:A,A5,B:B)/COUNTIF(A:A,A5))」の
「A」を「D」にして、後の手順は同じです。
→=IF(D5=D4,"",SUMIF(D:D,D5,B:B)/COUNTIF(D:D,D5))

※手順2～3＝名前を上からナンバリング
※レイアウト的にはナンバリングした番号がD列という微妙な位置になってしまうので、
　必要であれば、調整、もしくは非表示にした方が良いと思います

No.6 回答者： banzaiA 回答日時： 2010/07/20 21:24

＃５です。

ごめんなさい、間違えました。訂正します。


=AVERAGEIF($B:$B,$A$1,$B:$B)
ではなくて

=AVERAGEIF($Ａ:$Ａ,$A$1,$B:$B)
これでセル(Ａ１）にある項目の、Ｂ列の平均値がでます。

=AVERAGEIF($B:$B,$A$101,$B:$B)
ではなくて

=AVERAGEIF($Ａ:$Ａ,$A$101,$B:$B)

これでセル(Ａ101）にある項目の、Ｂ列の平均値がでます。

No.5 回答者： banzaiA 回答日時： 2010/07/20 20:54

エクセルは2007ですか？

関数
AVERAGEIF
は使用できますか？
できるという前提で、すすめます。

平均値を表示させるセルに、次の式を入れます。
=AVERAGEIF($B:$B,$A$1,$B:$B)
これでセル(Ａ１）にある項目の平均値がでます。

=AVERAGEIF($B:$B,$A$101,$B:$B)
これでセル(Ａ101）にある項目の平均値がでます。


質問内容から平均値を求める項目は１００個にも登ると思われますので、
平均値だけを表示するシートを別に追加した方が見やすいかなって老婆心ながら。

的外れの回答ならごめんなさい。

No.4 回答者： tom04 回答日時： 2010/07/20 20:23

こんばんは！

少し強引な感じもしますが・・・

1行目は見出し行で2行目からデータがあるとします。
一例です。
表示したいセルに
=SUMPRODUCT(($B$2:$B$10001>=(ROW(A1)-1)*100+1)*($B$2:$B$10001<=ROW(A1)*100)*($B$2:$B$10001))/SUMPRODUCT(($B$2:$B$10001>=(ROW(A1)-1)*100+1)*($B$2:$B$10001<=ROW(A1)*100))
（エラー処理はしていません。）

としてオートフィルで下へコピーではどうでしょうか？

とりあえずこれで2行目からのデータの100行ごとの平均が出ると思います。

以上、参考になればよいのですが
他に良い方法があれば読み流してくださいね。m(__)m

No.3 回答者： imogasi 回答日時： 2010/07/20 10:47

エクセルを使い出した初心者は、関数利用に凝り固まりがち。

「何でも関数」に見える。
この質問もそうでないか。
エクセルにはピボットテーブルという便利な機能があり、広く使われているのだ。
それを勉強のこと。
操作
データーピボットテーブル・・・
例データ
氏名計数
a10
b2
a3
c2
b4
a5
結果
平均 / 計数
氏名合計
a6
b3
c2
総計4.333333333
レイアウトのデータのところでダブルクリックして、普通は合計が既定値だが、そのところを、平均に直すだけ。
配列数式、ＶＢＡなどの方法もあるが、時間がかかり馬鹿らしい。

No.2 回答者： giougio 回答日時： 2010/07/20 07:49

マクロが使える環境ならば、以下のマクロを実行してみてください。

A列の名前が別の名前になった時、平均計算の関数をC列に書き込みます。

A列
No.1　※C列＝A1～A3の平均
No.1
No.1
No.2　→この段階で※の計算を書き込みます。
No.2



また、A列の開始行は「1」にしてありますが、
変更する際は、「StartRow = 1」の1の部分の数字を変更して下さい。

また、実行後、「！」とエラー（？）が出ると思うので、
表示したくない場合は、
ツール→オプション→エラーチェック
『設定』の「バックグラウンドでエラーチェックを行う」か、
『ルール』の「領域内のセルを除いた数式」の
どちらかのチェックボックスのチェックを外して下さい。


Sub A列名前、B列データでC列に平均値()

Dim StartRow As Long 'A列最初の開始行用変数
Dim RowSave As Long 'データの範囲選択とC列書き込み位置用変数
Dim String_RowSave As String '書き込む際に文字列に変換する為の変数
Dim String_i As String '書き込む際に文字列に変換する為の変数
Dim NumberName As String '名前を覚える為の変数

'A列の最初の開始行
StartRow = 1

RowSave = StartRow
NnumberName = Cells(StartRow, 1)
For i = StartRow To Cells(65536, 1).End(xlUp).Row

'名前が違う場合、今までの範囲のデータの平均を、C列の名前の最初の行に書き込む
If Cells(i, 1) <> NumberName Then
String_RowSave = RowSave
String_i = i - 1

Cells(RowSave, 3) = "=AVERAGE（B" + String_RowSave + ":B" + String_i + "）"

RowSave = i
NumberName = Cells(i, 1)
End If

Next

'最終のデータの処理
String_RowSave = RowSave
String_i = i - 1
Cells(RowSave, 3) = "=AVERAGE（B" + String_RowSave + ":B" + String_i + "）"

End Sub

No.1 回答者： KURUMITO 回答日時： 2010/07/20 06:57

正確に100行毎に平均点を求めるのでしたら、例えばＣ1セルにＮｏ1とでも入力して下方にオートフィルドラッグします。

Ｃ100セルにはＮｏ100のように表示されませね。
そこで平均点ですがＤ1セルには次の式を入力し下方にオートフィルドラッグします。

=IF(INDEX(B:B,ROW(A1)*100)="","",AVERAGE(INDEX(B:B,ROW(A1)*100-99):INDEX(B:B,ROW(A1)*100)))

これでＤ100セルの間に100行毎の平均点が表示されます。