Как найти сумму данных для дня

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

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

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

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

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))

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

Давайте представим, что мы работаем в торговой компании. Руководитель поставил нам задачу посчитать сумму продаж за последние 15 дней. За конкретный промежуток времени.

Давайте рассмотрим как это сделать.

У нас есть таблица с данными по продажам за каждый день. Для выполнения задачи нам потребуется функция СУММЕСЛИМН.

Как работает функция СУММЕСЛИМН?

Функция СУММЕСЛИМН в Excel используется для суммирования значений по нескольким критериям.

Синтаксис функции выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …) 

• диапазон_суммирования  – это диапазон данных, по которым будут вычисляться условия указанных вами критериев для суммирования данных;
• диапазон_условия1, условие1 – диапазон, в котором проверяется первое условие функции. Criteria_range1 (диапазон_условия1) и criteria1(условие1) составляют пару, определяющую, к какому диапазону применяется определенное условие при поиске. Соответствующие значения найденных в этом диапазоне ячеек суммируются в пределах аргумента sum_range (диапазон_суммирования).
• [диапазон_условия2], условие 2] – (опционально) – второй диапазон критериев, по которым будут вычисляться данные;

Формула для суммирования значений между двумя датами

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

Скачайте пример файла по ссылке.

Для суммирования продаж за указанный период нам потребуется в любой свободной ячейке использовать следующую формулу:

=СУММЕСЛИМН(B2:B28;A2:A28;»>=01.06.2018″;A2:A28;»<=15.06.2018″)

После ввода этой формулы, функция вернет значение 559 134₽. Это значение соответствует сумме продаж за период с 1 июня по 15 июня 2018 года.

Для проверки корректности расчета выделите диапазон ячеек с данными продаж, соответствущим этим датам и в правом нижнем углу вы увидите сумму значений.

Как работает эта формула

В нашей формуле мы использовали логические операторы в функции СУММЕСЛИМН, которые помогают нам суммировать данные в указанном диапазоне дат.

Давайте разложим формулу на составные части для понимания ее работы:

• Первым делом мы указываем диапазон с данными продаж (B2:B28), среди которого нам нужно выбрать какие значения мы будем суммировать
• Затем, мы указываем диапазон с данными, к которому будет применяться проверка на соответствие условию. В нашем случае это диапазон с датами (A2:A28)
• Следующим шагом мы задаем условие по отношению к диапазону с датами, по которому формула должна определить какие данные суммировать. Мы указали первое условие, что дата должна быть больше или равна 01.06.2018
• Заключительным шагом мы задаем второе условие к диапазону с датами (A2:A28), по которому формула должна суммировать данные за период меньший или равный 15.06.2018

Как результат, функция суммирует значения в диапазоне с 1 по 15 июня 2018 года.

Как суммировать значения между двумя динамическими датами

На примере выше мы рассмотрели как суммировать данные между двумя конкретными датами. Но что, если мы хотим суммировать данные, например, за последние 7 дней на ту дату, в которую мы открыли файл? Если мы не хотим каждый раз проставлять в формулу конкретные даты?

В этом случае нам поможет следующая формула:

=СУММЕСЛИМН(B2:B18;A2:A18;»<=»&СЕГОДНЯ();A2:A18;»>=»&СЕГОДНЯ()-6)

Как работает эта формула

В формуле, указанной выше, мы используем функцию СЕГОДНЯ для автоматического вычисления текущей даты.

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

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

Таким образом, диапазон двух дат в формуле теперь выглядит как: «Посчитай данные за период с сегодняшнего дня, до минус 6 дней от сегодняшнего дня».

Если у вас остались вопросы по этому примеру оставляйте их в комментариях.

=СУММЕСЛИМН ( значение ; дата ; «> =» & A1 ; дата ; «<» & A1 + 7 )

Суммируя недели, вы можете использовать формулу, основанную на функции СУММЕСЛИМН. В показанном примере, формула в F4 является:

=СУММЕСЛИМН($C$4:$C$12; $B$4:$B$12; «>=»&E4; $B$4:$B$12; «<» &E4+7)

Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.

В этой задаче мы настроим СУММЕСЛИМН подводить суммы по неделям, используя два критерия: (1) сроки больше или равны дате в колонке Е, (2) дата меньше, чем дата в колонке Е плюс 7 дней:

=СУММЕСЛИМН ( сумма ; дата ; «> =» & E4 ; дата ; «<» & E4 + 7 )

Когда эта формула копируется вниз, СУММЕСЛИМН генерирует сумму за каждую неделю.

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

= E4 + 7

Сумма по будням

=СУММПРОИЗВ((ДЕНЬНЕД ( даты ) = Номер_Дня) * значения )

Подводя данные по будним дням (т.е. сумма по понедельникам, вторникам, средам, четвергам и пятницам), вы можете использовать функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.

В показанном примере, формула в H4 является:

=СУММПРОИЗВ((ДЕНЬНЕД($B$4:$B$11;2)=G4)*$D$4:$D$11)

СУММПРОИЗВ вместо СУММЕСЛИ

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

Вместо этого мы используем удобную функцию СУММПРОИЗВ, которая обрабатывает массивы изящно, без необходимости использовать Ctrl + Shift + Enter.

Мы используем СУММПРОИЗВ только с одним аргументом, который состоит из этого выражения:

( Пн — пт ( даты ; 2 ) = G4 ) * АМТС

Работая изнутри, функция ДЕНЬНЕД конфигурируется с дополнительным аргументом 2, что приводит к его рассчитать номера 1-7 за дни, с понедельника по воскресенье, соответственно. Это не обязательно, но это делает ему легче перечислить дни в порядке и забрать номера в столбце G в определенной последовательности.

ДЕНЬНЕД оценивает каждое значение в указанном диапазоне дат «» и рассчитывает число. Результатом является массив следующим образом:

{3; 5; 3; 1; 2; 2; 4; 2}

Числа, рассчитанные ДЕНЬНЕД затем сравнивают со значением в G4, которое равно 1.

{3; 5; 3; 1; 2; 2; 4; 2} = 1

Результатом является массив истина/ложь значений.

{ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ}

Затем этот массив умножается на значения в названном «АМТС» диапазоне. СУММПРОИЗВ работает только с числами (не текстом или булевыми значениями), но математические операции автоматически преобразуют ИСТИНА/ЛОЖЬ значения в единицы и нули, так что мы имеем:

{0; 0; 0; 1; 0; 0; 0; 0} * {100; 250; 75; 275; 250; 100; 300; 125}

Который дает:

{0; 0; 0; 275; 0; 0; 0; 0}

С помощью всего этого одного массива в процессе СУММПРОИЗВ суммирует элементы и рассчитывает результат.