この式はなぜエラー #VALUE! になるのだろう？

質問

この式はなぜエラー #VALUE! になるのだろう？

　　A　 B　 C　 D
1　　　 92　95　96
2
3　 60　67　99
4　 99　13　74
5　 92　58　96
6　 93　59　10
7　 81　51　10
8　 81　95　98
9　 88　45　 6

範囲 A3:C9 に上図のデータがあると仮定します。そして、
１行目の各セルに次の値を返したい、というのが問題です。
B1: セル A3、A5、A7 の最大値
C1: セル B4、B6、B8 の最大値
D1: セル C5、C7、C9 の最大値

１行目の各セルに次の配列数式を入力すれば希望通りの値が得られます。

B1: {=MAX(OFFSET($A3,0,0,5,)*MOD(ROW(A1:A5),2))}
C1: {=MAX(OFFSET($A3,1,1,5,)*MOD(ROW(B1:B5),2))}
D1: {=MAX(OFFSET($A3,2,2,5,)*MOD(ROW(C1:C5),2))}

OFFSET関数の引数 0、1、あるいは 2 のところは COLUMN関数で置き換えられるはずと考えて

B1: {=MAX(OFFSET($A3,COLUMN(A1)-1,COLUMN(A1)-1,5,)*MOD(ROW(A1:A5),2))}
C1: {=MAX(OFFSET($A3,COLUMN(B1)-1,COLUMN(B1)-1,5,)*MOD(ROW(B1:B5),2))}
D1: {=MAX(OFFSET($A3,COLUMN(C1)-1,COLUMN(C1)-1,5,)*MOD(ROW(C1:C5),2))}

としたところ、いずれもエラー #VALUE! が返ってきます。
なぜエラーになるのか理解できません。どなたか私にアドバイスをいただけませんか？

参考までに申し上げると、http://okwave.jp/qa/q5897615.html の質問の回答を考えているときに、上の問題に遭遇しました。

回答No.2

Wendy02 · ベストアンサー

COLUMN()もROW()も本来は配列で値を返しているはずです。
ただ、OFFSET関数の引数として、COLUMN()側は受け付けないようです。

配列の縦と横では、そのマトリックスの位置によって返す値が変わってくるのだろうとは思いますが、その先は、全部、推測の話になってしまうから、推測の上に推測の話はやめておきます。

私の考えた数式：
{=MAX(INDEX(OFFSET(A$3,COLUMN(A1)-1,0,5,1),,)*MOD(ROW(A1:A5),2))}

ただ、OFFSET関数やINDRECT関数は、再計算関数ですから、使いづらい関数だと思います。こういうものは、誰でも、やってみなければ分かりませんが、結果論だと思います。だから、関数は難しいですね。昔、私も同じような配列の質問したことがあった時に、「やってみなければ分からない」と言われましたね。

結局、私は、配列は、パターン以外のものは手を出さないほうが良いということになってしまいましたが、やっぱり、掲示板ではどこでも、新しい方法が見つかっているようですから、私のように、挑戦することは諦めてはいけないようです。（私は、あまり関数の回答はしないようにしていますが……)

単に、Lotus1-2-3との互換性というだけなのか、改訂されていない関数と、改訂されている関数とがあるようです。私は、本格的に、Lotus1-2-3をやってはいないので、詳しくありません。古い関数(VisiCalc側)と、新しい関数(Lotus側とMS側)では、関数にはどういうイキサツがあるかは分かりませんが、意外にも、問題はMS側は認識していることが多いようですね。しかし、変えようとはしないようです。

お礼

》 OFFSET関数の引数として、COLUMN()側は受け付けないようです

そのように考えた方が好いようですね。

》 私の考えた数式：
》 {=MAX(INDEX(OFFSET(A$3,COLUMN(A1)-1,0,5,1),,)*MOD(ROW(A1:A5),2))}

実にお見事だと思います。今回の場合は「A$3」は単に「A3」で可ですね。

》 「やってみなければ分からない」

同感です。

》 私は、あまり関数の回答はしないようにしていますが……

マクロ音痴の私は関数の質問しかできないので、私を見かけたら何とか回答されるようお願いしておきます。

》 問題はMS側は認識していることが多いようですね。
》 しかし、変えようとはしないようです。

仕様という名の口実ですかね。(^_^)

丁寧なコメントをいただいて感謝しております。ありがとうございました。

2010/05/16 21:35

回答No.1

keithin

その式を「数式の検証」を使って計算をトレースしてみると，COLUMN関数がOFFSET関数の中で配列を残してしまいエラーになっている様子を確認できます。

例えばその式であれば
=MAX(OFFSET($A3,SUM(COLUMN(A1)-1),SUM(COLUMN(A1)-1),5,1)*MOD(ROW(A1:A5),2))
などのように変えてみると，エラーを回避できます。

どの関数がこのように問題を起こし，また起こさないのか，明確に示されたガイドラインはありません。経験によって対処を考えていく事になります。
一般にこちらのような初心者さん向けの質問相談で，ただ「こんな便利な計算がありますよ」と配列数式をやみくもに披露しても「実際には使えない」理由のひとつも，この辺りにあります。

お礼

「COLUMN関数がOFFSET関数の中で配列を残してしまいエラーになっている様子」が私には理解できませんでした。

》 …などのように変えてみると，エラーを回避できます

私には難解すぎますが、仰るとおりであることを確認しました。

何れにしても解決策があることが分かったのは仕合せでした。ありがとうございました。