基本情報技術者 2012年春期午後問13

次の表計算、ワークシート及びマクロの説明を読んで、設問1〜3に答えよ。

〔表計算の説明〕　表計算ソフトを用いて、ある大学学部所有の図書を管理するプロトタイプシステムを作成した。プロトタイプシステムは“図書情報”と“利用者情報”の二つのワークシートから成る。　〔ワークシート：図書情報〕　学部で所有している図書のうち200冊を抽出し、図1のワークシート“図書情報”を作成した。

(1) セルB2〜B201には、該当する図書名を入力する。セルA2〜A201には、1から始まる連番で図書IDを入力する。 (2) セルC2〜C201には、図書が貸出し中であれば、借りた利用者の利用者IDを入力する。そうでなければ空値である。 (3) セルD2〜D201には、図書が貸出し中であれば、貸し出した日付を入力する。そうでなければ空値である。 (4) セルE2〜E201には、返却期日を算出して格納する。貸出し中でなければ空値である。 (5) セルF2〜F201には、返却期日を過ぎても返却されていない図書に関して、延滞日数を求める式を入力する。この式は、返却期日を過ぎていなければ0に、貸出し中でなければ空値になる。 (6) セルI1には、本日の日付を求める式を入力する。 (7) 日付の表記はyy-mm-ddで表示されるが、表計算ソフトの内部では1970年1月1日からの経過日数を整数値で保持している。計算にはこの値を利用する。　〔ワークシート：利用者情報〕　利用対象者を50名抽出し、利用者ごとの情報から図2に示すワークシート“利用者情報”を作成した。

(1) セルB2〜B51には、氏名を入力する。セルA2〜A51には、1から始まる連番で利用者IDを入力する。 (2) セルC2〜C51には、属性（教員、大学院生、学部生、その他）を入力する。 (3) セルD2〜D51には、貸出上限までの現在の残り冊数（以下、残り貸出冊数という）を求める式を入力する。 (4) セルE2〜E51には、延滞状態の図書が1冊でもあれば“*”に、延滞状態の図書が無いときは空値になる式を入力する。 (5) セルG2〜G5には属性の名称を、セルH2〜H5とセルI2〜I5には、それぞれの属性に応じた貸出上限冊数と貸出日数を入力する。

設問1：

問題文を見る

　ワークシート“図書情報”及び“利用者情報”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。　　返却期日を算出するために、次の式をワークシート“図書情報”のセルE2に入力し、セルE3〜E201に複写する。　IF(C2 ≠ null,a,null) 　延滞日数を算出するために、次の式をワークシート“図書情報”のセルF2に入力し、セルF3〜F201に複写する。　IF(C2 ≠ null,b,null) 　残り貸出冊数を算出するために、次の式をワークシート“利用者情報”のセルD2に入力し、セルD3〜D51に複写する。　c 　延滞状態を表示するために、次の式をワークシート“利用者情報”のセルE2に入力し、セルE3〜E51に複写する。　d 　 aに関する解答群　ア：D2 + 垂直照合(C2,利用者情報!G$2〜I$5,3,0) 　イ：D2 + 垂直照合(C2,利用者情報!A$2〜C$51,1,0) 　ウ：D2 + 垂直照合(C2,利用者情報!A$2〜C$51,3,0) 　エ：D2 + 垂直照合(垂直照合(C2,利用者情報!A$2〜C$51,1,0),利用者情報!G$2〜I$5,3,0) 　オ：D2 + 垂直照合(垂直照合(C2,利用者情報!A$2〜C$51,3,0),利用者情報!G$2〜I$5,3,0) 　カ：D2 + 水平照合(C2,利用者情報!A$2〜C$51,1,0) 　キ：D2 + 水平照合(垂直照合(C2,利用者情報!A$2〜C$51,1,0),利用者情報!G$2〜I$5,3,0) 　ク：D2 + 水平照合(垂直照合(C2,利用者情報!A$2〜C$51,3,0),利用者情報!G$2〜I$5,3,0) 　 bに関する解答群　ア：E2 − I$1 　イ：I$1 − E2 　ウ：IF(E2 ≧ I$1,0,I$1 − E2) 　エ：IF(E2 ≧ I$1,E2,I$1) 　オ：IF(E2 ≧ I$1,I$1,E2) 　カ：IF(E2 ≧ I$1,I$1 − E2,0) 　 cに関する解答群　ア：垂直照合(C2,G$2〜I$5,2,0) 　イ：垂直照合(C2,G$2〜I$5,2,0) + 条件付個数(図書情報!C$2〜C$201,= A2) 　ウ：垂直照合(C2,G$2〜I$5,2,0) − 条件付個数(図書情報!C$2〜C$201,= A2) 　エ：垂直照合(C2,G$2〜I$5,3,0) 　オ：垂直照合(C2,G$2〜I$5,3,0) + 条件付個数(図書情報!C$2〜C$201,= A2) 　カ：垂直照合(C2,G$2〜I$5,3,0) − 条件付個数(図書情報!C$2〜C$201,= A2) 　 dに関する解答群　ア：IF(D2 > 0,'*',null) 　イ：IF(D2 = 0,'*',null) 　ウ：IF(合計(図書情報!C$2〜C$201) = C2,'*',null) 　エ：IF(合計(図書情報!C$2〜C$201) ≠ C2,'*',null) 　オ：IF(合計(図書情報!F$2〜F$201) = F2,'*',null) 　カ：IF(合計(図書情報!F$2〜F$201) ≠ F2,'*',null) 　キ：IF(条件付合計(図書情報!C$2〜C$201,= A2,図書情報!F$2〜F$201) > 0,'*',null) 　ク：IF(条件付合計(図書情報!C$2〜C$201,= A2,図書情報!F$2〜F$201) = 0,'*',null)

模範解答

a：オ、b：ウ、c：ウ、d：キ

解説

解答の導き方

まず、各列が何を表しているかを整理します。図書情報のC列には貸出中の図書を借りた人の利用者IDが入り、D列には貸出日、E列には返却期日、F列には延滞日数が入ります。利用者情報では、C列が属性、H列が貸出上限冊数、I列が貸出日数です。

a：返却期日の計算

返却期日は、貸出日に、その利用者の属性に応じた貸出日数を足して求めます。ただし、図書情報のC列にあるのは利用者IDであり、貸出日数を調べる表にあるのは属性です。そのため、次のように二段階で調べます。

利用者IDを使って利用者情報から属性を取り出します。
その属性を使って、属性ごとの貸出日数を取り出します。
貸出日に貸出日数を足します。

垂直照合は、範囲の左端の列から値を縦に探し、見つかった行の指定列の値を返す関数です。最初の垂直照合では、利用者情報のA列から利用者IDを探し、範囲内の3列目にある属性を取り出します。次の垂直照合では、その属性をG列から探し、範囲内の3列目にある貸出日数を取り出します。

例えば、図書情報で利用者IDが4、貸出日が11-05-08なら、利用者情報から属性の教員を取り出し、教員の貸出日数である28日を加えます。返却期日は11-06-05です。

この二段階の照合を表すのが選択肢オです。なお、垂直照合の最後の0は、利用者IDや属性を完全に一致させて探す指定です。

b：延滞日数の計算

延滞日数は、返却期日を過ぎているときだけ「本日の日付 − 返却期日」で求めます。返却期日が本日以降なら、延滞日数は0です。

問題文では、セルI1に本日の日付を求める式を入れるとあります。また、日付は内部では経過日数として扱われるため、日付同士を引けば日数の差を求められます。

したがって、返却期日が本日以降なら0、返却期日が本日より前なら「本日の日付 − 返却期日」とする選択肢ウが適切です。図1の例では、本日が11-06-15、返却期日が11-06-05なので、延滞日数は10日です。

c：残り貸出冊数の計算

残り貸出冊数は、貸出上限冊数から、現在借りている冊数を引いて求めます。

まず、利用者情報のC列にある属性を使い、G列〜I列の表から貸出上限冊数を取り出します。貸出上限冊数は範囲内の2列目、つまりH列にあります。

次に、図書情報のC列で、利用者情報のA列にある利用者IDと一致する値を数えます。図書情報のC列には、貸出中の図書を借りた人の利用者IDが入るため、この個数がその利用者の現在の貸出冊数です。

そのため、計算は「貸出上限冊数 − 現在借りている冊数」となります。貸出上限冊数を取り出す垂直照合の列番号は2で、現在の貸出冊数を数える条件付個数を引く選択肢ウが適切です。

d：延滞状態の表示

延滞しているかどうかは、利用者ごとに、その人が借りている図書の延滞日数を調べます。

条件付合計は、指定した条件に一致する行だけを対象に、指定した列の値を合計する関数です。ここでは、図書情報のC列が利用者情報のA列の利用者IDと一致する図書に絞り、F列の延滞日数を合計します。

延滞している図書が1冊でもあれば、その延滞日数の合計は0より大きくなり、「*」を表示します。延滞している図書がなければ、合計は0なので空値にします。この条件を表すのが選択肢キです。

誤りやすいポイント

利用者IDと属性を直接対応させてしまうこと
利用者IDから属性を調べ、その属性から貸出日数や貸出上限冊数を調べます。利用者IDと属性表を一度に照合するのではありません。
延滞日数の引き算の向きを逆にすること
延滞している場合は、本日の日付から返却期日を引きます。返却期日から本日の日付を引くと、延滞中に負の値になります。
残り貸出冊数で、借りている冊数を足してしまうこと
残りの冊数を求めるので、貸出上限冊数から現在借りている冊数を引きます。
残り貸出冊数と延滞状態を混同すること
残り貸出冊数があるかどうかは、延滞しているかどうかとは別です。延滞状態は、該当する利用者が借りた図書のF列を調べて判定します。
全利用者の延滞日数をまとめてしまうこと
延滞状態を表示するときは、C列の利用者IDで対象者を絞ってから、F列の延滞日数を合計します。

FAQ

Q: 垂直照合の最後にある0は、何を指定していますか？
A: 探す値を完全一致で照合する指定です。利用者IDや属性を、似た値ではなく一致する値として探します。

Q: 返却期日が本日と同じ場合、延滞日数はいくつですか？
A: 0です。返却期日が本日以降なら、返却期日を過ぎていないため延滞日数は0になります。

Q: 延滞日数を合計して0より大きいか調べるのはなぜですか？
A: 延滞していない貸出中の図書は0日で、延滞している図書は正の値です。そのため、対象者の延滞日数の合計が0より大きければ、少なくとも1冊は延滞しています。

関連キーワード: 垂直照合、完全一致検索、条件付個数、条件付合計、日付の差分

解説を読んでも分からないところは、AIに質問できます。この問題の本文と解説をふまえて答えます。

この設問をAIに質問する

クイズモードで開きます（AIへの質問は無料の会員登録で使えます）

設問2：

問題文を見る

　利用者が図書を借りる際、その情報を登録するマクロBorrowingをワークシート“図書情報”に格納した。ワークシート“図書情報”のセルB203に図書IDを、セルD203に利用者IDを入力して、マクロBorrowingを実行すると、次に示す三つの条件を満たすとき、貸出登録される。　 (1) 対象図書が貸出し中でない。 (2) 対象利用者の残り貸出冊数が1以上である。 (3) 対象利用者が現在借りている全ての図書が延滞状態でない。　　に入れる正しい答えを、解答群の中から選べ。

eに関する解答群　ア：論理積(相対(A1, B203, 2) = null, 相対(利用者情報!A1, D203, 3) > 0, 相対(利用者情報!A1, D203, 4) ≠ '*') 　イ：論理積(相対(A1, B203, 2) = null, 相対(利用者情報!A1, D203, 3) > 0, 相対(利用者情報!A1, D203, 4) = '*') 　ウ：論理積(相対(A1, B203, 2) = null, 相対(利用者情報!A1, D203, 3) = 0, 相対(利用者情報!A1, D203, 4) ≠ '*') 　エ：論理積(相対(A1, B203, 2) = null, 相対(利用者情報!A1, D203, 3) = 0, 相対(利用者情報!A1, D203, 4) = '*') 　オ：論理積(相対(A1, B203, 2) ≠ null, 相対(利用者情報!A1, D203, 3) > 0, 相対(利用者情報!A1, D203, 4) ≠ '*') 　カ：論理積(相対(A1, B203, 2) ≠ null, 相対(利用者情報!A1, D203, 3) > 0, 相対(利用者情報!A1, D203, 4) = '*') 　キ：論理積(相対(A1, B203, 2) ≠ null, 相対(利用者情報!A1, D203, 3) = 0, 相対(利用者情報!A1, D203, 4) ≠ '*') 　ク：論理積(相対(A1, B203, 2) ≠ null, 相対(利用者情報!A1, D203, 3) = 0, 相対(利用者情報!A1, D203, 4) = '*')

模範解答

e：ア

解説

解答の導き方

マクロが貸出登録を行うには、問題文にある三つの条件をすべて満たす必要があります。そこで、選択肢の各条件が何を確認しているかを順に見ます。

対象図書が貸出し中でないこと

図1では、図書が貸出し中の場合、列Cの「利用者ID」に借りた人のIDが入ります。貸出し中でなければ、列Cは空値です。

B203に入力された図書IDを使って対象の図書情報を探し、そこから2列右の値を調べるのが、相対(A1, B203, 2)です。図書IDの列Aから2列右は列Cなので、確認するのは対象図書の「利用者ID」です。

したがって、貸出し中でないことを表す条件は、相対(A1, B203, 2) = nullです。nullは、ここでは空値を意味します。
対象利用者の残り貸出冊数が1以上であること

図2では、利用者の「残り貸出冊数」は列Dにあります。D203に入力された利用者IDを使って利用者情報を探し、利用者IDの列Aから3列右にある値を調べるのが、相対(利用者情報!A1, D203, 3)です。

残り貸出冊数が1以上であれば借りられるので、この条件は0より大きいこと、つまり相対(利用者情報!A1, D203, 3) > 0です。
対象利用者が借りている図書に延滞がないこと

図2では、延滞中の図書が1冊でもある利用者の「延滞状態」欄に「*」が入ります。この欄は列Eにあるため、利用者IDの列Aから4列右にある値を調べるのが、相対(利用者情報!A1, D203, 4)です。

延滞状態でないことは、値が「*」ではないことを意味します。したがって、条件は相対(利用者情報!A1, D203, 4) ≠ '*' です。

三つの条件をすべて満たす必要があるため、これらを論理積で結びます。論理積は、結び付けた条件がすべて真のときに真になる判定です。選択肢の中で、図書の利用者IDが空値であること、残り貸出冊数が0より大きいこと、延滞状態が「*」ではないことをまとめているのはアです。

誤りやすいポイント

貸出し中でないことを確認する条件は、利用者IDが空値であることです。利用者IDが入っていることを条件にすると、すでに貸出し中の図書を選んでしまいます。
残り貸出冊数が1以上という条件は、値が0より大きいことです。残り貸出冊数が0の利用者は、新たに借りられません。
延滞状態が「」なら延滞中です。延滞がないことを確かめる条件は、「」と等しいことではなく、「*」と異なることです。
論理積では、三つの条件のうち一つでも満たさないと貸出登録されません。どれか一つだけ満たせばよい、という判定ではありません。

FAQ

Q: 残り貸出冊数があれば、延滞中でも借りられるのではないですか？

A: この問題では、残り貸出冊数があることに加えて、借りている図書に延滞がないことも条件です。そのため、残り冊数があっても延滞状態なら貸出登録されません。

Q: 延滞状態の欄が空値であることを直接条件にしないのはなぜですか？

A: 「」が延滞ありを表すため、「」と異なることを条件にすれば、延滞状態ではないことを判定できます。

Q: なぜ三つの条件を論理積で結びますか？

A: 問題文の貸出条件は三つとも満たす必要があるからです。どれか一つでも満たさない場合は、貸出登録できません。

関連キーワード: 論理積、相対参照、空値、条件判定、表計算

解説を読んでも分からないところは、AIに質問できます。この問題の本文と解説をふまえて答えます。

この設問をAIに質問する

クイズモードで開きます（AIへの質問は無料の会員登録で使えます）

設問3：

問題文を見る

　全利用者の過去の貸出履歴に基づき図書を推薦する機能について検討するために、ワークシート“貸出履歴”及びマクロRecommendBooksを作成した。

(1) 図書IDをセルA2〜A201に、利用者IDをセルB1〜AY1に入力する。セルB2〜AY201には、利用者が図書を借りたことがあるならば1を、無ければ0を入力する。 (2) セルB204に利用者IDを入力してマクロRecommendBooksを実行すると、セルAZ2〜AZ201にその利用者に対する個々の図書の推薦度の値を表示する。 (3) 利用者IDにiを指定したとき、各図書の推薦度は次の方法で算出する。　① 指定した利用者と残りの全ての利用者間の類似度を数値で表現し、セルB202〜AY202に求める。利用者IDがi、jの利用者間の類似度は、次式で定義する

s_{ij}

で表現する。

s_{ij} = ⎩ ⎨ ⎧ \frac{\sum _{k = 1}^{200} ( x _{k i} \times x _{k j} )}{200} 0 (i \neq = j) (i = j)

　ここで、

x_{k i}

は、利用者ID iの利用者が、図書ID kの図書を借りたことがあるならば1、そうでなければ0である。

　② 利用者ID iの図書ID kに対する推薦度

r_{k i}

は類似度

s_{ij}

を用いて表現し、セルAZ2〜AZ201に表示する。

r_{k i} = {0 \sum_{j = 1}^{50} (s_{ij} \times x_{k j}) (x_{k i} = 1 又は図書 ID k の図書が貸出し中の場合) (それ以外)

　　マクロRecommendBooks中のに入れる正しい答えを、解答群の中から選べ。

基本情報技術者試験（平成24年午後問13 マクロ：RecommendBooks）

f, hに関する解答群　ア：相対(A1, B204, K) * 相対(A1, J, K) 　イ：相対(A1, B204, K) * 相対(A1, K, J) 　ウ：相対(A1, J, K) * 相対(A1, J, K) 　エ：相対(A1, J, K) * 相対(A1, K, 51) 　オ：相対(A1, J, K) * 相対(A1, K, J) 　カ：相対(A1, K, B204) * 相対(A1, K, J) 　キ：相対(A1, K, J) * 相対(A1, 201, J) 　ク：相対(A1, K, J) * 相対(A1, 201, K) 　 gに関する解答群　ア：論理積(相対(A1, K, B204) = 0, 相対(図書情報!A1, K, 2) = null) 　イ：論理積(相対(A1, K, B204) = 0, 相対(図書情報!A1, K, 2) ≠ null) 　ウ：論理積(相対(A1, K, B204) = 1, 相対(図書情報!A1, K, 2) = null) 　エ：論理積(相対(A1, K, B204) = 1, 相対(図書情報!A1, K, 2) ≠ null) 　オ：論理和(相対(A1, K, B204) = 0, 相対(図書情報!A1, K, 2) = null) 　カ：論理和(相対(A1, K, B204) = 0, 相対(図書情報!A1, K, 2) ≠ null) 　キ：論理和(相対(A1, K, B204) = 1, 相対(図書情報!A1, K, 2) = null) 　ク：論理和(相対(A1, K, B204) = 1, 相対(図書情報!A1, K, 2) ≠ null)

模範解答

f：カ、g：ア、h：キ

解説

解答の導き方

「貸出履歴」では、セルB2〜AY201に、利用者が各図書を借りたことがあるかを0または1で記録しています。1は「借りたことがある」、0は「借りたことがない」という意味です。

マクロ（決まった処理を自動で実行する手順）の中にある「相対(A1, 行, 列)」は、セルA1から指定された行数・列数だけ移動したセルを表します。たとえば、相対(A1, K, J)は、図書IDがKの図書を利用者IDがJの利用者が借りたかを示すセルです。

f：利用者どうしの類似度を求める部分

問題文では、利用者IDが

i

と

j

の利用者間の類似度を、各図書についての借用状況の積を合計して求めると定義しています。

相対(A1, K, B204)は、指定した利用者 $i$ が図書ID $K$ を借りたことがあるかを表します。
相対(A1, K, J)は、利用者ID $J$ の利用者が図書ID $K$ を借りたことがあるかを表します。

したがって、Kごとに加算する値は、指定した利用者と利用者ID

J

の利用者の借用状況を掛け合わせたものです。両方がその図書を借りていれば積は1になり、どちらかが借りていなければ積は0になります。

そのため、fには「相対(A1, K, B204) * 相対(A1, K, J)」を選びます。これは解答群のカです。Kの繰返しが終わった後にNumBookで割る処理も、式にある「200で割る」計算に対応しています。

また、利用者ID

J

が指定した利用者IDと同じ場合は、繰返しの前に類似度を0にし、加算を行いません。これは問題文の「

i = j

の場合は0」という定義に沿っています。

g：推薦度を計算する図書の条件

推薦度の式では、指定した利用者がすでに借りた図書、または貸出し中の図書は、推薦度を0にします。反対に、推薦度の合計を計算するのは、次の両方を満たす図書です。

指定した利用者がまだ借りたことがない
図書が貸出し中ではない

相対(A1, K, B204)は、指定した利用者が図書ID

K

を借りたことがある場合に1、ない場合に0です。したがって「= 0」が必要です。

また、図書情報シートのC列には、貸出し中であれば利用者IDが入力され、貸出し中でなければ空値になります。相対(図書情報!A1, K, 2)はC列に当たるため、「= null」が貸出し中ではない状態を表します。

この2つの条件は、どちらも満たす必要があるので論理積で結びます。したがって、gは解答群のアです。

h：推薦度に加算する値

条件を満たす図書については、利用者ID

J

の利用者の類似度と、その利用者が図書ID

K

を借りたことがあるかを掛け合わせ、Jを変えながら合計します。

相対(A1, 201, J)は、利用者ID $J$ に対応する類似度 $s_{ij}$ です。類似度はセルB202〜AY202に格納されるため、行方向の指定が201のセルが使われます。
相対(A1, K, J)は、利用者ID $J$ の利用者が図書ID $K$ を借りたことがあるかを表す $x_{k j}$ です。

よって、加算する値は「相対(A1, K, J) * 相対(A1, 201, J)」です。これは解答群のキです。Jを1からNumUserまで繰り返して合計することで、問題文の推薦度

r_{k i}

の式になります。

誤りやすいポイント

fでは、指定した利用者の借用状況と、繰返し中の利用者の借用状況を掛け合わせます。相対(A1, K, B204)が指定した利用者、相対(A1, K, J)が利用者ID $J$ の利用者です。
gは論理積です。「指定した利用者が借りていない」だけでなく、「図書が貸出し中ではない」ことも必要です。どちらか一方だけを満たしても推薦度の計算には進みません。
図書情報シートのC列は、貸出し中なら利用者IDが入り、貸出し中でなければ空値です。そのため、貸出し中でない条件は「= null」です。
hでは、利用者ID $J$ に対応する類似度と、その利用者が図書を借りたことがあるかを掛けます。類似度だけ、または借用状況だけを合計するのではありません。

FAQ

Q: fで指定した利用者IDと同じJのとき、なぜ計算しないのですか？
A: 問題文では、同じ利用者どうしの類似度を0と定義しています。そのため、最初に0を設定し、指定した利用者と同じ場合は図書ごとの加算を行わないようにしています。

Q: gで論理和を選ばないのはなぜですか？
A: 推薦度を計算するのは、「指定した利用者がまだ借りていない」と「貸出し中ではない」の両方を満たす図書です。どちらか一方だけでよい条件ではないため、論理積になります。

Q: 借りられる図書でも、推薦度が0になることはありますか？
A: あります。類似する利用者がその図書を借りたことがなければ、hで加算される値がすべて0になり、推薦度も0になります。

関連キーワード: 類似度、二値データ、論理積、相対参照、繰返し処理

解説を読んでも分からないところは、AIに質問できます。この問題の本文と解説をふまえて答えます。

この設問をAIに質問する

クイズモードで開きます（AIへの質問は無料の会員登録で使えます）

\ せっかくなら /

基本情報技術者を
クイズ形式で学習しませんか？

クイズ画面へ遷移する→

すぐに利用可能！

基本情報技術者 2012年 春期 午後 問13

次の表計算、ワークシート及びマクロの説明を読んで、設問1〜3に答えよ。

.css-epvm6{white-space:nowrap;}設問1：

模範解答

解説

解答の導き方

誤りやすいポイント

FAQ

設問2：

模範解答

解説

解答の導き方

誤りやすいポイント

FAQ

設問3：

模範解答

解説

解答の導き方

誤りやすいポイント

FAQ

基本情報技術者 2012年春期午後問13

設問1：