Оглавление книги 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
Просто, если я удаляю какие-нибудь листы, при повторном запуске остаются "хвосты".
Worksheets("Оглавление").Range("A1:A1000").ClearНапример, ячейку А5 переименовать с "Отгрузки" на "Приход" и чтоб при этом автоматически изменилось название листа "Отгрузки" на "Приход"?
Щелкните правой кнопкой мыши по ярлычку листа с оглавлением - Исходный текст и вставьте туда вот такой код:
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Worksheets(Target.Row).Name = Target.Value Hyperlinks.Add anchor:=Target, Address:="", _ SubAddress:="'" & Target.Value & "'" & "!A1" End SubSub Переход_в_Меню() Worksheets("Меню").Select End Subпри создании макроса выпадает ошибка
.Не понимаю, что исправить в первой строке надо!?
Подскажите.
Worksheets("Оглавление").Range("A1:A1000").ClearЗатем второй макрос ниже на событие изменения текста в ячейке (которое меняет наименование листа)
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub Worksheets(Target.Row).Name = Target. Value Hyperlinks.Add anchor:=Target, Address:="", _ SubAddress:="'" & Target. Value & "'" & "!A1" End SubЗатем добавим макрос, чтобы не только ячейка меняла название листа, но и смена названия листа меняла текст в ячейке (ActiveSheet.Cells.ClearContents лишнее, если выше добавили строку)
Private Sub Worksheet_Activate() Dim iList ActiveSheet.Cells.ClearContents For Each iList In ActiveWorkbook.Worksheets With Worksheets(1).Cells(iList.Index, 1) .Hyperlinks.Add Anchor:=.Item(1), Address:="", _ SubAddress:="'" & iList.Name & "'" & "!A1" .Formula = iList.Name End With Next Sheets("Inhalt").Select End SubВопрос)) почему этак конструкция не работает, помогите плиз. Суть в том, что вы посоветовали как по ячейке листа название актуализировать, а чтобы работало сразу и в обратную сторону?
PS: При этом обязательно с очисткой от мусора) т.е. как эти три модуля по очистке, переносу значения ячейки в название листа и третьего: обратно из листа в ячейку.. объединить..
Еще идея, сделать так, чтобы, если ввожу в ячейку данные, а такого листа нет - он создавался.
Что-то вроде
Private Sub Worksheet_Change(ByVal T As Range) Set T = Intersect(T, Range("A1:A100")) 'çäåñü àäðåñ ÿ÷åéêè If T Is Nothing Then Exit Sub Worksheets(T.Row).Name = T. Value If Not T Is Nothing Then Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = T. Value Sheets(1).Activate ActiveSheet.Hyperlinks.Add Anchor:=T, Address:="", SubAddress:=Target. Value & "!A1" End If End Subsheet.Hyperlinks.Add Anchor:=sheet.Range("A1"), Address:="", SubAddress:= _ "Оглавление!A1", TextToDisplay:="Оглавление"А по делу один вопрос.
Как изменить код, чтобы в оглавление выводились только НЕ скрытые обычным способом листы. А то у меня 15 скрытых листов всякой справочной информации, разных шаблонов и т.д. И я не хотел бы, чтобы регистратор "по ним портил".
Благодарна буду за любой из двух вариантов:)
=ГИПЕРССЫЛКА("[Hiperlink.xlsx]"&АДРЕС(ПОИСКПОЗ(A7;Лист2!A:A;0);1;4;;"Лист2");A7)Private Sub Worksheet_Activate() ' макрос создания оглавления НЕ скрытых листов на листе Worksheets("Лист1").Range("A1:A100").Clear 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 Next End With Worksheets("Лист1").Range("A1:A1").Clear End SubСмайлы, конечно, того... парят.
Николай, спасибо большое за Ваш макрос.
Есть косяк, не знаю, как исправить. По гиперссылке "Назад" открывается тот же самый файл еще раз вместо перехода на главный лист в этом же файле. 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" и чтоб ячейки с комментариями тож перемещались вместе с ячейками
я пробовал записать отдельный макрос на сортировку ячеек, он как бы один раз срабатывает нормально, а при повторном запуске мешает все ячейки в столбце В:В.
Формула должна быть такая:
=ГИПЕРССЫЛКА("#'"&A2&"'!A1";">>>")Я переделал формулу чтобы текст ссылки был непосредственно именем листа, а не >>>>
=ГИПЕРССЫЛКА("#'"&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА())&"'";"")&"!A1"; ""&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА());""))Хороший прием, но пользовался, им через гиперссылки, спасибо вам.
Небольшое дополнение, всегда есть необходимость попасть из конкретного листа в оглавление, а потом перейти в любой лист, было бы хорошо, если бы вы дозаписали свой клип и учли бы это. Спасибо.
Все дело оказалось в имени листа!
Как теперь сделать так, чтоб имя не влияло на ссылку?
Предполагаю, что у вас могут быть пробелы в именах листов. Тогда имя листа во всех формулах нужно дополнительно заключить в апострофы.
Я использую второй способ. Мне он кажется самым изящным. ))
Нигде не смогла найти ответ на свой вопрос, возможно, вы могли бы мне что-то посоветовать.
У меня файл, 500 листов. Все имеют название из 2-х слов с пробелом, типа "SUN 125", "FOE FV" и т.д.
Чтобы моя формула по сводке данных в одну таблицу со всех листов заработала, нельзя чтобы в названии листов были пробелы.
Возможно ли изменить какой-либо формулой или с помощью макроса одним махом - все пробелы заменить на _ (нижнее подчеркивание), к примеру.
=ГИПЕРССЫЛКА("#"&ТЕКСТ(AF27;"ДД.ММ.ГГГГ")&"!A1";">>>")Очень полезная статья. Спасибо!
Попробовал на основе этого оглавления посчитать кол-во заполненных ячеек в первом столбце каждого листа с помощью формулы, но почему-то происходит ошибка формулы
=СЧЁТЗ("#"&A3&"!A:A)Хочется растянуть формулу и вуаля, вместо работы руками...