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

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

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

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

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

  1. В одном столбце у нас есть последовательные дни, которые и будут являться периодом в будущем. Первый столбец с данными для определения суммы из периода с условиями в Google Таблицах
  2. Следующий столбец показывает сферу деятельности, которой пользователь занимался в каждый из этих дней.Второй столбец с данными для определения суммы из периода с условиями в Google Таблицах
  3. Последний – полученная прибыль, сумму которой и будем считать в зависимости от выставленного периода и дополнительных условий.Третий столбец с данными для определения суммы из периода с условиями в Google Таблицах
  4. В строках выше я указываю начальную дату периода, конечное число и сферу деятельности, заработок из которой нужно узнать.Динамически изменяемые значения для определения суммы из периода с условиями в Google Таблицах

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

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

  1. Для начала разберемся с начальной датой периода. Выделим эту самую ячейку и развернем меню «Данные».Открытие меню Данные для определения суммы из периода с условиями в Google Таблицах
  2. Из него выберите пункт «Настроить проверку данных».Переход к Проверке данных для определения суммы из периода с условиями в Google Таблицах
  3. Понадобится ввести только одно изменение – указать столбец, из которого эти данные будут браться. Соответственно, выделите диапазон, где записаны все ваши числа, после чего сохраните изменения.Ввод диапазона в Проверке данных для определения суммы из периода с условиями в Google Таблицах
  4. Теперь рядом с числом появится кнопка со стрелкой вниз.Открытие списка Проверки данных для определения суммы из периода с условиями в Google Таблицах
  5. Нажмите ее для отображения списка всех дат. Из него можете выбрать любую, которая и будет обозначать начало вашего периода.Отображение первого списка Проверки данных для определения суммы из периода с условиями в Google Таблицах
  6. Абсолютно то же самое проверните и с ячейкой конечной даты, чтобы точно так же переключаться при необходимости.Отображение второго списка Проверки данных для определения суммы из периода с условиями в Google Таблицах
  7. С дополнительными условиями все примерно так же. Выделите пустую ячейку, где хотите сформировать список, запомните номер столбца и переходите в меню «Данные».Определение условий для Проверки данных для определения суммы из периода с условиями в Google Таблицах
  8. Снова вызовите функцию «Настроить проверку данных» и в новом окне задайте подходящий диапазон точно так же, как делали это ранее.Добавление условий для Проверки данных для определения суммы из периода с условиями в Google Таблицах
  9. Главное — записывайте одинаковые сферы деятельности одинаковыми символами, чтобы список формировался правильно, без лишних дублей.Добавленные условия Проверки данных для определения суммы из периода с условиями в Google Таблицах

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

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

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

  1. Выделите первую ячейку в том столбце, который можете сделать вспомогательным. Объявите в нем функцию =AND.Начало записи вспомогательной функции AND
  2. Далее разверните скобки и укажите первое условие неравенства (A2>=$D$1;). Обратите внимание на то, что ячейка D1 является статичной благодаря добавлению константы, то есть не будет растягиваться при дальнейшем формировании столбца. Первое условие функции AND
  3. Второе условие A2<=$E$1, где E1 – ячейка с последней датой периода.Второе условие функции AND
  4. Нажмите Enter для подтверждения формулы и растяните ее ровно на столько клеток вниз, сколько у вас ячеек в столбце с датами. В итоге она будет иметь примерно такой вид: =AND(A2>=$D$1;A2<=$E$1;B2=$F$1).Растягивание функции AND

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

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

  1. Объявите функцию =СУММЕСЛИ в том месте, где хотите разместить сам результат суммирования прибыли или других данных, вычисляемых в периоде.Объявление функции СУММЕСЛИ
  2. В качестве первого столбца для проверки укажите все ячейки с ИСТИНА и ЛОЖЬ, которые мы формировали ранее.Добавление столбца в СУММЕСЛИ
  3. Поставьте точку с запятой, добавьте ИСТИНА, поставьте еще раз точку с запятой и укажите диапазон данных со значениями, сумму которых нужно рассчитать. Нажмите Enter и посмотрите на результат.Выдача формулы СУММЕСЛИ для определения суммы из периода с условиями в Google Таблицах
  4. Я вручную выделяю все ячейки, которые входят в период по дням и смотрю, что формула работает корректно.Проверка формулы СУММЕСЛИ для определения суммы из периода с условиями в Google Таблицах

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

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

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

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

  1. Сначала объявите =SUMPRODUCT или =СУММПРОИЗВ на русском языке, в любой удобной клетке, куда хотите вывести результат.Объявление формулы SUMPRODUCT
  2. Откройте кавычки и укажите, что диапазон числа из диапазона дат должен быть больше или равняться первому числу в периоде. Это делается точно так же, как и при создании вспомогательного столбца в Способе 1.Первое условие для SUMPRODUCT
  3. Закройте скобки после первого условия и поставьте знак *, то есть в нашем случае мы объединим предыдущее условие со следующим. Как раз откройте скобки и напишите второе условие, указав, что значения в диапазоне дат должны быть меньше или равняться последнему числу в периоде.Второе условие для SUMPRODUCT для определения суммы из периода с условиями в Google Таблицах
  4. Остается только добавить еще один знак * и дописать, что при истинных значениях условий нужно суммировать числа из диапазона с прибылью.Данные для суммирования для определения суммы из периода с условиями в Google Таблицах
  5. Нажмите Enter и проверьте, сработала ли функция. Если нет, перечитайте мою инструкцию еще раз и проанализируйте ее составляющие со своими.Просмотр результата второй формулы для определения суммы из периода с условиями в Google Таблицах

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

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

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

Выбор ячейки с условием для определения суммы из периода с условиями в Google Таблицах

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

  1. Активируйте первую ячейку из вспомогательного столбца с функцией AND и добавьте к ней еще одно условие, которое выглядит как B2=$F$1. B2 – первая ячейка с видом деятельности, а F1 – то самое условие, которое мы выбираем при расчетах. Его нужно закрепить, чтобы далее ячейка не съехала.Модернизация первой формулы для определения суммы из периода с условиями в Google Таблицах
  2. Саму формулу растяните до последней ячейки вспомогательного столбца, зажав ее левой кнопкой мыши за правый нижний угол.Растягивание первой формулы для определения суммы из периода с условиями в Google Таблицах
  3. В функции =СУММЕСЛИ изменения произойдут автоматически, поскольку она берет данные из ИСТИНА и ЛОЖЬ. Теперь вы видите, что формула считает по-новому, с учетом введенного условия. Таких условий может быть практически неограниченное количество.Результат модернизации первой формулы для определения суммы из периода с условиями в Google Таблицах

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

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

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

Модернизация второй формулы для определения суммы из периода с условиями в Google Таблицах

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

Результат модернизации второй формулы для определения суммы из периода с условиями в Google Таблицах

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

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

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

источник

Related Posts