Формула за пребарување на Excel со повеќе критериуми

Со користење на формула за низа во Excel можеме да создадеме формула за пребарување која користи повеќе критериуми за да пронајдеме информации во базата на податоци или табелата со податоци.

Формата на низата вклучува вгнездување на функцијата MATCH во функцијата INDEX .

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

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

01 од 09

Внесување на податоците за прирачникот

Првиот чекор во упатството е да ги внесете податоците во работниот лист на Excel.

За да ги следите чекорите во упатството, внесете ги податоците прикажани на сликата погоре во следните ќелии .

• Внесете го горниот опсег на податоци во ќелиите D1 до F2
• Внесете го вториот опсег во ќелиите D5 до F11

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

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

Информации за опциите за форматирање слични на оние видени погоре се достапни во овој основен упатство за форматирање Excel.

02 од 09

Почнувајќи ја функцијата INDEX

Функцијата ИНДЕКС е една од ретките во Excel која има повеќе форми. Функцијата има образец за низа и образец за обраќање .

Образецот Array ги враќа вистинските податоци од базата на податоци или табелата со податоци, додека обрасците ја даваат референцата на ќелијата или локацијата на податоците во табелата.

Во ова упатство ќе ја употребиме формата Array, бидејќи сакаме да го знаеме името на добавувачот за титаниумски додатоци, а не референцата на ќелиите на овој добавувач во нашата база на податоци.

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

Чекори за чекори

1. Кликнете на ќелијата F3 за да ја направите активната ќелија . Ова е местото каде што ќе влеземе во вгнездена функција.
2. Кликнете на јазичето Формула на менито на лентата .
3. Изберете Пронајди и референца од лентата за да ја отворите функцијата паѓачката листа.
4. Кликнете на INDEX во списокот за да го прикажете дијалоговото поле Select Arguments .
5. Изберете ја низата, row_num, col_num опцијата во полето за дијалог.
6. Кликнете на OK за да го отворите дијалогот за INDEX функција.

03 од 09

Внесување на аргументот за низа на индекс

Првиот аргумент е аргументот Array. Овој аргумент го одредува опсегот на ќелии кои ќе бидат пребарувани за посакуваните податоци.

За ова упатство овој аргумент ќе биде нашата база на податоци за примероци.

Чекори за чекори

1. Во дијалогот за функцијата INDEX, кликнете на линијата Array .
2. Означете ги ќелиите од D6 до F11 во работниот лист за да го внесете опсегот во полето за дијалог.

04 од 09

Започнување на функцијата Вгнездрена MATCH

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

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

Во ова упатство, вгнездена функцијата MATCH и неговите аргументи ќе бидат внесени во втората линија на дијалогот INDEX функција - линија Row_num .

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

Внесување на аргумент Lookup_value на MATCH Функцијата

Првиот чекор при внесувањето на вгнездена функцијата MATCH е да го внесете аргументот Lookup_value .

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

Нормално Lookup_value прифаќа само еден критериум за пребарување или термин. За да бараме повеќе критериуми, мораме да ја прошириме Lookup_value .

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

Чекори за чекори

1. Во дијалогот за функцијата INDEX, кликнете на линијата Row_num .
2. Внесете го совпаѓањето со името на функцијата проследено со отворен круг заградата " ( "
3. Кликнете на ќелијата D3 за да ја внесете референцата на ќелијата во полето за дијалог.
4. Напишете ampersand " & " по клеточната референца D3 за да додадете втора референца на ќелијата.
5. Кликнете на ќелијата Е3 за да ја внесете оваа втора референца на ќелијата во дијалогот.
6. Напишете запирка "," по референцата Е3 за да го комплетирате внесот на аргументот Lookup_value на MATCH .
7. Оставете го дијалогот за INDEX функција отворена за следниот чекор во упатството.

Во последниот чекор од туториал Lookup_values ​​ќе бидат внесени во ќелиите D3 и E3 на работниот лист.

05 од 09

Додавање на Lookup_array за функцијата MATCH

Овој чекор опфаќа додавање на аргумент Lookup_array за вгнездена функцијата MATCH.

Lookup_array е опсегот на ќелии што функцијата MATCH ќе ја бара за да го пронајде аргументот Lookup_value додаден во претходниот чекор од упатството.

Бидејќи идентификувавме две полиња за пребарување во Lookup_array аргументот, мора да го сториме истото за Lookup_array . Функцијата MATCH бара само една низа за секој определен термин.

За да внесете повеќекратни низи, ние повторно го користиме амперсандот " & " за да ги конкатираме низите заедно.

Чекори за чекори

Овие чекори треба да се внесат по запирката внесена во претходниот чекор на линијата Row_num во дијалогот за функцијата INDEX.

1. Кликнете на линијата Row_num по запирката за да ја поставите точката за вметнување на крајот од тековниот запис.
2. Означете ги ќелиите D6 до D11 во работниот лист за да го внесете опсегот. Ова е првата низа на функцијата за пребарување.
3. Напишете ампернда " & " по референци на ќелијата D6: D11 затоа што сакаме функцијата да пребарува две низи.
4. Означете ги ќелиите E6 до E11 во работниот лист за да го внесете опсегот. Ова е втора низа која функцијата е да пребарува.
5. Напишете запирка "," по референцата E3 за да го комплетирате внесувањето на аргументот Lookup_array на MATCH функцијата.
6. Оставете го дијалогот за INDEX функција отворена за следниот чекор во упатството.

06 од 09

Додавање на типот на појавување и завршување на функцијата НАТПРЕВАР

Третиот и последен аргумент на функцијата MATCH е аргументот Match_type.

Овој аргумент му кажува на Excel како да одговара на Lookup_value со вредности во Lookup_array. Изборите се: 1, 0 или -1.

Овој аргумент е опционален. Ако е испуштено, функцијата користи почетна вредност од 1.

• ако Match_type = 1 или е испуштен: НАТПРЕВАР ја наоѓа најголемата вредност што е помала или еднаква на Lookup_value. Податоците Lookup_array мора да бидат подредени по растечки редослед.
• ако match_type = 0: MATCH ја наоѓа првата вредност која е точно еднаква на Lookup_value. Податоците Lookup_array можат да бидат подредени по кој било редослед.
• ако Match_type = -1: MATCH наоѓа најмалата вредност која е поголема или еднаква на Lookup_value. Податоците Lookup_array мора да бидат подредени по опаѓачки редослед.

Чекори за чекори

Овие чекори треба да се внесат по запирката внесена во претходниот чекор на линијата Row_num во дијалогот за функцијата INDEX.

1. Следејќи ги запирките на линијата Row_num , внеси нула " 0 " бидејќи сакаме вгнездената функција да ги врати точните совпаѓања со термините што ги внесуваме во ќелиите D3 и E3.
2. Напишете заклучок за затворање " ) " за да ја завршите функцијата НАТПРЕВАР.
3. Оставете го дијалогот за INDEX функција отворена за следниот чекор во упатството.

07 од 09

Врати се на функцијата INDEX

Сега, кога функцијата MATCH е завршена, ние ќе се преселиме во третата линија од дијалогот за отворен дијалог и ќе го внесеме последниот аргумент за функцијата ИНДЕКС.

Овој трет и последен аргумент е аргументот Column_num кој му кажува на Excel број на колони во опсегот D6 до F11 каде што ќе ги пронајде информациите што сакаме да ги врати функцијата. Во овој случај, снабдувач за титаниумски додатоци .

Чекори за чекори

1. Кликнете на линијата Column_num во полето за дијалог.
2. Внесете број три " 3 " (без цитати) на оваа линија, бидејќи ние бараме податоци во третата колона од опсегот D6 до F11.
3. Не кликнете OK или затворете го полето за дијалог INDEX. Мора да остане отворена за следниот чекор во туториал - создавање формула за низа .

08 од 09

Креирање на Формула за низа

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

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

Креирање формула за низа во Excel се врши со притискање на копчињата CTRL , SHIFT и ENTER на тастатурата во исто време.

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

Чекори за чекори

1. Со комплетираното дијалог прозорец сеуште отворен од претходниот чекор од ова упатство, притиснете и задржете ги копчињата CTRL и SHIFT на тастатурата, а потоа притиснете и ослободете го копчето ENTER .
2. Ако се направи правилно, дијалогот ќе се затвори и # N / A грешка ќе се појави во ќелијата F3 - ќелијата каде што влеговме во функцијата.
3. Грешка # N / A се појавува во ќелијата F3 бидејќи клетките D3 и E3 се празни. D3 и E3 се клетки каде што им кажавме на функцијата да ги пронајде Lookup_values ​​во чекор 5 од упатството. Откако ќе се додадат податоци за овие две ќелии, грешката ќе биде заменета со информации од базата на податоци .

09 од 09

Додавање на Критериуми за пребарување

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

Како што беше споменато во претходниот чекор, ние бараме да одговараат на термините Widgets од колона 1 и Титан од колоната 2.

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

Чекори за чекори

1. Кликнете на ќелијата D3.
2. Тип Виџети и притиснете го копчето Enter на тастатурата.
3. Кликнете на ќелијата Е3.
4. Внесете титаниум и притиснете го копчето Enter на тастатурата.
5. Името на добавувачот Widgets Inc треба да се појави во ќелијата F3 - локацијата на функцијата, бидејќи е единствениот добавувач на листата кој продава Титаниум додатоци.
6. Кога ќе кликнете на ќелијата F3 целосната функција
   {= ИНДЕКС (D6: F11, НАТПРЕВАР (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   се појавува во формуларот над работниот лист .

Забелешка: Во нашиот пример имаше само еден снабдувач за титаниумски додатоци. Ако имало повеќе од еден добавувач, снабдувачот кој е наведен прв во базата на податоци се враќа од функцијата.