Excel 関数

質問

先日こちらで、「数字と文字が含まれるもの（左に数字、右に文字の場合）の、文字までの（スペースを含む）桁数を表す関数（例えば｛１１ ３D｝であれば４を返すような関数　）」を教わりました。以下の関数です。

=MATCH(1,INDEX(N((MID(A1,ROW(A:A),1))>"9"),0),0)

正しく動いてくれたのですが正直なところ、知識がまだ浅く、一つ一つの関数の意味はわかっても全体としては全然理解できませんでした。また数字の９に””を付ける意味も全くわかりません。。。
詳しい方、理解できるように、できるだけ詳しく教えていただけませんでしょうか。

回答No.3

craftsman_t

この関数は、配列という考え方が使われるため複雑です。

●まず、配列を使わずに関数の働きを追います。
A1="１１ ３ＤＥ"　として、配列のかわりに5を代入すると

MID(A1,5,1)　="Ｄ"

(MID(A1,5,1))>"9" =true
　　数字の大小でなく、文字コードの大小を比較させるため"9"と表記します
　　簡単に言うと
　　　数字と空白＝ 9以下の文字コード →false
　　　数字以外　＝ 9を超える文字コード →true
　　となります。

N((MID(A1,5,1))>"9")=1
　true→1、false→0と変換します。

●次に配列を使った場合です。
配列とは値の集合で、excelでは{9,8,7}のように表します。
※セルへ　={9,8,7}など入力しても、先頭の9しか表示されません。

関数へ配列を代入すると、それぞれの値へ関数計算が行われ、新しい配列が返されます。

仮に、{1,2,3,4,5,6}という配列を用いて考えます。

MID(A1,{1,2,3,4,5,6｝,1)
={１,１, ,３,Ｄ,Ｅ}
　※先頭の1しか表示されず

(MID(A1,{1,2,3,4,5,6｝,1))>"9"
={false,false,false,false,true,true}

N((MID(A1,{1,2,3,4,5,6｝,1))>"9")
={0,0,0,0,1,1}

●次にmatch関数の働きについてです

入力された配列を左から検索し、値が合致した要素の番号を出力します。
例：
match(6,{3,6,9,12,15},0)=2

今回の関数では、1を検出すれば良いので、

match(1, N((MID(A1,{1,2,3,4,5,6},1))>"9") ,0)=4　・・・(1)

となります。
この式でも、文字Dが4桁目にある事を出力できます。

●最後に、ROW関数、INDEX関数についてです。

{1,2,3,4,5,6}という書き方では、7桁以上が入力されると正しく動きません。

任意の桁数に対応するために、ROW関数を使って大きな集合を作ります。
ROW(A:A)≒{1,2,3....,1048576}

ROW関数を使う場合、配列をMATCH関数へ渡すためにはindex関数を使う事が必要です。
そのため、
MATCH(1,INDEX(N((MID(A1,ROW(A:A),1))>"9"),0),0)

という書き方になります。
もし最大桁数が分かっているなら、式(1)のような記述でも問題無いです。