निकटतम नंबर ढूँढना - मेरे पास स्वस्थ भोजन

विषय-सूची

व्यवहार में, अक्सर ऐसे मामले होते हैं जब आपको और मुझे दी गई संख्या के संबंध में एक सेट (तालिका) में निकटतम मान खोजने की आवश्यकता होती है। यह हो सकता है, उदाहरण के लिए:

मात्रा के आधार पर छूट की गणना।
योजना के कार्यान्वयन के आधार पर बोनस की राशि की गणना।
दूरी के आधार पर शिपिंग दरों की गणना।
माल आदि के लिए उपयुक्त कंटेनरों का चयन।

इसके अलावा, स्थिति के आधार पर, ऊपर और नीचे दोनों तरफ गोलाई की आवश्यकता हो सकती है।

ऐसी समस्या को हल करने के कई तरीके हैं - स्पष्ट और इतने स्पष्ट नहीं -। आइए उन्हें क्रमिक रूप से देखें।

शुरू करने के लिए, आइए एक आपूर्तिकर्ता की कल्पना करें जो थोक पर छूट देता है, और छूट का प्रतिशत खरीदे गए सामान की मात्रा पर निर्भर करता है। उदाहरण के लिए, 5 से अधिक टुकड़े खरीदने पर 2% की छूट दी जाती है, और 20 टुकड़ों से खरीदते समय - पहले से ही 6%, आदि।

खरीदे गए सामान की मात्रा दर्ज करते समय छूट प्रतिशत की जल्दी और खूबसूरती से गणना कैसे करें?

विधि 1: नेस्टेड IFs

श्रृंखला से एक विधि "क्या सोचना है - आपको कूदने की ज़रूरत है!"। नेस्टेड फ़ंक्शंस का उपयोग करना IF (अगर) क्रमिक रूप से जाँच करने के लिए कि क्या सेल मान प्रत्येक अंतराल में आता है और संबंधित श्रेणी के लिए छूट प्रदर्शित करता है। लेकिन इस मामले में सूत्र बहुत बोझिल हो सकता है:

निकटतम संख्या ढूँढना

मुझे लगता है कि यह स्पष्ट है कि इस तरह की "राक्षस गुड़िया" को डिबग करना या कुछ समय बाद इसमें कुछ नई शर्तों को जोड़ने का प्रयास करना मजेदार है।

इसके अलावा, माइक्रोसॉफ्ट एक्सेल में आईएफ फ़ंक्शन के लिए नेस्टिंग सीमा है - पुराने संस्करणों में 7 गुना और नए संस्करणों में 64 गुना। अगर आपको और चाहिए तो क्या होगा?

विधि 2। अंतराल दृश्य के साथ VLOOKUP

यह विधि बहुत अधिक कॉम्पैक्ट है। छूट प्रतिशत की गणना करने के लिए, पौराणिक फ़ंक्शन का उपयोग करें VPR (VLOOKUP) अनुमानित खोज मोड में:

निकटतम संख्या ढूँढना

जहां

B4 - पहले लेन-देन में माल की मात्रा का मूल्य जिसके लिए हम छूट की तलाश कर रहे हैं
$जी$4:$एच$8 - डिस्काउंट टेबल का लिंक - बिना "हेडर" के और $ साइन के साथ तय किए गए पतों के साथ।
2 - छूट तालिका में कॉलम की क्रम संख्या जिससे हम छूट मूल्य प्राप्त करना चाहते हैं
जब सही है - यहीं पर "कुत्ते" को दफनाया जाता है। यदि अंतिम फ़ंक्शन तर्क के रूप में VPR उल्लिखित करना लेटा हुआ (झूठा) या 0, तो फ़ंक्शन खोजेगा सख्त मैच मात्रा कॉलम में (और हमारे मामले में यह #N/A त्रुटि देगा, क्योंकि छूट तालिका में कोई मान 49 नहीं है)। लेकिन अगर इसके बजाय लेटा हुआ लिखना जब सही है (सच) या 1, तो फ़ंक्शन सटीक के लिए नहीं दिखेगा, लेकिन निकटतम सबसे छोटा मूल्य और हमें उस छूट का प्रतिशत देगा जिसकी हमें आवश्यकता है।

इस पद्धति का नकारात्मक पक्ष पहले कॉलम द्वारा छूट तालिका को आरोही क्रम में क्रमबद्ध करने की आवश्यकता है। यदि ऐसी कोई छँटाई नहीं है (या इसे उल्टे क्रम में किया जाता है), तो हमारा सूत्र काम नहीं करेगा:

निकटतम संख्या ढूँढना

तदनुसार, इस दृष्टिकोण का उपयोग केवल निकटतम सबसे छोटे मूल्य को खोजने के लिए किया जा सकता है। यदि आपको निकटतम सबसे बड़ा खोजने की आवश्यकता है, तो आपको एक अलग दृष्टिकोण का उपयोग करना होगा।

विधि 3. INDEX और MATCH फ़ंक्शंस का उपयोग करके निकटतम सबसे बड़ा ढूँढना

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

VLOOKUP फ़ंक्शन यहां मदद नहीं करेगा, इसलिए आपको इसके एनालॉग का उपयोग करना होगा - INDEX फ़ंक्शन का एक गुच्छा (अनुक्रमणिका) और अधिक उजागर (मिलान):

निकटतम संख्या ढूँढना

यहां, अंतिम तर्क -1 के साथ MATCH फ़ंक्शन निकटतम सबसे बड़ा मान खोजने के तरीके में काम करता है, और INDEX फ़ंक्शन तब उस मॉडल का नाम निकालता है जिसकी हमें आसन्न कॉलम से आवश्यकता होती है।

विधि 4. नया फ़ंक्शन देखें (XLOOKUP)

यदि आपके पास सभी अद्यतनों के साथ Office 365 का संस्करण स्थापित है, तो VLOOKUP के बजाय (VLOOKUP) आप इसके एनालॉग का उपयोग कर सकते हैं - व्यू फ़ंक्शन (एक्सलुकअप), जिसका मैंने पहले ही विस्तार से विश्लेषण किया है:

निकटतम संख्या ढूँढना

यहाँ:

B4 - उत्पाद की मात्रा का प्रारंभिक मूल्य जिसके लिए हम छूट की तलाश कर रहे हैं
$जी$4:$जी$8 - वह रेंज जहां हम मैचों की तलाश कर रहे हैं
$एच$4:$एच$8 - परिणामों की वह श्रेणी जिससे आप छूट वापस करना चाहते हैं
चौथा तर्क (-1) में सटीक मिलान के बजाय निकटतम सबसे छोटी संख्या की खोज शामिल है जो हम चाहते हैं।

इस पद्धति का लाभ यह है कि छूट तालिका को छाँटने की कोई आवश्यकता नहीं है और यदि आवश्यक हो, तो न केवल निकटतम सबसे छोटा, बल्कि निकटतम सबसे बड़ा मूल्य भी खोजने की क्षमता है। इस मामले में अंतिम तर्क 1 होगा।

लेकिन, दुर्भाग्य से, अभी तक सभी के पास यह सुविधा नहीं है - केवल Office 365 के खुश मालिक।

विधि 5. पावर क्वेरी

यदि आप अभी तक एक्सेल के लिए शक्तिशाली और पूरी तरह से मुफ्त पावर क्वेरी ऐड-इन से परिचित नहीं हैं, तो आप यहां हैं। यदि आप पहले से ही परिचित हैं, तो आइए अपनी समस्या को हल करने के लिए इसका उपयोग करने का प्रयास करें।

आइए पहले कुछ प्रारंभिक कार्य करें:

आइए कीबोर्ड शॉर्टकट का उपयोग करके अपने स्रोत तालिकाओं को गतिशील (स्मार्ट) में बदलें कंट्रोल+T या टीम होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें).
स्पष्टता के लिए, आइए उन्हें नाम दें। बिक्री и छूट टैब निर्माता (डिज़ाइन).
बटन का उपयोग करके प्रत्येक तालिका को बारी-बारी से पावर क्वेरी में लोड करें टेबल/रेंज से टैब जानकारी (डेटा - टेबल/रेंज से). एक्सेल के हाल के संस्करणों में, इस बटन का नाम बदलकर कर दिया गया है पत्तों के साथ (शीट से).
यदि हमारे उदाहरण ("माल की मात्रा" और "मात्रा से ...") के अनुसार तालिकाओं में मात्राओं के साथ अलग-अलग कॉलम नाम हैं, तो उन्हें पावर क्वेरी में नाम दिया जाना चाहिए और उसी का नाम दिया जाना चाहिए।
उसके बाद, आप Power Query संपादक विंडो में आदेश का चयन करके Excel पर वापस लौट सकते हैं होम - बंद करें और लोड करें - बंद करें और लोड करें ... (होम - बंद करें और लोड करें - बंद करें और लोड करें ...) और फिर विकल्प बस एक कनेक्शन बनाएं (केवल कनेक्शन बनाएं).
फिर सबसे दिलचस्प शुरू होता है। यदि आपके पास पावर क्वेरी में अनुभव है, तो मुझे लगता है कि विचार की आगे की रेखा इन दो तालिकाओं को एक ला वीलुकअप में शामिल होने के लिए एक जॉइन क्वेरी (मर्ज) के साथ विलय करने की दिशा में होनी चाहिए, जैसा कि पिछली विधि में था। वास्तव में, हमें ऐड मोड में विलय करने की आवश्यकता होगी, जो पहली नज़र में बिल्कुल स्पष्ट नहीं है। एक्सेल टैब में चुनें डेटा - डेटा प्राप्त करें - अनुरोधों को मिलाएं - जोड़ें (डेटा - डेटा प्राप्त करें - प्रश्नों को मिलाएं - संलग्न करें) और फिर हमारे टेबल बिक्री и छूट दिखाई देने वाली विंडो में:
क्लिक करने के बाद OK हमारी टेबल एक दूसरे के नीचे - एक पूरे में चिपके रहेंगे। कृपया ध्यान दें कि इन तालिकाओं में माल की मात्रा वाले कॉलम एक-दूसरे के नीचे आते हैं, क्योंकि। उनका एक ही नाम है:
यदि बिक्री तालिका में पंक्तियों का मूल क्रम आपके लिए महत्वपूर्ण है, तो बाद के सभी परिवर्तनों के बाद आप इसे पुनर्स्थापित कर सकते हैं, कमांड का उपयोग करके हमारी तालिका में एक क्रमांकित कॉलम जोड़ें एक कॉलम जोड़ना - इंडेक्स कॉलम (कॉलम जोड़ें - इंडेक्स कॉलम). यदि लाइनों का क्रम आपके लिए मायने नहीं रखता है, तो आप इस चरण को छोड़ सकते हैं।
अब, तालिका के शीर्षलेख में ड्रॉप-डाउन सूची का उपयोग करके, इसे कॉलम द्वारा क्रमबद्ध करें मात्रा आरोही:
और मुख्य ट्रिक: कॉलम हेडर पर राइट-क्लिक करें छूट एक टीम चुनें नीचे भरें (नीचे भरें). के साथ खाली सेल रिक्त पिछले छूट मूल्यों के साथ स्वचालित रूप से भर गया:
यह कॉलम द्वारा क्रमबद्ध करके पंक्तियों के मूल अनुक्रम को पुनर्स्थापित करने के लिए बनी हुई है सूची (आप इसे बाद में सुरक्षित रूप से हटा सकते हैं) और एक फिल्टर के साथ अनावश्यक लाइनों से छुटकारा पाएं रिक्त कॉलम द्वारा लेनदेन कोड: