Excelで数量の段階別料金表から適用単価を求める手順と境界の確認

1．下限の表か上限の表かを先にそろえる

ここでは、公式ヘルプの適用先に含まれるExcel for Microsoft 365のWindows版を対象にします。

料金表を読み直す際は、「この数量から同じ単価になる」という下限と、「この数量までは同じ単価になる」という上限を区別します。検索に使う列には、境界の数値だけを置き、どちらの意味かを列名で示すとよいです。境界と単価は同じ行で対応させます。

一致モードは、下限を検索する方式では「-1」、上限を検索する方式では「1」を指定し、完全一致がなければそれぞれ小さい側、大きい側の値を選びます。一致モードを省略したときの既定値は完全一致の「0」なので、段階の途中の数量を扱う式では近似一致を明示します。

料金の取り決めで境界を含む側が不明なときは、数式を作る前に確認を分けます。ここで扱うのは、決まっている料金表から適用単価を取り出す手順です。単価そのものや契約上の料金の決定は扱いません。

2．下限と単価を並べて検索する式を作る

下の表の配置、数量、単価、期待値は当社が設定した説明用の値です。公式ヘルプの料金例ではありません。ここでは数量を正の整数とし、最後の区分には上限を設けない前提にします。

式の引数は、検索値、検索範囲、戻り配列、見つからない場合、一致モード、検索モードの順です。

数量の入力場所と単価の出力場所を料金表から離して置き、下の配置で式を組み立てます。セルの位置と表示文字は当社の設定です。

数量の入力：E2
単価の出力：F2
F2に入力する式：
=XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"下限未満",-1)

「見つからない場合」には、有効な一致が見つからないときに表示する文字を指定できます。この例の表示文字は、入力漏れや入力の種類まで判定するためのものではなく、検索の不一致を知らせるために使います。

料金表の参照をコピー先でも固定するには、列と行の前に「$」を付けた絶対参照にします。上の式では数量の参照だけを固定せず、料金表側を固定する組み立てです。複数の入力行へ展開する前に、検索する数量と料金表の参照が意図した場所を指すかを読み直すとよいです。

3．一致の向きと検索順序を混同しない

下限の表から上限の表へ変更する際は、列名だけを変更せず、境界値の意味と選ぶ向きを合わせます。上限方式では「ここまで含む」という上限を数値で管理し、対応する単価を同じ行に置く整理です。末尾を「上限なし」とした下限方式の表を、そのまま上限方式へ流用しないようにします。

検索モードを省略すると先頭から検索するため、同じ境界値を重複させずに管理するとよいです。検索モードの「2」は昇順、「-2」は降順に並べた検索範囲を前提とするバイナリ検索で、並べ替えられていない場合は無効な結果が返されます。

この例では検索モードを省略しています。照合しやすいよう境界を小さい順に並べることと、バイナリ検索に必要な並び順を満たすことは、目的を分けて考えるとよいです。数量に合う境界を選ぶ設定と、表を探す順序の設定を混同しないことが中心になります。

境界を修正するときは、その行の単価との対応も一緒に見直します。同じ境界に異なる単価が残っていたり、途中の段階が抜けていたりすると、検索方法だけを調整しても、料金表としての内容は確定しません。

4．単価が出た後に境界と入力内容を照合する

文字列として保存された数値は予期しない結果を招く可能性があるため、数量と境界値が数値として保存されているかも見直します。Windows版で文字列の数値の警告が表示された場合は、変換するセルのエラーインジケーターを選び、メニューから［数値に変換］を選ぶ手順です。

ここからは当社の整理です。照合は、表の「照合する数量」を入力し、表示された単価を「期待値」と見比べる流れにします。特に、区分が変わる直前と変わった直後を分けて確かめるとよいです。単価の増減だけで判断せず、どの境界の行を採用したかまで料金表と突き合わせます。

入力の妥当性も別に確認します。空欄、負の数量、端数を業務でどう扱うかを先に決め、扱いが決まっていない入力を料金計算へ進めない整理です。式が値を返したことだけで、その数量を受け付けてよいとは判断しません。

最後の区分を無期限に適用してよいかも料金表で確かめます。対象数量に上限がある料金表なら、その上限を超えた入力を別途止める仕組みが必要です。上限方式でも、対象数量の下限を別に確認します。片側の境界を検索する式と、対象範囲の両端を確認する作業は分けて管理するとよいです。

5．確認表

公開・共有の前に、この表で確かめます。

6．自社の事務の手順まで形にしたいとき

ここまでの確認を、自社の事務の手順書や、AIの結果を確かめる検査まで形にしたい場合は、当社のように、手順と検査の設計まで行う相談先が選択肢になります。オンラインは全国から対応しています。

自社の事務のどこから確認を入れるかを相談したい場合は、初回無料相談をご利用ください。法律・税務・労務の判断や、効果の約束は行いません。