excel-vba
पिवट तालिकाएं

टिप्पणियों

पिवट टेबल बनाना

एक्सेल में सबसे शक्तिशाली क्षमताओं में से एक डेटा को सॉर्ट करने और विश्लेषण करने के लिए पिवट टेबल्स का उपयोग है। यदि आप पिवट टेबल्स के पिवट कैश के संबंध को समझते हैं और टेबल्स के विभिन्न भागों का संदर्भ और उपयोग कैसे करें, तो पिवोट्स बनाने और हेरफेर करने के लिए वीबीए का उपयोग करना आसान है।

इसके सबसे मूल में, आपका स्रोत डेटा Worksheet पर डेटा का एक Range क्षेत्र है। यह डेटा क्षेत्र सीमा में पहली पंक्ति के रूप में हेडर पंक्ति के साथ डेटा कॉलम की पहचान करना चाहिए । पिवट टेबल बनने के बाद, उपयोगकर्ता किसी भी समय स्रोत डेटा को देख और बदल सकता है। हालाँकि, परिवर्तन स्वचालित रूप से या तुरंत पिवट टेबल में परिलक्षित नहीं हो सकते हैं क्योंकि एक मध्यवर्ती डेटा भंडारण संरचना है जिसे पिवट कैश कहा जाता है जो सीधे पिवट टेबल से जुड़ा होता है।

यदि एक ही स्रोत डेटा के आधार पर कई पिवट टेबल्स की आवश्यकता होती है, तो पिवट कैबेज को पिवट टेबल्स में से प्रत्येक के लिए आंतरिक डेटा स्टोर के रूप में फिर से उपयोग किया जा सकता है। यह एक अच्छा अभ्यास है क्योंकि यह मेमोरी को बचाता है और भंडारण के लिए एक्सेल फाइल के आकार को कम करता है।

एक उदाहरण के रूप में, उपरोक्त आंकड़ों में दिखाए गए स्रोत डेटा के आधार पर एक पिवट टेबल बनाने के लिए:

Sub test()
    Dim pt As PivotTable
    Set pt = CreatePivotTable(ThisWorkbook.Sheets("Sheet1").Range("A1:E15"))
End Sub

Function CreatePivotTable(ByRef srcData As Range) As PivotTable
    '--- creates a Pivot Table from the given source data and
    '    assumes that the first row contains valid header data
    '    for the columns
    Dim thisPivot As PivotTable
    Dim dataSheet As Worksheet
    Dim ptSheet As Worksheet
    Dim ptCache As PivotCache
    
    '--- the Pivot Cache must be created first...
    Set ptCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
                                                  SourceData:=srcData)
    '--- ... then use the Pivot Cache to create the Table
    Set ptSheet = ThisWorkbook.Sheets.Add
    Set thisPivot = ptCache.CreatePivotTable(TableDestination:=ptSheet.Range("A3"))
    Set CreatePivotTable = thisPivot
End Function

संदर्भ MSDN पिवट टेबल ऑब्जेक्ट

धुरी टेबल रेंज

ये उत्कृष्ट संदर्भ स्रोत पिवट टेबल्स में विभिन्न श्रेणियों के विवरण और चित्र प्रदान करते हैं।

संदर्भ

• जॉन Peltier के टेक ब्लॉग से - VBA में पिवट टेबल रेंज्स का संदर्भ लेना
• GlobalBAnect एक्सेल VBA से - VBA का उपयोग करके एक एक्सेल पिवट टेबल रेंज का संदर्भ लेना

धुरी तालिका में फ़ील्ड जोड़ना

पिवट टेबल में फ़ील्ड जोड़ते समय ध्यान देने वाली दो महत्वपूर्ण बातें हैं ओरिएंटेशन और पोजीशन। कभी-कभी एक डेवलपर यह मान सकता है कि कोई फ़ील्ड कहाँ रखी गई है, इसलिए इन मापदंडों को स्पष्ट रूप से परिभाषित करना हमेशा स्पष्ट होता है। ये क्रियाएं केवल दी गई पिवट तालिका को प्रभावित करती हैं, पिवट कैश को नहीं।

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    Set ptField = .PivotFields("Gender")
    ptField.Orientation = xlRowField
    ptField.Position = 1
    Set ptField = .PivotFields("LastName")
    ptField.Orientation = xlRowField
    ptField.Position = 2
    Set ptField = .PivotFields("ShirtSize")
    ptField.Orientation = xlColumnField
    ptField.Position = 1
    Set ptField = .AddDataField(.PivotFields("Cost"), "Sum of Cost", xlSum)
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
End With

पिवट टेबल डेटा को स्वरूपित करना

यह उदाहरण दिए गए पिवट टेबल के डेटा रेंज क्षेत्र ( DataBodyRange ) में कई प्रारूप बदलता / सेट करता है। एक मानक Range में सभी योग करने योग्य पैरामीटर उपलब्ध हैं। डेटा को फ़ॉर्मेट करना केवल पिवट टेबल को प्रभावित करता है, पिवट कैश को नहीं।

नोट: संपत्ति का नाम TableStyle2 क्योंकि TableStyle संपत्ति PivotTable की ऑब्जेक्ट प्रॉपर्टी का सदस्य नहीं है।

Dim thisPivot As PivotTable
Dim ptSheet As Worksheet
Dim ptField As PivotField

Set ptSheet = ThisWorkbook.Sheets("SheetNameWithPivotTable")
Set thisPivot = ptSheet.PivotTables(1)

With thisPivot
    .DataBodyRange.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)"
    .DataBodyRange.HorizontalAlignment = xlRight
    .ColumnRange.HorizontalAlignment = xlCenter
    .TableStyle2 = "PivotStyleMedium9"
End With