Чрез използване на матрична формула в Excel можем да създадем формула за търсене, която използва множество критерии за намиране на информация в база данни или таблица с данни. Формулата за масив включва вграждането на функцията MATCH в рамките на функцията INDEX.
Този урок включва стъпка по стъпка пример за създаване на формула за търсене, която използва множество критерии за намиране на доставчиктитанови джаджи в примерна база данни. Следвайте по-долу, за да научите колко лесно е да създадете свои собствени персонализирани формули за търсене.
Подгответе работната си книга в Excel с данни
За да изпълните стъпките в този урок, трябва да започнете като въведете примерните данни в следните клетки, както е показано на изображението по-горе. Редове 3 и 4 остават празни, за да могат да се приспособят към формулата на масива, създадена по време на този урок.
- Въведете най-горния диапазон от данни в клетките D1 да се F2
- Въведете втория обхват в клетките D5 да се F11
Урокът не включва форматирането, което се вижда в изображението, но това няма да повлияе на начина на работа на формулата за търсене. Информация за опциите за форматиране, подобни на тези, показани по-горе, можете да намерите в това базово обучение за форматиране на Excel.
02 от 08Създайте INDEX функция в Excel
Най- INDEX функцията е една от малкото в Excel, която има няколко формуляра. Функцията има Формуляр за масив и a Референтна форма, Формулярът Array връща действителните данни от база данни или таблица с данни, докато Референтната форма ви дава референтната клетка или местоположението на данните в таблицата.
В този наръчник ще използваме формуляра за масив, тъй като искаме да узнаем името на доставчика за титанови джунджурии, а не позоваването на клетката на този доставчик в нашата база данни. Изпълнете следните стъпки, за да създадете своята INDEX функция:
- Кликнете върху клетката F3 за да я превърнем в активна клетка - това е мястото, където ще влезем в вложената функция.
- Кликнете върху Формули в менюто на лентата.
- Избирам Търсене и справка от лентата с ленти, за да отворите падащото меню на функцията.
- Кликнете върху INDEX в списъка, за да изведете Изберете Аргументи диалогов прозорец.
- Избери масив, ред_на_но, col_num в диалоговия прозорец.
- Кликнете Добре за да отворите диалоговия прозорец на функцията INDEX; това ще отвори формуляра Builder в Excel.
- В Формула Builder, кликнете върху Array линия.
- Маркирайте клетки D6 да се F11, като кликнете и плъзнете, в работния лист, за да въведете диапазона в строителя.
В по - старите версии на Excel Формула Builder ще бъдат заменени с Функции на аргументите прозорец. Направете същите стъпки, които са описани в този урок, в прозореца.
03 от 08Стартирайте функцията за вмъкване на MATCH
При вмъкване на една функция в друга не е възможно да се отвори вторият или вградената функция за изграждане на формули o да въведете необходимите аргументи. Вложената функция трябва да бъде въведена като една от сортираните в първата функция.
При ръчно въвеждане на функции аргументите на функцията се разделят една от друга с запетая.
Първата стъпка при въвеждане на вложената MATCH функция е да въведете Lookup_value аргумент. Lookup_value ще бъде местоположението или референтната клетка за термина за търсене, който искаме да съпоставим в базата данни.
Обикновено Lookup_value приема само един критерий или термин за търсене. За да търсите няколко критерия, трябва да разширим Lookup_value; Tнеговата работа се осъществява чрез свързване или свързване на две или повече референтни клетки заедно с помощта на символа амперсанд - &.
- В Формула Builder, кликнете върху ROW_NUM линия.
- Въведете името на функцията МАЧ следвана от отворена кръгла конзола.
- Кликнете върху клетката D3 за да въведете тази клетка в диалоговия прозорец.
- Въведете ampersand след референтната клетка D3 за да добавите втора клетка за справка.
- Кликнете върху клетката E3 за да влезете в тази втора клетка.
- Въведете запетая след референтната клетка E3 за да завършите въвеждането на функцията MATCH Lookup_value аргумент.
В последната стъпка на урока, нашият Lookup_values ще бъдат въведени в клетки D3 и E3 на работния лист.
04 от 08Завършете функцията за вмъкване на MATCH
Тази стъпка обхваща добавянето на масив_търсене аргумент за вложените МАЧ функция. Lookup_array е диапазонът от клетки, които функцията MATCH ще търси, за да намери аргумента Lookup_value, добавен в предишната стъпка на урока.
Тъй като идентифицирахме две полета за търсене в аргумента Lookup_array, трябва да направим същото за Lookup_array. Функцията MATCH търси само един масив за всеки определен термин, като по този начин влиза в множество масиви, ние отново използваме ampersand, за да обединим масивите заедно.
Тези стъпки трябва да бъдат въведени след запетаята, въведена в предишната стъпка на ROW_NUM линия в INDEX функция.
- Кликнете върху ROW_NUM след запетая, за да поставите точката за вмъкване в края на текущия запис.
- Маркирайте клетки D6 да се D11 в работния лист, за да влезете в диапазона - това е първият масив, който трябва да търсите.
- Въведете ampersand след референтните клетки D6: D11 защото искаме функцията да търси две масиви.
- Маркирайте клетки E6 да се E11 в работния лист, за да влезете в диапазона - това е вторият масив, който трябва да търсите.
- Въведете запетая след референтната клетка E3 за да завършите въвеждането на МАЧ функция на масив_търсене аргумент.
- Остави INDEX отворен за следващата стъпка в урока.
Добавете аргумента тип MATCH
Третият и последен аргумент на функцията MATCH е Match_type аргумент; тя казва на Excel как да съвпадне с Lookup_value с стойности в Lookup_array - наличните опции са 1, 0 или -1.
Този аргумент е по избор. Ако е пропусната функцията използва стойността по подразбиране от 1.
- Ако Match_type = 1 или е пропуснат: MATCH намира най-голямата стойност, която е по-малка или равна на Lookup_value. Данните от Lookup_array трябва да бъдат сортирани във възходящ ред.
- Ако Match_type = 0: MATCH намира първата стойност, която е точно равна на Lookup_value. Данните от Lookup_array могат да бъдат сортирани в произволен ред.
- Ако Match_type = -1: MATCH намира най-малката стойност, която е по-голяма или равна на Lookup_value. Данните от Lookup_array трябва да бъдат сортирани в низходящ ред.
Тези стъпки трябва да бъдат въведени след запетаята, въведена в предишната стъпка на ROW_NUM линия в INDEX функция.
- Следвайки запетаята на ROW_NUM line, въведете нула - 0 - тъй като искаме вградената функция да върне точните съвпадения на въведените в клетките термини D3 и E3.
- Въведете затваряща кръгла конзола - ) - да завършите МАЧ функция.
- Остави Формула Builder отворете за следващата стъпка в урока.
Завършване на функцията INDEX
Сега, че МАЧ функцията се извършва, ще преминем към третия ред на Formula Builder и ще въведем последния аргумент за INDEX функция. Този трети и последен аргумент е колона_номер аргумент, който показва на Excel числото на колоната в диапазона D6 да се F11; това е мястото, където ще намери информацията, която искаме да бъде върната от функцията. В този случай доставчик за титанови джаджи.
- Кликнете върху колона_номер линия.
- Въведете номер три - 3 - на този ред, тъй като търсим данни в третата колона от диапазона D6 да се F11.
- Отново, оставете Формула Builder отворете за следващата стъпка в урока.
Създаване на формулата на масива
Преди да затворите Формула Builder, трябва да превърнем нашата вложена функция в масивна формула; това е, което позволява на нашата функция да търси няколко термини в таблицата с данни. В този урок искаме да съчетаем два термина: Widgets от колони 1 и титан от колона 2.
Създаването на формула за масив в Excel става чрез натискане на CTRL, SHIFT, и ENTER клавишите на клавиатурата едновременно. След като бъде натиснат, функцията ще бъде заобиколена от къдрави скоби, което означава, че функцията вече е масив.
- Затвори Формула Builder като кликнете върху Свършен бутон.
- След това изберете клетка F4 и натиснете бутона Въведете за да видите формулата.
- За да превърнете Формула в масив, едновременно натиснете CTRL + SHIFT + ENTER на вашата клавиатура.
- Ако е направено правилно a # N / A ще се появи грешка в клетка F3 - клетката, в която въведохме функцията.
- Най- # N / A в клетката се появява грешка F3 защото клетките D3 и E3 са празни. D3 и E3 са клетките, за които сме казали функцията да намерим Lookup_values, След като данните бъдат добавени към тези две клетки, грешката ще бъде заменена с информация от базата данни.
Добавяне на критериите за търсене
И накрая, ще добавим условията за търсене към нашия работен лист. Както бе споменато в предишната стъпка, ние се стремим да постигнем съответствие с условията Widgets от колони 1 и титан от колона 2. Ако и само ако формулата ни намери съвпадение и за двата термина в съответните колони в базата данни, тя ще върне стойността от третата колона.
- Кликнете върху клетката D3.
- Тип Widgets и натиснете бутона Въведете на клавиатурата.
- Кликнете върху клетката E3.
- Тип титан и натиснете бутона Въведете на клавиатурата.
- Името на доставчика Widgets Inc. трябва да се появи в клетка F3 - местоположението на функцията, тъй като тя е единственият доставчик, който продава Titanium Widgets.
- Когато кликнете върху клетката F3, пълната функция се появява в лентата за формули над работния лист.
{= ИНДЕКС (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
В нашия пример имаше само един доставчик на джаджи за титан. Ако има повече от един доставчик, доставчикът, посочен първо в базата данни, се връща от функцията.
