平成28年度春期基本情報技術者_表計算のマクロをExcelVBAで再現する
目次
1.出典
2.問題で使用するExcelシート「販売分析」「販売データ」のダミーデータの簡単な作り方
3.問題文の要旨
4.問題文(疑似言語)
5.解答群(疑似言語)
6.問題文(VBA翻訳)
7.VBA翻訳の補足説明
8.解答群(VBA翻訳)
9.正解
10.正解を入れたVBA完全版 ※動作確認済み
11.2次元配列バージョン ※動作確認済み
12.バックナンバー
13.ExcelVBA講座ご紹介
1.出典
経済産業省国家試験(IPA情報処理推進機構)
平成28年度春期 基本情報技術者試験 午後試験 選択問題 問13表計算設問2[解答欄e~g]
なお、試験問題及び正解の著作権はIPA情報処理推進機構に帰属します。
2.問題で使用する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.問題文の要旨
シート「販売データ」には販売システムから出力した過去3年間のデータが格納されている。年と商品データで昇順になっている。マクロを用いてA3, A8, A13に分析する3年の西暦年を出力し、3, 8, 13行目に売上高を千円未満四捨五入の千円単位で求めたい。
4.問題文(疑似言語)
このアルゴリズムでは、元の販売データの並び順(昇順)が違っていた場合、特に「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 ]
■
5.解答群(疑似言語)
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
6.問題文(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
7.VBA翻訳の補足説明
VBAにもRound関数はありますが、通常はWorksheetFunction.Roundを使います。
8.解答群(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
9.正解
e ア
商品コードの比較なので販売分析の2行目と販売データのD列の比較になる
f ウ
total_rowは販売分析の行番号を表す変数であって、販売データの変数ではない
g カ
4行おきに出力するのでtotal_rowを5加算する
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.2次元配列バージョン ※動作確認済み
問題の趣旨(出題意図)を変えないまま、2次元配列に置き換えてみました。
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講座ご紹介
この記事が気に入ったらサポートをしてみませんか?