У овом чланку ћемо научити како тражити вредности у 2д табели помоћу функције ИНДЕКС-МАТЦХ-МАТЦХ у Екцелу.
Сценариј:
Претпоставимо да морате да урадите више прегледа из табеле која има стотине колона. У таквим случајевима, коришћење различитих формула за свако тражење ће потрајати превише времена. Шта кажете на креирање формуле за динамичко претраживање коју можете потражити у заглављу. Да, можемо то учинити. Ова формула се назива ИНДЕКС МАТЦХ МАТЦХ формула, или рецимо 2д формула за тражење.
Како решити проблем?
Да бисмо формулу прво разумели, морамо се мало осврнути на следеће функције
- ИНДЕКС функција
- МАТЦХ функција
Функција ИНДЕКС враћа вредност по датом индексу у низу.
МАТЦХ функција враћа индекс првог појављивања вредности у низу (низ са једном димензијом).
Сада ћемо направити формулу користећи горње функције. Функција подударања ће вратити индекс вредности 1 у пољу заглавља реда. Друга функција МАТЦХ ће вратити индекс вредности 2 у пољу заглавља колоне. Бројеви индекса ће се сада уносити у функцију ИНДЕКС да би се добиле вредности испод вредности тражења из података 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.
- Вредности за тражење могу се дати као референца ћелије или директно помоћу знака навода (") у формули као аргумената.
Надам се да сте разумели како се користи Претраживање у 2Д табели помоћу функције ИНДЕКС & МАТЦХ у Екцелу. Овде истражите још чланака у Екцел вредности тражења. Слободно наведите своја питања испод у пољу за коментаре. Ми ћемо вам свакако помоћи.
Користите ИНДЕКС и МАТЦХ за тражење вредности : ИНДЕКС & МАТЦХ функција за тражење вредности према потреби.
Распон СУМ са ИНДЕКС -ом у Екцелу : Користите ИНДЕКС функцију да пронађете СУМ вредности према потреби.
Како се користи функција СУМ у програму Екцел : Пронађите СУМ бројева помоћу функције СУМ објашњене на примеру.
Како се користи функција ИНДЕКС у програму Екцел : Пронађите ИНДЕКС низа помоћу функције ИНДЕКС објашњене на примеру.
Како се користи функција МАТЦХ у програму Екцел : Пронађите МАТЦХ у низу користећи вредност ИНДЕКС унутар функције МАТЦХ објашњено на примеру.
Како се користи функција ЛООКУП у програму Екцел : Пронађите тражену вредност у низу помоћу функције ЛООКУП објашњене на примеру.
Како се користи функција ВЛООКУП у програму Екцел : Пронађите тражену вредност у низу помоћу функције ВЛООКУП објашњене на примеру.
Како се користи функција ХЛООКУП у програму Екцел : Пронађите тражену вредност у низу помоћу функције ХЛООКУП објашњене на примеру.
Популарни чланци
50 Екцел пречица за повећање ваше продуктивности
Измените падајућу листу
Апсолутна референца у Екцелу
Ако са условним обликовањем
Ако са замјенским знаковима
Поглед по датуму
Придружите се имену и презимену у екцелу