「腕試し問題」関数応用編 

Excelの腕試し問題305 売上日に応じた消費税率を取得する方法


問題  売上日付から、消費税率を取得するための計算式を入力してください。

消費税率は、時期によって変更されます。
税率が固定された計算式の場合は、税率の更新日の前後では正しく消費税を計算できない場合も発生します。
その状況を、回避する方法として以下の問題を出題します。
税率のセル(E2~E18セル)に、売上日付に応じた消費税率を取得する計算式を入力してください。

        Excel VLOOKUP関数で消費税率取得

サンプルファイルのダウンロード

消費税率マスター:G2~H6(消費税開始日/消費税率の基準となる領域)

G2~H6のセルには、消費税の情報が入力されています。
 ・更新日(G2~G6):消費税が開始された日付が入力されています。
    (日付は、昇順に並び替えられていなければならない)
  ・税率(H2~H6):税率が少数値が入力されています。(表示形式は「%」表示)
E2~E18のセル:「消費税」という名前を付けてください。

売上データ:B2~E18

入力済みのデータ
 ・商品の売上日付は、消費税の開始前後の日付を入力しています。
  ・商品名・売上個数・単価のセルには、適当な数値が入力されています。
税率(E2~E18):消費税率を取得できる様に計算式を入力してください。
 ・売上日付に応じた消費税率を取得できる様に計算式を入力してください。
 ・売上日付が空白の場合は、「0」を取得する様に計算式を工夫してください。
    (エラー表示対策のため)
 ・オプションの設定(詳細設定)で、ゼロ値を表示しない様に設定してください。

解法のコツ

Hint1:検査値の一致する場合の該当値を返す関数はVLOOKUP関数です。
 この問題の場合、検索方法の設定がポイントとなります。
Excel VLOOKUP関数

Hint2:売上日付が空白の場合は、「0」を表示させるのはIF関数です。
 エラー対策として、売上金額が「””(空白)」の場合、真の戻り値に、「””(空白)」が多様されます。
 この問題で、「0(ゼロ)」としたのは、この戻り値は計算式で再利用される可能性が高いことからです。
 「””(空白)」のまま計算式で再利用した場合、その再利用先で再びエラーが発生することが考えられます。
 それを防止するため、敢えて「0(ゼロ)」と返すように出題しています。
Excel IF関数で分岐