यह ट्यूटोरियल प्रदर्शित करेगा कि वीबीए का उपयोग करके पिवट टेबल्स के साथ कैसे काम किया जाए।
पिवट टेबल्स डेटा संक्षेपण उपकरण हैं जिनका उपयोग आप अपने डेटा से मुख्य अंतर्दृष्टि और सारांश निकालने के लिए कर सकते हैं। आइए एक उदाहरण देखें: हमारे पास सेल A1:D21 में एक स्रोत डेटा सेट है जिसमें बेचे गए उत्पादों का विवरण है, जो नीचे दिखाया गया है:
मूल्य प्राप्त करने के लिए GetPivotData का उपयोग करना
मान लें कि आपके पास मान/डेटा फ़ील्ड में बिक्री के साथ PivotTable1 नामक एक PivotTable है, पंक्ति फ़ील्ड के रूप में उत्पाद और कॉलम फ़ील्ड के रूप में क्षेत्र है। आप PivotTable.GetPivotData पद्धति का उपयोग Pivot Tables से मान वापस करने के लिए कर सकते हैं।
निम्न कोड PivotTable से $1,130.00 (पूर्वी क्षेत्र के लिए कुल बिक्री) लौटाएगा:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("बिक्री", "क्षेत्र", "पूर्व") |
इस मामले में, बिक्री "डेटाफ़ील्ड" है, "फ़ील्ड 1" क्षेत्र है और "आइटम 1" पूर्व है।
निम्नलिखित कोड पिवट टेबल से $980 (उत्तरी क्षेत्र में उत्पाद ABC की कुल बिक्री) लौटाएगा:
| 1 | MsgBox ActiveCell.PivotTable.GetPivotData ("बिक्री", "उत्पाद", "एबीसी", "क्षेत्र", "उत्तर") |
इस मामले में, बिक्री "डेटाफ़ील्ड" है, "फ़ील्ड 1" उत्पाद है, "आइटम 1" एबीसी है, "फ़ील्ड 2" क्षेत्र है और "आइटम 2" उत्तर है।
आप 2 से अधिक फ़ील्ड भी शामिल कर सकते हैं।
GetPivotData का सिंटैक्स है:
गेटपिवोटडेटा (जानकारी स्थान, फील्ड1, वस्तु 1, फ़ील्ड2, आइटम 2… ) कहां:
| पैरामीटर | विवरण |
|---|---|
| जानकारी स्थान | डेटा फ़ील्ड जैसे बिक्री, मात्रा आदि जिसमें संख्याएँ होती हैं। |
| फ़ील्ड 1 | तालिका में किसी स्तंभ या पंक्ति फ़ील्ड का नाम। |
| वस्तु 1 | फ़ील्ड 1 (वैकल्पिक) में किसी आइटम का नाम। |
| फ़ील्ड 2 | तालिका में कॉलम या पंक्ति फ़ील्ड का नाम (वैकल्पिक)। |
| आइटम 2 | फ़ील्ड 2 (वैकल्पिक) में किसी आइटम का नाम। |
शीट पर पिवट टेबल बनाना
सक्रिय कार्यपुस्तिका के पत्रक1 पर सेल J2 पर उपरोक्त डेटा श्रेणी के आधार पर एक पिवट तालिका बनाने के लिए, हम निम्नलिखित कोड का उपयोग करेंगे:
| 1234567891011 | वर्कशीट्स ("शीट 1")। सेल (1, 1)। चुनेंActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"शीट1!R1C1:R21C4", संस्करण:=xlPivotTableVersion15)।PivotTable बनाएं _TableDestination:="Sheet1!R2C10", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15शीट्स ("शीट 1")। चुनें |
परिणाम है:
नई शीट पर पिवट टेबल बनाना
सक्रिय कार्यपुस्तिका की एक नई शीट पर उपरोक्त डेटा श्रेणी के आधार पर एक पिवट टेबल बनाने के लिए, हम निम्नलिखित कोड का उपयोग करेंगे:
| 12345678910111213 | वर्कशीट्स ("शीट 1")। सेल (1, 1)। चुनेंपत्रक।जोड़ेंActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _"शीट1!R1C1:R21C4", संस्करण:=xlPivotTableVersion15)।PivotTable बनाएं _TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _:=xlPivotTableVersion15शीट्स ("शीट 2")। चुनें |
पिवट टेबल में फील्ड्स जोड़ना
आप ऊपर दी गई डेटा श्रेणी के आधार पर पिवोटटेबल1 नामक नव निर्मित पिवट तालिका में फ़ील्ड जोड़ सकते हैं। नोट: आपकी पिवट टेबल वाली शीट को एक्टिव शीट होना चाहिए।
पंक्तियाँ फ़ील्ड में उत्पाद जोड़ने के लिए, आप निम्न कोड का उपयोग करेंगे:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = xlRowFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Position = 1 |
कॉलम फ़ील्ड में क्षेत्र जोड़ने के लिए, आप निम्न कोड का उपयोग करेंगे:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlColumnFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
मुद्रा संख्या प्रारूप के साथ मूल्य अनुभाग में बिक्री जोड़ने के लिए, आप निम्नलिखित कोड का उपयोग करेंगे:
| 123456789 | ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _"PivotTable1").PivotFields("Sales"), "Sum of Sales", xlSumActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Sales") के साथ.NumberFormat = "$#,##0.00"के साथ समाप्त करना |
परिणाम है:
पिवट टेबल का रिपोर्ट लेआउट बदलना
आप अपनी पिवट टेबल का रिपोर्ट लेआउट बदल सकते हैं। निम्नलिखित कोड आपकी पिवट तालिका के रिपोर्ट लेआउट को सारणीबद्ध रूप में बदल देगा:
| 1 | ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight18" |
पिवट टेबल को हटाना
आप वीबीए का उपयोग करके एक पिवट टेबल को हटा सकते हैं। निम्न कोड सक्रिय पत्रक पर PivotTable1 नामक पिवट तालिका को हटा देगा:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, Trueचयन। सामग्री साफ़ करें |
वर्कबुक में सभी पिवट टेबल को फॉर्मेट करें
आप VBA का उपयोग करके किसी कार्यपुस्तिका में सभी Pivot Tables को प्रारूपित कर सकते हैं। निम्न कोड किसी कार्यपुस्तिका की सभी शीटों को लूप करने के लिए लूप संरचना का उपयोग करता है, और कार्यपुस्तिका में सभी पिवट तालिकाएँ हटाता है:
| 12345678910111213 | उप स्वरूपणAllThePivotTablesInAWorkbook ()वर्कशीट के रूप में डिम wksकार्यपुस्तिका के रूप में मंद wbडब्ल्यूबी = एक्टिववर्कबुक सेट करेंपिवट टेबल के रूप में मंद पीटीwb.Sheets में प्रत्येक सप्ताह के लिएप्रत्येक पीटी के लिए wks.PivotTables . मेंpt.TableStyle2 = "PivotStyleLight15"अगला पीटीअगले सप्ताहअंत उप |
VBA में लूप्स का उपयोग कैसे करें, इसके बारे में अधिक जानने के लिए यहां क्लिक करें।
पिवट टेबल के फील्ड्स को हटाना
आप VBA का उपयोग करके पिवट तालिका में फ़ील्ड निकाल सकते हैं। निम्न कोड सक्रिय पत्रक में PivotTable1 नामक पिवट तालिका से पंक्ति अनुभाग में उत्पाद फ़ील्ड को हटा देगा:
| 12 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Product").Orientation = _xlहिडन |
फ़िल्टर बनाना
PivotTable1 नामक एक Pivot Table को पंक्ति अनुभाग में उत्पाद और मान अनुभाग में बिक्री के साथ बनाया गया है। आप VBA का उपयोग करके अपनी पिवट तालिका के लिए एक फ़िल्टर भी बना सकते हैं। निम्न कोड फ़िल्टर अनुभाग में क्षेत्र के आधार पर एक फ़िल्टर बनाएगा:
| 123 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1 |
इस मामले में पूर्वी क्षेत्र में एकल रिपोर्ट आइटम के आधार पर अपनी पिवट तालिका को फ़िल्टर करने के लिए, आप निम्न कोड का उपयोग करेंगे:
| 12345 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").ClearAllFiltersActiveSheet.PivotTables("PivotTable1").PivotFields("Region").CurrentPage = _"पूर्व" |
मान लें कि आप अपनी पिवट टेबल को कई क्षेत्रों के आधार पर फ़िल्टर करना चाहते हैं, इस मामले में पूर्व और उत्तर में, आप निम्न कोड का उपयोग करेंगे:
| 1234567891011121314 | ActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Orientation = xlPageFieldActiveSheet.PivotTables("PivotTable1").PivotFields("Region").Position = 1ActiveSheet.PivotTables("PivotTable1").PivotFields("Region")। _EnableMultiplePageItems = TrueActiveSheet.PivotTables("PivotTable1").PivotFields("Region") के साथ.PivotItems("South").Visible = False.PivotItems("West").Visible = Falseके साथ समाप्त करना |
अपनी पिवट टेबल को रिफ्रेश करना
आप वीबीए में अपनी पिवट टेबल रीफ्रेश कर सकते हैं। VBA में PivotTable1 नामक विशिष्ट तालिका को ताज़ा करने के लिए आप निम्न कोड का उपयोग करेंगे:
| 1 | ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh |
