У овом чланку ћемо научити Како ПРЕГЛЕДАТИ вредност између два броја у Екцелу.
Сценариј:
Лако је потражити вредност са једним критеријумом у табели. Можемо једноставно користити ВЛООКУП. Али шта бисмо могли да учинимо ако се у вашим подацима подудара тај критеријум са више колона и морамо да тражимо у више колона да бисмо се подударали са вредношћу. Научимо како се овај проблем може решити коришћењем различитих верзија програма екцел за претраживање
ЛООКУП вредност између два броја
Користећи формулу ВЛООКУП
У приближном подударању ВЛООКУП -а, ако вредност није пронађена, тада се подудара последња вредност која је мања од вредности претраживања и враћа се вредност из датог индекса колоне.
Општа формула за вредност ЛООКУП између два броја:
| = ВЛООКУП (вредност, табела, лоокуп_цол, 1) |
И још једна ствар код Влоокупа је то што тражи вредност у колони и ако не пронађе вредност у низу колона, онда се подудара и враћа вредност која је мања од те вредности у низу табеле.
Напомена: ВЛООКУП подразумевано одговара приближном подударању, ако изоставимо променљиву тражење опсега. Приближно подударање је корисно када желите да направите приближно подударање и када имате распоред табела у растућем редоследу.
Користећи формулу ИНДЕКС и МАТЦХ
Формуле ИНДЕКС и МАТЦХ раде исти посао као горе, али са другачијом синтаксом. Али ако бисте само изабрали ону са функцијама које вам одговарају
Генеричка формула за вредност ЛООКУП између два броја
| = ИНДЕКС (лоокуп_ранге, МАТЦХ (1, ИНДЕКС ((критеријум1 = опсег1)*(критеријум2 = опсег2),0,1),0)) |
лоокуп_ранге: опсег из којег желите да преузмете вредност.
Критеријуми1, Критеријуми2, Критеријуми Н: Ово су критеријуми којима желите да се подударате у опсегу1, опсегу2 и распону Н. Можете имати до 270 критеријума - парова распона.
Опсег1, опсег2, опсегН: Ово су опсези у којима ћете одговарати вашим критеријима.
Пример:
Све ово може бити збуњујуће за разумевање. Хајде да схватимо како користити функцију користећи пример. Овде имамо студентску легитимацију и одговарајуће оцене на тесту. Сада смо морали да добијемо оцене за сваког ученика гледајући у табелу система оцена.
Да бисмо то учинили, користићемо атрибут функције ВЛООКУП, а ако функција ВЛООКУП не пронађе потпуно подударање, тражи приближно подударање само у табели и само ако је последњи аргумент функције било ИСТИНА или 1.
Табела оцена / бодова мора бити растућа
Користите формулу:
| = ВЛООКУП (Б2, табела, 2, 1) |
Како то функционише?
Функција Влоокуп тражи вредност 40 у колони ознака и враћа одговарајућу вредност, ако се подудара. Ако се не подудара, функција тражи мању вредност од вредности претраживања (тј. 40) и враћа њен резултат.
Овде је табле_арраи назван ранге ас сто у формули и Б2 је дат као референца ћелије.
Као што видите, добили смо оцену за првог ученика. Сада ћемо за добијање свих осталих оцена користити пречицу Цтрл + Д или користите опцију повлачења ћелије у Екцелу.
Овде су све оцене класе које користе функцију ВЛООКУП.
ЛООКУП вредност између два броја на табели Емплоиее
Имамо табелу која садржи детаље о свим запосленима у организацији на посебном листу. Прва колона садржи ИД ових запослених. Ову табелу сам назвао емп_дата.
Сада мој лист за претраживање мора дохватити податке о запосленом чији је ИД записан у ћелији Б3. Назвао сам Б3 као личну карту.
Ради лакшег разумевања, сви наслови колона су у истом редоследу као и табела емп_дата.
Сада напишите доњу формулу у ћелију Ц3 да бисте дохватили зону ИД -а запосленика написану у Б3.
| = ВЛООКУП (ИД, Емп_Дата, 2,0) |
Ово ће вратити зону запосленика Ид 1-1830456593 јер колона 2 у бази података садржи зону запослених.
Копирајте ову формулу у остале ћелије и промените број колоне у формули да бисте добили све информације о запосленима.
Све информације везане за поменути ИД можете видети у ћелији Б3. Који год ИД упишете у ћелију Б3, све информације ће бити дохваћене без икакве промене у формули.
Како ово ради?
Нема ништа лукаво. Једноставно користимо функцију ВЛООКУП за тражење ИД -а, а затим дохваћање поменуте колоне. Вежбајте ВЛООКУП користећи такве податке да бисте боље разумели ВЛООКУП.
Дохватите податке о запосленима помоћу наслова
У горњем примеру смо имали све колоне организоване истим редоследом, али биће тренутака када ћете имати базу података која ће имати стотине колона. У таквим случајевима овај начин преузимања података о запосленима неће бити добар. Биће боље ако формула може да погледа наслов колоне и преузме податке из те колоне из табеле запослених.
Дакле, за дохваћање вредности из табеле помоћу наслова колона користићемо метод двосмерног тражења или рецимо динамичку колону ВЛООКУП.
Користите ову формулу у ћелији Ц3 и копирајте у остале ћелије. Не морате ништа да мењате у формули, све ће бити преузето из тхд емп_дата.
| = ВЛООКУП (ИД, Емп_Дата, МАТЦХ (Ц2, Емп_Дата_Хеадерс, 0), 0) |
Ова формула једноставно преузима све информације из одговарајућих колона. Заглавља можете да мешате у извештају, то неће имати никакве разлике. Који год наслов је написан у горњој ћелији, одговарајући подаци садрже.
Како ово ради?
Ово је једноставно динамички ВЛООКУП. Можете прочитати о томе овде. Ако то објасним овде, постаће превелик чланак.
Преузмите ИД запосленог са делимичним подударањем
Може се десити да се не сећате целог личног документа запосленог, али ипак желите да преузмете податке о неком личном документу. У таквим случајевима парцијално подударање ВЛООКУП је најбоље решење.
На пример, ако знам да неки ид садржи 2345, али не знам цео ИД. Ако унесем овај број у ћелију Ц3, излаз ће бити сличан.
Не добијамо ништа. Пошто се ништа не подудара са 2345 у табели. Горњу формулу измените овако.
| =ВЛООКУП("*"&ИД&"*", Емп_Дата,УТАКМИЦА(Ц2, Емп_Дата_Хеадерс, 0), 0) |
Копирајте ово у цео ред. А сада имате податке о првом запосленом који садрже овај број.
Имајте на уму да ћемо први ИД који садржи одговарајући број добити у колони Емп Ид. Ако било који други ИД садржи исти број, ова формула неће дохватити податке о запосленом.
Ако желите да добијете све ИД -ове запослених који садрже исти број, користите формулу која тражи све подударне вредности.
Користећи формулу ИНДЕКС и МАТЦХ
Овде имамо табелу података. Желим повући име купца користећи датум резервације, градитеља и површину. Дакле, овде имам три критеријума и један опсег претраживања.
Напишите ову формулу у ћелију И4 притисните ентер.
| = ИНДЕКС (Е2: Е16, МАТЦХ (1, ИНДЕКС ((И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16), 0,1), 0)) |
Како то ради:
Већ знамо како функције ИНДЕКС и МАТЦХ функционишу у ЕКСЦЕЛ -у, па то нећу овде објашњавати. Овде ћемо говорити о трику који смо користили.
(И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16): Главни део је ово. Сваки део ове наредбе враћа низ вредности труе.
Када се логичке вредности помноже, враћају низ од 0 и 1. Множење функционише као оператор АНД. Дакле, када су све вредности тачне, тада враћа 1 елсе 0
(И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16) Ово ће укупно вратити
| {ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ}*
{ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ТРУЕ; ТРУЕ; ТРУЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ}* {ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ФАЛСЕ} |
Што ће се превести у
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
ИНДЕКС ((И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16), 0,1): Функција ИНДЕКС ће вратити исти низ ({0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}) на функцију МАТЦХ као низ за тражење.
МАТЦХ (1, ИНДЕКС ((И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16), 0,1): МАТЦХ функција ће тражити 1 у низу {0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0}. И вратиће индексни број првог 1 пронађеног у низу. Што је овде 8.
ИНДЕКС (Е2: Е16, МАТЦХ (1, ИНДЕКС ((И1 = А2: А16)*(И2 = Б2: Б16)*(И3 = Ц2: Ц16), 0,1), 0)): Коначно, ИНДЕКС ће се вратити вредност из датог опсега (Е2: Е16) при пронађеном индексу (8).
Овде су све напомене о опсервацији које користе формулу у Екцелу
Напомене:
- Колона табеле оцена / резултата мора бити у растућем редоследу или функција може дати погрешан резултат.
- Функција ВЛООКУП тражи вредност у првом реду Табле_арраи -а и издваја одговарајуће вредности само десно од опсега_лоокуп_ранге.
- Последњи аргумент функције ВЛООКУП функције мора бити постављен на ТРУЕ или 1 да би се добило приближно подударање.
- Функција ВЛООКУП враћа грешку ако је адреса радне свеске неважећа или погрешна.
- Функција ВЛООКУП враћа грешку ако се вредност не подудара.
Надам се да је овај чланак о томе како потражити вредност између два броја у Екцелу објашњен. Овде пронађите још чланака о израчунавању вредности и сродних Екцел формула. Ако су вам се допали наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште.
Начин коришћења функције Влоокуп у валидацији података : Ограничите кориснике да дозволе вредности из табеле за претраживање помоћу оквира формуле за проверу ваљаности података у Екцелу. Оквир са формулом у валидацији података омогућава вам да изаберете врсту потребног ограничења.
Како дохватити најновију цену у Екцелу : Уобичајено је ажурирање цена у било ком послу и коришћење најновијих цена за било коју куповину или продају је неопходно. За преузимање најновије цене са листе у Екцелу користимо функцију ЛООКУП. Функција ЛООКУП достиже најновију цену.
ВЛООКУП функција за израчунавање оцене у Екцелу : За израчунавање оцјена ИФ и ИФС нису једине функције које можете користити. ВЛООКУП је ефикаснији и динамичнији за такве условне прорачуне. За израчунавање оцена помоћу ВЛООКУП -а можемо користити ову формулу.
17 ствари о Екцел ВЛООКУП -у : ВЛООКУП се најчешће користи за дохваћање усклађених вриједности, али ВЛООКУП може учинити много више од овога. Ево 17 ствари о ВЛООКУП -у које бисте требали знати да бисте ефикасно користили.
ПОГЛЕДАЈТЕ први текст са листе у Екцелу : Функција ВЛООКУП ради добро са замјенским знаковима. Ово можемо користити за издвајање прве текстуалне вредности са дате листе у екцелу. Ево опште формуле.
ЛООКУП датум са последњом вредношћу на листи : Да бисмо дохватили датум који садржи последњу вредност користимо функцију ЛООКУП. Ова функција проверава да ли ћелија садржи последњу вредност у вектору, а затим користи ту референцу за враћање датума.
Популарни чланци:
50 Екцел пречица за повећање продуктивности : Убрзајте своје задатке у Екцелу. Ове пречице ће вам помоћи да повећате ефикасност рада у програму Екцел.
Како се користи функција ВЛООКУП у програму Екцел : Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција ИФ у програму Екцел : ИФ наредба у Екцелу проверава услов и враћа одређену вредност ако је услов ТРУЕ или враћа другу специфичну вредност ако је ФАЛСЕ.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.