✅ Швидко створюйте зведений аркуш за допомогою гіперпосилань у Excel

Зміст

Багато моїх колег витрачають багато часу на створення зведеного аркуша в Excel.

Типовий зведений аркуш містить назви всіх аркушів у різних осередках, і всі імена також мають гіперпосилання на ці аркуші.

Таким чином, ви можете натиснути на клітинку з назвою аркуша (скажімо січень, лютий, березень … ), і він перенесе вас на цей аркуш. Крім того, на кожному аркуші також є гіперпосилання, яке посилається на підсумковий аркуш.

Хоча мої колеги стали надзвичайно ефективними у цьому, це все одно марно витрачений час, коли ти можеш зробити те ж саме менш ніж за секунду (так, ти правильно прочитав).

Хитрість полягає в тому, щоб створити короткий макрос, який зробить це за вас.

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

Щось, як показано нижче:

Як ви можете бачити на зображенні вище, він миттєво створює підсумок під час запуску макросу (натисканням на кнопку). Назви аркушів мають гіперпосилання, що переводить вас на аркуш, коли ви натискаєте на нього.

Створіть зведений аркуш за допомогою гіперпосилань

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

Ось код:

Sub CreateSummary () 'Створено Sumit Bansal з trumpexcel.com' Цей код може бути використаний для створення підсумкового робочого аркуша з гіперпосиланнями Dim x як робочий аркуш Dim Counter як лічильник цілих чисел = 0 для кожного x у лічильнику Counter = Counter + 1 If Counter = 1 Потім GoTo Donothing With ActiveCell .Value = x.Name .Hyperlinks.Add ActiveCell, "", x.Name & "! A1", TextToDisplay: = x.Name, ScreenTip: = "Натисніть тут, щоб перейти до робочого аркуша" З аркушами (Лічильник) .Range ("A1"). Value = "Назад до" & ActiveSheet.Name .Hyperlinks.Add Sheets (x.Name) .Range ("A1"), "", _ "'" & ActiveSheet.Name & "'" & "!" & ActiveCell.Address, _ ScreenTip: = "Повернутися до" & ActiveSheet.Name Закінчити за допомогою End з ActiveCell.Offset (1, 0). Виберіть Donothing: Next x End Sub

Де розмістити цей код?

Щоб розмістити цей код у робочій книзі, виконайте наведені нижче дії.

Перейдіть на вкладку Розробник і натисніть на Visual Basic. Ви також можете скористатися комбінацією клавіш - ALT F11.
- Якщо ви можете знайти вкладку розробника на стрічці в Excel, натисніть тут, щоб дізнатися, як її отримати.
Ліворуч повинна бути панель Провідника проектів (якщо її немає, використовуйте Control + R, щоб зробити її видимою).
Перейдіть до пункту Вставити та натисніть в модулі. Це додає модуль до книги. Також праворуч ви побачите вікно коду (з миготливим курсором).
У вікні коду модуля скопіюйте та вставте вищевказаний код.

Запуск коду

Щоб запустити цей код:

Перейдіть на вкладку Розробник -> Код -> Макроси. Відкриється вікно діалогу макросів.
Виберіть Резюме створення макросу та натисніть Виконати.
Це запустить макрос і створить гіперпосилання в активному аркуші.

Інший спосіб запустити макрос - вставити кнопку/форму та призначити їй макрос. Зробити це:

Вставте фігуру в аркуш. Форматуйте форму так, як вам хочеться.
Клацніть на ньому правою кнопкою миші та виберіть Призначити макрос.
У полі Призначити макрос виберіть макрос, який потрібно призначити фігурі, і натисніть OK.

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

Завантажте файл звідси

Примітка:

Я жорстко закодував клітинку А1 на кожному аркуші, яка має гіперпосилання, щоб повернути вас до зведеного аркуша. Переконайтесь, що ви відповідно змінили це, якщо у вас є щось у комірці А1 на кожному аркуші.
Резюме не створює для себе гіперпосилання (що має сенс, оскільки ви вже на цьому аркуші).
Запустіть цей код, коли підсумковий аркуш є активним аркушем.
Можливо, ви захочете додати деяке форматування або перестановку. Але я сподіваюся, що цей код вирішить важку частину.
Збережіть цю книгу як розширення .xls або .xlsm, оскільки вона містить макрос.

Інші підручники Excel VBA:

Отримайте декілька значень пошуку без повторення в одній клітинці.
Матриця пріоритетності завдань - додаток VBA.
Як об’єднати кілька робочих книг в одну книгу Excel.
Цикли Excel VBA - для наступного, Do While, Do Until, для кожного (з прикладами).
Як записати макрос у - Покроковий посібник.
Як швидко видалити гіперпосилання з робочого аркуша в Excel.
Онлайн курс VBA Excel.