数式中の範囲指定がうまく引き渡させれません

質問

ｘｐで２００７のエクセルを使っています
今日久しぶりに配列数式でフォームを作ろうとしたのですが
一部の範囲指定が上手く引き渡されません
引き渡しに障害か出て
正しく配列定数(？)として認識されない部分を
indexでくくっても改善されません
どうしたものでしょうか？

回答No.7

end-u · ベストアンサー

あ、書き忘れました。
>マイクロソフトが理不尽な非公開仕様変更を頻繁に重ねる中で、
>現時点の短い間では、OFFSET内にcolumn　を含ませることに問題があると知ることが出来ました、
『現時点の短い間では』というのが何を指しているのか定かでないですが、
『OFFSET内にcolumn　を含ませることに問題がある』のは前からじゃないですかね。
少なくともExcel97でも同じ現象ですから。

>【配列数式において、OFFSET関数の引数にROW関数やCOLUMN関数は使えない。】
これはちょっと訂正。強調し過ぎです。
『OFFSET関数の引数にROW関数やCOLUMN関数を使った場合、
　配列数式で正しい答えを出してくれないケースもある』...な感じでいいでしょうか。

回答No.6

end-u

私の事は愚者と思って笑い飛ばして頂いて結構ですよ。

あなたがどれほどの回答スキルをお持ちかわかりませんが
700件を超える回答歴をお持ちの方だとは、回答前にプロフィールを確認して知っていました。

最初はあえて断言を避けて、少しのヒントを提示すれば
ご自分で解決できるレベルの方かなと思いました。
回答者の立場で参加した事がある方なら、
質問者の方には回答した内容を試してみたのかどうか、試した結果どうだったのか、
質疑の流れの中で応答していく事によって解決につながっていくのは経験済みでしょう？
私の回答に対するあなたの応答にはそれがありません。
最初から本スレッドの流れを何回も読んでみてください。

「鱗」って言ってみれば先入観ですよね。
スキルが高くても、他人の意見を受け容れる素直さや謙虚さが無ければ、
それ以上の成長は難しくなります。
相手がスキルが低かろうが、年少者だろうが、ある１点において自分が学ぶべきものがあれば
真摯に対応すべきではないですか。
ましてあなたは質問者の立場だったんですし。
別に「無知」と決めつけたわけではなく、ただ単に噛み合わなかっただけです。
回答#5のように掻い摘んで記述しなければ、結局理解頂けなかったわけでしょう？

まあ、『他人の意見を受け容れる素直さや謙虚さ』
これは私自身にも言える事なので
>余りにもこちらを無知と決めつけているかのように伺えましたので、
というあなたが感じた事実は覆らないでしょうから、今後は気をつけるようにします。
>それを見抜けないことこそ最も愚かしい愚者の表れと存じます。
これも見抜けませんでしたので、愚者の称号を謙虚に受け容れます。
なんでそんな爪の話になるのか自体、理解できませんのでやっぱり愚者なんでしょうねえ。

最後に。
>OFFSETもINDEXも揮発性関数なのですね。
昔はそうだったらしいですけど、INDEXは今は違うようですよ。
確認してみてください。
この点は別に結果を教えて頂かなくても結構ですから、これで締め切って頂いて構いません。
返信も要りません。

回答No.5

end-u

『参照の引き渡し不具合の解消であること』が目的ですよね？
【配列数式において、OFFSET関数の引数にROW関数やCOLUMN関数は使えない。】
使えないから手法を変えないといけません。
揮発性関数である、重たいOFFSETをそんなに使いたいのであれば
COLUMNS関数を使った代案も提示していますから使えば良いでしょう。
>試したりはなさらないのでしょうか？
と再度問いかけしたのに結果報告もないとは。

『PF9で局部的に結果を検証した場合意図した答えが得られる』
ここの検証が足りません。
>但し
>「ＰＦ９での検証時は検証順序を違えてない」
>と　しての話で御願いします。
そんな仮定は成り立ちません。

新規シート
A1セル：a
B1セル：a
B2セル：b
B3セル：c
C1セル：=OFFSET($A$1,COLUMN()-3,0)=$B$1:$B$3
C2セル：=OFFSET($A$1,COLUMNS($A1:C1)-3,0)=$B$1:$B$3
（C2セルに関しては検証の結果の代替式。比較のために提示）

C1式[F9]の結果
{#VALUE!;#VALUE!;#VALUE!}
C2式[F9]の結果
{TRUE;FALSE;FALSE}

望む結果は{TRUE;FALSE;FALSE}のはず。
C1式　OFFSET($A$1,COLUMN()-3,0)　[F9]の結果
{"a"}
{}つきの配列が返っている。

C1式　COLUMN()-3　[F9]の結果
{0}
{}つきの配列が返っている。

C1式　=OFFSET($A$1,COLUMN()-3,0)=$B$1:$B$3　の　COLUMN()-3　を直値に変えてみる。
=OFFSET($A$1,{0},0)=$B$1:$B$3
結果　{#VALUE!;#VALUE!;#VALUE!}

=OFFSET($A$1,0,0)=$B$1:$B$3
結果　{TRUE;FALSE;FALSE}

どうやらOFFSET関数の引数に配列を渡している事が不具合の原因らしい。
A列で　=COLUMN()　の式を[F9]確認すると
{1}
{}つきの配列が返っている。
【配列数式において、OFFSET関数の引数にROW関数やCOLUMN関数は使えない。】
バグなのか仕様なのかはわからないが、
OFFSET($A$1,COLUMN()-3,0)
と同じ結果を返す別の関数を使えば良い。
...が検証の結論。

お礼

以下はいうべき事ではないと思っていたので割愛していました、
申し訳在りません。

実は私は数年前にはこちらでトップクラスの回答者をしておりました、
恐らく他者の方に解答をされるような、
貴方ほどのレベルの方からの質問に対し
更に指南・解答する役割をしていたと思います。

で、
その際には全く問題なく動作していたと記憶している書き方が
今回ブランクを経て、動かなかった為、動揺し、
非公開内部仕様変更の情報を欲し
スレを起こしたという経緯があります。

INDEXは敢えてわざと使わなかったとお見知りおき下さい。

使えることは火を見るより明らかと知っていたので
検証しませんでした。
申し訳在りません。

ただこういうことは
もう御分かりの通り表すと角を立てるので
書くことを避けたかったのですが、
余りにもこちらを無知と決めつけているかのように伺えましたので、

御心を砕いて頂いた恩に報いたく、
御身のこれからの助けとなればと敢えて申し上げることを
踏み切りました。

人は爪を隠します、
見えている様が全ての能力とは限りませんし、
それを見抜けないことこそ最も愚かしい愚者の表れと存じます。
本当に「お気を害さねば良い」と気が気でならないのですが、…
真摯に他者の苦言を受けて糧と出来る方かが心配でならないのですが、…
貴方がそうでない愚者だった場合は平に御容赦下さい。

あと、
２点感謝を述べさせて頂ければと思います、

１つ目は
マイクロソフトが理不尽な非公開仕様変更を頻繁に重ねる中で、
現時点の短い間では、OFFSET内にcolumn　を含ませることに問題があると知ることが出来ました、
INDEXを掛けることで対処できるかと思いますが、
全く視点の外に放置していましたので物凄く「目から鱗」感を感じました。
ご指摘感謝致します。

もう１つは
揮発性関数という観念を知らなかったので
これについても学ぶことが出来、感激しました。
OFFSETもINDEXも揮発性関数なのですね。

実行スピードを気にしてプログラミングするのは
以前よりしてはいましたが、
揮発性関数と言う新たな開拓すべき余地を見いだせた気がして、
本当に少しではありますが心躍る思いです。

感謝致します。

回答No.4

end-u

繰り返しになりますが。

配列数式がうまく結果を返さない場合に、検証して調べる方法として。
■[数式の検証]では単独の結果しか返さないので使えない。
■元々の配列数式をいくつかのかたまりに分解して別セルに入力し、それぞれが望む結果を返すか確かめる。
もしくは
■数式バーでいくつかのかたまりごとに選択して[F9]キーで結果を検証する。
　（※目視で確認したら[Esc]キーです。[F9]キー結果の配列を確定しろという意味ではありません）

実際の関数の提示があれば、他の回答者の方も検証できるはずです。
提示がなければ自分で調べるしかないです。

>どうしたものやら…
だ　か　ら

OFFSET(データシート!$A$1,INT((COLUMN()-1)/2),0,1,1)

ここのOFFSETとCOLUMNの組み合わせが問題なので、

INDEX(データシート!$A$1:$A$100,INT((COLUMN()-1)/2)+1)

を使えば良いです。
って書いてるんですが、試したりはなさらないのでしょうか？
OFFSET(データシート!$A$1,INT((COLUMNS($A1:B1)-1)/2),0,1,1)
とか
OFFSET(データシート!$A$1,INT((INDEX(COLUMN(),1)-1)/2),0,1,1)
でもいいんですけど。
ROW関数やCOLUMN関数は結果が配列になるためOFFSET関数の引数としてその結果である配列を渡すと不具合。

お礼

度重なる来訪　有り難う御座います。

OFFSETはただの参照なのに対して
INDEXは別テーブルを内部的に別途作成、保持するので
高負荷が予想されます。

私はペンティアム3を未だに使っていることもあって
極力避けたく思うのです。

また、
このスレの趣旨が参照の引き渡し不具合の解消であることも考えると…
申し訳在りません。

尚、
PF9で局部的に結果を検証した場合
意図した答えが得られるにも関わらず
全てをＥｘｃｅｌに任せると意に反する答えを吐く場合は
どうすれば宜しいでしょうか？

但し
「ＰＦ９での検証時は検証順序を違えてない」
と　しての話で御願いします。

回答No.3

end-u

>エラーになるので数式の検証を施行させてみたのですが
>それによるとSheet1!$B$2:$B$110の部分が
>本来は{"現金";"現金";0;0;0;0;0;0;0;0;0;0;0;0…}と為るべきところが
>{"現金"}と、要素が1つしか引き渡されていませんでした
[数式の検証]では単独の結果しか返さないのではないかと思いますが、違うのでしょうか。
単純な配列数式で試してみてください。

もっとも、Sheet1!$B$2:$B$110 だけ選択して[F9]なら配列が返るはずです。
実際の関数の提示ができないなら、ご自分で各ステップごとに分割して、段階を追ってチェックしていくしかないと思いますが。

全体が見えない中、可能性としては
OFFSET(データシート!$A$1,INT((COLUMN()-1)/2),0,1,1)<>INDEX(仕訳入力フォーム!$B$2:$D$110,,3-2*ISODD(COLUMN()))
これを
INDEX(データシート!$A$1:$A$100,INT((COLUMN()-1)/2)+1)<>INDEX(仕訳入力フォーム!$B$2:$D$110,,3-2*ISODD(COLUMN()))
こんな感じで、OFFSETではなくINDEXを使えば良いような。
（..$A$100 は適当です）

お礼

早々におこし頂いていたのも関わらず
連絡が遅れ申し訳在りません
有り難う御座います。

確かに仰るように
配列定数の部分
仕訳入力フォーム!$B$2:$D$110
と、
可変部分
OFFSET(データシート!$A$1,INT((COLUMN()-1)/2)
を、各々先にＰＦ９で再計算させてやると
{"現金";"現金";0;0;0;0;0;0;0;0;0;0;0;0…}
と
{"現金"}
と、言った風になります。

そこで更に
全体を再計算させると
ちゃんとした思惑通りの結果が得られるのですが、

いちいちＰＦ９で再計算させてやらないと
誤った評価がされて
要素が正しく引き渡されなくなるのです。

で、通常の計算結果も
これに準じたものになってしまします。

どうしたものやら…

回答No.2

tom04

No.1です！
たびたびごめんなさい。

私の認識不足かもしれませんが・・・
配列数式にするのであれば、

=IF(OFFSET(Sheet3!$A$1,INT((ROW()-1)/2),0,1,1)<>Sheet1!$B$2:$B$110,ROW($1:$109))
としてみて、Shift＋Ctrl＋Enterキーで確定でもやはりエラーになるでしょうか？

根本的な解決法にはならないかもしれませんが、
この程度でごめんなさいね。m(__)m

お礼

再度の御来訪に感謝します、
こちらの苦境を察した御心遣いがこの身に染みる思いです
有り難う御座います。

ところで、
先の配列は
=if(…　…　…SMALL((OFFSET(データシート!$A$1,INT((COLUMN()-1)/2),0,1,1)<>INDEX(仕訳入力フォーム!$B$2:$D$110,,3-2*ISODD(COLUMN())))*1000+ROW($A$1:$A$109),ROW(B1))…　…　…)
と言ったような長文関数プログラミングのコアを為す部分で
この部位の正常出力ありきなものなので
配列数式として確定させてどうと為るものではないように思えるのです

後ろ足で砂を掛けるようで心苦しいのですが、
どうぞ御容赦下さい

回答No.1

tom04

こんばんは！
実際にどのような数式になっているのか判らないので
あくまで憶測ですが・・・

配列数式の場合
INDEX等の範囲指定の行数と他の数式の行数が一致しないとエラーになることが多いと思います。
通常1行目を見出し行としていることがほとんどだと思いますので、
INDEX等の範囲指定の領域は2行目からになると思います。
（列に関しても同様のことが言えます）

仮に
={INDEX(A2:A100,SMALL(IF($A$2:$A$100=○○,ROW($A$1:$A$99),ROW(A1)))}
のような数式ですと、
ROW($A$1:$A$99)　の部分を　ROW($A$1:$A$100)　にしてしまうと、
エラーになったり、ROW($A$2:$A$100)　にすると1行ずれてします。

他の原因の可能性もありますので、
ちゃんとした回答になっていないかもしれません。
この程度でごめんなさいね。m(__)m

お礼

早速の御来訪に感謝致します

実際の式ですが
=(OFFSET(Sheet3!$A$1,INT((COLUMN()-1)/2),0,1,1)<>Sheet1!$B$2:$B$110)
と言う単純なものです
エラーになるので数式の検証を施行させてみたのですが
それによるとSheet1!$B$2:$B$110の部分が
本来は{"現金";"現金";0;0;0;0;0;0;0;0;0;0;0;0…}と為るべきところが
{"現金"}と、要素が1つしか引き渡されていませんでした

PF9でOFFSETの部分を確定して定数化してやれば上手く行くのですが…
なにもしないとVALUE!の配列が帰ってきます
とほほ