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

  1. Какие есть способы фильтровать данные.Какие есть способы фильтровать данные.
  2. Как настраивать и сохранять встроенные фильтры таблиц.
  3. Как использовать функцию FILTER(), чтобы выдернуть нужные данные из большой таблицы.
  4. Как создавать сводные таблицы одной формулой.

Этот урок займет у вас 15—25 минут. В нем мы объединим наши знания из прошлых уроков с новыми.

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

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

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

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

Как создавать и сохранять фильтры

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

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

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

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

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

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

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

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

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

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

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

Отдельная проблема возникает, когда вы хотите просуммировать отфильтрованные числа. Дело в том, что скрытые ячейки никуда не делись — они все еще существуют, вы их просто не видите. Поэтому, если включить фильтр на рестораны и ввести формулу =SUM(C4:C102), она посчитает не только то, что сейчас видно на экране, а вообще все ячейки с C4 по С102.

Чтобы учесть только те ячейки, что мы видим, есть отдельная универсальная функция — SUBTOTAL(). В нашем случае формула будет выглядеть так: =SUBTOTAL(9;C:C). Вот как она устроена:

  • C:C — это диапазон, который мы будем суммировать;
  • 9 — это код функции.

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

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

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

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

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

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

Запомним

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

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

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

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

Как создавать фильтры функцией 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() хватило места для итоговой таблицы. Например, в нашем случае таблица с результатами работы фильтра занимает 6 строк и 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(). Вот как она должна работать: таблица берет диапазон с тратами, ищет в нем совпадения по дате (первый столбец) и по категории (второй столбец). Те значения трат, где совпадают оба условия, таблица выводит.

На языке таблиц это выглядит так:

=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 заняли траты на транспорт, выписанные в столбик. По-хорошему нам сейчас нужно просуммировать все значения из столбца 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. Протягиваем получившуюся формулу на остальные ячейки с категориями. Наше второе условие, B:B=F2, будет автоматически подстраиваться при протягивании формулы. В третьей строке формула будет искать совпадения с ячейкой F3, а в девятой — с F9:

Можно посчитать траты и за следующие месяцы. Формула будет точно такой же, единственное, что в ней должно поменяться, — номер месяца: с 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. С помощью этой функции и знакомой нам SUM() можно создавать сводные таблицы — в них данные будут суммироваться по категориям. Такие таблицы часто нужны в профессиональной деятельности, когда приходится работать с огромным массивом данных.

Функции и инструменты, которые вы освоили

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

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

  1. Отфильтруйте категории трат.
  2. Посчитайте, сколько в среднем тратите в каждой категории.
  3. Найдите категории с тратами выше среднего.
  4. Настройте окрашивание категорий, где тратите больше и меньше всего.
  5. Настройте счетчик, показывающий время до платежа за квартиру — или другого важного вам платежа.

Для этого вам понадобятся знания из всех пяти пройденных уроков. Удачи!