Excel. Создание указателя листов в книге

Рубрика: 7. Полезняшки Excel

Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение – просто выбрать лист из списка (рис. 1). Для этого кликните правой кнопкой мыши в левом нижнем углу экрана на одной из кнопок прокрутки.

Рис. 1. Выбор листа из списка

Рис. 1. Выбор листа из списка

Скачать заметку в формате Word или pdf, примеры в формате Excel2013 (с макросами)

Однако, если у вас десятки (а то и сотни) листов, а также, если вы хотите найти лист с определенным названием, такой метод вряд ли будет хорош. Создайте отдельную страницу в книге, на которой разместите указатель, связав гиперссылкой каждую строку с соответствующим листом. Вы сможете создать фильтр или находить листы контекстным поиском. Указатель можно создать двумя способами. [1]

Возможно, вам покажется привлекательной идея создать указатель вручную. Создайте новый лист, назовите его Указатель или подобным именем, введите имена всех остальных листов и свяжите записи гиперссылками с соответствующими листами, пройдя по меню ВставкаГиперссылка (рис. 2) или нажав сочетание клавиш Ctrl+K (К английское). Этот способ подойдет для книг с небольшим числом листов, которые будут меняться нечасто. Но даже в этом случае может быть затруднительно поддерживать актуальность указателя.

Рис. 2. Можно создать указатель вручную с помощью гиперссылок

Рис. 2. Можно создать указатель вручную с помощью гиперссылок

Более универсальный, но и более сложный путь – добавить код VBA. В этом случае указатель будет создаваться заново каждый раз при переходе на лист с указателем (к сожалению, это может замедлять работу, если листов много).

1. Сохраните вашу книгу как файл с поддержкой макросов (рис. 3)

Рис. 3. Книга Excel с поддержкой макросов

Рис. 3. Книга Excel с поддержкой макросов

2. Вставьте в вашу книгу новый лист и назовите его удобным именем — например, Указатель.

3. Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (рис. 4).

Рис. 4. Исходный текст

Рис. 4. Исходный текст

4. В открывшемся окне введите код VВА (рис. 5). Во избежания ошибок, рекомендую код скопировать из модуля в файле Excel.

Private Sub Worksheet_Activate()

Dim wSheet As Worksheet

Dim l As Long

l = 1

With Me

.Columns(1).ClearContents

.Cells(l, 1) = "INDEX"

.Cells(l, 1).Name = "INDEX"

End With

For Each wSheet In Worksheets

If wSheet.Name <> Me.Name Then

l = l + 1

With wSheet

.Range("A1").Name = "Start" & wSheet.Index

.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _

"Index", TextToDisplay:="Назад к указателю"

End With

Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _

SubAddress:="Start" & wSheet.Index, TextToDisplay:=wSheet.Name

End If

Next wSheet

End Sub

Рис. 5. Код VBA

Рис. 5. Код VBA

5. Нажмите Alt+Q, чтобы вернуться обратно к рабочей книге, и сохраните изменения. Не пугайтесь, что на листе Указатель ничего не произошло, и что вы не увидите ни одного макроса, если пройдете по меню ВидМакросы. Код устроен таким образом, что его нельзя запустить никаким образом, кроме как перейдя на лист Указатель. Поэтому перейдите на любой лист, а затем вернитесь на лист Указатель. Наблюдайте, как работает макрос!

Обратите внимание, что ячейкам А1 на каждом листе присваивается имя Start#, где # – уникальное целое число, представляющее номер листа в указателе. Это гарантирует, что у ячейки А1 каждого листа будет уникальное имя. Если у ячейки А1 на вашем листе уже есть имя, необходимо каждое упоминание ячейки А1 в коде изменить на что-то более подходящее — например, любую неиспользуемую ячейку листа. Увидеть образовавшийся список имен можно, пройдя по меню ФормулыДиспетчер имен (рис. 6).

Рис. 6. Диспетчер имен

Рис. 6. Диспетчер имен

Дополнение от 23 сентября 2016 г.

Еще один способ создания указателя использует макрофункцию в именованной формуле (подробнее см. Функция Получить.Ячейку). [2] Откройте книгу, в которой много рабочих листов и/или листов с диаграммами, и выполните следующее:

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

%d1%80%d0%b8%d1%81-7-%d1%81%d0%be%d0%b7%d0%b4%d0%b0%d0%bd%d0%b8%d0%b5-%d0%b8%d0%bc%d0%b5%d0%bd%d0%b8-%d0%b4%d0%bb%d1%8f-%d0%b8%d0%bc%d0%b5%d0%bd%d0%be%d0%b2%d0%b0%d0%bd%d0%bd%d0%be%d0%b9-%d1%84

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

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

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

%d1%80%d0%b8%d1%81-8-%d0%b8%d1%81%d0%bf%d0%be%d0%bb%d1%8c%d0%b7%d0%be%d0%b2%d0%b0%d0%bd%d0%b8%d0%b5-%d1%84%d0%be%d1%80%d0%bc%d1%83%d0%bb%d1%8b-%d0%b4%d0%bb%d1%8f-%d0%be%d1%82%d0%be%d0%b1%d1%80%d0%b0

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

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

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

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

%d1%80%d0%b8%d1%81-9-%d1%81%d0%be%d0%b7%d0%b4%d0%b0%d0%bd%d0%b8%d0%b5-%d1%81%d0%bf%d0%b8%d1%81%d0%ba%d0%b0-%d0%b3%d0%b8%d0%bf%d0%b5%d1%80%d1%81%d1%81%d1%8b%d0%bb%d0%be%d0%ba

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


[1] По материалам книги Р.Холи, Д.Холи. Excel. Трюки, стр. 38–40

[2] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 46–48.

Комментарии: 12 комментариев

Благодарю в очередной раз. Обязательно подберу себе вариант из предложенных в статье.

Спасибо за описание. Но вот проблемка возникла, после ввода кода VBA после перехода на лист и обратно на указатель, выдает ошибку, вернее красным выделяет:

.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
"Index", TextToDisplay:="Назад к указателю"

Моих знаний в чем проблема не хватает, подскажите что не так сделал?

Евгений, я проверил, и у меня не возникает такой ошибки. Пришлите файл в личку, попробую помочь.

У вас в приведенном здесь коде полная беда с кавычками. Сравните код на вставленном скриншоте и код, который в тексте.
Вот поправленный:

Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long
l = 1
With Me
.Columns(1).ClearContents
.Cells(l, 1) = «Index»
.Cells(l, 1).Name = «Index»
End With

For Each wSheet In Worksheets
If wSheet.Name Me.Name Then
l = l + 1
With wSheet
.Range(«A1»).Name = «Start» & wSheet.Index
.Hyperlinks.Add Anchor:=.Range(«A1″), Address:=»», SubAddress:= _
«Index», TextToDisplay:=»Назад к указателю»
End With
Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:=»», _
SubAddress:=»Start» & wSheet.Index, TextToDisplay:=wSheet.Name
End If
Next wSheet
End Sub

Скопировал поправленный, и тут же увидел как он вставился. После копирования вручную поменяйте все кавычки на «.

Тьфу на вашу форму комментариев…

Егор, это не моя форма комментариев))) Это WordPress — движок, на котором работает мой сайт — так обрабатывает кавычки. Спасибо за замечание, в тексте я поправил, а в вашем комментарии не стал)). Я некоторое время назад научился с этим бороться, см. Кавычки елочки: как заставить WordPress отображать обычные кавычки там, где вы этого хотите

Спасибо огромное и за подсказку и за указание первоисточников , очень помогло.

Добрый день! Спасибо Вам за макрос! У меня вот какой вопрос. Моя книга состоит как из листов, так и из диаграмм. Указатель внес в список только листы. Диаграммы — нет. Есть ли возможность через макрос также создать общий указатель из листов и диаграмм? Или вручную только? Спасибо заранее!

Екатерина, макрос устроен таким образом, что в процессе работы он на каждом листе в ячейке А1 создает запись «Назад к указателю», а также присваивает ячейке А1 имя Start#, где # – уникальное целое число, представляющее номер листа в указателе. Таким образом, листы, на которых нет ячейки А1, не могут быть обработаны. К сожалению, к таким листам относятся все листы с типом «Диаграмма»… Поищите в инете макрос, основанный на другом принципе.

Спасибо за ответ!

Есть программа на VBA которая одиноково обрабатывает листы Excel.
Почему не работает конструкция Sheets(S)…….?
Как задать адресацию листов из VBA?
Спасибо.


Прокомментировать