पावर क्वेरी, पावर पिवोट और वीबीए के साथ उन्नत एक्सेल डैशबोर्ड बनाएं - डेटा से लेकर इनसाइट तक

• बिक्री - इसमें लेनदेन डेटा शामिल है।
• उत्पाद – उत्पाद विवरण और श्रेणियां।
• ग्राहक – ग्राहक जानकारी.
• क्षेत्र – भौगोलिक जानकारी.

चरण 1:डेटा आयात के लिए पावर क्वेरी का उपयोग करें

डेटा आयात करें:

• डेटा पर जाएं टैब>> डेटा प्राप्त करें चुनें>> फ़ाइल से चुनें>>टेक्स्ट/CSV से चुनें .
• अपने sales_data.txt पर नेविगेट करें फ़ाइल>> आयात पर क्लिक करें .

• जब पावर क्वेरी संपादक खुलता है, तो डेटा की समीक्षा करें और ये परिवर्तन करें:
• डेटा प्रकार बदलें।
• किसी भी कॉलम पर राइट-क्लिक करें>> डेटा प्रकार बदलें चुनें>> डेटा प्रकार चुनें .
  • ऑर्डर दिनांक तारीख करना .
  • मात्रा पूर्ण संख्या तक .
  • इकाई मूल्य और छूट दशमलव संख्या तक .

• डुप्लिकेट हटाएं का उपयोग करके किसी भी डुप्लिकेट पंक्तियों को हटाएं .
• उचित डेटा प्रकार परिवर्तनों को लागू करते हुए, Product.csv, Customers.csv और Dates.csv के लिए आयात प्रक्रिया को दोहराएं।

पावर क्वेरी के साथ डेटा ट्रांसफ़ॉर्म करें:

• पावर क्वेरी एडिटर में, बिक्री डेटा चुनें।
• कॉलम जोड़ें पर जाएं टैब>> कस्टम कॉलम चुनें .
• इसे नाम दें:राजस्व .
• निम्न सूत्र सम्मिलित करें।

= [Quantity] * [UnitPrice] * (1-[DiscountRate])

• ठीक क्लिक करें .

• एक और कस्टम कॉलम जोड़ें।
• इसे नाम दें:मुनाफ़ा .
• निम्न सूत्र सम्मिलित करें।

Profit = [Revenue] - ([Quantity] * [UnitCost])

• ठीक क्लिक करें .
• लागत प्राप्त करने के लिए हमें उत्पाद तालिका के साथ विलय करना होगा।

अतिरिक्त जानकारी के लिए तालिकाएँ मर्ज करें:

• पावर क्वेरी एडिटर में बिक्री डेटा खुला हुआ।
• घर पर जाएं टैब>> क्वेरीज़ मर्ज करें पर क्लिक करें रिबन से.
  • उत्पाद आईडी चुनें विक्रय तालिका से.
  • उत्पाद का चयन करें तालिका बनाएं और ProductID पर शामिल हों .
  • ठीक क्लिक करें .

• विस्तार तालिका पर क्लिक करें विकल्प>> केवल यूनिटकोस्ट चुनें लाने के लिए कॉलम.
• ठीक क्लिक करें .

• अब, लाभ को खींचें विस्तारित उत्पाद के अंतर्गत कॉलम कदम.
• आपको लाभ की राशि मिलेगी.

• एक बार जब आप डेटा परिवर्तन का काम पूरा कर लें।
• क्लिक करें बंद करें और लोड करें...

• डेटा आयात करें में डिब्बा;
  • केवल कनेक्शन बनाएं चुनें .
  • इस डेटा को डेटा मॉडल में जोड़ें चुनें सभी चार तालिकाओं के लिए.
  • ठीक क्लिक करें .

चरण 2:पावर पिवोट के साथ डेटा मॉडल बनाएं

• फ़ाइल पर जाएं टैब>> विकल्प चुनें>> ऐड-इन्स चुनें .
• में प्रबंधित करें बॉक्स>> COM ऐड-इन्स चुनें>>जाएं चुनें .

• Microsoft Power Pivot for Excel चुनें .
• ठीक क्लिक करें .

• पावर पिवोट पर जाएं टैब>> प्रबंधित करें चुनें .
• यह पावर क्वेरी से आयातित डेटा को खोलेगा।

रिश्ते बनाएं :

• घर पर जाएं टैब>> आरेख दृश्य चुनें .
  • खींचें बिक्री[ProductID] उत्पाद[उत्पाद आईडी] पर .
  • खींचें बिक्री[CustomerID] ग्राहकों के लिए[CustomerID] .
  • खींचें ग्राहक[RegionID] क्षेत्र[RegionID] पर .

• या डिज़ाइन पर जाएं टैब>> रिश्ता बनाएं चुनें .
• फिर मेल खाने वाले कॉलम चुनें।

परिकलित उपाय बनाएं:

• पावर पिवोट में, बिक्री तालिका पर क्लिक करें।
• घर पर जाएं टैब>> उपाय चुनें>> नया उपाय क्लिक करें .
• या, घर पर जाएं टैब>> गणना क्षेत्र चुनें .
• परिकलित माप वहां डालें:

• कुल राजस्व:

Total Revenue := SUM(Sales[Revenue])

• कुल लाभ:

Total Profit := SUM(Sales[Profit])

• लाभ मार्जिन:

Profit Margin := DIVIDE([Total Profit], [Total Revenue], 0)

• कुल ऑर्डर:

Total Orders:=COUNTA(Sales[OrderID])

• औसत ऑर्डर मूल्य:

Average Order Value:=DIVIDE([Total Revenue], DISTINCTCOUNT(Sales[OrderID]), 0)

• YTD राजस्व:

YTD Revenue:=CALCULATE([Total Revenue], DATESYTD(Sales[OrderDate]))

• पिछले वर्ष का राजस्व:

Previous Year Revenue:=CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Sales[OrderDate]))

• वर्ष-दर-वर्ष वृद्धि:

YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

चरण 3:डैशबोर्ड घटक बनाने के लिए पिवट टेबल का उपयोग करें

पिवोटटेबल्स बनाएं

• सम्मिलित करें पर जाएं टैब>> पिवोटटेबल चुनें>> डेटा मॉडल से चुनें .
• पावर पिवोट में, होम पर जाएं टैब>> पिवोट टेबल चुनें .

• पिवोटटेबल बनाएं में डिब्बा;
  • नई वर्कशीट चुनें।
  • ठीक क्लिक करें .

• कुल राजस्व, कुल लाभ, लाभ मार्जिन और वृद्धि के लिए अलग-अलग पिवोटटेबल बनाएं।
• इन्हें उन कक्षों में रखें जो आपके डैशबोर्ड संरचना के साथ संरेखित हों।
• मुद्रा या प्रतिशत के रूप में उपयुक्त स्वरूपित करें।

चार्ट और विज़ुअलाइज़ेशन

• डेटा मॉडल से एक PivotTable बनाएं।
  • पंक्तियाँ:बिक्री[ऑर्डर दिनांक[माह]]
  • मान:कुल राजस्व
• पिवोटटेबल विश्लेषण पर जाएं टैब>> क्लस्टर्ड कॉलम चार्ट चुनें .

• डेटा मॉडल से एक PivotTable बनाएं।
  • पंक्तियाँ:उत्पाद[उत्पादनाम]
  • मान:कुल राजस्व
• कुल राजस्व के आधार पर क्रमबद्ध करें उतरते हुए, शीर्ष 10 पर फ़िल्टर करें।

• पिवोटटेबल विश्लेषण पर जाएं टैब>> क्लस्टर्ड बार चार्ट चुनें .

• डेटा मॉडल से एक PivotTable बनाएं।
  • पंक्तियाँ:उत्पाद[श्रेणी]
  • मान:कुल राजस्व, कुल लाभ, लाभ मार्जिन .
• पिवोटटेबल विश्लेषण पर जाएं टैब>> कॉम्बो चुनें>>क्लस्टर्ड कॉलम - सेकेंडरी एक्सिस पर लाइन चुनें .

चरण 4:इंटरैक्टिव तत्व सम्मिलित करें

• PivotAnalyze पर जाएं टैब>> स्लाइसर डालें चुनें .
• उत्पाद[श्रेणी] चुनें और ग्राहक[क्षेत्र] .
• ठीक क्लिक करें .

• उन्हें अपने डैशबोर्ड डिज़ाइन से मेल खाने के लिए प्रारूपित करें।

• PivotAnalyze पर जाएं टैब>> स्लाइसर डालें चुनें .
• बिक्री[ऑर्डर दिनांक] चुनें .
• ठीक क्लिक करें .

• आपके चार्ट के ऊपर स्थिति।
• अपनी डैशबोर्ड शैली से मेल खाने के लिए प्रारूप।

• प्रत्येक स्लाइसर पर राइट-क्लिक करें>> रिपोर्ट कनेक्शन चुनें .

• सभी PivotTables की जाँच करें यह सुनिश्चित करने के लिए कि फ़िल्टर विश्व स्तर पर लागू हों।
• ठीक क्लिक करें .

चरण 5:VBA के साथ अंतःक्रियाशीलता को स्वचालित और बढ़ाएं

उदाहरण 1:डेटा ताज़ा करें बटन

• डेवलपर पर जाएं टैब>> सम्मिलित करें चुनें>> बटन चुनें .
• डेटा ताज़ा करें करने के लिए बटन का नाम बदलें .
• राइट-क्लिक बटन>> मैक्रो असाइन करें>> चुनें नया चुनें .

• निम्नलिखित कोड को कॉपी-पेस्ट करें।

Sub RefreshDashboard()
 ThisWorkbook.RefreshAll
 MsgBox "Dashboard data refreshed!", vbInformation
End Sub

उदाहरण 2:डैशबोर्ड रीसेट बटन

• डेवलपर पर जाएं टैब>> सम्मिलित करें चुनें>> बटन चुनें .
• डैशबोर्ड रीसेट करें बटन का नाम बदलें .
• राइट-क्लिक बटन>> मैक्रो असाइन करें>> चुनें नया चुनें .
• निम्नलिखित कोड को कॉपी-पेस्ट करें।

Sub ResetDashboardFilter()
 Dim ws As Worksheet
 Dim slicer As slicerCache
 Dim pivotTable As pivotTable
 
 ' Clear all slicer caches
 For Each slicer In ActiveWorkbook.SlicerCaches
 slicer.ClearAllFilters
 Next slicer
 
 ' Reset any timelines (already using SlicerCaches)
 For Each slicer In ActiveWorkbook.SlicerCaches
 If slicer.SourceType = xlTimeline Then
 slicer.ClearAllFilters
 End If
 Next slicer
 
 ' Refresh pivot tables
 For Each ws In ActiveWorkbook.Worksheets
 For Each pivotTable In ws.PivotTables
 pivotTable.RefreshTable
 Next pivotTable
 Next ws
 
 MsgBox "Dashboard filters have been reset!", vbInformation, "Reset Filters"
End Sub

चरण 6:डैशबोर्ड लेआउट बनाएं

• डैशबोर्ड संरचना सेट करें:
  • डैशबोर्ड शीर्षक और दिनांक फ़िल्टर।
  • KPI अनुभाग (राजस्व, लाभ, मार्जिन, विकास)।
  • चार्ट (बिक्री रुझान, शीर्ष उत्पाद, क्षेत्रीय प्रदर्शन)।

• आप डेटा तालिकाओं और विस्तृत विश्लेषण के लिए एक सारांश तालिका बना सकते हैं।

• संगत फ़ॉर्मेटिंग लागू करें :
  • पूरे समय एक समान रंग योजना का उपयोग करें।
  • सभी तत्वों को ठीक से संरेखित करें।
  • डैशबोर्ड अनुभागों को अलग करने के लिए बॉर्डर जोड़ें।

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

चरण 7:परीक्षण और समस्या निवारण

• सुनिश्चित करें कि स्लाइसर सभी प्रासंगिक विज़ुअलाइज़ेशन को फ़िल्टर करते हैं।
• सत्यापित करें कि बटन मैक्रोज़ को सही ढंग से निष्पादित करते हैं।
• यह सुनिश्चित करने के लिए कि डेटा श्रेणियाँ ठीक से हैं, समयरेखा नियंत्रणों की जाँच करें।
  • चुनें सौंदर्य श्रेणी से स्लाइसर.
  • जनवरी-अप्रैल 2024 चुनें टाइमलाइन से.
• यह फ़िल्टर चयन के आधार पर संपूर्ण डैशबोर्ड को अपडेट कर देगा।

• रीसेट डैशबोर्ड पर क्लिक करें .
• एक संदेश पॉप अप होगा, “डैशबोर्ड फ़िल्टर रीसेट कर दिए गए हैं” .
• ठीक क्लिक करें .
• सभी फ़िल्टर हटा दिए जाएंगे, और आपको एक नया डैशबोर्ड मिलेगा।

• स्रोत डेटा को संशोधित करें।
• डेटा ताज़ा करें क्लिक करें .
• सुनिश्चित करें कि सभी गणनाएँ सही ढंग से अद्यतन हों।
• किसी टूटे हुए कनेक्शन या फ़ॉर्मूले की जाँच करें।

प्रैक्टिस वर्कबुक डाउनलोड करें

निष्कर्ष