基本情報技術者 2015年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
あるPC販売店では、セット値引きを導入することにした。セット値引きでは、指定された3種類の商品を同時に購入する場合、各商品を単品で購入する場合に比べて購入金額を安くする。セット値引きとなる商品の組合せ（以下、セットという）と値引きを適用していく順序は、商品の仕入状況と販売状況に基づいて適宜見直しを行う。セット値引きの導入に合わせて、これまで手書きであった購入伝票の作成について、表計算ソフトを使って行うことにした。
〔ワークシート：価格表〕
商品の単価を記載したワークシート“価格表”の例を、図1に示す。
(1) 販売している商品は50品目あり、セルA2～A51には1から始まる連番で商品番号を入力する。
(2) セルB2～B51には商品名を、セルC2～C51には商品コードを、セルD2～D51には単価を入力する。商品名と商品コードは、それぞれ重複するものはない。
〔ワークシート：セット値引き表〕
セットと値引き額を記載したワークシート“セット値引き表”の例を、図2に示す。
(1) セットは10種類あり、値引きを適用していく順に入力する。
(2) セルA3～A12には、1から始まる連番でセット番号を入力する。
(3) セルB3～B12には、セット名を入力する。
(4) セットは3種類の商品の組合せから成り、セルC3～H12には組み合わせる商品の商品コードと数量を入力する。商品コードは、ワークシート“価格表”に登録されているものを用いる。
(5) セルI3～I12には、セットの値引き額を入力する。
(6) 列K～Mは、セット値引き適用処理のための作業領域に用いる。
〔ワークシート：購入伝票〕
購入する商品の一覧（購入内容）、セット値引き適用状況の一覧（セット値引き内容）、合計金額などを表示するワークシート“購入伝票”の例を、図3に示す。
(1) 1回に購入できる商品は20品目以下である。ワークシート“購入伝票”のセルB7～C26の各行に、商品名と数量を1件ずつ入力すると、各行の列Dと列Eにそれぞれ単価と金額（数量×単価）が、セルE1に金額の合計が表示される。ワークシート“価格表”に登録されていない商品名を入力することはない。また、同一の商品を2行以上入力することができる。
(2) マクロCalc_Discount_Priceを実行すると、セルC30～C39に、セット値引きを適用するセットについてだけ数量が設定され、対応する各行の列Eにセット値引き金額（数量×値引き額）が、セルE2にセット値引き金額の合計が表示される。
設問1：
問題文を見る ワークシート“購入伝票”に関する次の説明中のに入れる正しい答えを、解答群の中から選べ。
(1) セルA7～A26には、1から始まる連番を入力する。
(2) セルE1には、購入する商品の合計金額を求めるための式を入力する。
(3) セルE2には、セット値引き金額の合計を求めるための式を入力する。
(4) セルE3には、購入金額を求めるための式を入力する。
(5) 商品ごとの単価を表示するために、セルD7に次の式を入力し、セルD8～D26に複写する。
IF(B7 = null, null, 垂直照合(B7, a, 0))
(6) 商品ごとの金額を求めるために、セルE7に次の式を入力し、セルE8～E26に複写する。
IF(D7 = null, null, C7 * D7)
(7) セルB30～B39には、ワークシート“セット値引き表”のセット名を、同表と同じ順序で表示するための式を入力する。
(8) セルC30～C39には、マクロCalc_Discount_Priceを用いて、数量を設定する。
(9) ワークシート“セット値引き表”の値引き額を、同表と同じ順序で表示するために、セルD30に次の式を入力し、セルD31～D39に複写する。
b
(10) セット値引き金額を求めるために、セルE30に次の式を入力し、セルE31～E39に複写する。
IF(C30 = null, null, C30 * D30)
aに関する解答群
ア：価格表!A$2:D$51, 2
イ：価格表!A$2:D$51, 3
ウ：価格表!A$2:D$51, 4
エ：価格表!B$2:D$51, 2
オ：価格表!B$2:D$51, 3
bに関する解答群
ア：セット値引き表!A3
イ：セット値引き表!A$3
ウ：セット値引き表!I3
エ：セット値引き表!I$3
模範解答
a：オ、b：ウ
解説
解答の導き方
この設問では、商品名から単価を表示する参照先と、セット値引き表から値引き額を取り出す参照先を考えます。
a：商品名から単価を探す範囲
セルD7の式では、セルB7の商品名を使って、ワークシート“価格表”から単価を探します。問題文には、商品名はセルB2～B51、単価はセルD2～D51に入力するとあります。
垂直照合は、指定した範囲の左端の列から値を探し、見つかった行の指定列の値を返す関数です。そのため、範囲の左端は商品名があるB列にします。単価はD列にあるので、B列から数えた列番号は、B列が1、C列が2、D列が3です。
したがって、範囲と列番号は「価格表!B$2:D$51, 3」となり、解答群はオです。末尾の0は、商品名が完全に一致する行を探す指定です。商品名に重複はないため、一致した商品を特定できます。
ほかの選択肢が適切でない理由も確認します。
- ア、イ、ウは範囲の左端がA列です。商品名ではなく商品番号を探すことになるため、セルB7の商品名を探す範囲として適切ではありません。
- エは商品名を探す列は合っていますが、列番号が2なので、返すのはC列の商品コードです。単価のD列を返すには列番号3が必要です。
b：セット値引き表から値引き額を表示する参照
セルD30には、セット値引き表の値引き額を表示します。問題文では、セルB30～B39にセット名を同じ順序で表示するとあります。セット値引き表でもセットは同じ順序で並んでいるため、D30では同表の3行目にある値引き額を参照します。値引き額の列はI列なので、参照先は「セット値引き表!I3」です。解答群ではウです。
この参照をセルD31～D39に複写すると、参照先の行番号が4、5、…、12と順に変わり、各セットの値引き額を表示できます。一方、「セット値引き表!I$3」のように行番号の前に$を付けると、複写しても行番号が3のままです。その場合、すべての行で3行目の値引き額を参照してしまいます。
また、A列はセット番号の列であり、値引き額の列ではありません。値引き額を表示するにはI列を参照する必要があります。
誤りやすいポイント
- 垂直照合の列番号は、ワークシート全体での列番号ではなく、指定した範囲の左端から数えます。B列から始まる範囲なら、D列は3列目です。
- 商品名を探すときは、検索範囲の左端に商品名の列を置きます。範囲に商品名の列が含まれていても、左端でなければ商品名を検索できません。
- セルを複写したとき、$の付いた行番号は固定されます。複写に合わせて行番号を変えたい参照には、$を付けません。
- セット番号と値引き額を取り違えないようにします。セット番号はA列、値引き額はI列です。
FAQ
Q: 垂直照合の最後の0は何を表しますか？
A: 検索値と完全に一致する値を探す指定です。この問題では、セルB7の商品名と同じ商品名を探します。
A: 検索値と完全に一致する値を探す指定です。この問題では、セルB7の商品名と同じ商品名を探します。
Q: セルD31に複写したとき、なぜ参照先の行番号が変わるのですか？
A: 「セット値引き表!I3」は行番号を固定していない参照だからです。下のセルへ複写すると、参照先もI4、I5のように変わります。
A: 「セット値引き表!I3」は行番号を固定していない参照だからです。下のセルへ複写すると、参照先もI4、I5のように変わります。
Q: 参照範囲の行番号に付いている$にはどんな役割がありますか？
A: セルD7の式を下へ複写しても、価格表の検索範囲がずれないように、行番号を固定します。
A: セルD7の式を下へ複写しても、価格表の検索範囲がずれないように、行番号を固定します。
関連キーワード: 垂直照合、完全一致検索、セル参照、絶対参照、相対参照
設問2：
問題文を見る セット値引き適用処理に関する次の説明及びマクロ中のに入れる正しい答えを、解答群の中から選べ。
〔セット値引き適用処理の説明〕
“セット値引き表”に記載されているセットに対して、セット番号が小さいものから順にセット化して、値引きを適用していく。
(1) セットを構成する商品の商品番号を表示するために、ワークシート“セット値引き表”のセルK3に次の式を入力し、セルK4～K12に複写する。
照合検索(C3, c, d)
同様の方法で、セルL3～L12とセルM3～M12にも、セットを構成する商品の商品番号を表示するための式を入力する。
(2) ワークシート“購入伝票”にマクロCalc_Discount_Priceを格納する。
〔マクロの説明〕
マクロCalc_Discount_Priceは、次の手順で処理を行う。
(1) 購入する各商品の数量を求め、配列Goods_Numに格納する。商品番号がi（i = 1, 2, …, 50）の商品の数量は、Goods_Num[i − 1]に格納される。
(2) セット番号が1, 2, …, 10の順に、セット値引きの対象となる数量を求めるために、(3)～(5)の処理を繰り返す。
(3) 配列Goods_Numについて、セットを構成する3種類の商品の数量を調べ、セットにできる最大の数量Set_Numを求める。
(4) 数量Set_Numが0でない場合は、ワークシート“購入伝票”のセットの数量のセルに数量Set_Numを格納し、セット値引きの対象とした3種類の商品の数量を、配列Goods_Numから減らす。
(5) 数量Set_Numが0の場合は、ワークシート“購入伝票”のセットの数量のセルに空値nullを格納する。
c, dに関する解答群
ア：価格表!A$2:A$51
イ：価格表!B$2:B$51
ウ：価格表!C$2:C$51
エ：価格表!D$2:D$51
eに関する解答群
ア：価格表!A2
イ：価格表!B2
ウ：価格表!C2
エ：相対(価格表!A2, I, 0)
オ：相対(価格表!B2, I, 0)
カ：相対(価格表!C2, I, 0)
fに関する解答群
ア：Num < Set_Num
イ：Num > Set_Num
ウ：Num = 0
エ：Num ≠ 0
オ：Num ≠ Set_Num
カ：Set_Num = 0
キ：Set_Num ≠ 0
gに関する解答群
ア：+ 1
イ：+ Set_Num
ウ：− 1
エ：− Set_Num
オ：* Set_Num
カ：/ Set_Num
模範解答
c：ウ、d：ア、e：オ、f：ア、g：オ
解説
解答の導き方
この処理では、まず購入した商品の数量を数え、その数量を使ってセットを作れる数を求めます。セット番号の小さいものから順に処理し、先に使った商品の数量は後のセットに使わないようにします。
1. 商品コードから商品番号を求める式を考えます
本文では、セット値引き表のC列に商品の「商品コード」、価格表のC列にも商品コードがあると分かります。また、マクロはセットを構成する商品の商品番号を使って、配列Goods_Numの該当要素を参照します。
そのため、照合検索では、まず価格表のC列から商品コードを探し、同じ行にあるA列の商品番号を返します。式は次の形です。
照合検索(C3, 価格表!C$2:C$51, 価格表!A$2:A$51)
検索する範囲がc、返す値の範囲がdなので、cはウ、dはアです。範囲の行番号に$を付けるのは、式を下の行へ複写しても検索範囲を変えないためです。一方、C3は複写先に合わせてC4、C5のように変わります。
2. 商品ごとの購入数量を数える式を考えます
マクロは、Iを0から49まで変えながら、価格表の商品を1品目ずつ調べます。商品番号の商品は、配列のに数量を格納します。つまり、商品番号1は、商品番号2はに対応します。
商品名を使って数量を集計するため、Nameには価格表のB列から商品名を取り出します。Iに応じて参照する行を動かす必要があるので、eには相対(価格表!B2, I, 0)を入れます。これにより、Iが0ならB2、Iが1ならB3を参照します。
続く条件付合計では、購入伝票のB7～B26からNameと一致する商品名の行を探し、対応するC7～C26の数量を合計します。同じ商品を複数の行に入力できるため、該当する数量を合算する必要があります。
3. セットにできる最大数を求めます
セットにできる数は、セットを構成する各商品のうち、最も不足しやすい商品によって決まります。
最初の商品の数量を、そのセットで必要な数量で割り、整数部分をSet_Numにします。例えば、ある商品を1セットに2個使い、購入数量が5個なら、作れるセット数はの整数部分である2です。端数の商品だけではセットを作れないため、整数部分を使います。
次に、残り2種類の商品についても同じ計算をします。計算結果をNumとし、Numが現在のSet_Numより小さい場合にSet_NumをNumに置き換えます。したがってfは「Num < Set_Num」です。こうして、3種類すべての商品がそろう最大数を求めます。
4. セットに使った数量を差し引きます
Set_Numが0でなければ、その数を購入伝票のセット数量欄に記入します。その後、セットに使った商品の数量をGoods_Numから減らします。
減らす量は、1セットに必要な数量だけではありません。セットをSet_Num個作るので、「1セットに必要な数量 × Set_Num」を差し引きます。したがってgは「* Set_Num」です。
例えば、1セットに商品を2個使い、セットを3個作るなら、配列から差し引く数量は個です。数量を差し引くことで、すでに前のセットに使った商品を、後のセットでも重ねて使ってしまうことを防ぎます。
Set_Numが0なら、そのセットは作れないため、購入伝票のセット数量欄には空値nullを格納します。
誤りやすいポイント
- 商品コードを探す範囲と、商品番号を返す範囲を取り違えないようにします。検索するのは価格表のC列、返すのはA列です。
- Nameには商品名を入れます。条件付合計で照合する範囲はB7～B26の商品名だからです。
- 商品名の参照は、Iに応じて行を移動させます。常に価格表のB2だけを見ると、50品目それぞれの数量を集計できません。
- セット数は、構成する商品のうち最も少ない数に合わせます。商品の数量を単純に足したり、最も多い数を選んだりするのではありません。
- 商品の数量を減らすときは、セット数だけを引くのではなく、「1セットに必要な数量 × セット数」を引きます。
FAQ
Q: 同じ商品が購入伝票の複数行にある場合、どう扱いますか？
A: 条件付合計で、その商品名に一致するすべての行の数量を合計します。例えば、マウス（USB接続）が複数行に分かれていても、合計した数量をセット判定に使えます。
A: 条件付合計で、その商品名に一致するすべての行の数量を合計します。例えば、マウス（USB接続）が複数行に分かれていても、合計した数量をセット判定に使えます。
Q: なぜ商品ごとの数量のうち、最も少ない数をセット数にするのですか？
A: セットは3種類の商品がそろって初めて作れるためです。どれか1種類でも足りなくなれば、それ以上はセットにできません。
A: セットは3種類の商品がそろって初めて作れるためです。どれか1種類でも足りなくなれば、それ以上はセットにできません。
Q: セットに使った数量を次の処理に残すと、何が問題ですか？
A: 同じ商品を複数のセットに重ねて使う計算になります。先のセットで使った数量を差し引くことで、購入した数量の範囲内で値引きを適用できます。
A: 同じ商品を複数のセットに重ねて使う計算になります。先のセットで使った数量を差し引くことで、購入した数量の範囲内で値引きを適用できます。
関連キーワード: 表計算、相対参照、絶対参照、条件付き合計、配列、整数部
