Книга: Google Таблицы
Назад: Условное форматирование
Дальше: Текстовые функции

ФУНКЦИИ СУММИРОВАНИЯ  ПОДСЧЕТА

SUM (СУММ)

ПРИМЕРЫ ПРИМЕНЕНИЯ ФОРМУЛЫ SUM (СУММ)

У нас есть таблица с численностью населения в пяти городах РФ. Посчитаем с помощью SUM количество жителей этих городов.

Начнем с классического способа — выберем в формуле только те ячейки, которые нам нужно просуммировать:

Если захватить ячейки, содержащие текст, формула все равно будет работать:

А еще можно выбрать сразу весь столбец, если мы уверены, что в нем только нужные нам данные и нет ничего лишнего:

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

Также мы можем просуммировать отдельные ячейки (чтобы перейти к следующему элементу, выбирая ячейки или диапазоны мышкой, зажмите Ctrl):

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

КЕЙС «СОВМЕЩАЕМ В ОДНОЙ ЯЧЕЙКЕ ТЕКСТ И ФОРМУЛУ SUM (СУММ)»

В ячейке С1 находится заголовок таблицы, попробуем совместить его с формулой SUM:

Для этого обрамим текст кавычками и добавим к нему формулу с помощью символа & (амперсанд):

Чтобы у числа появилась разрядность, добавим формулу ТЕКСТ с форматом "#,##0" (более подробно о форматах по ссылке: ):

А вот так это будет выглядеть, если использовать вместо амперсанда (&) формулу СЦЕПИТЬ (CONCATENATE):

Как вы могли понять, объединить можно все что угодно, например значения нескольких ячеек и текст:

КЕЙС С ФУНКЦИЕЙ SUM. ФОРМУЛА ОБРАЩАЕТСЯ ТОЛЬКО К ЗАПОЛНЕННОМУ УЧАСТКУ ДИАПАЗОНА

Вообще формула SUM очень простая — она суммирует все, что находится внутри ее скобок и что можно просуммировать: sum(a1:a). Но в нашей книге мы стараемся показывать интересные примеры, так что давайте я расскажу, как сделать так, чтобы формула обращалась только к заполненной части таблицы.

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

У нас есть такая табличка:

Сначала считаем количество занятых строк в столбце C с помощью СOUNTA (СЧЁТЗ):

Получается 16, и 16-я же последняя строка в нашем примере. Как вы уже, наверное, поняли, способ будет работать только в том случае, если данные занесены без пропусков строк.

Далее с помощью амперсанда (&) формируем наш диапазон:

Вводим диапазон открытого типа с началом там, откуда начинается суммирование, и обрамляем его в кавычки: "C2:C". Далее, используя &, добавляем нашу формулу, которая вернет значения — количество строк, которые будут заполнены.

Результат формулы:

Добавляем формулу ДВССЫЛ (INDIRECT), чтобы другие формулы идентифицировали нашу конструкцию как диапазон ячеек:

Обратите внимание, что выбранный диапазон сразу отображается в ячейках таблицы. Но нам нужно получить сумму его ячеек, а не выводить сам диапазон. Поэтому мы добавляем его в функцию СУММ (SUM):

Получилось! Теперь, если пользователи будут обновлять табличку и заполнять новые строки в столбце С без пропусков, наша формула самостоятельно увеличит свои границы и правильно посчитает общую сумму.

SUMIF (СУММЕСЛИ)

КЕЙСЫ SUMIF С ЧИСЛОВЫМИ УСЛОВИЯМИ

С помощью функции SUMIF (СУММЕСЛИ) можно подсчитать сумму ячеек, которые соответствуют нашему условию.

Синтаксис функции:

=SUMIF­(диапазон; условие; сумма диапазона)

=СУММЕСЛИ­(диапазон; условие; сумма диапазона)

Например, мы можем получить общее количество жителей городов с численностью населения более 2 000 000 человек:

Если диапазоны условия и суммирования совпадают, то последний можно не указывать, ячейки будут просуммированы верно:

Иногда может быть полезным не указывать условие в формуле, а сослаться на ячейку с ним, в нашем случае это ячейка B2:

КЕЙСЫ SUMIF С ТЕКСТОВЫМИ УСЛОВИЯМИ

Условием SUMIF могут быть и текстовые значения. В примере ниже подсчитаем сумму операций с типом «зарплата»:

В текстовых условиях можно использовать символьные шаблоны. Давайте подсчитаем население городов, название которых начинается на "Во" (символ * обозначает любую последовательность символов):

Чтобы заменить только один символ (допустим, мы не знаем, как правильно написать Барнаул), нужно использовать ?:

А под условие вида *а* попадут все города с одной или несколькими буквами а в названии.

Если добавить <> (не равно) в начало условия, то формула посчитает города без буквы а в названии:

(<> можно использовать и в формулах SUMIF с числовыми условиями, например <>1 выберет все ячейки, не равные 1.)

SUMIFS (СУММЕСЛИМН)

КЕЙСЫ SUMIFS

С помощью формулы SUMIFS (СУММЕСЛИМН) можно подсчитать сумму ячеек, отвечающих нескольким условиям.

Посмотрим на таблицу ниже. Для начала найдем сумму всех зарплат сотрудника Васи Иванова:

Формула будет следующей (слева направо):

1-й элемент — диапазон суммирования;

2-й и 3-й — диапазон первого условия и само условие (в нашем случае столбец B «Сотрудник» и «Вася Иванов»);

3-й и 4-й — диапазон второго условия и само условие (обратите внимание, что формула нечувствительна к регистру: я написал Зп, а в нашей таблице встречаются только зп, однако формула все равно работает):

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

1) больше или равно для первого числа месяца;

2) меньше или равно для последнего числа месяца.

Обратите внимание, что можно использовать ссылку на ячейку с датой, а можно прописать эту дату в формуле (в примере оба варианта):

Можно использовать формулу EOMONTH (КОНМЕСЯЦА) с аргументом 0, сославшись на ячейку с первым числом месяца, — она вернет последнее число этого месяца (эта формула подойдет для тех случаев, когда мы не знаем, сколько дней в месяце, или лень прописывать что-то руками):

Как и в формуле SUMIF, мы можем использовать символьные шаблоны. Давайте подсчитаем, сколько заработали все Васи за 2016 год (я знаю, что в таблице нет дат ранее 2016 года, поэтому буду использовать только одно условие на столбец с датой < 01.01.17):

Кстати, иногда нам нужно, чтобы формула понимала символ * (или ?) как обычный текст. Чтобы этого добиться, нужно поставить перед ним тильду ~:

COUNT (СЧЁТ) И COUNTA (СЧЁТЗ)

По структуре формулы аналогичны SUM.

Мы можем выбрать диапазон:

Либо конкретные ячейки и (или) диапазоны:

COUNTA (СЧЁТЗ) подсчитает не числовые значения, а количество любых непустых ячеек:

КЕЙС «ОБЪЕДИНЯЕМ ТЕКСТ, COUNTA И SUM»

Давайте попробуем написать в одной ячейке текст «Численность населения в (x) самых крупных городах РФ (y) человек». Вместо (x) и (y) подставим формулы.

Так посчитаем (x):

Так (y):

И объединим полученные данные с нашим текстом с помощью нескольких амперсандов (&). Текст всегда нужно писать в кавычках, иначе формула вернет ошибку:

COUNTIF (СЧЁТЕСЛИ)

С помощью COUNTIF (и COUNTIFS) мы можем подсчитать количество ячеек в выбранном диапазоне, которые соответствуют одному (COUNTIF) или нескольким нашим условиям (COUNTIFS). Отличие от SUMIF (SUMIFS) только одно — не нужно выбирать диапазон суммирования.

Начнем с простого: подсчитаем, сколько раз в диапазоне A2:C2 встречается Москва:

Диапазоном условия COUNTIF может быть и двумерный массив, состоящий из нескольких столбцов и строк:

С помощью условия ">0" (больше нуля) мы определим количество положительных числовых значений в диапазоне выборки:

С условием "<>"&"" (не равно пусто) наша формула выдаст количество непустых ячеек:

КЕЙС COUNTIF «СЧИТАЕМ КОЛИЧЕСТВО АДРЕСОВ В КАЖДОМ ГОРОДЕ»

У нас есть таблица со списком адресов. Задача: увидеть, сколько филиалов находится в конкретном городе.

Создадим новую колонку и введем в ячейку F2 формулу:

Обязательно зафиксируем диапазон C2:C12 (сделаем ссылки абсолютными).

Итак, мы закончили с формулой в ячейке F2, и она показывает, что Липецк встречается два раза среди всех адресов в С2:C6. Скопируем формулу на ячейки ниже.

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

КЕЙС COUNTIF С СИМВОЛЬНЫМИ ШАБЛОНАМИ

Мы всегда можем воспользоваться символьными шаблонами * и ?.

Напомню: * — любая последовательность символов, ? — один любой символ.

Формула ниже позволит подсчитать, сколько адресов в диапазоне D2:D12 начинается на ул. и содержит любые символы после:

В условиях с символьными шаблонами допустимы ссылки на ячейки:

КАК ПОСЧИТАТЬ КОЛИЧЕСТВО ТЕКСТОВЫХ ЯЧЕЕК, НАЧИНАЮЩИХСЯ НА «ЗВЕЗДОЧКУ», БЕЗ ДОПОЛНИТЕЛЬНЫХ ФОРМУЛ?

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

Для решения этой задачи нужна формула СЧЁТЕСЛИ:

Условие выглядит очень необычно, но мы можем все объяснить:

КОЛИЧЕСТВО ЗНАЧЕНИЙ ВЫШЕ СРЕДНЕГО СО СЧЁТЕСЛИ

Следующая формула вернет количество больших, чем среднее, значений в диапазоне:

=COUNTIF­(диапазон с данными;">"&­AVERAGE­(диапазон с данными))

=СЧЁТЕСЛИ­(диапазон с данными;">"&СРЗНАЧ­(диапазон с данными))

Например:

=COUNTIF­(B2:B20;">"&AVERAGE­(B2:B20))

=СЧЁТЕСЛИ­(B2:B20;">"&СРЗНАЧ­(B2:B20))

COUNTIFS (СЧЁТЕСЛИМН)

Используя COUNTIFS (СЧЁТЕСЛИМН), мы можем посчитать количество ячеек, которые удовлетворяют нескольким нашим условиям.

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

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

Перейдем к примерам с правильным построением формулы. У нас есть табличка со списком 25 самых крупных российских городов:

Посчитаем, у скольких городов население больше 500 000, но меньше 1 000 000 человек:

Мы применяем к одному диапазону два условия; формула отберет только те случаи, когда будут истинны оба.

Формула будет выглядеть так, если мы сошлемся на ячейки с условиями:

Или даже так, если мы хотим собрать каждое условие из двух ячеек:

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

Но даже если у вас всего одно условие для расчета (например, нужно посчитать, сколько раз встречается Москва в выбранном диапазоне), вы все равно можете использовать формулу СЧЁТЕСЛИМН.

SUMPRODUCT (СУММПРОИЗВ)

С помощью формулы SUMPRODUCT (СУММПРОИЗВ) можно посчитать сумму произведения двух массивов:

КЕЙС «СЧИТАЕМ СРЕДНЕЕ ВЗВЕШЕННОЕ С ИСПОЛЬЗОВАНИЕМ SUMPRODUCT»

Как пригодится формула SUMPRODUCT (СУММПРОИЗВ)? С ее помощью можно найти среднее взвешенное. Например, подсчитать среднюю продажу продавца Васи по всем пяти строкам в зависимости от количества продаж. Порядок действий:

1) сначала считаем сумму произведения столбцов В и С с помощью SUMPRODUCT (СУММПРОИЗВ) = 35 835;

2) затем результат поделим на количество продаж, то есть на сумму столбца В = 26;

3) итого = 228,65:

Эта конструкция поможет посчитать среднее, которое зависит от объема.

КЕЙС «SUMPRODUCT С ВЫБОРОМ МАССИВОВ ПРИ ПОМОЩИ УСЛОВИЯ»

Что делать, если помимо продавца Васи в нашей таблице есть Петя, а мы хотим работать только с Васиными продажами и посчитать их среднее взвешенное?

Можно сделать автофильтр и удалить строки с продавцом Петей, можно отсортировать данные и опять-таки оставить только то, с чем мы будем работать.

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

Эта формула — FILTER (у формулы нет перевода):

1) первый элемент — тот столбец (или несколько столбцов), который мы хотим отобразить;

2) второй элемент — столбец условия и само условие (их может быть несколько):

Как мы видим, формула показывает нам только те продажи (ячейки столбца В), которым соответствует продавец Вася (ячейки столбца А).

Теперь поместим эту конструкцию в СУММПРОИЗВ и добавим вторым массивом еще одну формулу FILTER, которая будет отбирать средний чек из столбца С:

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

Опять используем FILTER по столбцу B и помещаем его в формулу SUM (СУММ):

Готово.

AVERAGE (СРЗНАЧ), AVERAGEIF (СРЗНАЧЕСЛИ) И AVERAGEIFS (СРЗНАЧЕСЛИМН)

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

СРЗНАЧ (AVERAGE) позволяет рассчитать среднее арифметическое всех чисел в выбранном массиве, а если в нем будут встречаться ячейки с текстом (например, B1), то формула их проигнорирует:

По способам выбора данных в формулу она аналогична SUM (СУММ) — можно выбрать один диапазон, можно несколько, можно отдельные ячейки и диапазоны. Более подробно читайте в главе .

СРЗНАЧА (AVERAGEA) работает с ячейками, содержащими текст, определяя их как 0 и добавляя этот 0 в расчет среднего арифметического:

СРЗНАЧЕСЛИ (COUNTIF) позволяет рассчитать среднее ячеек, отобранных по одному критерию. Структура формулы аналогична SUMIF (СУММЕСЛИ):

Можно использовать символьные шаблоны, например, чтобы отобрать Петь с любыми фамилиями, сколько бы их ни было:

СРЗНАЧЕСЛИМН (COUNTIFS) позволяет посчитать среднее ячеек, отобранных по одному или нескольким условиям. Конструкция как у SUMIFS:

Назад: Условное форматирование
Дальше: Текстовые функции