基本情報技術者 2011年 秋期 午後 問13
次の表計算、ワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
三つの事業本部（以下、本部という）からなるK社では、次のルールに従って、社員の基本給及び賞与の額を決めている。
(1) 基本給
① 基本給は、毎月支給され、次の式で計算する。
基本給＝年齢給＋役職給
② 年齢給は、4月1日時点の年齢が50歳未満の場合、その年齢に7,000円を乗じて算出され、50歳以上の場合、固定額350,000円となる。
③ 役職給の金額は、表1に示すとおりである。
(2) 賞与
① 賞与は、次の式で計算する。
賞与＝基本給×（役職係数＋個人評価係数）
② 役職係数は、表2に示すとおりである。
③ 個人評価係数は、表3に示すとおり個人評価点の範囲によって決まる。
〔ワークシート：役職給〕
基本給の計算の基となる役職ごとの役職給を登録した図1のワークシート“役職給”を作成した。
役職をセルA2～A6に、役職の序列を示す数値である役職ランクをセルB2～B6に、役職給をセルC2～C6に入力する。
〔ワークシート：賞与係数〕
賞与の計算の基となる役職ごとの役職係数及び個人評価点に対応した個人評価係数を登録した図2のワークシート“賞与係数”を作成した。
(1) 役職をセルA3～A7に、役職ランクをセルB3～B7に、役職係数をセルC3～C7に入力する。
(2) 個人評価点の範囲の下限値をセルA11～A15に、個人評価係数をセルB11～B15に入力する。
〔ワークシート：基本給・賞与計算〕
K社の三つの本部（第1本部、第2本部、第3本部）に所属する社員107名の基本給及び賞与を計算するためのワークシート“基本給・賞与計算”を作成した。そのワークシートを図3に示す。
設問1：
問題文を見る ワークシート“基本給・賞与計算”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
(1) 社員番号をセルA2～A108に、役職をセルB2～B108に、本部をセルD2～D108に、4月1日時点での年齢をセルE2～E108に入力する。役職ランクを参照する計算式をセルC2～C108に入力する。
(2) 基本給を求めるための計算式を入力する。
① 役職給を求める計算式aをセルF2に入力し、セルF3～F108に複写する。
② 年齢給を求める計算式bをセルG2に入力し、セルG3～G108に複写する。
③ 基本給を求める計算式をセルH2～H108に入力する。
(3) 賞与を求めるための計算式を入力する。
① 役職係数を求める計算式をセルI2～I108に入力する。
② 個人評価点をセルJ2～J108に入力する。
③ 個人評価点に対応した個人評価係数を求めるための計算式cをセルK2に入力し、セルK3～K108に複写する。
④ 賞与を求める計算式をセルL2～L108に入力する。
aに関する解答群
ア：垂直照合(B1, 役職給!B$2～C$6, 2, 0)
イ：垂直照合(B1, 役職給!B$2～C$6, 3, 0)
ウ：垂直照合(C2, 役職給!A$2～C$6, 2, 0)
エ：垂直照合(C2, 役職給!B$2～C$6, 2, 0)
オ：水平照合(B1, 役職給!B$2～C$6, 2, 0)
カ：水平照合(B1, 役職給!B$2～C$6, 3, 0)
キ：水平照合(C2, 役職給!A$2～C$6, 2, 0)
ク：水平照合(C2, 役職給!B$2～C$6, 2, 0)
bに関する解答群
ア：IF(E2<49, C2 * 7, 350)
イ：IF(E2<49, E2 * 7, 350)
ウ：IF(E2<50, C2 * 7, 350)
エ：IF(E2<50, E2 * 7, 350)
オ：IF(E2≦51, C2 * 7, 350)
カ：IF(E2≦51, E2 * 7, 350)
キ：IF(E2≧50, C2 * 7, 350)
ク：IF(E2≧50, E2 * 7, 350)
cに関する解答群
ア：垂直照合(J2, 賞与係数!A$11～B$15, 2, 0)
イ：垂直照合(J2, 賞与係数!A$11～B$15, 2, 1)
ウ：垂直照合(J2, 賞与係数!B$3～C$7, 2, 0)
エ：垂直照合(J2, 賞与係数!B$3～C$7, 2, 1)
オ：水平照合(J2, 賞与係数!A$11～B$15, 2, 0)
カ：水平照合(J2, 賞与係数!A$11～B$15, 2, 1)
キ：水平照合(J2, 賞与係数!B$3～C$7, 2, 0)
ク：水平照合(J2, 賞与係数!B$3～C$7, 2, 1)
模範解答
a：エ、b：エ、c：イ
解説
解答の導き方
この設問では、役職給・年齢給・個人評価係数を、表に登録された値から求める式を選びます。表から値を探すときは、「何を手掛かりに探すか」と「見つけた行のどの列を返すか」を確認します。
a：役職給を求める式
本文では、役職ごとの役職給が「役職給」シートに登録されています。図1では、役職ランクがB列、役職給がC列にあります。
セルC2には役職ランクが入っています。その値を図1のB列から探し、同じ行のC列にある役職給を取り出せばよいです。
垂直照合（表の左端の列から値を探し、同じ行の指定列の値を返す処理）を使うと、範囲B$2～C$6の左端列であるB列からC2の値を探し、範囲内の2列目であるC列を返します。最後の引数を0にすると、役職ランクが完全に一致する行を探します。
したがって、選ぶ式は次のとおりです。
垂直照合(C2, 役職給!B$2～C$6, 2, 0)
範囲の先頭をB列にするのは、探す値である役職ランクがB列にあるためです。A列から始めると、役職名を探す表になり、C2の役職ランクを正しく探せません。
b：年齢給を求める式
本文では、4月1日時点の年齢が50歳未満なら「その年齢に7,000円を乗じて」計算し、50歳以上なら固定額350,000円としています。
ワークシートの金額は千円単位です。そのため、7,000円は7千円に当たり、年齢給は「年齢×7」で求められます。また、年齢はセルE2に入力されています。
よって、E2が50未満ならE2に7を掛け、そうでなければ350を返す式を選びます。
IF(E2<50, E2 * 7, 350)
この条件なら、49歳では千円、50歳では固定額の350千円になります。したがって、選ぶ式はエです。
c：個人評価係数を求める式
図2では、個人評価点の範囲の下限値がA11～A15に、対応する個人評価係数がB11～B15にあります。セルJ2の個人評価点を手掛かりに、該当する係数を求めます。
個人評価点は、表の下限値と同じ点数になるとは限りません。例えば、22点は表にある下限値そのものではありませんが、「21点～40点」の範囲に入るため、下限値21に対応する係数0.85を使います。
そのため、下限値の表を近似照合します。垂直照合の最後の引数を1にすると、検索値以下で最も大きい下限値を探します。J2が22なら、検索値以下で最も大きい下限値は21です。範囲A$11～B$15の2列目から、対応する個人評価係数を返します。
したがって、選ぶ式は次のとおりです。
垂直照合(J2, 賞与係数!A$11～B$15, 2, 1)
最後の引数が0の完全一致では、22と一致する下限値が表にないため、個人評価係数を求められません。下限値の並びに応じた範囲判定が必要なので、1を使います。
誤りやすいポイント
-
役職ランクと役職名を取り違える
aでは、セルC2の役職ランクを検索します。検索する値と、選んだ表の左端の列を対応させます。 -
垂直照合の列番号を表全体の列番号だと思う
aの範囲はB列から始まります。そのため、範囲内の2列目はC列です。役職給を返すには列番号2を指定します。 -
年齢の境界を「50歳以下」と考える
本文の条件は「50歳未満」です。したがって、条件はE2<50です。50歳は固定額になる側です。 -
個人評価点の検索に完全一致を使う
個人評価点は、下限値と同じ点数でなくても該当する範囲があります。下限値を使う表では、検索値以下で最も大きい下限値を選ぶ近似照合が必要です。 -
表の範囲が下へずれることを見落とす
計算式を下の行へ複写しても参照範囲を保つため、表の範囲の行番号を固定しています。$は、参照を固定するための記号です。
FAQ
Q: なぜaでは完全一致、cでは近似照合を使うのですか？
A: aでは、役職ランクと表の役職ランクをそのまま一致させます。一方、cでは、個人評価点が下限値と一致しない場合もあるため、該当範囲の下限値を探します。
A: aでは、役職ランクと表の役職ランクをそのまま一致させます。一方、cでは、個人評価点が下限値と一致しない場合もあるため、該当範囲の下限値を探します。
Q: cで個人評価点が22の場合、どの係数になりますか？
A: 下限値のうち22以下で最も大きい値は21です。21に対応する個人評価係数0.85を使います。
A: 下限値のうち22以下で最も大きい値は21です。21に対応する個人評価係数0.85を使います。
Q: 表の範囲に付いている$は何のためですか？
A: 計算式を下の行へ複写しても、参照する表の行番号がずれないように固定するためです。
A: 計算式を下の行へ複写しても、参照する表の行番号がずれないように固定するためです。
関連キーワード: 垂直照合、完全一致検索、近似一致検索、絶対参照、IF関数、四捨五入
設問2：
問題文を見る 賞与の見直しに関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
K社では、役職及び個人評価に加えて、本部ごとの利益（以下、本部利益という）を賞与に反映するために、本部利益に応じた評価点（以下、本部加点という）を加味するよう賞与の計算式を見直すことにした。
〔見直した賞与計算方式に関する説明〕
(1) 見直した賞与計算式は次のとおりである。
① 役職が本部長、部長又は課長のとき
賞与＝基本給×（役職係数＋個人評価係数＋本部加点／10）
② 役職が主任又はなしのとき
賞与＝基本給×（役職係数＋個人評価係数＋本部加点／20）
(2) 本部に属する社員の賞与の合計（以下、本部賞与合計という）が、本部利益の15％の金額（以下、本部賞与合計上限という）以下となる条件を満足する1～20の整数があるときは、その最大値を本部加点とする。無いときは、本部加点を0とする。
条件を満足する本部加点を求め、それを加味した賞与計算を行うために、ワークシート“新賞与計算”とマクロBonusCalcを作成した。そのワークシート及びマクロBonusCalcの実行結果の表示例を図4に示す。
〔ワークシート：新賞与計算〕
(1) 本部名をセルI2～I4に、本部利益をセルJ2～J4に、本部賞与合計上限を求める計算式をセルK2～K4に入力する。
(2) ワークシート“基本給・賞与計算”のセルA1～K108をワークシート“新賞与計算”のセルA5～K112に複写する。
(3) 所属本部によって決まる社員ごとの本部加点を求める計算式をセルL6～L112に入力する。社員ごとの賞与を求める計算式dをセルM6に入力し、セルM7～M112に複写する。
(4) 本部ごとの賞与合計を求める計算式eをセルM2に入力し、セルM3～M4に複写する。
〔マクロの説明〕
(1) マクロBonusCalcは、各本部の条件を満足する本部加点を求めるマクロである。
(2) マクロBonusCalcの処理手順は次のとおりである。
① 第1本部の本部加点の初期値を0とする。
② 本部加点に1を加算する。
③ 手順②で設定された本部加点に基づき、第1本部に所属する社員の賞与の合計である第1本部の本部賞与合計をワークシートの計算式から算出する。
④ 第1本部の本部賞与合計と本部賞与合計上限の値を比較し、前者が後者を上回る、又は、本部加点が20を超えたときは、手順⑤に進む。それ以外のときは、手順②に戻る。
⑤ このときの本部加点から1を減じた値を第1本部の本部加点とし、対応するセルに代入する。
⑥ 同様に、第2本部及び第3本部の本部加点をそれぞれ対応するセルに代入する。
dに関する解答群
ア：H6 * (I6 + K6) + IF(C6 < 3, L6 / 10, L6 / 20)
イ：H6 * (I6 + K6) + IF(C6 < 3, L6 / 20, L6 / 10)
ウ：H6 * (I6 + K6) + IF(C6 ≦ 3, L6 / 10, L6 / 20)
エ：H6 * (I6 + K6) + IF(C6 ≦ 3, L6 / 20, L6 / 10)
オ：H6 * (I6 + K6 + IF(C6 < 3, L6 / 10, L6 / 20))
カ：H6 * (I6 + K6 + IF(C6 < 3, L6 / 20, L6 / 10))
キ：H6 * (I6 + K6 + IF(C6 ≦ 3, L6 / 10, L6 / 20))
ク：H6 * (I6 + K6 + IF(C6 ≦ 3, L6 / 20, L6 / 10))
eに関する解答群
ア：条件付合計(C$6～C$112, = I2, L$6～L$112)
イ：条件付合計(C$6～C$112, = I2, M$6～M$112)
ウ：条件付合計(D$6～D$112, = I2, L$6～L$112)
エ：条件付合計(D$6～D$112, = I2, M$6～M$112)
オ：照合検索(I2, C$6～C$112, L$6～L$112)
カ：照合検索(I2, C$6～C$112, M$6～M$112)
キ：照合検索(I2, D$6～D$112, L$6～L$112)
ク：照合検索(I2, D$6～D$112, M$6～M$112)
fに関する解答群
ア：row: 1, row < 3, 1
イ：row: 1, row < 20, 1
ウ：row: 1, row ≦ 3, 1
エ：row: 1, row ≦ 20, 1
オ：row: 2, row < 4, 1
カ：row: 2, row ≦ 4, 1
キ：row: 6, row < 112, 1
ク：row: 6, row ≦ 112, 1
gに関する解答群
ア：論理積(M2 ≦ K2, DeptPoint ≦ 20)
イ：論理積(M2 ≦ K2, L2 ≦ 20)
ウ：論理積(相対(L1, row, 1) > 相対(L2, row, −1), DeptPoint > 20)
エ：論理積(相対(L1, row, 1) ≦ 相対(L1, row, −1), DeptPoint ≦ 20)
オ：論理和(M2 > K2, DeptPoint > 20)
カ：論理和(M2 > K2, L2 > 20)
キ：論理和(相対(L1, row, 1) > 相対(L2, row, −1), DeptPoint > 20)
ク：論理和(相対(L1, row, 1) ≦ 相対(L1, row, −1), DeptPoint ≦ 20)
hに関する解答群
ア：相対(L1, row, 0) ← DeptPoint
イ：相対(L1, row, 0) ← DeptPoint + 1
ウ：相対(L1, row, 0) ← DeptPoint − 1
エ：相対(L1, row, −1) ← DeptPoint
オ：相対(L1, row, −1) ← DeptPoint + 1
カ：相対(L1, row, −1) ← DeptPoint − 1
キ：相対(L2, row, 0) ← DeptPoint
ク：相対(L2, row, 0) ← DeptPoint + 1
ケ：相対(L2, row, −1) ← DeptPoint + 1
コ：相対(L2, row, −1) ← DeptPoint − 1
模範解答
d：キ、e：エ、f：ウ、g：エ、h：ウ
解説
解答の導き方
この設問では、見直した賞与の計算式に合う表計算式を選び、さらに本部ごとの本部加点を決めるマクロの処理を読み取ります。まず、図4の列が何を表しているかを確認すると、式の選び方やマクロの動きが分かりやすくなります。
図4では、社員ごとのデータが6行目から112行目にあり、社員の役職ランクはC列、本部はD列、基本給はH列、役職係数はI列、個人評価係数はK列、本部加点はL列、賞与はM列にあります。上部の本部別集計表では、本部賞与合計上限がK列、本部賞与合計がM列、本部加点がL列です。
d：社員ごとの賞与の式
問題文では、本部長、部長又は課長の場合は本部加点を10で割り、主任又はなしの場合は20で割るとしています。また、賞与は基本給に、役職係数・個人評価係数・本部加点の調整分を足した値を掛けて計算します。
役職ランクは、本部長が1、部長が2、課長が3、主任が4、なしが5です。そのため、ランクが3以下なら本部加点を10で割り、ランクが4以上なら20で割る式にします。条件によって値を選ぶIF（条件に応じて値を選ぶ関数）を使うと、次の形です。
H6 * (I6 + K6 + IF(C6 ≦ 3, L6 / 10, L6 / 20))
基本給H6に掛けるのは、役職係数I6と個人評価係数K6に本部加点の調整分を加えた合計です。図4のH0006の例では、基本給が550、役職係数が3.5、個人評価係数が0.85、本部加点が20、役職ランクが1です。したがって、
となり、小数第１位を四捨五入すると3,493です。以上から、dはキです。
e：本部ごとの賞与合計の式
本部賞与合計は、「その本部に所属する社員の賞与を全員分足した値」です。したがって、本部を表すD列が本部名I2と一致する社員について、賞与のM列を合計します。
条件に合う行の値を合計する条件付合計を使い、式は次の形になります。
条件付合計(D$6～D$112, = I2, M$6～M$112)
ここで、D列が本部名と一致するかを判定し、条件に合った行のM列を合計します。M2にこの式を入力してM3、M4に複写すると、I2がI3、I4に変わり、それぞれの本部について集計できます。一方、$を付けた範囲は絶対参照（式を複写しても参照範囲が変わらない指定）なので、複写後も6行目から112行目のままです。
社員の所属本部はD列にあり、合計する賞与はM列にあります。そのため、eはエです。L列は本部加点なので、合計する列としては使いません。また、本部ごとの社員は複数いるため、該当する値を一つ取り出す照合検索ではなく、合計する条件付合計を使います。
f：三つの本部を順に処理する繰返し
マクロでは、本部ごとに本部加点を求めます。本部別集計表は2行目から4行目までの3行です。相対参照（基準セルからの行・列のずれで参照先を決める指定）では、基準セルL1に対して行方向へ1ずらすとL2、2ずらすとL3、3ずらすとL4になります。
したがって、rowを1から3まで1ずつ増やす繰返しにすれば、三つの本部を順番に処理できます。選ぶのは、row: 1, row ≦ 3, 1を表すウです。
g：本部加点を増やし続ける条件
各本部について、マクロは本部加点を1ずつ増やし、その加点を使った賞与合計を確認します。基準セルL1から行方向へrowずらした位置は、処理中の本部加点のセルです。そこから列方向に1ずらしたセルは本部賞与合計、列方向に−1ずらしたセルは本部賞与合計上限になります。
たとえばrowが1なら、加点はL2、賞与合計はM2、合計上限はK2です。処理を続けてよいのは、賞与合計が上限以下であり、かつ、本部加点が20以下の場合です。この二つがどちらも成り立つことを論理積（複数の条件がすべて成り立つこと）で表します。
よって、gは次の条件です。
論理積(相対(L1, row, 1) ≦ 相対(L1, row, −1), DeptPoint ≦ 20)
これは選択肢のエです。条件を満たさなくなったら、賞与合計が上限を超えたか、本部加点が20を超えたことになります。
h：最後に有効だった本部加点を設定する処理
マクロは、本部加点を増やした直後に条件を確認します。そのため、条件を満たさなくなった時点のDeptPointは、上限を超える、または20を超える値です。採用するのは、その一つ前の値なので、処理中の本部加点のセルにDeptPoint − 1を代入します。
たとえば、加点を増やして21になった場合、上限以内だったとしても最大値は20です。21から1を引いて20を設定します。また、加点を1にした時点で賞与合計が上限を超えた場合は、1から1を引いて0を設定します。したがって、hはウです。
誤りやすいポイント
- dでは、本部加点の調整分も、ほかの係数と同じように基本給に掛けます。調整分を基本給との掛け算の外に置く式は誤りです。
- 役職ランクが3の課長も、本部加点を10で割る対象です。「3以下」が条件になるため、C6 ≦ 3とします。
- eでは、本部名と照合する列はD列、合計する賞与の列はM列です。C列は役職ランク、L列は本部加点なので、取り違えないようにします。
- gは「処理を続ける条件」です。合計が上限を超えた場合などの停止条件と混同しないようにします。
- hでは、条件を外れた値そのものではなく、その一つ前の値を設定します。条件を外れた値は採用できないためです。
FAQ
Q: 本部賞与合計が上限を超えた場合、どうして本部加点から1を引くのですか？
A: 上限を超えたときの加点は条件を満たしていません。マクロは加点を1ずつ試しているので、その一つ前の加点が条件を満たす最大値です。
A: 上限を超えたときの加点は条件を満たしていません。マクロは加点を1ずつ試しているので、その一つ前の加点が条件を満たす最大値です。
Q: 本部加点が20になっても上限を超えていない場合、次にどうなりますか？
A: マクロは次に21を試します。21は「1～20の整数」という条件の範囲外なので、処理を止め、21から1を引いた20を設定します。
A: マクロは次に21を試します。21は「1～20の整数」という条件の範囲外なので、処理を止め、21から1を引いた20を設定します。
Q: $を付けたセル範囲は、なぜ式を複写しても変わらないのですか？
A: $は絶対参照を表す記号で、複写後も参照先を固定します。これにより、M2の式をM3やM4に複写しても、社員データ全体を集計する範囲を保てます。
A: $は絶対参照を表す記号で、複写後も参照先を固定します。これにより、M2の式をM3やM4に複写しても、社員データ全体を集計する範囲を保てます。
関連キーワード: 表計算、絶対参照、相対参照、条件付き合計、論理積、繰返し処理
