Excelで数字の前のゼロ(前ゼロ)を表示して埋める(ゼロパディングする)方法
本エントリーの目次
Excelを使って表や資料の作成業務を行っていると、こんな問題が発生し、困ってしまうことがあります。
Excelのセルに『001』と入力しても、『1』に変換されてしまう！
たとえば商品の一覧表を作成していて、その商品コードや商品名、単価などを入力していたとします。
このとき、商品コードが001の商品があったので、以下のように『001』と入力。
次に商品名の入力を始めようと、別の列のセルにカーソルを移動したところ、『001』と入力されていたセルの内容が、自動的に(勝手に)『1』と書き換え・変換されてしまうのです。
つまり、正しい商品コードである『001』をExcelのセルに表示できない。
そして誤った商品コードである『1』に勝手に変換されてしまう、という問題が起こります。
一定の桁数に文字列を揃えるために、文字列の前にゼロを付与する処理はゼロパディングと呼ばれています
一般的には、一定の桁数に文字列を揃えるために、ベースとなる文字列の前にゼロを付与する処理は、ゼロパディング(zero padding)やゼロサプライ(zero supply)。
ゼロフィル(zero fill)と呼ばれています。
また、ベースとなる文字列の前に付与するゼロのことは、前ゼロと呼ばれていることが多いです。
先の『001』という文字列の例では、3桁の文字列とするために、ベースとなる文字列の1に、前ゼロを2つ付けてゼロパディングしている。
ということです。
今回は、Excelで数字の前に入力したゼロが、自動的に変換され消えてしまう現象の原因と、数字の前のゼロ(前ゼロ)を表示して埋める(ゼロパディングする)方法をご紹介しますので、前ゼロが消えてしまい困っているという方は、ぜひ参考になさってください！
Excelで数字の前に入力したゼロが、自動的に変換され消えてしまう現象の原因
Excelでは、数字を数値と文字列の2種類の方法で扱います。
数字を数値として扱う場合には、001と1は同じものです。
これに対して数字を文字列として扱うケースでは、001と1は異なるものとして扱います。
そしてExcelの初期設定状態のセルの書式設定である『標準』分類では、『001』といったようなゼロパディングされた数字を数値として扱います。
そのため、001という数値は1と同じであることから、自動的に1という数値に変換されてしまう、というわけ。
つまりこの挙動は、Excelの動作仕様によるものなのです。
Excelで数字の前のゼロを表示する方法
ここからは、Excelで数字の前のゼロを表示する方法をご紹介していきましょう！
今回は、以下のような商品一覧表の商品コード列を、前ゼロあり5桁の商品コードにする手順を例に、その方法をご説明します。
※上記表中の商品コードは、はるるが勝手に付与した独自のものであり、メーカーさんの商品コードなどではありません。
セルの書式設定を文字列とする方法
先ほど、『数字を数値として扱った場合、Excelは前ゼロを勝手に消してしまう』というような旨、ご説明しています。
そして『数字を文字列として扱うケースでは、001と1は異なるものとして扱う』とも書きました。
そのためセルの書式設定で『文字列』分類とし、数字を文字列として処理してもらうようにすれば、Excelは『001』という文字列と『1』という文字列は異なるものであると判断。
これにより『001』という入力は、そのまま変換されずに『001』として表示・処理されます。
この方法では、まずは文字列として処理してほしいセルの範囲を選択してください。
次に対象のセル範囲で右クリックを行いコンテキストメニューを表示。
表示されたメニューの中から、『セルの書式設定』メニューをクリックします。
すると『セルの書式設定』画面が表示されるので、『分類』で『文字列』を選択してから、『OK』ボタンをクリックします。
これで選択していたセル範囲の『分類』が『文字列』に変更され、それらセルの入力値は文字列として処理されるようになります。
あとは商品コード列に『00001』、『00002』、『00003』と入力していけば、前ゼロ付きの商品コードを入力しても、前ゼロが変換・削除されずそのまま残り続けます。
シングルクォートを先頭に付与する方法
Excelでは、セルの入力値の先頭に『’』(シングルクォート)文字を付与すると、そのセルのシングルクォート以後の数字や数式を文字列として表示する、という仕様があります。
たとえばシングルクォートを使用せず、以下のようにセルに『=3+6』と入力したとします。
この場合、Excelでは計算結果である『9』をセルに表示します。
ところが、セルに入力する計算式の先頭にシングルクォートを付与し、『’=3+6』と入力してみるとどうでしょう。
こちらのケースでは、シングルクォート文字の後の=3+6という計算式を文字列として扱って表示するため、計算結果ではなく『=3+6』という文字列がそのまま表示される、というわけです。
Excelのこの仕様を利用することでも、前ゼロ付きの文字列を表示可能です。
以下のように、セルに『’00001』と入力してみましょう。
その後、別のセルにフォーカスを移動すると、以下のように『00001』と表示されます。
同様に『’00002』、『’00003』と入力していけば、『00002』、『00003』と、前ゼロ付きの商品コードを表示でき、前ゼロが変換・削除されずそのまま残り続けます。
TEXT関数を使用する方法
Excelでは、表示形式コードと呼ばれる特殊な文字列を使用することで、数値に書式設定を適用し、数値の表示方法を変更するTEXT関数が用意されています。
この方法では、商品コード列をコピーし、新たに商品コード(計算式の元の数字)列を追加。
そして、商品コード列の内容をすべて削除した以下一覧表を用いて説明しましょう。
TEXT関数では、関数の計算の元となる『値』に対し、『表示形式』に表示形式コードを指定することで、数値の表示方法を変更します。
そこで、商品コード(計算式の元の数字)列のコードを計算の元とし、その結果を商品コード列に取得するため、以下の形式で数式を入力します。
1 | 『=TEXT(商品コード(計算式の元の数字)列のセル,"00000")』(下記例では『=TEXT(B3,"00000")』と入力。) |
この数式の入力により、B3セルの『値』に対して『表示形式』が適用され、『00001』と表示されます。
次にこの数式をオートフィルで下方向に複製してください。
以上の操作により『00001』～『00011』と、前ゼロ付きの商品コードが表示されるようになったので、最後に商品コード(計算式の元の数字)列を非表示状態とします。
参考：Excelで行や列、シートを非表示にしたり折りたたんで途中計算式を隠す方法
表示形式コードの0は、桁数が短くその桁に数値がない場合には、0を表示するという意味を持ちます。
したがって、先ほど『表示形式』に指定した表示形式コードである00000という文字列は、5桁に満たない場合は、先頭にゼロを付与して5桁に揃えて表示せよ、という意味となります。
今回は分かりやすくするために、商品コード(計算式の元の数字)列を追加して説明しています。
ですが商品コード(計算式の元の数字)列を追加せず、以下のように数式を変更すれば、同様の結果を取得可能です。
1 2 3 | =TEXT(B3,"00000") ↓ =TEXT(1,"00000") |
ちなみにTEXT関数では、前ゼロ付与の他にもさまざまな書式設定を適用可能です。
使用可能な表示形式コードについての詳細は、以下を参照ください。
Microsoft – Office サポート TEXT 関数
集計・計算に使いやすいように、Excelで数字の前のゼロを表示する方法
ここまでにご紹介したセルの書式設定を文字列とする方法やシングルクォートを先頭に付与する方法、TEXT関数を使用する方法には要注意ポイントが存在します。
それは、前ゼロ付きの表示をしたセルの値を集計・計算に使いにくい、ということです。
と言いますのも、これらの方法で前ゼロ付きの表示をしたセルを組み込んだ計算式が入力されたセルの書式設定が、そのセルの編集をすることで文字列に変わってしまう。
前ゼロ付きの表示をしたセルの値が、セル範囲の合計値を計算するSUM関数の計算結果に加味されないなどの問題が起こるからです。
※これらの動作仕様は、Excel 2016で確認しています。
そのため、前ゼロ付きの表示をしたセルの値を集計・計算に使いたい場合には、これからご紹介する『ユーザー定義』分類を使う方法をおすすめします。
『ユーザー定義』分類を使い、数字の前のゼロを表示する方法
この方法ではまず、セルの書式設定を文字列とする方法と同じ手順で『セルの書式設定』を開きます。
最初に、前ゼロ付きの表示をしたいセルの範囲を選択してください。
次に対象のセル範囲で右クリックを行いコンテキストメニューを表示。
表示されたメニューの中から、『セルの書式設定』メニューをクリックしてください。
すると『セルの書式設定』画面が表示されるので、『分類』で『ユーザー定義』を選択し、『G/標準』と表示されている『種類』を入力状態とします。
そして『種類』に『00000』と入力してから『OK』ボタンをクリックし、『セルの書式設定』画面を閉じてください。
以上の操作により、商品コード列の値が前ゼロ付きの表示に変更されています。
そして『00001』と表示されているB3セルの内容を見てみると、以下のように『1』と表示されています。
つまり『ユーザー定義』分類を使う方法では、セルの値は書き換えず、見た目だけ前ゼロ付きの表示とする、ということです。
この特性により、たとえば以下のように、前ゼロ付き表示の商品コードの値をすべて合計するような数式を入力したとします。(=sum(B3:B13)という数式により、B3～B13セルの値の合計値を計算。)
すると1～11までの数値を合計した66という値が、正しく算出できていることが分かります。
というわけで、前ゼロ付きの表示をしたセルの値を集計・計算に使いたい場合には、『ユーザー定義』分類を使う方法がおすすめです。
以上、参考になさってくださーい！