よくあるお悩み
Excelでセルから数字のみを抽出したい、文字のみを抽出したい。
既存の関数だけでは、数字や文字の量が変わると対応できない。
VBAはよくわからないから、手っ取り早く関数を教えて！
これ、配列数式と関数を組み合わせだけで、簡単に解決できます。
(数字や文字の長さが一定であれば、LeftやRight関数だけを使えばラクです)
いきなり答え
例えばA1(数字+文字)がある場合、A2(数字のみ),A3(文字のみ)のように自動で出力したい時。
@Excel2010
A1 = "50mg"
A2 = 50
A3 = "mg"
以下の関数で、数字だけ/文字だけを抽出できます。
@Excel2010
A1 = 50mg ;元データ
A2 = LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) ;A1から数字のみ"50"を抽出
A3 = SUBSTITUTE(A1,A2,"") ;A1から文字のみ"mg"を抽出
解説
巷にはこの関数の解説が少ないので、ここで解説します。
数字のみを抽出する関数
@Excel2010
A2 = LOOKUP(10^17,LEFT(A1,COLUMN($1:$1))*1) ;A1から数字のみ"50"を抽出
3つの関数が入り組んでいます。LEFT関数に1を乗じているのもポイントですね。
関数の動きを、ひとつひとつ見ていきましょう。
COLUMN($1:$1) ;配列数式
;1から256までの連続した数字が入る単純な配列。
;INDEX(配列,n)で、配列n番目のデータが見れます。中身を確認した結果は以下の通り。
;INDEX(COLUMN($1:$1),1)=1
;INDEX(COLUMN($1:$1),2)=2
;INDEX(COLUMN($1:$1),256)=256
ここまでは、(1,2,3・・・,256)というただの配列です。
次に元データ(A1セル)にアクセスします。
LEFT(A1,COLUMN($1:$1)) ;配列数式
;元データが入るA1セルを、配列番号の数分だけ左から切り出した配列。
;INDEX(配列,n)で、中身を確認した結果は以下の通り。
;INDEX(LEFT(A1,COLUMN($1:$1)),1)=5
;INDEX(LEFT(A1,COLUMN($1:$1)),2)=50
;INDEX(LEFT(A1,COLUMN($1:$1)),3)=50m
;INDEX(LEFT(A1,COLUMN($1:$1)),4)=50mg
元データから文字データを切り出しました。すべて"文字データ"の扱いなので、これをうまく利用します。
LEFT(A1,COLUMN($1:$1))*1) ;配列数式
;配列へ1を掛けることで、"文字データ"を"数字データ"にします。
;INDEX(配列,n)で、配列n番目のデータを見れます。中身はこんな感じでした。
;INDEX(LEFT(A1,COLUMN($1:$1))*1,1)=5
;INDEX(LEFT(A1,COLUMN($1:$1))*1,2)=50
;INDEX(LEFT(A1,COLUMN($1:$1))*1,3)=#VALUE
;INDEX(LEFT(A1,COLUMN($1:$1))*1,4)=#VALUE
文字データ配列へ1を乗じたことで、数字データだけ残りました。
LOOKUP(10^17, 配列)
;配列の最後にあるデータを返します。
;10^17は、超大きい数字の意味。
;今回、最後の配列は[2]です。50が入っていましたね。
;10^17,LEFT(A1,COLUMN($1:$1))*1=50
一番桁数が大きい配列を取り出せました。これで数字のみの抽出は完了。
文字のみを抽出する関数
@Excel2010
A3 = SUBSTITUTE(A1,A2,"") ;A1から文字のみ"mg"を抽出
こちらは簡単です。
SUBSTITUTE(A1,A2,"")
;元データから数字を除きます。
"50mg"から50を除いて、"mg"が残りました。これで文字のみの抽出は完了。
応用例
関数で単位を抽出したい場合に便利ですね。使い回しも利きます。単位がわかれば、CONVERT関数で単位合わせもできますよ。
例1: 10Mドル vs 100百万円、100mg vsと0.1g、どちらが大きい？などを自動処理
例2: M$10, €100を比較(単位の文字が左側にあるので、LEFT関数ではなくRIGHT関数を使えばOK)
参考
こちらも参考にしてください。
一番下のデータを取得する LOOKUP関数
http://www.relief.jp/itnote/archives/003450.php
誰がはじめに見つけたのかわかりませんが、Excelを使いこんでいる方の間では結構有名な、「裏ワザ」といっていい方法です。
おわり