Как сделать динамическую ссылку в excel
Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.
Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.
Синтаксис функции с описанием
Функция ДВССЫЛ в Excel: примеры
Начнем с хрестоматийного примера, чтобы понять принцип работы функции.
Имеется таблица с данными:
Примеры функции ДВССЫЛ:
Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).
Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.
Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.
Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.
Чтобы получить только нечетные записи, используем формулу:
Для выведения четных строк:
Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.
Чтобы функция сработала, все отчеты поместим на один лист.
Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.
Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.
Если мы используем только функцию ВПР, появится ошибка:
Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.
Функции ВПР и ДВССЫЛ в Excel
Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.
Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:
В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:
Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:
- $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
- $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
- _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.
Таким образом, эти две функции выполняют подобного рода задачи на отлично.
Допустим мы работает с рабочей книгой 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 частей. Но первый вариант более читабельный.
Имеем две таблицы на разных листах одной книги: таблицу с заказами от клиентов (на листе Заказы) и таблицу с клиентской базой (лист Клиенты). Работая с таблицей заказов, хочется иметь возможность быстро переходить на таблицу с клиентами, чтобы просмотреть подробные данные по клиенту (адрес, ФИО директора и т.д.). То есть, другими словами, хочется в таблице заказов иметь гиперссылку в каждой строке, при щелчке мышью по которой будет происходить переход на лист Клиенты, причем именно на ту строчку где упоминается данный клиент:
Что-то типа типа функции ВПР (VLOOKUP), но не ради подстановки данных, а для быстрой ссылки из одной таблицы в другую.
Шаг 1. Создаем переменную с именем листа
Для создания гиперссылок в будущем нам понадобится каждый раз прописывать имя текущего файла и имя листа Клиенты, на который пойдут ссылки. Поэтому проще один раз создать переменную в памяти Excel с нужным значением и обращаться к ней потом по мере надобности.
В Excel 2007/2010 для этого можно воспользоваться вкладкой Формулы (Formulas) и кнопкой Диспетчер имен (Name Manager) . В более старых версиях выбрать в меню Вставка - Имя - Присвоить (Insert - Name - Define) . В открывшемся окне нажмите кнопку Создать (New) и введите туда имя переменной (я назвал ее для примера Мой_Лист) и формулу в строку Диапазон (Reference) :
Разберем эту конструкцию на составляющие для понятности:
- ЯЧЕЙКА("имяфайла";Клиенты!$A$1) - функция, которая по адресу заданной ячейки (А1 с листа Клиенты) выдает любые нужные данные по листу и файлу. В данном случае - полный путь к текущему файлу до листа в виде D:\Рабочие документы\Договоры[Бюджет.xls]Клиенты
- Из этой строки нам нужна только часть с именем файла и листа (без диска и папок), поэтому мы ищем первое вхождение квадратной открывающей скобки в строку с помощью функции ПОИСК (FIND) и затем вырезаем из строки все, начиная с этого символа и до конца (256 символов) с помощью функции ПСТР (MID) .
- В конце, к вырезанному фрагменту с именем файла и листа приклеиваем восклицательный знак - стандартный разделитель имен листов и адресов ячеек в формулах, т.к. дальше должны будут идти адреса ячеек.
Таким образом эта формула выдает на выходе имя текущего файла в квадратных скобках с расширением с приклееным к нему именем листа и восклицательным знаком. Работу формулы легко проверить - просто введите в любую пустую ячейку =Мой_Лист и нажмите клавишу Enter.
Шаг 2. Создаем гиперссылки
Выделите пустую ячейку в строке напротив первого заказа и введите туда вот такую формулу:
= ГИПЕРССЫЛКА(Мой_Лист& АДРЕС( ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) ;1) ;">>")
Разберем ее на составляющие аналогичным образом:
- Функция ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0) - ищет порядковый номер ячейки в диапазоне А1:А7 на листе Клиенты, где встречается название текущего клиента из B2 (последний аргумент =0 означает поиск точного совпадения, аналогично функции ВПР)
- Функция АДРЕС формирует адрес ячейки (в виде текстовой строки) по номеру строки и столбца, т.е. адрес ячейки с нужным клиентом, куда должна потом ссылаться гиперссылка
- Затем мы приклеиваем к адресу ссылку на файл и лист (переменную Мой_Лист) и используем это в качестве аргумента для функции ГИПЕРССЫЛКА (HYPERLINK) , которая, собственно, и создает нужную нам ссылку.
При желании, можно заменить внешнее представление гиперссылки с банальных символов ">>" на что-нибудь поинтереснее с помощью функции СИМВОЛ (CHAR) , которая умеет выводить нестандартные символы по их кодам:
=ГИПЕРССЫЛКА(Мой_Лист&АДРЕС(ПОИСКПОЗ(B2;Клиенты!$A$1:$A$7;0);1);СИМВОЛ(117))
Так, например, если использовать шрифт Wingdings 3 и символ с кодом 117, то можно получить вот такие симпатичные значки гиперссылок:
Если вы хотя бы в общих чертах знакомы с функцией ВПР (VLOOKUP) (если нет, то сначала бегом сюда), то должны понимать, что эта и другие похожие на неё функции (ПРОСМОТРХ, ИНДЕКС и ПОИСКПОЗ, ВЫБОР и т.д.) всегда выдают в качестве результата значение - число, текст или дату, которые мы ищем в заданной таблице.
Но что, если вместо значения нам хочется получить живую гиперссылку, щёлкнув по которой мы могли бы мгновенно перепрыгнуть к найденному совпадению в другой таблице, чтобы посмотреть на него в общем контексте?
Предположим, что в качестве исходных данных у нас есть большая таблица по заказам для наших клиентов. Для удобства (хотя это и не обязательно) я преобразовал таблицу в динамическую "умную" сочетанием клавиш Ctrl + T и дал на вкладке Конструктор (Design) ей имя таблЗаказы :
На отдельном листе Сводная я построил сводную таблицу (хотя не обязательно это должна быть именно сводная - подойдёт любая таблица в принципе), где по исходным данным считается динамика продаж по месяцам для каждого клиента:
Добавим в таблицу заказов столбец с формулой, которая ищет название клиента для текущего заказа на листе Сводная . Используем для этого классическую связку функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) :
Теперь завернём нашу формулу в функцию ЯЧЕЙКА (CELL) , которую попросим вывести нам адрес найденной ячейки:
Теперь при щелчке мышью по любой из ссылок мы моментально перескочим на на ячейку с названием компании на листе со сводной таблицей.
Улучшение 1. Переход к нужному столбцу
Чтобы было совсем хорошо, немного усовершенствуем нашу формулу, чтобы переход происходил не названию клиента, а к конкретному числовому значению именно в том столбце-месяце, когда выполнен соответствующий заказ. Для этого придётся вспомнить, что функция ИНДЕКС (INDEX) в Excel очень многоплановая и может использоваться, в том числе, и в формате:
=ИНДЕКС( Двумерный_диапазон ; Номер_строки ; Номер_столбца )
То есть в качестве первого аргумента мы можем указать не столбец с названиями компаний в сводной, а всю область данных сводной таблицы, а в качестве третьего аргумента дописать номер нужного нам столбца. Его легко можно вычислить функцией МЕСЯЦ (MONTH) , возвращающей номер месяца для даты сделки:
Улучшение 2. Красивый символ ссылки
Второй аргумент функции ГИПЕРССЫЛКА - текст, который отображаться в ячейке со ссылкой - можно сделать посимпатичнее, если использовать вместо банальных знаков ">>" нестандартные символы из шрифтов Windings, Webdings и им подобных. Для этого можно использовать функцию СИМВОЛ (CHAR) , которая умеет отображать символы по их коду.
Так, например, символ с кодом 56 в шрифте Webdings даст нам красивую двойную стрелку для гиперссылки:
Улучшение 3. Подсветка текущей строки и активной ячейки
Ну, и для окончательной победы красоты над здравым смыслом можно прикрутить к нашему файлу еще и упрощенный вариант подсветки текущей строки и той ячейки, на которую мы переходим по ссылке. Для этого потребуется простенький макрос, который мы повесим на обработку события изменения выделения на листе Сводная.
Для этого щёлкнем правой кнопкой мыши по ярлычку листа Сводная и выберем команду Просмотреть код (View code) . В открывшееся окно редактора Visual Basic вставим следующий код:
Как легко сообразить, здесь мы сначала убираем заливку со всего листа, а затем заливаем желтым (код цвета 6) всю строку в сводной, а потом оранжевым (код 44) текущую ячейку.
Теперь при выделении любой ячейки внутри сводной (не важно - вручную или в результате перехода по нашей гиперссылке) будет подсвечиваться вся строка и ячейка с нужным нам месяцем:
Читайте также: