Филтрирането става много по-лесно с новите функции в Excel 365

От есента на 2018 г. следя отчети за новите функции на динамичния масив на Excel. Досега обаче те можеха да се използват само от потребители с канал за актуализация „Insider“ и „Monthly target“. Функции като UNIQUE, FILTER, SORT и SORT AFTER вече са налични и в канала за месечно актуализиране.
Тъй като сортирането и филтрирането на списъци е често срещана задача в Excel, първо опитах функциите FILTER и SORT. Мога да кажа едно нещо предварително: Изумен съм колко лесно е да работя и съм развълнуван от многото възможности. Ето първи пример.

Преглед на крайния резултат с FILTER

В бъдеще ще бъде възможно с натискането на един бутон: покажете и отпечатайте филтрирани данни на отделен лист

Тези, които често трябва да филтрират данните си, знаят проблема:

Първо се настройва филтърът.
След това се задава критерият за филтриране.
След това филтрираната база данни се копира на отделен лист.
Накрая се добавя заглавие и резултатът се отпечатва.

С новата функция ФИЛТЪР това е много по-лесно и по-бързо:

Просто изберете желания критерий за филтриране в една клетка, например продукт, клиент или регион.
Функцията FILTER незабавно генерира списък с приложимите записи на данни. ЗАВЪРШЕНИ.

Ето как работи: Използвайте FILTER, за да изброите само определени записи с данни

Следващата фигура показва набора от данни: Продажби, които се записват по дата, група артикули, регион, клиент, търговски представител (ADM) и нетни продажби.

Също така променям списъка с данните за продажбите Ctrl + T в „интелигентна“ маса. Чрез раздела Инструменти за маса Давам й името tbl_ продажби да се. Този подход има две предимства:

Писането на формулите става по-лесно, защото не ми се налага да работя с криптични препратки към клетки.
В допълнение, таблицата ми се коригира автоматично при въвеждане и изтриване на записи от данни и оценката ми надеждно осъществява достъп до текущата база данни.

Извлечение от базата данни, която трябва да бъде филтрирана

Искам да покажа само записите за клиента TerraMeda. Ето как го правя:

Превключвам към работния лист оценка и въведете името на клиента в клетка E3, така че тук TerraMeda.
В ред 4 на базата данни копирам заглавията на колоните в колони Б в G.
След това записите с данни за избрания клиент трябва да бъдат изброени в листа за оценка от ред 6. Така че маркирам клетка B6 и започвам формулата си с = ФИЛТЪР (
Тази функция има три аргумента матрица, заключвам и if_blank.
За първия аргумент матрица преместете мишката в горния ляв ъгъл на таблицата, както е показано по-долу. Мишката се превръща в наклонена черна стрелка. Достатъчно е едно щракване и всички записи на данни са маркирани.

Просто маркирайте базата данни със стрелката под ъгъл

След въвеждане на точка и запетая следва вторият аргумент: Excel трябва да провери дали името на клиента се появява в клетка E3 в колоната с клиентите. Отбелязвам колоната на клиента, която трябва да се търси, като просто щраквам над думата в базата данни клиент щракнете (мишката вече е вертикална черна стрелка). След това въвеждам знак за равенство и щраквам върху клетката за сравнение E3.
Формулата е засега = ФИЛТЪР (tbl_Umsatz; tbl_Umsatz [клиент] = оценка! E3
Нямам нужда от третия аргумент тук. Затова затварям формулата със скоба и Enter.
Резултатът сега изглежда почти като на фигура 1. Само числовите формати за колоната дата и нето все още трябва да се коригира. Правя това в раздела Начало в групата Числа, използвайки списъка с предварително дефинирани формати. Там избирам Дата, кратка като валута.

Готовата формула FILTER

Ето как става по-ясно: Сортирайте филтрираните резултати

Често е много полезно филтрираните резултати да се показват в определен ред, например сортирани по артикул, регион или продажби.
Благодарение на новите функции на масива това се прави само с няколко щраквания. За целта включвам функцията FILTER, която току-що създадох, във функцията SORT, както следва.

Поставих курсора пред думата FILTER в лентата с формули.
Предполагам Извинете а. Excel предлага функцията СОРТИРАНЕ. Приемам това предложение, като натискам раздел-бутон.
Ще поставя курсора в края на формулата и ще въведа точка с точка и запетая там.
Сега трябва да се аргументирам Индекс за сортиране въведете номера на колоната, според която филтрираният списък трябва да бъде сортиран. Искам да сортирам по продажби, затова сложих 6, защото колона 6 от матрицата са продажбите.
След поредната точка и запетая се взема решение дали да се сортира във възходящ или низходящ ред. Бих искал да имам най-високи продажби в горната част, така че изберете опцията -1.

За сортиране в низходящ ред -1 избирам

Уловете възможно съобщение за грешка с IFERROR

Моят филтриран и сортиран анализ на данни все още има малък недостатък. Ако клетка E3 в листа за оценка е празна, т.е. ако не е зададен критерий за филтриране, новото съобщение за грешка се появява в клетка B6 # LIME!.

Новото съобщение за грешка #KALK! в клетка B6

Избягвам това чрез ...

поставете курсора пред думата SORT в лентата с формули,
WENNF и въведете функцията от списъка с предложения в Excel IFERROR чрез натискане на раздел-Бутон за прилагане,
поставете курсора в края на формулата и въведете точка и запетая и две кавички,
след това затворете формулата със скоба и въведете.

Готовата формула изглежда така:

Прихващайте съобщенията за грешки специално с функцията IFERROR

перспектива

Би било по-елегантно, ако мога удобно да избера критерия за филтриране в клетка E3 чрез падащ списък. В следващия пост ще опиша как мога да направя това, като проверя данните и използвам новата функция UNIQUE.

И ако новите функции все още не са налични?

Новите функции на динамичен масив са налични само в Excel 365.
Понастоящем те са налични само ако каналът за актуализация е зададен на „Insider“ или „Месечно насочено“ или „Месечно“.
Всеки, който има канал за актуализация „два пъти годишно“, трябва да бъде търпелив.
Кой канал за актуализация е зададен може да се определи чрез последователността на командите файл >сметка разбирам.

Следващата фигура показва информацията, която се показва за канала за ежемесечно актуализиране. Тук решаващ е номерът на версията 1911 (т.е. ноември 2019 г.).

Информацията за канала за актуализация и версията, маркирани в жълто, е от решаващо значение