Функция ДВССЫЛ и динамическая ссылка на лис Excel
Допустим мы работает с рабочей книгой Excel, которая содержит множество листов. В старых версиях программы Excel максимальное количество созданных листов в книге достигало – 255, а в новых версиях листы не ограничены по количеству, ограничиваются только размером оперативной памяти компьютера. Нам необходимо получить значения ячейки с другого листа данной рабочей книги.
Пример функции ДВССЫЛ
Нам известно имя листа (Март) и оно введено в отдельную ячейку B1. Решить данную задачу легко опираясь на формулу, которая содержит 2 простые функции:
- ДВССЫЛ.
- СЦЕПИТЬ.
Для примера изобразим эту ситуацию и ее решение на рисунках:
На этом листе отображаются значения определенных ячеей с других листов книги Excel.
Чтобы отобразить первое значение из листа «Март» и его ячейки C3 в ячейку B2 главного листа введите формулу:
В результате мы получили значение ячейки C3 из листа «Март»:
Можно так же в аргументах функции СЦЕПИТЬ указать не только ссылку на ячейку с именем листа, но и ссылку на ячейку, которая в своем значении содержит имя ячейки этого листа. Тогда целый адрес можно динамически изменять при необходимости по названиям листов и адресов их ячеек. Чтобы получить такой эффект необходимо немного модифицировать нашу формулу:
- Добавьте новую строку между первой и второй. Для этого выделите целую строку 2 и нажмите комбинацию горячих клавиш CTRL+SHIFT+=. Или щелкните по заголовку второй строки правой кнопкой мышки, а потом из появившегося контекстного меню выберите опцию «Вставить».
- Для ячейки B2 введите значение C3.
- В ячейке B3 измените формулу, а точнее аргументы ее функции СЦЕПИТЬ:
Теперь формула работает в полном динамическом режиме:
Конечно же все возможности этой формулы нельзя описать в одной статье. Главная цель этого урока продемонстрировать каким способом можно динамически изменять ссылки на ячейки, даже на другие листы и книги.
Разбор принципа действия формулы динамической ссылки на лист Excel:
Функция ДВССЫЛ позволяет преобразовать текстовое значение в ссылку. При этом в первом ее аргументе указана функция СЦЕПИТЬ. Она позволяет собрать несколько разных текстов в одну динамическую ссылку используя несколько аргументов внутри функции. Во втором не обязательном аргументе ДВССЫЛ мы имеем возможность указывать номером стиль адресации:
- 1-классический (например, А1);
- 0-нумерированый по строкам и столбцам (например, R1С1).
Вернемся к функции СЦЕПИТЬ – предназначена для сложения нескольких частей текста в одну текстовую строку. В этом примере функция СЦЕПИТЬ собирает нам конструкцию ссылки с трех частей текста (в данном случаи). Каждая часть текстовой строки указывается в отдельном аргументе. Функция СЦЕПИТЬ позволяет создавать максимально до 255 аргументов.
- Имя листа (Март).
- Знак восклицания (!) обязательный символ для создания адреса указывающего на другой лист.
- Необходимая ячейка (C3).
Вместо имени листа и ячейки мы указываем B1 и B2 что позволяет нам в формуле динамически менять адрес ссылок и соответственно возвращаемое формулой значение.
Полезный совет! Для многих пользователей Excel часто удобнее использовать в место функции СЦЕПИТЬ символ амперсанд (&). Тогда наша формула будет содержать только одну функцию:
Данный метод более быстрый и особенно подойдет, когда нужно сложить текстовую строку более чем из 255 частей. Но первый вариант более читабельный.