✅ 6 формули за търсене в Excel

Съдържание

Всички познаваме известната звезда на функциите на Excel VLOOKUP. Обикновено се използва за търсене на стойности с уникален идентификатор. Но това не е единствената функция, която може да се използва за търсене на стойности в Excel. Има много други функции и формули, които могат да се използват за търсене на стойност. В тази статия ще ви запозная с всички тези функции и формули за търсене в Excel. Някои са дори по -добри от функцията VLOOKUP в Excel. Така че, прочетете до края.

1. Функцията Excel VLOOKUP

Първата функция за търсене в Excel е, разбира се, функцията VLOOKUP. Тази функция е известна по някаква причина. Можем да използваме тази функция, за да направим нещо повече от просто търсене. Но основната задача на тази функция е да търси стойности в таблицата, отляво надясно.

Синтаксис на функцията VLOOKUP:

= VLOOKUP (lookup_value, таблица_масив, col_index_number, [range_lookup])

Lookup_value: Стойността, по която искате да търсите в първата колона на масива от таблици.

Таблица_масив: Таблицата, в която искате да търсите/търсите

col_index_number: Номерът на колоната в масива от таблици, от който искате да извлечете резултати.

[range_lookup]: FALSE, ако искате да търсите точна стойност, TRUE, ако искате приблизително съвпадение.

Предимства на VLOOKUP:

Лесен за използване.
Бърз
Многократна употреба
Най -доброто за търсене на стойности във вертикален ред.

Недостатъци:

Използва се само за вертикално търсене
Връща само първата съвпадаща стойност.
Статично, докато не се използва с функция MATCH.
Не могат да се търсят стойности отляво на стойността за търсене.

Можете да прочетете подробно за тази формула за търсене в Excel тук.

2. Функцията Excel HLOOKUP

Функцията HLOOKUP е липсващата част от функцията VLOOKUP. Функцията HLOOKUP се използва за търсене на стойности хоризонтално. С други думи, когато искате да потърсите стойност в Excel чрез съвпадение на стойността в колони и да получите стойности от редове, тогава използваме функцията HLOOKUP. Това е точно обратното на функцията VLOOKUP.

Синтаксис на HLOOKUP

=HLOOKUP(стойност за търсене, масив от таблици, номер на индекс на ред, [range_lookup])

стойност на търсене: Стойността, която търсите.
Табличен масив: Таблицата, в която търсите стойността.
Номер на индекс на ред: Номерът на реда в таблицата, от който искате да извлечете данни.
[range_lookup]: нейният тип съвпадение. 0 за точното съвпадение и 1 за приблизително съвпадение.

Предимства на функцията HLOOKUP:

Той може да търси стойности хоризонтално.
Лесен за използване.
Многократна употреба
Бърз

Недостатъци:

Използва се само за хоризонтално търсене
Той връща само първата съвпадаща стойност.
Статично, докато не се използва с функцията MATCH.
Не могат да се търсят стойности над стойностите за търсене в таблицата.

Можете да научите за тази функция за търсене в Excel тук.

3. Формулата за търсене на INDEX-MATCH

Когато VLOOKUP и HLOOKUP не могат да достигнат, тази формула може да достигне. Това е най -добрата формула за търсене в Excel до Excel 2016 (XLOOKUP е на път).

Общата формула на INDEX MATCH

= INDEX (Диапазон на резултата, MATCH (lookup_value, диапазон на търсене, 0))

Result_Range: Това е диапазонът от диапазони, от който искате да извлечете стойност.

Lookup_value: Това е стойността, която искате да съпоставите.

Lookup_Range:Това е диапазон, в който искате да съответствате на стойността за търсене.

Предимства на формулата за търсене на INDEX-MATCH:

Може да се търси в четири посоки. Той може да търси стойности отляво и нагоре от стойността за търсене.
Динамичен.
Няма нужда да дефинирате индекса на ред или колона.

Недостатъци:

Може да е трудно за новите потребители.
Използва две функции на Excel в комбинация. Потребителите трябва да разберат работата на функцията INDEX и MATCH.

Можете да научите за тази формула тук.

4: Формула за търсене на Excel OFFSET-MATCH

Това е друга формула, която може да се използва за динамично търсене на стойности. Тази формула за търсене на Excel използва функцията OFFSET като функция за закрепване и MATCH като функция за подаване. Използвайки тази формула, можем динамично да извличаме стойности от таблица, като търсим в редове и колони.

Обща формула,

= OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0))

StartCell:Това е началната клетка на таблицата за търсене. Да речем, ако искате да потърсите в обхват A2: A10, тогава StartCell ще бъде A1.

RowLookupValue: Това е стойността за търсене, която искате да намерите в редове подStartCell.

RowLookupRange:Това е диапазонът, в който искате да потърсите RowLookupValue. Това е диапазонът по -долу StartCell (A2: A10).

ColLookupValue: Това е стойността за търсене, която искате да намерите в колони (заглавки).

ColLookupRange:Това е диапазонът, в който искате да потърсите ColLookupValue. Това е диапазонът от дясната страна на StartCell (като B1: D1).

Предимства на тази техника за търсене в Excel:

Бърз
Може да търси хоризонтално и вертикално.
Динамичен

Недостатъци:

Комплекс за някои хора.
Трябва да разберете работата на функцията OFFSET и MATCH.

Можете да научите подробно за тази формула за търсене тук.

5: Формула Excel LOOKUP Множество стойности

Всички горепосочени формули за търсене връщат първата намерена стойност от масива. Ако има повече от един мач, те няма да върнат други мачове. В този случай тази формула влиза в действие, за да спаси деня. Тази формула връща всички съвпадащи стойности от списъка, вместо само първото съвпадение.

Тази формула използва функциите INDEX, ROW и IF като основни. Функцията IFERROR може да се използва по избор за обработка на грешки.

Обща формула

{= INDEX (масив, малък (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (първа клетка от lookup_value_range) +1), ROW (1: 1)))}

Масив: Обхватът, от който искате да извлечете данни.
lookup_value: Вашата lookup_value, която искате да филтрирате.
lookup_value_range: Диапазонът, в който искате да филтрирате lookup_value.
Първата клетка в диапазона lookup_value: ако вашият диапазон lookup_value е $ A $ 5: $ A $ 100, тогава неговите $ A $ 5.
Важно: Всичко трябва да бъде абсолютно рефериран. lookup_value може да бъде относително според изискването.
Въведете го като формула на масив. След като напишете формула, натиснете CTRL+SHIFT+ENTER, за да я направите формула за масив.

Както можете да видите в gif, той връща всички съвпадения от таблицата на Excel.

Предимства:

Връща с множество съвпадащи стойности от таблицата на Excel.
Динамичен

Недостатъци:

Това е твърде сложно, за да може новият потребител да разбере.
Използва формула на масива
Трябва да дефинирате възможния брой изходи и да приложите тази формула като формула за многоклетъчен масив (Не в Excel 2019 и 365).
Бавен.

Можете да научите за тази формула тук подробно.

6: VLOOKUP-CHOOSE Търсене Формула на Excel

Така че повечето хора казват, че не е възможно да се търсят стойности отляво на стойността за търсене в Excel с помощта на функцията VLOOKUP. Съжалявам да кажа, но те грешат. Можем да търсим вляво от стойността за търсене в Excel, използвайки функцията VLOOKUP с помощта на функцията CHOOSE.

Обща формула:

= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)

lookup_value : стойност за търсене

lookup_range : range, къде да търсите lookup_value

req_range : диапазон, където се изисква съответната стойност

2 : втора колона, брой представляващ req_range

0 : потърсете точното съвпадение

В тази формула ние по същество създаваме виртуална таблица във формулата, използвайки функцията CHOOSE. Функцията CHOOSE създава таблица от две колони. Първата колона съдържа диапазона за търсене, а втората колона съдържа диапазона от резултати.

Предимства на формулата за търсене VLOOKUP-CHOOSE:

Можете да търсите вляво от стойността за търсене
Бърз
Лесно

Недостатъци:

Функцията CHOOSE се използва рядко. Потребителите трябва да разберат как работи.

Можете да научите за тази формула за търсене тук.

Така че да, момчета, това са различните функции и формули за търсене. Това не е всичко. В Excel може да има много повече формули за търсене, които могат да бъдат създадени с помощта на различни комбинации от формули на Excel. Ако имате някакви специални техники за търсене, моля споделете в секцията за коментари по -долу. Ще го включим в нашата статия с вашето име.

Надявам се да е било полезно и информативно. Ако имате някакви съмнения относно тази статия, попитайте ме в секцията за коментари по -долу.

10+ нови функции в Excel 2019 и 365: Въпреки че функциите, налични в Excel 2016 и по -стари, са достатъчни за изработване на всякакъв вид изчисления и автоматизация, понякога формулите стават сложни. Този вид незначителни, но важни неща се решават в Excel 2019 и 365.

17 неща за Excel VLOOKUP: VLOOKUP просто не е формула за търсене, а повече от това. VLOOKUP има много други способности и може да се използва за множество цели. В тази статия ние изследваме всички възможни приложения на функцията VLOOKUP.

10+ творчески разширени Excel диаграми, които да разтърсят вашето табло за управление: Excel е мощен инструмент за визуализация на данни, който може да се използва за създаване на зашеметяващи диаграми в Excel. Тези 15 разширени класации на Excel могат да бъдат използвани, за да хипнотизират вашите колеги и шефове.

4 Creative Target Vs таблици за постижения в Excel: Графиките за целта спрямо постиженията са най -основните и важни диаграми във всеки бизнес. Затова е по -добре да ги поставите по възможно най -креативния начин. Тези 4 творчески класации могат да накарат вашите табла да разтърсят презентацията.

Популярни статии:

50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.

Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.

Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.

Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.