Четыре способа использования структурированных ссылок в Microsoft Excel

Краткие советы

• Структурированные ссылки в Excel работают только с таблицами, отформатированными в программе соответствующим образом, а не с диапазонами данных.
• Использование структурированных ссылок делает формулы более удобочитаемыми и динамичными.
• При форматировании таблицы в Excel измените ее имя на что-нибудь значимое. В противном случае Excel назовет ее Таблица.[number]что может сбить с толку.
• Структурированные ссылки работают как внутри, так и снаружи таблиц, могут использоваться внутри других функций и автоматически обновляются, если заголовки получают новые имена.

Работа в Excel обычно вращается вокруг поиска связей между различными точками данных. Однако при вставке сложных формул повторное использование относительных и абсолютных явных ссылок на ячейки (например, «B7» или ее варианты) может помочь вам лишь до определенного момента, прежде чем строка формул превратится в нечитаемый беспорядок.

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

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

1. Расчет внутри таблиц

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

Например, создадим простую таблицу от B2 до F8 с данными о продажах магазина. Обратите внимание, что мы назвали таблицу «Продажи» (см. «Имя таблицы» в левом верхнем углу).

Подсчитаем итоговую сумму по каждой продаже:

Шаг 1: Нажмите F2 (но не значок раскрывающегося списка). Перейдите в «Домой», затем «Вставить» и выберите «Вставить столбцы таблицы справа». Это автоматически добавит новый столбец в таблицу.

Шаг 2. Назовите заголовок столбца G «Итого».

Шаг 3: В G3 вставьте =[@PricePerUnit]*[@Quantity] и нажмите Enter. Отформатируйте вывод ячейки по мере необходимости.

«[@PricePerUnit]” и “[@Quantity]» — это ссылки на соответствующие поля в этих столбцах. Аргумент «@» перед именами столбцов означает, что каждая ячейка результата будет использовать ссылки из одной и той же строки таблицы.

В переводе формула =[@PricePerUnit]*[@Quantity] в G3 по сути то же самое, что написать =$C3*$D3.

2. Получение диапазона за пределами таблицы

Если вы хотите использовать структурированную ссылку в ячейке вне таблицы, вам необходимо добавить к ссылке имя таблицы. В нашем предыдущем примере использование «Продажи»[Total]» выведет весь диапазон под заголовком «Итого» таблицы «Продажи». Это означает, что вы получите несколько значений в массиве, которым сможете манипулировать.

Вот как это выглядит в Excel в ячейке I3, при условии, что вы оставляете достаточно места для распространения диапазона.

3. Суммирование и частичное суммирование столбца

Чтобы быстро суммировать весь столбец, вы можете использовать галочку «Итого по строке» в настройках «Дизайн таблицы» (в разделе «Параметры стиля таблицы»). Ниже приведен пример получения итоговых значений по столбцам «Количество» и «Итого».

Хотя строку «Итого» нельзя переместить и она будет помещена в конец таблицы (с возможностью вставки), вы можете продублировать ее результат в другом месте:

• Чтобы получить сумму всех строк в столбце «Итого», используйте =SUM(Sales[Total]).
• Если вы хотите получить только сумму видимых столбцов, например, после фильтрации таблицы, используйте =SUBTOTAL(109,Sales[Total]). Эта формула — это то, что на самом деле делает опция «Итого по строке» в формате таблицы в своей строке.

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

• Чтобы получить сумму всех продаж Майка, вы можете использовать =СУММЕСЛИ(Продажи[Seller]«Майк», Продажи[Total]). В формуле «Майк» — это строка, введенная вручную.
• Чтобы получить сумму всех продуктов с идентификатором «41230», используйте следующую формулу =СУММЕСЛИ(Продажи[ProductID],41230,Продажи[Total]). Обратите внимание: поскольку столбец ProductID имеет формат «Общий», вы можете ввести число напрямую.

4. Проверка данных из таблицы с помощью КОСВЕННОЙ

Предположим, у вас есть ранее использованная таблица «Продажи». Вы можете создать собственные параметры проверки данных, чтобы упростить поиск по таблице. Давайте создадим таблицу меньшего размера, которая позволит вам выбирать между ProductID, Dates или Sellers, а затем выбрать любой отдельный элемент из этих подмножеств для отображения промежуточного итога.

Шаг 1. В ячейке B13 создайте проверку данных (вкладка «Данные» > «Инструменты для данных» > «Проверка данных»).

Шаг 2. Во всплывающем окне выберите «Список» в разделе «Разрешить», затем вручную вставьте значения столбцов в поле «Источник», разделив их запятыми. В данном случае мы вставили «ProductID, Seller, Date».

Шаг 3. В ячейке C13 создайте еще одну проверку данных. Снова выберите «Список». В качестве источника вставьте следующую формулу: =ДВССЫЛ(“Продажи[“&B13&”]”).

Шаг 4. В ячейке D13 используйте следующую формулу: =СУММЕСЛИ(ДВССЫЛ(“Продажи[“&B13&”]”),B14,Продажи[Total]).

Теперь вы можете выбрать параметры из двух списков проверки данных, и промежуточный итог будет отображаться в D13.

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