基本情報技術者 試験情報＆徹底解説 -新制度に完全対応-

次の表計算及びワークシートの説明を読んで，設問1，2に答えよ。

〔表計算の説明〕
　ある喫茶店では，これまで手書き伝票と電卓を使って料金計算を行ってきたが，飲料の品目サイズ別メニュー化及び料理オプションの追加に伴い，表計算ソフトを利用することにした。

〔ワークシート：メニュー〕

• この喫茶店の飲料，料理を記載した表計算ソフトのワークシート"メニュー"を作成した。そのワークシートを図1に示す。
  
• ワークシート"メニュー"中の各項目の説明は，次のとおりである。
  

  飲料品目

  "ブレンド"などの4品目である。

  サイズ

  "S"，"M"，"L"の3種類で，品目ごとサイズごとの価格である。

  セット割引

  料理と飲料を同時に注文した場合に適用される飲料の割引額である。
  　ワークシートには，負の値を設定する。

  料理品目

  "サンドイッチ"などの4品目である。

  オプション

  "単品"，"ランチ"，"大盛"の3種類である。"単品"は，料理だけを注文した場合の価格である。"ランチ"は，単品にスープ又はサラダが付く注文をした場合の価格である。"大盛"は，大盛を注文した場合の価格であり，"カレー"と"ナポリタン"のランチに限る。

〔ワークシート：伝票〕

• 注文された飲料及び料理の数量の入力によって，料金計算を行うためのワークシート"伝票"を作成した。そのワークシートの例を，図2に示す。
  
• ワークシート"伝票"には次の文字列及び計算式を入力する。
  1. 行2～27の種類，品目，タイプには，あらかじめ，次のすべての取り得る組合せを入力する。
     

     種類：

     "飲料"又は"料理"を入力する。

     品目：

     飲料又は料理の品目を入力する。

     タイプ：

     飲料の場合は，サイズの種類又は"セット割引"を入力する。
     料理の場合は，オプションの種類を入力する。

  2. 行2～27の単価には，ワークシート"メニュー"を参照して，該当する価格を求める計算式を入力する。
  3. 行2～27の料金には，品目，タイプごとの料金を求める計算式を，行28には，セット割引を加味した合計料金を求める計算式を入力する。
• ワークシート"伝票"を作成する。
  1. ワークシート"伝票"は，顧客のグループ単位に注文をまとめて作成する。
  2. グループ内の個々の顧客の注文に基づき，品目，タイプごとの注文数量を数量に入力する。
  3. 品目，タイプごとの料金が計算され，更に合計料金が計算される。

ワークシート"伝票"に関する次の記述中の　に入れる正しい答えを，解答群の中から選べ。

　セル D2 に単価を求めるための次の計算式を入力して，セル D3～D27 に複写する。ここで，複数のワークシート間でデータを参照するには，"ワークシート名!セル"又は"ワークシート名!セルの範囲"という形式で指定する。
　　　IF(A2＝'飲料',a,b)

〔ワークシート：割引〕
　顧客満足度向上のための施策として，次の割引策を適用することにした。

• 最適割引
  　顧客のグループ内で，料理と飲料のセットの組合せを変えることで，注文時のセット割引額よりも割引額が大きくなる場合がある。注文時の伝票の入力内容を調べ，注文された料理又は飲料の注文数量の少ない方をセット数の上限として，割引額の高い飲料からセットに割り当てていくことによって，セット割引額の合計が 最大となるように割引を行う。
• 数量割引
  　数量割引は，飲料を15個以上かつ料理を10個以上，又は料理と飲料を合わせて30個以上を注文した場合に適用され，最適割引時の合計料金から5％の割引を行う。
  　最適割引及び数量割引の計算を行うためのワークシート"割引"を作成した。そのワークシートの例を，図3に示す。
  　なお，数量割引後の合計料金は，小数点以下を切り捨てて表示する。

ワークシート"割引"に関する次の記述中の　に入れる正しい答えを，解答群の中から選べ。

• 行1～4では，セット割引，飲料，料理の注文数量から，最適セット数を求める。
  1. ワークシート"伝票"におけるセット割引，飲料の注文数の合計を求める次の計算式をセル B1，B2 に入力する。
     　　　セルB1　c
     　　　セルB2　d
     　セル B3 に料理の注文数の合計を求める計算式を入力する。
  2. 行4の最適セット数は，最適割引を行うためのセットの注文数の合計であり，飲料又は料理の注文数の合計値の少ない方になると最適セット数を求める計算式を，セル B4 に入力する。
• 行7～13では，"伝票"を参照しながら，注文時の注文数，セット数及び割引額を，セット割引単価の大きい順に並べ替えた品目ごとに集計する。さらに，最適割引を行った場合のセット数の割当数及び割引額の計算を行う。
  1. セル A9～A12 にセット割引単価の大きい順に並べ替えた飲料の品目を入力する。
  2. セル B9～B12 に品目ごとのセット割引額を入力する。
  3. セル C9 に注文時の品目ごとの注文数を求める次の計算式を入力し，セル C10～C12 に複写する。
     　　　e
  4. セル D9 に注文時の品目ごとのセット数を求める計算式を入力し，セル D10～D12 に複写する。
  5. セル E9～E12 には，注文時の品目ごとの割引額の計算式を入力する。
  6. セル F9 に最適割引を行うための最適セット数であるセル B4 の値を複写する。次に，セット割引をセット割引単価の大きい品目の順に割り当てたセット数を求めるためにセル G9 に次の計算式を入力し，セル G10～G12 に複写する。
     　　　f
  7. セル F10～F13 に，セット数を品目ごとに順次割り当てたときの割当数の残数を求める計算式を入力する。
  8. セル H9～H12 に，最適割引時の品目ごとの割引額の計算式を入力する。
  9. セル C13，D13，E13 に，注文時の注文数，セット数，割引額の合計値を求める計算式を入力する。セル F13 には，合計値ではなく，⑦で割当数の残数を求める計算式を入力する。セル G13，H13 に，最適割引時のセット数，割引額の合計値を求める計算式を入力する。
• セル B14 に，最適割引時の合計料金を求める計算式を入力する。
• 数量割引後の合計料金を求める次の計算式をセル B15 に入力する。
  　　　g