Оглавление книги Excel
Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:
Есть несколько способов реализовать подобное.
Видео
Способ 1. Создаваемые вручную гиперссылки
Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка - Гиперссылка (Insert - Hyperlink). В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод - функцию ГИПЕРССЫЛКА (HYPERLINK). Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:
=ГИПЕРССЫЛКА("#Оглавление!A1";"Назад в оглавление")
Эта функция создаст в текущей ячейке на всех выделенных листах гиперссылку с текстом "Назад в оглавление", щелчок по которой будет возвращать пользователя к листу Оглавление.
Способ 2. Динамическое оглавление с помощью формул
Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK), оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.
Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:
=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
=GET.WORKBOOK(1)
Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:
=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())
Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.
Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND), которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку (""). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager), двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")
Теперь наш список листов будет выглядеть существенно лучше:
Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой "хвост":
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА())
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Функция ТДАТА (NOW) выдает текущую дату (с временем), а функция Т превращает эту дату в пустую текстовую строку, которая затем приклеивается к нашему имени листа с помощью оператора склейки (&). Т.е. имя листа, фактически, не меняется, но поскольку функция ТДАТА пересчитывается и выдает новое время и дату при любом изменении листа, то и остальная часть нашей формулы вынуждена будет заново пересчитаться тоже и – как следствие – имена листов будут обновляться постоянно.
Для скрытия ошибок #ССЫЛКА (#REF), которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR), которая перехватывает любые ошибки и заменяет их на пустую строку (""):
И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (HYPERLINK), которая будет формировать адрес для перехода из имени листа:
Способ 3. Макрос
И, наконец, для создания оглавления можно использовать и несложный макрос. Правда, запускать его придется каждый раз при изменении структуры книги - в отличие от Способа 2, макрос их сам не отслеживает.
Откройте редактор Visual Basic, нажав Alt+F11 или выбрав (в старых версиях Excel) в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor). В открывшемся окне редактора создайте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этого макроса:
Sub SheetList() Dim sheet As Worksheet Dim cell As Range With ActiveWorkbook For Each sheet In ActiveWorkbook.Worksheets Set cell = Worksheets(1).Cells(sheet.Index, 1) .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1" cell.Formula = sheet.Name Next End With End Sub
Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню Сервис - Макрос - Макросы (Tools - Macro - Macros). Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление, как это было описано в Способе 1.
Ссылки по теме
- Что такое макрос, как его создать, куда копировать текст макроса, как запустить макрос?
- Автоматическое создание оглавления книги одной кнопкой (надстройка PLEX)
- Отправка писем с помощью функции ГИПЕРССЫЛКА
- Быстрый переход между листами книги Excel
Просто, если я удаляю какие-нибудь листы, при повторном запуске остаются "хвосты".
Например, ячейку А5 переименовать с "Отгрузки" на "Приход" и чтоб при этом автоматически изменилось название листа "Отгрузки" на "Приход"?
Щелкните правой кнопкой мыши по ярлычку листа с оглавлением - Исходный текст и вставьте туда вот такой код:
при создании макроса выпадает ошибка
Подскажите.
Затем второй макрос ниже на событие изменения текста в ячейке (которое меняет наименование листа)
Затем добавим макрос, чтобы не только ячейка меняла название листа, но и смена названия листа меняла текст в ячейке (ActiveSheet.Cells.ClearContents лишнее, если выше добавили строку)
Вопрос)) почему этак конструкция не работает, помогите плиз. Суть в том, что вы посоветовали как по ячейке листа название актуализировать, а чтобы работало сразу и в обратную сторону?
PS: При этом обязательно с очисткой от мусора) т.е. как эти три модуля по очистке, переносу значения ячейки в название листа и третьего: обратно из листа в ячейку.. объединить..
Еще идея, сделать так, чтобы, если ввожу в ячейку данные, а такого листа нет - он создавался.
Что-то вроде
А по делу один вопрос.
Как изменить код, чтобы в оглавление выводились только НЕ скрытые обычным способом листы. А то у меня 15 скрытых листов всякой справочной информации, разных шаблонов и т.д. И я не хотел бы, чтобы регистратор "по ним портил".
Благодарна буду за любой из двух вариантов:)
Смайлы, конечно, того... парят.
Николай, спасибо большое за Ваш макрос.
Есть косяк, не знаю, как исправить. По гиперссылке "Назад" открывается тот же самый файл еще раз вместо перехода на главный лист в этом же файле. ME 2010.
Заранее спасибо.
Разобрался в чем проблема. Файл сохранил в формате xlsm. Поменял в ссылке расширение и все заработало. Спасибо, полезная фишка.
задав в ней смещение на две строки вниз и на 3 столбца вправо
Можно ли Ваш макрос изменить так, чтобы в оглавлении были ссылки на А1 каждого из листов (а не на название листа), а в тексте ссылки было значение из А1 данного листа?
на cell.Formula = sheet.Cells(1, 1).Value
Теперь нет ограничения в 31 знак для длины записей (по содержимому А1 каждого не скрытого листа) в активном перечне, формируемом макросом.
Как это сделать в MS Excel 2010? Следую по-шагово как написано: (Tools - Macro - Visual Basic Editor). В открывшемся окне редактора создайте новый пустой модуль (меню Insert - Module), в открывшимся окне не вижу меню (Insert - Module) [img]C:\Documents and Settings\Казбек\Мои документы\Мои рисунки\Screen.jpg[/img]
Private Sub Worksheet_Activate() ' макрос создания оглавления НЕ скрытых листов на листе
Worksheets("Лист1"
Dim sheet As Worksheet
Dim cell As Range
s = 1
With ActiveWorkbook
For Each sheet In ActiveWorkbook.Worksheets
If sheet.Visible = True Then
Set cell = Worksheets(1).Cells(s, 1)
s = s + 1
.Worksheets(1).Hyperlinks.Add Anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!A1"
cell.Formula = sheet.Name
End If
sheet.Hyperlinks.Add Anchor:=sheet.Range("K1"
"Лист1!K1", TextToDisplay:="Лист1"
Next
End With
Worksheets("Лист1"
End Sub
Скрипт значительно облегчил жизнь. Спасибо большое!
Николай, а можете подсказать как можно сделать:
1) чтобы макрос всё-таки не выводил в формируемом списке название первого листа ("Оглавление"),
2) чтобы названия листов в итоговом списке выводились в алфавитном порядке?
Заранее признателен.
К сожалению, не работает самый первый макрос описанный вами. Делаю всё по инструкции, номера строк не вставляю как делают некоторые. Что-то происходит, но результатов никаких нет. То есть пустой лист как был пустой так и остался.
С уважением, Михаил.
Отличный способ, но возникла проблема. не происходит переход на диаграммы. Можно ли это сделать?
- у меня листы с названиями идут так: - "Главная", "999", "998", "997"...."1"
- список с названиями на листе "Главная" создается с ячейки А1 и вниз "999", "998", "997"...."1"
- список с коментариями или ещё с какими то заметками на листе "Главная" начинается с ячейки В1 и вниз
- мне нужно чтоб список создавался: "1" ,"2", "3" ...."999" и чтоб ячейки с комментариями тож перемещались вместе с ячейками
я пробовал записать отдельный макрос на сортировку ячеек, он как бы один раз срабатывает нормально, а при повторном запуске мешает все ячейки в столбце В:В.
Формула должна быть такая:
Я переделал формулу чтобы текст ссылки был непосредственно именем листа, а не >>>>
Хороший прием, но пользовался, им через гиперссылки, спасибо вам.
Небольшое дополнение, всегда есть необходимость попасть из конкретного листа в оглавление, а потом перейти в любой лист, было бы хорошо, если бы вы дозаписали свой клип и учли бы это. Спасибо.
Все дело оказалось в имени листа!
Как теперь сделать так, чтоб имя не влияло на ссылку?
Предполагаю, что у вас могут быть пробелы в именах листов. Тогда имя листа во всех формулах нужно дополнительно заключить в апострофы.
Я использую второй способ. Мне он кажется самым изящным. ))
Нигде не смогла найти ответ на свой вопрос, возможно, вы могли бы мне что-то посоветовать.
У меня файл, 500 листов. Все имеют название из 2-х слов с пробелом, типа "SUN 125", "FOE FV" и т.д.
Чтобы моя формула по сводке данных в одну таблицу со всех листов заработала, нельзя чтобы в названии листов были пробелы.
Возможно ли изменить какой-либо формулой или с помощью макроса одним махом - все пробелы заменить на _ (нижнее подчеркивание), к примеру.