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

Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:

toc1.png

Есть несколько способов реализовать подобное.

Видео

Способ 1. Создаваемые вручную гиперссылки

Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка - Гиперссылка (Insert - Hyperlink). В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:

toc2.png

Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод - функцию ГИПЕРССЫЛКА (HYPERLINK). Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:

=ГИПЕРССЫЛКА("#Оглавление!A1";"Назад в оглавление")

Эта функция создаст в текущей ячейке на всех выделенных листах гиперссылку с текстом "Назад в оглавление", щелчок по которой будет возвращать пользователя к листу Оглавление.

Способ 2. Динамическое оглавление с помощью формул

Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK), оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.

Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:

toc3.png

=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
=GET.WORKBOOK(1)

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:

=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())

Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.

toc4.png

Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND), которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку (""). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager), двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Теперь наш список листов будет выглядеть существенно лучше:

toc5.png

Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой "хвост":

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА())
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

Функция ТДАТА (NOW) выдает текущую дату (с временем), а функция Т превращает эту дату в пустую текстовую строку, которая затем приклеивается к нашему имени листа с помощью оператора склейки (&). Т.е. имя листа, фактически, не меняется, но поскольку функция ТДАТА пересчитывается и выдает новое время и дату при любом изменении листа, то и остальная часть нашей формулы вынуждена будет заново пересчитаться тоже и – как следствие – имена листов будут обновляться постоянно.

Для скрытия ошибок #ССЫЛКА (#REF), которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR), которая  перехватывает любые ошибки и заменяет их на пустую строку (""):

toc6.png

И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (HYPERLINK), которая будет формировать адрес для перехода из имени листа:

toc7.png

Способ 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.

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

 


Николай
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
F F
28.12.2012 11:02:16
Друзья,
при создании макроса выпадает ошибка
Must be first statement on the line.Не понимаю, что исправить в первой строке надо!?
Подскажите.
05.01.2013 14:24:46
Скачайте работающий пример, посмотрите код, сравните со своим - для начала. Если не поможет - пишите сюда или сразу на Форум.
Di
14.01.2013 14:20:52
Подскажите, а как сделать этот макрос доступным для всех книг ? есть ли у Экселя что-то типа шаблона, как в ворде? У меня Эксель 2003.
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
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... :/
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()
24.07.2013 12:08:31
Добрый день! Данный макрос: Private Sub Worksheet_Activate() не совсем понятно куда вставлять. Как сделать так, чтобы список обновлялся автоматически, при добавлении нового листа. У меня стоит MS Excel 2010. Использовал волшебный вышестоящий макрос от Николая Павлова.
24.07.2013 13:14:39
Небольшое дополнение, как сделать, чтобы при удалении/добавлении листов, список обновлялся автоматически?
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 каждого не скрытого листа) в активном перечне, формируемом макросом.
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.
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
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";">>>")
Добрый день Николай!

Хороший прием, но пользовался, им через гиперссылки, спасибо вам.
Небольшое дополнение, всегда есть необходимость попасть из конкретного листа в оглавление, а потом перейти в любой лист, было бы хорошо, если бы вы дозаписали свой клип и учли бы это. Спасибо.
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.
Хочется растянуть формулу и вуаля, вместо работы руками...
14.03.2016 12:09:54
Здравствуйте!
очень хорошая статья!
помогите решить проблему.
у меня книга состоит из нескольких одинаковых по формату листов, причём их кол-во постоянно меняется, и сводного листа (куда собирается инфа из промежуточных).
можно ли как нибудь формулой прописать заполнение сводного листа? я чувствую, что предложенный вариант 2 + функция ДВССЫЛ может решить проблему, но что-то не получается.
заранее благодарен.
05.08.2016 07:57:31
Уважаемый Николай Павлов.
Попробовал создать свое оглавление по Вашему фильму, но столкнулся со следующей проблемой.
функция =ЗАМЕНИТЬ(), требует наличия еще одного аргумента, а именно числа знаков, но число знаков она берет с названий листов.
Без этого аргумента Excel выдает ошибку, если же он будет иметь значение
В результате получается, что если значение этого аргумента будет 0, но ничего не изменится, а если больше нуля,то обрезаются название листов .
В общем как то так пока:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));0;"";)
Тут спрашивали как быть с гиперссылками на листы, в имени которых есть пробелы. В способе 2 можно немного доделать формулу ГИПЕРССЫЛКА для универсальности:

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