IFS Excel формула с датами

Question

Я снова в конце финансового года и настраиваю электронные таблицы Excel. Я использую формулу sumifs, которая просматривает дату и суммирует один из столбцов, если даты находятся между определенными значениями. то есть сложите значения в столбце «Банковские расходы» всех записей за период с 1 по 30 апреля.

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

Я не вижу, как это сделать, используя дату в виде текстового столбца. У кого-нибудь есть какие-либо идеи.

Для полной информации. Формула, которую я использую ...

= SUMIFS($ E $ 23:$ E $ 1000, $ A $ 23:$ A $ 1000, "> = 01/04/2017", $ A $ 23:$ A $ 1000, "<= 30/4/2017")

Спасибо за любую помощь

Answer 1

Возможно, вы захотите попробовать заменить критерии в SUMIF на CONCAT/CONCATENATE (в зависимости от версии Office) следующим образом:

CONCAT(">=01/03/";$A$1)

Затем вы помещаете желаемый год в $ A $ (или любую другую свободную ячейку). Ваша формула OP в конечном итоге будет выглядеть так:

=SUMIFS($E$23:$E$1000,$A$23:$A$1000,CONCAT(">=01/04/",$A$1),$A$23:$A$1000,CONCAT("<=30/4/",$A$1))

Когда вам нужно изменить год, просто введите новый год в $ A $, и все это должно быть пересчитано.

С уважением.

Answer 2

Создайте два именованных диапазона StartDate и EndDate затем обратитесь к ним в вашей формуле.

В идеале я хотел бы предложить преобразовать ваши данные в таблицу Excel (aka: ListObject), чтобы вы могли использовать структурированные ссылки в своих формулах. Их гораздо легче читать, чем диапазон адресов. Кроме того, вам не нужно редактировать диапазоны при добавлении новых данных, поскольку таблица расширяется автоматически.

Если вы не можете преобразовать свои данные в таблицу Excel, вы все равно можете использовать именованные диапазоны в формуле:

=SUMIFS(E23:$E$1000,A23:$A$1000,">="&StartDate,A23:$A$1000,"<="&EndDate)