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.2
- 回答日時:
上から下へ、1行ずつ処理して行くとして、どのような場合にどう判断するか、という条件を考えていくのが筋です。
・欄ごとに仕入書価格と申告金額を集計
→足し算するだけ。問題なし。
・別シートの10行目から表示させる
→書き出し先のシートを指定すれば良い。問題なし。
マクロの自動記述で自分の操作を記録してみればわかる事です。
・欄番号と輸入統計品目番号も合わせて表示したい
→1対1の関係にあるのなら問題なし。1対多数がある場合、金額の合計との整合をどうとるのか指定が必要。
・SMALLのグループの中では税率ごとで金額が一番大きい輸入統計品目番号を表示
→例えばデータの欄3の分はどちらも「無税」であるが、1行にまとめて金額は合計、品目番号は末尾04の方を書く?
同額の場合は無いのですね?
・欄が同じでSmallとLARGEが混在する事は皆無なのか。
・「別シートの10行目」からどのようなアウトプットを想定しているのか、書いて下さい。
欄 輸入統計品目番号 原産地 仕入書価格計 申告価格計
18708.70-0906 5,659.00 ¥777,320
28708.10-0006 2,250.00 ¥309,060
38708.94-0004 1589.5 ¥218,333
46211.43-2006 614.4 ¥84,393
57318.15-0195 142.62 ¥19,590
となればいいのでしょうか?
この回答への補足
すいません。Rows("31:60").ClearCont683005entsに余計な数字が入っていたのでこちらは間違っていましたが、この行を無効にしマクロを実行したところ輸入統計品目番号と仕入書価格の箇所には張り付かず、税率が合計され、原産地の箇所は同じ文字が無数に張り付きました。すいませんがもう少しご教示をお願いします。
補足日時:2014/09/26 18:54No.3
- 回答日時:
私ゃ「プロ」じゃないのであまりスマートでないでしょうが、2で回答したものを書き出せるマクロを書いておきます。
コメントも入れておきましたので状況に合わせて編集下さい。
特に、末部分のシート名とクリアの範囲は編集が要るはずですね。
注意
・データのあるシートでマクロ実行してください。
・データは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
'次行の1列目が空白でない限り処理を継続する指示。
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以外のタイトルはセルが結合されています。
補足日時:2014/09/26 15:53No.4
- 回答日時:
マクロ中にコメント入れておきましたが、どうも意図が伝わっていないようですね。
ご確認ください。
> 元データはA1からではなくA10から始まりますので
Lin = 2 'タイトル行を除くデータ先頭行を入れる
の数字2を10(11か?)に変更して下さい。
> データのあるシートでマクロを実行するとデータが削除され
?それは変です。先のマクロの★印コメント行の部分をそちらのシートに合わせて編集しましたか?
Sheets("別シート").Select '★シート名は実情に合わせて指定下さい。
Rows("10:100").ClearContents '★実情に合わせて範囲指定してください。
この2行で「別シート」を呼び出して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列セルを結合したものならば、2行目は
Cells(30 + aln, 3) = arg(aln, 3)
というふうにC列を示す3を入れる(列A,B,C…を1,2,3…の値に置き換える)。
あと分からなければ、マクロをステップ実行(F8)して処理がどう進んでいるのか確認してみることです。
一度配列に取り込みながら合算して、それを最後に書き出しているだけですから。
この回答への補足
教えて頂いた通りに記述しましたがRows("31:60").ClearCont683005ents '★実情に合わせて範囲指定してください。の箇所でデバックがでます。何故か分かりません。どこが間違っているのでしょうか。
補足日時:2014/09/26 18:22No.5
- 回答日時:
> 教えて頂いた通りに記述しましたが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
'次行の1列目が空白でない限り処理を継続する指示。
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
この回答への補足
いろいろとご教示いただきありがとうございます。もう一つお願いがあります。税率も表示させたいのですがどうすればいいでしょうか。添付のエクセルの表には税率が表記されていません。恐れ入りますがこちらのほうもご教示頂ければありがたいです。
補足日時:2014/09/26 21:19No.7ベストアンサー
- 回答日時:
> 税率も表示させたいのですがどうすればいいでしょうか。
書き出し部分を変更します。
税率は、配列argの4列目、つまりarg(x,4)に格納しています。
ただし、大小比較のために無税は0(ゼロ)値として格納していますので、「無税」表示とするなら書き出しの時に戻す必要があります。
~~~
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の通り、結合セルの先頭列にあわせて変更ください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) IFERROR、SMALL関数について 2 2022/08/22 23:40
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- 所得税 確定申告の収入金額欄、源泉徴収額の記入方法について 1 2023/03/12 23:03
- 消費税 消費税還付を受けるための輸出免税の会計処理(仕訳)を教えてください 4 2022/07/14 12:48
- 国産バイク 大型バイク カワサキz900rsに付ける ヨシムラのマフラーの質問です、 【 商 品 I D 】14 2 2023/06/27 13:43
- Excel(エクセル) 隣り合っていないセルを まとめて税込表示したい 8 2022/09/25 14:32
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- 確定申告 確定申告の課税される、所得金額が12番ー29番でマイナスになった場合は確定申告書の税金の計算項目は記 1 2023/03/11 11:00
- Excel(エクセル) Excelについて 6 2023/03/03 16:19
- 格安スマホ・SIMフリースマホ 今 ahamoを使っていて 4898円以下になる携帯会社があれば乗り換えたいです 20GBと24時間 9 2022/09/27 07:43
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
マクロの「SaveAs」でエラーが...
-
EXCELのSheet番号って変更でき...
-
EXCEL VBA 転記 条件分岐 新...
-
【VBA】特定の条件でセルをコピー
-
VBA 空白行に転記する
-
Count Ifのセルの範囲指定に変...
-
マクロ実行後に別シートの残像...
-
検索して修正したデータの上書転記
-
VBA 重複チェック後に値をワー...
-
vba 住所で判断して担当支店に...
-
ExcelのVBマクロを、バックグラ...
-
VBA-重複データ同士の照合
-
Changeイベントで複数セルへの...
-
複数シートの複数列に入力され...
-
GASでチェックボックスを一括of...
-
VBAで変数の数/変数名を動的に...
-
Excel VBAで、散布図のデータ範...
-
同じ作業(データコピー・貼付...
-
ExcelVBAでDo Until loopのネス...
-
100万件越えCSVから条件を満た...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
マクロの「SaveAs」でエラーが...
-
VBA 空白行に転記する
-
EXCELのSheet番号って変更でき...
-
マクロ実行後に別シートの残像...
-
VBA 別ブックからの転記の高速...
-
VBA別シートの最終行の次行へ転...
-
【VBA】特定の条件でセルをコピー
-
Count Ifのセルの範囲指定に変...
-
100万件越えCSVから条件を満た...
-
楽天RSSからエクセルVBAを使用...
-
VBAコードについて
-
Changeイベントで複数セルへの...
-
VBAで変数の数/変数名を動的に...
-
Excel2013で切り取り禁止
-
グラフマクロで系列を変数にす...
-
VBA 実行時エラー1004 rangeメ...
-
ExcelのVBマクロを、バックグラ...
-
Unionでの他のシートの参照につ...
-
Excel VBA オートフィルターで...
-
アクセスからエクセルへ出力時...
おすすめ情報