Excel Алтернативи на VLOOKUP (Съдържание)
- Алтернативи на VLOOKUP
- Примери за INDEX и MATCH функция с VLOOKUP ограничение
- Как да използвате VLOOKUP Алтернативи в Excel?
Алтернативи на VLOOKUP
Excel е пълен с формули, редове и колони. VLOOKUP е една от важните формули в excel. Не знаете формула Алтернатива на VLOOKUP в excel като отличен потребител, но добре знаете формулата.
Ако обаче смятате, че познаването на формулата на VLOOKUP е най-доброто нещо в excel, трябва незабавно да промените мисълта си (усетих, че знам, че VLOOKUP е най-върховото нещо в excel в началото на кариерата ми).
След като започнете да напредвате към напредналото ниво, ще започнете да усещате ограниченията на формулата VLOOKUP. Не знам дали знаете или не, VLOOKUP може да ви донесе данните само отляво надясно. Има случаи, в които исках да получа данните от дясно на ляво, но VLOOKUP не успя да ми помогне в тези случаи. Бях принуден да пренаредя данните си, за да свърша работата в моя полза.
Това обаче не е край, защото имаме няколко алтернативи, които да преодолеят ограниченията на VLOOKUP в excel.
Примери за INDEX и MATCH функция с VLOOKUP ограничение
Тези две са алтернативите на VLOOKUP в excel. За Алтернатива на VLOOKUP трябва да помним номера на колоната в случай на много колони, но в тези формули не трябва да помним нищо, просто трябва да разберем логиката на формулата.
Формула на INDEX:
- Масив: Каква е стойността на масива, който искате да получите?
- Ред номер: От кой номер на реда се опитвате да извлечете данните.
- Колона на колоната: от кой номер на колоната се опитвате да извлечете данните.
Функция на индекса - пример №1
Нека разгледаме някои примери за алтернативи на Excel на VLOOKUP.
Можете да изтеглите тази алтернатива на VLOOKUP Excel шаблон тук - Алтернативи на VLOOKUP Excel шаблонИмам проста таблица за продажби, съобразена със зоната.
Мога да получа данните, като използвам VLOOKUP, но тук ще използвам INDEX формулата. Отворете формулата в клетка E2.
Сега изберете необходимите стойности на колоната за резултат. Тук необходимите ми стойности са от B2 до B5. Ще избера диапазона като B2: B5 и ще го заключа.
Сега трябва да спомена номера на реда. За региона Юг номерът на реда е 2.
Сега трябва да спомена от коя колона се опитваме да извлечем данните. В този случай е първата колона, защото сме избрали само една колона в аргумента на масива.
INDEX ще ми донесе данните за ЮЖЕН регион.
Еха!!! INDEX направи трика за мен.
Проблемът обаче ще започне сега. Когато копирам и залепя формулата в клетките по-долу, трябва да променя номера на реда, тъй като клетката се променя. В текущия номер на реда на клетката е 1 и когато преминах към следващата клетка, тя трябва да бъде 2. INDEX не приема автоматично инкрементния номер на реда.
Можем да автоматизираме тази задача, като използваме функцията MATCH.
Функция MATCH - Пример №2
Той ще върне номера на реда на избрана стойност в предоставения диапазон.
- Стойност за търсене: Каква е търсената стойност, която се опитвате да намерите номера на реда?
- Масив за търсене: От кой диапазон се опитвате да намерите номера на реда
- Тип на съвпадение: какъв резултат искате да търсите. Независимо дали става въпрос за приблизителното съвпадение под стойността на търсене (1) или точното съвпадение (0) или приблизителното съвпадение над стойността на търсене (-1).
Сега ще използвам същите данни от горния пример. Ще се опитам да разгледам номера на реда за всеки регион.
Отворете формулата в клетка E2 и изберете стойността на търсене като D2 клетка стойност.
Сега масивът за търсене е от коя таблица се опитвате да знаете номера на реда. Нашата таблица с масиви е от A2 до A5.
Типът на съвпадение трябва да е точен, така че ще спомена 0 като аргумент.
Получих номера на редовете за всеки регион.
Плъзнете и пуснете формулата към останалите клетки.
Ограничение на VLOOKUP - Пример №3
VLOOKUP винаги работи от ляво на дясно. Сега, погледнете по-долу данните.
Трябва да извлека данни за заплатите от диапазона от А1 до Д21. Мога да направя това, като прилагам функция VLOOKUP.
Резултатът ще бъде както следва:
Плъзнете и пуснете формулата към останалите клетки.
В този момент VLOOKUP върши работата за мен. Но сега погледнете по-долу структурата на данните.
Въз основа на ИД на ИМП трябва да извлека данните, но ЕМП ИД е в самия десен край на действителните данни. VLOOKUP започва да избледнява в тези случаи.
Така че имаме изключителна алтернатива на VLOOKUP, наречена INDEX & MATCH функция.
Как да използвате алтернатива на VLOOKUP в Excel?
Алтернатива на VLOOKUP е много проста и лесна. Нека разберем как да използваме алтернативи на VLOOKUP в excel с някои примери.
Функция INDEX + MATCH като алтернатива на VLOOKUP - Пример №1
Сега вземете същата таблица от горния пример.
Отворете формулата INDEX в клетка H2.
Масивът не е нищо друго, освен какъв е резултатът, който се опитвате да намерите. Тук се опитваме да намерим стойността на заплатата, така че изберете целия диапазон на заплатите.
Сега трябва да споменем номера на реда. В примера на функцията MATCH научихме, че функцията MATCH може да ни даде номера на реда. Така че ще използвам MATCH функция вътре във функцията INDEX.
LOOKUP означава на какво основание се опитвам да намеря номера на реда. Тук за EMP ID се опитвам да намеря номера на реда, така че стойността на търсене е EMP ID.
Сега погледнете масива не е нищо, но това е колоната със стойност на търсенето на основната таблица.
Сега накрая тип съвпадение трябва да бъде точното съвпадение, така че споменете 0 като стойност.
Резултатът ще бъде както следва:
Yessssss !!!!!!! INDEX + MATCH работи точно като VLOOKUP, но може да извлече данните от всяка точка и навсякъде.
Плъзнете и пуснете формулата към останалите клетки.
LOOKUP функция като алтернатива на VLOOKUP - Пример №2
Самият LOOKUP е вградена функция в excel.
- Стойност за търсене: Това е стойността на базата, която търсите за резултата.
- Търсене на вектор: Това е диапазонът на стойността на Lookup в основната таблица.
- Вектор за резултата : Това е колоната с резултати в основната таблица.
Вземете същите данни от предишния пример.
Отворете формулата LOOKUP в клетка H2 и изберете стойността на търсене като G2.
Сега трябва да изберем вектора за търсене като D2 до D21 в главната таблица.
Сега най-накрая резултатният вектор трябва да бъде колоната с резултати, която се опитваме да извлечем, което е B2 до B21.
След като въведете формулата, натиснете клавиша за въвеждане и ще получим резултата.
Плъзнете и пуснете формулата към останалите клетки.
Неща, които трябва да запомните за Excel Алтернативи на VLOOKUP
- VLOOKUP може да работи само отляво надясно.
- Функцията MATCH ще върне номера на реда.
- Функциите INDEX + MATCH и LOOKUP не изискват номер на колона, за разлика от VLOOKUP, изискват номер на колона за извличане на данните, въпреки че необходимата колона вече е избрана.
- Структурата на данните няма значение за функциите на INDEX + MATCH и LOOKUP. Но за VLOOKUP структурата на данните има значение.
Препоръчителни статии
Това е ръководство за алтернативи на VLOOKUP в Excel. Тук обсъждаме примерите за алтернативи на VLOOKUP като например INDEX, MATCH и LOOKUP, заедно с практически примери и свалящ се шаблон на excel. Можете да разгледате и другите ни предложени статии -
- VLOOKUP функция в Excel
- Ръководства за функцията MATCH в Excel
- Как да използвате LOOKUP в Excel?
- IFERROR с VLOOKUP в Excel
- Примери за макроси в Excel