01 от 06
Стартиране на вложената MATCH функция
Въвеждане на функцията MATCH като аргумент на кодовия индекс
Обикновено VLOOKUP връща данни само от една колона на таблица с данни и тази колона се определя от аргумента за кодово число на колоната .
В този пример обаче имаме три колони, за които искаме да намерим данни, за да можем лесно да променим номера на колоната, без да редактираме нашата формула за търсене.
Тук влиза в действие функцията MATCH. Това ще ни позволи да съчетаем номера на колоната с името на полето - или януари, февруари или март -, които въвеждаме в клетка E2 на работния лист.
Функции за вмъкване
Функцията MATCH, следователно, действа като аргумент на кодовия индекс на VLOOKUP.
Това се постига чрез вмъкване на функцията MATCH в рамките на VLOOKUP в реда Col_index_num на диалоговия прозорец.
Въвеждане на функцията MATCH ръчно
Когато вмъкваме функции, Excel не ни позволява да отворим диалоговия прозорец на втората функция, за да въведем нейните аргументи.
Следователно функцията MATCH трябва да бъде въведена ръчно в реда Col_index_num .
При ръчно въвеждане на функции, всеки от аргументите на функцията трябва да бъде разделен със запетая "," .
Инструкции Стъпки
Въвеждане на Аргумента на Lookup_value на функцията MATCH
Първата стъпка при въвеждане на вградената функция MATCH е да въведете аргумента Lookup_value .
Lookup_value ще бъде местоположението или референтната клетка за термина за търсене, който искаме да съпоставим в базата данни.
- В диалоговия прозорец на функцията VLOOKUP кликнете върху колоната Col_index_num .
- Въведете съвпадението на името на функцията, последвано от отворена кръгла конзола " ( "
- Кликнете върху клетка E2, за да въведете тази клетка в диалоговия прозорец.
- Въведете запетая "," след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_value на MATCH функцията.
- Оставете отворения диалогов прозорец на функцията VLOOKUP за следващата стъпка в урока.
В последната стъпка на урока, Lookup_values ще бъдат въведени в клетки D2 и E2 на работния лист .
02 от 06
Добавяне на Lookup_array за функцията MATCH
Добавяне на Lookup_array за функцията MATCH
Тази стъпка обхваща добавянето на аргумента Lookup_array за вложената MATCH функция.
Lookup_array е диапазонът от клетки, които функцията MATCH ще търси, за да намери аргумента Lookup_value , добавен в предишната стъпка на урока.
В този пример искаме функцията MATCH да търси клетки D5 до G5 за съвпадение с името на месеца, който ще бъде въведен в клетка E2.
Инструкции Стъпки
Тези стъпки трябва да бъдат въведени след запетаята, въведена в предишната стъпка на реда Col_index_num в диалоговия прозорец VLOOKUP функцията.
- Ако е необходимо, кликнете върху реда Col_index_num след запетаята, за да поставите точката за вмъкване в края на текущия запис.
- Маркирайте клетки D5 до G5 в работния лист, за да въведете тези референтни клетки като обхват, в който се търси функцията.
- Натиснете клавиша F4 на клавиатурата, за да промените този диапазон в абсолютни референтни клетки . Това ще направи възможно копирането на завършената формула за търсене на други места в работния лист в последната стъпка на урока
- Въведете запетая "," след референтната клетка E3, за да завършите въвеждането на аргумента Lookup_array на MATCH функцията.
03 от 06
Добавяне на типа на съвпадението и завършване на функцията MATCH
Добавяне на типа на съвпадението и завършване на функцията 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 в диалоговия прозорец VLOOKUP функцията.
- Следвайки втората запетая в колоната Col_index_num , въведете нула " 0 ", тъй като искаме вложената функция да върне точното съвпадение на месеца в клетката E2.
- Въведете затваряща кръгла скоба " ) ", за да завършите функцията MATCH.
- Оставете отворения диалогов прозорец на функцията VLOOKUP за следващата стъпка в урока.
04 от 06
Въвеждане на аргумента за търсене на обхвата VLOOKUP
Аргументът за търсене на обхвата
Argument Range_lookup на VLOOKUP е логическа стойност (само TRUE или FALSE), която показва дали искате VLOOKUP да намери точна или приблизителна съвпадение с Lookup_value.
- Ако TRUE или ако този аргумент е пропуснат, VLOOKUP връща или точното съвпадение с Lookup_value, или, ако точното съвпадение не е намерено, VLOOKUP връща следващата най-голяма стойност. За формулата да направите това, данните в първата колона на Table_array трябва да бъдат сортирани във възходящ ред .
- Ако FALSE, VLOOKUP ще използва точна съвпадение само с Lookup_value. Ако в първата колона на Table_array има две или повече стойности, които съответстват на стойността за търсене, се използва първата намерена стойност. Ако точното съвпадение не е намерено, се връща грешка # N / A.
В този урок, тъй като ние търсим продажбите за определен месец, ще определим Range_lookup, равен на False .
Инструкции Стъпки
- Кликнете върху диапазона Range_lookup в диалоговия прозорец
- Въведете думата " False" в този ред, за да покажете, че искаме VLOOKUP да върне точното съвпадение на данните, които търсим
- Кликнете върху OK, за да завършите двуизмерната формула за търсене и затваряне на диалоговия прозорец
- Тъй като все още не сме въвели критериите за търсене в клетки D2 и E2, ще бъде налице грешка # N / A в клетка F2
- Тази грешка ще бъде коригирана в следващата стъпка в урока, когато ще добавим критериите за търсене в следващата стъпка на урока.
05 от 06
Тестване на двупосочната формула за търсене
Тестване на двупосочната формула за търсене
За да използвате формулата за търсене в две посоки, за да намерите месечните данни за продажбите за различните бисквитки, изброени в таблицата, въведете името на бисквитката в клетка D2, месецът в клетка E2 и натиснете клавиша ENTER на клавиатурата.
Данните за продажбите ще бъдат показани в клетка F2.
Инструкции Стъпки
- Кликнете върху клетката D2 във вашия работен лист
- Въведете овесена каша в клетка D2 и натиснете клавиша ENTER на клавиатурата
- Кликнете върху клетката E2
- Въведете февруари в клетка E2 и натиснете клавиша ENTER на клавиатурата
- Стойността 1,345 щатски долара - сумата на продажбите за бисквити от овесена каша през февруари - трябва да се покаже в клетка F2
- На този етап вашият работен лист трябва да съответства на примера на страница 1 на този урок
- Изпробвайте по-нататък формулата за търсене, като въведете всяка комбинация от типовете "бисквитки" и месеците, присъстващи в table_array, и цифрите за продажбите трябва да се показват в клетка F2
- Последната стъпка в урока включва копиране на формулата за търсене с помощта на дръжката за запълване .
Ако има съобщение за грешка като #REF! в клетка F2, този списък с съобщения за грешки VLOOKUP може да ви помогне да определите къде е проблемът.
06 от 06
Копиране на Формулата за търсене с две размери с дръжката за пълнене
Копиране на Формулата за търсене с две размери с дръжката за пълнене
За да се улесни сравнението на данните за различни месеци или за различни "бисквитки", формулата за търсене може да бъде копирана в други клетки, така че да могат да се показват едновременно повече количества.
Тъй като данните се представят в редовен модел в работния лист, можем да копираме формулата за търсене в клетка F2 до клетка F3.
Тъй като формулата се копира, Excel ще актуализира относителните референтни клетки, за да отрази новото местоположение на формулата. В този случай D2 става D3 и E2 става E3,
Освен това Excel поддържа абсолютната клетка, така че абсолютният диапазон $ D $ 5: $ G $ 5 остава същият, когато формулата се копира.
Има повече от един начин за копиране на данни в Excel, но вероятно най-лесният начин е чрез използване на дръжката за запълване.
Инструкции Стъпки
- Кликнете върху клетката D3 във вашия работен лист
- Въведете овесена каша в клетка D3 и натиснете клавиша ENTER на клавиатурата
- Кликнете върху клетката E3
- Въведете Марк в клетка E3 и натиснете клавиша ENTER на клавиатурата
- Кликнете върху клетката F2, за да я превърнете в активна клетка
- Поставете курсора на мишката върху черния квадрат в долния десен ъгъл. Показалецът ще се промени на знак плюс "+" - това е дръжката за запълване
- Кликнете върху левия бутон на мишката и плъзнете дръжката за пълнене до клетката F3
- Освободете бутона на мишката и клетката F3 трябва да съдържа двуизмерната формула за търсене
- Стойността $ 1,287 - сумата на продажбите за бисквитите от овесена каша в месеца март - трябва да се покаже в клетка F3