エクセルの以下の算式の説明をして欲しいです。Mshr1962さんの答えです。

質問

A1にもとデータが入っているとして
B1=MID(A1,3,SUMPRODUCT(NOT(ISERROR(VALUE(MID(A1,3,{1,2,3,4}))))*1))+0

回答No.2

imogasi · ベストアンサー

ご本人のご説明と併せて読んで見てください。
（１）前問の例で、ＯＳ1235着物23455と言う文字列があるとします。
ＯＳは２桁でいつも固定と言う例です。
（２）=MID(A1,3,LEN(A1)-2)で「1235着物23455」となります。この部分に注目します。
（３）この1235の部分が１桁(ＯＳ1)、２桁(ＯＳ23)、３桁(ＯＳ321)、４桁(ＯＳ4321)、のいずれの場合も有りえるので、その桁数をどう捉えるかが問題です。
その桁数は、次に文字である「着物」が現れたところで桁数が決まりますよね。
（４）幸いISNUMBERという「数値（数字文字ではない）を判別する関数」が有りそれを使えそうです。
「1235着物23455」がＣ１にあるとします。
=ISNUMBER(MID($C$1,1,1)*1)*1は1を判別していて1
=ISNUMBER(MID($C$1,2,1)*1)*1は2で1
=ISNUMBER(MID($C$1,3,1)*1)*1は3で1
=ISNUMBER(MID($C$1,4,1)*1)*1は5で1
=ISNUMBER(MID($C$1,5,1)*1)*1は着で0
「*1」はＩＳＮＵＭＢＥＲ関数が文字列数字１等だとＴＲＵＥにならないので＊１して、数値化している。
それで数字文字の時ＴＲＵＥが返るが、それを１にするために＊１している。
そして上例は４桁という例ですが、４は１＋１＋１＋１＋０と加えた数が桁数を表して入ることを見ぬく。
その和を出すために
ＳＵＭＰＲＯＤＵＣＴ関数を使う。
（５）もう一つ難しいのはMID($C$1,3,1)の3の部分で、第１式では１、第２式では２のようになって、変化してくれなければ（しなければ）なりません。
これに配列数式｛１，２，３，４｝を使います。
この辺は経験しないと、思いつかない難しい点と思います。ＯＫＷＥＢ回答でも初出？
（６）結局
=MID(C1,1,SUMPRODUCT(ISNUMBER(MID($C$1,{1,2,3,4},1)*1)*1))
と入れて、ＳＨＩＦＴ＋ＣＴＲＬキーを押しつつ、ＥＮＴＥＲキーを押すと、式の前後に｛と｝がついて、上例では
SUMPRODUCT(ISNUMBER(MID($C$1,{1,2,3,4},1)*1)*1)
が４を返すので、=MID(C1,1,4)になって、「1235」を返す。あと「ＯＳ」を結合すると商品コードが出ます。
残り後半は
=MID(A1,上記長さ＋１、LEN(A1)-上記長さ＋１）
=MID(A1,LEN(C10)+1,LEN(A1)-LEN(C10)+1)
のようになります。着物23455です。

お礼

細かい補足を丁寧にありがとうございました。
ただ今現在では全てを理解することが出来ないのであとでゆっくり印刷したものと私のファイルを比較して確認していってみようと思います。
ありがとうございました。

回答No.3

maruru01

こんにちは。maruru01です。

余計混乱するかも知れませんが、せっかくなのでB1の別解を。

=--MID(A1,3,COUNT(-MID(A1,3,{1,2,3,4})))

基本的な考え方は、No.1の方と同じで、VALUE関数の変わりに「-」（-1を掛けるということ）を使用しています。
さらに、SUMPRODUCT＋NOT＋ISERRORを、COUNT関数1つにしています。

お礼

参考になったというべきかまだ1番の方の答えも理解していないので、・・・
ありがとうございました。
関数恐るべしという感じです。

回答No.1

mshr1962

mshr1962です。ちょっと説明が難しいのですが．．．
MID(A1,3,{1,2,3,4})はA1の3桁目から1桁,2桁,3桁,4桁の文字を返します。

VALUE関数で数値化した場合、文字があると#VALUEのエラーになります。

NOT関数とISERROR関数で数値ならTRUE,文字（エラー）ならFALSEに置換しています。
SUMPRODUCTでTRUEを1,FALSEを0として加算しています。

上記でCR1テープなら1,CR9999テープなら4を返します。
要は何桁目までが数字かの判定を行っています。

最後にMID関数で3桁目から数字の桁分を抽出します。
ただこれは文字なので数字にするため+0しています。

各関数の詳細はヘルプで確認してください。

お礼

ご本人から説明いただいて感謝です。
レベルの違いというものを非常に実感する内容でした。
ありがとうございました。