Как сделать оглавление в эксель с гиперссылками

Оглавление книги на основе гиперссылок в EXCEL

Пусть в книге имеется несколько листов: к103, к104, к105, . (например перечень комнат). Составим оглавление, позволяющее быстро перемещаться по листам книги (см. файл примера ).

Создадим в столбце А перечень названий листов книги. В ячейке В9 запишем формулу:

= ГИПЕРССЫЛКА(«[«&$B$6&»]»&A9&»!A1″;»Нажмите ссылку, чтобы перейти на Лист «&A9&», в ячейку А1″)

в ячейке В6 содержится имя текущей книги (подробнее читайте в статье Извлечение имени файла ). В случае, если книга будет переименована, оглавление продолжит работать.

Если книгу не планируется переименовывать, то имя книги можно указать в текстовой форме:

=ГИПЕРССЫЛКА(«[_Оглавление_с_гиперссылками.xlsx]к103!A1»;»Нажмите ссылку, чтобы перейти на Лист «&A9&», в ячейку А1″)

При нажатии гиперссылки MS EXCEL перейдет в окно соответствующего листа.

Для обратного перехода к оглавлению можно создать ссылку с помощью стандартного механизма гиперссылок, нажав CTRL+K или через меню Вставка/ Связи/ Гиперссылка (после вызова окна Вставка гиперссылки выберите Связать с: местом в документе ).

Примечание : Ссылки, созданные с использованием стандартного механизма гиперссылок перестают работать при переименовании листов.

Как сделать оглавление в эксель с гиперссылками

Написал небольшой, но весьма полезный макрос для автоматизации создания оглавления с гиперссылками для книг Excel, которые содержат большое количество листов. Те, кто пробовал работать с такими книгами, прекрасно знают, как напрягает и бесит долгая возня с мышкой для перехода из одного листа в другой. На помощь приходит замечательная способность Excel создавать гиперссылки на ячейки, расположенные на других листах.

Мой макрос ярко выделяется из бесконечно унылой серой массы тем, что он:

1. Создаёт не только элементы оглавления, которые позволяют быстро перейти на любой лист книги, но ещё и на каждом листе размещает обратную ссылку, при помощи которой вы быстро возвращаетесь обратно в оглавление
2. Оглавление сделано при помощи фигур, поэтому выглядит весьма эстетично (рамочки, тень, выравнивание, etc.)

Это очень сильно сокращает время на перемещения между листами, если в вашей книге их более 15.

Скачать демонстрацию

Файл для скачивания

Перенос кода в вашу книгу

Подробно описан тут. Вам необходимо перенести модуль с названием TOC .

Более простой вариант

Есть более простой вариант, не требующий переноса кода в вашу книгу:

1. Откройте в Excel оба файла: и ваш, где надо создать оглавление, и мой демонстрационный
2. Перейдите на свой файл, создайте лист для оглавления
3. Через Alt + F8 вызовите макрос HyperTOC.xlsm!Создать_оглавление

Как сделать оглавление в Excel

Когда количество листов в книге становится более 10-15 штук, то начинаются трудности с навигацией.

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

Создание оглавление с помощью надстройки

Надстройка VBA-Excel содержит диспетчер листов в котором расположена команда Создать оглавление книги.

Для того чтобы создать оглавление:

1. Откройте вкладку меню VBA-Excel
2. Откройте меню Диспетчеры и выберите Диспетчер листов
   
3. Выберите команду Оглавление, нажав по ней левой кнопкой мыши.
   
4. В книге появится лист Оглавление, который будет выглядеть так, как показано на рисунке. Наименование листов в оглавление содержат гиперссылки, для быстрого перехода к нужному листу.

Внимание! Если в файле уже имеется лист Оглавление, макрос его удалит и создаст новое оглавление.

Надстройка
VBA-Excel

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

Создание оглавления книги

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

Настройка: пример файла Excel

Эта книга содержит данные, объекты и форматирование, ожидаемые скриптом.

Пример кода. Создание оглавление книги

Добавьте следующий скрипт в пример книги и попробуйте его самостоятельно!

function main(workbook: ExcelScript.Workbook) < // Insert a new worksheet at the beginning of the workbook. let tocSheet = workbook.addWorksheet(); tocSheet.setPosition(0); tocSheet.setName("Table of Contents"); // Give the worksheet a title in the sheet. tocSheet.getRange("A1").setValue("Table of Contents"); tocSheet.getRange("A1").getFormat().getFont().setBold(true); // Create the table of contents headers. let tocRange = tocSheet.getRange("A2:B2") tocRange.setValues([["#", "Name"]]); // Get the range for the table of contents entries. let worksheets = workbook.getWorksheets(); tocRange = tocRange.getResizedRange(worksheets.length, 0); // Loop through all worksheets in the workbook, except the first one. for (let i = 1; i < worksheets.length; i++) < // Create a row for each worksheet with its index and linked name. tocRange.getCell(i, 0).setValue(i); tocRange.getCell(i, 1).setHyperlink(< textToDisplay: worksheets[i].getName(), documentReference: `'$'!A1` >); >; // Activate the table of contents worksheet. tocSheet.activate(); >