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