Чрез комбиниране на функцията VLOOKUP на Excel с функцията COLUMN можете да създадете формула за търсене, която връща множество стойности от един ред на база данни или таблица с данни. Научете как да създадете формула за търсене, която връща множество стойности от един запис на данни.
Инструкциите в тази статия се отнасят за Excel 2019, 2016, 2013, 2010; и Excel за Microsoft 365.
Долен ред
Формулата за търсене изисква функцията COLUMN да бъде вложена във VLOOKUP. Влагането на функция включва въвеждане на втората функция като един от аргументите за първата функция.
Въведете данните за урока
В този урок функцията COLUMN е въведена като аргумент за номер на индекс на колона за VLOOKUP. Последната стъпка в урока включва копиране на формулата за търсене в допълнителни колони за извличане на допълнителни стойности за избраната част.
Първата стъпка в този урок е да въведете данните в работен лист на Excel. За да следвате стъпките в този урок, въведете данните, показани на изображението по-долу, в следните клетки:
- Въведете горния диапазон от данни в клетки от D1 до G1.
- Въведете втория диапазон в клетки D4 до G10.
Критериите за търсене и формулата за търсене, създадени в този урок, се въвеждат в ред 2 на работния лист.
Този урок не включва основното форматиране на Excel, показано на изображението, но това не засяга начина, по който работи формулата за търсене.
Създаване на именуван диапазон за таблицата с данни
Наименуваният диапазон е лесен начин за препратка към диапазон от данни във формула. Вместо да въвеждате препратките към клетка за данни, въведете името на диапазона.
Второ предимство на използването на именуван диапазон е, че референтните клетки за този диапазон никога не се променят, дори когато формулата се копира в други клетки в работния лист. Имената на диапазони са алтернатива на използването на абсолютни препратки към клетки за предотвратяване на грешки при копиране на формули.
Името на диапазона не включва заглавията или имената на полетата за данните (както е показано в ред 4), а само данните.
-
Маркирайте клетки D5 до G10 в работния лист.
Image -
Поставете курсора в полето за име, разположено над колона A, въведете Таблица, след което натиснете Enter. Клетките D5 до G10 имат името на диапазона на Таблица.
Image - Името на диапазона за аргумента на масива на таблицата на VLOOKUP се използва по-късно в този урок.
Отворете диалоговия прозорец VLOOKUP
Въпреки че е възможно формулата за търсене да се въведе директно в клетка в работен лист, за много хора е трудно да запазят синтаксиса ясен - особено за сложна формула като тази, използвана в този урок.
Като алтернатива използвайте диалоговия прозорец Аргументи на функцията VLOOKUP. Почти всички функции на Excel имат диалогов прозорец, където всеки от аргументите на функцията се въвежда на отделен ред.
-
Изберете клетка E2 от работния лист. Това е мястото, където ще се покажат резултатите от формулата за двуизмерно търсене.
Image -
На лентата отидете до раздела Формули и изберете Търсене и справка.
Image -
Изберете VLOOKUP, за да отворите диалоговия прозорец Аргументи на функцията.
Image - Диалоговият прозорец с аргументи на функцията е мястото, където се въвеждат параметрите на функцията VLOOKUP.
Въведете аргумента на търсената стойност
Обикновено търсената стойност съвпада с поле с данни в първата колона на таблицата с данни. В този пример стойността за търсене се отнася до името на частта, за която искате да намерите информация. Допустимите типове данни за търсената стойност са текстови данни, логически стойности, числа и препратки към клетки.
Абсолютни препратки към клетки
Когато формулите се копират в Excel, препратките към клетки се променят, за да отразят новото местоположение. Ако това се случи, D2, референтната клетка за търсената стойност, се променя и създава грешки в клетки F2 и G2.
Абсолютните препратки към клетки не се променят, когато формулите се копират.
За да предотвратите грешките, преобразувайте референтната клетка D2 в абсолютна референтна клетка. За да създадете абсолютна препратка към клетка, натиснете клавиша F4. Това добавя знаци за долар около препратката към клетката, като $D$2.
-
В диалоговия прозорец Аргументи на функцията поставете курсора в текстовото поле lookup_value. След това в работния лист изберете клетка D2, за да добавите препратка към тази клетка към търсена_стойност. Клетка D2 е мястото, където ще бъде въведено името на частта.
Image -
Без да местите точката на вмъкване, натиснете клавиша F4, за да преобразувате D2 в абсолютната референтна клетка $D$2.
Image - Оставете диалоговия прозорец на функцията VLOOKUP отворен за следващата стъпка в урока.
Въведете аргумента на табличния масив
Масивът от таблици е таблицата с данни, която формулата за търсене търси, за да намери информацията, която искате. Масивът на таблицата трябва да съдържа поне две колони с данни.
Първата колона съдържа аргумента за справочна стойност (който беше настроен в предишния раздел), докато втората колона се търси по формулата за търсене, за да се намери указаната от вас информация.
Аргументът на масива от таблицата трябва да бъде въведен или като диапазон, съдържащ препратките към клетките за таблицата с данни, или като име на диапазон.
За да добавите таблицата с данни към функцията VLOOKUP, поставете курсора в текстовото поле table_array в диалоговия прозорец и въведете Tableза въвеждане на името на диапазона за този аргумент.
Вградете функцията COLUMN
Обикновено VLOOKUP връща данни само от една колона на таблица с данни. Тази колона се задава от аргумента номер на индекс на колона. В този пример обаче има три колони и номерът на индекса на колоната трябва да се промени, без да се редактира формулата за търсене. За да постигнете това, вмъкнете функцията COLUMN във функцията VLOOKUP като аргумент Col_index_num.
Когато влага функции, Excel не отваря диалоговия прозорец на втората функция, за да въведе нейните аргументи. Функцията COLUMN трябва да се въведе ръчно. Функцията COLUMN има само един аргумент, аргументът Reference, който е препратка към клетка.
Функцията COLUMN връща номера на колоната, предоставена като аргумент за справка. Преобразува буквата на колоната в число.
За да намерите цената на артикул, използвайте данните в колона 2 на таблицата с данни. Този пример използва колона B като препратка за вмъкване на 2 в аргумента Col_index_num.
-
В диалоговия прозорец Аргументи на функцията поставете курсора в текстовото поле Col_index_num и въведете COLUMN(. (Не забравяйте да включите отворената кръгла скоба.)
Image -
В работния лист изберете клетка B1, за да въведете препратката към тази клетка като аргумент за препратка.
Image - Въведете затваряща кръгла скоба, за да завършите функцията COLUMN.
Въведете аргумента за търсене на диапазон на VLOOKUP
Аргументът 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, за да завършите формулата за търсене и да затворите диалоговия прозорец. Клетка E2 ще съдържа грешка N/A, тъй като критериите за търсене не са въведени в клетка D2. Тази грешка е временна. Ще бъде коригирано, когато критериите за търсене се добавят в последната стъпка на този урок.
Копирайте формулата за търсене и въведете критерии
Формулата за търсене извлича данни от множество колони на таблицата с данни наведнъж. За да направите това, формулата за търсене трябва да се намира във всички полета, от които искате информация.
За да извлечете данни от колони 2, 3 и 4 на таблицата с данни (цената, номера на частта и името на доставчика), въведете частично име като Lookup_value.
Тъй като данните са изложени по редовен модел в работния лист, копирайте формулата за търсене в клетка E2 в клетки F2 и G2 Докато формулата се копира, Excel актуализира относителната препратка към клетка във функцията COLUMN (клетка B1), за да отрази новото местоположение на формулата. Excel не променя абсолютната препратка към клетката (като $D$2) и наименования диапазон (Таблица), докато формулата се копира.
Има повече от един начин за копиране на данни в Excel, но най-лесният начин е да използвате манипулатора за попълване.
-
Изберете клетка E2, където се намира формулата за търсене, за да я направите активната клетка.
Image -
Плъзнете манипулатора за запълване до клетка G2. Клетки F2 и G2 показват грешката N/A, която присъства в клетка E2.
Image -
За да използвате формулите за търсене за извличане на информация от таблицата с данни, в работния лист изберете клетка D2, въведете Widget и натиснете Въведете.
Image Следната информация се показва в клетки E2 до G2.
- E2: $14,76 - цената на джаджа
- F2: PN-98769 - номерът на частта за джаджа
- G2: Widgets Inc. - името на доставчика на джаджи
-
За да тествате формулата на масива на VLOOKUP, въведете името на други части в клетка D2 и наблюдавайте резултатите в клетки от E2 до G2.
Image - Всяка клетка, съдържаща формулата за търсене, съдържа различна част от данните за хардуерния елемент, който сте търсили.
Функцията VLOOKUP с вложени функции като COLUMN предоставя мощен метод за търсене на данни в таблица, използвайки други данни като справка за търсене.
