Крис создал рабочий лист, в котором он использует именованные диапазоны (строки) в своих формулах. Он назвал всю строку продаж как «Продажи», а затем использует имя Продажи в различных формулах. Например, в любом заданном столбце он может сказать = Продажи, и значение строки Продажи для этого столбца будет возвращено формулой. Крису было интересно, как использовать одну и ту же технику формул для ссылки на ячейки в разных столбцах.

Это можно сделать несколькими способами. Прежде всего, вы можете использовать функцию ИНДЕКС для ссылки на ячейки. Строгий способ указать значение продаж в том же столбце выглядит следующим образом:

=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.)

image

Рисунок 1. Пример данных о продажах.

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

  1. Выберите «Параметры» в меню «Инструменты». Excel отображает диалоговое окно «Параметры».

  2. Убедитесь, что отображается вкладка «Расчет». (См. Рис. 2.)

  3. Убедитесь, что установлен флажок «Принимать метки в формулах».

  4. Щелкните ОК.

После этого изменения конфигурации вы можете использовать в качестве своих формул:

=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 [Относительные ссылки в именованных диапазонах].