Excel。ゼロ付番号で桁数を整えたいけど、文字数がバラバラで困っている。
＜フラッシュフィル・IF+LEN+TEXT+SUBSTITUTE関数＞
次の表をご覧ください。
A列が現在使用しているコードです。
桁数がバラバラで並び替えをすると綺麗に並ばないなど、業務に支障をきたしているので、B列の新コードのように整えたい場合、どのようにしたら楽に作れるのかということを今回紹介していきます。
A列の問題は、数値よりも、カテゴリー分けされている、アルファベットが問題です。
例えば、頭2桁がカテゴリーとなっていれば、LEFT関数を使って、それ以降は数値なので、それを整えるようにすればいいわけですが、今回は、アルファベットの桁数が”ab”と”bnk”と桁数がバラバラ。
こうなると、左から何文字目までが、カテゴリーなのかわからないので、LEFT関数では太刀打ちできません。
そこで、『フラッシュフィル』というExcel2010とかの旧バージョンには無い機能を使うことで、カテゴリーのアルファベットを抽出することが簡単にできますので、まず、フラッシュフィルを使ってみることにします。
次のように入力をします。
C2にabと入力しておきます。
フラッシュフィルは便利なのですが、自動的にこちらのやりたいことを完璧に汲み取れませんので、抽出したい形状のサンプルを入力しておくといいです。
C2をクリックして、データタブのデータツールにある「フラッシュフィル」をクリックします。
アルファベットだけが綺麗に抽出されました。ただし、フラッシュフィルは完璧なツールではありませんので、上手くいかないケースもあります。
【TEXT+SUBSTITUTE関数でゼロ付に変換する】
カテゴリーのアルファベットは抽出できました。次の問題は、数値の桁数がバラバラということですね。ab1は、ab00001とゼロを4個入れる必要がありますが、bnk652は、bnk0654となるので、ゼロは1個で済みます。数値の桁数を数えるのは大変です。
そこで使用する関数が、TEXT+SUBSTITUTE関数です。
D2に次のような数式を作成します。
=C2&TEXT(SUBSTITUTE(A2,C2,""),"00000")
オートフィルを使って数式をコピーしておきます。
数式について説明します。
=C2& までは、問題ないですね。&は文字結合させることができますので、C2のあとに文字を結合させます。
TEXT(SUBSTITUTE(A2,C2,""),"00000")
TEXT関数は、表示形式の関数です。
"00000"とすれば、ゼロ付数値にすることができます。
そして、SUBSTITUTE関数は、どのような処理をしているのかというと、
文字列を置換してくれる関数です。何をしているかというと、アルファベットを削除して、数値だけを残したいわけです。
この数式は、残った数値をゼロ付にする処理をしていたわけです。
ただし、これで完成では、ありません。
abは2文字でbnkは3文字なので、綺麗に揃っていません。ただ単に、ゼロが挿入されただけです。
そこで、IF関数を使って次のように数式を修正します。
=IF(LEN(C2)=2,C2&TEXT(SUBSTITUTE(A2,C2,""),"00000"),C2&TEXT(SUBSTITUTE(A2,C2,""),"0000"))
LEN関数を使って、文字数を数えます。
それに合わせて、ゼロの数を調整させています。
あとは、B列に値のコピーをすれば、完成です。
今回のようにコードを作る場合、将来を見越して、カテゴリーなど統一させるなど、最初に考えて作ると後が楽になりますが、修正する必要がある場合には、このようなフラッシュフィルを使ってみるという方法もあります。
