Определение имени листа в MS EXCEL для использования в функции ДВССЫЛ()

При изменении имени листа, все ссылки в формулах автоматически обновятся и будут продолжать работать. Исключение составляет функция ДВССЫЛ(), в которой имя листа может фигурировать в текстовой форме ДВССЫЛ("Лист1!A1"). В статье показано как использовать функцию ЯЧЕЙКА(), чтобы сохранить работоспособность формулы с функцией ДВССЫЛ().

Имя листа можно определить с помощью функции ЯЧЕЙКА(), об этом читайте в статье Определяем имя листа.

Предположим, что в ячейке B4 на Листе1 имеется формула =ДВССЫЛ("лист2!A1"). Если Лист2 переименовать в Лист3, то вышеуказанная формула работать не будет. Чтобы работоспособность формулы сохранилась - определим имя листа с помощью функции ЯЧЕЙКА() (см. файл примера).

  • Запишем на Листе1 в ячейке B1 формулу =ЯЧЕЙКА("адрес";лист2!A1) Формула вернет результат [_Определяем_имя_листа.xlsx]Лист2!$A$1, т.е. полный адрес ячейки с указанием названия книги и имени листа (ссылка должна быть на столбец с названием из одной буквы, например, ссылка лист2!AВ1 не годится);
  • Предполагая, что название книги не содержит квадратных скобок [  ], запишем формулу для изъятия из полученного результата имени листа: =ПСТР(B1;ПОИСК("]";B1)+1;ДЛСТР(B1)-ПОИСК("]";B1)-5);
  • Если в качестве названия листа использовано число, то функция ЯЧЕЙКА() возвращает название книги и листа в апострофах ('), например, '[_Определяем_имя_листа.xlsx]123456'!$A$1, что может привести к ошибке при определении имени листа;
  • Записав в ячейке В2 формулу =ЕСЛИОШИБКА(ПОИСК("'";B1);0), получим, что если название листа – число, то результат =1, если текст, то 0;
  • Слегка модифицируем формулу в ячейке B3 для определения названия листа: =ПСТР(B1;ПОИСК("]";B1)+1;ДЛСТР(B1)-ПОИСК("]";B1)-5-B2);

Теперь, записав вместо формулы =ДВССЫЛ("лист2!A1") формулу =ДВССЫЛ(B3&"!A1"), мы решим задачу: изменение имени Листа2 не повлияет на работоспособность формулы.

Внимание!
Иногда, когда открыто несколько книг, функция ЯЧЕЙКА() может работать некорректно. Для восстановления работоспособности формулы нужно нажать клавишу F9 (Формулы/ Вычисления/ Пересчет).

ПРИМЕЧАНИЕ:
С помощью обычных формул (не VBA) невозможно определить имя активного листа и адрес активной ячейки.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)