見出し画像

Excel 入力規則「無効データのマーク」で不正値を一括検出。月90分の目視チェックを5分にした3手順

DX推進委員会

SIer時代、経理部門の取引先マスタ管理を手伝っていたことがあります。1,200行のExcelを毎月チェックする当番で、支払サイトの欄に「45にち」と書かれていないか、締め日が「31」なのに2月の取引先はいないか、そういうのを目でひとつずつ確認していました。所要時間、毎月90分。

で、ある月。見事に見逃しました。「０」が全角で入っていたんです。請求データの取り込みが止まって、経理から内線。あのときの「あいびさん、ちょっといい？」の声、今でも覚えています。

先に結論を言います。この90分は、入力規則の「無効データのマーク」を知っていれば5分で終わっていました。既存データの不正値を、Excelが赤丸で囲んで教えてくれる機能です。今日はその話だけをします。

「入力規則は後から設定しても意味がない」と思っていませんか？


入力規則って、「これから入力するデータ」を弾く機能だと思われがちなんですよね。セルに変な値を入れようとするとエラーが出る、あれです。

だから既にデータが入っているシートを前にすると、みんな諦める。「もう1,200行入っちゃってるし、規則を入れても手遅れでしょ」と。私も10年前はそう思っていました。

ところが、これが半分間違いなんです。

入力規則には「無効データのマーク」という機能があって、設定した条件に違反している既存データを一括で赤丸表示できます。つまり後付けでも遅くない。むしろ後付けしてチェックに使うのが、この機能の一番おいしい使い方だったりします。

もうひとつ、知らないと痛い目を見る仕様があります。コピペは入力規則を素通りします。他のシートからセルごと貼り付けると、規則ごと上書きされて不正値が普通に入る。私が見逃した全角の「０」も、犯人は別ファイルからの貼り付けでした。入り口で防げない以上、既存データを後からチェックする手段が要るわけです。

1,000行を超えるデータの中の全角数字を、目視で全部拾える自信、ありますか？　私はもうないです。人間の目は3画面目あたりで確実に流し見になります。

3手順で不正値が赤丸になって浮かび上がる


ここから具体的な手順です。例として、取引先マスタのC列に「部署名」、D列に「支払サイト（日数）」、E列に「締め日」が入っているとします。

手順1：チェック条件を入力規則として「後付け」する


まず、チェックしたい列に条件を設定します。データが入っていても構いません。

  • チェック対象の列（例：C2:C1200）を選択する

  • - 「データ」タブ →「データの入力規則」をクリック

  • - 「設定」タブで条件を選ぶ

条件の例はこんな感じです。

  • 部署名（C列）：「リスト」を選び、元の値に `=部署マスタ!$A$2:$A$20` を指定

  • - 支払サイト（D列）：「整数」を選び、最小値0・最大値120

  • - 締め日（E列）：「整数」で1〜31

リストや範囲で表現できない条件は、「ユーザー設定」でカスタム数式が使えます。実務でよく使うのはこの2つです。

  • 取引先コードの重複禁止：`=COUNTIF($B$2:$B$1200,B2)=1`

  • - 数値であること（全角数字や文字列を弾く）：`=ISNUMBER(D2)`

`COUNTIF` の式は「B列全体の中で、この値の出現回数が1回であること」という意味です。2回以上出てきたら重複なので違反、という判定になります。全角の「４５」は文字列扱いなので、`ISNUMBER` が FALSE を返して引っかかります。私の90分を奪っていた犯人は、この1行の式で捕まえられたわけです。

手順2：「無効データのマーク」を押す。これだけ


ここが本題なのに、操作は10秒で終わります。

  • 「データ」タブの「データの入力規則」ボタンの右にある▼をクリック

  • - 「無効データのマーク」を選ぶ

これだけで、手順1の条件に違反しているセル全部に赤い楕円が付きます。

初めて押したときのことは忘れられません。1,200行のシートに赤丸が23個、一斉に浮かび上がったんです。ちょっと鳥肌が立ちました。隣の席の経理さんには「え、何の魔法？」と言われました。90分かけて目でやっていた仕事が、ワンクリック。

ちなみにこの赤丸、余談ですが印刷には出ませんし、ファイルを閉じると消えます。セルの値を直した瞬間にその赤丸だけ消えるので、消し込み作業のチェックリストとしてそのまま使えます。画面上の付箋みたいなものだと思ってください。

手順3：修正して、最後は関数で「残り0件」を確認する


赤丸のセルを上から順に直したら、仕上げです。

  • 「データの入力規則」の▼ →「入力規則マークのクリア」で表示をリセット

  • - もう一度「無効データのマーク」を押して、赤丸が出なければ完了

ただ、私は念のため関数でも件数を数えます。目視の敗北を経験した人間は疑り深いんです。


=SUMPRODUCT(--(COUNTIF(部署マスタ!$A$2:$A$20,C2:C1200)=0))
```

この式は「C列のうち、部署マスタに存在しない値の個数」を返します。`COUNTIF` が0（＝マスタにない）のセルを数えている、という構造です。これが0なら、リスト外の部署名は1件も残っていません。数値列なら `=SUMPRODUCT(--NOT(ISNUMBER(D2:D1200)))` で「数値でないセルの個数」が出ます。

赤丸で直して、関数で0を確認する。二段構えにしてから、見逃しは一度も起きていません。

Before/Afterを数字で並べてみる


導入前後で何が変わったか、正直に書きます。

  • 作業時間：月90分 → 月5分（約94%削減。5分の内訳は、マーク実行10秒＋修正と確認）

  • - 見逃し件数：月2〜3件 → 導入後6ヶ月で0件

  • - 精神状態：チェック当番の日が憂鬱 → 赤丸を消すだけの軽作業

時間もそうなんですが、一番大きいのは「見逃したかもしれない」という不安が消えたことでした。目視チェックって、終わった後も確信が持てないんですよね。90分かけたのに自信がない。あの感覚から解放されたのが、体感では一番の収穫です。

この3手順、あなたの職場ならどこで使えるか


考えてみてください。「他人が入力したExcelを、自分が目でチェックしている」場面、ありますよね。そこが全部候補です。

  • 勤怠データのCSVを貼り付けた後、出勤時刻に「9;00」みたいな入力ミスがないか確認する

  • - 顧客マスタの都道府県欄が、正式名称のリストから外れていないか洗い出す

  • - 棚卸表の数量欄に、マイナスや全角数字が紛れていないか検品する

共通点は「入力者が複数いて、貼り付けで値が入ってくる」こと。入り口で防げないデータほど、この後付けチェックが刺さります。

まとめ：明日の朝、1列だけ試してほしい


まずは明日の朝、あなたが一番ミスに悩まされている1列だけを選び、入力規則を設定して「無効データのマーク」を押してみてください。所要時間は5分もかかりません。赤丸がひとつでも浮かび上がったら、それがあなたの職場の90分を5分に変える入り口です。

長々と書きましたが、やることは3つだけです。

  1. 一番ミスが多い列を1つ選んで、入力規則を後付けする（リスト or 整数 or カスタム数式）

  2. 2. 「データの入力規則」の▼から「無効データのマーク」を押す

  3. 3. 赤丸を直して、COUNTIFで残り0件を確認する

最初から全列にやろうとしないでください。挫折します。私のおすすめは「先月ミスが見つかった、まさにその列」から。1列なら設定は3分で終わりますし、赤丸が1個でも見つかれば、その瞬間に元が取れています。

90分の目視チェックは、努力ではなく仕組みで消す。これが今日一番言いたかったことです。

Excelスキルを資格で証明しませんか？


実はこの入力規則とデータ検証、MOS Excel試験の出題範囲にがっつり含まれています。今日の3手順がそのまま試験対策になっている、と言ってもいいくらいです。

「Excelできます」と口で言うのと、資格として履歴書に書けるのとでは、転職や昇進の場面での通り方が違います。私もフリーランスになる前、単価交渉の材料として資格にはずいぶん助けられました。実務で覚えたテクニックを資格という形に変換しておくのは、地味に効く投資だと思っています。

MOS Excelの出題パターン攻略と実技対策をまとめた有料記事も書いています。今日の記事で「入力規則、ちゃんと押さえておきたいな」と思った方は、覗いてみてください。もちろん、まずは明日の朝の赤丸ワンクリックからで十分です。

いいなと思ったら応援しよう！