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)のような記述でも問題無いです。

回答No.2

bunjii

提示の数式は私の提言かと思います。
>一つ一つの関数の意味はわかっても全体としては全然理解できませんでした。
数式全体の解説が必要のようですね。
INDEX関数の中でA1セルの文字列を1文字ずつに切り分けて文字コードが"9"より大きい文字に対して1を、それ以外を0に置き換えた配列値を対象にMATCH関数で1の位置を検出する方法で目的の文字数を算出しています。

>また数字の９に””を付ける意味も全くわかりません。
文字を対象に大小を比較していますのでMID関数で1文字単位に切り出された文字の比較対象として数値の9ではなく文字列の9（"9"）との大小（文字コードで）が対象になります。

前回の質問（ https://okwave.jp/qa/q9388298.html ）では数字と半角の空白以外の文字が最初に現れた文字までの文字数を返す数式を要求されています。

回答No.1

msMike

ひとつ確認したいことが・・・
貴方が書いた｛１１ ３D｝の両端の括弧を除いたものは
１１ ３D
について、数字は全て全角文字、アルファベットと「３」の左のスペースは半角文字。それでよろしいのですか？！

補足

すみません、わかりにくくて。。。
全て半角です。誤解する表記で申し訳ありません。