Excel VLOOKUP функцын бүх нууц нь хүснэгтээс өгөгдлийг хайж олох, өөр хүснэгтэд задлахад зориулагдсан

2024 Зохиолч: Malcolm Clapton | [email protected]. Хамгийн сүүлд өөрчлөгдсөн: 2023-12-17 04:05

Өгүүллийг уншсаны дараа та Excel хүснэгтээс өгөгдлийг хэрхэн олж, өөр хүснэгтэд задлах талаар сурахаас гадна VLOOKUP функцтэй хамт ашиглаж болох аргуудыг сурах болно.

Excel дээр ажиллахдаа нэг хүснэгтээс өгөгдлийг олж, нөгөө хүснэгтэд задлах шаардлагатай байдаг. Хэрэв та үүнийг яаж хийхээ мэдэхгүй хэвээр байгаа бол нийтлэлийг уншсаны дараа та үүнийг хэрхэн хийхийг сурахаас гадна ямар нөхцөлд системээс хамгийн их гүйцэтгэлийг шахаж болохыг олж мэдэх болно. VLOOKUP функцтэй хамт ашиглах ёстой маш үр дүнтэй аргуудын ихэнхийг авч үзсэн болно.

Хэдийгээр та VLOOKUP функцийг олон жилийн турш ашиглаж байсан ч гэсэн өндөр магадлалтайгаар энэ нийтлэл танд хэрэгтэй бөгөөд таныг хайхрамжгүй орхихгүй. Жишээлбэл, би мэдээллийн технологийн мэргэжилтэн, дараа нь IT-ийн тэргүүний хувьд VLOOKUP програмыг 15 жилийн турш ашиглаж байгаа боловч одоо хүмүүст Excel програмыг мэргэжлийн түвшинд зааж эхэлснээр бүх нарийн ширийн зүйлийг даван туулж чадсан.

VLOOKUP гэсэн товчлол юм v босоо NS үзлэг. Үүний нэгэн адил VLOOKUP - Босоо ХАЙХ. Функцийн нэр нь баганад биш (хэвтээ - багана дээр давтаж, мөрийг засах) хүснэгтийн мөрөнд (босоо - мөр дээгүүр давтаж, баганыг засах) хайдаг болохыг бидэнд сануулж байна. VLOOKUP-д хэзээ ч хун болж чадахгүй муухай дэгдээхэй эгч байдаг - энэ бол HLOOKUP функц гэдгийг тэмдэглэх нь зүйтэй. HLOOKUP нь VLOOKUP-ээс ялгаатай нь хэвтээ хайлт хийдэг боловч Excel-ийн тухай ойлголт (мөн мэдээллийн зохион байгуулалтын тухай ойлголт) нь таны хүснэгтүүд цөөн багана, олон мөртэй байдаг гэсэн үг юм. Ийм учраас бид баганаар хайхаас хэд дахин олон удаа мөрөөр хайх хэрэгтэй болдог. Хэрэв та Excel-д HLO функцийг хэт олон удаа ашигладаг бол энэ амьдралд ямар нэг зүйлийг ойлгоогүй байх магадлалтай.

Синтакс

VLOOKUP функц нь дөрвөн параметртэй:

= VLOOKUP (;; [;]), энд:

- хүссэн утга (ховор тохиолдолд) эсвэл хүссэн утгыг агуулсан нүдний лавлагаа (тохиолдлын дийлэнх нь);

- параметрийн утгыг хайх АНХНЫ (!) баганад байгаа нүднүүдийн хүрээний (хоёр хэмжээст массив) лавлагаа;

- утгыг буцаах муж дахь баганын дугаар;

- Энэ нь мужын эхний баганыг өсөх дарааллаар эрэмбэлсэн эсэх асуултад хариулах маш чухал параметр юм. Хэрэв массивыг эрэмбэлсэн бол бид ҮНЭН эсвэл 1 утгыг зааж өгнө, өөрөөр хэлбэл - ХУДАЛ эсвэл 0. Хэрэв энэ параметрийг орхигдуулсан бол энэ нь анхдагчаар 1 болно.

VLOOKUP функцийг үйрмэг гэж мэддэг хүмүүсийн олонх нь дөрөв дэх параметрийн тайлбарыг уншсаны дараа үүнийг арай өөр хэлбэрээр харж дассан тул эвгүй санагдаж магадгүй гэж би мөрийцөж байна: ихэвчлэн тэд яг таарч тохирох тухай ярьдаг. хайх (ХУДАЛ эсвэл 0) эсвэл мужийг скан хийх (ҮНЭН эсвэл 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% биш, 5700%. Би энэ баримтыг нэлээд найдвартай шалгасан.

Ийм хурдан ажлын нууц нь эрэмбэлэгдсэн массив дээр маш үр дүнтэй хайлтын алгоритмыг ашиглах боломжтойд оршдог бөгөөд үүнийг хоёртын хайлт (тал хуваах арга, дихотоми арга) гэж нэрлэдэг. Тиймээс I төрлийн VLOOKUP үүнийг ашигладаг бөгөөд II төрлийн VLOOKUP нь ямар ч оновчлолгүйгээр хайдаг. Үүнтэй ижил параметрийг агуулсан MATCH функц болон зөвхөн эрэмбэлэгдсэн массивууд дээр ажилладаг, Lotus 1-2-3-тай нийцүүлэхийн тулд Excel-д орсон LOOKUP функцийн хувьд мөн адил юм.

Томъёоны сул тал

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 хүртэл өсгөх.

Тоог текст рүү хөрвүүлэх нь хоосон мөртэй холбох замаар хийгддэг бөгөөд энэ нь Excel-ийг өгөгдлийн төрлийг хөрвүүлэхэд хүргэдэг.

# N / A-г хэрхэн дарах вэ

Үүнийг IFERROR функцээр хийхэд маш тохиромжтой.

Жишээ нь: = IFERROR (VLOOKUP (D7; Бүтээгдэхүүн! $ A $ 2: $ C $ 5; 3; 0); "").

Хэрэв VLOOKUP алдааны код # N / A-г буцаавал IFERROR үүнийг таслан зогсоож 2-р параметрийг (энэ тохиолдолд хоосон мөр) орлуулах бөгөөд хэрэв алдаа гарахгүй бол энэ функц нь огт байхгүй мэт дүр эсгэх болно. Зөвхөн VLOOKUP хэвийн үр дүнг буцаасан байна.

Массив

Ихэнхдээ тэд массивын лавлагааг үнэмлэхүй болгохоо мартдаг бөгөөд массивыг сунгахдаа "хөвөгч" байдаг. A2: C5-ийн оронд $ A $ 2: $ C $ 5 ашиглахаа бүү мартаарай.

Лавлагааны массивыг ажлын дэвтрийн тусдаа хуудсан дээр байрлуулах нь зүйтэй. Энэ нь хөл доор орохгүй, илүү аюулгүй байх болно.

Энэ массивыг нэрлэсэн муж гэж зарлах нь илүү сайн санаа байх болно.

Олон хэрэглэгчид массивыг зааж өгөхдөө багануудыг бүхэлд нь зааж өгөх A: C гэх мэт бүтцийг ашигладаг. Таны массив шаардлагатай бүх мөрийг агуулж байгааг хянах шаардлагагүй тул энэ хандлага оршин байх эрхтэй. Хэрэв та хүснэгтэд анхны массив бүхий мөрүүдийг нэмбэл A: C гэж заасан мужийг тохируулах шаардлагагүй болно. Мэдээжийн хэрэг, энэхүү синтактик бүтэц нь Excel-ийг мужийг яг тодорхой зааж өгөхөөс арай илүү ажил хийхийг шаарддаг боловч энэ нэмэлт зардлыг үл тоомсорлож болно. Бид секундын зууны нэгийг ярьж байна.

За, суут ухааны ирмэг дээр - массивыг маягтаар зохион байгуулах.

COLUMN функцийг ашиглан задлах баганыг зааж өгнө

Хэрэв таны VLOOKUP ашиглан өгөгдөл авч байгаа хүснэгт нь хайлтын хүснэгттэй ижил бүтэцтэй, гэхдээ зүгээр л цөөн мөр агуулсан байвал та VLOOKUP дахь COLUMN () функцийг ашиглан татаж авах баганын тоог автоматаар тооцоолж болно. Энэ тохиолдолд бүх VLOOKUP томьёо ижил байх болно (эхний параметрт тохируулсан бөгөөд энэ нь автоматаар өөрчлөгддөг)! Эхний параметр нь үнэмлэхүй баганын координаттай болохыг анхаарна уу.

& "|" & ашиглан нийлмэл түлхүүр үүсгэх

Хэрэв хэд хэдэн баганаар нэгэн зэрэг хайх шаардлагатай бол хайлт хийх нийлмэл түлхүүр хийх шаардлагатай. Хэрэв буцаах утга нь текст биш ("Код" талбартай адил), харин тоон утгатай байсан бол илүү тохиромжтой SUMIFS томьёо үүнд тохирох бөгөөд нийлмэл баганын түлхүүр огт шаардлагагүй болно.

Энэ бол Lifehacker-д зориулсан миний анхны нийтлэл юм. Хэрэв танд таалагдсан бол би таныг зочлохыг урьж байна, мөн VLOOKUP функцийг ашиглах нууц болон бусад зүйлсийн талаар сэтгэгдлээ уншихыг урьж байна. Баярлалаа.:)