Быстрое создание итоговой таблицы с гиперссылками в Excel

Многие мои коллеги тратят много времени на создание сводной таблицы в Excel.

Типичный итоговый рабочий лист содержит имена всех рабочих листов в разных ячейках, и все имена также имеют гиперссылки на эти рабочие листы.

Таким образом, вы можете щелкнуть ячейку с именем листа (скажем, январь, февраль, март… ), и вы перейдете к этому рабочему листу. Кроме того, на каждом листе есть гиперссылка, которая ведет на итоговый лист.

Хотя мои коллеги стали очень эффективными в этом, это все еще пустая трата времени, когда вы можете сделать то же самое менее чем за секунду (да, вы все правильно прочитали).

Уловка состоит в том, чтобы создать короткий макрос, который сделает это за вас.

Независимо от того, сколько листов у вас есть, он мгновенно создаст итоговый рабочий лист с рабочими гиперссылками.

Что-то вроде того, что показано ниже:

Как вы можете видеть на изображении выше, он мгновенно создает сводку, когда вы запускаете макрос (нажав на кнопку). Имена листов имеют гиперссылку, по которой вы переходите к рабочему листу, когда вы щелкаете по нему.

Создать итоговый лист с гиперссылками

Вся тяжелая работа по созданию сводного рабочего листа выполняется с помощью короткого кода VBA. Вам просто нужно запустить код и сделать перерыв, так как теперь у вас будет немного свободного времени 🙂

Вот код:

Sub CreateSummary () 'Created by Sumit Bansal of trumpexcel.com' Этот код можно использовать для создания сводного рабочего листа с гиперссылками Dim x As Worksheet Dim Counter As Integer Counter = 0 For Each x In Worksheets Counter = Counter + 1 If Counter = 1 Затем GoTo ничего не делать с ActiveCell .Value = x.Name .Hyperlinks.Add ActiveCell, "", x.Name & "! A1", TextToDisplay: = x.Name, ScreenTip: = "Щелкните здесь, чтобы перейти к рабочему листу" с рабочими листами (Counter) .Range ("A1"). Value = "Back to" & ActiveSheet.Name .Hyperlinks.Add Sheets (x.Name) .Range ("A1"), "", _ "'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _ ScreenTip: = "Вернуться в" & ActiveSheet.Name End With End With ActiveCell.Offset (1, 0). Выберите Ничего не делать: Далее x End Sub 

Куда положить этот код?

Выполните следующие действия, чтобы поместить этот код в книгу:

1. Перейдите на вкладку разработчика и нажмите Visual Basic. Вы также можете использовать сочетание клавиш - ALT F11.
   • Если вы можете найти вкладку разработчика на ленте в Excel, щелкните здесь, чтобы узнать, как ее получить.
2. Слева должна быть панель Project Explorer (если ее нет, используйте Control + R, чтобы сделать ее видимой).
3. Перейдите во вкладку "Вставить" и щелкните в модуле. Это добавляет модуль в книгу. Кроме того, справа вы увидите окно кода (с мигающим курсором).
4. В окне кода модуля скопируйте и вставьте приведенный выше код.

Запуск кода

Чтобы запустить этот код:

• Перейдите на вкладку разработчика -> Код -> Макросы. Это откроет диалоговое окно макроса.
• Выберите макрос CreateSummary и нажмите «Выполнить».
• Это запустит макрос и создаст гиперссылки на активном листе.

Другой способ запустить макрос - вставить кнопку / фигуру и назначить ей макрос. Сделать это:

• Вставьте фигуру на лист. Отформатируйте фигуру так, как хотите.
• Щелкните его правой кнопкой мыши и выберите «Назначить макрос».
• В поле «Назначить макрос» выберите макрос, который нужно назначить фигуре, и нажмите «ОК».

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

Загрузите файл отсюда

Примечание:

1. Я жестко запрограммировал ячейку A1 на каждом листе, на которую есть гиперссылка, чтобы вы вернулись к сводной таблице. Убедитесь, что вы изменили его соответствующим образом, если у вас уже есть что-то в ячейке A1 на каждом листе.
2. Резюме не создает для себя гиперссылки (что имеет смысл, поскольку вы уже находитесь на этом листе).
3. Запустите этот код, когда итоговый рабочий лист является активным.
4. Вы можете захотеть добавить форматирование или перестановку. Но я надеюсь, что этот код позаботится о самой сложной части.
5. Сохраните эту книгу с расширением .xls или .xlsm, поскольку она содержит макрос.

Другие руководства по Excel VBA:

• Получите несколько значений поиска без повторения в одной ячейке.
• Матрица приоритезации задач - приложение VBA.
• Как объединить несколько книг в одну книгу Excel.
• Циклы Excel VBA - для «Далее», «Сделать пока», «Сделать до», «Для каждого» (с примерами).
• Как записать макрос - пошаговое руководство.
• Как быстро удалить гиперссылки с листа в Excel.
• Онлайн-курс Excel VBA.