Зачем нужен стиль ссылок R1C1

"У меня в Excel, в заголовках столбцов листа появились цифры (1,2,3…) вместо обычных букв (A,B,C…)! Все формулы превратились в непонятную кашу с буквами R и С! Что делать??? Помогите!"

Этот вопрос я слышу почти на каждом тренинге, да и на нашем форуме он всплывает с завидной периодичностью. Давайте, наконец, разберемся - что же это за хитрый режим ссылок, как с ним бороться и для чего он, собственно говоря, вообще предназначен.

Что это

Классическая и всем известная система адресации к ячейкам листа в Excel представляет собой сочетание буквы столбца и номера строки - морской бой или шахматы используют ту же идею для обозначения клеток доски. Третья сверху во втором столбце ячейка, например, будет иметь адрес B3. Иногда такой стиль ссылок еще называют "стилем А1". В формулах адреса могут использоваться с разным типом ссылок: относительными (просто B3), абсолютными ($B$3) и смешанного закрепления ($B3 или B$3). Если с долларами в формулах не очень понятно, то очень советую почитать тут про разные типы ссылок, прежде чем продолжать.

Однако же, существует еще и альтернативная малоизвестная система адресации, называемая "стилем R1C1". В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3C2 (R=row=строка, C=column=столбец). Относительные, абсолютные и смешанные ссылки в такой системе можно реализовать при помощи конструкций типа:

  • RC - относительная ссылка на текущую ячейку
  • R2C2 - то же самое, что $B$2 (абсолютная ссылка)
  • RC5 - ссылка на ячейку из пятого столбца в текущей строке
  • RC[-1] - ссылка на ячейку из предыдущего столбца в текущей строке
  • RC[2] - ссылка на ячейку, отстоящую на два столбца правее в той же строке
  • R[2]C[-3] - ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
  • R5C[-2] - ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
  • и т.д.

Ничего суперсложного, просто слегка необычно.

Как это включить/отключить

Мало кто использует этот режим осознанно. Обычно он случайно включается сам, например, при открытии кривых выгрузок из 1С в Excel и в некоторых других ситуациях. Отключить его совсем несложно. Самый простой путь:

В Excel 2007/2010: кнопка Офис (Файл) - Параметры Excel - Формулы - Стиль ссылок R1C1 (File - Excel Options - Formulas - R1C1-style)

r1c1_1.gif

В Excel 2003 и старше: Сервис - Параметры - Общие - Стиль ссылок R1C1 (Tools - Options - General - R1C1-style)

r1c1_2.gif

Если вам приходится делать это часто, то имеет смысл создать простой макрос, переключающий эти два режима туда-обратно:

Sub ChangeRefStyle()
    If Application.ReferenceStyle = xlA1 Then
        Application.ReferenceStyle = xlR1C1
    Else
        Application.ReferenceStyle = xlA1
    End If
End Sub

Можно сохранить его в личную книгу макросов и повесить на кнопку на панели инструментов или на сочетание клавиш (как это сделать описано тут).

Где это может быть полезно

А вот это правильный вопрос. Если звезды зажигают, то это кому-нибудь нужно. Есть несколько ситуаций, когда режим ссылок R1C1 удобнее, чем классический режим А1:

  • При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+~) в двух вариантах адресации:

r1c1_3.gif         r1c1_4.gif  

Найти ошибку в режиме R1C1 намного проще, правда?

  • Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
  • Некоторые функции Excel, например ДВССЫЛ (INDIRECT) могут работать в двух режимах - A1 или R1C1. И иногда оказывается удобнее использовать второй.
  • В коде макросов на VBA часто гораздо проще использовать стиль R1C1 для ввода формул в ячейки, чем классический A1. Так, например, если нам надо сложить два столбца чисел по десять ячеек в каждом (A1:A10 и B1:B10,) то мы могли бы использовать в макросе простой код:
    Range("C1:C10").FormulaR1C1="=RC[-2]*RC[-1]"

т.к. в режиме R1C1 все формулы будут одинаковые. В классическом же представлении в ячейках столбца С все формулы разные, и нам пришлось бы писать код циклического прохода по каждой ячейке, чтобы определить для нее формулу персонально, т.е. что-то типа:

For Each cell In Range("C1:C10")
    cell.Formula = "=" & cell.Offset(0, -2).Address & "*" & cell.Offset(0, -1).Address
Next cell

Ссылки по теме

  



Николай
11.11.2012 01:42:00
Спасибо за разъяснение. Всегда больше всего интересовал вопрос "почему". На мелких таблицах RC режим бесил, на крупных стал более понятен, но все на уровне интуиции.
Алексей
11.11.2012 01:43:09
Можно ли используя стиль ячеек "R1C1" сделать так, чтобы ссылка велась не на конкретное количество строк/столбцов назад/вперед, а на изменяемое число строк/столбцов назад/вперед.
Условно:
Не RC[1], а RC[-i] где i = значение из другой ячейки.
Какой синтаксис необходимо применять для написания подобной "изменяемой ссылки"?.
11.11.2012 01:45:33
Для такого можно использовать функцию СМЕЩ (OFFSET) - она выдает ссылку на диапазон, сдвинутый относительно исходного на определенное число строк и столбцов.
Например =СМЕЩ(A1;3;2) даст ссылку на B3
19.12.2013 15:21:26
Обычно, если для расчетов используется значение какой-то ячейки, то  при составлении расчетной конструкции (=.....) просто в нужном месте конструкции щёлкают по нужной ячейке, и ссылка на нее сама вставляенся в конструкцию.
Ещё можно присвоить нужной ячейке имя (выделить её, далее Меню-Вставка-Имя-Присвоить), например, Мой_рост, а потом использовать его в конструкции (=......+Мой_рост/.....). К моменту обращения к этой ячейке (т.е. до начала расчета) там должно находиться нужное/конкретное значение. Именованную ячейку можно "перетаскивать" в любое другое место, даже на другой лист, и там она будет с именем "Мой_рост".
Кстати, вышесказанное работает в любом стиле - "R1C1" и "A1".
Ринат
11.11.2012 01:43:40
огромное спасибо за решение дремучего вопроса!
03.04.2013 11:23:42
Огромное спасибо!!!!!! Сегодня открыла свои файлы и оказалось, что все формулы превратились в эту самую "кашу". Вспомнила, что на planetaexcel.ru читала про этот режим excel, без проблем нашла эту статью и все исправила. Не в первый раз вы меня так выручаете!!! Еще раз огромное спасибо!!!!
11.04.2013 09:09:28
Не за что! Будем стараться и дальше :)
17.05.2013 09:30:51
Спасибо. Разъяснили!:)
19.12.2013 14:33:36
Почему "каша"? Лично я предпочитаю работать и программировать в Excel (кстати, 2003) именно в стиле "R1C1". Считаю, очень удобно, наглядно,  логично и "математично". "AE:8" или (8,31), "BB:12" или (12,54) - что понятнее и естественнее?  "R1C1" - это же Декартова система координат (Y,X)  в школьном учебнике! ;)
21.12.2013 09:48:26
Old school :)
Для именно программирования он, кстати, однозначно удобнее, абсолютно согласен.
21.12.2013 10:08:17
Да, "Old school", возможно, теперь этому в школе не учат по системе ЕГЭ.
17.10.2014 07:40:32
спасибо! теперь все понятно!
18.11.2014 03:33:46
Недавно открыл для себя ещё одну мега удобную вещь,которую можно сделать только с этим стилем.  На сайте вроде не встречал, так что поделюсь с читателями, может кому пригодится.

Если в формуле хочется использовать именованные диапазоны (RangeNames), то есть вместо формулы B3*B4 иметь что-то вроде Объем * Цена, то обычно выделяют всю строку(столбец)  с исходными данными, то есть получается массив (или вектор - назовите как хотите), например "Выручка" - В3:X3, "Цена" - B4:X4. Тогда, конечно, можно вводить эти имена в формулу, и формула примет более благообразный и понятный вид, но есть несколько но
- при использовании некоторых формул, например МАКС, Excel будет использовать весь массив в качестве аргумента, то есть если у вас две цены Цена1; Цена 2, то с формулой Макс (Цена1, Цена2)  посчитается не максимальная цена из двух цен в текущем столбце, а максимальная цена за весь период.  
- Если зайти в режим редактирования - то будет подсвечиваться весь диапазон - тоже не всегда удобно и понятно, откуда берутся значение для формулы

Но можно задать не статичный диапазон, а смещающийся. Надо зайти в режим R1C1 и при создании диапазона не фиксировать столбец, то есть вместо R3C2:R3C24 вписать R3C. Тогда  в любой ячейке Excel при использовании этого диапазона будет использоваться значение из третьего ряда и текущего столбца, причём даже в режиме ссылок A1. И, что самое интересное, в режиме А1 адрес диапазона будет меняться, в зависимости от того, в какой ячейке вы находитесь. По мне, так это какая-то магия, если честно. Таки образом решаются вышеупомянутые проблемы.

Надеюсь, понятно объяснил. Пока писал, понял, что можно подобную вещь и без этого стиля ссылок сделать, но всё равно не так красиво получится.
03.09.2015 08:25:16
Подскажите пожалуйста каким кодом можно через VBA ввести в диапазон формулу массива через R1C1
14.03.2016 16:32:08
Приветствую!
Есть сочетание клавиш для переключения между стилями отображения адресов?  
23.09.2016 19:23:12
Подскажите пожалуйста как мне вывести значение ячейки в столбце с тем что ячейка наприм. 10 по счету сверху вниз но сам список может изменяться фильтрами  (т.е. считать надо только видимые ячейки).  СМЕЩ фиксировано считает и на фильтры не реагирует.  
Большое спасибо!:)
09.05.2018 08:18:57
Добрый день, подскажите как можно написать что значение ячейки равно значению ячейки другого листа, причем колонка зафиксирована, а строка определяется значением конкретной ячейки текущего листа.
например на листе2 в в ячейку C2 вводим цифровое значение, например 5
на листе 3 еcть колонка А, в которой рассчитываются некоторые значения по формуле. Дак вот нам надо взять значение из ячейки с листа 3 колонка А  строка 5 и записать ее значение в ячейку на листе 2. Спасибо за ответ.
05.12.2018 12:30:50
Добрый день!

Спасибо за разъяснения. Несколько раз сталкивался с этим вопросом, но либо не было времени его решить, либо обходился "и так сойдет"

Сейчас решил уделить немного времени... Все стало ясно гораздо быстрее, чем рассчитывал. Спасибо!
Наверх