業務Excel ツール
困りごとの解説

Excel の数式の列に混ざった手入力・式の食い違いの見つけ方

長く使われた業務Excelで、いちばん事故になりやすいのは「合計が合わない」「ここだけ値が古い」といった、数式の列に混ざった手入力です。月末に数字が合わなくて、担当者が数式を消して直接数字を打つ。その場は収まり、次の月からそのセルだけ更新されなくなります。作った人が辞めると、誰も気づけません。

典型的な3つの形

  1. 手入力の数字:数式の列の途中に、数字が直接入っている。
  2. 式の形が違う行:ほかの行は =B2*C2 なのに、1行だけ =B7*C7+500 のように、足し算が混ざる。
  3. 範囲のずれ:合計の SUM が、データを増やしたのに最後の行まで届いていない。

手入力の数字を探す

「ホーム」→「検索と選択」→「条件を選択してジャンプ」で、「定数」を選び、「数値」だけにチェックを残します。選ばれたセルが、数式ではなく直接入れた数字のセルです。数式の列を範囲選択してから実行すると、その列の中の手入力だけが選ばれます。逆に「数式」を選ぶと、数式のセルだけが選ばれます。色を付けて見比べると、混ざっているところが目に見えます。

数式の形が違う行を探す

  • 数式の表示:Ctrl+`(バッククォート)で、セルの値の代わりに数式が表示されます。列幅を広げて縦に眺めると、1行だけ違う式が見つかります。
  • R1C1 形式:「ファイル」→「オプション」→「数式」の「R1C1 参照形式を使用する」にすると、同じ構造の式は、行が違っても同じ文字列になります(=RC[-2]*RC[-1] など)。違う行だけが、文字として違って見えます。
  • エラーチェック:セルの左上に緑の三角が出ます。「数式が隣接する数式と異なっている」という趣旨の指摘は、まさにこの検出です。ただし、緑の三角を表示しない設定にしたブックでは見えません。

範囲のずれを探す

合計の式のセルを選び、F2 で編集状態にすると、参照している範囲が色の枠で表示されます。データの最終行まで枠が届いているかを見ます。表を「テーブル」に変換しておくと、行を足したときに範囲が自動で伸びるので、ずれの予防になります。

見つけにくい理由

手入力の数字は、見た目では数式の結果と区別がつきません。セルを1つずつ選んで数式バーを見るしかなく、数千行あると現実的ではありません。さらに、手入力した人は「その月だけの対応」のつもりでも、翌月以降も同じセルがそのまま残り、気づいたときには何か月も前の値が混ざっている、ということが起きます。合計や集計の結果が少しだけずれる程度だと、誰も疑いません。だからこそ、定期的に機械的な見直しをかける価値があります。

見つけたあとの考え方

  • 手入力の理由を、すぐに消さずに確かめます。「システムから取れない例外の値」という正当な理由がある場合もあります。
  • 直すときは、数式に戻すか、例外として色や注記で印を付けるかを決めます。
  • ブックを直す前に、直す前の値を控えておきます。

再発を防ぐ工夫

  • 入力してよいセルと、数式のセルを、色で分けます。数式のセルにはシートの保護をかけ、手で上書きできないようにします。
  • 例外として手で値を入れる場合は、セルにコメントを付けるか、別の列に「理由」を書く決まりにします。
  • 月に一度、「条件を選択してジャンプ」で定数のセルを数え、前月と比べます。

ほかの兆候もあわせて見る

手入力のほかにも、使っていない名前の定義、循環参照、隠しシート、保護された範囲など、「引き継ぎで困る兆候」はいくつもあります。ブックが1つなら人の目でも確かめられますが、数が増えると見落とします。

当サービスの「業務Excel 健康診断」は、数式の列に混ざった手入力と、同じ列で形が違う式を機械で探し、#REF!・循環参照・外部参照・隠しシート・マクロとあわせて危険度順に並べます。数式の本来の形が並んでいる範囲の内側だけを見るので、合計行のように「形が違って当然」のところは拾いません。

このツールで調べる

業務Excelの診断は、ブックを送るだけで始められます。無料の方は 1 回までお試しいただけます。

健康診断を使う マクロの仕様書を使う 依存マップを使う

ほかの解説

お送りいただいたファイルは、診断が終わった時点でサーバから消しています。保存も再利用もしません。
© 合同会社IKACHI