基本情報技術者 2019年 春期 午後 問13
次の表計算のワークシート及びマクロの説明を読んで、設問1，2に答えよ。
〔表計算の説明〕
小売販売のZ店では、会員として登録した顧客に会員カードを発行して販売促進活動に利用している。このたび、会員への2018年10月1日から2019年3月31日までの期間（以下、対象期間という）の販売データを基に、販売促進キャンペーンを実施することにした。
〔ワークシート：販売データ〕
ワークシート“販売データ”には、対象期間における会員への販売データが、販売日の昇順で格納されている。ワークシート“販売データ”の例を、図1に示す。
(1) 列Aのレシート番号は、1回の販売ごとに発行される一意の番号である。1回の販売で複数の種類の商品を販売したときは商品コードごとに行を替えて連続して格納し、同じレシート番号を付す。
(2) 列Bの販売日は、商品を販売した日付である。日付は、1900年1月1日からの経過日数として記録され、表計算ソフトの機能によって、yyyy-mm-dd（年-月-日）の形式で表示される。
(3) 列Cの会員番号は、1001〜9999の一意の数値である。
(4) 列Dの商品コードは販売した商品の商品コード、列Eの単価は販売単価、列Fの数量は販売数量、列Gは販売金額（単価×数量）である。
(5) 列Hの会員販売通番は、上位4桁を会員番号、下位3桁を会員ごとの販売の通番とする7桁の数値である。会員ごとの販売の通番は、行2から当該行までに格納された当該行の会員番号をもつ会員に対する販売回数であり、1,000未満である。ただし、同じレシート番号が続くときの二つ目以降の当該セルの値は0である。
(6) 同じレシート番号をもつ販売データの販売日及び会員番号は、全て同じである。
(7) 販売データは9,998件以下であり、販売データが入力されていない行の各セルには、空値が格納されている。
〔ワークシート：会員管理〕
ワークシート“会員管理”には、ワークシート“販売データ”のデータを基に、会員への販売状況や、販売促進キャンペーンのための情報を格納する。ワークシート“会員管理”の例を、図2に示す。
(1) セルB1の数値は、ワークシート“販売データ”に格納した販売データの行数である。セルE1の数値は、会員数であり、ワークシート“会員管理”の行4以降に格納したデータの行数に等しい。
(2) 列Aの行4以降には、全ての会員の会員番号を昇順に格納する。
(3) 列Bの販売額合計は、ワークシート“販売データ”の中にある当該行の会員番号をもつ会員に対する販売金額の合計である。
(4) 列Cの販売回数は、ワークシート“販売データ”中にある、当該行の会員番号をもつ会員に対する販売回数である。
(5) 列Dの最終販売日は、当該行の会員番号をもつ会員に対して最後に販売した日付である。対象期間に販売の記録がないときは、空値である。
(6) 列Eの会員クラスは、当該行の会員番号をもつ会員を、下に示すクラス分類のルールに従って分類した結果である。
(7) 列Fのクーポンは、キャンペーンで当該行の会員番号をもつ会員に発行するクーポンの総額である。クーポンはZ店で1枚1,000円として使用できる券であり、販売額合計が50,000円以上100,000円未満で1枚、100,000円以上150,000円未満で2枚、…というように販売額合計の50,000円ごとに1枚を発行する。
〔クラス分類のルール〕
(1) 対象期間における各会員の販売額合計と販売回数を、表1に示すランク分類基準に従って、それぞれランク1〜3に分類する。
(2) ワークシート“分類表”を参照して、会員をS、A、B、Cのクラスに分類する。表1のランク分類基準に基づいて作成したワークシート“分類表”を、図3に示す。
① セルD2〜F2には販売額合計のランクを示す値が、セルD3〜F3には販売額合計をセルD2〜F2のランクに分類するときの販売額合計の下限が格納されている。
② セルB4〜B6には販売回数のランクを示す値が、セルC4〜C6には販売回数をセルB4〜B6のランクに分類するときの販売回数の下限が格納されている。
③ セルD4〜F6には、当該セルの属する販売額合計と販売回数のランクから決まる会員クラスを示す“S”、“A”、“B”、“C”のいずれかが格納されている。
設問1：
問題文を見る ワークシート“会員管理”に関する次の記述中のに入れる正しい答えを、解答群の中から選べ。
(1) セルB4に販売額合計を求める式を入力し、セルB5〜B9002に複写する。
(2) セルC4に販売回数を求める式を入力し、セルC5〜C9002に複写する。
(3) セルD4に最終販売日を求める次の式を入力し、セルD5〜D9002に複写する。
IF(論理和(A4 = null,C4 = 0),null,a)
(4) セルE4に会員クラスを求める次の式を入力し、セルE5〜E9002に複写する。
IF(A4 = null,null,表引き(分類表!D$4:F$6,b))
(5) セル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：最終販売日
列Dでは、販売記録がない会員の最終販売日を空欄にします。式の条件には「A4 = null」または「C4 = 0」の場合に空欄を返す処理があります。つまり、販売記録がある会員について、販売データから最後の販売日を探せばよいです。
本文では、列Hの会員販売通番について「上位4桁を会員番号、下位3桁を会員ごとの販売の通番」とあります。したがって、会員番号と販売回数から、その会員の最後の販売を特定する値を作れます。
たとえば、会員番号が1001で販売回数が7回なら、検索する値は「会員番号×1000＋販売回数」、つまり1001007です。列Hには販売ごとの通番が記録され、同じレシート番号の2行目以降は0です。そのため、この値を列Hから探せば、7回目の販売の先頭行を見つけられます。
見つけた行から返すのは、販売日が入っている列Bです。レシート番号が入っている列Aではありません。よって、列Hを検索範囲、列Bを返す範囲とするカが適切です。
b：会員クラス
「表引き」は、表の中から行番号と列番号で指定した位置の値を取り出す関数です。ここでは、販売回数のランクで行を選び、販売額合計のランクで列を選びます。
分類表では、販売回数の下限がC4:C6に、販売額合計の下限がD3:F3に並んでいます。「照合一致」の最後の引数が1の場合、指定した値以下で最も大きい下限を探し、その位置を返します。下限は小さい順に並んでいるため、値がどのランクに入るかを判定できます。
式に入れる順番は、表引きの行番号が先、列番号が後です。
- 行番号：販売回数C4を、販売回数の下限C4:C6と照合します。
- 列番号：販売額合計B4を、販売額合計の下限D3:F3と照合します。
したがって、行番号にC4の照合結果、列番号にB4の照合結果を使うウが適切です。
たとえば、図2の会員番号1001は、販売回数が7回、販売額合計が198,500円です。販売回数の下限0、6、12のうち、7以下で最も大きい値は6なので行番号は2です。販売額合計の下限0、40,000、80,000のうち、198,500以下で最も大きい値は80,000なので列番号は3です。分類表の2行目・3列目に当たるセルはSです。
c：クーポンの総額
クーポンは販売額合計が50,000円増えるごとに1枚発行され、1枚は1,000円です。まず、販売額合計を50,000で割り、端数を切り捨てれば発行枚数になります。その枚数に1,000円を掛けると、クーポンの総額になります。
したがって、「B4を50,000で割る」「小数部分を切り捨てる」「1,000を掛ける」という順序で計算するウが適切です。
図2の会員番号1001の場合、販売額合計は198,500円です。計算すると、 なので、発行枚数は3枚です。総額は 円になります。
誤りやすいポイント
-
aで会員番号だけを検索すること
会員番号だけでは、その会員の何回目の販売かを特定できません。会員番号と販売回数を組み合わせた値で、最後の販売を特定します。 -
aで列Aを返す範囲にすること
列Aはレシート番号、列Bは販売日です。求めるのは最終販売日なので、返す範囲は列Bです。 -
bで行と列の順番を逆にすること
表引きでは、最初の照合結果が行番号、次の照合結果が列番号になります。販売回数から行を、販売額合計から列を求めます。 -
cで切り捨てる位置を間違えること
発行枚数を求めるには、販売額合計を50,000で割った後に小数部分を切り捨てます。割る前に切り捨てたり、1,000を掛けてから切り捨てたりすると、枚数に対応する金額になりません。
FAQ
Q: 販売回数が0回の会員は、最終販売日をどうしますか？
A: 販売データに該当する記録がないので、条件に従って空欄にします。列Dの式は、会員番号が空欄の場合や販売回数が0回の場合に、検索をせず空欄を返します。
A: 販売データに該当する記録がないので、条件に従って空欄にします。列Dの式は、会員番号が空欄の場合や販売回数が0回の場合に、検索をせず空欄を返します。
Q: 販売額合計が50,000円未満なら、クーポンは発行されませんか？
A: はい。50,000で割った結果の小数部分を切り捨てるため、販売額合計が50,000円未満なら枚数は0枚となり、クーポンの総額も0円です。
A: はい。50,000で割った結果の小数部分を切り捨てるため、販売額合計が50,000円未満なら枚数は0枚となり、クーポンの総額も0円です。
Q: 照合一致で下限を使うのはなぜですか？
A: 各ランクの基準は「0以上」「40,000以上」のように下限で表されています。対象の値以下で最も大きい下限を探せば、その値がどのランクに属するかを判定できます。
A: 各ランクの基準は「0以上」「40,000以上」のように下限で表されています。対象の値以下で最も大きい下限を探せば、その値がどのランクに属するかを判定できます。
関連キーワード: 表計算、照合検索、近似一致検索、二次元検索、切り捨て
設問2：
問題文を見る 新商品の見本の配布などの販売促進キャンペーンに活用するために、ワークシート“商品別管理”を作成し、マクロmanageMembersを格納した。マクロmanageMembers中のに入れる正しい答えを、解答群の中から選べ。
〔ワークシート：商品別管理〕
セルB1に商品の商品コードを入力して、マクロmanageMembersを実行すると、ワークシート“販売データ”及びワークシート“会員管理”に格納されているデータを基に、列Aの行3以降に当該商品を購入した会員の会員番号を、列Bの行3以降に会員番号ごとの当該商品の販売金額の合計（以下、商品販売額という）を、列Cの行3以降に会員番号ごとに最後に当該商品を販売した日付（以下、商品最終販売日という）を、それぞれ表示する。各行は同一会員に関する項目で構成し、商品販売額の降順に、商品販売額が等しいときは商品最終販売日の昇順に整列して表示する。ここで、マクロの実行開始時には、セルA3〜C9001に空値が格納されている。ワークシート“商品別管理”の例を、図4に示す。
〔マクロ：manageMembersの説明〕
このマクロは、9,001行3列の要素をもつ2次元配列sumTabを用意し、1以上会員数以下の全ての整数iに対して、sumTab[i,0]に会員番号を格納し、その会員番号に対応する商品販売額をsumTab[i,1]に、商品最終販売日をsumTab[i,2]に格納する。
(1) 1以上会員数＋1以下の全ての整数iに対して、sumTab[i,1]に初期値として0を格納する。
(2) ワークシート“販売データ”の行2以降のデータを上から順に参照し、商品コードがセルB1で指定した値と等しいとき、当該データの会員番号に対応した2次元配列sumTabの商品販売額の要素に販売金額を加算するとともに、商品最終販売日の要素に日付を格納する処理を繰り返す。
(3) 2次元配列sumTabの会員番号を格納した行を、商品販売額の降順に、商品販売額が等しいときは商品最終販売日の昇順に整列する。
(4) ワークシート“商品別管理”の行3以降のセルに、会員番号、商品販売額、商品最終販売日を、(3)の処理で整列された順に格納する。商品販売額が0の要素が現れたら、処理を終了する。
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：イ
解説
解答の導き方
1. 商品を買った会員の販売額を、正しい配列の行に加算します。
マクロは、ワークシート“販売データ”の行2以降を順に調べます。ループ変数のiが1のとき、基準セルの「相対(販売データ!A1、i、…)」が指すのは、実際の表の2行目です。列方向のずれが2なら列C、3なら列D、6なら列Gを指します。
そのため、商品コードを調べる「相対(販売データ!A1、i、3)」は列Dを、販売金額を読む「相対(販売データ!A1、i、6)」は列Gを指します。また、会員番号を読む「相対(販売データ!A1、i、2)」は列Cを指します。
該当する会員番号を照合一致で会員管理!A4:A9002から探すと、見つかった位置がindexに入ります。indexは会員管理の一覧における位置なので、商品販売額を加える配列の行にも使います。販売データを見ている行を表すiと、会員一覧での位置を表すindexは役割が異なります。
したがって、販売データの販売金額を、該当会員の配列要素に加算する処理は次の形です。
sumTab[index,1] ← sumTab[index,1] + 相対(販売データ!A1,i,6)
これはdの解答群のオです。商品コードが一致する販売データを見つけるたびに、同じ会員の販売金額がsumTab[index,1] に累積されます。
2. 商品を最後に販売した日付を記録します。
日付は列Bにあります。基準セルの「相対(販売データ!A1、i、1)」が列Bを指すので、その値をsumTab[index,2] に格納します。
販売データは販売日の昇順です。したがって、同じ会員が同じ商品を複数回買っている場合、後から調べる販売データの日付ほど新しい日付になります。見つけるたびに日付を上書きすれば、最後に残る値が商品最終販売日です。
3. 配列を、販売額の降順、同額なら日付の昇順に並べます。
設問では、商品販売額の多い順に並べ、販売額が等しい場合は商品最終販売日の早い順に並べるよう求めています。
この並び順を隣り合う二人の会員で考えます。前の会員の販売額が後の会員より小さければ、前後を入れ替えます。販売額が同じで、前の会員の日付が後の会員より遅ければ、やはり入れ替えます。
販売日を数値で比べると、遅い日付ほど値が大きくなります。そのため、入れ替え条件は次のとおりです。
論理和(sumTab[j,1] < sumTab[j + 1,1], 論理積(sumTab[j,1] = sumTab[j + 1,1], sumTab[j,2] > sumTab[j + 1,2]))
この条件に当てはまるとき、kを0から2まで動かして3つの要素を入れ替えます。つまり、会員番号・商品販売額・商品最終販売日をまとめて移動させ、同じ会員の情報がずれないようにしています。この条件はeの解答群のオです。
4. 並べ替え後の配列を、ワークシートに出力します。
sumTabの列番号は、0が会員番号、1が商品販売額、2が商品最終販売日です。一方、「相対(A2、i、j)」は、iが1のときワークシートの3行目を指し、jが0、1、2のとき列A、B、Cを指します。
したがって、配列の行iの3項目を同じ行のA列からC列に書き出す処理は、次の形です。
相対(A2,i,j) ← sumTab[i,j]
これはfの解答群のイです。商品販売額が0になった時点で出力を止めるのは、販売額の降順に並べているため、0の行以降には購入した会員がいないからです。
誤りやすいポイント
- 販売データの行を表すiと、会員一覧での位置を表すindexを混同しないようにします。販売金額を足す配列の行には、会員番号の照合結果であるindexを使います。
- 商品販売額を加算する値は列Gの販売金額です。列Eの単価や列Fの数量をそのまま加算するのではありません。
- 降順に並べるには、前の販売額が後の販売額より小さいときに入れ替えます。比較の向きを逆にすると、昇順になります。
- 販売額が同じ場合は、日付の値が大きい方、つまり後の日付が前にあるときに入れ替えます。
- 出力では、sumTabの列番号0、1、2をワークシートの列A、B、Cに対応させます。配列の添字とワークシートの行・列の位置は同じとは限りません。
FAQ
Q: なぜ販売データの行番号iではなくindexを使って販売額を加算するのですか？
A: iは販売データをどこまで読んだかを表します。indexは、該当する会員が会員一覧のどの位置にいるかを表します。販売額は会員ごとにまとめるため、配列の行にはindexを使います。
A: iは販売データをどこまで読んだかを表します。indexは、該当する会員が会員一覧のどの位置にいるかを表します。販売額は会員ごとにまとめるため、配列の行にはindexを使います。
Q: 販売額が同じとき、なぜ日付を「大きいか」で比べるのですか？
A: 日付の値は、日が後になるほど大きくなります。前の会員の日付が後の会員より大きい場合は順序が逆なので、入れ替えて早い日付を先にします。
A: 日付の値は、日が後になるほど大きくなります。前の会員の日付が後の会員より大きい場合は順序が逆なので、入れ替えて早い日付を先にします。
Q: 商品販売額が0になったら、なぜ出力を止めてよいのですか？
A: 販売額の降順に並べているので、購入した会員の正の販売額が先に並びます。0が現れた後に、正の販売額をもつ会員はありません。
A: 販売額の降順に並べているので、購入した会員の正の販売額が先に並びます。0が現れた後に、正の販売額をもつ会員はありません。
関連キーワード: 配列、照合、集計、バブルソート、論理演算、相対参照
