Сумма по месяцам в Excel

Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:

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

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

Более безопасная альтернатива

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

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

Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать это легко: В колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты (1/1/2016, 2/1/2016, 3/1/2016), затем используем формат дат ( "мммм") для отображения названия месяцев.

Это позволяет легко строить критерии нужных нам для СУММЕСЛИ. Чтобы соответствующая дата была больше или равна первому числу месяца, мы используем:

Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ. 

 В показанном примере, формула в Н5: 

В результате получится сумма всех продаж в марте, независимо от года.

Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. 

Сокращенный вариант результата массива выглядит следующим образом:

 Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот: 

 Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так: 

 Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом: 

Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.

СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.

Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.

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

Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.

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

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

Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать вещи проще: в колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты для первого числа каждого месяца (1/1/2016, 2/1/2016, 3 / 1/2016, и т.д.), а также используем пользовательский формат даты ( "МММ") для отображения названия месяцев.

Это позволяет легко строить критерии, нужные нам для СРЗНАЧЕСЛИМН. Чтобы соответствующие даты были больше или равны первому числу месяца, мы используем:

 И чтобы соответствующие даты были меньше или равны последнему дню месяца, мы используем: 

КОНМЕСЯЦА автоматически возвращается в последний день того же месяца, потому что мы поставляем ноль для месяца аргумента.

Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.