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

http://www.planetaexcel.ru/techniques/12/60/

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

Способ мой. Мой вариант

т

Название листа - =ЕСЛИОШИБКА(ЗАМЕНИТЬ(ИНДЕКС(Оглавление;СТРОКА()-3);1;НАЙТИ("]";ИНДЕКС(Оглавление;СТРОКА()-3));"");"")

Ссылка - =ГИПЕРССЫЛКА("#"&"'"&B4&"'"&"!A10";">>>")

Дата - =ЕСЛИОШИБКА(ЕСЛИ(ДВССЫЛ("'"&B4&"'"&"!A1")=0;"";ДВССЫЛ("'"&B4&"'"&"!A1"));"")

Наименование - =ДВССЫЛ("'"&B4&"'"&"!A3")

ЗП - =ДВССЫЛ("'"&B4&"'"&"!E5")

налог на ЗП - =ДВССЫЛ("'"&B4&"'"&"!E6")

амортизация - =ДВССЫЛ("'"&B4&"'"&"!E7")

материалы - =ДВССЫЛ("'"&B4&"'"&"!E8")

всп материалы - =ДВССЫЛ("'"&B4&"'"&"!E9")

и далее по столбцам

=ДВССЫЛ("'"&B4&"'"&"!E10")
=ДВССЫЛ("'"&B4&"'"&"!E11")
=ДВССЫЛ("'"&B4&"'"&"!E12")
=ДВССЫЛ("'"&B4&"'"&"!E13")
=ДВССЫЛ("'"&B4&"'"&"!E18")
=ДВССЫЛ("'"&B4&"'"&"!E19")