メインコンテンツへスキップ
見出し画像

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

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

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

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

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


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

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

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

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

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

    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回以上出てきたら重複なので違反、という判定になります。全角の「45」は文字列扱いなので、`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の出題パターン攻略と実技対策をまとめた有料記事も書いています。今日の記事で「入力規則、ちゃんと押さえておきたいな」と思った方は、覗いてみてください。もちろん、まずは明日の朝の赤丸ワンクリックからで十分です。

     
     
    元SIer→フリーランスエンジニア歴10年。Excel業務改善が専門。毎月20時間かかっていた集計作業を関数とVBAで2時間に削減した経験をベースに、事務職・経理・総務の方が今日から使えるExcelテクニックを書いています。無料記事で試して、良ければ有料記事もぜひ。

    あなたへのおすすめ