[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『ピボットテーブルでの計算しての集計』（ひー）
すみませんが教えて下さい。
下記のようなバイヤー毎の19年6月〜9月までの月額での
売り上げ実績と売り上げ見込みのデータがあります。
最上段に各月の営業稼働日があります。（6月は20日間の実績）
ピボットテーブルで、バイヤー軸だったり、商品軸で
各月の売り上げ金額の集計をする際に
そのままだと月間の合計の集計をする事になりますが、
営業稼働日、日当たりでの集計をしたいです。
データ上で稼働日で割り算をして、その後ピボットテーブルで集計
すればできるのは分かっておりますが、
データが無数にある為、簡略化をしたいです。
ピボットテーブルで集計する際に自動的に計算して集計できる方法
は無いでしょうか？どなたか教えて下さい。
ピボットテーブルで集計するにあたり、データがきちんと整備されていないと
集計できないのは分かっております。稼働日のデータの位置などは変更しても構いません。
以上、宜しくお願い致します。
稼働日 20 23 17 21
バイヤー 商品 19/6 19/7 19/8 19/9
バイヤーA 商品A 53234 63262 44582 59294
バイヤーA 商品B 5424 5772 2172 2622
バイヤーA 商品C 240 260 1250 732
バイヤーA 商品D 300 0 0 0
バイヤーA 商品E 5300 7287 5921 4199
バイヤーB 商品A 300 0 323 297
バイヤーB 商品B 25535 30155 20761 25513
バイヤーB 商品C 7104 8168 6514 8476
バイヤーB 商品D 2736 3936 2724 3372
バイヤーB 商品E 2943 806 370 370
バイヤーC 商品A 7 10 9 9
バイヤーC 商品B 0 0 0 0
バイヤーC 商品C 13410 19279 19299 14203
バイヤーC 商品D 2094 1892 2865 1775
バイヤーC 商品E 9582 9612 427 427
バイヤーE 商品A 60 3,676 11,232 14,166
バイヤーE 商品B 1525 4581 4243 4253
バイヤーE 商品C 0 0 0 0
バイヤーE 商品D 0 0 0 0
バイヤーE 商品E 0 0 0 0
＜ 使用 Excel：Excel2013、使用 OS：unknown ＞
おはようございます ＾＾ 回答では有りません m(_ _)m ＞＞営業稼働日、日当たりでの集計をしたいです。 は手動だとどんな計算式になり、結果をどの様に 表示したいか等、上記にご提示の表も含め、シート セル等の配置が解る表形式でさらに詳細をご説明 いただくと、多数、アドバイス、回答が有るかもしれません ← 多分 ＾＾； 最初からマクロで集計、思い通りの形式で出力。。。 も一案かもです。でもピボットも捨てがたいですね。 でわでわ m(_ _)m （隠居じーさん） 2019/08/13(火) 07:44
（γ） 2019/08/13(火) 07:58
隠居じーさん 様
結果の表示ですが、同じ形式の表にはなりますが、各月の売り上げを最上段の稼働日の数字で
割った数字が並ぶ形のイメージになります。
セル等の配置が分かる形で貼り付けたいのですが、うまく表示されず、このような形で申し訳ありません。
γ 様
『・「集計フィールド」の仕組みを使って、1営業日あたりの数値を作るかですが、』
これがやりたいです。これがうまくできずに悩んでおります。
初心者ですみませんがご教授の程よろしくお願い致します。
（ひー） 2019/08/13(火) 08:56
集計フィールドの挿入ではセルを参照しての集計はできず
フィールドを指定しての集計だと認識しています。
今回やりたいのは、19/6月とか7月の商品毎の『日当金額の集計』、バイヤー毎の『日当金額の集計』
になりますが、ピボットで集計した合計金額を表の最上段にある各月の稼働日で割り算をして合計したいです。
各月の稼働日が異なりますので、
19/6月は20日を参照して割り算
19/7月は23日を参照して割り算
をしたいのですが、それがピボットテーブルの集計フィールドでうまくできません。
すみませんがご教授よろしくお願い致します。
（ひー） 2019/08/13(火) 09:34
（γ） 2019/08/13(火) 09:49
ありがとうございました。
集計データは多々あり、簡素化をしたいと思ったのですが、
やはり難しいという事ですね。ありがとうございました。
（ひー） 2019/08/13(火) 09:54
元データの列を2倍の個数にするだけです。
それはセル参照して簡単にできます。
それもできないということですか。
（γ） 2019/08/13(火) 10:08
そこまでは最初の文面で述べていなかったのでうまく伝わらずにすみません。
（ひー） 2019/08/13(火) 10:20
＞何十というファイルを毎月毎月同様に列を増やして作成し直す ＞という工数を簡素化できないか、と考えました。
最初は大仕事になるかもしれませんが 後処理のことを将来的に考えると 列を増やすのではなく、縦にデータが増加するように設計したほうがベターだと思います
列の見出しで言うと
年月 （営業所） バイヤー 商品 金額
などとして、他の営業所も含めて、一つにまとめておけば、データの追加も容易ですし 集計も楽になると思います。 月別の稼働日を別テーブルで用意しておけば、1日当たりの平均値を出すのも難しくはないでしょう
現状のレイアウトにこだわりすぎると、苦労が続くばかりだと思いますが・・
参考まで
（渡辺ひかる） 2019/08/13(火) 10:57
アドバイス、ありがとうございます。
色々と検討してみます。
（ひー） 2019/08/13(火) 11:29
マクロを使っていいんでしょう？
データブック(複数）からマクロで日割ベースのブック(複数）を作る
日割ベースのブックでピボットテーブルを今まで通り作る。(これも面倒なら、マクロでやる）
・・てな段取りにすれば、何十というファイルを毎月やるんでも、 ストレスは「増えない」と思いますけどね。
（半平太） 2019/08/13(火) 12:17
ありがとうございます。当方マクロはまだまだ勉強不足です。
マクロが実現できるならストレスはかなり軽減できます。
もし教えて頂けるなら非常に助かります。
よろしくお願い致します。
（ひー） 2019/08/13(火) 13:34
＞もし教えて頂けるなら非常に助かります。
・・と言われましても、
データブックはどこにあるかも分かりませんし (一つのフォルダーにまとまっているなら問題ないですけど・・）
データシート名は何なのかも分かりません。 (決まったシート名なのですか？ それとも各ブック１シートしか入ってなくて、特定する迄もないのですか？）
（半平太） 2019/08/13(火) 15:24
情報不足ですみません。
データは１つのファイル、１つのシートの中にまとまっている
という前提で結構でございます。
シート名も特定せず、sheet1として結構です。
よろしくお願い致します。
（ひー） 2019/08/13(火) 15:53
今回マクロ化が出来るなら…と言っておりますのは、
月間での売上実績を日当平均の売上にデータ変換をする部分になります。
月間売上と稼働日のレイアウトは前述のようなレイアウトとなっております。
すみませんがよろしくお願い致します。
（ひー） 2019/08/13(火) 16:00
＞今回マクロ化が出来るなら…と言っておりますのは、 ＞月間での売上実績を日当平均の売上にデータ変換をする部分になります。
認識にズレがあるといけないので再度書きますが、
私の言っているマクロは、元のデータを日割にして、別ブックにするものです(同じブックでもいいですけど） そのブックを使って、従来通りピボットテーブルにしていただく構想です。
従来のピボットの結果をマクロで日割に直すことではないですけども、同じ認識ですか？
それと、これはちょっとピンとこないです。 ↓ ＞データは１つのファイル、１つのシートの中にまとまっている ＞という前提で結構でございます。
複数のブックの日割化を一気にマクロで実行しなかったら、有難味が激減すると思うのですが。
（半平太） 2019/08/13(火) 16:18
よろしくお願い致します。
（ひー） 2019/08/13(火) 17:00
＞同じ認識でございます。
半平太さんの提案は 別ブックにしろ、同一ブックにしろ データが増えるんですよ？
つまり
＞結果の表示ですが、同じ形式の表にはなりますが、各月の売り上げを最上段の稼働日の数字で ＞割った数字が並ぶ形のイメージになります。
これをマクロで行うということです
そういう意味では 作成場所の違いこそあれ γさんの提案と同じです。
しかも、現状では列が増えていくわけですから、毎月 マクロを回さなければなりません (前月までのファイルはムダになります）
日割りの元データがマクロで自動的に増えても、都度、ピボットテーブル範囲指定をし直して集計できればよし ということならば問題ありませんけれど・・・・・
（渡辺ひかる） 2019/08/13(火) 17:20
＞ファイルが様々なので、
と言っても、 稼働日がどの列から始まるか、 売上数値がどの行から始まるか、 だけの違いじゃないですか？ まぁ、この問題はペンディングとします。(しかし、やる気が出なくなるなぁ）
以下、取り敢えず「１つのファイル、１つのシート」で・・
<前提> １． データブックは既に開いてあるものとします。
２． 開いているエクセルブックは、データブックとマクロブックのみとします。
３． 新ブック作成後、3ブックが開いた状態になりますが、そのままとします。
(あとで使い勝手を考慮しながら、種々の改善を行う）
<マクロブックの標準モジュールへコピペ> ’↓
Sub DivByNetWorkdays()
Dim wBk As Workbook
Dim usedR As Range
Dim WkdyCel As Range
Dim rngToDivide As Range
If Workbooks.Count = 2 Then
For Each wBk In Workbooks 'データブックを特定する
If Not wBk Is ThisWorkbook Then
Exit For
End If
Next
Set usedR = wBk.Sheets("Sheet1").UsedRange
With Workbooks.Add.Sheets(1)
.Range(usedR.Address).Value = usedR.Value '新ブックに値を代入
Application.ScreenUpdating = False
For Each WkdyCel In .Rows(1).SpecialCells(xlCellTypeConstants, 1)
If IsNumeric(WkdyCel) Then '1行目が日数であれば
WkdyCel.Copy '営業日数をコピーして、日割数値を算出する
Set rngToDivide = .Range(WkdyCel.Offset(2), .Cells(.Rows.Count, WkdyCel.Column).End(xlUp))
rngToDivide.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide
End If
Next
rngToDivide.Cells(1, 1).Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
Else
MsgBox "2つのブックだけ開いてください"
Exit Sub
End If
End Sub
（半平太） 2019/08/13(火) 20:11
For Each WkdyCel In .Rows(1).SpecialCells(xlCellTypeConstants, 1)
上記の所でマクロが止まってしまいます。何がまずいでしょうか？
（ひー） 2019/08/13(火) 23:35
＞ For Each WkdyCel In .Rows(1).SpecialCells(xlCellTypeConstants, 1) ＞上記の所でマクロが止まってしまいます。何がまずいでしょうか？
見当も付きません。
１行目に、まともな稼働日データが無いって事なのでしょうけども、 そんな事態はちょっと考えられないです。
（半平太） 2019/08/14(水) 09:32
元データのセル番地が判らないので何とも言えませんが
Set usedR = wBk.Sheets("Sheet1").UsedRange
の後に、
Msgbox usedR.Address
として、どんなアドレスが表示されるか確認してみたらどうでしょう？
（渡辺ひかる） 2019/08/14(水) 11:09
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.