Структурированные ссылки в Excel

Как создать структурированные ссылки в Excel?

Структурированные ссылки начинаются с таблиц Excel. Как только таблицы создаются в Excel, он автоматически создает для вас структурированные ссылки.

Теперь взгляните на изображение ниже.

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

  • Шаг 1: Я дал ссылку на ячейку В3. Вместо того, чтобы отображать ссылку как B2, она отображается как Таблица 1[@Sales]. Вот Таблица 1 это имя таблицы, а @Продажи это столбец, о котором мы говорим. Все ячейки в этом столбце обозначаются именем таблицы, за которым следует имя заголовка столбца.
  • Шаг 2: Теперь я изменю имя таблицы на Таблица данных и измените заголовок столбца на Количество.
  • Шаг 3: Чтобы изменить имя таблицы, поместите курсор внутри таблицы> перейдите в Дизайн> Имя таблицы.

Изменить имя таблицы 1

  • Шаг 4: Упомяните название таблицы как Таблица данных.

Изменить имя таблицы 2

  • Шаг 5: Теперь изменим ссылку на ячейку B3.

Структурированная ссылка 1

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

Примеры

Вы можете скачать этот шаблон Excel для структурированных ссылок здесь — Шаблон для структурированных ссылок в Excel

Пример # 1

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

Позвольте мне применить формулу СУММ как для нормального диапазона, так и для таблицы Excel.

Формула СУММ для нормального диапазона.

Динамическая структурированная ссылка 1.1

Формула СУММЫ для таблицы Excel.

Динамическая структурированная ссылка 1.2

Позвольте мне добавить несколько строк к данным как обычных таблиц, так и таблиц Excel. Я добавил к данным 2 позиции, теперь вижу разницу.

Справочник по динамической структуре 1.3

Динамическая структурированная ссылка 1.4

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

Пример # 2

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

Структурированный справочный пример 2.1

Чтобы получить стоимость продаж, формула: Кол-во * Цена. Применим эту формулу к таблице.

Структурированный справочный пример 2.2

Формула говорит [@QTY] * [@PRICE]. Это более понятно, чем обычная ссылка на В2 * С2. Мы не получаем имя таблицы, если помещаем формулу в таблицу.

Проблемы с структурированными ссылками Excel

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

Проблема # 1

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

Теперь посмотрим на пример ниже. Я применил формулу СУММ в Excel для нормального диапазона.

Структурированная справочная задача 1.1

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

Структурированная справочная задача 1.2

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

Структурированная справочная задача 1.3

Теперь у нас есть сумма столбца Qty. Как и в случае с обычным диапазоном, формула скопирует текущую формулу и вставит ее в столбец Price, чтобы получить сумму Price.

Структурированная справочная задача 1.4

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

Перетащите формулу, чтобы изменить ссылку

Теперь мы знаем его недостатки. Мы больше не можем выполнять копирование и вставку со структурированными ссылками. Тогда как нам преодолеть это ограничение?

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

Структурированная справочная задача 1.5

Теперь мы обновили формулы, чтобы получить соответствующие итоги.

Проблема # 2

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

Давайте теперь посмотрим на приведенный ниже пример. У меня есть таблица продаж с несколькими записями, и я хочу объединить данные с помощью функции СУММЕСЛИ в excel.

Проблема 2.1

Теперь я применим функцию СУММЕСЛИ, чтобы получить консолидированные значения продаж для каждого продукта.

Проблема 2.2

Я применил формулу для января месяца. Поскольку это структурированная ссылка, мы не можем скопировать и вставить формулу в оставшиеся два столбца. Ссылка на февраль и март не изменится, поэтому я перетащу формулу.

Проблема 2.6

Ой!! Я не получил никаких значений в столбце «февраль и март». В чем будет проблема ??? Внимательно посмотрите на формулу.

Мы перетащили формулу с января месяца. В функции СУММЕСЛИ первым аргументом является диапазон критериев. Sales_Table [Product] так как мы перетащили формулу. Он изменился на Продажи_Стол [Jan].

Так как же нам с этим бороться ?? Нам нужно сделать первый аргумент, то есть столбец Product как абсолютный, а другие столбцы как относительную ссылку. В отличие от обычной ссылки, у нас нет возможности использовать клавишу F4 для изменения типа ссылки.

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

Проблема 2.3

Проблема 2.4

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

Проблема 2.5

Совет профессионала: Чтобы сделать строку ROW абсолютной ссылкой, нам нужно сделать запись двойной ROW, но нам нужно поставить символ @ перед именем ROW.

= Sales_Table[@[Product]:[Product]]

Как отключить структурированную справку в Excel?

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

  • Шаг 1: Перейдите в ФАЙЛ> Параметры.
  • Шаг 2: Формулы> Снимите флажок Используйте имена таблиц в формулах.

Отключить ссылку

То, что нужно запомнить

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

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.