平成28年度春期基本情報技術者_表計算のマクロをExcelVBAで再現する

2018年12月31日 18:47

１．出典
２．問題で使用するExcelシート「販売分析」「販売データ」のダミーデータの簡単な作り方
３．問題文の要旨
４．問題文（疑似言語）
５．解答群（疑似言語）
６．問題文（VBA翻訳）
７．VBA翻訳の補足説明
８．解答群（VBA翻訳）
９．正解
10．正解を入れたVBA完全版 ※動作確認済み
11．２次元配列バージョン ※動作確認済み
12．バックナンバー
13．ExcelVBA講座ご紹介

１．出典

経済産業省国家試験（IPA情報処理推進機構）
平成28年度春期基本情報技術者試験午後試験選択問題問13表計算設問２[解答欄e～g]

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

２．問題で使用するExcelシート「販売分析」「販売データ」のダミーデータの簡単な作り方

シート「販売分析」

シート「販売データ」

・セルA2以降：=INT(RAND()*3+2013)
・セルD2以降：=INDEX(販売分析!$C$2:$K$2,1,INT(RAND()*9+1))
・セルF2、G2以降：=INT(RAND()*50+60)*10
・Rand()を使ってダミーのデータを入力しているので値の貼り付けをして数式を消しておくこと。
・A列、D列の昇順で並べ替える。
・B、C、E列はマクロとは無関係なので空白でも良い。

３．問題文の要旨

シート「販売データ」には販売システムから出力した過去3年間のデータが格納されている。年と商品データで昇順になっている。マクロを用いてA3, A8, A13に分析する3年の西暦年を出力し、3, 8, 13行目に売上高を千円未満四捨五入の千円単位で求めたい。

４．問題文（疑似言語）

このアルゴリズムでは、元の販売データの並び順（昇順）が違っていた場合、特に「D080」のデータの場所によっては無限ループになる可能性があります。ご注意ください。

[マクロ：Sales_Total]
〇マクロ：Sales_Total
〇数値型：data_row, total_row, total_col, subtotal
・data_row ← 0
・total_row ← 0
・subtotal ← 0
■ 相対(販売データ!A2, data_row, 0) <> null
｜・相対(A3, total_row, 0) ← 相対(販売データ!A2, data_row, 0)
｜・total_col ← 0
｜■ 相対(A3, total_row, 0) = 相対(販売データ!A2, data_row, 0)
｜｜■ [ 解答欄e ]
｜｜｜・[ 解答欄f ]
｜｜｜・data_row ← data_row + 1
｜｜■
｜｜・相対(C3, total_row, total_col) ← 四捨五入(subtotal / 1000, 0)
｜｜・subtotal ← 0
｜｜・total_col ← total_col + 1
｜■
｜・[ 解答欄g ]
■

５．解答群（疑似言語）

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

６．問題文（VBA翻訳）

Sub Sales_Total()
   Dim data_row As Integer, total_row As Integer, total_col As Integer, subtotal As Integer
   data_row = 0
   total_row = 0
   subtotal = 0
   Do While Sheets("販売データ").Range("A2").Offset(data_row, 0) <> ""
       Range("A3").Offset(total_row, 0) = Sheets("販売データ").Range("A2").Offset(data_row, 0)
       total_col = 0
       Do While Range("A3").Offset(total_row, 0) = Sheets("販売データ").Range("A2").Offset(data_row, 0)
           Do While [ 解答欄e ]
               [ 解答欄f ]
               data_row = data_row + 1
           Loop
           Range("C3").Offset(total_row, total_col) = WorksheetFunction.Round(subtotal / 1000, 0)
           subtotal = 0
           total_col = total_col + 1
       Loop
       [ 解答欄g ]
   Loop
End Sub

７．VBA翻訳の補足説明

VBAにもRound関数はありますが、通常はWorksheetFunction.Roundを使います。

８．解答群（VBA翻訳）

eに関する解答群

　ア　Range("C2").Offset(0, total_col) = Sheets("販売データ").Range("D2").Offset(data_row, 0)
　イ　Range("C2").Offset(0, total_col) <> Sheets("販売データ").Range("B2").Offset(data_row, 0)
　ウ　Range("C2").Offset(0, total_col) <> Sheets("販売データ").Range("D2").Offset(data_row, 0)
　エ　Range("C2").Offset(total_row, 0) = Sheets("販売データ").Range("B2").Offset(data_row, 0)
　オ　Range("C2").Offset(total_row, 0) = Sheets("販売データ").Range("D2").Offset(data_row, 0)
　カ　Range("C2").Offset(total_row, 0) <> Sheets("販売データ").Range("B2").Offset(data_row, 0)

f, gに関する解答群

　ア　subtotal = subtotal + Sheets("販売データ").Range("A1").Offset(data_row, 5) * Sheets("販売データ").Range("A1").Offset(data_row, 6)
　イ　subtotal = subtotal + Sheets("販売データ").Range("A2").Offset(total_row, 5) * Sheets("販売データ").Range("A2").Offset(total_row, 6)
　ウ　subtotal = subtotal + Sheets("販売データ").Range("F2").Offset(data_row, 0) * Sheets("販売データ").Range("G2").Offset(data_row, 0)
　エ　total_row = 0
　オ　total_row = total_row + 1
　カ　total_row = total_row + 5

９．正解

e　ア
商品コードの比較なので販売分析の2行目と販売データのD列の比較になる
f　ウ
total_rowは販売分析の行番号を表す変数であって、販売データの変数ではない
g　カ
4行おきに出力するのでtotal_rowを５加算する

10．正解を入れたVBA完全版 ※動作確認済み

Sub Sales_Total()
   Dim data_row As Integer, total_row As Integer, total_col As Integer, subtotal As Long
   data_row = 0
   total_row = 0
   subtotal = 0
   Do While Sheets("販売データ").Range("A2").Offset(data_row, 0) <> ""
       Range("A3").Offset(total_row, 0) = Sheets("販売データ").Range("A2").Offset(data_row, 0)
       total_col = 0
       Do While Range("A3").Offset(total_row, 0) = Sheets("販売データ").Range("A2").Offset(data_row, 0)
           Do While Range("C2").Offset(0, total_col) = Sheets("販売データ").Range("D2").Offset(data_row, 0)
               subtotal = subtotal + Sheets("販売データ").Range("F2").Offset(data_row, 0) * Sheets("販売データ").Range("G2").Offset(data_row, 0)
               data_row = data_row + 1
           Loop
           Range("C3").Offset(total_row, total_col) = WorksheetFunction.Round(subtotal / 1000, 0)
           subtotal = 0
           total_col = total_col + 1
       Loop
       total_row = total_row + 5
   Loop
End Sub

11．２次元配列バージョン ※動作確認済み

問題の趣旨（出題意図）を変えないまま、２次元配列に置き換えてみました。

Sub Sales_Total()
  a = Sheets("販売データ").Range("A:G")
  b = Range("C2:K2")
  data_row = 2
  total_row = 0
  subtotal = 0
  Do While a(data_row, 1) <> ""
      Range("A3").Offset(total_row, 0) = a(data_row, 1)
      total_col = 0
      Do While Range("A3").Offset(total_row, 0) = a(data_row, 1)
          Do While b(1, total_col + 1) = a(data_row, 4)
              subtotal = subtotal + a(data_row, 6) * a(data_row, 7)
              data_row = data_row + 1
          Loop
          subtotal = WorksheetFunction.Round(subtotal / 1000, 0)
          Range("C3").Offset(total_row, total_col) = subtotal
          subtotal = 0
          total_col = total_col + 1
      Loop
      total_row = total_row + 5
  Loop
End Sub

12．バックナンバー

13．ExcelVBA講座ご紹介

この記事が気に入ったらサポートをしてみませんか？