使用量に応じて基本料金を表示する
VLOOKUP関数は、指定した検索値を基に一覧からデータを参照する関数です。
今回は、セルA15以下に用意されている一覧から基本料金を表示します。
ただし、今回のように検索値と一覧の値が完全一致でない場合には、一覧を作成する際に注意が必要です。
VLOOKUP関数の4つ目の引数「検索の型」を省略、もしくは「TRUE」とした場合、検索値は一覧から一番近い数値を探します。
ただし、検索値が「29」で一覧には「20」と「30」があった場合には「20」を一致する値として計算します。
なぜなら、一番近い数値でなおかつその値を超えない数値というルールがあるからです。
今回のように、使用量が「0〜20」「20〜49」「50〜99」・・・となっている場合は、範囲の上限値ではなく、下限値「0」「20」「50」・・・を一覧に入力します。また数値は昇順(小さい順から並べる)にしておきます(今回は、セルの書式設定を使って「0〜」「20〜」「50〜」・・・と表示しています)。
では、使用量に応じた基本料金を『VLOOKUP関数』を使って求めていきましょう。
- セルC5を選択する
- [関数の挿入]ボタンをクリックし、[関数の挿入]ダイアログボックスを表示する
- [関数の挿入]ダイアログボックスの[関数の分類]から[検索/行列]を選択する
- [関数名]から[VLOOKUP]を選択する
- [OK]ボタンをクリックし、[関数の引数]ダイアログボックスを表示する
- [検索値]にはセルB5をクリックする
- [範囲]にはセルA16からB20をドラッグし、[F4]キーを押して絶対参照にする
※ 数式をコピーした際に範囲がずれないよう、絶対参照にします。 - [列番号]には「2」と入力する
- [検索方法]には「TRUE」と入力する
※ 省略、もしくは「1」と入力してもOKです。Excel2003以前は[検索方法]が[検索の型]になっています。 - [OK]ボタンをクリックする
上野支店のガス使用量は「85」なので、一覧の「50〜」を参照して基本料金「\1500」を返します。
このように検索先の値が「XX〜XXの範囲」と、完全一致になっていない場合も簡単に求められます。
数式をコピーし、残りの支店の基本料金も求めておきましょう。
知っておくと便利!
《HLOOKUP関数》
VLOOKUP関数と同じ形で使用できる関数に、『HLOOKUP関数』があります。
HLOOKUP関数は、参照先の一覧が横方向に並んでいるときに使用します。