Excel-ийн VLOOKUP функцийг COLUMN функцтэй хослуулан бид өгөгдлийн сан , өгөгдлийн хүснэгтийн нэг эгнээнээс олон утгыг буцаах боломжийг олгодог хайлтын томъёог үүсгэж болно.
Дээрх зурагт үзүүлсэн жишээнд хайлтын томъёо нь янз бүрийн тоног төхөөрөмжид хамаарах үнэ, дугаар, нийлүүлэгч зэрэг бүх утгыг буцаадаг.
01-ийн 10
VLOOKUP ашиглан Excel-ийн олон утгыг буцаана
Доор жагсаасан алхмуудыг дагаж дээрх зураг дээр харагдаж буй томьёог нэг өгөгдлийн бичлэгээс олон утгыг буцаана.
Хайлтын томъёо нь COLUMN функцийг VLOOKUP дотор байрлуулсан байхыг шаарддаг.
Функцийг үүрэх нь хоёрдох функцийг эхний функцэд зориулж нэг аргумент гэж оруулах явдал юм.
Энэ зааварт COLUMN функцийг VLOOKUP-д зориулсан баганын индекс дугаар аргументээр оруулна.
Заавар дахь хамгийн сүүлийн алхам бол хайж буй томьёог сонгож авсан хэсгүүдэд нэмэлт утгуудыг авахын тулд нэмэлт баганад хуулбарлан оруулах явдал юм.
Tutorial агуулга
- Tutorial Data-г оруулна
- Өгөгдлийн хүснэгтэнд нэрлэсэн хязгаарыг үүсгэх
- VLOOKUP функцийг эхлүүлэх
- Үнэмлэхүй эсийн лавлагаа ашиглан Үзлэгийн Утганы утгыг оруулна
- Table Array Аргументыг оруулав
- Nested COLUMN функцыг оруулна
- VLOOKUP функцийг гүйцээх
- Хайлтын бариултай Харьцах лавлах формыг хуулж ав
- Lookup хэлбэрээр мэдээллийг сэргээх
10 дахь нь
Tutorial Data-ийг оруулна уу
Заавар дахь эхний алхам нь өгөгдлийг Excel ажлын хуудас руу оруулах явдал юм.
Дээрх зурагт харуулсан өгөгдлийг доорх нүд рүү оруулахын тулд зааварчилгааны алхмуудыг дагана уу.
- D1-ээс G1 хүртэлх өгөгдлийн хамгийн дээд талбарыг оруулна уу
- Д4-ээс G10 хүртэлх хоёр дахь хэсгийг оруулна уу
Энэхүү гарын авлагаас үүссэн хайлтын шалгуур болон хайж олох томъёо нь ажлын хуудасны 2-р мөрөнд орно.
Зааварт зураг дээр харагдах форматыг оруулаагүй боловч хайлтын томьёо хэрхэн ажилладаг талаар тайлбарлахгүй.
Дээр үзүүлсэнтэй төстэй хэлбэршүүлэлт хийх талаархи мэдээллийг энэ Basic Excel Formatting Tutorial дээрээс авах боломжтой.
Tutorial алхамууд
- Дээрх зурагт харагдаж буй өгөгдлийг D1-ээс G10 хүртэлх зургаар оруулна уу
03 - 10
Өгөгдлийн хүснэгтэнд нэрлэсэн хязгаарыг үүсгэх
Нэрлэсэн муж нь томьёонд янз бүрийн өгөгдлөөр хандах хялбар арга юм. Өгөгдлийн хувьд өгөгдлийн нүдэн дээр бичихийн оронд та зүгээр л тухайн мужийн нэрийг бичиж болно.
Нэрлэсэн мужийг ашиглах хоѐрдах давуу тал нь томъёог ажлын хуудсанд байгаа бусад нүдэнд хуулагдсан байсан ч энэ хүрээний хувьд лавлах нүд хэзээ ч өөрчлөгддөггүй.
Түгээмэл нэрс нь томъёог хуулбарлах үед алдаанаас сэргийлэхийн тулд үнэмлэхүй эсийн лавлагаа ашиглах өөр сонголт юм.
Тэмдэглэл: Хэлний нэр нь өгөгдөлд зориулсан гарчгууд буюу талбарын нэрийг агуулдаггүй (мөр 4) гэхдээ зөвхөн өгөгдөл өөрөө юм.
Tutorial алхамууд
- Ажлын хүснэгтэнд D5-ээс G10 нүдийг сонгохын тулд тэдгээрийг сонгоно уу
- А баганын дээр байрлах Нэрийн Шигтгээ дээр дарна уу
- Нэрийн хүснэгтэнд "Хүснэгт" гэж бичнэ үү
- Гар дээр ENTER товчийг дарна уу
- D5 хүртэл G10 нүднүүд нь "Хүснэгт" мужийн нэртэй байна. Бид зааварчилгааны дараа VLOOKUP хүснэгт массивын нэрийг ашиглах болно
04 - 10
VLOOKUP харилцах цонхыг нээ
Хэдийгээр бидний хайлтын томъёог ердөө л ажлын хуудасны нүдэнд шууд оруулан бичиж чаддаг ч олон хүн синтаксаа шулуун байлгахад төвөгтэй байдаг, ялангуяа энэ гарын авлагад байгаа бидний хэрэглэж байгаа томьёоны томъёоны хувьд томъёололыг ашиглахад хэцүү байдаг.
Өөрөөр хэлбэл, энэ тохиолдолд VLOOKUP харилцах цонхыг ашиглах явдал юм. Бараг бүх Excel-ийн функцүүд нь тусдаа мөрөн дээр функцийн аргумент бүрийг оруулах боломжийг олгодог харилцах цонхтой.
Tutorial алхамууд
- Ажлын хүснэгтийн E2 нүдэн дээр дарна уу. Хоёр хэмжээст хайлтын томъёоны үр дүнг харуулна
- Туузны томьёо товчлуур дээр дарна уу
- Функц дээрээс Lookup & Reference сонголт дээр дарж функцийг унагаах жагсаалтыг нээнэ
- Функцийн харилцах цонхыг нээхийн тулд жагсаалтад VLOOKUP дээр дарна уу
05-р сарын 10
Үнэмлэхүй эсийн лавлагаа ашиглан Үзлэгийн Утганы утгыг оруулна
Ихэнхдээ lookup утга нь өгөгдлийн хүснэгтийн эхний баганад өгөгдлийн талбартай таарч байдаг.
Бидний жишээн дээр, lookup утга нь бид мэдээлэл олж авахыг хүсч буй тоног төхөөрөмжийн хэсгийн нэрийг хэлнэ.
Нүдний харагдахуйц өгөгдлүүд нь:
- текстийн мэдээлэл
- логик утга (зөвхөн үнэн эсвэл буруу байна)
- тоо
- ажлын хүснэгтийн утгад обьектын лавлагаа
Энэ жишээнд бид нүдний нэрийг хаана байрлах D2 нүдээр оруулна.
Үнэмлэхүй Гар лавлагаа
Tutorial дээр дараагийн шатанд E2 нүдэнд хайлтын томъёог F2 ба G2 нүд рүү хуулна.
Ерөнхийдөө Excel-ийн томъёог хуулбарлах үед тэдгээрийн шинэ байршлыг тусгасан эсийн лавлагаа өөрчлөгддөг.
Хэрэв энэ тохиолдвол, D2 - F2, G2 нүдэн дэх алдааг үүсгэх томъёог хуулбарласнаар хайх утгын нүдний лавлагаа өөрчлөгдөх болно.
Алдаа гарахаас сэргийлэхийн тулд бид D2 лавлагааны үнэмлэхүй эсийн лавлагаа руу шилжих болно.
Томъёо хуулбарлах үед үнэмлэхүй эсийн лавлахууд өөрчлөгддөггүй.
Үнэн эсийн лавлахууд гар дээр F4 түлхүүрийг дарж үүсгэгддэг. Ингэснээр $ D $ 2 гэх мэт нүдний лавлагааны орчим долларын тэмдгийг нэмдэг
Tutorial алхамууд
- Харилцах цонхонд lookup_value мөрийг дарна уу
- D2 нүд дээр дарж энэ нүдний лавлагаа lookup_value мөр дээр нэмнэ үү. Энэ бол бид мэдээлэл хайж буй хэсгийн нэрийг бичнэ үү
- Оруулах цэгийг шилжүүлэлгүйгээр гар дээр F4 товчийг дарж D2-г үнэмлэхүй нүдний лавлах $ D $ 2 руу шилжүүлнэ
- Tutorial дахь дараагийн алхамд VLOOKUP функцын харилцах цонх нээгдэнэ
06-ийн 10
Table Array Аргументыг оруулав
Хүснэгтийн массив нь хайж буй томъёоллын хүснэгтэд бидний хүссэн мэдээллийг олохын тулд хайж буй өгөгдлийн хүснэгт юм.
Хүснэгтийн массив нь хамгийн багадаа хоёр багана байх ёстой.
- Эхний баганад хайлтын утгын аргументийг агуулна (зааварчилгааны өмнөх алхам)
- хоёрдугаарт, нэмэлт багануудыг хайж олох томъёогоор хайж олох болно.
Хүснэгтийн массивын өгөгдөл нь өгөгдлийн хүснэгтийн нүдний лавлахыг эсвэл мужийн нэрээр агуулсан мужийг оруулах ёстой.
Энэ жишээний хувьд бид хичээл дээрх 3-р хэсэгт заасан мушгийн нэрийг ашиглах болно.
Tutorial алхамууд
- Харилцах цонхон дээр table_array мөрийг дарна уу
- Энэ аргументын мужийн нэрийг оруулахын тулд "Хүснэгт" гэж бичнэ үү
- Tutorial дахь дараагийн алхамд VLOOKUP функцын харилцах цонх нээгдэнэ
10-р хэсэг
COLUMN функцийг үүрэх
Ихэнхдээ VLOOKUP нь өгөгдлийн хүснэгтийн нэг баганаас өгөгдлийг буцаадаг бөгөөд энэ баганыг баганын индексийн дугаар аргументаар тохируулдаг.
Гэхдээ энэ жишээнд бид өгөгдлөө буцаах гурван багана бий. Тиймээс бид өөрсдийн хайлтын томъёог засварлахгүйгээр баганын индекс дугаарыг хялбархан өөрчлөх арга зам хэрэгтэй болно.
Энд COLUMN функц орж ирдэг. Баганын индекс дугаартай аргумент болгон оруулснаар хайлтын хэлбэр томъёог D2 нүднээс E2 болон F2 нүднээс хуулж дараа нь зааварчилгаанд оруулна.
Үүрлэх функц
Тиймээс COLUMN функц нь VLOOKUP-ийн баганын индекс дугаартай аргумент болж ажилладаг .
Энэ нь харилцах цонхны Col_index_num мөр дэх VLOOKUP дотор COLUMN функцийг үүрэн гүйцэтгэснээр гүйцэтгэгдэнэ.
COLUMN функцыг гараар оруулах
Үүргэх үүргээ гүйцэтгэхэд Excel нь биднийг хоёрдогч функцийн харилцах цонхыг нээхийг зөвшөөрдөггүй.
Тиймээс COLUMN функцийг Col_index_num мөрөнд гараар оруулах ёстой.
COLUMN функц нь эсийн лавлагаа болох Лавлагааны аргументыг зөвхөн нэг аргументтай байна.
COLUMN Function-ийн Лавлагаа Аргументийг сонгох
COLUMN функцын ажил нь Лавлах аргументын өгсөн баганын тоог буцаах явдал юм.
Өөрөөр хэлбэл, баганын үсэг нь А багана бүхий тоогоор хөрвүүлж, эхний багана, В багана, гэх мэт.
Өгөгдөл эхний өгөгдлийн талбар нь өгөгдлийн хүснэгтийн хоёр баганын баганатай байгаа өгөгдлийн хүснэгтийн хоёр дахь багана юм. Үүнд: 2 баганыг авахын тулд Лавлагаа аргумент болгон В баганад байгаа эсийн лавлахыг сонгож болно. Col_index_num аргумент.
Tutorial алхамууд
- VLOOKUP функцын харилцах цонхон дээр Col_index_num мөрийг дар
- Функцийн нэрний баганыг дараагийн нээлттэй дугуй хаалтанд бичнэ үү " ( "
- Ажлын хүснэгтийн нүдэн дээр B1 нүдэн дээр дарж тухайн нүдийг лавлагаа аргумент болгон оруулна
- Хаалттай хаалтыг бичнэ үү " ) " COLUMN функцийг дуусгах
- Tutorial дахь дараагийн алхамд VLOOKUP функцын харилцах цонх нээгдэнэ
08-ийн 10
VLOOKUP Range Lookup заалтыг оруулав
VLOOKUP-ийн Range_lookup аргумент нь логик утга (зөвхөн TRUE эсвэл FALSE) бөгөөд энэ нь Lookup_value-ийн яг тохирсон эсвэл ойролцоо утгыг олох VLOOKUP-ийг хүсэх эсэхийг харуулдаг.
- Хэрэв ҮНЭГҮҮД эсвэл энэ аргыг орхигдуулсан бол VLOOKUP Lookup_-д яг тохирсон хариултыг буцаана, эсвэл яг тохирсон match олдоогүй бол VLOOKUP нь дараагийн хамгийн их утгыг буцаана. Үүнийг хийх томъёогоор Table_array-ийн эхний багана дахь өгөгдлийг эрэмбэлсэн дарааллаар эрэмбэлэх ёстой.
- Хэрвээ FALSE бол VLOOKUP нь зөвхөн Lookup_ хэмжигдэхүүнтэй яг таарч таарна. Хайлтын утгад тохирох Table_array эхний баганад хоёр буюу түүнээс дээш утгууд байгаа бол олсон эхний утга ашиглагдана. Зөв таарахгүй байгаа бол # Н / А алдаа буцаагдах болно.
Энэ зааварт бид тодорхой нэг тоног төхөөрөмжийн талаар тодорхой мэдээллийг хайж байгаа тул Range_lookup нь False- тай тэнцүү байх болно.
Tutorial алхамууд
- Харилцах цонхны Range_lookup мөр дээр дарна уу
- VLOOKUP-ийг хайж байгаа өгөгдөлтэй яг таарах хариултыг буцаахыг хүсч байгааг энэ мөрөн дэхь үгийг буруу бичнэ үү
- Look товчлуурыг дарж хайлтын томъёог бөглөж, ойр дотно харилцах цонхыг бөглөх
- Бид D2 нүд рүү хайлтын шалгуурт хараахан ороогүй байгаа тул E2 нүдэнд # N / A алдаа гарна
- Энэ алдааг заавраас сүүлчийн алхамд хайлтын шалгуурыг нэмэх үед засварлах болно
10-р сарын 10
Хайлтын бариултай Харьцах лавлах формыг хуулж ав
Хайлтын томъёо нь өгөгдлийн хүснэгтийн олон баганаас өгөгдлийг нэг удаа авахад зориулагдсан.
Үүнийг хийхийн тулд хайж олох томъёолол нь бидний хүссэн бүх талбарт байх ёстой.
Энэ зааварт өгөгдлийн хүснэгтийн багана 2, 3, 4-р өгөгдлүүдийг авахыг хүсч байна. Тухайлбал Lookup_ үнэмлэх мэтийн хэсэг, нэр, дугаар, нийлүүлэгчийн нэрийг оруулна.
Ажлын хүснэгтийн өгөгдлийг тогтмол хэв маягаар байрлуулсан тул E2 нүдэнд хайлтын томьёог F2 ба G2 нүдээр хуулж болно.
Томъёо хуулбарласнаар Excel нь томъёоны шинэ байрлалыг харуулахын тулд COLUMN функц (B1) дэх харьцангуй эсийн лавлагаа шинэчлэх болно.
Үүнээс гадна, Excel нь $ D $ 2 үнэмлэхүй эсийн лавлагааг өөрчилж чаддаггүй бөгөөд нэрлэсэн мужийг Хүснэгт хэлбэрээр хуулж авдаг.
Excel-д өгөгдлийг хуулбарлах нэгээс олон арга байдаг боловч хамгийн хялбар арга бол Fill Handle- ийг ашиглах явдал юм.
Tutorial алхамууд
- Э2 нүдэн дээр дарж хайж олох томъёог байрладаг - түүнийг идэвхтэй нүд болгох
- Хулганы заагчийг баруун доод буланд хар дөрвөлжин дээр байрлуул. Заагч нь " + " тэмдэг дээр нэмэх болно - энэ нь дүүргэх бариул
- Зүүн хулганы товчийг дараад дүүргэх бариулыг G2 нүд рүү чирнэ үү
- Хулганы товчлуур болон F3 нүд нь хоёр хэмжээст хайлтын томъёог агуулна
- Хэрэв зөв бол F2 ба G2 нүд нь E2 нүдэнд байгаа N / A алдаатай байх ёстой
10 - с 10
Хариуцлийн шалгуурыг оруулах
Хайлтын томьёог шаардлагатай нүдэнд хуулсны дараа өгөгдлийн хүснэгтийн мэдээллийг авахад ашиглаж болно.
Үүнийг хийхийн тулд Lookup_ хэмжигдэхүүнийг (D2) -д оруулахыг хүсч байгаа зүйлийн нэрийг бичээд ENTER товчийг дарж гар дээр дарна уу.
Үүнийг хийсний дараа хайлтын томъёог агуулсан нүд бүр таны хайж байгаа тоног төхөөрөмжийн талаархи өөр өөр өгөгдлийг агуулсан байх ёстой.
Tutorial алхамууд
- Ажлын хүснэгтэд D2 нүд дээр дарна уу
- Widget- г D2 нүд рүү оруулаад ENTER товчлуурыг дарах хэрэгтэй
- Дараах мэдээллийг E2-ээс G2 нүдэнд харуулах ёстой:
- E2 - $ 14.76 - виджетны үнэ
- F2 - PN-98769 - виджет хэсгийн тоо
- G2 - Widgets Inc. - Widgets-ийн нийлүүлэгчийн нэр
- V02-ийн массив томъёог цаашдын бусад хэсгүүдийн нэрийг D2 нүд рүү бичиж E2-ээс G2 хүртэлх үр дүнг ажиглаж
#REF гэх мэт алдааны мэдэгдэл ! E2, F2, эсвэл G2 нүдэнд гарч ирэхэд VLOOKUP алдааны мэдээллүүдийн жагсаалт энэ асуудалд хаана байгааг тодорхойлоход тусална.