[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『集計行へ計算式を登録するマクロ』（momo）
お世話になります。マクロ初心者です。よろしくお願い致します。
質問です。現在、集計結果のエクセルブックを他部門から貰っています。
商品販売仕入統計表なのですが、元はピボットで作成されたもので、値貼付けされているので計算式はありません。
ところが、商品分類別合計行の項目によって合計では異なってしまう項目があり、マクロで計算式を自動入力し正しい表記にしたいと考えたのですが、計算式を下記マクロの方法で登録すると行番号が固定行となってしまい商品分類別合計行毎に同じ数値が計算されてしまいます。
自動で行番号を取得する方法が無く困っています。
下記の記述内容も他のサイトでコピーしたもので、記述が読める訳ではありませんので、見よう見真似でやったものです。
お教え戴けると大変助かります。どうぞよろしくお願い申し上げます。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
分類数:20ケ(小計行数は20行発生します。)
分類合計行:集計都度、計行番号は異なります。
登録計算式:実際には合計行１行に計算式で再計算しなくてはならないのは８箇所あります。
現在、手作業で登録作業。
(毎回160箇所+合計行+8で168箇所の計算式の入換を行なっています)
例.平均販売単価 現状は単価の合計となっているので、平均単価に変更したい。
=合計売上金額÷合計販売数量
================================================================================
Sub 小計行計算式_平均売価()
Sheets("販売仕入統計情報").Select
Dim c As Range
Dim FirstAdd As String
Const MYTXT As String = "集計"
Set c = ActiveSheet.Columns("H:H").Find( _
What:=MYTXT, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
FirstAdd = c.Address
Do
c.Offset(, 12).Value = "=ROUND(S10/R10),2)
Set c = ActiveSheet.Columns("H:H").FindNext(c)
If c.Address = FirstAdd Then Exit Sub
Loop Until c Is Nothing
End If
End Sub
================================================================================
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
Q&Aサロンはマルチポスト禁止 （マルチーズ） 2015/11/25(水) 12:37
＞商品販売仕入統計表
その表のレイアウトとサンプルデータ、 および希望する結果値を例示して頂かないと始まらないです。
（半平太） 2015/11/25(水) 12:58
1 分類CD 小分類名 商品名 数量 売上金額 平均売単価 2 101010 アイウ ●●○○△■CCC 10 1.000 100.00 3 ●●○○△■AAA 20 2.500 125.00 4 ●●○○△■BBB 50 10,000 200.00 5 101010集計 80 13,500 425.00←168.75 6 201010 カキク ●○○△■●DDD 20 2.000 100.00 7 ●○○△■●FFF 200 5.000 25.00 8 ●○○△■●ZZZ 500 20,000 40.00 9 901010集計 720 27,000 165.00← 37.50 10 901010 ラリル ●○■△■●GGG 90 11.700 130.00 11 ●○■△■●EEE 700 56.000 80.00 12 ●○■△■●YYY 300 12,000 40.00 13 901010集計 1,090 250,700 250.00←230.00 14 合計 1,890 291,200 840.00←154.07
平均売単価の集計行と合計行が加算されているのでF5とF9・F13及びF14へ行番号を取得して
=ROUND((E/D).2)の計算式を登録して計算させるマクロとなります。
以上、宜しくお願い致します。
（momo） 2015/11/25(水) 14:47
＜対策＞ １．検索語彙を「計」だけにする ２．数式をR1C1形式にする ３．検索列をA列に訂正する ４．オフセット値を5列とする
Sub 小計行計算式_平均売価()
Sheets("販売仕入統計情報").Select
Dim c As Range
Dim FirstAdd As String
Const MYTXT As String = "計"
Set c = ActiveSheet.Columns("A:A").Find( _
What:=MYTXT, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
FirstAdd = c.Address
Do
c.Offset(, 5).FormulaR1C1 = "=ROUND(RC[-1]/RC[-2],2)"
Set c = ActiveSheet.Columns("A:A").FindNext(c)
If c.Address = FirstAdd Then Exit Sub
Loop Until c Is Nothing
End If
End Sub
<販売仕入統計情報 実行結果図> 行 _____A_____ ____B____ _______C_______ __D__ ____E____ _____F_____ 1 分類CD 小分類名 商品名 数量 売上金額 平均売単価 2 101010 アイウ ●●○○△■CCC 10 1 100 3 ●●○○△■AAA 20 2.5 125 4 ●●○○△■BBB 50 10,000 200 5 101010集計 80 13,500 168.75 6 201010 カキク ●○○△■●DDD 20 2 100 7 ●○○△■●FFF 200 5 25 8 ●○○△■●ZZZ 500 20,000 40 9 901010集計 720 27,000 37.5 10 901010 ラリル ●○■△■●GGG 90 11.7 130 11 ●○■△■●EEE 700 56 80 12 ●○■△■●YYY 300 12,000 40 13 901010集計 1,090 250,700 230 14 合計 1,890 291,200 154.07
（半平太） 2015/11/25(水) 15:34
With Sheets("販売仕入統計情報")
For Each cl In .UsedRange.Columns(2).Cells
If Trim(cl.Value) = "集計" Or Trim(cl.Offset(, -1).Value) = "合計" Then
cl.Offset(, 4).FormulaR1C1 = "=ROUND(RC[-1]/RC[-2],2)"
cl.Offset(, 4).NumberFormatLocal = "0.00_ "
End If
Next cl
End With
End Sub
（mm） 2015/11/25(水) 15:37
横から失礼します。
Sub 小計行計算式_平均売価()
Dim LastRow As Long, r As Long
LastRow = Sheets("販売仕入統計情報").Cells(Rows.Count, 1).End(xlUp).Row
For r = LastRow To 2 Step -1
If Right(Cells(r, 1).Value, 2) Like "*計" Then
Cells(r, 6).Formula = "=ROUND(E" & r & "/D" & r & ", 2)"
End If
Next
End Sub
みたいなのでもいいのかな。 （se_9） 2015/11/25(水) 15:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.