Използване на структурирани препратки с таблици на Excel

Какво се случва, когато използвам явни препратки към клетки?

Ако въведете явни препратки към клетки в изчисляема колона, може да е по-трудно да се види какво изчислява формулата.

1. В примерния работен лист щракнете върху клетка E2

2. В лентата за формули въведете =C2*D2 и натиснете клавиша Enter.

Обърнете внимание, че Excel копира формулата надолу в колоната, но не използва структурирани препратки. Ако например добавите колона между съществуващите колони C и D, ще трябва да поправите формулата.

Как се променя име на таблица?

Когато създавате таблица на Excel, Excel създава име на таблицата по подразбиране (Таблица1, Таблица2 и т.н.), но вие можете да промените името на таблицата, за да го направите по-смислено.

1. Изберете произволна клетка в таблицата, за да се покаже разделът Инструменти за таблица > Проектиране на лентата.

2. Въведете желаното име в полето Име на таблица и натиснете клавиша Enter.

В нашите примерни данни използвахме името ОтделПродажби.

Използвайте следните правила за имената на таблици:

• Използвайте валидни знаци  Винаги започвайте името с буква, долна черта (_) или обратно наклонена черта (\). Използвайте букви, цифри, точки и знаци за долна черта за останалата част от името. Не можете да използвате "C", "c", "R" или "r" за името, защото вече са указани като клавишна комбинация за избор на колоната или реда за активната клетка, когато ги въведете в полето Име или Отиди на.

• Не използвайте препратки към клетки  Имената не могат да съвпадат с препратки към клетки, например Z$100 или R1C1.

• Не използвайте интервал за разделяне на думите  В името не могат да се използват интервали. Можете да използвате долна черта (_) и точка (.) като разделите на думите. Например ОтделПродажби, Данък_Продажби или Първо.Тримесечие.

• Използвайте не повече от 255 знака Име на таблица може да съдържа до 255 знака.

• Използвайте уникални имена на таблици Дублиране на имена не се разрешава. Excel не прави разлика между главни и малки букви в имената, така че ако въведете "Продажби", но вече имате друга таблица с име "ПРОДАЖБИ" в същата работна книга, ще бъдете подканени да изберете неповтарящо се име.

• Използване на идентификатор на обект  Ако планирате да имате комбинация от таблици, обобщени таблици и диаграми, е добре да префиксирате имената си с типа на обекта. Например: tbl_Sales за таблица с продажби, pt_Sales за обобщена таблица за продажби и chrt_Sales за диаграма за продажби или ptchrt_Sales за обобщена диаграма за продажби. Това съхранява всичките ви имена в подреден списък в диспечера на имената.

Правила на синтаксиса за структурирана препратка

Можете също да въведете или промените структурирани препратки ръчно във формулата, но за да направите това, ще ви помогне да разберете синтаксиса на структурираните препратки. Нека разгледаме следния пример за формула:

=SUM(ОтделПродажби[[#Totals],[Сума на продажбите]],ОтделПродажби[[#Data],[Сума на комисионната]])

Тази формула има следните компоненти на структурирана препратка:

• Име на таблица:    ОтделПродажби е име на таблица по избор. То сочи към табличните данни без заглавките за редове или редовете за общи суми. Можете да използвате име на таблица по подразбиране, като Таблица1, или да го промените, за да използвате име по избор.

• Спецификатор на колона:    [Сума на продажбите] и [Сума на комисионата] са спецификатори на колони, които използват имената на колоните, които представят. Те сочат към данните в колоната без заглавката или реда за обща сума. Винаги ограждайте спецификаторите със скоби, както е показано.

• Спецификатор на елемент:    [#Totals] и [#Data] са спецификатори на специални елементи, които препращат към определени части от таблицата, като например сумарния ред.

• Спецификатор на таблица:    [[#Totals],[Сума на продажбите]] и [[#Data],[Сума на комисионата]] са спецификатори на таблица, които представят външните части на структурираната препратка. Външните препратки следват името на таблицата и вие ги ограждате в квадратни скоби.

• Структурирана препратка:    (ОтделПродажби[[#Totals],[Сума на продажбите]] и ОтделПродажби[[#Data],[Сума на комисионата]] са структурирани препратки, представени от низ, който започва с името на таблицата и завършва със спецификатора на колоната.

За да създадете или редактирате ръчно структурирани препратки, използвайте следните синтактични правила:

• Използвайте скоби около спецификаторите    Всички спецификатори на таблици, колони и на специални елементи трябва да бъдат оградени със съответстващи си квадратни скоби ([ ]). Спецификатор, който съдържа други спецификатори, изисква външни съответстващи си квадратни скоби за ограждане на вътрешните квадратни скоби на другите спецификатори. Например: =ОтделПродажби[[Продавач]:[Регион]]

• Всички заглавки на колони са текстови низове    Но те не изискват кавички, когато се използват в структурирана препратка. Числата или датите, например 2014 г. или 1.1.2014 г., също се считат за текстови низове. Не можете да използвате изрази със заглавки на колони. Например изразът ОтделПродажбиРезюмеФГ[[2014]:[2012]] няма да работи.

Използвайте скоби около заглавките на колони със специални знаци    Ако има специални знаци, цялата заглавка на колона трябва да бъде поставена в скоби, което означава, че за спецификатора на колона се изискват двойни квадратни скоби. Например: =ОтделПродажбиРезюмеФГ[[Обща сума $]]

Ето списък на специалните знаци, които се нуждаят допълнителни скоби във формулата:

• Табулация

• Знак за нов ред

• Знак за нов абзац

• Запетая (,)

• Двоеточие (:)

• Точка (.)

• Лява квадратна скоба ([)

• Дясна квадратна скоба (])

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

• Единична кавичка (')

• Двойна кавичка (")

• Лява фигурна скоба ({)

• Дясна фигурна скоба (})

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

• Карета (^)

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

• Звездичка (*)

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

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

• Знак минус (-)

• Знак "по-голямо" (>)

• Знак "по-малко" (<)

• Знак за деление (/)

• Знака @)

• Обратно наклонена черта (\)

• Удивителен знак (!)

• Лява скоба (()

• Дясна кръгла скоба ())

• Знак за процент (%)

• Въпросителен знак (?)

• Backtick (')

• Точка и запетая (;)

• Тилда (~)

• Долна черта (_)

• Използване на превключващ знак за някои специални знаци в заглавки на колони    Някои знаци имат специален смисъл и изискват използването на единична кавичка (') като превключващ знак. Пример: =ОтделПродажбиФГРезюме['#OfItems]

Ето списък на специалните знаци, които се нуждаят от превключващ знак (') във формулата:

• Лява квадратна скоба ([)

• Дясна квадратна скоба (])

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

• Единична кавичка (')

• Знака @)

Използване на знака за интервал за подобряване на четливостта в структурирана препратка    Можете да използвате знаци за интервал, за да подобрите четливостта на структурирана препратка. Пример: =ОтделПродажби[ [Продавач]:[Регион] ] или =ОтделПродажби[[#Headers], [#Data], [% комисиона]]

Препоръчително е да използвате интервал:

• След първата лява квадратна скоба ([)

• Пред последната дясна квадратна скоба (])

• След запетая.

Оператори за препратки

За по-голяма гъвкавост при задаване на диапазони от клетки можете да използвате следните оператори за препратки, за да ги съчетавате със спецификатори на колони.

Спецификатори на специални елементи

За препратка към определени части от таблицата, като например само реда за общи суми, можете да използвате някой от следните спецификатори на специални елементи във вашите структурирани препратки.

Квалифициране на структурирани препратки в изчисляеми колони

Когато създавате изчисляема колона, често използвате структурирана препратка, за да създадете формулата. Тази структурирана препратка може да бъде неквалифицирана или напълно квалифицирана. Например за да създадете изчисляема колона, наречена "Сума на комисионата", която изчислява сумата на комисионата в долари, можете да използвате следните формули:

Общото правило за следване е това: Ако използвате структурирани препратки в таблица, например когато създавате изчисляема колона, можете да използвате неквалифицирана структурирана препратка, но ако използвате структурираната препратка извън таблицата, трябва да използвате напълно квалифицирана структурирана препратка.

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

Ето няколко начина за използване на структурирани препратки.

Стратегии за работа със структурирани препратки

Имайте предвид следното, когато работите със структурирани препратки:

• Използване на формула за автодовършване    Възможно е да установите, че използването на "Автодовършване на формули" е много полезно, когато въвеждате структурирани препратки и за да гарантирате използването на правилен синтаксис. За повече информация вижте Използване на "Автодовършване на формули".

• Решете дали да генерирате структурирани препратки за таблици в полуселекции    По подразбиране, когато създавате формула, щракването върху диапазон от клетки в таблица полуизбере клетките и автоматично въвежда структурирана препратка вместо диапазона от клетки във формулата. Това поведение на полуселекция прави въвеждането на структурирана препратка много по-лесно. Можете да включвате или изключвате това поведение, като поставите или изчистите отметката от квадратчето Използвай имена на таблици във формули в диалоговия прозорец Опцииза > на файл > Формули > Работа с формули.

• Използване на работни книги с външни връзки към таблици на Excel в други работни книги    Ако една работна книга съдържа външна връзка към таблица на Excel в друга работна книга, тази свързана работна книга източник трябва да е отворена в Excel, за да се избегнат грешките #REF! в работната книга местоназначение, която съдържа връзките. Ако първо отворите работната книга местоназначение и се появят грешки #REF! , те ще бъдат отстранени, ако след това отворите работната книга източник. Ако първо отворите работната книга източник, не би трябвало да виждате кодове на грешка.

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

• Изключване на заглавките на колони    Можете да включвате и изключвате заглавките на колоните на таблицата от раздела Проектиране на таблица > Заглавен ред. Ако изключите заглавките на колони на таблица, структурираните препратки, които използват имена на колони, не се засягат и все още можете да ги използвате във формули. Структурираните препратки, които препращат директно към заглавките на таблицата (например =ОтделПродажби[[#Headers];[%Комисиона]]), ще доведат до #REF.

• Добавяне или изтриване на колони и редове на таблицата    Тъй като диапазоните от данни на таблица често се променят, препратките към клетки за структурирани препратки се настройват автоматично. Например ако използвате име на таблица във формула, за да преброите всички клетки с данни в таблицата и след това добавите ред с данни, препратката към клетка автоматично се настройва.

• Преименуване на таблица или колона    Ако преименувате таблица или колона, Excel автоматично променя използването на заглавията на тази таблица и колона във всички структурирани препратки, които се използват в работната книга.

• Преместване, копиране и попълване на структурирани препратки    Всички структурирани препратки остават същите, когато копирате или премествате формула, използваща структурирана препратка.

  Забележка: Копирането на структурирана препратка и попълването на структурирана препратка не са едно и също нещо. Когато копирате, всички структурирани препратки остават едни и същи, докато, когато попълвате формула, напълно квалифицираните структурирани препратки настройват спецификаторите на колони, подобно на серии, както е резюмирано в следната таблица.

Имате нужда от още помощ?

Винаги можете да попитате експерт в техническата общност на Excel или да получите поддръжка в Общността за отговори от.

Сродни теми

Общ преглед на таблиците
на ExcelВидео: Създаване и форматиране на таблица
на ExcelСумиране на данните в таблица
на ExcelФорматиране на таблица
на ExcelПреоразмеряване на таблица чрез добавяне или премахване на редове и колони
Филтриране на данни в диапазон или таблица
Преобразуване на таблица в диапазон
Проблеми със
съвместимостта при таблици на ExcelЕкспортиране на таблица на Excel в SharePoint
Общ преглед на формулите в Excel