入力したのに合計が変わらない｜数式が数字に置き換わったセルの見つけ方と戻し方

「入力したのに合計が変わっていません」と翌日に言われた

人から受け取った入力用のブックで、こんなことがありました。色の付いたセルに数字を入れるだけの、よくある作りのブックです。言われたとおりに入力して返したところ、翌日、受け取った相手から「合計が変わっていないところがあります」と連絡が来ました。

調べてみると、本来は数式が入っているはずのセルのうち、数か所が数字に置き換わっていました。見た目は数式の結果と同じ数字なので、入力した側からはまったく区別がつきません。入力欄をいくら直しても、そのセルは前の数字のまま動かないので、合計も変わらなかったのです。

直すときは、ブックを作った人に確認しながら、計算の順序を1つずつ追って数式を入れ直しました。かかった時間はおよそ1時間です。それ以来、自分がブックを作るときは、入力しないセルにロックをかけるようにしています。

この記事では、数式が数字に置き換わる原因、ブックの中からそういうセルを見つける方法、元に戻す手順、そして同じことを起こさない作り方を扱います。

そのセル、本当に数式ですか

最初に確かめるのはとても簡単です。合計が変わらないセルをクリックして、数式バーを見てください。

• 数式バーに =SUM(C2:C10) のような式が出る → 数式が入っています
• 数式バーに 128000 のような数字だけが出る → 数式ではなく、数字が直接入っています

数式が入っているはずのセルに数字だけが入っていたら、それが今回の原因です。セル上の見た目は同じでも、数式バーは嘘をつきません。

ただ、怪しいセルを1つずつクリックしていくのは、表が大きいと現実的ではありません。後半で、シート全体からまとめて見つける方法を紹介します。

数式が数字に変わる4つの経路

数式は、勝手に数字に変わることはありません。どこかで誰かの操作が入っています。よくある経路は次の4つです。

1. 数式の上に数字を打ち込んだ

いちばん多いのがこれです。入力欄の隣にある計算欄に、うっかり数字を入れてしまうケースです。入力用のセルと計算用のセルが見た目で区別しにくいブックほど起きやすくなります。

2. 値だけ貼り付けた

別の表から結果をコピーして［値の貼り付け］をすると、貼り付け先の数式は消えて数字だけが残ります。表を整えるつもりで範囲全体に値貼り付けをすると、数式の列までまとめて数字になります。

3. 合計を手で入力した

計算が合わないときに、「とりあえず正しい数字を入れておこう」と合計欄に直接数字を書くケースです。その場では数字が合いますが、元のデータが変わっても合計は動かなくなります。

4. 別のブックからシートや表をコピーした

数式ごとコピーしたつもりでも、貼り付け方によっては値だけが移ることがあります。また、元のブックを参照していた数式を値に変えて渡す、という運用をしている職場もあります。

筆者のケースでは、どの経路だったかまでは特定できませんでした。誰が触ったかを追うよりも、「数式でないセルを見つけて戻す」ほうを先にやるのが現実的です。

シート全体から「数式でない数字」を見つける

ここからは、怪しいセルをまとめて見つける方法です。表の大きさや目的に合わせて選んでください。

方法1：数式を表示に切り替える

Ctrl+Shift+@ を押すと、シート上の表示が計算結果から数式そのものに切り替わります。もう一度押すと元に戻ります。

数式の列の中に、ぽつんと数字だけのセルがあれば、それが置き換わったセルです。縦に同じ形の数式が並んでいる表なら、数秒で見つかります。

方法2：「定数」だけを選ぶ

1. 数式が入っているはずの列や範囲を選ぶ
2. Ctrl+G →［セル選択］をクリック
3. ［定数］を選び、［数値］だけにチェックを残して［OK］

選んだ範囲の中で、数式ではなく数字が直接入っているセルだけが選ばれます。「見つかりませんでした」と出れば、その範囲は全部数式です。

選ばれた状態のまま塗りつぶしの色を付けておくと、あとで数式を入れ直すときに、どのセルを直したのかを見失いません。直し終わったら色を消せば、作業の抜けもひと目で確かめられます。広い範囲で一度に調べたいときは、シート全体を選んでから同じ操作をしても構いません。その場合は、入力欄に入っている数字も一緒に選ばれるので、入力欄の色と見比べながら判断します。

方法3：条件付き書式で色を付ける

ブックをこれからも使うなら、目印を残しておくと便利です。

1. 数式が入っているべき範囲（たとえば D2:D200）を選ぶ
2. ［ホーム］→［条件付き書式］→［新しいルール］→［数式を使用して、書式設定するセルを決定］
3. 数式に =NOT(ISFORMULA(D2)) と入れ、目立つ塗りつぶしを設定する

ISFORMULA関数は、セルに数式が入っていればTRUEを返します。その否定を条件にしているので、数式でないセルにだけ色が付きます。誰かが上書きした瞬間に色が変わるので、翌日に指摘されるより先に自分で気づけます。

戻すときは、計算の順番をさかのぼる

置き換わったセルが見つかったら、数式を入れ直します。ここで焦って、目についたセルから直していくと、かえって混乱します。

計算の流れが「入力欄 → 小計 → 合計 → 別シートの集計」のように何段にもなっているブックでは、上流の数式が壊れていると、下流をいくら直しても正しい数字になりません。筆者が1時間かかったのも、この流れを1段ずつ確かめながら直したからです。

おすすめの順番は次のとおりです。

1. 入力欄に近いほうから直す：入力欄を直接使っている数式が先、それを使っている数式が後です。
2. 隣の行や列の数式をコピーする：同じ形の数式が並んでいるなら、正常なセルをコピーして貼れば戻ります。
3. 形が違うセルは、参照元のトレースで確かめる：正常なセルを選び、［数式］タブ→［参照元のトレース］を押すと、どのセルを使って計算しているかが矢印で出ます。同じ考え方で、置き換わったセルに入るべき式を組み立てます。
4. わからない数式は作った人に聞く：筆者もここは作成者に確認しました。意図がわからないまま推測で書くと、別の誤りを埋め込むことになります。
5. 最後に合計を検算する：入力欄の値を1つだけ変えてみて、合計がきちんと動くかを見ます。

手元に前回の版や、配られたときの元のブックが残っていれば、そこから同じセルの数式を写すのがいちばん確実です。

合計が合わない原因がほかにもありそうなときは、合計のずれ方から原因を絞る方法で、差額の出方から当たりを付けられます。

どこで計算が切れているかを1か所の入力で突き止める

置き換わったセルが1つや2つなら探しやすいのですが、数か所に散らばっていると、どこから直すべきか迷います。そんなときは、入力欄を1つだけ動かして、どこまで数字が伝わるかを見る方法が役に立ちます。

1. 入力欄の1つに、目立つ数字（たとえば元の値に1を足したもの）を入れる
2. その入力欄を使っているはずの小計、合計、集計のセルを順に見ていく
3. 数字が変わったセルと、変わらなかったセルの境目を探す

数字が変わらなくなった最初のセルが、計算が切れている場所です。そこの数式バーを見れば、数字が直接入っているはずです。確かめ終わったら、入力欄は元の値に戻しておきます。

数式が長くて、どの部分の計算がおかしいのか読み取れないときは、［数式］タブの［数式の検証］も使えます。［検証］ボタンを押すたびに、数式の中身が1段ずつ計算されて表示されるので、どの参照で想定と違う値を拾っているかがわかります。

値に変換して渡す運用との付き合い方

職場によっては、数式が入ったままのブックを外に出さず、「値に変換してから渡す」と決めているところもあります。数式の中身を見せたくない、ほかのブックへの参照を切りたい、といった理由です。

この運用自体は悪いものではありません。問題になるのは、値に変換したブックが、いつの間にか「次に使う元のブック」になってしまうことです。値にしたものは「渡す用」として別の名前で保存し、数式の入った元のブックを手元に残す。この線引きをしておくだけで、翌月に数式のないブックへ入力してしまう事故は防げます。

似ているけれど別の原因：計算方法が「手動」になっている

「入力しても合計が変わらない」という症状には、もう1つ有名な原因があります。ブックの計算方法が「手動」になっているケースです。

見分け方は簡単で、数式バーを見て数式がちゃんと入っているのに、値が更新されないならこちらを疑います。［数式］タブの［計算方法の設定］が「手動」になっていたら「自動」に戻してください。F9 キーを押して値が変わるなら、ほぼ間違いありません。

計算方法の設定は、その時点で開いているブックすべてに影響します。手動にしたブックを開いたまま別のブックを開くと、そちらも手動で計算されることがあるので、心当たりがなくても一度確かめる価値があります。

数式が数字に置き換わっている場合と、計算が止まっているだけの場合では、直し方がまったく違います。最初に数式バーを確かめるのは、この2つを取り違えないためでもあります。

入力しないセルにロックをかける

同じことを起こさないためには、そもそも数式のセルに入力できないようにしてしまうのが確実です。筆者がいま自分のブックでやっているのがこの方法です。

Excelでは、最初はすべてのセルに「ロック」が付いています。シートを保護すると、ロックが付いたセルは編集できなくなります。そこで「入力欄だけロックを外してから、シートを保護する」という順番で設定します。

1. 入力してもらうセル（色付きのセル）をすべて選ぶ
2. Ctrl+1 で［セルの書式設定］を開き、［保護］タブの［ロック］のチェックを外す
3. ［校閲］→［シートの保護］を押す
4. 許可する操作で「ロックされていないセル範囲の選択」にチェックが入っていることを確かめ、必要ならパスワードを付けて［OK］

これで、色付きのセルには入力できても、数式のセルには入力できなくなります。数式のセルに数字を打とうとすると、変更できない旨のメッセージが出ます。

入力欄に入れられる値も絞っておく

ロックを外した入力欄には何でも入れられるので、数字を入れてほしい欄に「済」や「-」が入ることがあります。数式は残っていても、文字が入った入力欄は計算から外れるので、結局合計が合わなくなります。

入力欄を選んで［データ］→［データの入力規則］を開き、［入力値の種類］を「整数」や「小数点数」にしておくと、数字以外は入力できなくなります。［エラーメッセージ］タブに「数字だけを入力してください」のように書いておけば、入力した人がその場で気づけます。

ロックだけでは防げない場面

ロックは強力ですが、万能ではありません。次のような場面は残ります。

• 入力欄に値ではなく、ほかの数式の結果を貼られる：入力欄はロックを外しているので、何を貼られても止められません。入力欄には入力規則で「数値」だけを許可しておくと、文字や想定外の値を防げます。
• 保護を外して作業される：パスワードを付けていないと、誰でも保護を外せます。共有するブックにはパスワードを付け、管理する人を決めておきます。
• 新しく足したシートは守られない：シートの保護はシートごとの設定です。あとから追加したシートや、コピーして作ったシートには、あらためて保護をかける必要があります。

ロックに加えて、前の章で紹介した条件付き書式の目印を残しておくと、すり抜けたときにも気づけます。ブックを人に渡す前提で、どこまで守りを固めるかの考え方は、人に引き継げる表の作り方でも扱っています。

受け取ったブックを返す前の3つの確認

最後に、人からもらったブックに入力して返すときの確認をまとめておきます。筆者が翌日に指摘を受けたときも、これをやっていれば自分で気づけたはずです。

1. 入力欄を1か所だけ変えて、合計が動くかを見る：動かなければ、どこかで数式が切れています。
2. Ctrl+Shift+@ で数式表示にして、計算欄をざっと眺める：数字だけのセルが混ざっていないかを見ます。
3. 計算方法が「自動」になっているかを見る：［数式］タブで一瞬で確かめられます。

数式が数字に置き換わるのは、合計が合わない原因の中でも、エラーが出ないぶん気づきにくいものです。合計が合わないとき、原因を1つずつ絞り込む流れをもう一歩踏み込んで確かめたい方は、合わない6原因を絞り込む診断フローも用意しています。