樞紐分析疑問

jntseng

用VBA 寫程式時,若要計算項目個數時,要如何寫呢? 若要新增CHAMID 計算項目個數時,要如何寫

raw data 如附件所示:
Book3.rar (88.02 KB)

下載次數: 9

2010-8-8 23:10

我寫的程式如下,缺CHAMID 計算項目個數

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的換上

1. with pt
   
2. .PivotFields("TRANSDT").Orientation = xlColumnField
   
3. .AddDataField pt.PivotFields("DEFQTY"), "加總的DEFQTY", xlSum
   
4. .AddDataField pt.PivotFields("CHAMID"), "計數的CHAMID", xlCount
   
5. .PivotFields("CHAMID").Orientation = xlRowField
   
6. .PivotFields("CHAMID").Position = 1
   
7. end with

複製代碼

jntseng

回復 2# kimbal
謝謝指導,經測試結果可行,額外想要再問另外一個問題,
那就是如附件所示,紅色框框的地方是不是可以改成變數呢?

下載 (34.62 KB)

2010-8-11 23:11

kimbal

回復 3# jntseng


可以,只是普通字串來的
如
S = "CHAMID"
.PivotFields(S)

jntseng

回復 4# kimbal
我想表達的是若我將range("a1")="CHAMID",可以寫成
PivotFields("range("a1")"),因為我想要讓range("a1")變成變動,可是我試過是不行 ,是不是有什麼方法可以達成呢?

kimbal

回復 5# jntseng

是
    PivotFields(range("a1"))
而不是
   PivotFields("range("a1")")

jntseng

回復 6# kimbal
我測試的結果不行,我寫的程式如下: (請問我試哪裡出問題了呢, 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 式暫試試這個?

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

複製代碼

jntseng

回復 8# kimbal
Dim a1 As String '定義a1為字串
Dim a2 As String '定義a2 為字串
a1 = Range("N2").Value 'row field欄位名可以變成變數
a2 = Range("O2").Value  'column field'row field欄位名可以變成變數
帶到程式內即可,若沒有定義為字串,執行會有問題