Как создать список всех листов, содержащихся в книге Excel при помощи макрофункции
Странно, но в Excel невозможно напрямую сгенерировать список имен листов из книги. В этой статье будет рассказано, как создать список всех листов, содержащихся в книге. Как и в предыдущей статье, здесь мы будем использовать макрофункцию из Excel 4 XLM в именованной формуле.
Начнем с книги, в которой много рабочих листов или листов с диаграммами. Выполните следующие действия, чтобы создать список названий листов.
- Вставьте новый лист, на котором будет находиться список.
- Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
- Впишите в поле Имя название Список.
- Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»») .
- Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.
Рис. 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).
Как получить список листов в книге excel
Данная команда создает новый лист и выводит в данном листе список листов рабочей книги, за исключением созданного листа со списком:
Чтобы воспользоваться данной командой перейдите во вкладку «ЁXCEL» Главного меню, нажмите кнопку «Листы» и выберите команду «Получить список листов книги»:
В открывшемся диалоговом окне выберите один из вариантов отображения списка листов и нажмите кнопку «ОК»:
Для отмены операции нажмите кнопку отмены:
Как вывести список листов в Excel
Иногда необходимо вывести список листов Excel в указанный диапазон. Если листов не много, то это достаточно просто сделать. Однако, если листов более 10, то задача займет много времени.
C помощью надстройки VBA-Excel можно выводить список выбранных листов книги в указанную ячейку.
Вывести список листов в ячейку можно следующим способом:
- Перейти на вкладку VBA-Excel (доступна после установки).
- В меню Диспетчеры выбрать пункт Диспетчер листов.
- Выбрать команду Список листов
- В диалоговом окне указать ячейку, в которую будет выводиться список листов. Вы можете вывести как все листы книги, так и те, которые вы выбрали в диспетчере листов.
- Нажать кнопку Создать список
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Как получить список листов в книге excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |