等間隔ごとのセルの平均値、最大値、最小値を求める関数式

質問

仕事で作業時間をエクセル表にまとめています。

A～Cに作業項番と作業内容、D,E,Fにそれぞれ作業時間の平均、最小、最大値が入っています。その次の列からそれぞれの作業時間が入っています。
ひとつの作業で4列分使い、左から作業開始時間、作業終了時間、所要時間、所要時間の小計と続いています。

今までは、平均、最小、最大をAVERAGE,MIN,MAX関数を使い、一つのセルずつ計算していました（例：=MIN(I9,M9,Q9,U9.Y9,AC9,・・・,)が、作業回数が30回を超えたところ、これらの方法が使えなくなってしまいました。（計算できる数値が30個までしかない）

30個以上の数値を計算させるためにはどうしたらよいのでしょうか。
また、式が長くなると管理が大変になってくるので、すっきりとまとめられるような関数があったら教えてください。

以上です。よろしくお願いします。

回答No.2

telescope · ベストアンサー

配列数式を使います。
=MAX(IF(MOD((COLUMN($I$9:$AP$9)-9),4)=0,$I$9:$AP$9,""))
CtrlキーとShiftキーを押しながら、Enterキーを押します。
数式には、自動的に ｛ } 　がついて、
{=MAX(IF(MOD((COLUMN($I$9:$AP$9)-9),4)=0,$I$9:$AP$9,""))}
のようになります。

例：=MIN(I9,M9,Q9,U9.Y9,AC9,・・・
の場合、列番号が I が9番目、Mが13番目・・・と４つ置きになっていますので、
範囲のなかで、列番号から９を引いた数を４で割った余りが０のものを計算するという意味です。AVERAGE,MAX関数も同じです。

例の通りでなかったら、適切に変えてください。
等間隔でしたら、なんらかの規則性があると思います。

お礼

配列数式というものを使うのですね。
早速試してみます。
名前の定義を使うやり方よりは式が複雑ですが、例を見ると結構汎用性があるような気がします。

貴重な情報どうもありがとうございました。

補足

上の式、試しさせていただきました。
重ねて質問なんですが、新しく作業時間を記入するときに式を書き直さなくて済むように、予め計算範囲を最右部(IV)まで広げたところ、意図しない計算結果になってしまいました。(おそらく空白セルが0として認識され、計算過程に盛り込まれたものと思われます。)
空白セルを計算対象からはずすようなことはできるのでしょうか。

再度お願いします。

回答No.3

telescope

こちらでどうでしょうか？

{=AVERAGE(IF($I$9:$IV$9<>"",IF(MOD((COLUMN($I$9:$IV$9)-9),4)=0,$I$9:$IV$9,"")))}

お礼

お礼遅くなってすいません。
上記の式を試させて頂きました。
理想通りの計算結果になりました!!

どうもありがとうございました。

回答No.1

wisemac21

例：=MIN(I9,M9,Q9,U9.Y9,AC9,・・・,)
この引数の参照範囲を名前の定義して、引数に定義名を使う方法があります。
=MIN(定義名)

お礼

該当セルごとに名前の定義をつけていく方法ですね。
早速試してみます。

貴重な情報ありがとうございました。