Ако већ неко време читате Екцел савете, увек сте нашли некога ко говори о употреби функција Екцел ИНДЕКС () & МАТЦХ () уместо Екцел ВЛООКУП. Говорећи у своје име, увек је било претешко покушати савладати ДВЕ нове функције истовремено. Али, то је кул трик. Дајте ми пет минута и покушаћу да то објасним на једноставном енглеском.
Преглед ВЛООКУП-а од 30 секунди
Рецимо да имате табелу евиденције запослених. Прва колона је број запосленог, а преостале колоне су различити подаци о запосленом. Кад год на радном листу имате број запосленог, можете да користите ВЛООКУП за враћање одређеног податка о запосленом. Синтакса је ВЛООКУП (вредност, опсег података, бр. Кол., ФАЛСЕ). Екцел каже: „Идите на опсег података. Пронађите ред који има (вредност) у првој колони опсега података. Вратите (кол. Бр.) Вредност из тог реда. Једном када то схватите, врло је једноставно и моћно.
Проблем
Једног дана имате ситуацију да имате име запосленог, али вам је потребан број запосленог. На следећој слици имате име у А10 и треба да пронађете број запосленог у Б10.
Када је кључно поље десно од података које желите да преузмете, ВЛООКУП неће радити. Када би само ВЛООКУП прихватио -1 као број колоне, не би било проблема. Али, није. Једно од уобичајених решења је привремено убацивање нове колоне А, копирање колоне имена у нову колону А, попуњавање ВЛООКУП-ом, лепљење посебних вредности, а затим брисање привремене колоне А. Екцел професионалци овај потез вероватно могу учинити у сну.
Предложићу вам да прихватите изазов и покушате да користите овај метод у једном кораку. Да, морат ћете нанијети формулу на зид неколико седмица, али и то сте већ давно урадили са ВЛООКУП-ом, зар не?
Мислим да је разлог што је ово тако тешко тај што користите две функције које вероватно никада раније нисте користили. Дакле, дајте да га разбијем на два дела.
Прво, ту је функција ИНДЕКС (). Ово је ужасно именована функција. Кад неко каже „индекс“, то ми не дочарава ништа слично ономе што ради ова функција. Индекс захтева три аргумента.
=INDEX(data range, row number, column number)
На енглеском језику, Екцел прелази на опсег података и враћа вам вредност у пресеку (реда броја) тх реда и (нумбер нумбер) тх колоне. Хеј, размисли - ово је прилично једноставно, зар не? =INDEX($A$2:$C$6,4,2)даће вам вредност у Б5.
Примјењујући ИНДЕКС () на наш проблем, можете схватити да бисте за враћање броја запосленог из опсега користили ово =INDEX($A$2:$A$6,?,1):. Заправо, овај његов део делује тако тривијално да делује бескорисно. Али, када замените упитник функцијом МАТЦХ (), имате решење.
Ево синтаксе:
=MATCH(Value, Single-column data range, FALSE)
Екцел каже: „Претражите опсег података и реците ми релативни број реда где ћете наћи подударање за (податке). Дакле, да бисте пронашли који ред имате запосленог у А10, користили бисте =MATCH(A10,$B$2:$B$6,FALSE). Да, ово је сложеније од индекса , али требало би да буде тачно уз улицу професионалаца ВЛООКУП-а. Ако А10 садржи „Миллер, Боб“, онда ће овај МАТЦХ вратити да је у 3. реду опсега Б2: Б6.
Ето га - функција МАТЦХ () говори функцији Индекс у који ред да погледа - готови сте. Узмите функцију Индекс, замените наш упитник функцијом МАТЦХ и сада можете да радите еквивалент ВЛООКУП-а када поље кључа није у левој колони. Ево функције коју треба користити:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Љепљива биљешка на мом зиду заправо је приказује у двије линије. Прво сам написао објашњење за МАТЦХ (). Испод тога сам написао објашњење за ИНДЕКС (). Затим сам нацртао облик левка између њих двоје како бих назначио да функција МАТЦХ () спада у 2. аргумент функције ИНДЕКС ().
Првих неколико пута када сам морао да урадим један од ових, био сам у искушењу да само ударим новом привременом колоном А тамо, али сам уместо тога прошао кроз бол. Бржи је и захтева мање манипулација. Дакле, следећи пут када пожелите да ставите негативан број у функцију ВЛООКУП, испробајте ову чудну комбинацију ИНДЕКС-а и МАТЦХ-а да бисте решили своје проблеме.
