Оглавление книги Excel

3690 14.10.2012 Скачать пример

Если в вашей рабочей книге 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.

Ссылки по теме

Страницы: 1 2

Николай

14.10.2012 23:15:14

Товарищи, а что нужно дописать, чтобы маркос предварительно стер информацию в столбце А, в который он будет добавлять оглавление?
Просто, если я удаляю какие-нибудь листы, при повторном запуске остаются "хвосты".

Ссылка

Николай Павлов

14.10.2012 23:19:44

Добавьте перед строкой с With следующую строку:

Worksheets("Оглавление").Range("A1:A1000").Clear

Родитель Ссылка

Александр Р

04.01.2013 03:50:44

Николай, подскажите пожалуйста, а возможен ли такой вариант в данном примере:
Например, ячейку А5 переименовать с "Отгрузки" на "Приход" и чтоб при этом автоматически изменилось название листа "Отгрузки" на "Приход"?

Родитель Ссылка

Николай Павлов

05.01.2013 14:18:02

Да, в модуль листа нужно будет добавить макрос обработки события изменения текста в ячейке.
Щелкните правой кнопкой мыши по ярлычку листа с оглавлением - Исходный текст и вставьте туда вот такой код:

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

Родитель Ссылка

Sergey_LSA

14.10.2012 23:17:34

Если надо часто переходить на один и тот же лист. Создайте на панели изменяемую кнопку из категории Макросы, прикрепите макрос в котором пропишите название нужного листа. Например надо переходить часто на лист с названием "Меню"

 Sub Переход_в_Меню()
 Worksheets("Меню").Select
 End Sub

Ссылка

Егор Кузнецов

04.08.2021 15:45:22

Блин, что я раньше до этого не додумался! Элементарно, но как удобно!

Родитель Ссылка

F F

28.12.2012 11:02:16

Друзья,
при создании макроса выпадает ошибка
Must be first statement on the line.Не понимаю, что исправить в первой строке надо!?
Подскажите.

Ссылка

Николай Павлов

05.01.2013 14:24:46

Скачайте работающий пример, посмотрите код, сравните со своим - для начала. Если не поможет - пишите сюда или сразу на Форум.

Родитель Ссылка

14.01.2013 14:20:52

Подскажите, а как сделать этот макрос доступным для всех книг ? есть ли у Экселя что-то типа шаблона, как в ворде? У меня Эксель 2003.

Ссылка

Remphan

31.01.2013 02:55:45

Берем макрос из примера. Добавляем

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: При этом обязательно с очисткой от мусора) т.е. как эти три модуля по очистке, переносу значения ячейки в название листа и третьего: обратно из листа в ячейку.. объединить..

Ссылка

Николай Павлов

31.01.2013 10:56:23

Перемудрили. Третий макрос в модуле листа оглавления в вашем случае должен выглядеть так:

Private Sub Worksheet_Activate()
    Application.EnableEvents=false
    Call SheetList
    Application.EnableEvents=true
End Sub

Родитель Ссылка

Remphan

05.02.2013 03:27:27

Извинения за позднее "спасибо".
Еще идея, сделать так, чтобы, если ввожу в ячейку данные, а такого листа нет - он создавался.
Что-то вроде

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 Sub

Фактически ваш скрипт отсюда http://www.planetaexcel.ru/techniques/3/60/#600 немного иной... Вот что не пойму, почему лист не переименовывается, в случае, если такой уже существует а создается новый.. поначалу запарился совсем с vba...

Ссылка

Денис Кожелев

26.10.2017 08:41:33

подскажите как правильно исправить этот макрос чтобы он правильно работал?

Родитель Ссылка

Иван Иванов

24.02.2013 16:35:38

Подскажите код макроса который можно добавить к макросу Sub SheetList , что бы автоматом создавалась кнопка на каждом листе в ячейке A1 с обратной ссылкой на лист Оглавление

Ссылка

Николай Павлов

28.02.2013 10:35:42

Можно, например, такую строку вставить перед Next:

sheet.Hyperlinks.Add Anchor:=sheet.Range("A1"), Address:="", SubAddress:= _
"Оглавление!A1", TextToDisplay:="Оглавление"

Родитель Ссылка

Антон Антоненко

04.03.2013 19:31:13

Уважаемый Николай. Ваш макрос--просто подарок судьбы. Как и весь сайт. Большое Вам спасибо.
А по делу один вопрос.
Как изменить код, чтобы в оглавление выводились только НЕ скрытые обычным способом листы. А то у меня 15 скрытых листов всякой справочной информации, разных шаблонов и т.д. И я не хотел бы, чтобы регистратор "по ним портил".

Ссылка

Николай Павлов

08.03.2013 15:41:07

Надо добавить проверку листа на видимость

Sub SheetList()
   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
End Sub

Родитель Ссылка

анастасия

10.08.2016 20:47:37

Подскажите, пж, как добавить условие в данный код, что выводились в лист оглавления имена-гиперссылки только тех листов, которые содержат в себе выделения цветом (например, Interior.color. зелёным цветом) или если присутствует в листе заранее заданное значение (которое ранее присвоено переменной)
Благодарна буду за любой из двух вариантов:)

Родитель Ссылка

Антон Антоненко

05.03.2013 01:51:14

Чтобы список на листе обновлялся "сам" при каждом открытии листа надо код поставить не в модуль, а на этот лист (там же в VBA). А первую строку заменить на

Private Sub Worksheet_Activate()

Ссылка

Gaidar Gaidar

24.07.2013 12:08:31

Добрый день! Данный макрос: Private Sub Worksheet_Activate() не совсем понятно куда вставлять. Как сделать так, чтобы список обновлялся автоматически, при добавлении нового листа. У меня стоит MS Excel 2010. Использовал волшебный вышестоящий макрос от Николая Павлова.

Родитель Ссылка

Gaidar Gaidar

24.07.2013 13:14:39

Небольшое дополнение, как сделать, чтобы при удалении/добавлении листов, список обновлялся автоматически?

Родитель Ссылка

Dronus

05.03.2013 14:38:47

Хочется поделиться авто оглавлением не для листов, а для ячеек на разных листах. Предположим есть длинная таблица и нам хочется переходить к отдельным главам с помощью Гиперссылки на ячейку, как автоматически прописать Гиперссылки?

=ГИПЕРССЫЛКА("[Hiperlink.xlsx]"&АДРЕС(ПОИСКПОЗ(A7;Лист2!A:A;0);1;4;;"Лист2");A7)

Функция ПОИСКПОЗ передает функции АДРЕС номер строки с найденным словом а АДРЕС передает функции ГИПЕРССЫЛКА ссылку на нужную ячейку. Смотрите пример и подскажите, грамотно ли составил я эту формулу (есть подозрение что можно сделать легче...) Скачать пример

Ссылка

Антон Антоненко

09.03.2013 17:39:56

Немного доработанный макрос от Николая.

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

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

Ссылка

Василий М

12.03.2013 15:15:14

Николай, благодарю за твой титанский труд.
Есть косяк, не знаю, как исправить. По гиперссылке "Назад" открывается тот же самый файл еще раз вместо перехода на главный лист в этом же файле. ME 2010.
Заранее спасибо.

Ссылка

Николай Павлов

13.03.2013 09:09:13

Василий, а гиперссылку Назад как создавали? Макросом? Или формулой ГИПЕРССЫЛКА? Или руками через Вставка - Гиперссылка?

Родитель Ссылка

Василий М

13.03.2013 12:58:57

Руками добавлял на выделенные листы в ячейку А1, =Гиперссылка("[Продажи]Оглавление!А1"; "Back").

Ссылка

Василий М

14.03.2013 15:44:19

Николай, приветствую.
Разобрался в чем проблема. Файл сохранил в формате xlsm. Поменял в ссылке расширение и все заработало. Спасибо, полезная фишка.

Ссылка

Алексей Левыкин

14.03.2013 16:32:51

Доброго времени суток! В продолжение темы хотелось бы спросить, как изменить первую ячейку в оглавлении, допустим на D3. Только учусь, поэтому не судите строго)

Ссылка

Николай Павлов

14.03.2013 21:41:36

Чуть подкорректировать строку:

Set cell = Worksheets(1).Cells(sheet.Index, 1)

задав в ней смещение на две строки вниз и на 3 столбца вправо

Set cell = Worksheets(1).Cells(sheet.Index+2, 4)

Родитель Ссылка

Антон Антоненко

02.04.2013 11:10:19

Николай, Ваши макросы, в частности, макрос создания активного оглавления по названиям листов просто прелесть. Но название листа в Эксель не может быть длиннее 31 знака. А у меня есть необходимость выведения в позиции оглавления данных более длинных. И они есть в А1 каждого листа (это формула сцепления данных из нескольких ячеек).
Можно ли Ваш макрос изменить так, чтобы в оглавлении были ссылки на А1 каждого из листов (а не на название листа), а в тексте ссылки было значение из А1 данного листа?

Ссылка

Антон Антоненко

02.04.2013 11:27:13

PS. ...если А1 не пустая.

Ссылка

Антон Антоненко

02.04.2013 23:28:55

Заменить cell.Formula = sheet.Name
на cell.Formula = sheet.Cells(1, 1).Value
Теперь нет ограничения в 31 знак для длины записей (по содержимому А1 каждого не скрытого листа) в активном перечне, формируемом макросом.

Ссылка

Gaidar Gaidar

19.06.2013 14:16:18

Добрый день,

Как это сделать в MS Excel 2010? Следую по-шагово как написано: (Tools - Macro - Visual Basic Editor). В открывшемся окне редактора создайте новый пустой модуль (меню Insert - Module), в открывшимся окне не вижу меню (Insert - Module) [img]C:\Documents and Settings\Казбек\Мои документы\Мои рисунки\Screen.jpg[/img]

Ссылка

Николай Павлов

02.07.2013 10:12:39

Лучше нажмите ALT+F11 - это во всех версиях точно работает и откроет вам редактор Visual Basic.

Родитель Ссылка

Milana

23.07.2013 17:20:24

Я все делаю как написанно, а макроса чтобы добавить его уже на новом листе в экселе нету...( Вроде что-то появилось, но оглавление не получается, при выполнении снова выбрасывает в Visual Basic и там выдает вот что (см. скрин.). Как всетаки сделать оглавление???

Ссылка

Николай Павлов

26.07.2013 09:22:54

Уберите номера строк

Родитель Ссылка

Николай

21.08.2013 20:22:54

Здравствуйте, подскажите пожалуйста можно ли изменить макрос для оглавления листов ( в книге листы из двух групп 1 группа информация о материалах, 2 группа информация об объектах) суть изменения в том чтоб на листе 1 было 2 колонки отображающих категории склада и объектов.
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
sheet.Hyperlinks.Add Anchor:=sheet.Range("K1"

, Address:="", SubAddress:= _
"Лист1!K1", TextToDisplay:="Лист1"
Next
End With
Worksheets("Лист1"

.Range("A1:A1"

.Clear
End Sub

Ссылка

Сергей К.

05.09.2013 09:39:11

Всем привет!
Скрипт значительно облегчил жизнь. Спасибо большое!
Николай, а можете подсказать как можно сделать:
1) чтобы макрос всё-таки не выводил в формируемом списке название первого листа ("Оглавление"),
2) чтобы названия листов в итоговом списке выводились в алфавитном порядке?
Заранее признателен.

Ссылка

Михаил Мошура

09.10.2013 12:16:13

Николай, добрый день.

К сожалению, не работает самый первый макрос описанный вами. Делаю всё по инструкции, номера строк не вставляю как делают некоторые. Что-то происходит, но результатов никаких нет. То есть пустой лист как был пустой так и остался.

С уважением, Михаил.

Ссылка

Романов Николай

09.12.2013 15:45:03

Добрый день!
Отличный способ, но возникла проблема. не происходит переход на диаграммы. Можно ли это сделать?

Ссылка

Романов Николай

09.12.2013 15:47:10

Я имел ввиду способ №2

Ссылка

Mehabr

20.01.2014 19:05:16

Добрый день, возник вопрос, что делать если в название листов есть пробел или скобка? Пример названия листа - "190 (01.14)", "191 (02.14)" (названия без кавычек конечно же). Если все оставить как есть, то ссылки не работают. Если убрать пробел и скобки, то ссылка начинает работать. Мне переименовать все листы, или можно как-то этого избежать?

Ссылка

Сашка Барабашка

26.03.2014 02:07:03

Всем привет) Крутой макрос. то что нужно. Спасиба.!!!

хотел узнать. что можно такого вписать в этот чудный макрос, чтоб список создавался вот так:
- у меня листы с названиями идут так: - "Главная", "999", "998", "997"...."1"
- список с названиями на листе "Главная" создается с ячейки А1 и вниз "999", "998", "997"...."1"
- список с коментариями или ещё с какими то заметками на листе "Главная" начинается с ячейки В1 и вниз
- мне нужно чтоб список создавался: "1" ,"2", "3" ...."999" и чтоб ячейки с комментариями тож перемещались вместе с ячейками
я пробовал записать отдельный макрос на сортировку ячеек, он как бы один раз срабатывает нормально, а при повторном запуске мешает все ячейки в столбце В:В.

Родитель Ссылка

Алиса

10.02.2015 16:55:25

Мехабр, вы смогли удалить в листах пробелы как-то одним способом? (не вручную)

Родитель Ссылка

Дмитрий Болдырев

05.05.2014 15:55:12

Небольшое замечание к способу №2: Если в названии листов будут пробелы - формула гиперссылки, указанная в статье не сработает!
Формула должна быть такая:

=ГИПЕРССЫЛКА("#'"&A2&"'!A1";">>>")

Ссылка

Екатерина Волкова

19.05.2023 14:46:20

Спасибо большое!

Родитель Ссылка

Дмитрий

09.10.2023 06:52:22

Долго не мог понять почему не работают ссылки, потом обнаружил что не работают те, у которых пробелы в имени.
Я переделал формулу чтобы текст ссылки был непосредственно именем листа, а не >>>>

=ГИПЕРССЫЛКА("#'"&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА())&"'";"")&"!A1"; ""&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА());""))

Родитель Ссылка

Валерий Березовский

10.06.2014 12:05:26

Добрый день Николай!

Хороший прием, но пользовался, им через гиперссылки, спасибо вам.
Небольшое дополнение, всегда есть необходимость попасть из конкретного листа в оглавление, а потом перейти в любой лист, было бы хорошо, если бы вы дозаписали свой клип и учли бы это. Спасибо.

Ссылка

Наталия

07.10.2014 18:35:12

- мне нужно чтоб список создавался: "1" ,"2", "3" ...."999" и чтоб ячейки с комментариями тож перемещались вместе с ячейками

Поддерживаю вопрос. Мне тоже нужно, чтобы комментарии привязаны были к названию листа. Подскажите, пожалуйста, как это можно сделать

Ссылка

Максим Дронов

10.12.2014 14:34:51

Вроде понял!
Все дело оказалось в имени листа!
Как теперь сделать так, чтоб имя не влияло на ссылку?

Ссылка

Николай Павлов

10.12.2014 15:16:26

Максим, как вам помочь не видя вашего файла?????
Предполагаю, что у вас могут быть пробелы в именах листов. Тогда имя листа во всех формулах нужно дополнительно заключить в апострофы.

Родитель Ссылка

Виктор Антропов

19.12.2014 12:38:37

Что нужно сделать, чтобы вновь добавленный в "старую" книгу лист Оглавление стал по списку первым и (желательно) чтобы собственное имя листа Оглавление не высвечивалось бы в списке листов.
Я использую второй способ. Мне он кажется самым изящным. ))

Ссылка

Алиса

10.02.2015 16:54:16

Николай, добрый день!
Нигде не смогла найти ответ на свой вопрос, возможно, вы могли бы мне что-то посоветовать.
У меня файл, 500 листов. Все имеют название из 2-х слов с пробелом, типа "SUN 125", "FOE FV" и т.д.
Чтобы моя формула по сводке данных в одну таблицу со всех листов заработала, нельзя чтобы в названии листов были пробелы.
Возможно ли изменить какой-либо формулой или с помощью макроса одним махом - все пробелы заменить на _ (нижнее подчеркивание), к примеру.

Ссылка

Николай Павлов

12.02.2015 10:05:32

Алиса, тут нужен макрос. Что-то типа:

Sub RenameSheets()
   For Each sh in ActiveWorkbook.Sheets
      sh.Name = replace(sh.Name," ","_")
   Next sh
End Sub

Родитель Ссылка

Алиса

14.02.2015 11:42:35

Николай, макрос работает! Огромное спасибо!!!

Родитель Ссылка

Иван Кищенко

23.10.2015 14:26:41

удалил

Родитель Ссылка

Ренат Мустафин

20.02.2015 12:43:52

Добрый день. У меня в названиях листов даты, дело в том что гиперссылка не видит дату, она видит число. Использую ваш пример 2. как поступить?

Ссылка

Ренат Мустафин

20.02.2015 13:31:03

додумался... дописал формулу

=ГИПЕРССЫЛКА("#"&ТЕКСТ(AF27;"ДД.ММ.ГГГГ")&"!A1";">>>")

Родитель Ссылка

Александр Дрокин

26.03.2015 15:44:35

Здравствуйте!

Очень полезная статья. Спасибо!

Попробовал на основе этого оглавления посчитать кол-во заполненных ячеек в первом столбце каждого листа с помощью формулы, но почему-то происходит ошибка формулы

=СЧЁТЗ("#"&A3&"!A:A)

P.S.
Хочется растянуть формулу и вуаля, вместо работы руками...

Ссылка

Николай Павлов

06.10.2017 09:56:44

Не хватает функции ДВССЫЛ (INDIRECT)

Родитель Ссылка

Антон Камышан

14.03.2016 12:09:54

Здравствуйте!
очень хорошая статья!
помогите решить проблему.
у меня книга состоит из нескольких одинаковых по формату листов, причём их кол-во постоянно меняется, и сводного листа (куда собирается инфа из промежуточных).
можно ли как нибудь формулой прописать заполнение сводного листа? я чувствую, что предложенный вариант 2 + функция ДВССЫЛ может решить проблему, но что-то не получается.
заранее благодарен.

Ссылка

Юрий Генсецкий

05.08.2016 07:57:31

Уважаемый Николай Павлов.
Попробовал создать свое оглавление по Вашему фильму, но столкнулся со следующей проблемой.
функция =ЗАМЕНИТЬ(), требует наличия еще одного аргумента, а именно числа знаков, но число знаков она берет с названий листов.
Без этого аргумента Excel выдает ошибку, если же он будет иметь значение
В результате получается, что если значение этого аргумента будет 0, но ничего не изменится, а если больше нуля,то обрезаются название листов .
В общем как то так пока:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));0;"";)

Ссылка

Александр Садловский

01.11.2016 19:14:36

Тут спрашивали как быть с гиперссылками на листы, в имени которых есть пробелы. В способе 2 можно немного доделать формулу ГИПЕРССЫЛКА для универсальности:

=ГИПЕРССЫЛКА("#"&"'"&A2&"'!A1";">>>>>")

Ссылка

wolfram

30.01.2017 07:45:00

Здравствуйте!
Скажите пожалуйста, второй способ - тоже подразумевает макрос, функция в листах, без поддержания макросов не работает.
Чистого способа на формулах нет?

Ссылка

Марина Черных

28.02.2017 16:42:52

Спасибо. БОЛЬШОЕ СПАСИБО, за вашу работу и помощь всем нам

Ссылка

Элвис Рэд

25.04.2017 14:12:21

Как изменить макрос для создания ссылки перехода на последнюю заполненную ячейку в определенном столбце, вместо А1, с обновлением при каждом открытии документа.

Ссылка

uvl77

17.09.2017 13:40:21

Здравствуйте.

Проблема такая создал новое имя (Вставка -> Имя -> Присвоить) [EXEL 2003].
При создании нового имени exel работал нормально пару дней.
Потом после двух дней при открытии файла стал выдавать:

Эта книга содержит макросы Microsoft Excel 4.0. Эти макросы могут создавать вирусы или другой вредоносный код.
Эти макросы будут отключены.

Все началось после копирования листа книги.
При удалении имени предупреждение не выдавалось. При повторном создании имени предупреждение стало выскакивать опять.
Разве эти имена считаются макросами? И почему первые два дня exel ни как не реагировал на них? (Уровни безопасности я не переключал).

ЧТО ДЕЛАТЬ?

P.S. Ниже описал какое имя и формулу вводил.

Вставка -> Имя -> Присвоить
Добавляю:
- Имя: ОГЛ
- Формула: =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)&Т(ТДАТА())

Ссылка

Николай Павлов

06.10.2017 09:54:21

Формально, это команды старого макроязыка XL4 и система безопасности на них реагирует. Понизьте уровень безопасности и все будет ОК.

Родитель Ссылка

hanaro

05.10.2017 14:36:22

Николай,
Интересует вопрос: как можно сделать связь между оглавлением и листами книги, чтобы при фильтрации ячеек оглавления, соответствующие листы скрывались/отображались автоматически?

Ссылка

Николай Павлов

06.10.2017 09:52:53

Такое только макросом.

Родитель Ссылка

Александр Лосев

13.12.2017 14:48:48

Николай, а возможно ли как-то проставить номера страниц с других листов на первом листе? Т.е. нужно оглавление (оно постоянно), в котором не приходилось бы менять номера страниц "вручную", т.к. другие листы книги периодически меняются по объему своего содержания...

Ссылка

Владимир Иванов

21.12.2017 13:05:33

Добрый день, есть простой вопрос с которым не могу справится, гугл что то толком не смог помочь

Надо сделать оглавление на первом листе с ссылками на определенные ячейки на втором листе.
И реализовать это так что бы при смещении ячейки на которую вела ссылка, ссылка перепривязывалась к изначальной ячейки ?

Ссылка

сергей пчёла

04.04.2018 21:44:04

Здравствуйте, уважаемый Николай! Я полный чайник в компьютерной технологии. Так в 56 лет пытаюсь что-то освоить. У меня вот в чём проблема.
Создаю лист оглавление... (создавал 1 и 3 способом), всё получается. Но при попытке открыть гиперссылки выскакивает окно (это действие запрещено политикой организации). Что я делаю не так? XL2013

Ссылка

Polina

30.09.2018 11:48:19

Добрый день.
Не работают две гиперссылки "Оформление на работу" и "Аудит" в документе.

Подскажите, пожалуйста, в чем ошибка.

Пример

Ссылка

Александр

12.10.2018 11:52:13

Добрый день, Николай! Оглавление выпадает с А1 и вниз по первому столбцу. Как сделать, чтобы оглавление выпадало в определенном диапазоне? К примеру с А1 до А40 и продолжалось с В2 до В40 , С2 до С40 и т.д. в зависимости от количества листов. Заранее спасибо!

Ссылка

Азат Куканов

17.01.2019 13:33:54

С Новым годом! Успехов, новых решений и благодарных пользователей Ваших продуктов! Всем пользователям удачи в освоении материала сайта!
Вопрос: Сделал оглавление с переходом по гиперссылке. Но при закрытии сайта требует сохранения в формате .xlsm, то есть с поддержкой макросов. В файле никаких макросов нет, проверил по всем Листам. При сохранении в .xlsx пропадает именованный диапазон и, естественно, по ошибке выполнения формулы ИНДЕКС не выгружается список Листов Книги. Винда 10ая, офис 365. PLEX - обновленный в конце года.
Самое интересное, что при сохранении в формате .xlsx пропадает именованный диапазон, созданный для заполнения таблицы именами листов. В формате с поддержкой макросов все работает нормально.

Ссылка

Антон

14.02.2019 12:38:31

Здравствуйте! Спасибо за полезный материал!

При попытке сделать интерактивное оглавление 2-м способом с колонкой названий листов всё прошло гладко, а вот гиперссылки из второй колонки заработали только некоторые, а большинство при попытке перехода стали выдавать ошибку "Неверная ссылка."

Немножко экспериментов и мне удалось подтвердить догадку, что проблема вызвана в наличием пробелов в названиях листов.

В общем, есть следующее решение проблемы. Формулу гиперссылки нужно писать в следующем виде:

=ГИПЕРССЫЛКА("#'"&A3&"'!A1";">>>")

или

 =HYPERLINK("#'"&A3&"'!A1";">>>")

Сами формулы равнозначны. Существенно использование апострофа (одинарной кавычки) после "#" и перед "!" - это нужно для экранирования листов с пробелами в имени листа.

Если кто-то сможет подробнее рассказать что и как в формуле с гиперссылкой и по какому принципу определяется место апострофов - было бы здорово. Я же здесь просто добавил апострофы по аналогии с примером. У меня всё заработало. Формула действует и для листов в именах которых есть пробелы, и в которых таковых нет. Во всяком случае в Excel 2010 русскоязычную ссылку я на целевом файле проверил.

Также было бы неплохо, если бы кто-то предложил способ как можно сделать автоматическую замену пробела в названии листов (например, на знак подчеркивания)?

Думаю, что если автор внесет соответствующие дополнения и уточнения в основную статью - это было бы полезным для всех читателей.

Ссылка

igor buda

16.03.2019 23:53:44

Всем добрый день. Во 2 способе используется гипер ссылка для перехода на другой лист . Помогите как это можно это сделать через макрос привязанный к кнопке . т.е. в меню всего одна кнопка при нажатии переход на любой лист а имя листа берется из под активного курсора.

Ссылка

Николай Кудашов

02.05.2019 21:47:07

Добрый вечер! Подскажите как сделать , чтобы при формировании оглавления по способу 2 добавление листов не обрезало их количество на странице оглавления. В вашем примере при добавлении листа с городом последний город в списке оглавления пропадает. Спасибо.

Ссылка

Александр Воронин

16.12.2019 12:25:11

Здравствуйте. Никак не могу найти решение простой, как мне кажется, задачи.

Как с помощью макроса перейти на лист, который был создан по времени последним. (при этом он может располагаться и первым, и в середине, и крайним среди множества листов)

или
Перейти на лист, название которого совпадает со значением в активной ячейке ...

Заранее благодарю.

Ссылка

Vadim Burlac

24.12.2019 01:09:15

Спасибо)) очень интересная информация, завтра применю.

Однако рассматривая видео появился вопрос: Если перешел по гиперссылке и оказался в конце листов, как бытро вернутся на заголовки не перелистывая? нельзя как-то закрепить лист с заголовками?

Ссылка

MonsieurX

21.05.2020 15:50:11

Добрый день!

На основе Способа 3 и комментариев смастерил такой код создания оглавления из двух списков "Регионы" и "Города":

Private Sub Worksheet_Activate() 'макрос создания оглавления
    Worksheets(1).Range("A3:A1000").Clear 'очистка списка "Регионы"
    Worksheets(1).Range("B3:B1000").Clear 'очистка списка "Города"
    Dim sheet As Worksheet
    Dim cell As Range
    With ActiveWorkbook 'создание оглавления "Регионы" из значения ячейки B1 каждого листа
        For Each sheet In ActiveWorkbook.Worksheets
            Set cell = Worksheets(1).Cells(sheet.Index + 2, 1)
            .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!B1"
            cell.Formula = sheet.Cells(1, 2).Value
        Next
    End With
    With ActiveWorkbook 'создание оглавления "Города" из значений ячеек K14:K70 каждого листа
        For Each sheet In ActiveWorkbook.Worksheets
            Set cell = Worksheets(1).Cells(sheet.Index + 2, 2)
            .Worksheets(1).Hyperlinks.Add anchor:=cell, Address:="", SubAddress:="'" & sheet.Name & "'" & "!B1"
            cell.Formula = sheet.Cells(14, 11).Value
        Next
    End With
    Worksheets(1).Range("A3:A1000").Sort Worksheets(1).Range("A3"), xlAscending 'сортировка по алфавиту списка "Регионы"
    Worksheets(1).Range("B3:B1000").Sort Worksheets(1).Range("B3"), xlAscending 'сортировка по алфавиту списка "Города"
End Sub

Подскажите, пожалуйста:

1) Лист "Оглавление" всегда первый и ссылка на нем на него же самого не нужна. Что нужно изменить/добавить в коде, чтобы лист "Оглавление" всегда игнорировался при создании оглавления?

2) Список "Города" должен формироваться из значений нескольких ячеек (определенного диапазона) каждого листа.

 cell.Formula = sheet.Cells(14, 11).Value

Что нужно изменить/добавить в коде, чтобы брались значения не только ячейки K14, а каждой из ячеек диапазона "K14:K70" каждого листа со ссылкой на ячейку B1 того листа, с которого берется значение (в ячейках диапазона формулы с возможным пустым значением, т.е. "" ) ?

Спасибо большое!

Ссылка

Роман Прокопцов

07.10.2020 09:38:48

Николай, большое спасибо за отличный способ сделать оглавление. Я хочу сделать подобие сводной таблицы, оглавление успешно создал с помощью 2го способа, все отлично работает, автоматически обновляется. Хочу использовать из оглавления из столбца А имя листа в формуле такого типа ='ИМЯ_ЛИСТА'!$C$2 но прямая ссылка на ячейку из оглавления не работает, нагуглил вариант с ДВССЫЛ, но с транскрипцией видимо что то не так делаю. Буду благодарен за варианты решения данной проблемы

Ссылка

Максим Андреев

02.05.2021 19:46:17

Здравствуйте Николай! Почему то на функцию "ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ" пишет неверная функция. Microsoft 365 версия 2008 (сборка 13127.21506)

Ссылка

Анна Катунова

11.03.2022 13:04:50

Здравствуйте, Николай! В способе с функцией "ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ" стало писать #Н/Д
как результат выполнения функции =ИНДЕКС(Оглавление;СТРОКА())
Office Stadndart 2019

Ссылка

Tony

14.03.2023 14:56:56

Работала формула =ИНДЕКС(Оглавление; СТРОКА()) прекрасно, пока не открыл ее на macbook
Почему-то там не возвращает имя листа
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW()) указано верно
В чем может быть дело?

Ссылка

LAMM V

03.05.2023 18:17:53

Не работает потому что нет такого оператора SUBST. Есть оператор SUBSTITUTION, но и он не подходит. ЗАМЕНИТЬ это оператор REPLACE.
Кроме того, иногда вместо точки с запятой нужно использовать запятую. (зависит от установок в операционной системе)

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())

Кроме того, ссылку можно сделать в одной ячейке, а не в двух - без использования знаков ">>>"

=HYPERLINK("#"&IFERROR(INDEX(Content, ROW()),"")&"!a1",IFERROR(INDEX(Content, ROW()),""))

Родитель Ссылка

Сергей

24.01.2025 10:33:52

Идем по способу 2. На листе "Оглавление" создаётся табличка вида:

Оглавление	Ссылка
Название листа 1	>>>>>

при этом формула для Имен листов (даже если в них есть пробелы):

=ЕСЛИОШИБКА(@ИНДЕКС(Оглавление;СТРОКА());"")

а формула для ссылок:

=ЕСЛИ(ТЕКСТ(A2;"@")="";"";ГИПЕРССЫЛКА("#"&"'"&A2&"'!A2";">>>>>"))

Преимущество метода: можно протянуть табличку вниз на N-строк, и, если листов нет, строки будут пустыми.

Обновление листа с Оглавлением возможно 2-мя путями:
1) банальным нажатием F9 (книга пересчитается и таблица обновится)
2) Если в код страницы (правой мыши по листу "Оглавление" -> "Просмотреть код" ) добавить макрос автоматического пересчета листа при любом изменении (переходе на др. ячейку и любые др. действия)

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveCell.Calculate
End Sub

Всё это работает в офисе 2021

Ссылка

Страницы: 1 2