При взаимодействии с Гугл Таблицей пользователь может создавать записи каждый день, указывая дату и какое-нибудь число рядом с ней, к примеру сумму заработанных денег. Здесь может быть еще одно условие – вид деятельности, в котором эта сумма была заработана. Пройдут недели или даже месяцы, таблица наполнится и понадобится узнать, сколько денег было заработано за определенный период в определенной сфере. Вычислить необходимую сумму можно двумя разными методами, которые я и хочу наглядно продемонстрировать.
Обращу ваше внимание на то, что во вступительном абзаце я описал только пример. Практических методов использования описываемых ниже формул может быть безграничное количество. Все зависит исключительно от ваших целей и исходных данных. Это же относится и количеству условий, ведь все формулы можно масштабировать, что тоже будет продемонстрировано, но в конце статьи, чтобы не усложнять и без того не самое простое объяснение.
Исходные данные и подготовительные работы
Я возьму таблицу, содержимое которой уже описал вкратце выше, вместе с этим проведу некоторые подготовительные работы, чтобы оптимизировать ее и облегчить вычисления в дальнейшем. Вы можете полностью скопировать этот пример или использовать его только для ознакомления с принципом работы формул. Давайте начнем с перечисления исходных данных:
-
В одном столбце у нас есть последовательные дни, которые и будут являться периодом в будущем.
-
Следующий столбец показывает сферу деятельности, которой пользователь занимался в каждый из этих дней.
-
Последний – полученная прибыль, сумму которой и будем считать в зависимости от выставленного периода и дополнительных условий.
-
В строках выше я указываю начальную дату периода, конечное число и сферу деятельности, заработок из которой нужно узнать.
Вы можете использовать просто численные обозначения дней от одного до бесконечности, другой формат даты, добавить несколько условий или суммировать не прибыль, а другие значения. Все зависит исключительно от имеющейся у вас на руках таблицы.
Теперь быстро затронем простую тему подготовительных работ. Я буду использовать проверку данных, чтобы сформировать список в этих самых вспомогательных ячейках. Это поможет не редактировать их каждый раз при изменении периода или условий, а просто выбирать подходящее значение из списка.
-
Для начала разберемся с начальной датой периода. Выделим эту самую ячейку и развернем меню «Данные».
-
Из него выберите пункт «Настроить проверку данных».
-
Понадобится ввести только одно изменение – указать столбец, из которого эти данные будут браться. Соответственно, выделите диапазон, где записаны все ваши числа, после чего сохраните изменения.
-
Теперь рядом с числом появится кнопка со стрелкой вниз.
-
Нажмите ее для отображения списка всех дат. Из него можете выбрать любую, которая и будет обозначать начало вашего периода.
-
Абсолютно то же самое проверните и с ячейкой конечной даты, чтобы точно так же переключаться при необходимости.
-
С дополнительными условиями все примерно так же. Выделите пустую ячейку, где хотите сформировать список, запомните номер столбца и переходите в меню «Данные».
-
Снова вызовите функцию «Настроить проверку данных» и в новом окне задайте подходящий диапазон точно так же, как делали это ранее.
-
Главное - записывайте одинаковые сферы деятельности одинаковыми символами, чтобы список формировался правильно, без лишних дублей.
На этом ознакомление с самой таблицей и проведение подготовительных работ завершено, поэтому давайте перейдем к разбору первой, более сложной формулы вычисления необходимой нам информации.
Способ 1: Использование функции AND и СУММЕСЛИ
Функция AND в Google Таблицах позволяет сравнить неравенство и вывести в ячейку значение ИСТИНА или ЛОЖЬ, отталкиваясь от того, подходит ли условие неравенства. Благодаря этой функции мы создадим вспомогательный столбец, при помощи которого будем вычислять все дни, входящие в необходимый нам период. Давайте наглядно разберем все составляющие первой формулы, которая будет нужна для дальнейших расчетов.
-
Выделите первую ячейку в том столбце, который можете сделать вспомогательным. Объявите в нем функцию =AND.
-
Далее разверните скобки и укажите первое условие неравенства (A2>=$D$1;). Обратите внимание на то, что ячейка D1 является статичной благодаря добавлению константы, то есть не будет растягиваться при дальнейшем формировании столбца.
-
Второе условие A2<=$E$1, где E1 – ячейка с последней датой периода.
-
Нажмите Enter для подтверждения формулы и растяните ее ровно на столько клеток вниз, сколько у вас ячеек в столбце с датами. В итоге она будет иметь примерно такой вид: =AND(A2>=$D$1;A2<=$E$1;B2=$F$1).
При помощи этой функции мы вычисляем даты, которые актуальны для выбранного пользователем периода. Значения ИСТИНА или ЛОЖЬ будут меняться в зависимости от того, какие даты вы будете самостоятельно указывать в качестве начала и конца периода. Соответственно, если значение ИСТИНА, значит, дата входит в период и значение прибыли будет включено в формулу. Если ЛОЖЬ, ячейка пропускается и осуществляется переход к следующей.
С первой частью формулы закончили, остается только создать основные расчеты, в которые и будут включены значения ИСТИНЫ и ЛЖИ из вспомогательного столбца. В этом нам поможет СУММЕСЛИ. Эта функция считает значение только в том случае, если ее устраивает заданное условие. Запись этой формулы в нашем случае выглядит следующим образом:
-
Объявите функцию =СУММЕСЛИ в том месте, где хотите разместить сам результат суммирования прибыли или других данных, вычисляемых в периоде.
-
В качестве первого столбца для проверки укажите все ячейки с ИСТИНА и ЛОЖЬ, которые мы формировали ранее.
-
Поставьте точку с запятой, добавьте ИСТИНА, поставьте еще раз точку с запятой и укажите диапазон данных со значениями, сумму которых нужно рассчитать. Нажмите Enter и посмотрите на результат.
-
Я вручную выделяю все ячейки, которые входят в период по дням и смотрю, что формула работает корректно.
Давайте более наглядно разберем ее. Формула имеет вид =СУММЕСЛИ(B17:B30;ИСТИНА;C2:C15). В первую очередь указывается проверяемый диапазон, далее – условие, которое нас устраивает, то есть это должна быть ИСТИНА. Последний аргумент – данные, которые будут суммироваться, если равно ИСТИНА, то есть столбец с нашей прибылью.
Способ 2: Использование функции SUMPRODUCT
Предыдущий метод имеет один весомый недостаток – необходимость использования вспомогательного столбца и совмещение нескольких функций. Да, этот столбец можно скрыть или переместить на другой лист, но это не всегда уместно. Если и вам кажется, что первый метод не очень подходит, давайте разберемся с тем, как выполнить эту же задачу, но с использованием функции SUMPRODUCT.
Объяснить само действие функции немного сложнее. Мы задаем для нее несколько аргументов с логическими условиями, после чего указываем, что нужно суммировать только те значения, которые подпадают под заданные условия. В наглядном виде, если речь идет о решении сегодняшней задачи, использование этой функции выглядит так:
-
Сначала объявите =SUMPRODUCT или =СУММПРОИЗВ на русском языке, в любой удобной клетке, куда хотите вывести результат.
-
Откройте кавычки и укажите, что диапазон числа из диапазона дат должен быть больше или равняться первому числу в периоде. Это делается точно так же, как и при создании вспомогательного столбца в Способе 1.
-
Закройте скобки после первого условия и поставьте знак *, то есть в нашем случае мы объединим предыдущее условие со следующим. Как раз откройте скобки и напишите второе условие, указав, что значения в диапазоне дат должны быть меньше или равняться последнему числу в периоде.
-
Остается только добавить еще один знак * и дописать, что при истинных значениях условий нужно суммировать числа из диапазона с прибылью.
-
Нажмите Enter и проверьте, сработала ли функция. Если нет, перечитайте мою инструкцию еще раз и проанализируйте ее составляющие со своими.
Полностью строка с этой формулой выглядит как =СУММПРОИЗВ((A2:A15>=$D$1)*(A2:A15<=$E$1)*(C2:C15)), поэтому можете просто скопировать ее и вставить, если номера ячеек в столбцах совпадают. В этой строке мы объявили два логических условия, после чего сказали формуле, какие данные стоит суммировать, если они устраивают заданные параметры.
Добавление условий к периоду
Как я и сказал, в конце разберем масштабирование наших формул, добавляя к ним различные условия. В моем случае это вид деятельности, которым человек занимался каждый день и получал за это прибыль.
Получается, что при помощи описанных выше формул нужно посчитать сумму не только в заданном диапазоне, но беря в расчет еще и вид деятельности, записанный в столбце рядом. Каждую из формул придется немного модернизировать, поэтому начнем с первой.
-
Активируйте первую ячейку из вспомогательного столбца с функцией AND и добавьте к ней еще одно условие, которое выглядит как B2=$F$1. B2 – первая ячейка с видом деятельности, а F1 – то самое условие, которое мы выбираем при расчетах. Его нужно закрепить, чтобы далее ячейка не съехала.
-
Саму формулу растяните до последней ячейки вспомогательного столбца, зажав ее левой кнопкой мыши за правый нижний угол.
-
В функции =СУММЕСЛИ изменения произойдут автоматически, поскольку она берет данные из ИСТИНА и ЛОЖЬ. Теперь вы видите, что формула считает по-новому, с учетом введенного условия. Таких условий может быть практически неограниченное количество.
Если вам нужно, оставляю модернизированную функцию с добавлением одного условия.
=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))
Мы с вами разобрались с выполнением не самой простой задачи. Вы узнали, что можете динамически изменять сам период для вычислений и добавлять различные условия. Если нужно, комбинируйте полученные результаты с другими формулами, создавайте сводные таблицы и многое другое. Если у вас возникнут вопросы по теме, смело задавайте их в комментариях, а я постараюсь предоставить оперативный ответ.
Комментарии