Фільтрування стає набагато простішим завдяки новим функціям в Excel 365

З осені 2018 року я стежу за звітами про нові функції динамічного масиву Excel. Поки що, однак, ними могли користуватися лише користувачі з каналом оновлення "Інсайдер" та "Щомісячне націлювання". Такі функції, як УНІКАЛЬНИЙ, ФІЛЬТР, СОРТУВАТИ та СОРТУВАТИ ПІСЛЯ, тепер також доступні в щомісячному каналі оновлення.
Оскільки сортування та фільтрування списків є загальним завданням у Excel, я спробував спочатку функції ФІЛЬТРУВАННЯ та СОРТУВАННЯ. Я можу сказати одне заздалегідь: я вражений тим, наскільки легко працювати, і схвильований багатьма можливостями. Ось перший приклад.

Попередній перегляд готового результату за допомогою FILTER

У майбутньому це стане можливим натисканням кнопки: відображення та друк відфільтрованих даних на окремому аркуші

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

Спочатку налаштовується фільтр.
Потім встановлюється критерій фільтра.
Потім відфільтрована база даних копіюється на окремий аркуш.
Нарешті, додається заголовок і результат друкується.

З новою функцією ФІЛЬТР це набагато простіше і швидше:

Просто виберіть бажаний критерій фільтра в одній комірці, наприклад товар, клієнт чи регіон.
Функція ФІЛЬТР негайно генерує список відповідних записів даних. ЗАВЕРШЕНО.

Ось як це працює: Використовуйте FILTER, щоб перерахувати лише певні записи даних

На наступному малюнку наведено набір даних: Продажі, зафіксовані за датою, групою товарів, регіоном, клієнтом, торговим представником (ADM) та чистими продажами.

Я також змінюю список із даними про продажі Ctrl + T в "розумний" стіл. Через вкладку Настільні інструменти Я даю їй ім’я tbl_ продажів до. Цей підхід має дві переваги:

Написати формули стає простіше, тому що мені не доводиться працювати з посиланнями на загадкові комірки.
Крім того, моя таблиця автоматично коригується під час введення та видалення записів даних, і моя оцінка надійно отримує доступ до поточної бази даних.

Витяг з бази даних, яку потрібно відфільтрувати

Я хочу лише відобразити записи для замовника TerraMeda. Ось як я це роблю:

Переходжу на робочий аркуш оцінка і введіть ім’я клієнта в комірку E3, отже, тут TerraMeda.
У рядку 4 бази даних я копіюю заголовки стовпців у стовпцях B до G.
Потім записи даних для обраного замовника повинні бути перелічені на аркуші оцінки з рядка 6. Тож я позначаю клітинку B6 і починаю формулу з = ФІЛЬТР (
Ця функція має три аргументи матриця, зафіксувати і if_blank.
Для першого аргументу матриця перемістіть мишу у верхній лівий кут таблиці, як показано нижче. Миша перетворюється на похилу чорну стрілку. Достатньо одного клацання, і всі записи даних позначені.

Просто позначте базу даних стрілкою під кутом

Після введення крапки з комою випливає другий аргумент: Excel повинен перевірити, чи не відображається ім’я клієнта в комірці E3 у стовпці з клієнтами. Я позначаю стовпець клієнта для пошуку, просто клацнувши над словом у базі даних замовника клацніть (миша тепер вертикальна чорна стрілка). Потім я вводжу знак рівності та клацну клітинку порівняння E3.
Формула поки що = ФІЛЬТР (tbl_Umsatz; tbl_Umsatz [замовник] = оцінка! E3
Мені тут не потрібний третій аргумент. Тож я закриваю формулу дужкою і ввожу.
Результат тепер виглядає майже як на малюнку 1. Тільки формати чисел для стовпця дата і чистий ще потрібно відрегулювати. Я роблю це на вкладці Головна у групі Числа, використовуючи список заздалегідь визначених форматів. Там я вибираю Дата, коротка як от валюта.

Готова формула ФІЛЬТР

Це стає зрозумілішим: сортуйте відфільтровані результати

Часто буває дуже корисно відображати відфільтровані результати у певному порядку, наприклад сортувати за товаром, регіоном чи продажем.
Завдяки новим функціям масиву це робиться всього за кілька кліків. Для цього я включаю функцію ФІЛЬТР, яку я щойно створив, у функцію СОРТУВАННЯ наступним чином.

Я ставлю курсор перед словом ФІЛЬТР у рядку формул.
я вважаю Вибачте a. Excel пропонує функцію СОРТУВАННЯ. Я приймаю цю пропозицію, натискаючи вкладку-кнопку.
Я поставлю курсор у кінець формули і введу там крапку з комою.
Тепер я повинен для аргументу Індекс сортування введіть номер стовпця, за яким слід відсортувати відфільтрований список. Я хочу сортувати за продажами, тому поставив 6, оскільки стовпець 6 матриці - це продажі.
Після чергової крапки з комою приймається рішення, чи сортувати за зростанням чи за спаданням. Я хотів би мати найвищі продажі вгорі, тому виберіть варіант -1.

Для сортування за спаданням -1 вибрати

Впізнайте можливе повідомлення про помилку за допомогою IFERROR

Мій відфільтрований та відсортований аналіз даних все ще має невеликий недолік. Якщо клітинка E3 в аркуші оцінки порожня, тобто якщо не вказано критерій фільтра, у клітинці B6 з’явиться нове повідомлення про помилку # LIME!.

Нове повідомлення про помилку #KALK! у клітинці B6

Я уникаю цього шляхом ...

поставте курсор перед словом SORT у рядку формул,
WENNF і введіть функцію зі списку пропозицій в Excel IFERROR натисканням кнопки вкладку-Кнопка Застосувати,
розташуйте курсор у кінці формули і введіть там крапку з комою та дві лапки,
потім закрийте формулу дужкою і введіть.

Готова формула виглядає так:

Перехоплюйте повідомлення про помилки спеціально за допомогою функції IFERROR

світогляд

Було б елегантніше, якби я міг зручно обрати критерій фільтра в комірці E3 через випадаючий список. У наступному дописі я опишу, як я можу це зробити, перевіряючи дані та використовуючи нову функцію UNIQUE.

І якщо нові функції (поки) недоступні?

Нові функції динамічного масиву доступні лише в Excel 365.
Наразі вони доступні лише в тому випадку, якщо для каналу оновлення встановлено значення “Інсайдерська”, “Щомісячна цільова” чи “Щомісячна”.
Кожен, хто має канал оновлення «два рази на рік», повинен бути терплячим.
Який встановлений канал оновлення можна визначити за допомогою послідовності команд файл >рахунок з'ясувати.

На наступному малюнку наведена інформація, яка відображається для каналу оновлення «Щомісяця». Номер версії тут є визначальним 1911 рік (тобто листопад 2019 р.).

Інформація про канал оновлення та версію, позначені жовтим, є вирішальною