Имате тези опции
Най -бързият и най -добрият начин за търсене на конкретни критерии е да използвате функцията VLOOKUP. С него е възможно да се намери стойност в Microsoft Excel за милисекунди.
VLOOKUP - така работи с критерий за търсене
За да разберат как VLOOKUP работи с множество критерии за търсене, потребителите на Excel първо трябва да разберат как функцията VLOOKUP е основно структурирана. Следващият пример показва броя на временните работници, наети във фирма през различните месеци на една календарна година.
Трябва да се използва VLOOKUP, за да се покаже колко временни работници са били наети през март и декември.
Формулата VLOOKUP в примера за декември е:
VLOOKUP (D8, A: B, 2, 0)
Формулата търси съответните номера за временни работници, както следва:
- Намерете стойността на D8 (месец декември) в колони A и B.
- Намерете стойността на колона В. Това е представено с 2 във формулата.
- 0 във формулата показва, че трябва да се търси точна стойност.
Функцията VLOOKUP търси един критерий за търсене. Матрицата на формулата е областта, в която се намират съответните стойности. В допълнение, колоната VLOOKUP трябва да съдържа стойността, която търсите.
Важно:
С функцията VLOOKUP критерият за търсене винаги трябва да е в колона 1. Търсените асоциирани стойности също трябва да бъдат показани вдясно от критерия за търсене. Ако електронната таблица е структурирана по различен начин, VLOOKUP няма да работи. В този случай комбинация от функциите INDEX и COMPARE може да бъде полезна при създаването на желаната заявка.
VLOOKUP с множество критерии за търсене - как да интегрирате функцията IF
В примера, показан по -горе, беше попитано колко временни работници са работили във фирма за определен месец. С функцията VLOOKUP специфичните стойности могат лесно да бъдат извлечени. В следния пример можете да видите обширен списък със статии с 36 записа [1], от която искате бързо и лесно да филтрирате подходящи номера на артикули въз основа на няколко критерия.
Критериите за търсене са изброени в електронната таблица вдясно. Намереният номер на артикула трябва да се появи в клетка H8.
Първата стъпка е да включите формула с VLOOKUP, която можете да използвате, за да намерите номера на артикул въз основа на критерий за търсене. След това разширявате тази формула стъпка по стъпка за другите критерии за търсене.
Използвайте следната формула в клетка H8, за да намерите номера на елемента за размера от клетка H3: [2]
= VLOOKUP (H3, A3: E40, 5
Формулата търси съответния номер на артикула по следния начин:
- Намерете стойността на H3 (размер 142) в колони от А до Е.
- Намерете стойността в колона E (номер на артикул). Това е представено с 5 във формулата.
В обобщение, функцията VLOOKUP предава пълния списък, в който търсите съдържание като първи аргумент. Критерият за търсене, който трябва да се търси в първата колона на прехвърлената област, е фокусиран като втори аргумент. Третият аргумент определя колоната, от която трябва да се върне резултатът.
Изпратете списъка, сортиран във възходящ ред въз основа на първата колона, за да може Excel да намери следващата по -ниска стойност. Формулата намира номера на артикула 2.253 от клетка E16. Тъй като размерът 142 от клетка H3 не се появява в списъка, се намира следващата по -ниска стойност 139.
Включете втори критерий за търсене
В следващата стъпка също искате да потърсите групата в клетка H4 като втори критерий. Трябва да се намери само номер на артикул, в който се намира търсената група.
За да направите това, добавете функция IF към формулата в клетка H8 и вмъкнете формулата като формула на масив:
= VLOOKUP (H3; IF (B3: B40 = H4; A3: E40; „“); 5)
Информация:
В Microsoft Excel се прави разлика между конвенционалните формули и по -мощните матрични формули. Матричните формули позволяват на потребителите на Excel да правят сложни изчисления. Те не могат да бъдат изпълнени със стандартните формули. Тъй като комбинацията от клавиши "CTRL + SHIFT + ENTER" трябва да бъде натисната след въвеждане на формулата, формулите на матрицата са международно известни като или CSE формули (CTRL + SHIFT + ENTER).
Потвърдете формулата, след като я въведете, като използвате комбинацията от клавиши Ctrl + Shift + Enter като формула на матрица. [3]
В резултат се доставя артикул номер 1.188. Формулата намира стойността 126 в колоната „Размер“. Това е най -голямата стойност в електронната таблица. Тя е по -малка или равна на търсената стойност 142 и има съдържание C2 по отношение на групата.
Функцията IF във формулата проверява всяка клетка в диапазона B3: B40 за съвпадение със съдържанието на клетка H4. Веднага след като има съвпадение, съответният ред в областта B3: E40 се предава на функцията VLOOKUP.
Лесно добавете допълнителни критерии за търсене по всяко време
Можете да добавите допълнителни критерии за търсене по същия начин. Можете да направите това, като вмъкнете повече IF функции във формулата. За да отговорите на четирите критерия за търсене, включете още две вложени IF функции във формулата в клетка H8:
= VLOOKUP (H3; IF (B3: B40 = H4; IF (C3: C40 = H5; IF (D3: D40 = H6; A3: E40; ““); ““); ““); 5)
След потвърждаване на формулата на матрицата с помощта на комбинацията от клавиши Ctrl + Shift + Enter, VLOOKUP връща номера на артикула 1.748. Това е числото от ред 14 за размер 125. Това е редът с най -високата стойност в колоната „Размер“, в който са изпълнени и трите допълнителни критерия. [4]
Първата функция IF не преминава веднага съответния ред към функцията VLOOKUP, ако проверката е положителна. Вместо това друга функция IF проверява дали има съвпадение с втория допълнителен критерий. Ако и тази проверка е положителна, се използва третата функция IF. Само когато и трите IF функции върнат положителна проверка, третата IF функция предава съответния ред на функцията VLOOKUP. По този начин можете да вложите до седем IF функции и по този начин да проверите до осем критерия.
Резюме: Ето един ефективен начин за търсене на множество критерии в Excel
Потребителите на Microsoft Excel постоянно са изправени пред въпроса как да четат данни и информация от обширни или вложени електронни таблици в ежедневната си работа. Потребителите на Excel ще намерят най -ефективната справочна функция във функцията VLOOKUP. Основно предимство на функцията VLOOKUP и други справочни функции е, че по подразбиране те търсят следващата по -ниска стойност, веднага щом търсената стойност не бъде намерена.
Тъй като функцията VLOOKUP е ограничена до един термин за търсене по подразбиране, трябва да се използва разширена формула за множество критерии за търсене. За да намерите няколко термина за търсене с помощта на функцията VLOOKUP, е подходяща формула на масив с IF функции. Той проверява отделните функции на IF една след друга и, ако резултатът е положителен, се позовава на VLOOKUP. Това гарантира, че правилният резултат се извежда за обширни електронни таблици с различни термини за търсене.
ЧЗВ
Какво може да направи функцията VLOOKUP?
Функцията VLOOKUP в Microsoft Excel е една от най -важните и ефективни функции в Excel. Чрез VLOOKUP можете автоматично да търсите в обширна електронна таблица за конкретна стойност. Резултатът се редактира и извежда в друга клетка.
Кога използвате функцията IF в EXCEL?
Функцията IF е една от най -широко използваните функции в Microsoft Excel. В първата стъпка функцията IF се използва за дефиниране на условие. Във втората стъпка свързвате IF например с функцията THEN и посочвате коя информация да бъде изведена. Функцията ELSE може да се използва и за определяне на алтернативния резултат. Практически пример: Ако служител А е работил повече от 240 дни в годината ТОГАВА бонус А ОЩЕ бонус Б.
Каква е разликата между формули за масиви и стандартни формули в Excel?
Най -известните в Excel са конвенционалните стандартни формули. В Excel матричните формули са мощни формули, с които е възможно да се правят сложни изчисления. За да се изпълни формула на масив, формулата на масива трябва да бъде потвърдена след въвеждането й с клавишната комбинация "CTRL + SHIFT + ENTER". Клавишната комбинация показва на Excel, че е формула за масив.
