基本情報技術者 2018年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1～3に答えよ。
〔表計算の説明〕
Z社は、店舗の窓口で自社商品に関する諸手続のサービスを行っている。来店した顧客が受付機のボタンを押すと、1から始まる連番の受付番号を記した受付票が発行される。窓口では、受付番号の順に担当者がPCを用いて業務処理用サーバ（以下、業務サーバという）の機能を利用しながらサービスを行う。最近、顧客へのアンケートで“待ち時間が長い”との意見が増えたので、表計算ソフトを用いて待ち時間の状況などを分析することにした。
なお、本問において、関数“条件付個数”は、セル範囲に含まれる空白セルでないセルのうち検索条件の記述で指定された条件を満たすセルの個数を返す。
〔ワークシート：来店状況〕
業務サーバには、顧客に対して行ったサービスに関するデータが受付番号ごとに記録される。この記録から分析に必要なデータを得るために、ある1日のデータを業務サーバから抽出、加工して、ワークシート“来店状況”に入力した。ワークシート“来店状況”の例を、図1に示す。
(1) 列A～Fの行3以降に、業務サーバから抽出した、受付票の発行後にサービスを受けた顧客（以下、窓口利用者という）のデータが入力されている。
データは、一つの受付番号に関するものが1レコードとして同一行に入力され、受付番号の昇順に整列されている。データの件数は5件以上250件以下であり、最後のデータが入力された行よりも下の行の各セルには空値が格納されている。
(2) 業務サーバは、時間を分の単位の整数値で保持し、時刻を午前0時00分からの経過分数として保持する。時間及び時刻の計算にはこの整数値を用いる。時間及び時刻は、hh:mm（時:分）の形式で表示する。
(3) 列Aの受付番号は受付票に記された番号であり、列Bの受付時刻はその受付票が発行された時刻である。受付機による受付票の発行は10時00分になると同時に開始し、19時00分になると同時に終了する。
(4) 列Cのサービス種別は、窓口利用者に対して行ったサービスの種別であり、A, B, Cの三つがある。全てのサービスは、1日に1回以上利用される。
(5) 列Dの窓口番号は、サービスを行った窓口の番号である。窓口は三つあり、それらの窓口番号は1, 2, 3である。どの窓口でも全ての種別のサービスを行っている。
(6) 列Eの開始時刻は窓口でサービスを開始した時刻、列Fのサービス時間はサービスに掛かった時間である。
(7) セルG3に、開始時刻にサービス時間を加えて終了時刻を求める次の式を入力し、セルG4～G252に複写する。
IF(A3=null,null,E3+F3)
(8) セルH3に、受付時刻から開始時刻までの時間（以下、待ち時間という）を求める次の式を入力し、セルH4～H252に複写する。
IF(A3=null,null,E3−B3)
(9) セルI3に、当該行の受付番号よりも小さい受付番号をもつ窓口利用者のうち、当該行の受付時刻にまだサービスが開始されていない人数（以下、待ち人数という）を求める次の式を入力し、セルI4～I252に複写する。ここで、待ち時間が0の窓口利用者は、待ち人数に含めない。
IF(A3=null,null, a )
(10) セルJ3に、列Hの待ち時間の値を大きい順に順位付けしたときの当該行の順位（以下、待ち時間順位という）を求める次の式を入力し、セルJ4～J252に複写する。ここで、待ち時間の等しいデータが複数あるときは、受付番号の小さいデータを上位とする。
IF(A3=null,null,条件付個数(H$3:H$252,＞H3) +条件付個数(H$3:H3, =H3))
設問1：
問題文を見る 注記 本設問は問題誤りのため、問13を選択した受験者全員を正解とする措置が取られている。
ワークシート“来店状況”に関する記述中のに入れる正しい答えを、解答群の中から選べ。
aに関する解答群
ア：条件付個数(E3:E$252, >B3)
イ：条件付個数(E$3:E3, <B3)
ウ：条件付個数(E$3:E3, >B3)
エ：条件付個数(G3:G$252, >B3)
オ：条件付個数(G3:G$252, <B3)
カ：条件付個数(G$3:G3, >B3)
模範解答
a：正解なし
解説
解答の導き方
この設問では、セルI列の「待ち人数」を求める式を選びます。まず、数える相手と条件を分けて考えます。
-
数える相手は、受付番号が現在の行より小さい窓口利用者です。
本文には、データが「受付番号の昇順に整列」とあります。そのため、現在の行より前にあるデータが、受付番号の小さい利用者に当たります。現在の行や、後ろの行の利用者は数えません。 -
数える条件は、現在の受付時刻にサービスをまだ開始していないことです。
E列はサービスの開始時刻、B列は受付時刻です。したがって、現在の受付時刻より後にサービスを開始する利用者を数えます。条件は、開始時刻が受付時刻より大きいこと、つまり「E列の値がB列の値より大きいこと」です。 -
条件は「より大きい」でなければなりません。
本文には「待ち時間が0の窓口利用者は、待ち人数に含めない」とあります。開始時刻と受付時刻が同じなら待ち時間は0なので、数えてはいけません。開始時刻が受付時刻と同じ場合も含めないため、条件は「以上」ではなく「より大きい」です。
ここまでをまとめると、数えるのは「現在の行より前にあるE列の値のうち、現在の行のB列の値より大きいもの」です。
選択肢では、ウがE列を使い、「より大きい」という条件も使っています。ただし、ウの範囲はE$3:E3のように現在の行まで含みます。現在の行の利用者は「受付番号が現在の行より小さい利用者」ではありません。さらに、現在の利用者の待ち時間が0より大きい場合は、その人自身の開始時刻も受付時刻より後になるため、ウでは本人まで数えてしまいます。
他の選択肢も、対象範囲が現在の行より後まで広がっていたり、開始時刻ではなく終了時刻を使っていたり、大小関係が逆だったりします。したがって、選択肢の中に条件を満たす式はありません。問題文の注記どおり、この設問は問題誤りとして、問13を選択した受験者全員を正解とする扱いになっています。
誤りやすいポイント
-
「待ち人数」は、待ち時間が長い人の数ではありません。
現在の受付時刻に、まだサービスを開始していない人の数です。すでにサービスを開始していて、終了していない人は含みません。 -
終了時刻ではなく、開始時刻を使います。
サービスが終わっているかではなく、始まっているかを判定するためです。終了時刻のG列を使う選択肢は、待っている人を正しく数えられません。 -
現在の行を対象に含めてはいけません。
待ち人数の対象は、現在の受付番号より小さい利用者です。現在の利用者本人は数えません。 -
等しい場合も数えると、待ち時間0の人が含まれます。
開始時刻と受付時刻が同じなら待ち時間は0です。そのため、「より大きい」という条件が必要です。
FAQ
Q: ウは開始時刻を使い、「より大きい」という条件も使っています。それでも正解ではないのですか？
A: 範囲に現在の行が含まれているためです。現在の利用者の待ち時間が0より大きいと、本人まで数えてしまいます。
A: 範囲に現在の行が含まれているためです。現在の利用者の待ち時間が0より大きいと、本人まで数えてしまいます。
Q: 終了時刻が受付時刻より後なら、まだ待っているということではありませんか？
A: そうとは限りません。受付時刻にサービスをすでに開始していて、まだ終わっていない場合もあります。「まだ開始していない人」を数えるには、終了時刻ではなく開始時刻を確認します。
A: そうとは限りません。受付時刻にサービスをすでに開始していて、まだ終わっていない場合もあります。「まだ開始していない人」を数えるには、終了時刻ではなく開始時刻を確認します。
Q: 選択肢の中から最も近いものを選べばよいですか？
A: いいえ。この設問は問題誤りとされており、模範解答は「正解なし」です。選択肢から無理に一つを選ぶ必要はありません。
A: いいえ。この設問は問題誤りとされており、模範解答は「正解なし」です。選択肢から無理に一つを選ぶ必要はありません。
関連キーワード: 表計算ソフト、条件付き集計、セル参照、絶対参照、相対参照、時刻計算
設問2：
問題文を見る ワークシート“分析”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：分析〕
ワークシート“来店状況”のデータを基に窓口利用者の状況を分析するために、ワークシート“分析”を作成し、セルA2～D12に“時間帯別分析”、セルF2～I6に“種別分析”、セルF9～I14に“待ち時間分析（上位5件）”の表を格納した。ワークシート“分析”の例を、図2に示す。
(1) 集計の区分を1時間ごとの時間帯とし、セルA4〜B12に、各時間帯の始まりの時刻（毎時の00分）と終わりの時刻（毎時の59分）を上から順に入力する。また、セルF4〜F6にサービスの種別を、セルF10〜F14に待ち時間の順位を示す数値1〜5を入力する。
(2) セルC4に、ワークシート“来店状況”のデータから、受付時刻が当該行の時間帯にあるデータの個数を求める次の式を入力し、セルC5〜C12に複写する。
b
(3) セルD4に、ワークシート“来店状況”のデータから、受付時刻が当該行の時間帯にあるデータの待ち時間の平均値を求める式を入力し、セルD5〜D12に複写する。ここで、当該行の時間帯の受付人数が0のときの待ち時間の平均値は0とする。
(4) セルG4に、ワークシート“来店状況”のデータから、サービス種別が当該行のサービス種別と一致するデータの件数を求める式を入力し、セルG5〜G6に複写する。また、セルH4に全サービス件数に対する当該行の件数の構成比率を求める式を入力し、セルH5〜H6に複写する。
(5) セルI4に、ワークシート“来店状況”のデータから、サービス種別が当該行のサービス種別と一致するデータのサービス時間の平均値を求める次の式を入力し、セルI5〜I6に複写する。
切捨て(c, 0)
(6) セルG10に、ワークシート“来店状況”のデータから、待ち時間順位がセルF10の順位に該当するデータの受付番号を求める次の式を入力し、セルG11〜G14に複写する。
d
(7) セルH10に、ワークシート“来店状況”のデータから、セルG10の受付番号に該当するデータの受付時刻を求める式を入力し、セルH11〜H14に複写する。
(8) セルI10に、ワークシート“来店状況”のデータから、セルG10の受付番号に該当するデータの待ち時間を求める式を入力し、セルI11〜I14に複写する。
bに関する解答群
ア：条件付個数(来店状況!B$3:B$252, >A4)
－条件付個数(来店状況!B$3:B$252, >A5)
イ：条件付個数(来店状況!B$3:B$252, >A4)
－条件付個数(来店状況!B$3:B$252, >B4)
ウ：条件付個数(来店状況!B$3:B$252, >B4)
－条件付個数(来店状況!B$3:B$252, >A4)
エ：条件付個数(来店状況!B$3:B$252, <A4)
－条件付個数(来店状況!B$3:B$252, ≦B4)
オ：条件付個数(来店状況!B$3:B$252, ≦B4)
－条件付個数(来店状況!B$3:B$252, <A4)
cに関する解答群
ア：合計(来店状況!F$3:F$252)／G4 * H4
イ：条件付合計(来店状況!C$3:C$252, =F4, 来店状況!F$3:F$252)／G4
ウ：条件付合計(来店状況!C$3:C$252, =F4, 来店状況!F$3:F$252)／合計(G$4:G$6)
エ：条件付合計(来店状況!F$3:F$252, =F4, 来店状況!C$3:C$252)／G4
オ：条件付合計(来店状況!F$3:F$252, =F4, 来店状況!C$3:C$252)／合計(G$4:G$6)
カ：平均(来店状況!F$3:F$252) * H4
dに関する解答群
ア：照合検索(F10, 来店状況!A$3:A$252, 来店状況!J$3:J$252)
イ：照合検索(F10, 来店状況!J$3:J$252, 来店状況!A$3:A$252)
ウ：垂直照合(順位(F10, 来店状況!H$3:H$252, 1), 来店状況!A$3:J$252, 1, 0)
エ：垂直照合(F10, 来店状況!A$3:J$252, 10, 0)
オ：表引き(来店状況!A$3:A$252, 順位(F10, 来店状況!H$3:H$252, 1), 1)
カ：表引き(来店状況!J$3:J$252, 照合一致(F10, 来店状況!A$3:A$252, 0), 1)
模範解答
b：オ、c：イ、d：イ
解説
解答の導き方
この設問では、時間帯ごとの受付人数、サービス種別ごとの平均サービス時間、待ち時間順位に対応する受付番号を求める式を選びます。まず、それぞれの式が何を数えたり取り出したりする必要があるかを確認します。
b：時間帯ごとの受付人数
本文では、セルA4〜B12に各時間帯の始まりと終わりを入力するとあります。したがって、セルC4では、受付時刻がA4以上、B4以下のデータを数えます。例えば、A4が10:00、B4が10:59なら、10:00から10:59までの受付が対象です。
この範囲に入るデータだけを直接数える代わりに、次のように差を取れます。
- B4以下の受付時刻の個数を数える
- A4より前の受付時刻の個数を引く
A4より前の受付時刻は、最初の個数にも含まれますが、引くことで除かれます。一方、A4以上B4以下の受付時刻は、最初の個数に含まれ、引く個数には含まれません。そのため、時間帯内の受付だけが残ります。
この条件を満たすのは、B4以下の個数からA4未満の個数を引くオです。
c：サービス種別ごとの平均サービス時間
セルI4では、F4に書かれたサービス種別について、サービス時間の平均を求めます。平均は「合計を件数で割った値」です。
- 条件付合計で、C列のサービス種別がF4と一致する行のF列（サービス時間）を合計します。
- その合計を、同じサービス種別の件数であるG4で割ります。
つまり、必要な計算は「該当するサービス時間の合計÷該当する件数」です。これに当てはまるのはイです。
本文では、平均サービス時間を求める式を切捨てで囲むよう指定しています。サービス時間は分単位の整数値なので、平均に小数部分が出た場合は、分の端数を切り捨てます。セルI4に入る式は、次の形です。
切捨て(条件付合計(来店状況!C$3:C$252, =F4, 来店状況!F$3:F$252)／G4, 0)
d：待ち時間順位に対応する受付番号
セルG10では、F10に入っている待ち時間順位に該当する受付番号を求めます。本文では、待ち時間順位は来店状況のJ列、受付番号はA列にあります。
したがって、F10の順位をJ列から探し、同じ行にあるA列の受付番号を取り出します。検索する範囲をJ列、取り出す範囲をA列にする式はイです。
照合検索(F10, 来店状況!J$3:J$252, 来店状況!A$3:A$252)
この問題では、待ち時間が同じデータについて受付番号の小さい方を上位にするため、順位が同じデータはありません。順位を手掛かりにすれば、対応する受付番号を一つに決められます。
誤りやすいポイント
-
時間帯の境界を取り違えること
受付時間帯の始まりと終わりをどちらも含めます。そのため、B4以下の個数からA4未満の個数を引きます。A4以下を引いてしまうと、時間帯の始まりちょうどの受付まで除かれます。 -
平均サービス時間と構成比率を混同すること
平均サービス時間は、その種別のサービス時間の合計を、その種別の件数で割ります。全サービス時間の平均に構成比率を掛けても、種別ごとの平均にはなりません。 -
照合検索で検索範囲と取り出す範囲を逆にすること
今回探す値は待ち時間順位なので、順位のJ列から検索します。見つかった行から受付番号のA列を取り出します。
FAQ
Q: 受付時刻が時間帯の始まりと同じ場合は、受付人数に含まれますか？
A: 含まれます。始まりの時刻より前の個数を引くので、始まりと同じ時刻の受付は差し引かれず、結果に残ります。
A: 含まれます。始まりの時刻より前の個数を引くので、始まりと同じ時刻の受付は差し引かれず、結果に残ります。
Q: なぜ平均サービス時間を件数で割るのですか？
A: 平均は、対象となる値を合計し、その値の個数で割って求めるためです。ここでは、対象となるサービス時間の合計を、同じ種別の件数であるG4で割ります。
A: 平均は、対象となる値を合計し、その値の個数で割って求めるためです。ここでは、対象となるサービス時間の合計を、同じ種別の件数であるG4で割ります。
Q: 待ち時間順位から受付番号を探すとき、なぜA列ではなくJ列を検索するのですか？
A: 探したい値は順位だからです。順位が記録されているJ列で該当行を探し、その行のA列から受付番号を取り出します。
A: 探したい値は順位だからです。順位が記録されているJ列で該当行を探し、その行のA列から受付番号を取り出します。
関連キーワード: 表計算、条件付個数、条件付合計、平均値、照合検索
設問3：
問題文を見る マクロqueue_simulation中のに入れる正しい答えを、解答群の中から選べ。
〔マクロ：queue_simulationの説明〕
待ち時間の短縮策を検討するために、窓口数を変えたときのサービスを行う窓口の窓口番号、開始時刻、終了時刻、待ち時間及び待ち人数の変化をシミュレーションするマクロqueue_simulationを作成し、ワークシート“来店状況”に格納した。シミュレーションに使う窓口数をワークシート“来店状況”のセルB1に入力し、受付番号、受付時刻、サービス種別及びサービス時間は、ワークシート“来店状況”のセルA3～F252の内容をそのまま用いる。窓口数として、1～8を指定できる。
(1) 各窓口でサービスを受けている窓口利用者へのサービスが終了する時刻（以下、前者終了時刻という）を格納する配列end_timeを用意する。配列end_timeの添字の値1～8は窓口番号の1～8と対応する。各要素の初期値として0を格納する。
(2) 処理する行（以下、処理行という）の位置を格納する変数work_lineを用意し、初期値を1とする。
(3) ワークシート“来店状況”の行3のデータから処理を始め、全てのデータの処理が終わるまで、①～④の処理を繰り返す。
① サービスを受ける窓口を決定するために、配列end_timeに格納されている前者終了時刻の中で最小の値をもつ要素を探し、その添字の値を窓口番号として処理行の列Dのセルに格納する。ここで、前者終了時刻が等しい窓口が複数あるときは、窓口番号が最小の窓口を選択する。
② ①で求めた前者終了時刻の最小の値が処理行の列Bの受付時刻よりも小さいときは受付時刻を、そうでなければ①で求めた前者終了時刻の最小の値を、開始時刻として処理行の列Eのセルに格納する。
③ ①で決定した窓口の前者終了時刻を更新するために、配列end_timeの当該窓口に対応する要素に、処理行の列Gの終了時刻を格納する。
④ 変数work_lineに1を加える。
eに関する解答群
ア：min_time < end_time[i]
イ：min_time < end_time[min_no]
ウ：min_time < 相対(A2, work_line, 1)
エ：min_time > end_time[i]
オ：min_time > end_time[min_no]
カ：min_time > 相対(A2, work_line, 1)
fに関する解答群
ア：end_time[i]
イ：end_time[min_no]
ウ：i
エ：min_no
オ：min_time
カ：work_line
gに関する解答群
ア：end_time[min_no] ← min_time
イ：end_time[min_no] ← 相対(A2, work_line, 4)
ウ：end_time[min_no] ← 相対(A2, work_line, 6)
エ：end_time[work_line] ← min_time
オ：end_time[work_line] ← 相対(A2, work_line, 4)
カ：end_time[work_line] ← 相対(A2, work_line, 6)
模範解答
e：エ、f：エ、g：ウ
解説
解答の導き方
このマクロでは、受付番号ごとに、最も早くサービスを始められる窓口を選びます。各窓口について「その窓口でサービスを受けている人のサービスが終わる時刻」を記録し、次の人を割り当てるときに使います。
1. 最も早く空く窓口を探します
配列（番号を使って複数の値を管理する入れ物）には、窓口ごとのサービス終了時刻が入ります。最初は、使う窓口数に応じて各要素に0を入れます。時刻の0は午前0時00分なので、最初の利用者が来る時刻より前です。
窓口を探す処理では、まず窓口番号1の終了時刻を、現在の最小値である とし、窓口番号1を最小値の窓口である にします。その後、窓口番号2から順に調べます。
各窓口の終了時刻 が、現在の最小値 より小さければ、新しい最小値として記録します。つまり、必要な条件は「」です。これに当たるのは、eの選択肢ではエです。
この条件では、終了時刻が同じ窓口が複数あっても最小値を更新しません。窓口番号の小さい順に調べているため、同じ時刻なら先に見つけた、番号の小さい窓口が選ばれます。これは「前者終了時刻が等しい窓口が複数あるときは、窓口番号が最小の窓口を選択する」という説明に合っています。
2. 選んだ窓口番号と開始時刻を記録します
には、最終的に選ばれた窓口番号が入っています。現在の処理行で、基準セルA2から行方向に処理位置分、列方向に3列進んだセルは列Dです。したがって、列Dには を格納します。fは選択肢のエです。
開始時刻は、利用者が受付を済ませた時刻と、選んだ窓口が前のサービスを終える時刻のうち、遅い方です。窓口がすでに空いていれば受付時刻から始められます。まだサービス中なら、前のサービスが終わるまで開始できません。この処理により、列Eには受付時刻より前の時刻や、窓口が空く前の時刻が入らなくなります。
3. 選んだ窓口の終了時刻を更新します
列Gには、列Eの開始時刻に列Fのサービス時間を加えた終了時刻が入ります。次の利用者を割り当てるときに使う終了時刻も、この新しいサービスが終わる時刻に更新する必要があります。
現在の処理行で基準セルA2から列方向に6列進んだセルは列Gです。そのため、選んだ窓口に対応する に、現在の行の列Gの値を格納します。gは選択肢のウです。
例えば、最初の行では全窓口の終了時刻が0なので、窓口番号1が選ばれます。受付時刻は10:02で、サービス時間は0:14です。開始時刻は10:02、終了時刻は10:16となるため、窓口番号1の を10:16に更新します。次の行では、まだ使われていない窓口番号2と3の終了時刻が同じです。この場合は番号の小さい窓口番号2が選ばれます。
誤りやすいポイント
- 最小値を探す条件の向きを逆にしないようにします。候補の終了時刻が現在の最小値より小さい場合に更新するので、条件は です。
- 時刻が同じ窓口が複数あるときは、窓口番号の小さい方を残します。そのため、同じ時刻の場合に最小値を更新する条件にはしません。
- 列Dに入れるのは、調査中の窓口番号 ではなく、最小の終了時刻をもつ窓口番号 です。
- の更新には、開始時刻ではなく終了時刻を使います。サービス時間を反映した列Gの値が必要です。
FAQ
Q: なぜ開始時刻は、受付時刻と窓口の終了時刻を比べるのですか？
A: 利用者は受付時刻より前にはサービスを受けられず、窓口が前のサービス中なら、その終了時刻より前にも始められません。両方の条件を満たすため、遅い方を開始時刻にします。
A: 利用者は受付時刻より前にはサービスを受けられず、窓口が前のサービス中なら、その終了時刻より前にも始められません。両方の条件を満たすため、遅い方を開始時刻にします。
Q: と はどう違いますか？
A: は、窓口を順に調べるときの番号です。 は、調べ終わった後に最も早く空くと判断された窓口の番号です。
A: は、窓口を順に調べるときの番号です。 は、調べ終わった後に最も早く空くと判断された窓口の番号です。
Q: なぜ終了時刻を列Gから取得するのですか？
A: 列Gには、開始時刻にサービス時間を加えた時刻が入るためです。この時刻を次の利用者の割当てに使います。
A: 列Gには、開始時刻にサービス時間を加えた時刻が入るためです。この時刻を次の利用者の割当てに使います。
関連キーワード: 配列、最小値探索、待ち行列、シミュレーション、条件分岐
