基本情報技術者 2013年 秋期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
ある科目を受講する学生を、知人関係の情報に基づいてグループ分けするプログラムを表計算ソフトで作成した。グループ分けする対象の学生は50人である。グループはそれぞれ10人で、各グループにはあらかじめ1人のグループ長が決められている。
〔ワークシート：知人関係類似度行列〕
任意の2学生の知人関係の情報を表現した“知人関係類似度行列”を作成した。ワークシート“知人関係類似度行列”の例を図1に示す。
(1) 各学生には1～50の受講者番号が振られている。セルA3～A52には、1から順番に受講者番号を入力する。セルB3～B52には受講者番号に対応する氏名を入力する。同様にセルC1～AZ1には受講者番号を、セルC2～AZ2には氏名を入力する。
(2) セルC3～AZ52には、対応する2人の知人関係の類似性をある指標に基づいて数値化した値（以下、類似度という）が格納されている。類似度は0以上1以下の数値である。各セルに対応する学生2人の組合せが同一であれば類似度は同じ値である。例えば、受講者番号1の佐藤一郎と受講者番号2の鈴木二郎の類似度に対応するセルC4, D3にはいずれも同じ値が格納される。また、同一学生同士に当たるセルC3, D4, …, AZ52には0が入力されている。
〔ワークシート：グループ分け〕
ワークシート“知人関係類似度行列”を基に、図2に示すワークシート“グループ分け”を作成した。
(1) セルA3～A52には、1から順番に受講者番号を入力する。セルB3～B52には、受講者番号に対応する氏名を入力する。
(2) セルC1～G1には、1から順番にグループ番号を入力する。
(3) セルC2～G2には、各グループのグループ長の受講者番号を入力する。
(4) セルC3～G52には、それぞれの学生（グループ長を含む）が、対応するグループに割り当てられている場合は1を、そうでない場合には0を入力する。
(5) セルH3～H52には、対応する学生が既にいずれかのグループに割り当てられている場合は1が、そうでない場合は0が表示される。
(6) セルC53～G53には、対応するグループに割り当てられた学生が10人であれば1が、そうでない場合は0が表示される。
(7) 本ワークシートは、グループ分け作業を支援するための推薦機能を有している。
どのグループにも割り当てられていない学生と、所属する人数が10人に達していないグループのグループ長との組合せの中で、類似度が最大となる組合せを抽出し、その組合せに含まれる学生の受講者番号とグループ長が属するグループ番号を行55に表示する。ただし、類似度が最大となる組合せが複数ある場合、次の優先順で最も優先度が高い組合せを表示する。
① 割り当てようとする学生の受講者番号が最も小さい組合せ
② ①に該当する組合せが複数存在する場合、その中でグループ長が属するグループ番号が最も小さい組合せ
(8) 列Jから列Qを、推薦機能のための計算領域に用いる。
設問1：
問題文を見る ワークシート“グループ分け”の推薦機能に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
(1) セルJ3～N52には、それぞれの学生と対応するグループ長との類似度の値を入力する。ただし、学生が既にいずれかのグループに割り当てられている場合、又は対応するグループに割り当てられている学生数が10人である場合には−1が入るようにしたい。そこで、次の式をセルJ3に入力し、セルJ3～N52に複写する。
IF(a, −1, 表引き(知人関係類似度行列!$C$3～$AZ$52, b))
(2) それぞれの学生を割り当てる候補となるグループを選定するために、次の式をセルO3に入力し、セルO4～O52に複写する。
最大(J3～N3)
さらに、次の式をセルQ3に入力し、セルQ4～Q52に複写する。
照合一致(O3, J3～N3, 0)
(3) 推薦する受講者番号を表示するために、次の式をセルP3に入力し、セルP4～P52に複写する。
順位(O3, O$3～O$52, 1)
さらに、次の式をセルC55に入力する。
c
(4) 推薦する学生の割当て先グループ番号を表示するために、次の式をセルG55に入力する。
d
aに関する解答群
ア：論理積($H3=0, C$53=0)
イ：論理積($H3=0, C$53=1)
ウ：論理積($H3=1, C$53=0)
エ：論理積($H3=1, C$53=1)
オ：論理和($H3=0, C$53=0)
カ：論理和($H3=0, C$53=1)
キ：論理和($H3=1, C$53=0)
ク：論理和($H3=1, C$53=1)
bに関する解答群
ア：$A3, C$1
イ：$A3, C$2
ウ：A$3, C$1
エ：A$3, C$2
cに関する解答群
ア：照合一致(1, P3～P52, 0)
イ：照合一致(50, P3～P52, 0)
ウ：照合検索(1, P3～P52, O3～O52)
エ：照合検索(50, P3～P52, O3～O52)
オ：条件付個数(H3～H52, =1)
カ：条件付個数(O3～O52, =−1)
キ：条件付個数(O3～O52, ≠−1)
dに関する解答群
ア：整数(条件付個数(H3～H52, =1) / 10) + 1
イ：表引き(Q3～Q52, C55, 1)
ウ：照合一致(C55, P3～P52, 0)
エ：照合検索(C55, P3～P52, O3～O52)
オ：照合検索(C55, Q3～Q52, A3～A52)
カ：条件付個数(C53～G53, =1) + 1
模範解答
a：ク、b：イ、c：ア、d：イ
解説
解答の導き方
推薦機能では、まず割当て可能な学生とグループの組合せだけを残します。その中から類似度が最も高い組合せを選び、同点の場合は受講者番号、グループ番号の順に優先順位を決めます。
(1) セルJ3～N52に入れる条件と類似度
セルH3～H52は、学生が既にグループに割り当てられているときに1です。また、セルC53～G53は、対応するグループが定員に達しているときに1です。
そのため、類似度を計算しない条件は「学生が割当て済み」または「グループが定員に達している」です。どちらか一方でも当てはまればよいので、条件には論理和を使います。
したがって、aはクの「論理和($H3=1, C$53=1)」です。条件に当てはまるときは、類似度の範囲である0以上1以下には含まれない「−1」を入れます。これにより、割当てできない組合せを区別できます。類似度0は有効な値なので、0を使うことはできません。
類似度を調べるには、対象の学生の受講者番号と、グループ長の受講者番号が必要です。受講者番号はセルA列にあり、グループ長の受講者番号はセルC2～G2にあります。グループ番号が書かれているC1～G1ではなく、グループ長の受講者番号が書かれている2行目を使う点が大切です。
セルJ3から始めて右や下に複写するとき、学生の番号は列Aを固定して行だけを変え、グループ長の番号は行2を固定して列だけを変えます。その参照の形に合うのが「$A3, C$2」なので、bはイです。
(2) 各学生に対する候補グループと、グループ内の優先順位
セルO3には、同じ行にあるJ3～N3の最大値を入れます。J3～N3には、その学生と各グループ長との類似度が入ります。割当てできない組合せには「−1」が入るため、割当て可能な組合せがあれば、0以上の類似度が「−1」より大きくなり、最大値として選ばれます。
セルQ3の「照合一致(O3, J3～N3, 0)」は、最大値と一致する最初の位置を求めます。J列からN列はグループ番号1から5の順に並んでいます。最大値が同じグループが複数ある場合、最初に見つかるのは番号が最も小さいグループです。これで、同じ学生について候補グループが同点になったときの優先順位を決められます。
(3) 推薦する学生を決める
セルO3～O52には、それぞれの学生について、割当て可能なグループの中で最も高い類似度が入ります。したがって、これらを比べれば、学生とグループ長の組合せ全体の中で類似度が最も高いものを見つけられます。
セルP3～P52の「順位(O3, O$3～O$52, 1)」は、O列の値を順位付けし、最大値を順位1にします。最大値が複数ある場合は、該当する学生の順位も同じになります。
セルC55では、セルP3～P52から順位1に当たる最初の位置を探します。「照合一致(1, P3～P52, 0)」は、順位1の位置を返すため、cはアです。受講者番号はA3からA52まで1から50の順に並んでいるので、返された位置はそのまま推薦する受講者番号になります。同じ最大類似度の学生が複数いる場合も、先に見つかる行の学生、つまり受講者番号が最も小さい学生が選ばれます。
(4) 推薦する学生の割当て先グループを決める
セルQ3～Q52には、それぞれの学生について、最大類似度となるグループの位置が入っています。セルC55には推薦する学生の受講者番号が入っているので、その番号を使ってQ列から該当する学生の位置を取り出せば、割当て先グループを求められます。
この処理に当たるのは「表引き(Q3～Q52, C55, 1)」で、dはイです。Q列の値は、グループ番号1から5の順に並ぶJ列からN列の位置です。その位置はグループ番号と一致するため、G55に表示するグループ番号になります。
誤りやすいポイント
- 割当て済みと定員充足のどちらか一方でも該当すれば、その組合せは使えません。条件を論理積ではなく論理和にする必要があります。
- 類似度行列を調べるときに使うのは、グループ番号ではなくグループ長の受講者番号です。グループ番号はセルC1～G1、グループ長の受講者番号はセルC2～G2にあります。
- 類似度0は有効な値です。割当て不可を0で表すと、有効な組合せと区別できなくなります。
- 最大値が同じとき、グループの優先順位はJ列からN列の並び順で決まります。学生の優先順位は、受講者番号順に並んだ行と、最初に一致する位置を返す処理で決まります。
- グループ番号は、定員に達したグループの数から計算するのではありません。推薦する学生に対して選ばれたQ列の値を取り出します。
FAQ
Q: なぜ割当て不可を「−1」で表すのですか？
A: 類似度は0以上1以下なので、「−1」なら有効な類似度と区別できます。また、割当て可能な組合せがあるときは、最大値を求める処理でも有効な値が選ばれます。
A: 類似度は0以上1以下なので、「−1」なら有効な類似度と区別できます。また、割当て可能な組合せがあるときは、最大値を求める処理でも有効な値が選ばれます。
Q: 学生の番号やグループ長の番号を、数式で固定する必要があるのはなぜですか？
A: 数式を別のセルに複写しても、参照する列や行のうち必要な部分を保つためです。学生番号は列Aを保ち、グループ長の番号は行2を保つようにしています。
A: 数式を別のセルに複写しても、参照する列や行のうち必要な部分を保つためです。学生番号は列Aを保ち、グループ長の番号は行2を保つようにしています。
Q: 同じ最大類似度の組合せがあるとき、なぜ優先順位どおりに選べるのですか？
A: Q列ではグループ番号の小さい順に最初の一致を選び、P列では受講者番号の小さい順に最初の順位1を選びます。この順番によって、同点時の優先順位を反映できます。
A: Q列ではグループ番号の小さい順に最初の一致を選び、P列では受講者番号の小さい順に最初の順位1を選びます。この順番によって、同点時の優先順位を反映できます。
関連キーワード: 表計算、絶対参照、相対参照、論理和、最大値、順位付け
設問2：
問題文を見る ワークシート“グループ分け”のセルC2～G2にグループ長の受講者番号を入力して実行すると以後のグループ分け処理を全て自動化するマクロGroupingを、ワークシート“グループ分け”に格納した。マクロGroupingは次の手順で処理を行う。
① セルC3～G52を全て0で初期化する。
② 5人のグループ長をそれぞれのグループに割り当てる。つまりセルC3～G52の中で対応するセルの値を1に変更する。
③ 推薦機能によって、推薦する学生の受講者番号はセルC55に、グループ番号はセルG55に表示されるので、セルC3～G52の中で対応するセルの値を1に変更する。
④ 全員がいずれかのグループに割り当てられるまで③を繰り返す。
マクロGrouping中のに入れる正しい答えを、解答群の中から選べ。
e, gに関する解答群
ア：相対(B2, C55, I) ← 1
イ：相対(B2, C55, G55) ← 1
ウ：相対(B2, I, G55) ← 1
エ：相対(B2, I, 相対(B2, 0, I)) ← 1
オ：相対(B2, I, 相対(B2, C55, I)) ← 1
カ：相対(B2, 相対(B2, 0, I), I) ← 1
キ：相対(B2, 相対(B2, C55, I), I) ← 1
ク：相対(B2, 相対(B2, 0, G55), I) ← 1
fに関する解答群
ア：NumGroups
イ：NumGroups * NumGroups
ウ：NumUsers
エ：NumUsers * (NumGroups - 1)
オ：NumUsers * NumGroups
カ：NumUsers + NumGroups
キ：NumUsers - NumGroups
ク：NumUsers / NumGroups
模範解答
e：カ、f：キ、g：イ
解説
解答の導き方
このマクロは、まずグループ長を割り当て、その後、推薦機能が示した学生を順番にグループへ追加します。空欄の意味を考えるには、セルの位置と繰返し回数を順に確認します。
1. 最初の繰返しで、グループごとの列を初期化します
最初の繰返しでは、変数がグループを表し、変数が学生を表しています。を1からまで動かし、各についてを1からまで動かします。
「相対(B2, J, I)」は、基準セルB2から、行方向に、列方向にだけ移動したセルを表します。たとえば、ならC3です。したがって、この繰返しで各グループの列にある人分のセルを0にできます。
この後の空欄eでは、初期化した列に、そのグループのグループ長を割り当てます。
2. 空欄eでは、グループ長のセルを1にします
グループ長の受講者番号は、グループ分けシートのC2～G2にあります。ならグループ1なので、グループ長の受講者番号はC2にあります。この値を得るのが「相対(B2, 0, I)」です。のとき、これはC2を指します。
グループ長の受講者番号を行方向の移動量に使い、グループ番号を列方向の移動量に使えば、該当する学生とグループの交点を指定できます。式は「相対(B2, 相対(B2, 0, I), I)」となるので、空欄eはカです。
たとえば、図2ではグループ1のグループ長の受講者番号が2です。のとき、B2から行方向に2、列方向に1だけ移動したセルはC4です。ここに1を入れることで、受講者番号2の学生をグループ1に割り当てられます。
3. 空欄fは、推薦による割当ての回数です
グループ長を割り当てた後、まだ割り当てられていない学生は、全体の人からグループ長の人を引いた人数です。
推薦機能は、まだ割り当てられていない学生を1人ずつ推薦し、繰返しごとに1人を追加します。そのため、この後の繰返しは45回必要です。選択肢では「NumUsers - NumGroups」に当たるキです。
4. 空欄gでは、推薦された学生とグループの交点を1にします
問題文では、推薦する学生の受講者番号がC55に、グループ番号がG55に表示されます。受講者番号は行方向の移動量、グループ番号は列方向の移動量として使います。
そのため、割当て先のセルは「相対(B2, C55, G55)」です。よって、空欄gはイです。
たとえば図2のC55が18、G55が1なら、B2から行方向に18、列方向に1だけ移動したC20に1を入れます。これで、受講者番号18の学生をグループ1に割り当てます。割当てが更新されると推薦機能の表示も更新され、次の繰返しではその時点の推薦に従って学生を追加できます。
誤りやすいポイント
- 「相対(B2, 行方向の移動量, 列方向の移動量)」の引数の順番を逆にしないようにします。学生番号は行方向、グループ番号は列方向の移動量です。
- グループ長を割り当てる場面では、C55やG55ではなく、グループ長の受講者番号が入力されたC2～G2を使います。
- 2回目の繰返し回数を回と考えないようにします。グループ長の人はすでに割り当てられているため、残りは45人です。
- 1を入れるセルは、受講者番号だけでもグループ番号だけでも決まりません。学生の行とグループの列の交点を指定します。
FAQ
Q: なぜ、グループ長を割り当てた後に45回繰り返すのですか？
A: 対象は50人で、そのうち5人はグループ長として先に割り当てられます。残る45人を1人ずつ追加するためです。
A: 対象は50人で、そのうち5人はグループ長として先に割り当てられます。残る45人を1人ずつ追加するためです。
Q: グループ分けの表で、セルに入れるのはグループ番号ではなく1なのですか？
A: C列～G列の見出しがグループ番号を表しています。そのため、該当する列のセルに1を入れれば、その学生がどのグループに割り当てられたかを表せます。
A: C列～G列の見出しがグループ番号を表しています。そのため、該当する列のセルに1を入れれば、その学生がどのグループに割り当てられたかを表せます。
Q: 推薦された学生を割り当てた後、次の推薦も同じ学生になるのですか？
A: 割当ての状態が変わると、推薦の対象となる学生やグループも変わります。次の繰返しでは、更新後の推薦表示を使います。
A: 割当ての状態が変わると、推薦の対象となる学生やグループも変わります。次の繰返しでは、更新後の推薦表示を使います。
関連キーワード: 表計算、相対参照、二重ループ、行列、マクロ
