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

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

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

Замість явних посилань на клітинки

У програмі Excel використовуються імена таблиць і клітинок

=SUM(C2:C7)

=SUM(ЗбутВідділу[Обсяг збуту])

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

Структуровані посилання також відображаються в разі створення формули, яка посилається на дані таблиці, за межами таблиці 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.

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

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

  • Використовуйте припустимі символи  Завжди починаються ім'я з букви, символ підкреслення (_) або зворотну скісну риску (\). Використання букв, чисел, періоди та інші ім'я символи підкреслення. Не можна використовувати "C", "c", "R" або "r" для імені, оскільки вони вже призначений сполучення клавіш для вибору стовпця або рядка для активної клітинки під час введення їх у полі ім'я або Перейти до .

  • Не використовуйте посилання на клітинки  Імена не можуть бути ідентичні посиланням на клітинки, наприклад Z$ 100 або R1C1.

  • Не використовуйте пробіл Відокремлюйте слова  Пробіли, не можна використовувати в імені. Як можна використовувати символ підкреслення (_) і періоду (.). Наприклад, продажі _ відділу, розділювачі або квартал.

  • Використовуйте не більше 255 символів. Ім’я таблиці може містити до 255 символів.

  • Давайте таблицям унікальні імена. Повторювати імена не дозволено. У програмі Excel не розрізняються символи верхнього й нижнього регістра в іменах, тому, якщо ввести ім’я "Збут" у книзі, у якій уже є таблиця з іменем "ЗБУТ", буде запропоновано вибрати унікальне ім’я.

  • Використання ідентифікатора об'єкта  Якщо ви плануєте поєднання таблиць, зведених таблиць і діаграм, варто префікса імена з тип об'єкта. Наприклад: tbl_Sales таблиці продажів, pt_Sales для продажів зведеної таблиці та chrt_Sales для продажів діаграми або ptchrt_Sales для продажів зведеної діаграми. Це зберігає всі імена у Упорядкований список в Диспетчер імен.

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

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

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

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

  • Ім'я таблиці:   Продажі _ відділу – це ім'я настроюваного таблиці. Це посилання дані таблиці без будь-який заголовок або підсумок рядків. Можна використовувати за промовчанням ім'я таблиці, наприклад Таблиця1, або змінити його, щоб використовувати власне ім'я.

  • Визначник стовпця:   [Обсяг продажів]і[Сума комісії] – це визначники стовпців, які використовуються імена стовпців, які вони представляють. Вони посилання на дані у стовпці, без будь-якого стовпця верхній колонтитул або підсумок рядка. Завжди беріть визначники у квадратних дужках, як показано.

  • Визначник елемента:   [#Totals] та [#Data] – це визначники спеціальних елементів, які посилаються на певні частини таблиці, такі як рядок підсумків.

  • Визначник таблиці:   [[#Підсумки],[Обсяг продажів]] і [[#Дані],[Сума комісії]] – це визначники таблиці, які відповідають зовнішнім частинам структурованого посилання. Зовнішні посилання розташовуються після імені таблиці та беруться у квадратні дужки.

  • Структуроване посилання:   (Продажі _ відділу [[#Totals], [обсяг збуту]] та продажі _ відділу [[#Data], [сума комісії]] – це структуровані посилання, представлені рядком, який починається з імені таблиці та закінчується визначником стовпця.

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

  • Використання у квадратні дужки визначники    Усі таблиці, стовпці та визначники спеціальних елементів потрібно брати в парні квадратні дужки ([). Визначник, що містить інші визначники вимагає зовнішніх парних дужок для дужок інших визначників. Наприклад: = продажі _ відділу [[продавець]: [область]]

  • Усі заголовки стовпців – це текстові рядки    Але вони не потребують цінових пропозицій, коли вони використовуються у структурованому посиланні. Чисел або дат, наприклад 2014 року або 1/1/2014 року, також вважаються текстові рядки. Не можна використовувати вирази із заголовками стовпців. Наприклад, вираз збут _ Відділуфінрікзведення [[2014 р.]: [2012 р.]] не працюватиме.

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

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

  • табуляція

  • Символ переведення рядка

  • Символ повернення каретки

  • Кома (,)

  • Двокрапка (:)

  • Крапка (.)

  • Ліва квадратна дужка ([)

  • Права квадратна дужка ([)

  • Решітка (#)

  • Одинарна лапка (')

  • Подвійні лапки (")

  • Ліва фігурна дужка ({)

  • Права фігурна дужка (})

  • Знак долара ($)

  • Кришка (^)

  • Амперсанд (&)

  • Зірочка (*)

  • Знак "плюс" (+)

  • Знак рівності (=)

  • Знак "мінус" (–)

  • Знак "більше" (>)

  • Знак "менше" (<)

  • Знак ділення (/)

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

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

  • Ліва квадратна дужка ([)

  • Права квадратна дужка ([)

  • Решітка (#)

  • Одинарна лапка (')

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

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

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

  • Перед останньою правою квадратною дужкою (])

  • Після коми

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

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

Структуроване посилання:

Посилається на:

За допомогою:

Відповідний діапазон клітинок:

=Продажі_відділу[[Продавець]:[Регіон]]

Усі клітинки у двох або більше сумісних стовпцях

: (двокрапка) оператор діапазону

A2:B7

=Продажі_відділу[Обсяг продажів],Продажі_відділу[Сума комісії]

Поєднання двох або більше стовпців

; (крапка з комою) оператор об’єднання

C2:C7, E2:E7

=Продажі_відділу[[Продавець]:[Обсяг продажів]] Продажі_відділу[[Регіон]:[Комісія у %]]

Перетин двох або більше стовпців

 (пробіл) оператор перетину

B2:C7

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

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

Визначник спеціального елемента:

Посилається на:

#Усі

Уся таблиця, включно з заголовками стовпців, даними та підсумками (якщо є).

#Дані

Лише рядки даних.

#Заголовки

Лише заголовки рядків.

#Підсумки

Лише рядок підсумку. У разі відсутності рядка повертається нульове значення.

#Цей рядок

або

@

або

@[Назва стовпця]

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

Визначники "#Цей рядок" автоматично замінюються в програмі Excel на коротший визначник @ у таблицях із кількома рядками даних. Але якщо в таблиці лише один рядок, програма Excel не заміняє визначник "#Цей рядок", що може призвести до неочікуваних результатів обчислення, коли буде додано інші рядки. Щоб уникнути проблем з обчисленням, введіть у таблицю кілька рядків, перш ніж вводити будь-які формули структурованих посилань.

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

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

Тип структурованого посилання

Приклад

Примітка

Неточне

=[Обсяг продажів]*[Комісія у %]

Перемножує відповідні значення поточного рядка.

Точне

=Продажі_відділу[Обсяг продажів]*Продажі_відділу[Комісія у %]

Перемножує відповідні значення кожного рядка для обох стовпців.

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

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

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

Структуроване посилання:

Посилається на:

Відповідний діапазон клітинок:

=ЗбутВідділу[[#Усі],[Обсяг збуту]]

Усі клітинки в стовпці "Обсяг продажів".

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 User Voice.

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

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

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×