Коришћењем формуле низа у Екцел-у можемо направити формулу за претраживање која користи више критеријума за проналажење информација у бази података или таблици података. Формула поља укључује урезивање функције МАТЦХ унутар функције ИНДЕКС.
Овај туторијал укључује корак по корак примера креирања формуле за претраживање која користи више критеријума за проналазак добављачатитаниум додатци у бази података узорака. Пратите како бисте научили колико је лако направити сопствене формуле за претраживање.
Припремите Екцел Воркбоок са подацима
Да бисте пратили кораке у овом упутству, морате започети уносом података узорака у следеће ћелије, како је приказано на слици изнад. Редови 3 и 4 остају празни да би се прилагодила форми низа креирана током овог туторијала.
- Унесите највише опсега података у ћелије Д1 до Ф2
- Унесите други опсег у ћелије Д5 до Ф11
Овај туторијал не укључује обликовање које се види на слици, али то неће утицати на то како функционира формула за претраживање. Информације о опцијама за обликовање сличне онима које су горе видљиве доступне су у овом Басиц Екцел форматирању туторијала.
02 од 08Креирајте ИНДЕКС функцију у Екцелу
Тхе ИНДЕКС функција је једна од ретких у Екцелу која има више облика. Функција има Формулар Арраи и а Референтни образац. Формулар Арраи враћа стварне податке из базе података или таблице података, док Референтни образац даје референту ћелије или локацију података у таблици.
У овом водичу ћемо користити Формулар Арраи јер желимо да упознамо име добављача за титанијумске видгетове, а не референцу ћелије овом добављачу у нашој бази података. Пратите ове кораке да бисте креирали своју ИНДЕКС функцију:
- Кликните на ћелију Ф3 да би она била активна ћелија - овде ћемо унети угнежену функцију.
- Кликните на Формуле таб на менију траке.
- Изаберите Претрага и референца из траке траке да отворите функцију пада.
- Кликните на ИНДЕКС на списку да се прикаже Изаберите Аргументи Дијалог.
- Изабрати арраи, ров_нум, цол_нум опцију у дијалогу.
- Кликните У реду да отворите оквир за дијалог ИНДЕКС; ово ће отворити Формула Буилдер у програму Екцел.
- У Формула Буилдер, кликните на Арраи лине.
- Означите ћелије Д6 до Ф11, кликом и повлачењем, на радном листу, да бисте унели опсег у градитељ.
У старијим верзијама Екцел-а Формула Буилдер биће замењен са Аргументи функције прозор. Користите исте кораке као што је наведено у овом упутству у прозор.
03 од 08Започните функцију уграђеног МАТЦХ-а
Када угнежете једну функцију у другу, није могуће отворити другу или уграђену формулу формуле о о уносити неопходне аргументе. Угнежена функција мора бити укуцана као једна од сортираних у првој функцији.
Када ручно уносите функције, аргументи функције су одвојени једни од других са зарезом.
Први корак у уласку у угнежену МАТЦХ функцију је да унесете Лоокуп_валуе расправа. Лоокуп_валуе ће бити референца локације или ћелије за израз који желимо да се подудара у бази података.
Обично Лоокуп_валуе прихвата само један критеријум претраживања или термин. Да бисмо претраживали више критеријума, морамо продужити Лоокуп_валуе; тњегова се врши повезивањем или повезивањем две или више ћелијских референци заједно користећи амперсанд симбол - &.
- У Формула Буилдер, кликните на Ров_нум лине.
- Укуцајте име функције УТАКМИЦА праћен отвореним округлим носачем.
- Кликните на ћелију Д3 да унесете референцу ћелије у дијалошки оквир.
- Укуцајте амперсанд након референце ћелије Д3 како би додали другу референцу ћелије.
- Кликните на ћелију Е3 да унесете ову другу референцу ћелије.
- Откуцајте зарез након референца ћелије Е3 да довршите унос функције МАТЦХ Лоокуп_валуе расправа.
У последњем кораку туториала, наша Лоокуп_валуес биће унете у ћелије Д3 и Е3 радног листа.
04 од 08Завршите функцију угнежене МАТЦХ
Овај корак покрива додавање Лоокуп_арраи аргумент за угнежени УТАКМИЦА функција. Лоокуп_арраи је опсег ћелија које ће МАТЦХ функција претраживати да пронађе аргумент Лоокуп_валуе додан у претходном кораку туториала.
Пошто смо идентификовали два поља за претрагу у аргументу Лоокуп_арраи, морамо то исто урадити и за Лоокуп_арраи. Функција МАТЦХ тражи само један низ за сваки наведени израз, тако да унесемо више низова, поново користимо амперсанд да заједно спојимо низове.
Ови кораци се уносе након што је запета унета у претходни корак на страници Ров_нум линија у ИНДЕКС функција.
- Кликните на Ров_нум линију после зареза да поставите тачку убацивања на крају тренутног уноса.
- Означите ћелије Д6 до Д11 на радном листу да бисте ушли у опсег - ово је први низ који је функција за претрагу.
- Укуцајте амперсанд након референци ћелије Д6: Д11 зато што желимо да функција тражи два поља.
- Означите ћелије Е6 до Е11 на радном листу да бисте ушли у опсег - ово је други низ који је функција за претраживање.
- Откуцајте зарез након референца ћелије Е3 да заврши унос УТАКМИЦА функције Лоокуп_арраи расправа.
- Оставити ИНДЕКС дијалог за функцију отворен за следећи корак у туторијалу.
Додајте МАТЦХ Типе Аргумент
Трећи и последњи аргумент функције МАТЦХ је Матцх_типе расправа; то говори Екцелу како да одговара Лоокуп_валуе са вредностима у Лоокуп_арраи - доступни избори су 1, 0 или -1.
Овај аргумент је необавезан. Ако је изостављено, функција користи подразумевану вредност од 1.
- Ако је Матцх_типе = 1 или је испуштен: МАТЦХ проналази највећу вриједност која је мања или једнака Лоокуп_валуе. Подаци Лоокуп_арраи морају бити сортирани по растућем редоследу.
- Ако је Матцх_типе = 0: МАТЦХ проналази прву вриједност која је тачно једнака Лоокуп_валуе. Подаци Лоокуп_арраи могу бити сортирани по било ком редоследу.
- Ако је Матцх_типе = -1: МАТЦХ проналази најмању вриједност која је већа или једнака Лоокуп_валуе. Подаци Лоокуп_арраи морају бити сортирани у падајућем редоследу.
Ови кораци се уносе након што је запета унета у претходни корак на страници Ров_нум линија у ИНДЕКС функција.
- После зареза на Ров_нум линију, унесите нулу - 0 - пошто желимо да угнежена функција врати тачна подударања са терминима који смо унели у ћелије Д3 и Е3.
- Укуцајте округлицу за затварање - ) - да довршите УТАКМИЦА функција.
- Оставити Формула Буилдер отворен за следећи корак у туторијалу.
Завршавање ИНДЕКС функције
Сад то УТАКМИЦА функција је завршена, прелазимо на трећу линију Формула Буилдера и унесите последњи аргумент за ИНДЕКС функција. Овај трећи и последњи аргумент је Цолумн_нум аргумент који Екцелу каже број колоне у опсегу Д6 до Ф11; овде се налази информација коју желимо вратити функцијом. У овом случају, добављач за титаниум видгетс.
- Кликните на Цолумн_нум лине.
- Унесите број три - 3 - на овој линији пошто тражимо податке у трећој колони овог опсега Д6 до Ф11.
- Опет, остави Формула Буилдер отворен за следећи корак у туторијалу.
Креирање Формуле Арраи
Пре него што затворите Формула Буилдер, потребно је претворити угнежену функцију у низ формула; ово омогућава нашој функцији да тражи више термина у таблици података. У овом упутству тражимо да се подударају два израза: Видгетс из колоне 1 и Титанијум из колоне 2.
Креирање низа формула у Екцелу се врши притиском на ЦТРЛ, СМЕНА, и ЕНТЕР тастере на тастатури у исто време. Када се притисне, функција ће бити окружена закривљеним зарезама, што указује на то да је функција сада низ.
- Затвори Формула Буилдер кликом на Готово дугме.
- Затим изаберите ћелију Ф4 и притисните Ентер кључ за преглед формуле.
- Да бисте Формулу претворили у низ, истовремено притисните ЦТРЛ + СХИФТ + ЕНТЕР на твојој тастатури.
- Ако се исправно уради а # Н / А грешка ће се појавити у ћелији Ф3 - ћелија у коју смо ушли у функцију.
- Тхе # Н / А грешка се појављује у ћелији Ф3 јер ћелије Д3 и Е3 су празни. Д3 и Е3 су ћелије у којима смо рекли функцији да пронађу Лоокуп_валуес. Када се подаци додају у ове две ћелије, грешка ће бити замењена информацијама из базе података.
Додавање критеријума претраге
Коначно, додати ћемо појмове за претрагу на наш радни лист. Као што је поменуто у претходном кораку, тражимо да се подударају са условима Видгетс из колоне 1 и Титанијум из колоне 2. Ако и само ако наша формула пронађе подударање за оба термина у одговарајућим ступцима у бази података, да ли ће враћати вредност из треће колоне.
- Кликните на ћелију Д3.
- Тип Видгетс и притисните Ентер на тастатури.
- Кликните на ћелију Е3.
- Тип Титанијум и притисните Ентер на тастатури.
- Име добављача Видгетс Инц. требало би да се појави у ћелији Ф3 - локацију функције јер је једини добављач наведен који продаје Титаниум Видгетс.
- Када кликнете на ћелију Ф3, комплетна функција се појављује у траци формуле изнад радног листа.
{= ИНДЕКС (Д6: Ф11, МАТЦХ (Д3 и Е3, Д6: Д11 и Е6: Е11, 0), 3)}
У нашем примеру, било је само једног добављача за титанијумске видгете. Ако постоји више од једног добављача, добављач који је први наведен у бази података враћа се од стране функције.
