¼Ï¯Ã¤ÀªRºÃ°Ý

jntseng

¥ÎVBA ¼gµ{¦¡®É,­Y­n­pºâ¶µ¥Ø­Ó¼Æ®É,­n¦p¦ó¼g©O? ­Y­n·s¼WCHAMID ­pºâ¶µ¥Ø­Ó¼Æ®É,­n¦p¦ó¼g

raw data ¦pªþ¥ó©Ò¥Ü:
Book3.rar (88.02 KB)

¤U¸ü¦¸¼Æ: 9

2010-8-8 23:10

§Ú¼gªºµ{¦¡¦p¤U,¯ÊCHAMID ­pºâ¶µ¥Ø­Ó¼Æ

Dim r As Single
r = [F65536].End(xlUp).Row
Set ptcache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="total glass!R1C2: R3030C12")
Set pt = ptcache.CreatePivotTable(TableDestination:="", TableName:="pivottable1")
With pt
.PivotFields("CHAMID").Orientation = xlRowField
.PivotFields("TRANSDT").Orientation = xlColumnField
.PivotFields("DEFQTY").Orientation = xlDataField
End With
End Sub

kimbal

§âwithªº´«¤W

1. with pt
   
2. .PivotFields("TRANSDT").Orientation = xlColumnField
   
3. .AddDataField pt.PivotFields("DEFQTY"), "¥[Á`ªºDEFQTY", xlSum
   
4. .AddDataField pt.PivotFields("CHAMID"), "­p¼ÆªºCHAMID", xlCount
   
5. .PivotFields("CHAMID").Orientation = xlRowField
   
6. .PivotFields("CHAMID").Position = 1
   
7. end with

½Æ»s¥N½X

jntseng

¦^´_ 2# kimbal
ÁÂÁ«ü¾É,¸g´ú¸Õµ²ªG¥i¦æ,ÃB¥~·Q­n¦A°Ý¥t¥~¤@­Ó°ÝÃD,
¨º´N¬O¦pªþ¥ó©Ò¥Ü,¬õ¦â®Ø®Øªº¦a¤è¬O¤£¬O¥i¥H§ï¦¨ÅܼƩO?

¤U¸ü (34.62 KB)

2010-8-11 23:11

kimbal

¦^´_ 3# jntseng


¥i¥H,¥u¬O´¶³q¦r¦ê¨Óªº
¦p
S = "CHAMID"
.PivotFields(S)

jntseng

¦^´_ 4# kimbal
§Ú·Qªí¹Fªº¬O­Y§Ú±Nrange("a1")="CHAMID",¥i¥H¼g¦¨
PivotFields("range("a1")"),¦]¬°§Ú·Q­nÅýrange("a1")Åܦ¨ÅÜ°Ê,¥i¬O§Ú¸Õ¹L¬O¤£¦æ ,¬O¤£¬O¦³¤°»ò¤èªk¥i¥H¹F¦¨©O?

kimbal

¦^´_ 5# jntseng

¬O
    PivotFields(range("a1"))
¦Ó¤£¬O
   PivotFields("range("a1")")

jntseng

¦^´_ 6# kimbal
§Ú´ú¸Õªºµ²ªG¤£¦æ,§Ú¼gªºµ{¦¡¦p¤U: (½Ð°Ý§Ú¸Õ­þ¸Ì¥X°ÝÃD¤F©O, range("n2")=TRANSDT,range("o2")=CHAMID

Dim r As Single
Dim a1 As Range
Dim a2 As Range
Set a1 = Range("N2") 'row field
Set a2 = Range("O2")  'column field
r = [F65536].End(xlUp).Row
Set ptcache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="total glass!R1C2: R3030C12")
Set pt = ptcache.CreatePivotTable(TableDestination:="", TableName:="pivottable1")
With pt
.PivotFields(Range("N2")).Orientation = xlColumnField
.AddDataField pt.PivotFields("DEFQTY"), "defect ¼Æ", xlSum
.AddDataField pt.PivotFields("CHAMID"), "glass ¼Æ", xlCount
.CalculatedFields.Add "density", "DEFQTY/CHAMID"
.PivotFields("density").Orientation = xlDataField
.Caption = " density"
.PivotFields(Range("O2")).Orientation = xlRowField
.PivotFields("CHAMID").Position = 1
End With
End Sub

kimbal

Oh sorry ¦¡¼È¸Õ¸Õ³o­Ó?

1. Dim r As Single
   
2. Dim a1 As Range
   
3. Dim a2 As Range
   
4. Set a1 = Range("N2") 'row field
   
5. Set a2 = Range("O2")  'column field
   
6. r = [F65536].End(xlUp).Row
   
7. Set ptcache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="total glass!R1C2: R3030C12")
   
8. Set pt = ptcache.CreatePivotTable(TableDestination:="", TableName:="pivottable1")
   
9. With pt
   
10. .PivotFields(Range("N2").value).Orientation = xlColumnField
    
11. .AddDataField pt.PivotFields("DEFQTY"), "defect ¼Æ", xlSum
    
12. .AddDataField pt.PivotFields("CHAMID"), "glass ¼Æ", xlCount
    
13. .CalculatedFields.Add "density", "DEFQTY/CHAMID"
    
14. .PivotFields("density").Orientation = xlDataField
    
15. .Caption = " density"
    
16. .PivotFields(Range("O2").value).Orientation = xlRowField
    
17. .PivotFields("CHAMID").Position = 1
    
18. End With

½Æ»s¥N½X

jntseng

¦^´_ 8# kimbal
Dim a1 As String '©w¸qa1¬°¦r¦ê
Dim a2 As String '©w¸qa2 ¬°¦r¦ê
a1 = Range("N2").Value 'row fieldÄæ¦ì¦W¥i¥HÅܦ¨ÅܼÆ
a2 = Range("O2").Value  'column field'row fieldÄæ¦ì¦W¥i¥HÅܦ¨ÅܼÆ
±a¨ìµ{¦¡¤º§Y¥i,­Y¨S¦³©w¸q¬°¦r¦ê,°õ¦æ·|¦³°ÝÃD