डेटा मॉडेलद्वारे पिव्होटचे फायदे

Excel मध्ये पिव्होट टेबल बनवताना, पहिल्या डायलॉग बॉक्समध्ये, जिथे आम्हाला प्रारंभिक श्रेणी सेट करण्यास सांगितले जाते आणि पिव्होट टेबल घालण्यासाठी जागा निवडण्यास सांगितले जाते, खाली एक अस्पष्ट परंतु अतिशय महत्त्वाचा चेकबॉक्स आहे – हा डेटा डेटा मॉडेलमध्ये जोडा (हा डेटा जोडा डेटा मॉडेल) आणि, थोडे वर, स्विच या पुस्तकाचे डेटा मॉडेल वापरा (या वर्कबुकचे डेटा मॉडेल वापरा):

दुर्दैवाने, अनेक वापरकर्ते जे बर्याच काळापासून पिव्होट टेबलशी परिचित आहेत आणि त्यांचा त्यांच्या कामात यशस्वीरित्या वापर करतात त्यांना कधीकधी या पर्यायांचा अर्थ खरोखरच समजत नाही आणि ते कधीही वापरत नाहीत. आणि व्यर्थ. शेवटी, डेटा मॉडेलसाठी पिव्होट टेबल तयार केल्याने आम्हाला क्लासिक एक्सेल पिव्होट टेबलच्या तुलनेत अनेक महत्त्वाचे फायदे मिळतात.

तथापि, या “बन्स” वर जवळून विचार करण्यापूर्वी, प्रथम हे डेटा मॉडेल काय आहे हे समजून घेऊया?

डेटा मॉडेल म्हणजे काय

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

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

मायक्रोसॉफ्ट एक्सेलमध्ये तयार केलेल्या विशेष अॅड-इनद्वारे मॉडेलचे व्यवस्थापन आणि गणना केली जाते - पॉवरपीव्होटज्याबद्दल मी आधीच लिहिले आहे. ते सक्षम करण्यासाठी, टॅबवर विकसक क्लिक करा COM अॅड-इन्स (विकासक — COM अॅड-इन्स) आणि योग्य बॉक्स चेक करा:

टॅब असल्यास विकसक (विकासक)तुम्ही ते रिबनवर पाहू शकत नाही, तुम्ही ते चालू करू शकता फाइल - पर्याय - रिबन सेटअप (फाइल — पर्याय — रिबन सानुकूलित करा). वर दर्शविलेल्या विंडोमध्ये COM अॅड-इन्सच्या सूचीमध्ये तुमच्याकडे पॉवर पिव्होट नसल्यास, ते तुमच्या Microsoft Office च्या आवृत्तीमध्ये समाविष्ट केलेले नाही 🙁

दिसत असलेल्या पॉवर पिव्होट टॅबवर, एक मोठे हलके हिरवे बटण असेल व्यवस्थापन (व्यवस्थापित करा), त्यावर क्लिक केल्याने एक्सेलच्या वर पॉवर पिव्होट विंडो उघडेल, जिथे आपण वर्तमान पुस्तकाच्या डेटा मॉडेलची सामग्री पाहू:

वाटेत एक महत्त्वाची टीप: एक्सेल वर्कबुकमध्ये फक्त एक डेटा मॉडेल असू शकतो.

डेटा मॉडेलमध्ये टेबल लोड करा

मॉडेलमध्ये डेटा लोड करण्यासाठी, प्रथम आम्ही टेबलला डायनॅमिक "स्मार्ट" कीबोर्ड शॉर्टकटमध्ये बदलतो. Ctrl+T आणि त्याला टॅबवर एक अनुकूल नाव द्या रचनाकार (डिझाइन). हे एक आवश्यक पाऊल आहे.

त्यानंतर तुम्ही निवडण्यासाठी तीनपैकी कोणतीही पद्धत वापरू शकता:

• बटण दाबा मॉडेलमध्ये जोडा (डेटा मॉडेलमध्ये जोडा) टॅब पॉवरपीव्होट टॅब होम पेज (मुख्यपृष्ठ).
  
• संघ निवडत आहे घाला - PivotTable (घाला — मुख्य सारणी) आणि चेकबॉक्स चालू करा हा डेटा डेटा मॉडेलमध्ये जोडा (हा डेटा डेटा मॉडेलमध्ये जोडा). या प्रकरणात, मॉडेलमध्ये लोड केलेल्या डेटानुसार, एक मुख्य सारणी देखील त्वरित तयार केली जाते.
  
• प्रगत टॅबवर डेटा (तारीख) बटणावर क्लिक करा टेबल/श्रेणीतून (टेबल/श्रेणीवरून)आमचे टेबल पॉवर क्वेरी एडिटरमध्ये लोड करण्यासाठी. हा मार्ग सर्वात लांब आहे, परंतु, इच्छित असल्यास, येथे तुम्ही अतिरिक्त डेटा साफ करणे, संपादन आणि सर्व प्रकारचे परिवर्तन करू शकता, ज्यामध्ये पॉवर क्वेरी खूप मजबूत आहे.

  नंतर कमांडद्वारे कॉम्बेड डेटा मॉडेलवर अपलोड केला जातो मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा...). उघडणाऱ्या विंडोमध्ये, पर्याय निवडा फक्त एक कनेक्शन तयार करा (केवळ कनेक्शन तयार करा) आणि, सर्वात महत्वाचे, एक टिक लावा हा डेटा डेटा मॉडेलमध्ये जोडा (हा डेटा डेटा मॉडेलमध्ये जोडा).
  

आम्ही डेटा मॉडेलचा सारांश तयार करतो

सारांश डेटा मॉडेल तयार करण्यासाठी, तुम्ही तीनपैकी कोणत्याही पद्धतीचा वापर करू शकता:

• बटण दाबा सारांश सारणी (मुख्य सारणी) पॉवर पिव्होट विंडोमध्ये.
• Excel मध्ये आदेश निवडा घाला - PivotTable आणि मोडवर स्विच करा या पुस्तकाचे डेटा मॉडेल वापरा (इन्सर्ट — पिव्होट टेबल — या वर्कबुकचे डेटा मॉडेल वापरा).
• संघ निवडत आहे घाला - PivotTable (घाला — मुख्य सारणी) आणि चेकबॉक्स चालू करा हा डेटा डेटा मॉडेलमध्ये जोडा (हा डेटा डेटा मॉडेलमध्ये जोडा). वर्तमान "स्मार्ट" सारणी मॉडेलमध्ये लोड केली जाईल आणि संपूर्ण मॉडेलसाठी सारांश सारणी तयार केली जाईल.

आता डेटा मॉडेलमध्ये डेटा कसा लोड करायचा आणि त्यावर सारांश कसा तयार करायचा हे आपण शोधून काढले आहे, त्यामुळे आपल्याला मिळणारे फायदे आणि फायद्यांचा शोध घेऊ या.

फायदा 1: सूत्रे न वापरता सारण्यांमधील संबंध

एक नियमित सारांश केवळ एका स्रोत सारणीतील डेटा वापरून तयार केला जाऊ शकतो. जर तुमच्याकडे त्यापैकी अनेक असतील, उदाहरणार्थ, विक्री, किंमत सूची, ग्राहक निर्देशिका, कराराची नोंदणी इ., तर तुम्हाला प्रथम VLOOKUP सारख्या फंक्शन्सचा वापर करून सर्व टेबलमधून डेटा गोळा करावा लागेल. (VLOOKUP), INDEX (INDEX), अधिक उघड (सामना), SUMMESLIMN (SUMIFS) आणि सारखे. हे लांबलचक, कंटाळवाणे आहे आणि तुमच्या एक्सेलला मोठ्या प्रमाणात डेटासह "विचार" मध्ये आणते.

डेटा मॉडेलच्या सारांशाच्या बाबतीत, सर्वकाही बरेच सोपे आहे. पॉवर पिव्होट विंडोमध्ये एकदा टेबलांमधील संबंध सेट करणे पुरेसे आहे – आणि ते पूर्ण झाले. हे करण्यासाठी, टॅबवर पॉवरपीव्होट बटण दाबा व्यवस्थापन (व्यवस्थापित करा) आणि नंतर दिसत असलेल्या विंडोमध्ये - बटण चार्ट दृश्य (आकृती दृश्य). दुवे तयार करण्यासाठी टेबल्समध्ये सामान्य (की) स्तंभ नावे (फील्ड) ड्रॅग करणे बाकी आहे:

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

फायदा 2: अद्वितीय मूल्ये मोजा

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

फील्ड - कमांडवर उजवे-क्लिक करा मूल्य फील्ड पर्याय आणि टॅबवर ऑपरेशन निवडा विविध घटकांची संख्या (वेगळी संख्या):

लाभ 3: सानुकूल DAX सूत्रे

काहीवेळा तुम्हाला मुख्य सारण्यांमध्ये विविध अतिरिक्त गणिते करावी लागतात. नियमित सारांशांमध्ये, हे गणना केलेल्या फील्ड आणि ऑब्जेक्ट्स वापरून केले जाते, तर डेटा मॉडेल सारांश विशिष्ट DAX भाषेत (DAX = डेटा विश्लेषण अभिव्यक्ती) उपायांचा वापर करते.

माप तयार करण्यासाठी, टॅबवर निवडा पॉवरपीव्होट आदेश उपाय - उपाय तयार करा (उपाय - नवीन उपाय) किंवा पिव्होट फील्ड सूचीमधील टेबलवर फक्त उजवे-क्लिक करा आणि निवडा माप घाला (माप जोडा) संदर्भ मेनूमध्ये:

उघडणाऱ्या विंडोमध्ये, सेट करा:

• टेबल नावजेथे तयार केलेले माप संग्रहित केले जाईल.
• नाव मोजा - नवीन फील्डसाठी तुम्हाला समजलेले कोणतेही नाव.
• वर्णन - पर्यायी.
• सुत्र - सर्वात महत्वाची गोष्ट, कारण येथे आपण एकतर व्यक्तिचलितपणे प्रविष्ट करतो किंवा बटणावर क्लिक करतो fx आणि सूचीमधून DAX फंक्शन निवडा, ज्याने परिणामाची गणना केली पाहिजे जेव्हा आम्ही आमचे मोजमाप मूल्य क्षेत्रामध्ये टाकतो.
• विंडोच्या खालच्या भागात, आपण सूचीतील मोजमापासाठी नंबरचे स्वरूप त्वरित सेट करू शकता वर्ग.
  

DAX भाषा समजण्यास नेहमीच सोपी नसते कारण ती वैयक्तिक मूल्यांसह नाही तर संपूर्ण स्तंभ आणि सारण्यांसह कार्य करते, म्हणजे क्लासिक एक्सेल सूत्रांनंतर विचारांची काही पुनर्रचना आवश्यक असते. तथापि, हे फायदेशीर आहे, कारण मोठ्या प्रमाणात डेटावर प्रक्रिया करण्याच्या त्याच्या क्षमतेची शक्ती जास्त मोजणे कठीण आहे.

फायदा 4: सानुकूल फील्ड पदानुक्रम

बर्‍याचदा, मानक अहवाल तयार करताना, तुम्हाला दिलेल्या अनुक्रमात फील्डचे समान संयोजन मुख्य सारणीमध्ये टाकावे लागते, उदाहरणार्थ वर्ष-तिमाही-महिना-दिवसकिंवा श्रेणी-उत्पादनकिंवा देश-शहर-ग्राहक इ. डेटा मॉडेल सारांश मध्ये, ही समस्या तुमची स्वतःची तयार करून सहजपणे सोडवली जाते पदानुक्रम - सानुकूल फील्ड संच.

पॉवर पिव्होट विंडोमध्ये, बटणासह चार्ट मोडवर स्विच करा चार्ट दृश्य टॅब होम पेज (मुख्यपृष्ठ — आकृती दृश्य), सह निवडा Ctrl इच्छित फील्ड आणि त्यावर उजवे-क्लिक करा. संदर्भ मेनूमध्ये कमांड असेल पदानुक्रम तयार करा (पदानुक्रम तयार करा):

तयार केलेल्या पदानुक्रमाचे नाव बदलले जाऊ शकते आणि आवश्यक फील्ड माऊससह ड्रॅग केले जाऊ शकते, जेणेकरुन नंतर एका हालचालीमध्ये ते सारांशमध्ये टाकले जाऊ शकतात:

फायदा 5: सानुकूल स्टॅन्सिल

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

हे करण्यासाठी, टॅबवर मुख्य सारणी विश्लेषण ड्रॉप डाउन सूचीमध्ये फील्ड, आयटम आणि सेट संबंधित आदेश आहेत (विश्लेषण - फील्ड्स, आयपदे आणि संच — पंक्ती/स्तंभ आयटमवर आधारित संच तयार करा):

उघडणार्‍या विंडोमध्ये, तुम्ही निवडकपणे कोणत्याही घटकांची स्थिती काढून टाकू शकता, जोडू शकता किंवा बदलू शकता आणि परिणामी सेट नवीन नावाखाली जतन करू शकता:

सर्व तयार केलेले संच एका वेगळ्या फोल्डरमध्ये PivotTable फील्ड पॅनेलमध्ये प्रदर्शित केले जातील, तेथून ते कोणत्याही नवीन PivotTable च्या पंक्ती आणि स्तंभ भागात मुक्तपणे ड्रॅग केले जाऊ शकतात:

फायदा 6: टेबल आणि स्तंभ निवडकपणे लपवा

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

पिव्होटटेबल फील्ड सूची उपखंडातून लपवलेला स्तंभ किंवा सारणी अदृश्य होईल. जर तुम्हाला वापरकर्त्यापासून काही सहाय्यक स्तंभ (उदाहरणार्थ, संबंध निर्माण करण्यासाठी गणना केलेले किंवा मुख्य मूल्यांसह स्तंभ) किंवा संपूर्ण सारण्या लपविण्याची आवश्यकता असल्यास ते खूप सोयीचे आहे.

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

तुम्ही नियमित पिव्होट टेबलमधील व्हॅल्यू एरियामधील कोणत्याही सेलवर डबल-क्लिक केल्यास, एक्सेल वेगळ्या शीटवर या सेलच्या गणनेमध्ये सामील असलेल्या स्त्रोत डेटाच्या तुकड्याची प्रत प्रदर्शित करते. ही एक अतिशय सुलभ गोष्ट आहे, ज्याला अधिकृतपणे ड्रिल-डाउन म्हणतात (ते सहसा "अयशस्वी" म्हणतात).

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

त्यानंतर, वर्तमान मूल्य (मॉडेल = एक्सप्लोरर) फिल्टर क्षेत्रात जाईल आणि सारांश कार्यालयांद्वारे तयार केला जाईल:

अर्थात, अशी प्रक्रिया अनेक वेळा पुनरावृत्ती केली जाऊ शकते, सतत आपल्या डेटामध्ये आपल्याला स्वारस्य असलेल्या दिशेने शोधत रहा.

फायदा 8: पिव्होटला क्यूब फंक्शन्समध्ये रूपांतरित करा

तुम्ही डेटा मॉडेलसाठी सारांशातील कोणताही सेल निवडल्यास आणि नंतर टॅबवर निवडा मुख्य सारणी विश्लेषण आदेश OLAP टूल्स - सूत्रांमध्ये रूपांतरित करा (विश्लेषण — OLAP टूल्स — सूत्रांमध्ये रूपांतरित करा), नंतर संपूर्ण सारांश आपोआप सूत्रांमध्ये रूपांतरित होईल. आता पंक्ती-स्तंभ क्षेत्रातील फील्ड मूल्ये आणि मूल्य क्षेत्रातील परिणाम विशेष घन कार्ये वापरून डेटा मॉडेलमधून पुनर्प्राप्त केले जातील: CUBEVALUE आणि CUBEMEMBER:

तांत्रिकदृष्ट्या, याचा अर्थ असा आहे की आता आम्ही सारांश हाताळत नाही, परंतु सूत्रांसह अनेक सेल वापरत आहोत, म्हणजे आम्ही आमच्या अहवालासह कोणतेही परिवर्तन सहजपणे करू शकतो जे सारांशमध्ये उपलब्ध नाहीत, उदाहरणार्थ, मध्यभागी नवीन पंक्ती किंवा स्तंभ घाला. अहवालाच्या, सारांशाच्या आत कोणतीही अतिरिक्त गणना करा, त्यांना कोणत्याही इच्छित पद्धतीने व्यवस्था करा, इ.

त्याच वेळी, स्त्रोत डेटासह कनेक्शन, अर्थातच, राहते आणि भविष्यात जेव्हा स्त्रोत बदलतात तेव्हा ही सूत्रे अद्यतनित केली जातील. सौंदर्य!

• पॉवर पिव्होट आणि पॉवर क्वेरीसह मुख्य सारणीमध्ये योजना-तथ्य विश्लेषण
• मल्टीलाइन हेडरसह मुख्य सारणी
• Power Pivot वापरून Excel मध्ये डेटाबेस तयार करा