averageif関数について

質問

№　　氏名　　　 売上　　　順位
１　　○○○○　11111　　　1
2　　 ××××　22222　　　2
3 △△△△ 33333 3
4 □□□□ 44444 4
5 ◇◇◇◇　55555 5

このような売上順位表があり、10人分データがある場合、
averageifを使って上位3名の売上平均を出す場合
=AVERAGEIF(D2:D11,"<=3",C2:C11)
下位3名の売上平均を出す場合
=AVERAGEIF(D2:D11,">=8",C2:C11)
という式になりますが、この表にデータが追加されたら上位は範囲と平均対象範囲を変えることになりますが　はじめから追加されることを見越して対応した式にすることは可能でしょうか？（Largeは使わずに）
また下位3名の場合の">=8"という条件も含めデータが増えた場合
smallを使わずaverageifで求められるでしょうか？
よろしくお願いします

回答No.4

watabe007 · ベストアンサー

>はじめから追加されることを見越して対応した式にする
=AVERAGEIF(D:D,"<=3",C:C)
=AVERAGEIF(D:D,">=" & MAX(D:D)-2,C:C)

お礼

回答ありがとうございます。
参考にさせていただきます。

回答No.3

imogasi

採集的に、質問したいことがよく分らないのだが。
入力ータ（特に行）が増加した場合でも、式を変えずに済む、ランクで限定した、売上平均を出したい（式を作りたい）ということと解釈して下記は書いている。
ーー
下記データをシートの下記セル範囲に作って、挿入-テーブル操作をして、で構造化する。
（ファイルーオプションー「数式の処理」「数式でテーブル名を利用する」、をONにしておくこと）
テーブル化されると、結果、第１行の見出しに当たる行の各列に▼が着く。色も変わり、データ部の１行おきに色が着く。
ーーー
例データ　A1:D16
氏名 売上 ランク 列1
山田 21 10 ヤマダ
植田 32 8 ウエダ
大田 11 13 オオタ
大村 45 5 オオムラ
河合 24 9 カワイ
神田 6 14 カンダ
木村 13 12 キムラ
吉岡 19 11 ヨシオカ
近藤 65 2 コンドウ
大野 36 7 オオノ
大川 46 4 オオカワ
北野 64 3 キタノ
中野 45 5 ナカノ
中北 2 15 ナカキタ
大島 72 1 オオシマ
＝＝
テーブル化して、結果、第１行の見出しに当たる行の各列に▼が着く。
本来この回答では、不要なのだが、D列のD2に入れる式は　=PHONETIC([@氏名])
式の複写操作はいらない。
＝＝＝
さて本題の、売上３位以上だけの行の、売り上げ平均は、例えば結果を、出すセルはG2として、G２の式は
AVERAGEIF(テーブル13[ランク],"<4",テーブル13[売上])
結果は　67,
検算は　72、65、64を足して２０１、３で割って６７
＝＝＝＝
これで最終行の次の行にデータを追加したりして、テスト・納得してみてください。
＝＝＝
今まで
（１）最終行を捉えるのはやめて、適当な行数＋アルファ行数を見積もり、そこの行までは==IF(A1="","",本来の関数式）を式複写して、データが途切れた行より下は空白になるようにした。
この点で、行数（デー数）が多いと式複写も手数だし、無駄な気もしていた。
その後Googleの表計算などで、FormulaArray関数が創設され、対抗上？MSも、構造化参照を（エクセル２０１０以後？）作った。エクセル使いの合う打で、流行っているかどうか、小生には判らないが、理論的ではあると思う。MSは主流にしたいのだろうが。
＝＝＝＝
「構造化参照」については、
Googleで「構造化参照」で照会し、
http://www4.synapse.ne.jp/yone/excel2013/excel2013_table_kouzouka.html
他、２－３記事読んでみてください。
（テーブル化は構造化参照以前からあるもののようですが。）

お礼

わかりにくい質問文にもかかわらず丁寧に回答いただきありがとうございます。
テーブルの機能もぜひ活用できたらと思います。
ありがとうございました。

回答No.2

chayamati

勘違いならゴメン
順位は売上の順位ですね、この項目を降順(Ｄ列）と昇順（Ｅ列)として式で求めます
　D2=RANK.EQ(C2,C:C,1)　E2=RANK.EQ(C2,C:C,0)
　これを縦にコピーします。
上位3件の平均=AVERAGEIF(D:D,"<=3",C:C)
下位3件の平均=AVERAGEIF(E:E,"<=3",C:C)
※アドレスで行№を外すとその列の全てが対象になります
尚、件数も入力セルを用意するとよいかも

お礼

ありがとうございます
順位は売り上げの順位です
思っていたイメージに近い式です
参考にさせていただきます

回答No.1

kkkkkm

上位
=AVERAGEIF(D2:D50,"<=3",C2:C50)
下位
=AVERAGEIF(D2:D50,">=" & MAX(D2:D50)-2,C2:C50)

のように範囲を大きくとっていればいいのではないでしょうか。

お礼

ありがとうございます。参考にさせていただきます。