डेटा मॉडल द्वारा पिवट के लाभ

एक्सेल में पिवट टेबल बनाते समय, पहले डायलॉग बॉक्स में, जहां हमें शुरुआती रेंज सेट करने और पिवट टेबल डालने के लिए जगह चुनने के लिए कहा जाता है, नीचे एक अगोचर लेकिन बहुत महत्वपूर्ण चेकबॉक्स होता है – इस डेटा को डेटा मॉडल में जोड़ें (यह डेटा जोड़ें डेटा मॉडल के लिए) और, थोड़ा अधिक, स्विच इस पुस्तक के डेटा मॉडल का उपयोग करें (इस कार्यपुस्तिका के डेटा मॉडल का उपयोग करें):

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

हालांकि, इन "बन्स" पर करीब से विचार करने से पहले, आइए पहले समझते हैं कि वास्तव में, यह डेटा मॉडल क्या है?

डेटा मॉडल क्या है

डेटा मॉडल (संक्षिप्त रूप में एमडी या डीएम = डेटा मॉडल) एक एक्सेल फ़ाइल के अंदर एक विशेष क्षेत्र है जहाँ आप सारणीबद्ध डेटा संग्रहीत कर सकते हैं - एक या एक से अधिक तालिकाएँ, यदि वांछित हो, तो एक दूसरे से जुड़ी हुई हैं। वास्तव में, यह एक एक्सेल वर्कबुक के अंदर एम्बेडेड एक छोटा डेटाबेस (OLAP क्यूब) है। एक्सेल की शीट पर नियमित (या स्मार्ट) टेबल के रूप में डेटा के क्लासिक स्टोरेज की तुलना में, डेटा मॉडल के कई महत्वपूर्ण फायदे हैं:

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

मॉडल का प्रबंधन और गणना माइक्रोसॉफ्ट एक्सेल में निर्मित एक विशेष ऐड-इन द्वारा की जाती है - PowerPivotजिसके बारे में मैं पहले ही लिख चुका हूँ। इसे सक्षम करने के लिए, टैब पर विकासक क्लिक करें कॉम ऐड-इन्स (डेवलपर - COM ऐड-इन्स) और उपयुक्त बॉक्स को चेक करें:

यदि टैब विकासक (डेवलपर)आप इसे रिबन पर नहीं देख सकते हैं, आप इसे चालू कर सकते हैं फ़ाइल - विकल्प - रिबन सेटअप (फ़ाइल - विकल्प - रिबन को अनुकूलित करें). यदि COM ऐड-इन्स की सूची में ऊपर दिखाई गई विंडो में आपके पास Power Pivot नहीं है, तो यह आपके Microsoft Office के संस्करण में शामिल नहीं है

दिखाई देने वाले Power Pivot टैब पर, एक बड़ा हल्का हरा बटन होगा प्रबंध (प्रबंधित करना), जिस पर क्लिक करने से एक्सेल के ऊपर पावर पिवट विंडो खुल जाएगी, जहां हम वर्तमान पुस्तक के डेटा मॉडल की सामग्री देखेंगे:

रास्ते में एक महत्वपूर्ण नोट: एक एक्सेल कार्यपुस्तिका में केवल एक डेटा मॉडल हो सकता है।

डेटा मॉडल में टेबल लोड करें

मॉडल में डेटा लोड करने के लिए, पहले हम तालिका को एक गतिशील "स्मार्ट" कीबोर्ड शॉर्टकट में बदल देते हैं कंट्रोल+T और इसे टैब पर एक दोस्ताना नाम दें निर्माता (डिज़ाइन). यह एक आवश्यक कदम है।

फिर आप इनमें से चुनने के लिए तीन विधियों में से किसी एक का उपयोग कर सकते हैं:

• बटन दबाएँ मॉडल में जोड़ें (डेटा मॉडल में जोड़ें) टैब PowerPivot टैब होम (घर).
  
• टीमों का चयन सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल) और चेकबॉक्स चालू करें इस डेटा को डेटा मॉडल में जोड़ें (इस डेटा को डेटा मॉडल में जोड़ें). इस मामले में, मॉडल में लोड किए गए डेटा के अनुसार, एक पिवट टेबल भी तुरंत बनाया जाता है।
  
• उन्नत टैब पर जानकारी (तारीख) बटन पर क्लिक करें टेबल/रेंज से (टेबल/रेंज से)हमारी तालिका को Power Query संपादक में लोड करने के लिए। यह पथ सबसे लंबा है, लेकिन, यदि वांछित है, तो आप यहां अतिरिक्त डेटा सफाई, संपादन और सभी प्रकार के परिवर्तन कर सकते हैं, जिसमें Power Query बहुत मजबूत है।

  फिर कॉम्बेड डेटा को कमांड द्वारा मॉडल पर अपलोड किया जाता है होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ...). खुलने वाली विंडो में, विकल्प चुनें बस एक कनेक्शन बनाएं (केवल कनेक्शन बनाएं) और, सबसे महत्वपूर्ण बात, एक टिक लगाएं इस डेटा को डेटा मॉडल में जोड़ें (इस डेटा को डेटा मॉडल में जोड़ें).
  

हम डेटा मॉडल का सारांश बनाते हैं

सारांश डेटा मॉडल बनाने के लिए, आप तीन तरीकों में से किसी एक का उपयोग कर सकते हैं:

• बटन दबाएं सारांश तालिका (पिवट तालिका) पावर पिवट विंडो में।
• एक्सेल में कमांड चुनें सम्मिलित करें - पिवोटटेबल और मोड पर स्विच करें इस पुस्तक के डेटा मॉडल का उपयोग करें (सम्मिलित करें - पिवट टेबल - इस कार्यपुस्तिका के डेटा मॉडल का उपयोग करें).
• टीमों का चयन सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल) और चेकबॉक्स चालू करें इस डेटा को डेटा मॉडल में जोड़ें (इस डेटा को डेटा मॉडल में जोड़ें). वर्तमान "स्मार्ट" तालिका को मॉडल में लोड किया जाएगा और संपूर्ण मॉडल के लिए एक सारांश तालिका बनाई जाएगी।

अब जबकि हमने यह जान लिया है कि डेटा मॉडल में डेटा कैसे लोड किया जाए और उस पर एक सारांश कैसे बनाया जाए, आइए इससे हमें मिलने वाले लाभों और लाभों का पता लगाएं।

लाभ 1: सूत्रों का उपयोग किए बिना तालिकाओं के बीच संबंध

एक नियमित सारांश केवल एक स्रोत तालिका के डेटा का उपयोग करके बनाया जा सकता है। यदि आपके पास उनमें से कई हैं, उदाहरण के लिए, बिक्री, मूल्य सूची, ग्राहक निर्देशिका, अनुबंधों का रजिस्टर, आदि, तो आपको सबसे पहले VLOOKUP जैसे कार्यों का उपयोग करके सभी तालिकाओं से डेटा एकत्र करना होगा। (VLOOKUP), अनुक्रमणिका (अनुक्रमणिका), अधिक उजागर (मिलान), सारांश (एसयूएमआईएफएस) और जैसे। यह लंबा, थकाऊ है और आपके एक्सेल को बड़ी मात्रा में डेटा के साथ "विचार" में ले जाता है।

डेटा मॉडल के सारांश के मामले में, सब कुछ बहुत आसान है। Power Pivot विंडो में एक बार तालिकाओं के बीच संबंध स्थापित करने के लिए पर्याप्त है - और यह हो गया है। ऐसा करने के लिए, टैब पर PowerPivot बटन दबाएँ प्रबंध (प्रबंधित करना) और फिर दिखाई देने वाली विंडो में - बटन चार्ट दृश्य (आरेख देखें). लिंक बनाने के लिए तालिकाओं के बीच सामान्य (कुंजी) कॉलम नाम (फ़ील्ड) खींचना बाकी है:

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

लाभ 2: अद्वितीय मूल्यों की गणना करें

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

फ़ील्ड पर राइट-क्लिक करें - कमांड मान फ़ील्ड विकल्प और टैब पर आपरेशन चुनें विभिन्न तत्वों की संख्या (अलग गिनती):

लाभ 3: कस्टम DAX सूत्र

कभी-कभी आपको पिवट टेबल में विभिन्न अतिरिक्त गणनाएं करनी पड़ती हैं। नियमित सारांशों में, यह परिकलित फ़ील्ड और ऑब्जेक्ट का उपयोग करके किया जाता है, जबकि डेटा मॉडल सारांश एक विशेष DAX भाषा (DAX = डेटा विश्लेषण अभिव्यक्ति) में उपायों का उपयोग करता है।

माप बनाने के लिए, टैब पर चयन करें PowerPivot आदेश उपाय - उपाय बनाएं (उपाय - नया उपाय) या पिवट फील्ड्स सूची में टेबल पर राइट-क्लिक करें और चुनें उपाय जोड़ें (माप जोड़ें) संदर्भ मेनू में:

खुलने वाली विंडो में, सेट करें:

• तालिका नामजहां बनाया गया उपाय संग्रहीत किया जाएगा।
• उपाय का नाम - कोई भी नाम जिसे आप नए क्षेत्र के लिए समझते हैं।
• Description - वैकल्पिक।
• सूत्र - सबसे महत्वपूर्ण बात, क्योंकि यहां हम या तो मैन्युअल रूप से दर्ज करते हैं, या बटन पर क्लिक करते हैं fx और सूची से एक DAX फ़ंक्शन का चयन करें, जो परिणाम की गणना तब करनी चाहिए जब हम अपना माप मान क्षेत्र में फेंकते हैं।
• खिड़की के निचले हिस्से में, आप तुरंत सूची में माप के लिए संख्या प्रारूप सेट कर सकते हैं वर्ग.
  

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

लाभ 4: कस्टम फ़ील्ड पदानुक्रम

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

Power Pivot विंडो में, बटन के साथ चार्ट मोड पर स्विच करें चार्ट दृश्य टैब होम (होम - आरेख देखें), के साथ चयन करें कंट्रोल वांछित फ़ील्ड और उन पर राइट-क्लिक करें। संदर्भ मेनू में कमांड होगा पदानुक्रम बनाएं (पदानुक्रम बनाएं):

बनाए गए पदानुक्रम का नाम बदला जा सकता है और माउस के साथ आवश्यक फ़ील्ड में खींचा जा सकता है, ताकि बाद में एक आंदोलन में उन्हें सारांश में फेंक दिया जा सके:

लाभ 5: कस्टम स्टेंसिल

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

ऐसा करने के लिए, टैब पर पिवट टेबल विश्लेषण ड्रॉप डाउन सूची में फ़ील्ड, आइटम और सेट संबंधित आदेश हैं (विश्लेषण - फील्ड्स, आईसमय और सेट — पंक्ति/स्तंभ मदों के आधार पर सेट बनाएँ):

खुलने वाली विंडो में, आप किसी भी तत्व की स्थिति को चुनिंदा रूप से हटा सकते हैं, जोड़ सकते हैं या बदल सकते हैं और परिणामी सेट को एक नए नाम के तहत सहेज सकते हैं:

सभी बनाए गए सेट PivotTable फ़ील्ड्स पैनल में एक अलग फ़ोल्डर में प्रदर्शित किए जाएंगे, जहां से उन्हें किसी भी नई PivotTable की पंक्तियों और कॉलम क्षेत्रों में स्वतंत्र रूप से खींचा जा सकता है:

लाभ 6: टेबल्स और कॉलम को चुनिंदा रूप से छुपाएं

हालांकि यह एक छोटा, लेकिन कुछ मामलों में बहुत ही सुखद लाभ है। Power Pivot विंडो में फ़ील्ड नाम या तालिका टैब पर राइट-क्लिक करके, आप कमांड का चयन कर सकते हैं क्लाइंट टूलकिट से छिपाएं (क्लाइंट टूल्स से छिपाएं):

छिपा हुआ स्तंभ या तालिका PivotTable फ़ील्ड सूची फलक से गायब हो जाएगी। यह बहुत सुविधाजनक है यदि आपको उपयोगकर्ता से कुछ सहायक कॉलम (उदाहरण के लिए, गणना या संबंध बनाने के लिए प्रमुख मानों वाले कॉलम) या यहां तक ​​​​कि संपूर्ण तालिकाओं को छिपाने की आवश्यकता है।

लाभ 7. उन्नत ड्रिल-डाउन

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

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

उसके बाद, वर्तमान मान (मॉडल = एक्सप्लोरर) फ़िल्टर क्षेत्र में जाएगा, और सारांश कार्यालयों द्वारा बनाया जाएगा:

बेशक, इस तरह की प्रक्रिया को कई बार दोहराया जा सकता है, लगातार अपने डेटा में उस दिशा में तल्लीन करना जिसमें आप रुचि रखते हैं।

लाभ 8: पिवट को क्यूब फंक्शन में बदलें

यदि आप डेटा मॉडल के सारांश में किसी सेल का चयन करते हैं और फिर टैब पर चयन करते हैं पिवट टेबल विश्लेषण आदेश OLAP टूल्स - फ़ार्मुलों में कनवर्ट करें (विश्लेषण करें - OLAP उपकरण - सूत्रों में कनवर्ट करें), तो संपूर्ण सारांश स्वचालित रूप से सूत्रों में परिवर्तित हो जाएगा। अब पंक्ति-स्तंभ क्षेत्र में फ़ील्ड मान और मान क्षेत्र में परिणाम विशेष क्यूब फ़ंक्शंस का उपयोग करके डेटा मॉडल से प्राप्त किए जाएंगे: CUBEVALUE और CUBEMEMBER:

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

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

• Power Pivot और Power Query के साथ पिवट तालिका में योजना-तथ्य विश्लेषण
• मल्टीलाइन हेडर वाली पिवट टेबल
• Power Pivot का उपयोग करके Excel में एक डेटाबेस बनाएँ