基本情報技術者 2009年 秋期 午後 問13
次の表計算及びワークシートの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
ある企業では、アルバイトの勤務を管理するための勤怠管理表と出勤割当表の作成を手作業で行っていたが、これを表計算ソフトで行うことにした。
〔ワークシート：勤怠管理〕
(1) 表計算ソフトのワークシート“勤怠管理”を作成した。その例を図1に示す。
(2) 時刻及び時間の表記はh:mm, hh:mm又はhhh:mmで表示されるが、内部では24時間を1とする実数値で記録されている。例えば、10:30は10.5/24 = 0.4375、18:00は18/24 = 0.75である。計算にはこの数値を利用する。
(3) 図1中の各項目の説明は、次のとおりである。
ID, 氏名：アルバイトの識別番号と氏名である。
日付：1か月分の日付がyyyy-mm-ddの形式で表示される。日数30日以下の月でも31日まで列挙する。
出社予定：出社予定日には1を、出社予定でない日や2009-09-31のように存在しない日には0を格納する。
出社時刻：始業時刻は10時である。10時に遅れた場合は遅刻扱いとなる。出社しなかった日、又は存在しない日のセルは空白とする。
退社時刻：終業時刻は17時である。17時前の退社は早退扱いとなる。出社しなかった日、又は存在しない日のセルは空白とする。
勤務時間：出社してから退社するまでの時間である。ただし、12時から13時までは昼休みであり、この時間は勤務時間に含まれない。出社しなかった日は0:00が表示される。また、日をまたいで勤務することはない。
勤怠理由：初期値は0である。欠勤、遅刻、早退について正当な理由がある場合は1を設定する。
ペナルティ：正当な理由なく出社予定日に欠勤した場合は3を、正当な理由なく遅刻又は早退した場合は1を表示する。これら以外の場合は0を表示する。
合計（勤務時間）：当該月の勤務時間の合計を表示する。
合計（ペナルティ）：当該月のペナルティの合計を表示する。ペナルティの合計は、出勤割当表を作成するときに利用する。
設問1：
問題文を見る ワークシート“勤怠管理”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
各日のペナルティを算出するために、ワークシート“勤怠管理”のセルG4に次の式を入力し、セルG5～G34に複写する。
IF(F4=1,0,IF(a,3,
IF(b,1,0)))
ペナルティの合計を算出するために、セルG35には次の式を入力する。
合計(G4～G34)
各日の勤務時間を算出するために、ワークシート“勤怠管理”のセルE4には次の式を入力し、セルE5～E34に複写する。算術演算のとき、空白セルは0として取り扱われる。
c
勤務時間の合計を算出するために、セルE35には次の式を入力する。
合計(E4～E34)
aに関する解答群
ア：否定(論理積(B4=0,C4=''))
イ：否定(論理積(B4=1,C4=''))
ウ：否定(論理和(B4=0,C4=''))
エ：否定(論理和(B4=1,C4=''))
オ：論理積(B4=1,C4='')
カ：論理和(B4=1,C4='')
bに関する解答群
ア：論理積(B4=1,論理積(C4≦(10/24),D4≧(17/24)))
イ：論理積(B4=1,論理和(C4>(10/24),D4<(17/24)))
ウ：論理積(C4≦(10/24),D4≧(17/24))
エ：論理和(B4=1,論理積(C4≦(10/24),D4≧(17/24)))
オ：論理和(B4=1,論理和(C4>(10/24),D4<(17/24)))
カ：論理和(C4>(10/24),D4<(17/24))
cに関する解答群
ア：D4－C4－1/24
イ：最小(D4,12/24)－最小(C4,12/24)＋最大(D4,13/24)－最大(C4,13/24)
ウ：最小(D4,13/24)－最小(C4,12/24)－1/24
エ：最小(D4,13/24)－最大(C4,12/24)－1/24
オ：最大(D4,12/24)－最小(C4,12/24)＋最大(D4,13/24)－最小(C4,13/24)
カ：最大(D4,13/24)－最小(C4,12/24)－1/24
キ：最大(D4,13/24)－最大(C4,12/24)－1/24
模範解答
a：オ、b：イ、c：イ
解説
解答の導き方
この設問では、ペナルティを決める条件と、昼休みを除いた勤務時間の計算方法を考えます。
1. 欠勤のペナルティを決める条件
ペナルティの式は、最初に「勤怠理由」が1かどうかを調べます。条件によって結果を切り替えるIF関数を使い、勤怠理由が1ならペナルティは0です。つまり、正当な理由がある場合は、欠勤・遅刻・早退のいずれでもペナルティを付けません。
理由がない場合、まず欠勤かどうかを調べます。本文では、出社予定日に出社しなかった場合、出社予定には1が入り、出社時刻は空白になります。したがって、欠勤の条件は「B4=1」かつ「C4=''」です。
論理積は、二つの条件が両方とも成り立つ場合に真となる条件です。この欠勤条件に当てはまればペナルティは3なので、aには「論理積(B4=1,C4='')」であるオを選びます。
2. 遅刻・早退のペナルティを決める条件
欠勤ではない場合、遅刻または早退かどうかを調べます。
本文では、始業時刻は10時で、10時より遅く出社すると遅刻です。また、終業時刻は17時で、17時より前に退社すると早退です。時刻は24時間を1とする数値で記録されるため、10時は10/24、17時は17/24です。
遅刻は「C4>(10/24)」、早退は「D4<(17/24)」です。どちらか一方でも当てはまればよいため、二つの条件は論理和で結びます。論理和は、複数の条件のうち一つでも成り立てば真となる条件です。
さらに、ペナルティの対象は出社予定日だけなので、「B4=1」も必要です。よって、bには「論理積(B4=1,論理和(C4>(10/24),D4<(17/24)))」であるイを選びます。
3. 勤務時間から昼休みを除く
勤務時間には、出社から退社までのうち12時から13時までの昼休みを含めません。そこで、勤務時間を「12時まで」と「13時以降」の二つに分けて計算します。
時刻の内部値は、たとえば12時なら12/24、13時なら13/24です。最小は指定した時刻を超えない範囲に切り詰め、最大は指定した時刻より前の値をその時刻まで引き上げます。このため、次の式では昼休みの区間が計算から外れます。
最小(D4,12/24)－最小(C4,12/24)＋最大(D4,13/24)－最大(C4,13/24)
たとえば、10:00に出社して17:15に退社した場合、12時までが2時間、13時以降が4時間15分です。合計は6時間15分となり、図1の勤務時間と一致します。
出社しなかった日は出社時刻と退社時刻が空白です。本文のとおり、計算では空白セルを0として扱います。この式では、空白の場合も二つの時間の差がそれぞれ0になり、勤務時間は0:00になります。したがって、cには上の式であるイを選びます。
なお、月の合計は、ペナルティなら「合計(G4～G34)」、勤務時間なら「合計(E4～E34)」で求めます。
誤りやすいポイント
- 欠勤の条件を、出社予定でない日と混同しないようにします。出社予定でない日はB4が0なので、出社時刻が空白でも欠勤ではありません。
- 遅刻と早退は、両方に当てはまる必要はありません。遅刻または早退のどちらか一方でペナルティの対象になります。
- 10時ちょうどの出社や17時ちょうどの退社は、遅刻・早退ではありません。そのため、比較は「>」と「<」です。
- 勤務時間から引くのは昼休みの1時間だけです。出社から退社までの時間をそのまま勤務時間にしないようにします。
FAQ
Q: 欠勤の条件を確認した後に、遅刻・早退の条件を調べるのはなぜですか？
A: 欠勤日も、出社時刻や退社時刻が空白です。そのまま遅刻・早退の条件を調べると、空白を使った比較が起きるため、先に欠勤を判定してペナルティを決めます。
A: 欠勤日も、出社時刻や退社時刻が空白です。そのまま遅刻・早退の条件を調べると、空白を使った比較が起きるため、先に欠勤を判定してペナルティを決めます。
Q: 出社時刻が10:00なら、遅刻の条件に当てはまりますか？
A: 当てはまりません。遅刻は10時より後の出社なので、「C4>(10/24)」と判定します。
A: 当てはまりません。遅刻は10時より後の出社なので、「C4>(10/24)」と判定します。
Q: 昼休みの時間は、勤務時間の式のどこで除かれますか？
A: 12時までの時間と13時以降の時間だけを足し合わせています。12時から13時までの間は、どちらの計算にも入りません。
A: 12時までの時間と13時以降の時間だけを足し合わせています。12時から13時までの間は、どちらの計算にも入りません。
関連キーワード: 表計算、条件分岐、論理積、論理和、時刻計算、最小値・最大値
設問2：
問題文を見る ワークシート“出勤割当表”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：勤務可能調査〕
月末になると翌月の出勤割当表の作成に先立って、アルバイトの申告した勤務可能日に基づいて、ワークシート“勤務可能調査”を作成する。その例を図2に示す。ワークシート“勤務可能調査”では、勤務可能なことを1で、勤務不可能なことを0で表す。また、存在しない日は、全員が0となる。
なお、アルバイトは15名とする。
〔ワークシート：出勤割当表〕
(1) ワークシート“勤務可能調査”に基づいて、ワークシート“出勤割当表”を作成する。その例を図3に示す。ワークシート“出勤割当表”では、勤務を割り当てた場合を1で、割り当てなかった場合を0で表す。
(2) 図3中の各項目の説明は、次のとおりである。
経験順位：業務経験が長い人ほど小さい値が入力されている。同順位の人はいない。
先々月ペナルティ：各アルバイトの先々月のペナルティの合計が入力されている。
先々月ペナルティ順位：各アルバイトの先々月ペナルティを昇順に順位付けする。先々月ペナルティが同じ場合は同順位となる。
(3) 出勤割当表の作成は、月初から月末まで順番に1日分ずつ次の規則に基づいて行う。
(a) その日に出社可能な人が5人以下の場合は、その全員を割り当てる。
(b) その日に出社可能な人が5人より多い場合は、その中から次の優先順位で5人を割り当てる。
① 先々月ペナルティ順位がより小さい。
② ①が等しい場合は、既に割当てが決められた期間（月初から前日まで）に割り当てられた回数がより少ない。
③ ①, ②がともに等しい場合は、経験順位がより小さい。
(4) (3)の規則を定式化するために、出社の可否、先々月ペナルティ順位、前日までの割当回数、経験順位からなる次のような7けたの数値（以下、評価値という）を定義する。
この評価値が小さい人から、順番に出勤を割り当てる。評価値の計算には、ワークシート“出勤割当表”の列AB〜APを使用する。
(5) ワークシート“出勤割当表”で用いる関数を表に示す。
(6) 複数のワークシート間でデータを参照する場合には、“ワークシート名!セル”又は“ワークシート名!セル範囲”という形式で指定する。
先々月ペナルティ順位を算出するために、ワークシート“出勤割当表”のセルB38には次の式を入力し、セルC38～P38に複写する。
順位(B37,$B37～$P37,0)
次に、セルAB3～AP33に評価値を設定する。セルAB3には次の式を入力し、セルAC3～AP3に複写する。
(((1－勤務可能調査!B3) * 100 + d) * 100) * 100 + e
セルAB4には次の式を入力し、セルAB4～AP33に複写する。
(((1－勤務可能調査!B4) * 100 + d) * 100 + f) * 100 + e
出勤者を決定するために、セルB3には次の式を入力し、セルB3～P33に複写する。
IF(論理積(g, h),1,0)
各アルバイトの割当日数の合計を算出するために、セルB34には次の式を入力し、セルC34～P34に複写する。
合計(B3～B33)
各日の合計割当人数を算出するために、セルQ3には次の式を入力し、セルQ4～Q33に複写する。
合計(B3～P3)
d〜fに関する解答群
ア：B$3
イ：B$36
ウ：B$38
エ：B3
オ：B36
カ：B38
キ：合計(B$3～B3)
ク：合計(B$3～$B3)
g, hに関する解答群
ア：勤務可能調査!B3=0
イ：勤務可能調査!B3=1
ウ：合計($B3～$P3)≦5
エ：合計($B3～$P3)≧5
オ：合計(勤務可能調査!$B3～$P3)≦5
カ：合計(勤務可能調査!$B3～$P3)≧5
キ：順位(AB3,$AB3～$AP3,0)≦5
ク：順位(AB3,$AB3～$AP3,1)≦5
ケ：順位(B3,$B3～$P3,0)≦5
コ：順位(B3,$B3～$P3,1)≦5
模範解答
d：ウ、e：イ、f：キ、g：イ、h：キ（g,hは順不同）
解説
解答の導き方
評価値を使って、各日の候補者から優先順位の高い人を選びます。空欄を埋めるには、評価値にどの情報を入れるのか、そして「割当てる」と判定する条件は何かを順に確認します。
-
評価値の各部分に入る情報を確認します。問題文では、優先順位を「先々月ペナルティ順位がより小さい」「前日までの割当回数がより少ない」「経験順位がより小さい」の順にするとしています。また、出社可能な人を先に選ぶため、出社可能かどうかも評価値に含めます。評価値は、これらの情報を優先順に並べた数値です。式の中で100を掛けるのは、次の情報を後ろの桁に加えるためです。評価値が小さいほど、優先順位が高くなります。
-
先々月ペナルティ順位を入れる位置を決めます。出社可能かどうかを示す値の次には、先々月ペナルティ順位を入れます。図3の「先々月ペナルティ順位」は38行目にあるので、参照するセルはB38です。ただし、評価値の式は下方向にも複写されます。行番号が複写先に合わせて変わらないように、38行目を固定します。一方、列はアルバイトごとに変える必要があるため固定しません。この参照はB$38となり、選択肢では d：ウ です。
-
経験順位を入れる位置を決めます。経験順位は36行目にあります。評価値を下方向に複写しても36行目を参照し続ける必要があります。一方、横方向に複写したときは、アルバイトごとに参照列が変わらなければなりません。そのため、行だけを固定するB$36を使います。選択肢では e：イ です。表計算では、参照に付ける「$」で、複写しても変えない列や行を指定できます。このように列や行の一部だけを固定する参照も使えます。
-
初日の評価値には、前日までの割当回数として0を入れます。3行目は月初の最初の日なので、前日までに割り当てた回数はありません。したがって、式の中では、先々月ペナルティ順位の後に100を2回掛け、割当回数の部分を0にしたうえで経験順位を加えています。これにより、初日もほかの日と同じ項目順の評価値になります。
-
2日目以降は、前日までの割当回数を求めます。4行目の評価値を作る時点では、3行目の割当ては決まっています。そこで、4行目の評価値には、3行目の割当回数を合計して入れます。使う式は合計(B$3～B3)です。下端のB3は、式を下方向に複写すると、現在の日の1行前までを指すように変化します。たとえば、5行目に複写すると合計(B$3～B4)となり、3行目から4行目までの割当回数を合計します。列は横方向への複写でアルバイトごとに変わり、開始行の3は固定されます。よって、f：キ です。この合計に当日分を含めると、まだ決まっていない当日の割当てを使うことになってしまうため、前日までの行だけを合計します。
-
割当てる人を決める条件を考えます。出勤者を決める式は、論理積の条件を満たす場合に1、満たさない場合に0を返します。論理積とは、指定した条件がすべて正しいときだけ正しいと判定する仕組みです。必要な条件は次の2つです。
- その人が勤務可能であること。勤務可能を1で表すので、条件は勤務可能調査!B3=1です。これは g：イ です。
- その人の評価値が、その日の候補者の中で上位5人以内であること。評価値は小さいほど優先順位が高く、順位関数の順序に0を指定すると昇順で順位を付けます。そのため、条件は順位(AB3,$AB3～$AP3,0)≦5です。これは h：キ です。
2つ目の条件だけでは、勤務不可能な人が選ばれるおそれがあります。勤務可能な人が5人以下の日は、勤務可能な人全員を割り当てるため、勤務可能かどうかの条件も必要です。2つの条件をともに満たした人だけが割当てられます。なお、gとhの順序は入れ替わっても意味は変わりません。
誤りやすいポイント
- 出社可能な人の判定を逆にしないこと。 勤務可能は1なので、条件は勤務可能調査!B3=1です。
- 順位関数の順序を取り違えないこと。 問題文では評価値が小さい人から割り当てます。したがって、順位関数は昇順を表す0を指定します。
- 評価値の順位だけで割当てを決めないこと。 勤務可能な人が5人以下の日もあるため、勤務可能であることを別の条件として確認します。
- 前日までの割当回数に当日分を含めないこと。 当日の割当てを決める前に評価値を計算するため、合計の範囲は前日までです。
- 複写で変える参照と固定する参照を区別すること。 アルバイトごとに列は変えますが、順位や経験順位などの行は変わらないようにします。
FAQ
Q: なぜ先々月ペナルティ順位や経験順位は、順位の数値をそのまま評価値に加えるのですか。
A: 問題文では、順位が小さい人ほど優先されます。評価値も小さいほど優先されるので、順位の数値をそのまま加えることで、優先順位を反映できます。
A: 問題文では、順位が小さい人ほど優先されます。評価値も小さいほど優先されるので、順位の数値をそのまま加えることで、優先順位を反映できます。
Q: 前日までの割当回数は、なぜその日の割当人数ではなく個人ごとに合計するのですか。
A: 優先順位の規則は、各人について「前日までに割り当てられた回数がより少ないか」を比べるものだからです。各人の列について、前日までの割当てを合計します。
A: 優先順位の規則は、各人について「前日までに割り当てられた回数がより少ないか」を比べるものだからです。各人の列について、前日までの割当てを合計します。
Q: 順位が5位以内なら、必ず割当てられますか。
A: いいえ。順位に加えて、その人が勤務可能であることも条件です。両方を満たした場合に割り当てられます。
A: いいえ。順位に加えて、その人が勤務可能であることも条件です。両方を満たした場合に割り当てられます。
関連キーワード: 表計算、相対参照、絶対参照、順位関数、論理積
