Excel распределение суммы по месяцам
Обновлено: 07.07.2024
Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этом случае мы устанавливаем СУММЕСЛИМН суммировать суммы по месяцам , используя два критерия: один для соответствующей даты больше или равной первому дню месяца, второй, для соответствующей даты меньше или равной последнему дню месяца. Другими словами, нам нужна формула , как эта:
Более безопасная альтернатива
Он более безопасен, поскольку сроки построены с отдельными значениями год, месяц и день, а не вводятся как текст, который должен быть интерпретирован Excel).
Это позволяет легко строить критерии нужных нам для СУММЕСЛИ. Чтобы соответствующая дата была больше или равна первому числу месяца, мы используем:
И чтобы соответствующая дата была меньше или равна последнему дню месяца, мы используем:
Где КОНМЕСЯЦА автоматически возвращается в последний день.
Конъюнкция с операндом (&) необходима при построении критериев, которые использует логический оператор с числовым значением.
Сумма за месяц игнорируя год
=СУММПРОИЗВ (( МЕСЯЦ ( даты ) = месяц ) * количество )
Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ.
В показанном примере, формула в Н5:
В результате получится сумма всех продаж в марте, независимо от года.
Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. Сокращенный вариант результата массива выглядит следующим образом:
Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:
Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:
Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:
Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.
СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.
Среднее значение по месяцам
Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.
В показанном примере, формула в F4 является:
Функция СРЗНАЧЕСЛИМН может находить среднее в диапазонах на основе нескольких критериев. В этом случае мы устанавливаем СРЗНАЧЕСЛИМН составлять среднее по месяцам , используя два критерия: (1) дата больше или равна первому дню месяца, (2) дата меньше или равна последнему дню месяца. Если мы жестко закодируем даты в январе 2016 года в формулу с использованием функции ДАТА, она будет выглядеть следующим образом:
Это позволяет легко строить критерии, нужные нам для СРЗНАЧЕСЛИМН. Чтобы соответствующие даты были больше или равны первому числу месяца, мы используем:
И чтобы соответствующие даты были меньше или равны последнему дню месяца, мы используем:
КОНМЕСЯЦА автоматически возвращается в последний день того же месяца, потому что мы поставляем ноль для месяца аргумента.
Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.
Если у вас есть диапазон данных, столбец A содержит некоторые даты, а столбец B - количество заказов, теперь вам нужно просуммировать числа на основе месяца и года из другого столбца. В этом случае я хочу подсчитать общее количество заказов за январь 2016 года, чтобы получить следующий результат. В этой статье я расскажу о некоторых трюках, которые помогут решить эту задачу в Excel.
Сумма значений на основе месяца и года с формулой
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Следующая формула может помочь вам получить общее значение на основе месяца и года из другого столбца, пожалуйста, сделайте следующее:
Введите эту формулу в пустую ячейку, в которой вы хотите получить результат: =SUMPRODUCT((MONTH(A2:A15)=1)*(YEAR(A2:A15)=2016)*(B2:B15)) (A2: A15 содержит ли ячейки даты, B2: B15 содержит значения, которые вы хотите суммировать, и число 1 указывает месяц январь, 2016 это год.) и нажмите Enter ключ для получения результата:
Сумма значений на основе месяца и года с Kutools for Excel
Если вас не интересует приведенная выше формула, здесь я могу представить вам удобный инструмент -Kutools for Excel, это может помочь вам решить и эту задачу.
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Во-первых, вы должны скопировать и вставить данные в резервную копию исходных данных.
2. Затем выберите диапазон дат и нажмите Kutools > Формат > Применить форматирование даты, см. снимок экрана:
3. В Применить форматирование даты диалоговом окне выберите формат даты месяца и года Мар-2001 что вы хотите использовать. Смотрите скриншот:
4. Нажмите Ok чтобы изменить формат даты на формат месяца и года, а затем выберите диапазон данных (A1: B15), с которым вы хотите работать, и продолжайте щелкать Kutools > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
5. В Расширенные ряды комбинирования диалоговом окне установите столбец Дата как Основной ключ, и выберите расчет для столбца "Заказ" под Рассчитать раздел, в данном случае я выбираю Сумма, см. снимок экрана:
6. Затем нажмите Ok кнопки, все номера заказов были добавлены вместе на основе одного месяца и года, см. снимок экрана:
Иногда у нас есть общая сумма, которую необходимо распределить на определенное количество месяцев в среднем с определенного начального месяца, в этом случае вы можете использовать формулу для быстрого вычисления среднего числа в Excel, как показано ниже:
Щелкните в начальной ячейке диапазона, который находится под номером первого месяца, введите эту формулу
B1 - ячейка суммы, B3 - номер монтирования, B2 - месяц начала, D1 - первое монтирование в диапазоне месяцев.
Нажмите Enter и перетащите маркер автозаполнения по всем ячейкам месяца. Теперь отображаются ежемесячные выплаты, которые необходимо распределить.
Быстрое разделение нескольких листов на отдельные книги в Excel
Прочие операции (статьи)
Как разбить адрес на отдельные части в Excel?
В этой статье показаны методы разбиения или разделения нескольких адресов на отдельные части в Excel.
Как разбить или разбить число на отдельные цифры в Excel?
Предположим, вам нужно разбить номер на отдельные цифры, как показано на скриншоте ниже, что вы можете сделать для этого? Эта статья предоставит вам два метода.
Как извлечь отчество только из полного имени в Excel?
Как мы знаем, у многих людей есть отчество, и мы обычно не используем их отчество. Теперь у вас есть список полных имен в Excel, и все они имеют отчество, и вы хотите извлечь только отчество.
Как быстро разбить ячейку в таблицу в Excel?
Вот несколько ячеек на листе, каждая из которых содержит несколько значений, и теперь я хочу разбить ячейки на диапазон в виде таблицы, как показано ниже. Есть какие-нибудь хитрости для решения этой задачи в Excel?
Читайте также: