match関数とindex関数について

質問

B列に空白を含む連続した数値の配列において、空欄と空欄の間の数値の個数を数える式として、以下の式を見たことがあります。

=IF(AND(B9<>"",B10=""),IF(ROW(B9)=COUNT($B$1:B9),ROW(B9),ROW(B9)-MATCH(1,INDEX(0/($B$1:B9=""),0))),"")

確かにこの式で求める答えが出るのですが、式の最後の「MATCH(1,INDEX(0/($B$1:B9=""),0)」の意味がよく分かりません。
・MATCH関数の範囲としているINDEXの内容はどういう意味なのか
・MATCH関数の検査値としている「１」はどういう意味なのか

ご教授願います。

回答No.4

HohoPapa · ベストアンサー

MATCH(1,INDEX(0/($B$1:B9=""),0))

この計算式は、$B$1:B9の範囲を調べ
値が空欄かつ、最後のセルの行番号を求めています。

どうして、それが求まるのか？．．．

INDEX(0/($B$1:B9=""),0)
この計算式では、第二引数が0なので、配列を返してきます。
（2018-03-24 23:09:34 回答No.3　で回答されています。）

どのような配列か．．．．

($B$1:B9="")　これは、、
B1セル以下　それぞれをこの式で評価し、
True/Falseを返してきます。　　　　　　※１
空欄ならTrue（1のこと）
空欄以外ならFalse（0のこと）です。

その後、
0/($B$1:B9="")の計算、つまり、※１が分母、分子が0の計算です。
計算結果は、セル値が空欄なら0、空欄以外なら#DIV/0! となります。
都合、配列は、0または、#DIV/0!の羅列になります。

更にその後、
MATCH(1,INDEX(0/($B$1:B9=""),0))
の計算式では、配列になかで
1以下かつ、最大値の埋まった値の配列上の位置を返してきます。
結局、0または、#DIV/0!しかないので、
0の埋まった最後の配列の行位置
（とりもなおさず、値が空欄かつ、最後のセルの行番号）が返ってきます。

なので、

=IF(AND(B9<>"",B10=""),IF(ROW(B9)=COUNT($B$1:B9),ROW(B9),ROW(B9)-MATCH(2,INDEX(1/($B$1:B9=""),0))),"")
や
=IF(AND(B9<>"",B10=""),IF(ROW(B9)=COUNT($B$1:B9),ROW(B9),ROW(B9)-MATCH(12,INDEX(10/($B$1:B9=""),0))),"")
でも同じ結果を得ることができます。

お礼

皆さま、ご回答ありがとうございます。
個人的に最も分かりやすかったものをベストアンサーにさせていただきました。

回答No.3

bunjii

>MATCH関数ですが、照合の型は0となっているのですが、検査値の1以下を検出するようなことになるものなのでしょうか。
質問に提示のMATCH関数の数式に誤りがあります。
「MATCH(1,INDEX(0/($B$1:B9=""),0)」　誤り：閉じ括弧が一つ足りません。
　　　　　↓
「MATCH(1,INDEX(0/($B$1:B9=""),0))」

INDEX関数の行番号を0または省略すると配列全体を外側の関数へ渡せます。但し、配列と行番号を区切るカンマは省略できません。
従って、あなたはMATCH関数の照合の型を見間違えています。（照合の型を省略している）

お礼

ご指摘、ありがとうございます。
index.match関数のことをよく知ることができました。

回答No.2

bunjii

>式の最後の「MATCH(1,INDEX(0/($B$1:B9=""),0)」の意味がよく分かりません。
>・MATCH関数の範囲としているINDEXの内容はどういう意味なのか
INDEX関数の範囲内で計算した結果を配列値でMATCH関数へ返しています。

>・MATCH関数の検査値としている「１」はどういう意味なのか
INDEX関数から返った配列値で1以下の値を最後に見つけた位置の検出に使っているようです。

お礼

ご回答、ありがとうございます。
MATCH関数ですが、照合の型は0となっているのですが、検査値の1以下を検出するようなことになるものなのでしょうか。

回答No.1

msMike

何やら米粒あるい微小のゴミみたいな図を添付されたみたいだけど、見えないので補足でキーボードから打ち込んでくれませんか？
貴方の添付図はもう勘弁して欲しい！。

お礼

添付図が小さくてすみません。
例えば、「1、2、25、32、 、56、36、 、23」と並んでいる数列において、空欄から空欄までの数値の入ったセルの数をカウントする数式に関する質問です。
例の数列ですと、32まででは「4」、次に36までで「2」という形を算出するものです。