基本情報技術者 2014年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
F社では、所有する顧客情報のデータを分析して営業活動に役立てている。このたび、情報漏えいなどの事故に備えるために、必要に応じて匿名性の高いデータに変換してから、分析を担当する部署に渡すことになった。そこで、顧客データに対して匿名化処理を行うプログラムを表計算ソフトで作成した。
〔ワークシート：顧客リスト〕
分析対象の顧客は5,000人である。各顧客に対して固有のID、氏名、郵便番号、住所、電話番号、年齢、職業コードの情報を所有している。顧客情報を格納したワークシート“顧客リスト”の例を図1に示す。ここで、郵便番号は7桁の数値として取り扱う。
〔ワークシート：匿名化顧客リスト〕
ワークシート“顧客リスト”を基に、匿名化処理を施したワークシート“匿名化顧客リスト”を作成した。ワークシート“匿名化顧客リスト”の例を図2に示す。
なお、列H、列Iは、後述のマクロGenerateListで使用する。
設問1：
問題文を見る ワークシート“匿名化顧客リスト”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。ここで、c1とc2に入れる答えは、cに関する解答群の中から組合せとして正しいものを選ぶものとする。
(1) 氏名、住所、電話番号の情報は完全に秘匿するために、“*”にする。そこでセルB2、D2、E2に“*”を入力し、それぞれセルB3〜B5001、D3〜D5001、E3〜E5001に複写する。
(2) 郵便番号は、7桁の郵便番号のうち上位2桁だけの値に置き換えることで匿名化する。そこで、次の式をセルC2に入力し、セルC3〜C5001に複写する。
a
(3) セルF2〜F5001には、図3に示す各年齢区分の最小値である表示年齢を表示することで匿名化する。各年齢区分の最小値である表示年齢を、セルK2〜K9に昇順に入力する。また、セルK10には、終端を示す1000を入力する。
例えば、顧客の年齢が23歳の場合、“20”と表示する。そこで、次の式をセルF2に入力し、セルF3〜F5001に複写する。
b
(4) 職業コードは図4に示す項目から成る3桁の数値である。各桁は、1〜9の整数値である。
セルG2〜G5001には、必要に応じて大分類化するか中分類化することで匿名化した職業コードを表示する。大分類化とは、中分類、小分類の値をいずれも0に置き換えた数値に変換することであり、中分類化とは、小分類の値を0に置き換えた数値に変換することである。大分類化、中分類化された3桁の数値をそれぞれ大分類コード、中分類コードと呼ぶ。
セルL2〜M41には、職業コードの大分類化、中分類化に関する情報が入力されている。存在し得る大分類コード、中分類コードは合わせて40種類であり、セルL2〜L41に昇順で格納されている。次の規則によって、職業コードを変換する。
① 大分類コードが格納されているセルの右側の対応するセルに“*”が格納されている場合、その大分類に属する職業コードは全て大分類コードに変換する。例えば、セルL2には大分類コード100が格納されていて、その右隣のセルM2は“*”なので、100番台の全ての職業コードは100に変換する。
② ①に該当しない場合であって、中分類コードが格納されているセルの右側の対応するセルに“*”が格納されている場合、その中分類に属する職業コードは全て中分類コードに変換する。例えば、セルL6には中分類コード210が格納されていて、右隣のセルM6は“*”なので、210番台の全ての職業コードは210に変換する。
③ ①、②のいずれも該当しない場合、職業コードは変換しない。
そこで、次の式をセルG2に入力し、セルG3〜G5001に複写する。
IF(垂直照合(c1,L$2〜M$41,2,0) = '*',c1,
IF(垂直照合(c2,L$2〜M$41,2,0) = '*',c2,
顧客リスト!G2))
aに関する解答群
ア：剰余(10 ^ 5,顧客リスト!C2)
イ：剰余(顧客リスト!C2,10 ^ 5)
ウ：整数部((顧客リスト!C2 / 10) ^ 5)
エ：整数部(顧客リスト!C2 / 10 ^ 5)
オ：整数部(顧客リスト!C2 / 10) ^ 5
bに関する解答群
ア：垂直照合(顧客リスト!F2,K$2〜K$10,1,0)
イ：垂直照合(顧客リスト!F2,K$2〜K$10,1,1)
ウ：垂直照合(顧客リスト!F2 + 1,K$2〜K$10,1,0)
エ：垂直照合(顧客リスト!F2 + 1,K$2〜K$10,1,1)
オ：表引き(K$2〜K$10,照合一致(顧客リスト!F2,K$2〜K$10,−1),1)
カ：表引き(K$2〜K$10,照合一致(顧客リスト!F2 + 1,K$2〜K$10,1),1)
cに関する解答群
模範解答
a：エ、b：イ、c：イ
解説
解答の導き方
この設問では、元の顧客情報をどのように置き換えるかを、郵便番号・年齢・職業コードごとに考えます。
(2) 郵便番号：上位2桁を取り出す
問題文には、郵便番号を「7桁の郵便番号のうち上位2桁だけの値に置き換える」とあります。上位2桁を取り出すには、郵便番号を で割り、その整数部分を求めます。
たとえば、顧客リストの郵便番号が1070062なら、
となり、整数部分は10です。これで上位2桁を取り出せます。
一方、剰余を使うと、割った後に余った下位5桁を取り出してしまいます。したがって、郵便番号を で割って整数部分を求める式を選びます。これは解答群のエです。
(3) 年齢：年齢区分の開始値を求める
図3では、たとえば「20～24歳」の表示年齢は20です。つまり、年齢に対して、それ以下で最も大きい区分の開始値を探せばよいことになります。
セルK2〜K10には、表示年齢が昇順で並んでいます。垂直照合（表の先頭列から値を探し、指定した列の値を返す関数）の検索方法を1にすると、検索値以下で最も大きい値を探します。したがって、顧客リスト!F2の年齢をそのまま検索値にする、解答群のイが適切です。
たとえば年齢が23歳なら、K列の値のうち23以下で最も大きい値は20です。そのため、表示年齢は20になります。検索方法を0にすると完全一致だけを探すため、23という値がK列になければ見つかりません。また、年齢に1を足すと、区分の境目で誤った値になることがあります。
K10の1000は、問題文で指定された終端値です。検索範囲には、この値も含めます。
(4) 職業コード：大分類を先に確認し、次に中分類を確認する
職業コードは3桁で、図4では上から大分類・中分類・小分類の各1桁で構成されています。職業コードを一般化するときは、末尾の桁を0に置き換えます。
たとえば、214について考えると、大分類コードは200、中分類コードは210です。
- 大分類コード：214の百の位を残し、残りを0にして200
- 中分類コード：214の十の位までを残し、一の位を0にして210
したがって、c1には大分類コード、c2には中分類コードを求める式を入れます。これらの値を求める組合せは解答群のイです。
次に、垂直照合でそれぞれのコードをL列から完全一致で探し、対応するM列が「*」かどうかを確認します。完全一致は、検索方法を0にして、同じ値がある場合だけ見つける指定です。
セルL5の200に対応するM5には「」がありません。一方、セルL6の210に対応するM6には「」があります。そのため、職業コード214は、まず大分類コード200を確認し、該当しなければ中分類コード210を確認して、210に変換されます。
この順番は重要です。大分類コードが「*」なら、同じ大分類に属する職業コードは大分類コードに変換する規則だからです。大分類に該当しない場合だけ、中分類を確認します。どちらにも該当しなければ、顧客リストにある元の職業コードをそのまま使います。
誤りやすいポイント
- 郵便番号に剰余を使うと、上位2桁ではなく下位5桁が得られます。上位桁を取り出すには、 で割って整数部分を求めます。
- 年齢の検索で完全一致を指定すると、23歳のようにK列にそのまま存在しない年齢を分類できません。区分の開始値を使うため、近似検索を選びます。
- 年齢に1を足して検索すると、区分の境目にいる人が次の区分に入る場合があります。年齢そのものを検索値にします。
- 職業コードでは、中分類より先に大分類を確認します。また、L列には元の職業コードすべてではなく、大分類コード・中分類コードが格納されています。
FAQ
Q: なぜ年齢の検索は近似検索なのですか？
A: K列には、各年齢区分の開始値だけが並んでいるためです。年齢と完全一致する値を探すのではなく、年齢以下で最も大きい開始値を探します。
A: K列には、各年齢区分の開始値だけが並んでいるためです。年齢と完全一致する値を探すのではなく、年齢以下で最も大きい開始値を探します。
Q: 職業コードが214なら、必ず210に変換されますか？
A: 必ずではありません。大分類コード200に「」があれば200に変換されます。大分類に該当せず、中分類コード210に「」がある場合に210へ変換されます。どちらにも該当しなければ214のままです。
A: 必ずではありません。大分類コード200に「」があれば200に変換されます。大分類に該当せず、中分類コード210に「」がある場合に210へ変換されます。どちらにも該当しなければ214のままです。
Q: 郵便番号の上位2桁が「00」のときは、どうなりますか？
A: 数値としては0になります。表示を2桁にする設定であれば、「00」と表示できます。
A: 数値としては0になります。表示を2桁にする設定であれば、「00」と表示できます。
関連キーワード: データ匿名化、整数部、剰余、近似検索、完全一致検索、データ一般化
設問2：
問題文を見る マクロGenerateListをワークシート“匿名化顧客リスト”に格納した。マクロGenerateList中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：提供リスト〕
匿名化された後の郵便番号、年齢、職業コードが全て同一となる顧客の数（多重度と呼ぶ）のリストをデータ分析の担当部署に渡すために、図5に示すワークシート“提供リスト”を作成する。
(1) ワークシート“匿名化顧客リスト”にマクロGenerateListを実装する。
(2) マクロGenerateListは、ワークシート“提供リスト”の列A〜Dに、ワークシート“匿名化顧客リスト”を郵便番号、年齢、職業コードの優先順で昇順に整列し、多重度とともに表示する。
(3) ワークシート“匿名化顧客リスト”のセルH2〜H5001は、マクロGenerateListの実行中に、どの顧客の情報が処理済みであるかを示すのに用いる。処理済みの顧客の場合は1を、そうでない場合は0を表示する。
(4) (2)の処理を行う際、次に処理する顧客を決定するために、ワークシート“匿名化顧客リスト”中の郵便番号、年齢、職業コードから成る次のような8桁の数値（以下、評価値という）を定義する。
この評価値が最小の顧客を処理対象とし、多重度を順次求めていく。
そこで、次の式をワークシート“匿名化顧客リスト”のセルI2に入力し、セルI3〜I5001に複写する。
H2 * 10000000 + C2 * 100000 + F2 * 1000 + G2
(5) ワークシート“匿名化顧客リスト”のセルN2には、表示する多重度の下限値が入力されている。ここで、表示する多重度の下限値は2以上の整数とする。多重度がこの値以上の郵便番号、年齢、職業コードの組を、多重度とともにワークシート“提供リスト”に表示する。
dに関する解答群
ア：条件付個数(I2〜I5001,> 相対(I1, I, 0))
イ：条件付個数(I2〜I5001,< 相対(I1, I, 0))
ウ：照合一致(最小(I2〜I5001),I2〜I5001,0)
エ：照合一致(最小(I2〜I5001),I2〜I5001,1)
オ：照合一致(最大(I2〜I5001),I2〜I5001,0)
カ：照合一致(最大(I2〜I5001),I2〜I5001,1)
キ：相対(I1, I, 0)
ク：相対(I1, J, 0)
e, fに関する解答群
ア：I ← I + 1
イ：I ← I + J
ウ：I ← I + K
エ：J ← I
オ：J ← J + 1
カ：J ← J + K
キ：J ← K + 1
ク：K ← J
ケ：K ← K + 1
コ：K ← K + J
模範解答
d：ウ、e：オ、f：ケ
解説
解答の導き方
このマクロは、匿名化後の郵便番号、年齢、職業コードが同じ顧客をまとめ、その人数を多重度として「提供リスト」に出力します。多重度とは、同じ組合せに当てはまる顧客の数です。
1. 評価値の最小値から、未処理の顧客を選びます
問題文では、評価値を次の式で求めます。
ここで、H列は処理済みなら1、未処理なら0です。式の先頭にある によって、処理済みの顧客の評価値は、未処理の顧客より大きくなります。そのため、評価値の最小値を選ぶと、未処理の顧客が優先されます。
また、郵便番号、年齢、職業コードの順に桁の重みが付いています。したがって、評価値の小さい順に選ぶことは、郵便番号、年齢、職業コードの優先順で昇順に並べることに対応します。郵便番号、年齢、職業コードが同じ顧客は、同じ評価値になります。
dには、評価値の最小値を見つけ、その値がI2〜I5001のどこにあるかを調べる処理が必要です。これに合うのは、ウの「最小値を求め、その値と完全に一致する位置を探す」式です。
照合一致の最後の引数が0の場合は、検索値と完全に一致する値を探します。結果は範囲内での位置なので、たとえば最初の顧客が見つかればminIDは1です。相対(H1, minID, 0)は、この位置を使ってH2などの該当する行を指します。
2. 選んだ顧客を処理済みにします
処理対象を選んだ後、相対(H1, minID, 0)に1を入れます。これで、その顧客の評価値は処理済みの値に変わります。次に最小値を探すときは、まだ処理していない顧客が優先されます。
こうして同じ処理を5,000人分繰り返せば、顧客を一人ずつ評価値の小さい順に処理できます。
3. 同じ評価値の顧客をまとめます
previousValueには、前に処理した顧客の評価値を記録します。選んだ顧客の評価値がpreviousValueと異なる場合は、新しい組合せに移ったことを意味します。郵便番号、年齢、職業コードのいずれかが異なるためです。
このときKは、直前の組合せに当てはまる顧客数です。直前の組合せの多重度がminMultiplicity以上なら、その組合せは提供対象になります。次の組合せを書き込む行へ移る必要があるため、Jを1増やします。したがってeは、J ← J + 1です。
一方、直前の組合せの多重度がminMultiplicity未満なら、その組合せは提供しません。Jを増やさず、同じ出力行に新しい組合せを上書きします。これにより、提供対象外の組合せのために空白の行ができるのを防げます。
新しい組合せに移ったときは、Kを1に戻します。新しく処理した顧客一人分を数え始めるためです。
選んだ顧客の評価値がpreviousValueと同じ場合は、同じ組合せの顧客がもう一人見つかったということです。この場合、Kを1増やします。したがってfは、K ← K + 1です。
その後、提供リストのD列にKを書き込みます。同じ組合せの顧客が見つかるたびに、多重度が更新されます。
4. 最後の組合せも多重度を確認します
ループが終わると、最後に処理した組合せについては、次の組合せに移る機会がないため、多重度を確認する処理が残りません。そのため最後に、提供リストのD列の値がminMultiplicity未満なら、その行のA列〜D列を消去します。
以上から、dでは最小の評価値に一致する位置を調べるウを選びます。eでは提供リストの次の行へ進むオ、fでは同じ組合せの顧客数を増やすケを選びます。
誤りやすいポイント
-
最小値ではなく最大値を選んでしまう
H列が0の未処理の顧客は、H列が1の処理済みの顧客より評価値が小さくなります。未処理の顧客を選ぶには、最小値を探します。 -
照合一致で近い値を探してしまう
求めた最小値が実際にある位置を特定するため、完全一致で検索する必要があります。 -
Jを毎回増やしてしまう
Jを増やすのは、直前の組合せが提供対象になる場合だけです。多重度が下限未満なら、その行を次の組合せに使います。 -
新しい組合せでもKを増やしてしまう
組合せが変わったときは、Kを1に戻します。同じ組合せが続くときだけ、Kを1増やします。
FAQ
Q: なぜ処理済みの顧客をH列で示すのですか？
A: 評価値は、顧客ごとに同じ値になる場合があります。処理済みかどうかを記録しないと、同じ顧客を何度も選ぶ可能性があります。H列を更新することで、次は未処理の顧客から選べます。
A: 評価値は、顧客ごとに同じ値になる場合があります。処理済みかどうかを記録しないと、同じ顧客を何度も選ぶ可能性があります。H列を更新することで、次は未処理の顧客から選べます。
Q: なぜ同じ組合せの顧客が続けて選ばれるのですか？
A: 評価値には郵便番号、年齢、職業コードが含まれています。この3項目が同じ顧客は同じ評価値になるため、評価値の小さい順に処理すると、同じ組合せの顧客がまとまって処理されます。
A: 評価値には郵便番号、年齢、職業コードが含まれています。この3項目が同じ顧客は同じ評価値になるため、評価値の小さい順に処理すると、同じ組合せの顧客がまとまって処理されます。
Q: なぜ最後に提供リストの行を消すのですか？
A: ループ中は、直前の組合せの多重度が下限以上かどうかを、次の組合せに移るときに確認しています。最後の組合せだけは次の組合せに移らないため、ループ終了後に確認し、下限未満なら行を消します。
A: ループ中は、直前の組合せの多重度が下限以上かどうかを、次の組合せに移るときに確認しています。最後の組合せだけは次の組合せに移らないため、ループ終了後に確認し、下限未満なら行を消します。
関連キーワード: 評価値、照合一致、昇順整列、多重度、匿名化、条件分岐
