Excel。氏名の一文字ずつをセル取り出して分割するには？
＜MID+ VLOOKUP +COLUMN関数＞
まずは、完成例を見ていただいた方がイメージしやすいと思いますので、
次の表があります。
納品書のような表で、どうしても、
担当者名を一文字ずつに分割してセルに表示させたいというのが
今回のリクエスト。
自分で入力するとしたら、B2のコードが変わる都度、
入力し直す必要が発生しますから、確かに効率が悪い。
問題が解決しやすくなりますので、
まずはやりたいことの流れを、考えてみましょう。
「文字を抽出する」ことがテーマ。これを押さえておきましょう。
まず思いつくのは、
B2のコードからA5:B8の表から担当者名を検索するので、
これは、VLOOKUP関数を使うと良さそうですね。
次は、VLOOKUPで検索した結果を一文字ずつセルに
振り分けていきたいので、
端から一文字ずつ抽出したいので、MID関数を使うと抽出できます。
さらに、その結果を列方向にオートフィルで数式をコピーします。
となると、2文字目・3文字目というように
抽出箇所をずらしていく必要がありますので、
COLUMN関数を使うとうまくいきそうな感じですね。
ということで、
今回は、MID+ VLOOKUP +COLUMN関数の
コラボレーションで数式を作成して、
問題を解決していきましょう。
あくまでも、
今回は、コードから担当者名を検索するのがテーマではなくて、
文字を抽出するのがテーマですから、MID関数が母体になるわけです。
では、C2をクリックして、MID関数ダイアログボックスを表示しましょう。
文字列。文字列には、コードで検索された担当者名が入力されてきますので、
VLOOKUP関数をネストしていきます。
名前ボックスの▼を使ってVLOOKUP関数ダイアログボックスを表示しましょう。
検索値には、コードを入力してあるところですので、$B$2。
範囲には、名簿が該当しますので、$A$6:$B$8。
オートフィルを使って数式をコピーするので、
絶対参照を忘れないようにしましょう。
列番号には、今回検索したいのは担当者名なので、
範囲の左から2列目のデータですから、2。
検索方法は、完全一致ですから、FALSE。
あるいは、0【ゼロ】でもOKですね。
そして、OKボタンは押さないように気を付けて、
数式バーを使って、MID関数に戻りましょう。
開始位置ですが、最初は1、
次は2、3と数字を増やすことによって、
一文字目二文字目というように抽出することが出来るのですが、
どうやって数値を増やしたらいいのか？
そこで、COLUMN関数が登場します。
列番号の情報を算出してくれる、COLUMN関数。
COLUMN(A2)
と設定することによって、1という数値を算出してくれます。
列方向にオートフィルすればCOLUMN(B2)となって
”2”という数値を算出してくれます。
文字数には、1文字分を抽出したいので、1。
あとは、OKボタンをクリックして、
列方向にオートフィルを使って数式をコピーしてみましょう。
なお、C2の数式は、
=MID(VLOOKUP($B$2,$A$6:$B$8,2,FALSE),COLUMN(A2),1)
手入力で数式が作れる方は、ダイアログボックスにこだわる必要はありませんね。数式をコピーすると、
このように、完成しました。
実際に一つずつのセルに一文字ずつ抽出入力する必要があるのか、
ないのかは別としてこのような方法を使うと作成することが出来ますので、
機会があれば是非。
