Как сделать макрос одной кнопкой в excel



Как сделать макрос одной кнопкой в excel
Как сделать макрос одной кнопкой в excel

Если в вашей рабочей книге Excel число листов перевалило за второй десяток, то навигация по листам начинает становится проблемой. Одним из красивых способов ее решения является создание листа-оглавления с гиперссылками, ведущими на соответствующие листы книги:

toc1.png

Есть несколько способов реализовать подобное.

Видео

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

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

toc2.png

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

=ГИПЕРССЫЛКА("#Оглавление!A1";"Назад в оглавление")

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

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

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

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

toc3.png

=ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1)
=GET.WORKBOOK(1)

Теперь в переменной Оглавление содержатся наши искомые имена. Чтобы извлечь их оттуда на лист, можно воспользоваться функцией ИНДЕКС (INDEX), которая «выдергивает» элементы из массива по их номеру:

=ИНДЕКС(Оглавление; СТРОКА())
=INDEX(Оглавление; ROW())

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

toc4.png

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

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")

Теперь наш список листов будет выглядеть существенно лучше:

toc5.png

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

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ("]";ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));"")&Т(ТДАТА())
=SUBST(GET.WORKBOOK(1);1;FIND("]";GET.WORKBOOK(1));"")&T(NOW())

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

Для скрытия ошибок #ССЫЛКА (#REF), которые будут появляться, если скопировать нашу формулу с функцией ИНДЕКС на большее количество ячеек, чем у нас есть листов, можно использовать функцию ЕСЛИОШИБКА (IFERROR), которая  перехватывает любые ошибки и заменяет их на пустую строку (""):

toc6.png

И, наконец, для добавления к именам листов "живых" гиперссылок для быстрой навигации, можно использовать все ту же функцию ГИПЕРССЫЛКА (HYPERLINK), которая будет формировать адрес для перехода из имени листа:

toc7.png

Способ 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.

Ссылки по теме

 




Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Как сделать макрос одной кнопкой в excel

Похожие новости:


  • Ликёры в домашних условиях с фото
  • Цефтриаксон схема уколов при сифилисе
  • Открытка мы против курения
  • Звонок на улице как сделать
  • Схема знаков зодиака близнецы