Привет, коллеги! На связи Ленивый аналитик.
Несмотря на то, что в последнее время я мало пишу про сам Excel (больше про Power Query/Pivot) - вспомнить, как оно экселе да формулами, будет не лишним.
Заодно проведем параллели с Power Query, чтобы вы решили для себя - что удобнее в разных ситуациях.
Для чего нам нужен Power Query? Помимо загрузки - это трансформация данных. Фильтрация, удаление дубликатов, лишних пробелов и т.д. Но если Power Query пока too much для вас, то можно и формулами это сделать.
Функция 1: УНИК — Мгновенное удаление дубликатов и извлечение списков
- Проблема: Повторяющиеся строки искажают итоги, сводные таблицы и отчеты.
- Решение: =УНИК(диапазон)
- Примеры:
- Получение списка уникальных клиентов из журнала заказов.
- Удаление дублирующихся транзакций.
Полностью показываем свою мощь с использованием их с "умными" таблицами.
- Важно: Это динамическая формула. Новые данные в исходном диапазоне автоматически появятся в результате (в примере ниже уже добавлен еще один сотрудник, и он появился в списке уникальных значений).
- Стоп-фактор использования функции: версия Excel.
Функция УНИК доступна в Excel от 2021 года и моложе, а так же в подписке Office 365.
- Что делать, если у вас "старый" Excel: использовать Power Query для удаление дубликатов или сделать многоступенчатую схему на формулах.
Можно удалить дубликаты стандартным инструментом на вкладке Данные - Удалить дубликаты. Но это будет статичный список, который нужно будет переделывать каждый раз при обновлении исходника.
Заменой УНИК будет следующая конструкция:
1. Добавляем технический столбец, который будет проставлять значение 1, если элемент встречается первый раз в списке, и значение 0 для всех последующих случаев.
2. Добавляем технический столбец, который присваивает порядковый номер каждому уникальному элементу
3. Создаем список со значениям от 1 до числа предполагаемой величины списка (у нас 7) и при помощи ВПР подтягиваем значения из таблицы-источника по столбцу Порядковый номер.
Минусы очевидны - полностью ручная поддержка. Но если версия Excel не позволяет, а желания пользоваться Power Query нет - вполне вариант.
Функция 2: ФИЛЬТР — Молниеносная фильтрация и выборка по условиям
- Проблема: Нужно быстро вытащить данные, отвечающие нескольким критериям, без создания сложных сводных таблиц.
- Решение: =ФИЛЬТР(массив_данных, условие1 * условие2, "Нет данных")
- Пример:
- Выбор всех заказов со статусом "Выполнен" по подразделению П1.
Функцию ФИЛЬТР также удобно использовать с "умными" таблицами.
- Стоп-фактор использования функции: версия Excel.
Функция ФИЛЬТР, как и УНИК, доступна в Excel от 2021 года и моложе, а так же в подписке Office 365.
- Что делать, если у вас "старый" Excel: использовать Power Query для создания динамического отфильтрованного списка.
Также возможно фильтровать данные через расширенный фильтр: вкладка Данные - блок Сортировка и фильтр - Дополнительно.
Для диапазона условий заголовки должны полностью совпадать с заголовками таблицы.
Ну и всегда можно отфильтровать таблицу вручную. Но неудобно.
Функции 3: СЖПРОБЕЛЫ и ПЕЧСИМВ — Борьба с невидимым хаосом (пробелы и символы)
- Проблема: Лишние пробелы в начале, конце или между словами, а также непечатаемые символы (переносы строк в ячейке) мешают корректному поиску, сопоставлению и фильтрации.
- Решение:
- =СЖПРОБЕЛЫ(текст) — удаляет все лишние пробелы, кроме одиночных между словами.
- =ПЕЧСИМВ(текст) — удаляет непечатаемые символы.
- Комбо: =СЖПРОБЕЛЫ(ПЕЧСИМВ(A1))
- Пример: удалим лишние пробелы и переносы строки из текстовых ячеек
- Стоп-фактор использования функции: отсутствуют. Эти формулы работают и в старых версиях Excel.
Кстати, это, пожалуй, один из тех редких случаев, где Power Query проигрывает Excel-формулам по функционалу!
Потому что то, что в Excel решается в одну строку вложенных формул, в PQ сделать немного сложнее.
Чтобы убрать переносы строк, нужно использовать инструмент Очистить: вкладка Преобразование - Столбец Текст - Очистить. Инструмент Очистить удаляет переносы строк и непечатные символы. Но не лишние пробелы!
Лишние пробелы удаляются инструментов Усечь (находится там же рядом с Очистить). Но - он удаляет лишние пробелы только в начале и в конце строки.
А чтобы удалить лишние пробелы внутри строки, нужно прибегнуть к языку М: создать новый шаг и в строке формул написать следующую конструкцию
= Table.TransformColumns(#"Обрезанный текст", {{"Исходник", each Text.Combine(List.RemoveItems(Text.Split(_, " "), {""}), " "), type text}})
где Обрезанный текст - это наименование предыдущего шага (заменить на свое),
а Исходник - название столбца с данными (тоже заменить на свое)
Такая вот небольшая заморочка, которая, впрочем, оправдывает себя тем, что проделать ее нужно лишь один раз.
________________________________________________________________________________
Еще одна продвинутая функция в EXCEL, которую я называю программирование на минималках - функция LET. О ней я рассказала в своем телеграмм-канале Ленивый аналитик.
________________________________________________________________________________
Надеюсь, было полезно.
В следующий раз, когда столкнетесь с «грязными» данными, не хватайтесь за их очистку вручную — вспомните про этот спасательный круг.