Тодорхой толгойтой баганыг хайх. VLOOKUP ашиглан өөр ажлын номноос хайх. Excel дээрх VLOOKUP - та үүнийг санах хэрэгтэй

Microsoft Excel нь хэрэглэгчийн ажлыг ихээхэн хөнгөвчлөх олон янзын функцтэй бөгөөд энэ нийтлэлд бид тэдгээрийн аль нэгнийх нь талаар ярих болно. Үүнийг VLOOKUP гэж нэрлэдэг бөгөөд хэрэв англиар бол VLOOKUP.

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

Тус функцийг үнийн жагсаалтад заасан үнэ бүхий баганыг бөглөхөд ихэвчлэн ашигладаг тул дараах жишээг авч үзье. Жишээлбэл, жимс жимсгэнэ (улбар шар) бүхий хүснэгт байдаг бөгөөд тэнд тус бүрдээ хэдэн кг захиалахыг зааж өгсөн болно. Үүний дагуу жимс тус бүрийн хувьд нийлүүлэгчээс өгсөн үнийн жагсаалтаас (ногоон) үнийг бичиж, дараа нь худалдан авалт хэр үнэтэй болохыг тооцоолох хэрэгтэй. Зүйл бүрийг харах, өгөгдөл дамжуулахад хэцүү байдаг, ялангуяа бараатай олон мянган шугам байгаа бол бид VLOOKUP ашигладаг.

Үнийн баганын эхний хүснэгтийн дээд нүдэн дээр, дараа нь томьёоны мөрөн дэх "fx" товчийг дарж функцийн мастер цонхыг нээнэ үү.

Бидний хийх дараагийн зүйл бол санал болгож буй талбарт аргументуудыг бичих явдал юм.

Талбарт налуу бичнэ үү "Хайлтын_утга"эхний хүснэгтэд бидний хайх утгыг онцлон тэмдэглэ. Надад энэ алим байна.

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

Томьёог бичиж, бүхэл бүтэн баганаар сунгасны дараа сонгосон муж доошоо шилжихгүй тул холбоосыг үнэмлэхүй болгох хэрэгтэй: талбар дахь өгөгдлийг сонгоод "F4" товчийг дарна уу. Одоо нүднүүдийн хаяг үнэмлэхүй болж, тэдэнд "$" тэмдэг нэмэгдсэн бөгөөд хүрээ өөрчлөгдөхгүй.

Баганын дугаар хаана байгаа бол хоёр дахь хүснэгтэд харгалзах дугаарыг өгөгдөл дамжуулахыг хүссэн баганад оруулна. Миний үнийн жагсаалтад жимс, үнэ багтсан тул хоёр дахь нь хэрэгтэй тул "2" гэсэн тоог тавьсан.

IN "Цаг хугацааны алдагдал"Бид "ХУДАЛ" гэж бичнэ - хэрэв та яг таарч тохирохыг хайх шаардлагатай бол "ҮНЭН" гэж бичнэ - утгууд нь ойролцоо байж болно. Бидний жишээн дээр бид эхнийхийг сонгодог. Хэрэв та энэ талбарт юу ч заагаагүй бол хоёр дахь нь анхдагчаар сонгогдоно. Дараа нь "OK" дээр дарна уу.

Энд дараахь зүйлийг анхаарч үзээрэй: хэрэв та тоонуудтай ажиллаж, "Үнэн" гэж зааж өгвөл хоёр дахь хүснэгтийг (энэ бол бидний үнийн жагсаалт) өсөх дарааллаар эрэмбэлэх ёстой. Жишээлбэл, 5.25-ыг хайж олоход 5.27-г олж, энэ мөрний өгөгдлийг авах болно, гэхдээ 5.2599-ээс доош тоо байж болох ч томьёо цааш харахгүй, учир нь доорх тоонууд нь зөвхөн том байна гэж бодож байна. .

VLOOKUP хэрхэн ажилладаг вэ? Энэ нь хүссэн утгыг (алим) авч, заасан хүрээний хамгийн зүүн баганад (жимсний жагсаалт) хайдаг. Хэрэв таарч байгаа бол утгыг ижил мөрнөөс, зөвхөн аргументуудад заасан баганаас (2) авч, бидэнд хэрэгтэй нүд рүү (C2) шилжүүлнэ. Томъёо дараах байдалтай байна.

ДҮРҮҮЛЭХ(A2,$G$2:$H$12,2,ХУДАЛ)

Одоо та баруун доод буланг татах замаар шаардлагатай тооны шугамыг сунгаж болно.

Жимсний нэрсийн дагуу бүх үнийг үнийн жагсаалтаас худалдан авалтын хүснэгтэд шилжүүлсэн.

Хэрэв та эхний хүснэгтэд үнийн жагсаалтад ороогүй бүтээгдэхүүний нэрс байгаа бол миний хувьд эдгээр нь хүнсний ногоо бол эдгээр цэгүүдийн эсрэг талд VLOOKUP томьёо нь #N/A алдаа гаргах болно.

Ажлын хуудсанд багана нэмэх үед функцийн Хүснэгтийн аргументын өгөгдөл автоматаар өөрчлөгдөнө. Жишээн дээр үнийг баруун тийш 2 багана шилжүүлсэн. Томьёотой дурын нүдийг сонгоод $G$2:$H$12-ын оронд одоо $I$2:$J$14 болсныг харцгаая.

Одоо Excel дээрх VLOOKUP функц болон унждаг жагсаалттай хэрхэн ажиллахаа олж мэдье. Эхлээд та доош унах жагсаалт гаргах хэрэгтэй. Нүдээ сонгоод дээд талд байгаа "Өгөгдөл" хэсгийг нээгээд товчлуур дээр дарна уу "Өгөгдөл шалгах".

Нээгдсэн "Өгөгдлийн төрөл" цонхонд "Жагсаалт" байх болно, доор нь бид эх сурвалжийг зааж өгсөн болно - эдгээр нь жимсний нэрс, өөрөөр хэлбэл эхний болон хоёрдугаар хүснэгтэд байгаа багана юм. "OK" дээр дарна уу.

Би F2-г сонгоод VLOOKUP функцийг оруулна. Эхний аргумент бол хийсэн жагсаалт (F1) юм.

Хоёр дахь нь заасан үнэ бүхий нийлүүлэгчийн хүснэгт юм. Энд зүүн багана нь доош унадаг жагсаалтыг бүрдүүлдэг өгөгдөлтэй тохирч байх ёстой гэдгийг санаарай.

Энэ нь хайлт шиг зүйл болсон: бид жимс сонгож, VPR үнийн жагсаалтаас үнийг нь олдог.

Хэрэв танд ижил үнэ байсан бол ханган нийлүүлэгчид танд шинэ жагсаалт өгсөн бол юу өөрчлөгдсөнийг ямар нэгэн байдлаар харах хэрэгтэй. Үүнийг гараар хийхэд удаан хугацаа шаардагдах боловч тухайн функцийн тусламжтайгаар бүх зүйлийг маш хурдан хийж болно. Та нэг багана нэмж, түүнд шинэ утгыг шилжүүлж, дараа нь өгөгдлийг харьцуулах хэрэгтэй болно.

D баганын дурын нүдэн дээр дараад шинээр оруулна уу.

Би үүнийг Шинэ үнэ гэж нэрлэсэн - шинэ үнэ энд байх бөгөөд хуучин үнийг зүүн талын баганад харуулах болно. Миний шинэ хүснэгт өөр хуудсан дээр байрладаг тул өгөгдөл нь өөр өөр хуудсан дээр байгаа бол VLOOKUP-г хэрхэн ашиглахыг ойлгох болно. Нэмэгдсэн баганад эхний хоосон нүдийг сонгоно уу.

Бид функцийг оруулаад аргументуудыг зааж өгдөг. Нэгдүгээрт, бид юу хайх болно, жишээн дээр алим (A2). Шинэ үнийн жагсаалтаас мужийг сонгохын тулд курсороо "Хүснэгт" талбарт байрлуулж, хүссэн хуудас руу очно уу, надад "Sheet1" байна.

Гарчиггүйгээр шаардлагатай багана, мөрүүдийг хулганаар сонгоно уу.

Дараа нь бид нүднүүдэд үнэмлэхүй лавлагаа хийдэг: “Sheet1!$A$2:$B$12” . Мөрийг сонгоод "F4" товчийг дарж нүдний хаягуудад долларын тэмдэг нэмнэ. Бид (2) баганыг зааж, "ХУДАЛ" гэж бичнэ.

Төгсгөлд нь "OK" товчийг дарна уу.

Одоо шинэ болон хуучин үнэ бүхий хоёр багана зэрэгцэн байрлаж байгаа бөгөөд та харааны харьцуулалт, эсвэл тодорхой томъёог ашиглах эсвэл нөхцөлт форматлах замаар хийж болно.

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

(13 үнэлгээ, дундаж: 5,00 5-аас)

Вэбмастер. Мэдээллийн аюулгүй байдлын чиглэлээр дээд боловсрол эзэмшсэн, ихэнх нийтлэл, компьютерийн мэдлэгийн хичээлийн зохиогч

Энэхүү заавар нь функцүүдийн гол давуу талыг тайлбарладаг ИНДЕКСТэгээд ХАЙХ Excel-тэй харьцуулахад тэднийг илүү сэтгэл татам болгодог VLOOKUP. Та функцэд тулгарч буй олон нарийн төвөгтэй ажлуудыг хялбархан даван туулахад туслах томъёоны хэд хэдэн жишээг харах болно. VLOOKUPхүчгүй.

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

Энэ яагаад бидэнд хэрэгтэй байна вэ? - Та асуух. Тийм ээ, учир нь VLOOKUPХайлт нь Excel-ийн цорын ганц хайлтын функц биш бөгөөд түүний олон хязгаарлалт нь таныг олон нөхцөл байдалд хүссэн үр дүндээ хүрэхэд саад болдог. Нөгөө талаас, функцууд ИНДЕКСТэгээд ХАЙХ– илүү уян хатан, хэд хэдэн онцлогтой харьцуулахад тэднийг илүү дур булаам болгодог VLOOKUP.

INDEX болон MATCH-ийн талаархи үндсэн мэдээлэл

Учир нь энэ хичээлийн зорилго нь функцүүдийн чадварыг харуулах явдал юм ИНДЕКСТэгээд ХАЙХ Excel дээр босоо хайлтыг хэрэгжүүлэхийн тулд бид тэдгээрийн синтакс болон хэрэглээний талаар ярихгүй.

Энд мөн чанарыг ойлгоход шаардагдах хамгийн бага зүйлийг танилцуулж, дараа нь ашиглах давуу талыг харуулсан томъёоны жишээг нарийвчлан авч үзэх болно. ИНДЕКСТэгээд ХАЙХоронд нь VLOOKUP.

INDEX – функцийн синтакс ба хэрэглээ

Чиг үүрэг ИНДЕКС Excel-ийн (INDEX) нь өгөгдсөн мөр, баганын дугаар дахь массиваас утгыг буцаана. Функц нь дараах синтакстай:




Аргумент бүр маш энгийн тайлбартай:

• массив(массив) нь утгыг гаргаж авахыг хүссэн нүднүүдийн муж юм.
• мөрийн_тоо(мөрийн_тоо) нь массив дахь утгыг гаргаж авахыг хүссэн мөрийн дугаар юм. Хэрэв заагаагүй бол аргумент шаардлагатай баганын_тоо(баганын_тоо).
• баганын_тоо(баганын_тоо) нь массив дахь утгыг гаргаж авахыг хүссэн баганын дугаар юм. Хэрэв заагаагүй бол аргумент шаардлагатай мөрийн_тоо(мөрийн_тоо)

Хэрэв хоёр аргументыг зааж өгсөн бол функц ИНДЕКСзаасан мөр, баганын уулзвар дээрх нүднээс утгыг буцаана.

Энд функцийн энгийн жишээ байна ИНДЕКС(ИНДЕКС):

ИНДЕКС(A1:C10,2,3)
=ИНДЕКС(A1:C10,2,3)

Томъёо нь мужийг хайдаг А1: С10доторх нүдний утгыг буцаана 2 дахьшугам ба 3мбагана, өөрөөр хэлбэл нүднээс C2.

Маш энгийн, тийм үү? Гэсэн хэдий ч практик дээр та аль мөр, багана хэрэгтэйг тэр бүр мэддэггүй тул функцийн тусламж хэрэгтэй болно ХАЙХ.

MATCH - функцийн синтакс ба хэрэглээ

Чиг үүрэг ТОГЛОЛТ Excel-ийн (MATCH) нь нүднүүдийн мужид заасан утгыг хайж, тухайн утгын муж дахь харьцангуй байрлалыг буцаана.

Жишээлбэл, хэрэв мужид байгаа бол B1:B3Нью-Йорк, Парис, Лондон гэсэн утгуудыг агуулж байвал дараах томьёо нь тоог буцаана 3 , учир нь "Лондон" нь жагсаалтын гурав дахь элемент юм.

MATCH("Лондон",B1:B3,0)
=MATCH("Лондон";B1:B3;0)

Чиг үүрэг ТОГЛОЛТ(MATCH) нь дараах синтакстай байна:

MATCH(хайх_утга, хайх_массив,)
MATCH(хайх_утга, хайлтын_массив, [тохируулах_төрөл])

• хайх_утга(хайлтын_утга) нь таны хайж буй тоо эсвэл текст юм. Аргумент нь логик эсвэл нүдний лавлагаа зэрэг утга байж болно.
• хайх_массив(lookup_array) – хайлт явагдаж буй нүдний муж.
• тохирох_төрөл(map_type) – Энэ аргумент нь функцийг хэлдэг ХАЙХ, та яг эсвэл ойролцоо тохирохыг олохыг хүсч байгаа эсэх:
  • 1 эсвэл тодорхойлоогүй– хүссэн хэмжээнээс бага буюу тэнцүү хамгийн их утгыг олно. Үзэж буй массив нь өсөх дарааллаар, өөрөөр хэлбэл хамгийн багаас том хүртэл эрэмблэгдсэн байх ёстой.
  • 0 – хүссэнтэй тэнцүү эхний утгыг олно. Хослолын хувьд ИНДЕКС/ХАЙХТанд үргэлж яг таарч байх шаардлагатай тул функцийн гурав дахь аргумент ХАЙХтэнцүү байх ёстой 0 .
  • -1 – хайлтын утгаас их буюу тэнцүү хамгийн бага утгыг олно. Үзэж буй массив нь буурах дарааллаар, өөрөөр хэлбэл томоос жижиг рүү эрэмблэгдсэн байх ёстой.

Эхлээд харахад функцийн ашиг тус ХАЙХэргэлзээ төрүүлдэг. Муж дахь элементийн байрлалыг хэн мэдэх шаардлагатай вэ? Бид энэ элементийн утгыг мэдэхийг хүсч байна!

Бидний хайж буй утгын харьцангуй байрлал (жишээ нь мөр ба/эсвэл баганын дугаар) нь аргументуудад зааж өгөх ёстой зүйл гэдгийг танд сануулъя. мөрийн_тоо(мөрийн_тоо) ба/эсвэл баганын_тоо(баганын_тоо) функцууд ИНДЕКС(INDEX). Таны санаж байгаагаар функц ИНДЕКСөгөгдсөн мөр, баганын огтлолцол дээрх утгыг буцаах боломжтой боловч бидний аль мөр, багана сонирхож байгааг тодорхойлж чадахгүй.

Excel дээр INDEX болон MATCH-г хэрхэн ашиглах талаар

Одоо та эдгээр хоёр функцийн талаархи үндсэн мэдээллийг мэдэж байгаа тул функцүүд хэрхэн ажиллах нь аль хэдийн тодорхой болсон гэдэгт би итгэж байна ХАЙХТэгээд ИНДЕКСхамтран ажиллах боломжтой. ХАЙХөгөгдсөн хүрээн дэх хайлтын утгын харьцангуй байрлалыг тодорхойлдог ба ИНДЕКСтухайн дугаарыг (эсвэл тоонуудыг) ашиглан харгалзах нүднээс үр дүнг буцаана.

Бүрэн тодорхой болоогүй байна уу? Функцуудыг танилцуулна уу ИНДЕКСТэгээд ХАЙХэнэ хэлбэрээр:

INDEX(,(ТАРУУЛАХ ( хайлтын үнэ цэнэ,бидний хайж буй багана,0))
=INDEX( бидний гаргаж авсан багана;(ТОХИРУУЛАХ( хайлтын үнэ цэнэ;бидний хайж буй багана;0))

Үүнийг жишээгээр ойлгоход илүү хялбар байх болно гэж би бодож байна. Танд улсын нийслэлүүдийн дараах жагсаалт байна гэж бодъё.



Дараах томъёог ашиглан нэг нийслэл, жишээлбэл Япон улсын хүн амыг олъё.

ИНДЕКС($D$2:$D$10,MATCH("Япон",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10,ТАРЧИЛСАН("Япон",$B$2:$B$10,0))

Одоо энэ томьёоны элемент бүр юу хийж байгааг харцгаая.

• Чиг үүрэг ТОГЛОЛТ(MATCH) баганад "Япон" гэсэн утгыг хайж олно Б, ялангуяа - эсүүдэд B2:B10, мөн тоо буцаана 3 , "Япон" жагсаалтын гуравдугаарт бичигдсэн тул.
• Чиг үүрэг ИНДЕКС(INDEX) ашигладаг 3 маргааны төлөө мөрийн_тоо(мөрийн_тоо), аль эгнээнээс утгыг буцаахыг зааж өгнө. Тэдгээр. Бид энгийн томъёог олж авдаг:

  ИНДЕКС($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10,3)

  Томъёо нь иймэрхүү зүйлийг хэлдэг: нүднүүдийг харах D2өмнө D10мөн утгыг гурав дахь эгнээнээс, өөрөөр хэлбэл нүднээс гаргаж авна D4, тоолох нь хоёр дахь мөрөөс эхэлдэг тул.

Энэ бол Excel дээр олж авсан үр дүн юм:



Чухал! Функцийн ашигладаг массив дахь мөр, баганын тоо ИНДЕКС(INDEX), аргументын утгуудтай тохирч байх ёстой мөрийн_тоо(мөрийн_тоо) ба баганын_тоо(баганын_тоо) функцууд ТОГЛОЛТ(ТОГЛОЛТ). Үгүй бол томъёоны үр дүн алдаатай байх болно.

Хүлээгээрэй, хүлээгээрэй... яагаад бид зүгээр л функц ашиглаж болохгүй гэж? VLOOKUP(VPR)? Төөрдөг байшинг олох гэж цаг үрэх нь утга учиртай юу? ХАЙХТэгээд ИНДЕКС?

VLOOKUP("Япон",$B$2:$D$2,3)
=VLOOKUP("Япон",$B$2:$D$2,3)

Энэ тохиолдолд ямар ч утгагүй юм! Энэ жишээний зорилго нь зөвхөн үзүүлэх зорилготой бөгөөд ингэснээр та хэрхэн ажилладагийг ойлгох болно ХАЙХТэгээд ИНДЕКСхоёр хоёроороо ажил. Дараах жишээнүүд нь багцын жинхэнэ хүчийг харуулах болно. ИНДЕКСТэгээд ХАЙХ, энэ нь олон хүнд хэцүү нөхцөл байдлыг амархан даван туулж чаддаг VLOOKUPмухардалд оров.

INDEX/MATCH яагаад VLOOKUP-ээс илүү байдаг вэ?

Босоо хайлт хийхэд ямар томъёог ашиглахаа шийдэхдээ ихэнх Excel багш нар үүнд итгэдэг ИНДЕКС/ХАЙХ-аас хамаагүй дээр VLOOKUP. Гэсэн хэдий ч Excel-ийн олон хэрэглэгчид ашиглахыг хичээдэг VLOOKUP, учир нь Энэ функц нь илүү хялбар байдаг. Энэ нь маш цөөхөн хүнээс шилжихийн бүх ашиг тусыг бүрэн ойлгодог тул ийм зүйл тохиолддог VLOOKUPнэг баглаа ИНДЕКСТэгээд ХАЙХ, мөн хэн ч илүү төвөгтэй томъёог судалж цаг үрэхийг хүсэхгүй байна.

Excel дээр MATCH/INDEX ашиглахын 4 үндсэн давуу тал:

1. Баруунаас зүүн тийш хайх. Excel програмын чадварлаг хэрэглэгч бүр мэддэг. VLOOKUPзүүн тийш харах боломжгүй бөгөөд энэ нь хайж буй утга нь шалгаж буй мужын хамгийн зүүн баганад байх ёстой гэсэн үг юм. Тохиолдолд ХАЙХ/ИНДЕКС, хайлтын багана нь хайлтын хүрээний зүүн эсвэл баруун талд байж болно. Жишээ нь: энэ функцийг үйлдэл дээр харуулах болно.

2. Багануудыг аюулгүйгээр нэмэх эсвэл хасах.Функц бүхий томьёо VLOOKUPХэрэв та хайлтын хүснэгтэд багана нэмэх эсвэл хасах юм бол ажиллахаа зогсоох эсвэл алдаатай утгыг буцаана уу. Функцийн хувьд VLOOKUPоруулсан эсвэл хассан багана нь синтакс учир томьёоны үр дүнг өөрчилнө VLOOKUPта өгөгдлийг задлахыг хүссэн бүх хүрээ болон тодорхой баганын дугаарыг зааж өгөхийг шаарддаг.

Жишээлбэл, хэрэв танд ширээ байгаа бол А1: С10, мөн та баганаас өгөгдөл авахыг хүсч байна Б, дараа нь та утгыг тохируулах хэрэгтэй 2 маргааны төлөө col_index_num(баганын_тоо) функцууд VLOOKUP, үүн шиг:

VLOOKUP("хайлтын утга",A1:C10,2)
=VLOOKUP("хайлтын утга";A1:C10;2)

Хэрэв та дараа нь баганын хооронд шинэ багана оруулбал АТэгээд Б, дараа нь аргументийн утгыг -аас өөрчлөх шаардлагатай болно 2 дээр 3 , эс бөгөөс томьёо нь шинээр оруулсан баганаас үр дүнг буцаана.

Ашиглаж байна ХАЙХ/ИНДЕКСХүссэн утгыг агуулсан багана нь шууд тодорхойлогдсон тул үр дүнг гажуудуулахгүйгээр судалж буй мужид багана нэмэх эсвэл хасах боломжтой. Үнэн хэрэгтээ энэ нь маш том давуу тал юм, ялангуяа их хэмжээний өгөгдөлтэй ажиллах шаардлагатай үед. Та ашигладаг функц бүрээ засахаас санаа зовохгүйгээр багана нэмж, хасах боломжтой VLOOKUP.

3. Хайж буй утгын хэмжээнд ямар ч хязгаарлалт байхгүй.Ашиглаж байна VLOOKUP, хайсан утгын урт нь 255 тэмдэгтээр хязгаарлагддаг гэдгийг санаарай, эс тэгвээс та алдаа гаргах эрсдэлтэй. #VALUE!(#ҮНЭ ЦЭНЭ!). Тиймээс, хэрэв хүснэгтэд урт мөр байгаа бол ашиглах боломжтой цорын ганц шийдэл бол ашиглах явдал юм ИНДЕКС/ХАЙХ.

Та энэ томъёог ашиглана гэж бодъё VLOOKUP-аас нүднүүдийг хайдаг В5өмнө D10нүдэнд заасан утга А2:

ДҮРҮҮЛЭХ(A2,B5:D10,3,ХУДАЛ)
=VLOOKUP(A2,B5:D10,3,ХУДАЛ)

Хэрэв нүдэн дэх утга нь байвал томьёо ажиллахгүй А2 255 тэмдэгтээс урт. Үүний оронд та ижил төстэй томъёог ашиглах хэрэгтэй ИНДЕКС/ХАЙХ:

ИНДЕКС(D5:D10, ТОХИРОХ(ҮНЭН,ИНДЕКС(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10, ТААРУУЛАХ(ҮНЭН,ИНДЕКС(B5:B10=A2,0),0))

4. Үйл ажиллагааны өндөр хурд.Хэрэв та жижиг хүснэгтүүдтэй ажилладаг бол Excel-ийн гүйцэтгэлийн ялгаа нь ялангуяа хамгийн сүүлийн үеийн хувилбаруудад мэдэгдэхүйц биш байх магадлалтай. Хэрэв та мянга мянган мөр, хэдэн зуун хайлтын томъёо агуулсан том хүснэгтүүдтэй ажилладаг бол Excel нь илүү хурдан ажиллах болно. ХАЙХТэгээд ИНДЕКСоронд нь VLOOKUP. Ерөнхийдөө энэ орлуулалт нь Excel-ийн хурдыг нэмэгдүүлдэг 13% .

Нөлөөлөл VLOOKUP Excel-ийн гүйцэтгэл нь ялангуяа ажлын дэвтэрт олон зуун нийлмэл массив томьёо агуулсан байвал мэдэгдэхүйц байх болно VLOOKUP+SUM. Баримт нь массив дахь утга бүрийг шалгахын тулд тусдаа функцийн дуудлага шаардлагатай болно VLOOKUP. Тиймээс, массив хичнээн их утгыг агуулж, таны хүснэгтэд илүү олон массив томьёо агуулагдах тусам Excel нь удааширдаг.

Нөгөө талаас функцтэй томьёо ХАЙХТэгээд ИНДЕКСЭнэ нь зүгээр л хайлт хийж, үр дүнг нь буцааснаар ижил төстэй ажлыг мэдэгдэхүйц хурдан гүйцэтгэдэг.

INDEX ба MATCH - томъёоны жишээ

Одоо та функцуудыг сурах ёстой шалтгааныг ойлгож байна ХАЙХТэгээд ИНДЕКС, хөгжилтэй хэсэг рүүгээ орж, онолын мэдлэгээ практикт хэрхэн хэрэгжүүлж болохыг харцгаая.

MATCH болон INDEX ашиглан зүүн талаас хэрхэн хайх вэ

Ямар ч сурах бичиг дээр VLOOKUPЭнэ функц нь зүүн тийш харах боломжгүй гэж хэлдэг. Тэдгээр. Хэрэв таны харж буй багана хайлтын муж дахь хамгийн зүүн талын багана биш бол түүнээс авах боломж байхгүй VLOOKUPхүссэн үр дүн.

Функцүүд ХАЙХТэгээд ИНДЕКС Excel нь илүү уян хатан бөгөөд таны авахыг хүссэн утга бүхий багана хаана байх нь хамаагүй. Жишээлбэл, муж улсын нийслэл, хүн амтай хүснэгтэд эргэн оръё. Энэ удаад бид томъёог бичих болно ХАЙХ/ИНДЕКС, энэ нь Оросын нийслэл (Москва) хүн амын тоогоор ямар байр эзэлдэгийг харуулах болно.

Доорх зургаас харахад томъёо нь энэ ажлыг төгс гүйцэтгэдэг.

ИНДЕКС($A$2:$A$10,MATCH("Орос",$B$2:$B$10,0))




Одоо та энэ томъёо хэрхэн ажилладагийг ойлгоход ямар ч асуудал гарах ёсгүй:

• Эхлээд функцийг ашиглая ТОГЛОЛТЖагсаалтад "Орос"-ын байр суурийг олж авсан (MATCH):

  MATCH("Орос",$B$2:$B$10,0))
  =MATCH("Орос",$B$2:$B$10,0))

• Дараа нь функцийн мужийг тохируулна уу ИНДЕКС(INDEX) -аас утгыг гаргаж авах. Манай тохиолдолд ийм байна А2: А10.
• Дараа нь бид хоёр хэсгийг нэгтгэж, томъёог авна.

  ИНДЕКС($A$2:$A$10; TАРЧИЛСАН("Орос";$B$2:$B$10,0))
  =ИНДЕКС($A$2:$A$10,МАТЧ("Орос",$B$2:$B$10,0))

Сэтгэгдэл:Зөв шийдэл бол үнэмлэхүй лавлагааг үргэлж ашиглах явдал юм ИНДЕКСТэгээд ХАЙХ, ингэснээр томьёог бусад нүд рүү хуулах үед хайлтын мужууд алдагдахгүй.

Excel-д INDEX болон MATCH ашиглан тооцоо хийх (ДУНДЖ, МАКС, MIN)

Та бусад Excel функцүүдийг дотор нь байрлуулж болно ИНДЕКСТэгээд ХАЙХжишээлбэл, хамгийн бага, хамгийн их эсвэл дундаж утгад хамгийн ойрын утгыг олох. Хүснэгттэй холбоотой томъёоны хэд хэдэн сонголт энд байна:

1. МАКС(MAX). Томъёо нь багананд хамгийн ихийг олдог Д Cижил мөр:

ИНДЕКС($C$2:$C$10, ТААРУУЛАХ(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10, ТОХИРЧ(MAX($D$2:I$10),$D$2:D$10,0))

Үр дүн: Бээжин

2. MIN(MIN). Томъёо нь баганад хамгийн бага хэмжээг олдог Дба баганаас утгыг буцаана Cижил мөр:

ИНДЕКС($C$2:$C$10, ТААРУУЛАХ(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10, ТААРУУЛАХ(MIN($D$2:I$10),$D$2:D$10,0))

Үр дүн: Лима

3. ДУНДЖ(ДУНДЖ). Томъёо нь мужын дундажийг тооцоолно D2: D10, дараа нь түүнд хамгийн ойрыг олж, баганаас утгыг буцаана Cижил мөр:

ИНДЕКС($C$2:$C$10,ТАРЧИЛСАН(ДУНДЖ($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10, ТОХИРОХ(ДУНДЖ($D$2:D$10),$D$2:D$10,1))

Үр дүн: Москва

INDEX болон MATCH-тай AVERAGE функцийг ашиглахдаа анхаарах зүйлс

Функцийг ашиглах ДУНДЖ-тай хослуулан ИНДЕКСТэгээд ХАЙХ, функцийн гурав дахь аргумент болгон ХАЙХихэвчлэн зааж өгөх шаардлагатай болно 1 эсвэл -1 таны харж буй муж дундажтай тэнцэх утгыг агуулж байгаа гэдэгт итгэлгүй байгаа тохиолдолд. Хэрэв та ийм үнэ цэнэ байгаа гэдэгт итгэлтэй байгаа бол тавь 0 яг тохирохыг олохын тулд.

• Хэрэв та зааж өгвөл 1 , хайлтын баганад байгаа утгуудыг өсөх дарааллаар эрэмбэлсэн байх ёстой бөгөөд томъёо нь дунджаас бага буюу тэнцүү хамгийн их утгыг буцаана.
• Хэрэв та зааж өгвөл -1 , хайлтын баганад байгаа утгуудыг буурах дарааллаар эрэмбэлж, дунджаас их буюу тэнцүү хамгийн бага утгыг буцаана.

Бидний жишээн дээр баганад байгаа утгууд Дөсөх дарааллаар эрэмблэгдсэн тул бид эвлүүлэх төрлийг ашигладаг 1 . Томъёо ИНДЕКС/SEARCHPOЗМосква хотын хүн ам дундаж утгаас хамгийн бага (12,269,006) тул "Москва"-г буцаана.



Мэдэгдэж буй мөр, баганыг хайхдаа INDEX болон MATCH-ийг хэрхэн ашиглах вэ

Энэ томьёо нь хоёр хэмжээст хайлттай тэнцэнэ VLOOKUPтодорхой мөр, баганын огтлолцол дээрх утгыг олох боломжийг танд олгоно.

Энэ жишээнд томъёо ИНДЕКС/ХАЙХЭнэ хичээл дээр бидний өмнө нь авч үзсэн томьёотой маш төстэй бөгөөд зөвхөн нэг ялгаа байх болно. Аль нь таагаарай?

Таны санаж байгаагаар функцийн синтакс ИНДЕКС(INDEX) нь гурван аргументыг зөвшөөрдөг:

INDEX(массив,мөрийн_тоо,)
INDEX(массив, мөрийн_тоо, [баганын_тоо])

Үүнийг таасан хүмүүст би баяр хүргэе!

Томъёоны загварыг бичиж эхэлцгээе. Үүнийг хийхийн тулд бидэнд аль хэдийн танил болсон томъёог авч үзье ИНДЕКС/ХАЙХмөн түүнд өөр функц нэмнэ үү ХАЙХ, энэ нь баганын дугаарыг буцаана.

INDEX(Таны хүснэгт ,(MATCH(, хайх багана,0)),(ТОХИРУУЛАХ(, хайх шугам,0))
=INDEX(Таны хүснэгт ,(MATCH( босоо хайлтын утга,хайх багана,0)),(ТОХИРУУЛАХ( хэвтээ хайлтын утга,хайх шугам,0))

Хоёр хэмжээст хайлтын хувьд аргумент дахь хүснэгтийг бүхэлд нь зааж өгөх хэрэгтэй гэдгийг анхаарна уу массив(массив) функцууд ИНДЕКС(INDEX).

Одоо энэ загварыг практик дээр туршиж үзье. Доор та дэлхийн хамгийн их хүн амтай орнуудын жагсаалтыг харна уу. Бидний даалгавар бол 2015 онд АНУ-ын хүн амыг олж мэдэх явдал юм гэж бодъё.



За, томъёогоо бичье. Би Excel-д үүрлэсэн функц бүхий цогц томьёо үүсгэх шаардлагатай үед эхлээд үүрлэсэн функц бүрийг тусад нь бичдэг.

Тиймээс хоёр функцээс эхэлье ХАЙХ, энэ нь функцийн мөр, баганын дугаарыг буцаана ИНДЕКС:

• Баганын хувьд MATCH- бид багана руу харж байна Б, эс тэгвээс мужид B2:B11, нүдэнд заасан утга H2(АНУ). Функц нь дараах байдлаар харагдах болно.

  ТОХИРУУЛАХ($H$2,$B$1:$B$11,0)
  =ТОХИРУУЛАХ($H$2,$B$1:$B$11,0)

  4 , учир нь "АНУ" нь баганын жагсаалтын 4-р элемент юм Б(гарчиг зэрэг).

• Мөрт зориулсан MATCH- бид эсийн утгыг хайж байна H3(2015) эгнээнд 1 , өөрөөр хэлбэл эсүүдэд A1: E1:

  ТААРУУЛАХ($H$3,$A$1:$E$1,0)
  =ТОХИРУУЛАХ($H$3,$A$1:$E$1,0)

  Энэ томъёоны үр дүн гарах болно 5 , 5-р баганад "2015" байгаа тул.

Одоо бид эдгээр томъёог функцэд оруулна ИНДЕКСболон voila:

ИНДЕКС($A$1:$E$11,ТАРЧИЛСАН($H$2,$B$1:$B$11,0), TАРЧИЛСАН($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11, ТОГЛОХ($H$2,$B$1:$B$11,0), ТОГЛОХ($H$3,$A$1:$E$1,0))

Хэрэв та функцуудыг солих юм бол ХАЙХТэдний буцаах утгууд дээр үндэслэн томъёо нь хялбар бөгөөд ойлгомжтой болно.

ИНДЕКС($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11,4,5))

Энэ томьёо нь уулзвар дээрх утгыг буцаана 4 дэхшугам ба 5 дахьмуж дахь багана А1: E11, өөрөөр хэлбэл нүдний утга E4. Зүгээр үү? Тийм ээ!



INDEX болон MATCH бүхий олон шалгуурын хайлт

заавар дээр VLOOKUPБид функц бүхий томъёоны жишээг үзүүлэв VLOOKUPолон шалгуурыг ашиглан хайх. Гэсэн хэдий ч энэ шийдлийн мэдэгдэхүйц хязгаарлалт нь туслах баганыг нэмэх хэрэгцээ байв. Сайн мэдээ: томъёо ИНДЕКС/ХАЙХТуслах багана үүсгэх шаардлагагүйгээр утгуудыг хоёр баганад хайх боломжтой!

Бидэнд захиалгын жагсаалт байгаа бөгөөд бид хоёр шалгуурт үндэслэн дүнг олохыг хүсч байна гэж бодъё худалдан авагчийн нэр(Хэрэглэгч) болон бүтээгдэхүүн(Бүтээгдэхүүн). Нэг худалдан авагч нэг дор хэд хэдэн өөр бүтээгдэхүүн худалдаж авах боломжтой, мөн хүснэгтэд байгаа худалдан авагчдын нэрс байгаа тул асуудал төвөгтэй байдаг. Хайлтын хүснэгтсанамсаргүй дарааллаар байрлуулсан.



Энд томъёо байна ИНДЕКС/ХАЙХасуудлыг шийддэг:

(=INDEX("Хайлтын хүснэгт"!$A$2:$C$13, MATCH(1,(A2="Хайлтын хүснэгт"!$A$2:$A$13)*
(B2="Хайлтын хүснэгт"!$B$2:$B$13),0),3))
(=INDEX("Хайлтын хүснэгт"!$A$2:$C$13; MATCH(1,(A2="Хайлтын хүснэгт"!$A$2:$A$13)*
(B2="Хайлтын хүснэгт"!$B$2:$B$13);0);3))

Энэ томъёо нь бидний өмнө нь авч үзсэн бусадтай харьцуулахад илүү төвөгтэй боловч функцүүдийн талаархи мэдлэгээр зэвсэглэсэн байдаг ИНДЕКСТэгээд ХАЙХЧи түүнийг ялах болно. Хамгийн хэцүү хэсэг бол функц юм ХАЙХ, үүнийг эхлээд тайлбарлах хэрэгтэй гэж бодож байна.

MATCH(1,(A2="Хайлтын хүснэгт"!$A$2:$A$13),0)*(B2="Хайлтын хүснэгт"!$B$2:$B$13)
MATCH(1;(A2="Хайлтын хүснэгт"!$A$2:$A$13);0)*(B2="Хайлтын хүснэгт"!$B$2:$B$13)

Дээр үзүүлсэн томъёонд бидний хайж буй утга нь байна 1 , хайлтын массив нь үржүүлгийн үр дүн юм. За, бид юуг үржүүлэх ёстой вэ, яагаад? Бүгдийг дарааллаар нь харцгаая:

• Баганын эхний утгыг авна уу А(Хэрэглэгч) хуудсан дээр Үндсэн хүснэгтмөн хуудсан дээрх хүснэгтэд байгаа бүх хэрэглэгчийн нэрстэй харьцуулна уу Хайлтын хүснэгт(A2:A13).
• Хэрэв тохирол олдвол тэгшитгэл буцаана 1 (ҮНЭН), хэрэв үгүй ​​бол - 0 (Худлаа).
• Дараа нь бид баганын утгуудын хувьд ижил зүйлийг хийнэ Б(Бүтээгдэхүүн).
• Дараа нь бид олж авсан үр дүнг үржүүлнэ (1 ба 0). Зөвхөн хоёр багананд таарч байгаа тохиолдолд (жишээ нь, хоёр шалгуур хоёулаа үнэн бол) та хүлээн авах болно 1 . Хэрэв хоёр шалгуур нь худал эсвэл тэдгээрийн зөвхөн нэг нь хангагдсан бол та хүлээн авах болно 0 .

Бидний яагаад асуусныг одоо та ойлгож байна 1 , хүссэн утга нь юу вэ? Функц нь зөв ХАЙХхоёр шалгуурыг хангасан тохиолдолд л албан тушаалаа буцаасан.

Жич:Энэ тохиолдолд та функцийн гурав дахь нэмэлт аргументыг ашиглах ёстой ИНДЕКС. Энэ нь зайлшгүй шаардлагатай, учир нь Эхний аргумент дээр бид хүснэгтийг бүхэлд нь зааж өгөх ба аль баганаас утгыг авахыг функцэд хэлэх ёстой. Манай тохиолдолд энэ нь багана юм C(Сум), тэгээд бид орлоо 3 .

Тэгээд эцэст нь, учир нь Бид массив дахь нүд бүрийг шалгах хэрэгтэй, энэ томъёо нь массивын томьёо байх ёстой. Та үүнийг хавсаргасан буржгар хаалтнаас харж болно. Тиймээс томьёог оруулж дууссаны дараа дарахаа бүү мартаарай Ctrl+Shift+Enter.

Хэрэв бүх зүйл зөв хийгдсэн бол доорх зурагт үзүүлсэн шиг үр дүн гарах болно.



INDEX болон MATCH-ийг Excel-ийн IFERROR-тай хослуулсан

Магадгүй та аль хэдийн анзаарсан байх (нэгээс олон удаа) хэрэв та буруу утгыг оруулбал, жишээлбэл, харж буй массив дотор байхгүй утгыг томьёо ИНДЕКС/ХАЙХалдаа мэдээлдэг #Үгүй(#Үгүй) эсвэл #VALUE!(#ҮНЭ ЦЭНЭ!). Хэрэв та ийм мессежийг илүү ойлгомжтой зүйлээр солихыг хүсвэл томъёог оруулж болно ИНДЕКСТэгээд ХАЙХфункц болгон хувиргана АЛДАА.

Функцийн синтакс АЛДААмаш энгийн:

ХЭРЭГСЭЛ(утга, алдааны_утга)
ХЭРЭГСЭЛ(утга, алдааны_утга)

Маргаан хаана байна үнэ цэнэ(утга) нь алдаа байгаа эсэхийг шалгаж буй утга (манай тохиолдолд томъёоны үр дүн юм ИНДЕКС/ХАЙХ); болон аргумент алдаа_хэрэв_утга(алдаа_хэрэв_утга) нь томьёо нь алдаа гаргавал буцаах утга юм.

Жишээлбэл, та функцэд оруулах боломжтой АЛДААүүн шиг:

ХЭРЭГЛЭЭ(ИНДЕКС($A$1:$E$11,ТАРЧИЛСАН($G$2,$B$1:$B$11,0), TАРЧИЛСАН ($G$3,$A$1:$E$1,0)),
"Тохирох зүйл олдсонгүй. Дахин оролдоно уу!") =ГЭРЭЛТ(ИНДЕКС($A$1:$E$11, TАРЧИЛГАА($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1 : $E$1;0);
"Тохирох зүйл олдсонгүй. Дахин оролдоно уу!")

Хэрэв хэн нэгэн буруу утгыг оруулсан бол томъёо нь дараах үр дүнг гаргах болно.



Хэрэв та алдаа гарсан тохиолдолд нүдийг хоосон орхихыг хүсвэл функцийн хоёр дахь аргументын утга болгон хашилтыг ("") ашиглаж болно. АЛДАА. Үүн шиг:

ХЭРЭГЛЭЭ(ИНДЕКС(массив,ТАРЦУУЛАХ(хайлтын_утга,хайлтын_массив,0),"")
ХЭРЭГЛЭЭ(ИНДЕКС(массив,ТАРЦУУЛАХ(хайх_утга,харагдсан_массив,0),"")

Энэ сурах бичигт дурдсан ядаж нэг томъёолол танд хэрэгтэй байсан гэж найдаж байна. Хэрэв танд энэ хичээлийн мэдээллийн дотроос тохирох шийдлийг олж чадаагүй хайлтын бусад асуудлуудтай тулгарсан бол тайлбар дээр асуудлаа чөлөөтэй тайлбарлана уу, бид бүгд хамтдаа үүнийг шийдэхийг хичээх болно.

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

Excel дээрх VLOOKUP функцын синтакс ба тайлбар

Тиймээс, энэ нийтлэлийн хоёр дахь гарчиг нь " Дэмжигчдэд зориулсан Excel-ийн VLOOKUP функц", VLOOKUP функц гэж юу болох, ямар үүрэг гүйцэтгэдэг болохыг олж мэдээд эхэлцгээе? Англи хэл дээрх VLOOKUP функц VLOOKUP, заасан утгыг хайж, өөр баганаас харгалзах утгыг буцаана.

VLOOKUP функц хэрхэн ажилладаг? Excel-ийн VLOOKUP функц нь өвөрмөц танигч дээр үндэслэн таны өгөгдлийн жагсаалтыг хайж, тухайн өвөрмөц танигчтай холбоотой мэдээллийг танд өгдөг.

VPR дахь "V" үсэг нь "босоо" гэсэн утгатай. Үүнийг ялгахад ашигладаг VLOOKUP болон GLOOKUP функцууд, энэ нь массивын дээд эгнээний утгыг хайдаг ("H" нь "хэвтээ" гэсэн утгатай).

VLOOKUP функц нь Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003-ын бүх хувилбарт боломжтой.

Синтакс VLOOKUP функцууд дараах байдлаар:

VLOOKUP(хайлтын_утга, хүснэгт, баганын_тоо, [завсрын_хайлт])

Өөрөө харж байгаа байх, VLOOKUP функц 4 параметр буюу аргументтай. Эхний гурван параметр шаардлагатай, сүүлчийнх нь сонголттой.

1. хайх_утгахайлтын утга юм.

Энэ нь утга (тоо, огноо, текст) эсвэл байж болно эсийн лавлагаа(хайлтын утгыг агуулсан нүдний лавлагаа) эсвэл бусад Excel функцээр буцаасан утга. Жишээлбэл:

• Тоо хайх: =VLOOKUP(40; A2:B15; 2) - томьёо нь 40 тоог хайх болно.
• Текст хайх: =VLOOKUP("алим", A2:B15, 2) - томьёо нь "алим" текстийг хайх болно. Та үргэлж "давхар хашилт" дотор текстийн утгыг оруулдаг гэдгийг анхаарна уу.
• Өөр нүднээс утгыг хайж байна: =VLOOKUP(C2, A2:B15, 2) - томъёо нь C2 нүднээс утгыг хайх болно.
1. Хүснэгт нь хоёр ба түүнээс дээш багана мэдээлэл юм.

гэдгийг санах VLOOKUP функцХүснэгтийн эхний баганад хүссэн утгыг үргэлж хайж байдаг. Таны хүснэгтэд текст, огноо, тоо эсвэл логик утга гэх мэт янз бүрийн утгыг агуулж болно. Үнэ цэнэ том жижиг үсгийн мэдрэмжгүй, энэ нь том жижиг үсгийг ижил гэж үзнэ гэсэн үг юм.

Тиймээс бидний томъёо =VLOOKUP(40, A2:B15, 2) нь A2:B15 хүснэгтийн эхний багана учир A2-аас A15 хүртэлх нүднүүдэд "40"-ийг хайх болно.

1. баганын_тоо - харгалзах мөр дэх утгыг буцаах хүснэгтийн баганын дугаар.

Заасан хүснэгтийн хамгийн зүүн талын багана нь 1, хоёр дахь багана нь 2, гурав дахь багана нь 3 гэх мэт.

4. интервалын_үзэхТа яг таарч байгаа эсэхийг (БУДАЛ) эсвэл ойролцоо тохирохыг (ҮНЭН эсвэл орхигдуулсан) хайж байгаа эсэхийг тодорхойлно. Энэ сүүлчийн параметр нь сонголттой боловч маш чухал юм.

Excel жишээн дэх VLOOKUP функц

Одоо зарим хэрэглээний тохиолдлыг авч үзье VLOOKUP функцууд бодит мэдээллийн хувьд.

Өөр өөр хуудсан дээрх VLOOKUP функц

Практикт VLOOKUP томьёо нь нэг хуудаснаас өгөгдөл хайхад ховор хэрэглэгддэг. Ихэнх тохиолдолд та өөр хуудаснаас холбогдох өгөгдлийг хайж, татаж авах хэрэгтэй болно.

руу VLOOKUP функцийг ашиглана уу Excel-ийн өөр хуудаснаас та ажлын хуудасны нэр болон нүднүүдийн мужаас өмнө хүснэгтийн аргумент дахь анхаарлын тэмдэг оруулах ёстой, жишээлбэл =VLOOKUP(40;Sheet2!A2:B15,2). Томъёо нь A2:B15 хайлтын муж нь Sheet2-д байгааг харуулж байна.

Мэдээжийн хэрэг, та хуудасны нэрийг гараар оруулах шаардлагагүй. Зүгээр л томьёог бичиж эхлэх ба хүснэгтийн аргументийн тухай ярихад хайлтын хуудас руу шилжиж, хулганаараа мужийг сонгоно уу.

Доорх зурган дээрх таны харж буй томьёо нь "Үнэ" ажлын хуудасны А баганын (A2:B9 хайлтын хүрээний 1-р багана) A2 нүдэнд ("Бүтээгдэхүүн 3") текстийг хайж байна:

ХАРАХ(A2,Үнэ!$A$2:$B$8,2,ХУДАЛ)

Excel-ийн VLOOKUP функц - өөр өөр хуудсан дээрх VLOOKUP функц

VLOOKUP томьёонд нэрлэсэн муж эсвэл хүснэгтийг хэрхэн ашиглах талаар

Хэрэв та ижил хайлтын мужийг хэд хэдэн VLOOKUP томьёонд ашиглахаар төлөвлөж байгаа бол түүний нэрийг шууд VLOOKUP томьёоны хүснэгтийн аргумент дээр үүсгэж, оруулж болно.

Нэрлэсэн муж үүсгэхийн тулд нүднүүдийг сонгоод, Томъёоны самбарын зүүн талд байгаа Нэр талбарт дурын нэрийг оруулна уу.



Excel-ийн VLOOKUP функц - Мужийг нэрлэх

Одоо та 1-р бүтээгдэхүүний үнийг авахын тулд дараах VLOOKUP томьёог бичиж болно.

VLOOKUP("Бүтээгдэхүүн 1"; Бүтээгдэхүүн;2)



Excel дээрх VLOOKUP функц - Мужийн нэртэй VLOOKUP функцийн жишээ

Excel дээрх ихэнх мужуудын нэр нь ажлын номыг бүхэлд нь хамардаг тул таны хайлтын хүрээ өөр ажлын хуудсан дээр байгаа ч гэсэн ажлын хуудасны нэрийг зааж өгөх шаардлагагүй. Ийм томъёо нь илүү ойлгомжтой байдаг. Мөн нэрлэсэн мужуудыг ашиглах нь нүднүүдийн сайн хувилбар байж болно. Томьёог бусад нүд рүү хуулах үед нэрлэсэн муж өөрчлөгддөггүй тул таны хайлтын хүрээ үргэлж зөв хэвээр байх болно гэдэгт итгэлтэй байж болно.

Хэрэв та хэд хэдэн нүдийг бүрэн функцтэй Excel хүснэгт болгон хөрвүүлсэн бол (Insert tab -> Table) хулганаараа хайлтын мужийг сонгох боломжтой бөгөөд Microsoft Excel нь томъёонд баганын нэр эсвэл хүснэгтийн нэрийг автоматаар нэмнэ. :



Excel дээрх VLOOKUP функц - Хүснэгтийн нэр бүхий VLOOKUP функцийн жишээ

Бүрэн томъёо нь иймэрхүү харагдаж болно:

VLOOKUP("Бүтээгдэхүүн 1";Хүснэгт6[[Бүтээгдэхүүн]:[Үнэ]];2)

эсвэл бүр =VLOOKUP("Бүтээгдэхүүн 1";Хүснэгт 6;2).

Нэрлэсэн мужуудын нэгэн адил баганын нэр тогтмол байх ба DLOOKUP томьёог хаана хуулсанаас үл хамааран нүдний лавлагаа өөрчлөгдөхгүй.

Олон нөхцөл бүхий VLOOKUP функц

Ингээд авч үзье хэд хэдэн нөхцөл бүхий VLOOKUP функцын жишээ. Бидэнд дараах оролтын өгөгдөл байна:



Excel-ийн VLOOKUP функц - Эх сурвалжийн өгөгдлийн хүснэгт

Бид ашиглах хэрэгтэй гэж бодъё Хэд хэдэн нөхцөл бүхий VLOOKUP функц. Жишээлбэл, бүтээгдэхүүний нэр, төрөл гэсэн хоёр шалгуурыг ашиглан бүтээгдэхүүний үнийг хайх.

1. Хэрэглэх Хэд хэдэн нөхцөл бүхий VLOOKUP функц эхэнд бүтээгдэхүүний нэр, төрөл бүхий мэдээллийг хадгалах нэмэлт багана оруулах шаардлагатай.

Тиймээс, "Үнэ" хуудсан дээр багана оруулаад A2 нүдэнд дараах томъёог оруулна уу.

Энэ томъёог ашиглан бид "Бүтээгдэхүүн" ба "Төрөл" баганын утгыг олж авна. Бүх нүдийг бөглөнө үү.

Одоо хайлтын хүснэгт дараах байдалтай байна.



Excel-ийн VLOOKUP функц - Туслах багана нэмэх

1. Одоо "Борлуулалт" хуудасны C2 нүдэнд бид дараах VLOOKUP томьёог бичнэ.

ХУДАЛТ(A2Үнэ!$A$1:$D$8,4,ХУДАЛ)

Бид үлдсэн нүдийг бөглөж, үр дүнд нь бүтээгдэхүүн бүрийн үнийг төрлөөр нь авна.



Excel-ийн VLOOKUP функц - Олон нөхцөл бүхий VLOOKUP жишээ

Одоо үүнийг харцгаая VLOOKUP функцийн алдаа.

VLOOKUP функц яагаад ажиллахгүй байна вэ?

Өгүүллийн энэ хэсэгт бид яагаад гэдгийг авч үзэх болно VLOOKUP функц ажиллахгүй байнамөн боломжтой VLOOKUP функцийн алдаа.

Алдааны төрөл	Шалтгаан	Шийдэл
	Хайж буй баганын байршил буруу байна	Хайлт хийж буй хүснэгтийн багана нь хамгийн зүүн талд байх ёстой. Хайж буй баганаа хүснэгтийн зүүн талд шилжүүлнэ үү. Эсвэл хүснэгтийн зүүн талд туслах давхар багана үүсгэ.
	Хүснэгтийн хүрээ тогтмол биш байна	Хэрэв эхний утгыг зөв харуулж, VLOOKUP томьёог сунгасны дараа зарим нүдэнд #N/A алдаа гарсан бол хүснэгтийн мужийг засаагүй болно. Хүснэгтийн мужийг түгжихийн тулд ($) ашиглан томьёог бөглөхдөө ижил мужийг ашиглана. Эсвэл ашиглах
	Яг тохирохыг олж чадсангүй (хэрэв байгаа бол цаг алдалгүй үзэхяг сонгосон утгыг хайх (0)
	IN цаг алдалгүй үзэххамгийн ойрын утгыг хайх (1) хийгдэх ба хайлт хийж буй хүснэгтийг эрэмбэлэхгүй.	Хүснэгтийн эхний баганыг нэрсийн өсөх дарааллаар эрэмбэл.
		TYPE эсвэл SPACE функцийг ашиглана уу.
	Баганын дугаарын утга нь хүснэгтийн баганын тооноос хэтэрсэн байна	Буцах утгыг агуулсан баганын дугаарыг шалгана уу.
	Томъёонд ишлэл байхгүй байна	Хэрэв та ашиглаж байгаа бол хүссэн утгаэсийн холбоос биш, харин текст байвал хашилтанд оруулах ёстой. Жишээлбэл: VLOOKUP("Бүтээгдэхүүн 1"; Үнэ!$A$2:$B$8,2,0)

Одоо ч гэсэн тэгнэ гэж найдаж байна хуурамч хүмүүсийн хувьд Excel дээрх VLOOKUP функцтодорхой болно.

Батьянов ДенисЗочин зохиолчийн хувьд тэрээр энэ нийтлэлд Excel-ийн нэг хүснэгтээс өгөгдлийг олж, нөгөө хүснэгтэд хэрхэн задлах талаар ярихаас гадна босоо харах функцийн бүх нууцыг дэлгэсэн.

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

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



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

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



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

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

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

VLOOKUP ашиглах жишээ

Тодорхой жишээн дээр VLOOKUP функц хэрхэн ажилладагийг харцгаая.

Бид хоёр ширээтэй. Тэдний эхнийх нь хүнсний бүтээгдэхүүний нэрсийг байрлуулсан худалдан авах хүснэгт юм. Нэрийн дараа дараагийн баганад худалдан авах шаардлагатай бүтээгдэхүүний тоо хэмжээний утгыг бичнэ. Дараа нь үнэ ирдэг. Мөн сүүлчийн баганад - тодорхой бүтээгдэхүүнийг худалдан авах нийт зардал, тоо хэмжээг үнээр үржүүлэхийн тулд нүдэнд аль хэдийн оруулсан томъёог ашиглан тооцоолно. Гэхдээ бид зөвхөн үнийн жагсаалт болох зэргэлдээх хүснэгтээс VLOOKUP функцийг ашиглан үнийг өсгөх хэрэгтэй болно.








Эндээс харахад төмсний үнийг үнийн жагсаалтаас хүснэгтэд нэмсэн байна. Бусад бүтээгдэхүүний нэрээр ийм төвөгтэй процедурыг давахгүйн тулд бид дүүргэсэн нүдний баруун доод буланд зогсоход загалмай гарч ирнэ. Бид энэ загалмайг хүснэгтийн хамгийн доод хэсэгт зурдаг.



Тиймээс бид VLOOKUP функцийг ашиглан шаардлагатай бүх өгөгдлийг нэг хүснэгтээс нөгөө хүснэгт рүү татсан.



Таны харж байгаагаар VLOOKUP функц нь эхлээд харахад тийм ч төвөгтэй биш юм. Түүний хэрэглээг ойлгох нь тийм ч хэцүү биш боловч энэ хэрэгслийг эзэмших нь хүснэгттэй ажиллахад маш их цаг хэмнэх болно.