Progress28.ru

IT Новости
7 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Двссыл в 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.
Читать еще:  Скачать документ в формате excel

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

Пример 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, зависящий от количества чисел в этом столбце.

Читать еще:  Анализ временных рядов в excel

Сумма для обоих диапазонов может быть вычислена, просто задав его имя в качестве аргумента для функции 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.

Читать еще:  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
×
×