エクセルで年齢別体重の平均

こんばんわ。
エクセルシートで下記の表があるとします。

　　A　 B
1　30　10
2　50　20
3　64　30
4　70　40
5　71　40
6　77　41
7　73　43
8　75　45
9　77　47
　（中略）
20　60　49
21　55　55

A列：体重、B列：年齢です。
表はB列の数字で若い順に上から下へ並んでいます。
列に対して名前（体重など）の定義はしていません。

40歳代のうち若い方から数えて3番目～8番目の人の体重の平均を取りたいと思います。
このような表がたくさんあるときに関数を使って簡単に処理したいのですが、どのようにすればよいでしょうか。
教えてください。
よろしくお願いします。

No.4 ベストアンサー 回答者： ja7awu 回答日時： 2003/07/17 10:54

INDIRECTも取っちゃって

=AVERAGE(OFFSET(A1,MATCH(39,B:B)+2,0,6,1))

No.5 回答者： imogasi 回答日時： 2003/07/17 22:03

ユーザー関数を作る解答を１つ。

（１）年齢順・体重順にソートして下さい。
　　　それぞれ昇順・降順は適当なものを選んでください。
（２）ＶＢＥの標準モジュールに下記を貼りつけてください。
Function avgr(age, rfrom, rto)
n = 0: s = 0: t = 0 '初期化
For i = 1 To 30 '第１行から第３０行までと仮定
a = Cells(i, "A") '年齢列について
Select Case a
Case age To age + 10
n = n + 1
If n > rfrom - 1 And n < rto + 1 Then '何番目か
s = s + 1
t = t + Cells(i, "B")
End If
Case Else
End Select
Next i
avgr = t / s '該当データ数、とその合計
End Function
（３）ワークシートのデータが入っているセル以外に
=avgr(40,3,8)と入れてください。求める平均が得られます。
また=round(avgr(40,3,8),0)などとも使えます。
簡単にするため４０台で２人しかいないとかのチェックは
手抜きしています。
（Ａ）上記s<rfrom、rfrom<rto、非負、非ゼロ
（Ｂ）sが０（該当者無し）で無い
などチェックが本来必要ですが。
別の方法で、簡単なのは年齢・体重でソートして、目で見て第３番目から第８番目のセル番地を読み
=average(B○:B○）と式を入れる方法です。

No.3 回答者： grumpy_the_dwarf 回答日時： 2003/07/17 10:11

40代の人が確実に8人以上いるのなら、

=AVERAGE(OFFSET(INDIRECT("A"&(MATCH(39,B:B))),3,0,6,1))
でおしまいです。

No.2 回答者： Taketoshi 回答日時： 2003/07/17 09:30

オートフィルタで抽出してから、平均をとってみては？

簡単な手順です。
1. 1列目に新規列を追加
2. A1に"体重"、B1に"年齢"、C1に"年齢"
3. C2に以下の式を設定。次行以降コピー
=INT(B2/10) & "0代"
4. [データ]-[フィルタ]-[オートフィルタ]
5. C列のプルダウンで、40代を選択すると、40代のみ抽出される

あとは、AVERAGE関数で平均を求めてください。

No.1 回答者： nihonjinn 回答日時： 2003/07/17 07:51

作業列を使います。

C1に
=IF(AND(B1>=40,B1<50),B1,100)
といれ必要なところまでコピーしてください。
40代の人の年齢を抜き出します。100と入っているのはエラー防止のための数値です。
D1に
=IF(C1<100,AND(RANK(C1,C:C,1)>=3,RANK(C1,C:C,1)<=8),A1,"")
といれ必要なところまでコピーしてください。
これで年齢が3番目～8番目の方の体重を抜き出します。
(しかしこれでは例えば8番目の年齢の人が複数いればサンプルは6人以上になってしまいます。）
平均体重は
=AVERAGE(D:D)
で出てきます。