क्या आपके पास एक एक्सेल फॉर्मूला है जो कई IF फ़ंक्शन को घोंसला देता है? जब आप बहुत से नेस्टेड IF स्टेटमेंट्स के साथ इस बिंदु पर पहुंचते हैं, तो आपको यह देखने की जरूरत है कि क्या पूरी चीज एक साधारण वीएलएफयूपी फ़ंक्शन के साथ सरल हो जाएगी। मैंने देखा है कि 1000-वर्ण सूत्र 30-वर्ण वाले VLOOKUP सूत्र में सरल किए जाते हैं। जब आपको बाद में नए मूल्यों को जोड़ना होता है, तो इसे बनाए रखना आसान होता है।
बहुत समय पहले, मैंने काम पर बिक्री के उपाध्यक्ष के लिए काम किया था। मैं हमेशा कुछ नए बोनस प्रोग्राम या कमीशन प्लान की मॉडलिंग कर रहा था। मैं हर तरह की स्थितियों के साथ योजनाओं की योजना बनाने के लिए अभ्यस्त हो गया। नीचे एक बहुत सुंदर है।
सामान्य दृष्टिकोण एक नेस्टेड IF सूत्र का निर्माण शुरू करना है। आप हमेशा उच्च अंत या सीमा के निचले छोर पर शुरू करते हैं। “यदि बिक्री $ 500K से अधिक है, तो छूट 20% है; नई तो… ।" IF फ़ंक्शन का तीसरा तर्क एक नया IF फ़ंक्शन है जो दूसरे स्तर के लिए परीक्षण करता है: यदि बिक्री $ 250K से अधिक है, तो छूट 15% है; नई तो,… "
ये सूत्र अधिक लंबे और लंबे होते हैं क्योंकि इनमें अधिक स्तर होते हैं। इस तरह के सूत्र का सबसे कठिन हिस्सा यह याद रखना है कि सूत्र के अंत में कितने बंद कोष्ठक हैं।
मिनी बोनस टिप
कोष्ठकों का मिलान
कोष्ठक के प्रत्येक नए स्तर के लिए विभिन्न रंगों के माध्यम से एक्सेल चक्र। जबकि एक्सेल रंगों का पुन: उपयोग करता है, यह केवल खुलने वाले कोष्ठक के लिए और मिलान समापन कोष्ठक के लिए काले रंग का उपयोग करता है। जैसा कि आप नीचे दिए गए फॉर्मूले को पूरा कर रहे हैं, बस एक काले कोष्ठक को टाइप करने तक कोष्ठक लिखना बंद रखें।
नेस्टेड फॉर्मूला टिप पर वापस जाएं
यदि आप Excel 2003 का उपयोग कर रहे हैं, तो आपका फॉर्मूला पहले से ही सीमा के पास है। इसके बाद, आप 7 IF फ़ंक्शन से अधिक घोंसला नहीं बना सकते। यह एक बदसूरत दिन था जब शक्तियों को कमीशन योजना को बदल दिया गया था और आपको आठवें IF फ़ंक्शन को जोड़ने के लिए एक तरीके की आवश्यकता थी। आज, आप 64 IF फ़ंक्शन को घोंसला बना सकते हैं। आपको ऐसा कभी नहीं करना चाहिए, लेकिन यह जानना अच्छा है कि कोई समस्या नहीं है 8 या 9।
नेस्टेड IF फ़ंक्शन का उपयोग करने के बजाय, VLOOKUP फ़ंक्शन के लिए असामान्य उपयोग का उपयोग करके देखें। जब VLOOKUP का चौथा तर्क फाल्स से ट्रू में बदलता है, तो फ़ंक्शन अब एक सटीक मैच की तलाश में नहीं है।
खैर, पहले VLOOKUP एक सटीक मिलान खोजने की कोशिश करता है। लेकिन अगर एक सटीक मिलान नहीं मिलता है, तो एक्सेल पंक्ति में बस से कम है जो आप खोज रहे हैं।
नीचे दी गई तालिका पर विचार करें। सेल C13 में, एक्सेल तालिका में $ 28,355 के लिए एक मैच की तलाश करेगा। जब यह 28355 नहीं मिल सकता है, तो एक्सेल उस मान से जुड़े छूट को लौटा देगा जो अभी कम है। इस मामले में, $ 10K स्तर के लिए 1% छूट।
जब आप नियमों को E13: F18 में तालिका में परिवर्तित करते हैं, तो आपको सबसे छोटे स्तर से शुरू करने और उच्चतम स्तर पर आगे बढ़ने की आवश्यकता होती है। हालांकि यह नियमों में अस्थिर था, अगर कोई बिक्री में $ 10,000 से कम है, तो छूट 0% होगी। आपको इसे तालिका में पहली पंक्ति के रूप में जोड़ना होगा।
सावधान
जब आप VLOOKUP के "ट्रू" संस्करण का उपयोग कर रहे हैं, तो आपकी तालिका को आरोही क्रमबद्ध करना होगा। बहुत से लोग मानते हैं कि सभी लुकअप टेबल को क्रमबद्ध करना होगा। लेकिन एक तालिका को केवल अनुमानित मिलान करने के मामले में क्रमबद्ध किया जाना चाहिए।
क्या होगा यदि आपका प्रबंधक पूरी तरह से स्व-निहित सूत्र चाहता है और बोनस तालिका को दाईं ओर नहीं देखना चाहता है? सूत्र बनाने के बाद, आप तालिका को सूत्र में सही एम्बेड कर सकते हैं।
सेल को डबल-क्लिक करके या सेल का चयन करके और F2 दबाकर एडिट मोड में फॉर्मूला रखें।
कर्सर का उपयोग करके, पूरे दूसरे तर्क का चयन करें: $ E $ 13: $ F $ 18।
F9 कुंजी दबाएं। एक्सेल लुकिंग टेबल को एरे स्थिरांक के रूप में एम्बेड करेगा। सरणी स्थिरांक में, एक अर्धविराम एक नई पंक्ति इंगित करता है, और एक अल्पविराम एक नया स्तंभ इंगित करता है।
एंट्रर दबाये। सूत्र को अन्य कक्षों में कॉपी करें।
अब आप तालिका को हटा सकते हैं। अंतिम सूत्र नीचे दिखाया गया है।
मुझे मैचिंग कोष्ठकों के बारे में सिखाने के लिए माइक गिर्विन का धन्यवाद। VLOOKUP तकनीक डैनी मैक, बोरियाना पेट्रोवा, एंड्रियास थेहोस और @mvcos द्वारा सुझाई गई थी।
वीडियो देखेंा
- एक तीखा कमीशन, बोनस या छूट कार्यक्रम के साथ, आपको अक्सर अपने IF फ़ंक्शन को घोंसला बनाना पड़ता है
- Excel 2003 की सीमा 7 नेस्टेड IF कथन है।
- अब आप 32 को घोंसला बना सकते हैं, लेकिन मुझे नहीं लगता कि आपको कभी भी घोंसला बनाना चाहिए 32
- जब आप कभी भी VLOOKUP के अनुमानित मैच संस्करण का उपयोग करेंगे? यह समय है।
- डिस्काउंट प्रोग्राम को लुकअप टेबल में अनुवाद करें
- VLOOKUP ज्यादातर मामलों में जवाब नहीं मिलेगा।
- पुटिंग, अंत में सच VLOOKUP को केवल कम मूल्य खोजने के लिए बताएगा।
- यह वही समय है जब VLOOKUP तालिका को क्रमबद्ध किया जाना है।
- VLOOKUP तालिका को किनारे पर नहीं रखना चाहते हैं? इसे सूत्र में एम्बेड करें।
- सूत्र को संपादित करने के लिए F2। लुकअप टेबल का चयन करें। F9 दबाएं। दर्ज।
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2030 - वीएलबुक के साथ बदला गया नेस्टेड!
मैं इस पूरी पुस्तक को पॉडकास्ट कर रहा हूँ, प्लेलिस्ट में आने के लिए ऊपरी-दाएँ हाथ के कोने पर "i" पर क्लिक करें!
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। ठीक है, यह वास्तव में या तो एक कमीशन कार्यक्रम, या एक छूट कार्यक्रम, या एक बोनस कार्यक्रम के साथ आम है, जहां हमारे पास टियर, अलग-अलग स्तरों, सही हैं? यदि आप $ 10000 से ऊपर हैं, तो आपको 1% छूट, $ 50000 5% छूट मिलती है। जब आप इस नेस्टेड IF स्टेटमेंट का निर्माण करते हैं, तो आपको सावधान रहना होगा, यदि आप इससे अधिक की तलाश में हैं, या यदि आप कम से कम देख रहे हैं, तो आप इसे शीर्ष छोर पर शुरू करते हैं। तो B10> 50000, 20%, अन्यथा एक और IF, B10> 250000, 25%, और इतने पर। यह सिर्फ एक लंबा और जटिल सूत्र है, और यदि आपकी तालिका का बड़ा, Excel 2003 में वापस, आप 7 से अधिक नेस्ट नहीं कर सकते हैं, तो हम यहां लगभग सीमा के पास हैं। अब आप 32 पर जा सकते हैं, मुझे नहीं लगता कि आपको कभी 32 पर जाना चाहिए, और यहां तक कि अगर आप चिल्ला रहे हैं "अरे रुको,एक्सेल 2016 में सामने आए नए फंक्शन के बारे में क्या है, फरवरी 2016 में आईएफएस फ़ंक्शन के साथ रिलीज़? " हाँ, यहाँ कम कोष्ठक हैं, और 97 वर्णों के बजाय 87 वर्ण हैं, यह अभी भी एक गड़बड़ है, चलो इससे छुटकारा पाएं और इसे VLOOKUP के साथ करें!
ठीक है, यहाँ कदम हैं, आप उन नियमों को लेते हैं, और आप उन्हें अपने सिर पर घुमाते हैं। पहली बात जो हमें कहनी है, यदि आपकी बिक्री में $ 0 थी, तो आपको 0% की छूट मिलती है, यदि आपकी बिक्री में $ 10000 की छूट है, तो आपको 1% की छूट मिलती है, यदि आपकी बिक्री में $ 50000 है, तो आपको 5% की छूट मिलती है । $ 100000, 10% छूट, $ 250000, 15% छूट, और फिर अंत में, कुछ भी> $ 500000 को 20% छूट मिलती है, ठीक है। अब बहुत बार, हर बार जब मैं VLOOKUP के बारे में बात कर रहा हूं, तो मैं कहता हूं कि आपके द्वारा बनाया गया हर VLOOKUP FALSE में समाप्त होने जा रहा है, और लोग कहेंगे "वैसे एक सेकंड रुको, वैसे भी TRUE संस्करण क्या है?" यह इस मामले के लिए है, जहां हम एक सीमा की तलाश कर रहे हैं, इसलिए हम इस मूल्य को देख रहे हैं, एक नियमित VLOOKUP, निश्चित रूप से, 2, FALSE 550000 नहीं मिलेगा, # N / A वापस करने जा रहा है!तो इस एक बहुत ही विशेष मामले में हम उस FALSE को एक TRUE में बदलने जा रहे हैं, और वह एक्सेल को बताएगा "550000 के लिए देखो, यदि आप इसे नहीं ढूंढ सकते हैं, तो हमें वह मूल्य दें जो अभी कम है।" तो यह हमें 20% देता है, कि यह क्या करता है, ठीक है? और यह वही समय है जब आपकी VLOOKUP तालिका को क्रमबद्ध किया जाना है, FALSE के साथ अन्य सभी समय पर, फिर भी इसे आप चाहते हैं। और हाँ, आप इसे छोड़ सकते हैं, सही कह सकते हैं, लेकिन मैंने हमेशा इसे सिर्फ अपने आप को याद दिलाने के लिए रखा है कि यह उन अजीब अविश्वसनीय खतरनाक बुकबुक में से एक है, और मैं इस सूत्र को कहीं और कॉपी नहीं करना चाहता। ठीक है, इसलिए हम कॉपी करेंगे और विशेष सूत्र चिपकाएँ, ठीक है।ठीक है? और यह वही समय है जब आपकी VLOOKUP तालिका को क्रमबद्ध किया जाना है, FALSE के साथ अन्य सभी समय पर, फिर भी इसे आप चाहते हैं। और हाँ, आप इसे छोड़ सकते हैं, सही कह सकते हैं, लेकिन मैंने हमेशा इसे सिर्फ अपने आप को याद दिलाने के लिए रखा है कि यह उन अजीब अविश्वसनीय खतरनाक बुकबुक में से एक है, और मैं इस सूत्र को कहीं और कॉपी नहीं करना चाहता। ठीक है, इसलिए हम कॉपी करेंगे और विशेष सूत्र चिपकाएँ, ठीक है।ठीक है? और यह एक ही समय है जब आपकी VLOOKUP तालिका को क्रमबद्ध किया जाना है, FALSE के साथ अन्य सभी समय, यह फिर भी आप चाहते हैं। और हाँ, आप इसे छोड़ सकते हैं, सही कह सकते हैं, लेकिन मैंने हमेशा इसे सिर्फ अपने आप को याद दिलाने के लिए रखा है कि यह उन अजीब अविश्वसनीय खतरनाक बुकबुक में से एक है, और मैं इस सूत्र को कहीं और कॉपी नहीं करना चाहता। ठीक है, इसलिए हम कॉपी करेंगे और विशेष सूत्र चिपकाएँ, ठीक है।
अगली शिकायत: आपका प्रबंधक लुकअप टेबल नहीं देखना चाहता है, वह चाहता है कि "बस उस लुकअप टेबल से छुटकारा मिल जाए।" ठीक है, हम यह कर सकते हैं कि लुकअप टेबल को एम्बेड करके, ExcelIFFun में माइक गिर्विन से मुझे मिली शानदार ट्रिक को देखें। F2 सूत्र को संपादन मोड में डालने के लिए, और फिर बहुत सावधानी से लुकअप टेबल के लिए सीमा का चयन करें। F9 दबाएं, और यह इस तालिका को लेता है और इसे सरणी नामकरण में रखता है, और फिर मैं सिर्फ Enter दबाता हूं। कॉपी करें कि नीचे, विशेष सूत्रों को चिपकाएँ, और फिर मैं लुकअप टेबल से छुटकारा पाने के लिए स्वतंत्र हूं, कि सभी अभी भी नीचे काम कर रहे हैं। यह एक बड़ी परेशानी है अगर आपको इसमें वापस आना है और इसे संपादित करना है, तो आपको बहुत सारी स्पष्टता के साथ वास्तव में घूरना होगा। अल्पविराम का मतलब है कि हम अगले कॉलम पर जा रहे हैं, अर्धविराम का मतलब अगली पंक्ति में जा रहा है, ठीक है,लेकिन आप सिद्धांत में वापस आ सकते हैं और उस सूत्र को बदल सकते हैं यदि श्रृंखला संख्याओं में से एक बदल जाती है।
ठीक है, इसलिए नेस्टेड IF स्टेटमेंट के बजाय VLOOKUP का उपयोग करते हुए 40 के हमारे रास्ते में टिप # 32 है, "सभी समय के 40 महानतम एक्सेल टिप्स", आपके पास यह पूरी पुस्तक हो सकती है। शीर्ष-दाएं हाथ के कोने पर "i" पर क्लिक करें, ई-बुक के लिए $ 10, प्रिंट बुक के लिए $ 25, ठीक है। इसलिए आज हम एक तीखा कमीशन बोनस, या छूट कार्यक्रम हल करने की कोशिश कर रहे हैं। नेस्टेड आईएफएस वास्तव में आम हैं, बाहर देखो, 7 आप एक्सेल 2003 में हो सकता है, आज आपके पास 32 हो सकते हैं, लेकिन आपके पास कभी 32 नहीं होना चाहिए। इसलिए वीएलबुक के अनुमानित MATCH संस्करण का उपयोग करने के लिए यह कब है। उस छूट कार्यक्रम को लें, इसे उल्टा करें, इसे छोटी संख्या के साथ शुरू होने वाली लुकअप तालिका में शामिल करें, और सबसे बड़ी संख्या पर जाएं। VLOOKUP, निश्चित रूप से, उत्तर नहीं मिलेगा, लेकिन अंत में VLOOKUP को बदलकर TRUE करें, यह बताएगा कि यह मान को कमतर मानने के लिए है,यह एकमात्र समय है जब तालिका को क्रमबद्ध किया जाना है। यदि आप उस तालिका को वहां से देखना नहीं चाहते हैं, तो आप सूत्र को संपादित करने के लिए माइक गिर्विन ट्रिक, F2 का उपयोग करके इसे एम्बेड कर सकते हैं, लुकअप तालिका का चयन करें, F9 दबाएँ, और फिर दर्ज करें।
ठीक है, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं, हम अगली बार आपको एक और नेटकास्ट से देखेंगे!
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: पॉडकास्ट 2030.xlsx
