В тази статия ще научим как да търсим няколко екземпляра на стойност в Excel.
Търсене на стойности с помощта на падащата опция?
Тук разбираме как можем да търсим различни резултати, използвайки формулата на масив от функции INDEX. Просто изберете стойността от списъка и съответният резултат ще бъде там.
Обща формула
| {= 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, за да я направите формула за масив.
Пример за търсене за множество резултати
Имам тези ученически данни в обхвата A2: E14. В клетка G1 имам падащо меню със стойности на региона, напр. Централна, Източна, Северна, Южна и Западна. Сега искам, който и регион да имам в G1, списък с всички ученици от този регион трябва да се показва в колоната H.
За да търсим множество стойности в excel, нека идентифицираме нашите променливи.
Масив: $ C $ 2: $ C $ 14
lookup_value: $ G $ 1
lookup_value_range: $ A $ 2: $ A $ 14
Първата клетка от диапазона lookup_value: $ A $ 2
Според горните данни нашата формула за извличане на множество стойности в Excel ще бъде:
| {= IFERROR (ИНДЕКС ($ C $ 2: $ C $ 14, МАЛКИ (АКО ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14)) -ROW ($ A $ 2) +1) , ROW (1: 1))), "Няма повече стойност")} |
Това е формула на масив. Не използвайте само Enter след въвеждане на формулата. Използвайте CTRL + SHIFT + ENTER заедно.
Сега нека разберем КАК РАБОТИ.
Въпреки че формулата може да изглежда сложна, но идеята е проста. Трябва да получим индексния номер за всяко появяване на стойност, след това да извлечем стойности, използвайки функцията INDEX на Excel.
Следователно основното предизвикателство е получаването на масив от индексни номера на lookup_value. За да получим индексни номера, използвахме IF и ROW функции. Формулата наистина е сложна, нека я разбием.
Искаме да получим стойности от колоната за ученици, така че нашият масив за функцията INDEX е $ C $ 2: $ C $ 14.
Сега трябва да дадем номера на редове от $ A $ 2: $ A $ 14 (справочна стойност), в която съществува стойността на G1 (да речем, засега G1 има централна в него).
АКО ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Сега тази част връща номера на ред, ако стойността на G1 е клетка (централно) в обхвата $ A $ 2: $ A $ 14 иначе се връща НЕВЯРНО. В този пример ще се върне
{2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
Сега, тъй като горният масив съдържа номера на редове от 1 -ви ред (1: 1) и имаме нужда от редове, започващи от нашия масив (A2: A14). За да направим това, използваме -ROW ($ A $ 2) +1 във формулата IF. Това ще върне редица редове, преди да стартира нашия масив.
За този пример е -1. Ако започваше от А3, щеше да върне -2 и така нататък. Това число ще бъде извадено от всяко число в масива, върнат от IF. Така че най -накрая АКО ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) това ще се преведе на {1; FALSE; FALSE; FALSE; FALSE;6;7; FALSE; FALSE;10; FALSE; FALSE; FALSE}.
След това този масив е заобиколен от SMALL функция. Тази функция връща N -тата най -малка стойност в дадения масив. Сега имаме МАЛКО ({2; FALSE; FALSE; FALSE; FALSE;7;8; FALSE; FALSE;11; FALSE; FALSE; FALSE}, ROW (1: 1)). ROW (1: 1) ще върне 1. Следователно горната функция ще върне първата най -малка стойност в масива, която е 2.
Когато копирате тази формула в клетките по -долу, ROW (1: 1) ще стане ROW (2: 2) и ще върне втората най -малка стойност в масива, която е 7 и така нататък. Това позволява на функцията първо да върне първата намерена стойност. Но ако искате първо да получите последната намерена стойност, използвайте функцията LARGE вместо функцията SMALL.
Сега, използвайки стойности, върнати по -горе, функцията INDEX лесно връща всяка съвпадаща стойност от диапазон.
Можете също така да извършите търсене на точни съвпадения, като използвате функциите INDEX и MATCH в Excel. Научете повече за това как да извършите търсене с чувствителност към регистъра, като използвате функцията INDEX & MATCH в Excel. Можете също да потърсите частичните съвпадения, като използвате заместващи знаци в Excel.
Надявам се, че тази статия за това как да търсите няколко екземпляра на стойност в Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Използвайте VLOOKUP от две или повече таблици за търсене | За търсене от множество таблици можем да използваме подход IFERROR. За да търсите от няколко таблици, грешката се приема като превключвател за следващата таблица. Друг метод може да бъде подход If.
Как да извършите търсене с чувствителност към регистъра в Excel | функцията VLOOKUP на excel не е чувствителна към регистъра и ще върне първата съвпадаща стойност от списъка. INDEX-MATCH не прави изключение, но може да бъде променен, за да стане чувствителен към регистър. Да видим как…
Търсене на често срещан текст с критерии в Excel | Търсенето най-често се появява в текст в диапазон, който използваме функцията INDEX-MATCH с MODE. Ето метода.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.