Странно, но в Excel невозможно напрямую сгенерировать список имен листов из книги. В этой статье будет рассказано, как создать список всех листов, содержащихся в книге. Как и в предыдущей статье, здесь мы будем использовать макрофункцию из Excel 4 XLM в именованной формуле.

Начнем с книги, в которой много рабочих листов или листов с диаграммами. Выполните следующие действия, чтобы создать список названий листов.

1. Вставьте новый лист, на котором будет находиться список.
2. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
3. Впишите в поле Имя название Список.
4. Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"").
5. Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.

Рис. 9.1. Работа с окном Создание имени для создания именованной формулы

Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа. Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Список;СТРОКА()).

На рис. 9.2 приведена данная формула для диапазона А1:А10. В книге семь листов, поэтому формула возвращает ошибку #ССЫЛКА! при попытке отобразить имя несуществующего листа. Чтобы избежать этого, измените формулу следующим образом: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА());"").

Рис. 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА()).

Рис. 9.3. Создание списка гиперссылок

Зачем может понадобиться список названий листов? На рис. 9.3 показано оглавление, созданное с помощью ссылки ГИПЕРССЫЛКА. В ячейке В1 записана следующая формула: =ГИПЕРССЫЛКА("#"&A1&"!A1";"Перейти по ссылке"). Щелкнув на гиперссылке, вы активизируете лист и выделяете ячейку А1. К сожалению, Excel не поддерживает гиперссылок, ведущих на лист с диаграммами, поэтому при попытке сослаться на такой лист программа выдаст ошибку.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).