Как использовать стиль ссылок r1c1 в excel?

В Excel существует два типа стилей ссылок на ячейки: первый — A1, второй — R1C1. Что ж, большинство пользователей Excel даже не знают о существовании стиля ссылок R1C1.

Но некоторым пользователям нравится его использовать, и они считают его более удобным, чем A1. Стиль R1C1 довольно старый.

Впервые он был представлен в Multiplan , разработанном Microsoft для Apple Macintosh.

Но через несколько лет Microsoft начала использовать стиль A1, поэтому людям, перешедшим с Lotus, он показался знакомым, но R1C1 всегда присутствовал в Excel.

В R1C1 меньше дополнительных преимуществ, чем в A1. И сегодня в этой статье я хотел бы поделиться с вами всеми аспектами использования стиля ссылок R1C1.

Итак, без лишних слов, давайте изучим эту вещь.

Разница между стилем ссылки A1 и R1C

В ссылочном стиле A1 у вас есть имя столбца в виде алфавита и имя строки в виде номера, и когда вы выбираете ячейку A1, это означает, что вы находитесь в столбце A и строке 1.

Но в R1C1 столбец и строка указаны числами.

Поэтому, когда вы выбираете ячейку A1, она показывает R1C1, что означает строку 1 и столбец 1, а если вы перейдете к A2, это будет R2C1.

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

В R1C1, когда вы ссылаетесь на ячейку, он создает адрес указанной ячейки , используя ее расстояние от активной ячейки .

Например, если вы ссылаетесь на ячейку B5 из ячейки A1, адрес B5 будет отображаться как R[4]C[1].

Теперь просто подумайте вот так. Ячейка B5 находится на 4 строки ниже и на 1 столбец перед ячейкой A1, поэтому ее адрес — R[4]C[1].

Но вот что интересно. Если вы обратитесь к той же ячейке из другой ячейки, ее адрес будет другим.

Следует понимать, что в стиле ссылок R1C1 не существует постоянного адреса ячейки (если вы используете относительную ссылку), поэтому адрес ячейки зависит от того, где вы на нее ссылаетесь.

Использование ссылки R1C1 — это реалистичный подход к работе со ссылками на ячейки.

Перед этой статьей я подробно опишу этот подход. Но сначала давайте научимся его активировать.

Как включить ссылку на ячейку R1C1 в Excel – простые шаги

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

Из параметров Excel

Выполните следующие простые шаги, чтобы установить ссылку R1C1 по умолчанию.

1. Перейдите на вкладку «Файл» ➜ «Параметры» ➜ «Формулы» ➜ «Работа с формулами».
2. Проверьте «Эталонный стиль R1C1».
3. Нажмите ОК.

Используйте код VBA

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

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

Как только вы сделаете R1C1 стилем ссылок по умолчанию, все ссылки в формулах во всех книгах изменятся.

Как работает стиль ссылок R1C1

Теперь вы поняли одну вещь в R1C1: у вас есть номер строки и номер столбца.

Но чтобы понять, как это работает, вам нужно изучить все типы ссылок.

Как и в случае с A1, в R1C1 можно использовать четыре разных типа ссылок, как показано в таблице выше. Но теперь давайте углубимся в каждый ссылочный тип.

1. Относительная ссылка R1C1

Использовать относительную ссылку в R1C1 довольно просто. В этом стиле ссылки, когда вы ссылаетесь на ячейку, создается адрес указанной ячейки, используя ее расстояние от активной ячейки.

Если вы хотите перейти к столбцу в правой части активной ячейки, число будет положительным, а если в левой части, то отрицательным. То же самое и с линией: положительное число для нижней части и отрицательное для активной линии вверху.

В приведенном выше примере мы использовали относительную ссылку для умножения ячейки, которая находится на 1 столбец впереди ячейки, на ячейку, которая находится на 2 столбца впереди.

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

2. Абсолютное задание R1C1.

Я уверен, что вы заметили эту вещь в приведенном выше примере : номера строк и столбцов заключены в скобки . Позвольте мне показать вам, что происходит, когда вы не используете скобки в ссылке на ячейку.

В приведенном выше примере, когда вы используете строку 2 и столбец 1 без скобок, это означает, что ячейка, на которую вы ссылаетесь, расположена точно в строке 2 и столбце 1 .

Вот что на самом деле: в R1C1, когда вы хотите использовать абсолютную ссылку, вы можете игнорировать использование скобок, и Excel будет рассматривать ячейку R1C1 (ячейку A1) как отправную точку.

Позвольте мне показать вам пример.

В приведенном выше примере мы умножили ячейку R2C3, которая находится во 2-й строке и 3-м столбце R1C1 (ячейка A1), на ячейку, расположенную на 1 столбец перед активной ячейкой.

R2C3 без скобок и когда мы перетаскиваем формулу, ссылка на ячейку вместе с ней не меняется .

3. R1C1 полуабсолютный/относительный

Это один из наиболее часто используемых стилей ссылок, в котором вы можете создать отдельный столбец или строку, абсолютную или относительную. Позвольте мне показать вам пример.

В приведенном выше примере мы умножаем значение в первой строке на значение в первом столбце.

• RC1: если вы игнорируете указание номера строки, Excel рассматривает строку в активной ячейке как ссылку. Итак, здесь столбец останется прежним, но строка изменится, как только вы перетащите формулу во все ячейки.
• A1C: Если вы игнорируете указание номера столбца, Excel рассматривает столбец активной ячейки как ссылку. Итак, здесь строка останется прежней, но столбец изменится, как только вы перетащите формулу во все ячейки.

Использование ссылки R1C1 в формулах

Как только вы переключитесь со стиля A1 на стиль R1C1, вы увидите, что все формулы в вашей электронной таблице изменились.

Возможно, вы впервые находите их запутанными, но на самом деле они довольно просты. Позвольте мне показать вам, как это работает, с помощью простой формулы суммы.

В приведенном ниже примере у нас есть таблица данных с ежемесячными продажами 10 продуктов со столбцом итогов в конце.

И здесь вы используете стиль ссылки A1.

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

Но как только вы измените ссылку с А1 на R1C1, все формулы изменились и в каждой итоговой ячейке они одинаковы.

Во всех десяти ячейках у нас есть формула ниже.

 =SUM(RC[-12]:RC[-1])

Эта формула гласит: «Суммируйте диапазон значений от ячейки 12 столбцов влево (C[–12]) в той же строке до ячейки, которая находится на один столбец левее (C[–1]) в тот же ряд. линия . «

Потому что во всех ячейках, где мы вычисляем сумму, диапазон значений находится в одном порядке и поэтому у нас для всех ячеек одна и та же формула.

Excel 2016 «Все в одном» для чайников. Совет . Вы можете использовать нотацию R1C1, чтобы убедиться, что вы правильно скопировали все формулы в таблицу рабочего листа. Переместите курсор мыши по всем ячейкам, содержащим формулы, скопированные в таблицу. Когда действует нотация R1C1, все копии исходной формулы во всей строке или во всем столбце таблицы должны быть идентичными при отображении в строке формул при активации их ячеек.

Использование ссылки R1C1 в VBA

Когда вы записываете макрос , вы можете видеть, что Excel использует формулу R1C1 для обращения к ячейкам и диапазонам.

И, если вы знаете, как использовать концепцию R1C1, вы можете легко редактировать записанные макрокоды и сэкономить много времени. Для этого вам нужно понять, как работает метод FormulaR1C1.

Предположим, вы хотите ввести в активную ячейку формулу, в которой нужно перемножить две ячейки, расположенные слева от активной ячейки.

 Sub R1C1Style() Selection.FormulaR1C1 = "=RC[-2]*RC[-1]" End Sub

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

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

Мы не можем использовать этот код для каждой ячейки, поскольку ссылка на ячейку будет фиксированной ( в противном случае нам придется использовать свойство Offset ). Более подробную информацию о методе Formula.R1C1 вы можете получить в руководстве Хорхе .

Заключение

Поняв R1C1, вы почувствуете некоторый страх, но как только вы его преодолеете, у вас появится новый, гибкий подход к работе в Excel.

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

1. В R1C1 R представляет строку, а C — столбец .
2. Чтобы сослаться на строку ниже и столбец перед активной ячейкой, вы можете использовать положительное число.
3. Чтобы сослаться на строку выше и столбец за активной ячейкой, вы можете использовать отрицательное число.
4. Когда вы используете номер строки или столбца без скобок, Excel рассматривает его как абсолютную ссылку.
5. В VBA для использования R1C1 необходимо использовать метод FormulaR1C1.

Надеюсь, эта статья оказалась для вас полезной и теперь скажите мне одну вещь.

Собираетесь ли вы начать использовать R1C1?

Обязательно поделитесь со мной своими мыслями в комментариях, мне будет интересно услышать ваше мнение. И, пожалуйста, не забудьте поделиться этим постом со своими друзьями, я уверен, им он понравится.