Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 35720
Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

Функция ДВССЫЛ() и стиль ссылок R1C1

Что мы знаем о функции ДВССЫЛ()?
Название ДВССЫЛ означает Двойная ссылка, то есть ссылка на ссылку. Эта функция позволяет получить значение ячейки, имя (адрес) которой является аргументом функции ДВССЫЛ(). Например, формула =ДВССЫЛ("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. 
Предположим, нам нужно подсчитывать сумму с накоплением для столбца с данными (см. рисунок). 

 


Одним из вариантов формулы является =СУММ(B1;A2). Но у этой формулы есть недостаток: при удалении строки формула ломается и возвращает ошибку #ССЫЛКА! Как сделать так, чтобы при удалении строки формула по-прежнему ссылалась на предыдущую ячейку? На помощь приходит ДВССЫЛ в формуле =СУММ(ДВССЫЛ("R[-1]C";0);A2). В данной формуле R[-1]C - это ссылка на ячейку, которая находится на строку выше от активной ячейки и в том же столбце. А второй аргумент функции ДВССЫЛ(), равный 0, как раз и позволяет использовать этот стиль ссылок в формуле, не изменяя общие настройки Excel.

 


Пример2.
Достаточно часто функция ДВССЫЛ() используется, когда нужно брать данные с разных листов в зависимости от значения ячейки, в которой содержится имя листа.
Предположим, есть некие данные за несколько лет по месяцам. Данные за год располагаются на листе с соответствующим именем. В приложенном файле-примере это листы 2014, 2015 и 2016.
На листе Отчёт мы хотим получить данные с выбранного листа. Выбор осуществляется с помощью выпадающего списка в ячейке B1. Структура таблиц на всех листах одинакова (см. рисунок)

 

 

В этом случае формула на листе Отчёт будет выглядеть так =ДВССЫЛ($B$1&"!RC";0), где в ячейке $B$1 содержится год, адрес RC означает, что мы получим значение из тех же строки и столбца, что и активная ячейка, а второй аргумент 0, как мы уже знаем, позволяет использовать стиль ссылок R1C1. Причём эта формула легко копируется как вниз, так и вправо на всю таблицу.


Кроме этого, предположим, на листах 2015 и 2016 мы хотим получить разницу показателей по сравнению с тем же периодом предыдущего года.

С этой задачей успешно справится формула =B4-ДВССЫЛ($B$1-1&"!RC[-2]";0). В данном случае мы берём значения с листа, год которого на 1 меньше, чем у текущего, из ячейки, находящейся на той же строке, но на два столбца левее активной, и вычитаем его из данных за текущий год. Формула также копируется вниз и вправо.

 

 

В заключении надо добавить, что функция ДВССЫЛ() является волатильной, поэтому не стоит увлекаться ей на больших объёмах

 

Скачать пример

 

Категория: Приёмы работы с формулами | Добавил: Pelena (15.10.2016)
Просмотров: 470 | Теги: стиль ссылок, двссыл, R1C1 | Рейтинг: 0.0/0


Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!