Excel-ийн индекс болон хайлтын функцууд нь vpr-ийн хамгийн сайн хувилбар юм. Өгөгдлийн жагсаалтаас утгыг олох Excel хүснэгтээс зөв тоог хэрхэн олох вэ

Өөр өөр зэргэлдээх бус мужид хуваагдсан хэсгүүдэд хуваагдсан хэд хэдэн хүснэгтүүд эсвэл бүр бие даасан хуудаснуудаар дамжуулан хайхаас илүү том боловч бүхэл бүтэн хүснэгт эсвэл зэргэлдээх нүднүүдээс хайх нь илүү хялбар байдаг. Хэд хэдэн ширээн дээр нэгэн зэрэг автомат хайлт хийсэн ч томоохон саад бэрхшээл гарч болзошгүй. Бүх өгөгдлийг нэг хүснэгтэд оруулах нь хэцүү, заримдаа бараг боломжгүй юм. Тодорхой жишээг ашиглан бид Excel-ийн олон хүснэгтээс нэгэн зэрэг хайх зөв шийдлийг харуулах болно.

Олон мужид нэгэн зэрэг хайлт хийх

Харааны жишээний хувьд нэг хуудасны зэргэлдээ бус мужид байрлах гурван энгийн тусдаа хүснэгтийг үүсгэцгээе.

20 ширхэг бүтээгдэхүүн үйлдвэрлэхэд шаардагдах хэмжээг хайж олох хэрэгтэй. Харамсалтай нь энэ өгөгдөл өөр өөр багана, мөрөнд байна. Тиймээс, юуны өмнө эдгээр бүтээгдэхүүнийг үйлдвэрлэхэд хэр хугацаа шаардагдахыг шалгах хэрэгтэй (эхний хүснэгт).

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

Excel-ийн дундаж хэрэглэгч VLOOKUP гэх мэт функцууд дээр суурилсан томъёог ашиглан шийдлийг хайдаг. Мөн хайлтыг 3 үе шаттайгаар (хүснэгт тус бүрээр тус тусад нь) гүйцэтгэнэ. Тусгай томъёог ашиглан зөвхөн 1 үе шатанд хайлт хийснээр та эцсийн үр дүнг шууд авах боломжтой болж байна. Үүний тулд:

1. E6 нүдэнд хайлтын асуулгын нөхцөл болох 20 гэсэн утгыг оруулна.
2. E7 нүдэнд дараах томъёог оруулна уу.

20 ширхэгийн үйлдвэрлэлийн өртөг. тодорхой бүтээгдэхүүн.

Томъёо олон хүснэгтэд VLOOKUP-тай хэрхэн ажилладаг вэ:

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

Гурав дахь функцийг хайсны үр дүнд бид хоёр дахь функцийн эхний аргумент болох 125 утгыг авна. Бүх параметрүүдийг хүлээн авсны дараа хоёрдахь функц нь үйлдвэрлэлд шаардагдах ажилчдын тоог хоёрдугаар хүснэгтээс харна. Үүний үр дүнд 5 гэсэн утгыг буцаадаг бөгөөд энэ нь үндсэн функцэд ашиглагдах болно. Хүлээн авсан бүх өгөгдөл дээр үндэслэн томъёо нь тооцооллын эцсийн үр дүнг буцаана. Тодруулбал, 20 ширхэг тодорхой бүтээгдэхүүн үйлдвэрлэхэд 1750 доллар шаардлагатай.

Энэ зарчмын дагуу та хэд хэдэн хуудаснаас VLOOKUP функцийн томъёог ашиглаж болно.

Microsoft Excel нь ихэвчлэн их хэмжээний мэдээлэлтэй ажилладаг. Энэ нь олон мянган мөр, багана, байрлал бүхий асар том хүснэгтүүдийг үүсгэдэг. Ийм массиваас тодорхой өгөгдлийг олоход хэцүү байж болно. Мөн заримдаа энэ нь огт боломжгүй юм. Энэ ажлыг хялбаршуулж болно. Excel програмаас зөв үгийг хэрхэн олох талаар суралц. Энэ нь танд баримт бичигт шилжихэд хялбар болгоно. Мөн та хайж буй мэдээлэл рүүгээ хурдан орох боломжтой.

Таны хайж буй бүх нүдний хаягийг харуулахын тулд дараах зүйлийг хийнэ үү.

1. Хэрэв танд Office 2010 байгаа бол Цэс - Засварлах - Хайх хэсэгт очно уу.
2. Оролтын талбар бүхий цонх нээгдэнэ. Үүн дээр хайлтын хэллэг бичнэ үү.
3. Excel 2007 дээр энэ товчлуур нь Засварлах самбарын Нүүр цэсэнд байдаг. Тэр баруун талд байна.
4. Бүх хувилбарт ижил төстэй үр дүнд Ctrl + F товчийг дарж хүрч болно.
5. Талбарт олохыг хүссэн үг, хэллэг, тоогоо бичнэ үү.
6. Баримт бичгийг бүхэлд нь хайхын тулд "Бүгдийг олох" дээр дарна уу. Хэрэв та "Дараах" дээр дарвал програм Excel нүдний курсорын доор байрлах нүднүүдийг нэг нэгээр нь сонгоно.
7. Процесс дуусахыг хүлээнэ үү. Баримт бичгийн хэмжээ том байх тусам систем удаан хайлт хийх болно.
8. Үр дүн бүхий жагсаалт гарч ирнэ: өгөгдсөн хэллэгт тохирсон нүднүүдийн нэр, хаяг, тэдгээрт бичигдсэн текст.
9. Мөр бүр дээр дарахад харгалзах нүд тодрох болно.
10. Тохиромжтой болгохын тулд та цонхыг "сунгаж" болно. Тиймээс энэ нь илүү олон мөрийг харуулах болно.
11. Өгөгдлийг эрэмбэлэхийн тулд хайлтын үр дүнгийн дээрх баганын нэр дээр дарна уу. Хэрэв та "Хуудас" дээр дарвал хуудасны нэрээс хамааран мөрүүд цагаан толгойн дарааллаар жагсах бөгөөд хэрэв та "Утга" -ыг сонговол утгаараа байрлана.
12. Эдгээр баганууд нь мөн "сунгасан" байна.

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

1. Шошгоны зөвхөн хэсгийг оруулна уу. Та дор хаяж нэг үсэгтэй байж болно - түүний байгаа бүх газрыг сонгох болно.
2. * (од) тэмдэг ашиглана уу? (асуултын тэмдэг). Тэд алга болсон тэмдэгтүүдийг орлуулдаг.
3. Асуулт нь нэг дутуу зүйл байгааг харуулж байна. Хэрэв та жишээ нь "P???" гэж бичвэл "P"-ээр эхэлсэн дөрвөн тэмдэгтээс бүрдсэн нүднүүд гарч ирнэ: "Анжис", "Талбай", "Хос" гэх мэт.
4. Од (*) нь дурын тооны тэмдэгтийг орлоно. "Rast" язгуурыг агуулсан бүх утгыг олохын тулд "*rast*" түлхүүрийг хайж эхэлнэ үү.

Та мөн тохиргоо руу орж болно:

1. Хайх цонхноос Сонголтууд дээр дарна уу.
2. "Үзэх" болон "Хайлтын хэсэг" хэсэгт тохирохыг хаанаас, ямар шалгуураар хайхыг хүсч байгаагаа зааж өгнө үү. Та томьёо, тэмдэглэл эсвэл утгыг сонгож болно.
3. Систем нь жижиг, том үсгийг ялгахын тулд "үсгийн мэдрэмжтэй" нүдийг шалгана уу.
4. Хэрэв та "Бүтэн нүд" нүдийг чагталвал үр дүн нь зөвхөн заасан хайлтын хэллэг агуулсан нүднүүдийг харуулах бөгөөд өөр зүйл байхгүй.

Нүдний форматын сонголтууд

Тодорхой дүүргэлт эсвэл загвар бүхий утгыг олохын тулд тохиргоог ашиглана уу. Excel дээр үг нь бусад текстээс ялгаатай байвал хэрхэн олохыг эндээс үзнэ үү.

1. Хайлтын талбарт "Сонголтууд" дээр товшоод "Формат" товчийг дарна уу. Хэд хэдэн цонхтой цэс нээгдэнэ.
2. Та тодорхой фонт, хүрээний төрөл, дэвсгэр өнгө, өгөгдлийн форматыг зааж өгч болно. Систем нь өгөгдсөн шалгуурт тохирсон газруудыг хайж олох болно.
3. Одоогийн нүднээс мэдээлэл авахын тулд (энэ үед сонгосон) "Энэ нүдний форматыг ашиглах" дээр дарна уу. Дараа нь програм нь ижил хэмжээ, төрлийн тэмдэгт, ижил өнгөтэй, ижил хүрээ гэх мэт бүх утгыг олох болно.

Олон үг хайх

Excel дээр та нүдийг бүхэл бүтэн хэллэгээр олох боломжтой. Гэхдээ хэрэв та "Цэнхэр бөмбөг" товчлуурыг оруулсан бол систем яг энэ хүсэлт дээр ажиллах болно. "Цэнхэр болор бөмбөлөг" эсвэл "Цэнхэр гялтганасан бөмбөг"-тэй утгууд үр дүнд харагдахгүй.

Excel дээр нэгээс олон үг, гэхдээ хэд хэдэн үгийг зэрэг олохын тулд дараах зүйлийг хийнэ үү.

1. Тэдгээрийг хайлтын талбарт бичнэ үү.
2. Тэдний хооронд оддыг байрлуул. Энэ нь "*Текст* *Текст2* *Текст3*" болж хувирна. Энэ нь заасан бичээсүүдийг агуулсан бүх утгыг олох болно. Тэдний хооронд ямар нэгэн дүр байгаа эсэхээс үл хамааран.
3. Ингэснээр та тус тусын үсэгтэй ч гэсэн түлхүүрийг тохируулж болно.

Шүүлтүүр

Шүүлтүүр ашиглан Excel-д хэрхэн хайх талаар эндээс үзнэ үү.

1. Дурын дүүргэсэн нүдийг сонгоно уу.
2. Home - Sort - Filter дээр дарна уу.
3. Нүднүүдийн дээд эгнээнд сумнууд гарч ирнэ. Энэ бол доош унах цэс юм. Нээх.
4. Текст талбарт хүсэлтээ оруулаад OK дарна уу.
5. Багана нь зөвхөн хайлтын хэллэг агуулсан нүднүүдийг харуулах болно.
6. Үр дүнг дахин тохируулахын тулд доош унах жагсаалтаас "Бүгдийг сонгох" гэснийг шалгана уу.
7. Шүүлтүүрийг унтраахын тулд эрэмбэлэх хэсэгт дахин товшино уу.

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

Excel-ээс хэллэг эсвэл тоо олохын тулд суурилуулсан интерфейсийн функцуудыг ашиглана уу. Та нэмэлт хайлтын сонголтыг сонгож, шүүлтүүрийг идэвхжүүлж болно.

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

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

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

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

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

Бид мөн чанарыг ойлгоход шаардлагатай доод хэмжээг энд өгөөд дараа нь ашиглах давуу талыг харуулсан томъёоны жишээг нарийвчлан шинжлэх болно. ИНДЕКСболон ТОГЛОЛТоронд нь VLOOKUP.

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

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

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

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

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

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

ИНДЕКС(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) нь хайлт хийх нүднүүдийн муж юм.
• тохирох_төрөл(тохирох_төрөл) - Энэ аргумент нь функцийг хэлдэг ТОГЛОЛТТа яг тохирох эсвэл ойролцоо тохирохыг олохыг хүсч байна уу:
  • 1 эсвэл тодорхойлоогүй– хүссэн хэмжээнээс бага буюу тэнцүү хамгийн их утгыг олно. Хайж буй массивыг өсөх дарааллаар, өөрөөр хэлбэл хамгийн багаас том хүртэл эрэмбэлэх ёстой.
  • 0 - хүссэнтэй тэнцүү эхний утгыг олно. Хослолын хувьд ИНДЕКС/ТОГЛОЛТТа үргэлж яг тохирохыг хүсдэг тул гурав дахь функцийн аргумент ТОГЛОЛТтэнцүү байх ёстой 0 .
  • -1 - хүссэн утгаас их буюу тэнцүү хамгийн бага утгыг олно. Үзэж буй массивыг буурах дарааллаар, өөрөөр хэлбэл томоос жижиг рүү эрэмбэлэх ёстой.

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

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

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

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

Бүрэн тодорхой болоогүй байна уу? Онцлогуудыг төлөөлөх ИНДЕКСболон ТОГЛОЛТэнэ хэлбэрээр:

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

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

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

ИНДЕКС($D$2:$D$10,МАТЧ("Япон",$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(VLOOKUP)? Лабиринтуудыг олох гэж цаг үрэх ямар ч хэрэг байна уу ТОГЛОЛТболон ИНДЕКС?

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-ийг хэрхэн ашиглах вэ

Энэ томъёо нь 2D хайлттай тэнцэнэ 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 Учир нь “2015” 5-р баганад байгаа.

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

ИНДЕКС($A$1:$E$11, ТААРУУЛАХ($H$2,$B$1:$B$11.0), ТОГЛОХ($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!(#ҮНЭ ЦЭНЭ!). Хэрэв та ийм мессежийг илүү ойлгомжтой зүйлээр солихыг хүсвэл томъёог оруулж болно ИНДЕКСболон ТОГЛОЛТфункц болгон хувиргана ЕСЛИОШИБКА.

Функцийн синтакс ЕСЛИОШИБКАмаш энгийн:

ХЭРЭГСЭЛ(утга, алдааны_утга)
IFERROR(утга, алдаа_хэрэв_утга)

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

Жишээлбэл, та функцийг оруулж болно ЕСЛИОШИБКАүүн шиг:

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

Одоо, хэрэв хэн нэгэн алдаатай утгыг оруулбал томъёо нь дараах үр дүнг өгнө.

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

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

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

Та тэдний дугаарыг ашиглан ажилтны утасны өргөтгөлийг олж, борлуулалтын дүнгийн комиссын хэмжээг зөв тооцоолох хэрэгтэй гэж бодъё. Жагсаалтаас тодорхой өгөгдлийг хурдан бөгөөд үр дүнтэй олохын тулд та өгөгдлийг хайж, өгөгдлийг зөв ашиглаж байгаа эсэхийг автоматаар шалгана. Өгөгдлийг харсны дараа та тооцоолол хийж, буцах утгыг зааж үр дүнг харуулах боломжтой. Өгөгдлийн жагсаалтаас утгыг хайх, үр дүнг харуулах хэд хэдэн арга байдаг.

Энэ нийтлэлд

Жагсаалт дахь утгуудыг яг тааруулах замаар босоо байдлаар олох

Та энэ даалгаврыг биелүүлэхийн тулд VLOOKUP функц эсвэл INDEX болон MATCH функцүүдийн хослолыг ашиглаж болно.

VLOOKUP функцийг ашиглах жишээ

VLOOKUP функц.

INDEXES болон MATCHES-ийн жишээ

Энэ нь юу гэсэн үг вэ:

=INDEX(C2:C10-аас MATCH-д тохирох утгыг буцаах шаардлагатай(B2:B10 массив дахь "Байцаа" эхний утга))

Томъёо нь C2:C10 нүдэнд харгалзах эхний утгыг хайна байцаа(B7-д) ба C7-д утгыг буцаана ( 100 ) нь харгалзах эхний утга юм байцаа.

Дэлгэрэнгүй мэдээллийг Index функц болон MATCH функцээс үзнэ үү.

Жагсаалтаас утгыг босоо байдлаар ойролцоо байдлаар олох

Үүнийг хийхийн тулд VLOOKUP функцийг ашиглана уу.

Чухал:Эхний эгнээнд байгаа утгуудыг өсөх дарааллаар эрэмбэлсэн эсэхийг шалгаарай.

Дээрх жишээн дээр VLOOKUP функц нь A2:B7 мужид 6 хоцролттой оюутны нэрийг хайж байна. Хүснэгтэнд оруулах зүйл байхгүй 6 хоцорсон тул VLOOKUP 6-аас доош дараагийн хамгийн өндөр тохирохыг хайж, нэртэй холбоотой 5-ын утгыг олно. Дэйв, улмаар буцаж ирдэг Дэйв.

Дэлгэрэнгүй мэдээллийг VLOOKUP функц хэсгээс үзнэ үү.

Тодорхойгүй хэмжээтэй жагсаалтаас босоо утгуудыг яг таарч байна

Энэ даалгаврыг биелүүлэхийн тулд OFFSET болон MATCH функцуудыг ашиглана уу.

Жич:Хэрэв өгөгдөл нь таны өдөр бүр шинэчлэгддэг гадаад өгөгдлийн мужид байгаа бол энэ аргыг хэрэглэнэ. Та В баганад Үнэ байгааг мэдэж байгаа боловч сервер хэдэн мөр өгөгдөл буцааж байгааг мэдэхгүй бөгөөд эхний багана нь цагаан толгойн үсгийн дарааллаар эрэмблэгдээгүй байна.

C1нь мужын зүүн дээд нүд (эхлэх нүд гэж нэрлэдэг) юм.

Тохирох("жүрж"; C2: C7; 0) C2:C7 мужид улбар шар өнгийг хайж байна. Та мужид эхлэх нүдийг оруулах ёсгүй.

1 - буцаах утгыг буцаах эхлэлийн нүдний баруун талд байгаа баганын тоо. Бидний жишээн дээр буцах утга нь D баганад байна. Борлуулалт.

Жагсаалтаас утгыг яг таарч хэвтээ байдлаар олох

Энэ ажлыг гүйцэтгэхийн тулд HLOOKUP функцийг ашиглана. Доорх жишээг үзүүлэв.

HLOOKUP функц нь баганын хайлтыг гүйцэтгэдэг Борлуулалтзаасан муж дахь 5-р мөрийн утгыг буцаана.

Дэлгэрэнгүй мэдээллийг HLOOKUP функцууд хэсгээс үзнэ үү.

Хайлтын шидтэнг ашиглан хайлтын томъёо үүсгэх (зөвхөн Excel 2007)

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

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

Excel оффисын программын гол зорилго нь тооцооллын хэрэгжилт юм. Энэхүү програмын баримт бичиг (Ном) нь тоо, текст эсвэл томъёогоор дүүргэсэн урт хүснэгт бүхий олон хуудас агуулж болно. Автомат хурдан хайлт нь тэдгээрээс шаардлагатай нүдийг олох боломжийг олгодог.

Энгийн хайлт

Excel хүснэгтээс утгыг хайхын тулд Нүүр хуудасны "Олох ба солих" хэрэгслийн унадаг жагсаалтыг нээгээд Хайх зүйл дээр дарна уу. Ctrl + F товчлуурын хослолыг ашиглан ижил нөлөөг авч болно.

Хамгийн энгийн тохиолдолд гарч ирэх Find and Replace цонхонд хайж буй утгаа оруулаад Find All дээр дарна уу.

Таны харж байгаагаар хайлтын үр дүн харилцах цонхны доод талд гарч ирсэн. Олсон утгыг хүснэгтэд улаанаар зурсан болно. Хэрэв та "Бүгдийг нь олох" гэсний оронд "Дараа нь олох" дээр дарвал энэ утгатай эхний нүдийг хайх бөгөөд дахин дарахад хоёр дахь нүдийг хайх болно.

Текстийг ижил аргаар хайдаг. Энэ тохиолдолд хайлтын талбарт хайлтын текстийг бичнэ.

Хэрэв өгөгдөл эсвэл текстийг Excel хүснэгтэд бүхэлд нь хайгаагүй бол эхлээд хайлтын хэсгийг сонгох шаардлагатай.

Нарийвчилсан хайлт

Та 3000-аас 3999 хүртэлх бүх утгыг олохыг хүсч байна гэж бодъё. Энэ тохиолдолд хайлтын мөрөнд 3??? гэж бичнэ. Орлогч тэмдэгт "?" бусдыг орлоно.

Гүйцэтгэсэн хайлтын үр дүнд дүн шинжилгээ хийхдээ зөв 9 үр дүнгийн зэрэгцээ хөтөлбөр нь улаанаар тэмдэглэсэн гэнэтийн үр дүнг гаргасан болохыг тэмдэглэж болно. Эдгээр нь нүд эсвэл томъёонд 3-ын тоо байгаатай холбоотой юм.

Буруу үр дүнг үл тоомсорлож, олж авсан ихэнх үр дүндээ сэтгэл хангалуун байж болно. Гэхдээ Excel 2010 дээрх хайлтын функц нь илүү нарийвчлалтай ажиллах боломжтой. Энэ нь харилцах цонхны Сонголт хэрэгсэл юм.

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

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

Эхний мөрөнд үлдсэн цорын ганц буруу үр дүнг арилгахын тулд хайлтын дэвшилтэт цонхноос "Бүтэн нүд" гэсэн зүйлийг сонгоно уу. Үүний дараа хайлтын үр дүн 100% үнэн зөв болно.

Ийм үр дүнд "Бүтэн нүд" гэсэн зүйлийг нэн даруй сонгох боломжтой ("Хайлтын талбар" дахь "Томъёо" гэсэн утгыг орхисон ч болно).

Одоо "Хайлт" зүйл рүү шилжье.

Хэрэв та "Хуудас дээр" гэсэн үндсэн утгыг сонговол "Номонд" гэсэн утгыг сонговол таны хайж буй нүднүүдийн хуудсан дээр байх шаардлагагүй болно. Дэлгэцийн зургаас харахад хэрэглэгч хоосон хуудсан дээр байхдаа хайлтыг эхлүүлсэн 2.

Нарийвчилсан хайлтын цонхны дараагийн зүйл нь "Browse" гэсэн хоёр утгатай. Анхдагчаар "мөрөөр" гэж тохируулсан бөгөөд энэ нь нүднүүдийг мөрөөр скан хийх дарааллыг хэлнэ. Өөр утгыг "баганаар" сонгох нь зөвхөн хайлтын чиглэл болон үр дүнгийн дарааллыг өөрчлөх болно.

Microsoft Excel-ийн баримт бичгүүдээс хайхдаа та "*" гэсэн өөр орлуулагч тэмдэг ашиглаж болно. Хэрэв авч үзвэл "?" ямар ч тэмдэгтийг илэрхийлсэн бол "*" нь нэг биш, хэдэн ч тэмдэгтийг орлоно. Луизианагийн хайлтын дэлгэцийн агшинг доор харуулав.

Заримдаа хайлт нь том жижиг үсгийг харгалзах шаардлагатай байдаг. Луизиана гэдэг үгийг томоор бичсэн бол хайлтын үр дүн өөрчлөгдөхгүй. Гэхдээ хэрэв та хайлтын дэвшилтэт цонхон дээрх "Үсгийн жижиг"-ийг сонговол хайлт амжилтгүй болно. Хөтөлбөр нь Луизиана, Луизиана гэсэн үгсийг өөр өөр гэж үзэх бөгөөд мэдээжийн хэрэг тэдний эхнийх нь олдохгүй.

Сорт хайх

Тоглолт хайж байна

Заримдаа хүснэгтэд давхардсан утгыг илрүүлэх шаардлагатай байдаг. Тохирох зүйлсийг хайхын тулд эхлээд хайлтын хүрээг тодруулах хэрэгтэй. Дараа нь ижил Нүүр хуудасны "Загвар" бүлгийн "Нөхцөлт форматлах" хэрэгслийг нээнэ үү. Дараа нь "Нүд сонгох дүрэм" ба "Давхардсан утгууд" гэсэн зүйлийг дараалан сонгоно уу.

Үр дүнг доорх дэлгэцийн агшинд харуулав.

Шаардлагатай бол хэрэглэгч тохирох нүднүүдийн харагдах өнгийг өөрчлөх боломжтой.

Шүүлтүүр

Өөр нэг төрлийн хайлт бол шүүлтүүр юм. Хэрэглэгч В баганад 3000-аас 4000 хүртэлх тоон утгыг олохыг хүсч байна гэж бодъё.

Таны харж байгаагаар зөвхөн оруулсан нөхцөлийг хангасан мөрүүд гарч ирнэ. Үлдсэнийг нь түр нуусан. Анхны төлөв рүү буцахын тулд 2-р алхамыг давтана уу.

Жишээ болгон Excel 2010 програмыг ашиглан хайлтын янз бүрийн хувилбаруудыг авч үзсэн.Бусад хувилбарын Excel дээр хэрхэн хайх вэ? 2003 оны хувилбарт шүүлтүүрт шилжихэд ялгаа бий. "Өгөгдөл" цэснээс "Шүүлтүүр", "Автомат шүүлтүүр", "Нөхцөл", "Хэрэглэгчийн автомат шүүлтүүр" гэсэн командуудыг дарааллаар сонгоно.

Видео: Excel хүснэгтээс хайх