平成28年春期試験午後問題　問13

午前試験免除制度対応！基本情報技術者試験のeラーニング【独習ゼミ】

問13　ソフトウェア開発(表計算)

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

［表計算の説明〕
　卸売業のＰ社は，家庭用品部門で販売している商品の取扱いを見直すことにした。そこで，販売管理システムから対象商品の連続した過去3年間の販売データを抽出し，表計算ソフトを利用して販売状況の分析を行い，今後の取扱いを検討することにした。対象商品は，商品コード A001，A002，B101，B201，C010，C011，C012，D070 及び D080 の9商品である。販売データは，各年の1月1日から12月31日までの1年を単位として分析し，分析する対象の各年で売上高が 0 の商品はない。
　
〔ワークシート：販売分析〕
　対象商品の販売状況を分析する指標を計算して，今後の取扱いを判定するワークシート"販売分析"を作成した。ワークシート"販売分析"の例を図1に示す。
  • ワークシートのセル A1～L21 は "売上高分析表"である。"売上高分析表"では，対象商品の年ごとの売上高を基にして，販売状況を分析する指標を求めている。
    1. セル C2～K2 には，対象商品の商品コードが昇順に格納されている。
    2. マクロ Sales_Total を実行すると，セル A3，セル A8 及びセル A13 には，分析する過去3年の西暦年が昇順に格納され，セル C3～K3，セル C8～K8 及び セル C13～K13 には，対象商品の当該年の売上高が，千円未満を四捨五入した千円単位の値で格納される。
    3. 対前年比率は，各商品の前年の売上高を基準にした当該年の売上高の割合である。
    4. 構成比率は，当該年の対象9商品の売上高の合計に占める各商品の売上高の割合である。
    5. 構成比率累計は，当該年の各商品の構成比率を，大きいものから順に当該商品まで累計した値である。ここで，当該商品と構成比率の同じ商品が複数あったときは，当該商品の分だけを加算する。
      pm13_1.png
    6. 管理区分は，各商品の構成比率累計が，表1の構成比率累計の範囲のどの区分に属するかを判定した結果であり，商品の管理方法を決定する指標である。
      pm13_2.png
    7. セル C18～K20 には，表2に示した各条件の真偽を商品ごとに判定した結果を設定する。セル C21～K21 には，各条件の真偽の組合せに基づいて，各商品の今後の取扱方針を設定する。
      pm13_3.png
    1. "Y"は条件が真，"N"は条件が偽であることを表す。
    2. 取扱方針は，条件の真，偽が各列の組合せに該当する商品の，今後の取扱方針を表す。
  • ワークシートのセル A23～E27 は"管理区分表"である。"管理区分表"には，管理区分と対応する構成比率累計の範囲が，表1のとおりに格納されている。
  • ワークシートのセル A29～J34 は "取扱判定表"である。
    1. セル C31～J34 には取扱方針を判定するための条件の組合せと，その組合せに基づいた取扱方針が，表2のとおりに格納されている。
    2. セル C30～J30 には，セル C31～J33 の各列の条件の"Y"を1，"N"を0とし，上から順に2進数の3桁目，2桁目，1桁目として並べたものを10進数に変換した値が，判定コードとして格納されている。
広告

設問1

ワークシート"販売分析"の作成に関する記述中の　に入れる正しい答えを，解答群の中から選べ。
  • セル L3，セル L8 及びセル L13 には，当該年の対象9商品の売上高の合計を求める式を入力する。
  • セル C4～L4 には，"－"を入力する。セル C9～L9 及びセル C14～L14 には，対前年比率を求める式を入力する。
  • セル C5～K5，セル C10～K10 及びセル C15～K15 には，構成比率を求める式を入力する。
  • セル C6 には，構成比率累計を求める次の式を入力し，セル D6～K6，セル C11～K11 及び セル C16～K16 に複写する。
    　　a
  • セル C7 には，管理区分を表示する次の式を入力し，セル D7～K7，セル C12～K12 及びセル C17～K17 に複写する。
    　　b
  • セル C18 には，直近2年の売上高が連続して前年よりも減少しているかどうかを 判定する次の式を入力し，セル D18～K18 に複写する。
    　　c
  • セル C19～K19 には，当該商品の直近の年の対前年比率が85％未満かどうかを判定する式を入力する。
  • セル C20～K20 には，当該商品の直近2年の管理区分が連続して"C"かどうかを判定する式を入力する。
  • セル C21 には，"取扱判定表"の条件がセル C18～C20 の値と一致する列に対応する取扱方針を表示する次の式を入力し，セル D21～K21 に複写する。
    　　d
a に関する解答群
  • 条件付合計($C5:$K5,＞C5,$C5:$K5)
  • 条件付合計($C5:$K5,＞C5,$C5:$K5)＋C5
  • 条件付合計($C5:$K5,≧C5,$C5:$K5)
  • 条件付合計($C5:$K5,≧C5,$C5:$K5)－C5
b に関する解答群
  • 表引き($A$25:$A$27,条件付個数($B$25:$B$27,＜C6),1)
  • 表引き($A$25:$A$27,条件付個数($D$25:$D$27,≦C6),1)
  • 表引き($A$25:$A$27,照合一致(C6,$B$25:$B$27,1),1)
  • 表引き($A$25:$A$27,照合一致(C6,$D$25:$D$27,－1),1)
c に関する解答群
  • IF(論理積(C9＜1,C14＜1),'Y','N')
  • IF(論理積(C9＞1,C14＞1),'N','Y')
  • IF(論理和(C9＜1,C14＜1),'Y','N')
  • IF(論理和(C9＞1,C14＞1),'N','Y')
d に関する解答群
  • 水平照合(IF(C18＝'Y',3,0)＋IF(C19＝'Y',2,0)＋
    　　IF(C20＝'Y',1,0),$C30:$J34,4,0)
  • 水平照合(IF(C18＝'Y',3,0)＋IF(C19＝'Y',2,0)＋
    　　IF(C20＝'Y',1,0),$C30:$J34,5,0)
  • 水平照合(IF(C18＝'Y',4,0)＋IF(C19＝'Y',2,0)＋
    　　IF(C20＝'Y',1,0),$C30:$J34,4,0)
  • 水平照合(IF(C18＝'Y',4,0)＋IF(C19＝'Y',2,0)＋
    　　IF(C20＝'Y',1,0),$C30:$J34,5,0)
解答選択欄
  • a:
  • b:
  • c:
  • d:
  • a=イ
  • b=ア
  • c=ア
  • d=エ

解説

〔aについて〕
ワークシート"販売分析"のセルC6(構成比率累計)に入力する正しい式を選択します。
「構成比率累計」セルに対する仕様は〔ワークシート：販売分析〕⑤にて、以下の記述があります。

「構成比率累計は，当該年の各商品の構成比率を，大きいものから順に当該商品まで累計した値である。ここで，当該商品と構成比率の同じ商品が複数あったときは，当該商品の分だけを加算する」

この構成比率累計は、各商品の構成比率の中から、当該商品の構成比率（セルC5）以上の値を合計することで求められます。ただし説明文の仕様を満たすため、単純に合計値を求めるのではなく、当該商品と同じ構成比率を持つ商品が複数あるときは、その商品の値を合計として含まない制限を付与する必要があります。

各式の検索のセル範囲と合計のセル範囲は「$C5:$K5」で同じです。このうち正しい式は、セル範囲($C5:$K5)の中から当該商品の構成比率(C5)より大きい数値(セルC5を含まない＝重複値は除外するため)の合計を求めた後に、個別にセルC5の値を加算する「イ」となります。
  • 当該商品の構成比率を除外した合計値を表示するため誤りです。
  • 正しい。
  • 検索条件に当該商品の値を含めているので、同じ構成比率の他の商品の値もまとめて合計されてしまいます（上記⑤の要求を満たせない）。
  • 「ウ」と同様に⑤の要求を満たせないのと同時に、構成比率値の重複の有無にかかわらずC5の値を差し引いているため誤りです。
〔bについて〕
管理区分は以下の基準で判定されます。
pm13_2.png
"表引き"を用いてセルA25(管理区分の上端)を起点に何行目の1列目を表示するかを正しく算出する式を選択します。第1引数のセル範囲と第3引数の列位置はどの選択肢も同じになっているため、第2引数の行位置が適切であるか否かを判定することとなります。

仮にC6の値である23.3%の場合には、管理区分Aとなるので第2引数の式が1(行目)を返すようになっていなければなりません。各関数が返す値を比較すると、
  • B25～B27の値(0%, 70%, 90%)のうち23.3%未満のセルの個数を返すので、1を返します（○）。
  • D25～D27の値(70%, 90%, 100%)のうち23.3%以下のセルの個数を返すので、0を返します（×）。
  • "照合検索"の第3引数に"1"を指定すると、式の値以下の最大値を検索します。B25～B27のうち23.3%以下の最大値は0％なので、1を返します（○）。
  • "照合検索"の第3引数に"-1"を指定すると、式の値以上の最小値を検索します。"-1"を指定するときには、検索するセル範囲の値が降順に整列されていなければならないという条件があるため、正しく動作しません（×）
ここまでで「イ」「エ」については誤りとわかります。残る2つの正誤については少し難しいのですが、境界値を正しく分類できるか否かがポイントとなっています。仮に構成比率が70%（管理区分A）であったとすると、「ア」は1を返すのに対して、「ウ」は2を返します。したがって「ア」の式が適切です。
pm13_6.png
〔cについて〕
セルC18～J18(売上高2年連続減)には、直近2年の売上高が連続して前年よりも減少しているかどうかを判別する式を入力します。

ここでは、C9(2014年の対前年比率)とC14(2015年の対前年比率)の値を比較します。両者とも1を下回れば2年連続減と判定できるので、論理積を使用して、両方が1より小さくなるときに'Y'を、そうでないときに'N'を返す「ア」が正解です。
  • 正しい。
  • 2年連続で前年を上回るときに'Y'を返すので誤りです。
  • どちらか一方が前年を下回ると'Y'を返すので誤りです。
  • どちらか一方が前年を上回ると'N'を、それ以外(両社とも前年以下)で'Y'を返します。一見正しく動作しそうですが、対前年比率が100％（減少していない）のときにも'Y'を返すので誤りです。
〔dについて〕
ここではセルC21(取扱方針)に、水平照合の式を使用した正しい式を選択します。

まずは、第1引数について考えます。IF関数にて、C18(売上高2年連続減)とC19(対前年比85%未満)及びC20(管理区分2年連続C)によって選別した値の合計を算出、この値を検索値とします。続いて、第1引数で求めた検索値を第3引数の範囲(取扱判定表!$C30:$J34)から検索します。"水平照合"ではセル範囲の上端行を左から右に操作するため、検索値と一致する「判定コード」を探索することとなります。

判定コードについては〔ワークシート：販売分析〕(3)の②に「セル C31～J33 の各列の条件の"Y"を1，"N"を0とし，上から順に2進数の3桁目，2桁日，1桁目として並べたものを10進数に変換した値」であるとの記載があります。つまり、判定コードは、NNN＝000＝0、NYN＝010＝2、YNY＝101＝5 というようにY/Nの値を各ビットに対応させ、10進数表現に変換したものとなります。2進数のビットは、1、2、4、8、…というように桁が上がるにつれ2倍になっていくので、
  • C18が'Y'であれば、22＝4
  • C19が'Y'であれば、21＝2
  • C20が'Y'であれば、20＝1
をそれぞれ加算すればY/Nの組合せを10進数の判定コードに変換できます。したがって、「IF(C18='Y', 3, 0),…」としている「ア」「イ」は誤りです。

「ウ」と「エ」の違いは、"水平照合"の第3引数です。"水平照合"の第3引数は行の位置を指定する引数であり、検索値を満たすセルから数えて1、2、…と下側に数えて、その位置のセルの値を返します。取扱い方針は、検索セル範囲(C30～J34)の上端(判定コード行)から数えて5行目なので、第3引数に指定する値は5(行目)になります。したがって「エ」の式が適切です。
pm13_7.png
広告

設問2

図2に示すワークシート"販売データ"のデータを基に，〔ワークシート：販売分析〕の説明の(1)の②で示した処理を実行するマクロ　Sales_Total を作成して，ワークシート"販売分析"に格納した。マクロ　Sales_Total の　に入れる正しい答えを，解答群の中から選べ。

〔ワークシート：販売データ〕
　ワークシート"販売データ"の例を図2に示す。
pm13_4.png
  • ワークシート"販売データ"には，販売管理システムから，対象商品の連続した過去3年間のデータを抽出して格納してある。
  • 行1には，見出しが格納されている。行2以降には，販売した年，月，日，商品コード，得意先コード，単価及び数量のデータが，年を第1キー，商品コードを第2キーとして昇順に格納されている。
  • データが格納されていないセルの値は，null である。
pm13_5.png
e に関する解答群
  • 相対(C2，0，total_col)＝相対(販売データ!D2，data_row，0)
  • 相対(C2，0，total_col)≠相対(販売データ!B2，data_row，0)
  • 相対(C2，0，total_col)≠相対(販売データ!D2，data_row，0)
  • 相対(C2，total_row，0)＝相対(販売データ!B2，data_row，0)
  • 相対(C2，total_row，0)＝相対(販売データ!D2，data_row，0)
  • 相対(C2，total_row，0)≠相対(販売データ!B2，data_row，0)
f，g に関する解答群
  • subtotal ← subtotal＋
    　相対(販売データ!A1，data_row，5)＊相対(販売データ!A1，data_row，6)
  • subtotal ← subtotal＋
    　相対(販売データ!A2，total_row，5)＊相対(販売データ!A2，total_row，6)
  • subtotal ← subtotal＋
    　相対(販売データ!F2，data_row，0)＊相対(販売データ!G2，data_row，0)
  • total_row ← 0
  • total_row ← total_row ＋ 1
  • total_row ← total_row ＋ 5
解答選択欄
  • e:
  • f:
  • g:
  • e=ア
  • f=ウ
  • g=カ

解説

〔eについて〕
売上高の集計は、年ごと、商品コードごとに行います。eの一つ外側のループでは、年が同一というのをループの継続条件にしているため、e、fには、商品ごとの合計を求めるための式が入ります。

以下の2つセルの比較を行い両者が等しい場合、fを実行する式を選択します。
  1. ワークシート"販売分析":C2(商品コード)を起点に total_col 列目のセル
  2. ワークシート"販売データ":D2(商品コード)を起点に data_row 行目のセル
データはそれぞれ①はX方向(横並び)に、②はY方向(縦並び)に列挙しているので、相対の引数はそれぞれ異なる点、この反復処理内においては total_col は固定、data_row は反復の度に1加算する点に注意してください。

①と②が等しいか否かを指定する「ア」が正解です。

「イ」「エ」「カ」は、①とワークシート"販売データ":B列(月)の比較を行うため誤りです。
「ウ」は、継続条件が商品コードが等しくない間となってしまうため誤りです。
「オ」は、相対の第2引数に total_row、第3引数に0指定すると、ワークシート"販売分析":C2(商品コード)を起点に total_row 行目、0列目のセルを比較するため誤りです。

〔fについて〕
こには、変数 subtotal を更新する処理を選択します。
当変数は反復処理直後に、ワークシート"販売分析"のその年の該当商品の売上高の行を表示する下の処理で使用されています。
相対(C3, total_row, total_col) ← 四捨五入(subtotal / 1000, 0)
ここまで subtotal は一切使われていませんので、fには subtotal に商品ごとの売上高を求める式が入ることになり、必然的に「ア」「イ」「ウ」の三択となります。商品ごとの売上高は、同じ商品コードをもつ行ごとの売上高を総計することで求められます。売上高は、F列(単価)×G列(数量)で求められるので、適切なセル指定となる「ウ」が適切です。

「ア」は、"相対"の第1引数が誤りです。"相対"は起点からの位置を起点から0、1、2、…と数えるため、A1ではなくA2を起点にすべきです。
「イ」は、"相対"の第2引数が誤りです。販売データの現在行を保持する変数は data_row ですので、第2引数には data_row を指定すべきです。

〔gについて〕
1年分の集計が終わる度に実行される処理が入ります。残る「エ」「オ」「カ」の三択です。
ワークシート"販売分析"で売上高を表示する行は、2013年：3行目 → 2014年：8行目 → 2015年：13行目と5行間隔になっているため、次の年の集計に入る前に total_row に5を加算する必要があります。

「エ」は、0固定にすると毎回A3を参照するので誤りです。
「オ」は、セルA3「2013」の次にセルA4「2013年の対前年比率」→セルA5→A6..を更新するため誤りです。
広告
広告

前の問題

▲
Pagetop