Что вы узнаете

  1. Какие есть способы фильтровать данные.
  2. Как настраивать и сохранять встроенные фильтры таблиц.
  3. Как создавать сводные таблицы одной формулой.

Оговоримся сразу: фильтрация данных чаще пригождается в профессиональной деятельности. Hапример, если нужно из большого списка продаж выделить определенные товары или даты, посчитать аналитику рекламных кампаний или проанализировать, сколько публикаций редакция сделала за определенный период.

Но в бытовой жизни фильтрация тоже может быть полезна. Первый пример, который приходит в голову, — это фильтрация списка трат. Предположим, весь январь и февраль вы исправно записывали свои траты — вписывали дату, категорию траты и сумму:

И теперь вы хотите посмотреть все свои траты на рестораны за январь. Для этого есть несколько способов. Эта таблица расположена на листе «Траты» вашей рабочей тетради — заходите, будем настраивать вместе.

СПОСОБ 1
Создаем и сохраняем фильтры

Данные из больших таблиц можно фильтровать прямо на листе — то есть по определенным условиям скрывать ненужные строки и оставлять только нужные. Для этого нужно выделить столбцы, которые вы планируете фильтровать, и нажать кнопку «Создать фильтр»:

Теперь у каждой ячейки в шапке таблицы появился небольшой треугольный значок фильтра — если нажать на него, появится список с правилами отбора. Фильтровать можно двумя способами:

  1. По условию. Например, оставить только строки с датами между 1 и 31 января 2021.
  2. По значению. Можно оставить только строки с конкретным значением, например «Рестораны».

В нашем примере пригодятся сразу оба способа фильтрации. Первым шагом оставим только траты на рестораны: жмем на фильтр у столбца B, сбрасываем галочки со всех значений и помечаем только «Рестораны»:

Теперь в нашем списке остались только траты на рестораны. Остальные данные не удалены, таблица их просто временно скрыла.

Дальше нужно отфильтровать даты — оставить только январские. Даты записаны в первом столбце, поэтому фильтровать будем по нему.

В первом столбце много разных дат, поэтому такой же подход, как с ресторанами, не сработает: нам придется вручную кликнуть на каждую дату в январе, а это неудобно. Так что будем фильтровать не по значению, а по условию.

Наше условие простое: дата должна быть между 01.01.2021 и 31.01.2021. Жмем на треугольник около столбца с датами и выбираем меню «Фильтровать по условию»:

Вы уже видели похожее меню в уроке про условное форматирование. Тут тоже есть список предустановленных условий — нам нужно условие «Между»:

В появившихся полях вводим даты, между которыми мы будем искать траты, и жмем «ОК»:

В результате мы получим список всех трат в ресторанах за январь:

Настроенный фильтр можно сохранить. Например, можно найти траты только на рестораны и сохранить этот фильтр — он появится в меню и его можно будет включать одним кликом, а не настраивать каждый раз заново. Чтобы сохранить выбранные условия фильтра, нужно нажать на стрелочку рядом с пунктом меню, в котором мы включали фильтр в самом начале, и выбрать «Сохранить как фильтр»:

Фильтр автоматически включится — и его можно будет переименовать, скопировать или удалить: в том же меню станет активным пункт «Действия». Отключить и включить сохраненный фильтр можно в этом же меню — в списке фильтров нужно нажать «Не выбрано»:

Если вы сохранили нужный фильтр и хотите снова раскрыть все строки вашей таблицы, достаточно нажать на кнопку меню «Отключить фильтр»:

С помощью фильтров можно не только фильтровать, но и сортировать данные — при этом меняется порядок строк в таблице. Например, траты можно расположить не в хронологическом порядке, а от меньшей к большей. Для этого нужно зайти в меню фильтра у столбца с тратами и выбрать сортировку от А до Я:

Важное уточнение: сортировка меняет местами строки только внутри выделенной фильтрами области — зеленой рамки. Например, если бы у нас был фильтр только по столбцу C, отсортировались бы данные только в нем, а столбцы A и B остались бы нетронутыми. Таблица при этом сбилась бы.

У встроенных фильтров есть преимущество: можно создать любое нужное количество фильтров с разными условиями и включать их в два клика из меню фильтров.

Но есть и минус: для каждого фильтра нужно вручную задавать условия, а таких фильтров может понадобиться очень много — например на каждую категорию трат и на каждый месяц. К тому же в каждый момент вы видите результат работы только одного фильтра — не получится посмотреть сводную картину по всем категориям.

Есть другой подход, который лишен этих недостатков: можно использовать функцию FILTER().

СПОСОБ 2
Создаем фильтры функцией FILTER()

Главное преимущество функции FILTER() в том, что она не меняет исходный лист, а вытягивает из него нужные данные в отдельные ячейки. Функция работает так: мы говорим ей, какой диапазон нужно оценить, и задаем условия, по которым будем искать, — а она в ответ выбирает из таблицы все строки, соответствующие этим условиям.

Проще всего пояснить на примере — вернемся к нашей таблице и снова найдем все траты в ресторанах за январь:

Формулу разместим справа от нашей таблицы — например в ячейке F2. Сначала внутри скобок задаем диапазон, который нужно анализировать, а затем через точку с запятой указываем все условия, которые должны выполняться. Каждое следующее условие отделяем от предыдущего точкой с запятой. В случае с тратами на рестораны в январе все должно выглядеть так:

  1. Ищем в диапазоне A:C, там указаны наши траты.
  2. Первое условие — текст в столбце B совпадает с «Рестораны».
  3. Второе условие — месяц в столбце A совпадает с январем.

В результате формула должна вывести нам все строки, которые соответствуют этим условиям.

Фактически формула может выглядеть так:

=FILTER(A:C;B:B="Рестораны";MONTH(A:A)=1)

Что есть что:

  1. A:C — диапазон, который мы будем анализировать. В нем находятся все наши траты с суммами и датами.
  2. B:B="Рестораны" — первое условие. Мы сказали таблице, что значение в столбце B должно совпадать с «Рестораны».
  3. MONTH(A:A)=1 — второе условие. Мы добавили в него функцию MONTH() — она выделяет месяц из даты. Нас интересует месяц № 1 — январь.

В результате мы получим вот такую мини-таблицу, которая будет начинаться в ячейке F2 — там, где мы разместили формулу с FILTER():

Важно, чтобы функции FILTER() хватило места для итоговой таблицы. Например, в нашем случае таблица с результатами работы фильтра занимает 7 строк и 3 столбца — если бы какая-то из ячеек в этом диапазоне была занята данными, формула выдала бы ошибку:

Функция FILTER() в Экселе

К сожалению, функция FILTER() есть не во всех версиях Экселя — мы не знаем, почему разработчики так поступают. В Гугл-таблицах она работает всегда и везде.

А еще в Экселе у функции немного другой синтаксис — чтобы прописать сразу несколько условий, их нужно разделять не точкой с запятой, а звездочкой, а каждое условие заключать в скобки.

В примере выше наша формула в Гугл-таблицах выглядела так:

=FILTER(A:C;B:B="Рестораны";MONTH(A:A)=1)

А в Экселе она будет выглядеть так:

=FILTER(A:C;(B:B="Рестораны")*(MONTH(A:A)=1))

(B:B="Рестораны") — первое условие

(MONTH(A:A)=1) — второе условие. Между ними — символ *



Еще с помощью функции FILTER() можно создавать удобные сводные таблицы буквально одной формулой. Например, можно посчитать, сколько вы потратили за определенный месяц в разных категориях:

Тонкость в том, что по умолчанию FILTER() выводит список всех трат в столбец. Например, если мы ищем траты в категории «Транспорт», функция выдаст портянку с числами:

Но если нам нужны не все эти числа по отдельности, а сразу их сумма, мы можем поместить функцию FILTER() внутрь функции SUM() — получится конструкция SUM(FILTER()). Результатом будет сумма всех ячеек, которые подходят под условия фильтра.

По смыслу результат такого объединения функций похож на результат уже знакомой вам функции SUMIFS(): мы суммируем только те ячейки, для которых выполняются нужные нам условия. Но функция FILTER() универсальнее, а еще ей проще задавать условия, поэтому во многих случаях она удобнее. Например, чтобы фильтровать по месяцу функцией SUMIFS(), в одном из предыдущих уроков нам приходилось создавать вспомогательный столбец с номером месяца. Для функции FILTER() такие ухищрения не нужны.

Перейдем к практике: подобьем статистику трат по всем категориям за январь и февраль — получится таблица как на иллюстрации выше. Откройте лист «Траты 4» вашей рабочей тетради — там уже забиты нужные траты:

Таблица будет устроена так: в столбце F выписаны все наши категории трат, а в столбце G стоит формула для фильтрации и суммирования, ее достаточно прописать один раз и протянуть вниз, на остальные траты.

Шаг 1. Выписываем категории трат. Можно сделать это вручную, но вы уже знакомы с функцией UNIQUE(), которая анализирует диапазон и выводит из него ячейки с неповторяющимися значениями. Как раз то, что нам нужно. В ячейку F1 запишем формулу =UNIQUE(B:B) — будем искать уникальные значения по всему столбцу B. Для удобства заголовок «Категория» выделим жирным, а в следующий столбец запишем название месяца, который будем анализировать, — «Январь»:

Шаг 2. Ищем все траты в категории «Транспорт» за январь. Мы уже выполняли похожую задачу, но тогда фильтр выводил нам все три столбца таблицы: дату, категорию и трату. Сейчас нам нужны исключительно траты, без двух первых столбцов.

Функция та же — FILTER(). Вот какие переменные мы в нее забьем:

  1. Диапазон.
  2. Условие 1: выбор нужной категории.
  3. Условие 2: выбор нужного месяца.

Соберем это в формулу:

=FILTER(C:C;B:B=F2;MONTH(A:A)=1)

Что есть что:

  1. C:C — диапазон, который мы будем выводить. В этом диапазоне у нас суммы трат.
  2. B:B=F2 — первое условие: значение из столбца B совпадает со значением в ячейке F2 — «Транспорт». Вместо ссылки на ячейку F2 можно было бы вручную прописать "Транспорт", как мы делали раньше, но сейчас мы собираем сводную таблицу по всем категориям — если не использовать ссылки на ячейки, а писать конкретные значения, нам придется вручную указывать их для каждой категории. А со ссылкой мы сможем написать формулу один раз и протянуть ее на все остальные категории.
  3. MONTH(A:A)=1 — второе условие: значение месяца из столбца A совпадает с 1, то есть это январь. Такое мы уже делали.

В результате получится вот такая штука:

Пока что данные в столбцах F и G не соответствуют друг другу: весь столбец G заняли траты на транспорт. Но сейчас мы сделаем финт ушами и приведем все в порядок.

Шаг 3. «Оборачиваем» наш фильтр в формулу суммы. Все, что нам нужно сделать, — взять формулу, которую мы составили на предыдущем этапе, и поместить ее внутрь скобок функции SUM().

Было: =FILTER(C:C;B:B=F2;MONTH(A:A)=1)

Стало: =SUM(FILTER(C:C;B:B=F2;MONTH(A:A)=1))

Если поместить эту формулу в ячейку G2, огромный список трат пропадет — вместо него будет сумма этих трат:

Шаг 4. Протягиваем получившуюся формулу на остальные ячейки с категориями. Тут вы и без нас все знаете:

Можно посчитать траты и за следующие месяцы. Формула будет точно такой же, единственное, что в ней должно поменяться, — номер месяца: с 1 на 2.

Было для января: =SUM(FILTER(C:C;B:B=F2;MONTH(A:A)=1))

Стало для февраля: =SUM(FILTER(C:C;B:B=F2;MONTH(A:A)=2))

Эту формулу мы тоже прописываем для нашей первой категории — транспорта, — а потом протягиваем вниз на остальные:

Если при составлении что-то пошло не так, сверьтесь с решебником, там есть пояснения.

Что вы узнали

  1. Есть несколько способов фильтровать данные. Основные из них — встроенные фильтры в таблицах и функция FILTER().
  2. Встроенные фильтры таблиц можно сохранять, а потом включать и выключать одной кнопкой.
  3. С помощью функции FILTER() можно задать сколько угодно условий и выводить по ним как всю таблицу, так и отдельные ее строки.
  4. Результат действия функции FILTER() — список строк или ячеек, которые соответствуют всем заданным условиям. Убедитесь, что функции хватает свободных ячеек, чтобы развернуть результат.
  5. Функцию FILTER() — да и многие другие — можно помещать внутрь других функций. Например, SUM(FILTER()) выведет не список ячеек, а сразу их сумму.