Excelでだって、アタマをゼロ(0)にしたい!郵便番号や電話番号も、シートで正確な表示をする方法

【2023年10月追記】

本記事で「準備中」とされていた「自動データ変換抑制機能」について、Microsoft 365 バージョン2309以降のアプリにて、一般提供開始されたことが確認できました。

［オプション］→［データ］タブのところで「先頭のゼロを削除して数値に変換する」のチェックボックスをチェック、もしくは、チェックはずしすることで、希望する表示方法に変更できるようになります。

なぜなら、計算を最大の目的とするExcelには、たとえば0123や007といった不自然な数値を、計算可能な形式へ自動変換してしまう、おせっかい機能があるからです。

とはいえ、たとえばVLOOKUPなどの関数を使って、Excelをデータベースのように活用する場合、形式やケタ数が勝手に変えられてしまっては、困ります。 たとえば、札幌市中央区の郵便番号 06000** や、ハイフン無しの携帯電話番号(09012345678)など。

“アタマ・ゼロ”問題は、郵便番号や電話番号ばかりでなく、ビジネス現場で使われるコード(商品コード/社員コードetc.)でも、しばしば直面する困りごとのひとつです。 今回は、この”アタマ・ゼロ”問題への対処方法についてご説明します。

一番カンタンなのは、文字列化してしまうこと

”アタマ・ゼロ”問題への対応として、もっともシンプルなのは、数値データを文字列化してしまうことでしょう。これは、実際に日々活用している人も多いのでは?

セルへ数値を入力する前に、アタマにアポストロフィ(‘)を入れると、数値データはシート上では文字列とみなされます。その証拠に、数字なのに、セルでは左揃え(文字列の基本配置)となりますね。

これと同じことは、［セルの書式設定］を文字列に変更したり、TEXT関数を使ったりすることでも可能になります。

TEXT関数の構文は、
=TEXT(値,表示形式)
となり、第2引数(表示形式)で、”アタマ・ゼロ(0)”と、以後の必要桁数を”#”の数で指定すれば、希望どおりの文字列が得られます。

文字列化したくなければ、［ユーザー定義］

とはいえ、文字列では、加減乗除の計算に使うことはできませんよね。 コード類を計算に使いたい(数値のまま、”アタマ・ゼロ”を足したい)場合には、［セルの書式設定］を文字列に変更せず、［ユーザー定義］で規定すればOKです。

数値のふくまれるセルで右クリック、もしくは［書式］→［セルの書式設定…］を選択して、書式設定ダイアログボックスを表示させます。［表示形式］タブで、［分類:］を「文字列」にしてしまうのではなく、「ユーザー定義」で”アタマ・ゼロ”指定をすれば、数値としての特性をもったまま、”アタマ・ゼロ”が表示されるようになります。

以上、ここまでが一般的な対処方法です。

ところが、2023年6月頃、突如ネット界隈で、”アタマ・ゼロ”問題になやむ多くのビジネス・パーソンから、歓喜の叫び声が聞かれるようになりました。

なんと、今後［Excelのオプション］設定画面から、自動で”アタマ・ゼロ(0)”をのこす(表示する)設定ができるようになるかも!!!!! というのです。

自動で”アタマ・ゼロ”表示する機能を、Microsoftが準備中

「お～、Office365のExcelをアップデートしたら「先頭のゼロを削除しない」オプションが追加されてる！」( X ※Twitter 34.8万回表示! )

「​​「Excel」で数値先頭のゼロが勝手に消される仕様、ようやく改善へ？　Twitterで歓喜の声」(やじうまの杜 2023年6月5日公開 )

これは、Microsoft 365 Insider プログラム(導入予定の新機能を試せるパイロット・プログラム)の、2023年6月更新分(バージョン2306)に出現した機能とのことです。

残念ながら、2023年9月にダウンロードしたInsider プログラム(バージョン2309)では、この機能は完全に取り下げられてしまっており、実際に試すことはできませんでした。

※Microsoft公式サポートセンターへ直接問い合わせ、この機能について「実装時期未定」との回答を受領しました。

しかし、X(Twitter)で約35万回表示されていることからも、この機能に対しての関心の高さが想像できます。

6月公開のβ版によれば、Excelのオプション設定画面に［先頭のゼロを解除して数値に変換する(Z)］チェックボックスが出現し、このチェックをはずすだけで(文字列扱ながら)”アタマ・ゼロ”のままのレコードが取り扱えるようになるのです。

統計データやテキストのとりこみをひんぱんに行うビジネス・パーソンにとっては、ノドから手が出るほどほしい新機能ですね。1日も早く、Microsoft 365版や永続版Excelにもこの機能が正式実装されることを、楽しみに待ちましょう。 それまでは、レガシーな「文字列化」か、「表示形式のユーザー定義」にて。

まとめ

文字列にしてしまう以外にも、Excelおせっかい機能を回避する方法はありました!自動設定機能の実装も楽しみです!

業務が複雑化し、”Excelだけ”のように単一ソフトで完結できる作業が少なくなってきているため、ソフト/ツール間でのデータ互換性は重要な課題です。

互換性をたもつ上で問題となるのが、”アタマ・ゼロ”問題をはじめとするExcelのおせっかい機能。そこをうまく克服しようと苦労するビジネス・パーソンがいかに多いことか、ネット上の書き込みを調べると、よく分かります。

Microsoft社も新機能追加を試みているようですが、実装されるまでは、アポストロフィ(‘)だけでないさまざまな回避方法を駆使して、できるかぎり効率的に、ミスなく互換データを手に入れる必要があります。