Агуулга
Энэхүү бяцхан заавар нь функцийг хэрхэн хийхийг тайлбарладаг VPR (VLOOKUP) том жижиг үсгээр хардаг бөгөөд Excel-ийн жижиг үсгээр хайх боломжтой хэд хэдэн томьёог харуулж, функц бүрийн давуу болон сул талуудыг зааж өгдөг.
Excel-ийн хэрэглэгч бүр босоо хайлт хийдэг функцийг мэддэг байх гэж бодож байна. Энэ нь зөв, энэ бол функц юм VPR. Гэсэн хэдий ч цөөхөн хүн үүнийг мэддэг VPR жижиг жижиг үсэг мэдэрдэггүй, өөрөөр хэлбэл жижиг, том үсгийн тэмдэгтүүд нь ижил байна.
Чадваргүйг харуулсан хурдан жишээ энд байна VPR бүртгэлийг таних. Нэг эс дотор байна гэж бодъё A1 "Төлбөр" болон нүдийг агуулна A2 – “Төлбөр”, томъёо:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… жагсаалтын нэгдүгээрт бичигдсэн утга учир “тооцоо” дээр хайлтаа зогсоож, нүднээс утгыг гаргаж авна. B1.
Энэ өгүүллийн сүүлд би яаж хийхийг танд үзүүлэх болно VPR Том жижгээр бичхээс хамаарна. Нэмж дурдахад бид Excel дээр том жижиг үсгээр хайлт хийх боломжтой хэд хэдэн функцийг сурах болно.
Бид хамгийн энгийнээс эхэлнэ - УРУУЛАХ (LOOKUP) болон SUMPRODUCTION (SUMPRODUCT), харамсалтай нь хэд хэдэн чухал хязгаарлалттай байдаг. Дараа нь бид арай илүү төвөгтэй томъёог нарийвчлан авч үзэх болно INDEX+TACH (INDEX+MATCH) нь ямар ч нөхцөл байдал, ямар ч өгөгдлийн багцтай төгс ажилладаг.
VLOOKUP функц нь том жижиг үсгийг харгалздаг
Таны мэдэж байгаагаар ердийн функц VPR жижиг жижиг жижиг үсэг тоогүй байна. Гэсэн хэдий ч том жижиг үсгийн мэдрэмжтэй болгох арга бий. Үүнийг хийхийн тулд та дараах жишээнд үзүүлсэн шиг хүснэгтэд туслах багана нэмэх хэрэгтэй.
Багана гэж бодъё B бүтээгдэхүүний танигч (Зүйл) байгаа бөгөөд та баганаас бүтээгдэхүүний үнэ болон харгалзах тайлбарыг гаргаж авахыг хүсч байна C и D. Асуудал нь танигч нь жижиг, том үсэг хоёуланг нь агуулж байдагт оршино. Жишээлбэл, эсийн утгууд B4 (001Tvci3u) болон B5 (001Tvci3U) нь зөвхөн сүүлчийн тэмдэгтийн хувьд ялгаатай, u и U тус тус.
Таны төсөөлж байгаагаар ердийн хайлтын томъёо
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
буцах болно $ 90, утгаас хойш 001Tvci3u -аас эрт хайлтын мужид байна 001Tvci3U. Гэхдээ энэ нь бидэнд хэрэгтэй зүйл биш гэж үү?
Функцээр хайх VPR Excel-ийн жижиг жижиг үсгийн хувьд та туслах баганыг нэмж, нүднүүдийг дараах томъёогоор дүүргэх шаардлагатай (B нь хайлтын багана):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Энэ томъёо нь хүссэн утгыг тусдаа тэмдэгт болгон хувааж, тэмдэгт бүрийг кодоор нь сольж өгдөг (жишээлбэл, оронд нь A оронд нь 65 настай a код 97) ба дараа нь эдгээр кодыг тоонуудын өвөрмөц мөр болгон нэгтгэнэ.
Үүний дараа бид энгийн функцийг ашигладаг VPR жижиг жижиг үсгээр хайхад:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Функцийг зөв ажиллуулах VPR Том жижиг үсэг мэдрэгчтэй байх нь хоёр хүчин зүйлээс хамаарна:
- Туслах багана нь үзэх боломжтой хэсгийн хамгийн зүүн талын багана байх ёстой.
- Таны хайж буй утга нь жинхэнэ утгын оронд тэмдэгтийн код агуулсан байх ёстой.
CODE функцийг хэрхэн зөв ашиглах вэ
Туслах баганын нүднүүдэд оруулсан томьёо нь таны хайлтын бүх утгууд ижил тооны тэмдэгттэй байна гэж үздэг. Хэрэв тийм биш бол та хамгийн бага, хамгийн том тоог мэдэж, аль болох олон функцийг нэмэх хэрэгтэй АЛДАА (IFERROR) хамгийн богино болон хамгийн урт хайсан утгын зөрүү нь хэдэн тэмдэгт байна.
Жишээлбэл, хайлтын хамгийн богино утга нь 3 тэмдэгт, хамгийн урт нь 5 тэмдэгт байвал энэ томъёог ашиглана уу:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Функцийн хувьд PSTR (MID) Та дараах аргументуудыг өгнө үү.
- 1-р аргумент - текст (текст) нь задлах тэмдэгтүүдийг агуулсан текст эсвэл нүдний лавлагаа юм (бидний тохиолдолд энэ нь B2)
- 2-р аргумент - эхлэл_тоо (эхлэх_байрлал) нь задлах тэмдэгтүүдийн эхний байрлал юм. чи орно 1 эхний функцэд PSTR, 2 - хоёр дахь функцэд PSTR гэх мэт
- 3-р аргумент - тэмдэгтийн_тоо (тэмдэгтийн_тоо) – Текстээс гаргаж авах тэмдэгтүүдийн тоог заана. Бидэнд үргэлж 1 тэмдэгт хэрэгтэй байдаг тул бүх функцэд бид бичдэг 1.
Хязгаарлалт: Чиг үүрэг VPR нь Excel-ийн жижиг жижиг үсгээр хайхад хамгийн сайн шийдэл биш юм. Нэгдүгээрт, туслах баганыг нэмэх шаардлагатай. Хоёрдугаарт, өгөгдөл нь нэгэн төрлийн, эсвэл хайсан утгуудын тэмдэгтүүдийн тодорхой тоо тодорхой байвал томъёо нь сайн ажилладаг. Хэрэв энэ нь таны хэрэг биш бол бидний доор үзүүлсэн шийдлүүдийн аль нэгийг ашиглах нь дээр.
Том жижиг үсгээр хайхад зориулсан LOOKUP функц
Чиг үүрэг УРУУЛАХ (LOOKUP) холбоотой VPR, гэхдээ түүний синтакс нь туслах багана нэмэлгүйгээр жижиг жижиг үсгээр хайх боломжийг олгодог. Үүнийг хийхийн тулд ашиглана уу УРУУЛАХ функцтэй хослуулсан ЯГ (ЯГ).
Хэрэв бид өмнөх жишээн дээрх өгөгдлийг (туслах баганагүйгээр) авбал дараахь томъёо нь даалгаврыг даван туулах болно.
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Хүрээнд томъёо хайдаг А2: А7 нүдний утгатай яг таарч байна F2 жижиг үсгийн мэдрэмжтэй бөгөөд ижил мөрийн В баганаас утгыг буцаана.
шиг VPRүйл ажиллагаа УРУУЛАХ Доорх дэлгэцийн зургаас харж байгаачлан текст болон тоон утгуудтай адил ажилладаг.
Чухал! Функцийн хувьд УРУУЛАХ зөв ажилласан бол хайлтын баганад байгаа утгуудыг өсөх дарааллаар, өөрөөр хэлбэл хамгийн багаас том хүртэл эрэмбэлэх ёстой.
Функц хэрхэн ажилладаг талаар товч тайлбарлая ЯГ дээр үзүүлсэн томъёонд, учир нь энэ нь гол цэг юм.
Чиг үүрэг ЯГ 1 ба 2-р аргумент дахь хоёр текстийн утгыг харьцуулж, хэрэв тэдгээр нь яг ижил байвал ҮНЭН, үгүй бол ХУДАЛ гэж буцаана. Энэ нь бидний хувьд чухал үүрэг гүйцэтгэдэг ЯГ Том жижгээр бичхээс хамаарна.
Бидний томъёо хэрхэн ажилладагийг харцгаая ХАРАХ+ЯГШ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- Чиг үүрэг ЯГ эсийн утгыг харьцуулна F2 баганад байгаа бүх элементүүдтэй A (A2:A7). Хэрэв яг таарч байвал ҮНЭН, үгүй бол ХУДАЛ буцаана.
- Та эхний функцийн аргументыг өгсөн тул УРУУЛАХ утга ҮНЭН бол, энэ нь зөвхөн тохирох утгыг том том жижиг жижиг жижиг жижиг тусгалтай олсон тохиолдолд заасан баганаас (манай тохиолдолд В багана) харгалзах утгыг гаргаж авдаг.
Энэ тайлбар нь ойлгомжтой байсан бөгөөд одоо та гол санааг ойлгосон гэж найдаж байна. Хэрэв тийм бол бид цаашид дүн шинжилгээ хийх бусад функцүүдэд танд ямар ч бэрхшээл гарахгүй, учир нь. Тэд бүгд ижил зарчим дээр ажилладаг.
Хязгаарлалт: Хайлтын баганад байгаа өгөгдлийг өсөх дарааллаар эрэмбэлсэн байх ёстой.
SUMPRODUCT – текстийн утгыг олдог, том жижиг үсгийн мэдрэмжтэй, гэхдээ зөвхөн тоог буцаана
Та гарчигнаас нь аль хэдийн ойлгосон байх. SUMPRODUCTION (SUMPRODUCT) нь Excel-ийн өөр нэг функц бөгөөд том жижиг үсгээр хайлт хийхэд тань туслах боловч зөвхөн тоон утгыг буцаана. Хэрэв энэ сонголт танд тохирохгүй бол та тэр даруй багц руу шилжиж болно INDEX+TACH, энэ нь ямар ч тохиолдол болон ямар ч өгөгдлийн төрлүүдийн шийдлийг өгдөг.
Эхлээд би энэ функцын синтаксийг товч тайлбарлая, энэ нь танд дараах том жижиг жижиг томьёог илүү сайн ойлгоход тусална.
Чиг үүрэг SUMPRODUCTION өгөгдсөн массивын элементүүдийг үржүүлж үр дүнгийн нийлбэрийг буцаана. Синтакс нь дараах байдлаар харагдаж байна.
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Бидэнд том жижиг үсгээр хайх шаардлагатай тул функцийг ашигладаг ЯГ (ЯГ) өмнөх жишээнээс үржүүлэгчийн нэг болгон:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Та санаж байгаа бол ЯГ эсийн утгыг харьцуулна F2 баганад байгаа бүх элементүүдтэй A. Хэрэв яг таарч байвал ҮНЭН, үгүй бол ХУДАЛ буцаана. Математик үйлдлүүдэд Excel нь ҮНЭН гэж авдаг 1, мөн FALSE for 0Цааш нь SUMPRODUCTION эдгээр тоог үржүүлж үр дүнг нэгтгэнэ.
Тэгийг тооцохгүй, учир нь үржүүлбэл үргэлж өгдөг 0. Багананд яг таарч байвал юу болохыг нарийвчлан авч үзье A олоод буцсан 1… функц SUMPRODUCTION баганад байгаа тоог үржүүлнэ B on 1 мөн үр дүнг буцаана - яг ижил тоо! Учир нь бусад бүтээгдэхүүний үр дүн нь тэг бөгөөд тэдгээр нь гарсан нийлбэрт нөлөөлөхгүй.
Харамсалтай нь функц SUMPRODUCTION Текстийн утга, огноотой ажиллах боломжгүй, учир нь тэдгээрийг үржүүлэх боломжгүй. Энэ тохиолдолд та алдааны мэдэгдэл хүлээн авах болно # ҮНЭЛГЭЭ! (#VALUE!) нүдэн дэх шиг F4 доорх зурган дээр:
Хязгаарлалт: Зөвхөн тоон утгыг буцаана.
INDEX + MATCH – ямар ч төрлийн өгөгдлийн том жижиг үсгээр хайх
Эцэст нь бид ямар ч өгөгдлийн багцтай ажиллах хязгааргүй, том жижиг жижиг хайлтын томъёонд ойрхон байна.
Энэ жишээ хамгийн сүүлд ирдэг бөгөөд энэ нь амттандаа хамгийн сайныг нь үлдээсэн учраас биш, харин өмнөх жишээнүүдээс олж авсан мэдлэг нь том жижиг үсгийн томьёог илүү сайн, хурдан ойлгоход тусална. INDEX+TACH (INDEX+MAtch).
Чиний таамаглаж байгаагаар функцүүдийн хослол ИЛҮҮ ИЛҮҮ и INDEX Excel-д илүү уян хатан, хүчирхэг хувилбар болгон ашигладаг VPR. VLOOKUP-ын оронд INDEX болон MATCH-ийг ашиглах нийтлэл нь эдгээр функцууд хэрхэн хамтран ажилладагийг төгс тайлбарлах болно.
Би зүгээр л гол санаануудыг дахин хэлье:
- Чиг үүрэг ИЛҮҮ ИЛҮҮ (MATCH) нь тухайн муж дахь утгыг хайж, түүний харьцангуй байрлалыг, өөрөөр хэлбэл мөр ба/эсвэл баганын дугаарыг буцаана;
- Дараа нь функц INDEX (INDEX) нь заасан багана ба/эсвэл мөрнөөс утгыг буцаана.
Томъёо руу INDEX+TACH Том жижиг үсгээр хайх боломжтой тул та түүнд зөвхөн нэг функц нэмэх хэрэгтэй. Энэ нь юу болохыг дахин таахад хэцүү биш юм ЯГ (ЯГ):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Энэ томъёонд ЯГ функцтэй хамт ажилладагтай ижил аргаар ажилладаг УРУУЛАХ, мөн ижил үр дүнг өгдөг:
Томъёо гэдгийг анхаарна уу INDEX+TACH буржгар хаалтанд хавсаргасан нь массив томьёо бөгөөд та үүнийг дарж дуусгах ёстой Ctrl + Shift + Enter.
Яагаад INDEX+MATCH нь том жижиг үсгээр хайхад хамгийн сайн шийдэл вэ?
Багцын гол давуу талууд INDEX и ИЛҮҮ ИЛҮҮ:
- Үүнээс ялгаатай нь туслах багана нэмэх шаардлагагүй VPR.
- Хайлтын баганыг эрэмбэлэх шаардлагагүй, ялгаатай нь УРУУЛАХ.
- Бүх төрлийн өгөгдөлтэй ажилладаг - тоо, текст, огноо.
Энэ томъёо төгс харагдаж байна, тийм үү? Үнэндээ тийм биш. Тийм учраас л.
Хайлтын утгатай холбоотой буцах утгын баганын нүд хоосон байна гэж бодъё. Томъёо ямар үр дүнд хүрэх вэ? Үгүй юу? Томъёо яг юуг буцаасныг харцгаая:
Өө, томьёо нь тэгийг буцаана! Хэрэв та цэвэр текстийн утгуудтай ажиллаж байгаа бол энэ нь тийм ч том асуудал биш байж магадгүй юм. Гэсэн хэдий ч, хүснэгтэд "бодит" тэг зэрэг тоонууд байгаа бол энэ нь асуудал болно.
Үнэн хэрэгтээ, бидний өмнө нь авч үзсэн бусад хайлтын томъёонууд (VLOOKUP, LOOKUP, SUMPRODUCT) ижил төстэй байдлаар ажилладаг. Гэхдээ та төгс томъёог хүсч байна, тийм үү?
Томъёоны жижиг үсгийн мэдрэмжтэй болгох INDEX+TACH төгс, функцэд оруулаарай IF (IF) нь буцах утгатай нүдийг шалгаж, хоосон байвал хоосон үр дүнг буцаана:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Энэ томъёонд:
- B буцах утгатай багана юм
- 1+ функцээр буцаасан нүдний харьцангуй байрлалыг эргүүлэх тоо юм ИЛҮҮ ИЛҮҮ, нүдний жинхэнэ хаяг руу. Жишээлбэл, манай функцэд ИЛҮҮ ИЛҮҮ хайлтын массив өгөгдсөн А2: А7, өөрөөр хэлбэл эсийн харьцангуй байрлал A2 болно 1, учир нь энэ нь массивын эхнийх юм. Гэхдээ эсийн бодит байрлал A2 баганад байна 2, тэгэхээр бид нэмнэ 1ялгааг нөхөж, үүрэг гүйцэтгэх ҮНЭГҮЙ (ШУУД БУС) хүссэн нүднээсээ утгыг гаргаж авсан.
Доорх зургууд нь том жижиг жижиг үсгийн засварласан томьёог харуулж байна INDEX+TACH Үйлдэл дээр. Хэрэв буцаж ирсэн нүд хоосон байвал энэ нь хоосон үр дүнг буцаана.
Би томьёог багана болгон дахин бичсэн Б:ДДэлгэцийн зураг дээрх томьёоны мөрөнд тааруулах.
Формула буцаж ирдэг 0хэрэв буцаасан нүдэнд тэг байвал.
Хэрэв та холбоосыг хүсвэл INDEX и ИЛҮҮ ИЛҮҮ Буцах утга хоосон үед зарим мессеж гарч ирвэл та үүнийг томъёоны сүүлчийн хашилтанд ("") бичиж болно, жишээлбэл:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
