సమీప సంఖ్యను కనుగొనడం

విషయ సూచిక

ఆచరణలో, మీరు మరియు నేను ఇచ్చిన సంఖ్యకు సంబంధించి సెట్ (టేబుల్) లో సన్నిహిత విలువను కనుగొనవలసి వచ్చినప్పుడు చాలా తరచుగా సందర్భాలు ఉన్నాయి. ఇది కావచ్చు, ఉదాహరణకు:

వాల్యూమ్ ఆధారంగా తగ్గింపు గణన.
ప్రణాళిక అమలుపై ఆధారపడి బోనస్‌ల మొత్తాన్ని లెక్కించడం.
దూరాన్ని బట్టి షిప్పింగ్ రేట్ల గణన.
వస్తువుల కోసం తగిన కంటైనర్ల ఎంపిక మొదలైనవి.

అంతేకాకుండా, పరిస్థితిని బట్టి పైకి క్రిందికి రౌండింగ్ అవసరం కావచ్చు.

అటువంటి సమస్యను పరిష్కరించడానికి అనేక మార్గాలు ఉన్నాయి - స్పష్టమైన మరియు అంత స్పష్టంగా లేవు. వాటిని వరుసగా చూద్దాం.

ప్రారంభించడానికి, హోల్‌సేల్‌పై డిస్కౌంట్‌లు ఇచ్చే సరఫరాదారుని ఊహించుకుందాం మరియు డిస్కౌంట్ శాతం కొనుగోలు చేసిన వస్తువుల పరిమాణంపై ఆధారపడి ఉంటుంది. ఉదాహరణకు, 5 ముక్కల కంటే ఎక్కువ కొనుగోలు చేసేటప్పుడు, 2% తగ్గింపు ఇవ్వబడుతుంది మరియు 20 ముక్కల నుండి కొనుగోలు చేసేటప్పుడు - ఇప్పటికే 6%, మొదలైనవి.

కొనుగోలు చేసిన వస్తువుల పరిమాణాన్ని నమోదు చేసేటప్పుడు తగ్గింపు శాతాన్ని త్వరగా మరియు అందంగా ఎలా లెక్కించాలి?

విధానం 1: నెస్టెడ్ IFలు

సిరీస్ నుండి ఒక పద్ధతి "ఆలోచించడానికి ఏమి ఉంది - మీరు దూకాలి!". సమూహ ఫంక్షన్లను ఉపయోగించడం IF (IF) సెల్ విలువ ప్రతి విరామాలలోకి వస్తుందో లేదో క్రమానుగతంగా తనిఖీ చేయడానికి మరియు సంబంధిత పరిధికి తగ్గింపును ప్రదర్శించడానికి. కానీ ఈ సందర్భంలో సూత్రం చాలా గజిబిజిగా మారుతుంది:

సమీప సంఖ్యను కనుగొనడం

అటువంటి "రాక్షసుడు బొమ్మ"ని డీబగ్ చేయడం లేదా కొంత సమయం తర్వాత దానికి కొన్ని కొత్త షరతులను జోడించడం సరదాగా ఉంటుందని నేను భావిస్తున్నాను.

అదనంగా, Microsoft Excel IF ఫంక్షన్ కోసం గూడు పరిమితిని కలిగి ఉంది - పాత సంస్కరణల్లో 7 సార్లు మరియు కొత్త సంస్కరణల్లో 64 సార్లు. మీకు మరింత అవసరమైతే ఏమి చేయాలి?

విధానం 2. విరామ వీక్షణతో VLOOKUP

ఈ పద్ధతి మరింత కాంపాక్ట్. తగ్గింపు శాతాన్ని లెక్కించడానికి, లెజెండరీ ఫంక్షన్‌ని ఉపయోగించండి VPR (VLOOKUP) సుమారు శోధన మోడ్‌లో:

సమీప సంఖ్యను కనుగొనడం

(ఇక్కడ

B4 - మేము డిస్కౌంట్ కోసం చూస్తున్న మొదటి లావాదేవీలో వస్తువుల పరిమాణం యొక్క విలువ
$G$4:$H$8 – డిస్కౌంట్ టేబుల్‌కి లింక్ – “హెడర్” లేకుండా మరియు $ గుర్తుతో పరిష్కరించబడిన చిరునామాలతో.
2 — డిస్కౌంట్ టేబుల్‌లోని కాలమ్ యొక్క ఆర్డినల్ సంఖ్య, దాని నుండి మనం తగ్గింపు విలువను పొందాలనుకుంటున్నాము
TRUE - ఇక్కడే "కుక్క" ఖననం చేయబడింది. చివరి ఫంక్షన్ వాదనగా ఉంటే VPR పేర్కొనవచ్చు అబద్ధం (తప్పు) లేదా 0, అప్పుడు ఫంక్షన్ చూస్తుంది కఠినమైన మ్యాచ్ పరిమాణం కాలమ్‌లో (మరియు మా విషయంలో ఇది #N/A లోపాన్ని ఇస్తుంది, ఎందుకంటే తగ్గింపు పట్టికలో విలువ 49 లేదు). కానీ బదులుగా ఉంటే అబద్ధం వ్రాయడానికి TRUE (నిజం) లేదా 1, అప్పుడు ఫంక్షన్ ఖచ్చితమైన కోసం కాదు, కానీ కనిపిస్తుంది సమీప చిన్నది విలువ మరియు మనకు అవసరమైన తగ్గింపు శాతాన్ని ఇస్తుంది.

ఈ పద్ధతి యొక్క ప్రతికూలత ఏమిటంటే, డిస్కౌంట్ పట్టికను మొదటి నిలువు వరుస ద్వారా ఆరోహణ క్రమంలో క్రమబద్ధీకరించడం. అటువంటి సార్టింగ్ లేకపోతే (లేదా అది రివర్స్ ఆర్డర్‌లో చేయబడుతుంది), అప్పుడు మా ఫార్ములా పనిచేయదు:

సమీప సంఖ్యను కనుగొనడం

దీని ప్రకారం, ఈ విధానం సమీప చిన్న విలువను కనుగొనడానికి మాత్రమే ఉపయోగించబడుతుంది. మీరు సమీపంలోని అతిపెద్దదాన్ని కనుగొనవలసి వస్తే, మీరు వేరే విధానాన్ని ఉపయోగించాలి.

విధానం 3. INDEX మరియు MATCH ఫంక్షన్‌లను ఉపయోగించి సమీపంలోని అతి పెద్దదాన్ని కనుగొనడం

ఇప్పుడు మన సమస్యను మరొక వైపు నుండి చూద్దాం. మేము వివిధ సామర్థ్యాల పారిశ్రామిక పంపుల యొక్క అనేక నమూనాలను విక్రయిస్తాము. ఎడమ వైపున ఉన్న సేల్స్ టేబుల్ కస్టమర్‌కు అవసరమైన శక్తిని చూపుతుంది. మేము సమీప గరిష్ట లేదా సమాన శక్తి యొక్క పంపును ఎంచుకోవాలి, కానీ ప్రాజెక్ట్ ద్వారా అవసరమైన దాని కంటే తక్కువ కాదు.

VLOOKUP ఫంక్షన్ ఇక్కడ సహాయం చేయదు, కాబట్టి మీరు దాని అనలాగ్‌ని ఉపయోగించాలి - INDEX ఫంక్షన్‌ల సమూహం (ఇండెక్స్) మరియు మరింత బహిర్గతం (మ్యాచ్):

సమీప సంఖ్యను కనుగొనడం

ఇక్కడ, చివరి ఆర్గ్యుమెంట్ -1తో ఉన్న MATCH ఫంక్షన్ సమీప అతిపెద్ద విలువను కనుగొనే మోడ్‌లో పని చేస్తుంది మరియు INDEX ఫంక్షన్ ప్రక్కనే ఉన్న నిలువు వరుస నుండి మనకు అవసరమైన మోడల్ పేరును సంగ్రహిస్తుంది.

విధానం 4. కొత్త ఫంక్షన్ వీక్షణ (XLOOKUP)

మీరు ఇన్‌స్టాల్ చేసిన అన్ని అప్‌డేట్‌లతో కూడిన Office 365 సంస్కరణను కలిగి ఉంటే, VLOOKUPకి బదులుగా (VLOOKUP) మీరు దాని అనలాగ్‌ని ఉపయోగించవచ్చు - VIEW ఫంక్షన్ (XLOOKUP), నేను ఇప్పటికే వివరంగా విశ్లేషించాను:

సమీప సంఖ్యను కనుగొనడం

ఇక్కడ:

B4 - మేము డిస్కౌంట్ కోసం చూస్తున్న ఉత్పత్తి పరిమాణం యొక్క ప్రారంభ విలువ
$G$4:$G$8 - మేము మ్యాచ్‌ల కోసం వెతుకుతున్న పరిధి
$H$4:$H$8 - మీరు తగ్గింపును తిరిగి ఇవ్వాలనుకుంటున్న ఫలితాల శ్రేణి
నాల్గవ వాదన (-1) ఖచ్చితమైన సరిపోలికకు బదులుగా మనకు కావలసిన సమీప చిన్న సంఖ్య కోసం శోధనను కలిగి ఉంటుంది.

ఈ పద్ధతి యొక్క ప్రయోజనాలు ఏమిటంటే, డిస్కౌంట్ పట్టికను క్రమబద్ధీకరించాల్సిన అవసరం లేదు మరియు అవసరమైతే, సమీప చిన్నది మాత్రమే కాకుండా, సమీప అతిపెద్ద విలువను కూడా శోధించే సామర్థ్యం. ఈ కేసులో చివరి వాదన 1 అవుతుంది.

కానీ, దురదృష్టవశాత్తు, ప్రతి ఒక్కరికీ ఇంకా ఈ ఫీచర్ లేదు - Office 365 యొక్క సంతోషకరమైన యజమానులు మాత్రమే.

విధానం 5. పవర్ ప్రశ్న

Excel కోసం శక్తివంతమైన మరియు పూర్తిగా ఉచిత పవర్ క్వెరీ యాడ్-ఇన్ మీకు ఇంకా తెలియకపోతే, మీరు ఇక్కడ ఉన్నారు. మీకు ఇప్పటికే తెలిసి ఉంటే, మా సమస్యను పరిష్కరించడానికి దాన్ని ఉపయోగించడానికి ప్రయత్నిద్దాం.

ముందుగా కొన్ని సన్నాహక పనులు చేద్దాం:

కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించి మన మూల పట్టికలను డైనమిక్ (స్మార్ట్)గా మారుద్దాం Ctrl+T లేదా జట్టు హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి (హోమ్ — టేబుల్ లాగా ఫార్మాట్ చేయండి).
స్పష్టత కోసం, వాటికి పేర్లు ఇద్దాం. అమ్మకాలు и డిస్కౌంట్ టాబ్ నమూనా రచయిత (రూపకల్పన).
బటన్‌ని ఉపయోగించి ప్రతి టేబుల్‌ను పవర్ క్వెరీగా లోడ్ చేయండి పట్టిక/పరిధి నుండి టాబ్ సమాచారం (డేటా - పట్టిక/పరిధి నుండి). Excel యొక్క ఇటీవలి సంస్కరణల్లో, ఈ బటన్ పేరు మార్చబడింది ఆకులతో (షీట్ నుండి).
మా ఉదాహరణలో ("వస్తువుల పరిమాణం" మరియు "మొత్తం నుండి ...") వంటి పరిమాణాలతో పట్టికలు వేర్వేరు నిలువు వరుస పేర్లను కలిగి ఉంటే, అవి తప్పనిసరిగా పవర్ క్వెరీలో పేరు మార్చబడాలి మరియు అదే పేరు పెట్టాలి.
ఆ తర్వాత, మీరు పవర్ క్వెరీ ఎడిటర్ విండోలో ఆదేశాన్ని ఎంచుకోవడం ద్వారా ఎక్సెల్‌కి తిరిగి రావచ్చు హోమ్ — మూసివేయి మరియు లోడ్ చేయండి — మూసివేయండి మరియు లోడ్ చేయండి… (హోమ్ — మూసివేయి&లోడ్ చేయండి — మూసివేయండి&లోడ్ చేయండి...) ఆపై ఎంపిక కేవలం కనెక్షన్‌ని సృష్టించండి (కనెక్షన్ మాత్రమే సృష్టించు).
అప్పుడు అత్యంత ఆసక్తికరమైన ప్రారంభమవుతుంది. మీకు పవర్ క్వెరీలో అనుభవం ఉన్నట్లయితే, మునుపటి పద్ధతిలో మాదిరిగానే ఈ రెండు టేబుల్‌లను జాయిన్ క్వెరీ (విలీనం) a la VLOOKUPతో విలీనం చేసే దిశలో తదుపరి ఆలోచనలు ఉండాలని నేను అనుకుంటాను. వాస్తవానికి, మేము యాడ్ మోడ్‌లో విలీనం చేయవలసి ఉంటుంది, ఇది మొదటి చూపులో స్పష్టంగా ఉండదు. ఎక్సెల్ ట్యాబ్‌లో ఎంచుకోండి డేటా - డేటా పొందండి - అభ్యర్థనలను కలపండి - జోడించండి (డేటా — డేటా పొందండి — ప్రశ్నలను కలపండి — అనుబంధం) ఆపై మా పట్టికలు అమ్మకాలు и డిస్కౌంట్ కనిపించే విండోలో:
క్లిక్ చేసిన తర్వాత OK మా పట్టికలు ఒకే మొత్తంలో అతుక్కొని ఉంటాయి - ఒకదానికొకటి కింద. దయచేసి ఈ పట్టికలలోని వస్తువుల పరిమాణంతో నిలువు వరుసలు ఒకదానికొకటి కిందకి వస్తాయి, ఎందుకంటే. వారికి ఒకే పేరు ఉంది:
సేల్స్ టేబుల్‌లోని వరుసల అసలైన క్రమం మీకు ముఖ్యమైనది అయితే, అన్ని తదుపరి పరివర్తనల తర్వాత మీరు దానిని పునరుద్ధరించవచ్చు, ఆదేశాన్ని ఉపయోగించి మా టేబుల్‌కి నంబర్‌లు గల నిలువు వరుసను జోడించండి. కాలమ్ - ఇండెక్స్ కాలమ్ కలుపుతోంది (నిలువు వరుసను జోడించండి — సూచిక నిలువు వరుస). పంక్తుల క్రమం మీకు పట్టింపు లేకపోతే, మీరు ఈ దశను దాటవేయవచ్చు.
ఇప్పుడు, పట్టిక యొక్క హెడర్‌లోని డ్రాప్-డౌన్ జాబితాను ఉపయోగించి, దానిని కాలమ్ ద్వారా క్రమబద్ధీకరించండి మొత్తము ఆరోహణ:
మరియు ప్రధాన ట్రిక్: కాలమ్ హెడర్‌పై కుడి-క్లిక్ చేయండి డిస్కౌంట్ జట్టును ఎంచుకోండి పూరించండి - డౌన్ (ఫిల్ - డౌన్). ఖాళీ సెల్స్ తో శూన్య మునుపటి తగ్గింపు విలువలతో స్వయంచాలకంగా పూరించబడుతుంది:
నిలువు వరుసల వారీగా క్రమబద్ధీకరించడం ద్వారా అడ్డు వరుసల అసలు క్రమాన్ని పునరుద్ధరించడానికి ఇది మిగిలి ఉంది ఇండెక్స్ (మీరు దీన్ని తర్వాత సురక్షితంగా తొలగించవచ్చు) మరియు ఫిల్టర్‌తో అనవసరమైన పంక్తులను వదిలించుకోండి శూన్య కాలమ్ ద్వారా లావాదేవీ కోడ్: