基本情報技術者 2010年 春期 午後 問13
次の表計算及びワークシートの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
ある喫茶店では、これまで手書き伝票と電卓を使って料金計算を行ってきたが、飲料の品目サイズ別メニュー化及び料理オプションの追加に伴い、表計算ソフトを利用することにした。
〔ワークシート：メニュー〕
(1) この喫茶店の飲料、料理を記載した表計算ソフトのワークシート“メニュー”を作成した。そのワークシートを図1に示す。
(2) ワークシート“メニュー”中の各項目の説明は、次のとおりである。
飲料品目：“ブレンド”などの4品目である。
サイズ：“S”、“M”、“L”の3種類で、品目ごとサイズごとの価格である。
セット割引：料理と飲料を同時に注文した場合に適用される飲料の割引額である。ワークシートには、負の値を設定する。
料理品目：“サンドイッチ”などの4品目である。
オプション：“単品”、“ランチ”、“大盛”の3種類である。“単品”は、料理だけを注文した場合の価格である。“ランチ”は、単品にスープ又はサラダが付く注文をした場合の価格である。“大盛”は、大盛を注文した場合の価格であり、“カレー”と“ナポリタン”のランチに限る。
〔ワークシート：伝票〕
(1) 注文された飲料及び料理の数量の入力によって、料金計算を行うためのワークシート“伝票”を作成した。そのワークシートの例を、図2に示す。
(2) ワークシート“伝票”には次の文字列及び計算式を入力する。
① 行2〜27の種類、品目、タイプには、あらかじめ、次のすべての取り得る組合せを入力する。
種類：“飲料”又は“料理”を入力する。
品目：飲料又は料理の品目を入力する。
タイプ：飲料の場合は、サイズの種類又は“セット割引”を入力する。料理の場合は、オプションの種類を入力する。
② 行2〜27の単価には、ワークシート“メニュー”を参照して、該当する価格を求める計算式を入力する。
③ 行2〜27の料金には、品目、タイプごとの料金を求める計算式を、行28には、セット割引を加味した合計料金を求める計算式を入力する。
(3) ワークシート“伝票”を作成する。
① ワークシート“伝票”は、顧客のグループ単位に注文をまとめて作成する。
② グループ内の個々の顧客の注文に基づき、品目、タイプごとの注文数量を数量に入力する。
③ 品目、タイプごとの料金が計算され、更に合計料金が計算される。
設問1：
問題文を見る ワークシート“伝票”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
セルD2に単価を求めるための次の計算式を入力して、セルD3〜D27に複写する。ここで、複数のワークシート間でデータを参照するには、“ワークシート名!セル”又は“ワークシート名!セルの範囲”という形式で指定する。
IF(A2 = '飲料',a,b)
解答群
ア：照合一致(配列(メニュー!$B$4〜$E$7,B2,C2),メニュー!$A$4〜$A$7)
イ：照合一致(配列(メニュー!$B$4〜$E$7,B2,C2),メニュー!$B$3〜$E$3)
ウ：照合一致(配列(メニュー!$H$4〜$J$7,B2,C2),メニュー!$G$4〜$G$7)
エ：照合一致(配列(メニュー!$H$4〜$J$7,B2,C2),メニュー!$H$3〜$J$3)
オ：配列(メニュー!$B$4〜$E$7,照合一致(B2,メニュー!$A$4〜$A$7),照合一致(C2,メニュー!$B$3〜$E$3))
カ：配列(メニュー!$B$4〜$E$7,照合一致(B2,メニュー!$B$3〜$E$3),照合一致(C2,メニュー!$A$4〜$A$7))
キ：配列(メニュー!$B$4〜$E$7,照合一致(C2,メニュー!$B$3〜$E$3),照合一致(B2,メニュー!$A$4〜$A$7))
ク：配列(メニュー!$H$4〜$J$7,照合一致(B2,メニュー!$G$4〜$G$7),照合一致(C2,メニュー!$H$3〜$J$3))
ケ：配列(メニュー!$H$4〜$J$7,照合一致(B2,メニュー!$H$3〜$J$3),照合一致(C2,メニュー!$G$4〜$G$7))
コ：配列(メニュー!$H$4〜$J$7,照合一致(C2,メニュー!$H$3〜$J$3),照合一致(B2,メニュー!$G$4〜$G$7))
模範解答
a：オ、b：ク
解説
解答の導き方
この計算式では、A列の「種類」を見て、飲料の単価を調べるか、料理の単価を調べるかを分けます。IFは、条件が成り立つ場合と成り立たない場合で、使う計算を切り替える関数です。
A2が「飲料」のときに使うのがa、そうでないときに使うのがbです。
飲料の場合
図1の飲料メニューでは、品目名がA4〜A7、単価がB4〜E7にあります。また、サイズや「セット割引」の見出しはB3〜E3にあります。
単価を探すには、次の2つの位置を調べます。
- B2の品目名が、A4〜A7の何番目にあるか。これが「配列」の行位置になります。
- C2のタイプが、B3〜E3の何番目にあるか。これが「配列」の列位置になります。
「照合一致」は、指定した範囲の中で値が何番目にあるかを返します。「配列」は、範囲の左上から数えた行位置と列位置を使って、該当する値を取り出します。
たとえば、B2が「ブレンド」、C2が「S」の場合、品目はA4〜A7の1番目、タイプはB3〜E3の1番目です。そのため、B4〜E7の1行目・1列目にある300が単価になります。
このように、行位置をB2から、列位置をC2から求める式は、解答群のオです。B3〜E3には「セット割引」も含まれるため、セット割引の単価も同じ方法で取り出せます。
料理の場合
図1の料理メニューでは、品目名がG4〜G7、単価がH4〜J7にあります。オプションの見出しはH3〜J3です。
飲料と同じように、B2の品目名がG4〜G7の何番目かを行位置にし、C2のオプションがH3〜J3の何番目かを列位置にします。この順序で位置を求める式は、解答群のクです。
たとえば、品目が「カレー」、オプションが「ランチ」の場合、品目はG4〜G7の3番目、オプションはH3〜J3の2番目です。したがって、H4〜J7の3行目・2列目にある850が単価になります。
以上から、aにはオ、bにはクが入ります。
誤りやすいポイント
- 「配列」の引数は、範囲、行位置、列位置の順です。行位置と列位置を逆にすると、正しい単価を取り出せません。
- 「照合一致」が返すのは、セルの番地ではなく、範囲の中で何番目にあるかです。図1のメニュー全体での行番号や列番号ではありません。
- 飲料の単価範囲はB4〜E7です。E列には「セット割引」の単価があるため、B4〜D7にすると割引を取り出せません。
- メニューの範囲は固定し、B2やC2は複写先に合わせて変わる参照です。メニューの範囲を固定する指定と、注文行に応じて変わる指定を混同しないようにします。
FAQ
Q: なぜ「照合一致」を2回使うのですか？
A: 単価表では、品目によって行が変わり、サイズやオプションによって列が変わるためです。品目から行位置を、タイプから列位置をそれぞれ求めます。
A: 単価表では、品目によって行が変わり、サイズやオプションによって列が変わるためです。品目から行位置を、タイプから列位置をそれぞれ求めます。
Q: 飲料の列位置を調べる範囲に「セット割引」も含めるのはなぜですか？
A: 「セット割引」も飲料のタイプとして伝票に入力され、単価を求める必要があるためです。見出しの範囲と単価の範囲の両方に、その列を含めます。
A: 「セット割引」も飲料のタイプとして伝票に入力され、単価を求める必要があるためです。見出しの範囲と単価の範囲の両方に、その列を含めます。
Q: メニューの範囲を固定するのはなぜですか？
A: 計算式をD3〜D27に複写しても、参照するメニューの場所がずれないようにするためです。一方、B2やC2は複写先の行に応じて変わり、各行の品目やタイプを調べます。
A: 計算式をD3〜D27に複写しても、参照するメニューの場所がずれないようにするためです。一方、B2やC2は複写先の行に応じて変わり、各行の品目やタイプを調べます。
関連キーワード: IF関数、配列参照、照合、相対参照、絶対参照
設問2：
問題文を見る ワークシート“割引”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：割引〕
顧客満足度向上のための施策として、次の割引策を適用することにした。
(1) 最適割引
顧客のグループ内で、料理と飲料のセットの組合せを変えることで、注文時のセット割引額よりも割引額が大きくなる場合がある。注文時の伝票の入力内容を調べ、注文された料理又は飲料の注文数量の少ない方をセット数の上限として、割引額の高い飲料からセットに割り当てていくことによって、セット割引額の合計が最大となるように割引を行う。
(2) 数量割引
数量割引は、飲料を15個以上かつ料理を10個以上、又は料理と飲料を合わせて30個以上を注文した場合に適用され、最適割引時の合計料金から5％の割引を行う。
最適割引及び数量割引の計算を行うためのワークシート“割引”を作成した。そのワークシートの例を、図3に示す。
なお、数量割引後の合計料金は、小数点以下を切り捨てて表示する。
(1) 行1〜4では、セット割引、飲料、料理の注文数量から、最適セット数を求める。
① ワークシート“伝票”におけるセット割引、飲料の注文数の合計を求める次の計算式をセルB1、B2に入力する。
セルB1 c
セルB2 d
セルB3に料理の注文数の合計を求める計算式を入力する。
② 行4の最適セット数は、最適割引を行うためのセットの注文数の合計であり、飲料又は料理の注文数の合計値の少ない方になる。最適セット数を求める計算式を、セルB4に入力する。
(2) 行7〜13では、“伝票”を参照しながら、注文時の注文数、セット数及び割引額を、セット割引単価の大きい順に並べ替えた品目ごとに集計する。さらに、最適割引を行った場合のセット数の割当数及び割引額の計算を行う。
① セルA9〜A12にセット割引単価の大きい順に並べ替えた飲料の品目を入力する。
② セルB9〜B12に品目ごとのセット割引額を入力する。
③ セルC9に注文時の品目ごとの注文数を求める次の計算式を入力し、セルC10〜C12に複写する。
e
④ セルD9に注文時の品目ごとのセット数を求める計算式を入力し、セルD10〜D12に複写する。
⑤ セルE9〜E12には、注文時の品目ごとの割引額の計算式を入力する。
⑥ セルF9に最適割引を行うための最適セット数であるセルB4の値を複写する。次に、セット割引をセット割引単価の大きい品目の順に割り当てたセット数を求めるためにセルG9に次の計算式を入力し、セルG10〜G12に複写する。
f
⑦ セルF10〜F13に、セット数を品目ごとに順次割り当てたときの割当数の残数を求める計算式を入力する。
⑧ セルH9〜H12に、最適割引時の品目ごとの割引額の計算式を入力する。
⑨ セルC13、D13、E13に、注文時の注文数、セット数、割引額の合計値を求める計算式を入力する。セルF13には、合計値ではなく、⑦で割当数の残数を求める計算式を入力する。セルG13、H13に、最適割引時のセット数、割引額の合計値を求める計算式を入力する。
(3) セルB14に、最適割引時の合計料金を求める計算式を入力する。
(4) 数量割引後の合計料金を求める次の計算式をセルB15に入力する。
g
c, dに関する解答群
ア：照合合計(A1,伝票!C2〜C27,伝票!E2〜E27)
イ：照合合計(A2,伝票!A2〜A27,伝票!E2〜E27)
ウ：照合合計(A2,伝票!A2〜A27,伝票!E2〜E27) − B1
エ：照合合計(A3,伝票!A2〜A27,伝票!E2〜E27)
オ：照合合計(A3,伝票!A2〜A27,伝票!E2〜E27) − B1
カ：複数条件照合合計((A2,'≠',伝票!A2〜A27),(A1,'=',伝票!C2〜C27),伝票!E2〜E27)
キ：複数条件照合合計((A3,'=',伝票!A2〜A27),(A1,'=',伝票!C2〜C27),伝票!E2〜E27)
ク：複数条件照合合計((A3,'=',伝票!A2〜A27),(A1,'≠',伝票!C2〜C27),伝票!E2〜E27)
eに関する解答群
ア：照合合計(A9,伝票!$B$2〜$B$27,伝票!$E$2〜$E$27)
イ：照合合計(A9,伝票!$B$2〜$B$27,伝票!$E$2〜$E$27) − 照合合計(B$7,伝票!$C$2〜$C$27,伝票!$E$2〜$E$27)
ウ：照合合計(B$7,伝票!$C$2〜$C$27,伝票!$E$2〜$E$27)
エ：照合合計(B$7,伝票!$C$2〜$C$27,伝票!$E$2〜$E$27) − 照合合計(A9,伝票!$B$2〜$B$27,伝票!$E$2〜$E$27)
オ：複数条件照合合計((A9,'=',伝票!$B$2〜$B$27),(B$7,'=',伝票!$C$2〜$C$27),伝票!$E$2〜$E$27)
カ：複数条件照合合計((A9,'=',伝票!$B$2〜$B$27),(B$7,'≠',伝票!C$2〜C$27),伝票!E$2〜E$27)
キ：複数条件照合合計((A9,'≠',伝票!B$2〜B$27),(B$7,'=',伝票!C$2〜C$27),伝票!E$2〜E$27)
ク：複数条件照合合計((A9,'≠',伝票!B$2〜B$27),(B$7,'≠',伝票!C$2〜C$27),伝票!E$2〜E$27)
fに関する解答群
ア：IF(F9 ≧ C9,C9,F9)
イ：IF(F9 ≧ C9,C9,F9 + C9)
ウ：IF(F9 ≧ C9,C9,F9 − C9)
エ：IF(F9 ≦ C9,C9,F9)
オ：IF(F9 ≦ C9,C9,F9 + C9)
カ：IF(F9 ≦ C9,C9,F9 − C9)
gに関する解答群
ア：B14 − IF(論理和(論理積(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0) * H13
イ：B14 − IF(論理積(論理和(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0) * H13
ウ：(1 + IF(論理和(論理積(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0)) * B14
エ：(1 + IF(論理積(論理和(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0)) * B14
オ：(1 − IF(論理和(論理積(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,1)) * B14
カ：(1 − IF(論理積(論理和(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,1)) * B14
キ：(1 − IF(論理積(論理和(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0)) * B14
ク：(1 − IF(論理和(論理積(B2 ≧ 15,B3 ≧ 10),(B2 + B3) ≧ 30),0.05,0)) * B14
模範解答
c：ア、d：ウ、e：カ、f：ア、g：ク
解説
解答の導き方
この問題では、注文時のセット割引をいったん集計し、割引額が大きくなるようにセットを組み直します。その後、条件を満たす場合に数量割引を適用します。まずは、各セルで数えている数量を整理します。
1. B1〜B4で、注文数と最適セット数を求める
表1の「照合合計」は、指定した値と一致するセルを探し、同じ位置にある数量を合計する関数です。
B1では、伝票のタイプが「セット割引」の行の数量を合計します。A1にセット割引の文字列があり、伝票ではタイプがC列、数量がE列なので、C列を検索してE列を合計する式を使います。このため、cはアです。
「飲料」の注文数を求めるときは、伝票のA列が「飲料」の行の数量を合計します。ただし、その中にはセット割引の行に入力された数量も含まれています。セット割引の行の数量は、飲料の注文数とは別に記録されたセット数なので、二重に数えないようB1を引きます。このため、dはウです。
一方、料理にはセット割引の行がありません。料理の注文数は、A列が「料理」の行の数量を合計すれば求められます。
最適セット数は、飲料と料理の注文数のうち少ない方です。料理と飲料を1組にするため、どちらかの注文数を超えてセットを作ることはできません。したがって、B4はB2とB3の小さい方になります。図3の例では、飲料が15個、料理が13個なので、最適セット数は13です。
2. eで、品目ごとの飲料注文数を集計する
C9では、A9にある品目と一致する飲料の数量を合計します。ただし、「セット割引」の行は飲料そのものの注文数ではないため、除外する必要があります。
表2の「複数条件照合合計」は、同じ行について複数の条件を同時に満たすものを探して、対応する数量を合計します。ここでは、次の2条件を使います。
- 伝票の品目がA9と一致する
- 伝票のタイプがB7と一致しない
B7は「セット割引」を表すので、この条件によってセット割引の行を除外できます。したがって、eはカです。
この式をC10〜C12に複写すると、A9がA10、A11、A12へと変わり、品目ごとの数量を集計できます。式中の絶対参照は、複写しても伝票の検索範囲がずれないようにするための指定です。
3. fで、最適セットを割り当てる
セットは、割引額の大きい飲料から順に割り当てます。飲料のセット割引額は負の値なので、数値として小さい「−300」の方が、「−150」よりも割引額の大きい品目です。図3では、抹茶ラテ、カフェモカ、カプチーノ、ブレンドの順に並んでいます。
F9には、まだ割り当てていない最適セット数が入ります。G9では、残りのセット数（F9）と、その品目の注文数（C9）を比べ、小さい方を割り当てます。
- 残りのセット数が注文数以上なら、注文数分を割り当てます。
- 残りのセット数が注文数より少なければ、残っている分だけ割り当てます。
つまり、「F9とC9のうち小さい方」を返すIF関数（条件によって値を選ぶ関数）を使います。このため、fはアです。
図3の例で、残りが13セット、抹茶ラテの注文数が3個なら、抹茶ラテに3セットを割り当てます。残りは10セットです。次のカフェモカにも3セットを割り当てて残りは7セット、カプチーノには5セットを割り当てて残りは2セットです。最後にブレンドへ2セットを割り当てると、残りは0になります。
F10〜F13では、このように前の品目への割当て後に残ったセット数を求めます。H9〜H12は、セット割引単価に割当数を掛けて品目ごとの割引額を求めます。図3では、最適割引時の割引額の合計は−2,950です。
4. B14で、最適割引後の合計料金を求める
伝票の合計料金には、すでに注文時のセット割引が反映されています。そのため、最適割引時の合計料金を求めるには、伝票の合計料金から注文時の割引を取り除き、最適割引時の割引を加えます。
図3の例では、伝票の合計料金は14,050、注文時の割引額の合計は−1,950、最適割引時の割引額の合計は−2,950です。注文時の割引が負の値なので、取り除くときは「−（−1,950）」になります。
したがって、最適割引時の合計料金は13,050です。
5. gで、数量割引を適用するか判定する
数量割引の条件は、次のどちらかです。
- 飲料が15個以上、かつ料理が10個以上
- 飲料と料理を合わせて30個以上
「どちらか」を表すのが論理和、「両方を満たす」を表すのが論理積です。したがって、判定部分は「飲料が15個以上かつ料理が10個以上」または「合計が30個以上」という形になります。
条件を満たした場合は、B14の合計料金から5％を割り引きます。条件を満たさない場合は割引率を0にします。よって、数量割引後の料金は「1から割引率を引いた値」にB14を掛ける式となり、gはクです。
図3の例では、飲料が15個、料理が13個なので、最初の条件を満たします。合計は28個ですが、2つ目の条件を満たす必要はありません。「または」で結ばれているため、最初の条件だけで数量割引が適用されます。
問題文では小数点以下を切り捨てて表示するため、数量割引後の合計料金は12,397です。
誤りやすいポイント
- B2では、飲料の数量を合計するだけでは不十分です。セット割引の数量も含まれるため、B1を引いて二重計上を防ぎます。
- eでは、品目だけで集計するとセット割引の行まで含まれます。品目の一致に加えて、タイプが「セット割引」ではないことも条件にします。
- 割引額は負の値です。−300は−150より数値として小さいですが、割引額の大きさは300の方が大きいため、先に割り当てます。
- 最適セット数をすべて一つの品目に割り当てるのではなく、各品目の注文数を超えないようにします。残数を次の品目へ引き継ぎます。
- 数量割引の条件は「飲料と料理の両方の個数条件」または「合計個数条件」です。2つの条件をすべて満たす必要があるわけではありません。
- 5％を掛ける対象は、最適割引後の合計料金です。割引額だけを対象にするのではありません。
FAQ
Q: B2の計算で、なぜB1を引くのですか？
A: B2は飲料の行の数量を合計します。その中には、飲料の注文数とは別に記録したセット割引の数量も含まれるため、B1を引いて重複を取り除きます。
A: B2は飲料の行の数量を合計します。その中には、飲料の注文数とは別に記録したセット割引の数量も含まれるため、B1を引いて重複を取り除きます。
Q: −300の割引を−150より先に割り当てるのはなぜですか？
A: 割引額は合計料金から差し引く値として負の数で記録されています。−300なら料金が300減り、−150なら150減るため、−300の方が割引額は大きくなります。
A: 割引額は合計料金から差し引く値として負の数で記録されています。−300なら料金が300減り、−150なら150減るため、−300の方が割引額は大きくなります。
Q: 合計が30個未満でも数量割引が適用されることはありますか？
A: あります。飲料が15個以上で、かつ料理が10個以上なら、合計個数が30個未満でも条件を満たします。図3の例はこのケースです。
A: あります。飲料が15個以上で、かつ料理が10個以上なら、合計個数が30個未満でも条件を満たします。図3の例はこのケースです。
関連キーワード: 表計算、条件付き集計、絶対参照、論理演算、IF関数、端数処理
