Структуровані посилання в Excel | Покрокове керівництво з прикладами

Як створити структуровані посилання в Excel?

Структуровані посилання починаються з таблиць Excel. Як тільки таблиці, створені в Excel, він автоматично створює структуровані посилання для вас.

Тепер погляньте на зображення нижче.

  • Крок 1: Я дав посилання на комірку B3, замість того, щоб показувати посилання як B2, воно відображається як Таблиця1 [@ Продажі]. Тут Table1 - це назва таблиці, а @Sales - стовпець, на який ми маємо на увазі. Усі комірки цього стовпця посилаються на ім'я таблиці, а потім - ім'я заголовка стовпця.
  • Крок 2: Тепер я зміню ім'я таблиці на Data_Table та заголовок стовпця на Сума .
  • Крок 3: Для того, щоб змінити назву таблиці, встановіть курсор всередині таблиці> перейдіть до пункту Дизайн> Назва таблиці.

  • Крок 4: Згадайте назву таблиці як Data_Table.

  • Крок 5: Тепер зміна дасть посилання на клітинку B3.

Отже, ми зрозуміли, що структуроване посилання має дві частини Назва таблиці та Назва стовпця.

Приклади

Ви можете завантажити цей шаблон Excel зі структурованими посиланнями - Шаблон Excel зі структурованими посиланнями

Приклад №1

Використовуючи структуровані посилання, ви можете зробити свою формулу динамічною. На відміну від звичайних посилань на клітинки, це дозволяє формулі працювати в разі додавання та видалення в діапазоні даних.

Дозвольте застосувати формулу SUM як для нормального діапазону, так і для таблиці Excel.

Формула SUM для нормального діапазону.

Формула SUM для таблиці Excel.

Дозвольте мені додати кілька рядків до даних як звичайної, так і таблиці Excel. Я додав до даних 2 рядки-позиції, тепер подивіться різницю.

Структуроване посилання в таблиці Excel показує оновлене значення, але нормальний діапазон даних не відображає оновлені значення, якщо ви не внесете деякі зміни до формули вручну.

Приклад №2

А тепер погляньмо на ще один приклад. У мене є інформація про назву, кількість та ціну. Використовуючи цю інформацію, мені потрібно визначити вартість продажу.

Для того, щоб отримати вартість реалізації, формулою є Кількість * Ціна . Давайте застосуємо цю формулу в таблиці.

Формула каже [@QTY] * [@PRICE]. Це зрозуміліше, ніж звичайне посилання на B2 * C2. Ми не отримуємо назви таблиці, якщо ми розміщуємо формулу всередині таблиці.

Проблеми зі структурованими посиланнями в Excel

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

Проблема No1

У структурованих посилань також є свої проблеми. Ми всі знайомі із застосуванням формули Excel та копіюванням або перетягуванням її до інших комірок, що залишилися. Це не той самий процес у структурованих посиланнях, він працює дещо інакше.

Тепер подивіться на приклад нижче. Я застосував формулу SUM у Excel для нормального діапазону.

Якщо я хочу підсумувати ціну та вартість продажу, я просто скопіюю та вставлю або перетягну поточну формулу в інші дві клітинки, і це дасть мені СУММУ значення Ціна та вартість продажу.

Тепер застосуйте ту саму формулу для таблиці Excel для стовпця Кількість.

Тепер ми отримали суму стовпця Кількість. Як і звичайний діапазон, формула скопіюйте поточну формулу та вставте її у стовпець Ціна, щоб отримати загальну ціну.

Боже мій!!! Він не відображає загальну кількість стовпців Ціна, а все ще відображає лише загальну кількість стовпців Кількість. Отже, ми не можемо скопіювати та вставити цю формулу в сусідню комірку або будь-яку іншу комірку для посилання на відповідний стовпець або рядок.

Перетягніть формулу, щоб змінити посилання

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

Рішення дуже просте, нам просто потрібно перетягнути формулу замість копіювання. Виділіть клітинку формули та скористайтеся ручкою заповнення та перетягніть її до решти двох комірок, щоб змінити посилання на стовпець на Ціна та Значення продажу.

Тепер ми оновили формули, щоб отримати відповідні підсумки.

Проблема No2

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

Давайте подивимось на приклад нижче. У мене є таблиця продажів із кількома записами, і я хочу консолідувати дані за допомогою функції SUMIF в Excel.

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

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

О !! Я не отримав жодних значень у стовпці Feb & Mar. У чому б проблема ??? Уважно розгляньте формулу.

Ми перетягнули формулу з січня місяця. У функції SUMIF першим аргументом є Criteria Range Sales_Table [Product],  оскільки ми перетягнули формулу, яка має зміни, до Sales _Table [Jan].

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

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

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

Порада професіонала: Щоб зробити РЯД абсолютним посиланням, нам потрібно зробити подвійний РЯД, але перед іменем РЯД потрібно поставити символ @.

= Таблиця продажів [@ [Продукт]: [Продукт]]

Як вимкнути структуровану довідку в Excel?

Якщо ви не любите структуровані посилання, ви можете вимкнути їх, виконавши наведені нижче дії.

  • Крок 1: Перейдіть до ФАЙЛ> Параметри.
  • Крок 2: Формули> Зніміть прапорець Використовувати назви таблиць у формулах.

Що слід пам’ятати

  • Для того, щоб зробити абсолютне посилання в структурованому посиланні, нам потрібно подвоїти ім'я стовпця.
  • Ми не можемо скопіювати формулу структурованого посилання, натомість нам потрібно перетягнути формулу.
  • Ми не можемо точно побачити, до якої комірки ми маємо на увазі структуровані посилання.
  • Якщо вас не цікавлять структуровані посилання, ви можете їх відключити.