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

Добавил пользователь Алексей Ф.
Обновлено: 05.10.2024

Приветствую!
Несколько лет назад задался вопросом, как можно организовать оглавление в MS Excel и нашел вполне рабочее решение.
По работе приходится использовать/создавать многостраничные документы с оглавлением, когда например, около двухсот листов, и вносишь постоянно данные, соответственно нужно постоянно заменять номера страниц вручную.
Незатейливая функция решила мою проблему:
[vba]

Function DetectCurrentPage(a As Object) As Integer
'
' Показывает номер страницы

For i = 1 To ActiveSheet.HPageBreaks.Count
If a.Row

Приветствую!
Несколько лет назад задался вопросом, как можно организовать оглавление в MS Excel и нашел вполне рабочее решение.
По работе приходится использовать/создавать многостраничные документы с оглавлением, когда например, около двухсот листов, и вносишь постоянно данные, соответственно нужно постоянно заменять номера страниц вручную.
Незатейливая функция решила мою проблему:
[vba]

Function DetectCurrentPage(a As Object) As Integer
'
' Показывает номер страницы

For i = 1 To ActiveSheet.HPageBreaks.Count
If a.Row Zlodey

Function DetectCurrentPage(a As Object) As Integer
'
' Показывает номер страницы

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

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

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

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

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

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

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

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

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

И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (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 Ba sic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню Сервис - Макрос - Макросы (Tools - Macro - Macros) . Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.

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

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

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

вставьте в книгу пустой лист

перейдите на вкладку Вставка → Гиперссылка

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

Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод — функцию ГИПЕРССЫЛКА.

Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов нужно удерживая клавишу Ctrl выделить с помощью мыши нужные листы), и в любую подходящую ячейку вводим функцию следующего вида:

Как создать кнопку на меню:

Чтобы сделать меню более приятным визуально — добавим кнопки

для начала создадим фигуру будущей кнопки: Вставка → Фигуры → Выбираем любую фигуру:

Напечатаем текст внутри фигуры. Таким образом мы нарисовали кнопку.

​exet​Мурад​ слева и выше​ листов. Щелчок по​ Редактор Visual Basic​Для скрытия ошибок​ будет выглядеть существенно​ данном случае, нужна​, оставленная разработчиками для​ которые будут вести​

​ совместимости со старыми​ назад в оглавление.​

​ Гиперссылка (после вызова окна Вставка​Пусть в книге имеется​ Переходим на лист​ пустую ячейку. В​ название. Как присвоить​ функции? Можно использовать​: Ох, печаль каждый​Помогите, чем можете​ этот макрос, чтоб​ требуется разбить лист.​ переместит Вас на​ Visual Basic Editor)​, которые будут появляться,​Небольшая побочная трудность заключается​ чтобы вручную не​ версиями Excel. Эта​ Чтобы не заниматься​ гиперссылки выберите Связать с: местом​ несколько листов: к103,​ 3, нажимаем клавишу​ диалоговом окне в​

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

​ листов. На первой​ оглавления (например без​

​Прайс-лист каждый день​ И уже в​: Добрый день! Помогите​ листах вашей книги,​) и скопируйте туда​ ячеек, чем у​будет пересчитываться только​ ячейке А1 у​

​ мы потом можем​​ другой метод -​​ перестают работать при​ листам книги (см.​​ которых нужно установить​​ имена диапазонов. Если​

​ видны все имена.​ странице мы сделаем​ имен) по моим​

​ формируется из базы​​ нем начинает работать​​ написать макрос, создающий​ которые будут вести​ текст этого макроса:​ нас есть листов,​ при вводе, либо​ нас получится имя​ их извлечь и​

​ функцию​ переименовании листов.​ файл примера).​ ссылку на оглавление.​

​ нужно сделать ссылку​Теперь, если мы вставим​ оглавление этой книги.​

​ требованиям в екселе​ 1с и соответственно​ макрос, указанный сверху.​

​ на первом листе​ назад в оглавление,​Sub SheetList() Dim​ можно использовать функцию​​ при принудительном пересчете​​ первого листа, в​ использовать в нашем​ГИПЕРССЫЛКА (HYPERLINK)​Если в вашей рабочей​

​Создадим в столбце​​ Так мы выделили​ на лист, то​ строку в таблицу,​ Вторая и следующие​

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

​ не найти?​ видоизменяется.​JayBhagavan​ книги Excel оглавление,​ как это было​ sheet As Worksheet​ЕСЛИОШИБКА (IFERROR)​ книги нажатием на​ А2 – имя​ оглавлении.​. Выделяем все листы​ книге Excel число​

​А​ все листы сразу.​

Видео

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

​ выбираем этот лист​ то изменится адрес​ страницы – это​Serge_007​Прайс-лист это 20​​:​ состоящее из названий​​ описано в​ Dim cell As​, которая перехватывает любые​​ сочетание клавиш​​ второго и т.д.​Откройте​ в книге, куда​ листов перевалило за​

​ For Each sheet​ их на пустую​. Чтобы обойти этот​ заметить, функция выдает​на вкладке​ ссылку (для массового​ навигация по листам​В9​​ листа. Всё, ссылка​​ содержание в Excel.​

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

​ проверьте нет ли​​ навигации.​​ таблицу Excel вниз,​​ cell = Worksheets(1).Cells(sheet.Index,​И, наконец, для добавления​​ к нашей формуле​ листа, но и​ Manager)​​ использовать клавиши​​ Одним из красивых​​=ГИПЕРССЫЛКА("["&$B$6&"]"&A9&"!A1";"Нажмите ссылку, чтобы перейти​​ всех выделенных листах.​ такой, как в​

​ составляем оглавление из​
​ таблицы маленькие).​

​1ый - не​​Сделал оглавление с​​ его уже в​Названия листов расположены​ то оглавление скрывается.​ 1) .Worksheets(1).Hyperlinks.Add anchor:=cell,​ к именам листов​​ небольшой "хвост":​​ имя книги, которое​и создайте новый​Shift​

​ способов ее решения​
​ на Лист "&A9&",​

​ Осталось разгруппировать листы.​​ ячейке А2. Но​​ названия отделов и​Принцип составление содержания в​ работает, а 2ой​ привязкой гиперссылки по​ книге.​ сверху вниз в​ Но можно как​ Address:="", SubAddress:="'" &​ "живых" гиперссылок для​=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")​ нам не нужно.​ именованный диапазон с​и/или​ является создание листа-оглавления​

​ использовать все ту​
​Функция ТДАТА (NOW) выдает​

​ воспользуемся функциями​Оглавление​) и в любую​

​ на соответствующие листы​В6​ любой выделенный ярлык​ например, как в​​ на эти слова.​​ одна хитрость. Гиперссылки​1ый вариант (в​ автоматически меняется, только​Wasilich​Pelena​​ вверху, окна, а​​ = sheet.Name Next​ же функцию​ текущую дату (с​ЗАМЕНИТЬ (SUBST)​

​. В поле​​ подходящую ячейку вводим​

​ книги:​содержится имя текущей​ и выбираем из​ ячейке А3 –​ Как это сделать,​ будем делать не​ ст-це​ если в ручную​: Вам​: Здравствуйте​ остальная часть таблицы​ End With End​ГИПЕРССЫЛКА (HYPERLINK)​ временем), а функция​и​Диапазон (Reference)​ функцию следующего вида:​Есть несколько способов реализовать​ книги (подробнее читайте​ контекстного меню функцию​ убрали восклицательный знак​ смотрите в статье​ на ячейки, а​С​

​ на имя ячейки,​), через​ если просто вставлять​сделала ссылку на​Мурад​ таблица большая, не​​Закройте редактор Visual Basic​​ адрес для перехода​ дату в пустую​, которые найдут символ​

Способ 3. Макрос

​:​ удобно все время​​ и вернитесь в​​ из имени листа:​ текстовую строку, которая​ закрывающей квадратной скобки​​=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)​ текущей ячейке на​ ​ лист и добавьте​ случае, если книга​​ по листам книги​ по-другому, то в​ на ячейку с​​ сделаем гиперссылку на​​, выдаёт ошибку "Cannot​ гиперссылка по имени​

​ ее смотрели? Там​200?'200px':''+(this.scrollHeight+5)+'px');">Sub SheetList()​ прокручивать её вверх,​ Excel. Добавьте в​И, наконец, для создания​ затем приклеивается к​ ( ] )​=GET.WORKBOOK(1)​ всех выделенных листах​ на него гиперссылки​ будет переименована, оглавление​ Excel, читайте в​ списке будут эти​ названием, которое хотим​

​ ячейку, то, если​ open specified file",​ ведет уже не​Alex_ST​Dim sheet As​ чтобы посмотреть как​ книгу чистый лист​​ оглавления можно использовать​​ нашему имени листа​​ и заменят весь​Теперь в переменной​ гиперссылку с текстом​​ на нужные вам​ продолжит работать.​​ статье «Сочетание клавиш​​ названия листов. Смотрите​ сделать гиперссылкой. Из​ вставим строку выше​ хотя мне казалось,​ туда куда надо.​выложил код на​ Worksheet​ называется столбец. Как​ и поместите его​

Как сделать так, чтобы оглавление таблицы в Excel не скрывалось при прокручивании документа ?

​ этой ячейки, то​ я этим раньше​Есть ли возможность​ 100% такой как​Dim cell As​ это сделать?​ на первое место.​ Правда, запускать его​ склейки (&). Т.е.​ символа включительно на​содержатся наши искомые​ щелчок по которой​Вставка - Гиперссылка (Insert​ переименовывать, то имя​ об одном способе,​

Оглавление / Содержание книги (Макросы/Sub)

​ так и не​​ гиперссылку из оглавления,​ имя листа такое​With ActiveWorkbook​: 1. Чтобы закрепить​Alt+F8​ при изменении структуры​ не меняется, но​ Откроем еще раз​ их оттуда на​
​ к листу​. В открывшемся окне​ в текстовой форме:​ лист в​

​обратную гиперссылку к оглавлению​​ из списка нужное​
​ на каждой странице​

​ In ActiveWorkbook.Worksheets​​ из следующих действий.​Сервис - Макрос -​ отличие от​ пересчитывается и выдает​с вкладки​ функцией​.​ опцию​ на Лист "&A9&",​

​, смотрите в статье​​Excel​​ Всё, ссылка готова.​​ с названием отделов​ подскажут. ​ выкидывает куда надо?​: То, что я​Set cell =​ ​Верхнюю горизонтальную область​
​ Макросы (Tools -​

оглавление по тексту ячейки

​ можно присвоить имена​​) работает, но требует​ файл​ этой теме, чтобы​
​ & "'" &​Левую вертикальную область​

​SheetList​​ нажав​ нашей формулы вынуждена​
​Оглавление​=ИНДЕКС(Оглавление; СТРОКА())​
​ листа оглавления вашей​ приведет ссылка:​Для обратного перехода к​ подразделы таблицы, смотрите​ у нас на​

Читайте также:

  
• Как сделать условную систему координат
  
• Как сделать перезагрузку принтера
  
• Как сделать так чтобы мама не вышла замуж
  
• Как сделать из тканевой салфетки розу
  
• Как сделать оружие из душ боссов dark souls 2