Как создать оглавление книги Excel
Вставка
- Опубліковано 14 жов 2024
- Несколько способов создать оглавление книги в Excel с гиперссылками для быстрого перехода между листами. Подробное описание и файл с примером www.planetaexce...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru...
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru...
Это высший пилотаж. Огромное спасибо за видео!
Большое спасибо, Николай ! Даете очень полезные уроки !!!
Николай, Большое спасибо за уроки. на данный момент мне нужно срочно подтянуть знания по Excel.
Вовремя ваше видео на глаза попалось, спасибо ✨
Ух! Мощь! Скорость! Красота!
Спасибо большое! Очень доступно и понятно. Продолжайте создавать подобные обучающие видео :)
Большое спасибо за лекцию! очень круто!
Благодарю. Очень полезная информация.
Класс! Буду пробовать. СПАСИБО огромное!
Хотелось бы добавить, что если в именах листов есть пробелы, то приведенная в видео формула создания гиперссылки выдаст ошибку при нажатии на гиперссылку левой кнопкой мыши.
Чтобы ошибки не возникало, имя листа необходимо окружить одинарными кавычками (символ №39), а значит приведенную формулу стоит немного дополнить:
=ГИПЕРССЫЛКА("#"&СИМВОЛ(39)&A2&СИМВОЛ(39)&"!A1";">>>")
Хорошее видео и очень полезный канал, спасибо.
да, я тоже об этом написал, но символ одинарной кавычки не обязательно вставлять через функцию, можно же просто его указать в кавычках напечатав, как и другие символы )
Проблема перехода была если формулу написать как на видео. Сделал вставкой "СИМФОЛ(39)". все заработало. Спасибо
Спасибо большое! Хочу добавить, что в названии листа не должно быть пробела, дефиса или какого-либо знака препинания, т.е Т-34 нужно писать как Т34. Иначе, недопустимая ссылка!!!!
в функции ГИПЕРССЫЛКА("#'" & A2 & "'!A1";">>>") необходимо ссылку на А2 обрамить в одинарные кавычки, иначе будет возникать ошибка при ссылке на листы, в имени которых присутствуют разделительные пробелы (Имя листа может состоять из двух и более слов)
Вы все правильно написали! ) Но почему в приведенной Вами формуле Вы так не сделали? )
@@excel1818 Видно зрение меня подводит, но я вижу в своей формуле одинарные кавычки (после знака # и перед знаком !)
@@Midavok да, есть! ) присмотрелся тоже! ))
Спасибо! Почти час мучался из-за этого)))
Очень завораживающе!
Огромное спасибо за видео урок!Очень помог!
ГЕНИАЛЬНО!!!
Согласен, очень удобно и практично.
Очень полезное видео, а то не могу подступиться к задаче составления каталога оборудования по категориям и по месту нахождения с указанием всех характеристик аппататов. Очень много листов, очень много строк. А то старший фельдшер намучался уже. Теперь с такой структурой дело пойдёт быстрее.
Даже у меня, получилось! Спасибо!!!
Супер, лайк сто раз заслуженный
Класс!Единственное, чтобы в оглавление добавился новый лист его необходимо переименовать, либо внести какое-то изменение в ячейке, иначе он не добавляется автоматически
Спасибо вам огромное!!!
Николай можно попросить вас урок, с советами как не утежелять файлы. Большая проблема на работе, создают файлы, производят какие то манипуляции и они полупустые но при том весят 3 - 4 мб.
Очень классный способ! А как сделать, чтобы при нажатии на гиперссылку перемещался не в конкретную ячейку, а в последнюю заполненную строку выбранного листа?
это тоже можно сделать относительно просто, только нужно знать в каком столбце искать последнюю заполненную ячейку (хотя, возможно, под последней заполненной ячейкой, Вы подразумеваете, например, не как я - самую нижнюю, а ту, в которую внесли данные позже, чем с другие, если так, то в общем виде я не подскажу решение, это уже сложнее)
До сегодня дня делал вот так www.mrexcel.com/forum/excel-questions/63895-listing-all-different-worksheets-file.html но если VBA смущает, то
это очень хороший способ. Спасибо!!!
Спасибо! Очень помогли!
самое удобное переключение было бы, если когда можно было сохранять листы нажатием кнопки например CTRL+ 1, другую CTRL+2, и переключение между ними с этими кнопками, очень быстро))
я это из игры старкрафт 1 много юзал))
Ctrl+1 в Excel открывает окно "Формат ячеек" :)
супер. Спасибо!
Николай, добрый день! Спасибо за подробный урок! Файл сохранять нужно с макросами .xlsm, но после закрытия файла повторного открытия на листе оглавления вместо списка листов выходит значение #ИМЯ?. Саисок снова выходит только после повторного открытия диспетчера имен и запуска функции. Как можно это побороть? Office 2007
файл можно сохранить в формате xlsb и все будет работать )
Спасибо!
Очень круто, но возвращаться в лист с оглавлением с последних листов - это такое же листание листов.
Можно на других листах делать ссылку "К оглавлению".
Спасибо за идею
Николай, большое Вам спасибо за такие полезные ролики! По последнему варианту создания оглавления гиперссылки ( >>>>> ) не работают. Пишет не верная ссылка.Название листов состоит из нескольких слов. Удаляю пробелы не помогает.
=ГИПЕРССЫЛКА("#"&"'"&A2&"'"&"!A1";">>>") - должно быть так.
Добавлены два знака '.
Здорово! Всё получилось, только оказывается в название листа не должно быть пробелов и знаков "-" и "=" иначе гиперссылка не работает, пришлось звать коллегу на помощь 😊🙈
СПАСИБО!
Кстати да. Спасибо за уточнение :)
В названии листа не должно быть не только символов, которые Вы перечислили (имеется ввиду, чтобы работало так, как показал Николай), но также лист не должен начинаться с цифры и еще ряд символов влияют на то, как имя листа будет возвращено предлагаемой формулой.
Если, все же, есть потребность, чтобы можно было пользоваться гиперссылками, которые получены с помощью функции ГИПЕРССЫЛКА() и при этом хочется, чтобы имя листа можно было назвать произвольно (то есть с пробелами и тд), то нужно немного модифицировать формулу предлагаемую Николаем.
А именно нужно взять в одинарные кавычки название листа, то есть формула (если взять за основу формулу из видео) примет вид
=ГИПЕРССЫЛКА("#"&"'"&A2&"'"&"!A1";">>>")
или (то же самое без лишних &)
=ГИПЕРССЫЛКА("#'"&A2&"'!A1";">>>")
Супер!
Николай можете пожалуйста подсказать процедуру как можно создать в существующем листе прилегающий к этому листу еще один лист. Например в листе имеются фотографий или же названии и при щелке появляется другой лист где же находится подробная информация об этой картине или же названия. Другой вариант например если имеется лист при нажатия на этот лист появляется другой список листов прилегающий к этому листу. К сожалению не могу просмотреть видео в чистом виде. Заранее благодарю
Большое спасибо! Но введение функции ТДАТА() корректирует Оглавление только при удалении листов, их переименовании, переносе в другое место. При добавлении не срабатывает. В чем может быть проблема у меня? P.S. Проблему разрешила. Но причину не пойму. Если добавить лист и не переименовать его, в оглавление он попадает только после ручного обновления формулы. Если же лист переименовать, всё срабатывает)))
Хорошо бы в самих уроках или в описании приводить соответствие функций в русской версии английским. В частности, хотелось бы узнать, что за функция "Т" в английской версии.
Все функции и кнопки-команды меню я всегда дублирую для английской версии в сопроводительной статье к каждому видео. Ссылка на статью - в описании. Посмотрите.
@@planetaexcel, спасибо.
Пожалуйста
подскажите
как так сделать
чтобы нексколько листов внизу имели главный лист групы и прятались как бы в него
и при нажатии на этот один главный лист выезжали вправо подчиненные под главный лист листы
Спасибо помог!!
Добрый день николай, все получилась, но произошел один нюанс, Гиперссылка не работает если в названии книги есть пробел. как можно это условие исключить?
Хорошо! Ваш диапазон оглавления не терпит изменений, если вы сначала удалили, а потом вставили новый лист. А расширится ли автоматически оглавление, если будут добавляться всё новые и новые листы?
Нет, не расширится. А как сделать чтобы расширялся?
@@aofadeev030911 вручную протягивать формулу после добавления листов
Добрый день.
Подскажите пожалуйста как сделать что бы цвет ячейки изменялся с помощью логического оператора. Как обозначить желтый цвет в формуле?
Можно обойтись без гиперссылок, используя макрос в модуле листа оглавления:
---------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [A2:A5]) Is Nothing Then
If Selection.Count > 1 Then
[A1].Select
GoTo EndSub
End If
SheetName = Target.Value
[A1].Select
Sheets(SheetName).Activate
End If
EndSub:
End Sub
---------------------
Активация нужного листа будет осуществляться при нажатии ячейки. Диапазон A1:A10 можно сделать динамическим, чтобы он автоматически определял последнюю заполненную ячейку.
А как боролись с проблемой нескольких слов в имени листа? Мне приходится "_" проставлять, чтобы ниперссылка Error не выдавала
Тетяна Мікрюкова - скорее всего наплодились "призрачные" ячейки вплоть до нижней максимальной строки. Проверить это можно бегунком справа - попробуйте "схватить" и плавно просмотреть рабочий лист. Если "плавно" сделать чертовски сложно (момент неуловим!), то призрачные ячейки найдены! Мочим гадов так: опуститесь на несколько строк ниже всех видитмых таблиц, нажмите на номер пустой строки слева, затем протяните бегунок справа вниз до упора и (удерживая shift) - выделите аналогично номер самой последней пустой строки (увидите её номер - не пугайтесь), затем нажмите правой кнопкой на этот же номер (либо любой другой номер строки) и в появившемся меню выбирайте "удалить". затем сохраняетесь! и проверяемся на размер. уосьо) Вохможно сложно описал - надутый после работы сижу)
За способ удаления - спасибо огромное, потренируюсь! Но хотелось бы еще понять какие действия вызывают появление данных призрачных ячеек, что бы не исправлять а предотвратить, как то так :)
+Тетяна Мікрюкова Предотвратить никак. Так работает эксель или люди в нём. Поэтому перед сохранением книги желательно проверять границы таблицы на листе нажатием Ctrl+End, курсор встанет на последнюю ячейку и если перед ней будут пустые строчки и/или столбцы, то можно смело их удалить, предварительно проверив на отсутствие данных в верхних строчках (для столбцов) и левых столбцах (для строчке), нажатием соответственно клавиш Ctrl+Up и Ctrl+Left.
Либо поступить ещё проще (при обнаружении пустых диапазонов). Перейти в конец таблицы (последняя ячейка данных) и, выделив до конца все строчки и столбцы удалить их. При сохранении размер книги может существенно уменьшиться.
А возможен переход и открывание листа, если они все скрыты? Например есть 10 листов, но они скрыты. Делается лист "оглавление" и путем нажатия, нужный лист, 5й или 8й, открывается и становится видимым.
Добрый день, есть простой вопрос с которым не могу справится, гугл что то толком не смог помочь
Надо сделать оглавление на первом листе с ссылками на определенные ячейки на втором листе.
И реализовать это так что бы при смещении ячейки на которую вела ссылка, ссылка перепривязывалась к изначальной ячейки ?
Сохраняю в Двоичной книге Excel, а при открытии ссылки на имя вкладок и гиперссылки - #ИМЯ?. Неужели у всех получается, а у меня нет. Подскажите.
Занимательное и познавательное видео. Только кому это нужно на практике? Разве что для отчётов (было у меня пару раз, необходимо было составить список листов для финального отчёта). В реальности обхожусь банальным перелистыванием с клавишей Ctrl. :) Спасибо за урок!
Очень много категорий оборудования и единиц оборудования на которые много параметров: модель, производитель, инвентарник, заводской номер, дата ввода в эксплуатацию, стоимость, место нахождения или хранения, поверка и т.д.
я еще чуть доработал. удобно было если книга открывается с листа оглавления. макрос:
Private Sub Workbook_Open()
' открытие листа при открытии книги
Sheets("оглавление").Activate
End Sub
+Игорь Анатольевич И что Вы доработали? При добавлении листов Вам придется постоянно дописывать
макрос:
Private Sub Workbook_Open()
Sheets("оглавление").Activate
Sheets("Москва ").Activate
End Sub
и т.д.
+Игорь Анатольевич Я Макрос малость не правильно прописал. Я думаю смысл понятен.
Giorgio Armani проблем не было никаких. По этому не понимаю о чем вы
Добрый день. А почему в одном документе получилось применить данный метод, а в другом нет. Вместо нужного имени листа выдает следующее- #ИМЯ?
неплохо, но лучше когда на каждом листе автоматически добавляется поле с выпадающим списком листов, таким образом с любого листа можно переходить на любой лист, но твой вариант для дилетантов конечно подойдет
=ГИПЕРССЫЛКА("#"&A2&"!A1";">>>")
Объясните, пожалуйста, что означает знак решетка “#” в формуле =ГИПЕРССЫЛКА("#"&A2&"!A1";">>>"), а также более подробно и обо всех других знаках в формуле.
хочу сделать оглавление в 3 колонки, вставляю правильное значение индекса, но не работает ссылки во 2ой и 3 колонке, что может быть не так?
У кого-нибудь была такая проблема, после написании гиперссылки точь в точь как Сделал Николай, появляется ошибка "недопустимая ссылка". Помогите с проблемой пожалуйста
пока не приклеил одинарные кавычки к названию листов в гиперссылке ничего не работало
Как закрепить лист в списке листов, чтобы остальные прокручивались - а этот, с оглавлением, был всегда виден?
Никак, к сожалению. Удобная была бы фича :(
Можно совместить в одном столбце имена и ссылку
=ГИПЕРССЫЛКА("#'"&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА())&"'";"")&"!A1"; ""&ЕСЛИОШИБКА(ИНДЕКС(ОГЛАВЛЕНИЕ;СТРОКА());""))
А как сделать чтобы список начинался с заданной ячейки, а не в A1, B1 ...
как это "с заданной ячейки"? просто там сделано оглавление, которое формирует перечень всех листов кроме самого листа с именем "оглавление" (этот лист слева направо идет первым), и, чтобы "скормить" функции ИНДЕКС() номер строки, Николай использовал функцию СТРОКА() прописав ее во второй строке, соответственно, что и привело к тому, что листы (имена листов) выводятся подряд начиная со второго по счету. А как нужно Вам? )
Нет нет нет! Постановка задачи была -- листов МНОГО. Хорошо, перепрыгнули на нужный лист, тоже так и делаем. А НАЗАД то как? Я обычно клею в угле гиперссылку "вернуться в оглавление" на каждом листе. Но в контексте задачи из примера проще действительно получать список страниц средствами эксель (рклик). Хотя получение ссылок получилось, молодец!
на вопрос "как назад" есть простой ответ: просто надо на панель быстрого доступа повесить одну кнопочку, которая, собственно и называется "Назад" ))
Наверное, это хорошее решение. Подскажите, пожалуйста, как приклеить гиперссылки "назад в оглавление" на каждом листе, если их порядка 100? Если выделить все листы, то кнопка "гиперссылка" становится не активной. Неужели вручную???
Хелп!!! кто подскажет??? В гиперссылке после ; в роли имени указываю содержимое конкретной ячейки ( ;'имя листа'!$W$3 ) и у меня вместо >>> или КНОПКА показываеся содержимое конкретной ячейки. Все супер! НО задача размножить,протянуть эту гиперссылку на много ячеек. КАК прописать имя листа чтобы оно менялось при протягивании? пол дня бьюсь, никак не получается.
При удалении листа символы >>> остаются!
Почему гиперссылка не работает? Не могу понять
Не получилось без искажений перенести :( может из-за того что много формул было
У, шайтан!