Power Query 実務テンプレ | クレンジング（前処理）系：数値範囲チェック

Excel VBA Power Query M Formula Language
スポンサーリンク

ゴールのイメージをそろえる

今回のテーマは「Power Query で “ありえない数値”を数値範囲で機械的にチェックする実務テンプレ」です。

やりたいことは、ざっくり言うとこうです。
「数量は 1〜999 のはずなのに、0 やマイナス、10000 が紛れ込んでいないか？」
「点数は 0〜100 点のはずなのに、-5 や 120 が入っていないか？」
「割引率は 0〜1（0〜100％）のはずなのに、1.5 や -0.2 がないか？」

つまり、「業務上ありえない数値を、範囲ルールで検出する」のが目的です。
“数値としては正しいけど、業務的にはおかしい”ものを炙り出す前処理です。

例題で「数値範囲チェック」を具体的にイメージする

シンプルな例：テストの点数（0〜100 点のはず）

次のようなテーブルを想像してください。

生徒ID氏名点数
S001田中80
S002鈴木95
S003佐藤120
S004山田-5
S005高橋100

ルールはこうだとします。

点数は 0〜100 の範囲であるべき

このとき、「数値としては number だけど、範囲的におかしい」行は次の2つです。

120（上限オーバー）
-5（下限未満）

これを Power Query で確実に検出するテンプレを作っていきます。

前提：数値範囲チェックの前に「数値化」が済んでいること

まずは列を number 型にしておく

数値範囲チェックは、「列が number 型である」ことが前提です。
もし元データが文字列（”80″、”120″、” -5 ” など）なら、先に数値変換テンプレ（Number.From＋try）で number にしておきます。

例えば、点数列を安全に数値化するなら、こんなイメージです。

Table.AddColumn(
    Source,
    "点数_num",
    each
        let
            v = [点数],
            t =
                if v = null then
                    null
                else
                    Text.Trim(Text.From(v)),
            parsed =
                try Number.From(t) otherwise null
        in
            parsed,
    type nullable number
)
Power Query

ここで「数値に変換できなかった行」は、そもそも“数値エラー”として別枠で扱うべきなので、
今回の「数値範囲チェック」は「数値として読めた行」に対して行う、というイメージです。

ステップ1：範囲外の数値だけを抽出する（エラー行一覧）

0〜100 の範囲から外れている点数を検出する

点数列がすでに number 型だと仮定して、
「0〜100 の範囲から外れている行だけ」を抽出するテンプレはこう書けます。

let
    Source = ・・・前のステップ・・・,

    ErrorRows =
        Table.SelectRows(
            Source,
            each
                let
                    v = [点数],
                    isNull = v = null,
                    isOutOfRange =
                        not isNull
                        and (v < 0 or v > 100)
                in
                    isOutOfRange
        )
in
    ErrorRows
Power Query

ここでのポイントを言葉で整理します。

v = [点数] で数値を取り出す。
v = null の行は「範囲チェック対象外」として一旦分けて考える（後でどう扱うか決められるように）。
null でない行について、v < 0 or v > 100 なら「範囲外」と判定する。
isOutOfRange が true の行だけを抽出する。

この ErrorRows には、120 と -5 の行だけが残ります。
つまり、「数値としては正しいが、業務ルールの範囲から外れている行の一覧」です。

ステップ2：逆に「範囲内の数値だけ」を本体として残す

本番集計に使うテーブルから“範囲外”を除外する

今度は逆に、「0〜100 の範囲内の点数だけを残したい」場合です。
さっきのロジックを反転させて、「範囲内 or null」の行だけを残すようにします。

let
    Source = ・・・前のステップ・・・,

    ValidRows =
        Table.SelectRows(
            Source,
            each
                let
                    v = [点数],
                    isNull = v = null,
                    isInRange =
                        not isNull
                        and (v >= 0 and v <= 100)
                in
                    isInRange
        )
in
    ValidRows
Power Query

ここでは、「null はそもそも点数が入っていないので、本体からも除外する」という設計にしています。
もし「null は別扱いにしたい」「null も残したい」などのルールがあれば、
isInRange の条件を少し変えれば OK です。

ステップ3：フラグ列として「範囲NG」を持たせるパターン

後からフィルタしやすいように logical 列を追加する

毎回 Table.SelectRows で分けるのではなく、
「この行の点数は範囲NGかどうか」をフラグ列として持たせておくと、後工程で扱いやすくなります。

let
    Source = ・・・前のステップ・・・,

    AddedFlag =
        Table.AddColumn(
            Source,
            "点数_範囲NG",
            each
                let
                    v = [点数],
                    isNull = v = null,
                    isOutOfRange =
                        not isNull
                        and (v < 0 or v > 100)
                in
                    isOutOfRange,
            type logical
        )
in
    AddedFlag
Power Query

この「点数_範囲NG」列が true の行だけをフィルタすれば「範囲エラー行一覧」、
false の行だけをフィルタすれば「範囲OK行一覧」になります。

フラグ列を持っておくと、
Excel 側のフィルタやピボット、別クエリでの条件などに使い回せるので、実務ではかなり便利です。

応用1：下限だけ／上限だけのチェック

「数量は 1 以上ならOK（上限なし）」のようなルール

数値範囲チェックは、必ずしも「下限と上限の両方」があるとは限りません。

数量は 1 以上ならOK（0 やマイナスはNG）
残高は 0 以上ならOK（マイナス残高はNG）
割引率は 0〜1 の間（両側チェック）

例えば、「数量は 1 以上であるべき」というルールなら、こう書けます。

Table.AddColumn(
    Source,
    "数量_範囲NG",
    each
        let
            v = [数量],
            isNull = v = null,
            isOutOfRange =
                not isNull
                and (v < 1)
        in
            isOutOfRange,
    type logical
)
Power Query

上限がない場合は、単純に v < 下限 だけをチェックすれば OK です。
逆に「上限だけチェックしたい」なら、v > 上限 だけを見ればいい、という形です。

応用2：割合・率のチェック（0〜1 or 0〜100）

割引率・達成率・進捗率などの“率”系

率系の列は、表現が 2 パターンあります。

0〜1（0.2 = 20％）
0〜100（20 = 20％）

どちらで管理しているかを最初に決めておくことが重要です。

例えば、「割引率は 0〜1 で管理している」なら、こうです。

Table.AddColumn(
    Source,
    "割引率_範囲NG",
    each
        let
            v = [割引率],
            isNull = v = null,
            isOutOfRange =
                not isNull
                and (v < 0 or v > 1)
        in
            isOutOfRange,
    type logical
)
Power Query

「0〜100 で管理している」なら、単純に 0 と 100 に置き換えれば OK です。
ここを曖昧にすると、「全部範囲外扱いになった」「全部OKになった」という事故が起きやすいので、
“率のスケール”は必ず明示しておきましょう。

応用3：複数列をまとめて数値範囲チェックする

数量・単価・金額を一気にチェックしたい

実務では、「範囲ルールを持つ数値列」が複数あることが多いです。

数量：1〜999
単価：0〜100000
金額：0〜10000000

こういう場合は、列ごとにフラグを持たせてもいいし、
「どれか一つでも範囲NGなら、その行は“数値範囲エラー行”」というまとめ方もできます。

例えば、「数量と単価のどちらかが範囲外なら NG」とするフラグ列はこう書けます。

Table.AddColumn(
    Source,
    "数値範囲NG",
    each
        let
            qty = [数量],
            unit = [単価],

            qtyOut =
                qty <> null
                and (qty < 1 or qty > 999),

            unitOut =
                unit <> null
                and (unit < 0 or unit > 100000)
        in
            qtyOut or unitOut,
    type logical
)
Power Query

この「数値範囲NG」が true の行だけを抽出すれば、
「どこかの数値が業務ルールから外れている行の一覧」が手に入ります。

重要ポイントの深掘り

「数値として正しい」と「業務的に正しい」は別物

数値範囲チェックの一番大事な感覚はここです。

Power Query 的には、-999999 も 9999999999 も「ただの number」で、エラーではありません。
でも、業務的には「数量が -5 はありえない」「点数が 120 はおかしい」という話になります。

つまり、

数値変換（Number.From）でエラーにならない
→ “数値としては”問題ない
数値範囲チェックでルールから外れる
→ “業務的には”おかしい

という二段構えで考えるのが大事です。
前者は「数値エラー検出」、後者は「数値範囲チェック」として、役割を分けておくと頭が整理されます。

NULL を範囲チェックに含めるかどうかを最初に決める

もう一つの重要ポイントは、「NULL をどう扱うか」です。

NULL も“範囲NG”として扱いたいのか
NULL は“未入力”として別のチェック（必須項目チェック）に任せるのか

例えば、「点数が NULL なのは別問題（未入力）なので、範囲チェックでは対象外」とするなら、
さっきのように isNull を分けておく設計がきれいです。

逆に、「NULL も含めて“点数としておかしい”とみなしたい」なら、
isOutOfRange の中に isNull を含めてしまえば OK です。

isOutOfRange =
    isNull
    or (v < 0 or v > 100)
Power Query

ここは、数値範囲チェックと必須項目チェックの“役割分担”の話でもあります。

実務テンプレとしてのまとめ

「数値範囲チェック」の本質は、次の二つです。

列を number 型にしておき、v < 下限 or v > 上限 で“業務ルールから外れた数値”を判定する。
その結果を、エラー行抽出・正常行抽出・フラグ列（○○_範囲NG）として使い分ける。

そして、実務で効かせるためには、

数値変換（数値エラー検出）と範囲チェックを分けて考える
NULL を範囲NGに含めるかどうかを最初に決める
率系（0〜1 or 0〜100）のスケールを明示しておく

という設計の部分がとても大事になります。

ここまであなたが積み上げてきた前処理テンプレ（数値変換、必須項目チェック、桁数チェックなど）と、
今回の「数値範囲チェック」を組み合わせると、
「数値が原因で集計結果が壊れる」パターンをかなりの精度で潰せるようになります。

スポンサーリンク
タイトルとURLをコピーしました