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

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

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

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

〔表計算の説明〕
 小売販売のZ店では,会員として登録した顧客に会員カードを発行して販売促進活動に利用している。このたび,会員への2018年10月1日から2019年3月31日までの期間(以下,対象期間という)の販売データを基に,販売促進キャンペーンを実施することにした。

〔ワークシート:販売データ〕
 ワークシート"販売データ"には,対象期間における会員への販売データが,販売日の昇順で格納されている。ワークシート"販売データ"の例を,図1に示す。
pm13_1.png
  • 列Aのレシート番号は,1回の販売ごとに発行される一意の番号である。1回の販売で複数の種類の商品を販売したときは商品コードごとに行を替えて連続して格納し,同じレシート番号を付す。
  • 列Bの販売日は,商品を販売した日付である。日付は,1900年1月1日からの経過日数として記録され,表計算ソフトの機能によって,yyyy-mm-dd(年-月-日)の形式で表示される。
  • 列Cの会員番号は,1001~9999の一意の数値である。
  • 列Dの商品コードは販売した商品の商品コード,列Eの単価は販売単価,列Fの数量は販売数量,列Gは販売金額(単価×数量)である。
  • 列Hの会員販売通番は,上位4桁を会員番号,下位3桁を会員ごとの販売の通番とする7桁の数値である。会員ごとの販売の通番は,行2から当該行までに格納された当該行の会員番号をもつ会員に対する販売回数であり,1,000未満である。ただし,同じレシート番号が続くときの二つ目以降の当該セルの値は0である。
  • 同じレシート番号をもつ販売データの販売日及び会員番号は,全て同じである。
  • 販売データは9,998件以下であり,販売データが入力されていない行の各セルには,空値が格納されている。

〔ワークシート:会員管理〕
 ワークシート"会員管理"には,ワークシート"販売データ"のデータを基に,会員への販売状況や,販売促進キャンペーンのための情報を格納する。ワークシート"会員管理"の例を,図2に示す。
pm13_2.png
  • セル B1 の数値は,ワークシート"販売データ"に格納した販売データの行数である。セル E1 の数値は,会員数であり,ワークシート"会員管理"の行4以降に格納したデータの行数に等しい。
  • 列Aの行4以降には,全ての会員の会員番号を昇順に格納する。
  • 列Bの販売額合計は,ワークシート"販売データ"の中にある当該行の会員番号をもつ会員に対する販売金額の合計である。
  • 列Cの販売回数は,ワークシート"販売データ"中にある,当該行の会員番号をもつ会員に対する販売回数である。
  • 列Dの最終販売日は,当該行の会員番号をもつ会員に対して最後に販売した日付である。対象期間に販売の記録がないときは,空値である。
  • 列Eの会員クラスは,当該行の会員番号をもつ会員を,下に示すクラス分類のルールに従って分類した結果である。
  • 列Fのクーポンは,キャンペーンで当該行の会員番号をもつ会員に発行するクーポンの総額である。クーポンはZ店で1枚1,000円として使用できる券であり,販売額合計が50,000円以上100,000円未満で1枚,100,000円以上150,000円未満で2枚,… というように販売額合計の50,000円ごとに1枚を発行する。

〔クラス分類のルール〕
  • 対象期間における各会員の販売額合計と販売回数を,表1に示すランク分類基準に従って,それぞれランク1~3に分類する。
    pm13_3.png
  • ワークシート"分類表"を参照して,会員をS,A,B,Cのクラスに分類する。表1のランク分類基準に基づいて作成したワークシート"分類表"を,図3に示す。
    pm13_4.png
  1. セル D2~F2 には販売額合計のランクを示す値が,セル D3~F3 には販売額合計をセル D2~F2 のランクに分類するときの販売額合計の下限が格納されている。
  2. セル B4~B6 には販売回数のランクを示す値が,セル C4~C6 には販売回数をセル B4~B6 のランクに分類するときの販売回数の下限が格納されている。
  3. セル D4~F6 には,当該セルの属する販売額合計と販売回数のランクから決まる会員クラスを示す"S',"A","B","C"のいずれかが格納されている。

設問1

ワークシート"会員管理"に関する次の記述中の に入れる正しい答えを,解答群の中から選べ。
  • セル B4 に販売額合計を求める式を入力し,セル B5~B9002 に複写する。
  • セル C4 に販売回数を求める式を入力し,セル C5~C9002 に複写する。
  • セル D4 に最終販売日を求める次の式を入力し,セル D5~D9002 に複写する。
     IF(論理和(A4=null,C4=0),null,a)
  • セル E4 に会員クラスを求める次の式を入力し,セル E5~E9002 に複写する。
     IF(A4=null,null,表引き(分類表!D$4:F$6,b))
  • セル F4 にクーポンの総額を求める次の式を入力し,セル F5~F9002 に複写する。
     IF(A4=null,null,c))
a に関する解答群
  • 照合検索(A4,販売データ!C$2:C$9999,販売データ!A$2:A$9999)
  • 照合検索(A4,販売データ!C$2:C$9999,販売データ!B$2:B$9999)
  • 照合検索(A4*1000+1,販売データ!H$2:H$9999,販売データ!A$2:A$9999)
  • 照合検索(A4*1000+1,販売データ!H$2:H$9999,販売データ!B$2:B$9999)
  • 照合検索(A4*1000+C4,販売データ!H$2:H$9999,販売データ!A$2:A$9999)
  • 照合検索(A4*1000+C4,販売データ!H$2:H$9999,販売データ!B$2:B$9999)
b に関する解答群
  • 照合一致(B4,分類表!C$4:C$6,1),照合一致(C4,分類表!D$3:F$3,1)
  • 照合一致(B4,分類表!D$3:F$3,1),照合一致(C4,分類表!C$4:C$6,1)
  • 照合一致(C4,分類表!C$4:C$6,1),照合一致(B4,分類表!D$3:F$3,1)
  • 照合一致(C4,分類表!D$3:F$3,1),照合一致(B4,分類表!C$4:C$6,1)
c に関する解答群
  • 切捨て(B4,-4)/50000*1000
  • 切捨て(B4*1000,-8)/50000
  • 切捨て(B4/50000,0)*1000
  • 切捨て(B4/50000*1000,0)
  • 切捨て(B4/50000*1000,-4)
解答選択欄
  • a:
  • b:
  • c:
  • a=
  • b=
  • c=

解説

aについて〕
処理aを囲んでいるIF関数の論理式と処理分岐は以下の通りです。
論理式:A4(会員番号)が空白、あるいは、C4(販売回数)が0:
 真の場合:何も表示しない。
 偽の場合:処理aを行う。
これは、処理対象から空白行及び会員ではあるが期間内の販売がない会員の行を除くものであるため、本題である「最終販売日」を求める式を選択すれば回答となります。

まず「ア」と「イ」は、選択欄の照合検索の検索値として「A4(会員番号)」を指定しています。そして、照合検索の検索条件のセル範囲の引数として、販売データ表のC列を指定しています。したがって、照合検索ではC列の"会員番号"が第1引数で指定された会員番号である行を上端から順に検索することとなります。購入回数が1回のみの会員ならばこれでも最終販売日が取得できますが、同じ会員が複数回購入している場合、そのうち最も上の行、すなわち最初の販売日のデータが取得されてしまうため誤りです。

残る4つの式は、販売データ表のH列、すなわち"会員販売通番"を対象に検索しています。この"会員販売通番"は上4桁が会員番号、下3桁が当該行の会員番号をもつ会員に対する販売回数(1,000未満)となっています。

最終販売日を求めたいのですから、販売データ表内でその会員に最後に販売したデータにアクセスする必要があります。会員管理表の販売回数の値は、期間内に当該会員に販売した回数ですから、最後に販売したデータの"会員販売通番"は「会員番号+販売回数」になっているはずです。「ウ」と「エ」では、C4(販売回数)でなく固定値の1を加算しているので、常に当該顧客の最初の販売日を取得することになってしまいます。よって、「ウ」「エ」は誤りです。

残った2つのうち、「オ」は、照合検索の抽出するセルの範囲に販売データ表のA列"レシート番号"を指定しているため誤りです。これで取得できるのは、その会員に最後に販売したデータのレシート番号になってしまいます。一方「カ」は、抽出するセルの範囲に販売データ表のB列"販売日"指定しているため「最終販売日」が取得できます。よって、aに入る式としては「カ」が適切です。

a=カ

bについて〕
処理bを囲んでいるIF関数は、処理aと同様です。
ここでは、本題である「会員クラス」を求める式を選択すれば回答となります。

bは、表引き関数の第2・3引数に当たるため、第2引数には番号を返す照合一致、第3引数には番号を返す"照合一致"が使われています。"照合一致"の第2・第3引数は、行番号、列番号の順である必要があります。

分類表を見ると、行番号で販売回数のランクを、列番号で販売額合計をランクを判定し、重なり合ったところが会員クラスの値になっています。「ア」と「イ」は、第2引数にB4(販売額合計)、第3引数にC4(販売回数)というように行・列を逆に指定しているため誤りです。

残った「ウ」と「エ」のうち、「エ」は照合一致の範囲を逆に指定しているため誤りです。よって、bに入る式としては「ウ」が適切です。

b=ウ

cについて〕
処理cを囲んでいるIF関数は、処理aと同様です。
ここでは、本題である「クーポンの総額」を求める式を選択すれば答えとなります。クーポンは「販売額合計」の50,000円ごとに1枚発行されます。

仮の値を用いて、販売額合計からクーポンの総額が正しく求められるかを検証していきます。仮に販売合計額を170,000円とすると、式の答えとなるべきクーポンの総額は3,000円となります。
  •  切捨て(170000, -4)/50000*1000
    =170000/50000*1000
    =3.4*1000=3400 …×
  •  切捨て(170000*1000, -8)/50000
    =100000000÷50000=2000 …×
  •  切捨て(170000/50000, 0)*1000
    =切捨て(3.4, 0)*1000
    =3*1000=3000 …〇
  •  切捨て(170000/50000*1000, 0)
    =切捨て(3400, 0)=3400 …×
  •  切捨て(170000/50000*1000, -4)
    =切捨て(3400, -4)=0 …×
唯一、「ウ」の式だけが正しいクーポンの総額を算出可能です。

c=ウ

設問2

新商品の見本の配布などの販売促進キャンペーンに活用するために,ワークシート"商品別管理"を作成し,マクロ manageMembers を格納した。マクロ manageMembers 中の に入れる正しい答えを,解答群の中から選べ。

〔ワークシート:商品別管理〕
 セル B1 に商品の商品コードを入力して,マクロ manageMembers を実行すると,ワークシート"販売データ"及びワークシート"会員管理"に格納されているデータを基に,列Aの行3以降に当該商品を購入した会員の会員番号を,列Bの行3以降に会員番号ごとの当該商品の販売金額の合計(以下,商品販売額という)を,列Cの行3以降に会員番号ごとに最後に当該商品を販売した日付(以下,商品最終販売日という)を,それぞれ表示する。各行は同一会員に関する項目で構成し,商品販売額の降順に,商品販売額が等しいときは商品最終販売日の昇順に整列して表示する。ここで,マクロの実行開始時には,セル A3~C9001 に空値が格納されている。ワークシート"商品別管理"の例を,図4に示す。
pm13_5.png
〔マクロ:manageMembersの説明〕
 このマクロは,9,001行3列の要素をもつ2次元配列 sumTab を用意し,1以上会員数以下の全ての整数 i に対して,sumTab[i,0]に会員番号を格納し,その会員番号に対応する商品販売額をsumTab[i,1]に,商品最終販売日をsumTab[i,2]に格納する。
  • 1以上会員数+1以下の全ての整数 i に対して,sumTab[i,1]に初期値として0を格納する。
  • ワークシート"販売データ"の行2以降のデータを上から順に参照し,商品コードがセル B1 で指定した値と等しいとき,当該データの会員番号に対応した2次元配列 sumTab の商品販売額の要素に販売金額を加算するとともに,商品最終販売日の要素に日付を格納する処理を繰り返す。
  • 2次元配列 sumTab の会員番号を格納した行を,商品販売額の降順に,商品販売額が等しいときは商品最終販売日の昇順に整列する。
  • ワークシート"商品別管理"の行3以降のセルに,会員番号,商品販売額,商品最終販売日を,(3)の処理で整列された順に格納する。商品販売額が0の要素が現れたら,処理を終了する。
pm13_6.png
d に関する解答群
  • sumTab[1,index] ← sumTab[1,index]+相対(販売データ!A1,i,6)
  • sumTab[1,index] ← sumTab[i,index]+相対(販売データ!A1,index,6)
  • sumTab[i,1] ← sumTab[i,1]+相対(販売データ!A1,i,6)
  • sumTab[i,1] ← sumTab[i,1]+相対(販売データ!A1,index,6)
  • sumTab[index,1] ← sumTab[index,1]+相対(販売データ!A1,i,6)
  • sumTab[index,1] ← sumTab[index,1]+相対(販売データ!A1,index,6)
e に関する解答群
  • 論理積(sumTab[j,1]<sumTab[j+1,1],論理和(sumTab[j,1]=
    sumTab[j+1,1],sumTab[j,2]>sumTab[j+1,2]))
  • 論理積(sumTab[j,1]>sumTab[j+1,1],
    sumTab[j,2]>sumTab[j+1,2])
  • 論理積(sumTab[j,1]>sumTab[j+1,1],論理和(sumTab[j,1]=
    sumTab[j+1,1],sumTab[j,2]<sumTab[j+1,2]))
  • 論理和(sumTab[j,1]<sumTab[j+1,1],
    sumTab[j,2]>sumTab[j+1,2])
  • 論理和(sumTab[j,1]<sumTab[j+1,1],論理積(sumTab[j,1]=
    sumTab[j+1,1],sumTab[j,2]>sumTab[j+1,2]))
  • 論理和(sumTab[j,1]>sumTab[j+1,1],論理積(sumTab[j,1]=
    sumTab[j+1,1],sumTab[j,2]<sumTab[j+1,2]))
f に関する解答群
  • 相対(A2,i,j) ← sumTab[i,1]
  • 相対(A2,i,j) ← sumTab[i,j]
  • 相対(A2,i,j+1) ← sumTab[i,j]
  • 相対(A2,j,i) ← sumTab[j,1]
  • 相対(A2,j,i) ← sumTab[j,i]
  • 相対(A2,j+1,i) ← sumTab[j, 1]
解答選択欄
  • d:
  • e:
  • f:
  • d=
  • e=
  • f=

解説

マクロ manageMembers は説明の(1)から(4)に対応した4つ部分で構成されています。
pm13_7.png

dについて〕
ここでは、(2)内の記述である「当該データの会員番号に対応した2次元配列 sumTab の商品販売額の要素に販売金額を加算する」を実行します。変数宣言部を見ると sumTab は [9001, 3] と定義されています。つまり、添え字の1つ目が行番号、2つ目が列番号を示しています。また、sumTabは、会員管理表の値を先頭から順にコピーしているので、sumTab[i, 0]の会員番号は昇順に整列されていることになります。
そして、変数 index には、セルB1(=集計対象の部品番号)と一致した行の行番号が格納されています。

アとイは、両者とも sumTab の行番号として1を指定しています。これだと常に2行目に固定設定することになるため誤りです。
ウとエは、両者ともカウンタ変数である i 番目に格納するため誤りです。iは販売データ表の現在参照している行位置を保持しています。商品販売額を加算するのは、sumTab内の当該会員番号の行でなければなりません。
カは、相対位置に変数 index を指定するため誤りです。
オは、index行の「商品販売額」に「販売データ」シートの販売額を加算した結果を設定するのが正しいので「オ」が正解です。

d=オ

eについて〕
ここでは、(3)内の記述である「商品販売額の降順に、商品販売額が等しいときは商品最終販売日の昇順に整列する」条件式を実行します。eには行の入れ替えを実行するか否かを判定するための条件式が入ります。

入れ替えを実行すべき時は、
  • 現在参照している行の商品販売額が、次の行の商品販売額よりも少ない
  • 現在参照している行の商品販売額が、次の行の商品販売額が同じ、かつ、現在参照している行の最終販売日(sumTab[j, 2])が、次の行の最終販売日(sumTab[j+1, 2])よりも大きい
という2つの条件のいずれかを満たす場合です。これをマクロの条件式に直すと
  • sumTab[j, 1] < sumTab[j+1, 1])
  • (sumTab[j, 1] = sumTab[j+1, 1]) かつ (sumTab[j, 2] > sumTab[j+1, 2])
表計算では、andを論理積関数、orを論理和関数で表すので、2つの式を論理和で評価し、"かつ"の部分を論理積で評価すると、
論理和(
sumTab[j,1]<sumTab[j+1,1],
論理積(sumTab[j,1]=sumTab[j+1,1],
    sumTab[j,2]>sumTab[j+1,2])
)
が適切な式です(見やすくするために数行に分けています)。

アとイとウは、最初が論理積での指定のため誤りです。
エは、「現在参照している行の商品販売額が、次の行の商品販売額が同じ」という条件が不足しているため誤りです。
カは、不等号に向きが逆です。これだと「商品販売額が次の行より大きい場合」「最終販売日が次の行より小さい場合」となってしまい、商品販売額の昇順、最終販売日の降順に整列されてしまうため誤りです。
オは、正しく条件を指定しているため正解です。

e=オ

fについて〕
ここでは(1)~(3)で該当行を抽出した後、整列済みの sumTab を変数iとjを使ってセルA2以降に順に出力すれば正解です。前述したように sumtab では[行, 列]でデータにアクセスします。また、相対も(基準セル, 行位置, 列位置)でセルを指定するので、単純に相対(A2, i, j)の位置に sumTab[i, j] の値を格納すれば良いことになります。

アとエは、sumTab[i, 1]としており、商品別管理のどの列も商品販売額のデータになってしまいます。よって誤りです。
ウは、出力先の相対列位置を j+1 としているため、B列に会員番号が、C列に商品販売額が、D列に最終販売日が表示されることになります。よって誤りです。
オとカは、それぞれ出力先の相対位置としてiとjを逆に指定しているため誤りです。また、sumTabの指定もそれぞれ誤りです。
イは、整列した sumTab のデータをiとjを使って順に出力するため正解です。

f=イ

Pagetop