- Структурированные ссылки позволяют использовать имена таблиц и столбцов вместо диапазонов ячеек, что повышает ясность и удобство сопровождения формул.
- Его синтаксис объединяет имя таблицы, спецификаторы элементов (#Data, #Totals, #Headers, @) и имена столбцов в квадратных скобках.
- Они автоматически обновляются при добавлении, удалении или переименовании строк, столбцов или таблиц, предотвращая типичные ошибки, связанные с адресами в формате A1.
- Правильное использование имен, автозаполнения и операторов ссылок делает таблицы Excel мощными инструментами для углубленного анализа.
Если вы часто работаете с таблицами в Excel, рано или поздно вы столкнетесь с этим. структурированные ссылкиПоначалу они могут показаться странными или непонятными, но как только вы разберетесь в них, они станут очень удобным инструментом для создания понятных и простых в поддержании формул.
Идея очень проста: вместо использования ссылок типа C2:C7, Excel позволяет использовать имена таблиц и столбцов, например: Отдел продаж o Отдел заработной платыТаким образом, вы с первого взгляда понимаете, что вычисляет формула, а ссылки автоматически корректируются при добавлении или удалении строк и столбцов из таблицы. Давайте спокойно рассмотрим это шаг за шагом, с множеством практических примеров.
Что именно представляют собой структурированные ссылки в Excel?
При преобразовании диапазона данных в таблицу Excel автоматически создает таблицу. имя для этой таблицы и для каждого из заголовков ее столбцовС этого момента вы можете использовать эти имена в формулах как внутри, так и вне самой таблицы.
Например, вместо того, чтобы писать формулу, подобную этой: =СУММ(C2:C7)Вы сможете написать что-то подобное: =СУММА(Отдел продаж)где "DeptVentas" — это имя таблицы, а "Amount de ventas" — имя столбца. Такое сочетание имени таблицы и имени столбца называется структурированная ссылка.
Эти источники обладают ключевым преимуществом: они обновляются сами При изменении таблицы, например, при добавлении еще одной строки с данными о продажах или вставке нового столбца, структурированная ссылка адаптируется, тогда как классическая ссылка C2:C7 может перестать охватывать весь необходимый диапазон, что потребует ручной корректировки формул.
Кроме того, структурированные ссылки можно использовать и вне таблиц. Это очень удобно, когда у вас книга, состоящая из нескольких страниц и множества таблиц, потому что Обратите внимание на понятные названия, такие как SalesDept. Это гораздо интуитивнее, чем расшифровка диапазонов A2:B7, разбросанных по всему файлу.
Создайте таблицу и используйте базовые структурированные ссылки.
Первый шаг к тому, чтобы научиться использовать структурированные ссылки, — это... Преобразуйте ваш диапазон в таблицу Excel.Будь то данные о продажах, зарплаты или любая другая информация, процедура всегда одинакова.
Представьте себе таблицу со следующими столбцами: Продавец, Регион, Сумма продаж, Процент комиссии и Сумма комиссии. Вы копируете данные на новый лист, включая заголовки, и вставляете их, начиная с первого столбца. ячейка А1Далее выберите любую ячейку в этом диапазоне и нажмите Ctrl + T чтобы Excel мог сгенерировать таблицу.
В появившемся окне подтвердите, что выбрана соответствующая опция. «В таблице есть заголовки» Функция включена и принята. Excel применит табличный дизайн, и с этого момента у вас будет доступ к структурированным ссылкам на основе этих заголовков.
Допустим, вы хотите рассчитать комиссию в столбце «Сумма комиссии», умножив сумму продаж на процент комиссии. Перейдите в ячейку E2, введите знак равенства (=) и щелкните ячейку C2 (Сумма продаж). В строке формул появится что-то подобное: ] вместо C2.
Далее введите звездочку (*) и щелкните ячейку D2 (процент комиссии). Excel автоматически заполнит формулу. ]Итоговая формула будет выглядеть примерно так: =]*]При нажатии клавиши Enter Excel автоматически создаст файл. вычисляемый столбец, скопировав формулу вниз и внутренне скорректировав ссылки на соответствующую строку.
Отличия от классических клеточных эталонов
Вы всегда можете выполнить вышеуказанный расчет, используя обычные ссылки на ячейки, например, написав... = C2 * D2 в ячейке E2. Excel также продублирует формулу вниз, но в этом случае вы будете использовать явные, а не структурированные ссылки.
Проблема такого подхода двояка. С одной стороны, формула менее читабельнаЕсли вы откроете этот файл через месяц, вам придётся вспомнить, что было в ячейках C2 и D2. Кроме того, любые структурные изменения (вставка нового столбца, перемещение столбца в другое место) могут заставить вас пересмотреть формулы, потому что Обозначение C2*D2 больше не будет иметь прежнего значения..
В случае со структурированными ссылками верно обратное. Благодаря таким выражениям, как... ] *]Вы с первого взгляда понимаете, какие столбцы задействованы, и если вы вставите между ними столбец, формула останется в силе. То есть, они являются более устойчив к изменениям и они уберегут вас от глупых ошибок.
Зачастую Excel сам предлагает структурированные ссылки при написании формул внутри таблицы, поэтому вам практически не нужно запоминать синтаксис; даже внешние помощники, такие как... ChatGPT для Excel Они могут помочь. Просто начните печатать, и пусть функция сделает свою работу. Автозаполнение формул Выполняйте основную работу.
Даже работая вне таблицы, вы можете щелкнуть и перетащить курсор по ячейкам таблицы во время ввода формулы, и Excel напрямую вставит структурированную ссылку вместо диапазона, например, A2:A100.
Как правильно называть и изменять таблицы в Excel
При каждом создании таблицы Excel присваивает ей общее имя, примерно такое: Таблица 1, Таблица 2 и так далее. Хотя эти названия и подходят, наиболее практичным вариантом является... Переименуйте таблицу, дав ей описательное имя. Позвольте мне рассказать, что означают эти данные.
Чтобы изменить имя, выберите любую ячейку в таблице, и появится вкладка «Конструктор таблиц» (в зависимости от версии — «Конструктор таблиц» или «Конструктор»). В поле «Имя таблицы» введите что-нибудь более осмысленное, например: Отдел продаж, Отдел заработной платы или аналогичное имя, и нажмите Enter. С этого момента все структурированные ссылки будут использовать это имя.
В Excel действуют определенные правила для имен таблиц. Они должны начинаться с символа '.'. буква, нижнее подчеркивание (_) или обратная косая черта (\)Далее вы можете использовать буквы, цифры, точки и подчеркивания. Однако вы не можете использовать имена, соответствующие ссылкам на ячейки, например, Z$100 или R1C1, а также не можете просто использовать "C", "c", "R" или "r", поскольку Excel резервирует их как сочетания клавиш для выбора столбцов или строк.
В имени также не допускаются пробелы. Вместо этого можно использовать подчеркивания или точки в качестве разделителяМожно создавать названия, например, SalesDept, SalesTax, FirstQuarter или PurchaseBonus. Еще одно ограничение — длина: название не может превышать Символы 255, что на практике почти никогда не станет проблемой.
Названия таблиц должны быть уникальными как в пределах рабочей книги, так и в файле Excel. Он не различает верхний и нижний регистрЭто означает, что если у вас уже есть таблица с названием SALES, вы не можете создать еще одну с тем же названием. Распространенный прием для организации работы — использование префиксов, указывающих на тип объекта, например: tbl_Sales для обычного стола, pt_Sales для сводной таблицы и chrt_Sales Для диаграммы. Таким образом, в Диспетчере имен у вас все будет идеально отсортировано по категориям.
Подробный синтаксис структурированных ссылок
Помимо простых примеров, структурированные ссылки обладают следующими свойствами: очень мощный синтаксис Это позволяет вам выбирать конкретные части таблицы: заголовки, строки данных, итоги, целые столбцы, пересечения и т. д. Овладение этими элементами имеет решающее значение при построении сложных моделей.
Типичная формула может выглядеть примерно так: =SUM(SalesDept,],SalesDept,])Здесь суммируются две величины: с одной стороны, сумма в столбце «Сумма продаж» (строка итогов), а с другой стороны, все данные в столбце «Сумма комиссии».
В этой формуле можно выделить несколько компонентов. название таблицы (например, SalesDept или SalaryDept) обозначает таблицу, с которой вы работаете. Появляется следующее: спецификаторы элементовКак o Это указывает на то, имеете ли вы в виду всю таблицу целиком, только данные, заголовки или строку с итоговыми результатами.
Тогда есть спецификаторы столбцовЭти спецификаторы используют имена заголовков. Например, , , или . Эти спецификаторы ссылаются на данные в этом столбце, не включая автоматически заголовок или строку итогов, если только вы не объедините спецификатор столбца со спецификатором элемента.
Наконец, набор, подобный ,] o ,] вести себя как спецификатор таблицыЭта строка, точно определяющая, какую часть таблицы вы хотите суммировать, подсчитывать, усреднять и т. д., целиком и полностью определяет структурированную ссылку.
При использовании этого синтаксиса помните, что Все уточнения должны быть заключены в квадратные скобки. Если вы включите другие спецификаторы внутри спецификатора, у вас будут вложенные квадратные скобки, и вам понадобятся внешние квадратные скобки для заключения набора, как в =Отдел продаж:], что относится ко всем ячейкам между столбцами «Коммерческий» и «Регион».
Специальные символы и пробелы в заголовках столбцов
Заголовки столбцов в структурированных ссылках обрабатываются внутри системы как текстовые строкиНо заключать их в кавычки не нужно. Они просто помещаются в квадратные скобки. Однако, если заголовок содержит определенные специальные символы (например, знаки препинания, пробелы или символы), Excel требует, чтобы они были заключены в кавычки. Весь заголовок следует заключить в дополнительные скобки..
Например, если у вас есть столбец с названием "Итоговая сумма в долларах", вам нужно будет написать что-то вроде: [Сводка по отделу продаж за год]Использование двойных квадратных скобок гарантирует, что Excel интерпретирует весь этот текст, включая пробелы, знаки доллара или знаки препинания, как имя столбца.
К символам, требующим использования дополнительных квадратных скобок, относятся: табуляция, переносы строк, перевод строки, запятые, двоеточия, точки, фигурные скобки, символ решетки (#), одинарные и двойные кавычки, фигурные скобки, знак доллара, каретка, амперсанд (&), звездочка, знаки плюс и минус, знак равенства, знаки больше и меньше, косая черта, знак @, обратная косая черта (\), восклицательный знак, скобки, знак процента, вопросительный знак, диакритический знак, точка с запятой, тильда и подчеркивание.
Кроме того, некоторые из этих символов имеют особое значение в Excel и требуют... экранированный символОбычно это одинарная кавычка ('), внутри ссылки. Например, если ваш заголовок содержит символ # или одинарную кавычку, вам может потребоваться написать что-то вроде: =РождественскийБонусОтделГод чтобы Excel мог правильно это интерпретировать.
Ещё одна важная рекомендация заключается в том, что для улучшения читаемости можно использовать пробелы внутри самого структурированного документа, например, в =Отдел продаж:] или в формулах, где перечислены несколько спецификаторов, например, ,,]. Обычно оставляют пробел после открывающей скобки, перед закрывающей скобкой и после точки с запятой который разделяет несколько аргументов внутри ссылки.
Операторы ссылок: диапазоны, объединения и пересечения
Структурированные справочные материалы также подходят для классической литературы. операторы ссылок ExcelЭто позволяет создавать диапазоны, комбинации столбцов или пересечения между областями таблицы без необходимости использования ссылок типа A1.
Когда вы используете оператор диапазона (двоеточие :), например: Отдел продаж:]Вы имеете в виду все ячейки в нескольких смежных столбцах. Если рассматривать их в терминах ячеек, то это будет что-то вроде A2:B7 или аналогичный диапазон, но в структурированном виде.
Если же вы хотите создать комбинацию несмежных столбцов, вы можете использовать следующий способ: оператор профсоюза (точка с запятой 😉), Так, например, Отдел продаж; Отдел продаж Выбирает два разных столбца, как если бы вы набирали C2:C7;E2:E7. Это полезно в функциях, которые принимают в качестве аргументов несколько диапазонов.
И, наконец, оператор перекресткаПробел, обозначаемый в формулах как пустое место, позволяет получить область пересечения двух диапазонов. В качестве примера можно привести следующее: Отдел продаж:] Отдел продаж:] Это указывает на пересечение между этими блоками столбцов, что эквивалентно промежуточному диапазону типа B2:C7 в классических справочниках.
Благодаря этим операторам вы можете создавать очень гибкие реферальные программы и продолжать получать от них выгоду. автоматическое обновление что таблицы Excel предоставляют при вставке или удалении строк и столбцов.
Специализированные идентификаторы элементов (#All, #Data, #Headers, #Totals, @)
Одним из ключевых элементов структурированных ссылок являются спецификаторы специальных элементовЭти уточнения указывают, какую именно часть таблицы вы хотите использовать в формуле. Они всегда записываются в квадратных скобках и начинаются с символа решетки (#), за исключением ссылки на текущую строку, которая обычно обозначается символом @.
Спецификатор Это относится ко всей таблице, включая заголовки, строки данных и строку итогов (если она существует). Это очень полезно в функциях, которым необходимо учитывать всю таблицу для корректной работы.
Спецификатор Это касается только строк данных, исключая заголовки и итоговые суммы. Между тем, Он указывает только на строку заголовка, и Это относится исключительно к строке с итоговыми значениями. Если эта строка с итоговыми значениями не активна в таблице, ссылка вернет значение NULL.
Для ссылки на текущая строка В вычисляемом столбце используются спецификаторы. или, короче говоря, символ @, Так, например, [Отдел продаж] Это относится к ячейке в столбце «Сумма комиссии» в той же строке, что и формула. Excel обычно автоматически заменяет её на символ «@», если таблица содержит более одной строки, поэтому вы, как правило, увидите сокращённую версию.
Следует отметить, что #Эта строка и символ @ не могут быть объединены с другими специальными спецификаторами элементов.Кроме того, если вы используете эти ссылки в строке заголовка или строке итогов, вы, скорее всего, получите ошибки #VALUE!, поскольку там нет "строки текущих данных" в обычном понимании.
Условные и неусловные ссылки на столбцы
При работе с таблицами Excel позволяет использовать следующие функции: более короткие структурированные ссылкиНеполные ссылки используются потому, что контекст таблицы сам по себе служит ссылкой. Однако при написании формул вне таблицы необходимо использовать полное имя или полную ссылку, включающую имя таблицы.
Например, в вычисляемом столбце таблицы "SalesDept" можно просто написать =*Excel понимает, что эти заголовки относятся к текущей таблице, поэтому нет необходимости писать DeptVentas перед каждым столбцом.
Однако, если вы хотите выполнить те же вычисления вне таблицы, вам потребуется использовать полную версию: =Отдел продаж*Отдел продажОбщее правило очень простое: Внутри таблицы допускаются неполные ссылки; за ее пределами необходимо использовать полное название таблицы..
Это улучшает как читаемость, так и удобство сопровождения формул, избегая неоднозначностей, когда у вас есть несколько таблиц с заголовками, которые могут повторяться в одной и той же рабочей книге Excel.
Практические примеры использования структурированных ссылок
Для подтверждения вышесказанного полезно рассмотреть несколько типичных примеров и их эквиваленты в ссылках на ячейки. Например, такая ссылка: Отдел продаж,] Это относится ко всем ячейкам в столбце «Сумма продаж», включая заголовок и, если имеется, строку с итоговыми значениями. Это эквивалентно, например, ячейкам C1:C8 в стандартном диапазоне.
Если вам нужен только заголовок столбца "% Комиссия", то используйте следующий вариант: Отдел продаж,]что по диапазону соответствует одной ячейке, например, D1. Для доступа к сумме столбца «Регион» в строке итогов используется следующая ссылка: Отдел продаж,]Это эквивалентно ячейке B8, которая вернет значение null, если строка с итоговыми значениями не включена.
Также можно комбинировать несколько спецификаторов. Справочник по стилю. Отдел продаж:] Это включает все ячейки между столбцами «Сумма продаж» и «Процент комиссии», включая заголовки. В стандартном диапазоне A1 это будет выглядеть примерно так: C1:D8. Если вам нужны только данные (без заголовков и итогов) из этих столбцов, вы будете использовать Отдел продаж:]Что-то похожее на D2:E7.
Ещё один интересный случай — это когда заголовки и данные объединяются в одной ссылке, например: Отдел продажЭта команда выделяет заголовок и все данные в столбце "% Комиссия", примерно соответствующем ячейкам D1:D7. Чтобы выбрать конкретную ячейку в текущей строке вычисляемого столбца, можно использовать [Отдел продаж]Это будет, например, E5, если текущая строка — пятая.
Очень часто при написании спецификатора #ЭтотРяд В таблице с несколькими строками Excel автоматически преобразует значение в сокращенную форму. @Оба выражения работают одинаково в рамках вычисляемого столбца, поэтому не беспокойтесь, если Excel перепишет вашу формулу; это ожидаемое поведение.
Стратегии и лучшие практики работы со структурированными ссылками
Чтобы извлечь максимальную пользу из структурированных ссылок, целесообразно использовать несколько источников. Функциональные возможности Excel При преобразовании диапазонов, связывании книг или изменении структуры таблицы следует учитывать определенные особенности поведения.
Первым союзником является функция Автозаполнение формулПри вводе текста в таблицу Excel будет предлагать названия столбцов и уточнения, сводя к минимуму синтаксические ошибки. Это особенно полезно для длинных заголовков или заголовков, содержащих специальные символы, где ошибка в скобках может испортить формулу.
По умолчанию, при выборе блока ячеек в таблице во время написания формулы, Excel генерирует... автоматический структурированный справочник вместо классического диапазона. Если вы предпочитаете вернуться к старому поведению, перейдите в меню Файл > Параметры > Формулы > Работа с формулами и установите или снимите флажок «Использовать имена таблиц в формулах».
При работе с несколькими связанными рабочими книгами помните, что если файл содержит внешние ссылки на таблицу Excel в другой рабочей книге, то исходная рабочая книга должна быть связана с файлом. открыть Чтобы избежать ошибок #REF! в целевой рабочей книге. Если вы сначала откроете целевую рабочую книгу и увидите много ошибок #REF!, обычно их можно устранить, открыв исходный файл с таблицей.
При преобразовании таблицы в обычный диапазон все ссылки, связанные с этой таблицей, преобразуются в ее диапазон. абсолютные эквиваленты стиля А1Однако, если вы преобразуете диапазон в таблицу, Excel не будет автоматически изменять существующие ссылки на этот диапазон, чтобы преобразовать их в структурированные ссылки; вам придется корректировать их самостоятельно, если вы хотите изменить стиль.
Ещё одна деталь: на вкладке «Дизайн таблицы» можно включить или отключить строка заголовкаЕсли вы его скроете, структурированные ссылки, использующие имена столбцов, по-прежнему будут работать, но ссылки, указывающие непосредственно на заголовки (например, что-то вроде SalesDept,]), вернут ошибку #REF.
Одно из главных преимуществ таблиц заключается в том, что, когда добавлять или удалять строки и столбцыСтруктурированные ссылки автоматически адаптируются. Если вы используете имя таблицы в функции для подсчета строк или суммирования данных, а затем добавляете новые записи, ссылка обновится, чтобы включить их.
Кроме того, если вы измените название таблицы или столбцаExcel вносит это изменение во все формулы, использующие данный идентификатор в рабочей книге. Это способствует использованию понятных и описательных имен, поскольку вы знаете, что можете переименовывать их, не опасаясь нарушения работы формул.
Наконец, при копировании или перемещении формул, содержащих структурированные ссылки, Спецификаторы обычно поддерживаются Совершенно верно. При заливке вверх или вниз Excel по умолчанию не изменяет названия столбцов; если вы делаете это, удерживая клавишу Ctrl, он может рассматривать их как последовательность. При заливке влево или вправо столбцы сдвигаются, как если бы они были последовательностью, а если вы заливаете, удерживая клавишу Shift, вместо перезаписи вставляются новые столбцы, а текущие значения сдвигаются.
Овладение всеми этими элементами — продуманными названиями таблиц, правильным синтаксисом, использованием спецификаторов, операторами ссылок и передовыми методами редактирования — делает структурированные ссылки очень мощным ресурсом для вас. Модели Excel должны быть более понятными, гибкими и устойчивыми к изменениям.Это особенно ценно, когда файлы разрастаются и используются несколькими пользователями.