基本情報技術者過去問題 平成30年秋期 午後問13

⇄問題文と設問を画面2分割で開く⇱問題PDF⇱表計算ソフトの機能・用語

問13 ソフトウェア開発(表計算)

次の表計算のワークシート及びマクロの説明を読んで,設問1〜3に答えよ。

〔表計算の説明〕
 Z社は,店舗の窓口で自社商品に関する諸手続のサービスを行っている。来店した顧客が受付機のボタンを押すと,1から始まる連番の受付番号を記した受付票が発行される。窓口では,受付番号の順に担当者がPCを用いて業務処理用サーバ(以下,業務サーバという)の機能を利用しながらサービスを行う。最近,顧客へのアンケートで"待ち時間が長い"との意見が増えたので,表計算ソフトを用いて待ち時間の状況などを分析することにした。
 なお,本問において,関数"条件付個数"は,セル範囲に含まれる空白セルでないセルのうち検索条件の記述で指定された条件を満たすセルの個数を返す。

〔ワークシート・来店状況〕
 業務サーバには,顧客に対して行ったサービスに関するデータが受付番号ごとに記録される。この記録から分析に必要なデータを得るために,ある1日のデータを業務サーバから抽出,加工して,ワークシート"来店状況"に入力した。ワークシート"来店状況"の例を,図1に示す。
  • 列A〜Fの行3以降に,業務サーバから抽出した,受付票の発行後にサービスを受けた顧客(以下,窓口利用者という)のデータが入力されている。
    データは,一つの受付番号に関するものが1レコードとして同一行に入力され,受付番号の昇順に整列されている。データの件数は5件以上250件以下であり,最後のデータが入力された行よりも下の行の各セルには空値が格納されている。
  • 業務サーバは,時間を分の単位の整数値で保持し,時刻を午前0時00分からの経過分数として保持する。時間及び時刻の計算にはこの整数値を用いる。時間及び時刻は,hh:mm(時:分)の形式で表示する。
  • 列Aの受付番号は受付票に記された番号であり,列Bの受付時刻はその受付票が発行された時刻である。受付機による受付票の発行は10時00分になると同時に開始し,19時00分になると同時に終了する。
  • 列Cのサービス種別は,窓口利用者に対して行ったサービスの種別であり,A,B,Cの三つがある。全てのサービスは,1日に1回以上利用される。
  • 列Dの窓口番号は,サービスを行った窓口の番号である。窓口は三つあり,それらの窓口番号は1,2,3である。どの窓口でも全ての種別のサービスを行っている。
  • 列Eの開始時刻は窓口でサービスを開始した時刻,列Fのサービス時間はサービスに掛かった時間である。
  • セル G3 に,開始時刻にサービス時間を加えて終了時刻を求める次の式を入力し,セル G4〜G252 に複写する。
      IF(A3=null,null,E3+F3)
  • セル H3 に,受付時刻から開始時刻までの時間(以下,待ち時間という)を求める次の式を入力し,セル H4〜H252 に複写する。
      IF(A3=null,null,E3−B3)
  • セル I3 に,当該行の受付番号以下の受付番号をもつ窓口利用者のうち,当該行の受付時刻にまだサービスが開始されていない人数(以下,待ち人数という)を求める次の式を入力し,セル I4〜I252 に複写する。ここで,待ち時間が0の窓口利用者は,待ち人数に含めない。
      IF(A3=null,null,a)
  • セル J3に,列Hの待ち時間の値を大きい順に順位付けしたときの当該行の順位(以下,待ち時間順位という)を求める次の式を入力し,セル J4〜J252 に複写する。ここで,待ち時間の等しいデータが複数あるときは,受付番号の小さいデータを上位とする。
      IF(A3=null,null,条件付個数(H$3:H$252,>H3)
        +条件付個数(H$3:H3,=H3))

設問1

ワークシート"来店状況"に関する記述中の に入れる正しい答えを,解答群の中から選べ。
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:

解答

  • a=

解説

aについて〕
I列に入力されるのは、受付時点で当該行の受付番号を含めた待ち人数を表示するための式です。受付時点で待っている人数は、受付時刻においてサービスが開始されていない受付記録の数と同じになります。すなわち、開始時刻が当該行の受付記録よりも大きい行の数を数えることになります。

例えば、図1の109行目の受付記録であれば、下記の範囲から受付時刻である18:52より後にサービスが開始された受付記録の数を調べることになります。この操作は、E3〜E109までのセル範囲の中から、B109の値より大きくなっているセルを数えると言い換えることができます。
pm13_4.gif/image-size:557×283
計算式は、I4〜I252までに複写されるので、セル範囲の上側は行位置を固定(E$3)にしておいて、セル範囲の下側は相対参照(E3)にしておけば下方向に複写されただけセル範囲が下に伸びるようになります。したがって、適切なセル範囲は「E$3:E3」です。後は当該行のB列の値よりも大きいセルをカウントしたいので、検索条件として「>B3」を指定します。

a=ウ:条件付個数(E$3:E3,>B3)

※設問1は問題誤りにつき全員正解の措置済みの設問ですが、当サイトでは問題文(9)の「受付番号よりも小さい受付番号」を「受付番号以下の受付番号」に改題することで問題が成立するようにしてあります。
〔ワークシート:分析〕
 ワークシート"来店状況"のデータを基に窓口利用者の状況を分析するために,ワークシート"分析"を作成し,セル A2〜D12 に"時間帯別分析",セル F2〜I6 に "種別分析",セル F9〜I14 に"待ち時間分析(上位5件)"の表を格納した。ワークシート"分析"の例を,図2に示す。
pm13_2.gif/image-size:551×359

設問2

ワークシート"分析"に関する次の記述中の に入れる正しい答えを,解答群の中から選べ。
  • 集計の区分を1時間ごとの時間帯とし,セル A4〜B12 に,各時間帯の始まりの時刻(毎時の00分)と終わりの時刻(毎時の59分)を上から順に入力する。また,セル F4〜F6 にサービスの種別を,セル F10〜F14 に待ち時間の順位を示す数値1〜5を入力する。
  • セル C4 に,ワークシート"来店状況"のデータから,受付時刻が当該行の時間帯にあるデータの個数を求める次の式を入力し,セル C5〜C12 に複写する。
      b
  • セル D4 に,ワークシート"来店状況"のデータから,受付時刻が当該行の時間帯にあるデータの待ち時間の平均値を求める式を入力し,セル D5〜D12 に複写する。ここで,当該行の時間帯の受付人数が0のときの待ち時間の平均値は0とする。
  • セル G4 に,ワークシート"来店状況"のデータから,サービス種別が当該行のサービス種別と一致するデータの件数を求める式を入力し,セル G5〜G6 に複写する。また,セル H4 に全サービス件数に対する当該行の件数の構成比率を求める式を入力し,セル H5〜H6 に複写する。
  • セル I4 に,ワークシート"来店状況"のデータから,サービス種別が当該行のサービス種別と一致するデータのサービス時間の平均値を求める次の式を入力し,セル I5〜I6 に複写する。
      切捨て(c,0)
  • セル G10 に,ワークシート"来店状況"のデータから,待ち時間順位がセル F10 の順位に該当するデータの受付番号を求める次の式を入力し,セル G11〜G14 に複写する。
      d
  • セル H10 に,ワークシート"来店状況"のデータから,セル G10 の受付番号に該当するデータの受付時刻を求める式を入力し,セル H11〜H14 に複写する。
  • セル 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$3J$252,照合一致(F10,来店状況!A$3:A$252,0),1)

解答選択欄

  • b:
  • c:
  • d:

解答

  • b=
  • c=
  • d=

解説

bについて〕
ワークシート"来店状況"のデータから、時間帯ごとに発行した受付番号の個数を"条件付き個数"で集計する処理です。

セルC4の該当時間内というのはA4(**:00)からB4(**:59)までを指すので、受付時間が、B4(終了時間)以下のセル数からA4(開始時間)未満のセル数を引くことで求められます。
正しい指定条件は「(略)≦B4−(略)<A4」ですので、「オ」が適切です。

仮にA列の値が12:00、B列の値が12:59だった場合を考えると各肢の式は次のようになります。
  • 受付時間が12:00より大きいセル数から、13:00より大きいセル数を引いた数になります。12:01〜13:00という時間帯になってしまうので誤りです。
  • 受付時間が12:00より大きいセル数から、12:59よりも大きいセル数を引いた数になります。12:01〜12:59という時間帯になってしまうので誤りです。
  • 受付時間が12:59よりも大きいセル数から、12:00よりも大きいセル数を引いた数になります。値がマイナス(若しくは0)になってしまうので誤りです。
  • 受付時間が12:00より小さいセル数から、12:59以下のセル数を引いた数になります。値がマイナス(若しくは0)になってしまうので誤りです。
  • 正しい。
cについて〕
今回求められているI4(平均サービス時間)、種別ごとにサービス時間を合計し、件数で条件付き合計で割ることで算出できます。したがって"条件付合計"を用いて、ワークシート"来店状況"の各行から、C列(サービス種別)の値がワークシート"分析"のF4(サービス種別)と一致する行のサービス時間だけを合計した後に、G4(件数)で割る式が正解です。
  • ワークシート"来店状況"のC列(サービス種別)を絞り込んでいないのと、さらにH4(構成比率)を指定するのは誤りです。
  • 正しい。
  • "条件付合計"でサービス種別ごとの平均値を求めた後、全件数(G$4:G$6)で割っているので、正しい平均サービス時間は得られません。
  • "条件付合計"の条件式としてサービス種別(F4)と平均サービス時間(F4)を比較することになるので誤りです。
  • 「ウ」と同様の理由で誤りです。
  • 待ち時間の平均値にH4(構成比率)を乗じても、正しい平均サービス時間は得られません。
dについて〕
待ち時間分析(上位5件)の、F10(順位)に相当するレコードのG10(受付番号)を求める式を選択します。
ワークシート"来店状況"J列には待ち時間順位が格納されているので、この値がワークシート"分析"F10〜F14(順位)と一致する行を検索することになります。この操作は"照合検索"を用いて、ワークシート"来店状況"J列から、ワークシート"分析"F10(順位)に一致するレコードを検索し、結果行のワークシート"来店状況"A列(受付番号)の値を表示することで実現可能です。したがって「イ」が適切です。
  • 検索範囲と結果範囲が逆なので誤りです。
  • 正しい。
  • "来店状況"H列(待ち時間)を対象範囲として順位の値(1〜5)の順位を求めているため誤りです。
  • "垂直照合"ではセル範囲の左端列を上から検索します。この式では順位の値を受付番号列から検索することとなるため誤りです。
  • "順位"内で、F10(順位)を基に順位を抽出しようとしていますが、適切な行番号は得られないため誤りです。
  • "照合一致"内で、順位の値(1〜5)を受付番号列から検索しているため誤りです。
〔マクロ:queue_simulation の説明〕
 待ち時間の短縮策を検討するために,窓口数を変えたときのサービスを行う窓口の窓口番号,開始時刻,終了時刻,待ち時間及び待ち人数の変化をシミュレーションするマクロ queue_simulation を作成し,ワークシート"来店状況"に格納した。シミュレーションに使う窓口数をワークシート"来店状況"のセル B1 に入力し,受付番号,受付時刻,サービス種別及びサービス時間は,ワークシート"来店状況"のセル A3〜F252 の内容をそのまま用いる。窓口数として,1〜8を指定できる。
  • 各窓口でサービスを受けている窓口利用者へのサービスが終了する時刻(以下,前者終了時刻という)を格納する配列 end_time を用意する。配列 end_time の添字の値1〜8は窓口番号の1〜8と対応する。各要素の初期値として0を格納する。
  • 処理する行(以下,処理行という)の位置を格納する変数 work_line を用意し,初期値を1とする。
  • ワークシート"来店状況"の行3のデータから処理を始め,全てのデータの処理が終わるまで,①〜④の処理を繰り返す。
    1. サービスを受ける窓口を決定するために,配列 end_time に格納されている前者終了時刻の中で,最小の値をもつ要素を探し,その添字の値を窓口番号として処理行の列Dのセルに格納する。ここで,前者終了時刻が等しい窓口が複数あるときは,窓口番号が最小の窓口を選択する。
    2. ①で求めた前者終了時刻の最小の値が処理行の列Bの受付時刻よりも小さいときは受付時刻を,そうでなければ①で求めた前者終了時刻の最小の値を,開始時刻として処理行の列Eのセルに格納する。
    3. ①で決定した窓口の前者終了時刻を更新するために,配列 end_time の当該窓口に対応する要素に,処理行の列Gの終了時刻を格納する。
    4. 変数 work_lineに1を加える。

設問3

マクロ queue_simulation 中の に入れる正しい答えを,解答群の中から選べ。

pm13_3.gif/image-size:416×538
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:

解答

  • e=
  • f=
  • g=

解説

eについて〕
分岐条件が入ります。分岐先では、min_time と min_no を更新する処理が行われています。

ここで行う処理は、「①サービスを受ける窓口を決定するために,配列 end_time に格納されている前者終了時刻の中で最小の値をもつ要素を探す」ことです。
カウンタ変数 i をループさせ、その間 end_time[i] の値が min_time より小さいか否かを比較します。小さいと判断した場合は min_time を end_time[i] で、min_no を i で更新することで、ループ終了時には min_time に最小の前者終了時刻が、min_no にはその要素番号(窓口番号)が格納されることとなります。

2つの変数を更新する条件を正しく指定している「エ」の「min_time > end_time[i]」が正解です。

「ア」「イ」「ウ」は、不等号が逆のため誤りです。
「オ」は、比較対象の添字が min_no です。min_time と end_time[min_no]は同じ時刻を示すので結果は常に偽となり、最小の前者終了時刻の窓口を得ることはできません。
「カ」は、受付時刻と前者終了時刻を比較することとなるため誤りです。

fについて〕
まず、fの代入先である「相対(A2, work_line, 3)」について考えます。列位置に3が指定されているので、D列、すなわち窓口番号を格納すべきあると判断できます。

問題文を読むと、この処理は「その添字の値を,窓口番号として処理行の列Dのセルに格納する」に該当するとわかります。配列 end_time の添字の値1〜8は窓口番号の1〜8と対応するため、前段のループ処理で求めた min_no をD列の値として格納するのが適切です。したがって「エ」が正解です。

「ア」「イ」「オ」は、時刻データですので誤りです。
「ウ」は、ループ終了後のiの値、つまり値が常に「B1(窓口数)+1」となるため誤りです。
「カ」は、処理行を示す変数なので誤りです。

gについて〕
③の「配列 end_time の当該窓口に対応する要素に,処理行の列Gの終了時刻を格納する」処理を行います。

当該行を処理する窓口番号は min_no に格納されているのですから、代入先となるのは end_time[min_no] です。処理行の列Gの終了時刻の値は「相対(A2,work_line,6)」で得られるので、「ウ」の「end_time[min_no] ← 相対(A2,work_line,6)」が適切です。

「エ」「オ」「カ」は、代入先を end_time[work_line] としているため誤りです。end_time は要素数9つの配列なので、work_lineを指定すると範囲外を参照することになります。
「ア」は、ループ処理で求めた最小の前者終了時刻を代入しているため誤りです。
「イ」は、相対の列位置に4が指定されており、当該行のE列(開始時刻)の値を代入しているため誤りです。

平成30年秋期 午後問題一覧

問1 問2 問3 問4 問5 問6 問7 問8 問9 問10 問11 問12 問13 採点講評
© 2010-2024 基本情報技術者試験ドットコム All Rights Reserved.

Pagetop