Новая серия нашего текстового шоу «Новые функции редактора электронных таблиц, которые появились с обновлением до версии 7.4» посвящена сразу двум функциям СОРТ и ФИЛЬТР. Если после первой серии про ПРОСМOТРХ вам показались скучными и занудными посты про ПОИСКПОЗХ и ПОСЛЕД, то сейчас снова будет просто и даже мелодраматично. Два героя, высокие отношения с данными. Прелесть, а не функции.
Уверены, после прочтения этого текста, вы скажите, что такие простые вещи и внимания-то не заслуживают. Однако это обманчивая простота. Она омыта слезами многих поколений пользователей редакторов электронных таблиц, которые писали длинные громоздкие формулы, чтобы сделать что-то элементарное. Достаточно сказать, что новые функции появились в мире лет 5 назад, а редакторы – десятки лет назад, то есть разработчикам понадобилось время, чтобы всё реализовать. Но теперь СОРТ и ФИЛЬТР особенно радуют тех, кто работают с массивами данных (а это значит, что вводить функции мы будем с помощью Ctrl+Shift+Enter).
Функция СОРТ
Угадайте с нулевого раза, что делает функция СОРТ? Правильно, сортирует диапазон или массив (второе важно для тех, кто работает с массивами). Посмотрите на пример. Исходная таблица отсортирована по второму столбу «Категория».
Если вы можете разглядеть формулу, то она выглядит вот так =СОРТ(A2:H27;2) и всё это заключено в фигурные скобки. Это еще не самый короткий вариант, но и не самый длинный. Давайте опишем синтаксис функции:
=СОРТ(массив,[индекс_сортировки],[порядок_сортировки],[по_столбцу])
Один обязательный параметр и три опциональных:
- массив — диапазон ячеек для фильтрации,
- [индекс_сортировки] – номер столбца или строки для сортировки, в нашем случае 2. Если вы не укажете, по умолчанию сортирует по первому.
- [порядок_сортировки] – можно оставить пустым, указать 1 или -1 – порядок сортировки по возрастанию (установлено по умолчанию) или по убыванию.
- [по_столбцу] – логическое значение, не числовое. При пустом параметре по умолчанию производится сортировка по строкам. Но если ваши данные расположены горизонтально, вы можете ввести ЛОЖЬ и сортировать по столбцам.
Тот пользователь, который не пользуется формулами, может удивиться. Зачем вводить формулы, когда, во-первых, есть функциональные таблицы с готовыми фильтрами, а во-вторых, кнопки сортировки по умолчанию и убыванию прямо на вкладке «Главная». Ответ простой: кнопку в другую формулу не вставишь. А для более простых задач, то есть небольших таблиц, которые легко окинуть взглядом, разумеется, нужно использовать базовые возможности.
Функция ФИЛЬТР
Не будем задавать вопрос, зачем нужна функция ФИЛЬТР. Понятно, что для того, чтобы фильтровать диапазон или массив. Смотрим простой пример.
Наша функция выглядит вот так =ФИЛЬТР(A2:H27;B2:B27=K2) и заключена в фигурные скобки массива. Теперь опишем синтаксис обычными словами (в инструкциях его описывают особенно — «птичьим языком»).
Перед началом напоминаем задачи фильтрации. Есть диапазон, в одном из столбцов которого содержится условие, по которому мы будем фильтровать (город, дата, фамилия, значение ИСТИНА/ЛОЖЬ). В нашем примере в столбце «Категория» мы ищем «Овощи» и на выходе получаем все строки по овощам.
Общий вид функции:
=ФИЛЬТР(массив,включить,[если_пусто])
Два обязательных параметра, один — опциональный:
- массив – все ячейки исходной таблицы,
- включить – столбец или строка, в которых мы ищем условие (критерий фильтрации). Если в этом столбце указано ИСТИНА/ЛОЖЬ или 1/0, то больше ничего не нужно. Если мы ищем конкретное значение (у нас слово «Овощи»), то это нужно прописать с помощью знака равенства
- [если_пусто] – то, что ответит фильтр, если ничего не найдет. Необязательный аргумент.
Чем это функция лучше фильтра, который включается кнопкой на вкладке «Главная»? Тем, что с исходной таблицей ничего не происходит, и тем, что можно использовать в формулах. Если что, фильтровать по нескольким условиям функция, разумеется, умеет.
Найдем «Овощи» и сразу «Самара». Для этого второй параметр записывается двумя скобками, перемноженными с помощью звездочки *. В строку вбиваем формулу такого вида =ФИЛЬТР(A2:H27;(B2:B27=K2)*(E2:E27=L2)) и нажимаем Ctrl+Shift+Enter. Пока всё прекрасно.
Дуэт функций СОРТ+ФИЛЬТР
Раз уже мы обещали мелодраму, то должен быть дуэт функций. Давайте усложним задачу. После фильтрации отсортируем по деньгам – по столбцу номер 6, еще в обратном порядке. Вы уже понимаете, какой вид примет формула? СОРТ будет работать в нашем массиве:
=СОРТ(ФИЛЬТР(A2:H27;(B2:B27=K2)*(E2:E27=L2)); 7;-1)
Результат на скриншоте ниже, специально поместили под предыдущей таблицей для сравнения.
Мы разобрали почти все новые функции. Осталось совсем чуть-чуть. А если хотите отвлечься от таблиц, почитайте посты на другие темы:
- Лайфхак: сохранить файл как изображение
- Это вообще законно? Открываем вложенные в документ файлы
- Новый «Органайзер ПРО» с поддержкой протокола EWS: обзор продукта и инструкция по установке
Пробуете ли вы все новинки или ограничиваетесь необходимыми по работе функциями? Используете ли нашу базу знаний в карточках? О чем еще вам хотелось бы почитать? Пишите удобным для вас способом в VK или в Telegram, а также оставляйте комментарии ниже. Всё прочтем и ответим!
