Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
=СЧЁТЕСЛИМН(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.
Отличия расширенного фильтра от обычного фильтра на рабочем листе в следующем:
• можно фильтровать результат на месте (как в обычном фильтре), а можно сразу выводить результаты в другое место, не фильтруя исходный диапазон;
• условия задаются не в самом фильтре, а в отдельных ячейках;
• можно фильтровать данные по нескольким независимым наборам условий (когда одному значению в одном столбце соответствует другое значение в другом).
Поясним последний пункт на примере. Допустим, у нас есть такие данные.
