అధునాతన VLOOKUP ఉదాహరణలు: బహుళ ప్రమాణాల శోధన

ఫంక్షన్‌పై మా ట్యుటోరియల్ రెండవ భాగంలో VPR (VLOOKUP) Excelలో, మీరు మొత్తం శక్తిని నిర్దేశించడంలో సహాయపడే కొన్ని ఉదాహరణలను మేము విశ్లేషిస్తాము VPR అత్యంత ప్రతిష్టాత్మకమైన Excel టాస్క్‌లను పరిష్కరించడానికి. ఈ ఫీచర్ ఎలా పనిచేస్తుందనే దాని గురించి మీకు ఇప్పటికే ప్రాథమిక జ్ఞానం ఉందని ఉదాహరణలు ఊహిస్తాయి. కాకపోతే, మీరు ఈ ట్యుటోరియల్ యొక్క మొదటి భాగంతో ప్రారంభించేందుకు ఆసక్తి కలిగి ఉండవచ్చు, ఇది సింటాక్స్ మరియు ప్రాథమిక వినియోగాన్ని వివరిస్తుంది. VPRPraktiske Push-Benachrichtigungen informerer Dich außerdem retzeitig über die Möglichkeit, Dein Windel-Abo anzupassen.

• బహుళ ప్రమాణాల ద్వారా Excelలో శోధించండి
• మేము VLOOKUPని ఉపయోగించి 2వ, 3వ, మొదలైన విలువలను సంగ్రహిస్తాము
• కావలసిన విలువ యొక్క అన్ని పునరావృత్తులు తిరిగి పొందండి
• తెలిసిన అడ్డు వరుస మరియు నిలువు వరుస ద్వారా XNUMXD శోధన
• ఒక ఫార్ములాలో బహుళ VLOOKUPలను ఉపయోగించడం
• వివిధ పట్టికల నుండి డేటా యొక్క డైనమిక్ ప్రత్యామ్నాయం

బహుళ ప్రమాణాల ద్వారా Excelలో శోధించండి

ఫంక్షన్ VPR Excel అనేది డేటాబేస్‌లో నిర్దిష్ట విలువ కోసం శోధనలను నిర్వహించడానికి నిజంగా శక్తివంతమైన సాధనం. అయితే, ఒక ముఖ్యమైన పరిమితి ఉంది - దాని వాక్యనిర్మాణం మీరు ఒక విలువ కోసం మాత్రమే శోధించడానికి అనుమతిస్తుంది. మీరు అనేక షరతుల ద్వారా శోధించాలనుకుంటే ఏమి చేయాలి? మీరు క్రింద పరిష్కారాన్ని కనుగొంటారు.

ఉదాహరణ 1: 2 విభిన్న ప్రమాణాల ద్వారా శోధించండి

మేము ఆర్డర్‌ల జాబితాను కలిగి ఉన్నామని అనుకుందాం మరియు మేము కనుగొనాలనుకుంటున్నాము వస్తువుల పరిమాణం (Qty.), రెండు ప్రమాణాల ఆధారంగా – క్లయింట్ పేరు (కస్టమర్) మరియు ఉత్పత్తి పేరు (ఉత్పత్తి). దిగువ పట్టిక నుండి చూడగలిగే విధంగా, ప్రతి కొనుగోలుదారుడు అనేక రకాల వస్తువులను ఆర్డర్ చేసినందున విషయం క్లిష్టంగా ఉంటుంది:

రెగ్యులర్ ఫంక్షన్ VPR ఈ దృష్టాంతంలో పని చేయదు ఎందుకంటే ఇది అందించిన శోధన విలువకు సరిపోయే మొదటి విలువను తిరిగి ఇస్తుంది. ఉదాహరణకు, మీరు ఒక వస్తువు పరిమాణం తెలుసుకోవాలనుకుంటే స్వీట్లు'కొనుగోలుదారు ఆదేశించాడు జెరెమీ హిల్, కింది సూత్రాన్ని వ్రాయండి:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- ఈ ఫార్ములా ఫలితాన్ని అందిస్తుంది 15ఉత్పత్తికి అనుగుణంగా యాపిల్స్, ఎందుకంటే ఇది సరిపోలే మొదటి విలువ.

ఒక సాధారణ ప్రత్యామ్నాయం ఉంది - కావలసిన అన్ని ప్రమాణాలను కలపడానికి అదనపు కాలమ్‌ను సృష్టించండి. మా ఉదాహరణలో, ఇవి నిలువు వరుసలు క్లయింట్ పేరు (కస్టమర్) మరియు ఉత్పత్తి పేరు (ఉత్పత్తి). విలీనమైన నిలువు వరుస ఎల్లప్పుడూ శోధన పరిధిలో ఎడమవైపున ఉన్న నిలువు వరుస అని మర్చిపోవద్దు, ఎందుకంటే ఇది ఫంక్షన్‌కు సంబంధించిన ఎడమ నిలువు వరుస VPR విలువ కోసం చూస్తున్నప్పుడు పైకి చూస్తుంది.

కాబట్టి, మీరు పట్టికకు సహాయక కాలమ్‌ని జోడించి, కింది ఫార్ములాను దాని అన్ని సెల్‌లపై కాపీ చేయండి: =B2&C2. మీరు స్ట్రింగ్ మరింత చదవగలిగేలా ఉండాలని కోరుకుంటే, మీరు ఒక ఖాళీతో కలిపి విలువలను వేరు చేయవచ్చు: =B2&» «&C2. ఆ తరువాత, మీరు క్రింది సూత్రాన్ని ఉపయోగించవచ్చు:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

సెల్ ఎక్కడ ఉంది B1 వాదన యొక్క ఏకీకృత విలువను కలిగి ఉంటుంది శోధన_ విలువ (lookup_value) మరియు 4 - వాదన col_index_num (column_number), అనగా తిరిగి పొందవలసిన డేటాను కలిగి ఉన్న నిలువు వరుస సంఖ్య.

ఉదాహరణ 2: మరొక షీట్‌లో పట్టిక వీక్షించబడే రెండు ప్రమాణాల ద్వారా VLOOKUP

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

మునుపటి ఉదాహరణలో వలె, మిశ్రమ విలువలతో లుక్అప్ పట్టికలో మీకు సహాయక కాలమ్ అవసరం. ఈ నిలువు వరుస తప్పనిసరిగా శోధన పరిధిలో ఎడమవైపు నిలువు వరుస అయి ఉండాలి.

కాబట్టి సూత్రం VPR ఇలా ఉండవచ్చు:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

ఇక్కడ, B మరియు C నిలువు వరుసలు కస్టమర్ పేర్లు మరియు ఉత్పత్తి పేర్లు మరియు లింక్‌ను కలిగి ఉంటాయి ఆర్డర్‌లు!$A&$2:$D$2 మరొక షీట్‌లో చూడవలసిన పట్టికను నిర్వచిస్తుంది.

సూత్రాన్ని మరింత చదవగలిగేలా చేయడానికి, మీరు వీక్షణ పరిధికి పేరు పెట్టవచ్చు, ఆపై ఫార్ములా చాలా సరళంగా కనిపిస్తుంది:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

ఫార్ములా పని చేయడానికి, మీరు చూస్తున్న పట్టికలో ఎడమవైపు నిలువు వరుసలోని విలువలు శోధన ప్రమాణాలలో ఉన్న విధంగానే కలపాలి. పై చిత్రంలో, మేము u2bu2band విలువలను కలిపి వాటి మధ్య ఖాళీని ఉంచాము, అదే విధంగా మీరు ఫంక్షన్ యొక్క మొదటి వాదనలో (BXNUMX& "" & CXNUMX) చేయాలి.

గుర్తుంచుకో! ఫంక్షన్ VPR 255 అక్షరాలకు పరిమితం చేయబడింది, ఇది 255 అక్షరాల కంటే ఎక్కువ పొడవు ఉన్న విలువ కోసం శోధించదు. దీన్ని గుర్తుంచుకోండి మరియు కావలసిన విలువ యొక్క పొడవు ఈ పరిమితిని మించకుండా చూసుకోండి.

సహాయక నిలువు వరుసను జోడించడం చాలా సొగసైనది కాదని మరియు ఎల్లప్పుడూ ఆమోదయోగ్యమైన పరిష్కారం కాదని నేను అంగీకరిస్తున్నాను. మీరు హెల్పర్ కాలమ్ లేకుండా కూడా అదే చేయవచ్చు, కానీ దీనికి ఫంక్షన్ల కలయికతో మరింత క్లిష్టమైన ఫార్ములా అవసరం INDEX (INDEX) మరియు MATCH (మరింత బహిర్గతమైంది).

మేము VLOOKUPని ఉపయోగించి 2వ, 3వ, మొదలైన విలువలను సంగ్రహిస్తాము

అది మీకు ముందే తెలుసు VPR ఒక సరిపోలే విలువను మాత్రమే తిరిగి ఇవ్వగలదు, మరింత ఖచ్చితంగా, మొదటిది కనుగొనబడింది. అయితే వీక్షించిన శ్రేణిలో ఈ విలువ అనేకసార్లు పునరావృతం చేయబడి, మీరు వాటిలో 2వ లేదా 3వ వాటిని సంగ్రహించాలనుకుంటే? అన్ని విలువలు ఉంటే? సమస్య క్లిష్టంగా కనిపిస్తోంది, కానీ పరిష్కారం ఉంది!

టేబుల్‌లోని ఒక కాలమ్‌లో కస్టమర్‌ల పేర్లు (కస్టమర్ పేరు) మరియు ఇతర కాలమ్‌లో వారు కొనుగోలు చేసిన ఉత్పత్తులు (ఉత్పత్తి) ఉన్నాయని అనుకుందాం. ఇచ్చిన కస్టమర్ కొనుగోలు చేసిన 2వ, 3వ మరియు 4వ వస్తువులను కనుగొనడానికి ప్రయత్నిద్దాం.

నిలువు వరుసకు ముందు సహాయక నిలువు వరుసను జోడించడం సులభమయిన మార్గం వినియోగదారుని పేరు మరియు ప్రతి పేరు యొక్క పునరావృత సంఖ్యతో కస్టమర్ పేర్లతో నింపండి, ఉదాహరణకు, జాన్ డో 1, జాన్ డో 2 మొదలైనవి. మేము ఫంక్షన్‌ని ఉపయోగించి నంబరింగ్‌తో ట్రిక్ చేస్తాము COUNTIF (COUNTIF), కస్టమర్ పేర్లు కాలమ్ Bలో ఉన్నందున:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

ఆ తర్వాత మీరు సాధారణ ఫంక్షన్ ఉపయోగించవచ్చు VPRఅవసరమైన క్రమాన్ని కనుగొనడానికి. ఉదాహరణకి:

• కనుగొనండి 2 వ కస్టమర్ ఆర్డర్ చేసిన వస్తువు డాన్ బ్రౌన్:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• కనుగొనండి 3 వ కస్టమర్ ఆర్డర్ చేసిన వస్తువు డాన్ బ్రౌన్:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

వాస్తవానికి, మీరు క్రింది చిత్రంలో చూపిన విధంగా టెక్స్ట్‌కు బదులుగా శోధన విలువగా సెల్ సూచనను నమోదు చేయవచ్చు:

మీరు మాత్రమే చూస్తున్నట్లయితే 2-ఇ పునరావృతం, మీరు మరింత క్లిష్టమైన సూత్రాన్ని సృష్టించడం ద్వారా సహాయక కాలమ్ లేకుండా దీన్ని చేయవచ్చు:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

ఈ సూత్రంలో:

• $F$2 – కొనుగోలుదారు పేరు ఉన్న సెల్ (ఇది మారదు, దయచేసి గమనించండి - లింక్ సంపూర్ణమైనది);
• $ బి $ - కాలమ్ వినియోగదారుని పేరు;
• Table4 – మీ టేబుల్ (ఈ స్థలం సాధారణ పరిధి కూడా కావచ్చు);
• $C16 - మీ పట్టిక లేదా పరిధి యొక్క ముగింపు సెల్.

ఈ ఫార్ములా రెండవ సరిపోలే విలువను మాత్రమే కనుగొంటుంది. మీరు మిగిలిన పునరావృతాలను సంగ్రహించవలసి వస్తే, మునుపటి పరిష్కారాన్ని ఉపయోగించండి.

మీకు అన్ని మ్యాచ్‌ల జాబితా అవసరమైతే - ఫంక్షన్ VPR ఇది సహాయకం కాదు, ఎందుకంటే ఇది ఒక సమయంలో ఒక విలువను మాత్రమే అందిస్తుంది - వ్యవధి. కానీ ఎక్సెల్ ఒక ఫంక్షన్ ఉంది INDEX (INDEX), ఇది ఈ పనిని సులభంగా ఎదుర్కోగలదు. అటువంటి ఫార్ములా ఎలా ఉంటుందో, మీరు ఈ క్రింది ఉదాహరణలో నేర్చుకుంటారు.

కావలసిన విలువ యొక్క అన్ని పునరావృత్తులు తిరిగి పొందండి

పైన చెప్పినట్లుగా VPR స్కాన్ చేసిన పరిధి నుండి అన్ని నకిలీ విలువలను సంగ్రహించలేము. దీన్ని చేయడానికి, మీకు కొన్ని ఎక్సెల్ ఫంక్షన్‌లతో రూపొందించబడిన కొంచెం సంక్లిష్టమైన ఫార్ములా అవసరం INDEX (INDEX), చిన్నది (చిన్న) మరియు ROW (LINE)

ఉదాహరణకు, దిగువ ఫార్ములా B2:B2 పరిధిలో సెల్ F16 నుండి విలువ యొక్క అన్ని పునరావృతాలను కనుగొంటుంది మరియు C నిలువు వరుసలోని అదే అడ్డు వరుసల నుండి ఫలితాన్ని అందిస్తుంది.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

ఈ శ్రేణి ఫార్ములాను సెల్‌ల వంటి బహుళ ప్రక్కనే ఉన్న సెల్‌లలోకి నమోదు చేయండి F4: F8దిగువ చిత్రంలో చూపిన విధంగా. సెల్‌ల సంఖ్య తప్పనిసరిగా శోధించిన విలువ యొక్క పునరావృతాల గరిష్ట సంఖ్యకు సమానంగా లేదా అంతకంటే ఎక్కువ ఉండాలి. క్లిక్ చేయడం మర్చిపోవద్దు Ctrl + Shift + ఎంటర్ చేయండిశ్రేణి సూత్రాన్ని సరిగ్గా నమోదు చేయడానికి.

ఇది ఎలా పని చేస్తుందో అర్థం చేసుకోవడంలో మీకు ఆసక్తి ఉంటే, ఫార్ములా వివరాల్లోకి కొంచెం డైవ్ చేద్దాం:

భాగం XX:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - సెల్ F2లోని విలువను B2:B16 పరిధిలోని ప్రతి విలువలతో సరిపోల్చండి. సరిపోలిక కనుగొనబడితే, వ్యక్తీకరణ STRING(C2:C16)-1 సంబంధిత పంక్తి సంఖ్యను అందిస్తుంది (విలువ -1 హెడర్ లైన్‌ని చేర్చకుండా మిమ్మల్ని అనుమతిస్తుంది). సరిపోలికలు లేకుంటే, ఫంక్షన్ IF (IF) ఖాళీ స్ట్రింగ్‌ని అందిస్తుంది.

ఫంక్షన్ ఫలితం IF (IF) అటువంటి క్షితిజ సమాంతర శ్రేణి ఉంటుంది: {1,"",3,"",5,"","","","","","",12,"","",""}

భాగం XX:

ROW()-3

СТРОКА()-3

ఇక్కడ ఫంక్షన్ ROW (LINE) అదనపు కౌంటర్‌గా పనిచేస్తుంది. ఫార్ములా F4:F9 కణాలలోకి కాపీ చేయబడినందున, మేము సంఖ్యను తీసివేస్తాము 3 విలువను పొందడానికి ఫంక్షన్ ఫలితం నుండి 1 సెల్ లో F4 (పంక్తి 4, 3 తీసివేయి) పొందడానికి 2 సెల్ లో F5 (పంక్తి 5, వ్యవకలనం 3) మరియు మొదలైనవి.

భాగం XX:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ఫంక్షన్ చిన్నది (చిన్న) తిరిగి వస్తుంది n-oh డేటా శ్రేణిలో అతి చిన్న విలువ. మా విషయంలో, ఏ స్థానం (చిన్నది నుండి) తిరిగి రావాలో ఫంక్షన్ ద్వారా నిర్ణయించబడుతుంది ROW (LINE) (పార్ట్ 2 చూడండి). కాబట్టి, ఒక సెల్ కోసం F4 ఫంక్షన్ చిన్న({శ్రేణి},1) తిరిగి 1 వ (చిన్న) శ్రేణి మూలకం, అనగా 1. సెల్ కోసం F5 తిరిగి 2 వ శ్రేణిలోని అతి చిన్న మూలకం, అంటే 3, మొదలైనవి

భాగం XX:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ఫంక్షన్ INDEX (INDEX) కేవలం శ్రేణిలోని నిర్దిష్ట సెల్ విలువను అందిస్తుంది సి 2: సి 16. సెల్ కోసం F4 ఫంక్షన్ సూచిక($C$2:$C$16) తిరిగి వస్తుంది యాపిల్స్కోసం F5 ఫంక్షన్ సూచిక($C$2:$C$16) తిరిగి వస్తుంది స్వీట్లు' మరియు అందువలన న.

భాగం XX:

IFERROR()

ЕСЛИОШИБКА()

చివరగా, మేము ఫంక్షన్ లోపల సూత్రాన్ని ఉంచాము IFERROR (IFERROR), ఎందుకంటే మీరు దోష సందేశంతో సంతోషించే అవకాశం లేదు #AT (#N/A) ఫార్ములా కాపీ చేయబడిన సెల్‌ల సంఖ్య వీక్షిస్తున్న పరిధిలోని నకిలీ విలువల సంఖ్య కంటే తక్కువగా ఉంటే.

తెలిసిన అడ్డు వరుస మరియు నిలువు వరుస ద్వారా XNUMXD శోధన

Excelలో XNUMXD శోధన చేయడం అనేది తెలిసిన అడ్డు వరుస మరియు నిలువు వరుస సంఖ్య ద్వారా విలువ కోసం శోధించడం. మరో మాటలో చెప్పాలంటే, మీరు నిర్దిష్ట అడ్డు వరుస మరియు నిలువు వరుస ఖండన వద్ద సెల్ విలువను సంగ్రహిస్తున్నారు.

కాబట్టి, మన టేబుల్‌కి తిరగండి మరియు ఫంక్షన్‌తో సూత్రాన్ని వ్రాస్దాం VPR, ఇది మార్చిలో విక్రయించిన నిమ్మకాయల ధర గురించి సమాచారాన్ని కనుగొంటుంది.

XNUMXD శోధనను నిర్వహించడానికి అనేక మార్గాలు ఉన్నాయి. ఎంపికలను తనిఖీ చేయండి మరియు మీకు బాగా సరిపోయేదాన్ని ఎంచుకోండి.

VLOOKUP మరియు MATCH ఫంక్షన్‌లు

మీరు ఫంక్షన్ల సమూహాన్ని ఉపయోగించవచ్చు VPR (VLOOKUP) మరియు మరింత బహిర్గతం (మ్యాచ్) ఫీల్డ్‌ల ఖండన వద్ద విలువను కనుగొనడానికి ఉత్పత్తి పేరు (స్ట్రింగ్) మరియు <span style="font-family: Mandali">నెల</span> ప్రశ్నలోని శ్రేణి (నిలువు వరుస)

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

పైన ఉన్న ఫార్ములా రెగ్యులర్ ఫంక్షన్ VPR, ఇది A2 నుండి A9 సెల్‌లలో “నిమ్మకాయలు” విలువ యొక్క ఖచ్చితమైన సరిపోలిక కోసం చూస్తుంది. కానీ మార్చి విక్రయాలు ఏ కాలమ్‌లో ఉన్నాయో మీకు తెలియనందున, మీరు మూడవ ఫంక్షన్ ఆర్గ్యుమెంట్ కోసం నిలువు వరుస సంఖ్యను సెట్ చేయలేరు. VPR. బదులుగా, ఫంక్షన్ ఉపయోగించబడుతుంది మరింత బహిర్గతంఈ నిలువు వరుసను నిర్వచించడానికి.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

మానవ భాషలోకి అనువదించబడిన ఈ ఫార్ములా అంటే:

• మేము "మార్" పాత్రల కోసం చూస్తున్నాము - వాదన శోధన_ విలువ (లుకప్_విలువ);
• A1 నుండి I1 వరకు సెల్‌లలో చూడటం - వాదన శోధన_శ్రేణి (lookup_array);
• ఖచ్చితమైన సరిపోలిక తిరిగి వస్తోంది - వాదన మ్యాచ్_రకం (మ్యాచ్_టైప్).

ఉపయోగించి 0 మూడవ వాదనలో, మీరు ఫంక్షన్లు అంటున్నారు మరింత బహిర్గతం మీరు వెతుకుతున్న విలువకు సరిగ్గా సరిపోయే మొదటి విలువ కోసం చూడండి. ఇది విలువకు సమానం FALSE (FALSE) నాల్గవ వాదన కోసం VPR.

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

SUMPRODUCT ఫంక్షన్

ఫంక్షన్ SUMPRODUCT (SUMPRODUCT) ఎంచుకున్న శ్రేణుల ఉత్పత్తుల మొత్తాన్ని అందిస్తుంది:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX మరియు MATCH ఫంక్షన్‌లు

తదుపరి వ్యాసంలో నేను ఈ విధులను వివరంగా వివరిస్తాను, కాబట్టి ప్రస్తుతానికి మీరు ఈ సూత్రాన్ని కాపీ చేయవచ్చు:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

పేరు గల పరిధులు మరియు ఖండన ఆపరేటర్

మీరు ఆ సంక్లిష్టమైన ఎక్సెల్ ఫార్ములాలన్నింటిలో లేకుంటే, మీరు ఈ దృశ్యమానమైన మరియు మరపురాని మార్గాన్ని ఇష్టపడవచ్చు:

1. పట్టికను ఎంచుకోండి, ట్యాబ్ తెరవండి సూత్రాలు (సూత్రాలు) మరియు క్లిక్ చేయండి ఎంపిక నుండి సృష్టించండి (ఎంపిక నుండి సృష్టించండి).
2. పెట్టెలను తనిఖీ చేయండి పై వరుస (పై లైన్‌లో) మరియు ఎడమ కాలమ్ (ఎడమవైపు నిలువు వరుసలో). Microsoft Excel మీ స్ప్రెడ్‌షీట్‌లోని ఎగువ వరుస మరియు ఎడమ కాలమ్‌లోని విలువల నుండి పరిధులకు పేర్లను కేటాయిస్తుంది. ఇప్పుడు మీరు సూత్రాలను సృష్టించకుండా నేరుగా ఈ పేర్లను ఉపయోగించి శోధించవచ్చు.
3. ఏదైనా ఖాళీ సెల్‌లో, వ్రాయండి =వరుస_పేరు కాలమ్_పేరు, ఉదాహరణకు ఇలా:

   = నిమ్మకాయలు మార్

   … లేదా వైస్ వెర్సా:

   =మార్ నిమ్మకాయలు

   అడ్డు వరుస మరియు నిలువు వరుస పేర్లు తప్పనిసరిగా ఖాళీతో వేరు చేయబడాలని గుర్తుంచుకోండి, ఈ సందర్భంలో ఖండన ఆపరేటర్ వలె పని చేస్తుంది.

మీరు ఒక పేరును నమోదు చేసినప్పుడు, Microsoft Excel మీరు ఫార్ములాను నమోదు చేసినట్లుగా సరిపోలే పేర్ల జాబితాతో టూల్‌టిప్‌ను చూపుతుంది.

4. ప్రెస్ ఎంటర్ మరియు ఫలితాన్ని తనిఖీ చేయండి

సాధారణంగా, మీరు పైన పేర్కొన్న పద్ధతుల్లో ఏది ఎంచుకున్నా, రెండు డైమెన్షనల్ శోధన ఫలితం ఒకే విధంగా ఉంటుంది:

ఒక ఫార్ములాలో బహుళ VLOOKUPలను ఉపయోగించడం

ప్రధాన పట్టిక మరియు శోధన పట్టిక ఒకే నిలువు వరుసను కలిగి ఉండవు మరియు ఇది సాధారణ ఫంక్షన్‌ను ఉపయోగించకుండా మిమ్మల్ని నిరోధిస్తుంది VPR. అయితే, మనకు ఆసక్తి ఉన్న సమాచారాన్ని కలిగి ఉండని మరొక పట్టిక ఉంది, కానీ ప్రధాన పట్టిక మరియు శోధన పట్టికతో ఒక సాధారణ నిలువు వరుస ఉంది.

కింది ఉదాహరణను పరిశీలిద్దాం. మనకు నిలువు వరుసతో కూడిన ప్రధాన పట్టిక ఉంది SKU (క్రొత్తది), మీరు మరొక పట్టిక నుండి సంబంధిత ధరలతో నిలువు వరుసను జోడించాలనుకుంటున్నారు. అదనంగా, మాకు 2 శోధన పట్టికలు ఉన్నాయి. మొదటిది (లుకప్ టేబుల్ 1) నవీకరించబడిన సంఖ్యలను కలిగి ఉంది SKU (క్రొత్తది) మరియు ఉత్పత్తి పేర్లు, మరియు రెండవది (లుకప్ టేబుల్ 2) - ఉత్పత్తి పేర్లు మరియు పాత సంఖ్యలు SKU (పాతది).

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

1. ఒక ఫంక్షన్ వ్రాయండి VPR, ఇది పట్టికలో ఉత్పత్తి పేరును కనుగొంటుంది శోధన పట్టిక 1ఉపయోగించి SKU, కావలసిన విలువ ప్రకారం:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   ఇక్కడ కొత్త_SKU - పేరు పరిధి $A:$B పట్టికలో శోధన పట్టిక 1, 2 - ఇది కాలమ్ B, ఇది వస్తువుల పేర్లను కలిగి ఉంటుంది (పై చిత్రాన్ని చూడండి)

2. పట్టిక నుండి ధరలను చొప్పించడానికి సూత్రాన్ని వ్రాయండి శోధన పట్టిక 2 ప్రసిద్ధ ఉత్పత్తి పేర్ల ఆధారంగా. దీన్ని చేయడానికి, మీరు ముందుగా సృష్టించిన సూత్రాన్ని కొత్త ఫంక్షన్ కోసం లుక్అప్ విలువగా అతికించండి VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   ఇక్కడ ధర - పేరు పరిధి $A:$C పట్టికలో శోధన పట్టిక 2, 3 కాలమ్ C ధరలను కలిగి ఉంటుంది.

మేము సృష్టించిన ఫార్ములా ద్వారా అందించబడిన ఫలితాన్ని దిగువ బొమ్మ చూపుతుంది:

VLOOKUP మరియు INDIRECTని ఉపయోగించి వివిధ పట్టికల నుండి డేటా యొక్క డైనమిక్ ప్రత్యామ్నాయం

ముందుగా, మనం ఒకరినొకరు సరిగ్గా అర్థం చేసుకున్నామని నిర్ధారించుకోవడానికి “వివిధ పట్టికల నుండి డేటా యొక్క డైనమిక్ ప్రత్యామ్నాయం” అనే వ్యక్తీకరణ ద్వారా మనం అర్థం ఏమిటో స్పష్టం చేద్దాం.

ఒకే ఫార్మాట్ యొక్క డేటాతో అనేక షీట్లు ఉన్నప్పుడు పరిస్థితులు ఉన్నాయి మరియు ఇచ్చిన సెల్‌లో నమోదు చేయబడిన విలువను బట్టి నిర్దిష్ట షీట్ నుండి అవసరమైన సమాచారాన్ని సేకరించడం అవసరం. దీన్ని ఒక ఉదాహరణతో వివరించడం సులభమని నేను భావిస్తున్నాను.

మీరు ఒకే ఉత్పత్తులతో మరియు ఒకే ఆకృతిలో అనేక ప్రాంతాలకు విక్రయాల నివేదికలను కలిగి ఉన్నారని ఊహించండి. మీరు నిర్దిష్ట ప్రాంతం కోసం అమ్మకాల గణాంకాలను కనుగొనాలనుకుంటున్నారు:

మీకు అలాంటి రెండు నివేదికలు మాత్రమే ఉంటే, మీరు ఫంక్షన్‌లతో అవమానకరమైన సాధారణ సూత్రాన్ని ఉపయోగించవచ్చు VPR и IF (IF) శోధించడానికి కావలసిన నివేదికను ఎంచుకోవడానికి:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

ఎక్కడ:

• $D$2 ఉత్పత్తి పేరును కలిగి ఉన్న సెల్. ఫార్ములాను ఇతర సెల్‌లకు కాపీ చేసేటప్పుడు లుక్అప్ విలువను మార్చకుండా ఉండటానికి మేము ఇక్కడ సంపూర్ణ సూచనలను ఉపయోగిస్తామని గమనించండి.
• $D3 అనేది ప్రాంతం పేరుతో ఉన్న సెల్. మేము ఫార్ములాను అదే నిలువు వరుసలోని ఇతర సెల్‌లకు కాపీ చేయాలని ప్లాన్ చేస్తున్నందున మేము సంపూర్ణ నిలువు వరుస సూచన మరియు సంబంధిత వరుస సూచనను ఉపయోగిస్తున్నాము.
• FL_Sales и CA_సేల్స్ - సంబంధిత విక్రయ నివేదికలను కలిగి ఉన్న పట్టికల పేర్లు (లేదా పేరున్న పరిధులు). ఉదాహరణకు, మీరు సాధారణ షీట్ పేర్లు మరియు సెల్ పరిధి సూచనలను ఉపయోగించవచ్చు 'FL షీట్'!$A$3:$B$10, కానీ పేరున్న పరిధులు చాలా సౌకర్యవంతంగా ఉంటాయి.

అయితే, అటువంటి అనేక పట్టికలు ఉన్నప్పుడు, ఫంక్షన్ IF ఉత్తమ పరిష్కారం కాదు. బదులుగా, మీరు ఫంక్షన్ ఉపయోగించవచ్చు పరోక్ష (INDIRECT) కావలసిన శోధన పరిధిని అందించడానికి.

మీకు బహుశా తెలిసినట్లుగా, ఫంక్షన్ పరోక్ష టెక్స్ట్ స్ట్రింగ్ ద్వారా అందించబడిన లింక్‌ను తిరిగి ఇవ్వడానికి ఉపయోగించబడుతుంది, ఇది ఇప్పుడు మనకు అవసరమైనది. కాబట్టి, పై ఫార్ములాలోని వ్యక్తీకరణను ఫంక్షన్‌తో ధైర్యంగా భర్తీ చేయండి IF ఫంక్షన్‌తో లింక్ చేయడానికి పరోక్ష. ఇక్కడ కలయిక ఉంది VPR и పరోక్ష దీనితో గొప్పగా పనిచేస్తుంది:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

ఎక్కడ:

• $D$2 – ఇది ఉత్పత్తి పేరుతో ఉన్న సెల్, సంపూర్ణ లింక్ కారణంగా ఇది మారదు.
• $D3 ప్రాంతం పేరులోని మొదటి భాగాన్ని కలిగి ఉన్న సెల్. మా ఉదాహరణలో, ఇది FL.
• _అమ్మకాలు - పేరు పెట్టబడిన అన్ని పరిధులు లేదా పట్టికల పేరు యొక్క సాధారణ భాగం. సెల్ D3లోని విలువతో కలిపినప్పుడు, ఇది అవసరమైన పరిధికి పూర్తి అర్హత కలిగిన పేరును ఏర్పరుస్తుంది. ఫంక్షన్‌కి కొత్తగా వచ్చిన వారి కోసం కొన్ని వివరాలు క్రింద ఉన్నాయి పరోక్ష.

INDIRECT మరియు VLOOKUP ఎలా పని చేస్తాయి

ముందుగా, ఫంక్షన్ యొక్క సింటాక్స్‌ని మీకు గుర్తు చేస్తాను పరోక్ష (పరోక్ష):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

మొదటి వాదన సెల్ రిఫరెన్స్ (A1 లేదా R1C1 శైలి), పరిధి పేరు లేదా టెక్స్ట్ స్ట్రింగ్ కావచ్చు. రెండవ ఆర్గ్యుమెంట్ మొదటి ఆర్గ్యుమెంట్‌లో ఏ లింక్ శైలిని కలిగి ఉందో నిర్ణయిస్తుంది:

• A1వాదన ఉంటే నిజమైన కోడ్ (TRUE) లేదా పేర్కొనబడలేదు;
• R1C1, ఉంటే FAS E (తప్పు).

మా విషయంలో, లింక్ శైలిని కలిగి ఉంటుంది A1, కాబట్టి మీరు రెండవ వాదనను విడిచిపెట్టి, మొదటిదానిపై దృష్టి పెట్టవచ్చు.

కాబట్టి మా అమ్మకాల నివేదికలకు తిరిగి వద్దాం. మీరు గుర్తుంచుకుంటే, ప్రతి నివేదిక ప్రత్యేక షీట్‌లో ఉన్న ప్రత్యేక పట్టిక. ఫార్ములా సరిగ్గా పని చేయడానికి, మీరు తప్పనిసరిగా మీ పట్టికలకు (లేదా పరిధులు) పేరు పెట్టాలి మరియు అన్ని పేర్లకు తప్పనిసరిగా ఉమ్మడి భాగం ఉండాలి. ఉదాహరణకు, ఇలా: CA_సేల్స్, FL_Sales, TX_Sales మరియు అందువలన న. మీరు చూడగలిగినట్లుగా, అన్ని పేర్లలో “_Sales” ఉంది.

ఫంక్షన్ పరోక్ష కాలమ్ D మరియు టెక్స్ట్ స్ట్రింగ్ “_Sales”లో విలువను కలుపుతుంది, తద్వారా తెలియజేస్తుంది VPR ఏ పట్టికలో వెతకాలి. సెల్ D3 "FL" విలువను కలిగి ఉంటే, సూత్రం పట్టికను శోధిస్తుంది FL_Sales, "CA" అయితే - పట్టికలో CA_సేల్స్ మరియు అందువలన న.

ఫంక్షన్ల ఫలితం VPR и పరోక్ష కింది విధంగా ఉంటుంది:

డేటా వేర్వేరు Excel పుస్తకాలలో ఉన్నట్లయితే, మీరు పేరు పెట్టబడిన పరిధికి ముందు పుస్తకం పేరును జోడించాలి, ఉదాహరణకు:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

ఫంక్షన్ అయితే పరోక్ష మరొక వర్క్‌బుక్‌ని సూచిస్తుంది, ఆ వర్క్‌బుక్ తప్పనిసరిగా తెరిచి ఉండాలి. ఇది మూసివేయబడితే, ఫంక్షన్ లోపాన్ని నివేదిస్తుంది. #REF! (#SSYL!).