Как посчитать сумму по датам в excel
Перейти к содержимому

Как посчитать сумму по датам в excel

  • автор:

Как посчитать сумму по дате в Excel

Как посчитать сумму по дате в Excel

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

=SUMIF( A1:A10 , C1 , B1:B10 ) 

Эта конкретная формула вычисляет сумму значений в диапазоне ячеек B1:B10 , только если соответствующие ячейки в диапазоне A1:A10 равны дате в ячейке C1 .

В следующем примере показано, как использовать эту формулу на практике.

Пример: вычислить сумму по дате в Excel

Предположим, у нас есть следующий набор данных, который показывает продажи некоторого продукта в разные даты:

Теперь предположим, что мы хотим рассчитать сумму продаж по дате.

Чтобы создать список уникальных дат, мы можем использовать следующую формулу:

=SORT(UNIQUE( A2:A15 )) 

Мы введем эту формулу в ячейку D2 :

По умолчанию Excel преобразует дату в количество дней с 01.01.1900.

Чтобы преобразовать эти числа в распознаваемый формат даты, просто выделите диапазон ячеек D2:D6 , затем щелкните стрелку раскрывающегося списка формата и выберите «Короткая дата» :

Даты будут преобразованы в распознаваемый формат даты:

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

=SUMIF( $A$2:$A$15 , D2 , $B$2:$B$15 ) 

Мы введем эту формулу в ячейку E2 , затем скопируем и вставим ее в каждую оставшуюся ячейку в столбце E:

Сумма Excel по дате

Из вывода мы видим:

  • Сумма продаж на 04.01.2022 15 .
  • Сумма продаж на 07.01.2022 19 .
  • Сумма продаж на 07.02.2022 19 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Сложение или вычитание дат

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

Добавление или вычитание дней из даты

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

Добавление или вычитание дней из даты с помощью =A2+B2, где A2 — это дата, а B2 — количество дней для добавления или вычитания.

  1. Введите даты выполнения в столбце A.
  2. Введите число дней для добавления или вычитания в столбце B. Вы можете ввести отрицательное число, чтобы вычесть дни из даты начала, и положительное число для добавления к дате.
  3. В ячейке C2 введите =A2+B2 и при необходимости скопируйте вниз.

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

Функцию EDATE можно использовать для быстрого добавления или вычитания месяцев из даты.

Для функции EDATE требуются два аргумента: дата начала и количество месяцев, которые нужно добавить или вычесть. Чтобы вычесть месяцы, введите отрицательное число в качестве второго аргумента. Например, =EDATE(«15.09.19»,-5) возвращает 15.04.19.

Используйте EDATE для добавления или вычитания месяцев из даты. В этом случае = EDATE(A2,B2), где A2 является датой, а B2 имеет количество месяцев для сложения или вычитания.

  1. В этом примере можно ввести начальные даты в столбце A.
  2. Введите число месяцев для добавления или вычитания в столбце B. Чтобы указать, следует ли вычесть месяц, можно ввести знак минуса (-) перед числом (например, -1).
  3. Введите =EDATE(A2,B2) в ячейку C2 и при необходимости скопируйте его.

  • В зависимости от формата ячеек, содержащих введенные формулы, Excel может отображать результаты в виде серийных номеров. Например, 8 февраля 2019 г. может отображаться как 43504.
  • В Excel даты хранятся в виде порядковых номеров, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 года является серийным номером 1, а 1 января 2010 года — серийным номером 40179, так как после 1 января 1900 года это 40 178 дней.
  • Если результаты отображаются в виде серийных номеров, выберите нужные ячейки и выполните следующие действия:
    • Нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Формат ячеек , и откройте вкладку Число .
    • В разделе Категория щелкните Дата, выберите нужный формат даты и нажмите кнопку ОК. Значения в ячейках должны отобразиться как даты.

    Добавление или вычитание лет из даты

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

    =ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2);ДЕНЬ(A2))

    Добавление или вычитание лет из начальной даты с помощью =DATE(YEAR(A2)+B2;MONTH(A2),DAY(A2))

    Принцип работы формулы:

    • Функция YEAR просматривает дату в ячейке A2 и возвращает значение 2019. Затем он добавляет 3 года из ячейки B2, в результате чего в 2022 году.
    • Функции MONTH и DAY возвращают только исходные значения из ячейки A2, но функция DATE требует их.
    • Наконец, функция DATE объединяет эти три значения в дату, которая будет 3 года в будущем — 08.02.22.

    Добавление или вычитание сочетания дней, месяцев и лет к дате или из нее

    В этом примере мы добавляем и вычитаем годы, месяцы и дни из начальной даты с помощью следующей формулы:

    =ДАТА(ГОД(A2)+B2;МЕСЯЦ(A2)+C2;ДЕНЬ(A2)+D2)

    Используйте функцию DATE для добавления или вычитания лет, месяцев или дней к дате или из нее.

    Принцип работы формулы:

    • Функция YEAR просматривает дату в ячейке A2 и возвращает значение 2019. Затем он добавляет 1 год из ячейки B2, что приводит к 2020 году.
    • Функция MONTH возвращает значение 6, а затем добавляет к нему 7 из ячейки C2. Это интересно, так как 6 + 7 = 13, то есть 1 год и 1 месяц. В этом случае формула распознает это и автоматически добавит еще один год к результату, наталкивая его с 2020 по 2021 год.
    • Функция DAY возвращает значение 8 и добавляет к нему значение 15. Это будет работать аналогично части формулы MONTH, если вы перейдете по количеству дней в заданном месяце.
    • Затем функция DATE объединяет эти три значения в дату, которая составляет 1 год, 7 месяцев и 15 дней в будущем — 23.01.21.

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

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

    Расчет даты

    1. В ячейке A1 введите 13.08.02.
    2. В ячейке B1 введите =A1-15.
    3. В ячейке C1 введите =A1+30.
    4. В ячейке D1 введите =C1-15.

    Добавление месяцев к дате

    Мы будем использовать функцию EDATE , и вам потребуется дата начала и количество месяцев, которые вы хотите добавить. Вот как добавить 16 месяцев к 24.10.13:

    Использование формулы ДАТАМЕС для добавления месяцев к дате

    1. В ячейке A1 введите 24.10.13.
    2. В ячейке B1 введите =EDATE(A1,16).
    3. Чтобы отформатировать результаты как даты, выберите ячейку B1. Щелкните стрелку рядом с полем Числовой формат, >Короткая дата.

    Вычитание месяцев из даты

    Мы будем использовать ту же функцию EDATE для вычитания месяцев из даты.

    Введите дату в ячейке A1, а в ячейке B1 введите формулу =EDATE(15.04.2013,-5).

    Вычитание месяцев из даты

    Здесь мы указываем значение даты начала, указывая дату, заключенную в кавычки.

    Вы также можете просто ссылаться на ячейку, содержащую значение даты, или с помощью формулы =EDATE(A1,-5)для того же результата.

    Дополнительные примеры

    Добавление лет к дате или вычитание лет из даты

    Примеры добавления и вычитания дат

    Добавленные или вычитаемые годы

    Суммы с датами в Excel

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

    Сумма, если Дата находится между

    В примере показано, ячейка H7 содержит формулу:

    Эта формула суммирует суммы в столбце D, если Дата в столбце C между датой в Н5 и Н6. В примере, Н5 содержит 15 июля 2019 и H6 содержит 15 августа 2019.

    Функция СУММЕСЛИМН поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), и несколько критериев.

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

    Обратите внимание, что мы должны заключить логические операторы в двойные кавычки ( «» ), а затем присоединиться с ссылками на ячейки с помощью амперсанда (&).

    Если вы хотите включить Дату начала или окончания, а также сроки между ними, используйте больше или равно («>=») и меньше или равно («<=»).

    Сумма, если Дата больше, чем

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

    Сумма, если Дата больше, чем

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

    Эта формула суммирует суммы в столбце D, если Дата в столбце C больше 1 октября 2019 года.

    Функция СУММЕСЛИ поддерживает логические операторы Excel (т. е. «=»,»>»,»>=», и т. д.), так что вы можете использовать их, как вам нравится в ваших критериях.

    В данном случае, мы хотим чтобы дата была больше, чем 1 октября 2019 года, поэтому мы используем оператор больше чем (>).

    Обратите внимание, что мы должны поставить оператор «больше, чем» в двойные кавычки и присоединить к нему амперсанд (&).

    ДАТА как ссылка на ячейку

    Если вы хотите выставить дату на листе, так что он может быть легко изменена, используйте эту формулу:

    где A1-ссылка на ячейку, которая содержит действительную дату.

    Альтернатива с СУММЕСЛИМН

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

    Обратите внимание, что диапазон суммирования всегда стоит первым в функции СУММЕСЛИМН.

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

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

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

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

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

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

    Он более безопасен, поскольку сроки построены с отдельными значениями год, месяц и день, а не вводятся как текст, который должен быть интерпретирован 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, и т.д.), а также используем пользовательский формат даты ( «МММ») для отображения названия месяцев.

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

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

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *