Excel (2016). Предположим, у меня есть какая-то часть Excel в AnalysisSheet, которая анализирует данные в некоторых других таблицах данных. И предположим следующее:
На самом деле таких таблиц несколько. Например, они могут называться MayData, JunData, JulData и так далее.
Все технические данные идентичны по формату; только их данные разные. Например, это может быть один и тот же финансовый отчет за разные месяцы
AnalysisSheet когда-либо просматривает только ОДИН из листов данных. В любой данной "точке использования" он анализирует ЛЮБОЕ MayData, ИЛИ JunData, ИЛИ JulData. и т.п.
Таблицы данных могут находиться в той же рабочей книге, что и AnalysisSheet, или они могут находиться в другой отдельной рабочей книге, или они могут быть распределены по ряду других рабочих книг или их комбинации.
Мне нужно провести анализ сегодня, и я не хочу бросать камни в мой компьютер (см. Ниже, где я опишу, как я это делаю в настоящее время)
ВОПРОС: В чистом Excel (то есть без VB и сторонних надстроек), как минимизировать работу, необходимую для того, чтобы позволить пользователю SheetAnalyze изменить, из каких данных берутся данные?
Как я сейчас это делаю
Я использую INDIRECT() для динамического построения ссылок на требуемый лист данных. Так, например, в AnalysisSheet у меня может быть следующее:
B1 = "MayData" (то есть имя листа, содержащего данные)
B2 = "MayReport.xlsx" (т. Е. Имя рабочей книги, содержащей таблицу данных; это поле остается пустым, если таблица данных находится в той же книге, что и AnalysisSheet)
B3 = "C23" (например, т. Е. Имя некоторой интересующей ячейки в листе данных)
И тогда у меня будет что-то вроде этого в B4:
B4 = CONCATENATE("'", IF(ISBLANK(B2), "", CONCATENATE("[", B2, "]")), B1, "'!», B3)
Последний в этом примере создает строку: '[MayReport.xlsx] MayData'!C23
Затем я могу использовать это в INDIRECT() для правильного анализа. Итак, чтобы посмотреть на эту конкретную камеру, я мог бы иметь;
B5 = НЕПРЯМОЙ (B4) (см. Примечание **)
который невосприимчив к изменениям названия таблицы данных или ее рабочей книги (или даже, если на то пошло, названия этой интересующей ячейки, хотя в этом примере я сказал, что все таблицы данных идентичны по формату),
Все, что мне нужно сделать, чтобы изменить, какие данные просматриваются, это изменить ячейки B1 и B2. И если я буду осторожен с вещами, я мог бы сократить его до изменения только названия месяца (в этом примере), а затем построить даже то, что в B1 и B2 для этого.
ПРОБЛЕМА В том, что INDIRECT() является изменчивым! В результате, когда набор данных большой, и, возможно, происходит много подобных вещей, производительность снижается. В худшем случае мне пришлось несколько минут ждать единственного изменения одной ячейки в AnalysisSheet, в то время как Excel просматривает каждую функцию INDIRECT() и перезапускает ее. Внизу в области состояния в правом нижнем углу моего листа я вижу, как загружается центральный процессор на всех узлах.
Буду признателен за любые идеи.
** Конечно, как правило, я бы не просто пошел на одну клетку. Вместо этого я бы извлек одну или несколько целых таблиц из целевого листа данных в лист или листы в той же книге, что и AnalysisSheet. Оттуда все остальное может использовать ссылки на эту локальную версию данных.