iPentec.com / Document / カテゴリ: Microsoft Excel / タグ: Excel Microsoft Office

セルの値の先頭に0を埋めて桁揃えする

概要

Excelで数値の頭に0をつけて桁を揃える、ゼロ埋め、ゼロパディングには、いくつかの方法があります。この記事では以下の方法を紹介します。

TEXT関数を利用する
REPT関数を利用する
カスタム書式設定を利用する

方法:TEXT関数を利用する

TEXT関数を利用して値を整形して0を埋めた文字列にする方法です。

書式

TEXT(整形する値, 整形フォーマット)

整形する値を整形フォーマットに適用して適用後の文字列を関数の戻り値として返します。C#,Visual BasicのFormat関数に近い機能の関数です。

手順1: 4桁の0埋め

Excelを起動し、下図の表を準備します。
セルの値の先頭に0を埋めて桁揃えする:画像1

C2セルに下記の数式を入力します。"0000"を与えることで4桁の数値で整形します。桁が足りない場合は0で埋められます。

=TEXT(B2,"0000")

セルの値の先頭に0を埋めて桁揃えする:画像2

C2セルに"0045"の値が表示されます。B2セルの数値の先頭桁を0で埋めて4桁に揃えた文字列がC2に表示されます。
セルの値の先頭に0を埋めて桁揃えする:画像3

C2セルをコピーし、数式をC3セル以下にペーストします。B列の数値を0埋めして4桁に桁揃えした文字列がC列に表示されます。
セルの値の先頭に0を埋めて桁揃えする:画像4

4桁で0埋めできました。

セルの値の先頭に0を埋めて桁揃えする:画像5

手順2: 7桁の0埋め

桁揃えする桁数を変える場合は、TEXT関数の第二引数の"0"の個数を変えます。
下図の例では、TEXT関数の第二引数に"0000000"を与えており、7桁で桁揃えします。
セルの値の先頭に0を埋めて桁揃えする:画像6

セルの値の先頭に0を埋めて桁揃えする:画像7
セルの値の先頭に0を埋めて桁揃えする:画像8
セルの値の先頭に0を埋めて桁揃えする:画像9

補足

なお、揃える桁数より、元の数値が大きい場合は数値全体がTEXT関数の結果になります。
セルの値の先頭に0を埋めて桁揃えする:画像10

方法: REPT関数を利用する

REPT関数を利用する方法があります。
REPT関数は第一引数で与えた文字列を指定した回数繰り返した文字列を返す関数です。

書式

REPT([繰り返す文字列], [回数])

[繰り返す文字列]を[回数]回繰り返した文字列を返す関数です。

手順

Excelを起動し、下図の表を準備します。
セルの値の先頭に0を埋めて桁揃えする:画像11

C2セルに次の数式を入力します。

=REPT("0",4-LEN(B2))&B2

セルの値の先頭に0を埋めて桁揃えする:画像12

数式を確定します。4桁で0埋めされた文字列が表示されます。
セルの値の先頭に0を埋めて桁揃えする:画像13

C2セルをコピーし、C3からC8セルにペーストします。
セルの値の先頭に0を埋めて桁揃えする:画像14

4桁で0埋めした文字列が作成できました。
セルの値の先頭に0を埋めて桁揃えする:画像15

数式の解説

=REPT("0",4-LEN(B2))&B2

REPT("0",4-LEN(B2)) は"0"の文字列を、4-LEN(B2)回繰り返した文字列を返します。ます。 LEN(B2) はB2セルの長さを返します。4-LEN(B2) は4-(B2セルの文字列長)となります。B2セルは2文字なので、4-2で2となり、"0"を2回繰り返し"00"となります。 &B2は文字列の結合の式となり、"00" & "38"(B2セルの値) となり "0038" がセルに表示されます。
4-LEN(B2)の"4"の部分を0埋めして揃えたい桁数を桁数に設定すれば、桁数を変えた0埋めもできます。

ワンポイント

入力される値が数値の場合は、TEXT関数を利用する方法、REPT関数を利用する方法、ユーザー定義の書式設定を利用する方法が利用できますが、入力される値に文字が混ざっている数値でない値の場合は、REPT関数を利用する方法でないと意図した動作になりません。
セルの値の先頭に0を埋めて桁揃えする:画像16

方法: ユーザー定義の書式設定を利用する

ユーザー定義の書式設定を利用する方法です。

手順

Excelを起動し、下図の表を準備します。
セルの値の先頭に0を埋めて桁揃えする:画像17

B列を選択します。
セルの値の先頭に0を埋めて桁揃えする:画像18

右クリックしポップアップメニューを表示します。メニューの[セルの書式設定]の項目をクリックします。
セルの値の先頭に0を埋めて桁揃えする:画像19

セルの書式設定ダイアログが表示されます。[表示形式]のタブをクリックして選択します。左側の[分類]のリストの[ユーザー定義]の項目をクリックして選択します。
セルの値の先頭に0を埋めて桁揃えする:画像20 セルの値の先頭に0を埋めて桁揃えする:画像21

右側のエリアの[種類]のテキストボックスに書式フォーマットを入力します。今回は6桁で0を埋めた桁揃えをすることにします。以下の文字列を入力します。

入力ができたら[OK]ボタンをクリックしてダイアログを閉じます。
セルの値の先頭に0を埋めて桁揃えする:画像22

Excelのメインウィンドウに戻るとB列の数値が整形できていることが確認できます。6桁で揃えられ、先頭は0で埋められています。
セルの値の先頭に0を埋めて桁揃えする:画像23

値を0で埋めて桁揃えできました。
セルの値の先頭に0を埋めて桁揃えする:画像24

A.W.S (Another World Story)

セルの値の先頭に0を埋めて桁揃えする:画像25

五月の夕暮れ。ダイニングの窓から金色の光が差し込み、こげ茶のテーブルを柔らかく照らしている。食器棚のガラスに反射した夕陽がキラキラと瞬き、炊きたてご飯の湯気と味噌汁の香りが部屋いっぱいに広がっていた。
テーブルの端ではノート PC を開いたあずさが眉間にしわを寄せ、Excel のセルとにらめっこしている。向かいには妹のみなが湯呑み茶碗を回しながら、その様子を興味津々で眺めていた。

「ねえお姉ちゃん、さっきから数字ばっかり眺めて溜め息ついてるけど、何してるの？」
「取引先に提出する在庫リストのコードを揃えてるの。桁数バラバラだと ‘システムが認識しません’ って冷たくはじかれるから、先頭に0を埋めて6桁に統一しなきゃいけないんだけど……入力数が多くて大変」
「0を埋める？　どうやって？　一個ずつ手で打つの？」
「いやいや、Excel にはちゃんと便利ワザがあるのよ。たとえば A2 に‘123’って入ってるときは、隣のセルに

=TEXT(A2, "000000")

って打つだけで‘000123’になるの」
「へぇ！　カスタム表示形式で‘000000’って設定してもいけるよね？」
「そうそう！　妹のくせに詳しいじゃない」

みなは得意げに胸を張ると、手元のティッシュ箱を Excel のショートカットキー代わりにポンと叩いた。
「ねえ、もし桁数ミスったまま提出しちゃったらどうなるの？」
「担当者さんに『コード違います』って即電話が来る。前に先頭ゼロが 1 個足りなくて、商品が“存在しない”扱いになったことがあったんだよね」
「“存在しないアイテム”かぁ……なんか RPG の隠しアイテムみたい」
「あはは。そうなれば逆にレアかもね」

笑いながらも、あずさは真剣な眼差しでセルの海をスクロールし、コーヒーをひと口。香ばしい苦味が緊張をほぐしてくれる。

みながふと思いついたように口を開いた。
「ところでさ、この‘ゼロ埋め’でお小遣いも桁増やせたらいいのにね。千円を‘000001000’とか！」
「残念ながらリアルマネーは関数じゃ増えません。ていうか金額も増えてないじゃない」
「むむむーぅ……あれ？」
みなが一点を指差す。セル B12 が薄い緑にハイライトされ、そこにはきっちり6桁の『000000』が。
「空のセルまで ‘ゼロ埋め’したんだ？」
「いや、そこは入力してないはず……」
あずさが訝しげに数式バーを見ると、そこには何もない。正真正銘、値として『000000』が鎮座している。

みながニヤリと笑った。
「お姉ちゃん。これ、在庫ゼロの品番って意味だと思う？」
「え？　そうかも……え、でもそんな商品リストにあったっけ？」
「なかったよ。だから――」

みなはそっと皿に盛ってあるコロッケを指でつまみ上げ、B12 のセルを見下ろして宣言した。

「在庫ゼロってことは、テーブルで食べちゃえば証拠隠滅だよね♪」
「ちょっ、Excel のゼロと夕飯の在庫を一緒にするなー！」

みなが口いっぱいにコロッケを頬張るのと、あずさが情けない声を上げるのが同時。
ダイニングにはふたりの笑い声と、どこかむなしげに整列した ‘000000’ が並び――

今日も我が家の帳尻は、ゼロで“ぴったり”合ったのでした。

著者

neg

iPentec.com の代表。ハードウェア、サーバー投資、管理などを担当。
Office 365やデータベースの記事なども担当。

作成日: 2016-08-22

改訂日: 2023-12-22

目次