基本情報技術者 2015年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1〜3に答えよ。
〔表計算の説明〕
ある会社では、社員ごとの社内向け学習教材の学習進捗状況を表計算ソフトで管理している。
〔ワークシート：学習進捗管理（単元記述部分）〕
(1) 社員ごとにワークシート“学習進捗管理”が用意され、学習進捗状況が記載される。ワークシート“学習進捗管理”の単元記述部分である列A〜Gの例を、図1に示す。
(2) 学習教材は12の単元から構成されており、単元にはそれぞれ固有のIDが割り振られている。
(3) セルA2〜A13には、単元のIDが昇順に格納されている。単元のIDは100から昇順に100刻みで付与されている。
(4) セルB2〜B13には、単元名が格納されている。
(5) 各単元は複数の項目から構成される。セルC2〜C13には、単元に含まれる項目の学習順序（以下、項目学習順序という）が格納されている。セルの値が“任意”の場合はどの項目から学習してもよいことを示し、セルの値が“順次”の場合は各項目を項目のIDが小さいものから順番に学習しなければならないことを示している。
(6) セルD2〜D13には、その単元の学習を開始してから完了するまでの学習に要する標準的な日数（以下、標準日数という）が格納されている。
(7) セルE2〜E13とセルF2〜F13には、それぞれ対応する単元の学習を開始した日（以下、学習開始日という）、学習を完了した日（以下、学習完了日という）がそれぞれマクロStartLearningとFinishLearningを用いて設定される。日付はyyyy-mm-ddの形式で表示されるが、表計算ソフトの内部では1970年1月1日からの経過日数を整数値で保持している。計算にはこの内部の整数値を利用する。
(8) セルG2〜G13には、それぞれの単元の学習に関する判定結果が表示される。学習完了日が設定されたとき、学習に要した日数が標準日数以下の場合には“○”が表示され、そうでなければ“△”が表示される。学習開始日も学習完了日も学習に要した日数に含まれる。ここで、休日は考慮せず、休日も1日と数える。例えば、学習開始日が2014年5月10日で学習完了日が2014年5月12日の場合、学習に要した日数は3である。また、学習完了日が設定されていない場合には空値が表示される。
設問1：
問題文を見る ワークシート“学習進捗管理”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
それぞれの単元の学習が標準日数内に完了したかどうかを表示するために、次の式をセルG2に入力し、セルG3〜G13に複写する。
IF(F2 = null,a)
aに関する解答群
ア：IF(F2 − E2 + 1 ≦ D2,'○','△'), null
イ：IF(F2 − E2 ≦ D2,'○','△'), null
ウ：IF(F2 − E2 − 1 ≦ D2,'○','△'), null
エ：null,IF(F2 − E2 + 1 ≦ D2,'○','△')
オ：null,IF(F2 − E2 ≦ D2,'○','△')
カ：null,IF(F2 − E2 − 1 ≦ D2,'○','△')
模範解答
a：エ
解説
解答の導き方
この式では、セルF2に学習完了日が入っているかを先に調べます。IF関数は「条件が成り立つ場合の値」と「成り立たない場合の値」を順に指定する関数です。
本文には、学習完了日が設定されていない場合は「空値が表示される」とあります。したがって、F2が空値なら、式の結果も空値にします。
F2に学習完了日が入っている場合は、学習に要した日数を標準日数と比べます。学習開始日と学習完了日の両方を日数に含めるため、日付の差に1を加えます。
学習に要した日数は、次の式で求められます。
学習完了日 − 学習開始日 + 1
例えば、本文の例のように学習開始日が2014年5月10日で学習完了日が2014年5月12日の場合、日付の差は2日です。開始日も数えるので、日となります。
日付は「1970年1月1日からの経過日数を整数値で保持」しているため、セルF2からセルE2を引けば、2つの日付の間の日数を計算できます。そのため、学習に要した日数が標準日数以下かどうかは、F2 − E2 + 1とD2を比べれば判断できます。
以上から、F2が空値なら空値を返し、そうでなければ「F2 − E2 + 1 ≦ D2」を調べる形にします。したがって、aに入るのはエです。
誤りやすいポイント
- 学習完了日が空値の場合と、学習に要した日数を判定する場合の順序を逆にしないようにします。まずF2が空値かを調べ、日数の計算はF2に日付がある場合に行います。
- F2 − E2だけでは、開始日を数えません。学習開始日と学習完了日の両方を含めるため、最後に1を加えます。
- 「標準日数以下」なので、学習に要した日数が標準日数と同じ場合も“○”です。比較には「≦」を使います。
FAQ
Q: なぜ日付を引くだけでは、学習に要した日数にならないのですか？
A: 日付の差は、開始日から完了日までの間隔を表すためです。学習開始日も1日として数えるので、日付の差に1を加えます。
A: 日付の差は、開始日から完了日までの間隔を表すためです。学習開始日も1日として数えるので、日付の差に1を加えます。
Q: 学習完了日が空値のとき、日数の判定をしないのはなぜですか？
A: 完了日がなければ、開始日から完了日までの日数を求められません。そのため、先にF2が空値かを調べ、空値なら結果も空値にします。
A: 完了日がなければ、開始日から完了日までの日数を求められません。そのため、先にF2が空値かを調べ、空値なら結果も空値にします。
Q: 標準日数と同じ日数で完了した場合は、どちらの判定になりますか？
A: 「標準日数以下」なので、同じ日数も条件を満たします。“○”が表示されます。
A: 「標準日数以下」なので、同じ日数も条件を満たします。“○”が表示されます。
関連キーワード: IF関数、条件分岐、日付計算、空値
設問2：
問題文を見る ワークシート“学習進捗管理”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。ここで、関数“表引き”は、行の位置として0が指定された場合には、nullを返すものとする。
〔ワークシート：学習進捗管理（項目記述部分）〕
(1) この学習教材の項目の総数は52である。ワークシート“学習進捗管理”の項目記述部分である列I〜Mの例を、図2に示す。
(2) セルI2〜I53には、項目のIDが昇順に格納されている。項目のIDは、その項目が属する単元のIDの下2桁を01から始まる連番にしたものである。
(3) セルJ2〜J53には、項目名が格納されている。
(4) セルK2〜K53には、それぞれの項目が学習開始可能であれば“可”が表示され、そうでなければ“不可”が表示される。学習開始可能であるとは、次の条件①、②をともに満たしていることをいう。
① 項目が属する単元が学習開始可能である。単元が学習開始可能であるとは、単元のIDが100であるか、又は、より小さいIDの単元の学習が全て完了していることをいう。
② 次の条件(a)又は(b)のどちらかを満たしている。
(a) 項目が属する単元の項目学習順序が“任意”である。
(b) 項目が属する単元の項目学習順序が“順次”であり、その項目のIDの下2桁が01であるか、又は、その単元の中でより小さいIDの項目の学習が全て完了している。
(5) セルL2〜L53とセルM2〜M53には、それぞれ対応する項目の学習開始日、学習完了日がそれぞれマクロStartLearningとマクロFinishLearningを用いて設定される。
(1) それぞれの項目が学習開始可能か否かを表示するために、セルK2に“可”を入力する。
(2) 次の式をセルK3に入力し、セルK4〜K53に複写する。
IF(論理積(
論理和(
切捨て(I3,−2) = 100,
表引き($F$2〜$F$13,b,1) ≠ null),
論理和(
垂直照合(切捨て(I3,−2),$A$2〜$C$13,3,0) = c,
剰余(I3,100) = 1,
d)),
'可','不可')
bに関する解答群
ア：照合一致(切捨て(I2,−2),$A$2〜$A$13,0)
イ：照合一致(切捨て(I2,−2),$A$2〜$A$13,0) − 1
ウ：照合一致(切捨て(I2,−2),$A$2〜$A$13,0) + 1
エ：照合一致(切捨て(I3,−2),$A$2〜$A$13,0)
オ：照合一致(切捨て(I3,−2),$A$2〜$A$13,0) − 1
カ：照合一致(切捨て(I3,−2),$A$2〜$A$13,0) + 1
cに関する解答群
ア：'順次'
イ：'任意'
dに関する解答群
ア：L3 = null
イ：L3 ≠ null
ウ：M2 = null
エ：M2 ≠ null
オ：M3 = null
カ：M3 ≠ null
模範解答
b：オ、c：イ、d：エ
解説
解答の導き方
セルK3の式は、項目を「学習開始可能」と判定するために、2つの条件を調べています。式にある「論理積」は両方の条件を満たすこと、「論理和」は並んだ条件のうち少なくとも1つを満たすことを意味します。
1. 項目が属する単元を学習開始できるか
条件①では、項目が属する単元が学習開始可能かを調べます。単元IDは100刻みなので、例えば項目IDの102から下2桁を切り捨てると、属する単元のIDである100になります。同じように、201からは200が得られます。
式の「表引き」は、指定した位置にある値を取り出す関数です。まず「照合一致」で、項目が属する単元IDがA2〜A13の中の何番目にあるかを調べます。次に、その位置から1を引きます。こうすると、F2〜F13の中から直前の単元の学習完了日を取り出せます。
単元IDが100の場合は、表引きの位置が0になり、指定のとおりnullが返ります。そのため、式では単元IDが100であること自体を別の条件として調べています。100より大きい単元については、直前の単元の学習完了日がnullでないことを確認します。したがって、bには、現在の項目IDから単元IDを求め、その照合位置から1を引くオが入ります。
「直前の単元の完了だけでよいのか」と思うかもしれません。ここでは、決められた開始条件に従って学習が進むことが前提です。例えば単元IDが200の学習は、単元IDが100の学習を完了しないと開始できません。単元IDが200の完了日が設定されているなら、開始に必要だった単元IDが100も完了しています。同様に、単元IDが300の完了日が設定されているなら、単元IDが200は完了済みであり、さらに単元IDが200の開始条件から、単元IDが100も完了済みです。このように前の単元の開始条件を順にたどれるため、直前の単元が完了していることを調べれば、それより前の単元も完了していると判断できます。
2. 単元内で項目をどの順番で学ぶか
条件②では、単元内の項目の学習順序を調べます。式の「垂直照合」は、単元IDをA2〜C13から探し、指定された列の値を取り出します。列番号の3は範囲内の3列目、つまり列Cを表します。列Cには項目学習順序が入っているため、取り出した値が「任意」なら、項目の順番を問わず学習できます。
一方、項目学習順序が「順次」の場合は、単元内で最初の項目か、それより小さいIDの項目を全て完了している必要があります。剰余(I3,100)が1になるのは、項目IDが101、201のように、下2桁が01のときです。これは単元内の最初の項目を表します。
最初の項目でない場合は、直前の項目の学習完了日を調べます。セルK3の式で現在の項目はI3の項目ですから、直前の項目の学習完了日はM2です。M2がnullでないことを調べるので、dにはエが入ります。式をK4以降に複写すると、参照先もM3、M4のように1行ずつ移ります。
「順次」の場合も、直前の項目の完了だけでよいのは、各項目が決められた順に進むためです。例えば項目IDが203の開始には、直前の202の完了が必要です。202の開始には201の完了が必要なので、202が完了していれば201も完了しています。これを順にたどることで、直前の項目の完了から、それより小さいIDの項目も完了済みと判断できます。
したがって、cには、順番を問わず学習できることを示す「任意」を選びます。解答群ではイです。
図2の項目IDが302の行を例にすると、属する単元は300で、直前の単元である200の完了日が設定されているため、単元についての条件は満たします。一方、単元300の項目学習順序は「順次」です。302は最初の項目ではなく、直前の項目301の学習完了日も設定されていないため、項目についての条件を満たしません。そのため、学習開始可能とは判定されません。
誤りやすいポイント
- 「照合一致」が返すのは、ワークシート上の行番号ではなく、指定範囲内での位置です。A2〜A13の2番目に単元IDがあれば、位置は2です。
- 単元IDが100のとき、照合位置から1を引くと0になります。これはエラーではなく、表引きがnullを返すケースです。単元IDが100であることを別に調べる条件が必要です。
- セルK3の式が判定するのは項目IDがI3の項目です。したがって、直前の項目の完了日として見るのはM3ではなくM2です。
- 「任意」は、直前の項目が未完了でも学習を開始できるという意味です。「順次」の場合だけ、項目の順番を確認します。
FAQ
Q: 表引きで位置が0になるのは、なぜですか？
A: 単元IDが100のとき、A2〜A13での照合位置は1です。そこから1を引くと0になり、表引きはnullを返します。単元IDが100は最初の単元なので、式では単元IDが100であることを別に確認しています。
A: 単元IDが100のとき、A2〜A13での照合位置は1です。そこから1を引くと0になり、表引きはnullを返します。単元IDが100は最初の単元なので、式では単元IDが100であることを別に確認しています。
Q: dにM3を選ぶと、なぜいけないのですか？
A: K3が判定するのは項目IDがI3の項目です。この項目を開始できるかを判断するには、1つ前の項目が完了しているかを調べます。その完了日はM2にあります。M3は判定対象の項目自身の完了日です。
A: K3が判定するのは項目IDがI3の項目です。この項目を開始できるかを判断するには、1つ前の項目が完了しているかを調べます。その完了日はM2にあります。M3は判定対象の項目自身の完了日です。
Q: 「順次」で項目IDが201の場合、直前の項目の完了を確認しなくてよいのですか？
A: 201は、その単元で最初の項目です。剰余(I3,100)が1になる項目IDは、下2桁が01の項目です。最初の項目なので、同じ単元内に先に完了すべき項目はありません。
A: 201は、その単元で最初の項目です。剰余(I3,100)が1になる項目IDは、下2桁が01の項目です。最初の項目なので、同じ単元内に先に完了すべき項目はありません。
関連キーワード: 表計算、論理演算、検索関数、相対参照
設問3：
問題文を見る 学習開始日を設定するマクロStartLearningと学習完了日を設定するマクロFinishLearningをワークシート“学習進捗管理”に格納した。マクロStartLearning中のに入れる正しい答えを、解答群の中から選べ。
(1) セルO2に学習を開始する項目のIDを入力し、マクロStartLearningを実行すると、次の①、②をともに満たす場合、対応する項目の学習開始日にマクロが実行された日付を設定する。
① その項目が学習開始可能である。
② その項目の学習開始日が空値である。
また、その項目が属する単元の学習開始日が空値ならば、単元の学習開始日にもマクロが実行された日付を設定する。
(2) マクロStartLearningの実行時のセルO2には、セルI2〜I53にあるいずれかの項目のIDが正しく入力されているものとする。
(3) マクロStartLearningでは、マクロの実行日を取得するために表1に示す関数を用いる。
eに関する解答群
ア：表引き(K2〜K53, ItemRow, 1) = '可', 表引き(E2〜E13, UnitRow, 1) = null
イ：表引き(K2〜K53, ItemRow, 1) = '可', 表引き(E2〜E13, UnitRow, 1) ≠ null
ウ：表引き(K2〜K53, ItemRow, 1) = '可', 表引き(L2〜L53, ItemRow, 1) = null
エ：表引き(K2〜K53, ItemRow, 1) = '可', 表引き(L2〜L53, ItemRow, 1) ≠ null
オ：表引き(K2〜K53, ItemRow, 1) = '不可', 表引き(E2〜E13, UnitRow, 1) = null
カ：表引き(K2〜K53, ItemRow, 1) = '不可', 表引き(E2〜E13, UnitRow, 1) ≠ null
キ：表引き(K2〜K53, ItemRow, 1) = '不可', 表引き(L2〜L53, ItemRow, 1) = null
ク：表引き(K2〜K53, ItemRow, 1) = '不可', 表引き(L2〜L53, ItemRow, 1) ≠ null
fに関する解答群
ア：表引き(E2〜E13, ItemRow, 1)
イ：表引き(F2〜F13, ItemRow, 1)
ウ：表引き(F2〜F13, UnitRow, 1)
エ：表引き(L2〜L53, ItemRow, 1)
オ：表引き(L2〜L53, UnitRow, 1)
カ：表引き(M2〜M53, ItemRow, 1)
キ：表引き(M2〜M53, UnitRow, 1)
模範解答
e：ウ、f：エ
解説
解答の導き方
まず、マクロが調べる二つの位置を整理します。
- ItemRowは、セルO2のIDが、項目の一覧であるI2〜I53の何番目にあるかを表します。
- UnitRowは、セルO2のIDを単元のIDに直した値が、単元の一覧であるA2〜A13の何番目にあるかを表します。
照合一致は、探している値が範囲内の何番目にあるかを返します。つまり、ItemRowは項目一覧の中の位置、UnitRowは単元一覧の中の位置です。どちらも、ワークシートの行番号そのものではありません。
次に、マクロが項目の学習開始日を設定する条件を考えます。問題文では、項目について「学習開始可能である」ことと「学習開始日が空値である」ことの両方を満たす場合に、開始日を設定するとあります。
項目の一覧では、学習開始可能かどうかはK列、学習開始日はL列です。したがって、条件は次の二つです。
- K列の該当項目が「可」である
- L列の該当項目が空値である
この条件に一致するのは、eの選択肢ウです。「可」だけではなく、開始日がまだ設定されていないことも確認する必要があります。すでに開始日がある項目を、マクロの実行日で上書きしないためです。
条件を満たすと、マクロは相対(L1, ItemRow, 0)に本日()を設定します。相対は、指定したセルから行・列の位置をずらして対象セルを決める指定です。ItemRowは項目一覧の位置なので、この指定は、該当項目のL列に実行日を設定します。
続いて、該当する単元の学習開始日を確認します。単元の開始日はE列にあり、相対(E1, UnitRow, 0)がその単元のセルを指します。そのセルが空値の場合に限り、fで選んだ値を設定します。
ここで必要なのは、さきほど開始日を設定した項目のL列の値です。L2〜L53は項目の一覧なので、そこから値を取り出す位置にはItemRowを使います。選択肢では、表引き(L2〜L53, ItemRow, 1)に当たるfの選択肢エです。
誤りやすいポイント
- 「学習開始可能」の条件だけで判断しないことです。学習開始日がすでに入っている場合は、開始日を設定しません。
- eの条件に、単元の学習開始日が空値かどうかを含めないことです。単元の開始日は、項目の開始日を設定した後に、別の判定で確認します。
- ItemRowとUnitRowを取り違えないことです。L2〜L53は項目の一覧なのでItemRowを使い、E2〜E13は単元の一覧なのでUnitRowを使います。
- fに単元の完了日や項目の完了日を選ばないことです。設定するのは単元の学習開始日なので、項目の学習開始日を取得します。
FAQ
Q: なぜ単元の学習開始日にも、本日()を直接設定しないのですか。
A: マクロは、項目の学習開始日を設定した後、その値を単元の学習開始日に使います。こうすれば、単元の開始日は、最初に学習を開始した項目の開始日と一致します。
A: マクロは、項目の学習開始日を設定した後、その値を単元の学習開始日に使います。こうすれば、単元の開始日は、最初に学習を開始した項目の開始日と一致します。
Q: 単元の学習開始日がすでに入っている場合はどうなりますか。
A: 空値の場合だけ値を設定するため、すでに設定された単元の開始日は上書きされません。
A: 空値の場合だけ値を設定するため、すでに設定された単元の開始日は上書きされません。
Q: ItemRowとUnitRowは、どちらも一覧の何番目かを示すのですか。
A: はい。ただし、ItemRowは項目一覧の位置、UnitRowは単元一覧の位置です。値を取り出す範囲に合わせて使い分けます。
A: はい。ただし、ItemRowは項目一覧の位置、UnitRowは単元一覧の位置です。値を取り出す範囲に合わせて使い分けます。
関連キーワード: 条件判定、表検索、相対位置、空値
