Excel распределение суммы по месяцам
Кто-нибудь помогите написать формулу, не могу распределить суммы из временных периодов по месяцам года. Если это не возможно напишите пожалуйста!
Описание задачи в файле.
Кто-нибудь помогите написать формулу, не могу распределить суммы из временных периодов по месяцам года. Если это не возможно напишите пожалуйста!
Описание задачи в файле. Bliznyakov
Описание задачи в файле. Автор - Bliznyakov
Дата добавления - 07.09.2017 в 09:38
Вариант с двумя доп. столбцами E и F (желтые ячейки) или с одним доп. столбцом Е (оранжевые ячейки) Pelena
Здравствуйте! Еще вариант (правда, гораздо больше доп.столбцов); и результаты почему-то немного отличаются от примера Елены.
Bliznyakov, проверьте, какой вариант правильней считает, интересно почему результаты немного отличаются
Здравствуйте! Еще вариант (правда, гораздо больше доп.столбцов); и результаты почему-то немного отличаются от примера Елены.
Bliznyakov, проверьте, какой вариант правильней считает, интересно почему результаты немного отличаются _Igor_61
Pelena, спасибо огромное. А нельзя сделать расчет без доп. столбцов, просто в примере только часть таблицы, вся таблица примерно раз в 100 больше, если еще добавить доп столбцы очень громоздко получается.
Pelena, спасибо огромное. А нельзя сделать расчет без доп. столбцов, просто в примере только часть таблицы, вся таблица примерно раз в 100 больше, если еще добавить доп столбцы очень громоздко получается. Bliznyakov
_Igor_61, Игорь, если я правильно поняла, там надо учитывать только рабочие дни.
И кстати, ещё вопрос, нет ли таких этапов, которые длятся больше месяца, то есть, например, начинаются в феврале, а заканчиваются в апреле. Тогда моё решение не сработает
_Igor_61, Игорь, если я правильно поняла, там надо учитывать только рабочие дни.
И кстати, ещё вопрос, нет ли таких этапов, которые длятся больше месяца, то есть, например, начинаются в феврале, а заканчиваются в апреле. Тогда моё решение не сработает Pelena
Как разделить общий платеж по месяцам в Excel?
Иногда у нас есть общая сумма, которую необходимо распределить на определенное количество месяцев в среднем с определенного начального месяца, в этом случае вы можете использовать формулу для быстрого вычисления среднего числа в Excel, как показано ниже:
Щелкните в начальной ячейке диапазона, который находится под номером первого месяца, введите эту формулу
B1 - ячейка суммы, B3 - номер монтирования, B2 - месяц начала, D1 - первое монтирование в диапазоне месяцев.
Нажмите Enter и перетащите маркер автозаполнения по всем ячейкам месяца. Теперь отображаются ежемесячные выплаты, которые необходимо распределить.
Быстрое разделение нескольких листов на отдельные книги в Excel
Прочие операции (статьи)
Как разбить адрес на отдельные части в Excel?
В этой статье показаны методы разбиения или разделения нескольких адресов на отдельные части в Excel.
Как разбить или разбить число на отдельные цифры в Excel?
Предположим, вам нужно разбить номер на отдельные цифры, как показано на скриншоте ниже, что вы можете сделать для этого? Эта статья предоставит вам два метода.
Как извлечь отчество только из полного имени в Excel?
Как мы знаем, у многих людей есть отчество, и мы обычно не используем их отчество. Теперь у вас есть список полных имен в Excel, и все они имеют отчество, и вы хотите извлечь только отчество.
Как быстро разбить ячейку в таблицу в Excel?
Вот несколько ячеек на листе, каждая из которых содержит несколько значений, и теперь я хочу разбить ячейки на диапазон в виде таблицы, как показано ниже. Есть какие-нибудь хитрости для решения этой задачи в Excel?
Как суммировать значения по месяцам и годам в Excel?
Если у вас есть диапазон данных, столбец 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. Затем выберите диапазон дат и нажмите Кутулс > Формат > Применить форматирование даты, см. снимок экрана:
3. В Применить форматирование даты диалоговом окне выберите формат даты месяца и года Мар-2001 что вы хотите использовать. Смотрите скриншот:
4. Нажмите Ok чтобы изменить формат даты на формат месяца и года, а затем выберите диапазон данных (A1: B15), с которым вы хотите работать, и продолжайте щелкать Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:
5. В Расширенные ряды комбинирования диалоговом окне установите столбец Дата как Основной ключ, и выберите расчет для столбца "Заказ" под Рассчитать раздел, в данном случае я выбираю Сумма, см. снимок экрана:
6. Затем нажмите Ok кнопки, все номера заказов были добавлены вместе на основе одного месяца и года, см. снимок экрана:
Демо: значения суммы на основе месяца и года с Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Эта функция поможет подвести итоги по месяцу при помощи функции КОНМЕСЯЦА. В примере формула выглядит так:
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этом случае мы устанавливаем СУММЕСЛИМН суммировать суммы по месяцам , используя два критерия: один для соответствующей даты больше или равной первому дню месяца, второй, для соответствующей даты меньше или равной последнему дню месяца. Другими словами, нам нужна формула , как эта:
Более безопасная альтернатива
Он более безопасен, поскольку сроки построены с отдельными значениями год, месяц и день, а не вводятся как текст, который должен быть интерпретирован Excel).
Как правило, это болезненно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «Февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев.
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать это легко: В колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты (1/1/2016, 2/1/2016, 3/1/2016), затем используем формат дат ( «мммм») для отображения названия месяцев.
Это позволяет легко строить критерии нужных нам для СУММЕСЛИ. Чтобы соответствующая дата была больше или равна первому числу месяца, мы используем:
И чтобы соответствующая дата была меньше или равна последнему дню месяца, мы используем:
Где КОНМЕСЯЦА автоматически возвращается в последний день.
Конъюнкция с операндом (&) необходима при построении критериев, которые использует логический оператор с числовым значением.
Сумма за месяц игнорируя год
=СУММПРОИЗВ (( МЕСЯЦ ( даты ) = месяц ) * количество )
Подводя данные по месяцам, игнорируя год, вы можете использовать формулу, основанную на функциях СУММПРОИЗВ и МЕСЯЦ.
В показанном примере, формула в Н5:
В результате получится сумма всех продаж в марте, независимо от года.
Внутри функции СУММПРОИЗВ, функция МЕСЯЦ используется для извлечения номера месяца для каждой даты в наборе данных. Сокращенный вариант результата массива выглядит следующим образом:
Каждое значение сравнивается с 3 (номер месяца март), чтобы получить результат, как этот:
Этот массив затем умножается на значения суммы, ассоциированных с каждой датой марта. Арифметическая операция изменяет ИСТИНА/ЛОЖЬ значения в единицы и нули, поэтому операция выглядит примерно так:
Где 100 это просто заполнитель на любую сумму. В результате получится один массив следующим образом:
Обратите внимание, что только «выжившие« суммы связаны с мартом, а остальные равны нулю.
СУММПРОИЗВ затем суммирует элементы в массиве и рассчитывает результат, 300 в сокращенном примере выше.
Среднее значение по месяцам
Чтобы высчитать среднее за месяц, вы можете использовать формулу, основанную на функции СРЗНАЧЕСЛИМН, с помощью функции КОНМЕСЯЦА.
В показанном примере, формула в F4 является:
Функция СРЗНАЧЕСЛИМН может находить среднее в диапазонах на основе нескольких критериев. В этом случае мы устанавливаем СРЗНАЧЕСЛИМН составлять среднее по месяцам , используя два критерия: (1) дата больше или равна первому дню месяца, (2) дата меньше или равна последнему дню месяца. Если мы жестко закодируем даты в январе 2016 года в формулу с использованием функции ДАТА, она будет выглядеть следующим образом:
Но мы не хотим, чтобы даты жестко кодировались, мы хотим, чтобы Excel создал эти даты для нас. Как правило, это сложно, потому что если вы добавляете названия месяцев в виде текста (т.е. «январь», «февраль», «март» и т.д.) в столбце Е вы должны пойти на лишние хлопоты, чтобы создать даты, которые можно использовать для критериев,
Тем не менее, в данном случае, мы используем простой трюк, чтобы сделать вещи проще: в колонке Е, вместо того, чтобы печатать названия месяцев, мы добавим фактические даты для первого числа каждого месяца (1/1/2016, 2/1/2016, 3 / 1/2016, и т.д.), а также используем пользовательский формат даты ( «МММ») для отображения названия месяцев.
Это позволяет легко строить критерии, нужные нам для СРЗНАЧЕСЛИМН. Чтобы соответствующие даты были больше или равны первому числу месяца, мы используем:
И чтобы соответствующие даты были меньше или равны последнему дню месяца, мы используем:
КОНМЕСЯЦА автоматически возвращается в последний день того же месяца, потому что мы поставляем ноль для месяца аргумента.
Примечание: конъюнкция с операндом (&) необходима при построении критериев на основе ссылки на ячейку.
Суммируя недели, вы можете использовать формулу, основанную на функции СУММЕСЛИМН. В показанном примере, формула в F4 является:
Функция СУММЕСЛИМН может суммировать диапазоны на основе нескольких критериев.
В этой задаче мы настроим СУММЕСЛИМН подводить суммы по неделям, используя два критерия: (1) сроки больше или равны дате в колонке Е, (2) дата меньше, чем дата в колонке Е плюс 7 дней:
Когда эта формула копируется вниз, СУММЕСЛИМН генерирует сумму за каждую неделю.
Даты в колонке Е являются понедельниками. Первая дата жестко закодирована, а остальные понедельники рассчитываются с помощью простой формулы:
Сумма по будням
=СУММПРОИЗВ((ДЕНЬНЕД ( даты ) = Номер_Дня) * значения )
Подводя данные по будним дням (т.е. сумма по понедельникам, вторникам, средам, четвергам и пятницам), вы можете использовать функцию СУММПРОИЗВ вместе с функцией ДЕНЬНЕД.
В показанном примере, формула в H4 является:
СУММПРОИЗВ вместо СУММЕСЛИ
Вы можете спросить , почему мы не используем СУММЕСЛИ или СУММЕСЛИМН функцию? Это очевидный способ подвести отчет по дням недели. Тем не менее, без добавления вспомогательного столбца со значениями будних дней, нет никакого способа , чтобы создать критерии для СУММЕСЛИ, который принимает во внимание рабочие дни.
Вместо этого мы используем удобную функцию СУММПРОИЗВ, которая обрабатывает массивы изящно, без необходимости использовать Ctrl + Shift + Enter.
Мы используем СУММПРОИЗВ только с одним аргументом, который состоит из этого выражения:
Работая изнутри, функция ДЕНЬНЕД конфигурируется с дополнительным аргументом 2, что приводит к его рассчитать номера 1-7 за дни, с понедельника по воскресенье, соответственно. Это не обязательно, но это делает ему легче перечислить дни в порядке и забрать номера в столбце G в определенной последовательности.
ДЕНЬНЕД оценивает каждое значение в указанном диапазоне дат «» и рассчитывает число. Результатом является массив следующим образом:
Числа, рассчитанные ДЕНЬНЕД затем сравнивают со значением в G4, которое равно 1.
Результатом является массив истина/ложь значений.
Затем этот массив умножается на значения в названном «АМТС» диапазоне. СУММПРОИЗВ работает только с числами (не текстом или булевыми значениями), но математические операции автоматически преобразуют ИСТИНА/ЛОЖЬ значения в единицы и нули, так что мы имеем:
С помощью всего этого одного массива в процессе СУММПРОИЗВ суммирует элементы и рассчитывает результат.
Читайте также: