Как сделать список листов в Excel?

Поговорим о различных способах создания списка листов книги в Excel для составления оглавления всей книги и удобной навигации между листами.


Приветствую всех, дорогие читатели блога TutorExcel.Ru.

Причин составить список всех листов книги в Excel может быть превеликое множество — составление оглавления, навигация и т.д.
Для небольшого количества листов эту задачу можно сделать вручную, просто руками скопировав все названия.
Однако при изменении имени листа (или их очередности в книге) придется по новой переписывать названия или изменять порядок.
К тому же, если в книге насчитывается не один или два, а уже несколько десятков листов, то с этой задачей будет справиться еще сложнее.

Поэтому рассмотрим 2 варианта создания списка, которые помогут нам автоматизировать процесс:

  • Формула. С помощью стандартных функций получим имена всех листов книги;
  • Пользовательская функция (UDF). Аналогичный способ реализованный посредством VBA.

Способ 1. Формула

Воспользуемся свойствами функции ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (в английском варианте GET.WORKBOOK), которая позволит нам получить список всех листов текущей книги.
Для начала перейдем в Диспетчер имен — переходим в панели вкладок на вкладку Формулы в раздел Определенные имена и добавляем новое имя (к примеру, Список_листов) в качестве диапазона которого прописываем следующую формулу:

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")

Применение диспетчера имен
Формально в качестве формулы можно было прописать только =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1), однако в этом случае в названиях листов будет также содержаться названия книги (например, [Книга1.xlsb]Лист1).
Удаляем все до символа правой закрывающейся квадратной скобки ], в итоге мы оставляем только имя листа (Лист1), и чтобы не делать это формульно каждый раз при обращении к отдельным элементам переменной Список_листов, сделаем это один раз сразу для всех элементов (как в примере выше).

Таким образом, в новой созданной переменной Список_листов содержатся название всех листов текущей книги, своего рода массив с данными, теперь же нам нужно извлечь их оттуда.
Для работы с массивом воспользуемся функцией ИНДЕКС (в английской версии INDEX), которая по порядковому номеру вытаскивает элемент массива, и функцией СТРОКА (английский вариант ROW) для создания простой нумерации:

Способ 1
Далее для удобства навигации с помощью функция ГИПЕРССЫЛКА (в английской версии HYPERLINK) добавим гиперссылки к названиям листов:

Способ 1. Добавление гиперссылки
В результате каждая гиперссылка будет вести на ячейку A1 соответствующего имени листа.
Переходим к следующему способу.

Способ 2. Пользовательская функция (UDF)

Чтобы каждый раз не вспоминать алгоритм извлечения имени листа, реализуем пользовательскую функцию с идентичным функционалом.
Все стандартно: переходим в редактор Visual Basic (сочетание клавиш Alt + F11), создаем новый модуль и копируем туда текст функции:

Алгоритм данной пользовательской функций простой — она возвращает имя N-го по порядку листа (где N — аргумент функции).

Теперь возвращаемся в Excel и пробуем новой функцией SheetList получить список листов книги (так же как и в предыдущем способе воспользуемся функцией СТРОКА для создания простой нумерации):

Способ 2
Повторяем операцию по добавлению гиперссылок для более удобной навигации:

Способ 2. Добавление гиперссылки

Все готово.
Скачать файл с примером.

Спасибо за внимание!
Если у вас есть мысли или вопросы по теме статьи — делитесь ими в комментариях.

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:
Поиск по сайту:

Добавить комментарий

Ваш e-mail не будет опубликован.

Читайте ранее:
Координатное выделение ячейки в Excel

Рассмотрим различные способы координатного выделения ячейки в Excel: с применением макросов и с помощью условного форматирования.

Закрыть