Быстрые ссылки
Excel известен как программа, позволяющая создавать сложные таблицы данных. Однако некоторые люди менее знакомы с особым типом ссылок, который идет в комплекте с этими таблицами: структурированными ссылками.
Структурированные ссылки можно использовать в Excel 2016 и выше, включая Excel 365.
Что такое структурированная ссылка?
Прямые ссылки на ячейки в Excel связывают одну ячейку с другой, используя заголовки столбцов и строк. Например, ячейка A1 находится в первом столбце и первой строке, в то время как ячейка C10 находится в третьем столбце и десятой строке.
С другой стороны, в отличие от использования заголовков столбцов и строк, структурированные ссылки используют имена таблиц и столбцов. В приведенном ниже примере,
брали значения из каждой из ячеек в столбце с названием "Ежедневная прибыль" и умножали их на семь.
Использование структурированных ссылок в таблице
Чтобы использовать структурированные ссылки в таблице, как в приведенном примере, вам сначала нужно добавить данные в вашу рабочую область Excel, с именами столбцов, включенными в верхней части этих данных. Если вы не добавите имена столбцов, Excel по умолчанию присвоит имена "Столбец 1", "Столбец 2" и так далее, что значительно менее ясно, когда вы хотите использовать заголовки в формулах позже.
Перед тем как использовать какие-либо данные для выполнения расчетов, выберите ячейку внутри ваших данных и нажмите "Форматировать как таблицу" на вкладке "Главная" на ленте. Там выберите дизайн, который подходит именно вам.
Теперь используйте диалоговое окно "Создание таблицы", чтобы убедиться, что все ваши данные выбраны, отметьте флажок "Моя таблица имеет заголовки" и нажмите "ОК".
Теперь, когда ваши данные находятся в отформатированной таблице Excel, вы готовы использовать структурированные ссылки.
В моем примере я выберу ячейку C2 (первая ячейка, где я хочу выполнить расчет) и введу:
Теперь я нажму на первую ячейку с данными в моем столбце "Ежедневная прибыль", и Excel вставит структурированную ссылку на этот столбец.
Квадратные скобки — это способ Excel указать, что он использует структурированную ссылку, а символ @ (также известный как оператор пересечения) означает, что расчет будет применяться соответственно к каждой строке в таблице.
Наконец, мне нужно умножить значения в столбце "Ежедневная прибыль" на семь, чтобы получить недельную прибыль, и закрыть круглые скобки.
Когда я нажму Enter, формула автоматически скопируется в оставшиеся ячейки в колонке "Недельная прибыль".
Если бы я вручную убрал символ @, расчет в колонке "Недельная прибыль" сложил бы всю колонку "Ежедневная прибыль" и умножил бы эту общую сумму на семь.
Использование структурированных ссылок вне таблицы
Структурированные ссылки также используются в формулах вне таблицы Excel, ссылаясь на данные таблицы. В этом примере я использую формулу XLOOKUP для расчета недельной прибыли в ячейке E4 на основе сайта, указанного в ячейке E2.
Первый шаг — дать имя таблице. В противном случае Excel назовет мои данные Table1, что может быть путаным, если в моей книге несколько таблиц. На самом деле, полезно приучить себя называть ваши таблицы каждый раз, когда вы их создаете в Excel, даже если вы не планируете ссылаться на них где-либо еще в своей таблице.
Для этого я выберу любую ячейку в таблице, открою вкладку "Дизайн таблицы" на ленте и введу соответствующее имя в поле Имя таблицы. В моем случае я назову свою таблицу "Прибыль".
Существуют определенные правила, которые нужно соблюдать при именовании вашей таблицы:
Теперь в ячейке E4 я могу начать свою формулу XLOOKUP, введя:
Затем мне нужно выбрать массив поиска, который составляет ячейки с A2 по A9. Обратите внимание, как моя формула автоматически преобразует это в структурированную ссылку, с "Прибыль" как именем таблицы, которую я создал ранее, и "Сайт" как заголовком столбца.
Наконец, я могу выбрать возвращаемый массив, который составляет ячейки с C2 по C9, и закрыть круглые скобки. Снова Excel превращает это в структурированную ссылку для меня.
Когда я нажму Enter, недельная прибыль будет успешно извлечена из моей таблицы.
Почему используются структурированные ссылки?
Вы можете подумать: "Какова цель?" Что ж, есть множество преимуществ использования структурированных ссылок вместо прямых ссылок при работе с таблицами в Excel.
Во-первых, структурированные ссылки легче создавать и читать по сравнению с прямыми ссылками. В результате я могу понять, что мои формулы предназначены для выполнения, просто взглянув на них, и легко распознать свою формулу, если Excel возвращает ошибку.
Во-вторых, если я добавлю дополнительную строку в свою таблицу, щелкнув и перетащив ручку в правом нижнем углу (помеченную "A" на скриншоте ниже), а затем дополню ее новыми данными, структурированные ссылки, которые я уже использовал в своей таблице, автоматически применятся к этим новым данным.
В этом примере я добавил данные для дополнительного сайта (строка 10). Недельная прибыль не только была рассчитана автоматически, когда я добавил ежедневную прибыль, но я также изменил ссылку на сайт в ячейке E2 на сайт I, и данные были извлечены корректно. Если бы я использовал прямые ссылки, мне бы пришлось сделать несколько ручных корректировок, чтобы достичь того же результата.
В-третьих, если я добавлю еще один столбец между моими столбцами "Ежедневная прибыль" и "Недельная прибыль", я могу быть спокоен, зная, что все ссылки на мои существующие столбцы останутся безопасными, так как я сослался на имена столбцов, а не на букву, связанную с каждым столбцом.
Наконец, структурированные ссылки динамичны. Если я изменю имя одного из столбцов в своей таблице, все связанные структурированные ссылки обновятся соответственно. В этом примере я изменил свой столбец "Недельная прибыль" на "ИТОГИ", и формула XLOOKUP в ячейке E4 приняла это изменение.
Короче говоря, как только вы создаете таблицу в Excel, вам стоит задуматься о том, чтобы использовать структурированные ссылки для максимального использования ваших данных.
Помимо вышеперечисленных преимуществ, структурированные ссылки также требуют меньше памяти вашего компьютера, чем прямые ссылки, что является одним из многих способов ускорить ваши таблицы Excel.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Вы также можете читать меня в:
- Telegram: https://t.me/gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru