意外に知られていない素人臭いExcel講座です。
今回は、表を作って、SUM関数で合計値を出してみたものの、
何やら計算結果が思ったのと違うことになっているのをなんとかする方法です。
症状の説明：巨人の年俸合計が0円
症状を説明します。
ある日、私は広島東洋カープと読売巨人軍の年俸差を調べるべく、
ネットから個人別年俸情報をExcelに打ち込んでいました。
全ての選手の情報を入力し終わり、
さてさて、合計でいくらなのかなと、
SUM関数で年俸列の合計をとってみました。
するとどうでしょう、広島の年俸合計が、
巨人の合計を上回っているではありませんか。
そんなはずありませんよね。
あの貧乏球団が成金巨人より多く払ってるなんて有り得ませんよね。
ていうか、そもそも巨人の年俸合計0になってますし。
あと広島も、なんかぱっと見少ないですよね。
どうやら、巨人の方も広島の方も両方何かがおかしいようです。
というわけで、原因究明です。
原因は大きくわけて二つあります。
数式の自動計算がOFFになっちゃってる！
1つはExcelの演算設定で、数式の自動計算がOFFになっているケースです。
Excelは基本的に全ての数式を”ことある事に”再計算します。
“ことあるごと”の代表的なのが、並べ替えを実行した時や、
フィルタの選択項目を変更した時ですね。
この自動計算、普段は別に気にすることもあまりないのですが、
表のデータ量が巨大になると、再計算するだけで、
どえらい時間がかかってしまうのです。
そういう時は、自動的に再計算されないように、
再計算を自動から手動に切り替えておくケースがあります。
なので、意図せず使っているシートが”手動計算”になっていると、
変なところで計算が止まっていることにより、
計算結果がおかしくなってしまいます。
というわけで、このケースの場合は、
再計算を自動に戻すか、戻したくないのなら、
手動で再計算をかければOKです。
治りましたか？治らないなら次の原因を疑いましょう。
数値だと思ったら文字列でしたー
もう一つの理由、こちらが本命ですが、
セルの書式設定がいたずらしているパターンです。
SUM関数は数字であっても文字列設定されていると合計してくれません。
↑の症例画像、実は巨人の全員と広島の上位陣の年俸が
文字列になっています。
セルの表示設定自体は”標準”として見えていますが、
Excel的には文字列として扱われています。
通常、文字列であれば、セル内で左端に文字列が寄っていたり、
セルの左上に三角のマークが出ていたりすることが多いので、
「あ、文字列なっとるわ、いかんいかん。」
と、気づくことが多いのですが、
オプションの数式のところで、
数式のバックグラウンドチェックをOFFにしていたり、
セルの文字位置を右寄せにしていたりすると、
一見普通の”数値”に見えてしまうので、
異常の発見が遅れてしまいます。
というわけで、原因がはっきりしたので、
問題となっている文字列を数値に戻してやりましょう。
一つ一つ手で直していくと日が暮れてしまいますので、
“区切り位置”コマンドを使います。
まず、数値に直したいセルをすべて選択して、
念のため、セルの書式設定ですべて数値に設定してから、
データリボン→データツール→区切り位置
です。
そして、何も考えずに完了ボタン押下です。
そうすると、選択範囲が一括で数値に変換され、
数式の再計算が自動になっていれば、
SUM関数の結果も自動で更新されます。
というわけで、これが正しく集計した結果です。
巨人53億円、広島30億円となりました。
そらそーですわな。
おわり。
コメント