Використання структурованих посилань із таблицями 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Клавіша табуляції

• Інформаційний канал рядка

• Повернення каретки

• Кома (,)

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

• Крапка (.)

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

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

• Знак фунта (#)

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

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

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

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

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

• Символ кришки (^)

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

• Зірочка (*)

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

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

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

• Символ більше (>)

• Символ менше (<)

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

• За знаком (@)

• Обернена скісна риска (\)

• Знак оклику (!)

• Ліва дужка (()

• Права дужка ())

• Знак відсотка (%)

• Знак питання (?)

• Backtick (')

• Крапка з комою (;)

• Тильда (~)

• Підкреслення (_)

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

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

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

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

• Знак фунта (#)

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

• За знаком (@)

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

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

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

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

• Після коми.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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