በሠንጠረዥ ውስጥ መረጃን ለማግኘት እና ወደ ሌላ ለማውጣት የ Excel VLOOKUP ሁሉም ሚስጥሮች

2024 ደራሲ ደራሲ: Malcolm Clapton | [email protected]. ለመጨረሻ ጊዜ የተሻሻለው: 2023-12-17 03:45

ጽሑፉን ካነበቡ በኋላ በ Excel ተመን ሉህ ውስጥ እንዴት መረጃን ማግኘት እንደሚችሉ እና ወደ ሌላ ማውጣት ብቻ ሳይሆን ከ VLOOKUP ተግባር ጋር ሊጠቀሙባቸው የሚችሉ ቴክኒኮችን ይማራሉ ።

በኤክሴል ውስጥ በሚሰሩበት ጊዜ ብዙውን ጊዜ መረጃን በአንድ ሠንጠረዥ ውስጥ መፈለግ እና ወደ ሌላ ማውጣት ያስፈልጋል። አሁንም ይህንን እንዴት ማድረግ እንዳለብዎ ካላወቁ, ጽሑፉን ካነበቡ በኋላ, ይህንን እንዴት እንደሚማሩ ብቻ ሳይሆን በየትኞቹ ሁኔታዎች ውስጥ ከፍተኛውን አፈፃፀም ከስርዓቱ ውስጥ ማውጣት እንደሚችሉ ይወቁ. ከ VLOOKUP ተግባር ጋር ተያይዞ ጥቅም ላይ መዋል ያለባቸው አብዛኛዎቹ በጣም ውጤታማ ቴክኒኮች ይታሰባሉ።

ምንም እንኳን ለዓመታት የ VLOOKUP ተግባርን እየተጠቀሙ ቢሆንም ፣ በከፍተኛ ደረጃ ይህ ጽሑፍ ለእርስዎ ጠቃሚ ይሆናል እና ግድየለሽነት አይተወዎትም። ለምሳሌ የአይቲ ስፔሻሊስት በመሆኔ እና ከዚያም በ IT ውስጥ ዋና ኃላፊ ሆኜ VLOOKUPን ለ15 ዓመታት ስጠቀም ቆይቻለሁ፣ ግን አሁን ሁሉንም ችግሮች ለመቋቋም የቻልኩት በፕሮፌሽናል ደረጃ ሰዎችን ኤክሴል ማስተማር ስጀምር ነው።

VLOOKUP የሚል ምህጻረ ቃል ነው። ቁ አቀባዊ ኤን.ኤስ ምርመራ. በተመሳሳይ፣ VLOOKUP - አቀባዊ ፍለጋ። የተግባሩ ስም ራሱ በሰንጠረዡ ረድፎች ውስጥ እንደሚፈልግ ይጠቁመናል (በአቀባዊ - በረድፎች ላይ መደጋገም እና ዓምዱን ማስተካከል) እና በአምዶች ውስጥ አይደለም (በአግድም - በአምዶች ላይ መደጋገም እና ረድፉን ማስተካከል)። VLOOKUP እህት እንዳላት ልብ ሊባል ይገባል - አስቀያሚ ዳክዬ በጭራሽ ስዋን የማይሆን - ይህ የ HLOOKUP ተግባር ነው። HLOOKUP ከ VLOOKUP በተቃራኒ አግድም ፍለጋዎችን ያከናውናል, ነገር ግን የ Excel ጽንሰ-ሐሳብ (እና በእርግጥ የውሂብ አደረጃጀት ጽንሰ-ሐሳብ) ሰንጠረዦችዎ ያነሱ አምዶች እና ብዙ ረድፎች እንዳላቸው ያመለክታል. ለዚህም ነው ከአምዶች ይልቅ ብዙ ጊዜ በረድፍ መፈለግ ያለብን። የ HLO ተግባርን በ Excel ውስጥ ብዙ ጊዜ የሚጠቀሙ ከሆነ ምናልባት በዚህ ህይወት ውስጥ የሆነ ነገር ያልተረዱት ሊሆን ይችላል።

አገባብ

የ VLOOKUP ተግባር አራት መለኪያዎች አሉት

= VLOOKUP (;; [;]), እዚህ:

- የሚፈለገው እሴት (አልፎ አልፎ) ወይም የሚፈለገውን እሴት የያዘ ሕዋስ (አብዛኛዎቹ ጉዳዮች) ማጣቀሻ;

- የሕዋሶች ክልል (ባለሁለት-ልኬት ድርድር) ማጣቀሻ ፣ በ FIRST (!) አምድ ውስጥ ፣ የመለኪያ እሴቱ የሚፈለግበት ፣

- እሴቱ በሚመለስበት ክልል ውስጥ ያለው የአምድ ቁጥር;

- ይህ የክልሉ የመጀመሪያ አምድ በከፍታ ቅደም ተከተል የተደረደረ ስለመሆኑ ለጥያቄው መልስ የሚሰጥ በጣም አስፈላጊ ግቤት ነው። አደራደሩ ከተደረደረ እሴቱን TRUE ወይም 1 እንገልፃለን፣ ካልሆነ - FALSE ወይም 0. ይህ ግቤት ከተተወ፣ ወደ 1 ይወርዳል።

ብዙ የ VLOOKUP ተግባርን እንደ ብልጭ የሚያውቁ ፣ የአራተኛውን ግቤት መግለጫ ካነበቡ በኋላ ፣ ትንሽ ለየት ባለ መልኩ ለማየት ስለለመዱ ምቾት ሊሰማቸው ይችላል ። ብዙውን ጊዜ እነሱ የሚያወሩት ስለ ትክክለኛ ግጥሚያ ሲሆን መፈለግ (FALSE ወይም 0) ወይም የክልል ቅኝት (TRUE ወይም 1)።

አሁን እስከ መጨረሻው የተነገረውን ትርጉም እስኪሰማዎት ድረስ ቀጣዩን አንቀጽ ብዙ ጊዜ ማጣራት እና ማንበብ ያስፈልግዎታል። እያንዳንዱ ቃል እዚያ አስፈላጊ ነው. ምሳሌዎች እርስዎ እንዲገነዘቡት ይረዱዎታል።

የ VLOOKUP ቀመር በትክክል እንዴት ይሰራል?

• የፎርሙላ አይነት I. የመጨረሻው ግቤት ከተተወ ወይም ከ 1 ጋር እኩል ከሆነ, VLOOKUP የመጀመሪያው አምድ በከፍታ ቅደም ተከተል የተደረደረ እንደሆነ ይገምታል, ስለዚህ ፍለጋው በረድፍ ላይ ይቆማል. ወዲያውኑ ከሚፈለገው በላይ ያለውን ዋጋ የያዘውን መስመር ይቀድማል … እንደዚህ ያለ ሕብረቁምፊ ካልተገኘ የክልሉ የመጨረሻው ረድፍ ይመለሳል።

  

• ቀመር II. የመጨረሻው ግቤት 0 ተብሎ ከተገለጸ ፣ VLOOKUP የድርድር የመጀመሪያውን አምድ በቅደም ተከተል ይቃኛል እና ከመለኪያው ጋር የመጀመሪያው ትክክለኛ ግጥሚያ ሲገኝ ወዲያውኑ ፍለጋውን ያቆማል ፣ አለበለዚያ # N / A (# N / A) የስህተት ኮድ ተመልሷል።

  

ቀመሮች የስራ ፍሰቶች

VPR ዓይነት I

VPR ዓይነት II

የቅጽ I

1. ቀመሮች እሴቶችን በየክልሎች ለማሰራጨት ጥቅም ላይ ሊውሉ ይችላሉ።
2. የመጀመሪያው አምድ የተባዙ እሴቶችን ከያዘ እና በትክክል ከተደረደረ ፣ ከዚያ የተባዙ እሴቶች ያላቸው የረድፎች የመጨረሻዎቹ ይመለሳሉ።
3. ከመጀመሪያው አምድ ሊይዝ ከሚችለው በላይ የሆነ ዋጋ ከፈለግክ በቀላሉ የሠንጠረዡን የመጨረሻ ረድፍ በቀላሉ ማግኘት ትችላለህ፣ ይህም በጣም ዋጋ ያለው ሊሆን ይችላል።
4. ይህ እይታ የ# N/A ስህተቱን የሚመልሰው ከተፈለገው ያነሰ ወይም እኩል የሆነ እሴት ካላገኘ ብቻ ነው።
5. የእርስዎ ድርድር ካልተደረደረ ቀመሩ የተሳሳቱ እሴቶችን እንደሚመልስ ለመረዳት በጣም ከባድ ነው።

ለ II ዓይነት ቀመሮች ማጣቀሻዎች

የሚፈለገው እሴት በአደራደሩ የመጀመሪያ አምድ ውስጥ ብዙ ጊዜ ከተከሰተ፣ ከዚያ ቀመሩ ለቀጣይ ውሂብ መልሶ ማግኛ የመጀመሪያውን ረድፍ ይመርጣል።

VLOOKUP አፈጻጸም

የጽሁፉ ጫፍ ላይ ደርሰሃል። ጥሩ ይመስላል፣ ዜሮን ወይም አንድን እንደ የመጨረሻው መለኪያ ብገለጽ ልዩነቱ ምንድን ነው? በመሠረቱ, ሁሉም ሰው በእርግጥ ዜሮን ይጠቁማል, ይህ በጣም ተግባራዊ ስለሆነ: የድርድር የመጀመሪያውን አምድ ለመደርደር መጨነቅ አያስፈልገዎትም, እሴቱ ተገኝቷል ወይም እንዳልሆነ ወዲያውኑ ማየት ይችላሉ. ነገር ግን ብዙ ሺ የ VLOOKUP ቀመሮች በሉህ ላይ ካሉ፣ VLOOKUP II ቀርፋፋ መሆኑን ያስተውላሉ። በተመሳሳይ ጊዜ ብዙውን ጊዜ ሁሉም ሰው ማሰብ ይጀምራል-

• የበለጠ ኃይለኛ ኮምፒተር እፈልጋለሁ;
• ፈጣን ቀመር ያስፈልገኛል፣ ለምሳሌ፣ ብዙ ሰዎች ስለ INDEX + MATCH ያውቃሉ፣ እሱም በፍጥነት ከ5-10% ነው ተብሎ ይታሰባል።

እና ጥቂት ሰዎች ልክ እንደ I አይነት VLOOKUP መጠቀም እንደጀመሩ እና የመጀመሪያው አምድ በማንኛውም መንገድ መደረደሩን ካረጋገጡ የ VLOOKUP ፍጥነት 57 ጊዜ ይጨምራል. በቃላት እጽፋለሁ - ሃምሳ ሰባት ጊዜ! 57% ሳይሆን 5,700% ይህንን እውነታ በአስተማማኝ ሁኔታ ፈትጬዋለሁ።

የዚህ ዓይነቱ ፈጣን ሥራ ምስጢር እጅግ በጣም ቀልጣፋ የፍለጋ ስልተ-ቀመር በተደረደረ ድርድር ላይ ሊተገበር ስለሚችል ነው ፣ እሱም ሁለትዮሽ ፍለጋ (ግማሽ ዘዴ ፣ ዲኮቶሚ ዘዴ) ይባላል። ስለዚህ VLOOKUP I ዓይነት ይተገበራል፣ እና VLOOKUP ዓይነት II ፍለጋዎችን ያለምንም ማመቻቸት ይፈልጋል። ተመሳሳይ መለኪያን ለሚያካትት የMATCH ተግባር እና የLOOKUP ተግባር በተደረደሩ ድርድሮች ላይ ብቻ የሚሰራ እና ከሎተስ 1-2-3 ጋር ለመስማማት በኤክሴል ውስጥ የተካተተ ነው።

የቀመርው ጉዳቶች

የ VLOOKUP ጉዳቶች ግልፅ ናቸው-በመጀመሪያ ፣ እሱ በተጠቀሰው ድርድር የመጀመሪያ አምድ ውስጥ ብቻ ይፈልጋል ፣ እና ሁለተኛ ፣ በዚህ አምድ በቀኝ በኩል። እና እንደተረዱት, አስፈላጊውን መረጃ የያዘው አምድ ከምንመለከትበት አምድ በስተግራ በኩል ሊሆን ይችላል. ቀደም ሲል የተጠቀሰው የቀመሮች ጥምረት INDEX + MATCH ይህ ጉድለት የለውም፣ ይህም ከ VLOOKUP (VLOOKUP) ጋር ሲነፃፀር መረጃን ከጠረጴዛዎች ለማውጣት በጣም ተለዋዋጭ መፍትሄ ያደርገዋል።

ቀመሩን በእውነተኛ ህይወት የመተግበር አንዳንድ ገፅታዎች

ክልል ፍለጋ

የክልል ፍለጋ ክላሲክ ምሳሌ ቅናሹን በትዕዛዝ መጠን የመወሰን ተግባር ነው።

የጽሑፍ ሕብረቁምፊዎችን ይፈልጉ

በእርግጥ, VLOOKUP ለቁጥሮች ብቻ ሳይሆን ለጽሑፍም ይመለከታል. ቀመሩ የገጸ-ባህሪያትን ጉዳይ እንደማይለይ ግምት ውስጥ ማስገባት ያስፈልጋል. የዱር ካርዶችን ከተጠቀሙ, ደብዛዛ ፍለጋ ማደራጀት ይችላሉ. ሁለት የዱር ምልክቶች አሉ: "?" - በጽሑፍ ሕብረቁምፊ ውስጥ ማንኛውንም አንድ ቁምፊ ይተካዋል, "*" - የማንኛውም ቁምፊዎችን ቁጥር ይተካዋል.

ቦታዎችን መዋጋት

ብዙውን ጊዜ ጥያቄው በሚፈልጉበት ጊዜ የትርፍ ቦታዎችን ችግር እንዴት እንደሚፈታ ይነሳል. የመፈለጊያ ሰንጠረዡ አሁንም ከነሱ ሊጸዳ የሚችል ከሆነ, የ VLOOKUP ቀመር የመጀመሪያ መለኪያ ሁልጊዜ በእርስዎ ላይ የተመሰረተ አይደለም. ስለዚህ፣ ሴሎቹን ከተጨማሪ ክፍተቶች ጋር የመዝጋት አደጋ ካለ፣ እሱን ለማጽዳት የ TRIM ተግባርን መጠቀም ይችላሉ።

የተለያዩ የውሂብ ቅርጸት

የ VLOOKUP ተግባር የመጀመሪያ ግቤት ቁጥርን የያዘ ሕዋስን የሚያመለክት ነገር ግን በሴሉ ውስጥ እንደ ጽሁፍ የተከማቸ ከሆነ እና የድርድር የመጀመሪያው አምድ ቁጥሮችን በትክክለኛው ቅርጸት ከያዘ ፍለጋው አይሳካም። ተቃራኒው ሁኔታም ይቻላል. መለኪያ 1ን ወደሚፈለገው ቅርጸት በመተርጎም ችግሩን በቀላሉ መፍታት ይቻላል፡-

= VLOOKUP (-- D7; ምርቶች! $ A $ 2: $ C $ 5; 3; 0) - D7 ጽሑፍ ከያዘ እና ጠረጴዛው ቁጥሮችን ከያዘ;

= VLOOKUP (D7 & ""); ምርቶች! $ A $ 2: $ C $ 5; 3; 0) - እና በተቃራኒው.

በነገራችን ላይ ጽሑፍን ወደ ቁጥር በአንድ ጊዜ በበርካታ መንገዶች መተርጎም ይችላሉ, ይምረጡ:

• ድርብ አሉታዊ -D7.
• በአንድ D7 * 1 ማባዛት።
• ዜሮ መደመር D7 + 0።
• ወደ መጀመሪያው ኃይል D7 ^ 1 ማሳደግ።

ቁጥርን ወደ ጽሑፍ መቀየር የሚከናወነው ከባዶ ሕብረቁምፊ ጋር በማጣመር ሲሆን ይህም ኤክሴል የውሂብ አይነትን እንዲቀይር ያስገድዳል.

# N/Aን እንዴት ማፈን እንደሚቻል

ይህ ከ IFERROR ተግባር ጋር ለመስራት በጣም ምቹ ነው።

ለምሳሌ: = IFERROR (VLOOKUP (D7; ምርቶች! $ 2: $ C $ 5; 3; 0); "").

VLOOKUP የስህተት ኮድ # N / A ከመለሰ ፣ ከዚያ IFERROR ጠልፎ ያስገባዋል እና ፓራሜትር 2 ን ይተካዋል (በዚህ አጋጣሚ ባዶ ሕብረቁምፊ) እና ምንም ስህተት ካልተከሰተ ይህ ተግባር በጭራሽ እንደሌለ ያስመስላል ፣ ግን መደበኛውን ውጤት የመለሰ VLOOKUP ብቻ አለ።

አደራደር

ብዙውን ጊዜ የድርድር ማመሳከሪያውን ፍጹም ማድረግን ይረሳሉ, እና ድርድር ሲዘረጋ "ይንሳፈፋል". ከ A2: C5 ይልቅ $ A $ 2: $ C $ 5 መጠቀሙን ያስታውሱ።

የማመሳከሪያውን ድርድር በተለየ የሥራ ደብተር ላይ ማስቀመጥ ጥሩ ሀሳብ ነው. ከእግር በታች አይወርድም, እና የበለጠ አስተማማኝ ይሆናል.

በጣም የተሻለው ሀሳብ ይህንን ድርድር እንደ የተሰየመ ክልል ማወጅ ነው።

ብዙ ተጠቃሚዎች፣ አደራደርን ሲገልጹ፣ እንደ A: C ያለ ግንባታ ይጠቀማሉ፣ ይህም ሙሉ ዓምዶችን ይገልፃል። የእርስዎ ድርድር ሁሉንም አስፈላጊ ሕብረቁምፊዎች ያካተተ የመሆኑን እውነታ ከመከታተል ስለዳኑ ይህ አካሄድ የመኖር መብት አለው። ከዋናው ድርድር ጋር ረድፎችን ወደ ሉህ ካከሉ፣ እንደ A፡ C የተገለፀው ክልል ማስተካከል አያስፈልገውም። በእርግጥ ይህ የአገባብ ግንባታ ኤክሴል ክልሉን በትክክል ከመግለጽ ይልቅ ትንሽ ተጨማሪ ስራ እንዲሰራ ያስገድደዋል፣ ነገር ግን ይህ ከፍተኛ ወጪ ችላ ሊባል ይችላል። እየተነጋገርን ያለነው ስለ ሰከንድ መቶኛዎች ነው።

ደህና, በሊቅ አፋፍ ላይ - ድርድርን በቅጹ ላይ ለማዘጋጀት.

የሚወጣበትን አምድ ለመለየት የ COLUMN ተግባርን በመጠቀም

VLOOKUP ን ተጠቅመህ መረጃ የምታመጣበት ሠንጠረዥ ከፍለጋ ሠንጠረዥ ጋር ተመሳሳይ መዋቅር ካለው፣ ነገር ግን በቀላሉ ጥቂት ረድፎችን ከያዘ፣ በ VLOOKUP ውስጥ ያለውን የCOLUMN () ተግባር በመጠቀም የሚወጡትን የአምዶች ቁጥሮች በራስ ሰር ለማስላት ትችላለህ። በዚህ አጋጣሚ ሁሉም የ VLOOKUP ቀመሮች አንድ አይነት ይሆናሉ (ለመጀመሪያው መለኪያ የተስተካከለ, በራስ-ሰር የሚቀየር)! የመጀመሪያው ግቤት ፍፁም የአምድ መጋጠሚያ እንዳለው ልብ ይበሉ።

በ & "|" እና የተዋሃደ ቁልፍ መፍጠር

በአንድ ጊዜ በበርካታ አምዶች መፈለግ አስፈላጊ ሆኖ ከተገኘ ለፍለጋው የተዋሃደ ቁልፍ ማድረግ አስፈላጊ ነው. የመመለሻ እሴቱ ጽሑፋዊ ካልሆነ (እንደ "ኮድ" መስክ) ፣ ግን ቁጥራዊ ፣ ከዚያ የበለጠ ምቹ የ SUMIFS ቀመር ለዚህ ተስማሚ ይሆናል እና የተዋሃደ አምድ ቁልፍ በጭራሽ አያስፈልግም።

ይህ ለ Lifehacker የመጀመሪያዬ መጣጥፍ ነው። ከወደዳችሁት፣ እንድትጎበኙ እጋብዛችኋለሁ፣ እንዲሁም ስለ VLOOKUP ተግባር እና ስለመሳሰሉት ምስጢሮችዎ በአስተያየቶች ውስጥ በደስታ ያንብቡ። አመሰግናለሁ.:)