बजट बनाम वास्तविक - एक्सेल टिप्स

विषय - सूची

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

बजट शीर्ष स्तर पर किया जाता है - क्षेत्र द्वारा उत्पाद लाइन द्वारा महीने तक राजस्व। वास्तविक समय के साथ धीरे-धीरे जमा होता है - चालान द्वारा चालान, लाइन आइटम द्वारा लाइन आइटम। छोटी बजट की फ़ाइल को वास्तविक डेटा से तुलना करना हमेशा के लिए एक दर्द रहा है। मुझे रॉब कोली उर्फ़ पॉवरप्रोप्रो.कॉम से यह ट्रिक बहुत पसंद है।

उदाहरण स्थापित करने के लिए, आपके पास 54-पंक्ति बजट तालिका है: प्रति उत्पाद प्रति क्षेत्र प्रति माह एक पंक्ति।

नमूना डेटा सेट

इनवॉइस फ़ाइल विस्तार स्तर पर है: इस वर्ष अब तक 422 पंक्तियाँ।

चालान विवरण देखें

दुनिया में कोई VLOOKUP नहीं है जो कभी भी आपको इन दो डेटा सेट से मेल खाने देगा। लेकिन, Power Pivot (Excel 2013 में डेटा मॉडल उर्फ) के लिए धन्यवाद, यह आसान हो जाता है।

आपको दो छोटे डेटा सेटों को लिंक करने के लिए छोटे छोटे तालिकाओं को बनाने की जरूरत है जिन्हें मैं "जॉइनर्स" कहता हूं। मेरे मामले में, उत्पाद, क्षेत्र और दिनांक दो तालिकाओं के बीच आम हैं। उत्पाद तालिका एक छोटी चार-कक्ष तालिका है। क्षेत्र के लिए Ditto। एक तालिका से डेटा की प्रतिलिपि बनाकर और निकालें डुप्लिकेट का उपयोग करके उनमें से प्रत्येक बनाएं।

जॉर्ज बर्लिन
जुड़ने वाले

दाईं ओर कैलेंडर तालिका वास्तव में बनाने के लिए कठिन थी। बजट डेटा में प्रति माह एक पंक्ति होती है, हमेशा महीने के अंत में गिरती है। इनवॉइस डेटा दैनिक तिथियां दिखाता है, आमतौर पर सप्ताह के दिनों में। इसलिए, मुझे दोनों डेटा सेट से दिनांक फ़ील्ड को एक एकल कॉलम में कॉपी करना था और फिर यह सुनिश्चित करने के लिए डुप्लिकेट हटा दें कि सभी तिथियों का प्रतिनिधित्व किया जाता है। मैं तब =TEXT(J4,"YYYY-MM")दैनिक तिथियों से एक मासिक कॉलम बनाता था।

यदि आपके पास पूर्ण पॉवर पिवट ऐड-इन नहीं है, तो आपको बजट तालिका से एक पिवट टेबल बनाने और डेटा मॉडल में इस डेटा को जोड़ने के लिए बॉक्स की जांच करने की आवश्यकता है।

डेटा मॉडल में जोड़ें

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

संबंध बनाएं संवाद

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

मुख्य बिंदु

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

परिणाम

यह काम करता है क्योंकि जॉइनर टेबल बजट और वास्तविक टेबल पर फ़िल्टर लागू करते हैं। यह एक सुंदर तकनीक है और यह दर्शाता है कि पावर पिवट केवल बड़े डेटा के लिए नहीं है।

वीडियो देखेंा

  • आपके पास एक छोटा टॉप-डाउन बजट डेटा सेट है
  • आप एक बॉटम-अप वास्तविक डेटा सेट की तुलना करना चाहते हैं
  • वास्तविक चालान चालान से आ सकते हैं
  • डेटा मॉडल आपको इन अलग-अलग आकार के डेटा सेटों की तुलना करने देगा
  • दोनों डेटा सेट को Ctrl + T तालिका में बनाएं
  • प्रत्येक पाठ फ़ील्ड के लिए जिसे आप रिपोर्ट करना चाहते हैं, एक योजक तालिका बनाएँ
  • मूल्यों की प्रतिलिपि बनाएँ और डुप्लिकेट निकालें
  • तिथियों के लिए, आप दोनों तालिकाओं से तिथियां शामिल कर सकते हैं और महीने के अंत में बदल सकते हैं
  • Joiners को Ctrl + T टेबल बना लें
  • वैकल्पिक लेकिन सभी पांच तालिकाओं के नाम के लिए उपयोगी
  • बजट से एक धुरी तालिका बनाएं और डेटा मॉडल चुनें
  • मूल तालिकाओं से बजट और वास्तविक का उपयोग करके एक धुरी तालिका बनाएं
  • अन्य सभी फ़ील्ड्स को जॉइनर टेबल से आना चाहिए
  • उत्पाद द्वारा स्लाइसर जोड़ें
  • बजट से लेकर जॉइनर्स तक तीन रिश्ते बनाएं
  • वास्तविक से जुड़ने वालों के लिए तीन रिश्ते बनाएँ
  • कल: पावर पिवट और डैक्स फॉर्मूले के साथ संबंध बनाना कितना आसान है

वीडियो ट्रांसक्रिप्ट

पॉडकास्ट से एक्सेल सीखें, एपिसोड 2016 - टॉप-डाउन बजट बनाम बॉटम-अप वास्तविक!

अरे, मैं इस पूरी किताब को पॉडकास्ट कर रहा हूं, शीर्ष-दाएं हाथ के कोने पर "i" पर क्लिक करें और प्लेलिस्ट का पालन करें।

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

ठीक है, यहाँ हमारे पास क्या है, बाईं ओर हमारे पास एक बजट है, यह बजट है, यह शीर्ष-स्तर पर, ऊपर-नीचे, प्रत्येक उत्पाद लाइन के लिए, प्रत्येक क्षेत्र के लिए, प्रत्येक महीने के लिए किया गया है, एक बजट है । यहां कई रिकॉर्ड नहीं, दाएं हाथ की ओर 55 की गिनती, हम वास्तविक से इसकी तुलना करने की कोशिश कर रहे हैं। वास्तविक इनवॉइस रजिस्टर से आ रहे हैं, इसलिए हमारे पास क्षेत्र, उत्पाद और राजस्व हैं, लेकिन वे व्यक्तिगत चालान हैं, यहां बहुत अधिक डेटा हैं, हम पहले से ही वर्ष के आधे रास्ते में हैं, और मेरे पास पहले से ही 423 रिकॉर्ड हैं। ठीक है, तो आप इन 55 को इन 423 में कैसे मैप करते हैं? VLOOKUP के साथ ऐसा करना कठिन हो सकता है, आपको पहले संक्षेप में प्रस्तुत करना होगा, लेकिन शुक्र है कि एक्सेल 2013 में, डेटा मॉडल वास्तव में, वास्तव में आसान बनाता है। इस छोटी सी मेज के साथ संवाद करने के लिए हमें इस बड़े पैमाने पर मेज की अनुमति देने की आवश्यकता क्या है, मैं उन्हें जॉइनर कहता हूं।छोटे तालिकाओं, उत्पाद, क्षेत्र और कैलेंडर, हम इन तीन तालिकाओं के लिए बजट में शामिल होने जा रहे हैं, हम इन तीन तालिकाओं के लिए वास्तविक में शामिल होने जा रहे हैं, और चमत्कारी रूप से धुरी तालिका काम करेगी। ठीक है, तो यहाँ है कि हम ऐसा कैसे करते हैं।

सबसे पहले मुझे जुड़ने वालों को बनाने की आवश्यकता है, इसलिए मैं कॉलम ए से इस उत्पाद क्षेत्र को लेता हूं, और मैं इसे कॉलम एफ पर कॉपी करता हूं, और फिर डेटा, डुप्लिकेट को हटा देता हूं, ठीक पर क्लिक करता हूं, और हम एक छोटी सी मेज के साथ छोड़ देते हैं, 1 शीर्ष 3 पंक्तियाँ। क्षेत्र के लिए एक ही बात, क्षेत्रों को ले लो, Ctrl + C, कॉलम G, Paste, Remove Duplicates पर जाएं, ठीक पर क्लिक करें, 3 पंक्तियों 1 शीर्ष लेख, ठीक। अब तारीखों के लिए, तिथियां समान नहीं हैं, ये महीने की समाप्ति तिथियां हैं, वे वास्तव में महीने की समाप्ति तिथियों के रूप में संग्रहीत हैं, और ये सप्ताह के दिन हैं। मैं दोनों सूचियों को लेने जा रहा हूँ, Ctrl + C दूसरी सूची और इसे यहाँ पेस्ट करें, Ctrl + V, फिर मैं छोटी सूची लेने जा रहा हूँ, इसे कॉपी करें, और इसे नीचे पेस्ट करें, ठीक है। और यह वास्तव में कष्टप्रद है कि, भले ही ये तारीखों के रूप में संग्रहीत हैं, वे महीनों के रूप में दिखाई दे रहे हैं, और डुप्लिकेट्स उन्हें समान नहीं देखेंगे।इसलिए इससे पहले कि मैं डुप्लिकेट हटाएं, मुझे इसे छोटी तिथि में बदलना होगा। डेटा, डेटा, डुप्लिकेट निकालें, ठीक पर क्लिक करें, और फिर इसे काम करने के लिए यहां से थोड़ा सा चुनें।

ठीक है, अब मैं दैनिक तिथि तक रिपोर्ट नहीं करना चाहता, इसलिए मैं यहां एक कॉलम जोड़ने जा रहा हूं, एक लुकअप कॉलम जो मंथ कहता है, और यह उस तारीख के बराबर EOMONTH होगा, 0, जो हमें बाहर कर देगा माह की समाप्ति। यह लघु तिथि के रूप में प्रारूपित करेगा, और इसे नीचे कॉपी करेगा, ठीक है। अब, हमें इनमें से प्रत्येक को Ctrl + T तालिका में बनाने की आवश्यकता है, इसलिए यहाँ से Ctrl + T, मेरी तालिका में हेडर, सुंदर हैं। छोटे लोग, यह महसूस नहीं करते हैं कि वे वहां हेडर हैं, इसलिए हमें यह सुनिश्चित करना होगा कि निशान और Ctrl + T, ठीक है, और वे इन तालिकाओं को Table1, Table2, Table3, वास्तव में उबाऊ नाम कहते हैं, है ना? इसलिए मैं इनका नाम बदलने जा रहा हूं और इसे BudTable, ProdTable, RegTable, My CalTable, और फिर ActTable, ठीक कहूंगा।

हम पहले तालिका से शुरू करते हैं, और जिस तरह से हम आज पॉवरपिव ​​का उपयोग नहीं करने जा रहे हैं, हम डेटा मॉडल के साथ यह सब करने जा रहे हैं। तो, एक्सेल 2013 या नया, आपके पास यह सम्मिलित है, PivotTable, हम "डेटा मॉडल में यह डेटा जोड़ें" के लिए बॉक्स की जांच करने जा रहे हैं, ठीक पर क्लिक करें, और हम जादू के साथ हमारे क्षेत्र की सूची प्राप्त करते हैं, जो देता है। मुझे कार्यपुस्तिका में सभी पाँच तालिकाओं, वास्तविक, बजट, कैलेंडर, उत्पाद, क्षेत्र से चुनें। ठीक है, इसलिए संख्याएँ बजट तालिका से आने वाली हैं, मैं बजट को वहाँ रखूँगा, और वास्तविक तालिका से मैं वास्तविक को वहाँ रखूँगा, लेकिन फिर यहाँ बाकी की धुरी तालिका के लिए बात है। कोई भी अन्य टेक्स्ट फ़ील्ड जो हम पंक्ति क्षेत्र या स्तंभ क्षेत्र में या स्लाइसर के रूप में डालने जा रहे हैं, उन्हें जॉइनर्स से आना होगा, उन्हें टेबल के बीच उन तालिकाओं से आना होगा।

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

और इसलिए मैं पुराने ढंग का उपयोग करने जा रहा हूं, यहां संवाद बनाएं, जहां हमारे पास बाईं ओर बजट तालिका है, और हम क्षेत्र क्षेत्र का उपयोग करने जा रहे हैं और उस क्षेत्र तालिका से संबंधित हैं, क्षेत्र क्षेत्र । ठीक है, 1/6 बनाए जाते हैं। मैं बनाएँ, फिर से चुनें बजट तालिका से हम उत्पाद पर जाते हैं, और फिर उत्पाद तालिका से उस उत्पाद को लिंक करते हैं, ठीक पर क्लिक करें। बजट तालिका दिनांक फ़ील्ड से, हम कैलेंडर तालिका में जाते हैं, और भाग्य फ़ील्ड, ठीक पर क्लिक करें, हम आधे रास्ते में हैं, ठीक है। वास्तविक तालिका से, हम क्षेत्र में जाते हैं, क्षेत्र तालिका में, ठीक पर क्लिक करें, वास्तविक तालिका से उत्पाद तक, और वास्तविक तालिका से कैलेंडर तक। मैं वास्तव में वैल्यू लेने जा रहा हूं और इसे साइड से नीचे करूंगा, ठीक है। डिज़ाइन, रिपोर्ट लेआउट, तबीयत फ़ॉर्म में दिखाएँ जो मुझे पसंद है वह देखने के लिए, सभी आइटम लेबल दोहराएँ, ठीक है,यह बिल्कुल आश्चर्यजनक है! अब हमारे पास यह छोटी सी मेज है, सैकड़ों अभिलेखों की इस तालिका में 50-कुछ रिकॉर्ड हैं, और हमने डेटा मॉडल के लिए एक एकल पिवट तालिका बनाई है। प्रत्येक के लिए जहां हम बजट देख सकते हैं, हम राजस्व देख सकते हैं, यह क्षेत्र से टूट गया है, यह महीना से टूट गया है, और यह उत्पाद द्वारा स्लाव है।

अब यह अवधारणा मुझे रॉब कोली से मिली जो पावर पिवट प्रो चलाते हैं, और रॉब ने बहुत सारी किताबें बनाई हैं, उनका नवीनतम "पॉवर पिवट और पॉवर बीआई" है। मुझे लगता है कि यह वास्तव में "पावर पिवट कीमिया" पुस्तक में था, यह वह है जिसे मैंने इसे देखा था और मैंने कहा "ठीक है, भले ही मेरे पास पावर पिवट के माध्यम से रिपोर्ट करने के लिए लाखों पंक्तियां न हों, यह वह है जो एक है मेरे जीवन में एक बड़ा अंतर आया है, बेमेल आकारों के दो डेटा सेट, और उन दोनों से रिपोर्ट करने की आवश्यकता है। ” खैर यह उदाहरण और कई अन्य लोग इस पुस्तक में हैं, मुझे अंततः पूरी पुस्तक पॉडकास्ट मिलेगी, ऐसा लगता है कि इसे ढाई महीने लगने वाले हैं। लेकिन आप आज पूरी किताब प्राप्त कर सकते हैं, उसी समय, वहां जाएं, पुस्तक खरीदें, ई-पुस्तक के लिए $ 10, प्रिंट पुस्तक के लिए $ 25, और आप एक ही बार में उन सभी युक्तियों को प्राप्त कर सकते हैं।

ठीक है, वास्तव में एक लंबा एपिसोड यहाँ: हमारे पास एक छोटा सा टॉप-अप बजट है और वास्तविक रूप से एक बॉटम है, वे अलग-अलग आकार के हैं, लेकिन एक्सेल 2013 में डेटा मॉडल का उपयोग कर रहे हैं … और यदि आप 2010 में हैं, तो आप कर सकते हैं सिद्धांत रूप में, पॉवर पिवट ऐड-इन प्राप्त करके ऐसा करें, और 2010 में इन सभी चरणों से गुजरें। दोनों डेटासेट को Ctrl + T तालिका में बनाएं, और फिर जिस चीज़ पर आप रिपोर्ट करना चाहते हैं, उसके लिए अपनी तालिका में शामिल हों। पंक्ति लेबल, या स्तंभ लेबल, या स्लाइसर, इसलिए उन मानों को कॉपी करें और तिथियों के लिए डुप्लिकेट हटा दें। मैंने वास्तव में दोनों तालिकाओं से मान लिया, क्योंकि प्रत्येक में कुछ अनोखे मूल्य थे, और फिर मैंने वहां बाहर निकलने के लिए, उन जॉइनर तालिकाओं को नियंत्रित तालिकाओं को बनाने के लिए EOMONTH का उपयोग किया। यह वैकल्पिक है, लेकिन मैंने सभी 5 तालिकाओं का नाम दिया है, क्योंकि जब आप उन संबंधों को सेट कर रहे होते हैं तो आसान होते हैं, बजाय कि टेबल 1 कहा जाए।तालिका 2, तालिका 3।

और इसलिए, बजट तालिका से प्रारंभ करें, सम्मिलित करें, PivotTable, डेटा मॉडल के लिए बॉक्स की जांच करें, और फिर बजट और वास्तविक का उपयोग करके एक धुरी तालिका का निर्माण करें। बाकी सब कुछ योजक तालिका से आता है, इसलिए पंक्ति और स्तंभ क्षेत्र में क्षेत्र और महीना, उत्पाद तालिका से स्लाइसर आए। और फिर हमें बजट से जुड़ने वालों से 3 रिश्ते बनाने थे, वास्तविक से जुड़ने वाले से 3 रिश्ते, और हमारे पास एक अद्भुत पिवट टेबल है। अब कल हम पावर पिवट टैब का उपयोग करने और कुछ अतिरिक्त गणनाएँ करने पर विचार करेंगे। तो यह सब संभव है, यह तब है जब हम एक परिकलित फ़ील्ड सम्मिलित करना चाहते हैं, वह यह है कि जब आपको Office 365 का प्रो प्लस संस्करण प्राप्त करने के लिए अतिरिक्त $ 2 प्रति माह का भुगतान करना होगा।

खैर हे, इस टिप के लिए पावर पिवट प्रो से रॉब कोली के लिए धन्यवाद, और द्वारा रोकने के लिए धन्यवाद, हम आपको अगली बार एक और नेटकास्ट से देखेंगे!

फ़ाइल डाउनलोड करें

यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2016.xlsx

दिलचस्प लेख...