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

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

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

Но для начала разберемся с еще одной базовой функцией — подсчетом среднего арифметического.

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

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

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

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

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


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

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

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

Ставим условия

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

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

Функция IF() устроена чуть сложнее предыдущих: она состоит из трех частей, которые указываются внутри скобок через точку с запятой: IF(M4<M2;”Это выше среднего за полгода!”;”Все в порядке”). Разберемся, что есть что.

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

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

“Всё в порядке” — это значение при несоблюдении условия. Таблица выведет его, если наше условие не выполняется — то есть если M4 окажется не меньше, чем М2, а больше или равно.

На человеческий язык формулу можно перевести так: если значение в ячейке М4 меньше значения в ячейке М2, нужно вывести текст «Это выше среднего за полгода!». А если нет — вывести текст «Все в порядке». Вот как формула выглядит в самой таблице:

Значения для условий можно оставлять и пустыми. Предположим, вы хотите видеть сообщение, только если превышаете свой средний лимит. А если не превышаете, то и сообщение вам не нужно. Можно просто оставить второй вариант результата пустым: =IF(M4<M2;"Это выше среднего за полгода!";)

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

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

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

Разберем таблицу: в столбцы A и B мы записываем, сколько пробежали в этом месяце, в ячейку E1 вводим цель — количество километров за месяц.

В ячейке E3 суммируются все ваши пробежки за месяц. Это просто формула =SUM(B:B).

Проверка условия — в ячейке E6. Там будет такая формула: =IF(E3<E1;E1-E3;"нисколько! 🏃🏃‍♀️"). Разберем её:

E3<E1 — утверждение, которое мы проверяем: мы пробежали меньше, чем запланировано.

E1-E3 — значение, если утверждение верное. Если мы пробежали меньше, чем нужно, вычитаем из цели наш пробег. Получаем дистанцию, которую еще осталось пробежать.

"нисколько! 🏃🏃‍♀️" — значение, если утверждение неверное. То есть если мы пробежали больше, чем планировали, или столько же — выводим такое сообщение. Как видите, внутри текста могут быть и эмодзи.

Другие функции с условиями

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

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

Вместо работы руками можно попросить таблицу все посчитать.

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

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

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

Шаг 2. Считаем, кто сколько потратил. Вот наши вводные: напротив каждой траты написано, кто платил. И нам нужно просуммировать только те покупки, за которые платил определенный человек. Поможет функция SUMIF() — сочетание уже знакомых нам функций SUM() и IF(). Она сразу и проверяет условие, и суммирует.

Начнем с Коли. Формулу будем вводить напротив его имени в столбце G. Она будет выглядеть так: =SUMIF(C:C;F2;B:B). Разберемся, что есть что:

C:C — диапазон, в котором мы будем искать интересующее нас значение для условия. В нашем случае условие — это имя. Обозначение C:C без чисел означает, что мы выделим весь столбец целиком.

F2 — имя, которое мы ищем. В нашем случае — «Коля».

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

Переведем на человеческий: если в столбце C («Кто платил») имя совпадает с F2 («Коля»), то нужно взять число в столбце B («Сколько») из той строчки, где имя совпало, а все совпадения суммировать.

В таблице все это будет выглядеть так:

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

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


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

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

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

Посчитаем, сколько пробежали за октябрь:

Первое, что нам нужно сделать, — создать вспомогательный столбец, в котором мы будем по дате определять номер месяца. Это делает функция MONTH(). Например, формула =MONTH(A2) выдаст значение 9: в ячейке A2 у нас дата в сентябре. Разместим эти формулы в столбце D и протянем на весь лист.

Формула для бега будет выглядеть так: =SUMIFS(C:C;D:D;10;B:B;F2). Разберемся, что есть что.

C:C — диапазон, который будем суммировать при выполнении условий.

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

10 — значение, которое мы будем искать в диапазоне D:D. Мы суммируем данные за октябрь, так что значение в нашем случае — 10, десятый месяц.

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

F2 — значение, которое мы будем искать в диапазоне B:B. В нашем случае в ячейке F2 указан текст «Бег» — его и будет искать функция в диапазоне B:B.

Переводим на человеческий: суммируй все значения в столбце C, которые соответствуют условиям. Условия такие: в столбце D — десятый месяц, в столбце B — тип активности «Бег».

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


Кроме SUMIF() и SUMIFS() есть еще несколько полезных функций — они устроены так же, как описанные выше:

  1. COUNTIF() и COUNTIFS() считают количество ячеек, соответствующих одному или нескольким условиям соответственно. В русскоязычной версии — СЧЕТЕСЛИ() и СЧЕТЕСЛИМН()
  2. AVERAGEIF() и AVERAGEIFS() считают среднее арифметическое среди ячеек, соответствующих одному или нескольким условиям соответственно. В русскоязычной версии — СРЗНАЧЕСЛИ() и СРЗНАЧЕСЛИМН().

Вот что вы узнали

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