基本情報技術者 2017年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1〜3に答えよ。
〔表計算の説明〕
S社では、社内の業務用PC（以下、PCという）からインターネットへの接続に、社内に設置したProxyサーバ（以下、サーバという）を利用している。サーバの運用担当であるTさんは、社内から社外のWebページへのアクセス状況を確認するために、サーバのアクセスログを分析することにした。
〔ワークシート：ログ〕
ワークシート“ログ”には、2017年4月3日（月）から2017年4月30日（日）までの4週間に社外のWebページにアクセスした記録だけをアクセスログから取り出し、集計に必要な加工を行った結果が入力されている。ワークシート“ログ”の例を、図1に示す。
(1) 行1は見出し行で、データは行2以降に入力されている。
(2) 列Aには、PCがサーバにアクセスした時刻が入力されている。ここで、時刻は、基準となる1970年1月1日（木曜日）の0時00分から当該時刻までの分を単位とする経過時間で表現する。
(3) 列Bには、社外のWebページにアクセスしたPCのIPアドレスが入力されている。
(4) 列Cには、アクセスしたWebページのURLが入力されている。
(5) 列Dには、列Aの時刻に対応する時間帯（0～23）を表示する式が入力されている。ここで、時間帯は、0時台（0時00分～0時59分）を0、1時台（1時00分～1時59分）を1、…、23時台（23時00分～23時59分）を23とする整数値である。
(6) 列Eには、列Aの時刻に対応する曜日を0～6の整数値で表現する曜日コードを表示する式が入力されている。ここで、曜日コードは、基準となる1970年1月1日の曜日である木曜日を0とし、水曜日の6までを順に割り振る。
(7) 列Fには、時間帯と曜日コードをつないで一つの文字列とした集計キーを表示する式が入力されている。
(8) データは最大9,998件までとし、データの最終行よりも下の行の列A～Cの各セルには空値が入力されている。
〔ワークシート：曜日〕
曜日と曜日コードとの対応を、ワークシート“曜日”に入力する。ワークシート“曜日”を、図2に示す。
設問1：
問題文を見る ワークシート“ログ”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。ここで、セルD2～F9999に入力する各式は、当該行の列Aのセルの値が空値であれば空値を返す。
(1) セルD2には、セルA2の時刻に対応する時間帯を表示する次の式を入力する。
a
(2) セルE2には、セルA2の時刻に対応する曜日コードを表示する次の式を入力する。ここで、1440は、1日を分に換算した値（24 × 60）である。
IF(A2 = null, null, 剰余(切捨て(A2／1440, 0), 7))
(3) セルF2には、セルD2とセルE2の値をつないで、一つの文字列として返す次の式を入力する。
IF(A2 = null, null, 結合(D2, E2))
(4) セル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：カ
解説
解答の導き方
求めるのは、セルA2に記録された時刻が何時台かを表す式です。
問題文では、時刻を「基準となる1970年1月1日（木曜日）の0時00分から当該時刻までの分を単位とする経過時間」で表すとあります。つまり、セルA2の値は「基準の時刻から何分たったか」を示しています。
時間帯を求めるには、次の順で計算します。
-
60で割って、経過時間を時間に直します。
1時間は60分なので、で経過した時間数が分かります。 -
小数部分を切り捨てます。
例えば、ある時刻が10時台の途中なら、経過時間には「10時間と少し」のように小数部分が生じます。時間帯は整数で表すため、切捨てで整数にします。 -
24で割った余りを求めます。
経過時間が何時間であっても、1日は24時間です。24時間ごとに時刻が0時に戻るので、24で割った余りを使えば、0～23の時間帯になります。
したがって、計算部分は「切捨て(A2／60, 0)を24で割った余り」です。選択肢では、空値なら空値を返す処理も含めて、この計算を行うカが当てはまります。
例として、図1のセルA2の値24852960を使って確かめます。
- 時間
- 414216を24で割った余りは0
よって、セルD2に表示される時間帯は0です。図1の行2の値とも一致します。
一方、曜日コードを求める式では、1日分の分数である1440で割り、経過した日数を求めてから7で割った余りを使います。時間帯は「1時間を表す60分」と「1日を表す24時間」を使うため、曜日コードの計算とは割る数や余りの取り方が異なります。
誤りやすいポイント
- 分を時間に直すには60で割ります。24で割ると、時間数に直せません。
- 時間帯は0～23なので、余りを求める数は24です。60で割った余りでは、時間帯ではなく別の値になります。
- 経過時間に小数部分があるときは、切り捨てて現在の時間帯を求めます。切上げると、次の時間帯になってしまう場合があります。
- 1440は1日分の分数です。これを使う計算は経過日数を求めるときに使い、時間帯を直接求める計算には使いません。
- IFでA2が空値かどうかを確認する処理も必要です。空値のときに計算を進めず、空値を返すためです。
FAQ
Q: なぜ経過した時間数を24で割った余りにするのですか？
A: 1日が24時間で、24時間たつと時刻が0時に戻るからです。経過した時間数を24で割った余りを使うと、0～23の範囲で時間帯を表せます。
A: 1日が24時間で、24時間たつと時刻が0時に戻るからです。経過した時間数を24で割った余りを使うと、0～23の範囲で時間帯を表せます。
Q: 曜日コードの式では、なぜ7で割った余りを使うのですか？
A: 曜日は7日で一周するためです。経過した日数を7で割った余りを使うと、基準となる曜日から何曜日分進んだかを求められます。
A: 曜日は7日で一周するためです。経過した日数を7で割った余りを使うと、基準となる曜日から何曜日分進んだかを求められます。
Q: IFで空値を確認するのはなぜですか？
A: データがない行では、時間帯も表示しないためです。空値を確認せず計算すると、データのない行に数値が表示されることがあります。
A: データがない行では、時間帯も表示しないためです。空値を確認せず計算すると、データのない行に数値が表示されることがあります。
関連キーワード: 表計算、経過時間、切捨て、剰余、曜日コード
設問2：
問題文を見る ワークシート“集計”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：集計〕
ワークシート“集計”には、“アクセス集計表”及び“アクセス分析表”を作成する。ワークシート“集計”の例を、図3に示す。
(1) “アクセス集計表”は、ワークシート“ログ”を参照して、社外のWebページにアクセスした回数（以下、アクセス回数という）を、該当する曜日、時間帯ごとに集計して表示する。
(2) “アクセス分析表”は、アクセス回数が、最多となる時間帯と最少となる時間帯、及び最多となる曜日と最少となる曜日を表示する。
(3) セルA3～A9には、曜日として“月”～“日”を入力する。
(4) セルB2～Y2には、時間帯として0～23を入力する。
(5) セルB3には、ワークシート“ログ”のデータの中から、曜日と時間帯がそれぞれ、当該セルと同じ行の列Aで示す曜日と、同じ列の行2で示す時間帯に一致するデータの個数を数えて表示する次の式を入力して、セルB3～Y9に複写する。
条件付個数(ログ!$F$2:$F$9999, b)
(6) セルZ3には、当該セルと同じ行の列B～Yの値を合計する式を入力し、セルZ4～Z9に複写する。
(7) セルB10には、当該セルと同じ列の行3～9の値を合計する式を入力し、セルC10～Z10に複写する。
(8) セルD14には、アクセス回数を時間帯ごとに集計したときに、その値が最多となる時間帯を表示する式を入力し、セルD15にはその値が最少となる時間帯を表示する式を入力する。ここで、最多、最少となる時間帯が複数あったときは、そのうちアクセス集計表の左端に最も近い時間帯を表示する。
(9) セル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, 照合一致(最大(Z3:Z9), Z3:Z9, 0))
模範解答
b：ウ、c：イ
解説
解答の導き方
この設問では、ログの各データに付けられた集計キーを使い、曜日と時間帯が一致するアクセス回数を数えます。また、曜日ごとの合計から最多の曜日を探します。
-
集計キーに使う値を確認します。ワークシート“ログ”の列Fには、「時間帯と曜日コードをつないで一つの文字列とした集計キー」が入っています。図1では、時間帯が0、曜日コードが4の行の集計キーは「04」です。また、時間帯が23、曜日コードが3の行では「233」です。つまり、集計キーは 時間帯を先に、曜日コードを後に つないだ文字列です。したがって、セルB3に必要なキーも、時間帯と曜日コードの順につくります。
-
曜日名から曜日コードを調べます。セルA3には曜日名が入っています。一方、ログの集計キーに必要なのは曜日コードです。そこで、ワークシート“曜日”の表から、曜日名に対応するコードを探します。図2では、曜日名が上の行、曜日コードがその下の行にあります。水平照合（横方向に並んだ値を探し、指定した行の値を返す関数）を使い、セルA3の曜日名を1行目から探して、2行目の曜日コードを取り出します。最後の「0」は、完全一致する曜日名を探す指定です。
-
時間帯と曜日コードをつなぎます。結合（値を文字列としてつなぐ関数）で、時間帯と曜日コードをつなぎます。セルB3の場合、時間帯は0で、セルA3の曜日名「月」に対応する曜日コードは4です。そのため、集計キーは「04」になります。この処理に当たるのは、解答群のウです。式の中では、B$2から時間帯を取り出し、水平照合で曜日コードを調べて後ろにつないでいます。BA3の「2は行2を固定するので、右に複写すると列だけが変わります。$A3は列Aを固定するので、下に複写すると行だけが変わります。これにより、B3～Y9へ複写しても、各セルの時間帯と曜日を正しく参照できます。
-
集計キーと一致するデータの個数を数えます。条件付個数（指定した範囲から条件に合うセルの個数を数える関数）で、ワークシート“ログ”のF列から、作成した集計キーと一致するセルを数えます。対象範囲はログのデータが入る可能性があるF2～F9999です。セルB3なら、集計キー「04」と一致する件数が数えられます。右や下へ複写すれば、時間帯や曜日に応じたキーに変わり、曜日別・時間帯別のアクセス回数が表に入ります。
-
曜日ごとの最多を調べます。図3では、曜日ごとの合計がZ3～Z9に、対応する曜日名がA3～A9に並んでいます。最大の曜日を探すには、まず最大(Z3:Z9)で曜日別合計の最大値を求めます。次に、照合検索（検索範囲から値を探し、同じ位置にある対応範囲の値を返す関数）で、その値に対応する曜日名をA3～A9から取り出します。したがって、セルD17に使う式は解答群のイです。図3の数値では、曜日別合計の最大値は1820で、その行の曜日は木です。最大値が複数ある場合も、Z3～Z9は上から曜日順に並んでいるため、最初に見つかった行の曜日が選ばれます。なお、時間帯ごとの最多を求める場合は、曜日別合計のZ3～Z9ではなく、時間帯別合計のB10～Y10を調べます。ここでは曜日の最多を求めるため、曜日別合計を使うことがポイントです。最少となる曜日も同じ考え方で、曜日別合計の最小値に対応する曜日名を取り出します。
誤りやすいポイント
-
曜日名の位置と曜日コードを混同すること
“曜日”の表では、曜日名の並び順と曜日コードの値は同じではありません。例えば、月は曜日名の行では左から5番目ですが、曜日コードは4です。位置ではなく、表の下段にあるコードを取り出します。 -
集計キーを逆の順番でつなぐこと
ログの集計キーは、時間帯の後ろに曜日コードをつなぎます。曜日コードを先にすると、ログの値と一致しません。 -
曜日別と時間帯別の合計範囲を取り違えること
曜日別の合計はZ3～Z9、時間帯別の合計はB10～Y10です。どちらの最多を求めるかに応じて、正しい範囲を選びます。 -
式を複写したときの参照位置を考えないこと
曜日を下方向に変える参照と、時間帯を右方向に変える参照を適切に固定しないと、複写後に意図しないセルを参照します。
FAQ
Q: 水平照合の最後にある「0」は何を指定していますか？
A: 曜日名を完全一致で探す指定です。似た値や別の位置の値ではなく、セルA3と同じ曜日名を探します。
A: 曜日名を完全一致で探す指定です。似た値や別の位置の値ではなく、セルA3と同じ曜日名を探します。
Q: 曜日コードを直接計算せず、ワークシート“曜日”を使うのはなぜですか？
A: 曜日名と曜日コードの対応を表から調べることで、曜日の並び順をコードと取り違えずに済むためです。
A: 曜日名と曜日コードの対応を表から調べることで、曜日の並び順をコードと取り違えずに済むためです。
Q: 最少となる曜日も、最多となる曜日と同じ方法で求められますか？
A: はい。曜日別合計の範囲を使い、最大値の代わりに最小値に対応する曜日名を取り出します。
A: はい。曜日別合計の範囲を使い、最大値の代わりに最小値に対応する曜日名を取り出します。
関連キーワード: 表計算関数、水平照合、完全一致検索、文字列結合、絶対参照
設問3：
問題文を見る Tさんは、ワークシート“ログ”のデータから、アクセス先のURLごとのアクセス回数を求めるマクロPage_countを作成し、ワークシート“アクセス先集計”に格納した。マクロPage_count中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：アクセス先集計〕
マクロ実行後のワークシート“アクセス先集計”の例を、図4に示す。
(1) 行1は見出し行である。
(2) マクロ実行前のセルA2〜B9999には、あらかじめ空値を入力しておく。
(3) マクロPage_countの実行結果は、行2以降に表示される。
〔マクロ：Page_countの説明〕
(1) ワークシート“ログ”のセルC2を最初の対象セルとして、以降、セルC3、C4、…と順次、対象セルを下に移しながら、対象セルの値が空値になるまで、(2)の処理を繰り返す。
(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：ア
解説
解答の導き方
このマクロは、ログに記録されたURLを1件ずつ確認し、同じURLが集計表にあればアクセス回数を増やします。まだ集計表にないURLなら、新しい行に登録します。URL（Uniform Resource Locator：Webページなどの場所を示す文字列）ごとに、アクセス回数を数える処理です。
まず、ログのどのセルを確認するかを見ます。マクロは、最初に「log_line ← 1」としています。次に参照するセルは「相対(ログ!A1, log_line, 2)」です。相対参照とは、基準となるセルから指定した行数・列数だけ移動したセルを指す方法です。ここではログのA1を基準に、1行下、2列右のC2を参照します。その後、log_lineを1ずつ増やすことで、C3、C4と順に確認します。対象セルが空値になったら、ログの確認を終えます。
次に、集計表の中からログのURLを探します。最初は「count_line ← 1」なので、集計表のA1から1行下にあるA2を確認します。count_urlには、確認中のセルにあるURLが入ります。
同じURLが見つからず、まだ空値にも着いていない間は、検索を続けます。つまり、count_urlが空値ではなく、かつlog_urlとも異なることが条件です。この条件に当たるのはdのウです。条件が成り立つたびにcount_lineを1増やし、集計表の次の行を確認します。
検索は、次のどちらかの状態になると終わります。
- count_urlがlog_urlと同じになり、同じURLが見つかる
- count_urlが空値になり、同じURLが見つからないまま登録用の行に着く
検索後に、count_urlが空値かどうかを判定します。空値なら、その行にはまだURLが登録されていません。そこで、列Aにlog_urlを入れ、列Bにアクセス回数の初期値である1を入れます。空値かどうかを調べる条件は「count_url = null」なので、eのイです。
空値ではない場合は、検索中に同じURLが見つかったということです。その場合は、見つかった行の列Bにあるアクセス回数を1増やします。A1からcount_line行下、1列右にあるセルは列Bです。この処理に当たるのはfのアです。
誤りやすいポイント
- 検索を続ける条件と、新しいURLを登録する条件は別です。検索を続けるのは、集計表のURLが空値ではなく、log_urlとも異なる場合です。
- 空値の行に着いたら、検索を続けず、そこにURLを登録します。空値は、まだURLが登録されていないことを示します。
- 同じURLがすでに見つかった場合は、新しい行を作りません。既存の行のアクセス回数だけを1増やします。
- log_lineはログの行を進める変数、count_lineは集計表で検索する行を進める変数です。役割を取り違えないようにします。
FAQ
Q: 同じURLが見つかった後も、集計表の下の行を調べますか？
A: 調べません。同じURLが見つかった時点で、その行のアクセス回数を増やせばよいからです。
A: 調べません。同じURLが見つかった時点で、その行のアクセス回数を増やせばよいからです。
Q: 空値の行に着いたら、列Bの値を1増やすのですか？
A: いいえ。その行にはまだURLが登録されていません。列AにURLを入れ、列Bに初期値の1を設定します。
A: いいえ。その行にはまだURLが登録されていません。列AにURLを入れ、列Bに初期値の1を設定します。
Q: count_lineを1増やすのはなぜですか？
A: 集計表の次の行を調べるためです。最初はA2を確認し、count_lineを1増やすとA3を確認します。
A: 集計表の次の行を調べるためです。最初はA2を確認し、count_lineを1増やすとA3を確認します。
関連キーワード: 表計算、相対参照、条件判定、繰返し処理、検索
