мазмуну
- Excelде бир нече критерийлер боюнча издеңиз
- VLOOKUP аркылуу 2, 3, ж.б. маанилерди чыгарабыз
- Керектүү маанидеги бардык кайталоолорду чыгарып алыңыз
- Белгилүү сап жана тилке боюнча XNUMXD издөө
- Бир формулада бир нече VLOOKUP колдонуу
- VLOOKUP жана КЫЙЫР аркылуу ар кандай таблицалардагы маалыматтарды динамикалык алмаштыруу
Функция боюнча окуу куралыбыздын экинчи бөлүгүндө VPR Excelде (VLOOKUP) биз сизге бардык күчтү башкарууга жардам бере турган бир нече мисалдарды талдайбыз VPR абдан дымактуу Excel милдеттерин чечүү үчүн. Мисалдар сизде бул функция кандайча иштээри тууралуу негизги билимиңиз бар деп болжолдойт. Болбосо, сиз бул окуу куралынын синтаксисин жана негизги колдонулушун түшүндүргөн биринчи бөлүгүнөн баштоого кызыкдар болушуңуз мүмкүн. VPR. Мейли, баштайлы.
Excelде бир нече критерийлер боюнча издеңиз
милдети VPR Excelде бул маалымат базасында белгилүү бир маанини издөө үчүн абдан күчтүү курал. Бирок, олуттуу чектөө бар - анын синтаксиси бир гана маанини издөөгө мүмкүндүк берет. Эгер бир нече шарттар боюнча издөөнү кааласаңызчы? Сиз төмөндө чечим таба аласыз.
1-мисал: 2 башка критерий боюнча издөө
Бизде буйрутмалардын тизмеси бар жана биз тапкыбыз келет дейли Товарлардын саны (Саны), эки критерийдин негизинде – Кардардын аты (Кардар) и продукт Name (Продукт). Төмөнкү таблицадан көрүнүп тургандай, сатып алуучулардын ар бири товарлардын бир нече түрүн заказ кылгандыгы менен маселе татаалдашат:
үзгүлтүксүз функция VPR бул сценарийде иштебейт, анткени ал берилген издөө маанисине дал келген биринчи маанини кайтарат. Мисалы, сиз буюмдун санын билгиңиз келсе таттууларсатып алуучу тарабынан заказ кылынган Джереми Хилл, төмөнкү формуланы жаз:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– бул формула жыйынтыкты кайтарат 15продуктуга туура келет Apples, анткени бул дал келген биринчи маани.
Жөнөкөй чечүүчү жол бар - бардык керектүү критерийлерди бириктире турган кошумча тилке түзүңүз. Биздин мисалда бул колонналар Кардардын аты (Кардар) и продукт Name (Продукт). Бириктирилген тилке дайыма издөө диапазонунун эң сол мамычасы болушу керек экенин унутпаңыз, анткени бул функциянын сол тилкеси. 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 аргументтин бириктирилген маанисин камтыйт search_value (издөө_маани) жана 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;ЛОЖЬ)
Бул жерде, 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), кардарлардын аттары В тилкесинде экенин эске алганда:
=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 $ – тилке Кардардын аты;
- Table4 – Сиздин үстөлүңүз (бул жер кадимки диапазон да болушу мүмкүн);
- $ C16 – столуңуздун же диапазонуңуздун акыркы уячасы.
Бул формула экинчи дал келген маанини гана табат. Калган кайталоолорду алуу керек болсо, мурунку чечимди колдонуңуз.
Эгер сизге бардык дал келгендердин тизмеси керек болсо – функция VPR бул жардамчы эмес, анткени ал бир убакта бир гана маанини кайтарат - мезгил. Бирок Excel функциясы бар INDEX (INDEX), бул милдетти оңой эле чече алат. Мындай формула кандай болоорун төмөнкү мисалдан билесиз.
Керектүү маанидеги бардык кайталоолорду чыгарып алыңыз
Жогоруда белгиленгендей VPR сканерленген диапазондон бардык кайталанма маанилерди чыгара албайт. Бул үчүн сизге бир аз татаалыраак формула керек, ал бир нече Excel функцияларынан турат, мисалы INDEX (INDEX), КИЧИНЕКЕЙ (КИЧИ) жана ROW (LINE)
Мисалы, төмөндөгү формула B2:B2 диапазонундагы F16 уячасындагы маанинин бардык кайталанышын табат жана С тилкесиндеги ошол эле саптардан жыйынтыкты кайтарат.
{=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массив формуласын туура киргизүү үчүн.
Эгер сиз анын кантип иштээрин түшүнгүңүз келсе, формуланын чоо-жайына бир аз кирип көрөлү:
Part 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,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Бул жерде функция ROW (LINE) кошумча эсептегич катары иштейт. Формула F4:F9 уячаларына көчүрүлгөндүктөн, санды чыгарабыз 3 функциянын натыйжасынан маанини алуу үчүн 1 клеткада F4 (4-сап, кемитүү 3) алуу 2 клеткада F5 (5-сап, 3-кемитүү) жана башкалар.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
милдети КИЧИНЕКЕЙ (КИЧИ) кайтарат жок х маалымат массивиндеги эң кичине маани. Биздин учурда, кайсы позиция (эң кичинесинен) кайтаруу функциясы менен аныкталат ROW (LINE) (2-бөлүктү караңыз). Ошентип, клетка үчүн F4 милдети КИЧИ({массив},1) кайтып келди 1-чи (эң кичинекей) массив элементи, б.а 1. Клетка үчүн F5 кайтып келди 2-чи массивдеги эң кичинекей элемент, б.а 3, Etc.
Part 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) кайра келет Applesүчүн F5 милдети ИНДЕКС($C$2:$C$16) кайра келет таттуулар жана башка.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
Акырында формуланы функциянын ичине киргизебиз КАТА (IFERROR), анткени сиз ката билдирүүсүнө ыраазы боло албайсыз #AT (#N/A) эгерде формула көчүрүлгөн уячалардын саны каралып жаткан диапазондогу кайталанма маанилердин санынан аз болсо.
Белгилүү сап жана тилке боюнча XNUMXD издөө
Excelде XNUMXD издөөнү аткаруу белгилүү сап жана тилке номери боюнча маанини издөөнү камтыйт. Башка сөз менен айтканда, сиз белгилүү бир саптын жана мамычанын кесилишинде уячанын маанисин чыгарып жатасыз.
Ошентип, таблицабызга кайрылып, функциясы бар формула жазалы VPR, анда март айында сатылган лимондун баасы тууралуу маалымат табат.
XNUMXD издөөнү жүргүзүүнүн бир нече жолу бар. Варианттарды карап чыгып, сизге эң ылайыктуусун тандаңыз.
VLOOKUP жана MATCH функциялары
Сиз бир топ функцияларды колдоно аласыз VPR (VLOOKUP) жана КӨБҮРӨГҮ АЧЫРЫЛГАН (MATCH) талаалардын кесилишиндеги маанини табуу үчүн продукт Name (сап) жана ай каралып жаткан массивдин (мамычасы):
=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)
Адам тилине которулган бул формула төмөнкүнү билдирет:
- Биз "Мар" каармандарын издеп жатабыз - аргумент search_value (издөө_маани);
- А1ден I1ге чейинки уячаларды кароо – аргумент издөө_массиви (издөө_массив);
- Так дал келүү - аргумент дал_түр (матч_түрү).
колдонуу менен 0 үчүнчү аргументте сиз функцияларды айтасыз КӨБҮРӨГҮ АЧЫРЫЛГАН сиз издеп жаткан мааниге дал келген биринчи маанини издеңиз. Бул мааниге барабар ЖАЛГАН (ЖАЛГАН) төртүнчү аргумент үчүн VPR.
Эки өлчөмдүү издөө же эки багыттуу издөө деп да белгилүү болгон Excelде эки тараптуу издөө формуласын ушундай жол менен түзө аласыз.
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 формулаларын билбесеңиз, сизге бул визуалдык жана эсте каларлык жол жагат:
- Таблицаны тандаңыз, өтмөктү ачыңыз Formulas (Формулалар) жана басыңыз Тандоодон түзүү (Тандоодон түзүү).
- Кутучаларды белгилеңиз Жогорку сап (жогорку сапта) жана Сол тилке (сол жактагы тилкеде). Microsoft Excel электрондук жадыбалыңыздын жогорку сапындагы жана сол тилкесиндеги маанилерден диапазонго аттарды дайындайт. Эми сиз формулаларды түзбөстөн түздөн-түз ушул аталыштарды колдонуп издей аласыз.
- Каалаган бош уячага жазыңыз =саптын_аты мамычанын_аты, мисалы, бул сыяктуу:
= Лимондор Мар
… же тескерисинче:
= Мар Лимонс
Сап менен мамычанын аттары боштук менен бөлүнүшү керек экенин унутпаңыз, бул учурда кесилиш оператору сыяктуу иштейт.
Сиз ат киргизгениңизде, Microsoft Excel формуланы киргизгендей эле, дал келген аттардын тизмеси менен инструментти көрсөтөт.
- басма сөз кирүү жана натыйжаны текшерүү
Жалпысынан, жогорудагы ыкмалардын кайсынысын тандабаңыз, эки өлчөмдүү издөөнүн натыйжасы бирдей болот:
Бир формулада бир нече VLOOKUP колдонуу
Негизги таблица менен издөө таблицасында жалпы бир тилке жок болуп калат жана бул кадимки функцияны колдонууга жол бербейт. VPR. Бирок, бизди кызыктырган маалыматты камтыбаган дагы бир таблица бар, бирок негизги таблица жана издөө таблицасы менен жалпы тилкеси бар.
Келгиле, төмөнкү мисалды карап көрөлү. Бизде мамычасы бар негизги таблица бар SKU (жаңы), бул жерде сиз башка таблицадан тиешелүү баалар менен мамычаны кошкуңуз келет. Мындан тышкары, бизде 2 издөө таблицасы бар. Биринчиси (Издөө таблицасы 1) жаңыланган сандарды камтыйт SKU (жаңы) жана өнүмдөрдүн аталыштары, экинчиси (2-таблицаны издөө) - товардын аталыштары жана эски номерлери SKU (эски).
Экинчи издөө таблицасынан бааларды негизги таблицага кошуу үчүн, сиз double деп аталган аракетти аткарышыңыз керек 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 жана КЫЙЫР аркылуу ар кандай таблицалардагы маалыматтарды динамикалык алмаштыруу
Биринчиден, бири-бирибизди туура түшүнүү үчүн “Ар түрдүү таблицалардагы маалыматтарды динамикалык алмаштыруу” деген сөз менен эмнени билдире турганыбызды тактап алалы.
Бир эле форматтагы маалыматтары бар бир нече барак бар болгон жагдайлар бар жана берилген уячага киргизилген мааниге жараша белгилүү бир барактан керектүү маалыматты алуу зарыл. Муну бир мисал менен түшүндүрүү оңой деп ойлойм.
Ошол эле өнүмдөрдү жана бир форматта бир нече аймактар үчүн сатуу отчетторун бар деп элестетиңиз. Сиз белгилүү бир аймак үчүн сатуу көрсөткүчтөрүн тапкыңыз келет:
Эгер сизде ушундай эки гана отчет болсо, анда функциялары менен маскаралык жөнөкөй формуланы колдонсоңуз болот VPR и IF (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_Sales и 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болсо, 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;ЛОЖЬ)
Эгерде функция КЫЙЫР башка иш китебин билдирет, ал иш китеби ачык болушу керек. Эгерде ал жабык болсо, функция ката жөнүндө кабарлайт. #РЕФ! (#SSYL!).