Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

строк, удовлетворяющих одному или нескольким условиям, то используем функцию СЧЁТЕСЛИ / COUNTIF или СЧЁТЕСЛИМН / COUNTIFS. Работает она аналогично, только диапазона суммирования/усреднения у нее нет — мы ничего не суммируем, а только подсчитываем, сколько строк удовлетворяют заданным условиям:

=СЧЁТЕСЛИМН(B2:B49;"электронная библиотека")

Общие правила записи условий в этих функциях:

• в них используются знаки сравнения: «больше» (>), «меньше» (<), «больше либо равно» (>=), «меньше либо равно» (<=), «не равно» (<>);

• если вы ищете точное совпадение с текстовым значением, а не сравниваете числа и даты, то эти знаки не нужны; просто укажите текст в кавычках или дайте ссылку на ячейку с текстом;

• условие берется в кавычки;

• можно ссылаться на ячейки с условиями (в таком случае нужно объединять знаки в условии со ссылкой на ячейку через амперсанд &) или указывать условия прямо в формуле (обратите внимание, что условия, как любые текстовые значения в формулах, указываются в кавычках).

Вот как записываются условия на разные типы данных.

Обратите внимание, что в условиях всех функций …ЕСЛИМН / …IFS регистр не учитывается, то есть вы можете ввести условие и как "МОСКВА", и как "Москва", и как "москва" — в любом случае все ячейки, в которых это слово встречается (и тоже в любом регистре), попадут в расчет.

СИМВОЛЫ ПОДСТАНОВКИ (WILDCARD CHARACTERS) В ФУНКЦИЯХ …ЕСЛИМН / …IFS

В условиях функций можно использовать два символа подстановки — * (звездочка) и ? (знак вопроса):

* — текстовая строка любой длины, включая нулевую (то есть на месте звездочки может быть любой текст или не быть вообще ничего);

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

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

И если нам нужно подсчитать продажи книг Роулинг, например, нам необходимо добавить по звездочке слева и справа от фамилии автора в условии функции:

=СУММЕСЛИМН(C: C;A: A;"*Роулинг*")

Это условие — любой текст (в том числе ничего, текст нулевой длины) + Роулинг (в любом регистре, напомним) + любой текст.

То есть слово между звездочек может встречаться в любом месте в ячейке. Если бы было нужно, например, искать ячейки с «Роулинг» только в конце, то условие выглядело бы так:

"*Роулинг"

Здесь все заканчивается на фамилии автора — после нее уже не предполагается никаких символов.

Что, если мы хотим выяснить сумму продаж или среднее значение по книгам с названиями из определенного количества символов? Например, только из четырех, как «Дюна» или «1984».

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

*«*»*

это любой текст, в котором встречаются кавычки-«елочки» с любым же текстом внутри.

Поэтому тут нам понадобится знак вопроса — это один любой символ. А значит, нам подойдет следующий шаблон:

*«????»*

Это любой текст + четыре любых символа внутри кавычек-«елочек» + любой текст.

Если вам нужно найти именно звездочки или знаки вопроса (например, чтобы удалить все звездочки в какой-то таблице), поставьте перед символом тильду (~):

~* — поиск звездочки;

— ? — поиск знака вопроса;

~~ — поиск самой тильды.

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

=СУММЕСЛИМН(C: C;A: A;"*~**")

ПОДСЧЕТ УНИКАЛЬНЫХ ЗНАЧЕНИЙ ПО УСЛОВИЯМ: ФУНКЦИЯ COUNTUNIQUEIFS В GOOGLE ТАБЛИЦАХ

Google Таблица с примером: COUNTUNIQUEIFS

https://mif.to/I0T9W

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

COUNTUNIQUEFS позволяет делать это с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.

Минимальные и максимальные значения с условиями

Функции МИН и МАКС

Для вычисления минимальных и максимальных значений есть функции с простыми названиями МИН / MIN и МАКС / MAX и таким же простым синтаксисом — в качестве аргумента (аргументов) указываются один или несколько диапазонов.

Вычисляем минимальную величину сделки в таблице

Функции МИНЕСЛИ, МАКСЕСЛИ

Файл с примерами: МИНЕСЛИ и МАКСЕСЛИ.xlsx

Начиная с Excel 2016, можно вычислять минимальное и максимальное значение по условиям: например, максимальную сделку не вообще, а с определенным типом товара. Синтаксис функций такой же, как у функций СУММЕСЛИМН, СРЗНАЧЕСЛИМН:

=МАКСЕСЛИ(максимальный_диапазон; диапазон_условия1; условие1; …)

Максимальный диапазон — диапазон, в котором мы ищем максимальное число.

В Google Таблицах эти функции тоже есть, названия у них там на английском при любом языке формул: MAXIFS, MINIFS. А вот функции МИН и МАКС будут иметь названия на русском, если у вас русскоязычные формулы.

Расширенный фильтр

Файл с примерами: Расширенный фильтр и функции БД.xlsx

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

Напомним, что обычный автофильтр можно включить на вкладке «Данные» → «Фильтр» (Data → Filter), а также с помощью сочетания клавиш Ctrl + Shift + L (

 + ⇑ + F). Кроме того, при создании таблицы (Ctrl + T или Ctrl + L) кнопки фильтра тоже появятся.

Расширенный же фильтр (диалоговое окно с его настройками) находится справа от обычного: в русскоязычном Excel это кнопка «Дополнительно», а в англоязычном интерфейсе — Advanced.

Отличия расширенного фильтра от обычного фильтра на рабочем листе в следующем:

• можно фильтровать результат на месте (как в обычном фильтре), а можно сразу выводить результаты в другое место, не фильтруя исходный диапазон;

• условия задаются не в самом фильтре, а в отдельных ячейках;

• можно фильтровать данные по нескольким независимым наборам условий (когда одному значению в одном столбце соответствует другое значение в другом).

Поясним последний пункт на примере. Допустим, у нас есть такие данные.