こんにちは。ヒトツメです。
今日は、多くの人を惑わす、Excelの表示形式とその変更方法についてのお話です。
以前からたまに触れることはありましたが、改めてまとめていきたいと思います。
そもそも表示形式とは
Excelの表示形式とは、ホームタブの数値のところに出ている、次のようなプルダウンで選べる選択肢のことを指すのが一般的です。
これを選択することによって、例えば日付を、「2022年7月30日」と表記するか、「2022/7/30」と表記するか、といったことを選択することが出来ます。
表示形式という言葉から想像するに、「なるほど、表示をするときの形式を選んでいるわけか」となると思います。前回の日付の話で、「1」と「1900年1月1日」はExcel上は同じ意味、という話をしましたが、その話を結び付けて考えると分かりやすいです。
奇妙な「文字列」
ただ、少しややこしいことに、この表示形式には、単なる表示方法の話という側面以外に、文字列かそうじゃないかという側面も持っているため、少し話がややこしいということに注意すべきです。
というのも、「1」という数値が入っているA1セルと、「1」という数値を入れた後で、「表示形式」を文字列に変更したセルは、セルの中身が同じか確認すると、「TRUE」と返ってきますが、「表示形式」を文字列に変更したうえで、「1」としたセルについては、セルの中身が同じになりません。
これは、Excelにおいては、数値の「1」と、文字の「’1’」は異なるということに起因するものです。且つ、表示形式が文字列になっているセルにベタ打ちでいれられた値は、「文字列の値」として処理されることが原因で起きる事象です。
このような理解から、文字列となっているセルに、例えば「2022年7月30日」といれても、同じように標準形式になっているセルに「2022年7月30日」と入れても、同じ内容にはなりません。それを証拠に、標準形式になっているセルに「2022年7月30日」と入れると、関数バーには「2022/7/30」と表示されますが、文字列形式のセルに入れた場合、関数バーには「2022年7月30日」と表示されます。
「’2022年7月30日’」という文字列が記載されているに過ぎない、という処理がされているわけです。
文字列を数値にする
さて、そんな文字列形式で入っている値を数値に変えるためには、いくつか方法がありますが、これを関数でする場合は、VALUE関数を使うのが一般的です。
文字列で「’1’」と入っているセルを参照して、VALUE関数を使うと、次のように、数値と同じになります。
逆は、TEXT関数というものを使います。数値となっているセルを参照しつつ、TEXT関数の第二引数に、「”@”」といれると、数値も文字列になります。
ただ、TEXT関数は、第二引数の形式で表示した値を文字列にするという関数であるという点に注意しなければいけません。たまにカンマ入りの数値にするために、TEXT関数を使って、「=TEXT(A1,”#,###)」としているのを見かけますが、これだと、カンマ区切りで表記した値の文字列、という意味になってしまうため、例えばA1に「1000」と入っていたとすると、この関数の結果は、「1,000」ではなく、「’1,000’」という意味になってしまいます。
したがって、このような結果を例えばSUM関数で合計の中に入れようとしても、結果に1,000が足されることはありません。この場合は素直にExcelの表示形式を指定するようにする必要があります。
さいごに
表示形式は、上記の通り、単なる表示方法の話という側面以外に、文字列かそうじゃないかという側面も持っているため、少しややこしいです。関数の参照にするとき、思わぬ誤りを生んでしまいます。ただ、しっかり覚えておいて身に付けると、思うような操作ができるようになり、意外と便利です。
実際、値を処理するうえで、「0」で始まる管理番号を付けるときなどは、文字列での処理は必須です。Excelを使っていると必ずぶち当たる壁ですが、少しずつ慣れながら覚えていっていただけると良いと思います。
コメント