У овом чланку ћемо научити како користити функцију ИНДЕКС и МАТЦХ за тражење вредности у Екцелу.
ИНДЕКС & МАТЦХ функција за замену функције влоокуп
Обично користимо функцију ВЛООКУП да пронађемо вредност знајући индекс реда и број колоне. За функцију ВЛООКУП морамо имати критеријуме за подударање броја колоне и реда и најважније је да вредност буде десно од одговарајуће вредности. Али подаци то не подржавају увек. Много пута морамо само тражити вредности не познајући индекс реда или колоне, већ само вредности које треба да се подударају. На пример: проналажење плате запосленог који има личну карту. У овоме ћемо тражити ИД запослених за индекс реда и Плату за индекс колоне. Хајде да научимо како можемо да извршимо ово претраживање помоћу ИНДЕКС & МАТЦХ.
Како решити проблем?
Да бисмо формулу прво разумели, морамо се мало осврнути на следеће функције
- ИНДЕКС функција
- МАТЦХ функција
Сада ћемо направити формулу користећи горње функције. Функција подударања ће вратити индекс вредности 1 у пољу заглавља реда. Друга функција МАТЦХ ће вратити индекс вредности 2 из поља заглавља колоне. Бројеви индекса ће се сада уносити у функцију ИНДЕКС како би се добиле вриједности испод тражене вриједности из података таблице.
Општа формула:
| = ИНДЕКС (подаци, МАТЦХ (лоокуп_валуе1, ров_хеадерс, 0, МАТЦХ (лоокуп_валуе2, цолумн_хеадерс, 0))) |
података : низ вредности унутар табеле без заглавља
лоокуп_валуе1 : вредност за тражење у ретку_заглавља.
ров_хеадерс : Поље индекса редова за тражење.
лоокуп_валуе1 : вредност за тражење у заглављу колоне.
цолумн_хеадерс : колона Индексни низ за тражење.
Пример:
Горе наведене изјаве могу бити компликоване за разумевање. Па хајде да то разумемо користећи формулу у примеру
Овде имамо списак резултата које су ученици постигли са својом листом предмета. Морамо пронаћи резултат за одређеног ученика (Гери) и предмет (друштвене студије) како је приказано на слици испод.
Вредност ученика1 мора да се подудара са низом Ров_хеадер, а вредност Субјецт2 мора да се подудара са низом Цолумн_хеадер.
Користите формулу у ћелији Ј6:
| = ИНДЕКС (табела, МАТЦХ (Ј5, ред, 0, МАТЦХ (Ј4, колона, 0))) |
Објашњење:
- Функција МАТЦХ подудара се са Студентовом вредношћу у ћелији Ј4 са низом заглавља реда и враћа његову позицију 3 као број.
- Функција МАТЦХ подудара вредност Субјецт у ћелији Ј5 са низом заглавља колоне и враћа њен положај 4 као број.
- Функција ИНДЕКС узима број индекса ретка и колоне и тражи податке у табели и враћа одговарајућу вредност.
- Аргумент типа МАТЦХ је фиксиран на 0. Пошто ће формула извући потпуно подударање.
Овде су вредности формуле дате као референце ћелија, а заглавље редака, табеле и заглавља ступаца дати као именовани опсези.
Као што можете видети на горњој снимци, добили смо оцену коју је студент добио Гари у Предмету Социологија као 36. И то доказује да формула ради добро и за недоумице погледајте доле напомене за разумевање.
Сада ћемо као бројеве користити приближно подударање са заглављима редова и заглављима колона. Приближно подударање узима само бројчане вриједности јер се не може примијенити на текстуалне вриједности
Овде имамо цену вредности према висини и ширини производа. Морамо пронаћи цену за одређену висину (34) и ширину (21) као што је приказано на слици испод.
Вредност Хеигхт1 мора да се подудара са низом Ров_хеадер, а вредност Видтх2 мора да се подудара са низом Цолумн_хеадер.
Користите формулу у ћелији К6:
| = ИНДЕКС (подаци, МАТЦХ (К4, Хеигхт, 1, МАТЦХ (К5, Видтх, 1))) |
Објашњење:
- Функција МАТЦХ подудара се с висином у ћелији К4 с низом заглавља реда и враћа његову позицију 3 као број.
- МАТЦХ функција одговара вредности Видтх у ћелији К5 са низом заглавља колоне и враћа њен положај 2 као број.
- Функција ИНДЕКС узима број индекса ретка и колоне и тражи податке у табели и враћа одговарајућу вредност.
- Аргумент типа МАТЦХ је фиксиран на 1. Пошто ће формула извући приближно подударање.
Овде су вредности формуле дате као референце ћелија, а заглавље редака, подаци и заглавље ступаца дати као именовани опсези као што је поменуто у горњој снимци.
Такође можете извршити тражење тачних подударања помоћу функција ИНДЕКС и МАТЦХ у Екцелу. Сазнајте више о томе како да извршите претраживање осетљиво на велика и мала слова помоћу функције ИНДЕКС & МАТЦХ у Екцелу. Делимична подударања можете потражити и помоћу џокера у Екцелу.
Као што видите на горњој снимци, добили смо цену добијену по висини (34) & Видтх (21) као 53.10. И то доказује да формула ради добро и за недоумице погледајте доње напомене ради бољег разумевања.
Напомене:
- Функција враћа грешку #НА ако је аргумент поља за претраживање функцији МАТЦХ 2Д низ који је поље заглавља података …
- Функција одговара тачној вредности јер је аргумент типа подударања са функцијом МАТЦХ 0.
- Вредности за тражење могу се дати као референца ћелије или директно помоћу знака навода (") у формули као аргумената.
Надам се да је овај чланак о томе како користити Коришћење функција ИНДЕКС и МАТЦХ за тражење вредности у Екцелу објашњен. Овде пронађите још чланака о израчунавању вредности и сродних Екцел формула. Ако вам се допадају наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у. Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште.
Користите ИНДЕКС и МАТЦХ за тражење вредности : Формула ИНДЕКС-МАТЦХ се користи за динамичко и прецизно тражење вредности у датој табели. Ово је алтернатива функцији ВЛООКУП и превазилази недостатке функције ВЛООКУП.
Користите ВЛООКУП из две или више табела за претраживање : За тражење из више табела можемо узети ИФЕРРОР приступ. За претраживање из више табела, грешка се узима као прекидач за следећу табелу. Друга метода може бити приступ Иф.
Како извршити претраживање осетљиво на велика и мала слова у програму Екцел : Екцелова функција ВЛООКУП не разликује велика и мала слова и она ће вратити прву одговарајућу вредност са листе. ИНДЕКС-МАТЦХ није изузетак, али се може изменити тако да буде осетљив на велика и мала слова. Да видимо како…
Потражите често појављујући текст са критеријумима у Екцелу : Претраживање се најчешће појављује у тексту у опсегу који користимо ИНДЕКС-МАТЦХ са МОДЕ функцијом. Ево методе.
Популарни чланци:
Како се користи функција ИФ у програму Екцел : ИФ наредба у Екцелу проверава услов и враћа одређену вредност ако је услов ТРУЕ или враћа другу специфичну вредност ако је ФАЛСЕ.
Како се користи функција ВЛООКУП у програму Екцел : Ово је једна од најчешће кориштених и најпопуларнијих функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи функција СУМИФ у програму Екцел : Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.
Како се користи функција ЦОУНТИФ у програму Екцел : Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.