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

Забележка:  Бихме искали да ви осигурим най-новото помощно съдържание възможно най-бързо на вашия собствен език. Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас, в дъното на тази страница? Ето статията на английски за бърза справка.

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

Вместо да използва явни препратки към клетки

Excel използва имената на таблицата и колоните

=Sum(C2:C7)

=SUM(ОтделПродажби[Сума на продажбите])

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

Структурираните препратки се появяват и когато създавате формула извън таблица на 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, Excel създава име на таблицата по подразбиране (Таблица1, Таблица2 и т.н.), но вие можете да промените името на таблицата, за да го направите по-смислено.

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

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

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

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

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

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

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

  • Използвайте не повече от 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]] например няма да работи.

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

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

  • Табулация

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

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

  • Запетая (,)

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

  • Точка (.)

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

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

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

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

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

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

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

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

  • Карета (^)

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

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

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

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

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

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

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

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

  • Използване на превключващ знак за някои специални знаци в заглавки на колони    Някои знаци имат специален смисъл и изискват използването на единична кавичка (') като превключващ знак. Пример: =ОтделПродажбиФГРезюме['#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, да получите поддръжка в общността за отговори или да предложите нова функция или подобрение на User Voice за Excel.

Сродни теми

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

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×