= СУММЕСЛИМН( значения ; Диапазон_Значений ; «> =» & A1 ; Диапазон_Значений; «<=» & КОНМЕСЯЦА( A1 ; 0 ))
Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:
=СУММЕСЛИМН(C4:C9;B4:B9; «>=» & E4; B4:B9; «<=» &КОНМЕСЯЦА( E4; 0 ))
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этом случае мы устанавливаем СУММЕСЛИМН суммировать суммы по месяцам , используя два критерия: один для соответствующей даты больше или равной первому дню месяца, второй, для соответствующей даты меньше или равной последнему дню месяца. Другими словами, нам нужна формула , как эта:
= СУММЕСЛИМН( сумма ; дата ; «> =» & «1/1/2016» ; дата ; «<=» & «1/31/2016» )
Более безопасная альтернатива
=СУММЕСЛИМН ( сумма ; дата ; «> =» & ДАТА ( 2016 ; 1 ; 1 ); дата ; «<=» & ДАТА ( 2016 ; 1 ; 31 ))
Он более безопасен, поскольку сроки построены с отдельными значениями год, месяц и день, а не вводятся как текст, который должен быть интерпретирован Excel).
Как правило, это болезненно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «Февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев.
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать это легко: В колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты (1/1/2016, 2/1/2016, 3/1/2016), затем используем формат дат ( «мммм») для отображения названия месяцев.
Это позволяет легко строить критерии нужных нам для СУММЕСЛИ. Чтобы соответствующая дата была больше или равна первому числу месяца, мы используем:
«> =» & E4
И чтобы соответствующая дата была меньше или равна последнему дню месяца, мы используем:
«<=» &КОНМЕСЯЦА( E4 ; 0 )
Где КОНМЕСЯЦА автоматически возвращается в последний день.
Конъюнкция с операндом (&) необходима при построении критериев, которые использует логический оператор с числовым значением.
Сумма за месяц игнорируя год
=СУММПРОИЗВ (( МЕСЯЦ ( даты ) = месяц ) * количество )
Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ.
В показанном примере, формула в Н5:
=СУММПРОИЗВ((МЕСЯЦ(B4:B19)=3)*E4:E19)
В результате получится сумма всех продаж в марте, независимо от года.
Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. Сокращенный вариант результата массива выглядит следующим образом:
{ 1 ; 1 ; 1 ; 2 ; 2 ; 2 ; 3 ; 3 ; 3 }
Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:
{ ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ЛОЖЬ ; ИСТИНА ; ИСТИНА ; ИСТИНА }
Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:
{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 1 ; 1 } * { 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 ; 100 }
Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:
{ 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 100 ; 100 ; 100 }
Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.
СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.
Среднее значение по месяцам
=СРЗНАЧЕСЛИМН( значения ; даты ; «> =» & A1 ; даты ; «<=» & КОНМЕСЯЦА( A1 ))
Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.
В показанном примере, формула в F4 является:
=СРЗНАЧЕСЛИМН(D5:D12;C5:C12;»>=F5″;C5:C12;»»<=КОНМЕСЯЦА(F5;0))
Функция СРЗНАЧЕСЛИМН может находить среднее в диапазонах на основе нескольких критериев. В этом случае мы устанавливаем СРЗНАЧЕСЛИМН составлять среднее по месяцам , используя два критерия: (1) дата больше или равна первому дню месяца, (2) дата меньше или равна последнему дню месяца. Если мы жестко закодируем даты в январе 2016 года в формулу с использованием функции ДАТА, она будет выглядеть следующим образом:
=СРЗНАЧЕСЛИМН( суммы ; сроки ; «> =» & ДАТА ( 2016 ; 1 ; 1 ); даты ; «<=» & ДАТА ( 2016 ; 1 ; 31 ))
Кодирование дат
Но мы не хотим, чтобы даты жестко кодировались, мы хотим, чтобы Excel создал эти даты для нас. Как правило, это сложно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев,
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать вещи проще: в колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты для первого числа каждого месяца (1/1/2016, 2/1/2016, 3 / 1/2016, и т.д.), а также используем пользовательский формат даты ( «МММ») для отображения названия месяцев.
Это позволяет легко строить критерии, нужные нам для СРЗНАЧЕСЛИМН. Чтобы соответствующие даты были больше или равны первому числу месяца, мы используем:
«> =» & E4
И чтобы соответствующие даты были меньше или равны последнему дню месяца, мы используем:
«<=» & КОНМЕСЯЦА( E4 ; 0 )
КОНМЕСЯЦА автоматически возвращается в последний день того же месяца, потому что мы поставляем ноль для месяца аргумента.
Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.