Excel使っていて何回か失敗したことのある「自動データ変換」の機能。
Office365の新しいオプション設定でこれが抑制できそうだったのでご報告します。個人的にはすごくいいオプションです。
新しいオプション設定
しばらく気づかなかったのですが、Excelのオプション設定に新たな項目が追加されたのを発見しました。
対象のオプション設定画面は、Excel画面の「ファイル」⇒「オプション」で開くExcelオプション画面の左ペインのリストから「データ」をクリックして開きます。
この画面の下部に新たなオプションが追加されていました。
これ、どれも良い感じのオプションです。（今までオフにしたかったオプションという意味で）
以下の解説は公式のExcel for Microsoft 365「自動データ変換を設定する」のページでも確認出来ます。
リンク：
なお、今回取り上げた設定項目はデフォルトで有効化されています。
先頭のゼロを削除して数値に変換する
画面上の説明：（抜粋）
数値データの先頭に 0 をテキストとして格納し、先頭の 0 を保持する場合ｈ、数値に変換して先頭のゼロを失うのではなく、このオプションを無効にします。
例）00123はテキストの00123のままで数値123には変換されません
Excelのセルに電話番号や数字コードといった先頭が「0」から始まる数値データを入力すると、"自動的に"先頭の0を省いた状態でセルに保存される、という機能です。
こういう数値を入力した際に、
入力を確定させると、先頭の「0」が自動削除される、と言う動き。
このオプションを無効にすることで、携帯電話の電話帳編集などでExcelを使用したときに、電話番号の先頭「0」が失われてしまうような失敗を防ぐことが出来そうです。
ロング数値の最初の15桁を保持し、科学的記数法で表示する
画面上の説明：（抜粋）
このオプションを無効にすると、16桁以上の数値データをテキストとして保存し、最初の15桁のみを保持して科学的記数法で表示可能な数値に変換する代わりに、全ての桁を保持します。
例）12345678901234567890 はテキストの 12345678901234567890 のままで12345678901234500000 という数値に変換されたり、1.234567E+19として科学的記数法で表示されたりすることはありません。
ロング数値、というのは桁数の多いデータを指しています。
例えば、セルの表示幅が足りないときや表示領域が足りても16桁を超える数字だった場合、「E＋桁数の数字」でセル内の数字が表示されます。
例として1TBをバイト数で表示した場合の画面です。例えばB2セルを広げると、
このようにEがない数字で表示されますが、A2セルは既に桁数が16桁を超えているので、セルの表示領域を広げていてもE+数字表示はかわりません。見づらい。
文字"E"を囲む数字を科学的記数法に基づく数値に変換する
画面上の説明：（抜粋）
科学的記数法で数値に変換するのではなく、文字"E"を囲む数値データをテキストとして格納するには、このオプションを無効にします。
例）123E5 はテキストでは 123E5 のままであり、科学的記数法で数値1.23E+07 に変換されません。
要するに、数字データ後に"E＋数字"を記載すると、アルファベット"E"じゃなくて"この後に続く0の数"をExcelに指示することになる機能です。
このように
セルに「111E3」と入力すると、
"E3"の部分は"0が三つ"と解釈され、セルの数字は"111000"という数字データになるので、数字＋アルファベットを混ぜたコードデータを取り扱う場合には困った自動変換になることがあります。
連続する文字と数字を日付に変換する
画面上の説明：（抜粋）
このオプションを無効にすると、連続した文字と数字を含む"日付のように見える"値が日付に変換されずテキストとして格納されます。
例）JAN1はテキストのJAN1のまま格納され、1月1日という日付には変換されません。
例外）「JAN 1」や「JAN-1」など、スペースやその他文字が含まれるデータは、引き続き日付として扱われる場合があります。
これは影響を受けたことはなかったのですが、試しにセルに入力してみました。
手入力で、
Oct11と入力してEnterキー押下すると、
自動的に「2023/10/11」と自動変換され、セルの書式も
自動的に「日付－月のアルファベット」という書式に変更されました。
追加オプション：.csvファイルまたは同様のファイルを読み込む際に自動データ変換があれば通知する
画面上の説明：（抜粋）
データ変換を行うダイアログボックスの表示を停止する場合は、このオプションを無効にします。既定では、ダイアログボックスが表示されます。ただし、上記の全てのオプションを向こうにした場合、ダイアログボックスは表示されません。
今回の記事のきっかけとなったオプションです。
自動的なデータ変換が発生するセルを含む.csvファイルを開いたときに、ダイアログボックスを表示して、データ変換を実行するよ、とユーザに確認を求めてきます。
対象の.csvを開いてみると分かりますが、いままで無言でデータの自動変換をしていたExcelは、更新済バージョンからはちゃんと変換する旨を教えてくれるようになりました。
メッセージ下の「.csvまたは類似のファイルでの既定の変換について通知しません。」をチェックして画面を完了させると（おそらく）次からは確認画面が表示されなくなります。ただ、前述のオプション画面から有効/無効は切り替えることができそうです。
やってみた設定
設定はこのように変更してみました。あくまで自分自身が個人的に使いやすい設定ということです。
無効にした設定：
- 先頭のゼロを削除して数値に変換する
- ロング数値の最初の15桁を保持し、科学的記数法で表示する
- 文字"E"を囲む数字を科学的記数法に基づく数値に変換する
有効（デフォルトのまま）にした設定
- 連続する文字と数字を日付に変換する
- 追加オプション：.csvファイルまたは同様のファイルを読み込む際に自動データ変換があれば通知する
先頭の「0」が勝手に取り外されるのはちょっと不便していたので助かります。あと、数字の桁数によってE+桁数となっていたのも数字だけで表現されていたほうが使いやすいためチェックボックスを外して無効としました。
ただ、変換されることがダイアログボックスで注意されるのならそれはそれでいいので、チェックを残しておくことにしましたが、よく読むと、上記のチェックが無効化されているのであまり意味はなさそうです。
これでCSVファイルの電話帳を開いても、
勝手に0が削除されたり、E+桁数で表示が変わったりすることなく、Excelでデータ表示することが出来るようになりました。
ちょっとした話ですが、対象となるデータを日常的に取り扱っている場合には効果絶大な設定となる可能性も感じる、新たなExcel設定に思えました。
