Всем добрый день, прошу Вашей помощи.
Ситуация (сразу буду описывать на прикрепленном примере):
1. В книге есть три листа r1, r2, r3, содержащие данные для расчета.
2. На основании этих листов проводятся некоторые вычисления в листах 1.1, 2.1, 3.1.
3. Вычисления и оформление на этих листах одинаковы, с точностью до наименования листа, с которого они берут данные. Т.е. лист 1.1 расчитывается на основании листа r1, лист 2.1 - на основании r2, ... .
Дополнения (о том, наскольлко сложен реальный мир):
1. Листов, что расчетных, что расчитываемых больше.
2. Формул на каждом листе много, они, местами, очень большие.
3. Каждый лист на самом деле использует данные не одного расчетного листа а нескольких.
Проблема:
Иногда принцип расчета какой-то конкретной ячейки на расчетных листах может меняться, требуется найти/создать наиболее удобный механизм корректировки формул на всех листах.
Рассмотренные варианты:
1. Меняем все на одном листе, к примеру, первом. Копируем его поверх остальных. Через замену (Ctrl+H) меняем на странице ссылки с 'r1' на нужный индекс, можно записать макрос.
Почему данный вариант не подходит: ручная замена может привести к ошибкам, макрос нужно затачивать под каждый конкретный лист или группу однотипных листов + переименование листа точно приведет к ошибкам.
2. Использование ДВССЫЛ. Где-то в шапке или в боттоме формируем карту ссылок, в которой расписываем какие листы являются основой для текущего. Составля формулы пишем их через ДВССЫЛ. Поменяли что-то на одном листе, спокойно копируем на другие.
Почему данный вариант не подходит: использование ДВССЫЛ сильно загромождает формулу и сильно мешает ее осмыслению, анализу, корректировкам. Если формула и так занимает много-много строк, то разбавление каждой ссылки ДВССЫЛом делает ее вообще нечитаемой.
К чему пришел я?
Долго думал, как побороть свою проблему, единственное что придумал: создание собственной пользовательской функции, которой даешь указатель на ячейку с формулой образцом и говоришь, какие названия листов в этой формуле и на что нужно заменить, а потом присвоить формулу-результат текущей ячейке.
Т.е., к примеру лист 3.1, ячейка А1:
=Функциякорректировки('Шаблон'!A1;"r1";"r3"). На входе была формула =('r1'!B1+'r1'!B3)/'r1'!B2, получили =('r3'!B1+'r3'!B3)/'r3'!B2.
Более того, просто не придумал как это написать, в идеале функция должна выглядеть так:
=Функциякорректировки('Шаблон'!A1;ДиапазонПравилКорректировки).
ДиапазонПравилКорректировки - область шириной в две ячейки и произвольной длины. В левой ячейке каждой строки содержится искомое имя листа, в правой - то на которое нужно заменить.
Что примерно должна делать функция я понимаю, но знания VB явно нехватает для написания это штуки, представляю ее себе так:
function Функциякорректировки(шаблон, диапазон) {
считываем формулу из шаблона;
пробегаем циклом по всем строкам диапазона {
если лист из правой ячейки не существует - выдаем ошибку;
если все ок, то ищем в формуле подстроку из левой ячейки и меняем на значение правой ячейки;
}
возвращаем новую формулу;
}
Ну и собственно вопрос/просьба: может быть вы сталкивались с подобной задачей и нашли какой-то более элегантный способ ее решения - прошу поделитесь. Ну, а если никакого другого варианта нет, помогите написать функцию. У меня знания VB отсутствуют, функцию я написать-напишу, но это потребует больших временных затрат на изучение базы, а у Вас, если такая функция возможна, займет пару тройку минут. Поэтому и прошу у Вас помощи в реализации.
К теме прилагаю простенький файл, для тестов.
