Как сделать оглавление в экселе
Обновлено: 04.07.2024
Я немного подсел на макросы. Раньше я был против этих автоматизаций, так как их трудно самому писать правильно, как говорится "без бутылки не разберешься", но есть моменты в работе, которые возможно упростить и облегчить только при помощи этой фишки в екселе.
Вчера я рассказывал про макрос, который можно использовать для снятия защиты с листа или книги если вы не знаете пароль. Почитать статью можно здесь - Макросы в Excel - как, не зная пароль, снять защиту с листа или книги? . А e;t сегодня я хочу поделиться с вами еще одним лайфхаком - способом как сделать оглавление в файле Excel.
Такие приложения, как Word, Power Point, PDF файлы - все имеют возможность создавать оглавление в начале файла. Excel тоже отличный инструмент - почему бы и ему не завести его? Бывают случаи, когда у вас много листов в книге и все, что вам нужно, это оглавление в начале для удобной навигации. Вот готовый макрос, который создает новый лист в качестве первого листа и прописывает ссылки на все листы в книге. Поверьте, это очень удобно.
Как запустить макрос?
Для примера я создал файл ексель с фотографиями 5 городов столиц дружественных нам стран.
Разберется даже школьник 👲- макрос создания оглавления из рабочих листов в книге Excel.🔠Да, и Украина тоже. Лично для меня Украина много значит, я там вырос и у меня там осталось много друзей. Теперь к сути. Листы специально переименовал по названию городов, и в каждом листе прикрепил фотографию с достопримечательностью города. Далее все, что нужно сделать - это вставить готовый код в редактор макроса и запустить его. Как это сделать?
1. Ищем на панели в ленте "Вид" вкладку "Макросы" и кликаем на нее. Далее опять кликаем и на "Макросы" и у нас открывается второстепенное окно с перечнем прописанных в книге макросов. У нас еще ни одного нет, поэтому в строке с названием сразу пишем "Оглавление" (это просто название макроса) и нажимаем "Создать".
Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:
Есть несколько способов реализовать подобное.
Видео
Способ 1. Создаваемые вручную гиперссылки
Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка - Гиперссылка (Insert - Hyperlink) . В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод - функцию ГИПЕРССЫЛКА (HYPERLINK) . Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:
Способ 2. Динамическое оглавление с помощью формул
Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK) , оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.
Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:
Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX) , которая «выдергивает» элементы из массива по их номеру:
=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())
Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.
Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND) , которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку (""). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas - Name Manager) , двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:
Теперь наш список листов будет выглядеть существенно лучше:
Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой "хвост":
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА())
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())
Функция ТДАТА (NOW) выдает текущую дату (с временем), а функция Т превращает эту дату в пустую текстовую строку, которая затем приклеивается к нашему имени листа с помощью оператора склейки (&). Т.е. имя листа, фактически, не меняется, но поскольку функция ТДАТА пересчитывается и выдает новое время и дату при любом изменении листа, то и остальная часть нашей формулы вынуждена будет заново пересчитаться тоже и – как следствие – имена листов будут обновляться постоянно.
И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (HYPERLINK) , которая будет формировать адрес для перехода из имени листа:
Способ 3. Макрос
И, наконец, для создания оглавления можно использовать и несложный макрос. Правда, запускать его придется каждый раз при изменении структуры книги - в отличие от Способа 2, макрос их сам не отслеживает.
Откройте редактор Visual Basic, нажав Alt+F11 или выбрав (в старых версиях Excel) в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor) . В открывшемся окне редактора создайте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этого макроса:
Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню Сервис - Макрос - Макросы (Tools - Macro - Macros) . Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление, как это было описано в Способе 1.
Работа в Excel не ограничивается одним листом.
Практически всегда файл состоит из нескольких листов.
Давайте разберёмся как сделать оглавление в Excel, чтобы было удобно работать с файлами из 10 и более листов?
Каждый раз сталкиваясь с книгой Microsoft Excel с большим количеством листов, невольно хочется иметь удобную навигацию по ней.
В этой статье мы рассмотрим каким образом можно создавать оглавление в Excel.
Обратимся к нашему примеру, его можно скачать по ссылке ниже.
Перейдём во вкладку Оглавление, в ней мы и будем создавать гиперссылки на каждый лист отдельно.
Для создания гиперссылки перейдём во вкладку Вставка и в группе Ссылка, выберем единственный пункт Гиперссылка.
После заполнения всех полей, нажимаем ОК, и мы получим первую рабочую Гиперссылку, при нажатии на которую мы переместимся на соответствующий Лист.
Вызвать меню создания Гиперссылки так же возможно более быстрым способом:
просто кликнув правой кнопкой мыши на ячейку, в которую Вы хотите поместить ссылку, и в появившемся меню в самом низу выбрать Гиперссылка,
далее заполняем всё, как делалось выше.
Создадим по тому же принципу Гиперссылки в Оглавлении для всех остальных Листов.
Выделим все листы, кроме листа с Оглавлением.
Для этого достаточно зажать клавишу Ctrl или Shift и кликнуть по каждому листу.
Гиперссылка назад в оглавление
Запишем следующую формулу,
После ввода формулы нажимаем комбинацию клавиш Ctrl + Enter, чтобы применить формулу ко всем выделенным листам.
Теперь на каждом листе у нас есть обратная Гиперссылка.
Мы можем перемещаться из Оглавления в любое место книги, и обратно.
Вот и всё.
Пробегитесь по вашему файлу и оцените насколько стало удобно.
Это особенно полезно для файлов с несколькими десятками листов, где можно очень легко потеряться, но только не с нашей навигацией.
Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.
Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:
Есть несколько способов реализовать подобное.
Видео
Способ 1. Создаваемые вручную гиперссылки
Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка — Гиперссылка (Insert — Hyperlink). В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод — функцию ГИПЕРССЫЛКА (HYPERLINK). Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:
Способ 2. Динамическое оглавление с помощью формул
Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK), оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.
Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:
Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:
=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())
Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.
Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND), которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку («»). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas — Name Manager), двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:
Теперь наш список листов будет выглядеть существенно лучше:
Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой «хвост»:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА())
=SUBST(GET.WORKBOOK(1);1;FIND(«]»;GET.WORKBOOK(1));»»)&T(NOW())
Функция ТДАТА (NOW) выдает текущую дату (с временем), а функция Т превращает эту дату в пустую текстовую строку, которая затем приклеивается к нашему имени листа с помощью оператора склейки (&). Т.е. имя листа, фактически, не меняется, но поскольку функция ТДАТА пересчитывается и выдает новое время и дату при любом изменении листа, то и остальная часть нашей формулы вынуждена будет заново пересчитаться тоже и – как следствие – имена листов будут обновляться постоянно.
И, наконец, для добавления к именам листов «живых» гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (HYPERLINK), которая будет формировать адрес для перехода из имени листа:
Способ 3. Макрос
И, наконец, для создания оглавления можно использовать и несложный макрос. Правда, запускать его придется каждый раз при изменении структуры книги — в отличие от Способа 2, макрос их сам не отслеживает.
Откройте редактор Visual Basic, нажав Alt+F11 или выбрав (в старых версиях Excel) в меню Сервис — Макрос — Редактор Visual Basic (Tools — Macro — Visual Basic Editor). В открывшемся окне редактора создайте новый пустой модуль (меню Insert — Module) и скопируйте туда текст этого макроса:
Закройте редактор Visual Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте меню Сервис — Макрос — Макросы (Tools — Macro — Macros). Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление, как это было описано в Способе 1.
Ссылки по теме
- Что такое макрос, как его создать, куда копировать текст макроса, как запустить макрос?
- Автоматическое создание оглавления книги одной кнопкой (надстройка PLEX)
- Отправка писем с помощью функции ГИПЕРССЫЛКА
- Быстрый переход между листами книги Excel
Способ 1. Создаваемые вручную гиперссылки
Вставьте в книгу пустой лист и добавьте на него гиперссылки на нужные вам листы, используя команду Вставка — Гиперссылка (Insert — Hyperlink). В открывшемся окне нужно выбрать слева опцию Место в документе и задать внешнее текстовое отображение и адрес ячейки, куда приведет ссылка:
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление. Чтобы не заниматься ручным созданием гиперссылок и копированием их потом на каждый лист, лучше использовать другой метод — функцию ГИПЕРССЫЛКА (HYPERLINK). Выделяем все листы в книге, куда хотим добавить обратную ссылку (для массового выделения листов можно использовать клавиши Shift и/или Ctrl) и в любую подходящую ячейку вводим функцию следующего вида:
Способ 2. Динамическое оглавление с помощью формул
Это хоть и слегка экзотический, но весьма красивый и удобный способ создания автоматического листа оглавления вашей книги. Экзотический – потому что в нем используется недокументированная XLM-функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (GET.WORKBOOK), оставленная разработчиками для совместимости со старыми версиями Excel. Эта функция выгружает список всех листов текущей книги в заданную переменную, из которой мы потом можем их извлечь и использовать в нашем оглавлении.
Откройте Диспетчер Имен на вкладке Формулы (Formulas – Name Manager) и создайте новый именованный диапазон с именем, допустим, Оглавление. В поле Диапазон (Reference) введите вот такую формулу:
Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:
=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())
Функция СТРОКА (ROW) выдает номер текущей строки и, в данном случае, нужна только для того, чтобы вручную не создавать отдельный столбец с порядковыми номерами извлекаемых элементов (1,2,3…). Таким образом, в ячейке А1 у нас получится имя первого листа, в А2 – имя второго и т.д.
Неплохо. Однако, как можно заметить, функция выдает не только имя листа, но и имя книги, которое нам не нужно. Чтобы его убрать, воспользуемся функциями ЗАМЕНИТЬ (SUBST) и НАЙТИ (FIND), которые найдут символ закрывающей квадратной скобки ( ] ) и заменят весь текст до этого символа включительно на пустую строку («»). Откроем еще раз Диспетчер имен с вкладки Формулы (Formulas — Name Manager), двойным щелчком откроем созданный диапазон Оглавление и изменим его формулу:
Теперь наш список листов будет выглядеть существенно лучше:
Небольшая побочная трудность заключается в том, что наша формула в именованном диапазоне Оглавление будет пересчитываться только при вводе, либо при принудительном пересчете книги нажатием на сочетание клавиш Ctrl+Alt+F9. Чтобы обойти этот неприятный момент, добавим к нашей формуле небольшой «хвост»:
=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()) =SUBST(GET.WORKBOOK(1);1;FIND(«]»;GET.WORKBOOK(1));»»)&T(NOW())
Функция ТДАТА (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 Basic и вернитесь в Excel. Добавьте в книгу чистый лист и поместите его на первое место. Затем нажмите Alt+F8 или откройте менюСервис — Макрос — Макросы (Tools — Macro — Macros). Найдите там созданный макрос SheetList и запустите его на выполнение. Макрос создаст на первом листе книги список гиперссылок с названиями листов. Щелчок по любой из них переместит Вас на нужный лист.
Для удобства можно создать также и обратные ссылки на всех листах вашей книги, которые будут вести назад в оглавление, как это было описано в Способе 1.
Способ мой. Мой вариант
т Название листа — =ЕСЛИОШИБКА(ЗАМЕНИТЬ(ИНДЕКС(Оглавление;СТРОКА()-3);1;НАЙТИ(«]»;ИНДЕКС(Оглавление;СТРОКА()-3));»»);»»)
Читайте также: