Використання структурованих посилань із таблицями 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. Скопіюйте зразок даних із таблиці вище включно із заголовками стовпців і вставте його в клітинку А1 нового пустого аркуша Excel.

  2. Щоб створити таблицю, виділіть клітинку A1 і натисніть клавіші Ctrl+T.

  3. Переконайтеся, що прапорець Таблиця із заголовками встановлено, і натисніть кнопку OK.

  4. Введіть знак рівності (=) у клітинку E2 та клацніть клітинку C2.

У рядку формул після знака рівності з’явиться структуроване посилання [@[Обсяг збуту]].

  1. Введіть зірочку (*) одразу після правої дужки та клацніть клітинку D2.

У рядку формул після зірочки з’явиться структуроване посилання @[Комісія, %].

  1. Натисніть клавішу Enter.

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

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

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

  1. На зразку аркуша у програмі Excel виділіть клітинку E2.

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

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

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

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

  1. Клацніть будь-яку клітинку в таблиці Excel, щоб на стрічці з’явилася контекстна вкладка "Робота з таблицями".

  2. На вкладці Конструктор введіть потрібне ім’я в поле Ім’я таблиці та натисніть клавішу Enter.

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

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

  • Використання v alid символи Завжди починаються ім'я з букви, символ підкреслення (_) або зворотну скісну риску (\). Використання букв, чисел, періоди та інші ім'я символи підкреслення.

В імені не можна використовувати символи С, с, R та r, оскільки вони вже використовуються як ярлики – якщо ввести їх у поле Ім’я або Перейти, можна вибрати стовпець або рядок активної клітинки.

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

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

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

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

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

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

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

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

  • Ім’я таблиці   ЗбутВідділу – це настроюване ім’я таблиці. Воно посилається на дані таблиці, крім рядків заголовків і підсумку. Можна залишити ім’я таблиці за замовчуванням, наприклад "Таблиця1", або змінити його на власне.

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

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

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

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

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

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

  • Усі заголовки стовпців – це текстові рядки    Але вони не потребують цінових пропозицій, коли вони використовуються у структурованому посиланні. Чисел або дат, наприклад 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

=Продажі_відділу[[#Цей рядок], [Сума комісії]]

або

=Продажі_відділу[@Сума комісії]

Клітинка на перетині поточного рядка та стовпця "Сума комісії". У випадку використання в тому самому рядку, що й рядок заголовка або рядок підсумків, буде повернуто помилку "#ЗНАЧЕННЯ!".

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

E5 (якщо поточний рядок — 5)

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

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

  • Використання автозаповнення формул.    Ви можете виявити, що за допомогою автозаповнення формул – це дуже корисна для введення структурованих посилань і для дотримання правил синтаксису. Додаткові відомості про Використання автозаповнення формул.

  • Необхідність створювати структуровані посилання для таблиць у напіввиділенні.    Якщо, створюючи формулу, клацнути діапазон клітинок у таблиці, за замовчуванням їх буде напіввиділено, а у формулу буде автоматичного введено структуроване посилання замість діапазону клітинок. Завдяки цьому значно простіше вводити структуровані посилання. Напіввиділення можна активувати або вимкнути. Для цього в діалоговому вікні Параметри Excel у категорії Формули в розділі Робота з формулами слід установити або зняти прапорець Використовувати імена таблиць у формулах.

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

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

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

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

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

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

    Примітка : Копіювання структурованих посилань та виконавши заповнення структурованого посилання є те ж саме. У разі копіювання, усі структуровані посилання лишаються незмінні, під час, коли ви заповнюєте формулу, точні структурні посилання Змінити визначники стовпців як ряди, як наведено в таблиці нижче.

Якщо напрямок заповнення такий:

Клавіша, яку слід натиснути під час заповнення

Результат

Вгору або вниз

Нічого

Змінення визначника стовпця відсутнє.

Вгору або вниз

Ctrl

Визначники стовпців змінюються як ряди.

Праворуч або ліворуч

Нічого

Визначники стовпців змінюються як ряди.

Вгору, вниз, праворуч або ліворуч

Зсув

Замість перезаписування значень у поточних клітинках поточні значення клітинок переміщуються, а визначники вставляються.

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

Див. також

Огляд формул у програмі Excel

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

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

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

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

×