[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『製品ｺｰﾄﾞ別集計方法』（信州）
週単位でｱｯﾌﾟﾃﾞｰﾄされる製品の売上金額をVBAで製品ｺｰﾄﾞの左端のアルファベット群毎にAVG、MAX、MINを算出して 別ｼｰﾄに記録したいのですが、VBA記述がわかりません。どなたか知恵を貸して頂けませんか？ （現在は手動でいちいち算出する、という面倒な事をやっている為・・・）
A B C
1 製品ｺｰﾄﾞ 製造ﾒｰｶｰ 売上金額
2 ABC-1 アンテナ 250000
3 ＡＢ-DS アンテナ 150000
4 CNG-1A 坂本精密 900000
5 C3-6 日本産業 4000000
6 HNP-87 群馬精密 1625000
8 ZFGED ラーメン堂 1575000
9 CLOP 胴元産業 1750000
10 HHH-HA ヘテロ産業 10800000
11 ZOLO-78 坂本精密 25200000
12 C6-67 日本産業 2450000
13 CAMO-6 カメラ工業 2500000
14 CAPE-8 カメラ工業 12800000
15 Z890 Ｚ工業 11570000
16 Z956 Ｚ工業 23900000
17 HARRY ヘテロ産業 1800000
製品ｺｰﾄﾞ別に並べ替えて
製品ｺｰﾄﾞ 製造ﾒｰｶｰ 金額 ABC-1 アンテナ 250000 AB-DS アンテナ 150000 C3-6 日本産業 4000000 C6-67 日本産業 2450000 CAMO-6 カメラ工業 22500000 CAPE-8 カメラ工業 12800000 CLOP 胴元産業 1750000 CNG-1A 坂本精密 900000 HARRY ヘテロ産業 1800000 HHH-HA ヘテロ産 10800000 HNP-87 群馬精密 1625000 Z890 Ｚ工業 11570000 Z956 Ｚ工業 23900000 ZFGED ラーメン堂 1575000 ZOLO-78 坂本精密 25200000
ｱﾙﾌｧﾍﾞｯﾄ順に並べ替えた製品ｺｰﾄﾞをｱﾙﾌｧﾍﾞｯﾄ群毎にAVG,MAX,MINを求め、それを別ｼｰﾄに記録したいのです。
A B C 1 A AVG : Aは製品ｺｰﾄﾞの左端の頭文字 2 A MAX 3 A MIN 4 5 C AVG : Cは製品ｺｰﾄﾞの左端の頭文字 6 C MAX 7 C MIN 8 9 H AVG 10 H MAX 11 H MIN 12 13 Z AVG 14 Z MAX 15 Z MIN 16 17 全体の AVG 18 全体の MAX 19 全体の MIN
Excel2000, OS:Windows2000
「別シート」はすでに用意されているものでしょうか。 コードを作成するつもりはありませんが、アイデアだけ提示。 (1)元のデータへ（別にどこでも良いのだけど）作業列を設定。 (2)作業列にLEFT関数で先頭の文字列だけ抽出。 (3)フィルタオプションで重複のない先頭文字列をさらに別の範囲へリストアップ。 (4)リストアップした物を並べ替え (5)先頭文字列のリストを順番にオートフィルタで作業列をフィルタリング。 (6)フィルタされたデータに対してWorksheetFunctionのSubtotal関数で計算し 指定のセル位置へその値を返す。 (7)リストアップした先頭文字の数だけ(5)(6)を繰り返す。 (8)作業列を削除。 という具合のコードを作成する。 ついでにマクロによらない方法。 (1)並べ替えは完了しているとして、C列を挿入。 (2)一行目に適当な見出しを作成して 二行目に =LEFT(B2) としてA列の先頭文字を抽出。 この式をデータ数分、フィルコピー。 (3)データ範囲を選択するか、範囲内の任意の一セルを選択した状態で (4)データ＞集計 でグループの基準に作業列を指定、 集計の方法に「平均」を選択、 集計するフィールドで「金額」にチェック。 「現在の集計表と置き換える」のチェックをはずしてOK。 (5)「最大値」「最小値」について集計作業を繰り返し、A列B列を非表示、 (6)アウトライン第２レベルで折りたたむと、次のような集計表が出来上がります。 区分 金額 A 平均 200000 A 最大値 250000 A 最小値 150000 C 平均 7400000 C 最大値 22500000 C 最小値 900000 H 平均 4741666.667 H 最大値 10800000 H 最小値 1625000 Z 平均 15561250 Z 最大値 25200000 Z 最小値 1575000 全体の平均 8084666.667 全体の最大値 25200000 全体の最小値 150000 ちょっと出来上がりは違いますが。 (みやほりん)(-_∂)b
こんばんは〜♪
ピボットテーブルは、いかがでしょうか？
A B C D [1] 製品ｺｰﾄﾞ 製造ﾒｰｶｰ 売上金額 先頭コード [2] ABC-1 アンテナ 250000 A [3] AB-DS アンテナ 150000 A [4] C3-6 日本産業 900000 C [5] C6-67 日本産業 400000 C [6] CAMO-6 カメラ工業 162500 C
>製品ｺｰﾄﾞ別に並べ替えて
別シートへ並べ替えるのでしょうか？ ★元データを別シートへ、コピぺしてから 並べ替え？
★みやほりんさんと同じく D列に作業列を作ります。 D2 =LEFT(B2) 下へコピー。
★ピボットテーブルから (1)ウィザード1/3 →次へ (2)ウィザード2/3 範囲→先頭行からデータ行をまでの範囲を選択(ドラッグ)
(3)ウィザード3/3 レイアウトから 行→先頭コードをドラッグ データ→売上金額を３つドラッグ で、OK
(4)作成先は、お好みに。 完了
★データの列の下の3行を順番に 右クリックで、フィールドの設定で 平均 最大値 最小値を選択します。
こんな感じに出来上がります 元データの金額を少し変えてしまったので みやほりんさんと集計金額が違いますが
先頭コード データ 集計
A 最大値 / 売上金額 250000
最小値 / 売上金額2 150000
平均 / 売上金額3 200000
C 最大値 / 売上金額 12800000
最小値 / 売上金額2 900000
平均 / 売上金額3 4066666.667
H 最大値 / 売上金額 10800000
最小値 / 売上金額2 1625000
平均 / 売上金額3 4741666.667
Z 最大値 / 売上金額 25200000
最小値 / 売上金額2 1575000
平均 / 売上金額3 15561250
全体の 最大値 / 売上金額 25200000
全体の 最小値 / 売上金額2 150000
全体の 平均 / 売上金額3 6751333.333
ご参考にどうぞ〜。。。 Ms.Rinでした〜♪♪
みやほりんさん、Ms.Rinさん、アドバイス有難うございます。 午後、又レスします。 めずらしく雨が降っている標高1,000ｍの信州でした。
ピボットﾃｰﾌﾞﾙでの作成、初めてでしたのでこういうやり方もあるんだ！と知り、目からうろこが落ちる思いでした・・・ 便利ですね〜。この作業をマクロ化して、週単位でｱｯﾌﾟﾃﾞｰﾄするｱﾙﾌｧﾍﾞｯﾄ群別集計に使えるかこれから試してみます。 信州
ｱﾙﾌｧﾍﾞｯﾄ群に並べ替えるマクロをＶＢＡ化しようと思い、下記のｺｰﾄﾞを記述して実行したのですが、エラーになり、 うまくいきませんでした。どこに問題があるでしょうか？
Sub 先頭コード作成2()
Dim n As Integer
n = Count.Formula("A:A")
Range("D2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-3])"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D" & "n"), Type:=xlFillDefault
Range("D2:D" & "n").Select
Range("E1").Select
End Sub
信州
n = Count.Formula("A:A")
これは、最終行までの行数をカウントしようとしているのでしょうか。
A列に歯抜けデータがないという前提なら、
n = WorksheetFunction.CountA(Range("A:A"))
などで取得できます。
(みやほりん)(-_∂)b
みやりほんさん、ありがとうございます。 これからﾁｬﾚﾝｼﾞしてみます。 信州
ピボットテーブル利用の方針なら、並べ替えは必要ないような気もします。 (みやほりん)(-_∂)b
ピボットﾃｰﾌﾞﾙを活用したマクロ化であれば、日々のｱｯﾌﾟﾃﾞｰﾄにも対応可能ですか？その辺がよくわからないのですが・・
***** 雪が降り始めた信州でした ****
マクロなら大抵のことはできると思いますが、 「アップデート」が具体的にどのような手順によるものか、 また、集計を作成するシートが固定されたものなのか、それとも新規に追加していくのか、 など不明な点がたくさんあるので具体的なアドバイスが差し上げにくいのです。 (みやほりん)(-_∂)b 年末業務多忙のため、たぶん次回のレスは年明けです。
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.