содржина
Сите класични функции за пребарување и замена на типови VPR (VLOOKUP), GPR (ХЛОКУП), ПОИЗЛОЖЕНИ (натпревар) а таквите како нив имаат една важна карактеристика – бараат од почеток до крај, односно од лево кон десно или од врвот до дното во изворните податоци. Веднаш штом ќе се најде првото совпаѓање, пребарувањето запира и се наоѓа само првото појавување на елементот што ни треба.
Што да правиме ако треба да ја најдеме не првата, туку последната појава? На пример, последната трансакција за клиентот, последното плаќање, најновата нарачка итн.?
Метод 1: Наоѓање на последниот ред со формула за низа
Ако оригиналната табела нема колона со датум или сериски број на ред (нарачка, плаќање...), тогаш нашата задача е, всушност, да го најдеме последниот ред што го задоволува дадениот услов. Ова може да се направи со следнава формула за низа:
Тука:
- функција IF (АКО) ги проверува сите ќелии во колона една по една Клиент и го прикажува бројот на линијата ако го содржи името што ни треба. Бројот на линијата на листот ни го дава функцијата ЛИНИЈА (ROW), но бидејќи ни треба бројот на редот во табелата, дополнително треба да одземеме 1, бидејќи имаме заглавие во табелата.
- Потоа функцијата MAX (МАКС) ја избира максималната вредност од формираниот сет на броеви на редови, односно бројот на најновата линија на клиентот.
- функција Индекс (ИНДЕКС) ја враќа содржината на ќелијата со пронајдениот последен број од која било друга потребна колона од табелата (Код за нарачка).
Сето ова мора да се внесе како формула за низа, т.е.
- Во Office 365 со инсталирани најнови ажурирања и поддршка за динамички низи, можете едноставно да притиснете Внесете.
- Во сите други верзии, откако ќе ја внесете формулата, ќе треба да ја притиснете кратенката на тастатурата Ctrl+Префрлат+Внесете, што автоматски ќе му додаде кадрави загради во лентата со формула.
Метод 2: Обратно пребарување со новата функција LOOKUP
Веќе напишав долга статија со видео за нова функција VIEW (XLOOKUP), кој се појави во најновите верзии на Office за да го замени стариот VLOOKUP (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