Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение – просто выбрать лист из списка (рис. 1). Для этого кликните правой кнопкой мыши в левом нижнем углу экрана на одной из кнопок прокрутки.
Рис. 1. Выбор листа из списка
Скачать заметку в формате Word или pdf, примеры в формате Excel2013 (с макросами)
Однако, если у вас десятки (а то и сотни) листов, а также, если вы хотите найти лист с определенным названием, такой метод вряд ли будет хорош. Создайте отдельную страницу в книге, на которой разместите указатель, связав гиперссылкой каждую строку с соответствующим листом. Вы сможете создать фильтр или находить листы контекстным поиском. Указатель можно создать двумя способами. [1]
Возможно, вам покажется привлекательной идея создать указатель вручную. Создайте новый лист, назовите его Указатель или подобным именем, введите имена всех остальных листов и свяжите записи гиперссылками с соответствующими листами, пройдя по меню Вставка → Гиперссылка (рис. 2) или нажав сочетание клавиш Ctrl+K (К английское). Этот способ подойдет для книг с небольшим числом листов, которые будут меняться нечасто. Но даже в этом случае может быть затруднительно поддерживать актуальность указателя.
Рис. 2. Можно создать указатель вручную с помощью гиперссылок
Более универсальный, но и более сложный путь – добавить код VBA. В этом случае указатель будет создаваться заново каждый раз при переходе на лист с указателем (к сожалению, это может замедлять работу, если листов много).
1. Сохраните вашу книгу как файл с поддержкой макросов (рис. 3)
Рис. 3. Книга Excel с поддержкой макросов
2. Вставьте в вашу книгу новый лист и назовите его удобным именем — например, Указатель.
3. Правой кнопкой мыши щелкните ярлычок листа и в контекстном меню выберите команду Исходный текст (рис. 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. Нажмите Alt+Q, чтобы вернуться обратно к рабочей книге, и сохраните изменения. Не пугайтесь, что на листе Указатель ничего не произошло, и что вы не увидите ни одного макроса, если пройдете по меню Вид → Макросы. Код устроен таким образом, что его нельзя запустить никаким образом, кроме как перейдя на лист Указатель. Поэтому перейдите на любой лист, а затем вернитесь на лист Указатель. Наблюдайте, как работает макрос!
Обратите внимание, что ячейкам А1 на каждом листе присваивается имя Start#, где # – уникальное целое число, представляющее номер листа в указателе. Это гарантирует, что у ячейки А1 каждого листа будет уникальное имя. Если у ячейки А1 на вашем листе уже есть имя, необходимо каждое упоминание ячейки А1 в коде изменить на что-то более подходящее — например, любую неиспользуемую ячейку листа. Увидеть образовавшийся список имен можно, пройдя по меню Формулы → Диспетчер имен (рис. 6).
Рис. 6. Диспетчер имен
Дополнение от 23 сентября 2016 г.
Еще один способ создания указателя использует макрофункцию в именованной формуле (подробнее см. Функция Получить.Ячейку). [2] Откройте книгу, в которой много рабочих листов и/или листов с диаграммами, и выполните следующее:
- Вставьте новый лист, на котором будет находиться список (назовите его, например, Указатель).
- Выполните команду Формулы –> Присвоить имя, чтобы открыть диалоговое окно Создание имени.
- Впишите в поле Имя название Список.
- Введите следующую формулу в поле Диапазон (рис. 7): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)
- Нажмите ОК, чтобы закрыть диалоговое окно Создание имени.
Рис. 7. Создание имени для именованной формулы
Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа.
Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Слисок;СТРОКА()). На рис. 8 приведена данная формула для диапазона А1:А24. В книге 21 лист, поэтому формула возвращает ошибку #ССЫЛКА! при попытке отобразить имя несуществующего листа. Чтобы избежать этого, измените формулу следующим образом: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА()));»»)
Рис. 8. Использование формулы для отображения списка имен листов
Список названий листов можно откорректировать, если вы переименовываете, добавляете или удаляете листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов обновлялись автоматически, измените формулу, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»; ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()). Функия Т проверяет, является ли значение текстовым, и возвращает сам текст, если да, либо две кавычки «» (пустой текст), если нет.
Зачем может понадобиться список названий листов? На рис. 9 показано оглавление, созданное как указано выше, а таже ссылки на ячейку А1 соответствующего листа. Например, в ячейке В2 записана формула =ГИПЕРССЫЛКА(«#»&A2&»!A1″;»Перейти по ссылке»). Щелкнув на гиперссылке, вы активизируете лист и выделяете ячейку А1. К сожалению, Excel не поддерживает гиперссылки, ведущие на лист с диаграммами, поэтому при попытке сослаться на такой лист программа выдаст ошибку.
Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm).
Рис. 9. Создание списка гиперссылок
[1] По материалам книги Р.Холи, Д.Холи. Excel. Трюки, стр. 38–40
[2] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 46–48.
Благодарю в очередной раз. Обязательно подберу себе вариант из предложенных в статье.
Спасибо за описание. Но вот проблемка возникла, после ввода кода 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?
Спасибо.