डेटा मॉडल का उपयोग करके VLOOKUP से बचें। तो, आपके पास दो तालिकाएँ हैं जिन्हें पिवट तालिका करने से पहले VLOOKUP के साथ जुड़ने की आवश्यकता है। यदि आपके पास Excel 2013 या Windows पीसी पर नया है, तो अब आप इसे बस और आसानी से कर सकते हैं।
कहें कि आपके पास उत्पाद, ग्राहक और बिक्री की जानकारी वाला डेटा सेट है।
आईटी विभाग वहां सेक्टर लगाना भूल गया। यहां एक लुकअप टेबल है जो ग्राहक को सेक्टर में मैप करती है। VLOOKUP के लिए समय सही है?
यदि आपके पास Excel 2013 या Excel 2016 है, तो इन डेटा सेटों में शामिल होने के लिए VLOOKUP करने की कोई आवश्यकता नहीं है। Excel के इन दोनों संस्करणों ने Power Pivot इंजन को कोर Excel में शामिल किया है। (आप Excel 2010 के लिए पॉवर पिव्ट ऐड-इन का उपयोग करके भी ऐसा कर सकते हैं, लेकिन कुछ अतिरिक्त चरण हैं।)
मूल डेटा सेट और लुकअप टेबल दोनों में, होम, फॉर्मेट को टेबल के रूप में उपयोग करें। तालिका उपकरण टैब पर, तालिका को तालिका 1 से कुछ सार्थक नाम दें। मैंने डेटा और सेक्टर का उपयोग किया है।
डेटा तालिका में एक सेल का चयन करें। इंसर्ट, पिवट टेबल चुनें। एक्सेल 2013 में शुरू करने के लिए, एक अतिरिक्त बॉक्स है इस डेटा को उस डेटा मॉडल में जोड़ें जिसे आपको ठीक क्लिक करने से पहले चुनना चाहिए।
डेटा तालिका से फ़ील्ड्स के साथ पिवट टेबल फ़ील्ड्स सूची दिखाई देती है। राजस्व चुनें। क्योंकि आप डेटा मॉडल का उपयोग कर रहे हैं, लिस्ट में सबसे ऊपर एक नई लाइन दिखाई देती है, जो एक्टिव या ऑल की पेशकश करती है। सभी पर क्लिक करें।
हैरानी की बात है, PivotTable फ़ील्ड्स सूची कार्यपुस्तिका में अन्य सभी तालिकाओं को प्रस्तुत करती है। यह ग्राउंडब्रेकिंग है। आपने अभी तक VLOOKUP नहीं किया है। सेक्टर तालिका का विस्तार करें और सेक्टर चुनें। दो चीजें होती हैं जो आपको चेतावनी देती हैं कि समस्या है।
सबसे पहले, धुरी तालिका सभी कोशिकाओं में समान संख्या के साथ दिखाई देती है।
शायद अधिक सूक्ष्म चेतावनी एक पीत बॉक्स है जो PivotTable फ़ील्ड्स सूची के शीर्ष पर दिखाई देता है, यह दर्शाता है कि आपको एक संबंध बनाने की आवश्यकता है। बनाएँ चुनें। (यदि आप एक्सेल 2010 या 2016 में हैं, तो ऑटो-डिटेक्ट के साथ अपना भाग्य लें।)
संबंध बनाएँ संवाद में, आपके पास चार ड्रॉपडाउन मेनू हैं। तालिका के तहत डेटा चुनें, ग्राहक कॉलम के तहत (विदेशी), और संबंधित तालिका के अंतर्गत क्षेत्र। पॉवर धुरी स्वचालित रूप से संबंधित कॉलम (प्राथमिक) के तहत मिलान कॉलम में भर जाएगी। ओके पर क्लिक करें।
परिणामी धुरी तालिका मूल डेटा और लुकअप तालिका का एक मैशप है। कोई VLOOKUPs की आवश्यकता नहीं है।
वीडियो देखेंा
- Excel 2013 में, पिवट टेबल संवाद डेटा मॉडल प्रदान करता है
- यह Power Pivot Engine के लिए कोड शब्द है
- डेटा मॉडल का उपयोग करने के लिए, कार्यपुस्तिका में प्रत्येक तालिका से एक Ctrl + T तालिका बनाएं
- पहली तालिका से एक धुरी तालिका बनाएँ
- पिवट टेबल फील्ड सूची में, सक्रिय से सभी में बदलें
- लुकअप टेबल से एक फ़ील्ड चुनें
- या तो संबंध बनाएं या ऑटो-डिटेक्ट
- 2013 में ऑटो-डिटेक्ट नहीं था
- सामान्य रूप से पावर पिवट का सुझाव देने के लिए कॉलिन माइकल और एलेजांद्रो क्विकेनो को धन्यवाद।
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2014 - VLOOKUP को हटा दें!
इस संपूर्ण पुस्तक को पॉडकास्टिंग करते हुए, प्लेलिस्ट के लिए शीर्ष-दाएं कोने में "i" पर क्लिक करें!
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं, यह वास्तव में डेटा मॉडल के साथ VLOOKUP को खत्म करना कहलाता है! अब मैं क्षमा चाहता हूँ, यह एक्सेल २०१३ और नया है, यदि आप २०१० के एक्सेल में वापस आ गए हैं, तो आपको पॉवर पिवट ऐड-इन डाउनलोड करना होगा, २०१० में कौन सा कोर्स वापस करना है। इसलिए हमारे पास जो है वह हमारे पास है मुख्य डेटा सेट, यहां एक ग्राहक क्षेत्र है, और फिर मेरे पास एक छोटी सी तालिका है जो ग्राहक को सेक्टर में मैप करती है, मुझे सेक्टर द्वारा कुल राजस्व बनाने की आवश्यकता है, है ना? यह एक VLOOKUP है, बस एक VLOOKUP करें, लेकिन अरे, Excel 2013 के लिए धन्यवाद, हमें एक VLOOKUP नहीं करना है! मैंने इन दोनों को एक तालिका में बनाया, और टेबल टूल्स, डिज़ाइन पर, मैं तालिकाओं का नाम बदल देता हूं, मैं इसे एक सेक्टर कहता हूं, और मैं इसे एक डेटा कहता हूं, इसे तालिका में बनाने के लिए, बस एक सेल चुनें, Ctrl + दबाएं टी। इसलिए यदि हमारे पास कुछ हेडिंग और कुछ नंबर हैं, जब आप Ctrl + T दबाते हैं,वे पूछते हैं "आपकी तालिका के लिए डेटा कहां है?", मेरी तालिका में हेडर हैं, और फिर वे इसे टेबल 3 कहते हैं, आप इसे कुछ और कहते हैं। ठीक है, यह है कि मैंने उन दो तालिकाओं को कैसे बनाया, मैं इस तालिका से छुटकारा पाने जा रहा हूं, ठीक है।
तो इस ट्रिक को काम करने के लिए, सभी डेटा को टेबल में रहना होगा। हम सम्मिलित करें टैब पर जाते हैं, PivotTable चुनें, और नीचे दाईं ओर नीचे, इस डेटा को डेटा मॉडल में जोड़ें। यह बहुत सहज लगता है, है ना? फ्लैशिंग पॉइंट जैसा कुछ भी नहीं है जो कह रहा है कि "अरे, यह आपको आश्चर्यजनक चीजें करने देगा!" और जो वे यहां कह रहे हैं, जो वे कहने की कोशिश नहीं कर रहे हैं वह है- ओह, वैसे, एक्सेल 2013 की हर कॉपी में इसके पीछे पॉवर पिवट इंजन है। आप जानते हैं, यदि आप Office 365 में हैं, तो आप महीने में $ 10 का भुगतान कर रहे हैं, और वे चाहते हैं कि आप Power Pivot प्राप्त करने के लिए $ 12 या $ 15 एक महीने का भुगतान करें, अतिरिक्त दो या पाँच रुपये। ठीक है, हे, शा, बताओ, तुम वास्तव में एक्सेल 2013 में पहले से ही अधिकांश पावर पिवट हैं। ठीक है, इसलिए मैं ठीक क्लिक करता हूं, डेटा मॉडल को लोड करने में थोड़ा अधिक समय लगता है, ठीक है, लेकिन यह ठीक है, और ठीक है यहाँ,PivotTable फ़ील्ड्स में, मेरे पास सभी फ़ील्ड्स की एक सूची है। इसलिए, मैं निश्चित रूप से रेवेन्यू दिखाना चाहता हूं, लेकिन एक्टिव और ऑल के साथ अलग क्या है। जब मैं सभी का चयन करता हूं, तो मुझे कार्यपुस्तिका में सभी तालिकाएं मिलती हैं। ठीक है, इसलिए मैं सेक्टरों में जाता हूं, और मैंने कहा कि मैं रीव्स क्षेत्र में सेक्टर डालना चाहता हूं। अब, शुरू में, रिपोर्ट गलत होने जा रही है, 6.7 मिलियन को पूरे रास्ते देखें, और यहाँ पर यह पीला चेतावनी कहेंगे कि आपको एक रिश्ता बनाना है।और यहाँ पर यह पीला चेतावनी कहेगा कि आपको एक रिश्ता बनाना है।और यहाँ पर यह पीला चेतावनी कहेगा कि आपको एक रिश्ता बनाना है।
अब ठीक है, 2010 में पावर पिवट के साथ, यह बस होगा, इसने AutoDetect की पेशकश की, 2013 में उन्होंने AutoDetect को बाहर कर दिया, और 2016 में वे AutoDetect को वापस लाए, ठीक है? मुझे आपको दिखाना चाहिए कि क्रेट कैसा दिखता है, लेकिन जब मैं इस क्रिएट बटन पर क्लिक करता हूं, तो ओह, हाँ, यह ठीक है, अच्छा है। तो हमारे पहले टेबल डेटा से, मेरे पास संबंधित टेब सेक्टर से कस्टमर नामक फ़ील्ड है, मेरे पास कस्टमर नामक फ़ील्ड है, और फिर आप ठीक पर क्लिक करते हैं, ठीक है। लेकिन मुझे सिर्फ यह दिखाने दीजिए कि ऑटोडेट कितना शांत है, अगर आप 2016 में होते हैं, तो, उन्होंने यह पता लगाया, यह कितना सही है? आपको VLOOKUP के बारे में चिंता करने की आवश्यकता नहीं है, और अल्पविराम अंत में आता है, यदि VLOOKUP आपके सिर को चोट पहुँचाता है, तो आप डेटा मॉडल से प्यार करने जा रहे हैं। उन दो तालिकाओं को ले लिया, उन्हें एक साथ जोड़ दिया, आप जानते हैं, जैसे कि एक्सेस करेगा, मुझे लगता है, और एक धुरी तालिका बनाई, बिल्कुल आश्चर्यजनक।इसलिए अगली बार जब आप दो तालिकाओं के बीच VLOOKUP करना है तो डेटा मॉडल की जाँच करें। अच्छी तरह से यह और अन्य सभी 40 युक्तियां पुस्तक में हैं, शीर्ष दाएं कोने पर "i" पर क्लिक करें। आप पुस्तक खरीद सकते हैं, वीडियो की इस पूरी श्रृंखला के लिए एक संपूर्ण क्रॉस रेफ़रेंस है, अगस्त के सभी, पूरे सितंबर, बिल्ली, हम अक्टूबर में भी ले जा सकते हैं पूरी बात करने के लिए।
ठीक है, आज पुन: उपयोग करें: Excel 2013 में शुरू, पिवट टेबल संवाद डेटा मॉडल नामक कुछ प्रदान करता है, यह पावर पिवट इंजन के लिए कोड शब्द है। अपनी पिवट टेबल बनाने से पहले, प्रत्येक वर्कबुक से एक टेबल बनाने के लिए Ctrl + T करें, मैंने प्रत्येक को नाम देने के लिए अतिरिक्त समय लिया। पहली तालिका से एक पिवट तालिका बनाएँ, और फिर फ़ील्ड सूची में, ऊपर जाएं और सक्रिय से सभी में बदलें। लुकअप टेबल से एक फ़ील्ड चुनें, और फिर यह आपको चेतावनी देगा कि आपको या तो संबंध बनाना है, या ऑटोडेट, 2013 में, आपको क्रिएट पर क्लिक करना होगा। लेकिन यह क्या है, इसे बनाने के लिए 4 क्लिक करें, 5 यदि आप ओके बटन की गिनती करते हैं, तो वास्तव में, वास्तव में करना आसान है।
ठीक है, कॉलिन, माइकल, और एलेजांद्रो क्विकेनो ने पुस्तकों के लिए सामान्य रूप से पावर पिवट का सुझाव दिया, उनके लिए धन्यवाद, द्वारा रोकने के लिए धन्यवाद, हम आपको अगली बार एक और नेटकास्ट से देखेंगे!
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2014.xlsx
