基本情報技術者 2009年 春期 午後 問13
次の表計算及びワークシートの説明を読んで、設問に答えよ。
〔表計算の説明〕
E社は、3種類の商品M1〜M3を自社で生産し、3か所の販売拠点P1〜P3で販売している。販売する商品は、前月までに生産したものである。
(1) E社では、各販売拠点で2月に立てた4〜9月の半年分の商品ごとの月別販売計画を基に、次の①及び②に従って3〜8月の半年分の月別生産計画を作成している。
① 過去の経験から、販売計画量以上に売れる月があるので、各月の在庫量は、翌月の販売計画量に20％の余裕を加えた数量とする。
② 商品ごとに1か月間に生産できる最大の数量（以下、最大月産量という）が決まっているので、前月にすべてを生産できない場合がある。その場合、不足分は前々月までに生産するように計画する。
(2) 各販売拠点で2月に立てた4〜9月の半年分の商品ごとの月別販売計画は、表計算ソフトのワークシート“販売計画”に1行ずつ入力する。そのワークシートの例を図1に示す。
(3) 図1のワークシート“販売計画”を基に、商品M2について3〜8月の半年分の月別生産計画を作成したワークシート“生産計画”の例を図2に示す。
(4) 一つの商品についての月別生産計画の作成は、次の手順で行う。
① 4〜9月の期間について、E社全体での月ごとの販売計画量を求める。
② 3〜8月の期間について、月ごとに新たに必要となる商品の数量（以下、必要生産量という）を、最大月産量を考慮せずに求める。
③ 3〜8月の各月における必要生産量を確保するために、最大月産量を考慮して、月ごとに生産する商品の数量（以下、計画生産量という）を求める。
(5) 各月の必要生産量の求め方は、次のとおりである。
必要生産量は、当月の必要在庫量から繰越在庫量を差し引いた数量である。ただし、繰越在庫量が必要在庫量以上ある場合は、商品の生産は必要ないので0となる。
① 必要在庫量は最低限もつ必要がある在庫の量で、翌月の販売計画量の120％とする。
② 繰越在庫量とは、前月の繰越在庫量と必要生産量の合計から、当月の販売計画量を差し引いた数量である。
なお、3月の繰越在庫量は0である。
(6) 各月の計画生産量の求め方は、次のとおりである。
計画生産量は、当月の必要生産量と翌月の繰上生産量の合計である。ただし、この数量が最大月産量を超える場合は、計画生産量は最大月産量とする。
繰上生産量とは、当月の必要生産量と翌月の繰上生産量の合計から、当月の計画生産量を差し引いた数量である。
(7) ワークシート“生産計画”で用いる関数を表に示す。
〔ワークシート：生産計画〕
ワークシート“生産計画”の作成方法は、次のとおりである。
(1) セルA2に、商品名としてM1〜M3のいずれかを入力する。
(2) セルB2に、セルA2の商品の最大月産量を入力する。
(3) セルE2〜J2に、4〜9月の販売計画量を計算するための式を入力する。これを実行するために、セルE2に次の式を入力し、セルF2〜J2に複写する。複数のワークシート間でデータを参照する場合には“ワークシート名!セル”又は“ワークシート名!セル範囲”という形式で指定する。
照合合計($A2,販売計画!a,販売計画!b)
(4) セルD3〜I3に、3〜8月の必要在庫量（小数点以下切上げ）を計算するための式を入力する。これを実行するために、セルD3に次の式を入力し、セルE3〜I3に複写する。
c
(5) セルD4に、3月の繰越在庫量として0を入力する。セルE4〜I4に、4〜8月の繰越在庫量を計算するための式を入力する。これを実行するために、セルE4に次の式を入力し、セルF4〜I4に複写する。
d
(6) セルD5〜I5に、3〜8月の必要生産量を計算するための式を入力する。これを実行するために、セルD5に次の式を入力し、セルE5〜I5に複写する。
IF(e,0,D3 - D4)
(7) セルJ6に、9月の繰上生産量として0を入力する。セルD6〜I6に、3〜8月の繰上生産量を計算するための式を入力する。これを実行するために、セルI6に次の式を入力し、セルD6〜H6に複写する。
f
(8) セルD7〜I7に、3〜8月の計画生産量を計算するための式を入力する。これを実行するために、セルI7に次の式を入力し、セルD7〜H7に複写する。
IF(g,I5 + J6,$B2)設問1：
問題文を見る ワークシート“生産計画”の説明中のに入れる正しい答えを、解答群の中から選べ。
a, bに関する解答群
ア：B2〜B10
イ：C2〜C10
ウ：H2〜H10
エ：$B2〜$B10
オ：$C2〜$C10
カ：$H2〜$H10
cに関する解答群
ア：切上げ(E2) * 0.2
イ：切上げ(E2) * 1.2
ウ：切上げ(E2 * 0.2)
エ：切上げ(E2 * 1.2)
dに関する解答群
ア：D3 + D4 - D5
イ：D3 + D4 - E2
ウ：D3 + D5 - D4
エ：D3 + D5 - E2
オ：D4 + D5 - D3
カ：D4 + D5 - E2
eに関する解答群
ア：D3 ≦ D4
イ：D3 ≧ D4
ウ：D3 < 0
エ：D3 ≧ 0
オ：D4 < 0
カ：D4 ≧ 0
fに関する解答群
ア：I5 + J6
イ：I5 + J6 - I7
ウ：I7 + J6
エ：I7 + J6 - I5
gに関する解答群
ア：I5 + J6 ≦ $B2
イ：I5 + J6 > $B2
ウ：I5 ≦ $B2
エ：I5 > $B2
オ：J6 ≦ $B2
カ：J6 > $B2
模範解答
a：エ、b：イ、c：エ、d：カ、e：ア、f：イ、g：ア
解説
解答の導き方
この問題では、販売拠点ごとの販売計画量を商品別に合計し、その値から必要在庫量と必要生産量を求めます。その後、最大月産量を考慮して計画生産量を決めます。
a、b：商品ごとの販売計画量を合計する
本文の関数の説明によると、照合合計は「照合値と等しい値をもつセル」を探し、同じ位置にある対応範囲の数値を合計します。
図1では、商品名はB列、4月計画量はC列にあります。セルA2の商品名を販売計画のB列から探し、見つかった行の4月計画量をC列から合計します。
たとえば、商品M2の4月計画量は、3か所の販売拠点の値を合計して、
となります。
式を右に複写しても、商品名を探す列はB列のままです。そのため、aは列Bを固定した「$B2〜$B10」です。一方、合計する月別計画量の列は、右に複写するたびにC列からD列、E列へと変わる必要があります。そのため、bは列を固定しない「C2〜C10」です。
このことから、aにはエ、bにはイが入ります。式は次の形です。
照合合計($A2,販売計画!$B2〜$B10,販売計画!C2〜C10)
絶対参照は、式を複写しても指定した列や行が変わらない参照方法です。相対参照は、複写先に応じて参照先が変わる方法です。この式では、商品名を探す列は固定し、販売計画量の列は複写先に応じて変えます。
c：翌月の販売に備える必要在庫量を求める
本文では、必要在庫量は「翌月の販売計画量の120％」とされています。3月の必要在庫量を求めるD3では、翌月である4月の販売計画量E2を使います。
販売計画量の120％は、販売計画量に1.2を掛けた値です。小数点以下を切り上げるため、式は「切上げ(E2 * 1.2)」です。これがcのエに当たります。
たとえば、E2が1,500の場合は、
です。式を右に複写すると、参照先も翌月の販売計画量へ移ります。
d、e：繰越在庫量と必要生産量を求める
本文では、繰越在庫量を「前月の繰越在庫量と必要生産量の合計から、当月の販売計画量を差し引いた数量」としています。
4月の繰越在庫量を求めるE4では、3月の繰越在庫量D4と3月の必要生産量D5を足し、4月の販売計画量E2を引きます。したがって、式は「D4 + D5 - E2」で、dはカです。
3月の繰越在庫量D4は0、3月の必要生産量D5は1,800、4月の販売計画量E2は1,500です。よって、4月の繰越在庫量は次のとおりです。
必要生産量は、必要在庫量から繰越在庫量を引いて求めます。ただし、繰越在庫量が必要在庫量以上ある場合は、生産する必要がないので0にします。
そのため、必要在庫量D3が繰越在庫量D4以下の場合を条件にして、「IF(D3 ≦ D4,0,D3 - D4)」とします。eはアです。
商品M2の必要在庫量、繰越在庫量、必要生産量を順に計算すると、次のようになります。
必要在庫量は、翌月の販売計画量を使って求めます。たとえば、3月の必要在庫量は4月の販売計画量1,500の120％なので1,800です。4月の必要在庫量は、5月の販売計画量3,500の120％なので4,200です。
f、g：最大月産量を考慮して計画生産量を求める
計画生産量は、当月の必要生産量と、翌月から繰り上げる生産量の合計を基に決めます。ただし、合計が最大月産量を超える場合は、最大月産量までしか生産できません。
8月の計画生産量を求めるI7では、8月の必要生産量I5に、9月の繰上生産量J6を加えます。J6には、9月の繰上生産量として0が入力されています。
合計が最大月産量以下なら、その合計を計画生産量とします。合計が最大月産量を超える場合は、最大月産量を計画生産量とします。したがって、条件は「I5 + J6 ≦ $B2」で、gはアです。
繰上生産量は、必要生産量と翌月からの繰上生産量の合計から、当月の計画生産量を引いて求めます。8月の場合は「I5 + J6 - I7」となるため、fはイです。
計画生産量と繰上生産量は、8月から前の月に向かって計算します。
- 8月は、必要生産量が24で、最大月産量3,000以下です。そのため計画生産量は24、繰上生産量は24 + 0 - 24 = 0です。
- 4月は、必要生産量が3,900で、最大月産量3,000を超えます。そのため計画生産量は3,000です。生産できなかった900は、繰上生産量として前の月に回します。
- 3月は、自分の必要生産量1,800に、4月から繰り上げられた900を加えます。合計は2,700で最大月産量3,000以下なので、計画生産量は2,700です。
誤りやすいポイント
- 商品名を探す範囲と、販売計画量を合計する範囲を取り違えないようにします。商品名はB列から探し、販売計画量は月ごとの列から合計します。
- 必要在庫量の計算では、当月ではなく翌月の販売計画量を使います。3月の必要在庫量なら、4月の販売計画量が対象です。
- 必要生産量は、必要在庫量から繰越在庫量を引きます。繰越在庫量のほうが多いときは、必要生産量を0にします。
- 最大月産量を超えて作れない分は、繰上生産量として前の月に回します。繰上生産量と、販売後に残る繰越在庫量は別の値です。
- 式を複写する方向を意識します。月ごとの販売計画量は横に複写すると参照列が変わりますが、商品名や最大月産量を参照する列は固定します。
FAQ
Q: 3月の必要在庫量に、なぜ4月の販売計画量を使うのですか？
A: 3月に確保する在庫は、次の月である4月の販売に備えるためです。そのため、4月の販売計画量の120％が3月の必要在庫量になります。
A: 3月に確保する在庫は、次の月である4月の販売に備えるためです。そのため、4月の販売計画量の120％が3月の必要在庫量になります。
Q: 繰上生産量と繰越在庫量は、どう違いますか？
A: 繰上生産量は、最大月産量の制約によって当月に作りきれなかった分を、前の月に生産する量です。繰越在庫量は、前月からの在庫と生産量から当月の販売量を差し引いて、次の月へ持ち越す在庫です。
A: 繰上生産量は、最大月産量の制約によって当月に作りきれなかった分を、前の月に生産する量です。繰越在庫量は、前月からの在庫と生産量から当月の販売量を差し引いて、次の月へ持ち越す在庫です。
Q: 式で列を固定するのはなぜですか？
A: 式を横に複写したとき、商品名を探す列や最大月産量の列まで移動すると、正しく計算できなくなるためです。一方、月別の販売計画量は、複写先に応じて参照する列を変える必要があります。
A: 式を横に複写したとき、商品名を探す列や最大月産量の列まで移動すると、正しく計算できなくなるためです。一方、月別の販売計画量は、複写先に応じて参照する列を変える必要があります。
関連キーワード: 表計算、相対参照、絶対参照、条件分岐、切上げ処理、在庫管理
