Как сделать ссылку двойную
Как сделать двойную ссылку в excel?
Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.
Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.
Синтаксис функции с описанием
Почему при работе функции ДВССЫЛ возникают ошибки:
Функция ДВССЫЛ в Excel: примеры
Начнем с хрестоматийного примера, чтобы понять принцип работы функции.
Имеется таблица с данными:
Примеры функции ДВССЫЛ:
Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).
Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.
Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.
Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.
Чтобы получить только нечетные записи, используем формулу:
Для выведения четных строк:
Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.
Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.
Чтобы функция сработала, все отчеты поместим на один лист.
Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.
Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.
Если мы используем только функцию ВПР, появится ошибка:
Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.
Исправить положение помогла функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой.
Функции ВПР и ДВССЫЛ в Excel
Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.
Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:
В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:
Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:
Таким образом, эти две функции выполняют подобного рода задачи на отлично.
Я как раз собирался написать о том, как работает функция ДВССЫЛ, почему бы не рассмотреть ее именно на этом примере.
Итак, мы имеем файл Excel, в котором много листов. Давайте рассмотрим простой пример, где названия листов у нас не менялись. То есть у нас есть Лист1, Лист2, Лист3 и так далее.
Допустим, что на каждом листе (Лист1-Лист5) у нас есть данные по выручке 5 различных магазинов в виде одинаковых таблиц данных.
На листе 6 есть результирующая таблица, где нам необходимо заполнить ее данными, взяв их со всех листов.
Таким образом, чтобы заполнить данные за январь нам необходимо взять данные по выручке магазинов, находящихся в ячейке B2, но на 5 различных листов.
Конечно, в нашем примере это можно было бы сделать в ручную, указав просто ссылки на соответствующие значения, но если листов очень много, или данных очень много, то вручную это сделать будет очень затруднительно.
Существует различные способы решить данную задачу, например, с помощью простенького макроса, который можно записать макрорекодером. Ну а мы с вами для решения задачи будет использовать функцию ДВССЫЛ.
ДВССЫЛ – функция, которая возвращает ссылку заданную текстовой строкой.
Чтобы понять принцип действия данной функцию, давайте рассмотрим такой пример.
Посмотрите на рисунок ниже. В ячейке А1 написан текст D4, а в самой ячейке D4 указано число 9999. Если мы напишем формулу ДВССЫЛ(A1), то в результате получим число 9999. То же самое мы получим если напишем формулу так:
=ДВССЫЛ(«D4»)
То есть мы написали текстом адрес ячейки D4 и функция ДВССЫЛ вернула нам то значение, которое находится по данному адресу (D4).
Надеюсь, все стало более понятным. Теперь давайте рассмотрим на нашем примере. Чтобы заполнить данные за январь с разных листов нам необходимо прописать следующие ссылки на ячейки.
=Лист1!B2 (Выручка за январь магазина 1)
=Лист2! В2 (Выручка за январь магазина 2) и т.д.
Но сложность заключается в том, что если ячейку просто протянуть вниз, то Лист1 не будет меняться на Лист2 и так далее.
Если мы пропишем формулу
ДВCСЫЛ(«Лист1!B2»), ДВCСЫЛ(«Лист2!B2») и так далее, то функция будет возвращать нам то же самое, но это так же не решает нашу проблему, ведь «Лист1!B2» прописан обычным текстом и так же не будет изменяться при протягивании.
Для решения нашей задачи мы разделим текст «Лист1!B2» на две части (отдельно «Лист1» и отдельно «!B2» — обратите внимание на восклицательный знак во второй части текста) и потом их склеим. Текст Лист1, Лист2 и так далее мы пропишем напротив соответствующих магазинов, при этом написав Лист1, мы сможем просто протянуть ячейку вниз и автоматически получить список Лист1, Лист2 и так далее.
Теперь сцепим этот текст внутри функции ДВССЫЛ с помощью знака & и получим формулу, которая будет автоматически протягиваться и при этом будут подтягиваться данные из одной и той же ячейки, но разных листов.
Итоговая формула выглядит именно так, но я специально расписал с самого начала по шагам, чтобы было понятно из чего состоит данная формула. В принципе, этого уже достаточно, чтобы за короткий срок решить задачу, указанную в начале статьи. Чтобы заполнить данные за февраль, нам необходимо в ячейке D2 написать такую же формулу, но поменять текст «!B2» на «!B3»
=ДВССЫЛ(A2&»!B3″) и протянуть вниз, аналогично за март.
Но никто нам не мешает немного усложнить формулу и заполнить ее автоматически, протянув формулу вниз, а затем вправо. Тут я не буду подробно описывать, но если вы разобрались с предыдущим примером, то легко поймете и данный.
Итак, мы решили задачу, как быстро заполнить данные за определенный месяц, но нам необходимо заполнить данные за остальные месяца. Мы знаем, что данные за Январь соответствуют ячейке B2, февраль — B3, Март — B4. То есть буква «B» остается неизменной, меняется лишь цифра. Зная это мы просто разобьем текст внутри ссылки ДВССЫЛ не на 2 части, а на три. Добавим дополнительно строку над таблицей и напишем сверху цифры соответствующие ячейки месяца. Пропишем формулу.
=ДВССЫЛ($A3&»!B»&C$1)
которую можно протянуть вправо и вниз.
Если вам понравилась статья, пожалуйста, нажмите +1 и «Мне нравится». Так же подписывайтесь на нашу рассылку или вступайте в нашу группу ВКонтакте, чтобы не пропустить наши следующие уроки по Excel
SirExcel — безграничные возможности Excel
Функция INDIRECT (ДВССЫЛ) в Excel используется когда у вас есть ссылки в виде текста, и вы хотите получить значения из этих ссылок.
Что возвращает функция
Функция возвращает ссылку, заданную текстовой строкой.
=INDIRECT(ref_text, ) – английская версия
=ДВССЫЛ(ссылка_на_текст;) – русская версия
Примеры использования функции ДВССЫЛ в Excel Пример 1. Используем ссылку на ячейку для получения значения
Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):
Формула в ячейке С1:
=INDIRECT(“A1”) – английская версия
=ДВССЫЛ(“A1”) – русская версия
Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.
Вы можете спросить – почему бы нам просто не использовать “=A1” вместо использования функции INDIRECT (ДВССЫЛ)?
Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.
Функция очень полезна, если вы хотите заблокировать ссылку на ячейку таким образом, чтобы она не изменялась при вставке строк / столбцов в рабочий лист.
Пример 2. Получаем данные по ссылке на ячейку
Также, с помощью функции вы можете извлечь значения из ячейки, ссылка на которую хранится в самой ячейке.
На примере выше, ячейка “А1” содержит в себе число “123”.
Ячейка “С1” ссылается на ячейку “А1”.
Теперь, используя с помощью функции вы можете указать ячейку С1 как аргумент функции, который выведет по итогу значение ячейки А1.
Важно. Вам не нужно использовать кавычки в ячейке С1, значение ссылки на ячейку должно быть указано в текстовом формате.
Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)
Вы можете создать ссылку на ячейку используя комбинацию текстовых и числовых значений.
Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(“A”&C1) вы получите ссылку на значение ячейки “А2”.
Практическое применение этого способа актуально, когда вы хотите динамически ссылаться на ячейки на основе значения в какой-либо другой ячейке.
Пример 4. Ссылаемся на диапазон ячеек с помощью функции INDIRECT (ДВССЫЛ)
С помощью функции вы можете ссылаться на диапазон ячеек.
Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(“A1:A5”) будет ссылаться на данные из диапазона ячеек “A1:A5”.
Используя функцию SUM (СУММ) и INDIRECT (ДВССЫЛ) вместе, вы можете рассчитать сумму, а также максимальные и минимальные значения диапазона.
Пример 5. Ссылаемся на именованный диапазон значений с использованием функции INDIRECT (ДВССЫЛ)
Если вы создали именованный диапазон в Excel, вы можете обратиться к нему с помощью функции INDIRECT (ДВССЫЛ).
Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:
Зададим для следующих ячеек названия:
Для того чтобы задать название любому диапазону значений, достаточно выбрать ячейки и присвоить им название в соответствующем боксе:
После этого вы можете обратиться к указанным диапазонам с помощью формулы:
=INDIRECT(“Именованный диапазон”) – английская версия
=ДВССЫЛ(“Именованный диапазон”) – русская версия
Например, если вы хотите узнать средний балл среди студентов по математике – используйте следующую формулу:
=AVERAGE(INDIRECT(“Математика”)) – английская версия
=СРЗНАЧ(ДВССЫЛ(“Математика”)) – русская версия
Если имя диапазона указано в ячейке (“F2” в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.
Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)
C помощью этой функции вы можете создавать зависимый выпадающий список.
Например, предположим, что у вас есть две колонки с названиями “Россия” и “США”, в строках указаны города этих стран, как указано на примере ниже:
Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”.
Теперь, в ячейке “D2” создайте выпадающий список для “России” и “США”. Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.
Теперь, для создания зависимого выпадающего списка:
Теперь, если вы выберите в первом выпадающем списке, например, страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну “США” из первого выпадающего списка.
На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:
Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.
«Ну ОК», — скажете вы. «И что тут полезного?».
Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.
Пример 1. Транспонирование
Классика жанра: нужно превратить вертикальный диа
пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:
Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN).
Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, C2=R1C3, D2=R1C4 и т.д.
Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE), то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:
Пример 2. Суммирование по интервалу
Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET). Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM):
Пример 3. Выпадающий список по умной таблице
Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:
Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:
Пример 4. Несбиваемые ссылки
Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:
Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.
Пример 5. Сбор данных с нескольких листов
Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):
Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.
Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:
Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.
Подводные камни
При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:
Ссылки по теме
Одной из встроенных функций программы Excel является ДВССЫЛ. Её задача состоит в том, чтобы возвращать в элемент листа, где она расположена, содержимое ячейки, на которую указана в ней в виде аргумента ссылка в текстовом формате.
Казалось бы, что ничего особенного в этом нет, так как отобразить содержимое одной ячейки в другой можно и более простыми способами. Но, как оказывается, с использованием данного оператора связаны некоторые нюансы, которые делают его уникальным. В некоторых случаях данная формула способна решать такие задачи, с которыми другими способами просто не справиться или это будет гораздо сложнее сделать. Давайте узнаем подробнее, что собой представляет оператор ДВССЫЛ и как его можно использовать на практике.
Применение формулы ДВССЫЛ
Само наименование данного оператора ДВССЫЛ расшифровывается, как «Двойная ссылка». Собственно, это и указывает на его предназначение – выводить данные посредством указанной ссылки из одной ячейки в другую. Причем, в отличие от большинства других функций, работающих со ссылками, она должна быть указана в текстовом формате, то есть, выделена с обеих сторон кавычками.
Данный оператор относится к категории функций «Ссылки и массивы» и имеет следующий синтаксис:
Таким образом, формула имеет всего два аргумента.
Аргумент «Ссылка на ячейку» представлен в виде ссылки на элемент листа, данные содержащиеся в котором нужно отобразить. При этом указанная ссылка должна иметь текстовый вид, то есть, быть «обернута» кавычками.
Аргумент «A1» не является обязательным и в подавляющем большинстве случаев его вообще не нужно указывать. Он может иметь два значения «ИСТИНА» и «ЛОЖЬ». В первом случае оператор определяет ссылки в стиле «A1», а именно такой стиль включен в Excel по умолчанию. Если значение аргумента не указывать вовсе, то оно будет считаться именно как «ИСТИНА». Во втором случае ссылки определяются в стиле «R1C1». Данный стиль ссылок нужно специально включать в настройках Эксель.
Если говорить просто, то ДВССЫЛ является своеобразным эквивалентом ссылки одной ячейки на другую после знака «равно». Например, в большинстве случаев выражение
будет эквивалентно выражению
Но в отличие от выражения «=A1» оператор ДВССЫЛ привязывается не к конкретной ячейке, а к координатам элемента на листе.
Рассмотрим, что это означает на простейшем примере. В ячейках B8 и B9 соответственно размещена записанная через «=» формула и функция ДВССЫЛ. Обе формулы ссылаются на элемент B4 и выводят его содержимое на лист. Естественно это содержимое одинаковое.
Добавляем в таблицу ещё один пустой элемент. Как видим, строки сдвинулись. В формуле с применением «равно» значение осталось прежним, так как она ссылается на конечную ячейку, пусть даже её координаты и изменились, а вот данные выводимые оператором ДВССЫЛ поменялись. Это связано с тем, что он ссылается не на элемент листа, а на координаты. После добавления строки адрес B4 содержит другой элемент листа. Его содержимое теперь формула и выводит на лист.
Данный оператор способен выводить в другую ячейку не только числа, но и текст, результат вычисления формул и любые другие значения, которые расположены в выбранном элементе листа. Но на практике данная функция редко когда применяется самостоятельно, а гораздо чаще бывает составной частью сложных формул.
Нужно отметить, что оператор применим для ссылок на другие листы и даже на содержимое других книг Excel, но в этом случае они должны быть запущены.
Теперь давайте рассмотрим конкретные примеры применения оператора.
Пример 1: одиночное применение оператора
Для начала рассмотрим простейший пример, в котором функция ДВССЫЛ выступает самостоятельно, чтобы вы могли понять суть её работы.
Имеем произвольную таблицу. Стоит задача отобразить данные первой ячейки первого столбца в первый элемент отдельной колонки при помощи изучаемой формулы.
В поле «A1», так как мы работает в обычном типе координат, можно поставить значение «ИСТИНА», а можно оставить его вообще пустым, что мы и сделаем. Это будут равнозначные действия.
После этого щелкаем по кнопке «OK».
Урок: Мастер функций в программе Excel
Пример 2: использование оператора в комплексной формуле
А теперь давайте посмотрим на пример гораздо более частого применения оператора ДВССЫЛ, когда он является составной частью комплексной формулы.
Имеем помесячную таблицу доходов предприятия. Нам нужно подсчитать сумму дохода за определенный период времени, например март – май или июнь – ноябрь. Конечно, для этого можно воспользоваться формулой простого суммирования, но в этом случае при необходимости подсчета общего результата за каждый период нам все время придется менять эту формулу. А вот при использовании функции ДВССЫЛ можно будет производить изменение суммированного диапазона, просто в отдельных ячейках указав соответствующий месяц. Попробуем использовать данный вариант на практике сначала для вычисления суммы за период с марта по май. При этом будет использована формула с комбинацией операторов СУММ и ДВССЫЛ.
Итак, чтобы присвоить имя первому элементу столбца, выделяем его и жмем правую кнопку мыши. Открывается контекстное меню. Выбираем в нем пункт «Присвоить имя…».
В целом количество аргументов может достигать значения 255. Но все эти аргументы являются однородными. Они представляют собой число или координаты ячейки, в которой это число содержится. Также они могут выступать в виде встроенной формулы, которая рассчитывает нужное число или указывает на адрес элемента листа, где оно размещается. Именно в этом качестве встроенной функции и будет использоваться нами оператор ДВССЫЛ в данном случае.
Устанавливаем курсор в поле «Число1». Затем жмем на пиктограмму в виде перевернутого треугольника справа от поля наименования диапазонов. Раскрывается список последних используемых функций. Если среди них присутствует наименование «ДВССЫЛ», то сразу кликаем по нему для перехода в окно аргументов данной функции. Но вполне может быть, что в этом списке вы его не обнаружите. В таком случае нужно щелкнуть по наименованию «Другие функции…» в самом низу списка.
После того, как адрес отобразился в поле, не спешим жать на кнопку «OK», так как это вложенная функция, и действия с ней отличаются от обычного алгоритма. Щелкаем по наименованию «СУММ» в строке формул.
Урок: Как посчитать сумму в Экселе
Как видим, несмотря на то, что функцию ДВССЫЛ нельзя назвать одной из наиболее популярных у пользователей, тем не менее, она помогает решить задачи различной сложности в Excel гораздо проще, чем это можно было бы сделать при помощи других инструментов. Более всего данный оператор полезен в составе сложных формул, в которых он является составной частью выражения. Но все-таки нужно отметить, что все возможности оператора ДВССЫЛ довольно тяжелы для понимания. Это как раз и объясняет малую популярность данной полезной функции у пользователей.
Мы рады, что смогли помочь Вам в решении проблемы.
Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.