Как узнать сумму из периода с условиями в Google Таблице

При взаимодействии с Гугл Таблицей пользователь может создавать записи каждый день, указывая дату и какое-нибудь число рядом с ней, к примеру сумму заработанных денег. Здесь может быть еще одно условие – вид деятельности, в котором эта сумма была заработана. Пройдут недели или даже месяцы, таблица наполнится и понадобится узнать, сколько денег было заработано за определенный период в определенной сфере. Вычислить необходимую сумму можно двумя разными методами, которые я и хочу наглядно продемонстрировать.

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

Исходные данные и подготовительные работы

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

1. В одном столбце у нас есть последовательные дни, которые и будут являться периодом в будущем. 

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

3. Последний – полученная прибыль, сумму которой и будем считать в зависимости от выставленного периода и дополнительных условий.

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

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

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

1. Для начала разберемся с начальной датой периода. Выделим эту самую ячейку и развернем меню «Данные».

2. Из него выберите пункт «Настроить проверку данных».

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

4. Теперь рядом с числом появится кнопка со стрелкой вниз.

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

6. Абсолютно то же самое проверните и с ячейкой конечной даты, чтобы точно так же переключаться при необходимости.

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

8. Снова вызовите функцию «Настроить проверку данных» и в новом окне задайте подходящий диапазон точно так же, как делали это ранее.

9. Главное - записывайте одинаковые сферы деятельности одинаковыми символами, чтобы список формировался правильно, без лишних дублей.

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

Способ 1: Использование функции AND и СУММЕСЛИ

Функция AND в Google Таблицах позволяет сравнить неравенство и вывести в ячейку значение ИСТИНА или ЛОЖЬ, отталкиваясь от того, подходит ли условие неравенства. Благодаря этой функции мы создадим вспомогательный столбец, при помощи которого будем вычислять все дни, входящие в необходимый нам период. Давайте наглядно разберем все составляющие первой формулы, которая будет нужна для дальнейших расчетов.

1. Выделите первую ячейку в том столбце, который можете сделать вспомогательным. Объявите в нем функцию =AND.

2. Далее разверните скобки и укажите первое условие неравенства (A2>=$D$1;). Обратите внимание на то, что ячейка D1 является статичной благодаря добавлению константы, то есть не будет растягиваться при дальнейшем формировании столбца. 

3. Второе условие A2<=$E$1, где E1 – ячейка с последней датой периода.

4. Нажмите Enter для подтверждения формулы и растяните ее ровно на столько клеток вниз, сколько у вас ячеек в столбце с датами. В итоге она будет иметь примерно такой вид: =AND(A2>=$D$1;A2<=$E$1;B2=$F$1).

При помощи этой функции мы вычисляем даты, которые актуальны для выбранного пользователем периода. Значения ИСТИНА или ЛОЖЬ будут меняться в зависимости от того, какие даты вы будете самостоятельно указывать в качестве начала и конца периода. Соответственно, если значение ИСТИНА, значит, дата входит в период и значение прибыли будет включено в формулу. Если ЛОЖЬ, ячейка пропускается и осуществляется переход к следующей.

С первой частью формулы закончили, остается только создать основные расчеты, в которые и будут включены значения ИСТИНЫ и ЛЖИ из вспомогательного столбца. В этом нам поможет СУММЕСЛИ. Эта функция считает значение только в том случае, если ее устраивает заданное условие. Запись этой формулы в нашем случае выглядит следующим образом:

1. Объявите функцию =СУММЕСЛИ в том месте, где хотите разместить сам результат суммирования прибыли или других данных, вычисляемых в периоде.

2. В качестве первого столбца для проверки укажите все ячейки с ИСТИНА и ЛОЖЬ, которые мы формировали ранее.

3. Поставьте точку с запятой, добавьте ИСТИНА, поставьте еще раз точку с запятой и укажите диапазон данных со значениями, сумму которых нужно рассчитать. Нажмите Enter и посмотрите на результат.

4. Я вручную выделяю все ячейки, которые входят в период по дням и смотрю, что формула работает корректно.

Давайте более наглядно разберем ее. Формула имеет вид =СУММЕСЛИ(B17:B30;ИСТИНА;C2:C15). В первую очередь указывается проверяемый диапазон, далее – условие, которое нас устраивает, то есть это должна быть ИСТИНА. Последний аргумент – данные, которые будут суммироваться, если равно ИСТИНА, то есть столбец с нашей прибылью.

Способ 2: Использование функции SUMPRODUCT

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

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

1. Сначала объявите =SUMPRODUCT или =СУММПРОИЗВ на русском языке, в любой удобной клетке, куда хотите вывести результат.

2. Откройте кавычки и укажите, что диапазон числа из диапазона дат должен быть больше или равняться первому числу в периоде. Это делается точно так же, как и при создании вспомогательного столбца в Способе 1.

3. Закройте скобки после первого условия и поставьте знак *, то есть в нашем случае мы объединим предыдущее условие со следующим. Как раз откройте скобки и напишите второе условие, указав, что значения в диапазоне дат должны быть меньше или равняться последнему числу в периоде.

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

5. Нажмите Enter и проверьте, сработала ли функция. Если нет, перечитайте мою инструкцию еще раз и проанализируйте ее составляющие со своими.

Полностью строка с этой формулой выглядит как =СУММПРОИЗВ((A2:A15>=$D$1)*(A2:A15<=$E$1)*(C2:C15)), поэтому можете просто скопировать ее и вставить, если номера ячеек в столбцах совпадают. В этой строке мы объявили два логических условия, после чего сказали формуле, какие данные стоит суммировать, если они устраивают заданные параметры. 

Добавление условий к периоду

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

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

1. Активируйте первую ячейку из вспомогательного столбца с функцией AND и добавьте к ней еще одно условие, которое выглядит как B2=$F$1. B2 – первая ячейка с видом деятельности, а F1 – то самое условие, которое мы выбираем при расчетах. Его нужно закрепить, чтобы далее ячейка не съехала.

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

3. В функции =СУММЕСЛИ изменения произойдут автоматически, поскольку она берет данные из ИСТИНА и ЛОЖЬ. Теперь вы видите, что формула считает по-новому, с учетом введенного условия. Таких условий может быть практически неограниченное количество.

Если вам нужно, оставляю модернизированную функцию с добавлением одного условия.

=AND(A2>=$D$1;A2<=$E$1;B2=$F$1)

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

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

Как и в случае с предыдущей формулой, предоставляю модернизированный вариант, если вдруг решите его скопировать для дальнейшего использования.

=СУММПРОИЗВ((A2:A15>=$D$1)*(A2:A15<=$E$1)*(B2:B15=$F$1)*(C2:C15))

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