Формула за претраживање Екцел са вишеструким критеријумима

Коришћењем формуле низа у Екцел-у можемо направити формулу за претраживање која користи више критеријума за проналажење информација у бази података или таблици података.

Формула поља укључује урезивање функције МАТЦХ унутар функције ИНДЕКС .

Овај туторијал укључује корак по корак пример креирања формула за претраживање која користи више критеријума за проналажење добављача титанијумских додатака у бази података узорака.

Пратећи кораке у туторијалним темама испод, креира и користи формулу која се види на слици изнад.

01 од 09

Унос података о туторијалу

Први корак у туторијалу је унос података у Екцел радни лист .

Да бисте пратили кораке у туторијалу, унесите податке приказане на слици изнад у следеће ћелије .

• Унесите највише опсега података у ћелије Д1 до Ф2
• Унесите други опсег у ћелије Д5 до Ф11

Редови 3 и 4 остају празни да би се прилагодила форми низа креирана током овог туторијала.

Овај туторијал не укључује обликовање које се види на слици, али то неће утицати на то како функционира формула за претраживање.

Информације о опцијама за обликовање сличне онима које су горе видљиве доступне су у овом Басиц Екцел форматирању туторијала.

02 од 09

Покретање ИНДЕКС функције

Функција ИНДЕКС је једна од ретких у програму Екцел која има више облика. Функција има Формулар Арраи и Референтни Формулар .

Формулар Арраи враћа стварне податке из базе података или таблице података, док Референтни образац даје референту ћелије или локацију података у таблици.

У овом туториалу ћемо користити Формулар Арраи јер желимо да сазнамо име добављача за титанијумске видгетове, а не референтну ћелију овом добављачу у нашој бази података.

Сваки образац има другу листу аргумената који се морају изабрати пре почетка функције.

Корак по корак

1. Кликните на ћелију Ф3 да бисте постали активна ћелија . Овдје ћемо унети угнежену функцију.
2. Кликните на картицу Формуле у менију траке .
3. Одаберите траку и референцу са траке да бисте отворили функцију падајуће листе.
4. Кликните на ИНДЕКС на листи да бисте приказали дијалог Селецт Аргументс .
5. Изаберете опцију арраи, ров_нум, цол_нум у дијалошком оквиру.
6. Кликните на дугме ОК да бисте отворили оквир за дијалог ИНДЕКС.

03 од 09

Уношење аргумента Арраи аргумента функције ИНДЕКС

Први аргумент је аргумент Арраи. Овај аргумент одређује распон ћелија које треба претраживати за жељене податке.

За овај туторијал овај аргумент ће бити наша база података узорака.

Корак по корак

1. У дијалогу ИНДЕКС функције кликните на линију Арраи .
2. Означите ћелије Д6 до Ф11 на радном листу да бисте ушли у опсег у дијалошки оквир.

04 од 09

Започињање функције угнежене МАТЦХ

Када гнежите једну функцију у другу, није могуће отворити дијалог прозора друге или угнежене функције да унесете неопходне аргументе .

Угнежена функција мора бити унета као један од аргумената прве функције.

У овом туторијалу, угнежена МАТЦХ функција и његови аргументи ће се унети у другу линију дијалога ИНДЕКС функције - линија Ров_нум .

Важно је напоменути да приликом уноса функција ручно, аргументи функције су одвојени једни од других зарезом "," .

Уношење аргумента Лоокуп_валуе аргумента МАТЦХ функције

Први корак у уносу угнежене функције МАТЦХ је да унесете аргумент Лоокуп_валуе .

Лоокуп_валуе ће бити референца локације или ћелије за израз који желимо да се подудара у бази података.

Обично Лоокуп_валуе прихвата само један критеријум претраживања или термин. Да бисмо претраживали више критеријума, морамо продужити Лоокуп_валуе .

Ово се ради повезивањем или повезивањем две или више референци ћелија користећи амперсанд симбол " & ".

Корак по корак

1. У дијалогу ИНДЕКС функције кликните на линију Ров_нум .
2. Укуцајте подударање имена функције, а затим отворени округли носач " ( "
3. Кликните на ћелију Д3 да бисте унели референцу ћелије у дијалошки оквир.
4. Укуцајте амперсанд " & " након ћелије референце Д3 како бисте додали другу референцу ћелије.
5. Кликните на ћелију Е3 да бисте унели ову другу референцу ћелије у дијалог бок.
6. Укуцајте зарез "," након референце Е3 ћелије да заврши унос функције Лоокуп_валуе МАТЦХ функције.
7. Оставите оквир за дијалог ИНДЕКС отворен за следећи корак у туторијалу.

У последњем кораку туториала Лоокуп_валуес ће се унети у ћелије Д3 и Е3 радног листа.

05 од 09

Додавање Лоокуп_арраи за МАТЦХ функцију

Овај корак покрива додавање аргумента Лоокуп_арраи за угнежену МАТЦХ функцију.

Лоокуп_арраи је опсег ћелија које ће МАТЦХ функција претраживати да пронађе аргумент Лоокуп_валуе додан у претходном кораку туториала.

Пошто смо идентификовали два поља за претрагу у аргументу Лоокуп_арраи , морамо то исто урадити и за Лоокуп_арраи . Функција МАТЦХ тражи само један низ за сваки наведени израз.

Да унесемо више поља, поново користимо амперсанд " & " да заједно спојимо низове.

Корак по корак

Ови кораци се уносе након уноса зарезе у претходном кораку на линији Ров_нум у дијалогу ИНДЕКС функције.

1. Кликните на линију Ров_нум након зареза да бисте поставили тачку убацивања на крају текућег уноса.
2. Истакните ћелије Д6 до Д11 на радном листу да бисте ушли у опсег. Ово је први низ који је функција за претрагу.
3. Укуцајте амперсанд " & " након референце ћелија Д6: Д11 јер желимо да функција тражи два поља.
4. Истакните ћелије Е6 до Е11 на радном листу да бисте ушли у опсег. Ово је други низ који је функција за претраживање.
5. Укуцајте зарез "," након референце Е3 ћелије да заврши унос функције Лоокуп_арраи МАТЦХ функције.
6. Оставите оквир за дијалог ИНДЕКС отворен за следећи корак у туторијалу.

06 од 09

Додавање типа утакмице и довршавање функције МАТЦХ

Трећи и завршни аргумент МАТЦХ-а је аргумент Матцх_типе.

Овај аргумент говори Екцелу како да одговори Лоокуп_валуе с вриједностима у лоокуп_арраи-у. Избор је: 1, 0 или -1.

Овај аргумент је необавезан. Ако је изостављено, функција користи подразумевану вредност од 1.

• ако је Матцх_типе = 1 или је испуштен: МАТЦХ проналази највећу вредност која је мања или једнака Лоокуп_валуе. Подаци Лоокуп_арраи морају бити сортирани по растућем редоследу.
• ако матцх_типе = 0: МАТЦХ проналази прву вриједност која је тачно једнака Лоокуп_валуе. Подаци Лоокуп_арраи могу бити сортирани по било ком редоследу.
• ако Матцх_типе = -1: МАТЦХ проналази најмању вредност која је већа или једнака Лоокуп_валуе. Подаци Лоокуп_арраи морају бити сортирани у падајућем редоследу.

Корак по корак

Ови кораци се уносе након уноса зарезе у претходном кораку на линији Ров_нум у дијалогу ИНДЕКС функције.

1. Након зареза на линији Ров_нум , унесите нулу " 0 ", јер желимо да угнежена функција врати тачне поклапање са терминима који уносимо у ћелије Д3 и Е3.
2. Укуцајте закључну конзолу " ) " да бисте довршили функцију МАТЦХ.
3. Оставите оквир за дијалог ИНДЕКС отворен за следећи корак у туторијалу.

07 од 09

Назад на функцију ИНДЕКС

Сада када је завршена функција МАТЦХ, прелазимо на трећу линију отвореног дијалога и уђемо у последњи аргумент за ИНДЕКС функцију.

Овај трећи и последњи аргумент је аргумент Цолумн_нум који указује Екцелу број колоне у опсегу Д6 до Ф11 где ће пронаћи информације које желимо вратити функцијом. У овом случају, добављач за титанијумске видгете .

Корак по корак

1. Кликните на линију Цолумн_нум у дијалошком оквиру.
2. Унесите број три " 3 " (без цитата) на овој линији, јер тражимо податке у трећој колони од опсега Д6 до Ф11.
3. Немојте кликнути на ОК или затворити оквир за дијалог ИНДЕКС. Она мора остати отворена за следећи корак у туторијалу - креирање низа формула .

08 од 09

Креирање Формуле Арраи

Пре затварања дијалога потребно је претворити нашу угнежену функцију у низ формула .

Формула низова омогућава оно што тражи више термина у табели података. У овом туторијалу желимо да подударамо са два појма: Видгет из колоне 1 и титана из колоне 2.

Креирање формула формула у Екцелу се врши притиском тастера ЦТРЛ , СХИФТ и ЕНТЕР на тастатури истовремено.

Ефекат притискања ових тастера је да окружите функцију завртањима: {} показујући да је сада формула низа.

Корак по корак

1. Са завршеним дијалогом који се и даље отвара са претходног корака овог туторијала, притисните и задржите тастере ЦТРЛ и СХИФТ на тастатури, а затим притисните и пустите тастер ЕНТЕР .
2. Ако се исправно изврши, дијалог се затвори и # Н / А грешка ће се појавити у ћелији Ф3 - ћелија у коју смо унели функцију.
3. Грешка # Н / А се појављује у ћелији Ф3 јер су ћелије Д3 и Е3 празне. Д3 и Е3 су ћелије у којима смо рекли функцији да нађемо Лоокуп_валуес у кораку 5 туториала. Када се подаци додају у ове две ћелије, грешка ће бити замењена информацијама из базе података .

09 од 09

Додавање критеријума претраге

Последњи корак у туторијалу је додавање појмова за претрагу на наш радни лист.

Као што је поменуто у претходном кораку, тражимо да се подударају са терминима Видгетс из колоне 1 и Титаниум из колоне 2.

Ако и само ако наша формула пронађе подударање за оба термина у одговарајућим ступцима у бази података, да ли ће вратити вредност из треће колоне.

Корак по корак

1. Кликните на ћелију Д3.
2. Укуцајте Видгет и притисните тастер Ентер на тастатури.
3. Кликните на ћелију Е3.
4. Укуцајте Титаниум и притисните тастер Ентер на тастатури.
5. Добављач име Видгетс Инц би требало да се појави у ћелији Ф3 - локацију функције јер је једини добављач наведен који продаје Титаниум Видгетс.
6. Када кликнете на ћелију Ф3 комплетну функцију
   {= ИНДЕКС (Д6: Ф11, МАТЦХ (Д3 и Е3, Д6: Д11 и Е6: Е11, 0), 3)}
   појављује се у траци формуле изнад радног листа .

Напомена: У нашем примеру постоји само један добављач за титанијумске видгете. Ако постоји више од једног добављача, добављач који је први наведен у бази података враћа се од стране функције.