Использование структурированных ссылок в таблицах 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. Чтобы создать таблицу, выделите любую ячейку в диапазоне данных и нажмите Клавиши Ctrl + T.

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

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

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

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

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

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

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

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

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

  1. В образце на листе щелкните ячейку E2

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

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

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

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

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

  2. Введите имя в поле Имя таблицы и нажмите клавишу Ввод.

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

Для имен таблиц используются следующие правила:

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

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

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

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

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

  • Используйте идентификатор объекта  Если вы планируете одновременно таблицы, сводные таблицы и диаграммы, рекомендуется префикса имен с типом объекта. Например: tbl_Sales для таблицы sales, pt_Sales по продажам сводной таблицы и chrt_Sales для продажи диаграммы или ptchrt_Sales для продажи сводной диаграммы. В этом случае всех имен в упорядоченного списка в окне Диспетчер имен.

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

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

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

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

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

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

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

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

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

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

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

  • Все заголовки столбцов — это текстовые строки    Но они не требуется предложения с расценками, когда они используются структурированной ссылки. Также считаются текстовых строк, чисел и дат, например 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

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

ИЛИ

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

Ячейка на пересечении текущей строки и столбца Объемкомиссии. Если используется в одну строку заголовка или строки итогов, то возвращается #VALUE! ошибки.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Не нажимать

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

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

CTRL

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

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

Нет

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

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

SHIFT

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также:

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

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

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

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

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

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

×