基本情報技術者 2016年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
卸売業のP社は、家庭用品部門で販売している商品の取扱いを見直すことにした。そこで、販売管理システムから対象商品の連続した過去3年間の販売データを抽出し、表計算ソフトを利用して販売状況の分析を行い、今後の取扱いを検討することにした。対象商品は、商品コードA001、A002、B101、B201、C010、C011、C012、D070及びD080の9商品である。販売データは、各年の1月1日から12月31日までの1年を単位として分析し、分析する対象の各年で売上高が0の商品はない。
〔ワークシート：販売分析〕
対象商品の販売状況を分析する指標を計算して、今後の取扱いを判定するワークシート“販売分析”を作成した。ワークシート“販売分析”の例を図1に示す。
(1) ワークシートのセルA1〜L21は“売上高分析表”である。“売上高分析表”では、対象商品の年ごとの売上高を基にして、販売状況を分析する指標を求めている。
① セルC2〜K2には、対象商品の商品コードが昇順に格納されている。
② マクロSales_Totalを実行すると、セルA3、セルA8及びセルA13には、分析する過去3年の西暦年が昇順に格納され、セルC3〜K3、セルC8〜K8及びセルC13〜K13には、対象商品の当該年の売上高が、千円未満を四捨五入した千円単位の値で格納される。
③ 対前年比率は、各商品の前年の売上高を基準にした当該年の売上高の割合である。
④ 構成比率は、当該年の対象9商品の売上高の合計に占める各商品の売上高の割合である。
⑤ 構成比率累計は、当該年の各商品の構成比率を、大きいものから順に当該商品まで累計した値である。ここで、当該商品と構成比率の同じ商品が複数あったときは、当該商品の分だけを加算する。
⑥ 管理区分は、各商品の構成比率累計が、表1の構成比率累計の範囲のどの区分に属するかを判定した結果であり、商品の管理方法を決定する指標である。
⑦ セルC18〜K20には、表2に示した各条件の真偽を商品ごとに判定した結果を設定する。セルC21〜K21には、各条件の真偽の組合せに基づいて、各商品の今後の取扱方針を設定する。
(a) “Y”は条件が真、“N”は条件が偽であることを表す。
(b) 取扱方針は、条件の真、偽が各列の組合せに該当する商品の、今後の取扱方針を表す。
(2) ワークシートのセルA23〜E27は“管理区分表”である。“管理区分表”には、管理区分と対応する構成比率累計の範囲が、表1のとおりに格納されている。
(3) ワークシートのセルA29〜J34は“取扱判定表”である。
① セルC31〜J34には取扱方針を判定するための条件の組合せと、その組合せに基づいた取扱方針が、表2のとおりに格納されている。
② セルC30〜J30には、セルC31〜J33の各列の条件の“Y”を1、“N”を0とし、上から順に2進数の3桁目、2桁目、1桁目として並べたものを10進数に変換した値が、判定コードとして格納されている。
設問1：
問題文を見る ワークシート“販売分析”の作成に関する記述中のに入れる正しい答えを、解答群の中から選べ。
(1) セルL3、セルL8及びセルL13には、当該年の対象9商品の売上高の合計を求める式を入力する。
(2) セルC4〜L4には、“−”を入力する。セルC9〜L9及びセルC14〜L14には、対前年比率を求める式を入力する。
(3) セルC5〜K5、セルC10〜K10及びセルC15〜K15には、構成比率を求める式を入力する。
(4) セルC6には、構成比率累計を求める次の式を入力し、セルD6〜K6、セルC11〜K11及びセルC16〜K16に複写する。
a
(5) セルC7には、管理区分を表示する次の式を入力し、セルD7〜K7、セルC12〜K12及びセルC17〜K17に複写する。
b
(6) セルC18には、直近2年の売上高が連続して前年よりも減少しているかどうかを判定する次の式を入力し、セルD18〜K18に複写する。
c
(7) セルC19〜K19には、当該商品の直近の年の対前年比率が85％未満かどうかを判定する式を入力する。
(8) セルC20〜K20には、当該商品の直近2年の管理区分が連続して“C”かどうかを判定する式を入力する。
(9) セル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：構成比率累計
構成比率累計は、構成比率が大きい商品から順に合計した値です。ただし、本文には「当該商品と構成比率の同じ商品が複数あったときは、当該商品の分だけを加算する」とあります。
そのため、セルC6では、商品ごとの構成比率が入っているC5〜K5のうち、C5より大きい値だけを合計し、最後にC5を加えます。式は次の形です。
「条件付合計($C5:$K5,> C5,$C5:$K5) + C5」
条件付合計は、指定した条件に合う値だけを合計する関数です。条件を「C5より大きい」とすることで、同じ構成比率の商品は合計に含めません。最後にC5を加えるので、当該商品自身は含まれます。
範囲の列を固定する絶対参照（式を横に複写しても参照する列が変わらない指定）にしているため、式をD6〜K6に複写しても、各商品の構成比率が入った行全体を調べられます。一方、条件のC5は複写先に応じてD5、E5のように変わります。
したがって、aは イ です。構成比率と同じ値を持つ別の商品まで含めてしまう「≧」では、本文の条件に合いません。
b：管理区分
管理区分の範囲は、Aが「0%を超え、70%以下」、Bが「70%を超え、90%以下」、Cが「90%を超え、100%以下」です。
管理区分表のB25:B27にある境界値（0%、70%、90%）のうち、構成比率累計より小さいものを数えると、当てはまる区分の行番号になります。条件付個数は、指定した条件に合うセルの個数を数える関数です。表引きは、指定した行番号の値を表から取り出す関数です。
セルC6が70%以下なら、C6より小さい境界値は0%だけなので、個数は1です。これにより、A25:A27の1行目にあるAが選ばれます。70%を超えると70%も数えられ、個数が2になるので、Bが選ばれます。同じ考え方で、90%を超えると個数が3になり、Cが選ばれます。
式は次のとおりです。
「表引き($A$25:$A$27,条件付個数($B$25:$B$27, < C6),1)」
「未満」の条件で数えるため、ちょうど70%はA、ちょうど90%はBに分類されます。これは、表の「以下」という境界を正しく扱うためです。
したがって、bは ア です。
c：直近2年の売上高が連続して減少したか
対前年比率は、前年の売上高を1としたときの当該年の売上高の割合です。たとえば、100%は数値としては1に当たります。したがって、対前年比率が1未満なら、前年より売上高が減少しています。
C9は直近の2年のうち前の年の対前年比率、C14は直近の年の対前年比率です。2年連続で減少したと判定するには、両方が1未満でなければなりません。論理積は、複数の条件がすべて真のときに真となる判定です。
そのため、「C9 < 1」と「C14 < 1」の両方を論理積で判定し、両方が真ならY、そうでなければNとします。IFは、条件が真の場合と偽の場合で返す値を分ける関数です。
式は次のとおりです。
「IF(論理積(C9 < 1, C14 < 1),'Y','N')」
したがって、cは ア です。論理和では、どちらか一方だけの減少でも条件を満たしてしまいます。
d：条件の組合せから取扱方針を選ぶ
取扱判定表では、3つの条件を上から順に2進数の3桁目、2桁目、1桁目として並べます。2進数は、各桁の値を0または1で表す数の仕組みです。各桁の位の値は、上から順に4、2、1です。
そのため、条件がYなら対応する値を足し、Nなら0を足します。セルC18、C19、C20に対応する重みは、それぞれ4、2、1です。たとえば、条件がY、N、Yなら、2進数の101に当たり、判定コードは5になります。
判定コードを使って取扱方針を取り出す関数が水平照合です。表範囲の先頭行から検索値を探し、指定した行の値を返します。セルC30:J34の5行目はワークシートの34行目で、取扱方針が並んでいます。また、検索方法の0は完全に一致する値を探す指定です。
したがって、判定コードの重みを4、2、1とし、表範囲の5行目を取り出す式を選びます。dは エ です。
誤りやすいポイント
- 構成比率累計で「以上」を使うと、構成比率が同じ別の商品まで加算してしまいます。条件は「より大きい」とし、当該商品自身は別に加えます。
- 管理区分の境界では、70%はA、90%はBに含まれます。「以下」の範囲に含まれる境界値を、次の区分にしてはいけません。
- 2年連続の減少は、2年分の対前年比率がどちらも1未満である必要があります。片方だけの減少では条件を満たしません。
- 3つの条件を2進数にするとき、上から順に対応する重みは4、2、1です。最初の条件に3を掛けるのではありません。
- 水平照合で取扱方針を返すには、表範囲の5行目を指定します。4行目は取扱方針の行ではありません。
FAQ
Q: 構成比率累計を求めるとき、商品を構成比率の大きい順に並べ替えなくてもよいのですか？
A: 並べ替えなくても求められます。当該商品の構成比率より大きい値だけを合計し、当該商品の値を加えることで、大きい順に累計した値と同じ結果になります。
A: 並べ替えなくても求められます。当該商品の構成比率より大きい値だけを合計し、当該商品の値を加えることで、大きい順に累計した値と同じ結果になります。
Q: 70%ちょうどの商品がBではなくAになるのはなぜですか？
A: Aの範囲が「70%以下」だからです。同様に、90%ちょうどは「90%以下」のBに含まれます。
A: Aの範囲が「70%以下」だからです。同様に、90%ちょうどは「90%以下」のBに含まれます。
Q: 取扱判定表のコードで、Yを1、Nを0に置き換えるだけではいけないのですか？
A: 置き換えた3桁を10進数のコードにするため、各桁に重みを付けます。上から順に4、2、1を使って合計します。
A: 置き換えた3桁を10進数のコードにするため、各桁に重みを付けます。上から順に4、2、1を使って合計します。
関連キーワード: 絶対参照、条件付き集計、論理積、二進数、表引き、水平照合
設問2：
問題文を見る 図2に示すワークシート“販売データ”のデータを基に、〔ワークシート：販売分析〕の説明の(1)の②で示した処理を実行するマクロSales_Totalを作成して、ワークシート“販売分析”に格納した。マクロSales_Totalのに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：販売データ〕
ワークシート“販売データ”の例を図2に示す。
(1) ワークシート“販売データ”には、販売管理システムから、対象商品の連続した過去3年間のデータを抽出して格納してある。
(2) 行1には、見出しが格納されている。行2以降には、販売した年、月、日、商品コード、得意先コード、単価及び数量のデータが、年を第1キー、商品コードを第2キーとして昇順に格納されている。
(3) データが格納されていないセルの値は、nullである。
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：カ
解説
解答の導き方
このマクロは、販売データを年と商品ごとにまとめ、売上高を千円単位にしてワークシートへ書き込む処理です。まず、変数（処理の途中で値を入れておく場所）の役割を整理します。
1. 年ごとのデータを処理します
販売データは「年を第1キー、商品コードを第2キーとして昇順に格納」されています。つまり、同じ年のデータがまとまり、その中では同じ商品コードのデータも連続します。
data_rowは最初に0です。相対(A2, data_row, 0)は、A2からdata_row行下のセルを指します。そのため、data_rowが0のときは販売データのA2、1のときはA3を読みます。
販売分析では、相対(A3, total_row, 0)に処理中の年を書き込みます。total_rowが0ならA3です。マクロは、販売データの年がこの年と同じ間、その年の売上高を集計します。
2. 商品コードが一致するデータを集計します
販売分析の見出しには、商品コードが昇順に並んでいます。total_colが0なら相対(C2, 0, total_col)はC2、1ならD2を指します。一方、販売データの商品コードはD列にあります。
したがって、現在の商品の売上高を集める条件は、販売分析の商品コードと販売データの現在行の商品コードが等しいことです。これに当たるのは eのア です。
条件が真の間は、現在の販売データの売上高をsubtotalに加え、data_rowを1増やします。同じ商品コードの記録が続く間は、これを繰り返します。商品コードが変わると条件が偽になり、その商品分の集計を終えます。
3. 単価と数量から売上高を計算します
図2では、単価はF列、数量はG列です。1件の売上高は、単価と数量を掛けて求めます。現在処理しているデータ行の値を使うため、F2とG2をdata_row分だけ下へ移動して参照します。
この処理は、次の式に当たる fのウ です。
同じ年・商品コードの販売記録が複数ある場合も、subtotalに加算していくことで合計できます。
4. 合計を千円単位にして書き込みます
商品コードごとの集計が終わると、subtotalを1000で割り、千円未満を四捨五入して販売分析へ書き込みます。マクロの四捨五入(subtotal / 1000, 0)は、小数点以下を0桁にする指定です。書き込み後はsubtotalを0に戻し、次の商品を集計します。
5. 次の年の書き込み位置へ進みます
販売分析では、各年の分析結果が5行ずつ並びます。たとえば、年はA3、A8、A13に書き込みます。したがって、次の年へ進むときはtotal_rowを5増やします。これに当たるのは gのカ です。
以上から、eでは商品コードの一致を条件にし、fでは現在行の単価と数量を使って売上高を加算し、gでは年ごとの出力位置を5行進めます。
誤りやすいポイント
- 販売データの商品コードはD列です。B列は月なので、商品コードとの照合には使いません。
- 商品コードを照合するときは、販売分析側の見出しをtotal_colで横に移動し、販売データ側の現在行をdata_rowで下に移動します。この2つの役割を取り違えないようにします。
- 売上高を集計する行はdata_rowで決まります。total_rowは販売分析への書き込み位置を示すため、単価や数量の行を決めるのには使いません。
- total_rowを1だけ増やすのではなく、年ごとの分析欄が5行ずつ離れているため、5増やします。
FAQ
Q: 商品コードが変わったら、なぜすぐに次の商品へ進めるのですか？
A: 販売データが年、商品コードの順に並んでいるためです。同じ商品の記録がまとまっているので、商品コードが変わった時点で、その商品の売上高の合計が確定します。
A: 販売データが年、商品コードの順に並んでいるためです。同じ商品の記録がまとまっているので、商品コードが変わった時点で、その商品の売上高の合計が確定します。
Q: subtotalを商品ごとに0へ戻すのはなぜですか？
A: 次の商品を集計するときに、前の商品分の合計が混ざらないようにするためです。
A: 次の商品を集計するときに、前の商品分の合計が混ざらないようにするためです。
Q: 販売分析へ書き込むときに1000で割るのはなぜですか？
A: 表の単位が千円だからです。販売データから計算した売上高を千円単位に直し、指定どおり千円未満を四捨五入します。
A: 表の単位が千円だからです。販売データから計算した売上高を千円単位に直し、指定どおり千円未満を四捨五入します。
関連キーワード: 表計算、相対参照、繰返し処理、集計処理、四捨五入
