Садржај
Све класичне функције претраге и замене типова ВПР (ВЛООКУП), ГПР (ХЛООКУП), ИЗЛОЖЕНИЈИ (УТАКМИЦА) а они попут њих имају једну важну особину – претражују од почетка до краја, односно с лева на десно или одозго на доле у изворним подацима. Чим се пронађе прво подударање, претрага се зауставља и проналази се само прво појављивање елемента који нам је потребан.
Шта да радимо ако треба да пронађемо не прву, већ последњу појаву? На пример, последња трансакција за клијента, последња уплата, последња поруџбина, итд.?
Метод 1: Проналажење последњег реда са формулом низа
Ако оригинална табела нема колону са датумом или серијским бројем реда (поруџбина, уплата…), онда је наш задатак, у ствари, да пронађемо последњи ред који задовољава задати услов. Ово се може урадити помоћу следеће формуле низа:
Овде:
- функција IF (АКО) проверава све ћелије у колони једну по једну Купац и приказује број реда ако садржи име које нам је потребно. Број реда на листу нам даје функција ЛИНЕ (РЕД), али пошто нам је потребан број реда у табели, додатно морамо да одузмемо 1, јер имамо заглавље у табели.
- Затим функција МАКС (МАКС) бира максималну вредност из формираног скупа бројева редова, односно број последње линије клијента.
- функција ИНДЕКС (ИНДЕКС) враћа садржај ћелије са пронађеним последњим бројем из било које друге обавезне колоне табеле (Шифра поруџбине).
Све ово се мора унети као формула низа, односно:
- У Оффице 365 са инсталираним најновијим ажурирањима и подршком за динамичке низове, можете једноставно да притиснете унети.
- У свим осталим верзијама, након уноса формуле, мораћете да притиснете пречицу на тастатури Цтрл+смена+унети, који ће му аутоматски додати витичасте заграде у траци формуле.
Метод 2: Обрнуто тражење са новом функцијом ЛООКУП
Већ сам написао дугачак чланак са видео снимком о новој функцији ПОГЛЕД (КСЛООКУП), који се појавио у најновијим верзијама система Оффице да би заменио стари ВЛООКУП (ВЛООКУП). Уз помоћ БРОВСЕ наш задатак је решен сасвим елементарно, јер. за ову функцију (за разлику од ВЛООКУП-а), можете експлицитно да подесите смер претраге: одозго надоле или одоздо према горе – њен последњи аргумент (-1) је одговоран за ово:
Метод 3. Потражите стринг са најновијим датумом
Ако у изворним подацима имамо колону са серијским бројем или датумом који има сличну улогу, онда је задатак измењен – не треба да пронађемо последњи (најнижи) ред са подударањем, већ ред са најновијим ( максимум) датум.
Већ сам детаљно расправљао о томе како се то ради помоћу класичних функција, а сада хајде да покушамо да искористимо снагу нових функција динамичког низа. За већу лепоту и практичност, такође претварамо оригиналну табелу у „паметну“ табелу користећи пречицу на тастатури Цтрл+T или команде Почетна – Формат као табела (Почетна — Формат као табела).
Уз њихову помоћ, овај „пар убица“ веома грациозно решава наш проблем:
Овде:
- Прво функција ФИЛТЕР (ФИЛТЕР) бира само оне редове из наше табеле где је у колони Купац – име које нам је потребно.
- Затим функција РАЗРЕД (ВРСТА) сортира изабране редове по датуму у опадајућем редоследу, са најновијим договором на врху.
- функција ИНДЕКС (ИНДЕКС) извлачи први ред, односно враћа последњу трговину која нам је потребна.
- И, коначно, екстерна функција ФИЛТЕР уклања додатне 1. и 3. колоне из резултата (Шифра поруџбине и Купац) и оставља само датум и износ. За ово се користи низ константи. {0;1;0;1}, дефинишући које колоне желимо (1) или не желимо (0) да прикажемо.
Метод 4: Проналажење последњег подударања у Повер Куерију
Па, ради комплетности, хајде да погледамо решење нашег проблема обрнутог претраживања помоћу додатка Повер Куери. Уз њену помоћ све се решава веома брзо и лепо.
1. Хајде да претворимо нашу оригиналну табелу у „паметну“ користећи пречицу на тастатури Цтрл+T или команде Почетна – Формат као табела (Почетна — Формат као табела).
2. Учитајте га у Повер Куери помоћу дугмета Из табеле/опсега табулатор Датум (Подаци — из табеле/опсега).
3. Ми сортирамо (преко падајуће листе филтера у заглављу) нашу табелу по опадајућем редоследу датума, тако да су најновије трансакције на врху.
4… На картици Трансформација изабрати тим Група од (Трансформ — Групирај по) и подесите груписање по купцима, и као функцију агрегирања изаберите опцију Све линије (Сви редови). Нову колону можете именовати како год желите – на пример Detalji.
Након груписања, добићемо листу јединствених имена наших клијената и у колони Detalji – табеле са свим трансакцијама сваке од њих, где ће први ред бити последња трансакција, што нам је потребно:
5. Додајте нову израчунату колону помоћу дугмета Прилагођена колона табулатор Додај колону (Додај колону — Додај прилагођену колону)и унесите следећу формулу:
Овде Detalji – ово је колона из које узимамо табеле по купцима, и 0 {} је број реда који желимо да издвојимо (нумерација редова у Повер Куерију почиње од нуле). Добијамо колону са записима (Запис), где је сваки унос први ред из сваке табеле:
Остаје да проширите садржај свих записа помоћу дугмета са двоструким стрелицама у заглављу колоне Последњи договор одабиром жељених колона:
… а затим избришите колону која више није потребна Detalji десним кликом на њен наслов – Уклоните колоне (Уклони колоне).
Након учитавања резултата на лист кроз Почетна — Затвори и учитај — Затвори и учитај (Почетна — Затвори и учитај — Затвори и учитај до…) добићемо тако лепу табелу са листом недавних трансакција, као што смо желели:
Када мењате изворне податке, не смете да заборавите да ажурирате резултате десним кликом на њих – команда Ажурирајте и сачувајте (Освјежи) или пречица на тастатури Цтрл+други+F5.
- Функција ЛООКУП је потомак ВЛООКУП-а
- Како користити нове функције динамичког низа СОРТ, ФИЛТЕР и УНИЦ
- Проналажење последње ћелије која није празна у реду или колони помоћу функције ЛООКУП