VBA²£¥Í¼Ï¯Ã¤ÀªRªíRowField¤pp°ÝÃD
- ©«¤l
- 1
- ¥DÃD
- 1
- ºëµØ
- 0
- ¿n¤À
- 2
- ÂI¦W
- 0
- §@·~¨t²Î
- Vista
- ³nÅ骩¥»
- Office2007
- ¾\ŪÅv
- 10
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2011-12-23
- ³Ì«áµn¿ý
- 2011-12-23
|
VBA²£¥Í¼Ï¯Ã¤ÀªRªíRowField¤pp°ÝÃD
¥»©«³Ì«á¥Ñ ahager ©ó 2011-12-23 22:15 ½s¿è
¦U¦ì°ª¤â§A¦n~
¦³»Ýn¥Î¨ìVBA²£¥Í¼Ï¯Ã¤ÀªRªí
¥Ø«e½m²ß°ÝÃD¦p¤U
¦³»Ýn¥Î¨ìDepartment ©M CategoryªºRowField
¦ý¬ODepartment¤£»Ýn²£¥Í¤pp
¦bexcel¸Ìªº¾Þ§@¤w¸g§ä¥X¨Ó¤F
¦b¦C¼ÐÅÒ--> Department --> Äæ¦ì³]©w --> ¤pp»P¿z¿ï--> ±q¦Û°Ê§ï¬°µL´N¦n¤F
¦ý³o¬q»yªk¤£¤Ó·|¼g
½Ðª¾¹Dªº°ª¤â¸Ñµª ·PÁ ^_____^
¥H¤U¬O§Ú°Ñ¦Òªººô¶©Mµ{¦¡½X
http://blog.xuite.net/crdotlin/excel/7649722- With PT
- ' ³]©wÄæ¦ì
- .PivotFields("DEPARTMENT").Orientation = xlRowField
- .PivotFields("Category").Orientation = xlRowField
- .PivotFields("MONTH").Orientation = xlColumnField
- .PivotFields("DIVISION").Orientation = xlPageField
- .PivotFields("BUDGET").Orientation = xlDataField
- .PivotFields("ACTUAL").Orientation = xlDataField
-
- ' ·s¼WpºâÄæ¦ìVariance, ¨äȬ°BUDGET´îACTUALÄæ¦ì
- .CalculatedFields.Add "Variance", "=BUDGET-ACTUAL"
- ' ±NVarianceÄæ¦ì¸m©ó¸ê®Æ½d³ò
- .PivotFields("Variance").Orientation = xlDataField
-
- ' ¦bMONTHÄæ¤W·s¼Wpºâ¸ê®Æ¶µ¥ØQ1,Q2,Q3,Q4µ¥, ¨äȤÀ§O¬°¦Upªº¤ë¥÷©M
- With .PivotFields("MONTH")
- With .CalculatedItems
- .Add "Q1", "= Jan+ Feb+ Mar"
- .Add "Q2", "= Apr+ May+ Jun"
- .Add "Q3", "= Jul+ Aug+ Sep"
- .Add "Q4", "= Oct+ Nov+ Dec"
- End With
-
- ' ³]©wQ1,Q2,Q3,Q4pºâ¶µ¥Øªº¦ì¸m
- .PivotItems("Q1").Position = 4
- .PivotItems("Q2").Position = 8
- .PivotItems("Q3").Position = 12
- .PivotItems("Q4").Position = 16
- End With
½Æ»s¥N½X |
|
|
|
|
|
|