INDEX関数の範囲について

エクセルで、指定したセルの前後の空白セルのカウントをする関数を使いたくて検索をしたら、以下のページがヒットし、ここから応用しようと思ったのですが、数式の内容が理解できません。
http://www.excel.studio-kazu.jp/kw/2011112116471 …


具体的にはこの関数
=IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")
の中の
(B1:H1<>0)*1
の部分なんですが、INDEX関数ってINDEX(範囲,行番号,列番号)ですよね？

ということはつまり
(B1:H1<>0)*1
に範囲指定の意味があるとは思うんですが、これだけの知識では説明できないことがあるので教えてください。

①なぜ1をかけるのか分からない。
実際にセルに入れて試してみると(B1は空白)
=(B1:H1<>0)*1→0
=(B1:H1<>0)→FALSE
となり結果が変わります。

②この数式を入れる列を範囲外に出すとエラーになる
=(B1:H1<>0)*1
をB列~H列までのどのセルに入れても同じ数値が帰ってくるのに対して、例えばI列に入れると「#VALUE！」が返される。
ところが全文
=IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")
入れるとI列やZ列に入れてもちゃんと同じ数字が返される。

③この数式の代わりに返される数を代入すると結果が変わる
例えばB1とC2が空白、D2に1が入力されているとき
=IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")→2
=(B1:H1<>0)*1→0
なので代入して以下のようにすると
=IFERROR(MATCH(1,INDEX(0,0),0)-1,"△")→△

代入できないことからどうやらINDEX関数以外にも
(B1:H1<>0)*1
の部分が干渉する仕組みらしいということはわかりましたが、その理屈や仕組みが全く分かりません。

いままでEXCELの関数はどんなに複雑で長くても順番に紐解いていけば誰でもわかると思っていただけに、全然解けなくてかなり衝撃的です(^_^;)
というか
(B1:H1<>0)*1
の部分が関数でもないので検索方法すらわからないのです。

どなたかぜひこの謎を解いて教えてください。

質問者からの補足コメント

• EXCELのバージョンは2010です。

  No.1の回答に寄せられた補足コメントです。 補足日時：2015/09/12 13:56

No.2 ベストアンサー 回答者： CoalTar 回答日時： 2015/09/12 15:57

ちゃんと解析してくれる質問者に、久々に出会えた気がする。

＞②この数式を入れる列を範囲外に出すとエラーになる
＞=(B1:H1<>0)*1
この場合、B列に入れれば B1セルを参照してくれるのでB1セルの値のみが返ります
同様に、C列に入れれば C1セルを、D列に入れれば D1セルを参照し、その値のみが返ります
しかし。B:H列以外では、参照できないのでエラーとなります。

＞=(B1:H1<>0)*1
この数式で何をしたいかっていうとB1からH1セルまでで、
0以外か、0(空白セル)かを判断し、0か1を並べた値を欲しいのです。

適当なセルにこの数式を書いて、[F2]→[F9] と順に押してみてください([Esc]で戻せます)
0か1で並んだ配列定数が返ります。(列を区切る時は ,(カンマ)、行を区切る時は;(セミコロン))

配列ってなんぞや？ ですが、限られた範囲(○行△列)と思ってくれればよいです。
で、B1:H1は、1行7列の限られた範囲になりますので、セル範囲とも呼べるし、配列とも呼べます

で、普通に数式を書くとそのまま、単一のセルの値が返ってしまいますが、
配列として返したい場合があります。

1行7列のセル範囲を選択して(たとえばA2:G2)
=(B1:H1<>0)*1
を数式バーに入力し、入力の確定時に[Ctrl]と[Shft]キーを押しながら、[Enter] で確定します。

これで、配列が返せます。数式バーをみると
{=(B1:H1<>0)*1}
のように表示されているなら成功です。[F2]→[F9]の結果が、セルに書き込まれました。

で、INDEXが何をしているかっていうと、配列を返してくれる関数だってことです。
＊＊＊＊＊＊＊＊＊＊＊＊＊ヘルプより＊＊＊＊＊＊＊＊＊＊＊＊＊
INDEX(配列,行番号,列番号) の形式では、配列内にある、指定したセルまたはセルの配列の値が返されます。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
=(B1:H1<>0)*1 のままでは、配列と認識してくれないので
先の説明のように[Ctrl]+[Shft] ＋[Enter] で確定するか、INDEX関数で配列として認識させるのです。

INDEX関数の行番号と列番号は省略可能なので
=INDEX((B1:H1<>0)*1,)
という具合です。0を入力しても同じです。
=INDEX((B1:H1<>0)*1,0,0)

ただ、0の意味があまりないので配列として認識させたいんだなって意味合いの暗黙のルールって感じで
INDEX((B1:H1<>0)*1,)
と使う方が多いです。(最初に使った人の影響もあると思う)

でエラー処理と-1を除いたメイン箇所
=MATCH(1,(B1:H1<>0)*1,0)
[Ctrl]+[Shft] ＋[Enter] で確定させ、問題の部分を配列と認識させることでも可能です。

もっと知りたければ「配列数式」でググってみてください。

この回答へのお礼

ありがとうございます！！

>B列に入れれば B1セルを参照してくれる
こんな単純なことにも気が付きませんでした(^_^;)
いろいろ試して見たつもりなんですがお恥ずかしい・・・。

>先の説明のように[Ctrl]+[Shft] ＋[Enter] で確定するか、INDEX関数で配列として認識させるのです。
これでよくわかりました。
(B1:H1<>0)*1
が単一のセルを参照していることに気が付かなかったのでここまで考えることができませんでした。

>[Ctrl]+[Shft] ＋[Enter]
これってヒストグラムつくるときに使うやつですね。
そうか・・・今までそういうものだとして使ってきたのですが、あれはfrequencyが配列関数だからなんですね。

とても参考になりました！
「配列数式」でググってもっと自由にEXCELをいじれるように頑張ります！

お礼日時：2015/09/12 18:21

No.4 回答者： mike_g 回答日時： 2015/09/12 18:01

(B1:H1<>0)*1

の部分だけに着目します。

式 =IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")
が入力してあるセルを選択すると、[数式バー]には添付図上段が表示されます。

その[数式バー]内で (B1:H1<>0)*1 の部分だけをマウスで撫で撫でして選択した後でＦ９キーを１回だけツンしてみてください。
添付図下段の表示に変貌する筈です。

つまり、
(B1:H1<>0)*1 の部分は、{0,1,0,0,0,0,0} という立派な範囲なのです。

ついでながら、「*1」している理由は FALSE、TRUE をそれぞれ 0、1 に置き換えるためではなかろうかと推察できる次第です。
MATCH で数値 1 を検索するために？

この回答へのお礼

>その[数式バー]内で (B1:H1<>0)*1 の部分だけをマウスで撫で撫でして選択した後でＦ９キーを１回だけツンしてみてください。
添付図下段の表示に変貌する筈です。

これすごい！
知らなかった。
知ったからと言って私が今すぐ何に使えるわけではないですが、こういうテクニックはとても楽しいです。
普段できるだけ自分で解決してしまうようにしていますが、人に聞かないと分からないこともありますね。

ありがとうございます！

お礼日時：2015/09/12 18:30

No.3 回答者： esupuresso 回答日時： 2015/09/12 17:06

「*1」の部分はエクセル以前の問題で、コンピュータは「0」と「1」の２進数から成り立っているということを念頭に考える必要があります。

「0」はFalse＝OFFの状態
「1」はTrue＝ONの状態
この２進数をうまく利用したのが　(B1:H1<>0)*1　この部分の式です。
(B1:H1<>0)　この式だとTrueかFalseのどちらかが結果として返ってきますが「*1」を付け加えることによって数値化されTrueは「1」として返り、未入力のセルに対しては「0」が返ってきますので
MATCH(1,INDEX((B1:H1<>0)*1,0),0　の部分の検索値「１」に対して何列目に入力、またはスペースを入れたセルがあるのかを返してきますから、(B1:H1<>0)*1　の式が成り立ちます。

また(B1:H1<>0)*1　この式の「*1」を省いて(B1:H1<>0)とした場合はMATCH関数の検索セルには「True」を入力してあげると同じ結果を返してきます。
=IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")　←この式に対して
=IFERROR(MATCH(TRUE,INDEX((B1:H1<>0),0),0)-1,"△")　←この様になります。

但し、=IFERROR(MATCH(1,INDEX((B1:H1<>0)*1,0),0)-1,"△")　の式ですと「-1」と１列分引か去っていますのでセルB1~H1の範囲に対してB1は０行目となります。

※実際、人が作った式を解析する場合は、式内の範囲設定されている部分に気を付け（循環参照にならないよう）適当なセルにコピー＆貼り付けをし、範囲内に数値や文字などを入力して返ってくる結果を見ながら検証していくとより理解度も上がります。

この回答へのお礼

>「*1」の部分はエクセル以前の問題で、コンピュータは「0」と「1」の２進数から成り立っているということを念頭に考える必要があります。
「0」はFalse＝OFFの状態
「1」はTrue＝ONの状態

なるほど。
だから1をかけると数字になるんですね。
納得です。

>※実際、人が作った式を解析する場合は、式内の範囲設定されている部分に気を付け（循環参照にならないよう）適当なセルにコピー＆貼り付けをし、範囲内に数値や文字などを入力して返ってくる結果を見ながら検証していくとより理解度も上がります。

普段そうやって数式を解析して応用して使っていたんですが、FALSE*1が理解不能でつまずいていました。
疑問を解いていただいてありがとうございます！

お礼日時：2015/09/12 18:24

No.1 回答者： 銀鱗 回答日時： 2015/09/12 13:23

をれを行っているExcelのバージョンはいくつだろうか。

自分の知る限りでは
(B1:H1<>0)*1
ような指定を行うと"#Value"エラーになるはずだ。
唯一値を返すことができるのは配列関数を使った場合。
その場合は
{=(B1:H1<>0)*1}
となり別物だ。

とりあえず
=(B1:H1<>0)*1→0
=(B1:H1<>0)→FALSE
に関してエラーはさておき、
True*1=1
False*1=0
と答えておく。
TrueとFalseの意味は理解しているだろうか。
Trueは条件成立
Falseは条件不成立
…だ。
しかし、先に書いたとおりどちらも"＃Valueエラー"になるはずなのだが。

この回答へのお礼

条件成立と不成立は知っていましたが、それに1をかけると1or0になるのは初めて知りました。
＃Valueエラーには書いた通り、指定した列の範囲内にあるうちはなりません。
指定範囲から列が外れると＃Valueエラーになります。

つまり本来なら{}が必要なところを、何らかの要因でエラーにならずに返されてるということですか？

①の疑問は解けました！
ありがとうございます

お礼日時：2015/09/12 13:57