わえなび ワード&エクセル問題集 waenavi

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

2019春期基本情報技術者試験の表計算をExcel&VBAで完全再現してみた!【表計算マクロ】


2019年4月21日(日)に、平成最後の情報処理技術者試験(国家試験)が実施されました。

基本情報技術者試験では毎回、表計算の問題が出題されますが、今回は、データ、判定基準、分析表をそれぞれ別のシートに作成するという基本を押さえた問題で、珍しく、近年にない良問が出題されました。

さらに、今回初めて「2次元配列」が出題されました。ExcelVBAで2次元配列は必須ですから当然の出題と言えます。リスト形式の表に、INDEX、MATCH、LOOKUPのような基本的な関数、そして、入れ替えのアルゴリズムを含むマクロと、非常にシンプルな問題で、Excelを理解できている人であれば楽勝だったのではないでしょうか?

このような関数やマクロであれば一般事務の人でも使いこなす人はいますので、いくらプログラム未経験でも、この程度の簡単な問題が解けなかったら「情報処理技術者」を名乗る資格は無いと思います。そこで、今回の問題をExcelで再現する方法を紹介しますので、皆さんも一度作ってみてはいかがでしょうか?

f:id:waenavi:20190422033426j:plain

 

目次

1.出典

試験問題及び正解の著作権はIPA情報処理推進機構に帰属します。

経済産業省国家試験(IPA情報処理推進機構)
平成31年度春期・基本情報技術者試験・午後試験選択問13表計算
https://www.jitec.ipa.go.jp/1_04hanni_sukiru/mondai_kaitou_2019h31.html#31haru

 

2.前半の表計算部分の問題文の要旨

小売販売のZ店では、会員登録をしている顧客の2018/10/1~2019/3/31の販売データを基に販促キャンペーンを行うことになりました。

  • 「販売データ」シートには、販売データが販売日の昇順で格納されています。
  • 「会員管理」シートには、販促キャンペーンのための情報を格納します。なお、E列の会員クラスは「分類表」シートの基準に基づいて分類します。また、F列のクーポンは、販売額合計の50000円ごとに1000円クーポンを1枚発行します。

 

3.問題で使用するExcelシートの簡単な作り方

(1)「販売データ」シート

問題では3333件となっていますが、サンプルとして33件にしています。これら33件を入力すれば、今回の問題はほぼ再現できます。

A列は、1回の販売ごとに発行するレシートにつけられる一意の番号です。1回の販売で複数の商品を販売したら複数行になります。したがって、同じレシート番号が続くこともあります。

H列の上4桁は会員番号、下3桁はその会員に対する販売回数(販売した商品の数ではなくレシートの枚数のことです)を連番で表します。ただし、前の行と同じレシート番号の場合は0にします。

  • A列:直接入力、5行目を90330986にしてオートフィル(連番)
  • B列:「yyyy-mm-dd」の表示形式にする。6行目を「2019-02-24」にしてオートフィル(連番)
  • C~F列:下の表のとおりに直接入力(ほとんどオートフィル)
  • G列:=E2*F2
  • H列:=(C2*1000+COUNTIFS(C$1:C1,C2,H$1:H1,"<>0")+1)*(A1<>A2)

f:id:waenavi:20190422021041j:plain

 

(2)「分類表」シート

これは入力するだけです。例えば、販売金額の合計が80000円以上で、販売回数が6回以上であればSランクとなります。

f:id:waenavi:20190422022117j:plain

 

(3)「会員管理」シート

会員番号は1001~最大9999ですが、問題では1531番まで発行されています。

  • A列:連番1001~1531
  • B列:=IF(A4="","",SUMIF(販売データ!C$2:C$10016,A4,販売データ!G$2:G$10016))
  • C列:=IF(A4="","",COUNTIFS(販売データ!C$2:C$10016,A4,販売データ!H$2:H$10016,"<>0"))
  • D列:=IF(OR(A4="",C4=0),"",INDEX(販売データ!B$2:B$1000,MATCH(A4*1000+C4,販売データ!H$2:H$1000,0)))
  • E列:=IF(A4="","",INDEX(分類表!D$4:F$6,MATCH(C4,分類表!C$4:C$6,1),MATCH(B4,分類表!D$3:F$3,1)))
  • F列:=IF(A4="","",ROUNDDOWN(B4/50000,0)*1000)

f:id:waenavi:20190422022248j:plain

 

(4)「商品別管理」シート

「商品別管理」シートのB1に商品コードを入力して、マクロ「manageMembers」を実行すると、3行目以降に結果が出力されます。

f:id:waenavi:20190422022542j:plain

 

4.表計算部分の問題と答え

(1)問題

「会員管理」シートのD4~F4に入力する式を答える問題です。INDEX、LOOKUP、MATCH、ROUNDDOWNというExcel2級レベルの簡単な問題です。

  • D4=IF(論理和(A4=null,C4=0),null,[ 解答欄 a ])
  • E4=IF(A4=null,null,表引き(分類表!D$4:F$6,[ 解答欄 b ]))
  • F4=IF(A4=null,null,[ 解答欄 c ])

(2)解答群

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$4:F$6,1),照合一致(C4,分類表!C$4:C$6,1)
  • ウ 照合一致(C4,分類表!C$4:C$6,1),照合一致(B4,分類表!D$3:F$3,1)
  • エ 照合一致(C4,分類表!D$4:F$6,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)

(3)正解

a-カ、b-ウ、c-ウ

(4)解説

aについて

C列の会員番号を探すだけでは「最終販売」を特定することができません。上4桁は会員番号、下3桁はその会員に対する販売回数なので、会員番号*1000+販売回数としてH列を探せば「最終販売」の行を特定することができます。販売日はB列にあるので「照合検索(会員番号*1000+販売回数,H列,B列)」となります。なお、Excelで再現する場合、LOOKUP関数は完全一致ができないので、INDEXとMATCHを使います(上記)。

bについて

表引き(INDEX)の引数は、INDEX(配列,行番号,列番号)です。問題冊子の最後に書いてあるので覚える必要はありません。販売回数(C列)と販売額合計(B列)でランクを決定するので「照合一致(C4,縦,1),照合一致(B4,横,1)」となります。

cについて

クーポン枚数*1000という形になるのは明らかなので、アかウに絞られます。販売合計が6万円の場合、1000円クーポン1枚なので、「60000」で計算してみたら分かります。

  • ア ROUNDDOWN(60000,-4)/50000*1000 = 1200
  • イ ROUNDDOWN(60000*1000,-8)/50000 = 0
  • ウ ROUNDDOWN(60000/50000,0)*1000 = 1000
  • エ ROUNDDOWN(60000/50000*1000,0) = 1200
  • オ ROUNDDOWN(60000/50000*1000,-4) = 0

 

5.マクロの概要

「商品別管理」シートのセルB1に商品コードを入力して、マクロ「manageMembers」を実行すると、二次元配列sumTab[9001,3]を用意して、i=1~会員数(会員管理!E1)に対して次のデータを格納します。

  • sumTab[i,0]…会員番号(連番)
  • sumTab[i,1]…その商品の販売額
  • sumTab[i,2]…その商品の最終販売日

初期値としてsumTab[1~会員数+1,1]に0を格納します。販売データのシートを2行目から順に参照して、会員番号ごとに配列に販売額と販売日を格納していきます。配列を、商品販売額の降順、最終販売日の昇順に並べ替えて、「商品別管理」シートの3行目以降に1行ずつ出力していきますが、商品販売額が0になったら、出力処理を終了します。

f:id:waenavi:20190422025006j:plain

 

6.マクロ(疑似言語)

[マクロ:manageMembers]
〇マクロ:manageMembers
〇数値型:i, j, k, memNo, index, temp, sumTab[9001,3]
■ i: i, i<=会員管理!E1, 1
|・sumTab[i, 0] ← 相対 (会員管理!A3, i, 0)
|・sumTab[i, 1] ← 0
■
・sumTab[会員管理!E1 + 1, 1] ← 0
■ i: 1, i<= 会員管理!B1, 1
|▲ 相対(販売データ!A1, i, 3) = B1
||・memNo ← 相対(販売データ!A1, i, 2)
||・index ← 照合一致(memNo, 会員管理!A4:A9002, 0)
||・[ 解答欄 d ]
||・sumTab[index, 2] ← 相対(販売データ!A1, i, 1)
|▼
■
■ i: 1, i<= 会員管理!E1, 1
|■ j: 1, j<= 会員管理!E1 - i, 1
||▲ [ 解答欄 e ]
|||■ k: 0, k<=2, 1
||||・temp ← sumTab[j, k]
||||・sumTab[j, k] ← sumTab[j + 1, k]
||||・sumTab[j + 1, k] ← temp
|||■
||▼
|■
■
・i ← 1
■ sumTab[i, 1] > 0
|■ j: 0, j<=2, 1
||・[ 解答欄 f ]
|■
|・i ← i + 1
■

 

7.マクロの解答群(疑似言語)

dに関する解答群

ア subTab[1, index] ← subTab[1, index] + 相対(販売データ!A1, i ,6)
イ subTab[1, index] ← subTab[1, index] + 相対(販売データ!A1, index ,6)
ウ subTab[i, 1] ← subTab[i, 1] + 相対(販売データ!A1, i ,6)
エ subTab[i, 1] ← subTab[i, 1] + 相対(販売データ!A1, index ,6)
オ subTab[index, 1] ← subTab[index, 1] + 相対(販売データ!A1, i ,6)
カ subTab[index, 1] ← subTab[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]

 

8.マクロの解答

(1)正解

d-オ、e-オ、f-イ

(2)解説

マクロについて

  • 1つ目のループ:配列の0列目に会員番号を連番で、1列目に0を代入しています。
  • 2つ目のループ:会員番号memNoと行数indexを求めて、1列目に販売額を加算、2列目に日付を入れます。
  • 3つ目のループ:「tmp=X」「X=Y」「Y=tmp」は入れ替えの基本パターンです。sumTab[j, k] と sumTab[j + 1, k]を入れ替えます。
  • 4つ目のループ:2次元配列の内容をシートに転記しています。

dについて

次の行でsumTab[index, 2]に日付を代入していることから、sumTab[index, 1]を求めようとしていることは明らかです。この時点でオとカに絞られます。sumTab[index, 1]は販売額の合計ですが、合計を求めるには「SUM=SUM+X」の形です。そして、配列の行数(会員531人)が「index」で、販売データの行数(件数3333件)が「i」であることを考えれば、subTabの第1次元が「index」、販売データの相対の行数が「i」になることがわかります。

eについて

なぜ入れ替えるかというと、「商品販売額の降順、最終販売日の昇順」に整列するためです。そうなっていなければ入れ替えます。つまり、「商品販売額が次の行(j+1)より小さい場合」OR「販売額が同じ場合 AND 販売日が次の行より大きい場合」となります。

fについて

j=0~2(列を表している)なので、2次元配列sumTab[i, j]をシートに転記しているだけです。1列目~3列目に転記するので相対の列番号は0~2(j)です。

 

9.VBA翻訳に関する補足説明

  1. 変数indexは予約語なので、「ind」にしています(以下同じ)。
  2. 疑似言語の[9001,3]はVBAでは(9000,2)となります。
  3. 問題文によると日付はシリアル値で扱うとのことなので、データ型はLong型にしています。
  4. 標準モジュールではなく、商品別管理シートに格納して実行してください(問題文の指示です)。

 

10.問題文(VBA翻訳)

Sub manageMembers()
    Dim i As Long, j As Long, k As Long, memNo As Long, ind As Long, sumTab(9000, 2) As Long
    For i = 1 To Sheets("会員管理").Range("E1")
        sumTab(i, 0) = Sheets("会員管理").Range("A3").Offset(i, 0)
        sumTab(i, 1) = 0
    Next
    sumTab(Sheets("会員管理").Range("E1"), 1) = 0
    For i = 1 To Sheets("会員管理").Range("B1")
        If Sheets("販売データ").Range("A1").Offset(i, 3) = Range("B1") Then
            memNo = Sheets("販売データ").Range("A1").Offset(i, 2)
            ind = WorksheetFunction.Match(memNo, Sheets("会員管理").Range("A4:A9002"), 0)
            [ 解答欄 d ]
            sumTab(ind, 2) = Sheets("販売データ").Range("A1").Offset(i, 1)
        End If
    Next
    For i = 1 To Sheets("会員管理").Range("E1")
        For j = 1 To Sheets("会員管理").Range("E1") - i
            If [ 解答欄 e ] Then
                For k = 0 To 2
                    temp = sumTab(j, k)
                    sumTab(j, k) = sumTab(j + 1, k)
                    sumTab(j + 1, k) = temp
                Next
            End If
        Next
    Next
    i = 1
    Do While sumTab(i, 1) > 0
        For j = 0 To 2
            [ 解答欄 f ]
        Next
        i = i + 1
    Loop
End Sub

 

11.解答群(VBA翻訳)

dに関する解答群

ア sumTab(1, ind) = sumTab(1, ind) + Sheets("販売データ").Range("A1").Offset(i, 6)
イ sumTab(1, ind) = sumTab(1, ind) + Sheets("販売データ").Range("A1").Offset(ind, 6)
ウ sumTab(i, 1) = sumTab(i, 1) + Sheets("販売データ").Range("A1").Offset(i, 6)
エ sumTab(i, 1) = sumTab(i, 1) + Sheets("販売データ").Range("A1").Offset(ind, 6)
オ sumTab(ind, 1) = sumTab(ind, 1) + Sheets("販売データ").Range("A1").Offset(i, 6)
カ sumTab(ind, 1) = sumTab(ind, 1) + Sheets("販売データ").Range("A1").Offset(ind, 6)

 

eに関する解答群

ア sumTab(j, 1) < sumTab(j + 1, 1) And (sumTab(j, 1) = sumTab(j + 1, 1) Or sumTab(j, 2) > sumTab(j + 1, 2))
イ sumTab(j, 1) > sumTab(j + 1, 1) And sumTab(j, 2) > sumTab(j + 1, 2)
ウ sumTab(j, 1) > sumTab(j + 1, 1) And (sumTab(j, 1) = sumTab(j + 1, 1) Or sumTab(j, 2) < sumTab(j + 1, 2))
エ sumTab(j, 1) < sumTab(j + 1, 1) Or sumTab(j, 2) > sumTab(j + 1, 2)
オ sumTab(j, 1) < sumTab(j + 1, 1) Or (sumTab(j, 1) = sumTab(j + 1, 1) And sumTab(j, 2) > sumTab(j + 1, 2))
カ sumTab(j, 1) > sumTab(j + 1, 1) Or (sumTab(j, 1) = sumTab(j + 1, 1) And sumTab(j, 2) < sumTab(j + 1, 2))

 

fに関する解答群

ア Range("A2").Offset(i, j) = sumTab(i, 1)
イ Range("A2").Offset(i, j) = sumTab(i, j)
ウ Range("A2").Offset(i, j + 1) = sumTab(i, j)
エ Range("A2").Offset(j, i) = sumTab(j, 1)
オ Range("A2").Offset(j, i) = sumTab(j, i)
カ Range("A2").Offset(j + 1, i) = sumTab(j, 1)

 

12.正解を入れた完全版(VBA翻訳)

Sub manageMembers()
    Dim i As Long, j As Long, k As Long, memNo As Long, ind As Long, sumTab(9000, 2) As Long
    For i = 1 To Sheets("会員管理").Range("E1")
        sumTab(i, 0) = Sheets("会員管理").Range("A3").Offset(i, 0)
        sumTab(i, 1) = 0
    Next
    sumTab(Sheets("会員管理").Range("E1"), 1) = 0
    For i = 1 To Sheets("会員管理").Range("B1")
        If Sheets("販売データ").Range("A1").Offset(i, 3) = Range("B1") Then
            memNo = Sheets("販売データ").Range("A1").Offset(i, 2)
            ind = WorksheetFunction.Match(memNo, Sheets("会員管理").Range("A4:A9002"), 0)
            sumTab(ind, 1) = sumTab(ind, 1) + Sheets("販売データ").Range("A1").Offset(i, 6)
            sumTab(ind, 2) = Sheets("販売データ").Range("A1").Offset(i, 1)
        End If
    Next
    For i = 1 To Sheets("会員管理").Range("E1")
        For j = 1 To Sheets("会員管理").Range("E1") - i
            If sumTab(j, 1) < sumTab(j + 1, 1) Or (sumTab(j, 1) = sumTab(j + 1, 1) And sumTab(j, 2) > sumTab(j + 1, 2)) Then
                For k = 0 To 2
                    temp = sumTab(j, k)
                    sumTab(j, k) = sumTab(j + 1, k)
                    sumTab(j + 1, k) = temp
                Next
            End If
        Next
    Next
    i = 1
    Do While sumTab(i, 1) > 0
        For j = 0 To 2
            Range("A2").Offset(i, j) = sumTab(i, j)
        Next
        i = i + 1
    Loop
End Sub

 

13.結論:わえなび的な講評

平成28年から30年にかけて、複雑な表と不可解なアルゴリズムの問題が連続して出題されていました。

 

今回は、その反省をしたのか(?)改善されてびっくりしました。めでたし、めでたし。これで、VBA経験者が有利になって良い傾向です!!!そして、2次元配列が出題されたのも評価できます。2次元配列が無かったら表計算とは言えませんから、次回以降も続けてほしいです。

問題の説明文も、リスト形式の表とそれぞれの列(フィールド)の説明だけという感じで、問題文をほとんど読まなくても解けるくらいシンプルになりました。非常に良いと思います。

 

欲を言えば、表計算の部分を減らして、コードの修正や意図を問う問題を増やすともっと良いと思います。例えば、sumTab[会員管理!E1 + 1, 1]に0を代入している理由とか、入れ替えの3重ループを丸ごと記述式の解答欄にするとか・・・

最近は小学生でもプログラミングを習う時代ですから、この程度のアルゴリズムが分からない人に合格証書を渡す必要はありません。次々回の2020年春試験からアルゴリズムと表計算の配点が引き上げられますので、今後も、新時代「令和」にふさわしい表計算の良問を期待しています。

 


過去の問題の完全再現(VBAのみ)はこちら。

note.com

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ