Как в vba excel макросом конвертировать адрес из a1 в r1c1
Этот вопрос я слышу почти на каждом тренинге, да и на нашем форуме он всплывает с завидной периодичностью. Давайте, наконец, разберемся - что же это за хитрый режим ссылок, как с ним бороться и для чего он, собственно говоря, вообще предназначен.
Что это
Классическая и всем известная система адресации к ячейкам листа в Excel представляет собой сочетание буквы столбца и номера строки - морской бой или шахматы используют ту же идею для обозначения клеток доски. Третья сверху во втором столбце ячейка, например, будет иметь адрес B3. Иногда такой стиль ссылок еще называют "стилем А1". В формулах адреса могут использоваться с разным типом ссылок: относительными (просто B3), абсолютными ($B$3) и смешанного закрепления ($B3 или B$3). Если с долларами в формулах не очень понятно, то очень советую почитать тут про разные типы ссылок, прежде чем продолжать.
Однако же, существует еще и альтернативная малоизвестная система адресации, называемая "стилем R1C1". В этой системе и строки и столбцы обозначаются цифрами. Адрес ячейки B3 в такой системе будет выглядеть как R3 C2 (R=row=строка, C=column=столбец). Относительные, абсолютные и смешанные ссылки в такой системе можно реализовать при помощи конструкций типа:
- R C - относительная ссылка на текущую ячейку
- R2 C2 - то же самое, что $B$2 (абсолютная ссылка)
- R C5 - ссылка на ячейку из пятого столбца в текущей строке
- R C[-1] - ссылка на ячейку из предыдущего столбца в текущей строке
- R C[2] - ссылка на ячейку, отстоящую на два столбца правее в той же строке
- R[2] C[-3] - ссылка на ячейку, отстоящую на две строки ниже и на три столбца левее от текущей ячейки
- R5 C[-2] - ссылка на ячейку из пятой строки, отстоящую на два столбца левее текущей ячейки
- и т.д.
Ничего суперсложного, просто слегка необычно.
Как это включить/отключить
Мало кто использует этот режим осознанно. Обычно он случайно включается сам, например, при открытии кривых выгрузок из 1С в Excel и в некоторых других ситуациях. Отключить его совсем несложно. Самый простой путь:
В Excel 2007/2010: кнопка Офис (Файл) - Параметры Excel - Формулы - Стиль ссылок R1C1 (File - Excel Options - Formulas - R1C1-style)
В Excel 2003 и старше: Сервис - Параметры - Общие - Стиль ссылок R1C1 (Tools - Options - General - R1C1-style)
Если вам приходится делать это часто, то имеет смысл создать простой макрос, переключающий эти два режима туда-обратно:
Можно сохранить его в личную книгу макросов и повесить на кнопку на панели инструментов или на сочетание клавиш (как это сделать описано тут).
Где это может быть полезно
А вот это правильный вопрос. Если звезды зажигают, то это кому-нибудь нужно. Есть несколько ситуаций, когда режим ссылок R1C1 удобнее, чем классический режим А1:
- При проверке формул и поиске ошибок в таблицах иногда гораздо удобнее использовать режим ссылок R1C1, потому что в нем однотипные формулы выглядят не просто похоже, а абсолютно одинаково. Сравните, например, одну и ту же таблицу в режиме отладки формул (CTRL+~) в двух вариантах адресации:
Найти ошибку в режиме R1C1 намного проще, правда?
- Если большая таблица с данными на вашем листе начинает занимать уже по нескольку сотен строк по ширине и высоте, то толку от адреса ячейки типа BT235 в формуле немного. Видеть номер столбца в такой ситуации может быть гораздо полезнее, чем его же буквы.
- Некоторые функции Excel, например ДВССЫЛ (INDIRECT) могут работать в двух режимах - A1 или R1C1. И иногда оказывается удобнее использовать второй.
- В коде макросов на VBA часто гораздо проще использовать стиль R1C1 для ввода формул в ячейки, чем классический A1. Так, например, если нам надо сложить два столбца чисел по десять ячеек в каждом (A1:A10 и B1:B10,) то мы могли бы использовать в макросе простой код:
т.к. в режиме R1C1 все формулы будут одинаковые. В классическом же представлении в ячейках столбца С все формулы разные, и нам пришлось бы писать код циклического прохода по каждой ячейке, чтобы определить для нее формулу персонально, т.е. что-то типа:
Ссылки по теме
Спасибо за разъяснение. Всегда больше всего интересовал вопрос "почему". На мелких таблицах RC режим бесил, на крупных стал более понятен, но все на уровне интуиции.
Можно ли используя стиль ячеек "R1C1" сделать так, чтобы ссылка велась не на конкретное количество строк/столбцов назад/вперед, а на изменяемое число строк/столбцов назад/вперед.
Условно:
Не RC[1], а RC[-i] где i = значение из другой ячейки.
Какой синтаксис необходимо применять для написания подобной "изменяемой ссылки"?.
Для такого можно использовать функцию СМЕЩ (OFFSET) - она выдает ссылку на диапазон, сдвинутый относительно исходного на определенное число строк и столбцов.
Например =СМЕЩ(A1;3;2) даст ссылку на B3
Обычно, если для расчетов используется значение какой-то ячейки, то при составлении расчетной конструкции (=. ) просто в нужном месте конструкции щёлкают по нужной ячейке, и ссылка на нее сама вставляенся в конструкцию.
Ещё можно присвоить нужной ячейке имя (выделить её, далее Меню-Вставка-Имя-Присвоить), например, Мой_рост, а потом использовать его в конструкции (=. +Мой_рост/. ). К моменту обращения к этой ячейке (т.е. до начала расчета) там должно находиться нужное/конкретное значение. Именованную ячейку можно "перетаскивать" в любое другое место, даже на другой лист, и там она будет с именем "Мой_рост".
Кстати, вышесказанное работает в любом стиле - "R1C1" и "A1".
Не за что! Будем стараться и дальше
Спасибо. Разъяснили!
Почему "каша"? Лично я предпочитаю работать и программировать в Excel (кстати, 2003) именно в стиле "R1C1". Считаю, очень удобно, наглядно, логично и "математично". "AE:8" или (8,31), "BB:12" или (12,54) - что понятнее и естественнее? "R1C1" - это же Декартова система координат (Y,X) в школьном учебнике!
Old school
Для именно программирования он, кстати, однозначно удобнее, абсолютно согласен.
Недавно открыл для себя ещё одну мега удобную вещь,которую можно сделать только с этим стилем. На сайте вроде не встречал, так что поделюсь с читателями, может кому пригодится.
Если в формуле хочется использовать именованные диапазоны (RangeNames), то есть вместо формулы B3*B4 иметь что-то вроде Объем * Цена, то обычно выделяют всю строку(столбец) с исходными данными, то есть получается массив (или вектор - назовите как хотите), например "Выручка" - В3:X3, "Цена" - B4:X4. Тогда, конечно, можно вводить эти имена в формулу, и формула примет более благообразный и понятный вид, но есть несколько но
- при использовании некоторых формул, например МАКС, Excel будет использовать весь массив в качестве аргумента, то есть если у вас две цены Цена1; Цена 2, то с формулой Макс (Цена1, Цена2) посчитается не максимальная цена из двух цен в текущем столбце, а максимальная цена за весь период.
- Если зайти в режим редактирования - то будет подсвечиваться весь диапазон - тоже не всегда удобно и понятно, откуда берутся значение для формулы
Но можно задать не статичный диапазон, а смещающийся. Надо зайти в режим R1C1 и при создании диапазона не фиксировать столбец, то есть вместо R3C2:R3C24 вписать R3C. Тогда в любой ячейке Excel при использовании этого диапазона будет использоваться значение из третьего ряда и текущего столбца, причём даже в режиме ссылок A1. И, что самое интересное, в режиме А1 адрес диапазона будет меняться, в зависимости от того, в какой ячейке вы находитесь. По мне, так это какая-то магия, если честно. Таки образом решаются вышеупомянутые проблемы.
Надеюсь, понятно объяснил. Пока писал, понял, что можно подобную вещь и без этого стиля ссылок сделать, но всё равно не так красиво получится.
Хей други, скажите как перевести абсолютный адрес одной ячейки из формата A1 в формат R1C1 и обратно.
Например, $A$1 -> R1C1, R1C1 -> $A$1.
Изменения формата ячейки Excel средствами VBA в зависимости от значения другой ячейки
Здравствуйте. Столкнулся с проблемой. Необходимо на листе Excel Залить, предположим, ячейку "C4".
Копировать ячейки с красным шрифтом с одного листа на другой
Добрый День! Есть задача копировать с листа3 на лист1 ячейки(всего их 30) с красным шрифтом.
Как вытащить значение ячейки из одного документа в другой
Есть документ, который служит для расчета проектных данных и фактических. В этот документ нужно по.
Копирование значения ячейки с одного листа и вставка в другой
Здравствуйте! Делаю дз по информатике, VBA. Задание заключается вот в чем: есть два листа в.
Debug.Print [R1C1].Address() выбивает ошибку:
Run-time error '424'
Object required
Добавлено через 4 минуты
Если глобально поменять стиль ссылок в документе на R1C1, то пашут оба варианта, но я не имею доступа к компу, на котором тестируются программы, так что нуна найти другое решение.
Получаю Wrong Answer на 3 тесте:
КулХацкеръ, а, это учебное задание. Приведите условие ПОЛНОСТЬЮ.
Существуют адреса, которые являются допустимыми в обеих стилях, но означают разные диапазоны, например
R3 - относительная ссылка на одну ячейка в А1, абсолютная ссылка на 3-ю строку в R1C1;
C5:C7 - относительная ссылка на диапазон в А1, абсолютная ссылка на 3 столбца в R1C1.
В ячейке A1 листа "Input" дано n - количество адресов для обработки.
В диапазоне B1:Bn листа "Input" - абсолютные адреса одной ячейки для обработки в форматах A1 и/или R1C1.
Для каждой ячейки диапазона B1:Bn листа "Input" вывести в соответствующую ячейку диапазона A1:An листа "Output" адрес ячейки, представленный в другом формате.
Для R3 ответ должен быть "Неверный формат"! ни относительные ссылки, ни диапазоны не допускаются согласно условию, допускаются только абсолютные ссылки на одну ячейку.
Для C5:C7 ответ должен быть "Неверный формат".
Добавлено через 1 час 0 минут
Кто-нибудь мне поможет?
Добавлено через 4 часа 48 минут
OK, с 3 тестом разобрался самостоятельно:
Добавлено через 1 час 25 минут
Казанский, дружище, помоги плиз.
Проставляется таких формул в ячейки примерно около 200.
Сейчас нужно расширить диапозон с В10000 до В15000
Перезаписывать макрорекордом долго. и как всегда будет где-нить ошибка или не в ту ячейку. и т.п.
В связи с этим 2 вопроса:
1) как можно изменить в уже написанном макросе стиль ссылок на ячейки в человеческий вид?
если можно изменить, то потом просто - поиск и замена В10000 на В15000 во всех модулях.
2) Где настраивается макрорекордер чтоб он записывал по человечески в читаемом и понимаемом (для меня) формате?? Просмотрел все, стиль ссылок в параметрах без отметки "в стиле Р1С1".
Изменение стиля тэга Select после выбора элемента списка?
Всем привет, друзья, подскажите пожалуйста, хочу неким образом изменить выпадающий список.
Изменение стиля
Здравствуйте! У меня проблема с изменением стиля,нужно сделать невидимый текст,видемым.
Изменение стиля
Добрый день. Кто нибудь сталкивался с такой проблемой, при переводе на стиль ХР все.
mrf, стиль ссылок R1C1 близок к внутреннему формату хранения формул в Excel. Поэтому рекордер так и записывает.
Когда Вы протягиваете ячейку с формулой - ячейки заполняются ТОЙ ЖЕ САМОЙ формулой в стиле R1C1.
И для программирования этот стиль часто бывает удобнее, т.к. позволяет использовать номера столбцов, а не их буквенные имена.
Вместо
но если при выполнении макроса активная ячейка будет другой, то и формула получится другая.
Почему нужно расширить диапазон именно до 15000 строки? А не до 20000 например? В макросе можно найти последнюю строку и подставить ее в формулу, тем самым избегая лишних вычислений.
Казанский, это я все понимаю, но дело в том, что формулы не протягиваются, а "впечатываются" в различные ячейки. Формулы разные(где-то countcolor, где-то через if и т.п.), у каждой разный диапазон по столбцам, но все заканчиваются в одной и той же(по номеру) строчке. Примерно 60% этих формул находятся в одном макросе, остальные 40% разбросаны по 30 другим макросам. Проблема, ввиду моих поверхностных знаний о ВБА и обучении только по этому форму и, в частности по Вашим постам и советам, была заложена мной изначально.. к сожалению..
Теперь, когда проект разросся, проблема вышла на передний план.. Переделывать все долго и утомительно, переписывать макрорекордером тоже не удобно. Вот и подумал, что возможно чем-нибудь автоматически переделать стиль ссылок, а потом также автоматически фаинд-реплейс 10000 на 15000.
Ограничение в 15000 в некоторых местах установлено специально как контрольная точка для тотальной перепроверки. В некоторых местах установлено для того, чтобы на одном листе сначала считать первые 15000, а потом вторые 15000 (которые идут со сдвигом в столбцах).
Помогите разобратся с R1C1. есть переменая которая хранит значение frm.items(i) : '72'!R[-4]C[3] где 72 это номер листа. теперь относительно это ссылку нужно достать данные с другой ячейки.
Что-то типа:
Вставка R1C1 формулы по условию
Добрый вечер! Прошу подсказать с задачей: Как в столбец В вставить для Апельсинов одну R1C1.
Замена формулы стиля R1C1
имеется макрос Sub Макрос1() Set p4 = ThisWorkbook.Sheets(1).Cells.Find(what:="дата.
Как прочесть формулу в стиле R1C1?
Подскажите, плиз, что это за формула и как ее можно прочитать. Спасибо. ActiveCell.FormulaR1C1 =.
Как задать диапазон копирования в стиле R1C1
Есть выражение типа Cells(roww, collGo).Range("A1:HN1").Copy В части ("A1:HN1") необходимо.
это относительная ссылка. Чтобы получить диапазон по этой ссылке, надо знать, относительно какого диапазона она сделана.
pluprod, если Вы опускаете последний аргумент RelativeTo метода Application.ConvertFormula, он возвращает ссылку относительно ActiveCell. Это допустимо, если у вас вся работа происходит через Select-Selection (хотя в этом случае можно утверждать, что код неоптимальный), в ином случае результат может быть непредсказуемым.
Range.Find метод не работает при адресации ячеек r1c1 - error 1004
Всем привет, собственно сабж. ВОт проблемный код (ошибка вылетает на строке 16 из-за того, что.
Стиль ссылок R1C1
Здравствуйте По поводу Microsoft Excel. Скажите пожалуйста, кто нибудь встречался с такой.
Стиль ссылок R1C1
Всем доброго времени суток. Есть файл екселя (прайс лист) Там очень интересно сделано: 1.
Как в Excel изменить ReferenceStyle на R1C1 с учетом позднего связывания?
Доброго времени суток! Не подскажете, как в Excel изменить ReferenceStyle на R1C1 с учетом.
Работа с двумерными числовыми массивами. Использование указателей. Использование функций пользователя.
Помогите пожалуйста. Сделать три варианта: первый вариант – передача данных между.
Возвращает строковое значение, представляющее ссылку на диапазон на языке макроса.
Синтаксис
выражение.Адрес (RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
выражение: переменная, представляющая объект Range.
Параметры
Имя | Обязательный или необязательный | Тип данных | Описание |
---|---|---|---|
RowAbsolute | Необязательный | Variant | Значение True, чтобы возвратить часть строки ссылки в качестве абсолютной ссылки. Значение по умолчанию — True. |
ColumnAbsolute | Необязательный | Variant | Значение True, чтобы возвратить часть столбца ссылки в качестве абсолютной ссылки. Значение по умолчанию — True. |
ReferenceStyle | Необязательный | XlReferenceStyle | Стиль ссылки. Значение по умолчанию — xlA1. |
External | Необязательный | Variant | Значение True, чтобы вернуть внешнюю ссылку. Значение False, чтобы вернуть локальную ссылку. Значение по умолчанию — False. |
RelativeTo | Необязательный | Variant | Если RowAbsolute и ColumnAbsolute имеют значение False, а ReferenceStyle — xlR1C1, необходимо включить начальную точку для относительной ссылки. Этот аргумент является объектом Range, определяющим начальную точку. |
Примечания
Если ссылка содержит более одной ячейки, аргументы RowAbsolute и ColumnAbsolute применяются ко всем строкам и столбцам.
Пример
Поддержка и обратная связь
Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.
Читайте также: