「腕試し問題」おさらい編 

Excelの腕試し問題102 自動的に増減する名前範囲(OFFSET関数)

問題を解く前に、「セルの名前の活用の弱点を知る」を見て、この問題の意義を考えてみてください。

注意 セルの名前活用の弱点を知る
問題 セルの範囲の変動に対応する名前の設定
確認 データを追加して動作確認

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

注意:セルの名前活用の弱点を知る

Excelでは、セルの名前を計算式・入力規則などで使用すると、ワークブックのメンテナンスが極めて便利になります。
  Excel 参照範囲の変更

そんな便利なセルの名前ですが、そのまま使用するには物足りなさも存在します。
それは、商品マスターなどは追加されたり・削除されたりするのが一般的だということ
です。
(以下の図の様な、変更はしばしば発生します)
Excel 参照範囲の変更

 そんな時、その度に名前の範囲を変更するのは厄介な作業です。
また、範囲の名前の変更を忘れてしまうと、正しい計算結果を得ることができなくなってしまいます。
それを放置してしまうと、会社に損失を与えることにもなりかねません。
そんな、リスクから解消される便利な方法があるとするならば、画期的な方法だと思いませんか。
実は、そんな便利な方法があるのです。
ここでは、そんな便利な方法をマスターしていただきたいと考え問題として出題してみました。
是非マスターしてください。

トップに戻る

問題 セルの範囲の変動に対応する名前の設定

 問題の細部は、問題の詳細にしたがって正解を求めてください。
  Excel 参照範囲の変更

商品マスターに1件のデータ(「aaa7 サンドイッチ 150円」)が追加されると考えてください。
Excel マスターの追加

問題の詳細
セルの範囲に、名前がついています。
 ・I8~I13セル:「商品コード」
 ・I8~K13セル:「商品マスター」
商品名(E4~)・単価(F4~)のセルに、上図のとおり計算式が入力されています。
 ・商品名(E4セル~):「=IF($C4="","",VLOOKUP($C4,商品マスター,2,0))」
 ・単価(F4セル~):「=IF($C4="","",VLOOKUP($C4,商品マスター,3,0))」
データが追加・削除された場合でも、自動的に範囲が変動する様に設定してください。
 ・商品コード
 ・商品マスター 
 Excelのセルの名前の定義

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

トップに戻る

確認 データを追加して動作確認

商品マスターに追加(確認のための準備)

I14セル:「aaa7」と入力
J14セル:「サンドイッチ」と入力
K14セル:「150」 と入力

売上データに追加

A16セル:「2014/2/7」と入力
B16セル:「遠藤 秀幸」と入力
C16セル:入力規則(リスト)に「aaa7」 が追加されていることを確認する。
        リストに「aaa7」が追加されていない場合⇒「商品コード」の範囲が正しく設定がされていません。
        追加されている場合は、「aaa7」を選択し次に進んでください。
D16セル:「5」と入力
E16~G16のセルに
    正しく計算結果が表示されれば、「商品マスタ」の範囲は正しく設定されています。
    計算結果が正しくない場合は、「商品マスタ」の範囲は正しく設定されていません。

トップに戻る