セルの書式と予期しない値の変更

結論

セルの書式は事前に「文字列」にしておくほうが安全。

背景

キーボードからタイプした文字列に比べて見た目が変わるだけなのか、値まで変わるのかがよくわからない。

セルに入力した後で書式を変更した場合にも、見た目が変わるだけなのか、値まで変わるのかがよくわからない。

データの型とセルの書式

Excelにおいてセルに格納されるデータの型とセルの書式が明確に区別されていないこと

データの型を直接指定できず、書式の設定によりデータの型を間接的に示すことしかできないこと

「標準」の書式がVisual Basicにおけるバリアント型のようなデータの型を示すこと

実験

データはキーボードからタイプするかメモ帳から単純なテキストをペーストすることにより入力する

データの入力の前に書式を指定し、データを入力したらどう解釈されるか確認する

データの入力後に書式を変更し、データがどう変化するか確認する

セルの外観では無く内部でどのような値が保持されているかはセルを選択した上で数式バーにより確認する

入力するデータの文字列

メモ帳にて以下の11種類の文字列を用意する。

code:input.txt

1.2345678901234567890

12345678901234567890

0123

'0123

0123456789

012345678901234567890

TRUE

true

1/2/3

1:2:3

1/2/3 1:2:3

メモ帳からのコピー・アンド・ペースト

メモ帳からクリップボード経由でのコピー・アンド・ペーストであれば純粋なテキストなので書式に関する情報の混入を防ぐことができる。Excelにペーストしたときの「貼り付けのオプション」でも書式に関するオプションは表示されない。

https://gyazo.com/9744f62e7fc56650e39134fc7c40e76f

事前に設定する書式

セルの書式を事前に文字列、数値、標準の３種類に設定する。

A列～C列の奇数行は文字列、D列～F列の奇数行は数値、G列～I列の奇数行は標準に設定する。

偶数行（薄緑色）のセルの書式は文字列にしておく。

メモ帳からのコピー・アンド・ペースト後

書式が文字列のセルの色を青で、書式が数値のセルの色をピンクで、書式が標準のセルの色をしろで示す。

G19:I19はペーストにより自動的に書式が「日付」に設定されたため、橙色にした。

G21:I21とG23:I23はペーストにより自動的に書式が「ユーザー定義」に設定されたため、黄色にした。

https://gyazo.com/3be99fd7de3822ee2cfba2d32d70d0fb

A列はメモ帳に用意した文字列がそのまま入力されている。書式が「数値」であったり「標準」であったりすると、A列と異なる表示のされ方となっていることが分かる。

書式の変更

データのペースト後に書式を変更した。

D列, H列は「文字列」に、B列, G列は「数値」に、C列, F列, I列は「標準」に変更した。

I列は事前に「標準」であったので書式の変更は必要無いはずだったが、自動的に「日付」と「ユーザー定義」に変更されたセルがあったため「標準」に戻した。

https://gyazo.com/c049fbf4dad704a8ad7cbed846f60f80

数式バーによる保持されている値の確認

セルの外観とセルに保持されている値の違いを見るため、データの入力した各セルを選択して数式バーに表示される文字列をその下の緑色のセルに記入した。緑色のセルの書式はすべて「文字列」に設定した。見やすさのためにすべてのセルを左揃えにした。

https://gyazo.com/ac715ffedeb17c73385ce6e53d33fba2

D列, E列, F列の偶数行と、G列、H列、I列の偶数行の値が同じであることから、書式が「数値」であるか「標準」であるかは内部的に保持されている値には影響しないことが分かる。「日付」や「ユーザー定義」という書式はただ見た目を変える効果しか持たず、内部的には数値として保持されていることが分かる。

入力したデータとの違い

A列はキーボードから入力したままの文字列である。これとすべての行で一致しているのはB列とC列だけである。つまり事前にセルの書式を「文字列」にしておかなければデータは改変されてしまうことが分かる。

https://gyazo.com/69864b24769cd6ea6e89e9a1db598e45

実験2021-11-11T07:02:53.000Z

メモ帳に20文字の0と20文字の9を用意し、ここからExcelにコピペする。

B列とC列の各行の表示形式をあらかじめ「標準」「文字列」「数値」「分数」「指数」「@」「0」「#」にしておく。

ペーストした文字列がそのまま維持されるのは「文字列」にしておいたセルだけ。

https://gyazo.com/b703c64a53f1d2049235ffd32e4f23d3

ペースト後に表示形式を「文字列」にしてもどうにもならない。

https://gyazo.com/96d3c5090377bf54d3f1a810cc3a1774

ペースト後に表示形式を「#」にしてもどうにもならない。

https://gyazo.com/d54567578ecb6efa5c43fb28645dbe58

https://gyazo.com/f9ecf4c2aaae39ab5d89f57971cba3e2

表示形式を「文字列」に変更。

なぜ指数形式表示のままなのか。

指数表示の結果を「文字列」として扱うということらしい。

https://gyazo.com/7b189b41e0cbb65daf5ec690d9fe184b