Excel быстрое переключение стиля ссылок
= Мир MS Excel/Статьи об Excel
| Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
| Приёмы работы с формулами [13] |
| Настройки Excel [3] |
| Инструменты Excel [4] |
| Интеграция Excel с другими приложениями [4] |
| Форматирование [1] |
| Выпадающие списки [2] |
| Примечания [1] |
| Сводные таблицы [1] |
| Гиперссылки [1] |
| Excel и интернет [1] |
| Excel для Windows и Excel для Mac OS [2] |
Что мы знаем о функции ДВССЫЛ()?
Название ДВССЫЛ означает Двойная ссылка, то есть ссылка на ссылку. Эта функция позволяет получить значение ячейки, имя (адрес) которой является аргументом функции ДВССЫЛ(). Например, формула =ДВССЫЛ("A1") вернёт содержимое ячейки А1, а формула =ДВССЫЛ(A1) вернёт содержимое ячейки, адрес которой находится в ячейке А1. Аналогичный результат дадут формулы =ДВССЫЛ("R1C1") и =ДВССЫЛ(R1C1), если Excel настроен на применение стиля ссылок R1C1.
Небольшое отступление. Excel в зависимости от настроек работает с одним из двух стилей ссылок: A1 или R1C1. В первом случае столбцы обозначаются буквами латинского алфавита, соответственно, адрес ячейки выглядит как А1, D10 и т.д. Во втором случае столбцы, так же как и строки, обозначаются числами, а адрес выглядит, например, так: R1C1, R10C4 (строка10 столбец4), R[-1]C (ячейка в предыдущей строке и в том же столбце относительно активной).
Большинство пользователей предпочитают использовать стиль A1, но для некоторых задач стиль R1C1 является более удобным.
Изменить используемый стиль ссылок можно в Параметрах Excel -- Формулы -- флажок Стиль ссылок R1C1.
Второй, необязательный, аргумент функции ДВССЫЛ(), который может быть равен 0 или 1 (ЛОЖЬ или ИСТИНА), как раз определяет стиль используемой ссылки. 1 соответствует стилю A1 (принимается по умолчанию), а 0 - типу R1C1
Самое интересное заключается в том, что функция ДВССЫЛ() позволяет менять стиль ссылок в формуле, не изменяя настройки Excel, например, использовать в формулах стиль ссылок R1C1, в то время как Excel настроен на стиль A1.
Рассмотрим несколько примеров.
Пример1.
Предположим, нам нужно подсчитывать сумму с накоплением для столбца с данными (см. рисунок).
Пример2.
Достаточно часто функция ДВССЫЛ() используется, когда нужно брать данные с разных листов в зависимости от значения ячейки, в которой содержится имя листа.
Предположим, есть некие данные за несколько лет по месяцам. Данные за год располагаются на листе с соответствующим именем. В приложенном файле-примере это листы 2014, 2015 и 2016.
На листе Отчёт мы хотим получить данные с выбранного листа. Выбор осуществляется с помощью выпадающего списка в ячейке B1. Структура таблиц на всех листах одинакова (см. рисунок)
В этом случае формула на листе Отчёт будет выглядеть так =ДВССЫЛ($B$1&"!RC";0), где в ячейке $B$1 содержится год, адрес RC означает, что мы получим значение из тех же строки и столбца, что и активная ячейка, а второй аргумент 0, как мы уже знаем, позволяет использовать стиль ссылок R1C1. Причём эта формула легко копируется как вниз, так и вправо на всю таблицу.
Кроме этого, предположим, на листах 2015 и 2016 мы хотим получить разницу показателей по сравнению с тем же периодом предыдущего года.
С этой задачей успешно справится формула =B4-ДВССЫЛ($B$1-1&"!RC[-2]";0). В данном случае мы берём значения с листа, год которого на 1 меньше, чем у текущего, из ячейки, находящейся на той же строке, но на два столбца левее активной, и вычитаем его из данных за текущий год. Формула также копируется вниз и вправо.
В заключении надо добавить, что функция ДВССЫЛ() является волатильной, поэтому не стоит увлекаться ей на больших объёмах
Как включить или отключить стиль ссылок R1C1 в Excel?
Как уже говорилось выше, по умолчанию в Excel используется стиль ссылок A1, поэтому чтобы начать работать с R1C1 сначала надо его включить. Для этого заходим в Файл -> Параметры и во вкладке Формулы и в блоке Работа с формулами ставим галочку напротив поля Стиль ссылок R1C1:
Соответственно, если нужно вернуться к стандартному варианту адресации вида A1, то стиль R1C1 можно отключить убрав соответствующую галочку в настройках.
Особенности и отличия стилей адресации A1 и R1C1
Как известно, в Excel есть 3 типа ссылок (тут можно почитать подробнее): относительные (А1), абсолютные ($А$1) и смешанные ($А1 и А$1), где знак доллара ($) служит закреплением номера строки или столбца.
В случае со стилем R1C1 также можно использовать любой тип ссылки, но принцип их составления будет несколько другим:
- RC. Относительная ссылка на текущую ячейку;
- R1C1. Абсолютная ссылка на ячейку на пересечении строки 1 и столбца 1 (аналог $A$1);
- RC2. Ссылка на ячейку из 2 столбца текущей строки;
- R3C. Ссылка на ячейку из 3 строки текущего столбца;
- RC[4]. Ссылка на ячейку на 4 столбца правее текущей ячейки;
- R[-5]C. Ссылка на ячейку на 5 строк выше текущей ячейки;
- R6C[7]. Ссылка на ячейку из 6 строки и на 7 столбцов правее текущей ячейки;
- и т.д.
В общем и целом, получается, что аналогом закрепления строки или столбца (символа $) для стиля R1C1 является использование чисел после символа строки или столбца (т.е. после букв R или C).
В итоге, основное и самое главное отличие между А1 и R1C1 состоит в том, что для относительных ссылок стиль А1 за точку отсчета берет начало листа, а R1C1 ячейку в которой написана формула.
Именно на этом и строятся основные преимущества использования R1C1, давайте подробнее на них остановимся.
Преимущества стиля ссылок R1C1
Можно выделить 2 основных преимущества, при которых использовать R1C1 предпочтительнее, чем A1: при проверке формул (поиске ошибок) и в макросах.
Рассмотрим пример: возьмем простую таблицу, в которой проверим на корректность расчета формулы в последнем столбце:
Видите ошибку?
На первый взгляд может показаться, что никаких ошибок нет и все нормально, формулы похожи друг на друга, из общего ряда ничего не выбивается. Но если присмотреться более внимательно, то можно заметить, что на самом деле ошибка есть и в 4 и 5 строках формулы поменяны местами друг с другом.
Давайте теперь посмотрим эту же таблицу со стилем ячеек R1C1:
В данном случае сразу видно, что формулы в 4 и 5 строках не похожи на другие, и найти ошибку уже гораздо проще.
На таблицах большего размера выискивать ошибку будет еще труднее, поэтому данный способ поиска может существенно упростить и ускорить процесс.
Аналогичный прием можно применить и в случае с работой с ячейками при создании макросов.
В случае, если нам нужно прописать для диапазона ячеек формулы произведения двух столбцов (как в примере выше), то для стиля R1C1 все формулы будут записываться абсолютно одинаково (=RC[-2]*RC[-1]), поэтому в коде макроса можно сразу прописать формулу для всех ячеек диапазона. Для стиля A1, к сожалению, такой способ не подойдет, так как записи всех формул отличаются друг от друга и каждую нужно будет прописывать отдельно, что несколько усложняет задачу.
R1C1 в функциях Excel
При изменении стиля с A1 на R1C1 все ссылки используемые в качестве аргументов в функциях будут автоматически отображаться в новом формате, и никаких проблем с изменением стиля возникнуть не должно.
Однако в Excel есть функции, в которых возможно применение обоих стилей адресации вне зависимости от установленного режима в настройках. В частности, функции ДВССЫЛ (INDIRECT в английской версии) и АДРЕС (ADDRESS в английской версии) могут работать в обоих режимах.
В качестве одного из аргументов в данных функциях задается стиль используемых ссылок (A1 или R1C1), и в некоторых случаях бывает предпочтительнее использовать как раз R1C1.
Спасибо за внимание!
Если у вас есть мысли или вопросы по использованию и преимуществам разных стилей ссылок — пишите в комментариях.
После этого вы увидите, что привычные буквы исчезли, и теперь появились цифры:
На первой картинке мы видим, что все формулы отличаются. А в стиле R1C1 все формулы абсолютно одинаковые и сразу видно, где ошибка. Конечно, данный пример надуманный, но, когда данных очень много, польза данного стиля становится очевидной.
Думаю теперь у вас не должно возникнуть вопросов с чтением стиля R1C1 .
Напоследок, давайте напишем макрос, который будет переводить из одного стиля в другой, и повесим кнопку данного макроса на панель быстрого доступа:
В данном случае вы можете начать записывать макрос, затем его остановить. Далее нужно будет войти в макрос и вставить данный код.
Готово, теперь наш макрос висит на панели быстрого доступа. Попробуйте попереключать стили и убедитесь, что всё работает:
В данной статье вы узнали, что такое стиль R1C1 в Excel, а также в каких случаях его стоит применять.
На связи был Алексей Гулынин, оставляйте свои комментарии, увидимся в следующих статьях.
Такое случается иногда - открыв файл, содержащий Excel-таблицу, мы вдруг обнаруживаем, что привычная буквенная нумерация колонок в нем заменена цифровой или наоборот. Это означает, что создатель таблицы использовал стиль ссылок, отличный от установленного в нашем экземпляре редактора таблиц. Поменять его не составляет особого труда.
- Как изменить стиль ссылок
- Как изменить цвет одной ссылки
- Как изменить цвет гиперссылки
- Как изменить шрифт ссылки
Откройте в табличном редакторе файл, содержащий ту таблицу, стиль ссылок в которой нужно изменить и нажмите большую круглую кнопку в левом верхнем углу окна редактора. Microsoft называет ее «Кнопка Office».
Щелкните прямоугольную кнопку с надписью «Параметры Excel», помещенную в правый нижний угол главного меню, рядом с кнопкой «Выход из Excel». Можно вместо щелчка нажать на клавиатуре клавишу «М». В любом случае в результате будет открыто окно, дающее доступ к настройкам табличного редактора.
Перейдите на вкладку настроек, связанных с вычислением формул, быстродействием и обработкой ошибок. Для этого выберите в списке, размещенном в левой панели окна настроек, строку «Формулы».
Найдите секцию «Работа с формулами» - она помещена на этой вкладке под секцией «Параметры вычислений». В этой секции и находится чекбокс, в котором следует поставить (либо снять) отметку, чтобы изменить стиль ссылок, используемый в открытом в настоящий момент документе. Справа от чекбокса помещена надпись «Стиль ссылок R1C1». Изменить значение этой настройки можно, щелкнув ее курсором мышки или нажав на клавиатуре сочетание клавиши ALT с клавишей единицы.
Сохраните документ с измененной системой нумерации колонок. После того, как вы внесли изменения в настройки, редактор автоматически изменил обозначения ссылок, использованных в формулах всех ячеек этого документа. Однако если вы не сохраните документ в таком виде, то при следующей его загрузке ссылки вновь будут присутствовать в формулах и заголовках колонок в своем исходном стиле.
В более ранней версии табличного редактора кнопка Office отсутствует, а для доступа к соответствующей настройке изменения стиля ссылок в Excel 2003 надо использовать раздел «Параметры» в его меню. Такой же чекбокс с надписью «Стиль ссылок R1C1» размещен на вкладке «Общие».
Читайте также: