Агуулга
Функцийн талаархи бидний зааварчилгааны хоёрдугаар хэсэгт VPR Excel дээр (VLOOKUP) бид танд бүх хүчийг чиглүүлэхэд туслах хэд хэдэн жишээнд дүн шинжилгээ хийх болно VPR Excel-ийн хамгийн амбицтай ажлуудыг шийдвэрлэх. Жишээ нь та энэ функц хэрхэн ажилладаг талаар анхан шатны мэдлэгтэй болсон гэж таамаглаж байна. Хэрэв тийм биш бол та синтакс болон үндсэн хэрэглээг тайлбарласан энэхүү гарын авлагын эхний хэсгээс эхлэхийг сонирхож магадгүй юм. VPR. За ингээд эхэлцгээе.
Excel дээр олон шалгуураар хай
Чиг үүрэг VPR Excel нь өгөгдлийн сангийн тодорхой утгыг хайхад үнэхээр хүчирхэг хэрэгсэл юм. Гэсэн хэдий ч мэдэгдэхүйц хязгаарлалт байдаг - түүний синтакс нь зөвхөн нэг утгыг хайх боломжийг олгодог. Хэрэв та олон нөхцөлөөр хайхыг хүсвэл яах вэ? Та доорх шийдлийг олох болно.
Жишээ 1: 2 өөр шалгуураар хай
Бидэнд захиалгын жагсаалт байгаа бөгөөд бид олохыг хүсч байна гэж бодъё Барааны тоо хэмжээ (Тоо хэмжээ), хоёр шалгуурт үндэслэн - Үйлчлүүлэгчийн нэр (Хэрэглэгч) и Название продукта (Бүтээгдэхүүн). Худалдан авагч бүр хэд хэдэн төрлийн бараа захиалсан тул асуудал төвөгтэй болж байгааг доорх хүснэгтээс харж болно.
тогтмол үйл ажиллагаа VPR Энэ хувилбарт ажиллахгүй, учир нь өгөгдсөн хайлтын утгатай таарч байгаа анхны утгыг буцаана. Жишээлбэл, хэрэв та барааны хэмжээг мэдэхийг хүсч байвал Амттанхудалдан авагчийн захиалсан Жереми Хилл, дараах томъёог бичнэ үү.
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– энэ томъёо нь үр дүнг буцаана 15бүтээгдэхүүнд тохирсон Алим, учир нь энэ нь тохирох анхны утга юм.
Энгийн шийдэл байдаг - хүссэн бүх шалгуурыг нэгтгэх нэмэлт багана үүсгэх. Бидний жишээнд эдгээр нь багана юм Үйлчлүүлэгчийн нэр (Хэрэглэгч) и Название продукта (Бүтээгдэхүүн). Нэгтгэсэн багана нь хайлтын муж дахь хамгийн зүүн талын багана байх ёстой гэдгийг бүү мартаарай, учир нь энэ нь функцийн зүүн багана юм. VPR үнэ цэнийг хайж байхдаа дээшээ хардаг.
Тиймээс та хүснэгтэд туслах баганыг нэмж, дараах томьёог бүх нүднүүдэд хуулна уу. =B2&C2. Хэрэв та мөрийг уншихад хялбар болгохыг хүсвэл хосолсон утгуудыг зайгаар тусгаарлаж болно. =B2&» «&C2. Үүний дараа та дараах томъёог ашиглаж болно.
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Эс хаана байна B1 аргументийн холбосон утгыг агуулна хайх_ үнэлгээ (хайлтын_утга) ба 4 - Маргаан col_index_num (баганын_тоо), өөрөөр хэлбэл олж авах өгөгдлийг агуулсан баганын дугаар.
Жишээ 2: Хүснэгтийг өөр хуудсан дээрээс харж байгаа хоёр шалгуураар VLOOKUP
Хэрэв та үндсэн хүснэгтийг (Үндсэн хүснэгт) өөр хуудсан дээр эсвэл Excel-ийн өөр ажлын дэвтэрт байрлах хоёр дахь хүснэгтээс (Харах хүснэгт) өгөгдлийг нэмж шинэчлэх шаардлагатай бол оруулсан томъёонд хүссэн утгыг шууд цуглуулж болно. үндсэн хүснэгтэд.
Өмнөх жишээний нэгэн адил танд Хайлтын хүснэгтэд хосолсон утгууд бүхий туслах багана хэрэгтэй болно. Энэ багана нь хайлтын муж дахь хамгийн зүүн талын багана байх ёстой.
Тиймээс томъёо нь VPR иймэрхүү байж болно:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Энд В ба С баганад хэрэглэгчийн нэр, бүтээгдэхүүний нэр, холбоосыг тус тус агуулна Захиалга!$A&$2:$D$2 өөр хуудаснаас хайх хүснэгтийг тодорхойлно.
Томьёог илүү уншигдахуйц болгохын тулд та харах мужид нэр өгч, дараа нь томъёо нь илүү хялбар харагдах болно.
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Томъёо ажиллахын тулд таны харж буй хүснэгтийн хамгийн зүүн талын баганад байгаа утгуудыг хайлтын шалгууртай яг ижил байдлаар нэгтгэх ёстой. Дээрх зураг дээр бид u2bu2band утгуудыг нэгтгэж, тэдгээрийн хооронд зай тавьсан бөгөөд та функцийн эхний аргумент дээр хийх хэрэгтэй (BXNUMX & "" & CXNUMX).
Санаж байна уу! Чиг үүрэг VPR 255 тэмдэгтээр хязгаарлагдах тул 255 тэмдэгтээс илүү урттай утгыг хайж чадахгүй. Үүнийг санаж, хүссэн утгын урт нь энэ хязгаараас хэтрэхгүй эсэхийг шалгаарай.
Туслах багана нэмэх нь хамгийн гоёмсог, үргэлж хүлээн зөвшөөрөгдөхгүй шийдэл биш гэдгийг би хүлээн зөвшөөрч байна. Та туслах баганагүйгээр ижил зүйлийг хийж болно, гэхдээ энэ нь функцүүдийн хослол бүхий илүү төвөгтэй томъёог шаарддаг INDEX (INDEX) ба MATCH (ИЛҮҮ ИЛҮҮ).
Бид VLOOKUP ашиглан 2, 3 гэх мэт утгыг гаргаж авдаг
Та үүнийг хэдийнэ мэдэж байгаа VPR зөвхөн нэг тохирох утгыг буцаах боломжтой, илүү нарийвчлалтай, эхний олсон утгыг. Гэхдээ энэ утга нь үзсэн массив дээр хэд хэдэн удаа давтагдсан бөгөөд та тэдгээрийн 2 эсвэл 3-ыг задлахыг хүсвэл яах вэ? Хэрэв бүх үнэт зүйлс байвал яах вэ? Асуудал төвөгтэй мэт санагдаж байгаа ч шийдэл нь байгаа!
Хүснэгтийн нэг баганад үйлчлүүлэгчдийн нэрс (Хэрэглэгчийн нэр), нөгөө баганад тэдний худалдаж авсан бүтээгдэхүүн (Бүтээгдэхүүн) байна гэж бодъё. Өгөгдсөн хэрэглэгчийн худалдан авсан 2, 3, 4 дэх барааг олохыг хичээцгээе.
Хамгийн хялбар арга бол баганын өмнө туслах баганыг нэмэх явдал юм Хэрэглэгчийн нэр мөн нэр бүрийн давталтын дугаар бүхий хэрэглэгчийн нэрээр бөглөнө үү, жишээлбэл, Жон Доу1, Жон Доу2 гэх мэт. Бид функцийг ашиглан дугаарлах заль мэхийг хийх болно COUNTIF (COUNTIF), хэрэглэгчийн нэр B баганад байгаа учир:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Үүний дараа та ердийн функцийг ашиглаж болно VPRшаардлагатай захиалгыг олох. Жишээлбэл:
- хай 2-р үйлчлүүлэгчийн захиалсан бараа Дэн Браун:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - хай 3-р үйлчлүүлэгчийн захиалсан бараа Дэн Браун:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Үнэн хэрэгтээ та дараах зурагт үзүүлсэн шиг текстийн оронд хайлтын утга болгон нүдний лавлагааг оруулж болно.
Хэрэв та зөвхөн хайж байгаа бол 2-д давталт, та илүү нарийн төвөгтэй томъёог үүсгэх замаар туслах баганагүйгээр хийж болно.
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
Энэ томъёонд:
- $F$2 – худалдан авагчийн нэрийг агуулсан нүд (энэ нь өөрчлөгдөөгүй, холбоос нь үнэмлэхүй гэдгийг анхаарна уу);
- $ B $ - багана Хэрэглэгчийн нэр;
- Хүснэгт4 – Таны ширээ (энэ газар бас энгийн газар байж болно);
- $ C16 – таны хүснэгт эсвэл мужын төгсгөлийн нүд.
Энэ томъёо нь зөвхөн хоёр дахь тохирох утгыг олдог. Хэрэв та үлдсэн давталтуудыг задлах шаардлагатай бол өмнөх шийдлийг ашиглана уу.
Хэрэв танд тохирох бүх жагсаалт хэрэгтэй бол - функц VPR Энэ нь туслагч биш, учир нь энэ нь нэг удаад зөвхөн нэг утгыг буцаадаг. Гэхдээ Excel нь функцтэй INDEX (INDEX), энэ ажлыг амархан даван туулж чадна. Ийм томъёо хэрхэн харагдахыг та дараах жишээнээс сурах болно.
Хүссэн утгын бүх давталтыг сэргээнэ үү
Дээр дурдсанчлан VPR сканнердсан мужаас бүх давхардсан утгыг гаргаж авах боломжгүй. Үүнийг хийхийн тулд танд Excel-ийн хэд хэдэн функцээс бүрдсэн арай илүү төвөгтэй томъёо хэрэгтэй болно INDEX (INDEX), Жижиг (ЖИЖИГ) ба зурвас газар (ШУГАМ)
Жишээлбэл, доорх томьёо нь B2:B2 муж дахь F16 нүдний утгын бүх давталтыг олж, C баганын ижил мөрүүдийн үр дүнг буцаана.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Энэ массивын томьёог нүд гэх мэт олон зэргэлдээ нүдэнд оруулна F4: F8доорх зурагт үзүүлсэн шиг. Нүдний тоо нь хайсан утгын хамгийн их давталтын тоотой тэнцүү буюу түүнээс их байх ёстой. Дарахаа бүү мартаарай Ctrl + Shift + Enterмассивын томьёог зөв оруулах.
Хэрэв та энэ нь хэрхэн ажилладагийг ойлгохыг сонирхож байгаа бол томъёоны нарийн ширийн зүйлийг бага зэрэг судалъя:
-Р хэсэг 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - F2 нүдний утгыг B2:B16 муж дахь утгууд тус бүртэй харьцуулна уу. Хэрэв тохирох зүйл олдвол илэрхийлэл STRING(C2:C16)-1 харгалзах мөрийн дугаарыг буцаана (утга -1 толгойн мөрийг оруулахгүй байхыг зөвшөөрдөг). Хэрэв тохирох зүйл байхгүй бол функц IF (IF) хоосон мөрийг буцаана.
Функцийн үр дүн IF (Хэрэв) ийм хэвтээ массив байх болно: {1,"",3,"",5,"","","","","","",12,"","",""}
-Р хэсэг 2:
ROW()-3
СТРОКА()-3
Энд функц байна зурвас газар (LINE) нь нэмэлт тоолуурын үүрэг гүйцэтгэдэг. Томьёог F4:F9 нүднүүдэд хуулсан тул бид тоог хасна 3 функцийн үр дүнгээс утгыг авна 1 эс дотор F4 (мөр 4, хасах 3) авах 2 эс дотор F5 (мөр 5, хасах 3) гэх мэт.
-Р хэсэг 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Чиг үүрэг Жижиг (ЖИЖИГ) буцаана үгүй ээ өгөгдлийн массив дахь хамгийн бага утга. Манай тохиолдолд аль байрлалыг (хамгийн багаас) буцаах нь функцээр тодорхойлогддог зурвас газар (ШУГАМ) (2-р хэсгийг үзнэ үү). Тэгэхээр эсийн хувьд F4 үйл ажиллагаа ЖИЖИГ({массив},1) буцах 1-р (хамгийн жижиг) массив элемент, өөрөөр хэлбэл 1. Эсийн хувьд F5 буцах 2-р массивын хамгийн жижиг элемент, өөрөөр хэлбэл 3Гэх мэт
-Р хэсэг 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Чиг үүрэг INDEX (INDEX) нь массив дахь тодорхой нүдний утгыг буцаана С2: С16. Эсийн хувьд F4 үйл ажиллагаа ИНДЕКС($C$2:$C$16) буцах болно АлимУчир нь F5 үйл ажиллагаа ИНДЕКС($C$2:$C$16) буцах болно Амттан гэх мэт.
-Р хэсэг 5:
IFERROR()
ЕСЛИОШИБКА()
Эцэст нь бид томъёог функц дотор байрлуулна АЛДАА (IFERROR), учир нь та алдааны мэдэгдэлд сэтгэл хангалуун байх магадлал багатай #AT (#Үгүй) хэрэв томьёог хуулсан нүдний тоо нь үзэж буй муж дахь давхардсан утгуудын тооноос бага байвал.
Мэдэгдэж буй мөр, баганаар XNUMXD хайлт хийх
Excel дээр XNUMXD хайлт хийх нь мэдэгдэж буй мөр, баганын дугаараар утгыг хайх явдал юм. Өөрөөр хэлбэл, та тодорхой мөр, баганын огтлолцол дээр байгаа нүдний утгыг задалж байна.
Ингээд хүснэгтээ эргүүлээд функц бүхий томьёо бичье VPR, XNUMX-р сард борлуулсан нимбэгний үнийн талаархи мэдээллийг олж авах болно.
XNUMXD хайлт хийх хэд хэдэн арга байдаг. Сонголтуудыг шалгаад өөрт хамгийн тохиромжтойг нь сонго.
VLOOKUP болон MATCH функцууд
Та хэд хэдэн функцийг ашиглаж болно VPR (VLOOKUP) болон ИЛҮҮ ИЛҮҮ (MATCH) талбаруудын огтлолцол дээрх утгыг олох Название продукта (мөр) ба Сараар Асуултын массивын (багана):
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Дээрх томъёо нь ердийн функц юм VPR, энэ нь A2-аас A9 хүртэлх нүднүүдийн "Нимбэг" гэсэн утгатай яг таарч байгааг хайдаг. Гэхдээ XNUMX-р сарын борлуулалт аль баганад байгааг мэдэхгүй байгаа тул та гурав дахь функцын аргументийн баганын дугаарыг тохируулах боломжгүй болно. VPR. Үүний оронд функцийг ашигладаг ИЛҮҮ ИЛҮҮЭнэ баганыг тодорхойлох.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Хүний хэл рүү орчуулбал энэ томьёо нь:
- Бид "Мар" гэсэн дүрүүдийг хайж байна - маргаан хайх_ үнэлгээ (хайлтын_утга);
- A1-ээс I1 хүртэлх нүднүүдийг харах – аргумент хайх_массив (хайлтын_массив);
- Яг тохирохыг буцаах – аргумент тохирох_төрөл (тохирох_төрөл).
ашиглах 0 Гурав дахь аргумент дээр та функцуудыг хэлдэг ИЛҮҮ ИЛҮҮ Таны хайж буй утгад яг таарч байгаа анхны утгыг хайж олоорой. Энэ нь үнэ цэнэтэй тэнцүү юм Буруу байна (ХУДАЛ) дөрөв дэх аргументийн хувьд VPR.
Хоёр хэмжээст хайлт эсвэл хоёр чиглэлтэй хайлт гэгддэг Excel дээр хоёр талын хайлтын томъёог ингэж үүсгэж болно.
SUMPRODUCT функц
Чиг үүрэг SUMPRODUCTION (SUMPRODUCT) нь сонгосон массивуудын бүтээгдэхүүний нийлбэрийг буцаана:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX болон MATCH функцууд
Дараагийн өгүүллээр би эдгээр функцийг нарийвчлан тайлбарлах болно, тиймээс та одоохондоо энэ томъёог хуулж болно.
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Нэрлэсэн мужууд ба огтлолцлын оператор
Хэрэв та эдгээр бүх нарийн төвөгтэй Excel томьёог мэддэггүй бол танд дараах харагдах бөгөөд мартагдашгүй арга таалагдаж магадгүй юм.
- Хүснэгтийг сонгоод табыг нээнэ үү Томъёо (Томъёо) болон товшино уу Сонголтоос үүсгэх (Сонголтоос үүсгэх).
- Хайрцагуудыг шалгана уу Дээд эгнээ (дээрх мөрөнд) ба Зүүн багана (зүүн талд байгаа баганад). Microsoft Excel нь хүснэгтийн дээд мөр ба зүүн баганын утгуудын мужуудад нэр өгөх болно. Одоо та томьёо үүсгэхгүйгээр шууд эдгээр нэрсийг ашиглан хайлт хийх боломжтой.
- Ямар ч хоосон нүдэнд бичнэ үү =мөр_нэр баганын_нэр, жишээ нь иймэрхүү:
= Lemons XNUMX-р сар
… эсвэл эсрэгээр:
= Мар Лемонс
Мөр болон баганын нэрийг зайгаар тусгаарлах ёстой бөгөөд энэ тохиолдолд огтлолцлын оператор шиг ажилладаг гэдгийг санаарай.
Таныг нэр оруулах үед Microsoft Excel нь томьёо оруулахтай адил тохирох нэрсийн жагсаалт бүхий зөвлөмжийг харуулах болно.
- Press оруулна уу мөн үр дүнг шалгана уу
Ерөнхийдөө, дээрх аргуудын аль нь ч сонгосон хоёр хэмжээст хайлтын үр дүн ижил байх болно.
Нэг томъёонд олон VLOOKUP ашиглах
Үндсэн хүснэгт болон хайлтын хүснэгтэд нийтлэг нэг багана байдаггүй бөгөөд энэ нь таныг ердийн функцийг ашиглахаас сэргийлдэг. VPR. Гэсэн хэдий ч бидний сонирхож буй мэдээллийг агуулаагүй, үндсэн хүснэгт болон хайлтын хүснэгттэй нийтлэг баганатай өөр нэг хүснэгт бий.
Дараах жишээг харцгаая. Бидэнд багана бүхий үндсэн хүснэгт байна SKU (шинэ), та өөр хүснэгтээс харгалзах үнэ бүхий баганыг нэмэхийг хүсч байна. Нэмж дурдахад бид 2 хайлтын хүснэгттэй. Эхнийх нь (Хүснэгт 1) шинэчлэгдсэн тоонуудыг агуулна SKU (шинэ) болон бүтээгдэхүүний нэр, хоёр дахь нь (Хүснэгт 2) - бүтээгдэхүүний нэр, хуучин дугаар SKU (хуучин).
Хоёрдахь хайлтын хүснэгтээс үнийг үндсэн хүснэгтэд нэмэхийн тулд давхар гэж нэрлэгддэг үйлдлийг хийх ёстой VPR эсвэл үүрлэсэн VPR.
- Функц бичнэ үү VPR, энэ нь хүснэгтээс бүтээгдэхүүний нэрийг олдог Хайлтын хүснэгт 1ашиглах SKU, хүссэн утгаараа:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Энд Шинэ_SKU - нэрлэсэн муж $A:$B хүснэгтэд Хайлтын хүснэгт 1, 2 – энэ нь барааны нэрийг агуулсан В багана (дээрх зургийг харна уу)
- Хүснэгтээс үнийг оруулах томьёог бичнэ үү Хайлтын хүснэгт 2 алдартай бүтээгдэхүүний нэр дээр үндэслэсэн. Үүнийг хийхийн тулд өмнө нь үүсгэсэн томъёогоо шинэ функцийн хайлтын утга болгон буулгана уу VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Энд Үнэ - нэрлэсэн муж $A:$C хүснэгтэд Хайлтын хүснэгт 2, 3 үнэ агуулсан C багана юм.
Доорх зурагт бидний үүсгэсэн томъёогоор буцаасан үр дүнг харуулав.
VLOOKUP болон DIRECT ашиглан өөр өөр хүснэгтүүдийн өгөгдлийг динамикаар орлуулах
Эхлээд бид бие биенээ зөв ойлгохын тулд "Янз бүрийн хүснэгтийн өгөгдлийг динамикаар солих" гэсэн хэллэгээр юу хэлэх гээд байгааг тодруулъя.
Ижил форматтай өгөгдөл бүхий хэд хэдэн хуудас байх тохиолдол байдаг бөгөөд өгөгдсөн нүдэнд оруулсан утгаас хамааран тодорхой хуудаснаас шаардлагатай мэдээллийг гаргаж авах шаардлагатай болдог. Үүнийг жишээгээр тайлбарлахад илүү хялбар гэж бодож байна.
Та ижил бүтээгдэхүүнтэй, ижил форматтай хэд хэдэн бүс нутагт борлуулалтын тайлантай байна гэж төсөөлөөд үз дээ. Та тодорхой бүс нутгийн борлуулалтын тоог олохыг хүсч байна:
Хэрэв танд ийм хоёр л тайлан байгаа бол та функц бүхий гутамшигтай энгийн томъёог ашиглаж болно VPR и IF (Хэрэв) хайхыг хүссэн тайлангаа сонгохын тулд:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
хаана:
- $ D $ 2 байна нь бүтээгдэхүүний нэрийг агуулсан нүд юм. Томьёог бусад нүд рүү хуулахдаа хайлтын утгыг өөрчлөхгүйн тулд бид энд үнэмлэхүй лавлагаа ашигладаг болохыг анхаарна уу.
- $D3 бүс нутгийн нэртэй нүд юм. Бид томьёог нэг баганын бусад нүдэнд хуулахаар төлөвлөж байгаа тул үнэмлэхүй баганын лавлагаа болон харьцангуй мөрийн лавлагааг ашиглаж байна.
- FL_Салes и CA_Sales - борлуулалтын холбогдох тайланг агуулсан хүснэгтүүдийн нэрс (эсвэл нэрлэсэн муж). Жишээлбэл, та ердийн хуудасны нэр, нүдний мужын лавлагааг ашиглаж болно 'FL Sheet'!$A$3:$B$10, гэхдээ нэрлэсэн мужууд нь илүү тохиромжтой.
Гэсэн хэдий ч, ийм олон хүснэгтүүд байгаа үед функц IF хамгийн сайн шийдэл биш юм. Үүний оронд та функцийг ашиглаж болно ҮНЭГҮЙ (ШУУД ) хүссэн хайлтын хүрээг буцаана.
Чиний мэдэж байгаагаар функц ҮНЭГҮЙ нь текстийн стрингээр өгсөн холбоосыг буцаахад хэрэглэгддэг бөгөөд энэ нь яг одоо бидэнд хэрэгтэй зүйл юм. Тиймээс дээрх томьёоны илэрхийллийг функцээр зоригтойгоор солино IF функцтэй холбох ҮНЭГҮЙ. Энд нэг хослол байна VPR и ҮНЭГҮЙ Хамтарч ажиллавал
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
хаана:
- $ D $ 2 байна – энэ нь бүтээгдэхүүний нэр бүхий нүд бөгөөд үнэмлэхүй холбоосын улмаас өөрчлөгдөөгүй.
- $D3 нь бүсийн нэрний эхний хэсгийг агуулсан нүд юм. Бидний жишээнд энэ FL.
- _Борлуулалт – бүх нэрлэсэн муж эсвэл хүснэгтийн нэрний нийтлэг хэсэг. D3 нүдэн дэх утгыг нэгтгэх үед энэ нь шаардлагатай мужийг бүрэн хангасан нэрийг бүрдүүлнэ. Функцэд шинээр орсон хүмүүст зориулсан зарим мэдээллийг доор харуулав ҮНЭГҮЙ.
ШУУД БУС болон VLOOKUP хэрхэн ажилладаг
Эхлээд функцийн синтаксийг сануулъя ҮНЭГҮЙ (ШУУД БУС):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Эхний аргумент нь нүдний лавлагаа (A1 эсвэл R1C1 загвар), мужын нэр эсвэл текстийн мөр байж болно. Хоёрдахь аргумент нь эхний аргументад ямар төрлийн холбоос агуулагдаж байгааг тодорхойлдог.
- A1хэрвээ маргаан бол ҮНЭН КОД (ҮНЭН) эсвэл тодорхойлогдоогүй;
- R1C1, хэрэв FAS E (ХУДАЛ).
Манай тохиолдолд холбоос нь хэв маягтай байдаг A1, ингэснээр та хоёр дахь аргументыг орхиж, эхнийх нь дээр анхаарлаа төвлөрүүлж болно.
Ингээд борлуулалтын тайландаа эргэн орцгооё. Хэрэв та санаж байгаа бол тайлан бүр нь тусдаа хуудсан дээр байрладаг тусдаа хүснэгт юм. Томьёог зөв ажиллуулахын тулд та хүснэгтүүдээ (эсвэл муж) нэрлэх ёстой бөгөөд бүх нэр нь нийтлэг хэсэгтэй байх ёстой. Жишээлбэл, иймэрхүү: CA_Sales, FL_Борлуулалт, TX_Борлуулалт гэх мэт. Таны харж байгаагаар "_Борлуулалт" бүх нэрэнд байдаг.
Чиг үүрэг ҮНЭГҮЙ D баганын утга болон "_Борлуулалт" гэсэн текстийн мөрийг холбосноор үүнийг хэлнэ VPR аль хүснэгтээс хайх. Хэрэв D3 нүд "FL" утгыг агуулж байвал томьёо нь хүснэгтээс хайх болно FL_Борлуулалт, хэрэв "CA" бол хүснэгтэд байна CA_Sales гэх мэт.
Функцуудын үр дүн VPR и ҮНЭГҮЙ дараах байх болно.
Хэрэв өгөгдөл нь өөр өөр Excel-ийн номонд байгаа бол та нэрлэсэн мужаас өмнө номын нэрийг нэмэх хэрэгтэй, жишээлбэл:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Хэрэв функц ҮНЭГҮЙ өөр ажлын номыг хэлнэ, тэр ажлын дэвтэр нээлттэй байх ёстой. Хэрэв энэ нь хаагдсан бол функц алдааг мэдээлэх болно. #REF! (#SSYL!).