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

आधुनिक एक्सेल डैशबोर्ड सरल चार्ट और टेबल से कहीं आगे विकसित हुए हैं। पावर क्वेरी को डेटा ट्रांसफॉर्मेशन के लिए, पावर पिवट को उन्नत डेटा मॉडलिंग और विश्लेषण के लिए, और VBA को ऑटोमेशन और उन्नत इंटरएक्टिविटी के लिए जोड़कर, आप पेशेवर डैशबोर्ड बना सकते हैं।

इस ट्यूटोरियल में, हम दिखाएंगे कि पावर क्वेरी, पावर पिवट, और VBA का उपयोग करके उन्नत एक्सेल डैशबोर्ड कैसे बनाएं।

आइए एक काल्पनिक रिटेल कंपनी के लिए एक बिक्री डैशबोर्ड बनाएं जो कई क्षेत्रों में उत्पाद बेचती है। हम निम्नलिखित टेबल वाले डेटासेट के साथ काम करेंगे।

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

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

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

• डेटा टैब >> डेटा प्राप्त करें >> फ़ाइल से >> टेक्स्ट/CSV से चुनें।
• अपनी sales_data.txt फ़ाइल पर नेविगेट करें >> आयात पर क्लिक करें।
• जब पावर क्वेरी एडिटर खुलता है, तो डेटा की समीक्षा करें और ये ट्रांसफॉर्मेशन करें:
  • डेटा प्रकार बदलें।
  • किसी भी कॉलम पर राइट-क्लिक >> डेटा प्रकार बदलें >> डेटा प्रकार चुनें।
    • OrderDate को डेट में।
    • Quantity को पूर्ण संख्या में।
    • UnitPrice और Discount को दशमलव संख्या में।
  • डुप्लिकेट निकालें का उपयोग करके किसी भी डुप्लिकेट पंक्ति को हटाएं।
  • Products.csv, Customers.csv, और Dates.csv के लिए आयात प्रक्रिया दोहराएं, उपयुक्त डेटा प्रकार ट्रांसफॉर्मेशन लागू करें।

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

आइए पावर क्वेरी का उपयोग करके कार्यों को करके अपने बिक्री डेटा को बढ़ाएं।

गणना कॉलम जोड़ें:

• पावर क्वेरी एडिटर में, बिक्री डेटा चुनें।
• कॉलम जोड़ें टैब >> कस्टम कॉलम चुनें।
• इसे नाम दें: राजस्व।
• निम्न फ़ॉर्मूला डालें: = [Quantity] * [UnitPrice] * (1-[DiscountRate])
• OK पर क्लिक करें।
• एक और कस्टम कॉलम जोड़ें।
• इसे नाम दें: लाभ।
• निम्न फ़ॉर्मूला डालें: = [Revenue] - ([Quantity] * [UnitCost])
• OK पर क्लिक करें।
• लागत प्राप्त करने के लिए उत्पाद टेबल के साथ मर्ज करना होगा।

अतिरिक्त इनसाइट्स के लिए टेबल मर्ज करें:

• बिक्री डेटा खुला होने पर पावर क्वेरी एडिटर में।
• होम टैब >> रिबन से क्वेरी मर्ज करें पर क्लिक करें।
  • बिक्री टेबल से ProductID चुनें।
  • उत्पाद टेबल चुनें और ProductID पर जॉइन करें।
  • OK पर क्लिक करें।
• टेबल विस्तार करें विकल्प पर क्लिक करें >> केवल UnitCost कॉलम चुनें।
• OK पर क्लिक करें।
• अब, लाभ कॉलम को विस्तारित उत्पाद चरण के नीचे खींचें।
• आपको लाभ राशि मिल जाएगी।

• डेटा ट्रांसफॉर्मेशन पूरा होने पर।
• बंद करें और लोड करें... पर क्लिक करें।
• डेटा आयात बॉक्स में;
  • केवल कनेक्शन बनाएं चुनें।
  • सभी चार टेबल के लिए इस डेटा को डेटा मॉडल में जोड़ें चुनें।
  • OK पर क्लिक करें।

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

पावर पिवट खोलें:

यदि पावर पिवट रिबन में उपलब्ध नहीं है, तो इसे सक्षम करें।

• फ़ाइल टैब >> विकल्प >> ऐड-इन्स चुनें।
• प्रबंधित बॉक्स में >> COM ऐड-इन्स चुनें >> जाएं चुनें।
• एक्सेल के लिए माइक्रोसॉफ्ट पावर पिवट चुनें।
• OK पर क्लिक करें।
• पावर पिवट टैब >> प्रबंधित चुनें।
• यह पावर क्वेरी से आयातित डेटा खोलेगा।

संबंध बनाएं:

• होम टैब >> डायग्राम व्यू चुनें।
  • बिक्री[ProductID] को उत्पाद[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 वृद्धि: YOY Growth := DIVIDE([Total Revenue] - [Previous Year Revenue], [Previous Year Revenue], 0)

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

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

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

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

राजस्व प्रवृत्ति चार्ट:

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

शीर्ष उत्पाद चार्ट:

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

श्रेणी प्रदर्शन:

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

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

स्लाइसर और टाइमलाइन डालें:

उत्पाद और क्षेत्र स्लाइसर बनाएं:

• पिवटएनालाइज़ टैब >> स्लाइसर डालें चुनें।
• उत्पाद[श्रेणी] और ग्राहक[क्षेत्र] चुनें।
• OK पर क्लिक करें।
• उन्हें अपने डैशबोर्ड डिज़ाइन से मिलान करने के लिए फ़ॉर्मेट करें।

दिनांक फ़िल्टरिंग के लिए टाइमलाइन जोड़ें:

• पिवटएनालाइज़ टैब >> स्लाइसर डालें चुनें।
• बिक्री[OrderDate] चुनें।
• OK पर क्लिक करें।
• अपने चार्ट के ऊपर रखें।
• अपने डैशबोर्ड स्टाइल से मिलान करने के लिए फ़ॉर्मेट करें।

सभी स्लाइसर को पिवटटेबल से कनेक्ट करें:

• प्रत्येक स्लाइसर पर राइट-क्लिक >> रिपोर्ट कनेक्शन चुनें।
• सभी पिवटटेबल चेक करें ताकि फ़िल्टर वैश्विक स्तर पर लागू हों।
• OK पर क्लिक करें।

चरण 5: VBA के साथ ऑटोमेट करें और इंटरएक्टिविटी बढ़ाएं

हम डैशबोर्ड को डायनामिक और नेविगेट करने में आसान बनाने के लिए VBA का उपयोग करेंगे।

उदाहरण 1: डेटा रिफ्रेश बटन

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

VBA कोड:

Sub RefreshDashboard()
  ThisWorkbook.RefreshAll
  MsgBox "डैशबोर्ड डेटा रिफ्रेश हो गया!", vbInformation
End Sub

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

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

VBA कोड:

Sub ResetDashboardFilter()
  Dim ws As Worksheet
  Dim slicer As SlicerCache
  Dim pivotTable As PivotTable
  
  ' सभी स्लाइसर कैश साफ़ करें
  For Each slicer In ActiveWorkbook.SlicerCaches
    slicer.ClearAllFilters
  Next slicer
  
  ' टाइमलाइन रीसेट करें (पहले से ही SlicerCaches का उपयोग कर रहे हैं)
  For Each slicer In ActiveWorkbook.SlicerCaches
    If slicer.SourceType = xlTimeline Then
      slicer.ClearAllFilters
    End If
  Next slicer
  
  ' पिवट टेबल रिफ्रेश करें
  For Each ws In ActiveWorkbook.Worksheets
    For Each pivotTable In ws.PivotTables
      pivotTable.RefreshTable
    Next pivotTable
  Next ws
  
  MsgBox "डैशबोर्ड फ़िल्टर रीसेट हो गए हैं!", vbInformation, "फ़िल्टर रीसेट करें"
End Sub

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

डैशबोर्ड नाम से एक नई वर्कशीट बनाएं।

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

चरण 7: टेस्ट करें और समस्या निवारण करें

सभी इंटरैक्टिव तत्वों का टेस्ट करें:

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

डेटा फ़िल्टर रीसेट टेस्ट करें:

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

डेटा रिफ्रेश टेस्ट करें:

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

निष्कर्ष

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