全角・半角混在の文字列から半角文字のみ取り出す

エクセル勉強中です。問題集で理解できないところがあります。
数式がどういう意味をもつのか教えて頂けるとありがたいです。
よろしくお願いします。

画像添付の問題になります。
A列に製品名が入っています。（製品番号：半角文字）（製品名：全角文字）
B列に半角文字の製品番号だけを取り出しなさいというものです。
半角文字の開始位置がバラバラになっているところが問題のポイントになっています。

回答ですが
B2：
=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0),),LEN(A2)*2-LENB(A2))
こちらで半角文字のみ取り出せるようです。回答には数式のみで何故この関数を使うのか？
使うことでどういった結果を導くなどの解説が一切ありません。（ちなみに出版会社の便利技的な問題集です）

MID関数で製品名A2から開始位置を指定して、全角半角をLEN関数LENB関数で半角文字数を
算出して文字列を抽出するという事は理解できます。
ただ、この開始位置の指定の所が理解できません。
数式を分割してみましたが
=MID(A2,COLUMN(2:2),1)の所はどの行も製品名の１文字目ですよね・・・
その値にLENB関数で文字数？
数式の検証で見てみると配列のような結果が次々と現れて・・・
MATCH関数もありますしINDEX関数が何か関係しているような気はしているのですが、
INDEX関数と言えば配列に行番号・列番号と例えば表の該当するセルの位置抽出の
知識しかありません。一つのセルでINDEX関数？

すいません。独学で勉強していてこの程度の知識ですが、この数式の考え方教えてくださる方よろしくお願いします。

No.6 ベストアンサー 回答者： MackyNo1 回答日時： 2014/10/23 13:49

ご質問のような半角文字を抽出するなら、提示された数式は一部非効率でわかりにくいところがありますが、個人的には最も簡単な配列数式になっていると思いますので参考までに検証方法を補足します。

まずB2セルに以下の式を入力してください(半角文字が１１文字目までに出現する場合）。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(A:K),1)),0),),LEN(A2)*2-LENB(A2))

COLUMN関数の部分はROW関数で以下のようにするほうが数式がわかりよいかもしれません。

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,ROW($1:$11),1)),0),),LEN(A2)*2-LENB(A2))

この画面上部の数式バーの数式のCOLUMN(A:K)（またはROW($1:$11)）の部分をドラッグして選択しF9キーを押すと,「{1,2,3,4,5,6,7,8,9,10,11}」と表示されます（Escで解除）。

次に、MID(A2,COLUMN(A:K),1)の部分を選択し、F9キーを押すと、「{"お","徳","用","M","G","0","5","K","R","カ","ラ"}」とA2セルの先頭から１１文字目までの文字が取得できていることがわかります。

次に、LENB(MID(A2,ROW($1:$11),1))の部分を選択し（INDEXを含む部分はこの配列を範囲に変換しているだけですので、この部分を省略するならCtrl+Shift+Enterで確定する必要があります）、F9キーを押すと、「{2,2,2,1,1,1,1,1,1,2,2}」のように、それぞれの文字の半角、全角が1と2で表示されます。

次にMATCH(1,INDEX(LENB(MID(A2,ROW($1:$11),1)),0),)の部分はMATCH関数(第三引数が０または省略）で１（＝半角文字）が最初に出現する場所を調べています(上記の例なら4番目）。

この回答へのお礼

MackyNo1様

再び、ご回答ありがとうございます。
皆様からご指導いただいた内容を1つずつ確認していたら
お礼がたいへん遅くなりお詫び申し上げます。

まさにご提示いただいたような検証内容を順に作成し理解しようともがいていました。
素晴らしいです。このような内容のものがすらすらと作成できればいいのですが。

COLUMNの所は確かにROW関数を利用する方が文字数として理解しやすいですね。
LENBの所の*1についても回答者3のbunjii様のご指導で理解できました。

＞一部非効率でわかりにくいところがありますが・・・
皆様からのご指導で回答者1・4 Chiquilin様も仰られる非効率が理解できました。

あくまで学習用の参考書なので編集者の方の意図はわかりませんが、ある意味
非効率の理由など皆様から教えて頂き単なる回答以上の学習ができたように思います。

この本の内容の良し悪しを判別するほどの知識がまだ私にはありません。
この先にも内容で非効率な物も出てくるかわかりません。
本を捨てた方が良いというご意見も頂戴しましたが、とりあえず残り半分程度
学習し、非効率を判別できるような知識を身につけて行きたいと思います。


すいません。重ね重ねご丁寧にありがとうございました。

お礼日時：2014/10/27 18:10

No.5 回答者： Chiquilin 回答日時： 2014/10/23 10:24

何度も失礼。

もし「お徳用MG05KRカラーマグネット」から「MG05KR」
を抜き出したいなら

=REPLACE(LEFT(A2,LOOKUP(100,ROW(A$1:A$99)/(LENB(MID(A2,ROW(A$1:A$99),1))=1))),1,MATCH(1,INDEX(LENB(MID(A2,ROW(A$1:A$99),1)),0),0)-1,"")

これでいいんじゃないかと思います。

この回答へのお礼

Chiquilin　様

再び、ご回答ありがとうございます。
お礼が遅くなりお詫び申し上げます。

ご提示いただきました

=REPLACE(LEFT(A2,LOOKUP(100,ROW(A$1:A$99)/(LENB(MID(A2,ROW(A$1:A$99),1))=1))),1,MATCH(1,INDEX(LENB(MID(A2,ROW(A$1:A$99),1)),0),0)-1,"")
確認できました。ただ、ご想像がつくと思いますが、まだ理解はできていません。

LOOKUP(100,ROW(A$1:A$99)/(LENB(MID(A2,ROW(A$1:A$99),1))=1))),

これもLOOKUP関数　配列形式の特性を利用するテクニックなのですかね？
ご回答を下さっている皆さんにとってはテクニックではなくごく自然な事で当たり前の事なのでしょうね。


しばらく時間をかけて調べ学習したいと思います。
すいません。ご丁寧に度々ありがとうございました。

お礼日時：2014/10/27 17:23

No.4 回答者： Chiquilin 回答日時： 2014/10/23 10:19

> 例えば製品名に“MG05KRカラーマグネットお徳用”というように

> “お徳用”という文字が各々に入っていた場合は目的が果たせません。
何の問題もないはずですが……

=MID(A2,IF(LENB(LEFT(A2))=1,1,LENB(A2)-LEN(A2)+1),LEN(A2)*2-LENB(A2))

でも同じです。

「お徳用MG05KRカラーマグネット」なら別ですが その場合は元の式
でもダメなはずですね？

> ようやく半分ぐらいまで進んだのですがこの本ダメなのかな？？
その式を見る限りでも 突っ込みどころがいくつかあります。

まず何故「COLUMN(2:2)」なんて馬鹿な指定の仕方をしたのか合理的
な理由がありません。 Excel2003以前なら「COLUMN(2:2)」は「1～256」
くらいで ちょうど良かったかもしれませんが。「ROW(A$1:A$99」な
どとした方が よほど分かりやすいはずです。

次に「*1」が入っていますが これは飾り以上の何の意味もありません。
この部分だけを見ても この数式を書いた人が意味をほぼ理解せずに使っ
ていることが読みとれます。

INDEXを入れているのも Enter確定にしたいだけの理由です。きっぱり
無駄です。

この数式を見る限り １０年近く前の質問掲示板で流行った方法を そのま
まもってきたという印象ですね。多分理解もせずに。

この回答へのお礼

Chiquilin　様

再び、ご回答ありがとうございます。
お礼が遅くなりお詫び申し上げます。

こちらの説明不足で申し訳ございません。お察しの通り
“MG05KRカラーマグネットお徳用”の各々というのは文字の前後にという意味です。

“MG05KRカラーマグネットお徳用”でも”お徳用MG05KRカラーマグネット”でも

=MID(A2,MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0),),LEN(A2)*2-LENB(A2))

なら両方半角文字を抽出してくれます。
前後どちらに来ても配列内の半角文字位置を探すからだとこちらで理解しています。

「COLUMN(2:2)」につきましてはご指摘の通りムダですね。当初数式の検証で確認した際にも今思えば、
この範囲の数字の羅列が次から次にあり余計に何のこっちゃ？となりました。ほかの方も仰って頂いている
通り適度な文字数でという事ですね。
記述頂いている通りROW(A$1:A$99)　単に配列カウントが欲しいだけならCOLUMNをあえて使う必要もないのですね。

すいません。ご丁寧にありがとうございました。

お礼日時：2014/10/27 16:43

No.3 回答者： bunjii 回答日時： 2014/10/22 20:11

>INDEX関数と言えば配列に行番号・列番号と例えば表の該当するセルの位置抽出の

知識しかありません。一つのセルでINDEX関数？
質問の数式「INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0)」はMATCH関数へ配列値を渡すための1つの方法です。
INDEX関数で1列の配列値「LENB(MID(A2,COLUMN(2:2),1))*1」から1以上の行番号を指定すると1つの値が返りますが行番号に0を指定すると配列値の全てが返ります。
この性質を利用してMATCH関数に渡せば1つのセル内の文字列を配列値としてMATCH関数に渡せます。
LENB関数は与えられた文字のバイト数を返しますのでA2セルの文字列を一文字ずつ切り離して配列値に変換できます。
LENB(MID(A2,COLUMN(2:2),1))*1の*1はLENB関数の戻り値は数値なので不要です。
数式を組み立てた人はLENB関数の戻り値が文字列と勘違いして1を乗じて数値化する積りだったと思われます。
下記の2つの数式は等価ですが、2番目の数式はCtrl+Shift+Enterで確定しないと正しい結果が得られません。
INDEX関数を介してMATCH関数に引き渡すときはEnterキーで確定しても正しい処理になります。
=MATCH(1,INDEX(LENB(MID(A2,COLUMN(2:2),1)),0),)
=MATCH(1,LENB(MID(A2,COLUMN(2:2),1)),0)

この回答へのお礼

bunjii 様

早々にご回答を寄せて頂きありがとうございます。
申し訳ございません。皆さんの様に充分な知識があり柔軟に考えられれば
すぐに理解できるのでしょうが、なんせ頭を整理するのに時間がかかってしまいお礼が遅くなりました。

ご指導いただきましたINDEX関数について行番号・列番号の“0”が重要ポイントなのですね。


＞1以上の行番号を指定すると1つの値が返りますが行番号に0を指定すると配列値の全てが返ります。

ここの部分ですが、仮に1つのセル内（例えばA7セル）に
=INDEX(LENB(MID(A2,COLUMN(A:K),1)),0)
と入力をして(column(2:2)はムダと教えて頂きましたので（A:K）の１１文字にしています)
決定するとA7セル表示には“1”が返ってきますが表示上の問題で
配列全ての{1,1,1,1,1,1,2,2,2,2,2}で先頭の1だけが表示されている。しかし同じように
=INDEX(LENB(MID(A2,COLUMN(A:K),1)),1)
と入力するとA7セルには “1”が返ってきますが、こちらは表示上の配列の先頭ではなく
１文字目を指定した１文字で“1”
=INDEX(LENB(MID(A2,COLUMN(A:K),1)),7)
と入力すると７文字目の１文字“2”のみ返ってくる。だから今回の様にMATCHにつなげる為、
全体の配列が必要な場合は“行番号は”0“入力が必須という感じで考え方よろしいのですね？

こちらもご丁寧にご解説ありがとございました。このお礼の文面でもどこで引っかかっていたか、
こちらのレベルが容易に推測できるかと思います。
これまでINDEX関数についても配列についても何問も学習してきたつもりでしたが、
分かったつもりの浅知恵なのですね。昨日からINDEXの“0”ネットなどで色々物色してみましたが
なかなか見つけられずで、お時間かかってしまいました。
このような使い方あまり一般的ではないのでしょうか？
だいぶ賢くなったような気になっています。継続して身につけて行きたいと思います。
本当に、ありがとうございました。

お礼日時：2014/10/24 20:27

No.2 回答者： MackyNo1 回答日時： 2014/10/22 17:25

数式を説明すると、まず「=MID(A2,COLUMN(2:2),1)」の部分ですが、COLUMN(2:2)は２行目の列番号、すなわち「1,2,3,4・・・・、最大列番号」のような連続する数値の配列になります。

しかし文字数がそんなに多くなることはないので、実際は「MID(A2,COLUMN(A:K),1)」のような数式にしたほうが検証も容易になるだけでなく、計算負荷が少ないので実戦的にも適切だと思います。

この数式の意味は、A2セルの１文字目から、１１文字目までの１文字ずつを抽出した配列を取得している（この部分を選択してF9で計算内容が確認できます）ことになります。

次に「LENB(MID(A2,COLUMN(A:K),1))*1」の数式は上記の配列の全角半角をLENB関数で判定しています（1になる場所が半角文字）。

INDEX(LENB(MID(A2,COLUMN(2:2),1))*1,0)

上記のINDEX関数は上記の配列をCtrl+Shift+Enterで確定しなくても通常の範囲データとして扱えるようにしています(第一引数に配列、第二引数を省略または0にします）。

最後にMATCH関数で最初に1が出てくる（＝半角）文字の開始位置を求めていることになります。

この回答へのお礼

MackyNo1 様

早々にご回答を寄せて頂きありがとうございます。
理解を深めるのにお時間を要し遅くなりすいません。

まず、COLUMN(2:2)について２行目全体という事はカラーリファレンスで想像がついたのですが、
単体の数式でセルに入れるとCOLUMN(2:2)でも（3：3）でも結果が１になるのでどういう事？？と
パニックでした。No1回答者様も仰って頂いているとおり私、配列についてド素人以下という事ですね。
COLUMN関数はCOLUMN(A1)等例えばVLOOKUP関数の時に列番号として単体数値として利用するもの
ぐらいの知識しかなかったです。
範囲にする事で配列として利用できるのですね。
A2文字列の１文字目２文字目とMID関数で１文字ずつ抽出と。
"文字数がそんなに多くなることはないので"
というのは今エクセル2013を使用していますので、16384文字分１文字ずつ抽出させている
という式になると、それによって処理時間も変わるし単にムダという事ですね。

INDEX関数は範囲にしたいだけの為なのですか。
確かに配列数式で
｛=MID(A2,MATCH(1,LENB(MID(A2,COLUMN(A:K),1))*1,0),LEN(A2)*2-LENB(A2))｝
と入力すれば同じ結果が得られました。

お忙しい中ご丁寧に順序立てて、この猿にでも（猿以下かも知れませんが）理解できる
ご解説ありがとうございました。まだ、このようなレベルなものなので応用が利くほどでは
ございませんが、繰り返し繰り返しで少しずつより深く知識を身につけて行きたいと思います。

本当に、ありがとうございました。

お礼日時：2014/10/23 16:38

No.1 回答者： Chiquilin 回答日時： 2014/10/22 17:21

読んでる限り 現時点であなたに説明してもまず理解できないと思います。

おそらく配列数式すら分かりませんよね？　配列計算と INDEXの配列の
使い方を理解しないと 説明しても分かったつもりになるのが関の山です。

「COLUMN(2:2)」という使い方から見て １０年近く前にネットで出回っ
ていた数式をそのままパクったのだろうと思います。

というか今回の場合なら そんな馬鹿みたいな（失礼）式いらんでしょう。
半角の文字列はどちらか一方に固まっていることが前提なんだから

=IF(LENB(LEFT(A2))=1,LEFT(A2,LEN(A2)*2-LENB(A2)),RIGHT(A2,LEN(A2)*2-LENB(A2)))

これでいいはずです。１０００件くらいで試してみれば分かりますが 処理
の重さが全く違います。

> ちなみに出版会社の便利技的な問題集です
数式見る限り碌な会社ではないです。その本 捨てた方がいいと思います。

この回答へのお礼

Chiquilin様

早々にご回答を寄せて頂きありがとうございます。
学習中でこれまでも色々な問題を繰り返し解いていきながら
Excelの理解を深めている途中です。ご指摘の通り“分かったつもりに”っていうのが
今の私の的確なるご指摘ですね。。。
バカには無理ですかね～まだハードルが高すぎるのか？
そんな私にご回答を寄せて頂きありがとうございます。

ご指導いただきましたIF関数の数式
=IF(LENB(LEFT(A2))=1,LEFT(A2,LEN(A2)*2-LENB(A2)),RIGHT(A2,LEN(A2)*2-LENB(A2)))
こちらについては私も考えました。仰る通りこの問題に関してはこの数式でも回答が得られますが、
例えば製品名に“MG05KRカラーマグネットお徳用”というように“お徳用”という文字が各々に入っていた場合は目的が果たせません。
ただ、回答にある数式でしたらこの商品名でも対応できます。（まぁ商品名なんて色々になりますので無理やり感は否めませんが）目的はあくまで学習であり関数の理解ですので、できればまず何となくでもどういった数式なのか？きっかけをつかみ理解を深められればと思いご質問させて頂きました。

出所について、商品名はこのような所でまずいのかと思いまして濁しましたが
○和○ステムの○○○○極意っていう物です。
一応伏せ文字にしてみました。
ようやく半分ぐらいまで進んだのですがこの本ダメなのかな？？

すいません。ありがとうございました。

お礼日時：2014/10/22 21:39