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

При создании таблицы Excel Excel назначает имя этой таблице и каждому заголовку столбца в таблице. Можно сделать так, чтобы при добавлении формул эти имена отображались автоматически и ссылки на ячейки в таблице можно было выбрать вместо ввода вручную. Вот пример того, что происходит в Excel:

Прямая ссылка на ячейки	Имена таблицы и столбцов в Excel
=СУММ(C2:C7)	=СУММ(ОтделПродаж[ОбъемПродаж])

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

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

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

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

1. Скопируйте образец данных из таблицы выше, включая заголовки столбцов, и вставьте их в ячейку A1 нового листа Excel.
2. Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите клавиши CTRL+T.
3. Убедитесь, что установлен флажок "Таблица содержит заголовки", и нажмите кнопку "ОК".
4. В ячейке E2 введите знак равенства (=) и выберите ячейку C2.
   В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].
5. Введите звездочку (*) сразу после закрывающей квадратной скобки и выберите ячейку D2.
   В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].
6. Нажмите клавишу ВВОД.
   Excel автоматически создает вычисляемый столбец и копирует формулу вниз по нему, корректируя ее для каждой строки.

Что произойдет, если я буду использовать прямые ссылки на ячейки?

Если вы введете в вычисляемый столбец прямые ссылки на ячейки, может быть сложнее понять, что вычисляет формула.

1. В образце листа выберите ячейку E2
2. В строке формул введите =C2*D2 и нажмите клавишу ВВОД.

Обратите внимание на то, что хотя Excel копирует формулу вниз по столбцу, структурированные ссылки не используются. Если, например, вы добавите столбец между столбцами C и D, вам придется исправлять формулу.

Как изменить имя таблицы?

При создании таблицы Excel ей назначается имя по умолчанию ("Таблица1", "Таблица2" и т. д.), но его можно изменить, чтобы сделать более осмысленным.

1. Выберите любую ячейку таблицы, чтобы на ленте появилась вкладка "Конструктор таблиц ".
2. Введите нужное имя в поле "Имя таблицы " и нажмите клавишу ВВОД.

В нашем примере данных мы использовали имя DeptSales.

При выборе имени таблицы соблюдайте такие правила:

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

• Не используйте ссылки на ячейки Имена не могут совпадать со ссылкой на ячейку, например Z$100 или R1C1.
• Не используйте пробел для разделения слов Пробелы в имени использовать нельзя. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Примеры допустимых имен: ОтделПродаж, Налог_на_продажи, Первый.квартал.
• Используйте не более 255 символов Имя таблицы может содержать до 255 знаков.
• Использование уникальных имен таблиц Повторяющиеся имена не допускаются. В Excel не учитывается регистр букв в именах, поэтому если вы ввели слово "Продажи", но в той же книге уже есть другое имя "ПРОДАЖИ", вам будет предложено выбрать уникальное имя.
• Использование идентификатора объекта Если вы планируете использовать сочетание таблиц, сводных таблиц и диаграмм, рекомендуется добавить к именам префикс типа объекта. Например: tbl_Sales для таблицы продаж, pt_Sales для сводной таблицы продаж, chrt_Sales для диаграммы продаж или ptchrt_Sales для сводной диаграммы по продажам. При этом все ваши имена будут сохранены в упорядоченном списке в диспетчере имен.

Правила синтаксиса структурированных ссылок

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

=СУММ(ОтделПродаж[[#Итого],[ОбъемПродаж]],ОтделПродаж[[#Данные],[ОбъемКомиссии]])

В этой формуле используются указанные ниже компоненты структурированной ссылки.

• **Имя таблицы:**DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без строк заголовков и итогов. Вы можете использовать имя таблицы по умолчанию, например "Таблица1", или изменить его на пользовательское имя.
• Спецификатор столбца:[Объем продаж]и[Сумма комиссионных] — это спецификаторы столбцов, в которых используются имена столбцов, которые они представляют. Они ссылаются на данные столбца без заголовка столбца или строки итогов. Спецификаторы всегда заключайте в квадратные скобки, как показано ниже.
• Спецификатор элемента:[#Totals] и [#Data] — это специальные спецификаторы элементов, которые ссылаются на определенные части таблицы, например на строку итогов.
• Спецификатор таблицы:[[#Totals],[Сумма продаж]] и [[#Data],[Сумма комиссионных]] — это спецификаторы таблицы, представляющие внешние части структурированной ссылки. Внешние ссылки следуют за именем таблицы и заключаются в квадратные скобки.
• Структурированные ссылки:(DeptSales[[#Totals],[Sales Amount]] и DeptSales[[#Data],[Commission Amount]] — это структурированные ссылки, представленные строкой, которая начинается с имени таблицы и заканчивается спецификатором столбца.

Чтобы создавать или изменять структурированные ссылки вручную, используйте следующие правила синтаксиса:

• Заключение спецификаторов в квадратные скобки Все описания таблиц, столбцов и специальных элементов должны быть заключены в соответствующие скобки ([ ]). Указатель, содержащий другие указатели, требует наличия таких же внешних скобок, в которые будут заключены внутренние скобки других указателей. Например: =DeptSales[[Sales]:[Region]]
• Все заголовки столбцов представляют собой текстовые строки Но они не требуют кавычек, когда они используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Выражения нельзя использовать с заголовками столбцов. Например, выражение DeptSalesFYSummary[[2014]:[2012]] не будет работать.

Заключение заголовков столбцов в квадратные скобки со специальными символами При наличии специальных знаков необходимо заключить в скобки весь заголовок столбца, то есть в описании столбца требуются двойные скобки. Пример: =ОтделПродажСводкаФГ[[Итого $]]

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

• TAB
• Лента линии
• Возврат каретки
• Запятая (,)
• Двоеточие (:)
• Точка (.)
• Левая квадратная скобка ([)
• Правая квадратная скобка (])
• Решетка (#)
• Одинарная кавычка (')
• Двойные кавычки (")
• Левая круглая скобка ({)
• Правая круглая скобка (})
• Знак рубля ($)
• Caret (^)
• Амперсанд (&)
• Звездочка (*)
• Знак "плюс" (+)
• Знак равенства (=)
• Знак "минус" (-)
• Символ "больше" (>)
• Символ "меньше" (<)
• Знак деления (/)
• Знаком буквы (@)
• Обратная косая черта (\)
• Восклицательный знак (!)
• Левая круглая скобка (( )
• Правая круглая скобка ())
• Знак процента (%)
• Вопросительный знак (?)
• Обратная галочка (')
• Точка с запятой (;)
• Тильда (~)
• Символ подчеркивания (_)

• Использование escape-символов для специальных символов в заголовках столбцов Некоторые символы имеют особое значение и требуют использования в качестве escape-символа одинарных кавычек ('). Пример: =ОтделПродажСводкаФГ['#Элементов]

Ниже приведен список специальных символов, в формуле которых требуется escape-символ (').

• Левая квадратная скобка ([)
• Правая квадратная скобка (])
• Решетка (#)
• Одинарная кавычка (')
• Знаком буквы (@)

Используйте пробел для повышения удобочитаемости структурированных ссылок Для улучшения читаемости структурированной ссылки можно использовать пробелы. Пример: =ОтделПродаж[ [Продавец]:[Регион] ] или =ОтделПродаж[[#Заголовки], [#Данные], [ПроцентКомиссии]].

Рекомендуется использовать один пробел:

• после первой открывающей квадратной скобки ([)
• Перед последней правой квадратной скобкой (]).
• После запятой.

Операторы ссылок

Перечисленные ниже операторы ссылок служат для составления комбинаций из указателей столбцов, что позволяет более гибко задавать диапазоны ячеек.

Эта структурированная ссылка:	Ссылается на:	Используя:	Диапазон ячеек:
=ОтделПродаж[[Продавец]:[Регион]]	Все ячейки в двух или более смежных столбцах	: (двоеточие) — оператор ссылки	A2:B7
=ОтделПродаж[ОбъемПродаж],ОтделПродаж[ОбъемКомиссии]	Сочетание двух или более столбцов	, (запятая) — оператор объединения	C2:C7, E2:E7
=ОтделПродаж[[Продавец]:[ОбъемПродаж]] ОтделПродаж[[Регион]:[ПроцентКомиссии]]	Пересечение двух или более столбцов	(пробел) — оператор пересечения	B2:C7

Указатели специальных элементов

Чтобы сослаться на определенную часть таблицы, например на строку итогов, в структурированных ссылках можно использовать перечисленные ниже указатели специальных элементов.

Этот указатель специального элемента:	Ссылается на:
#Все	Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).
#Данные	Только строки данных.
#Заголовки	Только строка заголовка.
#Итого	Только строка итога. Если ее нет, будет возвращено значение null.
#Эта строка ИЛИ @ или @[Имя столбца]	Только ячейки в той же строке, где располагается формула. Эти спецификаторы не могут быть объединены с какими-либо другими спецификациями специальных элементов. Используйте их для установки неявного пересечения в ссылке или для переопределения неявного пересечения и ссылки на отдельные значения из столбца. Excel автоматически заменяет указатели "#Эта строка" более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если таблица содержит только одну строку, Excel не заменяет спецификатор #This строки, что может привести к неожиданным результатам вычислений при добавлении дополнительных строк. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

Определение структурированных ссылок в вычисляемых столбцах

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

Тип структурированной ссылки	Пример	Примечания
Неопределенная	=[ОбъемПродаж]*[ПроцентКомиссии]	Перемножает соответствующие значения из текущей строки.
Полностью определенная	=ОтделПродаж[ОбъемПродаж]*ОтделПродаж[ПроцентКомиссии]	Перемножает соответствующие значения из каждой строки обоих столбцов.

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

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

Ниже приведены примеры использования структурированных ссылок.

Эта структурированная ссылка:	Ссылается на:	Диапазон ячеек:
=ОтделПродаж[[#Все],[ОбъемПродаж]]	Все ячейки в столбце "ОбъемПродаж".	C1:C8
=ОтделПродаж[[#Заголовки],[ПроцентКомиссии]]	Заголовок столбца "ПроцентКомиссии".	D1
=ОтделПродаж[[#Итого],[Регион]]	Итог столбца "Регион". Если нет строки итогов, будет возвращено значение ноль.	B8
=ОтделПродаж[[#Все],[ОбъемПродаж]:[ПроцентКомиссии]]	Все ячейки в столбцах "ОбъемПродаж" и "ПроцентКомиссии".	C1:D8
=ОтделПродаж[[#Данные],[ПроцентКомиссии]:[ОбъемКомиссии]]	Только данные в столбцах "ПроцентКомиссии" и "ОбъемКомиссии".	D2:E7
=ОтделПродаж[[#Заголовки],[Регион]:[ОбъемКомиссии]]	Только заголовки столбцов от "Регион" до "ОбъемКомиссии".	B1:E1
=ОтделПродаж[[#Итого],[ОбъемПродаж]:[ОбъемКомиссии]]	Итоги столбцов от "ОбъемПродаж" до "ОбъемКомиссии". Если нет строки итогов, будет возвращено значение null.	C8:E8
=ОтделПродаж[[#Заголовки],[#Данные],[ПроцентКомиссии]]	Только заголовок и данные столбца "ПроцентКомиссии".	D1:D7
=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]] ИЛИ =ОтделПродаж[@ОбъемКомиссии]	Ячейка на пересечении текущей строки и столбца "Сумма комиссионных". Если он используется в той же строке, что и заголовок или итог, возвращается ошибка #VALUE!. Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.	E5 (если текущая строка — 5)

Методы работы со структурированными ссылками

При работе со структурированными ссылками учитывайте следующее:

• Автозаполнение формул может оказаться очень полезным при вводе структурированных ссылок для соблюдения правил синтаксиса. Дополнительные сведения см. в статье Использование автозаполнения формул.

• Выбор необходимости создания структурированных ссылок для таблиц в полувыборках По умолчанию при создании формулы при выделении диапазона ячеек в таблице выделяются частично ячейки и автоматически вводится структурированная ссылка вместо диапазона ячеек в формуле. Псевдовыбор облегчает ввод структурированной ссылки. Вы можете включить или отключить это поведение, установив или сняв флажок "Проверка использования имен таблиц в формулах" в диалоговом окне "Параметры> файла>:формулы:>работа с формулами".

• Использование книг с внешними ссылками на таблицы Excel в других книгах Если книга содержит внешнюю ссылку на таблицу Excel в другой книге, эта связанная исходная книга должна быть открыта в Excel, чтобы избежать ошибок #REF! в конечной книге, содержащей ссылки. Если сначала открыть целевую книгу и появляются ошибки #REF!, они будут устранены при открытии исходной книги. Если сначала открыть исходную книгу, вы не увидите кодов ошибок.

• Преобразование диапазона в таблицу и таблицы в диапазон При преобразовании таблицы в диапазон все ссылки на ячейки заменяются эквивалентными абсолютными ссылками в стиле А1. При преобразовании диапазона в таблицу ссылки на ячейки этого диапазона не изменяются автоматически на эквивалентные структурированные ссылки.

• Отключение заголовков столбцов Вы можете включить или отключить заголовки столбцов таблицы в строке заголовков на вкладке "> Конструктор таблиц". Если отключить заголовки столбцов таблицы, структурированные ссылки с именами столбцов не будут затронуты, и их по-прежнему можно будет использовать в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблиц (например, =DeptSales[[#Headers],[%Commission]]), приведут к #REF.

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

• Переименование таблицы или столбца. Если переименовать столбец или таблицу, в приложении Excel автоматически изменится название этой таблицы или заголовок столбца, используемые во всех структурированных ссылках книги.

• Перемещение, копирование и заполнение структурированных ссылок При копировании или перемещении формулы, содержащей структурированную ссылку, все структурированные ссылки сохраняются.

  Примечание

  Копирование структурированной ссылки и заполнение структурированной ссылки — это не одно и то же. При копировании все структурированные ссылки остаются неизменными, а при заполнении формулой полные структурированные ссылки изменяют спецификаторы столбцов как ряд, как показано в следующей таблице.

Направление заполнения:	Если нажать во время заполнения:	Выполняется действие:
Вверх или вниз	Не нажимать	Указатели столбцов не будут изменены.
Вверх или вниз	CTRL	Указатели столбцов настраиваются как ряд.
Вправо или влево	Нет	Указатели столбцов настраиваются как ряд.
Вверх, вниз, вправо или влево	SHIFT	Вместо перезаписи значений в текущих ячейках их значения перемещаются и вставляются спецификаторы столбцов.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Статьи по теме

Общие сведения о таблицах Excel
Создание и форматирование таблиц
Данные итогов в таблице Excel
Форматирование таблицы Excel
Изменение размера таблицы путем добавления или удаления строк и столбцов
Фильтрация данных в диапазоне или таблице
Преобразование таблицы в диапазон
Проблемы совместимости таблиц Excel
Экспорт таблицы Excel в SharePoint
Обзор формул в Excel