Многие мои коллеги тратят много времени на создание сводной таблицы в 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
Куда положить этот код?
Выполните следующие действия, чтобы поместить этот код в книгу:
- Перейдите на вкладку разработчика и нажмите Visual Basic. Вы также можете использовать сочетание клавиш - ALT F11.
- Если вы можете найти вкладку разработчика на ленте в Excel, щелкните здесь, чтобы узнать, как ее получить.
- Если вы можете найти вкладку разработчика на ленте в Excel, щелкните здесь, чтобы узнать, как ее получить.
- Слева должна быть панель Project Explorer (если ее нет, используйте Control + R, чтобы сделать ее видимой).
- Перейдите во вкладку "Вставить" и щелкните в модуле. Это добавляет модуль в книгу. Кроме того, справа вы увидите окно кода (с мигающим курсором).
- В окне кода модуля скопируйте и вставьте приведенный выше код.
Запуск кода
Чтобы запустить этот код:
- Перейдите на вкладку разработчика -> Код -> Макросы. Это откроет диалоговое окно макроса.
- Выберите макрос CreateSummary и нажмите «Выполнить».
- Это запустит макрос и создаст гиперссылки на активном листе.
Другой способ запустить макрос - вставить кнопку / фигуру и назначить ей макрос. Сделать это:
- Вставьте фигуру на лист. Отформатируйте фигуру так, как хотите.
- Щелкните его правой кнопкой мыши и выберите «Назначить макрос».
- В поле «Назначить макрос» выберите макрос, который нужно назначить фигуре, и нажмите «ОК».
Теперь вы можете просто щелкнуть фигуру, чтобы запустить макрос.
Загрузите файл отсюда
Примечание:
- Я жестко запрограммировал ячейку A1 на каждом листе, на которую есть гиперссылка, чтобы вы вернулись к сводной таблице. Убедитесь, что вы изменили его соответствующим образом, если у вас уже есть что-то в ячейке A1 на каждом листе.
- Резюме не создает для себя гиперссылки (что имеет смысл, поскольку вы уже находитесь на этом листе).
- Запустите этот код, когда итоговый рабочий лист является активным.
- Вы можете захотеть добавить форматирование или перестановку. Но я надеюсь, что этот код позаботится о самой сложной части.
- Сохраните эту книгу с расширением .xls или .xlsm, поскольку она содержит макрос.
Другие руководства по Excel VBA:
- Получите несколько значений поиска без повторения в одной ячейке.
- Матрица приоритезации задач - приложение VBA.
- Как объединить несколько книг в одну книгу Excel.
- Циклы Excel VBA - для «Далее», «Сделать пока», «Сделать до», «Для каждого» (с примерами).
- Как записать макрос - пошаговое руководство.
- Как быстро удалить гиперссылки с листа в Excel.
- Онлайн-курс Excel VBA.
