यह फायर फाइटर बजट की समस्या है। फायरहाउस में लोग एक्सेल में अपने बजट गलत कर रहे हैं। एक अद्भुत पावर क्वेरी परिवर्तन समाधान प्रदान करता है।
वीडियो देखेंा
- स्टीव को उन संख्याओं को जोड़ना होगा जो एक पाठ कॉलम में दर्ज किए गए हैं
- प्रत्येक सेल में कई लाइनें होती हैं, जिन्हें alt = "+ Enter द्वारा अलग किया जाता है
- उन पंक्तियों को पंक्तियों में विभाजित करने की आवश्यकता है, फिर प्रत्येक सेल के मध्य से डॉलर की राशि को पार्स करें
- लागत केंद्र द्वारा संक्षेप
- लुकअप टेबल बनाएं
- रिक्त पंक्ति में त्रुटियों को अनदेखा करने के लिए IFNA का उपयोग करके लुकअप टेबल से योग प्राप्त करें
- बोनस: जब वे सेल बदलते हैं तो वर्कशीट को अपडेट करने के लिए एक इवेंट मैक्रो जोड़ें।
वीडियो ट्रांसक्रिप्ट
एक्सेल, पॉडकास्ट एपिसोड 2160 से सीखें: SUM डेटा जो बीन अल्ट + एंटर किया गया है।
अरे। नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं। मैं झूठ नहीं बोल रहा। मुझे किसी ऐसे व्यक्ति से प्रश्न मिला है जिसके पास डेटा - बजट डेटा है - जो इस तरह दिखता है। अब, मैं यहां नकली शब्द डालता हूं ताकि हमारे पास उनके बजट की जानकारी न हो, लेकिन लेखा विभाग में व्यक्ति का नया, एक कंपनी में चला गया, और यह कंपनी वर्षों से इस तरह से अपने बजट का काम कर रही है। वे बजट का लेखा-जोखा नहीं कर रहे हैं, वे लोग लाइन में हैं, लेकिन यह वह तरीका है जो वे कर रहे हैं, और वह उन्हें बदलने के लिए नहीं मिल सकता है। तो, यहाँ हमारा लक्ष्य है। वह कहते हैं कि यह बजट में वर्ड टाइप करने जितना ही बुरा है।
खैर, लगभग, लेकिन सौभाग्य से, बिजली क्वेरी के लिए धन्यवाद, यह हमारी समस्या को बचाने वाला है। यहाँ हमारा लक्ष्य है। यहाँ पर हर COST CENTER के लिए, हम उन सभी नंबरों की कुल रिपोर्ट करना चाहते हैं। तो, वहाँ खर्च नाम, एक -, नियमित रूप से एक, फिर एक $ संकेत है, और फिर, जीवन को रोचक बनाने के लिए, हर एक बार एक के बाद एक यादृच्छिक नोट; हर समय नहीं, बस कुछ समय के लिए। प्रत्येक के बीच खाली पंक्ति। टन और टन डेटा।
तो, यहाँ मैं क्या करने जा रहा हूँ। मैं बहुत नीचे, बहुत आखिरी सेल पर आने वाला हूँ, मैं शीर्षकों सहित इस सामान का चयन करने जा रहा हूँ। मैं एक NAME बनाने जा रहा हूँ। मैं इसे MyData कहने जा रहा हूं। MyData, उस तरह, ठीक है? ठीक है। अब हम पावर क्वेरी का उपयोग करने जा रहे हैं, जो कि 2010 या 2013 में मुफ़्त है, जो 2016 और 2016 में 365 कार्यालय में निर्मित है। यह एक टेबल या रेंज से आने वाली है। ठीक है। पहली बात, कभी भी हमारे पास COLUMN A में वे रिक्त स्थान होते हैं, जिन सभी NULLS से हम छुटकारा चाहते हैं। इसलिए मैं NULL को अनचेक करने जा रहा हूं। बहुत बढ़िया। अच्छा जी। वास्तव में, इस डेटा में, डेटा के इस संस्करण में, क्योंकि मैं एक VLOOKUP बनाने जा रहा हूं, हमें इस कॉलम की आवश्यकता नहीं है। इसलिए, मैं राइट-क्लिक करने जा रहा हूं और उस कॉलम से छुटकारा पा सकता हूं, इसलिए REMOVE कॉलम।
ठीक है। अब, यहाँ पर जादू करने वाला जादू होने वाला है। इस कॉलम को चुनें, एक DELIMITER द्वारा SPLIT COLUMN, और हम निश्चित रूप से उन्नत में जाने वाले हैं। सीमांकक एक विशेष चरित्र होने जा रहा है और हम परिसीमन की प्रत्येक घटना को विभाजित करने जा रहे हैं। तो, यहाँ, मुझे लगता है कि वे वास्तव में पहले ही इसका पता लगा चुके हैं क्योंकि मैंने इसका विस्तार किया है, लेकिन मैं आपको दिखाने जा रहा हूं। INSERT विशेष प्रभार। मैं यह कह रहा हूँ कि यह एक लाइन फ़ीड है, ठीक है, इसलिए, लाइन फ़ीड की प्रत्येक घटना पर, और मैं इन दिनों में जा रहा हूँ। ठीक है, और यहाँ क्या होने जा रहा है, 1, 2, 3, 4, 5, मैं 5 पंक्तियाँ लेने जा रहा हूँ या मैं 1001 कहने जा रहा हूँ, लेकिन, प्रत्येक पंक्ति में, यह एक अलग है इस सेल से लाइन। यह आश्चर्यजनक है। 1, 2, 3, 4, 5, 1001 है। ठीक है। अब हमें सिर्फ इस बुरे लड़के को बाहर निकालने की जरूरत है। ठीक है,इसलिए, उस कॉलम को चुनें, SPLIT COLUMN BY A DELIMITER। इस बार, एक सीमांकक $ संकेत होने जा रहा है। यह सही है, एक बार, पहले $ संकेत पर हम पाते हैं, बस के मामले में भविष्य के हिस्से में एक $ साइन आउट है। हम SPLIT INT COLUMNS में जा रहे हैं। ओके पर क्लिक करें। ठीक है। तो, विवरण हैं। यहां हमारा पैसा है।
अब, मैं इसे स्पिक में विभाजित करने जा रहा हूं। तो, इस कॉलम को चुनें, एक DELIMITER द्वारा SPUM REMOVE में जा रहे हैं। वास्तव में, इसकी आवश्यकता नहीं है क्योंकि मैं सिर्फ उस सभी सामान को प्राप्त करने की कोशिश कर रहा हूं, इसलिए मैं REMOVE करने जा रहा हूं।
अब, परिवर्तन। ग्रुप बाय COST CENTER, NEW COLUMN NAME को TOTAL कहा जाने वाला है, OPERATION SUM होने जा रहा है, और हम किस कॉलम में जा रहे हैं? विवरण ।2। सुंदर। ठीक क्लिक करें, ठीक है, और जो हम समाप्त करते हैं, उन सभी पंक्ति वस्तुओं के कुल के साथ COST केंद्र प्रति एक पंक्ति है। घर, बंद और लोड। यह शायद एक नई वर्कशीट डालने जा रहा है। मुझे उम्मीद है कि यह एक नया वर्कशीट सम्मिलित करता है, और यह करता है, और उस वर्कशीट को MYDATA_1 कहा जाता है। MYDATA_1
ठीक है। अब हम यहां मूल डेटा में वापस आने वाले हैं और इन चरणों को करते हैं। हमारे परिणामों में पहले एक, 1001 की VLOOKUP पर। यह एक परिपत्र संदर्भ स्थापित करने की तरह है, लेकिन यह हमें एक परिपत्र संदर्भ देने वाला नहीं है। , 2, FALSE। मुझे सटीक मैच चाहिए। ठीक है, लेकिन हम खाली कोशिकाओं के लिए ऐसा नहीं करना चाहते हैं। तो, मैं यह कहने जा रहा हूं, कि वास्तव में, चलो इसे पूरी तरह से कॉपी करते हैं। नियंत्रण + सी, हम जो कुछ भी प्राप्त कर रहे हैं उसे देखने के लिए नीचे जाएं। शायद हम N / As को प्राप्त कर रहे हैं और मैं IFNA के साथ इससे छुटकारा पा सकता हूं। हाँ, सुंदर, ठीक है। तो, चलो बस एन / ए एस से छुटकारा पाएं। यदि एन / ए, तो हम सिर्फ "" चाहते हैं। हम वहां कुछ भी नहीं चाहते हैं। नियंत्रण + ENTER। ठीक है। अब, यह कुल होना चाहिए। चलो देखते हैं कि क्या हम एक छोटी खोज कर सकते हैं और सिर्फ गणित कर सकते हैं। = 627.37 + 7264.25 + 6066।01 + 4010.66 + 9773.94, और कुल, 27742.23 है। भयानक गुस्सा। (= IFNA (VLOOKUP (A2, MyData_1,2, FALSE), ""))
अब, यहाँ सौदा है। इसलिए, हमारे पास उन लोगों की कतार है जो सामान बदल रहे हैं, ठीक है, और इसलिए हम कहते हैं कि वे गुजरते हैं और वे बजट को बदलते हैं, 40294.48, और वे यहां आते हैं और इसे एक को बदलकर 6000 कर देते हैं, और वे जोड़ते हैं एक नया, ALT + ENTER, SOMETHING - $ साइन, $ 1000 बस जोड़ा गया। ठीक है। अब, निश्चित रूप से, जब मैं ENTER दबाता हूं, तो यह संख्या, 40294.48, अद्यतन करने के लिए नहीं जा रही है, ठीक है, लेकिन हमें जो करना है वह डेटा टैब पर जाना है और हम सभी को पुनः लोड करना चाहते हैं। तो, 40294.48। घड़ी, घड़ी, घड़ी, घड़ी। सभी को रीफ्रेश करें। अद्भुत गुस्सा।
मुझे पावर क्वेरी पसंद है। पावर क्वेरी सबसे आश्चर्यजनक चीज है। यह डेटा, जो अनिवार्य रूप से एक सेल में केवल शब्द डेटा की तरह है, अब हमारे पास इसे अपडेट किया जा रहा है। आप शायद किसी प्रकार का एक मैक्रो भी बना सकते हैं जो कहता है कि हर बार जब कोई COLUMN C में कुछ बदलता है, तो हम आगे बढ़ते हैं और मैक्रो का उपयोग करके REFRESH ALL पर क्लिक करते हैं और बस उन परिणामों को लगातार, लगातार ताज़ा करते हैं।
क्या भयावह प्रश्न भेजा गया है। मुझे स्टीव के साथ बुरा लगता है, जिन्हें इससे निपटना है, लेकिन अब, ऑफिस 365 में पावर क्वेरी का उपयोग करते हुए या 2010 या 2013 के लिए डाउनलोड किया गया है, आपके पास इसे हल करने के लिए एक बहुत ही आसान तरीका है।
रुको। ठीक है, एक परिशिष्ट: चलो इसे और भी बेहतर बनाते हैं। इस शीट को DATA कहा जाता है और मैंने कार्यपुस्तिका को मैक्रो-सक्षम के रूप में सहेजा है, इसलिए xlsm। यदि आप xlsx हैं, तो xlsm के रूप में बचत करना न छोड़ें। ALT + F11। DATA नामक कार्यपुस्तिका को ढूंढें, डबल-क्लिक, शीर्ष बाएँ, कार्यपत्रक, और उसके बाद किसी भी समय हम कार्यपत्रक को बदलते हैं, और हम ACTIVEWORKBOOK.REFRESHALL, और फिर बंद करने के लिए जा रहे हैं, ठीक है, और अब इसे आज़माएँ। आइए कुछ संपादित करें। तो, हम उन रसभरी को लेंगे जो वर्तमान में 8,000 हैं और हम इसे 1000 में बदल देंगे, इसलिए हम 7000 से कम कर रहे हैं। जब मैं ENTER दबाता हूं, तो मैं देखना चाहता हूं कि 42,000 35,000 तक नीचे चले जाते हैं। आह। बहुत बढ़िया।
अच्छा हे। यह वह जगह है जहां मैं आमतौर पर आपसे मेरी पुस्तक खरीदने के लिए विनती करता हूं, लेकिन आज, मैं आपको अपने मित्रों की पुस्तक खरीदने के लिए कहने जा रहा हूं - केन पल्स और मिगुएल एस्कोबार - एम फॉर (डेटा) मोंकी है। पावर क्वेरी के बारे में मैंने जो कुछ भी सीखा, मैंने इस किताब से सीखा। यह एक अद्भुत पुस्तक है। उसकी जांच करो।
एपिसोड रैप-अप: स्टीव के पास संख्याएँ हैं जो एक टेक्स्ट कॉलम में दर्ज की गई हैं; प्रत्येक सेल में कई लाइनें, ALT + ENTER द्वारा अलग की जाती हैं; उन पंक्तियों को पंक्तियों में विभाजित करने की आवश्यकता है, फिर प्रत्येक सेल के मध्य से डॉलर की राशि को पार्स करें; COST CENTER द्वारा संक्षेप; एक लुकिंग टेबल का निर्माण; रिक्त पंक्ति में त्रुटियों को अनदेखा करने के लिए IFNA का उपयोग करते हुए लुकअप टेबल से योग प्राप्त करें; और फिर, एक बोनस, अंत में मैक्रो, एक इवेंट मैक्रो को वर्कशीट अपडेट करने के लिए जब वे एक सेल बदलते हैं।
मैं स्टीव को उस प्रश्न को भेजने के लिए धन्यवाद देना चाहता हूं और मुझे खुशी है कि मेरे पास एक जवाब है - पावर क्वेरी से पहले, यह वास्तव में, वास्तव में कठिन होगा - और मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2160.xlsm
