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

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

Вместо да използва явни препратки към клетки	Excel използва имената на таблицата и колоните
=Sum(C2:C7)	=SUM(ОтделПродажби[Сума на продажбите])

Тази комбинация от имена на таблици и колони се нарича структурирана препратка. Имената в структурираните препратки се настройват винаги, когато добавяте или премахвате данни в таблицата.

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

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

Продавач	Регион	Сума на продажбите	% комисиона
Ивайло	Север	260	10%
Костадин	Юг	660	15%
Илияна	Изток	940	15%
Петър	Запад	410	12%
Вяра	Север	800	15%
Димитър	Юг	900	15%

Копирайте примерните данни в таблицата по-горе, включително заглавията на колоните, и ги поставете в клетка A1 на нов работен лист на Excel.
За да създадете таблицата, изберете произволна клетка в диапазона от данни и натиснете Ctrl+T.
Уверете се, че квадратчето Моята таблица има заглавки е отметнато, и изберете OK.
В клетка E2 въведете знак за равенство (=) и изберете клетка C2.

В лентата за формули структурираната препратка [@[Сума на продажбите]] се появява след знака за равенство.
Въведете звездичка (*) непосредствено след затварящата скоба и изберете клетка D2.

В лентата за формули структурираната препратка [@[% комисиона]] се появява след звездичката.
Натиснете Enter.

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

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

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

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

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

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

Когато създавате таблица на Excel, Excel създава име на таблицата по подразбиране (Таблица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], [% комисиона]]

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

След първата лява квадратна скоба ([)
Пред последната дясна квадратна скоба (])
След запетая.

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

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

Тази структурирана препратка:	Препраща към:	Като използва:	Което е диапазон от клетки:
=ОтделПродажби[[Продавач]:[Регион]]	Всички клетки в две или повече съседни колони	: (двоеточие) оператор за диапазон	A2:B7
=ОтделПродажби[Сума на продажбите];ОтделПродажби[Сума на комисионата]	Комбинация на две или повече колони	; (точка и запетая) оператор за обединение	C2:C7; E2:E7
=ОтделПродажби[[Продавач]:[Сума на продажбите]];ОтделПродажби[[Регион]:[% комисиона]]	Сечение на две или повече колони	(интервал) оператор за сечение	B2:C7

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

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

Този спецификатор на специален елемент:	Препраща към:
#All	Цялата таблица, включително заглавките на колони, данни и общи суми (ако има).
#Data	Само редовете с данни.
#Headers	Само заглавния ред.
#Totals	Само сумарния ред. Ако не съществува, връща се null.
#This Row ИЛИ @ ИЛИ @[Име на колона]	Само клетките на същия ред като формулата. Тези спецификатори не могат да се комбинират с никакви други спецификатори на специални елементи. Използвайте ги, за да постигнете поведение на неявно сечение за препратката или за да заместите поведението на неявно сечение и да посочите отделни стойности от колона. Excel автоматично променя спецификаторите #This Row в по-краткия спецификатор @ в таблици, които имат повече от един ред с данни. Но ако вашата таблица има само един ред, Excel не замества спецификатора #This Row, което може да доведе до неочаквани резултати при изчисление, когато добавите повече редове. За да избегнете проблеми при изчисляване, се уверете, че въвеждате няколко реда в таблицата си, преди да въведете формули за структурирани препратки.

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

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

Тип на структурираната препратка	Пример	Коментар
Неквалифицирана	=[Сума на продажбите]*[% комисиона]	Умножава съответните стойности от текущия ред.
Напълно квалифицирана	=ОтделПродажби[Сума на продажбите]*ОтделПродажби[% комисиона]	Умножава съответните стойности за всеки ред и за двете колони.

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

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

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

Тази структурирана препратка:	Препраща към:	Което е диапазон от клетки:
= ОтделПродажби[[#All];[Сума на продажбите]]	Всички клетки в колоната "Сума на продажбите".	C1:C8
=ОтделПродажби[[#Headers];[% комисиона]]	Заглавката на колоната "% комисиона".	D1
=ОтделПродажби[[#Totals];[Регион]]	Общата сума на колоната Регион. Ако няма сумарен ред, връща се null.	B8
=ОтделПродажби[[#All];[Сума на продажбите]:[% комисиона]]	Всички клетки в "Сума на продажбите" и "% комисиона".	C1:D8
=ОтделПродажби[[#Data];[% комисиона]:[Сума на комисионата]]	Само данните в колоните "% комисиона" и "Сума на комисионата".	D2:E7
=DeptSales[[#Headers];[Регион]:[Сума на комисионата]]	Само заглавките на колоните между "Регион" и "Сума на комисионата".	B1:E1
=ОтделПродажби[[#Totals];[Сума на продажбите]:[Сума на комисионата]]	Общите суми за колоните от "Сума на продажбите" до "Сума на комисионата". Ако няма сумарен ред, връща се null.	C8:E8
=ОтделПродажби[[#Headers];[#Data];[% комисиона]]	Само заглавката и данните на "% комисиона".	D1:D7
=ОтделПродажби[[#This Row]; [Сума на комисионата]] ...или =ОтделПродажби[@Сума на комисионата]	Клетката в пресечната точка на текущия ред и колоната Сума на комисионата. Ако се използва в същия ред като заглавен или сумарен ред, това ще върне грешка #VALUE! . Ако въведете по-дългата форма на тази структурирана препратка (#This Row) в таблица с множество редове с данни, Excel автоматично я замества с по-късата форма (@). И двете вършат една и съща работа.	E5 (ако текущият ред е 5)

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

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

Използване на формула за автодовършване Възможно е да установите, че използването на "Автодовършване на формули" е много полезно, когато въвеждате структурирани препратки и за да гарантирате използването на правилен синтаксис. За повече информация вижте Използване на "Автодовършване на формули".
Решете дали да генерирате структурирани препратки за таблици в полуселекции По подразбиране, когато създавате формула, избирането на диапазон от клетки в таблица полуизбира клетките и автоматично въвежда структурирана препратка вместо диапазона от клетки във формулата. Това поведение на полуселекция прави въвеждането на структурирана препратка много по-лесно. Можете да включвате или изключвате това поведение, като поставите или изчистите отметката от квадратчето Използвай имена на таблици във формули в диалоговия прозорец Опцииза > на файл > Формули > Работа с формули.
Използване на работни книги с външни връзки към таблици на Excel в други работни книги Ако една работна книга съдържа външна връзка към таблица на Excel в друга работна книга, тази свързана работна книга източник трябва да е отворена в Excel, за да се избегнат грешките #REF! в работната книга местоназначение, която съдържа връзките. Ако първо отворите работната книга местоназначение и се появят грешки #REF! , те ще бъдат отстранени, ако след това отворите работната книга източник. Ако първо отворите работната книга източник, не би трябвало да виждате кодове на грешка.
Преобразуване на диапазон в таблица и на таблица в диапазон Когато преобразувате таблица в диапазон, всички препратки към клетки се променят в техния еквивалент с абсолютни препратки в стил A1. Когато преобразувате диапазон в таблица, Excel не променя автоматично никакви препратки към клетки на този диапазон в техните еквивалентни структурирани препратки.
Изключване на заглавките на колони Можете да включвате и изключвате заглавките на колоните на таблицата от раздела Проектиране на таблица > заглавен ред. Ако изключите заглавките на колони на таблица, структурираните препратки, които използват имена на колони, не се засягат и все още можете да ги използвате във формули. Структурираните препратки, които препращат директно към заглавките на таблицата (например =ОтделПродажби[[#Headers];[%Комисиона]]), ще доведат до #REF.
Добавяне или изтриване на колони и редове на таблицата Тъй като диапазоните от данни на таблица често се променят, препратките към клетки за структурирани препратки се настройват автоматично. Например ако използвате име на таблица във формула, за да преброите всички клетки с данни в таблицата и след това добавите ред с данни, препратката към клетка автоматично се настройва.
Преименуване на таблица или колона Ако преименувате таблица или колона, Excel автоматично променя използването на заглавията на тази таблица и колона във всички структурирани препратки, които се използват в работната книга.
Преместване, копиране и попълване на структурирани препратки Всички структурирани препратки остават същите, когато копирате или премествате формула, използваща структурирана препратка.

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

Ако посоката на попълване е:	И по време на попълването натиснете:	Тогава:
Нагоре или надолу	Нищо	Няма настройване на спецификатор на колона.
Нагоре или надолу	Ctrl	Спецификаторите на колони се настройват подобно на серии.
Надясно или наляво	Няма	Спецификаторите на колони се настройват подобно на серии.
Нагоре, надолу, надясно или наляво	Shift	Вместо да се презаписват стойности в текущите клетки, се преместват текущите стойности на клетките и се вмъкват спецификатори на колони.

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

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

Сродни теми

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