Обычно мы можем легко находить / искать ячейки с помощью функции поиска и замены. Но иногда нам нужно искать по имени листа. Например, на панели вкладок листа сжимаются десятки листов, и нам нужно быстро найти рабочий лист и перейти к нему. Так как? В этой статье мы обсудим оба метода, чтобы легко решить эту проблему.
- Поиск по имени рабочего листа с VBA
- Поиск по имени рабочего листа с помощью «Создать список имен листов» Kutools for Excel (5 шаги)
- Поиск по имени рабочего листа с помощью панели навигации Kutools for Excel (2 шаги)
Поиск по имени рабочего листа с VBA
Этот метод представит макрос VBA для простого поиска на листе в активной книге.
1, нажмите другой + F11 одновременно клавиши, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, а затем вставьте следующий код VBA в новое открывающееся окно модуля.
VBA: поиск по имени листа
Sub SearchSheetName()
Dim xName As String
Dim xFound As Boolean
xName = InputBox("Enter sheet name to find in workbook:", "Sheet search")
If xName = "" Then Exit Sub
On Error Resume Next
ActiveWorkbook.Sheets(xName).Select
xFound = (Err = 0)
On Error GoTo 0
If xFound Then
MsgBox "Sheet '" & xName & "' has been found and selected!"
Else
MsgBox "The sheet '" & xName & "' could not be found in this workbook!"
End If
End Sub3, нажмите F5 ключ или щелкните Run кнопку для запуска этого VBA.
4. Появится диалоговое окно поиска листа, введите имя листа, на котором вы будете искать, и нажмите кнопку OK кнопка. Смотрите скриншот:
Появится другое диалоговое окно, в котором сообщается, найден рабочий лист или нет. Просто закрой это.
Примечание: Этот VBA требует точно совпадающего имени рабочего листа. Только вы вводите точно полное имя рабочего листа, вы можете узнать этот рабочий лист с помощью этого VBA.
Поиск по имени рабочего листа с помощью «Создать список имен листов» Kutools for Excel
В приведенном выше VBA может быть непросто найти рабочий лист, если вы не можете точно вспомнить и ввести имя рабочего листа. Этот метод познакомит Kutools for ExcelАвтора Создать список имен листов утилита для поиска и простого перехода к рабочему листу.
1. Нажмите Кутулс Плюс > Рабочий лист > Создать список имен листов , чтобы открыть диалоговое окно «Создать список имен листов». Смотрите скриншоты:
2. В открывшемся диалоговом окне Create List of Sheet Names укажите параметры: (1) проверить Содержит список гиперссылок вариант; (2) введите имя для нового созданного индексного листа; (3) указать место нового индексного листа; и (4) перечислите все имена рабочих листов в одном столбце индексного листа.
3, Нажмите Ok кнопка. Теперь создается новый индексный лист, который помещается перед всеми листами.
4. Перейдите к индексной странице и нажмите Ctrl + F одновременно с клавишами, чтобы открыть диалоговое окно «Найти и заменить», введите ключевое слово в поле Найти то, что и нажмите Найти все кнопка. Смотрите скриншот:
Теперь все имена листов, содержащие ключевые слова, найдены и перечислены в нижней части диалогового окна «Найти и заменить». См. Диалоговое окно выше.
5. Щелкните имя найденного рабочего листа в диалоговом окне «Найти и заменить», чтобы выбрать соответствующую ячейку, затем щелкните выбранную ячейку на индексном листе, и он сразу перейдет к соответствующему рабочему листу.
Демо: поиск по имени листа в Excel
Поиск по имени рабочего листа с помощью панели навигации Kutools for Excel
Если у вас есть Kutools for Excel установлен, вы можете легко искать рабочие листы по имени листа в области навигации. Пожалуйста, сделайте следующее:
Kutools for Excel – Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now
1. По умолчанию открывается панель навигации. Если закрыто, нажмите Кутулс > Область переходов чтобы отобразить панель навигации, а затем щелкните 
(Рабочая книга и лист) в левой части области навигации.
2. Вперед, чтобы проверить Фильтр флажок в области навигации и введите указанные ключевые слова в следующее поле. Теперь все листы, имена которых содержат указанные ключевые слова, будут найдены и перечислены в поле ниже. Смотрите скриншот:
Внимание: При нажатии на результаты поиска сразу открывается соответствующий рабочий лист.
Демонстрация: поиск по имени рабочего листа с помощью панели навигации Kutools for Excel
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
- Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
- Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
- Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Комментарии (6)
Оценок пока нет. Оцените первым!
На чтение 6 мин. Просмотров 250 Опубликовано 21.05.2021
Обычно мы можем легко находить/искать ячейки с помощью функции поиска и замены. Но иногда нам нужно искать по имени листа. Например, на панели вкладок листа сжимаются десятки листов, и нам нужно быстро найти рабочий лист и перейти к нему. Так как? В этой статье обсуждаются оба метода, позволяющие легко решить эту проблему.
- Поиск по имени рабочего листа с помощью VBA
- Поиск по имени рабочего листа с помощью Создать список имен листов Kutools for Excel ( 5 шагов )
- Поиск по имени рабочего листа с помощью области навигации Kutools for Excel ( 2 шага )
Содержание
- Быстрый поиск по имени рабочего листа с помощью Kutools for Excel
- Поиск по имени рабочего листа с помощью VBA
- Поиск по имени рабочего листа с помощью Create List of Sheet Names of Kutools for Excel
- Поиск по имени рабочего листа с панелью навигации Kutools for Excel
- Статьи по теме:
Быстрый поиск по имени рабочего листа с помощью Kutools for Excel
Теперь в Kutools for Excel улучшена его панель навигации и поддерживает простой поиск по имени листа, как показано на снимке экрана ниже. 30-дневная бесплатная пробная версия полнофункциональной версии!
Вкладка Office Включает редактирование и просмотр с вкладками в Office и делает вашу работу намного проще …
Подробнее … Скачать бесплатно …
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Повторно использовать что угодно: добавьте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подробнее … Бесплатная загрузка …
->
Поиск по имени рабочего листа с помощью VBA
Этот метод вводит макрос VBA для простого поиска на листе в активной книге.
1 . Одновременно нажмите клавиши Alt + F11 , чтобы открыть окно Microsoft Visual Basic для приложений.
2 . Нажмите Вставить > Module , а затем вставьте следующий код VBA в новое открывающееся окно модуля.
VBA: поиск по имени листа
3 . Нажмите клавишу F5 или кнопку Run , чтобы запустить этот VBA.
4 . Появится диалоговое окно поиска на листе, введите имя листа, на котором вы будете искать, и нажмите кнопку OK . См. Снимок экрана:
Появится другое диалоговое окно и сообщит, есть ли на листе найдено или нет. Просто закройте его.
Примечание. Для этого VBA требуется точно совпадающее имя рабочего листа. Только вы вводите точно полное имя рабочего листа, вы можете узнать этот рабочий лист с помощью этого VBA.
Поиск по имени рабочего листа с помощью Create List of Sheet Names of Kutools for Excel
В приведенном выше VBA может быть непросто найти рабочий лист, если вы не можете точно запомните и введите имя рабочего листа. Этот метод познакомит вас с утилитой Kutools for Excel Create List of Sheet Names для быстрого поиска и перехода к рабочему листу.
Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия!
Kutools for Excel – объединяет более 300 дополнительных функций и инструментов для Microsoft Excel
Перейти к загрузке
Бесплатная пробная версия 60 днейПокупка
PayPal/MyCommerce
->
1 . Нажмите Kutools Plus > Рабочий лист > Создать список имен листов , чтобы открыть диалоговое окно «Создать список имен листов». См. Скриншоты:
2 . В открывшемся диалоговом окне «Создать список имен листов» укажите параметры: (1) установите флажок Содержит список гиперссылок ; (2) введите имя для нового созданного индексного листа; (3) укажите место нового индексного листа; и (4) перечислить все имена рабочих листов в одном столбце на индексном листе.
3 . Нажмите кнопку ОК . Теперь создается новый индексный лист, который помещается перед всеми листами.
4 . Перейдите к индексной странице и одновременно нажмите клавиши Ctrl + F , чтобы открыть диалоговое окно «Найти и заменить», введите ключевое слово в Найдите поле и нажмите кнопку Найти все .. См. Снимок экрана:
Теперь все имена листов, содержащие ключевые слова, найдены и перечислены в нижней части окна поиска. и диалоговое окно “Заменить”. См. Диалоговое окно выше.
5 . Щелкните имя найденного листа в диалоговом окне «Найти и заменить», чтобы выбрать соответствующую ячейку, затем щелкните выбранную ячейку в индексном листе, и он сразу перейдет к соответствующему листу.
Демонстрация: поиск по имени листа в Excel
Kutools for Excel включает более 300 удобных инструментов для Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Загрузить и бесплатную пробную версию !
Поиск по имени рабочего листа с панелью навигации Kutools for Excel
Если у вас установлен Kutools for Excel, вы можете легко искать листы по имени листа в области навигации. Пожалуйста, сделайте следующее:
Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас
1 . По умолчанию открывается панель навигации. Если закрыто, нажмите Kutools > Панель навигации , чтобы отобразить панель навигации, а затем нажмите 
(Рабочая книга и лист) в левой части области навигации.
2 . Установите флажок Фильтр в области навигации и введите указанные ключевые слова в следующее поле. Теперь все листы, имена которых содержат указанные ключевые слова, будут найдены и перечислены в поле ниже. См. Снимок экрана:
Примечание : нажатие результаты поиска перескочат и сразу откроют соответствующий рабочий лист.
Демо: поиск по имени рабочего листа с помощью панели навигации Kutools for Excel
Kutools for Excel включает более 300 удобных инструментов для Excel, которые можно попробовать бесплатно без ограничения в течение 30 дней. Загрузить и бесплатную пробную версию !
Статьи по теме:
Создать гиперссылку в ячейке на другой лист в той же книге
MulTEx »
22 Ноябрь 2015 11601 просмотров
Поиск листов
Данная функция является частью надстройки MulTEx
Вызов команды:
MulTEx -группа Книги/Листы –Листы –Поиск листов
Команда поможет быстро найти листы по их названию. Актуально для книг, листов в которых 15 и более.
Сразу после вызова команды появляется форма с активным полем поиска. Т.е. сразу после вызова можно начать набирать символы, которые содержатся в имени листа и по мере набора в форме будут отображаться все листы, содержащие указанные символы/слова.
После того, как нужные листы найдены и отображены в форме с ними можно проделать следующие действия:
Перейти на лист:
необходимо выбрать лист, на который надо перейти (сделать лист активным). Если выбрано несколько листов – выделен будет только первый выбранный. Для чего это может быть нужно: когда листов в книге много не так-то просто найти именно нужный по имени. Стандартные возможности Excel в этом плане не очень удобны – придется глазами просматривать длинный список из всех листов и искать именно нужный/нужные.
Важно: в форме отображается так же видимость листов. При попытке перейти на скрытый лист MulTEx сообщит, что это невозможно, т.к. лист скрыт. Для начала лист надо будет отобразить, для чего можно воспользоваться командой Изменить видимость листов.
Удалить:
выбираются (проставлением галочек в форме напротив имен нужных листов) листы, которые необходимо удалить. После нажатия Удалить будут удалены все выбранные листы, в том числе скрытые. Важно: при попытке удалить листы из защищенной книги MulTEx выдаст сообщение о том, что книга защищена и защиту необходимо снять (вкладка Рецензирование –Защитить книгу). Если на книгу установлен пароль, но вспомнить его не получается – можно воспользоваться командой Снять защиту с книги(без пароля).
Расскажи друзьям, если статья оказалась полезной:
Видеоинструкции по использованию надстройки MulTEx
|
|||
| misha122062
13.01.10 – 12:34 |
Есть список листов и есть файл Excel, нам надо проверить есть ли указанные листы в файле. |
||
| Rie
1 – 13.01.10 – 12:37 |
(0) Индексом у листа в коллекции может быть не только номер, но и имя листа. |
||
| misha122062
2 – 13.01.10 – 12:44 |
понял |
||
| dk
3 – 13.01.10 – 12:48 |
НашФайл.Sheets(1).Name |
||
| DrZombi
4 – 13.01.10 – 12:49 |
(3)Еще вопрос: А как получить список листов 😉 ? |
||
| AndrewKiev
5 – 13.01.10 – 12:52 |
Эксель = ПолучитьCOMОбъект(Файл); //получим список Листов и добавим их в табчасть “Доступные листы” Для каждого Лист Из СписокЛистов Цикл НоваяСтрока.Лист = Лист.Name; |
||
| AndrewKiev
6 – 13.01.10 – 12:57 |
оно? |
||
| dk
7 – 13.01.10 – 13:10 |
Для Сч = 1 По НашФайл.Sheets.Count Цикл |
||
| DrZombi
8 – 13.01.10 – 13:13 |
(7)Я почему-то решил, что сего объекта нет в екселе 🙁 “НашФайл.Sheets.Count” |
||
| DrZombi
9 – 13.01.10 – 13:13 |
(5)Через ком тоже неплохо :)… Но пока он для меня, что-то далекое и пугающее 🙂 |
||
|
DrZombi 10 – 13.01.10 – 13:14 |
… ну все… пора домой… ужо только на черный текст обращаю внимание 🙁 |
TurboConf — расширение возможностей Конфигуратора 1С
Один посетитель сайта (Валерий) недавно спросил: «Есть ли инструмент поиска листа в книге по его названию». Возможное решение – просто выбрать лист из списка (рис. 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.
