Относительные ссылки в именованных диапазонах (Microsoft Excel)
Крис создал рабочий лист, в котором он использует именованные диапазоны (строки) в своих формулах. Он назвал всю строку продаж как «Продажи», а затем использует имя Продажи в различных формулах. Например, в любом заданном столбце он может сказать = Продажи, и значение строки Продажи для этого столбца будет возвращено формулой. Крису было интересно, как использовать одну и ту же технику формул для ссылки на ячейки в разных столбцах.
Это можно сделать несколькими способами. Прежде всего, вы можете использовать функцию ИНДЕКС для ссылки на ячейки. Строгий способ указать значение продаж в том же столбце выглядит следующим образом:
=INDEX(Sales,1,COLUMN())
Это работает, если именованный диапазон продаж действительно относится ко всей строке на листе. Если это не так (например, «Продажи» могут относиться к ячейкам C10: K10), то следующая формула относится к значению «Продажи» в том же столбце, в котором встречается формула:
=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1)
Если вы хотите сослаться на другой столбец, просто настройте значение, добавляемое к обозначению столбца в функции ИНДЕКС. Например, если вы хотите определить разницу между продажами в текущем столбце и продажами в предыдущем столбце, вы должны использовать следующее:
=INDEX(Sales,1,COLUMN()-COLUMN(Sales)+1) - INDEX(Sales,1,COLUMN()-COLUMN(Sales))
«Сокращенная» версия этой формулы будет выглядеть следующим образом:
=Sales - INDEX(Sales,1,COLUMN()-COLUMN(Sales))
Есть и другие функции, которые вы можете использовать помимо ИНДЕКСА (например, СМЕЩЕНИЕ), но техника все та же — вы должны найти способ сослаться на смещение от текущего столбца.
Однако есть более простой способ получить желаемые данные. Предположим, что у вашего диапазона продаж также есть строка заголовка, подобная тому, что показано на следующем рисунке: (См. Рисунок 1.)
Рисунок 1. Пример данных о продажах.
В строке заголовка указаны годы для диапазона, а значения под заголовками — это те, которые фактически составляют диапазон продаж. Чтобы убедиться, что этот метод работает, выполните следующие действия:
-
Выберите «Параметры» в меню «Инструменты». Excel отображает диалоговое окно «Параметры».
-
Убедитесь, что отображается вкладка «Расчет». (См. Рис. 2.)
-
Убедитесь, что установлен флажок «Принимать метки в формулах».
-
Щелкните ОК.
После этого изменения конфигурации вы можете использовать в качестве своих формул:
=Sales '2009' – Sales '2004'
На самом деле вы инструктируете Excel работать с объединениями ячеек. В этом случае Sales «2009» возвращает ячейку на пересечении диапазона продаж и столбца «2009». Аналогичное объединение возвращается для части формулы справа от знака минус. Результат — вычитание двух желаемых значений.
ExcelTips — ваш источник экономичного обучения Microsoft Excel.
Этот совет (3202) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздние версии) здесь:
link: / excelribbon-Relative_References_within_Named_Ranges [Относительные ссылки в именованных диапазонах]
.