[EXCEL] INDEX関数の不具合？

質問

EXCEL2003を使用しています。

A1:A3のセルに次の数式を配列数式として入力すると、期待した結果と異なる出力が得られました。

=INDEX({"a";"b";""},{3;3;3})

期待した結果は、A1:A3すべてのセルが""(空文字?)となるはずでしたが、実際にはA1のみ""となり、A2:A3には0(ゼロ)が出力されました。

式を変えて試した所、どうやら、配列内の同じ""を複数回INDEXで抜き出すと、2個目以降が0になってしまうようです。

同じ式をEXCEL2007で試すと期待通りの結果になります。これはEXCEL2003の不具合なのでしょうか。
内部の計算がどうなっているのかなど、詳しい方の御意見を頂けるとありがたいです。

回答No.2

Wendy02 · ベストアンサー

こんばんは。

>質問の意図は、この数式の機能を実現したいのではなく、配列数式のガイドラインのようなものを知りたいということです。

最初に、私も、昔、同じことを、日経BPのExcelの達人と言われる芳坂氏にお聞きしたことがあります。彼曰く、「やってみなければわからない」とのことでした。彼が本当にそう思って回答したかは分かりませんが、私は、そんなものかと思いました。

この掲示板でも、配列数式の達人さんがいます。私は、すでに、関数をいじるようなことはやめて、プログラミング側に移ってしまいましたが、ここの達人さんのワザを見ていると、数年前にはなかったような、新しいワザが出てきています。そうしてみると、その個人というよりも、掲示板というか、多くの人たちの集まりの中から、新しいワザが生み出されているのではないか、と思います。それは、国内外を問わず、多くのユーザーの偶然の発見から、発見されて、発表したものが構築して成り立っているように思います。自分で考えるだけでなく、多くの掲示板を眺めて拾っていくしかないのかもしれません。

それで、

#MSのNewsGroup（英語）辺りに聞いてみれば、違った反応は出てくるかもしれません。

と書いたわけです。ここは、一日に、ものすごい量が書き込まれます。平凡な解答もあれば、誤解答もあれば、優れた数式を書く人もいます。それは、ここの掲示板でも同じことです。

>例えばSMALL関数のヘルプを見ると、同様に第二引数に配列を指定できることを明示してありませんが、公式HPで以下の使い方を紹介していました。

同じ系列のRANK関数は、配列が使えません。配列の使える関数とそうでない関数の差というのは、一種の使用契約があるのかもしれません。概してMicrosoft独自で開発したオリジナル関数というのは、それほど多くはないようです。

お書きになった数式は、私は、もし必要になれば、ユーザー定義関数を作ってしまいますので、必ずしも、凝った数式は作らないのですが、補足に書いた数式は私には分からないですね。

ところで、あまり配列数式は、深く追いかけないほうがよいと思います。配列数式は、一つの数式にまとめられますが、読みにくいのと、メモリの使用量が多いので負担が多いのではないかと思います。

お礼

こんばんは。

ご回答ありがとうございます。
「やってみなければわからない」ですか。詳しい方にそう言われると、やや残念でもありますが、少し安心しました。

>その個人というよりも、掲示板というか、多くの人たちの集まりの中から、新しいワザが生み出されているのではないか、と思います。

私がこの質問をしたきっかけも、この掲示板の中で、自分では思いつかないようなワザを見て、自分でもその様な式を(試行錯誤の結果ではなく、関数の正しい理解の結果として)作れるようになりたいと思い、組み込み関数の仕様の理解を深めようと思ったからでした。
深い理解だけではなく、経験の積み重ねも大事なのですね。

配列数式は、最近知ったばかりで頻繁に使用していますが、今後は効率も考慮しつつほどほどに付き合っていこうと思います。

この掲示板のようなコミュニティを利用するのは、質問するのも閲覧するのも今回が初めてになりますが、他の掲示板も含めて、有効に活用していこうと思います。アドバイス大変参考になりました。ありがとうございます。

回答No.1

Wendy02

こんばんは。

>=INDEX({"a";"b";""},{3;3;3})

Excel 2007 と Excel 2003 の以上の式の違いは確認しましたが、何を求めるものでしょうか？

数式自体の意味が分かりません。その数式を、どういう場面に使うのでしょうか。
ワークシートのセルを参照して使えばよいのだと思いますが。

Excel 2007 では、数式のプログラミング・コードは変わったようですが、もともと、領域の配列の確定は、INDEX 関数には用いないはずです。

一応、ヘルプで確認してみると、

=INDEX(配列,行番号,列番号)

第１次引数は、{"a";"b";""} であっていますが、
第２次引数に、配列を入れるというようにはなっていないと思います。
第３次引数を省略すれば、その列全体になります。

第2,3 は、基本的に少なくともひとつの引数は、1つの数値ということになっています。

>内部の計算がどうなっているのかなど詳しい方の御意見を頂けるとありがたいです。

INDEX関数の解析なのでしょうか？
もし、そういう趣旨ですと、なかなか難しいです。MSが公開しているわけではありませんし、おそらくは、INDEX関数のコンセプト自体は、Lotus123側（開発者は別）から受け継いだもので、今回、Excel 2007 で、従来の配列の取り方を変えたというだけで、その詳細が明らかになっているわけではありません。仮に、MSに聞いても、おそらくは、まともな返事は返ってこないだろうし、仮に答えても仕様が違います、だけで終わってしまうのではないでしょうか。

MSのNewsGroup（英語）辺りに聞いてみれば、違った反応は出てくるかもしれません。

そういうレベルで問題にするなら、自分で試行錯誤しながら、VBAやVSやC++で作ってみればよいのだと思います。作り方は公開されているはずです。そういう現象に対して、今の時点では想像の範囲にはありません。

INDEX関数は、第1次引数を、領域のマトリックスを、そのまま、配列するか、セルの領域を格納しています。そこから、2次元の縦・横（１次・2次）を数値アドレスで取り出すように出来ているだけで、ご質問者さんが言うような、このマトリックスのアドレスに配列を入れるなどという使い方は、どこにもないと思います。少なくとも、私は知りませんし、仮にあったとしても、どうやって実際に使うのか、なおさら知りません。

領域の配列の確定をしなければ、それは、第2引数も第3引数も、配列のインデックスの最初しか取りませんから、単に数値1つと同じです。それでは配列を入れる意味がありません。

今回は、0　が出るということは、たぶん、INDEX 関数に、3行の配列の確定を行ったのだと思います。

>配列内の同じ""を複数回INDEXで抜き出すと、2個目以降が0になってしまうようです。
>これはEXCEL2003の不具合なのでしょうか。

規定以外の使用法（少なくとも私は知りません）に対して、期待通りの値が返ってこなかったことを、不具合というのかは分かりませんが、Excel 2003 には、第2・第3引数の型に、配列が入るようにはなっていないのだと思います。通常は、エラーを出すように作るのですが、次バージョンでの開発の将来性を残して、エラーを出さなかったのかもしれません。

FREQUENCY 関数のように、領域の配列の確定で関数内のデータを一覧にして、そこからまとめ合わせて抜き出すような使い方はしないはずです。

Excel 2007 の現象は確認しましたが、そうした使い方のINDEX 関数を使いようがないように思います。

補足

こんばんは

詳細な回答ありがとうございます。
なるほど、私の使い方が想定外(仕様外?)だったわけですね。

>第２次引数に、配列を入れるというようにはなっていないと思います。

例えばSMALL関数のヘルプを見ると、同様に第二引数に配列を指定できることを明示してありませんが、公式HPで以下の使い方を紹介していました。

{=SMALL(A1:A10,{1;2;3})}

これに加え、質問の数式の第一引数を{1;2;3}のような数値の配列にした場合には私の期待通りの結果が得られるため、引数に配列を使えない方が例外だと思い込んでおりました。

>内部の計算がどうなっているのかなど詳しい方の御意見を頂けるとありがたいです。

上記の通り、ヘルプを見ただけでは引数に配列が使える場合と使えない場合が判断できなかったため、それぞれの場合について何処が異なっているのか(使えるかどうかの判断基準)を知りたいと思い、このような質問を書きました。

>数式自体の意味が分かりません。その数式を、どういう場面に使うのでしょうか。

問題点を明確にする意図で作った数式なので、これ自体に意味はありません。実際には以下のような式で現象を確認しました。

{=INDEX(IF(ROW(INDIRECT("1:"&ROWS(DATA)+1))<=ROWS(DATA),DATA,""),SMALL(IF(IF(LEN(DATA),MATCH(DATA,DATA,))=ROW(INDIRECT("1:"&ROWS(DATA))),ROW(INDIRECT("1:"&ROWS(DATA))),ROWS(DATA)+1),ROW(INDIRECT("1:"&ROWS(DATA)))))}

これは、DATAと名前をつけたn行1列の範囲のデータから一意の値を抜き出す数式です。もちろんこのような数式をつくる必要は無いと思いますが、INDEXの仕様が私の想定通りであればこういった使い方もできるかな。といった程度のものです。(EXCEL2007では機能します)

長くなってしまいましたが、質問の意図は、この数式の機能を実現したいのではなく、配列数式のガイドラインのようなものを知りたいということです。(INDEX関数の引数に配列が使用できないことを経験以外から判断できるのか、など)