Я рассчитываю рассчитать набор данных, которые я могу использовать повторно, не обновляя ссылки на столбцы каждый месяц. На этом снимке экрана показаны примеры таблиц, с которыми я работаю.
В таблице 1 приведены исходные данные для различных проектов, которые в дальнейшем разбиты на отдельные позиции. Цифры обновляются каждый месяц, и по мере закрытия месяца заголовок меняется с «Прогноз» на «Фактические данные».
В таблице 2 я суммирую данные по проектам и позициям за конкретный месяц. Я хочу, чтобы он всегда показывал фактические данные за последний месяц и прогноз на следующий месяц. В каждой строке должна быть указана произвольная группа проектов, которые будут перечислены отдельно (ячейки I9:K10). Я хочу заголовки по категориям (есть несколько из них, которые также должны быть сгруппированы). Я добавляю данные для каждого проекта в отдельности, так как они могут отображаться в любом месте таблицы. Итак, прямо сейчас формула в I6 имеет вид =SUMIFS(D:D,$A:$A,I$9,$B:$B,$I5)+SUMIFS(D:D,$A:$A,J$9,$B:$B,$I5)+SUMIFS(D:D,$A:$A,K$9,$B:$B,$I5)
(т.е. добавление значений за этот месяц при сопоставлении идентификатора проекта и категории. Повторите для каждого идентификатора проекта)
Вышеуказанный метод не самый элегантный, но он работает для моих нужд. Единственная проблема заключается в том, что мне нужно обновлять исходный столбец D:D каждый месяц. Я хочу быть в состоянии автоматизировать эту часть.
я смогу
- найдите последнее вхождение 'Actuals' и заполните месяц 2017 05 в I4, используя =LOOKUP(2,1/($1:$1="Actuals"),$2:$2)
- найти первое вхождение "Прогноз" и заполнить месяц 2017 06 в K4, используя =INDEX($2:$2,1,MATCH("Forecast",$1:$1,0))
- заполните соответствующие номера столбцов (4 и 5) в J4 и L4, используя =MATCH(I4,$2:$2,0)
Теперь мне нужен способ найти это значение '4' в J4 и использовать его вместо D:D в SUMIFS, чтобы в следующем месяце, когда фактические данные были обновлены в Таблице 1, Таблица 2 автоматически выберет данные следующего месяца. ,
Обратите внимание, что в окончательной рабочей таблице таблицы 1 и 2 будут находиться в отдельных таблицах.
Я нашел некоторые решения, использующие VBA, с которыми мне не хочется заниматься. Есть ли способ решить эту проблему с помощью обычных формул Excel?