जवळचा नंबर शोधत आहे - माझ्या जवळ हेल्दी फूड

सामग्री

सराव मध्ये, बर्याचदा अशी प्रकरणे असतात जेव्हा आपण आणि मला दिलेल्या संख्येच्या संबंधात सेट (टेबल) मध्ये सर्वात जवळचे मूल्य शोधण्याची आवश्यकता असते. हे असू शकते, उदाहरणार्थ:

व्हॉल्यूमवर अवलंबून सूटची गणना.
योजनेच्या अंमलबजावणीवर अवलंबून बोनसच्या रकमेची गणना.
अंतरावर अवलंबून शिपिंग दरांची गणना.
मालासाठी योग्य कंटेनरची निवड इ.

शिवाय, परिस्थितीनुसार वर आणि खाली अशा दोन्ही बाजूने गोलाकार आवश्यक असू शकतो.

अशा समस्येचे निराकरण करण्याचे अनेक मार्ग आहेत - स्पष्ट आणि इतके स्पष्ट नाही. चला त्या क्रमाने पाहू.

सुरुवातीस, घाऊक विक्रीवर सवलत देणार्‍या पुरवठादाराची कल्पना करूया आणि सवलतीची टक्केवारी खरेदी केलेल्या वस्तूंच्या प्रमाणात अवलंबून असते. उदाहरणार्थ, 5 पेक्षा जास्त तुकडे खरेदी करताना, 2% सवलत दिली जाते आणि 20 तुकड्यांमधून खरेदी करताना - आधीच 6% इ.

खरेदी केलेल्या वस्तूंचे प्रमाण प्रविष्ट करताना सवलतीच्या टक्केवारीची द्रुत आणि सुंदर गणना कशी करावी?

पद्धत 1: नेस्टेड IFs

मालिकेतील एक पद्धत "विचार करण्यासारखे काय आहे - आपल्याला उडी मारण्याची आवश्यकता आहे!". नेस्टेड फंक्शन्स वापरणे IF (तर) सेल मूल्य प्रत्येक मध्यांतरात येते की नाही हे क्रमशः तपासण्यासाठी आणि संबंधित श्रेणीसाठी सूट प्रदर्शित करण्यासाठी. परंतु या प्रकरणातील सूत्र खूप त्रासदायक ठरू शकते:

जवळचा क्रमांक शोधत आहे

मला असे वाटते की अशा "मॉन्स्टर डॉल" डीबग करणे किंवा काही काळानंतर त्यात काही नवीन अटी जोडण्याचा प्रयत्न करणे मनोरंजक आहे.

याव्यतिरिक्त, मायक्रोसॉफ्ट एक्सेलमध्ये IF फंक्शनसाठी नेस्टिंग मर्यादा आहे - जुन्या आवृत्त्यांमध्ये 7 वेळा आणि नवीन आवृत्त्यांमध्ये 64 वेळा. जर तुम्हाला आणखी गरज असेल तर?

पद्धत 2. मध्यांतर दृश्यासह VLOOKUP

ही पद्धत अधिक कॉम्पॅक्ट आहे. सूट टक्केवारीची गणना करण्यासाठी, पौराणिक कार्य वापरा व्हीपीआर (VLOOKUP) अंदाजे शोध मोडमध्ये:

जवळचा क्रमांक शोधत आहे

जेथे

B4 - पहिल्या व्यवहारातील वस्तूंच्या प्रमाणाचे मूल्य ज्यासाठी आम्ही सवलत शोधत आहोत
$G$4:$H$8 – डिस्काउंट टेबलची लिंक – “शीर्षलेख” शिवाय आणि $ चिन्हासह निश्चित केलेल्या पत्त्यांसह.
2 — डिस्काउंट टेबलमधील कॉलमची क्रमिक संख्या ज्यामधून आम्हाला सूट मूल्य मिळवायचे आहे
खरे - येथे "कुत्रा" पुरला आहे. शेवटचे फंक्शन आर्ग्युमेंट म्हणून असल्यास व्हीपीआर निर्दिष्ट करा खोटे बोलणे (असत्य) किंवा 0, नंतर फंक्शन शोधेल कडक सामना प्रमाण स्तंभात (आणि आमच्या बाबतीत ते #N/A त्रुटी देईल, कारण सवलत सारणीमध्ये कोणतेही मूल्य 49 नाही). पण त्याऐवजी जर खोटे बोलणे लिहू खरे (खरे) किंवा 1, नंतर फंक्शन अचूक शोधणार नाही, परंतु सर्वात लहान मूल्य आणि आम्हाला सवलतीची टक्केवारी देईल.

सवलत सारणी पहिल्या स्तंभानुसार चढत्या क्रमाने क्रमवारी लावणे ही या पद्धतीची नकारात्मक बाजू आहे. असे कोणतेही वर्गीकरण नसल्यास (किंवा ते उलट क्रमाने केले जाते), तर आमचे सूत्र कार्य करणार नाही:

जवळचा क्रमांक शोधत आहे

त्यानुसार, हा दृष्टिकोन फक्त सर्वात जवळचे सर्वात लहान मूल्य शोधण्यासाठी वापरला जाऊ शकतो. जर तुम्हाला सर्वात जवळचा सर्वात मोठा शोधायचा असेल तर तुम्हाला वेगळा दृष्टिकोन वापरावा लागेल.

पद्धत 3. INDEX आणि MATCH फंक्शन्स वापरून सर्वात जवळचे सर्वात मोठे शोधणे

आता आपल्या समस्येकडे दुसऱ्या बाजूने पाहू. समजा आम्ही विविध क्षमतेच्या औद्योगिक पंपांचे अनेक मॉडेल विकतो. डावीकडील विक्री तक्ता ग्राहकाला आवश्यक असलेली शक्ती दर्शविते. आम्हाला जवळच्या जास्तीत जास्त किंवा समान शक्तीचा पंप निवडण्याची आवश्यकता आहे, परंतु प्रकल्पासाठी आवश्यक असलेल्यापेक्षा कमी नाही.

VLOOKUP फंक्शन येथे मदत करणार नाही, त्यामुळे तुम्हाला त्याचे अॅनालॉग वापरावे लागतील – INDEX फंक्शन्सचा एक समूह (INDEX) आणि अधिक उघड (सामना):

जवळचा क्रमांक शोधत आहे

येथे, शेवटचे आर्ग्युमेंट -1 असलेले MATCH फंक्शन सर्वात जवळचे सर्वात मोठे मूल्य शोधण्याच्या मोडमध्ये कार्य करते आणि INDEX फंक्शन नंतर आपल्याला आवश्यक असलेले मॉडेल नाव जवळच्या स्तंभातून काढते.

पद्धत 4. नवीन फंक्शन VIEW (XLOOKUP)

तुमच्याकडे Office 365 ची आवृत्ती सर्व अद्यतनांसह स्थापित असल्यास, VLOOKUP ऐवजी (VLOOKUP) तुम्ही त्याचे analogue वापरू शकता - VIEW फंक्शन (XLOOKUP), ज्याचे मी आधीच तपशीलवार विश्लेषण केले आहे:

जवळचा क्रमांक शोधत आहे

येथे:

B4 - ज्या उत्पादनासाठी आम्ही सवलत शोधत आहोत त्या उत्पादनाच्या परिमाणाचे प्रारंभिक मूल्य
$G$4:$G$8 - ज्या श्रेणीत आम्ही सामने शोधत आहोत
$H$4:$H$8 - परिणामांची श्रेणी ज्यामधून तुम्हाला सूट परत करायची आहे
चौथा युक्तिवाद (-1) मध्ये अचूक जुळण्याऐवजी आम्हाला पाहिजे असलेल्या सर्वात जवळच्या सर्वात लहान संख्येचा शोध समाविष्ट आहे.

या पद्धतीचे फायदे असे आहेत की सवलत सारणी क्रमवारी लावण्याची गरज नाही आणि आवश्यक असल्यास शोधण्याची क्षमता, केवळ सर्वात जवळील सर्वात लहान नाही तर सर्वात जवळचे सर्वात मोठे मूल्य देखील आहे. या प्रकरणातील शेवटचा युक्तिवाद 1 असेल.

परंतु, दुर्दैवाने, प्रत्येकाकडे अद्याप हे वैशिष्ट्य नाही - फक्त Office 365 चे आनंदी मालक.

पद्धत 5. पॉवर क्वेरी

एक्सेलसाठी तुम्ही शक्तिशाली आणि पूर्णपणे मोफत पॉवर क्वेरी अॅड-इनशी परिचित नसल्यास, तुम्ही येथे आहात. आपण आधीच परिचित असल्यास, नंतर आमच्या समस्येचे निराकरण करण्यासाठी ते वापरण्याचा प्रयत्न करूया.

प्रथम काही तयारीचे काम करूया:

चला कीबोर्ड शॉर्टकट वापरून आमच्या स्रोत सारण्या डायनॅमिक (स्मार्ट) मध्ये रूपांतरित करूया Ctrl+T किंवा संघ मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित).
स्पष्टतेसाठी, त्यांची नावे देऊ. विक्री и सवलत टॅब रचनाकार (डिझाइन).
बटण वापरून प्रत्येक टेबल्स पॉवर क्वेरीमध्ये लोड करा टेबल/श्रेणीतून टॅब डेटा (डेटा — सारणी/श्रेणीवरून). Excel च्या अलीकडील आवृत्त्यांमध्ये, या बटणाचे नाव बदलले गेले आहे पाने सह (पत्रकावरून).
आमच्या उदाहरणाप्रमाणे (“मालांचे प्रमाण” आणि “…”) प्रमाणे, जर टेबल्सना परिमाणांसह भिन्न स्तंभ नावे असतील, तर त्यांना पॉवर क्वेरीमध्ये पुनर्नामित करणे आवश्यक आहे आणि तेच नाव दिले पाहिजे.
त्यानंतर, पॉवर क्वेरी एडिटर विंडोमधील कमांड निवडून तुम्ही एक्सेलवर परत येऊ शकता मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि लोड करा… (मुख्यपृष्ठ — बंद करा आणि लोड करा — बंद करा आणि त्यावर लोड करा...) आणि नंतर पर्याय फक्त एक कनेक्शन तयार करा (केवळ कनेक्शन तयार करा).
मग सर्वात मनोरंजक सुरू होते. जर तुम्हाला पॉवर क्वेरीचा अनुभव असेल, तर मी असे गृहीत धरतो की या दोन तक्त्याला जोडणी क्वेरी (मर्ज) a la VLOOKUP सह विलीन करण्याच्या दिशेने पुढील विचारसरणी असावी, जसे मागील पद्धतीमध्ये होते. खरं तर, आम्हाला ऍड मोडमध्ये विलीन करणे आवश्यक आहे, जे पहिल्या दृष्टीक्षेपात अजिबात स्पष्ट नाही. एक्सेल टॅबमध्ये निवडा डेटा - डेटा मिळवा - विनंत्या एकत्र करा - जोडा (डेटा — डेटा मिळवा — क्वेरी एकत्र करा — संलग्न करा) आणि मग आमचे टेबल विक्री и सवलत दिसत असलेल्या विंडोमध्ये:
वर क्लिक केल्यानंतर OK आमची टेबले एकमेकांच्या खाली - एक संपूर्ण मध्ये चिकटलेली असतील. कृपया लक्षात घ्या की या सारण्यांमधील मालाचे प्रमाण असलेले स्तंभ एकमेकांच्या खाली पडले आहेत, कारण. त्यांचे समान नाव आहे:
विक्री सारणीतील पंक्तींचा मूळ क्रम तुमच्यासाठी महत्त्वाचा असल्यास, त्यानंतरच्या सर्व परिवर्तनानंतर तुम्ही ते पुनर्संचयित करू शकता, कमांड वापरून आमच्या टेबलमध्ये क्रमांकित स्तंभ जोडा. स्तंभ जोडणे - अनुक्रमणिका स्तंभ (स्तंभ जोडा — अनुक्रमणिका स्तंभ). ओळींचा क्रम तुमच्यासाठी काही फरक पडत नसल्यास, तुम्ही ही पायरी वगळू शकता.
आता, टेबलच्या शीर्षलेखातील ड्रॉप-डाउन सूची वापरून, स्तंभानुसार क्रमवारी लावा प्रमाण चढत्या:
आणि मुख्य युक्ती: स्तंभ शीर्षलेखावर उजवे-क्लिक करा सवलत एक संघ निवडा भरा - खाली (भरणे - खाली). सह रिक्त पेशी निरर्थक मागील सवलत मूल्यांसह स्वयंचलितपणे भरले:
स्तंभानुसार क्रमवारी करून पंक्तींचा मूळ क्रम पुनर्संचयित करणे बाकी आहे निर्देशांक (आपण नंतर ते सुरक्षितपणे हटवू शकता) आणि फिल्टरसह अनावश्यक रेषांपासून मुक्त व्हा निरर्थक स्तंभानुसार व्यवहार कोड: