会社別の数値区間から対応する値を求める
会社ごとに設定された数値の範囲を使って、適用する料率を探します。会社名で結合したあと、下限・上限との差を計算して候補を絞ります。
「0以上10未満なら料率2」のように範囲で料率が決まる場合、金額をそのまま結合しても見つけられません。まず会社名で料率表に結合し、金額と下限・上限の差を計算して、範囲内の候補だけを残します。
境界の10はどちらの区間か
架空のMeasurementsにBandsのRateを付けます。区間はLower以上、Upper未満です。Alphaの10は0～10には入らず、10～20に入ります。Betaの15にAlphaの料率を適用してはいけません。
Measurements
| Record | Company | Value |
|---|---|---|
| M1 | Alpha | 5 |
| M2 | Alpha | 10 |
| M3 | Beta | 15 |
| M4 | Alpha | 25 |
Bands
| Company | Lower | Upper | Rate |
|---|---|---|---|
| Alpha | 0 | 10 | 2 |
| Alpha | 10 | 20 | 3 |
| Beta | 0 | 20 | 4 |
会社で結び、2つの差を計算する
- 両表を読み込み、Company同士を「一致する行のみ」で結びます。絞り込み前の候補はAlphaの3行×2区間とBetaの1行×1区間で計7行です。
- Bands側のCompanyは目アイコンで非表示にできます。Value・Lower・Upperが数値として扱われていることを確認し、計算のため表示したままにします。
- 「結果を整理」→「計算」で「引き算 · A − B」を選び、A=Value、B=Lower、名前=LowerGapにします。次の計算はA=Upper、B=Value、名前=UpperGapです。
- 2つの計算の下に「絞り込み」を2つ追加します。LowerGapは0「以上」、UpperGapは0「より大きい」と設定します。両条件で下限を含み上限を含まない区間になります。
- 適用後、「結果のプレビュー」でM1・M2・M3の3行と料率を確認し、Excelに保存します。確認用のLowerGap・UpperGapも結果に残ります。
範囲外のM4は結果に含まれない
M2は下限と同じ10なので、LowerGap=0となる2番目の区間に残ります。最初の区間ではUpperGap=0となり、条件に合いません。M4の25はAlphaのどちらの範囲にも入らず、結果から除かれます。この手順では、該当する料率がない行は残りません。
| Record | Company | Value | Lower | Upper | Rate | LowerGap | UpperGap |
|---|---|---|---|---|---|---|---|
| M1 | Alpha | 5 | 0 | 10 | 2 | 5 | 5 |
| M2 | Alpha | 10 | 10 | 20 | 3 | 0 | 10 |
| M3 | Beta | 15 | 0 | 20 | 4 | 15 | 5 |
区間の重なりと候補件数の注意
大きな表で実行する前に、会社ごとの測定行数×区間数で候補件数を見積もってください。例は7行ですが、表が大きいと急増します。Lower < Upperと同じ会社内での区間の重なりも確認します。区間が重なると1つの測定値に複数の料率が残ります。両端を含めるならUpperGapも0以上にしますが、共有する境界が2行に一致する場合があります。空欄や数値でない入力は数値条件を通らないため、元表で別途確認します。
自分のファイルで始めましょう
JOIN SHEETを使ってみる ↗Excel・CSVの作業はPCでご利用ください。
新しいタブで開くため、作業中のデータは置き換わりません。
サンプルの検証日:
出典
Index match a numerical value that appears between two columns (Reddit r/excel)
原文は会社と売上額を使い、2つの数値で示された区間に対応する利益を求める方法を尋ねています。 上の表と手順は、この質問を参考に別途作成した架空の例です。質問者の実データや製品の利用体験ではありません。
