У функцији ВЛООКУП често дефинишемо цол_индек_но статиц. Тврдо га кодирамо унутар формуле ВЛООКУП, попут ВЛООКУП -а (ид, подаци,3, 0). Проблем настаје када уметнемо или избришемо колону унутар података. Ако уклонимо или додамо колону пре или после треће колоне, трећа колона се више неће односити на предвиђену колону. Ово је један проблем. Други је када имате више колона за тражење. Морате да уредите индекс колоне у свакој формули. Једноставно лепљење копија неће помоћи.
Али шта ако кажете ВЛООКУП -у да погледа наслове и врати само одговарајућу вредност наслова. Ово се назива двосмерни ВЛООКУП.
На пример, ако имам формулу ВЛООКУП заоцене колону, онда би ВЛООКУП требало да тражи оцене колона у подацима и повратна вредност из те колоне. Ово ће решити наш проблем.
Хмм … У реду, па како ћемо то учинити? Коришћењем функције Матцх унутар функције ВЛООКУП.
Општа формула
=ВЛООКУП(лоокуп_валуе, табле_арраи, МАТЦХ (лоокуп_хеадинг, табле_хеадингс, 0), 0)
Лоокуп_валуе: вредност тражења у првој колони табле_арраи.
Поље_табеле: опсег у којем желите да извршите претрагу. Нпр. А2, Д10.
Лоокуп_хеадинг: наслов који желите да пронађете у насловима табле_арраи.
Наслови табеле: Референца наслова у низу табела. На пример. ако је табела А2, Д10 и наслови на врху сваке колоне, онда је њена А1: Д1.
Дакле, сада знамо шта нам је потребно за динамички цол_индек, хајде да све разјаснимо примером.
Пример динамичког ВЛООКУП -а
За овај пример имамо ову табелу која садржи податке ученика у опсегу А4: Е16.
Користећи ролл но и хеадинг, желим да преузмем податке из ове табеле. На пример, у ћелији Х4 желим да добијем податке о роли која није записана у ћелији Г4 и о наслову у Х3. Ако променим наслов, подаци из одговарајућег опсега треба да се преузму у ћелију Х4.
Напишите ову формулу у ћелију Х4
= ВЛООКУП (Г4, Б4: Е16, МАТЦХ (Х3, Б3: Е3,0), 0)
Пошто је наш низ табела Б4: Е16, наш низ наслова постаје Б3: Е3.
Белешка: Ако су ваши подаци добро структурирани, наслови колона ће имати исти број колона и то је први ред у табели.
Како то ради:
Дакле, главни део је аутоматска процена броја индекса колоне. Да бисмо то учинили, користили смо функцију МАТЦХ.
УТАКМИЦА (Х3, Б3: Е3,0): Пошто Х3 садржи „студент“, МАТЦХ ће вратити 2. Да је Х3 имао „Оцену“, вратио би 4, и тако даље. Формула ВЛООКУП ће коначно имати свој цол_индек_нум.
= ВЛООКУП (Г4, Б4: Е16,2,0)
Као што знамо, функција МАТЦХ враћа индексни број дате вредности у испорученом једнодимензионалном опсегу. Према томе, МАТЦХ ће тражити било коју вредност написану у Х3 у опсегу Б3: Е3 и вратиће свој индексни број.
Сада кад год промените наслов у Х3, ако је у насловима, ова формула ће вратити вредност из одговарајуће колоне. У супротном ћете имати грешку #Н/А.
ВЛООКУП у више колона брзо
У горњем примеру нам је требао одговор из вредности једне колоне. Али шта ако желите да добијете више колона одједном. Ако копирате горњу формулу, вратиће се грешке. Морамо да направимо неке мање измене у њему како бисмо га учинили преносивим.
Коришћење апсолутних референци са ВЛООКУП -ом
Напишите испод формулу у ћелији Х2.
= ВЛООКУП ($ Г2, $ Б $ 2: $ Е $ 14, МАТЦХ (Х $ 1, $ Б $ 1: $ Е $ 1,0), 0)
Сада копирајте Х2 у све ћелије у опсегу Х2: Ј6 да бисте га попунили подацима.
Како то ради:
Овде сам дао апсолутна референца сваког опсега осим реда у вредности тражења за ВЛООКУП ($ Г2) и ступац у лоокуп_валуе за МАТЦХ (Х $ 1).
$ Г2: Ово ће омогућити да се ред промени за вредност претраживања за функцију ВЛООКУП током копирања надоле, али ће ограничити промену колоне при копирању са десне стране. Због чега ће ВЛООКУП тражити ИД из колоне Г само са релативним редом.
Слично, Х $ 1 дозволиће промену колоне при хоризонталном копирању и ограничиће ред при копирању надоле.
Коришћење именованих опсега
Горњи пример добро функционише, али постаје изазован за читање и писање ове формуле. А ово уопште није преносиво. Ово се може поједноставити коришћењем именовани распони.
Овде ћемо прво дати нека имена. За овај пример сам именовао
$ Б $ 2: $ Е $ 14: као Подаци
$ Б $ 1: $ Е $ 1: као Наслови
Х $ 1: Именујте га као наслов. Нека колоне буду релативне. Да бисте то урадили, изаберите Х1. Притисните ЦТРЛ+Ф3, кликните на ново, у одељку Односи се на уклањање „$“ са предње стране Х.
$ Г2: Слично, назовите га РоллНо. Овај пут чини ред релативним уклањањем '$' са предње стране 2.
Сада, када имате сва имена на листу, напишите ову формулу било где у екцел датотеци. Увек ће добити тачан одговор.
= ВЛООКУП (РоллНо, Дата, МАТЦХ (Наслов, Наслови, 0), 0)
Видите, свако може ово да прочита и разуме.
Дакле, користећи ове методе, цол_индек_нум можете учинити динамичним. Обавестите ме да ли вам је ово помогло у одељку коментара испод.
Како се користи тФункција ВЛООКУП у Екцелу
Релативна и апсолутна референца у Екцелу
Именовани распони у програму Екцел
Како ВЛООКУП из различитих Екцел листова
ВЛООКУП Више вредности
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности : Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи тФункција ВЛООКУП у Екцелу : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.