[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『上位〇番までの平均』（MECO）
自分なりに考えましたが、ギブアップです。
問い）
C,D列にクラス毎のデータが下表のように入力されており、
A,B列にクラス毎の上位○番目までの平均値を表示する
表）
A B C D クラス 平均値 クラス データ a * a # b * a # a # クラス データ b # b #
※データは数値以外に空欄あり、数値は整数３桁まで
※平均値は下１桁で四捨五入
※クラスは40まで、C,D列は1000行まで
よろしくお願いします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
こんなことかな
B2:=ROUND(SUMPRODUCT(LARGE(($C$2:$C$100=A2)*$D$2:$D$100,COLUMN(A1:Z1))*(COLUMN(A1:Z1)<=○))/○,)
D列に 「データ」のように文字を入力しないで下さい （By） 2014/04/04(金) 22:56
実際のデータをコピペして試行しましたが、「#VALUE!」になります。
（○には「3」を入力）
>D列に「データ」のように文字を入力しないで下さい
に、抵触するのが原因と思われますが、第三者から提供されるデータを扱う為
これはできません。（都度、加工するのが面倒なので＾＾；）
説明不足ですみません。
尚、マクロではなく関数式で、できれば配列数式（CSE入力）は避けたいです。
わがままいいますが、よろしくお願いします。
（MECO） 2014/04/05(土) 07:21
配列数式ですが
=ROUND(SUM(LARGE(IF($C$2:$C$100=A2,$D$2:$D$100,),{1,2,3}))/3,)
（By） 2014/04/05(土) 08:06
うまくいくようです！！
6番目までなら、（略）,{1,2,3}))/3,)を（略）,{1,2,3,4,5,6}))/6,)に
すればいいんですね？
式の意味は、これから考えてみますが、先ずはこのまま使用させていただきます。
配列数式以外でありましたら、また教えてください。。
（MECO） 2014/04/05(土) 09:18
ちょっと、お聞きしたいのですけど、
１．同じクラスは、一つの範囲に固まっているんですか？ それとも、下の方で「a」がまた出現したりするんですか？
２．データが下図の様だった場合、上位3番目の平均は何になるんですか？ （同値があるなんて考えなくていいのですか？）
C D クラス データ a 3 a 3 a 2 a 2 a 2 a 1
３．極端な話、データが2個しかないクラスで、 上位3番目までの平均を求めるなんてことはやらないと考えていいですね？
（半平太） 2014/04/05(土) 17:43
２．同値はあり得ます。
例示の場合、「3+3+2」の平均にしたいです。
３．各クラス、10個～20個程度のデータです。
上位〇番目の〇は、任意に変えられるように特定セルを
絶対参照するつもりです。
説明不足でした、よろしくお願いします。
（MECO） 2014/04/05(土) 18:16
＞１．クラスは、固まっています。
固まっているなら、こんなので良さそうな気もしますけど、 実データでやると、どうなるのでしょうね。。。
B2セル =ROUND(AVERAGE(INDEX(LARGE(OFFSET(D$1,MATCH(A2,C:C,0)-1,0,COUNTIF(C:C,A2)),ROW(INDIRECT("1:"&E2))),0)),0)
<結果図> 行 ___A___ ___B___ ___C___ ___D___ ___E___ 1 クラス 平均値 クラス データ ○番目 2 a 25 a 30 3 3 b 54 a 30 2 4 a 15 5 a 15 6 a 2 7 a 2 8 a 1 9 クラス データ 10 b 44 11 b 55 12 b 53
（半平太） 2014/04/05(土) 20:27
ただ、昨晩考えてたのですが、AVERAGE(INDEX(LARGE．．．が
さっぱりわかりません（涙）
INDEX関数って、ふだん使うことないですが、このように
使うものなんですね？
確かに、他の質問回答でよくみかけましたが、それぞれ用途が
違うんでしょうね～
もう少し“あがいて”みますが、わかる気がしません．．^ ^;
（MECO） 2014/04/06(日) 09:06
＞確かに、他の質問回答でよくみかけましたが、それぞれ用途が ＞違うんでしょうね～
いえ、多分同じです。
配列数式回避策として常套手段化しています。
※ Index(…,0) の部分を「…」だけにして、CSE入力すれば同じことです。
（半平太） 2014/04/06(日) 13:50
>配列数式回避策として常套手段化して．．
そうでしたか、よくみかけるわけですね。
ところで、「ROW(INDIRECT("1:"&E2))」は一体何をしているのでしょう？
〇番目までのをINDEX関数で“格納”して、それを「平均」している．．
というイメージを持っているのですが．．違いますか？
解説、お願いできますでしょうか？
（MECO） 2014/04/06(日) 18:13
＞ところで、「ROW(INDIRECT("1:"&E2))」は一体何をしているのでしょう？
＞〇番目までのをINDEX関数で“格納”して、それを「平均」している．．
＞というイメージを持っているのですが．．違いますか？
その通りです。
＞6番目までなら、（略）,{1,2,3}))/3,)を（略）,{1,2,3,4,5,6}))/6,)に
こんな決め打ちでなく、E2セルで指定されるファクタ数の配列を「動的」に作る仕掛けです。
（半平太） 2014/04/06(日) 19:11
>E2セルで指定されるファクタ数の配列を「動的」に作る仕掛け
確かに、「ROW(INDIRECT("1:"&E2))」部分を「Ｆ９」キーでみると
「{1;2;3}」と表示されますが、この式で何故そうなるか？．．が
INDIRECT関数を調べても“さっぱり？？”なわけで．．（涙 ^ ^;）
急ぎはしませんが、もう少し解説お願いできますか？
あるいは、参考になるものの紹介でも．．。
よろしくお願いします。
（MECO） 2014/04/06(日) 21:42
＞もう少し解説お願いできますか？ ＞INDIRECT関数を調べても“さっぱり？？”なわけで
INDIRECT()は、文字列でセル範囲を指定できる関数です。
なので、ROW(1:3)は → ROW(INDIRECT("1:3")) と書けます。
E2セルに3が入っていたら、ROW(INDIRECT("1:"&E2)) と書いても同じ意味になります。
（半平太） 2014/04/07(月) 10:07
>INDIRECT()は、文字列でセル範囲を指定できる関数です。
この部分は、ようやく理解できました^ ^;
が、(By)さんの回答にもありますが、LARGE(範囲,順位)の「順位」のところを配列（？）にする部分が理解できません。
ネットで調べてるのですが、なかなかいい説明を見つけられません。
（単に能力不足に過ぎませんが．．涙）
ここがわからない限り、ダメなんでしょうね～。
許されるなら、解説願えないでしょうか？
（MECO） 2014/04/09(水) 11:24
＞LARGE(範囲,順位)の「順位」のところを配列（？）にする部分が理解できません。
順位のところを配列にしなかったら、順位と同じ数だけLARGE関数を書かなきゃなりません。
=AVERAGE(LARGE(範囲,1),LARGE(範囲,2),LARGE(範囲,3))
3番目までなら我慢も出来るでしょうが、10番目でもやる気が起きますか？
起きなければ、順位だけが異なるのであり、しかも連番になっている。
ならば、この形を工夫してクリア出来ないかと思案するのが自然ではないですか？
↓
=AVERAGE(LARGE(範囲,{1,2,3}))
=AVERAGE(LARGE(範囲,ROW(1:3))) ←配列数式
=AVERAGE(INDEX(LARGE(範囲,ROW(1:3)),0))
（半平太） 2014/04/09(水) 17:27
「LARGE(範囲,{1,2,3})」とした場合、最初の１番目の結果のみが表示されますよね？
それが「AVERAGE(LARGE(範囲,{1,2,3})」ではちゃんと３番目までの平均が計算されます。
ここのところがわからないため、前に進めません．．
「LARGE(範囲,{1,2,3})」でＦ９キーを押すと、{１番目の結果,２番目の結果,３番目の結果}が
表示されるので、“それでいいのだ”と思うしかないのでしょうか？
オツムの血の巡りが悪いので、どうもスッキリしないのですが．．（涙）
（MECO） 2014/04/09(水) 18:17
AVERAGEは、元々1つ以上の引数の平均を出す関数なんですから、 配列ファクタを全て考慮にいれて結果を出してくれるのは、 当たり前すぎる気がしますけど。
LARGE(範囲,{1,2,3})で、複数の値が表示されないのは、
一つのセルには一つの結果しか表示できないからです。
1番目か3番目かエラーかは分かりませんが、とにかく一つの結果しか
表示できないです。それも当たり前すぎる気がします。
で、実際には、配列は1番目が表示される。 2番目も見たければ、2セル同時選択して、配列数式で入れることになる。 これも、配列に関しては、常識中の常識です。
（半平太） 2014/04/09(水) 19:40
>2番目も見たければ、2セル同時選択して、配列数式で入れることになる →恥ずかしいことですが、セルを選択するときに“縦”に選択しており（当然同じ値）わけがわからなくなっていました。
昨晩、本稿を最初から何回も読み返し、ようやく一歩前へ進めそうな気になりました^ ^;
>=AVERAGE(LARGE(範囲,{1,2,3}))
>=AVERAGE(LARGE(範囲,ROW(1:3))) ←配列数式
>=AVERAGE(INDEX(LARGE(範囲,ROW(1:3)),0))
↑も理解でき、全てスッキリしました！
本当に丁寧な説明、ありがとうございました。
（MECO） 2014/04/10(木) 13:34
＞→恥ずかしいことですが、セルを選択するときに“縦”に選択しており（当然同じ値）わけがわからなくなっていました。
多分、もう一つ分かっていないことがあるのかも知れないです。
配列は、縦方向と横方向 があると云うことです。
{1,2,3} これは右方向
{1;2;3} これは下方向
ROW(1:3) これがどちらなのか、ご自分で判断して下さい。
（半平太） 2014/04/10(木) 14:33
=AVERAGE(LARGE(範囲,{1,2,3}))で{1,2,3}を{1;2;3}にしても同じ結果ですよね？
ところが、{=AVERAGE(LARGE(範囲,ROW(1:3)))}でROW(1:3)をCOLUMN(1:3)だと「#NUM!」です。
（その下の式も同様）
何故1番上の式では“縦”“横”同じ結果なのに、下2つでは“縦”しかダメなんでしょう？？
ROWは“縦”、COLUMNは“横”ではないのでしょうか？
あるいは、{1;2;3}とROW(1:3)ではそもそも概念（？）が違う？
訂正）
{1;2;3}とROW(1:3)の概念（？）云々ではなく、
配列数式とINDEX関数は“縦”しか使えないということでしょうか？
あるいは工夫すれば、COLUMN関数も使えるということでしょうか？
（MECO） 2014/04/10(木) 16:05
こんな感じでもいいかな(OFFSET関数、INDIRECT関数が嫌いなので・・) =ROUND(AVERAGE(LARGE(IF($C$2:$C$100=A2,$D$2:$D$100,),ROW(A1:INDEX(A:A,E2)))),0) 配列数式です
因みにCOLUMN関数を使用すれば =ROUND(AVERAGE(LARGE(IF($C$2:$C$100=A2,$D$2:$D$100,),COLUMN($A$1:INDEX($1:$1,E2)))),0)
{1,2,3} これは右方向 COLUMN(A1:C1)
{1;2;3} これは下方向 ROW(A1:A3)
>ROW(1:3)をCOLUMN(1:3)だと「#NUM!」です ROW(1:3)をCOLUMN(A:C) （By） 2014/04/10(木) 18:12
>ROW(1:3)をCOLUMN(A:C) そうでした、また恥ずかしいマチガイを．．＾＾；
“配列”についてはもっと勉強します．．
（MECO） 2014/04/11(金) 11:33
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.