Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Диспетчер имен. Относительная ссылка без привязки к активному листу
 
#1
21.06.2018 11:17:10
Уважаемые коллеги,

В диспетчере имен можно создать зависимость, одновременно привязанную абсолютными ссылками к неактивным листу(-ам) и относительными ссылками на к активному листу
Пожалуйста, посмотрите прилагаемый файл:
* На первом листе я задаю коэффициенты линейной зависимости
* а на втором листе использую формулу (свернутую при помощи диспетчера имен), вычисляющую свое значение в зависимости от указанного рядом (в ячейке слева от формулы) значения аргумента функции.

Подскажите, пожалуйста, можно ли создать именованную_формулу с относительными ссылками без жесткой привязки к активному листу?
В рамках примера - для использования той же формулы не только на втором, но и на третьем листе.

В тоже время, если не использовать диспетчер имен, то относительные ссылки прекрасно копируются и правильно интерпретируются на любом листе...
 
 
 
#2
21.06.2018 12:09:53
Если X - в столбце B :
=Лист1!$C$4*ИНДЕКС($B:$B;СТРОКА())+Лист1!$C$5

Для любого столбца (значение X - слева от ячейки с именем формулы) :
=Лист1!$C$4*Ч(ДВССЫЛ(АДРЕС(СТРОКА();СТОЛБЕЦ()-1)))+Лист1!$C$5
 
 
 
#3
21.06.2018 12:23:33
Возможно я что-то делаю не так, но Ваш вариант также привязывает относительные ссылки к активному листу (на момент редактирования поля диапазон в источнике данных)...
==========================UPDATE===================
Попробовал Ваш второй вариант формулы - он работает. За счет полного использования достаточно громоздкой конструкции в качестве относительной ссылки.
Большое спасибо за решение, однако в виду того, что реальная функция значительно сложнее примера (содержит больше относительных ссылок), то предложенное решение оказывается сложнее, чем создание нескольких одинаковых формул для каждого из листов...

Может быть у кого-нибудь есть другие предложения?
Изменено: IKor - 21.06.2018 12:32:21 (проверка второй формулы)
 
 
 
#4
21.06.2018 12:29:57
=Лист1!$C$4*ИНДЕКС(Лист2!$B:$B;СТРОКА())+Лист1!$C$5
не равно
=Лист1!$C$4*ИНДЕКС($B:$B;СТРОКА())+Лист1!$C$5
 
 
 
#5
21.06.2018 12:36:09
Именно об этой проблеме я и пишу: при использовании Диспетчера имен MS Excel добавляет ко всем ссылкам (и абсолютным, и относительным) имена листов. И если на обычном листе формулы поддаются корректировке, то в редакторе Диспетчера задач коррекция не проходит... :(
 
 
 
#6
21.06.2018 12:43:53
Для редактирования формул в Диспетчере нажмите F2.
 
 
 
#7
21.06.2018 12:51:35
Благодарю за совет.
К сожалению моя проблема возникает в момент сохранения изменений в редакторе Диспетчера задач.

В качестве временного решения (работающего!) мне пришла в голову возможность сворачивания в отдельную именованную_переменную самого аргумента функции (в предложенном Вами синтаксисе).
Однако, я боюсь даже представить себе насколько много аргументов мне придется вычислять и насколько долгим окажется процесс в реальном документе...
=========================UPDATE=============================­=====
Формулу для аргумента можно записать чуть короче
Код
=ДВССЫЛ("RC[-1]";0)
Однако, "ДВССЫЛ является волатильной (volatile) или "летучей" функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться."  
Изменено: IKor - 21.06.2018 13:00:14
 
 
 
#8
21.06.2018 13:58:11
Цитата
IKor написал:
К сожалению моя проблема возникает в момент сохранения изменений в редакторе Диспетчера задач.
(имхо) Длинные формулы лучше создавать на рабочем листе, а потом копировать в поле "Диапазон" диалога "Создание Имени".
 
 
 
#9
21.06.2018 15:43:01
Благодарю за Ваш комментарий.
Обычно я именно так и поступаю.
Более того - именно на листе я готовлю заготовку для источника данных именованного диапазона и в этом случае.
Проблема заключается в том, что в моей версии MS Excel 2013 (и вероятно не только в ней) при сохранении формулы редактор самостоятельно добавляет к "коротким ссылкам на активный лист" название самого листа - что препятствует корректному использованию этого же имени на других листах.
Очевидно, что при редактировании именованной формулы из другого листа (стирании старого имени листа) редактор самостоятельно подставляет новое имя в "короткую ссылку".

В настоящий момент я вижу два решения своей проблемы:
1. использование однотипных, но разных формул для каждого из листов
2. использование единой формулы на базе ДВССЫЛ()

Буду признателен за другие идеи.
Изменено: IKor - 21.06.2018 15:43:53
 
 
 
#10
21.06.2018 23:44:26
Цитата
IKor написал #5:
Именно об этой проблеме я и пишу: при использовании Диспетчера имен MS Excel добавляет ко всем ссылкам (и абсолютным, и относительным) имена листов.
Понял свою ошибку с формулой
=Лист1!$C$4*ИНДЕКС($B:$B;СТРОКА())+Лист1!$C$5
Выход:
Цитата
IKor написал #9:
использование однотипных, но разных формул для каждого из листов
Да, создавать формулы-функции с Областью [видимости] - [текущий] Лист.
-----------------------------------------------
Рекомендую (only) макрос для создания именованных формул на листе:
Скрытый текст
 
 
 
#11
22.06.2018 10:58:14
Спасибо за предложенное решение!
Но я боюсь, как бы лекарство не оказалось бы горше болезни...

Ввиду того, что реальная задача относится к созданию формулы, возвращающей значение плотности среды в зависимости от ее температуры и давления (квадратичная по обоим параметрам), то ИМХО проще уже создать сразу нужную UDF (определенную пользователем функцию).
Но это не наш метод! :)

Уже до каких только ухищрений я не опускался лишь бы не использовать VBA... видимо, и сейчас буду использовать разные именованные функции для разных листов...
 
 
 
#12
26.07.2020 17:28:42
Не подскажете по данной теме... задачка похожего характера.
Попробовал создать команду в диспетчере имен по поиску позиции с относительной строкой на определённом листе. Написал следующую формулу:
=ПОИСКПОЗ(ДВССЫЛ("C" & СТРОКА());База!$D$5:$D$250;0)
но эта конструкция не работает, т.к. результат команды СТРОКА() представляется массивом и как следствие ошибка.
Формула вставленная в ячейку считает без проблем, но введённая в Диспетчер нет.
Если формулу разбить на две части, в том же Диспетчере, как:
1. Ссылка =ДВССЫЛ("C" & СТРОКА())
2. =ПОИСКПОЗ(Ссылка;База!$D$5:$D$250;0)
то работает. а хотелось бы в одну строку не плодя Имена.
Можно сделать, чтобы работало и одной строкой?
Спасибо.
 
 
 
#13
26.07.2020 18:27:57
Vl Vl, а Ваг вопрос соответствует заявленной теме? Пока что вижу единственное, что их связывает - это диспетчер имён.
 
 
 
#14
26.07.2020 18:48:52
Вроде соответствует.. зачинщик темы написал:
Цитата
IKor написал:
Подскажите, пожалуйста, можно ли создать именованную_формулу с относительными ссылками без жесткой привязки к активному листу?
Вот и я пытаюсь создать относительную ссылку посредством Диспетчера, но появилась проблемка с работой формулы.
А создавать отдельную тему на схожую тему посчитал излишним.
 
 
 
#15
12.04.2024 12:34:52
Я тут пытался создать Имя с Адресом без привязки к Активному листу. И вроде бы каждый раз Excel подставляет Имя листа.
Но если убрать имя листа и оставить только знак "!", то адрес становится универсальным.

=!E$7

И независимым. Смещать строки после этого лучше не стоит.
 
 
 
Страницы: 1
Читают тему
Наверх