基本情報技術者過去問題平成29年秋期午後問13(ソフトウェア開発(表計算))

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

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

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

〔表計算の説明〕
　S社では，社内の業務用PC(以下，PCという)からインターネットへの接続に，社内に設置したProxyサーバ(以下，サーバという)を利用している。サーバの運用担当であるTさんは，社内から社外のWebページへのアクセス状況を確認するために，サーバのアクセスログを分析することにした。

〔ワークシート：ログ〕
　ワークシート"ログ"には，2017年4月3日(月)から2017年4月30日(日)までの4週間に社外のWebページにアクセスした記録だけをアクセスログから取り出し，集計に必要な加工を行った結果が入力されている。ワークシート"ログ"の例を，図1に示す。

行1は見出し行で，データは行2以降に入力されている。
列Aには，PCがサーバにアクセスした時刻が入力されている。ここで，時刻は，基準となる1970年1月1日(木曜日)の0時00分から当該時刻までの分を単位とする経過時間で表現する。
列Bには，社外のWebページにアクセスしたPCのIPアドレスが入力されている。
列Cには，アクセスしたWebページのURLが入力されている。
列Dには，列Aの時刻に対応する時間帯(0～23)を表示する式が入力されている。ここで，時間帯は，0時台(0時00分～0時59分)を0，1時台(1時00分～1時59分)を1，…，23時台(23時00分～23時59分)を23とする整数値である。
列Eには，列Aの時刻に対応する曜日を0～6の整数値で表現する曜日コードを表示する式が入力されている。ここで，曜日コードは，基準となる1970年1月1日の曜日である木曜日を0とし，水曜日の6までを順に割り振る。
列Fには，時間帯と曜日コードをつないで一つの文字列とした集計キーを表示する式が入力されている。
データは最大9998件までとし，データの最終行よりも下の行の列A～Cの各セルには空値が入力されている。

〔ワークシート:曜日〕
　曜日と曜日コードとの対応を，ワークシート"曜日"に入力する。ワークシート"曜日"を，図2に示す。

設問1

ワークシート"ログ"に関する次の記述中の　に入れる正しい答えを，解答群の中から選べ。ここで，セル D2～F9999 に入力する各式は，当該行の列Aのセルの値が空値であれば空値を返す。

セル D2 には，セル A2 の時刻に対応する時間帯を表示する次の式を入力する。
　a
セル E2 には，セル A2 の時刻に対応する曜日コードを表示する次の式を入力する。ここで，1440は，1日を分に換算した値(24×60)である。
　IF(A2＝null，null，剰余(切捨て(A2／1440，0)，7))
セル F2 には，セル D2 とセル E2 の値をつないで，一つの文字列として返す次の式を入力する。
　IF(A2＝null，null，結合(D2，E2))
セル D2，E2，F2 に入力した式を，行3～9999 の対応する列のセルに複写する。

a に関する解答群

IF(A2＝null,null,切上げ(剰余(A2／24,60),0))
IF(A2＝null,null,切上げ(剰余(A2,60)／1440,0))
IF(A2＝null,null,切捨て(剰余(A2／24,60),0))
IF(A2＝null,null,切捨て(剰余(A2,60)／1440,0))
IF(A2＝null,null,剰余(切捨て(A2／1440,0),60))
IF(A2＝null,null,剰余(切捨て(A2／60,0),24))

解答選択欄

解答

a=カ

解説

本文中の記述「入力する式は当該行の列Aのセルの値が空値であれば空値を返す」という処理は、下のIF関数を用いることで実現できます。

IF A2「時刻」が空白
　真の場合：何も表示しない。
　偽の場合：処理aを行う。

〔aについて〕
A列(時刻)には、PCがサーバにアクセスした時刻が入力されています。

この数値は、基準となる1970年1月1日(木曜日)0時00分から当該時刻(サーバアクセス)までを経過時間を、分単位で表現したものです。D2(時間帯)には、時間帯(0～23の値)を表示するので分単位から時間帯の値に変換する式が必要になります。

まず、A2の数値を時単位に変換するため60(分)で除算します。今回は時単位を求められているので、割り切れなかった小数部（分の部分）は切り捨てるのが正しいです。

次に、時単位に変換した数値を24時間単位(0～23)に変換するため、24で割った余りの数値を結果値とします。もし24で割り切れるならちょうど0(時台)とみなすことができ、1が余れば1(時)、2が余れば2(時)…23が余れば23(時)となるので、D2(時間帯)の条件を満たす数値となるからです。

A2(時刻)÷60(分) → 切捨て(A2／60, 0)
算出した経過時間÷24 → 剰余(①, 24)

正しい式は、②剰余(①切捨て(A2／60, 0), 24)となるので、「カ」が正解です。

「ア」「イ」は、切上げ関数を使用するので誤りです。
「ウ」「エ」は、①と②の入れ子が逆転しているので誤りです。
「オ」は、各オペランドに1440を除算することで結果が不正になるため誤りです。

設問2

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

〔ワークシート：集計〕
　ワークシート"集計"には，"アクセス集計表"及び"アクセス分析表"を作成する。ワークシート"集計"の例を，図3に示す。

"アクセス集計表"は，ワークシート"ログ"を参照して，社外のWebページにアクセスした回数(以下，アクセス回数という)を，該当する曜日，時間帯ごとに集計して表示する。
"アクセス分析表"は，アクセス回数が，最多となる時間帯と最少となる時間帯，及び最多となる曜日と最少となる曜日を表示する。
セル A3～A9 には，曜日として"月"～"日"を入力する。
セル B2～Y2 には，時間帯として0～23を入力する。
セル B3 には，ワークシート"ログ"のデータの中から，曜日と時間帯がそれぞれ，当該セルと同じ行の列Aで示す曜日と，同じ列の行2で示す時間帯に一致するデータの個数を数えて表示する次の式を入力して，セル B3～Y9 に複写する。
　条件付個数(ログ!$F$2:$F$9999,b)
セル Z3 には，当該セルと同じ行の列B～Yの値を合計する式を入力し，セル Z4～Z9 に複写する。
セル B10 には，当該セルと同じ列の行3～9の値を合計する式を入力し，セル C10～Z10 に複写する。
セル D14 には，アクセス回数を時間帯ごとに集計したときに，その値が最多となる時間帯を表示する式を入力し，セル D15 にはその値が最少となる時間帯を表示する式を入力する。ここで，最多，最少となる時間帯が複数あったときは，そのうちアクセス集計表の左端に最も近い時間帯を表示する。
セル D17 には，アクセス回数を曜日ごとに集計したときに，その値が最多となる曜日を，"月"～"日"で表示する次の式を入力する。
　c
　セル D18 には，アクセス回数を曜日ごとに集計したときに，その値が最少となる曜日を，"月" ～"日"で表示する式を入力する。
　ここで，最多，最少となる曜日が複数あったときは，そのうちアクセス集計表の上端に最も近い曜日を表示する。

b に関する解答群

＝結合(B$2,照合一致($A3,曜日!$B$1:$H$1,0))
＝結合(B$2,照合検索($A3,曜日!$B$2:$H$2,曜日!$B$1:$H$1))
＝結合(B$2,水平照合($A3,曜日!$B$1:$H$2,2,0))
＝結合(照合一致($A3,曜日!$B$1:$H$1,0),B$2)
＝結合(照合検索($A3,曜日!$B$2:$H$2,曜日!$B$1:$H$1),B$2)
＝結合(水平照合($A3,曜日!$B$1:$H$2,2,0),B$2)

c に関する解答群

照合検索(最大(B10:Y10),B10:Y10,B2:Y2)
照合検索(最大(Z3:Z9),Z3:Z9,A3:A9)
照合検索(照合一致(最大(Z3:Z9),Z3:Z9,0)－1,曜日!B2:H2,曜日!B1:H1)
表引き(A3:Z9,1,照合一致(最大(Z3:Z9),Z3:Z9,0))
表引き(A3:Z9,照合一致(最大(Z3:Z9),Z3:Z9,0),
　　照合一致(最大(B10:Y10),B10:Y10,0))
表引き(曜日!B1:H1,1,照合一致(最大(A3:Z9),Z3:Z9,0))

解答選択欄

解答

b=ウ
c=イ

解説

〔bについて〕
セルB3に時間帯ごと曜日ごとのアクセス数を表示する"条件付き個数"の式のうちbに入る式を選択します。

条件付個数(ログ!$F$2:$F$9999,b)

ワークシート"ログ"のF列には集計キーとして時間帯と曜日コードを結合した文字列が格納されているので、ワークシート"集計"のセルごとに、このキーに合致する行を数えることとなります。例えばセルB3の場合には、時間帯が"0"、曜日コードが4(月曜日)ですから、F列から集計キーが"04"のセルを数えることとなります。

"結合"関数は、引数として与えられた文字列を連結させた文字列を返します。セルB3の場合で言えば、時間帯を表す文字はワークシート"集計"のB2に格納されています。集計キーは「時間帯＋曜日」のフォーマットになっているので、B$2を文字列の後半に指定している「エ」「オ」「カ」は誤りと判断できます。

次に曜日を表す文字列($A3)から曜日コードを取得する式を考えます。照合一致、照合検索、水平照合の動作をもとに正誤を判断すると、

照合一致($A3,曜日!$B$1:$H$1,0): 照合一致は、セルの値ではなくセルの相対的な位置情報を返します。よって誤りです。
照合検索($A3,曜日!$B$2:$H$2,曜日!$B$1:$H$1): 曜日コードを検索して曜日の文字列を返す指定になってしまっています。結果として得たいのは曜日コードですので検索範囲と抽出範囲の指定が逆です。
水平照合($A3,曜日!$B$1:$H$2,2,0): ワークシート"曜日"をB1,C1,…,H1の順に曜日が一致するセルを探し、セル範囲のうち当該列の2行目を取得します。これにより適切な曜日コードが得られるので正解です。

したがって適切な式は「ウ」です。

〔cについて〕
D17の「最多となる曜日」は、Z3～Z9の中から最大の数値を持つ行を取得し、その行の曜日列の値を取得すれば表示可能です。
手順としては次のようになります。

最大値(Z3～Z9)で、Z3～Z9の中から最大の数値を取得する
①の値をもとにZ3～Z9を検索し、一致する行を取得する
②で一致した行の、A列(A3～A9)の値を取得する

この動作を実現するのが"照合検索"を用いた「イ」の式です。

「ア」は、範囲の指定がすべて誤りです。
「ウ」は、照合一致はセルの相対的な位置情報を返しますが、照合検索の第1引数には最大値を指定する必要があるため誤りです。
「エ」「オ」「カ」は、「表引き(A3:A9, 照合一致(最大(Z3:Z9), Z3:Z9, 0), 1)」という式であれば抽出可能ですが、どちらも該当しないので誤りです。

設問3

Tさんは，ワークシート"ログ"のデータから，アクセス先のURLごとのアクセス回数を求めるマクロ Page_count を作成し，ワークシート"アクセス先集計"に格納した。マクロ Page_count 中の　に入れる正しい答えを，解答群の中から選べ。

〔ワークシート：アクセス先集計〕
　マクロ実行後のワークシート"アクセス先集計"の例を，図4に示す。

行1は見出し行である。
マクロ実行前のセルA2～B9999には，あらかじめ空値を入力しておく。
マクロ Page_count の実行結果は，行2以降に表示される。

〔マクロ：Page_countの説明〕

ワークシート"ログ"のセル C2 を最初の対象セルとして，以降，セル C3，C4，…と順次，対象セルを下に移しながら，対象セルの値が空値になるまで，(2)の処理を繰り返す。
ワークシート"ログ"の対象セルに入力されているURLと同じ値が入力されているかどうかを，ワークシート"アクセス先集計"のセル A2 から下に検索し，同じURLを値としてもつセルが見つかったときは，当該行の列Bのアクセス回数に1を加える。列Aのセルの値が空値になるまで検索しても同じURLを値としてもつセルが見つからなかったときは，最初に現れた空値の行の，列Aに対象セルの値を，列Bに1を格納する。

d に関する解答群

論理積(count_url＝null，count_url＝log_url)
論理積(count_url＝null，count_url≠log_url)
論理積(count_url≠null，count_url≠log_url)
論理和(count_url＝null，count_url＝log_url)
論理和(count_url＝null，count_url≠log_url)
論理和(count_url≠null，count_url≠log_url)

e に関する解答群

count_url＝log_url
count_url＝null
count_url≠null
log_url＝null
log_url≠null

f に関する解答群

相対(A1，count_line，1) ← 相対(A1，count_line，1)＋1
相対(A1，count_line，1) ← 相対(ログ!A1，log_line，2)
相対(A1，count_line，2) ← 相対(A1，count_line，2)＋1
相対(A1，count_line，2) ← 相対(ログ!A1，log_line，2)
相対(A1，log_line，1) ← 相対(A1，log_line，1)＋1
相対(A1，log_line，2) ← 相対(A1，log_line，2)＋1

解答選択欄

解答

d=ウ
e=イ
f=ア

解説

マクロ Page_count では、整数型である変数 log_line と count_line をカウンタ変数として反復処理を行います。

[メインループ]
(1)の説明にあるように、ワークシート"ログ"のセルC2を起点にC3, C4,…と上から順に走査し、URLの同値比較を行います。この表は空行がそのデータの終端を意味するので、比較対象セル(C列のlog_line行目)の値が空(NULL)になるまで繰り返す式になっています。

[サブループ]
(2)の説明にあるように、出力先であるワークシート"アクセス先集計"の列を上から順に走査し、URLの同値比較を行います。

〔dについて〕
サブループの継続条件が入ります。サブループでは、現在検索対象となっているURLがワークシート"アクセス集計"のA列に存在するかどうかをチェックする処理です。ループを終了する条件は以下の2つのいずれかです。

同じURLが見つかった場合
最終行まで検索しても同じURLが見つからなかった場合

現在検索対象となっているURLは log_url に、次の行のURLは count_url に格納されているので、ループを継続するのは次の2つを共に満たす場合となります。

次行のURLが検索対象のURlではない: count_url ≠ log_url
次行が空行ではない: count_url ≠ null

この2つの論理式を論理積で囲んだ条件式が適切です。したがって正解は「ウ」となります。

〔eについて〕
分岐処理の条件式が入ります。
この処理は、〔マクロ：Page_countの説明〕(2)の「同じURLが見つかったときは…，見つからなかったときは…」の分岐に該当します。eが真であるときは、count_line行目のセルにURLと1を格納しています。これは「最初に現れた空値の行の列Aに対象セルの値を，列Bに1を格納する」処理に該当します。つまり、eには「列Aのセルの値が空値になるまで検索しても同じURLを値としてもつセルが見つからなかった」ときに真となる条件式を選択しなければなりません。

ワークシート"アクセス集計"を最終行まで走査しても同じURLが見つからなかったときは、「count_url = null」となってサブループを抜けているはずです。よって、これを指定にすれば、同じURLが見つからなかったときに真となる条件式となります。したがって「イ」が正解です。

「ア」「ウ」は、同じURLが見つかった場合に真となる条件式なので誤りです。
「エ」は、常に偽となるため誤りです。
「オ」は、常に真となるため誤りです。

〔fについて〕
eが偽の場合、つまり同じURLが見つかった場合に行う処理を選択します。ここで行う処理は、〔マクロ：Page_countの説明〕(2)の「当該行の列Bのアクセス回数に1を加える」です。
同じURLが見つかった場合には、count_line にワークシート"アクセス集計"内で現在処理中の行位置が格納されています。当該行の列Bは「相対(A1，count_line，1)」で表せるので、相対(A1，count_line，1)を「現在値＋1」で更新する「ア」の式が適切です。

「イ」「エ」は、ワークシート"ログ"のセル内容を格納するので誤りです。
「ウ」「カ」は、格納先の列位置がC列となるため誤りです。
「オ」「カ」は、相対の第二引数にワークシート"ログ"内の現在行位置を保持する log_line を指定しているため誤りです。

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

問1　問2　問3　問4　問5　問6　問7　問8　問9　問10　問11　問12　問13　採点講評