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