エクセル文字列抽出関数についての補足説明のお願い

質問

エクセルの使用歴は長いのですが、簡単な関数以外使ったことがない初心者です。
文字列の中から、英字と数字を抜き出す関数を検索し、たどり着きました。
やりたいことは解決したのですが、入力した式の意味がわかりません。
以下の式で、関数の書式や目的は調べることが出来ますが、なぜそう言う処理を行うと、
英字と数字に分離できるかを初心者にわかりやすく教えて下さい。

宜しくお願い致します。

--------------------------------
A1に「aaaa1111」があるとき
B1に
　=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1に
　=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
で「aaaa」と「1111」が分離できます

以上

回答No.3

bunjii · ベストアンサー

>以下の式で、関数の書式や目的は調べることが出来ますが、なぜそう言う処理を行うと、英字と数字に分離できるかを初心者にわかりやすく教えて下さい。
初心者には難しいと思います。

>A1に「aaaa1111」があるとき
英字が4文字で5文字目から数字が並んでいることは人間の目で一瞬の読み取れます。
これはアナログ的に判断しています。
コンピューターはデジタル式に順番に比較しなければなりません。
そのためには手順が正しくないとエラーになって目的を達成できません。
英字と数字の境目をどのように判断するかを論理立てしなければなりません。
数字は0～9の10種類であるのでA1の文字列に0～9の文字が何番目に有るかを拾い出します。
この時に1つでも見つからない文字があるとエラーになって以後の処理を止めてしまいます。
A1に数字は1のみなのでエラーを起こさないために"0123456789"を後ろに追加してエラーを防止します。
A1&"012345679" → "aaaa11110123456789"
前提条件が分からないときを考慮してA1&"0123456789"を検索対象にしていると思います。

>B1に　=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
数式は（）の内側から順に評価されることはご存知と思いますので順番に説明します。

FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789") → {9,5,11,12,13,14,15,16,17,18}
FIND関数の第1引数の文字列を第2引数の中から探して最初に見つかった位置を返します。
例題の第1引数は｛｝で括られた10個の文字であり複数の返り値があります。
評価は並び順で0、1、2、・・・・の順に見つかった位置を羅列します。

MIN({9,5,11,12,13,14,15,16,17,18})) → 5
FIND関数の返り値（配列）から最小の値を返します。

LEFT(A1,5-1) → "aaaa"
MIN関数の返り値（5）を利用してA1の文字列から左側の4文字を切り出します。

>C1に　=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))
B1の処理に準じて処理されます。
MID関数は第1引数の文字列（A1）の指定文字位置（第2引数）から指定文字数（第3引数）を切り出します。
第3引数は過大の値を指定したとき最後の文字までを返します。

用語が理解できないときは補足してください。

お礼

bunjii様、回答いただきましてありがとうございました。初心者にも判りやすい説明をしていただきまして本当にありがとうございました。お陰様で、理解できました。

回答No.2

keithin

＞英字と数字に分離できるかを初心者にわかりやすく教えて下さい

カギになるのは，先頭から数えて「５文字目」に「最初の数字が現れる」ということです。

この５が判れば，
=LEFT(A1,5-1)　で前半の「aaaa」が
=MID(A1,5,沢山の数字)　で後半の「1111」が
それぞれ取り出せます。

次のステップとして，数式の中に入ってる
MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))
という部分で，今回の「５」を求めます。

これは配列数式という，通常のエクセルの参考書には出てこない計算のやり方を使いますが，やってる事自体は比較的単純です。

【step1】
A1&"0123456789"
を使って「aaa11110123456789」という文字列をまず作ります。
これはいまご相談が「文字数字」の並びになってる前提で書かれてますが，現実のＡ１にはもしかすると「abcde」のように数字が含まれてない言葉が記入されてしまうかもしれません。その場合にもエラーになったりせず，ちゃんと「前半のabcde」です「後半はありません」という結果を計算するための細工です。

【step2】
FIND(ある文字，"aaaa11110123456789")
という関数で，ある文字（たとえば「１」）が「aaaa11110123456789」の先頭から数えて何番目に現れるか計算します
今回はいきなり１がヒットして５番目に現れます

【step3】
ＦＩＮＤ関数の「ある文字」の所に「{0,1,2,3,4,5,6,7,8,9}」という書きぶりで，０から９まで合計１０個の数字をそれぞれ与える計算をさせます。これを配列計算と言います。
この段取りにより
０に対しては９が計算されます
１に対しては５が計算されます
２に対しては１１が計算されます
　：
９に対しては１８が計算されます
以上を束ねた｛9,5,11,12,13,14,15,16,17,18}という配列がFIND関数の結果になります

【step4】
MIN({9,5,11,12,13,14,15,16,17,18})
という計算で，最初の（＝先頭の）５が計算されます。

以上の計算ステップは，言葉でだらだらと説明を読む代わりにエクセルの「数式の検証」という機能を使い，ご自分の目でステップを追って確認していくことができます。
ご利用のエクセルのバージョンが不明のご相談ですが，エクセル２００７以降をご利用なら数式タブにあるので，実際に手を動かしていちど確認してみて下さい。

お礼

keithin様、判りやすい解説付きで補足していただきまして、本当にありがとうございました。大変参考になりました。いただいたアドバイスを元に、「数式の検証」機能も勉強して、自分で確認できるよう頑張ります。又、初心者のためExcelのバージョンを明記するのを忘れてしまい、申し訳ありませんでした。早々の対応、本当にありがとうございました。

回答No.1

FEX2053

MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))

これが分かったらわかりますよね。

これは、0～9の数字がA1の中にあるかどうかを探しているんです。
{0,1,2,…,9}のどれかをA1から探し、その先頭の位置を返せって
言う関数ですから。

A1&"0123456789"は、A1だけでもいいのですが、A1に数字がない
とエラーしてしまうので、念のためひっつけただけです。

逆に言えば、もしA1に入ってる数字が"1"だけなら

=LEFT(A1,FIND("1",A1)-1)

これだけで、"aaaa"が引っ張り出せます。これならわかりますよね。

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1))

も同じで

=MID(A1,FIND("1",A1),LEN(A1))

ってことで、「"1"が見つかった位置から最後の文字（=LEN(A1)）まで
引っ張り出せ」って処理ですから、後ろの"1111"が出てきます。

お礼

FEX2053様、早々に回答いただきまして、ありがとうございました。お陰様で、疑問が解けました。初心者に付き、質問するにも基本ルール等が良く判らず、又、お礼についても不行き届きの点があるかも知れませんが、ご容赦下さい。本当にありがとうございました。