条件ごとに違う範囲を参照してみよう
次に、各支店の単価を求めます。単価は夏季と冬季で金額が異なるため、セルE2で「夏季」を選んだ場合は「セルC16からC20」、「冬季」を選んだ場合は「セルD16からD20」の値を返すようにします。
条件により違う範囲を参照したい場合は、『INDIRECT関数』と検索関数を組み合わせて使用します。
まずは、セルE2に「夏季」「冬季」を指定するためのリストを設定しましょう。
▼ 図4-1 入力規則を設定する
- セルE2を選択する
- [データ]タブをクリックし、[データツール]グループの[データの入力規則]を選択する
- [入力規則]ダイアログボックスの[設定]タブを表示し、[入力値の種類]から[リスト]を選択する
- [元の値]ボックスに「夏季,冬季」と入力する
※ リストに表示したい項目は、「,（半角カンマ）」で区切って入力します。 - [OK]ボタンをクリックする
続いて、参照先に名前を定義しておきます。セルC16からC20を「夏季」、セルD16からD20を「冬季」にします。
▼ 図4-2 セル範囲に名前を付ける
- セルC16からC20を選択する
- [名前]ボックス内をクリックし、「夏季」と入力して[Enter]キーで確定する
- [名前]ボックスに「夏季」と表示される
- 同様に、セルD16からD20に「冬季」と名前を付ける
では、LOOKUP関数とINDIRECT関数を使って、各支店の単価を求めてみましょう。
▼ 図4-3 INDIRECT関数を選択する
- セルD5を選択する。[関数の挿入]ボタンをクリックし、[関数の分類]から[検索/行列]を選び[LOOKUP]を選択して[LOOKUP]関数のダイアログボックスを表示する
※ [引数の選択]ダイアログボックスが表示されたら、「検査値、検査範囲、対応範囲」を選択してOKをクリックする - LOOKUP関数の[検査値]にはセルB5を選択する
- 検査範囲にはセルB16からB20を選択し、[F4]キーを押して絶対参照にする
- 対応範囲にはINDIRECT関数を指定するため、対応範囲をクリックしてカーソルを立て数式バー左側のボックスから[INDIRECT]を選択する
※ 一覧に表示されない場合は、[その他の関数]を選択して[関数の挿入]ダイアログボックスから指定します。
▼ 図4-4 INDIRECT関数を作成
INDIRECT関数は、指定されたセルに入力されている文字を介し、間接的なセルの指定を行う関数です。
- [参照文字列]にはセルE2をクリックし、[F4]キーを押して絶対参照にする
- [OK]ボタンをクリックする
▼ 図4-5 単価を求める数式が完成
数式が完成したら、セルE2に「夏季」「冬季」のいずれかを選択し、単価が切り替わることを確認します。
（セルE2が空欄だと、数式はエラー「#REF」を返します）
残りの支店分も数式をコピーし、金額には「=基本料金+使用量*単価」の数式を作成しましょう。
すべて完成したら、セルE2を「夏季」「冬季」に切り替えると、単価と金額も切り替わることが確認できます。
