Функция двссыл в excel примеры

Microsoft Excel

трюки • приёмы • решения

Как в таблицах Excel использовать функцию ДВССЫЛ

Чтобы сделать формулу более гибкой, для создания ссылки на диапазон вы можете воспользоваться функцией ДВССЫЛ. Эта редко используемая функция принимает текстовый аргумент, который напоминает ссылку на диапазон, а затем преобразует его в фактическую ссылку на диапазон. Когда вы поймете, как работает эта функция, вы сможете применять ее для создания более мощных интерактивных электронных таблиц.

Косвенное указание строк

На рис. 124.1 показан пример использования функции ДВССЫЛ. Формула Е5 будет следующей:
=СУММ(ДВССЫЛ(«B»&E2&»:B»&E3))

Рис. 124.1. Для суммирования предоставленных пользователем строк применяется функция

Обратите внимание, что в аргументе функции ДВССЫЛ указан оператор конкатенации. Он позволяет создать ссылку на диапазон, используя значения в ячейках Е2 и Е3. Таким образом, если Е2 содержит 2, а Е3 содержит 4, ссылка на диапазон получается идентичной этой строке: «В2:В4» .

Функция ДВССЫЛ преобразует строку в фактическую ссылку на диапазон, которая затем передается функции СУММ. В сущности, формула возвращает: =СУММ(В2:В4) . При изменении значений в Е2 и Е3 формула обновляется и отображает суммы указанных строк.

Косвенное указание названий листов

На рис. 124.2 продемонстрирован другой пример — на этот раз применятся ссылка на лист. Столбец А листа Итого содержит текст, который соответствует другим листам книги. Столбец В содержит формулы, которые ссылаются на эти пункты текста. Например, формула в ячейке В2 следующая:
=СУММ(ДВССЫЛ(A2&»!F1:F10″)) .

Рис. 124.2. Столбец А листа Итого содержит текст, который соответствует другим листам книги

Эта формула объединяет текст в А2 со ссылкой на диапазон. Функция ДВССЫЛ вычисляет результат и преобразует его в фактическую ссылку на диапазон. Результат эквивалентен следующей формуле:
=СУММ(Север!F1:F10) .

Формула копируется вниз по столбцу. Каждая формула возвращает сумму диапазона F1:F10 соответствующего листа.

Создание неизменной ссылки на ячейку

Другое применение функции ДВССЫЛ состоит в том, чтобы создать такую ссылку на ячейку, которая никогда не изменяется. Для примера рассмотрим формулу, которая суммирует значения в первых 12 строках столбца А: =СУММ(А1:А12) .

Если вставить новую строку 1, Excel изменит формулу на такую: =СУММ(А2:А13) . Другими словами, формула подстраивается так, чтобы по-прежнему ссылаться на исходные данные (и она уже не суммирует первые 12 строк столбца А).

Чтобы предотвратить Excel от изменения ссылок на ячейки, используйте функцию ДВССЫЛ: =СУММ(ДВССЫЛ(«А1:А12»)) . Эта формула всегда возвращает сумму первых 12 строк в столбце А.

Функция ДВССЫЛ в 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)), где с помощью функции ПОИСКПОЗ находим относительное положение оценки кандидата в критерии оценок, а функцией ДВССЫЛ подтягиваем полученную оценку для каждого кандидата.

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

Разбор функции ДВССЫЛ (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 с примерами использования

Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.

Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.

Синтаксис функции с описанием

Функция ДВССЫЛ в Excel: примеры

Начнем с хрестоматийного примера, чтобы понять принцип работы функции.

Имеется таблица с данными:

Примеры функции ДВССЫЛ:

Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).

Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.

Читать еще:  Round функция excel

Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.

Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.

Чтобы получить только нечетные записи, используем формулу:

Для выведения четных строк:

Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.

Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.

Чтобы функция сработала, все отчеты поместим на один лист.

Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.

Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.

Если мы используем только функцию ВПР, появится ошибка:

Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.

Исправить положение помогла функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой.

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

Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.

Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:

В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:

Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:

  • $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
  • $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
  • _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.

Таким образом, эти две функции выполняют подобного рода задачи на отлично.

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.

Читать еще:  Excel впр по нескольким критериям

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.

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector