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

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

Прямая ссылка на ячейки

Имена таблицы и столбцов в Excel

=СУММ(C2:C7)

=СУММ(ОтделПродаж[ОбъемПродаж])

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

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

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

Продавец

Регион

Объем Продаж

ПроцентКомиссии

Объ ем Комис сии

Владимир

Северный

260

10 %

Сергей

Южный

660

15 %

Мария

Восточный

940

15 %

Алексей

Западный

410

12 %

Юлия

Северный

800

15 %

Вадим

Южный

900

15 %

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

  2. Чтобы создать таблицу, выделите ячейку A1 и нажмите клавиши CTRL+T.

  3. Установите флажок Таблица с заголовками и нажмите кнопку ОК.

  4. В ячейке E2 введите знак равенства (=), а затем щелкните ячейку C2.

В строке формул после знака равенства появится структурированная ссылка [@[ОбъемПродаж]].

  1. Введите звездочку (*) сразу же после закрывающей скобки и щелкните ячейку D2.

В строке формул после звездочки появится структурированная ссылка [@[ПроцентКомиссии]].

  1. Нажмите клавишу ВВОД.

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

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

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

  1. На листе Excel с примером щелкните ячейку E2.

  2. В строке формул введите = C2 * D2 и нажмите клавишу ВВОД.

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

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

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

  1. Щелкните любую ячейку таблицы Excel, чтобы отобразить на ленте раздел "Работа с таблицами".

  2. На вкладке Конструктор введите нужное имя в поле Имя таблицы и нажмите клавишу ВВОД.

В этом примере мы используем имя ОтделПродаж.

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

  • Использование v alid символов Всегда запускать имени с буквы, символ подчеркивания (_) или обратной косой черты (\). Используйте буквы, числа, периоды и знаков для остальных имя подчеркивания.

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

  • Не используйте c рассказать ссылки Имена не могут быть так же, как ссылки на ячейки, например Z$ 100 или R1C1.

  • Не используйте s в удобном для вас для разделения слов. В имени нельзя использовать пробелы. Вы можете использовать без пробела или введите символ подчеркивания (_) и точкой (.) в качестве разделителей. Например, Отделпродаж, Налог_на_продажи или первый.квартал.

  • Используйте не более 255 знаков. Имя таблицы может содержать не более 255 знаков.

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

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

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

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

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

  • Имя таблицы.   ОтделПродаж — это настраиваемое имя таблицы. Оно ссылается на данные таблицы, за исключением заголовков и строк итогов. Можно использовать имя таблицы по умолчанию (например, "Таблица1") или заменить его собственным.

  • Указатель столбца.   [ОбъемПродаж] и [ОбъемКомиссии] — это указатели столбцов, в которых используются названия соответствующих столбцов. Они ссылаются на данные столбца, за исключением его заголовка и итогов. Всегда заключайте указатели в квадратные скобки, как показано здесь.

  • Указатель элемента.   [#Итого] и [#Данные] — это указатели специальных элементов, которые ссылаются на определенные части таблицы, такие как строка итогов.

  • Указатель таблицы.   [[#Итого],[ОбъемПродаж]] и [[#Данные],[ОбъемКомиссии]] — это указатели таблицы, которые представляют внешние части структурированной ссылки. Внешняя часть следует за именем таблицы и заключается в квадратные скобки.

  • Структурированная ссылка.   ОтделПродаж[[#Итого],[ОбъемПродаж]] и ОтделПродаж[[#Данные],[ОбъемКомиссии]] — это структурированные ссылки, представленные в виде строки, которая начинается с имени таблицы и заканчивается указателем столбца.

При создании или изменении структурированных ссылок вручную учитывайте перечисленные ниже правила синтаксиса.

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

  • Все заголовки столбцов — это текстовые строки    Но они не требуется предложения с расценками, когда они используются структурированной ссылки. Также считаются текстовых строк, чисел и дат, например 1/1/2014 г., или 2014 г. Нельзя использовать выражения с заголовками столбцов. Например, выражение Отделпродажсводкафг [[2014 г]: [2012]] не работают.

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

Дополнительные скобки в формуле нужны при наличии таких специальных знаков:

  • табуляция;

  • перевод строки;

  • возврат каретки;

  • запятая (,);

  • двоеточие (:);

  • точка (.);

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

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

  • решетка (#);

  • одинарная кавычка (');

  • двойная кавычка (");

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

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

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

  • крышка (^);

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

  • звездочка (*);

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

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

  • знак "минус" (-);

  • знак "больше" (>);

  • знак "меньше" (<);

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

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

При наличии таких специальных знаков escape-символ (') в формуле необходим:

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

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

  • решетка (#);

  • одинарная кавычка (').

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

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

  • после первой левой скобки ([);

  • перед последней правой скобкой (]);

  • после запятой.

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

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

Эта структурированная ссылка:

Ссылается на:

Используя:

Диапазон ячеек:

=ОтделПродаж[[Продавец]:[Регион]]

Все ячейки в двух или более смежных столбцах

: (двоеточие) — оператор ссылки

A2:B7

=ОтделПродаж[ОбъемПродаж],ОтделПродаж[ОбъемКомиссии]

Сочетание двух или более столбцов

, (запятая) — оператор объединения

C2:C7, E2:E7

=ОтделПродаж[[Продавец]:[ОбъемПродаж]] ОтделПродаж[[Регион]:[ПроцентКомиссии]]

Пересечение двух или более столбцов

 (пробел) — оператор пересечения

B2:C7

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

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

Этот указатель специального элемента:

Ссылается на:

#Все

Вся таблица, включая заголовки столбцов, данные и итоги (если они есть).

#Данные

Только строки данных.

#Заголовки

Только строка заголовка.

#Итого

Только строка итога. Если ее нет, будет возвращено значение null.

#Эта строка

ИЛИ

@

ИЛИ

@[Имя столбца]

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

Excel автоматически заменяет указатели "#Эта строка" более короткими указателями @ в таблицах, содержащих больше одной строки данных. Но если в таблице только одна строка, Excel не заменяет указатель "#Эта строка", и это может привести к тому, что при добавлении строк вычисления будут возвращать непредвиденные результаты. Чтобы избежать таких проблем при вычислениях, добавьте в таблицу несколько строк, прежде чем использовать формулы со структурированными ссылками.

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

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

Тип структурированной ссылки

Пример

Примечания

Неопределенная

=[ОбъемПродаж]*[ПроцентКомиссии]

Перемножает соответствующие значения из текущей строки.

Полностью определенная

=ОтделПродаж[ОбъемПродаж]*ОтделПродаж[ПроцентКомиссии]

Перемножает соответствующие значения из каждой строки обоих столбцов.

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

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

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

Эта структурированная ссылка:

Ссылается на:

Диапазон ячеек:

=ОтделПродаж[[#Все],[ОбъемПродаж]]

Все ячейки в столбце "ОбъемПродаж".

C1:C8

=ОтделПродаж[[#Заголовки],[ПроцентКомиссии]]

Заголовок столбца "ПроцентКомиссии".

D1

=ОтделПродаж[[#Итого],[Регион]]

Итог столбца "Регион". Если нет строки итогов, будет возвращено значение ноль.

B8

=ОтделПродаж[[#Все],[ОбъемПродаж]:[ПроцентКомиссии]]

Все ячейки в столбцах "ОбъемПродаж" и "ПроцентКомиссии".

C1:D8

=ОтделПродаж[[#Данные],[ПроцентКомиссии]:[ОбъемКомиссии]]

Только данные в столбцах "ПроцентКомиссии" и "ОбъемКомиссии".

D2:E7

=ОтделПродаж[[#Заголовки],[Регион]:[ОбъемКомиссии]]

Только заголовки столбцов от "Регион" до "ОбъемКомиссии".

B1:E1

=ОтделПродаж[[#Итого],[ОбъемПродаж]:[ОбъемКомиссии]]

Итоги столбцов от "ОбъемПродаж" до "ОбъемКомиссии". Если нет строки итогов, будет возвращено значение null.

C8:E8

=ОтделПродаж[[#Заголовки],[#Данные],[ПроцентКомиссии]]

Только заголовок и данные столбца "ПроцентКомиссии".

D1:D7

=ОтделПродаж[[#Эта строка], [ОбъемКомиссии]]

ИЛИ

=ОтделПродаж[@ОбъемКомиссии]

Ячейка на пересечении текущей строки и столбца "ОбъемКомиссии". Если эта ссылка используется в строке заголовка или итога, она вернет ошибку #ЗНАЧ.

Если ввести длинную форму этой структурированной ссылки (#Эта строка) в таблице с несколькими строками данных, Excel автоматически заменит ее укороченной формой (со знаком @). Две эти формы идентичны.

E5 (если текущая строка — 5)

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

При работе со структурированными ссылками рекомендуется обращать внимание на перечисленные ниже аспекты.

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

  • Целесообразность создания структурированных ссылок в режиме псевдовыбора.    По умолчанию, когда создается формула, то при выборе диапазона ячеек внутри таблицы с помощью мыши выполняется псевдовыбор ячеек и вместо диапазона ячеек в формуле автоматически вводится структурированная ссылка. Псевдовыбор облегчает ввод структурированной ссылки. Эту функцию можно включить или отключить, установив или сняв флажок Использовать имена таблиц в формулах (диалоговое окно Параметры Excel, категория Формулы, раздел Работа с формулами).

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

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

  • Отключение заголовков столбцов.    Отключение заголовков столбцов таблицы (на вкладке Конструктор в группе Параметры стилей таблиц снимите флажок Строка заголовка) не влияет на структурированные ссылки, использующие эти имена столбцов; их все равно можно использовать в формулах. Структурированные ссылки, которые ссылаются непосредственно на заголовки таблицы (например, =ОтделПродаж[[#Заголовки],[ПроцентКомиссии]]), приведут к возникновению ошибки #ССЫЛКА.

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

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

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

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

Направление заполнения:

Если нажать во время заполнения:

Выполняется действие:

Вверх или вниз

Не нажимать

Указатели столбцов не будут изменены.

Вверх или вниз

CTRL

Указатели столбцов настраиваются как ряд.

Вправо или влево

Нет

Указатели столбцов настраиваются как ряд.

Вверх, вниз, вправо или влево

SHIFT

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

Примечания : 

  • Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

См. также

Общие сведения о формулах в Excel

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

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×