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

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

Также можно ссылаться на данные из других листов. Информацию о ссылках на данные из других листов см. В разделе Формулы: справочные данные из других листов.

Сводка типов ссылок на формулы

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

Чтобы сослаться на это Отформатируйте это так
Отдельная ячейка Имя столбца, номер строки = Budget1
Имя столбца содержит пробел или заканчивается числом Заключите имя столбца в скобки. = [Столбец A] 1
= DAY ([Q1] 1)
Абсолютная ссылка (всегда относится к этой конкретной ячейке, строке или столбцу) Введите символ $ перед именем столбца, номером строки или обоими. = $ [Столбец A] $ 1
= [Столбец B] $ 1
= $ [Столбец C] 1
Несколько прерывистых ячеек Используйте запятую между ссылками на ячейки . = SUM (Бюджет1, Расходы4, [Прогнозируемая прибыль] 20)
Диапазон ячеек в одном столбце Ссылка на первую ячейку в диапазоне, затем на последнюю ячейку, разделенную двоеточием. = SUM (Budget1: Budget12)
Весь столбец (включая все новые добавленные ячейки) Имя столбца, разделенное знаком: (двоеточие). = SUM (Бюджет: Бюджет)
Диапазон ячеек в нескольких столбцах Ссылка на крайнюю правую верхнюю ячейку, затем крайняя левая ячейка, разделенная знаком: (двоеточие). = SUM (1 января: 5 марта)
отдельная ячейка, диапазон ячеек или полные столбцы с другого листа Введите имя ранее созданной ссылки на другой лист, заключенное в фигурные скобки. = COUNT ({my_sheet1 Range1})

Более подробное описание ссылочных типов

Ссылка на отдельные ячейки

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

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

Формула возвращает 2 994,00 доллара США, общая стоимость этого элемента:

Имена ссылочных столбцов, содержащие пробелы или заканчивающиеся цифрами

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

= [Годовой бюджет] 1 + [Годовой бюджет] 2

= [Q1] 1 + [Q2] 1

= [Риск/Проблема] 5 + [Непредвиденные обстоятельства/Смягчение] 5

Создать абсолютную ссылку

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

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

= $ [Столбец A] $ 1 * $ [Столбец B] $ 1

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

= [Column A] $ 1 * [Column B] $ 1

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

= $ [Column A] 1 * $ [Column B] 1

Ссылка на диапазон ячеек в одном столбце

Чтобы ссылаться на диапазон ячеек, введите : (двоеточие) между двумя ссылками на ячейки.

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

Формула возвращает 40 763,75 долларов США, итого всех значений запасов:

Ссылка на весь столбец

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

= SUM ([Годовой бюджет]: [Годовой бюджет])

ПРИМЕЧАНИЕ. Если формула помещена в столбец, на который она ссылается, она не будет ссылаться на ячейку, в которой находится формула. Например, если вы должны были поместить приведенный выше пример формулы в столбец с именем «Годовой бюджет», формула SUM суммирует все ячейки, кроме ячейки, содержащей формулу.

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

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

Например, формула в столбце «Общий запас» следующей таблицы управления запасами суммирует значения из столбцов «Запас A», «Запас B» и «Запас C» в строке 1:

Формула возвращает 998, общее акции из трех мест:



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

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

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

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

= Sum (C2: C7)

= SUM (DeptSales [Сумма продаж])

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

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

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

Продажи Человек

Регион

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

Комиссия%

Размер комиссии

Джо

Север

260

10%

Роберт

Юг

660

15%

Мишель

Восток

940

15%

Эрих

Запад

410

12%

Дафна

Север

800

15%

Роб

Итак uth

900

15%

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

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

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

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

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

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

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

  6. Нажмите Enter .

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

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

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

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

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

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

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

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

  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 для сводной диаграммы продаж. При этом все ваши имена сохраняются в упорядоченном списке в диспетчере имен.

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

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

= SUM (DeptSales [[# Totals], [Sales Amount]], DeptSales [[# Data] , [Размер комиссии]])

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

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

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

  • Спецификатор элемента: [# Итоги] и [# Data] — специальные описатели элементов, которые относятся к определенным частям таблицы, например к итоговой строке.

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

  • Структурированная ссылка: (DeptSales [[# Totals], [Sales Amount]] и DeptSales [[# Data], [Commission Amount]] — это структурированные ссылки, представленные строкой, начинающейся с имя таблицы и заканчивается спецификатором столбца.

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

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

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

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

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

  • Tab

  • Перевод строки

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

  • Запятая (,)

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

  • Период (. )

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

  • Правая скобка (])

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

  • Одиночная кавычка (‘)

  • Двойные кавычки («)

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

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

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

  • Карет (^)

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

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

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

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

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

  • Больше, чем символ (>)

  • Меньше символа (

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

  • Используйте escape-символ для некоторых специальных символов в заголовках столбцов Некоторые символы имеют особое значение и требуют использования одинарная кавычка (‘) в качестве escape-символа. Например: =DeptSalesFYSummary[‘#OfItems ]

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

  • Левая скобка ([)

  • Правая скобка (])

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

  • Одиночная кавычка (‘)

Используйте пробел, чтобы улучшить читаемость структурированной справки. Вы могут использовать пробелы, чтобы улучшить читаемость структурированной ссылки. Например: = DeptSales [[Продавец]: [Регион]] или = DeptSales [[# Headers], [#Data], [% Commission]]

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

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

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

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

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

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

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

Относится к:

При использовании:

Какой диапазон ячеек:

= DeptSales [[продавец]: [регион]]

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

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

A2: B7

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

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

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

C2: C7, E2: E7

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

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

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

B2: C7

Спецификация ial спецификаторы элементов

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

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

Относится к:

#All

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

#Data

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

#Headers

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

#Totals

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

#This Row

или

@

или

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

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

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

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

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

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

Пример

Комментарий

Unqualified

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

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

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

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

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

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

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

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

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

Относится к:

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

= DeptSales [[# All] , [Сумма продаж]]

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

C1: C8

= DeptSales [[# Headers], [% Commission]]

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

D1

= DeptSales [[# Totals], [Region]]

Общая сумма Столбец региона. Если строки итогов нет, возвращается null.

B8

= DeptSales [[# All], [Sales Amount]: [% Commission]]

Все ячейки в Sales Amount и% комиссии.

C1: D8

= DeptSales [[# Data], [% Commission]: [Commission Amount]]

Только данные столбцов% Commission и Commission Amount.

D2: E7

= DeptSales [[#Headers], [Region]: [Commission Amount]]

Только заголовки столбцов между Region и Commission Amount.

B1: E1

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

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

C8: E8

= DeptSales [[# заголовки], [# данные], [% комиссии]]

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

D1: D7

= DeptSales [[#This Row], [Commission Amount]]

или

= DeptSales [@Commission Amount]

Ячейка на пересечении текущей строки и столбца «Сумма комиссии». Если используется в той же строке, что и заголовок или итоговая строка, это вернет #VALUE! ошибка.

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

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

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

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

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

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

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

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

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

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

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

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

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

Если направление заливки:

И во время заполнения вы нажимаете:

Затем:

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

Ничего

Регулировка спецификатора столбца отсутствует.

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

Ctrl

Спецификаторы столбца настраиваются как серия.

Справа или слева

None

Спецификаторы столбца настраиваются как ряд.

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

Shift

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

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

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

Связанные темы

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

Оцените статью
logicle.ru
Добавить комментарий