У меня есть электронная таблица, которая использует функцию «суммис» и подсчитывает месяцы в диапазонах дат;
=СУММЕСЛИМН(Сумма;DPIF;">=1/1/2021";DPIF;"<=31/1/2021")
Эта формула повторяется двенадцать раз (для каждого месяца), и с переходом с 2021 на 2022 год мне теперь придется вручную изменять диапазоны дат (годов) во всех двенадцати формулах.
Я попытался изменить только год (в формуле 2021), чтобы он ссылался на другую ячейку, но Excel больше не видит его как дату.
Как сделать так, чтобы мою электронную таблицу можно было использовать из года в год?
решение1
Подход 1
Вместо того, чтобы жестко кодировать начальные и конечные даты в своих SUMIFS
функциях, сгенерируйте их с помощью формул Excel, а затем ссылайтесь на ячейки, содержащие эти вычисленные значения дат.DATE
иEOMONTH
функции можно использовать, как показано на снимке экрана ниже.
Тогда вашу 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
- поместите две полные даты (не только год) в отдельную ячейку. Затем в формуле замените значение даты ссылками на ячейки
- чтобы изменить все формулы одновременно, выполните команду «заменить все» на вашем листе
- в следующем году измените только значения даты в двух отдельных ячейках