Excelでゼロ埋めするテクニック
ゼロ埋めってなに？
ゼロ埋めとかゼロパディング、ゼロフィルといった言葉を聞いたことがあるでしょうか。
どれも意味としては文字列の桁数を揃えるために0（ゼロ）を付け足すということです。
たとえば次のようなデータがあったとします。
| データ |
|---|
| 1 |
| 15 |
| 120 |
このデータをゼロ埋めすると次のようになります。
| データ |
|---|
| 001 |
| 015 |
| 120 |
ゼロ埋めすることで桁数が揃ってデータが見やすくなりましたね。
ゼロ埋めをする理由
ゼロ埋めの利点はデータが見やすくなるという点だけではありません。
ゼロ埋めをすることによってある問題を回避することができるのです。
次のようなExcelデータがあったとします。
このデータをソートするとどうなるでしょうか。
Data1からData12まできれいに整列すると思うでしょうか。
実際はこうなります。
ソートの仕組みをよく分かっていないと「ちゃんとソートできてないじゃん！このExcel壊れてる！」なんて思うかもしれませんが、この結果に何も問題はありません。
詳しくは割愛しますが、ソートをする際は文字を一つずつ比較してデータを並べ替えています。
この場合4文字目まではすべて同じですが、5文字目が若い順に並べ替えられます。
その結果Data1の次にData10、Data11、Data12、Data2...と続くのです。
ソートの仕組みから考えてこの結果は至極まっとうなものなのです。
でもきっと、求めている結果はこうじゃないですよね？
そんな時にゼロ埋めをしておけばこんな感じで思った通りにソートできるのです。
つまり、ゼロ埋めすれば思ってたのと違う感じでソートされてしまう問題を回避できるのです。
Excelでゼロ埋めする方法
それでは、Excelでゼロ埋めする方法を考えていきましょう。
1から自分でExcelファイルを作る場合はゼロ埋めを意識していれば問題ないですよね。
よくあるパターンとしては誰かが作ったExcelファイルを使って作業する場合などでしょう。
「ゼロ埋めされてなくてちゃんとソートできないじゃん！」となることが多々あります。
ゼロ埋めをする対象として以下のようなケースを考えていきます。
- 数値のみ
- 定型文字列 + 数値
- 非定型文字列 + 数値
数値のみ
これが一番簡単なケースです。
（数値のみの場合はゼロ埋めしなくても正しくソートしてくれます）
次のようなデータをゼロ埋めしていきます。
まず考えるべきことは、何桁でゼロ埋めするかということです。
上の図では最大値は12340なので5桁でもいいでしょうし、今後もっと桁の大きい数値を追加する可能性があるならもっと余裕を持たせておいた方がいいでしょう。
とりあえず今回はこのデータに対して10桁のゼロ埋めをしてみましょう。
(n桁でゼロ埋めと表現する場合、ゼロ埋めした後の桁数がn桁になるということです)
A列のデータを基に、B列にゼロ埋めした数値を作成します。
B1のセルに下記の式を入力しましょう。
=RIGHT(REPT("0", 10) & A1, 10)
入力したらB1セルの右下の黒い四角の部分をダブルクリックするとセルの内容が下までコピーされます。
これでゼロ埋めができました。
定型文字列 + 数値
定型文字列というのはその名の通り決まった型の文字です。
下記の画像でいうと「Data」がそれに該当します。
これをゼロ埋めするには文字列部分と数値部分を分けて、数値部分をゼロ埋めして文字列と結合するという手順を取ります。
まず、文字列部分を切り出すには次のようにします。
=LEFT(対象のセル, 定型文字列の文字数)
実際の入力はこんな感じになります。
=LEFT(A1, 4)
次に数値部分を切り出すには次のようにします。
=MID(対象のセル, 定型文字列の文字数+1, 数値部分の文字数)
実際の入力はこうなります。
=MID(A1, 5, LEN(A1))
LEN関数は引数の文字数を返す関数です。
上記例でMID関数の数値部分の文字数にこれを使用していますが、必要な文字数を超える分は無視されるので問題ありません。
これが数値部分の文字数より少なくなるとその分文字が削られてしまうので、確実に数値部分の文字列を上回る数値を取得するためにLEN(A1)としました。
以上を踏まえて、A列のデータをゼロ埋めするにはB列に次のような式を入力します。
今回は5桁のゼロ埋めとしました。
=LEFT(A1, 4) & RIGHT(REPT("0", 5) & MID(A1, 5, LEN(A1)), 5)
これでゼロ埋めできました。
B列を基準にソートすれば正い順番でソートされます。
非定型文字列 + 数値
非定型文字列とは決まった型を持たない文字列のことで、ここではとりわけ文字数の揃っていない文字列という意味で使用しています。
イメージとしては次のような感じになります。
「文字列 + 数値」という形式ですが文字列の部分の文字数が揃っていません。
これをExcel関数だけで文字列部分と数値部分に分けるのは非常に困難です。
色々こねくり回せば何とかなるかもしれませんが、Excel関数は複雑になると壊滅的に読みにくくなってしまうので別の方法を検討した方がいいでしょう。
簡単な方法としてはテキストエディタを使用します。
私はnotepad++というエディタを使用していますがサクラエディタ等なんでも構いません。
とにかく正規表現が使えるものを選んでください。
テキストエディタで文字列と数値を分ける
まず、Excelのデータをテキストエディタに貼り付けます。
次に、下記の様に置換します。
検索条件は「(\D+)(\d+)」とし、置換条件は「$1\t$2」とします。
こうすることで文字列部分と数値部分がタブで区切られます。
テキストエディタでの作業は以上です。
Excelに戻ってゼロ埋めする
置換したテキストエディタのデータをExcelに貼り付けると次のようになります。
D1セルに次のような式を入力します。
ここでは5桁でゼロ埋めします。
=B1 & RIGHT(REPT("0", 5) & C1, 5)
これでゼロ埋めできました。
D列を基準にソートすれば正しい順番でソートされます。
もう少し詳しいゼロ埋めの解説
ここまで詳しい説明をすっ飛ばしてきましたが、なぜ以下のようにするとゼロ埋めできるのか見ていきましょう。
=RIGHT(REPT("0", 10) & A1, 10)
この式は3つのパートに分けられます。
ひとつ目は REPT("0", 10) の部分。
REPT関数は指定した文字列を指定した回数繰り返した文字列を返します。
REPT("0", 10)とすると「0000000000」という10桁の文字列になります。
次に & A1 の部分。
&を使用すると文字列を結合することができます。
A1が「555」だったとするとREPT("0", 10)と結合することで「0000000000555」という13桁の文字列になります。
最後にRIGHT関数の部分です。
RIGHT関数は指定した文字列を右から指定した文字数分だけ切り出す関数です。
RIGHT(REPT("0", 10) & A1, 10)とすることで「0000000000555」を右から10文字切り出した「0000000555」という10桁の文字列になりゼロ埋めができるのです。
