Мундариҷа
- Ҷустуҷӯ дар Excel аз рӯи меъёрҳои гуногун
- Мо бо истифода аз VLOOKUP арзишҳои 2, 3 ва ғайраро истихроҷ мекунем
- Ҳама такрорҳои арзиши дилхоҳро дарёфт кунед
- Ҷустуҷӯи XNUMXD аз рӯи сатр ва сутуни маълум
- Истифодаи VLOOKUP-ҳои сершумор дар як формула
- Ивазкунии динамикии маълумот аз ҷадвалҳои гуногун бо истифода аз VLOOKUP ва INDIRECT
Дар қисми дуюми дастури мо дар бораи функсия VPR (VLOOKUP) дар Excel, мо якчанд мисолҳоро таҳлил хоҳем кард, ки ба шумо тамоми қудратро идора мекунанд 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_um (сутуни_рақам), яъне рақами сутуни дорои маълумоте, ки бояд гирифта шавад.
Мисоли 2: VLOOKUP аз рӯи ду меъёр бо ҷадвал дар варақи дигар дида мешавад
Агар ба шумо лозим аст, ки ҷадвали асосиро (Ҷадвали асосӣ) бо илова кардани маълумот аз ҷадвали дуюм (Ҷадвали ҷустуҷӯ), ки дар варақи дигар ё дар китоби дигари кории Excel ҷойгир аст, навсозӣ кунед, шумо метавонед арзиши дилхоҳро бевосита дар формулаи воридкардаатон ҷамъ кунед. ба ҷадвали асосӣ.
Мисли мисоли қаблӣ, ба шумо сутуни ёрирасон дар ҷадвали Ҷустуҷӯ бо арзишҳои якҷоя лозим аст. Ин сутун бояд сутуни чаптарин дар диапазони ҷустуҷӯ бошад.
Пас, формула бо VPR метавонад чунин бошад:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Дар ин ҷо, сутунҳои B ва C мутаносибан номҳои муштариён ва номҳои маҳсулот ва истинодро дар бар мегиранд Фармоишҳо!$A&$2:$D$2 ҷадвалро барои ҷустуҷӯ дар варақи дигар муайян мекунад.
Барои хондантар кардани формула, шумо метавонед ба диапазони намоиш ном диҳед ва он гоҳ формула хеле соддатар менамояд:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Барои кор кардани формула, арзишҳо дар сутуни чапи ҷадвал, ки шумо мебинед, бояд маҳз ҳамон тавре ки дар меъёрҳои ҷустуҷӯ муттаҳид карда шаванд. Дар расми боло, мо арзишҳои u2bu2band-ро муттаҳид кардем, ки дар байни онҳо фосила гузоштем, ҳамон тавре ки шумо бояд дар аргументи аввали функсия (BXNUMX& “” & CXNUMX) иҷро кунед.
Дар хотир доред! функсия VPR бо 255 аломат маҳдуд аст, он наметавонад арзиши дарозии зиёда аз 255 аломатро ҷустуҷӯ кунад. Инро дар хотир нигоҳ доред ва боварӣ ҳосил кунед, ки дарозии арзиши дилхоҳ аз ин маҳдудият зиёд нест.
Ман розӣ ҳастам, ки илова кардани сутуни ёрирасон роҳи беҳтарин ва на ҳамеша қобили қабул нест. Шумо метавонед ҳамин корро бе сутуни ёрирасон иҷро кунед, аммо ин формулаи мураккабтареро бо маҷмӯи функсияҳо талаб мекунад INDEX (ИНДЕКС) ва МАТР (БЕШТАР ФОШ ШУД).
Мо бо истифода аз 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-e такрор, шумо метавонед онро бе сутуни ёрирасон тавассути сохтани формулаи мураккабтар иҷро кунед:
=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 (ИНДЕКС), ХУРД (ХУРД) ва ҷудошуда (САТР)
Масалан, формулаи зер ҳамаи такрорҳои арзишро аз чашмаки F2 дар диапазони B2:B16 пайдо мекунад ва натиҷаро аз ҳамон сатрҳои сутуни С бармегардонад.
{=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 (АГАР) чунин массиви уфуқӣ хоҳад буд: {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) танҳо арзиши ячейкаи мушаххасро дар массив бармегардонад C2:C16. Барои ҳуҷайра F4 функсия ИНДЕКС($C$2:$C$16) бармегардад себбарои F5 функсия ИНДЕКС($C$2:$C$16) бармегардад Шириниҳо ва ғайра.
Қисми 5:
IFERROR()
ЕСЛИОШИБКА()
Дар охир, мо формуларо дар дохили функсия мегузорем ХАТО (IFERROR), зеро гумон аст, ки шумо аз паёми хато розӣ нашавед #AT (#Н/А) агар шумораи чашмакҳое, ки формула ба онҳо нусхабардорӣ карда мешавад, аз шумораи арзишҳои такрорӣ дар диапазони дидашаванда камтар бошад.
Ҷустуҷӯи XNUMXD аз рӯи сатр ва сутуни маълум
Иҷрои ҷустуҷӯи XNUMXD дар Excel ҷустуҷӯи арзишро аз рӯи рақами сатр ва сутуни маълум дар бар мегирад. Ба ибораи дигар, шумо арзиши чашмакро дар чорроҳаи сатр ва сутуни мушаххас истихроҷ мекунед.
Пас, биёед ба ҷадвали худ рӯй оварем ва формуларо бо функсия нависед VPR, ки дар бораи арзиши лимуи дар мохи март фурухташуда маълумот пайдо мекунад.
Якчанд роҳҳо барои анҷом додани ҷустуҷӯи 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 меҷӯяд. Аммо азбаски шумо намедонед, ки фурӯшҳои моҳи март дар кадом сутун қарор доранд, шумо наметавонед рақами сутунро барои далели функсияи сеюм муқаррар кунед. VPR. Ба ҷои ин, функсия истифода мешавад БЕШТАР ФОШбарои муайян кардани ин сутун.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Ин формула ба забони инсон тарҷума шудааст, маънои:
- Мо персонажхои «Мар»-ро чустучу мекунем ҷустуҷӯ_арзиш (арзиши_ҷустуҷӯ);
- Ҷустуҷӯ дар чашмакҳо аз A1 то I1 - далел ҷустуҷӯ_массив (массиви ҷустуҷӯ);
- Бозгашти мувофиқати дақиқ - далел навъи мувофиқ (навъи_мувофиқ).
Бо истифода аз 0 дар далели сеюм шумо функсияҳоро мегӯед БЕШТАР ФОШ арзиши аввалро ҷустуҷӯ кунед, ки ба арзише, ки шумо ҷустуҷӯ мекунед, комилан мувофиқ аст. Ин ба арзиш баробар аст козиб (ДУРУ) барои далели чорум VPR.
Ҳамин тавр шумо метавонед формулаи ҷустуҷӯи дутарафаро дар Excel эҷод кунед, ки онро ҷустуҷӯи дуҷониба ё ҷустуҷӯи дуҷониба низ маълум аст.
Функсияи SUMPRODUCT
функсия SUMPRODUCT (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 ба диапазонҳо аз арзишҳои сатри боло ва сутуни чапи ҷадвали шумо номҳо таъин мекунад. Акнун шумо метавонед бо истифода аз ин номҳо мустақиман бидуни сохтани формулаҳо ҷустуҷӯ кунед.
- Дар ҳама ҳуҷайраҳои холӣ нависед =сатр_номи сутун_номи, масалан, чунин:
= Лимон март
… ё баръакс:
= Мар Лимонс
Дар хотир доред, ки номҳои сатр ва сутун бояд бо фосила ҷудо карда шаванд, ки дар ин ҳолат мисли оператори буриш кор мекунад.
Ҳангоми ворид кардани ном, Microsoft Excel маслиҳати абзорро бо рӯйхати номҳои мувофиқ нишон медиҳад, ба монанди ҳангоми ворид кардани формула.
- матбуот даромадан ва натиҷаро тафтиш кунед
Умуман, кадоме аз усулҳои дар боло зикршударо интихоб кунед, натиҷаи ҷустуҷӯи дученака якхела хоҳад буд:
Истифодаи VLOOKUP-ҳои сершумор дар як формула
Чунин мешавад, ки ҷадвали асосӣ ва ҷадвали ҷустуҷӯ як сутуни умумӣ надоранд ва ин ба шумо имкон намедиҳад, ки функсияи муқаррариро истифода баред. VPR. Бо вуҷуди ин, ҷадвали дигаре вуҷуд дорад, ки маълумоте, ки мо ба он таваҷҷӯҳ дорем, дар бар намегирад, аммо сутуни умумӣ бо ҷадвали асосӣ ва ҷадвали ҷустуҷӯ дорад.
Биёед мисоли зеринро дида бароем. Мо ҷадвали асосӣ бо сутун дорем SKU (нав), ки шумо мехоҳед сутунеро бо нархҳои мувофиқ аз ҷадвали дигар илова кунед. Илова бар ин, мо 2 ҷадвали ҷустуҷӯ дорем. Якум (Ҷадвали ҷустуҷӯи 1) рақамҳои навшударо дар бар мегирад SKU (нав) ва номҳои маҳсулот ва дуюм (Ҷадвали ҷустуҷӯи 2) - номҳои маҳсулот ва рақамҳои кӯҳна SKU (кӯҳна).
Барои илова кардани нархҳо аз ҷадвали дуюми ҷустуҷӯ ба ҷадвали асосӣ, шумо бояд амалеро иҷро кунед, ки ҳамчун дукарата маълум аст VPR ё лона гузошта шудааст VPR.
- Функсия нависед VPR, ки номи маҳсулотро дар ҷадвал пайдо мекунад Ҷадвали ҷустуҷӯ 1истифода SKU, ҳамчун арзиши дилхоҳ:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Дар ин ҷо New_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 ва INDIRECT
Аввалан, биёед фаҳмем, ки ибораи "Ивазкунии динамикии маълумот аз ҷадвалҳои гуногун" чиро дар назар дорем, то боварӣ ҳосил кунем, ки мо якдигарро дуруст мефаҳмем.
Ҳолатҳое вуҷуд доранд, ки якчанд варақаҳои дорои маълумотҳои як формат вуҷуд доранд ва вобаста ба қимате, ки дар ячейка дохил карда мешавад, аз варақи муайян маълумоти заруриро гирифтан лозим аст. Ман фикр мекунам, ки инро бо мисол шарҳ додан осонтар аст.
Тасаввур кунед, ки шумо ҳисоботи фурӯшро барои якчанд минтақаҳо бо як маҳсулот ва дар як формат доред. Шумо мехоҳед рақамҳои фурӯшро барои як минтақаи мушаххас пайдо кунед:
Агар шумо танҳо ду чунин гузориш дошта бошед, пас шумо метавонед формулаи оддиро бо функсияҳо истифода баред 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'!$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агар далел бошад РАМЗИ ҳақиқӣ (TRUE) ё муайян нашудааст;
- R1C1, агар FЧй Э (ДУРУГ).
Дар мавриди мо, пайвандак услуб дорад A1, Пас шумо метавонед далели дуюмро тарк кунед ва ба аввал тамаркуз кунед.
Пас, биёед ба гузоришҳои фурӯши худ баргардем. Агар шумо дар хотир доред, пас ҳар як гузориш ҷадвали алоҳидаест, ки дар варақи алоҳида ҷойгир аст. Барои дуруст кор кардани формула, шумо бояд ҷадвалҳои (ё диапазонҳои) худро номбар кунед ва ҳамаи номҳо бояд як қисми умумӣ дошта бошанд. Масалан, ба ин монанд: CA_Sales, FL_Sales, TX_Sales ва ғайра. Тавре ки шумо мебинед, "_Sales" дар ҳама номҳо мавҷуд аст.
функсия Бавосита арзишро дар сутуни D ва сатри матнии "_Sales" мепайвандад, ба ин васила нақл мекунад VPR дар кадом ҷадвал ҷустуҷӯ кардан лозим аст. Агар чашмаки D3 арзиши "FL" дошта бошад, формула ҷадвалро ҷустуҷӯ мекунад FL_Sales, агар "CA" - дар ҷадвал CA_Sales ва ғайра.
Натиҷаи иҷрои вазифаҳо VPR и Бавосита зерин хоҳад буд:
Агар маълумот дар китобҳои гуногуни Excel ҷойгир бошад, шумо бояд номи китобро пеш аз диапазони номбаршуда илова кунед, масалан:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Агар функсия Бавосита ба дафтари кори дигар дахл дорад, он дафтари корӣ бояд кушода бошад. Агар он пӯшида бошад, функсия дар бораи хато хабар медиҳад. #REF! (#SSYL!).