Съдържание
Всички класически функции за търсене и заместване на тип ВПР (VLOOKUP), GPR (HLOOKUP), ПО-ИЗЛОЖЕН (СЪВПАДА) а такива като тях имат една важна особеност – търсят от началото до края, т.е. отляво надясно или отгоре надолу в изходните данни. Веднага щом бъде намерено първото съвпадение, търсенето спира и се намира само първото срещане на необходимия ни елемент.
Какво да правим, ако трябва да намерим не първото, а последното срещане? Например последната транзакция за клиента, последното плащане, последната поръчка и т.н.?
Метод 1: Намиране на последния ред с формула за масив
Ако оригиналната таблица няма колона с дата или пореден номер на ред (поръчка, плащане...), тогава нашата задача всъщност е да намерим последния ред, който отговаря на даденото условие. Това може да стане със следната формула за масив:
Тук:
- функция IF (АКО) проверява всички клетки в колона една по една Клиентски и показва номера на реда, ако съдържа името, от което се нуждаем. Номерът на реда на листа ни се дава от функцията LINE (РЕД), но тъй като се нуждаем от номера на реда в таблицата, трябва допълнително да извадим 1, защото имаме заглавка в таблицата.
- След това функцията MAX (Макс) избира максималната стойност от формирания набор от номера на редове, т.е. номера на последния ред на клиента.
- функция INDEX (ИНДЕКС) връща съдържанието на клетката с намереното последно число от всяка друга необходима колона на таблицата (Код на поръчката).
Всичко това трябва да бъде въведено като формула за масив, т.е.:
- В Office 365 с най-новите инсталирани актуализации и поддръжка за динамични масиви можете просто да натиснете Въведете.
- Във всички останали версии, след като въведете формулата, ще трябва да натиснете клавишната комбинация Ctrl+Превключване+Въведете, което автоматично ще добави фигурни скоби към него в лентата за формули.
Метод 2: Обратно търсене с новата функция LOOKUP
Вече написах дълга статия с видеоклип за нова функция ВИЖТЕ (XLOOKUP), който се появи в най-новите версии на Office, за да замени стария VLOOKUP (VLOOKUP). С помощта на BROWSE нашата задача се решава доста елементарно, т.к. за тази функция (за разлика от VLOOKUP), можете изрично да зададете посоката на търсене: отгоре надолу или отдолу нагоре – последният й аргумент (-1) е отговорен за това:
Метод 3. Потърсете низ с най-новата дата
Ако в изходните данни имаме колона със сериен номер или дата, която играе подобна роля, тогава задачата се модифицира – трябва да намерим не последния (най-долния) ред със съвпадение, а реда с най-късния ( максимална) дата.
Вече обсъдих подробно как да направя това с помощта на класически функции, а сега нека се опитаме да използваме силата на новите функции за динамичен масив. За по-голяма красота и удобство ние също преобразуваме оригиналната маса в „интелигентна“ маса с помощта на клавишна комбинация Ctrl+T или команди Начало – Форматиране като таблица (Начало — Форматиране като таблица).
С тяхна помощ тази „убийствена двойка“ решава нашия проблем много грациозно:
Тук:
- Първо функция FILTER (ФИЛТРИРАНЕ) избира само онези редове от нашата таблица, където в колоната Клиентски – името, от което се нуждаем.
- След това функцията КЛАС (ВИД) сортира избраните редове по дата в низходящ ред, като най-скорошната сделка е най-отгоре.
- функция INDEX (ИНДЕКС) извлича първия ред, т.е. връща последната сделка, от която се нуждаем.
- И накрая, външната функция 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+Друг+F5.
- Функцията LOOKUP е наследник на VLOOKUP
- Как да използвате новите функции за динамичен масив SORT, FILTER и UNIC
- Намиране на последната непразна клетка в ред или колона с функцията LOOKUP