基本情報技術者 2018年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
Z社は現在、表計算ソフトを用いた会議室の予約システムを作成中である。会議室は15室あり、それぞれ一意の会議室番号が振られている。また、収容可能人数（以下、定員という）は同一ではなく、スクリーンがある会議室とない会議室がある。会議室の利用は、9時から21時まで可能である。予約システムは、ワークシート“会議室選定”とワークシート“予約リスト”で構成される。
〔ワークシート：会議室選定〕
はじめに、ワークシート“会議室選定”を作成した。ワークシート“会議室選定”の例を、図1に示す。
(1) セルA2〜A16には、会議室番号が入力されている。セルB2〜B16には、会議室の定員が入力されている。セルC2〜C16には、スクリーンがある会議室であれば“有”が、そうでなければ“無”が入力されている。
(2) 予約したい会議室の条件を、セルA19〜E19に入力する。セルA19に会議室の利用日を、セルB19に開始時刻を、セルC19に終了時刻を入力する。セルD19には、利用人数を入力する。セルE19には、スクリーンの利用を希望する場合は“要”を、そうでない場合は“不要”を入力する。終了時刻は開始時刻よりも後でなければならない。日付はYYYYMMDD、時刻はhhmmの形式で表現される整数値である。
(3) (2)で条件を入力した後、ワークシート“会議室選定”に格納されているマクロSelectRoomを実行すると、ワークシート“予約リスト”に格納されている予約の一覧を参照し、会議室ごとに、条件に指定した利用日の開始時刻から終了時刻までの間（開始時刻及び終了時刻を含まない）に他の予約が入っていない場合は“可”を、そうでなければ“否”を、セルD2〜D16に格納する。
(4) セルE2〜E16には、対応する会議室の収容率を求める式が入力されている。ここで収容率は、次の式で求める。
収容率＝利用人数／定員
ただし、利用人数が定員を超える場合、スクリーンの利用を希望しているのに会議室にスクリーンがない場合、又は当該行の列Dの値が“否”の場合は、収容率は0にする。
(5) セルA22には、推奨会議室の会議室番号を表示する式が入力されている。ここで、推奨会議室とは、収容率が最も大きな正の値となる会議室である。ただし、この収容率が等しい会議室が複数あるとき、それらの中で表の最も上に位置する会議室番号を表示する。また、条件を満たす会議室がないときには、“なし”を表示する。
〔ワークシート：予約リスト〕
予約の一覧は、ワークシート“予約リスト”に格納されている。ワークシート“予約リスト”の例を、図2に示す。
(1) 1件の予約のデータが1行に入力されている。列Aには、予約ごとに一意となる予約IDが格納されている。
(2) 列Bには利用日、列Cには開始時刻、列Dには終了時刻、列Eには利用人数が格納されている。日付はYYYYMMDD、時刻はhhmmの形式で表現される整数値である。
(3) 列Fには、スクリーンの利用を希望する場合は“要”が、そうでない場合は“不要”が格納されている。
(4) 列Gには、予約された会議室番号が格納されている。
(5) 予約のデータは、利用日の昇順に常に整列されている。
(6) 新しい予約のデータを登録するには、ワークシート“予約リスト”に格納されているマクロRegisterを実行する。また、予約総数は9,998件までとし、データの最終行よりも下の行の列A〜Gの各セルには空値が格納されている。
(7) 会議室の予約は9時から21時まで行うことができる。21時を過ぎたら直ちに、ワークシート“予約リスト”に格納されているマクロUpdateを実行する。マクロUpdateは、利用が終了した予約のデータを予約の一覧から消去するために、その予約のデータの入った行より下の行の予約のデータを上方向に詰める。
(8) マクロRegister, Update, SelectRoomは、いずれもその実行中に他のマクロが実行されることはない。
設問1：
問題文を見る ワークシート“会議室選定”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
(1) 次の式をセルE2に入力し、セルE3〜E16に複写する。
IF(論理和(a),0,D$19／B2)
(2) 次の式を、セルA22に入力する。
IF(b,'なし',c)
aに関する解答群
ア：論理積(E$19='要',C2='有'),D2='否',D$19 > B2
イ：論理積(E$19='要',C2='無'),D2='否',D$19 > B2
ウ：論理積(E$19='要',C2='有',D2='否'),D$19 > B2
エ：論理積(E$19='要',C2='無',D2='否'),D$19 > B2
オ：論理積(E$19='不要',C2='有'),D2='否',D$19 > B2
カ：論理積(E$19='不要',C2='無'),D2='否',D$19 > B2
キ：論理積(E$19='不要',C2='有',D2='否'),D$19 > B2
ク：論理積(E$19='不要',C2='無',D2='否'),D$19 > B2
bに関する解答群
ア：最小(E2:E16) = 0
イ：最小(E2:E16) = 1
ウ：最小(E2:E16) > 0
エ：最小(E2:E16) < 1
オ：最大(E2:E16) = 0
カ：最大(E2:E16) = 1
キ：最大(E2:E16) > 0
ク：最大(E2:E16) < 1
cに関する解答群
ア：照合一致(最小(E2:E16),E2:E16,0)
イ：照合一致(最大(E2:E16),E2:E16,0)
ウ：照合検索(最小(E2:E16),E2:E16,A2:A16)
エ：照合検索(最大(E2:E16),E2:E16,A2:A16)
オ：表引き(A2:A16,順位(最小(E2:E16),E2:E16,0),1)
カ：表引き(A2:A16,順位(最大(E2:E16),E2:E16,0),1)
模範解答
a：イ、b：オ、c：エ
解説
解答の導き方
この設問では、会議室ごとの収容率を求める式と、収容率を使って推奨会議室を選ぶ式を考えます。
-
a：収容率を0にする条件を整理します。問題文では、利用人数が定員を超える場合、スクリーンが必要なのに会議室にスクリーンがない場合、使用可否が「否」の場合に、収容率を0にするとしています。セルE2の式は、条件に当てはまるときに0を返し、当てはまらないときに利用人数を定員で割ります。つまり、0にする条件は「どれか一つでも当てはまる」場合です。そのため、全体を論理和でまとめます。スクリーンについては、セルE19が「要」で、会議室のスクリーンが「無」のときに条件に当てはまります。この二つが同時に成り立つ必要があるので、ここだけを論理積でまとめます。したがって、論理和の中身は次の三つです。
- 論理積(E$19='要',C2='無')：スクリーンが必要なのに、会議室にない
- D2='否'：予約できる状態ではない
- D$19 > B2：利用人数が定員を超えている
よって、aにはイが入ります。セルE3〜E16に式を複写するため、条件入力のセルE19、D19では行番号を固定し、会議室ごとに変わるC2、D2、B2の行番号は固定しません。 -
b：「なし」と表示する条件を考えます。セルA22の式は、最初の条件が真なら「なし」を表示し、そうでなければ推奨会議室を表示します。収容率は、条件に合わない会議室なら0です。条件に合う会議室では「利用人数／定員」なので正の値になります。また、利用人数が定員を超える会議室は0にされます。つまり、収容率はすべて0以上です。そのため、条件を満たす会議室が一つもないときは、収容率の最大値も0になります。最小値が0かどうかでは、ほかに正の収容率があるかを判断できません。よって、bには「最大(E2:E16) = 0」を選び、オが入ります。
-
c：最大の収容率に対応する会議室番号を求めます。最大の収容率を持つ会議室の番号を表示するには、まずE2:E16から最大値を探し、その値に対応する会議室番号をA2:A16から取得します。「照合検索」は、検索範囲で値を探し、同じ位置にある対応範囲の値を返す関数です。したがって、最大値をE2:E16で探し、対応する会議室番号をA2:A16から取得する「照合検索(最大(E2:E16),E2:E16,A2:A16)」が適切です。最大の収容率が複数ある場合も、検索範囲で最初に見つかった値に対応する会議室番号が返ります。E2:E16は表の上から下に並んでいるため、これは表の最も上にある会議室番号です。よって、cにはエが入ります。
誤りやすいポイント
- スクリーンが必要な場合に条件となるのは、「要」と「無」の組合せです。「要」と「有」ではありません。
- 収容率を0にする三つの条件は、どれか一つでも当てはまればよい条件です。一方、「スクリーンが必要」と「スクリーンがない」は、同時に成り立つ必要があります。
- 「なし」とするかの判断には、最小値ではなく最大値を使います。最小値が0でも、別の会議室に正の収容率がある場合があるためです。
- 推奨会議室は、収容率が最小の会議室ではなく、最大の正の値を持つ会議室です。
- 最大値と同じ収容率の会議室が複数ある場合は、表の上から順に探したときに最初に見つかる会議室番号が返ることを確認します。
FAQ
Q: スクリーンの利用希望が「不要」の場合、スクリーンがない会議室は選べませんか？
A: 選べます。スクリーンがないことが条件になるのは、スクリーンの利用希望が「要」の場合だけです。
A: 選べます。スクリーンがないことが条件になるのは、スクリーンの利用希望が「要」の場合だけです。
Q: 最大値が0なら、なぜ条件を満たす会議室がないと判断できるのですか？
A: 収容率は0か正の値で、負の値にはなりません。最大値が0なら、どの会議室の収容率も0なので、正の収容率を持つ会議室がありません。
A: 収容率は0か正の値で、負の値にはなりません。最大値が0なら、どの会議室の収容率も0なので、正の収容率を持つ会議室がありません。
Q: 最大の収容率が同じ会議室がある場合、どの会議室番号が返りますか？
A: 検索範囲で最初に見つかった値に対応する番号が返ります。表では上の行から探すため、最も上の会議室番号になります。
A: 検索範囲で最初に見つかった値に対応する番号が返ります。表では上の行から探すため、最も上の会議室番号になります。
関連キーワード: 論理和、論理積、条件分岐、複合参照、検索関数
設問2：
問題文を見る マクロSelectRoom中のに入れる正しい答えを、解答群の中から選べ。
dに関する解答群
ア：論理積(表引き(予約リスト!C2:C10000, J, 1) ≧ C19,表引き(予約リスト!D2:D10000, J, 1) ≦ B19)
イ：論理積(表引き(予約リスト!C2:C10000, J, 1) ≦ C19,表引き(予約リスト!D2:D10000, J, 1) ≧ B19)
ウ：論理積(表引き(予約リスト!D2:D10000, J, 1) ≧ C19,表引き(予約リスト!C2:C10000, J, 1) ≦ B19)
エ：論理積(表引き(予約リスト!D2:D10000, J, 1) ≦ C19,表引き(予約リスト!C2:C10000, J, 1) ≧ B19)
オ：論理和(表引き(予約リスト!C2:C10000, J, 1) ≧ C19,表引き(予約リスト!D2:D10000, J, 1) ≦ B19)
カ：論理和(表引き(予約リスト!C2:C10000, J, 1) ≦ C19,表引き(予約リスト!D2:D10000, J, 1) ≧ B19)
キ：論理和(表引き(予約リスト!D2:D10000, J, 1) ≧ C19,表引き(予約リスト!C2:C10000, J, 1) ≦ B19)
ク：論理和(表引き(予約リスト!D2:D10000, J, 1) ≦ C19,表引き(予約リスト!C2:C10000, J, 1) ≧ B19)
eに関する解答群
ア：相対(D2, J - 1, 0)
イ：相対(D2, J, 0)
ウ：相対(D2,照合一致(表引き(予約リスト!G2:G10000, J, 1), A2:A16, 0) - 1, 0)
エ：相対(D2,照合一致(表引き(予約リスト!G2:G10000, J, 1), 予約リスト!G2:G10000, 0) - 1, 0)
オ：相対(D2,照合一致(予約リスト!G2, A2:A16, 0) - 1, 0)
カ：相対(D2,照合一致(予約リスト!G2, A2:A16, 1) - 1, 0)
fに関する解答群
ア：表引き(予約リスト!B2:B10000, J, 1) = A19
イ：表引き(予約リスト!B2:B10000, J, 1) = null
ウ：表引き(予約リスト!B2:B10000, J, 1) ≠ A19
エ：表引き(予約リスト!B2:B10000, J, 1) ≠ null
オ：表引き(予約リスト!C2:C10000, J, 1) < B19
カ：表引き(予約リスト!D2:D10000, J, 1) > C19
模範解答
d：オ、e：ウ、f：ア
解説
解答の導き方
このマクロでは、会議室ごとの使用可否をいったん「可」にし、希望日に入っている予約を順に調べます。予約時間が重なる会議室があれば、その会議室を「否」にします。
1. 予約時間が重なる条件を考えます
希望する時間帯は、セルB19の開始時刻からセルC19の終了時刻までです。一方、予約リストの各予約は、列Cが開始時刻、列Dが終了時刻です。
予約時間が重ならないのは、次のどちらかの場合です。
- 既存予約の開始時刻が、希望する終了時刻以降
- 既存予約の終了時刻が、希望する開始時刻以前
したがって、重ならない条件は「どちらか一方が成り立つ」という論理和です。選択肢では、既存予約の開始時刻がC19以上、または既存予約の終了時刻がB19以下となっているオが該当します。
マクロはこの条件を「否定」しています。つまり、重ならない条件が成り立たない場合、すなわち予約時間が重なる場合に、会議室を「否」にします。
希望時間が1300から1530までの場合を考えると、既存予約が1300から1530なら、重ならない条件はどちらも成り立ちません。否定すると「重なる」と判断され、その予約の会議室が「否」になります。一方、既存予約が900から1100なら、既存予約の終了時刻が希望する開始時刻以前なので、時間は重なりません。
2. 重なった予約の会議室に対応するセルを特定します
予約リストの列Gには、予約された会議室番号が入っています。この会議室番号を、会議室選定のA2:A16から探し、対応する行の列Dに「否」を入れる必要があります。
照合一致は、指定した値と一致する値を探す処理です。列Gから現在調べている予約の会議室番号を取り出し、A2:A16から完全一致で探すと、その会議室が表の何番目にあるかが分かります。
相対(D2, 行数, 0)は、セルD2から指定した行数だけ移動したセルを表します。たとえば、会議室番号がA2にあれば移動は0行、A3にあれば移動は1行です。したがって、列Gの会議室番号をA2:A16で探し、その位置からD列のセルを決めるウが適切です。
ここで、予約リスト上の位置を表すJを、そのまま会議室選定の行位置として使うことはできません。Jは予約リスト内の予約の位置だからです。会議室番号を会議室選定の表で改めて探す必要があります。
3. 同じ利用日の予約だけを調べます
予約リストは利用日の昇順に並んでいます。そのため、A19の利用日と一致する最初の予約を見つけた後、Jを1ずつ増やせば、その日の予約を続けて調べられます。同じ利用日の予約は連続して並ぶため、列Bの日付がA19と一致する間は処理を続け、別の日付になったら終了します。
よって、繰返しを続ける条件は「列Bの日付がA19と等しい」です。これに該当するのはアです。
マクロ全体では、会議室ごとに列Dを「可」にした後、その日の予約を調べ、時間が重なる予約があれば、その予約の会議室に対応する列Dを「否」にします。予約がない日なら、初期設定の「可」のままになります。
誤りやすいポイント
- dは「予約時間が重なる条件」ではなく、「重ならない条件」を指定します。擬似言語ではその条件を否定しているため、重なるときに「否」になります。
- 「重ならない」は、予約が希望時間より後の場合だけではありません。予約が希望時間より前の場合も含むので、条件は論理積ではなく論理和です。
- eで使うJは、予約リスト内の予約位置です。会議室選定の行を指定するには、列Gの会議室番号をA2:A16から探します。
- fでは、列Bの日付がA19と一致する間だけ調べます。日付順に並んでいるので、日付が変わった時点でその日の予約をすべて確認したことになります。
FAQ
Q: 予約の終了時刻と希望の開始時刻が同じ場合は、時間が重なりますか？
A: 重なりません。予約の終了時刻が希望の開始時刻以前なら、予約は希望時間の前に終わっています。そのため、dの「重ならない条件」に含まれます。
A: 重なりません。予約の終了時刻が希望の開始時刻以前なら、予約は希望時間の前に終わっています。そのため、dの「重ならない条件」に含まれます。
Q: なぜ会議室番号をA2:A16から探し直すのですか？
A: Jは予約リストの中で何番目の予約かを表し、会議室選定の表の行位置とは関係がないからです。予約リストの列Gにある会議室番号から、会議室選定の該当行を特定します。
A: Jは予約リストの中で何番目の予約かを表し、会議室選定の表の行位置とは関係がないからです。予約リストの列Gにある会議室番号から、会議室選定の該当行を特定します。
Q: fで時刻ではなく日付を調べるのはなぜですか？
A: その日の予約をまとめて読み進めるためです。各予約の時刻が希望時間と重なるかどうかはdで調べ、fでは「まだ同じ利用日の予約が続いているか」を確認します。
A: その日の予約をまとめて読み進めるためです。各予約の時刻が希望時間と重なるかどうかはdで調べ、fでは「まだ同じ利用日の予約が続いているか」を確認します。
関連キーワード: 論理和、条件分岐、繰返し処理、完全一致検索、相対参照
