एक्सेल VBA पिवट टेबल
Pivot Tables बड़ी मात्रा में डेटा की रिपोर्ट को सारांशित करने का दिल हैं। हम VBA कोडिंग के माध्यम से एक धुरी तालिका बनाने की प्रक्रिया को भी स्वचालित कर सकते हैं। वे किसी भी रिपोर्ट या डैशबोर्ड का महत्वपूर्ण हिस्सा हैं, एक्सेल में एक बटन के साथ टेबल बनाना आसान है लेकिन वीबीए में हमें अपनी धुरी तालिका को स्वचालित करने के लिए कुछ कोड लिखने होंगे, एक्सेल 2007 से पहले और इसके पुराने संस्करणों में वीबीए में हमें भाग लेने की आवश्यकता नहीं थी धुरी तालिकाओं के लिए एक कैश बनाएँ, लेकिन एक्सेल 2010 में और इसके नए संस्करण कैश की आवश्यकता है।
वीबीए हमारे कार्यस्थल में हमारे लिए बहुत समय बचा सकता है। भले ही यह महारत हासिल करना आसान नहीं है, लेकिन इसे सीखने के लिए समय व्यतीत करना आसान है। मुझे VBA के माध्यम से पिवट टेबल बनाने की प्रक्रिया को समझने में 6 महीने लगे। आप जानते हैं कि उन 6 महीनों ने मेरे लिए क्या चमत्कार किया है क्योंकि मैंने पिवट टेबल बनाने का प्रयास करते समय बहुत सारी गलतियाँ की हैं।
लेकिन वास्तविक बात यह है कि मैंने अपनी गलतियों से सीखा है, और अब मैं आपको यह लेख लिख रहा हूं कि आपको कोड का उपयोग करके धुरी तालिकाओं को कैसे बनाया जाए।
एक बटन के सिर्फ एक क्लिक के साथ, हम रिपोर्ट बना सकते हैं।
VBA में पिवट टेबल बनाने के चरण
पिवट टेबल बनाने के लिए डेटा होना जरूरी है। इसके लिए मैंने कुछ डमी डेटा बनाए हैं। आप उसी डेटा के साथ मेरे साथ काम करने के लिए कार्यपुस्तिका डाउनलोड कर सकते हैं।
चरण 1: पिवट टेबल एक ऐसी वस्तु है, जो पिवट टेबल को संदर्भित करती है, चर को PivotTables के रूप में घोषित करती है।
कोड:
सब PivotTable () डिम PTable PivotTable एंड सब के रूप में
चरण 2: पहले एक पिवट टेबल बनाने से पहले, हमें डेटा के स्रोत को परिभाषित करने के लिए एक पिवट कैश बनाने की आवश्यकता है।
नियमित कार्यपत्रकों में, हमें परेशान किए बिना धुरी तालिका पृष्ठभूमि में एक धुरी कैश बनाएगी। लेकिन VBA में, हमें बनाना होगा।
इसके लिए, वैरिएबल को PivotCache परिभाषित करें।
कोड:
PivotCache के रूप में मंद PCache
चरण 3: पिवट डेटा रेंज निर्धारित करने के लिए चर को एक सीमा के रूप में परिभाषित करें।
कोड:
डिम प्रांगे अस रेंज
चरण 4: एक पिवट टेबल डालने के लिए, हमें एक वर्कशीट के रूप में वेरिएबल को घोषित करने के लिए पिवट टेबल के लिए वर्कशीट जोड़ने के लिए एक अलग शीट की आवश्यकता होती है।
कोड:
वर्क्स के रूप में मंद PSheet
चरण 5: इसी तरह, कार्यपत्रक वाले डेटा को संदर्भित करने के लिए वर्कशीट के रूप में एक और चर घोषित करें।
कोड:
वर्क्स के रूप में डिम DSheet
चरण 6: अंत में, अंतिम उपयोग की गई पंक्ति और कॉलम को खोजने के लिए, दो और चर को लॉन्ग के रूप में परिभाषित करें।
कोड:
लंबे समय के रूप में लंबे समय के रूप में डिम LR
चरण 7: अब, हमें धुरी तालिका बनाने के लिए एक नई शीट सम्मिलित करने की आवश्यकता है। इससे पहले, यदि कोई पिवट शीट है, तो हमें उसे हटाने की आवश्यकता है।
चरण 8: अब, ऑब्जेक्ट चर PSheet और DSheet को पिवट शीट और डेटा शीट पर क्रमशः सेट करें।
चरण 9: डेटाशीट में अंतिम उपयोग की गई पंक्ति और अंतिम उपयोग किए गए कॉलम का पता लगाएं।
चरण 10: अब अंतिम पंक्ति और अंतिम कॉलम का उपयोग करके धुरी श्रेणी निर्धारित करें।
यह डेटा रेंज को पूरी तरह से सेट कर देगा। डेटाशीट में डेटा का कोई जोड़ या विलोपन होने पर भी यह स्वचालित रूप से डेटा रेंज का चयन करेगा।
चरण 11: एक पिवट टेबल बनाने से पहले, हमें एक पिवट कैश बनाने की आवश्यकता है। नीचे VBA कोड का उपयोग करके धुरी कैश चर सेट करें।
चरण 12: अब, एक रिक्त पिवट तालिका बनाएँ।
चरण 13: धुरी तालिका डालने के बाद, हमें पहले पंक्ति फ़ील्ड सम्मिलित करने की आवश्यकता है। इसलिए मैं अपने देश कॉलम के रूप में पंक्ति फ़ील्ड सम्मिलित करूंगा।
नोट: डेटा कॉलम को समझने के लिए कार्यपुस्तिका डाउनलोड करें।
चरण 14: अब, एक और आइटम मैं दूसरी स्थिति आइटम के रूप में पंक्ति क्षेत्र में सम्मिलित करूंगा। मैं उत्पाद को दूसरी पंक्ति वस्तु के रूप में पंक्ति क्षेत्र में सम्मिलित करूंगा।
चरण 15: पंक्ति फ़ील्ड में कॉलम डालने के बाद, हमें कॉलम फ़ील्ड में मान सम्मिलित करने की आवश्यकता है। मैं कॉलम फ़ील्ड में "सेगमेंट" सम्मिलित करूंगा।
चरण 16: अब, हमें डेटा फ़ील्ड में नंबर डालने की आवश्यकता है। तो डेटा क्षेत्र में "बिक्री" डालें।
चरण 17: हमें पिवट टेबल सारांश भाग के साथ किया जाता है। अब हमें तालिका को प्रारूपित करने की आवश्यकता है। धुरी तालिका को प्रारूपित करने के लिए, नीचे दिए गए कोड का उपयोग करें।
सारणीबद्ध रूप में पंक्ति में दर्ज मूल्यों को दिखाने के लिए, नीचे का कोड जोड़ें।
ठीक है, हम कर रहे हैं यदि हम F5 कुंजी या मैन्युअल रूप से इस कोड को चलाते हैं, तो हमें इस तरह की धुरी तालिका प्राप्त करनी चाहिए।
इस तरह, VBA कोडिंग का उपयोग करके, हम एक धुरी तालिका बनाने की प्रक्रिया को स्वचालित कर सकते हैं।
आपके संदर्भ के लिए, मैंने नीचे कोड दिया है।
Sub PivotTable() Dim PTable As PivotTable Dim PCache As PivotCache Dim PRange As Range Dim PSheet As Worksheet Dim DSheet As Worksheet Dim LR As Long Dim LC As Long On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Worksheets("Pivot Sheet").Delete 'This will delete the exisiting pivot table worksheet Worksheets.Add After:=ActiveSheet ' This will add new worksheet ActiveSheet.Name = "Pivot Sheet" ' This will rename the worksheet as "Pivot Sheet" On Error GoTo 0 Set PSheet = Worksheets("Pivot Sheet") Set DSheet = Worksheets("Data Sheet") 'Find Last used row and column in data sheet LR = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LC = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column 'Set the pivot table data range Set PRange = DSheet.Cells(1, 1).Resize(LR, LC) 'Set pivot cahe Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange) 'Create blank pivot table Set PTable = PCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="Sales_Report") 'Insert country to Row Filed With PSheet.PivotTables("Sales_Report").PivotFields("Country") .Orientation = xlRowField .Position = 1 End With 'Insert Product to Row Filed & position 2 With PSheet.PivotTables("Sales_Report").PivotFields("Product") .Orientation = xlRowField .Position = 2 End With 'Insert Segment to Column Filed & position 1 With PSheet.PivotTables("Sales_Report").PivotFields("Segment") .Orientation = xlColumnField .Position = 1 End With 'Insert Sales column to the data field With PSheet.PivotTables("Sales_Report").PivotFields("Sales") .Orientation = xlDataField .Position = 1 End With 'Format Pivot Table PSheet.PivotTables("Sales_Report").ShowTableStyleRowStripes = True PSheet.PivotTables("Sales_Report").TableStyle2 = "PivotStyleMedium14" 'Show in Tabular form PSheet.PivotTables("Sales_Report").RowAxisLayout xlTabularRow Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
