☰

Избегайте необходимости менять год в датах формул

tag-icon • tag-icon microsoft-excel
Избегайте необходимости менять год в датах формул

У меня есть электронная таблица, которая использует функцию «суммис» и подсчитывает месяцы в диапазонах дат;

=СУММЕСЛИМН(Сумма;DPIF;">=1/1/2021";DPIF;"<=31/1/2021")

Эта формула повторяется двенадцать раз (для каждого месяца), и с переходом с 2021 на 2022 год мне теперь придется вручную изменять диапазоны дат (годов) во всех двенадцати формулах.

Я попытался изменить только год (в формуле 2021), чтобы он ссылался на другую ячейку, но Excel больше не видит его как дату.

Как сделать так, чтобы мою электронную таблицу можно было использовать из года в год?

решение1

Подход 1

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

снимок экрана для генерации дат начала/окончания для 12 месяцев определенного года

Тогда вашу SUMIFSформулу для января можно изменить следующим образом:

=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)

Для месяца Январь ячейка Sheet1!B3имеет начальную дату месяца и Sheet1!C3конечную дату. Измените эти ссылки на ячейки, куда вы поместили вычисленные значения дат. Для Февраля ячейки начальной/конечной даты — Sheet1!B4и Sheet1!C4, для Марта Sheet1!B5— и Sheet1!C5и т. д. Если ваши 12 ежемесячных SUMIFSформул организованы в виде диапазона из 12 строк по 1 столбцу, вы сможете скопировать формулу Января вниз на следующие 11 месяцев.

Каждый из амперсандов (знаков &) в формулах объединяет оператор сравнения со значением в указанной ячейке, поэтому измененная формула ">="&Sheet1!B3фактически эквивалентна , ">=01/01/2024"поскольку Sheet1!B3возвращает значение даты 01.01.2024.

Чтобы изменить год, просто измените значение года в соответствующей ячейке - ячейка Sheet1!B1на снимке экрана. SUMIFSВсе ваши 12 ежемесячных формул будут автоматически ссылаться на даты начала/окончания нового года, что устраняет необходимость редактирования формул.

Этот подход также управляет датой окончания месяца для февраля в високосные годы (как показано на снимке экрана в ячейке C4).

Подход 2

Альтернативный подход заключается в преобразовании DPIFзначений даты в значения номеров месяцев путем добавления дополнительного диапазона к существующим Amount/DPIFдиапазонам с помощьюMONTHфункционировать как

=MONTH(date)

где dateпредставляет ячейку даты в DPIFдиапазоне.

Вызов этих ежемесячных значений MPIFозначает, что вы затем можете использовать формулы, такие как

=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)

не беспокоясь о годе вообще.

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

В этом случае уточнением решения является добавление значений года к данным в YPIFдиапазоне с использованиемYEARфункция для получения этих значений. Затем вы бы добавили второй критерий к SUMIFSформулам, чтобы включить эти годовые значения, например

=SUMIFS(Amount;MPIF;1;YPIF;2024)

Конечно, это означает, что вам все равно придется изменять 12 формул каждый год, хотя эти изменения немного проще, чем те, которые у вас есть сейчас. Чтобы избежать этого, поместите значение года в отдельную ячейку и ссылайтесь на него, используя формулу, например =SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)

Подход 3

Не рекомендую, но включен для полноты, измените ваши формулы на

=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
и т. д.

Это включает в себя единовременную работу по редактированию, которая будет действовать в течение 2021, 2022 и 2023 годов, но исключит 29/02/2024 из ежемесячной суммы за февраль 2024 года, если только кто-то не забудет внести изменения, и может вызвать проблемы в 2025 году, если не вернуть все обратно.

решение2

  • поместите две полные даты (не только год) в отдельную ячейку. Затем в формуле замените значение даты ссылками на ячейки
  • чтобы изменить все формулы одновременно, выполните команду «заменить все» на вашем листе
  • в следующем году измените только значения даты в двух отдельных ячейках

Связанный контент