→ Format as Table) на ленте, фильтр создается автоматически (подробнее об этом инструменте мы поговорим в соответствующей главе ниже).
О наличии фильтра на листе нам сигнализируют кнопка «Фильтр» и собственно кнопки фильтра в ячейках с заголовками.
Теперь можно фильтровать данные. Щелкаем на кнопку в заголовке, чтобы по соответствующему столбцу выбрать значения.
После того как хотя бы в одном столбце выбраны не все значения, то есть фильтрация данных имеет место, визуально происходит следующее: кнопка фильтра в столбце (столбцах) с установленными фильтрами меняется (появляется иконка воронки), цвет номеров строк на листе в тех строках, в которых данные фильтруются, меняется на синий.
Отображаются строки с двумя продуктами из трех — курсами и электронными библиотеками
Чтобы снять фильтрацию в определенном столбце, нажмите «Снять фильтрацию с…» (Clear Filter From) или «Выделить все» (Select All), что сделает все элементы выделенными, а это значит, что фильтрации по столбцу не будет.
Чтобы снять фильтрацию во всех столбцах быстро, проще всего снова нажать Ctrl + Shift + L.
Допустим, нам нужны все сделки с индивидуальными предпринимателями или ООО. Конечно, в нашем случае можно выбрать их и по одному, но что, если клиентов (как это и будет в реальной жизни) намного больше?
Тогда лучше воспользоваться текстовыми фильтрами: в открывающемся списке будет несколько вариантов.
Например, можно выбрать строки, которые начинаются с определенных символов (в нашем случае важно быть уверенными в том, что правовая форма указана именно в начале ячейки).
В фильтре можно использовать символы подстановки: например, если вы хотите фильтровать и ООО, и ОАО, можно поставить знак вопроса вместо второй буквы. Тогда будут фильтроваться все строки с буквами О на первой и третьей позиции и любым символом между ними.
Поэтому, если вам нужны строго два варианта, лучше выбрать «ИЛИ» (Or) и указать их без символов подстановки.
Уникальных чисел, как правило, в любой таблице много, и здесь выбирать или исключать по одному в списке в большинстве случаев неудобно. Помогают числовые фильтры — возможность выбирать все числа выше или ниже определенного значения или в заданном диапазоне.
Например, можно отобразить строки со сделками крупнее 12 000.
Обратите внимание на числовой фильтр «Первые 10» (Top 10). По умолчанию это 10 наибольших значений.
Но здесь можно изменять количество и смотреть на наименьшие, а не наибольшие значения — например, отобразить 5 самых маленьких сделок.
Кроме того, можно наложить условие не по количеству элементов, а по процентам. Например, посмотреть на 20% самых крупных сделок.
Фильтровать можно по цвету (заливки и шрифта), и неважно, был этот цвет изменен вами вручную или он появляется в результате работы условного форматирования.
Предположим, у нас есть правило условного форматирования: выходные дни автоматически заливаются цветом (подробнее о правилах условного форматирования с формулами вы сможете прочитать в соответствующей главе).
Здесь используется формула для форматирования ячеек =ДЕНЬНЕД(D2;2)>5 — мы проверяем, что день недели больше 5-го (то есть 6 или 7). Второй аргумент функции ДЕНЬНЕД определяет тип нумерации; 2 — начало недели с понедельника, то есть ему соответствует единица.
Мы можем отфильтровать (и отсортировать тоже) эти строки по цвету.
В Google Таблицах фильтр устанавливается на панели инструментов или через меню «Данные» (Alt + D → F).
Чтобы настраивать фильтры, которые не будут видны другим пользователям при совместной работе (с книгами Excel, хранящимися в облаке), используйте «Представления» (Sheet Views) — о них подробнее в соответствующей главе ниже.
Результаты фильтрации не обновляются сами. Если вы поменяли данные и какая-то строка уже не должна отображаться при текущих критериях в фильтре, она все равно будет отображаться, пока вы не примените фильтр снова. Если добавились новые данные в конце диапазона, они тоже не отфильтруются автоматически.
Для обновления фильтра нажмите Ctrl + Alt + L. Или кнопку «Повторить» (Reapply) на ленте рядом с кнопкой фильтра.
Если у вас Excel 2021 / Microsoft 365 или вы используете Google Таблицы, можно использовать функции СОРТ / SORT и ФИЛЬТР / FILTER. Так как это функции, то результат (отсортированные и/или отфильтрованные данные) будет обновляться автоматически при изменении исходных данных.
Обычный фильтр и результаты фильтрации в Google Таблицах видны пользователям с любым доступом. Если вы отфильтровали данные с его помощью, то другие люди не увидят скрытые строки, пока не снимут фильтр (снять его они смогут, только имея доступ на редактирование).
Режимы фильтрации (Filter views) видны только тому пользователю, который в этот фильтр зашел. Их можно сохранять под названиями и передавать ссылки на них другим (это очень удобно, когда нужно вместе посмотреть на какой-то срез данных), об этом чуть ниже.
Доступ ко всем созданным в файле фильтрам (режимам фильтрации, Filter views) есть у любого пользователя, даже с доступом на просмотр (но менять и удалять их он не сможет).
Пользователь с доступом на просмотр/комментирование может создавать только временные фильтры или просматривать чужие режимы фильтрации. Временные фильтры не сохраняются после закрытия вкладки и не видны другим пользователям.
Именно режимы фильтрации — оптимальное решение для совместной работы. Обычный фильтр лучше вообще не использовать, если пользователей больше одного. При этом не забывайте, что фильтры сохраняются. По умолчанию — под названиями вида ФильтрN, где N — порядковый номер.
Хорошее правило — периодически удалять все фильтры без названий, чтобы не засорять файл. Если у фильтра нет названия, значит, его создали «на один раз». Правильная же ситуация — когда у каждого отдела/сотрудника/ситуации есть свой фильтр, например: «Иванов», «Маркетинг» или «Проекты в работе с приоритетом 2».
Режимы фильтрации и временные фильтры можно использовать в ситуации, когда кто-то из ваших коллег воспользовался обычной фильтрацией, а вы не хотите его фильтр сбивать. Создайте свой фильтр и смотрите в нем таблицу вместе со скрытыми строками.
Подробнее о фильтрах в Google (и аналоге в Microsoft 365) — в главе «Совместная работа».
Вычисления в Excel: функции и формулы
Что такое формула и функция в Excel
Формула Excel (как и Google Таблиц) — это вычисление в ячейке. В отличие от значения, введенного в ячейку вручную (текста, числа или даты, вставленных как значения или введенных с клавиатуры), формула вычисляется и возвращает результат, который пересчитывается, если изменяются ячейки, которые в этой формуле используются (на которые она ссылается).
Автоматический пересчет формул можно отключить — например, если их в книге очень много и вам не хочется после любого действия ждать, пока большое количество формул обновится. Это можно сделать в параметрах Excel: Параметры → Формулы → Вычисления в книге → Вручную (Options → Formulas → Workbook Calculation → Manual).
А также на ленте во вкладке «Формулы».
Там же справа есть кнопки для пересчета всех листов (F9, Fn + F9) или текущего листа (Shift + F9, Fn + ⇑ + F9).
Конечно, можно использовать формулы в Excel и как обычный калькулятор — без ссылок на другие ячейки, с использованием только чисел и математических знаков. Например, разделить одно число на другое. Хотя наиболее эффективно, конечно, ссылаться на другие ячейки, именно это делает таблицы живыми, позволяя не пересчитывать все вручную и проводить сценарный анализ: менять только некоторые параметры на входе и сразу видеть изменившиеся ячейки, которые зависят от этого параметра — напрямую или опосредованно через другие вычисления.
Формулу всегда видно в строке формул (в примере ниже мы как раз используем формулу Excel как обычный калькулятор, не задействуя другие ячейки в вычислении).
Кроме того, можно включить режим отображения формул: вкладка «Формулы» — «Показать формулы» (Show Formulas), тогда вы будете видеть их прямо в ячейках.
Числовые значения в таком режиме будут отображаться без форматирования.
Ввод формулы начинается со знака «равно» (=). Если вы хотите ввести текст, который начинается с этого знака, введите перед ним апостроф (') — он автоматически превращает значение в ячейке в текст, и оно не будет вычисляться как формула.
В формулах используются:
• знаки математических операций: плюс (+), минус (-), деление (/), умножение (*), возведение в степень (^);
• знак «амперсанд» (&) — он используется для «склеивания» (объединения, конкатенации) текстовых значений;
• круглые скобки для определения порядка операций;
• числа и текст, введенные вручную прямо в формуле (текст указывается в формулах в кавычках);
• ссылки на ячейки и диапазоны (они указываются без кавычек);
• пробел: это оператор пересечения (например, A1:E5 B2:D6 — это ссылка на пересечение двух диапазонов (на их общие ячейки));