Как сделать оглавление в эксель
Приветствую!
Несколько лет назад задался вопросом, как можно организовать оглавление в 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ый вариант (в автоматически меняется, толькоWasilichPelena вверху, окна, а = 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_STDim sheet As чтобы посмотреть как книгу чистый лист оглавления можно использовать нашему имени листа и заменят весьТеперь в переменной гиперссылку с текстом на нужные вам продолжит работать. статье «Сочетание клавиш названия листов. Смотрите сделать гиперссылкой. Из вставим строку выше хотя мне казалось, туда куда надо.выложил код на Worksheet называется столбец. Как и поместите его
Как сделать так, чтобы оглавление таблицы в Excel не скрывалось при прокручивании документа ?
этой ячейки, то я этим раньшеЕсть ли возможность 100% такой какDim cell As это сделать? на первое место. Правда, запускать его склейки (&). Т.е. символа включительно насодержатся наши искомые щелчок по которойВставка - Гиперссылка (Insert переименовывать, то имя об одном способе,
Оглавление / Содержание книги (Макросы/Sub)
так и не гиперссылку из оглавления, имя листа такоеWith ActiveWorkbook: 1. Чтобы закрепитьAlt+F8 при изменении структуры не меняется, но Откроем еще раз их оттуда на
к листу. В открывшемся окне в текстовой форме: лист в
обратную гиперссылку к оглавлению из списка нужное
на каждой странице
In ActiveWorkbook.Worksheets из следующих действий.Сервис - Макрос - отличие от пересчитывается и выдаетс вкладки функцией. опцию на Лист "&A9&",
, смотрите в статьеExcel Всё, ссылка готова. с названием отделов подскажут. выкидывает куда надо?: То, что яSet cell = Верхнюю горизонтальную область
Макросы (Tools -
оглавление по тексту ячейки
можно присвоить имена) работает, но требует файл этой теме, чтобы
& "'" &Левую вертикальную область
SheetList нажав нашей формулы вынуждена
Оглавление=ИНДЕКС(Оглавление; СТРОКА())
листа оглавления вашей приведет ссылка:Для обратного перехода к подразделы таблицы, смотрите у нас на
Читайте также: