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

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

=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». (В Excel 2007 нажмите кнопку «Office», а затем выберите «Параметры Excel. В Excel 2010 и Excel 2013 откройте вкладку« Файл »на ленте, а затем нажмите« Параметры ».)

  2. Щелкните ссылку «Формулы» в левой части диалогового окна. (См. Рис. 2.)

  3. Убедитесь, что установлен флажок Использовать имена таблиц в формулах.

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

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

=Sales '2013' – Sales '2008'

На самом деле вы инструктируете Excel работать с объединениями ячеек. В этом случае Sales «2013» возвращает ячейку на пересечении диапазона продаж и столбца «2013». Аналогичное объединение возвращается для части формулы справа от знака минус. Результат — вычитание двух желаемых значений.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9177) применим к Microsoft Excel 2007, 2010 и 2013. Вы можете найти версию этого совета для более старого интерфейса меню Excel здесь:

link: / excel-Relative_References_within_Named_Ranges [Относительные ссылки в именованных диапазонах].