የላቀ VLOOKUP ምሳሌዎች፡ ባለብዙ መስፈርት ፍለጋ

በተግባሩ ላይ በትምህርታችን ሁለተኛ ክፍል VPR (VLOOKUP) በ Excel ውስጥ ሁሉንም ኃይል ለመምራት የሚረዱዎትን ጥቂት ምሳሌዎችን እንመረምራለን VPR በጣም ትልቅ የ Excel ስራዎችን ለመፍታት. ምሳሌዎቹ ይህ ባህሪ እንዴት እንደሚሰራ አስቀድሞ መሰረታዊ እውቀት እንዳለዎት ይገምታሉ። ካልሆነ፣ አገባቡን እና መሰረታዊ አጠቃቀሙን በሚያብራራው በዚህ አጋዥ ስልጠና የመጀመሪያ ክፍል ለመጀመር ፍላጎት ሊኖርዎት ይችላል። VPR. ደህና, እንጀምር.

• በ 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 የክርክሩ የተጠናከረ እሴት ይዟል መፈለጊያ_ዋጋ (የፍለጋ_እሴት) እና 4 - ክርክር ኮል_ኢንዴክስ_ቁጥር (የአምድ_ቁጥር)፣ ማለትም የተገኘው መረጃ የያዘው የአምድ ቁጥር።

ምሳሌ 2፡ VLOOKUP በሁለት መስፈርቶች በሌላ ሉህ ላይ ሠንጠረዥ እየታየ ነው።

በሌላ ሉህ ላይ ወይም በሌላ ኤክሴል የስራ ደብተር ውስጥ የሚገኘውን ከሁለተኛው ሰንጠረዥ (Lookup table) ላይ መረጃ በመጨመር ዋናውን ሠንጠረዥ (ዋና ሠንጠረዥ) ማዘመን ካስፈለገዎት የሚፈለገውን ዋጋ በሚያስገቡት ቀመር ውስጥ በቀጥታ መሰብሰብ ይችላሉ። ወደ ዋናው ጠረጴዛ.

ልክ እንደ ቀደመው ምሳሌ፣ በ Lookup ሠንጠረዥ ውስጥ ከተጣመሩ እሴቶች ጋር ረዳት አምድ ያስፈልግዎታል። ይህ አምድ በፍለጋ ክልል ውስጥ በግራ በኩል ያለው ዓምድ መሆን አለበት።

ስለዚህ ቀመር በ 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 - የገዢውን ስም የያዘ ሕዋስ (ያልተለወጠ ነው, እባክዎን ያስተውሉ - አገናኙ ፍጹም ነው);
• $ B$ - አምድ የደንበኛ ስም;
• ሠንጠረዥ 4 - ጠረጴዛዎ (ይህ ቦታ መደበኛ ክልል ሊሆን ይችላል);
• $C16 - የጠረጴዛዎ ወይም የክልልዎ የመጨረሻ ሕዋስ።

ይህ ቀመር ሁለተኛውን ተዛማጅ እሴት ብቻ ያገኛል። የተቀሩትን ድግግሞሾች ማውጣት ከፈለጉ, የቀደመውን መፍትሄ ይጠቀሙ.

የሁሉም ግጥሚያዎች ዝርዝር ከፈለጉ - ተግባሩ VPR ይህ ረዳት አይደለም ፣ ምክንያቱም በአንድ ጊዜ አንድ እሴት ብቻ ይመልሳል - ጊዜ። ግን ኤክሴል ተግባር አለው። INDEX (INDEX), ይህን ተግባር በቀላሉ መቋቋም የሚችል. እንዲህ ዓይነቱ ቀመር እንዴት እንደሚመስል, በሚከተለው ምሳሌ ውስጥ ይማራሉ.

የሚፈለገውን እሴት ሁሉንም ድግግሞሾችን ሰርስረው ያውጡ

ከላይ እንደተጠቀሰው VPR ሁሉንም የተባዙ እሴቶች ከተቃኘው ክልል ማውጣት አይችሉም። ይህንን ለማድረግ እንደ ከበርካታ የ Excel ተግባራት የተሰራ ትንሽ ውስብስብ ቀመር ያስፈልግዎታል INDEX (INDEX)፣ አክል (ትንሽ) እና ROW (LINE)

ለምሳሌ፣ ከዚህ በታች ያለው ቀመር ሁሉንም የእሴቱን ድግግሞሽ ከሴል F2 በ B2:B16 ክልል ውስጥ ያገኛል እና ውጤቱን በአምድ ሐ ውስጥ ካሉት ተመሳሳይ ረድፎች ይመልሳል።

{=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 + Enter ይጫኑየድርድር ቀመሩን በትክክል ለማስገባት.

እንዴት እንደሚሰራ ለመረዳት ፍላጎት ካሎት፣ ወደ የቀመሩ ዝርዝሮች ትንሽ እንዝለቅ፡-

ክፍል 1:

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 (ከሆነ) እንደዚህ ያለ አግድም ድርድር ይኖራል፡- {1,"",3,"",5,"","","","","","",12,"","",""}

ክፍል 2:

ROW()-3

СТРОКА()-3

እዚህ ተግባሩ ROW (LINE) እንደ ተጨማሪ ቆጣሪ ይሠራል። ቀመሩ በሴሎች F4:F9 ስለሚገለበጥ, ቁጥሩን እንቀንሳለን 3 ዋጋ ለማግኘት ከተግባር ውጤት 1 በሴል ውስጥ F4 (መስመር 4፣ መቀነስ 3) ለማግኘት 2 በሴል ውስጥ F5 (መስመር 5፣ 3 ቀንስ) እና የመሳሰሉት።

ክፍል 3:

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

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

ሥራ አክል (ትንሽ) ይመለሳል n-ኦ በመረጃ ድርድር ውስጥ ያለው ትንሹ እሴት። በእኛ ሁኔታ, የትኛው ቦታ (ከትንሹ) ለመመለስ በተግባሩ ይወሰናል ROW (LINE) (ክፍል 2 ይመልከቱ)። ስለዚህ, ለአንድ ሕዋስ F4 ሥራ ትንሽ({array}፣1) ተመልሶ ይመጣል 1 ኛ (ትንሹ) የድርድር አካል፣ ማለትም 1. ለሴል F5 ተመልሶ ይመጣል 2 ኛ በድርድር ውስጥ ያለው ትንሹ አካል፣ ማለትም 3, ወዘተ

ክፍል 4:

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) በቀላሉ የአንድ የተወሰነ ሕዋስ ዋጋ በአንድ ድርድር ውስጥ ይመልሳል C2፡ C16. ለሴል F4 ሥራ ኢንዴክስ($C$2:$C$16) ይመለሳል ፖምያህል F5 ሥራ ኢንዴክስ($C$2:$C$16) ይመለሳል ጣፋጮች እናም ይቀጥላል.

ክፍል 5:

IFERROR()

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

በመጨረሻም ቀመሩን በተግባር ውስጥ እናስቀምጣለን IFERROR (IFERROR)፣ ምክንያቱም በስህተት መልዕክቱ ደስተኛ መሆንዎ አይቀርም #AT (#N/A) ቀመሩ የተገለበጠባቸው የሕዋሶች ብዛት እየታየ ባለው ክልል ውስጥ ካሉ የተባዙ እሴቶች ያነሰ ከሆነ።

የ XNUMXD ፍለጋ በሚታወቀው ረድፍ እና አምድ

በኤክሴል ውስጥ የ XNUMXD ፍለጋን ማከናወን በሚታወቅ ረድፍ እና በአምድ ቁጥር ዋጋ መፈለግን ያካትታል። በሌላ አነጋገር የሴል እሴቱን በአንድ የተወሰነ ረድፍ እና አምድ መገናኛ ላይ እያወጡት ነው።

እንግዲያው ወደ ጠረጴዛችን እንዞር እና ከተግባር ጋር ቀመር እንፃፍ VPRበመጋቢት ውስጥ ስለሚሸጡት የሎሚ ዋጋ መረጃ የሚያገኘው።

የ XNUMXD ፍለጋን ለማከናወን ብዙ መንገዶች አሉ። አማራጮቹን ይመልከቱ እና ለእርስዎ በጣም የሚስማማውን ይምረጡ።

VLOOKUP እና MATCH ተግባራት

ብዙ ተግባራትን መጠቀም ይችላሉ። VPR (VLOOKUP) እና የበለጠ የተጋለጠ (MATCH) በመስኮቹ መገናኛ ላይ ያለውን ዋጋ ለማግኘት የምርት ስም። (ሕብረቁምፊ) እና ወር በጥያቄ ውስጥ ያለው የድርድር (ዓምድ)፡-

=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 ሴሎች ውስጥ መመልከት - ክርክር ፍለጋ_ድርድር (ፍለጋ_ድርድር);
• ትክክለኛውን ግጥሚያ በመመለስ ላይ - ክርክር ግጥሚያ_አይነት (ተዛማጅ_አይነት)።

በመጠቀም ላይ 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. ሳጥኖቹን ይፈትሹ የላይኛው ረድፍ (ከላይ ባለው መስመር) እና የግራ ዓምድ (በግራ በኩል ባለው አምድ ውስጥ). ማይክሮሶፍት ኤክሴል ከተመን ሉህ በላይኛው ረድፍ እና ግራ አምድ ላይ ካሉት እሴቶች ላሉ ክልሎች ስሞችን ይመድባል። አሁን እነዚህን ቀመሮች ሳይፈጥሩ በቀጥታ እነዚህን ስሞች በመጠቀም መፈለግ ይችላሉ።
3. በማንኛውም ባዶ ሕዋስ ውስጥ, ይጻፉ = ረድፍ_ስም የአምድ_ስምለምሳሌ እንደዚህ፡-

   = ሎሚ ማር

   … ወይም በተቃራኒው፡-

   = ማር ሎሚ

   ያስታውሱ የረድፍ እና የአምድ ስሞች በቦታ መለየት አለባቸው, በዚህ ሁኔታ እንደ መገናኛ ኦፕሬተር ይሠራል.

ስም ሲያስገቡ ማይክሮሶፍት ኤክሴል ልክ ቀመር ሲያስገቡ ተዛማጅ ስሞች ዝርዝር የያዘ የመሳሪያ ቲፕ ያሳያል።

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 እና INNDIRECTን በመጠቀም ከተለያዩ ሰንጠረዦች የመጣ ውሂብ ተለዋዋጭ መተካት

በመጀመሪያ፣ በትክክል መረዳታችንን ለማረጋገጥ “ከተለያዩ ሠንጠረዦች ተለዋዋጭ ዳታ መተካት” የሚለውን አገላለጽ ምን ማለታችን እንደሆነ እናብራራ።

ተመሳሳይ ቅርፀት ያላቸው በርካታ ሉሆች ሲኖሩ ሁኔታዎች አሉ, እና በተሰጠው ሕዋስ ውስጥ በገባው እሴት ላይ በመመስረት አስፈላጊውን መረጃ ከአንድ የተወሰነ ሉህ ማውጣት አስፈላጊ ነው. ይህንን በምሳሌ ማስረዳት የሚቀል ይመስለኛል።

ለተመሳሳይ ምርቶች እና በተመሳሳይ ቅርጸት ለብዙ ክልሎች የሽያጭ ሪፖርቶች እንዳለህ አስብ. ለአንድ የተወሰነ ክልል የሽያጭ አሃዞችን ማግኘት ይፈልጋሉ፡-

እንደዚህ ያሉ ሁለት ሪፖርቶች ብቻ ካሉዎት, ከተግባሮች ጋር አሳፋሪ ቀላል ቀመር መጠቀም ይችላሉ 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 የክልሉ ስም ያለው ሕዋስ ነው። ፍፁም የአምድ ማጣቀሻ እና አንጻራዊ የረድፍ ማጣቀሻ እየተጠቀምን ነው ምክንያቱም ቀመሩን በተመሳሳይ አምድ ውስጥ ወደሌሎች ህዋሶች ለመቅዳት ስላቀድን ነው።
• ኤፍኤል_ሳልes и CA_ሽያጭ - ተዛማጅ የሽያጭ ሪፖርቶችን የያዙ የሰንጠረዦች ስሞች (ወይም የተሰየሙ ክልሎች)። ለምሳሌ የተለመዱትን የሉህ ስሞች እና የሕዋስ ክልል ማጣቀሻዎችን መጠቀም ትችላለህ 'FL Sheet'!$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) ወይም አልተገለጸም;
• አር 1 ሲ 1፣ ከሆነ Fአስ ኢ (ሐሰት)

በእኛ ሁኔታ, አገናኙ ዘይቤ አለው A1, ስለዚህ ሁለተኛውን ክርክር ትተህ በመጀመሪያው ላይ ማተኮር ትችላለህ.

ስለዚህ ወደ የሽያጭ ሪፖርታችን እንመለስ። ካስታወሱ, እያንዳንዱ ዘገባ በተለየ ሉህ ላይ የሚገኝ የተለየ ሰንጠረዥ ነው. ቀመሩ በትክክል እንዲሰራ፣ ሰንጠረዦችዎን (ወይም ክልሎችን) መሰየም አለብዎት፣ እና ሁሉም ስሞች የጋራ ክፍል ሊኖራቸው ይገባል። ለምሳሌ፣ እንደዚህ፡- CA_ሽያጭ, FL_ሽያጭ, TX_ሽያጭ እናም ይቀጥላል. እንደምታየው፣ "_ሽያጭ" በሁሉም ስሞች ውስጥ አለ።

ሥራ ችግር በአምድ D ውስጥ ያለውን ዋጋ እና የጽሑፍ ሕብረቁምፊውን "_ሽያጭ" ያገናኛል, በዚህም ይነገራል VPR በየትኛው ሰንጠረዥ ውስጥ መፈለግ. ሕዋስ D3 የ "FL" እሴት ከያዘ, ቀመሩ ሰንጠረዡን ይፈልጋል FL_ሽያጭ, "CA" ከሆነ - በጠረጴዛው ውስጥ CA_ሽያጭ እናም ይቀጥላል.

የተግባሮቹ ውጤት VPR и ችግር የሚከተለው ይሆናል።

ውሂቡ በተለያዩ የ Excel መጽሐፍት ውስጥ የሚገኝ ከሆነ ከተጠቀሰው ክልል በፊት የመጽሐፉን ስም ማከል ያስፈልግዎታል ፣ ለምሳሌ-

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

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

ተግባሩ ከሆነ ችግር ሌላ የሥራ መጽሐፍን ያመለክታል፣ ያ የሥራ መጽሐፍ ክፍት መሆን አለበት። ከተዘጋ, ተግባሩ ስህተት ሪፖርት ያደርጋል. # ረፍ! (#SSYL!)