質問: エクセルのAverage関数で、INDIRECT関数を用いて参照する範囲を外部参照とする手法について教えて下さい。 シートが、「基本設定」に加えて、「シート1」から「シート5」まで5種類あり、例えば「基本設定」シートを通じて「シート1」のA1に「山田太郎」、「シート2」のA1に「日本はな子」などの名前を入力したシートのみを対象として、各シートに入力したデータ(数値)の平均をとりたいと考えています。 各シートの名称は、「基本設定」シートに記載しています。その時々で名前を入れる人数が変わるのですが、名称が入ったシートの数を「基本設定」シート上で「Counta関数」で数えることが可能です。そのため、Avarage(「シート1」:「シート●」!B4:B10)といったように式を作り、●の部分をCounta関数とリンクさせてAvarage計算をさせたいと考えていますが、うまくいきません。INDIRECT関数などを使うのかと思うのですが、Avarage関数とINDIRECT関数がうまく合致しなくて作業が止まりました。 この条件で、アドバイスをいただけると幸いです。
回答: 基本設定のシートには作業列を設けて対応します。 平均のデータを求めたい条件を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) 右側の作業列が目障りでしたらそれらの列を選択して「非表示」にすればよいでしょう。