Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.
Задача: ссылки в стиле R1C1 полезны в нескольких ситуациях, в частности, при использовании VBA, функции ДВССЫЛ (см., например, последний раздел заметки Ссылка на другой лист с помощью ДВССЫЛ) и условного форматирования. Поэтому краткое знакомство с ними будет полезно.
До появления платформы IBM PC существовало несколько популярных приложений электронных таблиц. Это были и VisiCalc и Quattro Pro и Multiplan. Впервые стиль А1 именования ячеек был представлен в VisiCalc. Но этот продукт быстро проиграл конкурентную гонку. Multiplan выпускался компанией Microsoft до выхода Excel. В этом продукте для адресации ячеек использовался формат R1C1, который с тех пор доступен и в Excel. Но именно Лотус 1-2-3 вырвался на вершину славы сразу же после выхода в 1982 г. и стал доминировать на рынке приложений электронных таблиц для ПК. Не в последнюю очередь благодаря удобной системе именования ячеек А1. [1]
Во время войн электронных таблиц, Microsoft осознала, что большинство пользователей голосуют за стиль ссылок А1, и чтобы конкурировать, ей придется делать вид, что в Excel используются ссылки А1. Конечно, Excel только делает вид, что использует А1. В действительности «за кулисами» работают R1C1. Если вы не верите мне, пройдите по меню ФАЙЛ –> Параметры, перейдите на вкладку Формулы, и в области Работа с формулами поставьте галочку Стиль ссылок R1C1 (рис. 1).
Рис. 1. Вы всего в одном клике от стиля ссылок R1C1
Скачать заметку в формате Word или pdf, примеры в формате Excel
Обратите внимание, что я не призываю вас перейти на стиль ссылок R1C1. Я не такой сумасшедший, как Microsoft, которая при переходе с Excel 2003 на Excel 2007 полностью заменила привычное меню. Вместо этого, я предлагаю вам изучить стиль R1C1, чтобы применять его, когда это уместно.
Решение: стиль ссылок R1C1 содержит буквы R (row – строка) и С (column – столбец). Без каких-либо чисел (т.е. в виде RC) R означает «та же строка, в которой введена формула» и C означает «тот же столбец, в который введена формула». Таким образом, простейшая ссылка в стиле R1C1 – это =RC. Если вы находитесь в ячейке C10 и введете =RC, вы имеете ввиду ячейку С10. Осторожно! Это приведет к возникновению ошибки – циклической ссылки.
Когда в дополнение к буквам R и C используются числа в квадратных скобках, вы ссылаетесь на ячейку, отстоящую от данной на указанное число ячеек. Например, формула =RC[-1] введенная в С10 ссылается на В10, а =R[10]C в С10 – на С20. Вы можете одновременно изменить, и строку, и столбец. Например, формула =R[1]С[1] введенная в С10 ссылается на D11.
Примечание: для строк положительные числа направляют вниз листа, отрицательные – вверх. Для столбцов положительные числа направляют вправо, отрицательные – влево.
Если вы хотите построить формулу для расчета рентабельности (GP) в колонке I, вы должны разделить значение в соответствующей строке столбца H на значение в столбце F (рис. 2). Видно, что формулы в столбце I разные в каждой строке.
Рис. 2. Со ссылками в стиле А1 (верхний рисунок) формулы в каждой ячейке разные, со ссылками в стиле R1C1 (нижний рисунок) формулы одинаковые.
А что если перейти к ссылкам в стиле R1C1? Вам нужно разделить значение в столбце на один левее текущего, на значение в столбце на три левее текущего. Вы можете использовать одну и туже формулу, независимо от того, в какой строке вы находитесь: =RC[-1]/RC[-3].
А что с абсолютными ссылками в стиле R1C1? Формулы в столбце J (рис. 3) гарантируют, что вы всегда используете налоговую ставку из ячейки М1. Для указания абсолютной ссылки в стиле R1C1, опустите квадратные скобки и укажите не смещение от текущей строки, а абсолютные номера строки и столбца. Например, ячейка $М$1 – это 13-й столбец строки 1, поэтому, используя R1C13.
Рис. 3. Абсолютные ссылки в стиле А1 (верхний рисунок) и R1C1 (нижний рисунок)
Резюме: ссылки R1C1 пригодятся вам при записи макросов, программировании VBA, использовании функции ДВССЫЛ и условном форматировании.
[1] Цитируется по изданию Пол Киммел и др. Excel 2003 и VBA. Справочник программиста.