Excel има много функции - около 450+ от тях.
И много от тях са просто страхотни. Количеството работа, което можете да свършите с няколко формули, все още ме изненадва (дори след като сте използвали Excel повече от 10 години).
И сред всички тези невероятни функции, комбинацията от функции INDEX MATCH се откроява.
Аз съм голям фен на комбинацията INDEX MATCH и много пъти го изяснявах.
Дори написах статия за Index Match VS VLOOKUP, която предизвика малко дебат (можете да проверите секцията за коментари за някои фойерверки).
И днес пиша тази статия, фокусирана единствено върху Index Match, за да ви покажа някои прости и усъвършенствани сценарии, при които можете да използвате тази мощна комбинация от формули и да свършите работата.
Забележка: В Excel има и други формули за търсене - като VLOOKUP и HLOOKUP и те са страхотни. Много хора намират VLOOKUP за по -лесен за използване (и това също е вярно). Вярвам, че INDEX MATCH е по -добър вариант в много случаи. Но тъй като на хората им е трудно, той свиква по -малко. Затова се опитвам да го опростя с помощта на този урок.Сега, преди да ви покажа как комбинацията от INDEX MATCH променя света на анализаторите и учените по данни, нека първо ви запозная с отделните части - функциите INDEX и MATCH.
INDEX функция: Намира стойността въз основа на координатите
Най -лесният начин да разберете как работи функцията Index е като мислите за нея като GPS сателит.
Веднага щом кажете на сателита координатите на географската ширина и дължина, той ще знае точно къде да отиде и да намери това местоположение.
Така че, въпреки невероятния брой комбинации от дълги ширини, спътникът ще знае точно къде да търси.
Бързо потърсих работното си място и това получих.
Както и да е, стига география.
Точно както сателитът се нуждае от координати на географска ширина и дължина, функцията INDEX в Excel ще се нуждае от номера на реда и колоната, за да знае към коя клетка се отнася.
И това е функцията Excel INDEX в ядка.
Така че нека да го определя с прости думи за вас.
Функцията INDEX ще използва номера на реда и номера на колоната, за да намери клетка в дадения диапазон и да върне стойността в нея.
Сама по себе си INDEX е много проста функция, без помощна програма. В крайна сметка в повечето случаи е малко вероятно да знаете номерата на редовете и колоните.
Но…
Фактът, че можете да го използвате с други функции (подсказка: MATCH), които могат да намерят номера на реда и номера на колоната, прави INDEX изключително мощна функция на Excel.
По -долу е синтаксисът на функцията INDEX:
= INDEX (масив, номер на ред, [номер на колона]) = INDEX (масив, номер на ред, [номер на колона], [номер на област])
- масив - а диапазон от клетки или константа на масив.
- ред_ номер - номера на реда, от който трябва да се извлече стойността.
- [col_num] - номера на колоната, от която трябва да се извлече стойността. Въпреки че това е незадължителен аргумент, но ако row_num не е предоставен, той трябва да бъде даден.
- [номер на площ] - (По избор) Ако аргументът на масива се състои от множество диапазони, това число ще се използва за избор на препратка от всички диапазони.
Функцията INDEX има 2 синтаксиса (само FYI).
Първият се използва в повечето случаи. Вторият се използва само в напреднали случаи (като например тристранно търсене), които ще разгледаме в един от примерите по-късно в този урок.
Но ако сте нов в тази функция, просто запомнете първия синтаксис.
По -долу е видео, което обяснява как да използвате функцията INDEX
Функция MATCH: Намира позицията, базирана на стойност за търсене
Връщайки се към предишния ми пример за дължина и ширина, MATCH е функцията, която може да намери тези позиции (в света на електронните таблици на Excel).
На прост език функцията Excel MATCH може да намери позицията на клетка в диапазон.
И на каква база ще намери позицията на клетката?
Въз основа на стойността за търсене.
Например, ако имате списък, както е показано по -долу и искате да намерите позицията на името „Маркирай“ в него, тогава можете да използвате функцията MATCH.
Функцията връща 3, тъй като това е позицията на клетката с името Mark в нея.
Функцията MATCH започва да търси отгоре надолу стойността за търсене (която е „Mark“) в определения диапазон (който е A1: A9 в този пример). Веднага щом намери името, той връща позицията в този конкретен диапазон.
По -долу е синтаксисът на функцията MATCH в Excel.
= MATCH (lookup_value, lookup_array, [match_type])
- lookup_value - Стойността, за която търсите съвпадение в lookup_array.
- lookup_array - Обхватът на клетките, в които търсите lookup_value.
- [match_type] - (По избор) Това определя как Excel трябва да търси съответстваща стойност. Тя може да приема три стойности -1, 0 или 1.
Разбиране на аргумента за типа на съвпадението във функцията MATCH
Има едно допълнително нещо, което трябва да знаете за функцията MATCH и тя е за това как тя преминава през данните и намира позицията на клетката.
Третият аргумент на функцията MATCH може да бъде 0, 1 или -1.
По -долу е дадено обяснение как работят тези аргументи:
- 0 - това ще търси точно съвпадение на стойността. Ако се намери точно съвпадение, функцията MATCH ще върне позицията на клетката. Иначе ще върне грешка.
- 1 - това намира най -голямата стойност, която е по -малка или равна на стойността за търсене. За да работи това, диапазонът от данни трябва да бъде сортиран във възходящ ред.
- -1 - това намира най -малката стойност, която е по -голяма или равна на стойността за търсене. За да работи това, диапазонът от данни трябва да бъде сортиран в низходящ ред.
По -долу е видео, което обяснява как да използвате функцията MATCH (заедно с аргумента за типа съвпадение)
Да обобщим и да го опиша с прости думи:
- INDEX се нуждае от позицията на клетката (номер на ред и колона) и дава стойността на клетката.
- MATCH намира позицията, като използва справочна стойност.
Нека ги комбинираме, за да създадем електроцентрала (INDEX + MATCH)
Сега, когато имате основно разбиране за това как функциите INDEX и MATCH работят поотделно, нека комбинираме тези две и да научим за всички прекрасни неща, които може да направи.
За да разбера това по -добре, имам няколко примера, които използват комбинацията INDEX MATCH.
Ще започна с прост пример и след това ще ви покажа и някои разширени случаи на използване.
Щракнете тук, за да изтеглите примерния файл
Пример 1: Просто търсене с помощта на INDEX MATCH Combo
Нека направим просто търсене с INDEX/MATCH.
По -долу е таблица, в която имам оценки за десет студенти.
От тази таблица искам да намеря знаците за Джим.
По -долу е формулата, която лесно може да направи това:
= ИНДЕКС ($ A $ 2: $ B $ 11, MATCH ("Джим", $ A $ 2: $ A $ 11,0), 2)
Сега, ако мислите, че това може лесно да се направи с помощта на функция VLOOKUP, прав сте! Това не е най -доброто използване на страхотността на INDEX MATCH. Въпреки факта, че съм фен на INDEX MATCH, това е малко по -трудно от VLOOKUP. Ако извличането на данни от колона вдясно е всичко, което искате да направите, препоръчвам ви да използвате VLOOKUP.
Причината, поради която показах този пример, който също може лесно да се направи с VLOOKUP, е да ви покажа как работи INDEX MATCH в проста настройка.
Сега нека покажа полза от INDEX MATCH.
Да предположим, че имате същите данни, но вместо да ги имате в колони, имате ги в редове (както е показано по -долу).
Знаеш ли какво, все още можеш да използваш комбинацията INDEX MATCH, за да получиш оценките на Джим.
По -долу е формулата, която ще ви даде резултат:
= ИНДЕКС ($ B $ 1: $ K $ 2,2, MATCH („Джим“, $ B $ 1: $ K $ 1,0))
Обърнете внимание, че трябва да промените диапазона и да превключите частите на ред/колона, за да може тази формула да работи и за хоризонтални данни.
Това не може да се направи с VLOOKUP, но все пак можете да направите това лесно с HLOOKUP.
Комбинацията INDEX MATCH може лесно да обработва хоризонтални, както и вертикални данни.Щракнете тук, за да изтеглите примерния файл
Пример 2: Търсене вляво
Това е по -често, отколкото си мислите.
Много пъти може да се наложи да извлечете данните от колона, която е вляво от колоната, която има стойността за търсене.
Нещо, както е показано по -долу:
За да разберете продажбите на Майкъл, ще трябва да направите справка вляво.
Ако мислите за VLOOKUP, позволете ми да спра до тук.
VLOOKUP не е създаден да търси и извлича стойностите вляво.
Можете ли все пак да го направите с помощта на VLOOKUP?
Да, можеш!
Но това може да се превърне в дълга и грозна формула.
Така че, ако искате да направите търсене и извличане на данни от колоните вляво, по -добре е да използвате комбинацията INDEX MATCH.
По -долу е формулата, която ще получи номера на продажбите на Майкъл:
= ИНДЕКС ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
Пример 3: Двупосочно търсене
Досега видяхме примерите, при които искахме да извлечем данните от колоната, съседна на колоната, която има стойността за търсене.
Но в реалния живот данните често обхващат множество колони.
INDEX MATCH може лесно да се справи с двупосочно търсене.
По -долу е набор от данни за оценките на ученика по три различни предмета.
Ако искате бързо да извлечете оценките на ученик и по трите предмета, можете да направите това с INDEX MATCH.
Формулата по -долу ще ви даде оценките за Джим за всичките три предмета (копирайте и поставете в една клетка и плъзнете, за да попълните други клетки или копирайте и поставете върху други клетки).
= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Позволете ми бързо да обясня и тази формула.
Формулата INDEX използва B2: D11 като диапазон.
Първият MATCH използва името (Jim в клетка F3) и извлича позицията му в колоната с имена (A2: A11). Това се превръща в номер на ред, от който трябва да бъдат извлечени данните.
Втората формула MATCH използва името на субекта (в клетка G2), за да получи позицията на това конкретно име на субекта в B1: D1. Например математиката е 1, физиката е 2 и химията е 3.
Тъй като тези MATCH позиции се подават във функцията INDEX, тя връща резултата въз основа на името на ученика и името на предмета.
Тази формула е динамична, което означава, че ако промените името на ученика или имената на предметите, тя все още ще работи и ще извлече правилните данни.
Едно страхотно нещо при използването на INDEX/MATCH е, че дори да размените имената на темите, това ще продължи да ви дава правилния резултат.Пример 4: Търсене на стойност от множество колони/критерии
Да предположим, че имате набор от данни, както е показано по -долу и искате да извлечете маркировките за „Mark Long“.
Тъй като данните са в две колони, не мога да потърся Марк и да получа данните.
Ако го направя по този начин, ще получа данните за марки за Mark Frost, а не за Mark Long (защото функцията MATCH ще ми даде резултата за MARK, който отговаря).
Един от начините да направите това е да създадете помощна колона и да комбинирате имената. След като имате помощната колона, можете да използвате VLOOKUP и да получите данните за маркировките.
Ако се интересувате да научите как да направите това, прочетете този урок за използване на VLOOKUP с множество критерии.
Но с комбинацията INDEX/MATCH не се нуждаете от помощна колона. Можете да създадете формула, която обработва множество критерии в самата формула.
Формулата по -долу ще даде резултат.
= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Позволете ми бързо да обясня какво прави тази формула.
Частта MATCH във формулата комбинира стойността за търсене (Mark и Long), както и целия масив за търсене. Когато $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 се използва като масив за търсене, той всъщност проверява стойността за търсене спрямо комбинирания низ от име и фамилия (разделен от символа на тръбата).
Това гарантира, че ще получите правилния резултат, без да използвате помощни колони.
Можете да направите този вид търсене (където има няколко колони/критерии) и с VLOOKUP, но трябва да използвате помощна колона. Комбинацията INDEX MATCH улеснява леко това без никакви помощни колони.Пример 5: Вземете стойности от цял ред/колона
В горните примери използвахме функцията INDEX, за да получим стойност от конкретна клетка. Предоставяте номера на ред и колона и той връща стойността в тази конкретна клетка.
Но можете да направите повече.
Можете също да използвате функцията INDEX, за да получите стойностите от цял ред или колона.
И как може това да бъде полезно, питате!
Да предположим, че искате да знаете общия резултат на Джим по трите предмета.
Можете да използвате функцията INDEX, за да получите първо всички оценки на Джим и след това да използвате функцията SUM, за да получите обща сума.
Нека да видим как да направим това.
По -долу имам резултатите на всички ученици по три предмета.
Формулата по -долу ще ми даде общия резултат на Джим по трите предмета.
= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Позволете ми да обясня как работи тази формула.
Номерът тук е да използвате 0 като номер на колона.
Когато използвате 0 като номер на колона във функцията INDEX, тя ще върне всички стойности на реда. По същия начин, ако използвате 0 като номер на ред, той ще върне всички стойности в колоната.
Така че долната част на формулата връща масив от стойности - {97, 70, 73}
ИНДЕКС ($ B $ 2: $ D $ 11, МАТЧ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Ако просто въведете горната формула в клетка в Excel и натиснете Enter, ще видите #VALUE! грешка. Това е така, защото не връща единична стойност, а масив от стойност.
Но не се притеснявайте, масивът от стойности все още е там. Можете да проверите това, като изберете формулата и натиснете клавиша F9. Той ще ви покаже резултата от формулата, която в този случай е масив от три стойности - {97, 70, 73}
Сега, ако увиете тази формула INDEX във функцията SUM, тя ще ви даде сумата от всички оценки, отбелязани от Джим.
Можете също да използвате същото, за да получите най -високите, най -ниските и средните оценки на Джим.
Точно както направихме това за студент, можете да направите това и за предмет. Например, ако искате среден резултат по предмет, можете да запазите номера на реда като 0 във формулата INDEX и той ще ви даде всички стойности на колоните за този предмет.
Щракнете тук, за да изтеглите примерния файл
Пример 6: Намерете оценката на ученика (приблизителна техника на съвпадение)
Досега използвахме формулата MATCH, за да получим точното съвпадение на стойността за търсене.
Но можете да го използвате и за приблизително съвпадение.
Какво, по дяволите, е приблизителното съвпадение?
Нека обясня.
Когато търсите неща като имена или идентификатори, търсите точно съвпадение. Но понякога трябва да знаете диапазона, в който се намират вашите стойности за търсене. Това обикновено е случаят с числата.
Например, като класен ръководител, може да искате да знаете каква е оценката на всеки ученик по предмет и оценката се определя въз основа на резултата.
По -долу е даден пример, където искам оценката за всички ученици и оценяването се решава въз основа на таблицата вдясно.
Така че, ако един ученик получи по -малко от 33, оценката е F и ако той/тя получи по -малко от 50, но повече от 33, това е E и т.н.
По -долу е формулата, която ще направи това.
= ИНДЕКС ($ F $ 3: $ F $ 8, МАТЧ (B2, $ E $ 3: $ E $ 8,1), 1)
Позволете ми да обясня как работи тази формула.
Във функцията MATCH използвахме 1 като аргумент [match_type]. Този аргумент ще върне най -голямата стойност, която е по -малка или равна на стойността за търсене.
Това означава, че формулата MATCH преминава през диапазона от маркировки и веднага щом намери диапазон от марки, който е равен или по -малък от стойността на маркировките за търсене, той ще спре там и ще върне позицията си.
Така че, ако стойността на маркировката за търсене е 20, функцията MATCH ще върне 1, а ако е 85, ще върне 5.
И функцията INDEX използва тази стойност на позицията, за да получи оценката.
ВАЖНО: За да работи това, вашите данни трябва да бъдат сортирани във възходящ ред. Ако не е така, можете да получите грешни резултати.Обърнете внимание, че горното може да се направи и с помощта на формулата VLOOKUP по -долу:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, ИСТИНА)
Но функцията MATCH може да отиде още по -далеч, когато става въпрос за приблизително съвпадение.
Можете също да имате низходящи данни и да използвате комбинацията INDEX MATCH, за да намерите резултата. Например, ако променя реда на таблицата с оценки (както е показано по -долу), все още мога да намеря оценките на учениците.
За да направя това, всичко, което трябва да направя, е да променя аргумента [match_type] на -1.
По -долу е формулата, която използвах:
= ИНДЕКС ($ F $ 3: $ F $ 8, МАТЧ (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP също може да направи приблизително съвпадение, но само когато данните са сортирани във възходящ ред (но не работи, ако данните са сортирани в низходящ ред).
Пример 7: Търсения, чувствителни към регистъра
Досега всички проверки, които направихме, не са чувствителни към регистъра.
Това означава, че независимо дали стойността за търсене е Джим или ДЖИМ или Джим, няма значение. Ще получите същия резултат.
Но какво ще стане, ако искате търсенето да е чувствително към регистъра.
Това обикновено е така, когато имате големи набори от данни и възможност за повторение или различни имена/идентификатори (с единствената разлика, която е такава)
Да предположим например, че имам следния набор от данни за ученици, където има двама ученици с името Джим (единствената разлика е, че един е въведен като Джим, а друг като Джим).
Обърнете внимание, че има двама ученици със същото име - Джим (клетка А2 и А5).
Тъй като нормалното търсене не би работило, трябва да направите търсене с чувствителност към регистър.
По -долу е формулата, която ще ви даде правилния резултат. Тъй като това е формула за масив, трябва да използвате Control + Shift + Enter.
= ИНДЕКС ($ B $ 2: $ B $ 11, МАТЧ (ИСТИНСКИ, ТОЧНИ (D3, A2: A11), 0), 1)
Позволете ми да обясня как работи тази формула.
Функцията EXACT проверява за точно съвпадение на стойността за търсене (която в този случай е „jim“). Той преминава през всички имена и връща FALSE, ако не съвпада, и TRUE, ако е съвпадение.
Така че изходът на функцията EXACT в този пример е - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}
Обърнете внимание, че има само едно TRUE, когато функцията EXACT намери перфектно съвпадение.
След това функцията MATCH намира позицията на TRUE в масива, върнат от функцията EXACT, която е 4 в този пример.
След като имаме позицията, функцията INDEX я използва, за да намери маркировките.
Пример 8: Намерете най -близкото съвпадение
Нека сега да напреднем малко напред.
Да предположим, че имате набор от данни, където искате да намерите лицето, което има трудов стаж най -близо до необходимия опит (споменато в клетка D2).
Въпреки че формулите за търсене не са създадени за това, можете да го комбинирате с други функции (като MIN и ABS), за да направите това.
По -долу е формулата, която ще намери човека с опит най -близък до необходимия и ще върне името на лицето. Обърнете внимание, че преживяването трябва да бъде най -близко (което може да бъде или по -малко, или повече).
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Тъй като това е формула за масив, трябва да използвате Control + Shift + Enter.
Номерът в тази формула е да промените стойността за търсене и масива за търсене, за да намерите минималната разлика в опита в необходимите и действителните стойности.
Преди да обясня формулата, нека разберем как бихте го направили ръчно.
Ще преминете през всяка клетка в колона В и ще намерите разликата в опита между това, което се изисква, и това, което човек има. След като имате всички разлики, ще намерите тази, която е минимална, и ще извлечете името на този човек.
Точно това правим с тази формула.
Нека обясня.
Стойността за търсене във формулата MATCH е MIN (ABS (D2-B2: B15)).
Тази част ви дава минималната разлика между дадения опит (който е 2,5 години) и всички останали преживявания. В този пример връща 0,3
Обърнете внимание, че съм използвал ABS, за да се уверя, че търся най -близкия (което може да бъде повече или по -малко от даденото преживяване).
Сега тази минимална стойност се превръща в нашата стойност за търсене.
Масивът за търсене във функцията MATCH е ABS (D2- $ B $ 2: $ B $ 15).
Това ни дава масив от числа, от които 2,5 (необходимия опит) е изваден.
Така че сега имаме стойност за търсене (0,3) и масив за търсене ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
Функцията MATCH намира позицията на 0,3 в този масив, което е и позицията на името на лицето, което има най -близък опит.
След това този номер на позиция се използва от функцията INDEX за връщане на името на лицето.
Свързано четене: Намерете най -близкото съвпадение в Excel (примери с формули за търсене)
Щракнете тук, за да изтеглите примерния файл
Пример 9: Използвайте INDEX MATCH с заместващи знаци
Ако искате да търсите стойност, когато има частично съвпадение, тогава трябва да използвате заместващи знаци.
Например по -долу е набор от данни за името на компанията и техните пазарни капитализации и искате да искате да получите пазарната капитализация. данни за трите компании вдясно.
Тъй като това не са точни съвпадения, не можете да правите редовно търсене в този случай.
Но все пак можете да получите правилните данни, като използвате звездичка (*), която е заместващ знак.
По -долу е формулата, която ще ви даде данните, като съпоставите имената на компаниите от главната колона и извлечете цифрата на пазарната капитализация за нея.
= ИНДЕКС ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Позволете ми да обясня как работи тази формула.
Тъй като няма точно съвпадение на стойностите за търсене, използвах D2 & ”*” като стойност за търсене във функцията MATCH.
Звездичката е заместващ знак, който представлява произволен брой знаци. Това означава, че Apple* във формулата ще означава всеки текстов низ, който започва с думата Apple и може да има произволен брой знаци след него.
Така че, когато Apple* се използва като стойност за търсене и формулата MATCH я търси в колона А, връща позицията на „Apple Inc.“, тъй като започва с думата Apple.
Можете също да използвате заместващи знаци, за да намерите текстови низове, където стойността за търсене е между тях. Например, ако използвате * Apple * като стойност за търсене, той ще намери всеки низ, който има думата ябълка навсякъде в него.
Забележка: Тази техника работи добре, когато имате само един случай на съвпадение. Но ако имате няколко екземпляра на съвпадение (например Apple Inc и Apple Corporation, тогава функцията MATCH ще върне позицията само на първия съответстващ екземпляр.
Пример 10: Трипосочно търсене
Това е усъвършенствано използване на INDEX MATCH, но все пак ще го покрия, за да ви покажа силата на тази комбинация.
Не забравяйте, че казах, че функцията INDEX има два синтаксиса:
= INDEX (масив, номер на ред, [номер на колона]) = INDEX (масив, номер на ред, [номер на колона], [номер на област])
Досега във всички наши примери сме използвали само първия.
Но за тристранно търсене трябва да използвате втория синтаксис.
Нека първо обясня какво означава трипосочен поглед.
При двупосочно търсене използваме формулата INDEX MATCH, за да получим оценките, когато имаме името на ученика и името на предмета. Например извличането на белезите на Джим в математиката е двупосочно търсене.
Тристранният поглед би добавил друго измерение към него. Да предположим например, че имате набор от данни, както е показано по-долу и искате да знаете резултата на Джим по математика в средносрочен изпит, тогава това би било тристранно търсене.
По -долу е формулата, която ще даде резултат.
= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))
Горната формула проверява за три неща - името на студента, предмета и изпита. След като намери правилната стойност, тя я връща в клетката.
Позволете ми да обясня как работи тази формула, като разбия формулата на части.
- масив - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): Вместо да използвам един масив, в този случай съм използвал три масива в скоби.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): Функцията MATCH се използва за намиране на позицията на името на студента в клетка $ F $ 5 в списъка с името на студента.
- col_num - МАТЧ (G $ 4, $ B $ 2: $ D $ 2,0): Функцията MATCH се използва за намиране на позицията на името на обекта в клетка $ B $ 2 в списъка с името на субекта.
- [area_num] - IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3)): Стойността на номера на областта казва на функцията INDEX кой от трите масива да използва за извличане на стойността. Ако изпитът е Unit Term, функцията IF ще върне 1, а функцията INDEX ще използва първия масив за извличане на стойността. Ако изпитът е средносрочен, формулата IF ще върне 2, в противен случай ще върне 3.
Това е усъвършенстван пример за използване на INDEX MATCH и е малко вероятно да намерите ситуация, в която трябва да използвате това. Но все пак е добре да знаете какво могат да направят формулите на Excel.
Щракнете тук, за да изтеглите примерния файл
Защо INDEX/MATCH е по -добър от VLOOKUP?
ИЛИ?
Да, така е - в повечето случаи.
След малко ще представя моя случай.
Но преди да го направя, нека кажа това - VLOOKUP е изключително полезна функция и ми харесва. Може да прави много неща в Excel и аз го използвам от време на време. Като казах това, това не означава, че не може да има нищо по -добро, а INDEX/MATCH (с повече гъвкавост и функционалности) е по -добър.
Така че, ако искате да направите основно търсене, по -добре използвайте VLOOKUP.
INDEX/MATCH е VLOOKUP за стероиди. И след като научите INDEX/MATCH, винаги може да предпочетете да го използвате (особено поради гъвкавостта, която притежава).
Без да го разтягаме твърде много, позволете ми бързо да ви дам причините, поради които INDEX/MATCH е по -добър от VLOOKUP.
INDEX/MATCH може да гледа отляво (както и отдясно) на стойността за търсене
Разгледах го в един от горните примери.
Ако имате стойност, която е вляво от стойността за търсене, не можете да направите това с VLOOKUP
Поне не само с VLOOKUP.
Да, можете да комбинирате VLOOKUP с други формули и да го направите, но става сложно и объркано.
INDEX/MATCH, от друга страна, е направен да търси навсякъде (било то наляво, надясно, нагоре или надолу)
INDEX/MATCH може да работи с вертикални и хоризонтални диапазони
Отново, с пълно уважение към VLOOKUP, не е направено това.
В края на краищата V във VLOOKUP означава вертикално.
VLOOKUP може да преминава само през данни, които са вертикални, докато INDEX/MATCH може да преминава през данни както вертикално, така и хоризонтално.
Разбира се, има функцията HLOOKUP, която да се грижи за хоризонталното търсене, но тогава не е VLOOKUP … нали?
Харесва ми фактът, че комбинацията INDEX MATCH е достатъчно гъвкава, за да работи както с вертикални, така и с хоризонтални данни.
VLOOKUP не може да работи с низходящи данни
Що се отнася до приблизителното съвпадение, VLOOKUP и INDEX/MATCH са на едно и също ниво.
Но INDEX MATCH приема точката, тъй като може да обработва и данни, които са в низходящ ред.
Показвам това в един от примерите в този урок, където трябва да намерим оценката на учениците въз основа на таблицата за оценяване. Ако таблицата е сортирана в низходящ ред, VLOOKUP няма да работи (но INDEX MATCH би работил).
INDEX/MATCH може да бъде малко по -бърз
Ще бъда честен. Аз самият не проведох този тест.
Разчитам на мъдростта на майстор на Excel - Чарли Кайд.
Разликата в скоростта във VLOOKUP и INDEX/MATCH е едва забележима, когато имате малки набори от данни. Но ако имате хиляди редове и много колони, това може да бъде решаващ фактор.
В своята статия Чарли Кайд заявява:
„В най-лошия случай методът INDEX-MATCH е толкова бърз, колкото VLOOKUP; в най -добрия случай е много по -бързо. "
INDEX/MATCH не зависи от действителната позиция на колоната
Ако имате набор от данни, както е показано по -долу, докато извличате резултата на Джим по физика, можете да направите това с помощта на VLOOKUP.
И за да направите това, можете да посочите номера на колоната като 3 във VLOOKUP.
Всичко е наред.
Но какво ще стане, ако изтрия колоната Math.
В този случай формулата VLOOKUP ще се счупи.
Защо? - Тъй като беше трудно кодирано да се използва третата колона и когато изтрия колона между тях, третата колона става втората колона.
Използването на INDEX/MATCH в този случай е по -добре, тъй като можете да направите номера на колоната динамичен, като използвате MATCH. Така че вместо номер на колона, той проверява името на темата и използва това, за да върне номера на колоната.
Със сигурност можете да направите това, като комбинирате VLOOKUP с MATCH, но ако все пак комбинирате, защо не го направите с INDEX, който е много по -гъвкав.
Когато използвате INDEX/MATCH, можете безопасно да вмъквате/изтривате колони във вашия набор от данни.
Въпреки всички тези фактори, има причина VLOOKUP да е толкова популярен.
И това е голяма причина.
VLOOKUP е по -лесен за използване
VLOOKUP приема максимум четири аргумента. Ако можете да увиете главата си около тези четири, можете да тръгнете.
И тъй като повечето от основните случаи за търсене се обработват и от VLOOKUP, той бързо се превърна в най -популярната функция на Excel.
Наричам го Краля на функциите на Excel.
INDEX/MATCH, от друга страна, е малко по -труден за използване. Може да се закачите, ако започнете да го използвате, но за начинаещ VLOOKUP е много по -лесен за обяснение и учене.
И това не е игра с нулева сума.
Така че, ако сте нов в света за търсене и не знаете как да използвате VLOOKUP, по -добре научете това.
Имам подробно ръководство за използването на VLOOKUP в Excel (с много примери)
Моето намерение в тази статия не е да поставя две страхотни функции една срещу друга. Исках да ви покажа силата на комбинацията INDEX MATCH и всички страхотни неща, които може да направи.
Надявам се, че сте намерили тази статия за полезна.
Кажете ми вашите мисли в раздела за коментари и ако откриете някаква грешка в този урок, моля, уведомете ме.
