Excelの入力規則だけでCSVは検査できる？外部から届くデータを同じ基準で確認する方法

1. 自分で管理する入力フォームと、外部から届くCSV

「商品コードを必須にする」「在庫数は0以上にする」といった条件は、入力時にも受領後にも使えます。違うのはルールの良し悪しではなく、誰が入力の仕組みを管理でき、いつ確認するかです。

• 自社のExcelフォーム：入力する人へ選択肢や警告を示し、入力時のミスを減らす。
• 他社・店舗から受け取る完成済みCSV：提出元の仕組みに依存せず、受領側が決めた条件で取込前に確認する。

CSVに保存されるのはテキストや値であり、Excelブックの入力規則の設定は保持されません。Excel側のテンプレートに入力規則を作っただけで、外部CSVの全行が自動的に検査済みになるわけではありません。一方、Excelでも、受領後のデータを検査できます。既存値に規則を適用する方法や、式・フィルターで調べる方法があります。

2. 自社の入力用Excelなら、まず入力規則を使う

たとえば在庫数の列へ0以上の整数だけを入力してほしい場合、デスクトップ版Excelでは次のように設定します。メニュー名や利用できる機能は版・環境によって異なります。

1. 在庫数を入力するセル範囲を選びます。
2. ［データ］→［データの入力規則］を開きます。
3. ［設定］で［整数］、条件を［次の値以上］、最小値を0にします。空欄の扱いも業務条件に合わせて確認します。
4. ［エラーメッセージ］で警告の内容・スタイルを設定します。商品コードを選ばせるなら［リスト］も候補です。
5. 正常な値と負数を直接入力して、想定どおりのメッセージになるか試します。

コピー・貼り付けやオートフィルなどでは、検証メッセージが出ず無効な値が入る場合があります。入力規則を「どんな経路の入力も必ず防ぐ仕組み」とは考えず、取り込み後の確認も組み合わせます。

入力済みの値も「無効なデータを円で囲む」で確認できる

デスクトップ版Excelでは、対象に適切な入力規則を設定したうえで、［データの入力規則］のメニューから［無効なデータを円で囲む］を選ぶと、規則に合わない既存セルを確認できます。Excel for the webではこの機能が表示されない場合があります。受領CSVも検査用ブックへ取り込み、必要な規則を用意して調べる選択肢があります。元CSVは残し、規則や式を保存したい検査用ブックは別の.xlsxとして管理します。

3. 受領済みCSVをExcelで調べる：店舗＋商品の例

まず原本CSVを別途保管します。［データ］→［テキスト/CSVから］で取り込み、必要に応じて［データの変換］で店舗コード・商品コードを文字列型にしてから読み込みます。先頭ゼロがあるコードを数値へ変換すると、識別に必要なゼロが失われる場合があるためです。表示書式だけを後から変えても、失った元の桁数が正しいかは判断できません。自動の型変換ステップも確認します。

次は説明用の架空データです。A列が店舗コード、B列が商品コード、C列が在庫数、見出しは1行目、データは2〜6行目とします。「各店舗で各商品は1行」と決めている業務を想定します。

1. 空欄を探す：フィルターで商品コードの空白を表示します。店舗コードも必要なら同様に確認します。空白文字だけのセルは、空欄とは別に扱われることがあるため、入力表記の条件も決めます。
2. 店舗＋商品の重複を調べる：補助列D2に次の式を入れ、6行目までコピーします。2以上の行をフィルターで確認します。

=COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)

この例では2行目と4行目がそれぞれ2、3行目は1になります。異なる店舗でA001を扱うことは普通なので、商品コードだけの重複を異常としません。ロット別在庫ならロットもキーに含めるなど、一意にしたい単位は業務で決めます。実データでは数式の末尾行を全件に合わせ、空欄確認を先に行います。

数値範囲と商品マスターは、別の条件として見る

在庫数が数値かつ0以上でよい場合は、補助列に次の式を入れてFALSEの行を確認できます。整数限定なら、その条件も追加して確認します。

=IF(ISNUMBER(C2),C2>=0,FALSE)

商品マスターを同じ検査用ブックの「商品マスター」シートへ読み込み、有効コードをA2:A100に置いた例では、次の式で存在を確認できます。空欄はFALSEにし、照合範囲は実際のマスター全件に合わせます。

=IF(B2="",FALSE,COUNTIF(商品マスター!$A$2:$A$100,B2)>0)

この例のコードは英数字のみを想定しています。COUNTIF系の比較では大文字・小文字を区別せず、*や?はワイルドカードとして扱われるため、区別や特殊文字が必要な業務では式を見直します。マスターの更新日、コードの文字列型、余分な空白も確認してください。Excelの式や検査用ブックを維持できるなら、少数の受領ファイルはこの方法で十分な場合があります。

4. Power Queryで毎月のCSV検査を組む場合

同じ構造のCSVを繰り返し受け取るなら、ExcelのPower Queryに取り込み・変換・確認の手順を保存する方法もあります。自社でクエリを作成・保守できる場合に向きます。

1. ［テキスト/CSVから］取り込み、文字コード・区切り・列見出しを確認します。
2. コード列は文字列、数量列は必要な数値型など、業務に合う型を指定します。変換エラーを無視せず確認します。
3. ［表示］の列の品質・列の分布・列のプロファイルを有効にし、空欄やエラー、値の分布を確認します。
4. 複合キーでのグループ化やマスターとの結合など、必要な条件を自分で設計します。結合時も双方の型やキーをそろえます。
5. 翌月分へ切り替えたら、対象ファイル・全件の範囲・列の変更・エラーを確認します。

列プロファイルは既定では先頭1,000行に基づきます。全行を対象にする場合は、エディター左下の［上位1000行に基づく列プロファイリング］から［データセット全体に基づく列プロファイリング］へ切り替えます。先頭だけの確認を全件検査と取り違えないことが大切です。

列の品質でエラーがないことと、すべての商品が業務マスターに存在することは別の確認です。必要な条件を組み、データ構造が変わった際に見直せるなら、専用ツールを買わずに運用できます。

5. 保存した検査ルールを、毎月の1ファイルずつに使う

Small Work Labの「表データ品質チェッカー v0.1.10」は、Excel・CSVに保存済みのルールを適用するWindows用ローカルアプリです。検査条件を式として組み直す代わりに、設定したルールセットを次のファイルへ再利用する選択肢です。

1. ［新しいルールを作成］から見本のExcel・CSVを選びます。Excelではシートとデータ開始行も指定します。
2. プレビューの列見出しを選び、［選択した列にルールを追加］から、商品コードの空欄禁止、在庫数の数値・最小値、店舗＋商品の複数列の重複禁止など、必要な条件を設定します。
3. 別マスターへの存在確認では、参照ファイルと列を確認します。マスターの値集合をルール内に固定するのではなく、検査時にそのファイルを毎回読み取ります。
4. ルールセットを保存し、翌月以降は［このルールで検査］から1ファイルずつ選びます。図の9月・10月・11月は、個別に検査するファイルの例です。
5. 結果の［OK判定］［違反］［検査不能］を分けて読みます。OK判定はそのルールで問題を検出しなかった判定であり、業務全体の正しさを保証する表示ではありません。

「違反0」でも検査不能があれば、全件正常を意味しません。対象列・マスター列は保存時の列位置＋見出しで確認します。見出しが変わった場合、その列を使うルールは検査不能となり、別の列を推測して追跡しません。列順変更・列挿入時には、ルールを編集して対象列を見直してください。

対象ファイルと参照マスターは読み取り専用です。元データの自動修正・上書き、店舗CSVの自動結合、結果のExcelファイルへの書き出し、監査完了の保証をするものではありません。検出した内容を元に、提出元への確認や修正の判断は利用者が行います。