Использование косвенного для ведения годовой суммы из другого листа

Question

Первый раз постер. Я пытаюсь суммировать ячейки с более ранней датированной рабочей таблицы, чтобы сохранить текущие значения для сравнения. То, как сейчас настраивается моя рабочая тетрадь, на каждом листе у меня есть данные за один год, а используемые данные представляют собой ежемесячные отчеты. Ежемесячные отчеты вставляются в мою таблицу "Шаблон". Данные из них затем организуются во временные ряды на отдельных листах, а название для каждого листа - это год, которому соответствуют данные. При добавлении новых данных в течение года я хотел собрать итоговые данные за предыдущий год, чтобы сравнить похожие временные рамки. Вот как выглядит моя формула в настоящее время.

=SUM(INDIRECT("'"&LEFT($A13,4)&"'!C17:OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))"))

LEFT($A13,4) ссылается на год, который я хочу получить для текущего листа, и MONTH(RIGHT(Template!C$3,10)) определяет номер месяца для текущего отчета. Итак, за май 2015 года Left($A13, 4) вернется "2014" а также MONTH(RIGHT(Template!C$3,10)) вернет значение 5,

Поскольку (я надеюсь) очевидно, я пытаюсь начать суммирование в начале годовой серии, найденной на листе предыдущего года, а затем использовать OFFSET Внесите только те месяцы, которые заканчиваются текущим отчетным месяцем для этого года, чтобы внести изменения с начала года. Это возвращается #REF! в Excel, когда я пытаюсь сделать это. Первые впечатления делают, кажется, что INDIRECT не признает OFFSET как функция, и рассматривает это как текст. Есть ли способ обойти это?

Я вижу, что нечто подобное было опубликовано ранее, но не смог перевести на мою ситуацию. Любая помощь будет принята с благодарностью.

Answer 1

Трудно копировать без данных, так что возьмите это за то, что оно стоит...

Разбивая вашу формулу (один отдельный аргумент на строку), мы имеем

=SUM(
  INDIRECT(
    "'"
    &
    LEFT($A13,4)
    &
    "'!C17:OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))"
  )
)

имеется в виду начало строки "'!C17:OFFSET(... вероятно, рассматривается как текст в Excel, так как он заключен в кавычки. Вы, вероятно, хотите

=SUM(
  INDIRECT(
    "'"
    &
    LEFT($A13,4)
    &
    "'!"
    &
    C17
    &
    ":"
    &
    OFFSET(C$16,MONTH(RIGHT(Template!C$3,10)))
  )
)

для того, чтобы кормить весь синтаксис (', !, :и т. д.) INDIRECT в виде строк и все значения ячеек в результате формулы.

Answer 2

Спасибо, что нашли время, чтобы опубликовать ответ на мой вопрос. Я думаю, что нашел гораздо более подходящее решение моей проблемы, чем я предполагал ранее. Вместо того, чтобы иметь только годовые таблицы с данными за один год, вместо этого я добавляю отчеты в конец новой таблицы со всеми временными рядами на листе под названием "Необработанные данные" со столбцом "А" новый лист, содержащий дату окончания месяца. Так выглядит моя формула для добавления новых отчетов в таблицу "Необработанные данные".

=IF(AND(YEAR($A2)=YEAR(Template!$F$4), MONTH('Raw Data'!$A2)=MONTH(Template!$F$4)),Template!C$35," ")

Template!$F$4 ссылается на дату, найденную в отчете.

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

=SUM(INDEX('Raw Data'!C:C,(MATCH(EOMONTH($A17,-12),'Raw Data'!$A:$A,0))):OFFSET(INDEX('Raw Data'!C:C,(MATCH(EOMONTH($A17,-13),'Raw Data'!$A:$A,0))),MONTH(RIGHT(Template!$F$4,10)),0))

EOMONTH($A17, -12) настроен на постоянную привязку к январю предыдущего года. Это то есть OFFSET по номеру месяца, найденного в отчете, на который ссылается MONTH(RIGHT(Template!$F$4,10)) и подвела.