集計して別シートに集計結果を出す

質問

VBAのプロの方にお願いがあります。下記、内容で欄ごとに仕入書価格と申告金額を集計しその結果を別シートの10行目から表示させるにはどのようなプログラムを作成すればいいでしょうか。欄番号と輸入統計品目番号も合わせて表示したいです。またSMALLのグループの中では税率ごとで金額が一番大きい輸入統計品目番号を表示させることはできますでしょうか。どなたかお力をお貸しください。よろしくお願いいたします。

欄　大額・少額　輸入品目統計番号　　税率　仕入書価格　 申告金額
1　　　Large　　　　8708.70-0906　　　　　無税　　　5,659.00　　　¥777,320
2　　　Large　　　　8708.10-0006　　　　　無税　　　2,250.00　　　¥309,060
3　　　Small　　　　8708.94-0004　　　　　 無税　　　　854.50　　　¥117,374
3　　　Small　　　　8708.29-0006　　　　　 無税　　　　735.00　　　¥100,959
4　　　Small　　　　6211.43-2006　　　　　　9.1%　　　　420.00　　　　¥57,691
4　　　Small　　　　6204.62-2001　　　　　　9.1%　　　　194.40　　　　¥26,702
5　　　Small　　　　7318.15-0195　　　　　　2.8%　　　　142.62　　　　¥19,590

回答No.7

CC_T · ベストアンサー

> 税率も表示させたいのですがどうすればいいでしょうか。
書き出し部分を変更します。
税率は、配列argの４列目、つまりarg(x,4）に格納しています。
ただし、大小比較のために無税は０（ゼロ）値として格納していますので、「無税」表示とするなら書き出しの時に戻す必要があります。

～～～
　　While　arg(aln,　1)　>　0
　　　　Cells(30　+　aln,　1)　=　arg(aln,　1)
　　　　Cells(30　+　aln,　2)　=　arg(aln,　3)
　　　　If　（arg(aln,　4)＝0）　then　　　　　　　'//ここから税率の書き出し
　　　　　Cells(30　+　aln,　3)　=　"無税"　　　'　値が0％の時に無税に変換する。
　　　　Else
　　　　　Cells(30　+　aln,　3)　=　arg(aln,　4)　　'　税率数値を書き出す。
　　　　End If　　　　　　　　　　　　　　　　　　　　'ここまで//　　
　　　　Cells(30　+　aln,　4)　=　arg(aln,　5)
　　　　Cells(30　+　aln,　5)　=　arg(aln,　6)
　　　　aln　=　aln　+　1
　　Wend
～～～
なお、
　Cells(30　+　aln,　□)
の□部分は、回答4の通り、結合セルの先頭列にあわせて変更ください。

お礼

今回のVBAのプログラムを拝見しまして非常に参考になりました。まだまだ、勉強不足のようです。大変ありがとうございました！

回答No.6

CC_T

おまけ。先のマクロの実行結果です。

回答No.5

CC_T

>　教えて頂いた通りに記述しましたがRows("31:60").ClearCont683005ents
>　の箇所でデバックがでます。何故か分かりません。どこが間違っているのでしょうか。

ClearContentsの命令の途中に「683005」が混じってますが、コピペミスでしょうか？(^^;
Rows("31:60").ClearContents
です。

こちらではエラー出ずに動いて処理できてます。
おっと、品目を高税率の方に書き換えできてないですね。…訂正しときます。

～～～～～Sub　Macro1()
'
'変数、行列を宣言する。
　　Dim　Lin　As　Integer,　Clm　As　Integer,　arg(1000,　10)　As　Variant
　　Dim　aln　As　Integer,　i　As　Integer

'初期値設定
　　Lin　=　11　'タイトル行を除くデータ先頭行を入れる
　　aln　=　1

'1行目データを配列に読み込み
　　For　i　=　1　To　3
　　　　arg(aln,　i)　=　Cells(Lin,　i)
　　Next　i

　　If　Cells(Lin,　4)　=　"無税"　Then　　'　変数型を数値型とするため、ゼロ値で取り込む
　　　　arg(aln,　4)　=　0
　　Else
　　　　arg(aln,　4)　=　Cells(Lin,　4)
　　End　If

　　For　i　=　5　To　6
　　　　arg(aln,　i)　=　Cells(Lin,　i)
　　Next　i

'次行の１列目が空白でない限り処理を継続する指示。

　　Do
　　　　Lin　=　Lin　+　1　'処理行を次行に

'欄の値が配列の「欄」の値と同じか判定する。
　　　　If　(arg(aln,　1)　=　Cells(Lin,　1))　Then

'　→同値ならば、金額を配列値と足しあわせる
　　　　　　arg(aln,　5)　=　arg(aln,　5)　+　Cells(Lin,　5)
　　　　　　arg(aln,　6)　=　arg(aln,　6)　+　Cells(Lin,　6)

'　　配列の税率とデータの税率を比較し、高率の品目と税率で配列を上書きする。
　　　　　　If　Not　(Cells(Lin,　4)　=　"無税")　Then
　　　　　　　　If　arg(aln,　4)　<　Cells(Lin,　4)　Then
　　　　　　　　　　arg(aln,　4)　=　Cells(Lin,　4)
　　　　　　　　　　arg(aln,　3)　=　Cells(Lin,　3)
　　　　　　　　End　If
　　　　　　End　If

'　→異値ならば、新たなデータとして行列の次行に読み込む。
　　　　　　Else
　　　　　　　　aln　=　aln　+　1

　　　　　　　　For　i　=　1　To　3
　　　　　　　　　　arg(aln,　i)　=　Cells(Lin,　i)
　　　　　　　　Next　i

　　　　　　　　If　Cells(Lin,　4)　=　"無税"　Then
　　　　　　　　　　arg(aln,　4)　=　0
　　　　　　　　Else
　　　　　　　　　　arg(aln,　4)　=　Cells(Lin,　4)
　　　　　　　　End　If

　　　　　　　　For　i　=　5　To　6
　　　　　　　　　　arg(aln,　i)　=　Cells(Lin,　i)
　　　　　　　　Next　i
　　　　End　If

　　Loop　While　Cells(Lin,　1)　>　0

'「別のシート」の情報を一旦クリア（しとかないと、下に前処理時のゴミが残っちゃうことがある）
　　Sheets("別シート").Select　　　　'★シート名は実情に合わせて指定下さい。
　　Rows("31:100").ClearContents　　'★実情に合わせて範囲指定してください。

'統計結果の書き込み
　　aln　=　1

　　While　arg(aln,　1)　>　0
　　　　Cells(30　+　aln,　1)　=　arg(aln,　1)
　　　　Cells(30　+　aln,　2)　=　arg(aln,　3)
　　　　Cells(30　+　aln,　4)　=　arg(aln,　5)
　　　　Cells(30　+　aln,　5)　=　arg(aln,　6)
　　　　aln　=　aln　+　1
　　Wend

End　Sub

補足

いろいろとご教示いただきありがとうございます。もう一つお願いがあります。税率も表示させたいのですがどうすればいいでしょうか。添付のエクセルの表には税率が表記されていません。恐れ入りますがこちらのほうもご教示頂ければありがたいです。

回答No.4

CC_T

マクロ中にコメント入れておきましたが、どうも意図が伝わっていないようですね。
ご確認ください。

> 元データはA1からではなくA10から始まりますので
　Lin = 2 'タイトル行を除くデータ先頭行を入れる
の数字２を10（11か？）に変更して下さい。

> データのあるシートでマクロを実行するとデータが削除され
？それは変です。先のマクロの★印コメント行の部分をそちらのシートに合わせて編集しましたか？
　Sheets("別シート").Select　　'★シート名は実情に合わせて指定下さい。
　Rows("10:100").ClearContents　'★実情に合わせて範囲指定してください。
この２行で「別シート」を呼び出して10行目～100行をクリアする処理をしていますので、「別シート」の部分は目的のシート名に変える事が必要です。
また、A31からスタートするのであれば、
　Rows("31:100").ClearContents
などとする変更が必要です。（これで31行から100行までをクリアしています）
更に範囲指定矩形に変えたいなら、マクロの自動記録で範囲指定の仕方を確認してみてください。

> 別シートで集計結果を貼り付ける開始位置ですがこちらもA31からスタート
> 30行目にはNo（欄番号）、～　の見出しが記載
> No以外のタイトルはセルが結合されています。
最後の部分、
　Cells(10 + aln, 1) = arg(aln, 1)
　　～
　Cells(10 + aln, 5) = arg(aln, 6)
の部分を変更してください。
　Cells(30 + aln, 1) = arg(aln, 1)
　Cells(30 + aln, x) = arg(aln, 3)
　Cells(30 + aln, x) = arg(aln, 5)
　Cells(30 + aln, x) = arg(aln, 6)
って感じですね。この「x」の部分に、結合セルの先頭列を指定してください。
例えば統計品目番部分がC列セルからE列セルを結合したものならば、２行目は
　Cells(30 + aln, 3) = arg(aln, 3)
というふうにC列を示す３を入れる（列A,B,C…を1,2,3…の値に置き換える）。

あと分からなければ、マクロをステップ実行（F8)して処理がどう進んでいるのか確認してみることです。
一度配列に取り込みながら合算して、それを最後に書き出しているだけですから。

補足

教えて頂いた通りに記述しましたがRows("31:60").ClearCont683005ents '★実情に合わせて範囲指定してください。の箇所でデバックがでます。何故か分かりません。どこが間違っているのでしょうか。

回答No.3

CC_T

私ゃ「プロ」じゃないのであまりスマートでないでしょうが、２で回答したものを書き出せるマクロを書いておきます。
コメントも入れておきましたので状況に合わせて編集下さい。
特に、末部分のシート名とクリアの範囲は編集が要るはずですね。

注意
・データのあるシートでマクロ実行してください。
・データはA1セルから連続で入力されていて、「欄」の値でソートされているという前提で作成してあります。
・書き出すシート名称を「別シート」としてあります。

～～～～～

Sub Macro1()
'
'変数、行列を宣言する。
Dim Lin As Integer, Clm As Integer, arg(1000, 10) As Variant
Dim aln As Integer, i As Integer

'初期値設定
Lin = 2 'タイトル行を除くデータ先頭行を入れる
aln = 1
'1行目データを配列に読み込み
For i = 1 To 3
arg(aln, i) = Cells(Lin, i)
Next i
If Cells(Lin, 4) = "無税" Then　' 変数型を数値型とするため、ゼロ値で取り込む
arg(aln, 4) = 0
Else
arg(aln, 4) = Cells(Lin, 4)
End If
For i = 5 To 6
arg(aln, i) = Cells(Lin, i)
Next i

'次行の１列目が空白でない限り処理を継続する指示。
Do
Lin = Lin + 1 '処理行を次行に
'欄の値が配列の「欄」の値と同じか判定する。
If (arg(aln, 1) = Cells(Lin, 1)) Then
'　→同値ならば、金額を配列値と足しあわせる
arg(aln, 5) = arg(aln, 5) + Cells(Lin, 5)
arg(aln, 6) = arg(aln, 6) + Cells(Lin, 6)
'　　配列の税率とデータの税率を比較し、高率の品目と税率で配列を上書きする。
If Not (Cells(Lin, 4) = "無税") Then
arg(aln, 4) = Application.Max(arg(aln, 4), Cells(Lin, 4))
End If
'　→異値ならば、新たなデータとして行列の次行に読み込む。
Else
aln = aln + 1
For i = 1 To 3
arg(aln, i) = Cells(Lin, i)
Next i
If Cells(Lin, 4) = "無税" Then
arg(aln, 4) = 0
Else
arg(aln, 4) = Cells(Lin, 4)
End If
For i = 5 To 6
arg(aln, i) = Cells(Lin, i)
Next i
End If
Loop While Cells(Lin, 1) > 0

'「別のシート」を一旦クリアし
Sheets("別シート").Select　　'★シート名は実情に合わせて指定下さい。
Rows("10:100").ClearContents　'★実情に合わせて範囲指定してください。

'統計結果の書き込み
Cells(10, 1) = "欄"
Cells(10, 2) = "輸入統計品目番号"
Cells(10, 3) = "原産地"
Cells(10, 4) = "仕入書価格"
Cells(10, 5) = "申告価格"
aln = 1
While arg(aln, 1) > 0
Cells(10 + aln, 1) = arg(aln, 1)
Cells(10 + aln, 2) = arg(aln, 3)
Cells(10 + aln, 4) = arg(aln, 5)
Cells(10 + aln, 5) = arg(aln, 6)
aln = aln + 1
Wend

End Sub

補足

作成していただいたプログラムを実行しましたがなかなかうまくいきません。元データはA1からではなくA10から始まりますので訂正します。また、データのあるシートでマクロを実行するとデータが削除されてしまします。このデータは削除されずにそのまま生かしたいです。別シートで集計結果を貼り付ける開始位置ですがこちらもA31からスタートになります。あらかじめ30行目にはNo（欄番号）、輸入統計品目番号、大額・少額、原産地、仕入書価格、申告金額の見出しが記載されています。No以外のタイトルはセルが結合されています。

回答No.2

CC_T

上から下へ、１行ずつ処理して行くとして、どのような場合にどう判断するか、という条件を考えていくのが筋です。

・欄ごとに仕入書価格と申告金額を集計
→足し算するだけ。問題なし。

・別シートの10行目から表示させる
→書き出し先のシートを指定すれば良い。問題なし。
　マクロの自動記述で自分の操作を記録してみればわかる事です。

・欄番号と輸入統計品目番号も合わせて表示したい
→１対１の関係にあるのなら問題なし。1対多数がある場合、金額の合計との整合をどうとるのか指定が必要。

・SMALLのグループの中では税率ごとで金額が一番大きい輸入統計品目番号を表示
→例えばデータの欄３の分はどちらも「無税」であるが、１行にまとめて金額は合計、品目番号は末尾04の方を書く？
　同額の場合は無いのですね？

・欄が同じでSmallとLARGEが混在する事は皆無なのか。

・「別シートの10行目」からどのようなアウトプットを想定しているのか、書いて下さい。
欄　輸入統計品目番号　原産地　仕入書価格計　申告価格計
1 8708.70-0906 　　　　　　　5,659.00　　　　　¥777,320
2 8708.10-0006 　　　　　　　2,250.00　　　　　¥309,060
3 8708.94-0004 　　　　　　　1589.5　　　　　￥218,333
4 6211.43-2006 　　　　　　　 614.4　　　　　　￥84,393
5 7318.15-0195 　　　　　　　 142.62　　　 　　　¥19,590
となればいいのでしょうか？

補足

すいません。Rows("31:60").ClearCont683005entsに余計な数字が入っていたのでこちらは間違っていましたが、この行を無効にしマクロを実行したところ輸入統計品目番号と仕入書価格の箇所には張り付かず、税率が合計され、原産地の箇所は同じ文字が無数に張り付きました。すいませんがもう少しご教示をお願いします。

回答No.1

CC_T

質問意図が不明確で回答できません。
欄毎に仕入書価格＋申告金額を別シート10行目から順に表示すればいいのでしょうか？

データ側はわかりましたから、これが別シートでどういう表示になって欲しいかを書いて、捕捉されると良いと思います。

補足

内容が不十分ですいませんでした。別シートのA列10行目から順に欄、輸入統計品目番号、原産地、仕入書価格、申告価格の順番で集計結果を表示したいです。