Використання структурованих посилань із таблицями Excel

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

Замість явних посилань на клітинки	У програмі Excel використовуються імена таблиць і клітинок
=SUM(C2:C7)	=SUM(ЗбутВідділу[Обсяг збуту])

Таке поєднання імен таблиць і стовпців називається структурованим посиланням. Імена в структурованих посиланнях коригуються, якщо додати або видалити дані в таблиці.

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

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

Торговий представник	Регіон	Обсяг збуту	Комісія, %	Сума комісії
Петро	Північ	260	10 %
Роман	Південь	660	15 %
Ірина	Схід	940	15 %
Остап	Захід	410	12 %
Орися	Північ	800	15 %
Ростислав	Південь	900	15 %

1. Скопіюйте зразок даних у таблиці вище, включно із заголовками стовпців, і вставте його в клітинку A1 нового аркуша Excel.
2. Щоб створити таблицю, виберіть будь-яку клітинку в діапазоні даних і натисніть клавіші Ctrl+T.
3. Переконайтеся, що прапорець "Таблиця із заголовками" встановлено, і натисніть кнопку "OK".
4. Введіть знак рівності (=) у клітинці E2 та виділіть клітинку C2.
   У рядку формул після знака рівності з’явиться структуроване посилання [@[Обсяг збуту]].
5. Введіть зірочку (*) одразу після правої дужки та виділіть клітинку D2.
   У рядку формул після зірочки з’явиться структуроване посилання @[Комісія, %].
6. Натисніть клавішу Enter.
   У програмі Excel буде автоматично створено обчислюваний стовпець, а в кожну його клітинку буде вставлено формулу, адаптовану для кожного рядка.

Наслідки використання явних посилань на клітинки

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

1. На зразку аркуша виділіть клітинку E2
2. У рядку формул введіть =C2*D2 і натисніть клавішу Enter.

Зверніть увагу, якщо програма Excel копіює формулу по всьому стовпці, структуровані посилання не використовуються. Якщо, наприклад, ви додасте стовпець між наявними стовпцями C та D, формулу потрібно буде змінити.

Змінення імені таблиці

Таблиця, створена в програмі Excel, отримує ім’я за замовчуванням ("Taблиця1", "Taблиця2" тощо), однак його можна змінити на змістовніше.

1. Клацніть будь-яку клітинку в таблиці, щоб на стрічці відобразилася вкладка " Конструктор таблиць ".
2. Введіть потрібне ім'я в полі " Ім'я таблиці " та натисніть клавішу Enter.

У даних нашого зразка використано назву "ЗбутВідділу".

Дотримуйтеся цих правил, призначаючи імена таблицям:

• Використовувати припустимі символи Ім'я має завжди починатися з букви, символу підкреслення (_) або зворотної скісної риски (\). Для решти імені можна використовувати букви, цифри, крапки та символи підкреслення. В імені не можна використовувати символи С, с, R і r, оскільки вони вже використовуються як ярлики – якщо ввести їх у полі "Ім'я або перехід ", буде вибрано стовпець або рядок активної клітинки.

• Не використовуйте посилання на клітинки Імена не можуть бути ідентичні посиланням на клітинки, наприклад Z$100 або R1C1.
• Не розділяйте слова пробілами Пробіли в імені використовувати не можна. Для розділення слів можна використовувати символ підкреслення (_) і крапку (.). Наприклад, "ЗбутВідділу", "Sales_Tax" або "Перший.квартал".
• не використовуйте більше 255 символів ; Ім'я таблиці може містити до 255 символів.
• Використання унікальних імен таблиць Повторювані імена не допускаються. Excel не розрізняє регістр між буквами верхнього та нижнього регістрів, тому, якщо в одній книзі ви ввели слово "Продаж", але вже маєте інше ім'я – "ПРОДАЖ", буде запропоновано вибрати унікальне ім'я.
• Використання ідентифікатора об'єкта Якщо ви плануєте поєднувати таблиці, зведені таблиці та діаграми, радимо додавати до імен префікс типу об'єкта. Наприклад: tbl_Sales для таблиці збуту, pt_Sales для зведеної таблиці збуту, chrt_Sales для діаграми продажів або ptchrt_Sales для зведеної діаграми збуту. Усі ваші імена буде збережено в упорядкованому списку в диспетчері імен.

Правила синтаксису для структурованих посилань

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

=SUM(ЗбутВідділу[[#Підсумки],[Обсяг збуту]],ЗбутВідділу[[#Дані],[Сума комісії]])

Ця формула містить нижченаведені компоненти структурованого посилання:

• **Ім'я таблиці:**ЗбутВідділу – це спеціальне ім'я таблиці. Воно посилається на дані таблиці, крім рядків заголовків і підсумку. Можна залишити ім’я таблиці за замовчуванням, наприклад "Таблиця1", або змінити його на власне.
• Визначник стовпця:[Обсяг збуту] та [Сума комісії] – це визначники стовпців, у яких використовуються імена відповідних стовпців. Вони посилаються на дані стовпця, крім його рядка заголовка й підсумку. Завжди вказуйте визначники у квадратних дужках, як показано тут.
• Визначник елемента:[#Totals] та [#Data] – це визначники спеціальних елементів, які стосуються певних частин таблиці, наприклад рядка підсумків.
• Визначник таблиці:[[#Підсумки],[Обсяг продажів]] і [[#Дані],[Сума комісії]] – це визначники таблиці, які відповідають зовнішнім частинам структурованого посилання. Зовнішні посилання розташовуються після імені таблиці та беруться у квадратні дужки.
• Структуроване посилання:(DeptSales[[#Totals],[Обсяг продажів]] і DeptSales[[#Data],[Commission Amount]] – це структуровані посилання, представлені рядком, що починається з імені таблиці та закінчується визначником стовпця.

Щоб створити або змінити структуровані посилання вручну, дотримуйтеся цих правил синтаксису:

• Беріть визначники у квадратні дужки. Усі визначники таблиць, стовпців і спеціальних елементів необхідно брати в парні квадратні дужки ([ ]). Визначник, який містить інші визначники, слід ще раз взяти в дужки, які охоплюють внутрішні дужки інших визначників. Наприклад: =ЗбутВідділу[[Продавець]:[Регіон]].
• Усі заголовки стовпців – це текстові рядки. Але вони не потребують лапок, коли використовуються в структурованому посиланні. Числа й дати, наприклад 2014 або 01.01.2014, також вважаються текстовими рядками. Не можна використовувати вирази із заголовками стовпців. Наприклад, вираз ЗбутВідділуФінРікЗведення[[2014]:[2012]] не працюватиме.

Беріть заголовки стовпців зі спеціальними символами у квадратні дужки . Якщо в заголовку стовпця є спеціальні символи, його потрібно повністю взяти у квадратні дужки, тобто визначник стовпця має містити подвійні квадратні дужки. Наприклад: =ЗбутВідділуФінРікЗведення[[Загальна сума в ₴]].

Ось список спеціальних символів, для яких у формулі потрібні додаткові квадратні дужки:

• Клавіша табуляції
• Символ переведення рядка
• Повернення каретки
• Кома (,)
• Двокрапка (:)
• Крапка (.)
• Ліва квадратна дужка ([)
• Права квадратна дужка (])
• Решітка (#)
• Одинарна лапка (')
• Подвійні лапки (")
• Ліва фігурна дужка ({)
• Права фігурна дужка (})
• Знак долара ($)
• Символ "кришка" (^)
• Амперсанд (&)
• Зірочка (*)
• Знак "плюс" (+)
• Знак рівності (=)
• Знак "мінус" (-)
• Знак "більше" (>)
• Знак "менше" (<)
• Знак ділення (/)
• Равлик (@)
• Обернена скісна риска (\)
• Знак оклику (!)
• Відкриваюча дужка ()
• Закриваюча дужка ())
• Знак відсотка (%)
• Знак питання (?)
• Зворотна галочка (')
• Крапка з комою (;)
• Тильда (~)
• Підкреслення (_)

• Використовуйте символ виходу для деяких спеціальних символів у заголовках стовпців. Для деяких символів зі спеціальним призначенням необхідно використовувати одинарну лапку (') як символ виходу. Наприклад: =ЗбутВідділуФінРікЗведення['#Геш-тег].

Ось список спеціальних символів, для яких у формулі потрібен символ виходу ('):

• Ліва квадратна дужка ([)
• Права квадратна дужка (])
• Решітка (#)
• Одинарна лапка (')
• Равлик (@)

Використовуйте пробіли, щоб полегшити сприйняття структурованого посилання. Щоб полегшити сприйняття структурованих посилань, можна використовувати пробіли. Наприклад: =ЗбутВідділу[ [Продавець]:[Регіон] ] або =ЗбутВідділу[[#Заголовки], [#Дані], [Комісія, %]]

Ми радимо використовувати один пробіл:

• Після першої лівої квадратної дужки ([)
• Перед останньою правою квадратною дужкою (]).
• Після коми.

Оператори посилань

Використовуйте ці оператори посилань, щоб поєднувати визначники стовпців і гнучкіше вибирати діапазони клітинок:

Структуроване посилання:	Посилається на:	За допомогою:	Відповідний діапазон клітинок:
=Продажі_відділу[[Продавець]:[Регіон]]	Усі клітинки у двох або більше сумісних стовпцях	: (двокрапка) оператор діапазону	A2:B7
=Продажі_відділу[Обсяг продажів],Продажі_відділу[Сума комісії]	Поєднання двох або більше стовпців	; (крапка з комою) оператор об’єднання	C2:C7, E2:E7
=Продажі_відділу[[Продавець]:[Обсяг продажів]] Продажі_відділу[[Регіон]:[Комісія у %]]	Перетин двох або більше стовпців	(пробіл) оператор перетину	B2:C7

Визначники спеціальних елементів

Щоб створити посилання на певну частину таблиці, наприклад тільки на рядок підсумків, можна скористатися будь-яким із цих визначників спеціальних елементів у структурованих посиланнях:

Визначник спеціального елемента:	Посилається на:
#Усі	Уся таблиця, включно з заголовками стовпців, даними та підсумками (якщо є).
#Дані	Лише рядки даних.
#Заголовки	Лише заголовки рядків.
#Підсумки	Лише рядок підсумку. У разі відсутності рядка повертається нульове значення.
#Цей рядок або @ або @[Назва стовпця]	Лише клітинки в тому самому рядку, що й формула. Ці визначники не можна поєднувати з іншими визначниками спеціальних елементів. Вони використовуються, щоб примусово застосувати неявний перетин до посилань або перевизначити його й посилатися на окремі значення стовпця. Визначники "#Цей рядок" автоматично замінюються в програмі Excel на коротший визначник @ у таблицях із кількома рядками даних. Але якщо таблиця містить лише один рядок, програма Excel не замінює визначник #This рядок, оскільки це може призвести до неочікуваних результатів обчислення, якщо додати більше рядків. Щоб уникнути проблем з обчисленням, введіть у таблицю кілька рядків, перш ніж вводити будь-які формули структурованих посилань.

Уточнення структурованих посилань в обчислюваних стовпцях

Коли створюється обчислюваний стовпець, часто використовується структуроване посилання, щоб ввести формулу. Це структуроване посилання може бути неточне або точне. Наприклад, щоб створити обчислюваний стовпець під назвою "Сума комісії", у якому обчислюється сума комісійної винагороди в гривнях, скористайтеся такими формулами:

Тип структурованого посилання	Приклад	Примітка
Неточне	=[Обсяг продажів]*[Комісія у %]	Перемножує відповідні значення поточного рядка.
Точне	=Продажі_відділу[Обсяг продажів]*Продажі_відділу[Комісія у %]	Перемножує відповідні значення кожного рядка для обох стовпців.

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

Приклади використання структурованих посилань

Нижче наведено кілька способів використання структурованих посилань.

Структуроване посилання:	Посилається на:	Відповідний діапазон клітинок:
=ЗбутВідділу[[#Усі],[Обсяг збуту]]	Усі клітинки в стовпці "Обсяг продажів".	C1:C8
=Продажі_відділу[[#Заголовки],[Комісія у %]]	Заголовок стовпця "Комісія у %".	D1
=Збут_Відділу[[#Підсумки];[Область]]	Підсумок стовпця "Область". У разі відсутності цього стовпця повертається нульове значення.	B8
=Продажі_відділу[[#Усі],[Обсяг продажів]:[Комісія у %]]	Усі клітинки у стовпцях "Обсяг продажів" і "Комісія у %".	C1:D8
=Продажі_відділу[[#Дані],[Комісія у %]:[Сума комісії]]	Лише дані стовпців "Комісія у %" і "Сума комісії".	D2:E7
=Продажі_відділу[[#Заголовки],[Регіон]:[Сума комісії]]	Лише заголовки стовпців між стовпцями "Область" і "Сума комісії".	B1:E1
=Продажі_відділу[[#Підсумки],[Обсяг продажів]:[Сума комісії]]	Підсумки діапазону стовпців від "Обсяг продажів" до "Сума комісії". За відсутності рядка підсумків повертається нульове значення.	C8:E8
=Продажі_відділу[[#Заголовки],[#Дані],[Комісія у %]]	Лише заголовок і дані стовпця "Комісія у %".	D1:D7
=Продажі_відділу[[#Цей рядок], [Сума комісії]] або =Продажі_відділу[@Сума комісії]	Клітинка на перетині поточного рядка та стовпця "Сума комісії". Якщо використати цей параметр в одному рядку з рядком заголовка або підсумку, повернеться помилка #VALUE! . Якщо в таблицю з кількома рядками даних вводиться це структуроване посилання ("#Цей рядок") у довшій формі, Excel автоматично заміняє його на посилання в коротшій формі (@). Вони обидва функціонують однаково.	E5 (якщо поточний рядок — 5)

Стратегії роботи зі структурованими посиланнями

Працюючи зі структурованими посиланнями, зверніть увагу на таке:

• Використання автозаповнення формул. Функція автозаповнення формул може виявитися дуже корисною, якщо потрібно ввести структуровані посилання та забезпечити дотримання правил синтаксису. Докладні відомості див. в статті "Використання автозаповнення формул".

• Вибір способу створення структурованих посилань для напіввиділених таблиць За замовчуванням, якщо створити формулу, діапазон клітинок у таблиці буде виділено наполовину, а замість діапазону клітинок у формулі буде автоматично введено структуроване посилання. Таке напіввиділення значно полегшує введення структурованих посилань. Щоб увімкнути або вимкнути цю поведінку, установіть або зніміть прапорець "Використовувати імена таблиць у формулах" у діалоговому вікні "Параметри>файлу>:формули>:робота з формулами".

• Використання книг із зовнішніми посиланнями на таблиці Excel в інших книгах Якщо книга містить зовнішнє посилання на таблицю Excel в іншій книзі, цю книгу потрібно відкрити в програмі Excel, щоб уникнути помилок #REF! у книзі призначення, яка містить зв'язки. Якщо спочатку відкриється цільова книга та з'являться помилки #REF!, їх буде вирішено, якщо потім відкрити вихідну книгу. Якщо спочатку відкрити вихідну книгу, коди помилок не відображатимуться.

• Перетворення діапазону на таблицю та навпаки . Якщо перетворити таблицю на діапазон, усі посилання на клітинки буде замінено на відповідні абсолютні посилання в стилі A1. Якщо перетворити діапазон на таблицю, жодні посилання на клітинки цього діапазону в програмі Excel не буде замінено автоматично на еквівалентні структуровані посилання.

• Вимкнення заголовків стовпців За допомогою рядка заголовка вкладки > "Конструктор таблиць" можна вмикати й вимикати заголовки стовпців таблиці. Якщо вимкнути заголовки стовпців таблиці, це не вплине на структуровані посилання, у яких використовуються імена стовпців, – їх можна буде використовувати у формулах і надалі. структуровані посилання, які спрямовують безпосередньо на заголовки таблиці (наприклад, =ЗбутВідділу[[#Headers],[%Комісія]]), призведуть до #REF.

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

• Перейменування таблиці або стовпця. Під час перейменування стовпця або таблиці Excel автоматично змінює умови використання даної таблиці та заголовка стовпця всіма структурованими посиланнями, які використовуються у книзі.

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

  Примітка.

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

Якщо напрямок заповнення такий:	Клавіша, яку слід натиснути під час заповнення	Результат
Вгору або вниз	Нічого	Змінення визначника стовпця відсутнє.
Вгору або вниз	Ctrl	Визначники стовпців змінюються як ряди.
Праворуч або ліворуч	Нічого	Визначники стовпців змінюються як ряди.
Вгору, вниз, праворуч або ліворуч	Shift	Замість перезаписування значень у поточних клітинках поточні значення клітинок переміщуються, а визначники вставляються.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.

Пов’язані теми

Огляд таблиць Excel
Створення або видалення таблиць
Обчислення підсумків даних у таблиці Excel
Форматування таблиці Excel
Змінення розміру таблиці за допомогою додавання або видалення рядків і стовпців
Фільтрування даних у діапазоні або таблиці
Перетворення таблиці на діапазон
Проблеми сумісності таблиць Excel
Експортування таблиці Excel до списку SharePoint
Огляд формул в Excel