निकटतम नम्बर खोज्दै

अभ्यासमा, प्रायः त्यहाँ केसहरू हुन्छन् जब तपाइँ र मैले दिइएको संख्याको सम्बन्धमा सेट (तालिका) मा नजिकको मान फेला पार्न आवश्यक हुन्छ। यो हुन सक्छ, उदाहरणका लागि:

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

यसबाहेक, स्थितिमा निर्भर गर्दै, राउन्डिङ माथि र तल दुवै आवश्यक हुन सक्छ।

त्यहाँ धेरै तरिकाहरू छन् - स्पष्ट र स्पष्ट छैन - यस्तो समस्या समाधान गर्न। तिनीहरूलाई क्रमशः हेरौं।

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

खरिद गरिएका सामानहरूको मात्रा प्रविष्ट गर्दा छुट प्रतिशत कसरी छिटो र सुन्दर ढंगले गणना गर्ने?

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

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

मलाई लाग्छ कि यो स्पष्ट छ कि यस्तो "राक्षस पुतली" लाई डिबग गर्नु वा केहि समय पछि यसमा केहि नयाँ सर्तहरू थप्ने प्रयास गर्नु रमाइलो छ।

थप रूपमा, माइक्रोसफ्ट एक्सेलमा IF प्रकार्यको लागि नेस्टिङ सीमा छ - पुरानो संस्करणहरूमा 7 पटक र नयाँ संस्करणहरूमा 64 पटक। यदि तपाईंलाई थप चाहिन्छ भने के हुन्छ?

विधि २. अन्तराल दृश्यको साथ VLOOKUP

यो विधि धेरै अधिक संकुचित छ। छुट प्रतिशत गणना गर्न, पौराणिक प्रकार्य प्रयोग गर्नुहोस् VPR (VLOOKUP) अनुमानित खोज मोडमा:

जहाँ

• B4 - पहिलो लेनदेनमा सामानको मात्राको मूल्य जसको लागि हामी छुट खोजिरहेका छौं
  
• $G$4:$H$8 - छुट तालिकाको लिङ्क - बिना "हेडर" र $ चिन्हको साथ निश्चित ठेगानाहरूसँग।
  
• 2 - छुट तालिकामा स्तम्भको क्रम संख्या जसबाट हामी छुट मूल्य प्राप्त गर्न चाहन्छौं
• साँचो - यहाँ "कुकुर" गाडिएको छ। यदि अन्तिम प्रकार्य तर्कको रूपमा VPR निर्दिष्ट गर्नुहोस् झुटो (FALSE) वा 0, त्यसपछि प्रकार्य खोज्नेछ कडा मिलान मात्रा स्तम्भमा (र हाम्रो अवस्थामा यसले #N/A त्रुटि दिनेछ, किनकि छुट तालिकामा कुनै मान 49 छैन)। तर यदि यसको सट्टा झुटो लेख्न साँचो (सत्य) वा 1, तब प्रकार्यले सटीकको लागि हेर्दैन, तर सबैभन्दा नजिकको सानो मान र हामीलाई चाहिने छुटको प्रतिशत दिनेछ।

यस विधिको नकारात्मक पक्ष पहिलो स्तम्भ द्वारा बढ्दो क्रममा छूट तालिका क्रमबद्ध गर्न आवश्यक छ। यदि त्यहाँ त्यस्तो कुनै क्रमबद्ध छैन (वा यो उल्टो क्रममा गरिन्छ), तब हाम्रो सूत्रले काम गर्दैन:

तदनुसार, यो दृष्टिकोण केवल निकटतम सानो मान फेला पार्न प्रयोग गर्न सकिन्छ। यदि तपाईंलाई सबैभन्दा नजिकको सबैभन्दा ठूलो फेला पार्न आवश्यक छ भने, तपाईंले फरक दृष्टिकोण प्रयोग गर्नुपर्छ।

विधि 3. INDEX र MATCH प्रकार्यहरू प्रयोग गरेर सबैभन्दा नजिकको सबैभन्दा ठूलो फेला पार्दै

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

VLOOKUP प्रकार्यले यहाँ मद्दत गर्दैन, त्यसैले तपाईले यसको एनालग प्रयोग गर्नुपर्नेछ - INDEX प्रकार्यहरूको गुच्छा। (INDEX) र अधिक खुलासा (MATCH):

यहाँ, अन्तिम आर्गुमेन्ट -१ सँग MATCH प्रकार्यले सबैभन्दा नजिकको सबैभन्दा ठूलो मान फेला पार्ने मोडमा काम गर्छ, र INDEX प्रकार्यले हामीलाई छेउछाउको स्तम्भबाट चाहिने मोडेल नाम निकाल्छ।

विधि 4. नयाँ प्रकार्य दृश्य (XLOOKUP)

यदि तपाइँसँग Office 365 को सबै अद्यावधिकहरू स्थापना भएको संस्करण छ भने, VLOOKUP को सट्टा (VLOOKUP) तपाईं यसको एनालग प्रयोग गर्न सक्नुहुन्छ - दृश्य प्रकार्य (XLOOKUP), जुन मैले पहिले नै विस्तृत रूपमा विश्लेषण गरेको छु:

यहाँ:

• B4 - उत्पादनको मात्राको प्रारम्भिक मूल्य जसको लागि हामीले छुट खोजिरहेका छौं
• $G$4:$G$8 - दायरा जहाँ हामी मिलानहरू खोजिरहेका छौं
  
• $H$4:$H$8 - परिणामहरूको दायरा जसबाट तपाइँ छूट फिर्ता गर्न चाहानुहुन्छ
• चौथो तर्क (-1) ले ठ्याक्कै मिल्दोको सट्टामा हामीले चाहेको सबैभन्दा नजिकको सानो संख्याको खोजी समावेश गर्दछ।
  

यस विधिका फाइदाहरू छन् कि छुट तालिका क्रमबद्ध गर्न आवश्यक छैन र आवश्यक भएमा खोजी गर्ने क्षमता, नजीकको सबैभन्दा सानो मात्र होइन, सबैभन्दा नजिकको सबैभन्दा ठूलो मूल्य पनि। यस मामला मा अन्तिम तर्क 1 हुनेछ।

तर, दुर्भाग्यवश, सबैसँग यो सुविधा छैन - केवल Office 365 का खुसी मालिकहरू।

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

यदि तपाइँ अझै एक्सेलको लागि शक्तिशाली र पूर्ण रूपमा निःशुल्क पावर क्वेरी एड-इनसँग परिचित हुनुहुन्न भने, तपाइँ यहाँ हुनुहुन्छ। यदि तपाइँ पहिले नै परिचित हुनुहुन्छ भने, त्यसपछि हाम्रो समस्या समाधान गर्न यसलाई प्रयोग गर्ने प्रयास गरौं।

पहिले केही तयारीका काम गरौं:

1. किबोर्ड सर्टकट प्रयोग गरेर हाम्रो स्रोत तालिकाहरूलाई डायनामिक (स्मार्ट) मा रूपान्तरण गरौं Ctrl+T वा टोली गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा).
2. स्पष्टताको लागि, तिनीहरूलाई नाम दिनुहोस्। बिक्री и छूट ट्याब कन्स्ट्रक्टर (डिजाईन).
3. प्रत्येक तालिकालाई पावर क्वेरीमा बटन प्रयोग गरेर लोड गर्नुहोस् तालिका/दायराबाट ट्याब तथ्याङ्क (डेटा - तालिका/दायराबाट)। Excel को हालैका संस्करणहरूमा, यो बटनलाई पुन: नामाकरण गरिएको छ पातहरु संग (पानाबाट).
   
4. यदि तालिकाहरूमा परिमाणका साथ फरक स्तम्भ नामहरू छन्, जस्तै हाम्रो उदाहरणमा ("सामानको मात्रा" र "बाट ..."), त्यसपछि तिनीहरूको नाम Power Query मा पुन: नामाकरण गरिनुपर्छ र उही नाम राख्नुपर्छ।
5. त्यस पछि, तपाइँ Power Query सम्पादक विन्डोमा आदेश चयन गरेर Excel मा फर्कन सक्नुहुन्छ घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... (घर - बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ...) र त्यसपछि विकल्प बस एक जडान सिर्जना गर्नुहोस् (मात्र जडान सिर्जना गर्नुहोस्).

   
   

6. त्यसपछि सबैभन्दा रोचक सुरु हुन्छ। यदि तपाइँसँग Power Query मा अनुभव छ भने, त्यसपछि म मान्छु कि विचारको अर्को रेखा यी दुई तालिकाहरूलाई जोड्ने क्वेरी (मर्ज) a la VLOOKUP सँग मर्ज गर्ने दिशामा हुनुपर्छ, जस्तै अघिल्लो विधिमा भएको थियो। वास्तवमा, हामीले एड मोडमा मर्ज गर्न आवश्यक छ, जुन पहिलो नजरमा स्पष्ट छैन। एक्सेल ट्याबमा चयन गर्नुहोस् डाटा - डाटा प्राप्त गर्नुहोस् - अनुरोधहरू जोड्नुहोस् - थप्नुहोस् (डेटा - डाटा प्राप्त गर्नुहोस् - प्रश्नहरू जोड्नुहोस् - संलग्न गर्नुहोस्) र त्यसपछि हाम्रो टेबल बिक्री и छूट देखा पर्ने विन्डोमा:

   

7. क्लिक गर्नु पछि OK हाम्रा टेबलहरू एक अर्काको मुनि - एकल पूरैमा टाँसिएका हुनेछन्। कृपया ध्यान दिनुहोस् कि यी तालिकाहरूमा सामानहरूको मात्राको साथ स्तम्भहरू एक अर्काको मुनि परेका थिए, किनभने। तिनीहरूको एउटै नाम छ:

   

8. यदि बिक्री तालिकामा पङ्क्तिहरूको मूल अनुक्रम तपाईंको लागि महत्त्वपूर्ण छ भने, त्यसपछि सबै पछिल्ला परिवर्तनहरू पछि तपाईंले यसलाई पुनर्स्थापना गर्न सक्नुहुन्छ, आदेश प्रयोग गरेर हाम्रो तालिकामा अंकित स्तम्भ थप्नुहोस्। स्तम्भ थप्दै - अनुक्रमणिका स्तम्भ (स्तम्भ थप्नुहोस् — अनुक्रमणिका स्तम्भ)। यदि रेखाहरूको अनुक्रमले तपाइँलाई फरक पार्दैन भने, तपाइँ यो चरण छोड्न सक्नुहुन्छ।
9. अब, तालिकाको हेडरमा ड्रप-डाउन सूची प्रयोग गरेर, यसलाई स्तम्भद्वारा क्रमबद्ध गर्नुहोस् मात्रा आरोहण:

   

10. र मुख्य चाल: स्तम्भ हेडरमा दायाँ क्लिक गर्नुहोस् छुट टोली छान्नुहोस् भर्नुहोस् - तल (भर्नुहोस् - तल)। को साथ खाली कक्षहरू शून्य स्वचालित रूपमा अघिल्लो छुट मानहरू भरिएको छ:

    

11. स्तम्भ अनुसार क्रमबद्ध गरेर पङ्क्तिहरूको मूल अनुक्रम पुनर्स्थापना गर्न बाँकी छ सूचकांक (तपाईं यसलाई पछि सुरक्षित रूपमा मेटाउन सक्नुहुन्छ) र फिल्टरको साथ अनावश्यक लाइनहरूबाट छुटकारा पाउनुहोस् शून्य स्तम्भ द्वारा लेनदेन कोड:

    
    

• डाटा खोज्न र लुकअप गर्न VLOOKUP प्रकार्य प्रयोग गर्दै
• VLOOKUP (VLOOKUP) को प्रयोग केस-संवेदनशील छ
• XNUMXD VLOOKUP (VLOOKUP)