змест
Усе класічныя функцыі пошуку і падстаноўкі тыпу ВПР (ВПР), GPR (ГРН), БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ) і ў такіх, як яны, ёсць адна важная асаблівасць - яны шукаюць ад пачатку да канца, гэта значыць злева направа або зверху ўніз у зыходных дадзеных. Як толькі знойдзена першае адпаведнае супадзенне, пошук спыняецца і будзе знойдзены толькі першы элемент, які нам патрэбны.
Што рабіць, калі трэба знайсці не першае, а апошняе ўваходжанне? Напрыклад, апошняя транзакцыя для кліента, апошні плацёж, апошні заказ і г.д.?
Метад 1: Пошук апошняга радка з дапамогай формулы масіву
Калі ў зыходнай табліцы няма слупка з датай або парадкавым нумарам радка (заказ, плацёж…), то наша задача, уласна, знайсці апошні радок, які задавальняе зададзенай умове. Гэта можна зрабіць з дапамогай наступнай формулы масіва:
Тут:
- функцыя IF (КАЛІ) правярае ўсе вочкі ў слупку адну за адной Кліент і адлюстроўвае нумар радка, калі ён змяшчае патрэбнае нам імя. Нумар радка на аркушы задаецца нам функцыяй ЛІНІЯ (РАДОК), але паколькі нам патрэбны нумар радка ў табліцы, мы дадаткова павінны адняць 1, таму што ў нас ёсць загаловак у табліцы.
- Затым функцыя MAX (МАКС.) выбірае максімальнае значэнне са сфармаванага набору нумароў радкоў, г.зн. нумар апошняй радкі кліента.
- функцыя індэкс (ІНДЭКС) вяртае змесціва ячэйкі са знойдзеным апошнім лікам з любога іншага неабходнага слупка табліцы (код заказу).
Усё гэта трэба ўводзіць як формула масіва, гэта значыць:
- У Office 365 з усталяванымі апошнімі абнаўленнямі і падтрымкай дынамічных масіваў вы можаце проста націснуць Уводзіць.
- Ва ўсіх астатніх версіях пасля ўводу формулы прыйдзецца націскаць спалучэнне клавіш Ctrl+Зрух+Уводзіць, які аўтаматычна дадасць да яго фігурныя дужкі ў радку формул.
Спосаб 2: Зваротны пошук з дапамогай новай функцыі LOOKUP
Я ўжо напісаў вялікі артыкул з відэа пра новую функцыю ПОГЛЯД (XLOOKUP), які з'явіўся ў апошніх версіях Office замест старога VLOOKUP (ВПР). З дапамогай BROWSE наша задача вырашаецца даволі элементарна, т.к. для гэтай функцыі (у адрозненне ад VLOOKUP) вы можаце яўна задаць кірунак пошуку: зверху ўніз або знізу ўверх - за гэта адказвае яе апошні аргумент (-1):
Спосаб 3. Пошук радка з апошняй датай
Калі ў зыходных дадзеных у нас ёсць слупок з парадкавым нумарам або датай, якая выконвае аналагічную ролю, то задача мадыфікуецца – трэба знайсці не апошні (найменшы) радок з супадзеннем, а радок з апошнім ( максімальная) дата.
Я ўжо падрабязна абмяркоўваў, як гэта зрабіць з дапамогай класічных функцый, а цяпер давайце паспрабуем выкарыстаць магчымасці новых функцый дынамічнага масіву. Для большай прыгажосці і зручнасці мы таксама канвертуем арыгінальную табліцу ў «разумную» з дапамогай спалучэння клавіш Ctrl+T або каманды Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу).
З іх дапамогай гэтая «пара-забойца» вырашае нашу праблему вельмі вытанчана:
Тут:
- Функцыя першая Фільтр (ФІЛЬТР) выбірае толькі тыя радкі з нашай табліцы, дзе ў слупку Кліент – патрэбная нам назва.
- Затым функцыя GRADE (сартаваць) сартуе выбраныя радкі па даце ў парадку змяншэння, з апошняй здзелкай уверсе.
- функцыя індэкс (ІНДЭКС) здабывае першы радок, г.зн. вяртае апошнюю патрэбную здзелку.
- І, нарэшце, знешняя функцыя FILTER выдаляе лішнія 1-ы і 3-і слупкі з вынікаў (код заказу и Кліент) і пакідае толькі дату і суму. Для гэтага выкарыстоўваецца масіў канстант. {0;1;0;1}, вызначаючы, якія слупкі мы хочам (1) або не хочам (0) адлюстроўваць.
Спосаб 4: Пошук апошняга супадзення ў Power Query
Што ж, для паўнаты карціны давайце паглядзім на рашэнне нашай праблемы зваротнага пошуку з выкарыстаннем надбудовы Power Query. З яе дапамогай усё вырашаецца вельмі хутка і прыгожа.
1. Давайце пераўтворым нашу зыходную табліцу ў «разумную» з дапамогай спалучэння клавіш Ctrl+T або каманды Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу).
2. Загрузіце яго ў Power Query з дапамогай кнопкі З табліцы/дыяпазону таб Дата (Дадзеныя — з табліцы/дыяпазону).
3. Мы сартуем (праз выпадальны спіс фільтра ў загалоўку) нашу табліцу ў парадку змяншэння даты, так што самыя апошнія транзакцыі знаходзяцца зверху.
4… Ва ўкладцы Пераўтварэнне выбраць каманду група па (Пераўтварэнне — Група па) і ўсталюйце групоўку па кліентах, а ў якасці функцыі агрэгацыі абярыце опцыю Усе радкі (Усе радкі). Вы можаце назваць новы слупок як заўгодна - напрыклад дэталі.
Пасля групоўкі мы атрымаем спіс унікальных імёнаў нашых кліентаў і ў слупку дэталі – табліцы з усімі транзакцыямі кожнай з іх, дзе першым радком будзе апошняя транзакцыя, што нам і трэба:
5. Дадайце новы вылічальны слупок з дапамогай кнопкі Карыстальніцкі слупок таб Дадаць слупок (Дадаць слупок — Дадаць карыстальніцкі слупок)і ўвядзіце наступную формулу:
Тут дэталі – гэта калонка, з якой мы бярэм табліцы па кліентах, і 0 {} гэта нумар радка, які мы хочам атрымаць (нумарацыя радкоў у Power Query пачынаецца з нуля). Атрымліваем слупок з запісамі (запіс), дзе кожны запіс з'яўляецца першым радком кожнай табліцы:
Засталося разгарнуць змесціва ўсіх запісаў кнопкай з падвойнымі стрэлкамі ў загалоўку слупка Апошняя справа выбар патрэбных слупкоў:
… а затым выдаліць слупок, які больш не патрэбны дэталі пстрыкнуўшы правай кнопкай мышы на яго назве - Выдаліць слупкі (Выдаліць слупкі).
Пасля загрузкі вынікаў на ліст праз Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць (Галоўная старонка — Зачыніць і загрузіць — Зачыніць і загрузіць у…) мы атрымаем такую прыгожую табліцу са спісам апошніх транзакцый, як мы і хацелі:
Калі вы змяняеце зыходныя дадзеныя, не забудзьцеся абнавіць вынікі, пстрыкнуўшы іх правай кнопкай мышы - каманда Абнавіць і захаваць (Абнавіць) або спалучэнне клавіш Ctrl+Alt+F5.
- Функцыя LOOKUP з'яўляецца нашчадкам VLOOKUP
- Як выкарыстоўваць новыя функцыі дынамічнага масіву SORT, FILTER і UNIC
- Пошук апошняй непустой ячэйкі ў радку або слупку з дапамогай функцыі LOOKUP