Двссыл в excel примеры

Разбор функции ДВССЫЛ (INDIRECT) на примерах

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:

Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.

«Ну ОК», — скажете вы. «И что тут полезного?».

Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.

Пример 1. Транспонирование

пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:

Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN) .

Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C 2 , C2=R1C 3 , D2=R1C 4 и т.д.

Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE) , то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:

Пример 2. Суммирование по интервалу

Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET) . Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM) :

Пример 3. Выпадающий список по умной таблице

Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:

Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:

Пример 4. Несбиваемые ссылки

Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:

Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.

Пример 5. Сбор данных с нескольких листов

Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):

Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.

Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:

Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.

Подводные камни

При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:

• Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
• С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический — без проблем.
• ДВССЫЛ является волатильной (volatile) или «летучей» функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

Функция ДВССЫЛ в Excel

Рассмотрим примеры использования функции ДВССЫЛ в Excel, которая позволяет преобразовывать текст из ячейки в ссылку.

Функция ДВССЫЛ чрезвычайно полезна тем, что при использовании данной функции есть возможность изменять ссылки на ячейки и диапазоны в формуле не изменяя при этом саму формулу.
Другими словами, введенная формула =B2 идентична формуле =ДВССЫЛ(«B2»), однако в первом варианте мы оперируем ссылкой, а во втором — текстом, который можно изменять.

Описание функции ДВССЫЛ

ДВССЫЛ(ссылка_на_ячейку; [a1])
Возвращает ссылку, заданную текстовой строкой.

• Ссылка на ячейку(обязательный аргумент) — ссылка в виде текста вида A1 или R1C1;
• A1(необязательный аргумент) — вид ссылки, в случае когда аргумент принимает значение ИСТИНА (или опущен), то ссылка трактуется как вид A1, когда принимает значение ЛОЖЬ, то как вид R1C1.

Примеры использования функции ДВССЫЛ

Пример 1. Ссылка на ячейку

Начнем с простой задачи, который мы уже частично разобрали.
Введем произвольное значение в ячейку A1, теперь чтобы сделать ссылку на ячейку введем формулу =ДВССЫЛ(«A1»), например, в ячейку A2:

Пример 2. Ссылка на другой лист

Немного усложним задачу, и применим формулу ДВССЫЛ для ссылки на другой лист.
Перейдем на любой другой лист книги и вводим формулу =ДВССЫЛ(«Пример_1!A1»), где лист Пример_1 — лист из первого примера:

Пример 3. Функции

Рассмотрим примеры с одновременным применением функции ДВССЫЛ и других функций.

Функция СУММ

Предположим, что у нас имеется таблицами с помесячными данными о продажах фруктов за 12 месяцев, и мы хотим просуммировать продажи накопленным итогом за переменное количество месяцев:

Функция СУММ с прямой ссылкой на диапазон решает эту задачу, например, можно применить формулу =СУММ(B2:B5) для подсчета продаж апельсинов.
Однако тогда при изменении периода нам придется менять и диапазон в исходной формуле.
Обойдем эту проблему записав диапазон в текстовом виде с использованием ссылок на другие ячейки — запишем формулу =СУММ(ДВССЫЛ(B15&2&»:»&B15&(1+$A16))), где ячейка A16 отвечает за номер периода:

Расписывая по шагам данную формулу, мы в конце получим формулу =СУММ(B2:B5), что нам и требовалось.

Функция ПОИСКПОЗ

Рассмотрим еще один пример. Предположим, что у нас есть результаты тестирования группы кандидатов и критерии их оценки:

Записываем в оценку кандидатов формулу =ДВССЫЛ(«G»&ПОИСКПОЗ(B2;$F$1:$F$6;1)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

Обратите внимание, что функция ДВССЫЛ не работает, если ссылка указывает на ячейку или диапазон в закрытой книге.
Подробно ознакомиться со всеми разобранными примерами — скачать пример.

30 функций Excel за 30 дней: ДВССЫЛ (INDIRECT)

Поздравляю! Вы добрались до финального дня марафона 30 функций Excel за 30 дней. Это было длинное интересное путешествие, в течение которого Вы узнали много полезных вещей о функциях Excel.

30-й день марафона мы посвятим изучению функции INDIRECT (ДВССЫЛ), которая возвращает ссылку, заданную текстовой строкой. С помощью этой функции можно создавать зависимые выпадающие списки. Например, когда выбор страны из выпадающего списка определяет, какие варианты появятся в выпадающем списке городов.

Итак, давайте внимательно посмотрим теоретическую часть по функции INDIRECT (ДВССЫЛ) и изучим практические примеры её применения. Если у Вас есть дополнительная информация или примеры, пожалуйста, делитесь ими в комментариях.

Функция 30: INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовой строкой.

Как можно использовать функцию INDIRECT (ДВССЫЛ)?

Поскольку функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовой строкой, то с её помощью Вы сможете:

• Создать не сдвигающуюся начальную ссылку.
• Создать ссылку на статичный именованный диапазон.
• Создать ссылку, используя информацию о листе, строке и столбце.
• Создать не сдвигающийся массив чисел.

Синтаксис INDIRECT (ДВССЫЛ)

Функция INDIRECT (ДВССЫЛ) имеет вот такой синтаксис:

INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)

• ref_text (ссылка_на_ячейку) – это текст ссылки.
• a1 – если равен TRUE (ИСТИНА) или не указан, то будет использован стиль ссылки A1; а если FALSE (ЛОЖЬ), то стиль R1C1.

Ловушки INDIRECT (ДВССЫЛ)

• Функция INDIRECT (ДВССЫЛ) пересчитывается при любом изменении значений на листе Excel. Это может сильно замедлить работу Вашей книги, если функция используется во многих формулах.
• Если функция INDIRECT (ДВССЫЛ) создаёт ссылку на другую книгу Excel, то эта книга должна быть открыта, иначе формула сообщит об ошибке #REF! (#ССЫЛКА!).
• Если функция INDIRECT (ДВССЫЛ) создаёт ссылку на диапазон, превышающий предельное число строк и столбцов, то формула сообщит об ошибке #REF! (#ССЫЛКА!).
• Функция INDIRECT (ДВССЫЛ) не может создать ссылку на динамический именованный диапазон.

Пример 1: Создаем не сдвигающуюся начальную ссылку

В первом примере в столбцах C и E находятся одинаковые числа, их суммы, посчитанные при помощи функции SUM (СУММ), тоже одинаковы. Тем не менее, формулы немного отличаются. В ячейке C8 формула вот такая:

В ячейке E8 функция INDIRECT (ДВССЫЛ) создаёт ссылку на начальную ячейку E2:

Если вверху листа вставить строку и добавить значение для января (Jan), то сумма в столбце C не изменится. Изменится формула, отреагировав на прибавление строки:

Однако, функция INDIRECT (ДВССЫЛ) фиксирует E2 как начальную ячейку, поэтому значение января автоматически включается в подсчёт суммы по столбцу E. Конечная ячейка изменилась, но на начальную это не повлияло.

Пример 2: Создаем ссылку на статичный именованный диапазон

Функция INDIRECT (ДВССЫЛ) может создать ссылку на именованный диапазон. В этом примере голубые ячейки составляют диапазон NumList. Кроме этого, из значений в столбце B создан еще и динамический диапазон NumListDyn, зависящий от количества чисел в этом столбце.

Сумма для обоих диапазонов может быть вычислена, просто задав его имя в качестве аргумента для функции SUM (СУММ), как это можно увидеть в ячейках E3 и E4.

=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)

Вместо того, чтобы вводить с клавиатуры имя диапазона в функцию SUM (СУММ), Вы можете сослаться на имя, записанное в одной из ячеек листа. Например, если имя NumList записано в ячейке D7, то формула в ячейке E7 будет вот такая:

К сожалению, функция INDIRECT (ДВССЫЛ) не может создать ссылку на динамический диапазон, поэтому, когда Вы скопируете эту формулу вниз в ячейку E8, то получите сообщение об ошибке #REF! (#ССЫЛКА!).

Пример 3: Создаём ссылку используя информацию о листе, строке и столбце

Вы легко можете создать ссылку, опираясь на номера строк и столбцов, а также используя значение FALSE (ЛОЖЬ) для второго аргумента функции INDIRECT (ДВССЫЛ). Так создается ссылка стиля R1C1. В этом примере мы дополнительно добавили к ссылке имя листа – ‘MyLinks’!R2C2

Пример 4: Создаём не сдвигающийся массив чисел

Иногда в формулах Excel необходимо использовать массив чисел. В следующем примере мы хотим получить среднее из 3-х наибольших чисел в столбце B. Числа могут быть вписаны в формулу, как это сделано в ячейке D4:

Если Вам потребуется массив побольше, то Вы вряд ли захотите вписывать в формулу все числа. Второй вариант – это использовать функцию ROW (СТРОКА), как это сделано в формуле массива, введенной в ячейку D5:

Третий вариант – это использовать функцию ROW (СТРОКА) вместе с INDIRECT (ДВССЫЛ), как это сделано с помощью формулы массива в ячейке D6:

Результат для всех 3-х формул будет одинаковым:

Однако, если сверху листа вставить строки, вторая формула возвратит не верный результат из-за того, что ссылки в формуле изменятся вместе со сдвигом строк. Теперь, вместо среднего значения трёх максимальных чисел, формула возвращает среднее 3-го, 4-го и 5-го по величине чисел.

При помощи функции INDIRECT (ДВССЫЛ), третья формула сохраняет корректные ссылки на строки и продолжает показывать верный результат.

Excel. Примеры использования функции ДВССЫЛ (INDIRECT)

Функция ДВССЫЛ (INDIRECT) — одна из наиболее трудных в освоении функций Excel. Однако умение использовать ее позволит вам решать многие из задач, кажущихся вам сейчас неразрешимыми. По сути, если в формуле есть раздел ДВССЫЛ со ссылкой на ячейку, эта ссылка обрабатывается как содержимое соответствующей ячейки. [1] Например (рис. 1), в ячейке С4 я ввел формулу =ДВССЫЛ(А4), и Excel возвратил значение, равное 6. Excel возвращает именно это значение, поскольку ссылка на А4 немедленно заменяется текстовой строкой В4. Следовательно, формула обрабатывается как =В4, что дает нам 6. По аналогии, если ввести в ячейке С5 формулу =ДВССЫЛ(А5), Excel вернет значение ячейки В5, то есть 9.

Рис. 1. Простой пример функции ДВССЫЛ

Скачать заметку в формате Word или pdf, также доступны примеры и Задание_3 в формате Excel2013

1. Формулы моей электронной таблицы часто содержат ссылки на ячейки, диапазоны или на то и на другое вместе. Можно ли не изменять ссылки в формулах, а поместить их в отдельные ячейки, чтобы редактировать ссылки на ячейки и диапазоны, не затрагивая при этом формулы?

В диапазоне ячеек В4:Н16 (рис. 2) приведены данные о ежемесячных продажах шести товаров за 12 месяцев. Сейчас я подсчитаю общие продажи каждого товара за месяцы со 2 по 12. Простейший способ подсчитать это — скопировать формулу СУММ(С6:С16) из ячейки С18 в диапазон D18:H18. Предположим, вам потребовалось изменить месяцы, по которым производится подсчет. Скажем, вы решите подсчитать общие продажи за месяцы 3–12. Можно изменить формулу в ячейке С18 на СУММ(С7:С16) и затем скопировать ее в диапазон D18:H18. Однако может быть не всегда удобно, поскольку вам приходится копировать формулу из ячейки С18 в диапазон D18:H18, и, не просматривая формул, никто не узнает, какие строки суммируются.

Рис. 2. Функция ДВССЫЛ позволяет изменять ссылки на ячейки в формулах, не изменяя сами формулы

Функция ДВССЫЛ предлагает другое решение. Я указал в ячейках D2 и Е2 номера начальной и конечной суммируемых строк. Теперь, при использовании функции ДВССЫЛ, мне достаточно изменить значения в ячейках D2 и Е2, чтобы конечная сумма обновилась, включив только те строки, которые мы хотим. Кроме того, значения ячеек D2 и Е2 наглядно показывают, какие строки (месяцы) суммируются! Все, что мне требуется, — скопировать из ячейки С18 в диапазон D18:H18 формулу =СУММ(ДВССЫЛ(C$3&$D$2&»:»&C$3&$E$2)). Каждая ссылка на ячейку в этой формуле обрабатывается как содержимое соответствующей ячейки. С$3 обрабатывается как С, $D$2 — как 6, а $Е$2 — как 16. Используя символ конкатенации — & (сцепления), Excel обрабатывает эту формулу как СУММ(С6:С16), что нам и требуется. Формула в ячейке D18 обрабатывается как СУММ(D6:D16), что также дает нужный нам результат. Конечно, если нам захочется просуммировать продажи, скажем, с 4 по 6 месяц, мы просто введем 8 в ячейку D2 и 10 в ячейку Е2. После этого формула в ячейке С18 вернет 33 + 82 + 75 = 190.

2. В ячейке В1 книги Excel, начиная с Лист1 и заканчивая Лист7 (рис. 3) содержатся данные о продажах товара за месяц. Есть ли какой-нибудь простой способ написать и скопировать формулу, которая выводила бы данные о продажах этого товара за каждый месяц на одном листе?

Рис. 3. Данные о продажах товара за 1–7 месяц, выведенные с помощью функции ДВССЫЛ

Предположим, Лист1 содержит данные о продажах за первый месяц, Лист2 — за второй и т.д. Пусть в первом месяце продажи равны 1. Например, вы хотите вывести продажи за все месяцы на одном листе. Нудный способ — подсчитать продажи за первый месяц с помощью формулы =Лист1!В1 продажи за второй месяц с помощью формулы =Лист2!В1 и т.д. Если ваши данных охватывают 100 месяцев, такое решение грозит грандиозной головной болью. Гораздо более изящный способ — вывести данные о продажах за первый месяц в ячейке С4 листа Лист1 с помощью формулы =ДВССЫЛ($A$4&B4&»!B1″), Excel обработает $A$4 как «Лист», В4 — как 1, и «!B1» — как строку текста !В1. Формула целиком будет обработана как =Лист1!В1, то есть покажет данные о продажах за первый месяц, содержащиеся в ячейке В1 листа Лист1. Скопировав эту формулу в диапазон С5:С10, вы отобразите содержимое ячейки В1 листов со 2 по 7. Обратите внимание: при копировании формулы из ячейки С4 в ячейку С5 ссылка на В4 заменяется ссылкой на В5, и формула в ячейке С5 возвращает значение ячейки Лист2!В1 и т.д.

3. Предположим, я суммирую значения из диапазона А5:А10 посредством формулы СУММ(А5:А10). Если вставить где-нибудь между 5 и 10 строками пустую строку, формула автоматически изменится на СУММ(А5:А11). Можно ли написать формулу, которая при вставке пустой строки между 5 и 10 строками все равно суммировала бы значения из диапазона А5:А10?

Рис. 4 иллюстрирует несколько способов сложения чисел из диапазона А5:А10. В ячейке А12 я ввел обычную формулу СУММ(А5:А10). Аналогичным образом формула СУММ($А$5:$А$10) в ячейке С9 тоже возвращает значение 33. Тем не менее, если вставить строку между 5 и 10 строками, обе формулы попытаются сложить ячейки диапазона А5:А11.

Функция ДВССЫЛ (INDIRECT) предоставляет, как минимум, два способа сложения значений из диапазона А5:А10. В ячейке D9 я ввел формулу =СУММ(ДВССЫЛ(«A5:A10»)). Excel обрабатывает ДВССЫЛ(«A5:A10») как строку текста «A5:A10», и поэтому, даже если я добавлю строку в электронную таблицу, формула по-прежнему будет суммировать значения из диапазона А5:А10.

Рис. 4. Несколько способов сложения значений ячеек из диапазона А5:А10; под значением суммы написана формула

Еще один вариант сложения значений из диапазона А5:А10 при помощи функции ДВССЫЛ – формула =СУММ(ДВССЫЛ(«A»&C4&»:A»&D4)), которую я ввел в ячейке С5. Excel обрабатывает ссылку на С3 как 5, а ссылку на D3 — как 10, в результате чего формула преобразуется в СУММ(А5:А10). Вставка пустой строки между 5 и 10 строками никак не скажется на формуле, поскольку ссылка на С3 по-прежнему будет обрабатываться как 5, а ссылка на D3 — как 10. На рис. 5 показаны результаты суммирования, выполненные посредством наших четырех формул после того, как ниже 7-й строки была добавлена пустая строка.

Рис. 5. Результаты, возвращенные формулами СУММ после того, как ниже строки 7 была вставлена пустая строка

Обратите внимание: классические формулы СУММ, не включающие оператор ДВССЫЛ, автоматически изменились и суммируют значения из диапазона А5:А11, по-прежнему возвращая значение 33. Две формулы СУММ, включающие оператор ДВССЫЛ, суммируют значения из диапазона А5:А10, в результате при вычислениях теряется число 2 (оно теперь находится в ячейке A11). Формулы СУММ, содержащие оператор ДВССЫЛ, возвращают значение 31.

Контрольные задания (с ответами)

1. Функция АДРЕС возвращает адрес ячейки, сопоставленный со строкой и столбцом. Например, формула АДРЕС(3;4) возвращает $D$3. Что вернет формула =ДВССЫЛ(АДРЕС(3;4))?

2. Лист Задание_2 содержит данные о продажах пяти товаров в четырех регионах. С помощью функции ДВССЫЛ создайте формулы, которые позволят легко суммировать общие продажи любых последовательно пронумерованных товаров, например Товар 1—Товар 3, Товар 2—Товар 5 и т.д.

3. Книга Excel Задание_3 содержит шесть листов. На листе Лист1 записаны данные о продажах товаров за первый месяц. Эти данные всегда указываются в диапазоне Е5:Н5. Используя функцию ДВССЫЛ, составьте таблицу с информацией о продажах каждого товара по месяцам на отдельном листе.

Ответы

1. Формула =ДВССЫЛ(АДРЕС(3;4)) вернет значение ячейки $D$3.

2. Фрагмент формулы ДВССЫЛ(«B»&B8+1&»:»&»E»&B9+1) возвращает диапазон В3:Е5:

3. В ячейке Е9 записана формула =ДВССЫЛ($B$8&$D9&»!»&E$7&5), которая возвращает значение ячейки Лист1!Е5:

Возможно, вас также заинтересует:

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel. Анализ данных и построение бизнес-моделей, глава 22.