基本情報技術者 2012年 秋期 午後 問13
次の表計算、ワークシート及びマクロの説明を読んで、設問1～3に答えよ。
〔表計算の説明〕
S市では、急激な人口増加に伴い小学校の新設を検討している。そこで表計算ソフトを用いて候補地選定に関するシミュレーションを行うことにした。
(1) 図1のように、縦横が同じ長さの升目（以下、メッシュという）でS市を分割する。また、候補地選定では、処理を簡略にするために、それぞれのメッシュ内に住んでいる全ての小学生（以下、児童という）は、メッシュの中心点から同じ小学校に通学するとみなす。
(2) S市をメッシュで分割したときの左上を原点、水平方向右向きをx軸の正の向き、垂直方向下向きをy軸の正の向き、メッシュの1辺の長さを1とする2次元座標系を定義する。このとき、S市を分割したメッシュの個数は70であった。
(3) 各メッシュを区別するためにIDを設定する。左上の座標が(x, y)であるメッシュのIDをx + 100yとする。ここで、全てのメッシュの左上のx座標は0以上100未満の整数、y座標は0以上の整数とする。
〔ワークシート：児童数分布表〕
S市の各メッシュの中心点の座標、メッシュ内に住んでいる児童数、小学校が配置可能なメッシュであるかどうかを記録する、図2のワークシート“児童数分布表”を作成した。
(1) セルA2～A71には、メッシュのIDを入力する。
(2) セルB2～B71には、該当するメッシュの中心点のx座標を求める式を、セルC2～C71には、該当するメッシュの中心点のy座標を求める式を入力する。
(3) セルD2～D71には、該当するメッシュ内に住んでいる児童数を入力する。
(4) セルE2～E71には、小学校が設置可能なメッシュのとき“*”を入力する。
設問1：
問題文を見る ワークシート“児童数分布表”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
各メッシュの中心点のx座標を求めるために、次の式をセルB2に入力し、セルB3～B71に複写する。
a
各メッシュの中心点のy座標を求めるために、次の式をセルC2に入力し、セルC3～C71に複写する。
b
a, bに関する解答群
ア：A2／100 + 0.5
イ：剰余(10, A2)／10 + 0.5
ウ：剰余(100, A2) + 0.5
エ：剰余(A2, 10)／10 + 0.5
オ：剰余(A2, 100) + 0.5
カ：整数部((A2 + 0.5)／100)
キ：整数部(A2／100) + 0.5
ク：整数部(A2／100 + 0.5)
模範解答
a：オ、b：キ
解説
解答の導き方
各メッシュのIDは、左上の座標を とすると「」です。また、メッシュの1辺の長さは1なので、中心点は左上から横にも縦にも 進んだ位置です。したがって、中心点の座標は になります。
IDから と を取り出す方法を考えます。IDを100で割ると、
です。 は0以上100未満の整数なので、 は0以上1未満です。このため、IDを100で割った値の整数部は になります。一方、IDを100で割った余りは です。
よって、中心点の座標は「IDを100で割った余りに を足した値」です。選択肢では「剰余(A2, 100) + 0.5」に当たるので、aはオです。
中心点の座標は「IDを100で割った値の整数部に を足した値」です。選択肢では「整数部(A2／100) + 0.5」に当たるので、bはキです。
誤りやすいポイント
- IDを10で割った余りから座標を求めると、が2桁の場合に正しく取り出せません。IDは「」で作られているため、100で割った余りを使います。
- IDを100で割った値そのものをとするのではなく、整数部を使います。割った値にはの部分も含まれるためです。
- を足し忘れると、メッシュの左上の座標になってしまいます。求めるのはメッシュの中心点の座標です。
FAQ
Q: なぜ100で割った余りがになるのですか？
A: IDはで、は100未満です。は100で割り切れるため、余りとして残るのはです。
A: IDはで、は100未満です。は100で割り切れるため、余りとして残るのはです。
Q: なぜ中心点の座標にを足すのですか？
A: 左上の座標からメッシュの中央までは、横にも縦にも辺の長さの半分だけ進むためです。辺の長さが1なので、半分はです。
A: 左上の座標からメッシュの中央までは、横にも縦にも辺の長さの半分だけ進むためです。辺の長さが1なので、半分はです。
関連キーワード: 座標、剰余、整数部、メッシュ、表計算関数
設問2：
問題文を見る ワークシート“小学校配置”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：小学校配置〕
S市には現在、A小学校、B小学校、C小学校の3小学校が設置されているが、既に定員を超過している状態である。そこで、定員超過を是正するために小学校を新設することになった。図3に示すように、新設小学校の座標を指定することで、各小学校に通学することになる児童数（以下、対象児童数という）が予測できるワークシート“小学校配置”を作成した。
(1) セルB1～E1には、各小学校の名称を入力する。セルB2～D3には、既設の各小学校の座標及び座標を入力する。セルE2、E3には、新設小学校の座標及び座標を入力する。
(2) セルA12～A81には、ワークシート“児童数分布表”のセルA2～A71を複写する。
(3) セルB12～E81には、メッシュの中心点から各小学校までの距離を求める式を入力する。座標(, )と(, )の距離は次式で求められる。
(4) セルF12～F81には、対応するメッシュ内に住んでいる児童が現在通学している小学校（現通学小学校という）の名称を入力する。3小学校のうち最も距離が短い小学校に通学しているとは限らない。
(5) セルG12～G81には、小学校を新設した場合、対応するメッシュ内に住んでいる児童が通学すべき小学校の名称を求める式を入力する。新設小学校までの距離が、現通学小学校までの距離よりも短いメッシュに住んでいる児童は、新設小学校へ転校することになる。
(6) セルB4～E4には、各小学校が配置されているメッシュのIDを求める式を入力する。
(7) セルB5～E5には、小学校をセルE2、E3で示す場所に新設する場合の各小学校の対象児童数を求める式を入力する。
(8) セルB6～E6には、各小学校の定員を入力する。
(9) セルB7～E7には、各小学校の対象児童数を定員で割った値（充足率という）を求める式を入力する。
(10) セルB9～D9には、制約条件として、各小学校の対象児童数が定員を超えていなければ空値に、定員を超えていれば“×”になる式を入力する。
(11) セルE9には、制約条件として、次の条件を全て満たしていれば空値に、一つでも条件を満たさない場合は“×”になる式を入力する。
条件1：小学校の座標が設置可能なメッシュに含まれている。
条件2：小学校の対象児童数が定員を超えていない。
条件3：小学校を配置するメッシュ内に既設小学校が存在しない。
(12) セルF9には、既設3小学校及び新設小学校の全てが制約条件を満たしていれば空値に、そうでなければ“×”になる式を入力する。
各メッシュの中心点から各小学校までの距離を算出するために、次の式をセルB12に入力し、セルB12～E81に複写する。
c
小学校の新設後、新たに通学することになる小学校（新通学小学校という）を求めるために、次の式をセルG12に入力し、セルG13～G81に複写する。
d
各小学校が配置されているメッシュのIDを算出するために、次の式をセルB4に入力し、セルC4～E4に複写する。
整数部(B2) + 整数部(B3) * 100
対象児童数を算出するために、次の式をセルB5に入力し、セルC5～E5に複写する。
e
充足率を算出するために、次の式をセルB7に入力し、セルC7～E7に複写する。
B5／B6
既設3小学校が制約条件を満たしているかどうかを判定するために、次の式をセルB9に入力し、セルC9、D9に複写する。
IF(B5 ≦ B6, null, '×')
新設小学校が制約条件を満たしているかどうかを判定するために、次の式をセルE9に入力する。
IF(f, null, '×')
全ての小学校が制約条件を満たしているかどうかを判定するために、次の式をセルF9に入力する。
IF(論理積(B9 = null, C9 = null, D9 = null, E9 = null), null, '×')
cに関する解答群
ア：平方根( (児童数分布表!$B2 - B$2)^2 + (児童数分布表!$C2 - B$3)^2 )
イ：平方根( (児童数分布表!$B2 - B2)^2 + (児童数分布表!$C2 - B3)^2 )
ウ：平方根( (児童数分布表!B2 - B$2)^2 + (児童数分布表!C2 - B$3)^2 )
エ：平方根( (児童数分布表!B2 - B2)^2 + (児童数分布表!C2 - B3)^2 )
オ：(平方根(児童数分布表!$B2 - B$2) + 平方根(児童数分布表!$C2 - B$3))^2
カ：(平方根(児童数分布表!$B2 - B2) + 平方根(児童数分布表!$C2 - B3))^2
キ：(平方根(児童数分布表!B2 - B$2) + 平方根(児童数分布表!C2 - B$3))^2
ク：(平方根(児童数分布表!B2 - B2) + 平方根(児童数分布表!C2 - B3))^2
dに関する解答群
ア：IF(最小(B12～D12) ≦ E12, E$1, F12)
イ：IF(最小(B12～D12) ≦ E12, F12, E$1)
ウ：IF(照合一致(F12, B$1～D$1, 0) ≦ E12, E$1, F12)
エ：IF(照合一致(F12, B$1～D$1, 0) ≦ E12, F12, E$1)
オ：IF(照合検索(F12, B$1～D$1, B12～D12) ≦ E12, E$1, F12)
カ：IF(照合検索(F12, B$1～D$1, B12～D12) ≦ E12, F12, E$1)
キ：IF(水平照合(F12, B1～D12, 12, 0) ≦ E12, E$1, F12)
ク：IF(水平照合(F12, B1～D12, 12, 0) ≦ E12, F12, E$1)
eに関する解答群
ア：条件付合計($F12～$F81, = B1, 児童数分布表!$A2～$A71)
イ：条件付合計($F12～$F81, = B1, 児童数分布表!$D2～$D71)
ウ：条件付合計($F12～$F81, ≠ B1, 児童数分布表!$A2～$A71)
エ：条件付合計($F12～$F81, ≠ B1, 児童数分布表!$D2～$D71)
オ：条件付合計($G12～$G81, = B1, 児童数分布表!$A2～$A71)
カ：条件付合計($G12～$G81, = B1, 児童数分布表!$D2～$D71)
キ：条件付合計($G12～$G81, ≠ B1, 児童数分布表!$A2～$A71)
ク：条件付合計($G12～$G81, ≠ B1, 児童数分布表!$D2～$D71)
fに関する解答群
ア：論理積(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) = '*', E5 ≦ E6, E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)
イ：論理積(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) = '*', E5 ≦ E6,否定(論理積(E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)))
ウ：論理積(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) ≠ '*', E5 ≦ E6, E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)
エ：論理積(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) ≠ '*', E5 ≦ E6,否定(論理積(E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)))
オ：論理和(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) = '*', E5 ≦ E6,論理積(E4 ≠ B4, E4 ≠ C4, E4 ≠ D4))
カ：論理和(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) = '*', E5 ≦ E6, E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)
キ：論理和(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) ≠ '*', E5 ≦ E6,論理積(E4 ≠ B4, E4 ≠ C4, E4 ≠ D4))
ク：論理和(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) ≠ '*', E5 ≦ E6, E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)
模範解答
c：ア、d：カ、e：カ、f：ア
解説
解答の導き方
この設問では、メッシュから小学校までの距離、新設後の通学先、対象児童数、新設小学校の設置条件を順に考えます。
式を複写するときは、セル参照の動き方に注意します。相対参照は、式を複写した場所に合わせて行や列が変わる参照です。絶対参照は、$を付けた行や列を固定する参照です。
c：各メッシュから各小学校までの距離
問題文では、2点の距離を、座標の差をそれぞれ二乗して足し、その平方根を求める式で計算しています。メッシュの中心点の座標と、小学校の座標を使えばよいことが分かります。
セルB12の式を右へ複写すると、距離を求める小学校がB列からC列、D列、E列へ変わります。下へ複写すると、対象となるメッシュの行が変わります。したがって、メッシュ側は列を固定し、小学校側は行を固定します。
- メッシュ側の参照：児童数分布表のB列、C列は固定し、行は複写先に合わせて変えます。
- 小学校側の参照：行は固定し、列は複写先に合わせて変えます。
この参照の形を使い、距離を求める式になっているのはアです。
d：新設後の通学先
問題文には、新設小学校までの距離が現通学小学校までの距離より短い場合に転校するとあります。また、現在の通学先は、3小学校のうち最も近い小学校とは限りません。
そこで、まずF12の現通学小学校名をB1～D1から探し、その学校に対応する距離をB12～D12から取り出します。照合検索は、ある値を探し、その値に対応する別の値を取り出す関数です。
取り出した現通学小学校までの距離がE12以下なら、現在の通学先を維持します。新設小学校までの距離がそれより短い場合にだけ、新設小学校へ通学先を変更します。距離が同じ場合は「短い」場合ではないため、現在の通学先のままです。
この判定を表す式は次のとおりです。
IF(照合検索(F12, B$1～D$1, B12～D12) ≦ E12, F12, E$1)
式を下方向に複写しても、学校名が入力された1行目は変わらないように固定します。この式に当てはまるのはカです。
e：各小学校の対象児童数
対象児童数は、新設後の通学先を示すG列を使って集計します。F列は現在の通学先なので、F列を使うと、新設後に転校する児童を反映できません。
条件付合計は、条件に合う行の数値を合計する関数です。ここでは、G12～G81の通学先がB1の小学校名と一致する行について、「児童数分布表」のD2～D71にある児童数を合計します。A列はメッシュIDなので、合計する範囲には使いません。
式は次のとおりです。
条件付合計($G12～$G81, = B1, 児童数分布表!$D2～$D71)
B5の式を右へ複写すると、条件に使う小学校名はB1からC1、D1、E1へ変わります。したがって、eはカです。
f：新設小学校の設置条件
E9の式では、設置条件を全て満たしているときに空値を返し、一つでも満たさないときに“×”を返します。したがって、IFの条件には「全ての設置条件を満たしているか」を指定します。
条件を一つずつ確認します。
-
小学校を設置できるメッシュであること
垂直照合は、表の先頭列から値を探し、指定した列の値を取り出す関数です。E4のメッシュIDを「児童数分布表」のA列から探し、5列目の設置可能欄を取り出します。その値が“*”なら設置可能です。最後の0は、メッシュIDが完全に一致する行を探す指定です。 -
対象児童数が定員を超えないこと
E5がE6以下であることを確認します。 -
既設小学校と同じメッシュではないこと
E4がB4、C4、D4のどれとも異なる必要があります。つまり、3つの不等号が全て成り立つ必要があります。
この3条件を全て満たす必要があるため、論理積でまとめます。論理積は、指定した条件が全て真の場合に真となる処理です。
論理積(垂直照合(E4, 児童数分布表!$A2～$E71, 5, 0) = '*', E5 ≦ E6, E4 ≠ B4, E4 ≠ C4, E4 ≠ D4)
この形に当てはまるのはアです。
誤りやすいポイント
-
cでセル参照の固定を間違える
メッシュの座標は、下へ複写すると行が変わります。小学校の座標は、右へ複写すると列が変わります。固定する行と列を取り違えないようにします。 -
dで最も近い既設小学校を選ぶ
現通学小学校は、最も近い小学校とは限りません。F列の学校名から現在の通学先を特定し、その学校までの距離を使います。 -
dで距離が同じときに新設小学校へ変更する
転校するのは、新設小学校までの距離が現通学小学校までの距離より短い場合です。同じ距離なら、現通学小学校のままです。 -
eで集計する列を間違える
新設後の通学先を示すG列で条件を判定し、児童数分布表のD列の児童数を合計します。 -
fで条件の一部だけを確認する
設置条件は3つ全てを満たす必要があります。一つでも満たさなければ“×”になるので、論理積を使います。
FAQ
Q: dで、現通学小学校までの距離を調べるのはなぜですか？
A: 問題文では、現通学小学校が最も近い小学校とは限らないとされています。F列の学校名を使い、実際の現通学小学校までの距離を調べる必要があります。
A: 問題文では、現通学小学校が最も近い小学校とは限らないとされています。F列の学校名を使い、実際の現通学小学校までの距離を調べる必要があります。
Q: fで、既設小学校と同じメッシュではないことを、なぜ3つの不等号で確認するのですか？
A: 新設小学校のメッシュIDは、既設3校のどれとも一致してはいけないからです。B4、C4、D4のそれぞれと異なることを確認します。
A: 新設小学校のメッシュIDは、既設3校のどれとも一致してはいけないからです。B4、C4、D4のそれぞれと異なることを確認します。
Q: 対象児童数の集計で、児童数分布表のA列ではなくD列を使うのはなぜですか？
A: A列にはメッシュID、D列には児童数が入っているからです。対象児童数として合計するのは、D列の値です。
A: A列にはメッシュID、D列には児童数が入っているからです。対象児童数として合計するのは、D列の値です。
関連キーワード: 相対参照、絶対参照、条件付合計、照合検索、垂直照合、論理積
設問3：
問題文を見る 小学校の新設に適している場所の座標を探索することを考える。メッシュを探索するためのマクロOptimalLocationをワークシート“小学校配置”に格納した。マクロOptimalLocationを実行すると、新設小学校の座標が入るセルE2及びE3の値を、S市を構成する各メッシュの中心点の座標で順次変化させていく。最終的に、全ての小学校が制約条件を満たし、かつ、各小学校の充足率のうちの最小値が最も大きくなる座標を、新設小学校の座標として表示する。マクロOptimalLocationのに入れる正しい答えを、解答群の中から選べ。ここで、全ての小学校が制約条件を満たすメッシュは一つ以上あるものとする。
gに関する解答群
ア：最小(B7～E7) < MaxRatio
イ：最小(B7～E7) > MaxRatio
ウ：論理積(最小(B7～E7) < MaxRatio, F9 = null)
エ：論理積(最小(B7～E7) < MaxRatio, F9 ≠ null)
オ：論理積(最小(B7～E7) > MaxRatio, F9 = null)
カ：論理積(最小(B7～E7) > MaxRatio, F9 ≠ null)
キ：論理和(最小(B7～E7) < MaxRatio, F9 = null)
ク：論理和(最小(B7～E7) < MaxRatio, F9 ≠ null)
ケ：論理和(最小(B7～E7) > MaxRatio, F9 = null)
コ：論理和(最小(B7～E7) > MaxRatio, F9 ≠ null)
hに関する解答群
ア：MaxRatio ← E7
イ：MaxRatio ←最小(B7～D7)
ウ：MaxRatio ←最小(B7～E7)
エ：MaxRatio ←最大(B7～D7)
オ：MaxRatio ←最大(B7～E7)
カ：MaxRatio ←最大(最小(B7～D7), E7)
模範解答
g：オ、h：ウ
解説
解答の導き方
このマクロは、新設小学校の候補地を一つずつ試し、制約条件を満たす候補の中から、各小学校の充足率の最小値が最も大きくなる場所を探します。
-
設置可能なメッシュを順番に調べます。児童数分布表には、メッシュの情報が2行目から71行目まで並んでいます。マクロは I を1から70まで変えて、70個のメッシュを順に確認します。ここでの I はメッシュのIDではなく、表の行を順番に数える値です。「相対(児童数分布表!A1, I, 4)＝'＊'」は、各メッシュの設置可能欄を調べています。値が「＊」のメッシュだけが、新設小学校の候補になります。
-
候補地の座標を設定し、制約条件を確認します。候補地であれば、そのメッシュの中心点の座標をE2とE3に入れます。たとえば I=1 のときは、児童数分布表の2行目からx座標とy座標を取得します。座標を変えると、小学校までの距離や通学先などが更新され、充足率も変わります。図3のF9は、全体の制約条件の判定を示しています。F9が空欄、つまり null である候補だけが、制約条件を満たします。
-
候補地の評価値を計算します。問題文では、条件を満たす候補地の中から「各小学校の充足率のうちの最小値が最も大きくなる座標」を選ぶとしています。図3のB7～D7には既存の小学校の充足率が、E7には新設小学校の充足率が入っています。したがって、候補地の評価値は 最小(B7～E7) です。各小学校のうち、最も充足率が低い学校の値を候補地の評価値とし、その値ができるだけ大きくなる場所を探します。
-
制約条件を満たし、評価値が記録済みの値を上回る場合に更新します。MaxRatio は、これまでに見つけた候補地の評価値の最大値です。今回の候補地を記録するには、次の両方を満たす必要があります。
- 最小(B7～E7) > MaxRatio である
- F9 = null である
どちらも必要な条件なので、gには論理積（複数の条件がすべて成り立つときに成り立つ条件）を使います。この条件に当たるのは オ です。条件を満たしたら、今回の評価値 最小(B7～E7) を MaxRatio に記録します。よって、hには ウ が入ります。また、その候補地の順番を MinI に記録します。最終的に MinI を使って候補地の座標を取得するためです。比較が「より大きい」場合に限られるので、評価値が同じ候補地が後から見つかっても更新されません。ただし、同じ最大値を実現する候補地も最適解になり得ます。既に記録した候補地の評価値が最大のままであれば、その候補地を保持して問題ありません。 -
最後に、記録した候補地の座標を表示します。繰返しの途中では、E2とE3がそのとき試している候補地の座標になります。全てのメッシュを確認した後、マクロは MinI に記録した候補地の座標をE2とE3に入れ直します。これにより、最後に試した場所ではなく、最も良い候補地が表示されます。
誤りやすいポイント
-
各小学校の充足率の最大値を評価するのではありません。
問題が求めているのは、各小学校の充足率のうち最小の値を大きくすることです。そのため、候補地の評価値は 最小(B7～E7) です。 -
新設小学校の充足率も評価に含めます。
E7が新設小学校の充足率です。全ての小学校を対象にするため、B7～E7から最小値を求めます。 -
制約条件と評価値の比較は、両方必要です。
充足率の最小値が高くても、F9が空欄でなければ候補として採用できません。したがって、gは「評価値が記録済みの値を上回る」と「F9が空欄」の論理積です。 -
評価値が同じ候補地も最適解になり得ます。
同じ最大値を実現する候補地も最適です。後から見つかった同値の候補に更新しないのは、既に記録した候補地をそのまま残せばよいためです。
FAQ
Q: なぜ 最小(B7～D7) ではなく 最小(B7～E7) を使うのですか？
A: E7には新設小学校の充足率が入ります。既存の小学校だけでなく、新設小学校も含めた全ての小学校を評価するためです。
A: E7には新設小学校の充足率が入ります。既存の小学校だけでなく、新設小学校も含めた全ての小学校を評価するためです。
Q: MaxRatio と MinI の両方を更新するのはなぜですか？
A: MaxRatio は最良の評価値を記録し、MinI はその評価値を実現した候補地を特定するために使います。評価値だけでは、最後に表示する座標を決められません。
A: MaxRatio は最良の評価値を記録し、MinI はその評価値を実現した候補地を特定するために使います。評価値だけでは、最後に表示する座標を決められません。
Q: 最小充足率が同じ候補地が複数ある場合、どれが最適解ですか？
A: 最大の最小充足率を実現していれば、どの候補地も最適解になり得ます。このマクロは、先に記録した候補地をそのまま表示します。
A: 最大の最小充足率を実現していれば、どの候補地も最適解になり得ます。このマクロは、先に記録した候補地をそのまま表示します。
関連キーワード: 充足率、論理積、繰返し処理、相対参照、最大化
