Ако четете съвети за Excel от известно време, неизменно сте намерили някой, който говори за използване на функции INDEX () и MATCH () на Excel вместо Excel VLOOKUP. Говорейки за себе си, винаги беше твърде трудно да се опитам да овладея ДВЕ нови функции едновременно. Но това е страхотен трик. Дайте ми пет минути и ще се опитам да го обясня на прост английски.
30-секундната рецензия на VLOOKUP
Да предположим, че имате таблица със записи на служителите. Първата колона е номер на служител, а останалите колони са различни данни за служителя. Всеки път, когато имате номер на служител в работния лист, можете да използвате VLOOKUP, за да върнете конкретни данни за служителя. Синтаксисът е VLOOKUP (стойност, диапазон от данни, номер на колоната, FALSE). В Excel се казва: "Отидете до диапазона от данни. Намерете ред, който има (стойност) в първата колона на диапазона от данни. Върнете стойността (номер на колона) от този ред. След като го разберете, това е много просто и мощно.
Проблемът
Един ден имате ситуация, в която имате името на служителя, но имате нужда от номера на служителя. На следващото изображение имате име в A10 и трябва да намерите номера на служителя в B10.
Когато ключовото поле е вдясно от данните, които искате да извлечете, VLOOKUP няма да работи. Ако само VLOOKUP би приел -1 като номер на колоната, нямаше да има проблем. Но това не е така. Едно често срещано решение е временно вмъкване на нова колона A, копиране на колоната с имена в новата колона A, попълване с VLOOKUP, поставяне на специални стойности и след това изтриване на временната колона A. Професионалистите на Excel вероятно могат да направят това преместване в сън.
Предлагам ви да приемете предизвикателството и да се опитате да използвате този метод от една стъпка. Да, ще трябва да залепите формулата на стената си в продължение на няколко седмици, но и това сте направили с VLOOKUP отдавна, нали?
Мисля, че причината за това е толкова трудна е, че използвате две функции, които вероятно никога преди не сте използвали. И така, позволете ми да го разбия на две части.
Първо, има функцията INDEX (). Това е ужасно наречена функция. Когато някой каже „индекс“, това не предизвиква нищо в съзнанието ми, подобно на това, което прави тази функция. Индексът изисква три аргумента.
=INDEX(data range, row number, column number)
На английски език Excel преминава към диапазона от данни и ви връща стойността в пресечната точка на (ред номер) th ред и (номер на колона) th колона. Хей, помисли - това е доста просто, нали? =INDEX($A$2:$C$6,4,2)ще ви даде стойността в B5.
Прилагането INDEX (), за да ни проблем, можете да разберете, че за да се върнете номера на служител от границите, трябва да използвате това: =INDEX($A$2:$A$6,?,1). Всъщност това парче изглежда толкова тривиално, че изглежда безполезно. Но когато замените въпросителния знак с функция MATCH (), имате решение.
Ето синтаксиса:
=MATCH(Value, Single-column data range, FALSE)
Той казва на Excel: „Търсете в диапазона от данни и ми кажете относителния номер на реда, където ще намерите съвпадение за (данни). Така че, за да намерите кой ред има служителят в A10, бихте използвали =MATCH(A10,$B$2:$B$6,FALSE). Да, това е по-сложно от индекса , но трябва да е точно по алеята на професионалистите от VLOOKUP. Ако A10 съдържа "Miller, Bob", тогава този MATCH ще върне, че е в 3-ия ред от диапазона B2: B6.
Ето го - функцията MATCH () казва на функцията Index в кой ред да погледне - готово. Вземете функцията Index, заменете нашия въпросителен знак с функцията MATCH и вече можете да правите еквивалента на VLOOKUPs, когато ключовото поле не е в лявата колона. Ето функцията, която да се използва:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Залепващата бележка на стената ми всъщност я показва като два реда. Първо изписах обяснението за MATCH (). По-долу написах обяснението за INDEX (). След това нарисувах форма на фуния между двете, за да покажа, че функцията MATCH () пада във втория аргумент на функцията INDEX ().
Първите няколко пъти, когато трябваше да направя едно от тях, бях изкушен просто да забия нова временна колона А там, но вместо това претърпях болката да го направя по този начин. Той е по-бърз и изисква по-малко манипулация. Така че, следващия път, когато желаете да можете да поставите отрицателно число във функцията VLOOKUP, опитайте тази странна комбинация от INDEX и MATCH, за да разрешите проблемите си.
