Укажите правильные обозначения диапазонов таблицы в программе excel

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Mobile Еще. Меньше

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

Вместо использования явных ссылок на ячейки

Excel использует имена таблиц и столбцов

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

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

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

Продавец

Сумма продажи

% комиссии

Сумма комиссии

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

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

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

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

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

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

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

Нажмите Enter.

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

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

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

На образце рабочего листа щелкните ячейку E2

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

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

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

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

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

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

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

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

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

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

Не используйте пробел для разделения слов Пробелы нельзя использовать в имени. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.). Например, DeptSales, Sales_Tax или First.Quarter.

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

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

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

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

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

Эта формула содержит следующие компоненты структурированных ссылок:

Имя таблицы: DeptSales — это пользовательское имя таблицы. Он ссылается на данные таблицы без каких-либо заголовков или итоговых строк. Вы можете использовать имя таблицы по умолчанию, например Table1, или изменить его, чтобы использовать собственное имя.

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

Для создания или редактирования структурированных ссылок вручную используйте следующие правила синтаксиса:

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

Все заголовки столбцов представляют собой текстовые строки. Но они не требуют кавычек, когда используются в структурированной ссылке. Числа или даты, например 2014 или 01.01.2014, также считаются текстовыми строками. Вы не можете использовать выражения с заголовками столбцов. Например, выражение DeptSalesFYSummary[[2014]:[2012]] не будет работать.

Используйте скобки вокруг заголовков столбцов со специальными символами. Если есть специальные символы, весь заголовок столбца должен быть заключен в квадратные скобки, что означает, что в спецификаторе столбца требуются двойные скобки. Например: =DeptSalesFYSummary[[Общая сумма в долларах США]]

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

Одинарная кавычка (')

Двойная кавычка ("")

Больше символа (>)

Вот список специальных символов, для которых требуется escape-символ (‘) в формуле:

Одинарная кавычка (')

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

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

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

Ссылочные операторы

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

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

С помощью:

Что такое диапазон ячеек:

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

: (двоеточие) оператор диапазона

=DeptSales[Сумма продаж],DeptSales[Сумма комиссии]

Комбинация двух или более столбцов

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

=Отдел продаж[[Продавец]:[Сумма продажи]] Отдел продаж[[Регион]:[% комиссии]]

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

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

Особые спецификаторы элементов

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

Этот специальный спецификатор элемента:

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

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

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

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

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

Квалификация структурированных ссылок в вычисляемых столбцах

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

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

=[Сумма продажи]*[% комиссии]

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

=DeptSales[Сумма продаж]*DeptSales[% комиссии]

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

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

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

Вот несколько способов использования структурированных ссылок.

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

Что такое диапазон ячеек:

Все ячейки в столбце "Сумма продаж".

Заголовок столбца "% комиссии".

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

Все ячейки в поле "Сумма продаж" и "% комиссии".

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

Только заголовки столбцов между «Регион» и «Сумма комиссии».

Итого в столбцах «Сумма продаж» по «Сумме комиссии».Если строки "Итоги" нет, возвращается значение null.

Только заголовок и данные % комиссии.

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

Стратегии работы со структурированными ссылками

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

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

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

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

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

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

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

Вы можете дополнительно настроить форматирование таблицы, выбрав параметры экспресс-стилей для элементов таблицы, таких как "Заголовок" и "Итого строк", "Первый и последний столбцы", "Связанные строки и столбцы", а также с помощью автоматической фильтрации.

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

Выберите стиль таблицы

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

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

На вкладке "Главная" нажмите "Форматировать как таблицу".

Выберите стиль таблицы, который хотите использовать.

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

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

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

Пользовательские стили таблиц сохраняются только в текущей книге и недоступны в других книгах.

Создайте собственный стиль таблицы

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

На вкладке "Главная" нажмите "Форматировать как таблицу" или разверните галерею "Стили таблиц" на вкладке "Инструменты для таблиц" > "Дизайн" (вкладка "Таблица" на Mac).

Нажмите «Новый стиль таблицы», чтобы открыть диалоговое окно «Новый стиль таблицы».

В поле Имя введите имя нового стиля таблицы.

В поле Элемент таблицы выполните одно из следующих действий:

Чтобы отформатировать элемент, щелкните элемент, затем нажмите «Формат», а затем выберите нужные параметры форматирования на вкладках «Шрифт», «Граница» или «Заливка».

Чтобы удалить существующее форматирование элемента, щелкните элемент, а затем нажмите "Очистить".

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

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

Удалить пользовательский стиль таблицы

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

В разделе "Пользовательский" щелкните правой кнопкой мыши стиль таблицы, который нужно удалить, и выберите "Удалить" в контекстном меню.

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

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

Нажмите "Очистить".

Таблица будет отображаться в формате таблицы по умолчанию.

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

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

Выберите любую ячейку в таблице.

Перейдите в раздел Инструменты для работы с таблицами > Дизайн или вкладку Таблица на Mac и в группе Параметры стиля таблицы установите или снимите флажки со следующих параметров:

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

Ряд итогов. Быстро добавляйте в таблицу функции ПРОМЕЖУТОЧНЫЕ ИТОГОВ, такие как СУММ, СРЕДНЕЕ, СЧЕТЧИК, МИН/МАКС, из раскрывающегося списка. Функции ПРОМЕЖУТОЧНЫХ ИТОГОВ позволяют включать или игнорировать скрытые строки в расчетах.

Первый столбец — применение или удаление форматирования к первому столбцу таблицы.

Последний столбец — применение или удаление форматирования к последнему столбцу таблицы.

Ряды с полосами — отображайте нечетные и четные строки с чередующимся затенением для удобства чтения.

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

Кнопка «Фильтр» — включение и выключение автофильтра.

В Excel для Интернета можно применить параметры стиля таблицы для форматирования элементов таблицы.

Выберите параметры стиля таблицы для форматирования элементов таблицы

Выберите любую ячейку в таблице.

На вкладке "Конструктор таблиц" в разделе "Параметры стиля" установите или снимите любой из следующих параметров:

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

Ряды с полосами — отображайте нечетные и четные строки с чередующимся затенением для удобства чтения.

Первый столбец — применение или удаление форматирования к первому столбцу таблицы.

Последний столбец — применение или удаление форматирования к последнему столбцу таблицы.

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

Кнопка «Фильтр» — включение и выключение автофильтра.

Нужна дополнительная помощь?

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

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

Важно! Для преобразования в диапазон у вас должна быть таблица Excel для начала. Дополнительные сведения см. в разделе Создание или удаление таблицы Excel.

Нажмите в любом месте таблицы, а затем выберите Работа с таблицами > Дизайн на ленте.

В группе "Инструменты" нажмите "Преобразовать в диапазон".

Нажмите правой кнопкой мыши на таблицу, затем в контекстном меню выберите Таблица > Преобразовать в диапазон.

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

Нажмите в любом месте таблицы, а затем перейдите на вкладку "Таблица".

Нажмите Преобразовать в диапазон.

Нажмите "Да", чтобы подтвердить действие.

Нужна дополнительная помощь?

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

Примечание. Таблицы Excel не следует путать с таблицами данных, которые являются частью набора команд анализа "что, если". Дополнительные сведения о таблицах данных см. в разделе Вычисление нескольких результатов с помощью таблицы данных.

Узнайте об элементах таблицы Excel

Таблица может включать следующие элементы:

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

Вы можете отключить строку заголовка в таблице. Дополнительные сведения см. в разделе Включение и отключение заголовков таблицы Excel.

Ряды с полосами Чередующееся затенение или полоса в строках помогает лучше различать данные.

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

Строка итогов После добавления строки итогов в таблицу Excel предоставляет раскрывающийся список Автосумма для выбора из таких функций, как СУММ, СРЗНАЧ и т. д. Когда вы выберете один из этих параметров, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая будет игнорировать строки, которые по умолчанию были скрыты фильтром. Если вы хотите включить в свои расчеты скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

Другие способы изменения размера таблицы см. в разделе Изменение размера таблицы путем добавления строк и столбцов.

Создать таблицу

Вы можете создать сколько угодно таблиц в электронной таблице.

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

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

Выберите Главная > Форматировать как таблицу.

Выберите стиль таблицы.

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

Эффективная работа с табличными данными

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

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

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

Экспорт таблицы Excel на сайт SharePoint

Если у вас есть авторский доступ к сайту SharePoint, вы можете использовать его для экспорта таблицы Excel в список SharePoint. Таким образом, другие люди смогут просматривать, редактировать и обновлять данные таблицы в списке SharePoint. Вы можете создать одностороннее подключение к списку SharePoint, чтобы обновлять данные таблицы на листе, чтобы включить изменения, внесенные в данные в списке SharePoint. Дополнительные сведения см. в разделе Экспорт таблицы Excel в SharePoint.

Нужна дополнительная помощь?

Читайте также: