зміст
Якщо ви хоча б знайомі з функцією VPR (ВПР) (якщо ні, то спочатку запустіть сюди), то ви повинні розуміти, що ця та інші подібні їй функції (ПЕРЕГЛЯД, ІНДЕКС і ПОШУК, ВИБІР і т.д.) завжди видають в результаті значення – число, текст або дата, які ми шукаємо в даній таблиці.
Але що, якщо замість значення ми хочемо отримати живе гіперпосилання, натиснувши на яке, ми зможемо миттєво перейти до знайденого збігу в іншій таблиці, щоб переглянути його в загальному контексті?
Скажімо, у нас є велика таблиця замовлень для наших клієнтів як вхідні дані. Для зручності (хоча це і не обов'язково) я перетворив таблицю на динамічну «розумну» комбінацію клавіш Ctrl+T і дав на табл Конструктор (Дизайн) її ім'я вкладкаЗамовлення:
На окремому аркуші Консолідований Я побудував зведену таблицю (хоча це не обов'язково повинна бути саме зведена таблиця - в принципі підходить будь-яка таблиця), де за вихідними даними розраховується динаміка продажів по місяцях для кожного клієнта:
Давайте додамо стовпець до таблиці замовлень із формулою, яка шукає ім’я клієнта для поточного замовлення на аркуші Консолідований. Для цього ми використовуємо класичний пакет функцій ІНДЕКС (ІНДЕКС) и БІЛЬШ ЕКСПОЗИЦІЙНИЙ (МАТЧ):
Тепер давайте обернемо нашу формулу у функцію КЛЕТКА (КЛІТИНКА), яку ми попросимо вивести адресу знайденої комірки:
І, нарешті, все, що вийшло, ставимо у функцію ГІПЕРПОСИЛАННЯ (ГІПЕРПОСИЛАННЯ), який у Microsoft Excel може створити живе гіперпосилання на заданий шлях (адресу). Єдине, що неочевидно, так це те, що вам доведеться приклеїти до отриманої адреси знак решетки (#), щоб посилання коректно сприймалося Excel як внутрішнє (з аркуша на аркуш):
Тепер при натисканні на будь-яке з посилань ми миттєво переходимо до комірки з назвою компанії на аркуші зі зведеною таблицею.
Щоб це було дійсно добре, давайте трохи вдосконалимо нашу формулу, щоб перехід відбувався не до імені клієнта, а до конкретного числового значення саме в стовпці місяця, коли було виконано відповідне замовлення. Для цього треба пам'ятати, що функція ІНДЕКС (ІНДЕКС) в Excel є дуже універсальним і може використовуватися, серед іншого, у форматі:
= ІНДЕКС ( XNUMXD_діапазон; Номер_рядка; Номер_стовпця )
Тобто в якості першого аргументу ми можемо вказати не стовпець з назвами компаній у зведеній таблиці, а всю область даних зведеної таблиці, а в якості третього аргументу додати номер потрібного нам стовпця. Його легко обчислити за допомогою функції МІСЯЦЬ (МІСЯЦЬ), який повертає номер місяця для дати угоди:
Покращення 2. Гарний символ посилання
Другий аргумент функції ГІПЕРПОСИЛАННЯ – текст, який відображається в комірці з посиланням – можна зробити красивішим, якщо замість банальних знаків «>>» використовувати нестандартні символи зі шрифтів Windings, Webdings тощо. Для цього можна скористатися функцією СИМВОЛ (CHAR), який може відображати символи за їх кодом.
Так, наприклад, код символу 56 у шрифті Webdings дасть нам гарну подвійну стрілку для гіперпосилання:
Покращення 3. Виділіть поточний рядок і активну клітинку
Ну а для остаточної перемоги краси над здоровим глуздом можна також прикріпити до нашого файлу спрощений варіант підсвічування поточного рядка і комірки, на яку переходимо за посиланням. Для цього знадобиться простий макрос, який ми повісимо для обробки події зміни вибору на аркуші Консолідований.
Для цього клацніть правою кнопкою миші вкладку «Підсумок» і виберіть команду вид код (Погляд код). Вставте наступний код у вікно редактора Visual Basic, яке відкриється:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.ColorIndex = -4142 Cells(ActiveCell.Row, 1).Resize(1, 14).Interior.ColorIndex = 6 ActiveCell.Interior.ColorIndex = 44 End Sub
Як ви можете легко побачити, тут ми спочатку видаляємо заливку з усього аркуша, а потім заповнюємо весь рядок у підсумку жовтим (кольоровий код 6), а потім помаранчевим (код 44) поточну клітинку.
Тепер при виділенні будь-якої комірки в осередку зведення (неважливо – вручну чи в результаті кліку по нашому гіперпосиланню) буде виділено весь рядок і комірку з потрібним нам місяцем:
Краса 🙂
PS Тільки не забудьте зберегти файл у форматі з підтримкою макросів (xlsm або xlsb).
- Створення зовнішніх і внутрішніх посилань за допомогою функції HYPERLINK
- Створення електронних листів за допомогою функції HYPERLINK