INDEX/MATCH е мощна алтернатива на Excel VLOOKUP, позволяваща на потребителите да извършват разширени търсения с множество критерии. Този метод предлага повече гъвкавост и бързина, което го прави предпочитана опция за много потребители.
Този урок ще ви преведе през стъпките за замяна на VLOOKUP с INDEX/MATCH в Excel. Ще научите как да настроите формулата INDEX/MATCH, да я използвате за извличане на данни и отстраняване на често срещани грешки.
До края на тази статия можете да замените VLOOKUP с INDEX/MATCH, което ви позволява да работите по-ефективно и ефективно в Excel. Да започваме!
Съдържание
- Основи на Excel INDEX и MATCH
- INDEX MATCH срещу VLOOKUP: Кое да използвате в Excel?
- 4 причини защо INDEX MATCH е по-добър от VLOOKUP
- Използване на INDEX MATCH в Excel
- Използване на Excel INDEX MATCH с функции
- Често задавани въпроси
- Последни мисли
Основи на Excel INDEX и MATCH
Този урок е за това как да използвате функциите INDEX и MATCH в Excel, за да направите VLOOKUP. Функцията INDEX връща стойност в масив въз основа на посочените от вас номера на редове и колони.
Междувременно функцията MATCH търси стойност за търсене в диапазон от клетки и връща относителната позиция на тази стойност в диапазона.
За да използвате тези функции заедно, трябва да използвате функцията MATCH, за да определите номерата на редовете и колоните на данните, които искате да извлечете, и след това да използвате функцията INDEX, за да извлечете тези данни.
За да използвате функцията INDEX, посочете диапазона от клетки, от които искате да върнете стойност, номера на реда и номера на колоната. Ако пропуснете едно от числата, другото е задължително.
Ето примерна таблица, която можем да използваме, за да демонстрираме как да използваме функциите INDEX и MATCH заедно:
Да приемем, че искате да извлечете цената на бананите. Вместо да използвате VLOOKUP, можете да използвате функциите INDEX и MATCH заедно.
Първо, ще използвате функцията MATCH, за да определите номера на реда на „Банан“ в колоната „Плодове“:
=MATCH("Банан", A2:A5, 0)
Тази формула ще върне стойността "2", тъй като "Банан" е във втория ред на колоната "Плодове".
След това ще използвате отново функцията MATCH, за да определите номера на колоната на „Цена“ в заглавния ред:
=MATCH("Цена", A1:C1, 0)
Тази формула ще върне стойността "3", тъй като "Цена" е в третата колона на заглавния ред.
И накрая, можете да използвате функцията INDEX, за да извлечете цената на бананите от таблицата:
=ИНДЕКС(A1:C5; 2; 3)
Тази формула ще върне стойността "$0,50", цената на бананите.
Използването на тези две функции заедно може да бъде много полезно при работа с реални данни. Тя ви позволява да извличате данни въз основа на конкретни критерии, вместо просто да търсите стойности в таблица.
Комбинирането на INDEX и MATCH ви позволява да създавате по-сложни формули за търсене и да извършвате разширени търсения с множество критерии.
INDEX MATCH срещу VLOOKUP: Кое да използвате в Excel?
При търсене на информация в Excel могат да се използват различни функции. Две популярни са VLOOKUP и INDEX MATCH. Въпреки че VLOOKUP е по-опростен, INDEX MATCH има повече предимства.
Ето някои причини, поради които INDEX MATCH е по-добър:
- Гъвкавост: С VLOOKUP стойността за търсене трябва да бъде в първата колона на таблицата. С INDEX MATCH можете да търсите стойности във всяка колона, което я прави по-гъвкава.
- Точност: VLOOKUP търси само точни съвпадения, докато INDEX MATCH може да обработва и приблизителни съвпадения. Ако вашите данни не са точни, INDEX MATCH пак може да намери най-близкото съвпадение.
- Скорост: INDEX MATCH е по-бърз от VLOOKUP при търсене на големи количества данни. VLOOKUP може да забави вашата електронна таблица, когато търсите в голяма таблица.
- Четивност: формулите INDEX MATCH са по-лесни за четене и разбиране от формулите VLOOKUP. Това улеснява другите да разберат вашите формули, ако трябва да работят с вашата електронна таблица.
Докато VLOOKUP може да е по-опростен, INDEX MATCH е по-гъвкав, точен, по-бърз и по-лесен за четене. Така че си струва да отделите време, за да научите как да го използвате.
4 причини защо INDEX MATCH е по-добър от VLOOKUP
- Търсене отдясно наляво: VLOOKUP може да търси само стойности отдясно на колоната за справка, но INDEX MATCH може да търси стойности отляво. Това е полезно, когато търсената ви стойност е извън най-лявата колона на таблицата.
- Безопасно вмъкване или изтриване на колони: Когато използвате VLOOKUP, добавянето или изтриването на колони може да наруши вашата формула, защото трябва да посочите индекса на колоната. С INDEX MATCH вие указвате диапазона на върнатата колона, така че можете да добавяте или изтривате колони, без да променяте формулата.
- Няма ограничение за размера на търсената стойност: VLOOKUP има ограничение от 255 знака за търсената стойност, но INDEX MATCH може да обработва дълги низове във вашите данни.
- По-бърза скорост на обработка: Ако имате големи набори от данни, INDEX MATCH ще бъде по-бърз от VLOOKUP, защото обработва само търсенето и връща колони, а не цялата таблица. Това е особено важно, ако вашата работна книга има много сложни формули.
Ако искате да направите вашите формули на Excel по-гъвкави, точни и ефективни, помислете за използването на INDEX MATCH вместо VLOOKUP.
Използване на INDEX MATCH в Excel
Да кажем, че имаме таблица с плодове и техните цени, но искаме да знаем цената на конкретен плод, въпреки че имената на плодовете са във втората колона на таблицата. Можем да използваме функцията INDEX MATCH, за да намерим цената на плода, който търсим.
Първо ще изберем клетката, в която искаме да се появи резултатът. След това ще въведем следната формула:
=ИНДЕКС(C2:C8;MATCH("Apple";B2:B8;0))
Ето какво означава тази формула:
INDEX(C2:C8) казва на Excel да потърси резултата в ценовата колона (колона C).
MATCH("Apple",B2:B8,0) казва на Excel да намери реда, където се намира името на плода "Apple" в таблицата (което е в колона B). „0“ в края на функцията MATCH означава, че искаме точно съвпадение.
Обединявайки всичко това, формулата казва на Excel да потърси в колоната с цените реда, където се намира „Apple“, и да върне съответната цена.
Използвайки функцията INDEX MATCH, можем лесно да търсим информация в Excel, дори ако нашата търсена стойност не е в най-лявата колона на нашата таблица.
Използване на Excel INDEX MATCH с функции
В Excel можете да използвате функции като MAX, MIN и AVERAGE, за да намерите най-големите, най-малките и средните стойности в диапазон от данни. Но какво ще стане, ако трябва да получите стойност от друга колона, съответстваща на тези стойности? Това е мястото, където INDEX MATCH идва на помощ.
Да кажем, че имаме таблица с плодове, техните цени и броя на продажбите им. Искаме да намерим плода с най-високи продажби и да получим неговата цена. Можем да го направим с помощта на формулата INDEX MATCH с MAX:
- Изберете клетката, в която искате да се появи резултатът.
- Въведете следната формула: =INDEX(B2:B8, MATCH(MAX(C2:C8), D2:D8, 0))
Ето какво означава тази формула:
- INDEX(B2:B8) казва на Excel да потърси резултата в колоната за цена (колона B).
- MATCH(MAX(C2:C8), D2:D8, 0) казва на Excel да намери реда, където се намира най-големият брой продажби в таблицата (който е в колона D). „0“ в края на функцията MATCH означава, че искаме точно съвпадение.
- Обединявайки всичко това, формулата казва на Excel да потърси в колоната с цените реда, където се намира най-високото число на продажбите, и да върне съответната цена.
Използвайки INDEX MATCH с MAX, можем лесно да намерим цената на плода с най-високи продажби.
Можете да използвате подобни формули, за да намерите най-малката стойност с INDEX MATCH с MIN и стойността, най-близка до средната с INDEX MATCH с AVERAGE. Коригирайте аргумента match_type във функцията MATCH в зависимост от това как са организирани вашите данни.
Ето таблица за онагледяване на примера:
Като използваме INDEX MATCH с MAX, можем да открием, че плодът с най-високи продажби е ананасът, който е $2,00.
Често задавани въпроси
INDEX MATCH по-добър ли е от VLOOKUP?
INDEX MATCH често се счита за по-гъвкав и мощен от VLOOKUP, защото позволява вертикални и хоризонтални търсения, множество критерии и динамични диапазони.
Каква е формулата за заместване на VLOOKUP в Excel?
Формулата за заместване на VLOOKUP в Excel е комбинацията INDEX MATCH, където INDEX връща стойността от определен диапазон въз основа на съвпадащ критерий, получен с MATCH.
Как да използвам INDEX MATCH в Excel?
За да използвате INDEX MATCH в Excel, трябва да укажете диапазона от стойности, в който искате да търсите, да използвате функцията MATCH, за да намерите позицията на желаната стойност, и след това да използвате INDEX, за да извлечете съответната стойност от друг диапазон.
Защо да използвате INDEX MATCH в Excel?
INDEX MATCH в Excel предлага по-голяма гъвкавост за извършване на търсения, тъй като може да обработва както вертикални, така и хоризонтални търсения, множество критерии и позволява по-разширено извличане на данни в сравнение с VLOOKUP.
Как да съпоставя два списъка в Excel?
За да съпоставите два списъка в Excel, можете да използвате функцията MATCH, за да намерите позицията на стойност в един списък и след това да използвате INDEX, за да извлечете съответната стойност от другия списък, или можете да използвате комбинацията VLOOKUP или INDEX MATCH, за да сравните и извличане на съответстващи стойности.
Последни мисли
В заключение, докато VLOOKUP е полезна функция, INDEX/MATCH е по-мощна и гъвкава опция за търсене на информация в Excel.
Чрез комбиниране на функциите INDEX и MATCH потребителите могат да извършват търсения отляво надясно и отдясно наляво и дори могат да извършват по-сложни търсения чрез функции за влагане.
В крайна сметка, научаването да използвате INDEX/MATCH може да спести време и да увеличи ефективността при работа с данни в Excel.
Още нещо
Ако имате секунда, моля, споделете тази статия в социалните мрежи; някой друг също може да се възползва.
Абонирайте се за нашия бюлетин и бъдете първите, които ще четат нашите бъдещи статии, рецензии и публикации в блогове направо във входящата си електронна поща. Ние също така предлагаме сделки, промоции и актуализации за нашите продукти и ги споделяме по имейл. Няма да пропуснете нито един.
Свързани статии
» Как да ограничите редове и колони в работен лист на Excel
» Отстраняване на грешка „OLE Action Excel“ в Microsoft Excel
» Начини за редуване на цветовете на редове в Excel [Ръководство/]