Excel文字列を数値変換する～見た目は数字なのに計算できない！とお困りの方へ

セルには明らかに数字が入っているのに、何故かSum関数やら引き算やら、数値計算ができない！──というシチュエーションの原因は、大抵、セルの書式が文字列になっていることによります。この場合、単に、セルの書式設定を「文字列」から「標準」などに変更するだけでは、計算結果を表示してくれません。何故なら文字列の書式 設定で入力されてしまった数字は、単にセルの書式設定を「標準(或いは数値など)」に戻すだけでは、見た目は数字でもやっぱりExcelには「文字列」として認識されてしまうからです。

よってここでは、「文字列」認識されてしまっているセルの値を「数値」として認識するよう、細工する必要がでてきます。以下が私がよく使っている文字列→数値変換の手順です。

1. VALUE関数を利用して、あいているスペースに、セルの書式が文字列になってしまっているセルを引数にして、だだーーっとドラッッグしてコピー(B1のセルが文字列だとしたら、[=VALUE(B1)]とかです)。
2. 貼り付け予定先のエリアの書式を[標準]にしておく([文字列]ではなく！[文字列]のセルに貼り付けても、再び文字列になってしまいます)。
3. VALUE関数で表示させたセルをコピー→形式を選択して貼り付け→[値]オプションをチェック！→任意のセル(貼り付け先のセルの書式は[標準]だとか、ちゃんとしたものを選んでください)を選択して、貼り付け！

Excelでのcsvファイルの取り扱い方でやったように、0落ちをおそれるばかりに、シートを全選択して、全てのセルの書式を文字列にしたときなどに、「見た目は数字なのに、Sum関数さえ使えない！」という自体によく陥るわけですね。それはセルの書式が文字列だから計算できないわけで(見た目は数字だけど、あくまでも「文字列」として扱っているわけですなー。Excelはそのセルを数字だとは思っていないので、計算などはできないわけです…、とデータ型の意識の希薄な人々の場合にはちと説明しづらいのですが、そーゆーもんだと思ってください)、計算したい列などに対しては、文字列から数値変換する必要があるんですね。そういう場面で、このテの変換ワザをよく使っていマス。

Excel上で文字列から数字変換するコワザは他にも色々あって、Value関数を使うかわりに、空いているセルに「=文字列のセル*1」という乗算を行なって、乗算を入れたセルの書式を「標準」選択。[形式を選択して貼り付け]→[値]オプションという流れに入るやり方も、王道の一つとしてあります。

さらにExcel2002あたりのバージョンからは、文字列のセルに数字のみを入れていると、左上隅に緑色のエラーインジケータが表示されているセルが現れ、そのインジゲータを選ぶと「！」が表示。「！」をクリックすると、[数値に変換する]というメニューが表示されるので、これを選ぶと、数字に変換される親切設計になっています。エラーインジゲータが一定範囲で連続して出ている列なんかだと、まとめて列のかたまり毎選択して、いっぺんに数値に変換は可能です。ですが、まー、一定の範囲をまとめて数値変換したいときには、イマヒトツ一発の動作で決まりづらいので、個人的には、関数やコピー業に慣れている人は、Value関数などの計算式を入れる方法が、途中、標準のセルが混ざっていようが気にせず確実に数値変換できるので、ベンリと感じるのではないかなー、という気もします。その辺は、その人の好みによりますが…。

他には、空いているセルの書式を[標準]にしてから「1」を入力。そのセルをコピー。文字列の書式になっている数値型にしたいセルの範囲を選択したのち[形式を選択して貼り付け]→[乗算]を選択→[OK]といったやり方。

[データ]→[区切り位置]→[カンマやタブの…]を選択→[次へ]→区切り文字に[タブ]を選んだまま→[次へ]→[列のデータ形式]で[標準/G]を選んだまま[完了]というやり方もあります。しかしこのやり方では、セルの内容的には数値になっているものの(例えば0つき数字であれば、0が落ちている=数値変換はなされているということ)、セルの書式は文字列のままになってしまうので、最終的には数値にしたかったセルの書式を[標準]に設定しなおした方が、キレイに決まると思います。

ちなみにプログラムでExcelにデータを吐き出すとき、シートを全選択してセルの書式は一旦文字列にしてからデータを吐き出す…、という流れで書く人は意外と多いので、納品されたExcel のセルの書式設定が[文字列]になっていて、その後、「数字として加工ができん！」というような場面は、よくありマス。まー、Excelをプログラムで吐き出す立場からすれば、日付だろうと、0つき数字だろうと、書式を文字列にしてしまえば「見た目」は確実に確保されるわけですから、指定された「見た目どおり」という意味では手軽な手段なんですよね。

というわけで「この納品されてきた Excel表にちょっと手を加えたいのに、どーして計算できないの？！」と大騒ぎしている人々の尻拭い/相談されることは、Excelラブを公言している立場上、好むと好まざるとにかかわらず意外と多いわけです。あんまり変換する表の数が多いときは、責任もって確実に全ての表を、単に0つき数字のコードとして使っているのか数値計算用する必要のあるフィールドなのかをイチイチ見極めて、変換するのは私にとっても苦痛なので(そもそも自分の担当案件ではございませんので…)、発注先に再納品を要求するよう薦めたり、数値変換のやり方を教えて、「あとは自力で頑張ってね」と申し渡したりするわけですが(二度とこんなザルな発注をしてくれるなよ…、てなところです)。

そんなこんなで、Excel納品を依頼する場合には、一応、この辺のセルの書式についても、発注先には予め注文をつけていた方が無難 だと思います。その一方で、このテのExcelを納品をしてくるプログラマ側には、素人が発注するとき、この辺のことは当然気づかないで発注しているんだから、プロならその辺まで察してフォローしてやれよ、とも思うわけですが…。発注元の「仕様」に入っていなかった云々というより、そもそもExcel納品を依頼されているときに、Excelとして死んでいる表を納品してどーする、と思うわけですね…。