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

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

На этот урок вам понадобится 15—25 минут.

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

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

Как посчитать среднее

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

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

За среднее значение отвечает функция AVERAGE() — с английского так и переводится, «среднее». Она складывает все значения из ячеек и делит на количество этих ячеек. Если мы хотим получить среднее значение за период с января по июнь, формула будет выглядеть как =AVERAGE(B2:G2):

Если в дальнейшем мы продолжим вносить ежемесячные траты, формулу можно будет просто «протянуть» — скопировать на следующие ячейки вправо.

Помните, что при копировании формулы адреса ячеек смещаются: если в ячейке G4 мы считали период с января по июнь, то при копировании формулы в ячейку H4 получится период с февраля по июль. Так мы всегда будем получать среднее за последние полгода:

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

Чтобы не писать в каждой новой ячейке новую формулу, можно использовать символ $ — зафиксировать с его помощью начало нашего диапазона: =AVERAGE($B2:G2). Перед символом B у нас теперь стоит $ — это значит, что при копировании формулы начало диапазона всегда будет в столбце B, то есть считать будем с января. А конец диапазона будет сдвигаться. Если в ячейке G4 мы считаем среднее с января по июнь, то в ячейке H4 будет среднее с января по июль:

Как поставить таблице условия

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

Чтобы эта штука сработала, нам нужно научиться ставить таблице условия: при определенном результате таблица должна делать что-то одно, при другом результате — что-то другое. Для этого существует целое семейство функций IF().

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

В нашем случае формула будет выглядеть так:

=IF(H2>H4;"Это выше среднего!";"Все в порядке").

На человеческом языке формула и условие звучат так: если значение в ячейке H2, то есть наш расход в этом месяце, превышает значение H4, наш средний расход, нужно вывести предупреждение «Это выше среднего!». Если не превышает, нужно вывести сообщение «Все в порядке».

H2>H4 — это условие, то есть утверждение, которое мы будем проверять. В нашем случае мы проверяем, превышает ли расход в этом месяце среднее значение, которое считается функцией AVERAGE().

"Это выше среднего!" — это значение при соблюдении условия. То, что должно случиться, если наше утверждение верно. Если значение в ячейке H2 действительно больше, чем в H4, то таблица выведет текст «Это выше среднего!». Чтобы таблица восприняла текст именно как текст, его нужно указывать в кавычках — “”.

"Все в порядке" — это значение при несоблюдении условия. Таблица выведет его, если наше условие не выполняется — то есть если H4 (среднее) окажется не меньше H2 (текущего), а больше или равно ему.

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

Результаты для условий можно оставлять и пустыми. Например, вам не нужно сообщение «Все в порядке» — вы хотите видеть только предупреждение, если превышаете свой средний лимит.

Условие: H2>H4. Проверяем, превышает ли наш текущий расход среднемесячный лимит.

Если выполняется: вывести предупреждение «Это выше среднего!».

Если не выполняется: не выводить ничего. Эту часть можно оставить пустой.

Собираем все это в формулу, которую поймет таблица:

=IF(H2>H4;"Это выше среднего!";)

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

Задание

Сравнивать можно не только одну ячейку с другой, но и ячейку с числом. Предположим, вы хотите, чтобы предупреждение выводилось, когда вы превышаете не средний расход, а отметку в 70 000 Р.

Попробуйте переделать формулу на листе «Траты», чтобы решить эту задачу.

Если что-то пошло не так, можно свериться с решебником.

Как сделать, чтобы по условию применялись другие функции

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

Например, вы решили ограничить свой бюджет на продукты — тратить в месяц не больше 15 000 Р. Сделаем табличку, которая покажет, сколько еще можно потратить:

Разберем таблицу: в столбцы A и B мы записываем, сколько потратили в этом месяце, в ячейку F1 вводим лимит — сумму, которую можем потратить за месяц. В ячейке F4 суммируются все расходы за месяц — это просто формула, которая складывает все значения из столбца B: =SUM(B:B).

Что должна сделать таблица? Если фактические расходы меньше лимита, в ячейке F7 нужно вывести остаток лимита. Если он превышен, вместо расчета выводится сообщение. А если от лимита осталось меньше 5000 Р, в ячейке F8 выведем предупреждение об этом.

Сначала собираем формулу в ячейке F7: =IF(F4<F1;F1-F4;"Превышен лимит!"). Разберемся, что есть что.

Условие: фактические траты меньше лимита, F4<F1.

Если выполняется: показываем остаток — то есть вычитаем из лимита фактические траты, F1-F4.

Если не выполняется: выводим сообщение, что лимит превышен.

А теперь — формулу в ячейке F8: =IF(F7<5000;"Осталось меньше 5 тысяч!";).

Условие: доступный остаток меньше 5000 Р, F7<5000.

Если выполняется: выводим предупреждение, что осталось меньше 5 тысяч.

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

Задание

Составьте аналогичную таблицу для расходов на развлечения: допустим, лимит на месяц — 5000 Р, а предупреждение вы хотите видеть, если остаток меньше 1000 Р.

Если что-то пошло не так, сверьтесь с решебником.

Запомним

  1. Функция IF() отвечает за постановку условий таблице. Такая функция включает три части: условие для проверки, команду при соблюдении условия — можно, например, вывести сообщение — и команду при несоблюдении условия.
  2. Команды при соблюдении условия может и не быть. Тогда таблица подаст сигнал, только когда заданное вами условие не выполняется. Наоборот тоже можно.
  3. Команда — это не обязательно вывод текста. Можно попросить таблицу что-то посчитать или запустить другую формулу.

Как суммировать ячейки по условию

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

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

Выделяем уникальные значения. Нам нужно составить список участников веселья. Конечно, когда вас всего четверо, можно сделать это вручную, но когда нужно найти сразу много уникальных значений, лучше использовать функцию UNIQUE() — переводится как «уникальный». Если применить ее к диапазону с именами, она уберет из него все повторения и выделит уникальные значения:

Уникальные значения будут выводиться в зависимости от диапазона: если ищем их в вертикальном диапазоне, результат выведется в столбик, а если в горизонтальном — в строчку. В нашем случае — в столбик.

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

Ставим условие и суммируем по нему. Теперь считаем, кто сколько потратил. Вот наши вводные: напротив каждой траты написано, кто платил. И нам нужно просуммировать только те покупки, что оплачивал определенный человек. То есть в ячейке G2 мы хотим получить полную сумму, которую заплатил Коля, в G3 — Маша. И так далее.

Поможет функция SUMIF() — сочетание уже знакомых нам функций SUM()и IF(). Она проверяет условие, а если оно выполняется — сразу суммирует значения в ячейках.

Разберем функцию на примере Коли и его трат.

Что должна сделать таблица? Найти имя «Коля» в диапазоне C:C и запомнить все номера строк, где это имя есть. Например, в строках 2 и 6. Потом сопоставить эти номера строк с диапазоном, по которому нужно суммировать, — то есть B:B. А потом взять значения из ячеек B2 и B6 соответственно — и сложить.

Собираем формулу напротив имени Коли в столбце G: =SUMIF(C:C;F2;B:B). Вот что она в себя включает.

Диапазон, в котором мы будем проверять условие. То есть набор ячеек, где мы будем искать определенное имя. В нашем случае это весь столбец С, указываем C:C.

Условие, которое будем проверять. Имя в нашем диапазоне должно совпадать с тем, что выписано в правой части листа. Имя Коли выписано в ячейке F2 — указываем ее.

Диапазон, который будем суммировать. Нужно указать, где у нас записаны траты. Это столбец B, пишем B:B.

Эти части разделяются точкой с запятой.

В таблице результат будет выглядеть так:

Теперь нам нужно посчитать, сколько потратили остальные участники поездки. Для этого достаточно «протянуть» формулу вниз — на соответствующие ячейки. При смещении автоматически сменится адрес ячейки с именем — с F2 на F3 и так далее:

Как суммировать ячейки по нескольким условиям сразу

Условий может быть и несколько. Продолжим наш пример с отпуском. Предположим, вы хотите подбить детальную статистику: кто, сколько и на что именно потратил. Например, сколько Коля потратил на жилье.

В этом случае должны суммироваться ячейки, которые соответствуют сразу двум условиям: имя совпадает с «Коля», а категория совпадает с «Жилье».

Продолжим работать на том же листе рабочей тетради. Первым делом составим вот такую матрицу:

Сначала соберем все уникальные названия трат с помощью функции UNIQUE(). Составим формулу =UNIQUE(A2:A28)и вставим ее в ячейку F10:

Теперь нам нужно сделать горизонтальную ось — пропишем на ней имена вручную:

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

Поможет функция =SUMIFS() — она суммирует сразу по нескольким условиям. Окончание S, как это бывает в английском, образует множественное число. Разберем на примере Коли и трат на жилье.

Итак, нам нужна формула, которая просуммирует значения из столбца B, если в столбце A значение совпадает с «Жилье», а в столбце C — с «Коля». Выполняться должны оба условия одновременно. Обратите внимание: для этой функции сначала нужно указать диапазон, в котором таблица будет суммировать, и только потом — условия.

Диапазон, который суммируем, — в нашем случае это столбец с тратами, B:B.

Диапазон, в котором проверяем первое условие. По порядку начнем со столбца A — в нем мы будем проверять, совпадает ли название траты с «Жилье». Пишем A:A.

Условие для первого диапазона — то есть значение, которое мы будем искать в столбце A. Мы будем искать трату «Жилье», F10.

Диапазон, в котором проверяем второе условие. Теперь нужен столбец C — в нем мы будем проверять, совпадает ли имя с «Коля». Пишем C:C.

Условие для второго диапазона — имя, которое ищем в столбце C. В нашей матрице имя «Коля» записано в ячейке G9.

Собираем все это в формулу через точку с запятой: =SUMIFS(B:B;A:A;F10;C:C;G9). Формулу разместим в ячейке G10 — на пересечении категорий «Коля» и «Жилье».

Вот как это должно выглядеть в нашей таблице:

Запомним

  1. С помощью функции SUMIF() можно просуммировать значения по заданному условию. Для этого нужно последовательно задать: диапазон, в котором проверяется условие; условие, которое будем проверять; диапазон, который будем суммировать.
  2. Функция SUMIFS() суммирует сразу по нескольким условиям, поэтому частей в формуле будет больше: добавится еще один диапазон и условие для него.
  3. Кроме SUMIF() и SUMIFS() есть еще несколько полезных аналогичных функций, их мы разберем в следующем уроке, когда будем учиться анализировать условия, связанные с датами.

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

  1. AVERAGE() — высчитывает среднее арифметическое.
  2. UNIQUE() — убирает повторы и выделяет уникальные значения.
  3. IF() — ставит условия таблице.
  4. SUMIF() — суммирует значения по заданному условию.
  5. SUMIFS() — суммирует значения сразу по нескольким условиям.