エクセルのAverage関数で、INDIRECT関数を用いて参照する範囲を外部参照とする手法について教えて下さい。

Excel | Excel110閲覧xmlns="http://www.w3.org/2000/svg">25

ベストアンサー

0
画像

画像の「基本情報」シートを見ていただければと思いますが、評価者①~⑤は固定しておらず、2名の時もあれば、5名の時もあります。 例えば、評価者③まで名前が入っていれば、各シート「評価者トップ」、「評価者①」、「評価者②」、「評価者③」のシートに各評価者が評価対象の評価点を入力することとなるが、「評価者④」と「評価者⑤」のシートは活用されません。そのため、この条件でAvarageの計算を行うと、評価者④と⑤は数値がすべてゼロ扱いであり平均が薄まってしまうので(本来分母は4となるはずなのに、⑤まで計算されて分母6で計算されてしまう)、評価者④と⑤のシートを除いて計算したいと考えています。 故に、例えば本シートで入力される評価者の人数(COUNTA(C4:C9))の増減に合わせて、シート「1.(要入力)総括表」の(E11)に表示されているAvarageの計算が変動するようにしたいと考えています。

画像

ThanksImg質問者からのお礼コメント

質問が不鮮明で何度もやり取りすることになりましたが、丁寧にご助言をいただき大変助かりました。 ご助言内容も非常に的確で、大変勉強になりました。 改めて御礼申し上げます。

お礼日時:9/26 21:31

その他の回答(1件)

0

基本設定のシートには作業列を設けて対応します。 平均のデータを求めたい条件をA2セルから下方に入力するとしてA2セルには平均をとりたいシート範囲で最も小さいシートのシート名を例えばSheet1のように入力します。A3セルにはシートの範囲で最後のシートのシート名をSheet5のように入力します。 A4セルにはそれらのシートで平均を求めたいセルの範囲を例えばB4:B10のように入力します。 A5セルには上記の条件で求められる平均と文字を入力します。 そこで作業列ですがF1セルには次の式を入力して横方向にドラッグコピーします。 =IF(COLUMN(A1)=1,$A2,IF(RIGHT($A2,1)+COLUMN(A1)-1>RIGHT($A3,1)*1,"","Sheet"&RIGHT($A2,1)+COLUMN(A1)-1)) E2セルには合計とでも入力してからF2セルには次の式を入力して横方向にドラッグコピーします。 =IF(F$1="","",SUM(INDIRECT(F$1&"!"&$A$4))) E3セルには数値の数とでも入力してからF3セルには次の式を入力して横方向にドラッグコピーします。 =IF(F$1="","",COUNT(INDIRECT(F$1&"!"&$A$4))) その後にお求めの平均ですがB5セルには次の式を入力すればよいでしょう。 =ROUND(SUM(F2:J2)/SUM(F3:J3),1) 右側の作業列が目障りでしたらそれらの列を選択して「非表示」にすればよいでしょう。

画像の「基本情報」シートを見ていただければと思いますが、評価者①~⑤は固定しておらず、2名の時もあれば、5名の時もあります。 例えば、評価者③まで名前が入っていれば、各シート「評価者トップ」、「評価者①」、「評価者②」、「評価者③」のシートに各評価者が評価対象の評価点を入力することとなるが、「評価者④」と「評価者⑤」のシートは活用されません。そのため、この条件でAvarageの計算を行うと、評価者④と⑤は数値がすべてゼロ扱いであり平均が薄まってしまうので(本来分母は4となるはずなのに、⑤まで計算されて分母6で計算されてしまう)、評価者④と⑤のシートを除いて計算したいと考えています。 故に、例えば本シートで入力される評価者の人数(COUNTA(C4:C9))の増減に合わせて、シート「1.(要入力)総括表」の(E11)に表示されているAvarageの計算が変動するようにしたいと考えています。

画像