Как сделать список листов в 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!

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

Комментарии (11)

  1. Дмитрий

    Функцию ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ не нашёл в 2007-м Экселе, подавно её нет и в 2003-м

    1. tutorexcel Автор поста

      Эта функция не является стандартной, поэтому ее не получится найти через мастер функций.

  2. ZorK71

    Надо получить из файла с большим кол-вом листов, таблицу из двух колонок:
    1. Имя листа;
    2. содержимое ячейки A9 каждого листа (это текст, название организации).

    Кто может подсказать как?
    Можно в отдельном файле, можно в том же на новый лист.
    Или направьте в какой теме спросить…

    Спасибо.

    1. tutorexcel Автор поста

      Имя листа можно получить как описано в примерах выше, а чтобы получить содержимое определенной ячейки каждого листа, то можно воспользоваться функцией ДВССЫЛ — она превратит текст в ссылку.
      Например, если в ячейке A1 записано имя листа, то =ДВССЫЛ(A1&"!A9") вернет значение ячейки A9 с листа из ячейки A1.

  3. ZorK71

    А можно доработать функцию в этом файле что бы она опрашивала не в открытой книге, а брала листы из другого файла?

    1. tutorexcel Автор поста

      Да, попробуйте вместо атрибута ActiveWorkbook (т.е. текущая активная книга) использовать Workbooks(«Книга.xlsx»), где Книга.xlsx — другой открытый файл.

      1. ZorK71

        Спасибо!
        Получилось.

        Я не подскажете, как в текущем каталоге получить список файлов по шаблону и потом уже получить список из двух колонок?
        Типо:
        Книга1; Лист1
        Книга1; Лист2
        Книга1; Лист3
        Книга2; Лист1
        Книга2; Лист2
        Книга3; Лист1
        и т.д.

        Спасибо.

        1. tutorexcel Автор поста

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

  4. Ta6ypeTka

    Такой вопрос:

    1) Вот, я использую get.workbook. У меня есть лист, где будет этот список. И мне надо, чтобы название самого этого листа не показывалось (на котором будет этот список. (если этого нельзя сделать без макроса, нет проблем, пуать будет макрос)

    2) Лучше, если этот список вынести на отдельный лист (т.к. на том, первом, будет стягиваться дата с остальных листов), и чтобы список высвечивал все листы, кроме этих двух, и чтобы ячейки не были пустыми (просто if()=»…»;»»;»…») не вариант).

    3) В идеале, сделать этот список автообновляющимся.

    Дополнительно:

    Есть очень необычная задачка. Связана с этим же файлом. Нужно, чтобы, при открытии файла высвечивалось окошко, куда нужно ввести свое имя (файл будет предназначен для нескольких человек) и, в зависимости от введенного, открывается только один лист с соответствующим названием. Если такого нет, он создается, если есть, открывается имеющийся (наверное, достаточно просто будет спрятать остальные листы). При этом, при вводе определенного имени (меня, автора файла), открываются нормально все листы.

    1. Ta6ypeTka

      Дополнение к последнему:

      В случае создания нового листа, нужно, чтобы копировалась часть шапки первого листа.

  5. Ta6ypeTka

    Решение на первых 2 вопроса нашел. Надо просто добавить +2 после row(). Все гениальное просто:)

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

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