Repeater-zone.ru

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

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

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

Функция ДВССЫЛ в программе Microsoft Excel

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

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

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

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

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

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».

    =АДРЕС(1;1) – возвращает $A$1.
    =АДРЕС(1;1;4) – возвращает A1.
    =АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
    =АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].

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

    Пример 1. Используем ссылку на ячейку для получения значения

    Функция ДВССЫЛ получает ссылку на ячейку как исходные данные и возвращает значение ячейки по этой ссылке (как показано в примере ниже):

    INDIRECT (ДВССЫЛ) в Excel - 1

    Формула в ячейке С1:

    =INDIRECT(“A1”) – английская версия

    =ДВССЫЛ(“A1”) – русская версия

    Функция получает ссылку на ячейку (в двойных кавычках) и возвращает значение этой ячейки, которая равна “123”.

    Вы можете спросить – почему бы нам просто не использовать “=A1” вместо использования функции INDIRECT (ДВССЫЛ) ?

    Если в данном случае вы введете в ячейку С1 формулу “=A1” или “=$A$1”, то она выдаст вам тот же результат, что находится в ячейке А1. Но если вы вставите в таблице строку выше, вы можете заметить, что ссылка на ячейку будет автоматически изменена.

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

    Пример 2. Получаем данные по ссылке на ячейку

    Также, с помощью функции вы можете извлечь значения из ячейки, ссылка на которую хранится в самой ячейке.

    INDIRECT (ДВССЫЛ) в Excel - 2

    На примере выше, ячейка “А1” содержит в себе число “123”.

    Ячейка “С1” ссылается на ячейку “А1”.

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

    Важно. Вам не нужно использовать кавычки в ячейке С1, значение ссылки на ячейку должно быть указано в текстовом формате.

    Пример 3. Используем комбинацию текстового и числового значений в функции INDIRECT (ДВССЫЛ)

    Вы можете создать ссылку на ячейку используя комбинацию текстовых и числовых значений.

    Например, если в ячейке С1 указано число “2”, то используя формулу =INDIRECT(“A”&C1) или =ДВССЫЛ(“A”&C1) вы получите ссылку на значение ячейки “А2”.

    Функция INDIRECT (ДВССЫЛ) в Excel - 3

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

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

    С помощью функции вы можете ссылаться на диапазон ячеек.

    Например, =INDIRECT(“A1:A5”) или =ДВССЫЛ(“A1:A5”) будет ссылаться на данные из диапазона ячеек “A1:A5”.

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

    Функция INDIRECT (ДВССЫЛ) в Excel - 4

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

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

    Например, представим что у вас есть оценки по 5 студентам по трем предметам как показано ниже:

    Функция INDIRECT (ДВССЫЛ) в Excel - 5

    Зададим для следующих ячеек названия:

    • B2:B6: Математика
    • C2:C6: Физика
    • D2:D6: Химия

    Для того чтобы задать название любому диапазону значений, достаточно выбрать ячейки и присвоить им название в соответствующем боксе:

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

    После этого вы можете обратиться к указанным диапазонам с помощью формулы:

    =INDIRECT(“Именованный диапазон”) – английская версия

    =ДВССЫЛ(“Именованный диапазон”) – русская версия

    Например, если вы хотите узнать средний балл среди студентов по математике – используйте следующую формулу:

    =AVERAGE(INDIRECT(“Математика”)) – английская версия

    =СРЗНАЧ(ДВССЫЛ(“Математика”)) – русская версия

    Если имя диапазона указано в ячейке (“F2” в приведенном ниже примере указан как “Матем”), вы можете использовать ссылку на ячейку прямо в формуле. В следующем примере показано, как вычислять среднее значение с использованием именных диапазонов.

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

    Пример 6. Создаем зависимый выпадающий список с помощью INDIRECT (ДВССЫЛ)

    C помощью этой функции вы можете создавать зависимый выпадающий список.

    Например, предположим, что у вас есть две колонки с названиями “Россия” и “США”, в строках указаны города этих стран, как указано на примере ниже:

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

    Для того, чтобы создать зависимый выпадающий список вам нужно создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”.

    Теперь, в ячейке “D2” создайте выпадающий список для “России” и “США”. Так мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

    функция INDIRECT (ДВССЫЛ) в Excel

    Теперь, для создания зависимого выпадающего списка:

    Функция INDIRECT (ДВССЫЛ) в Excel. Как использовать?

    • Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
    • Кликните по вкладке “Data” -> “Data Validation”;
    • На вкладке “Настройки” в разделе “Allow” выберите List;
    • В разделе “Source” укажите ссылку: =INDIRECT($D$2) или =ДВССЫЛ($D$2) ;
    • Нажмите ОК

    Теперь, если вы выберите в первом выпадающем списке, например, страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Такая же ситуация, если вы выберите страну “США” из первого выпадающего списка.

    функция INDIRECT (ДВССЫЛ) в Excel

    Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

    Трюк №25. Как в Excel при проверке данных заставить Excel использовать список на другом рабочем листе

    Один из параметров, доступных при проверке данных, это параметр Список (List), то есть удобный раскрывающийся список, из которого пользователи могут выбрать определенные элементы. Но есть один недостаток — если вы попытаетесь сослаться на список, находящийся на другом рабочем листе, то получите сообщение, что это невозможно. К счастью, при помощи очередного трюка невозможное можно сделать возможным.

    Заставить Excel при проверке данных ссылаться на список на другом рабочем листе можно двумя способами — при помощи именованных диапазонов и функции ДВССЫЛ (INDIRECT).

    Способ 1. Именованные диапазоны

    Вероятно, самый простой и быстрый способ выполнить эту задачу — присвоить имя диапазону, где размещается список. Для этого упражнения мы предполагаем, что диапазону присвоено имя MyRange. Выделите ячейку, в которой должен будет появиться этот раскрывающийся список, и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =MyRange. Щелкните на кнопке ОК. Теперь список (который находится на другом рабочем листе) можно использовать как список проверки.

    Способ 2. Функция ДВССЫЛ (INDIRECT)

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

    Предположим, список находится на листе Sheet1 в диапазоне $А$1:$А$10 . Щелкните любую ячейку на другом рабочем листе, где должен будет появиться этот список проверки. Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующую функцию: =INDIRECT(«Sheet1!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet1!$А$1:$А$10») . Убедитесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl окажется в вашем раскрывающемся списке проверки.

    Если имя рабочего листа, на котором расположен список, содержит пробелы, функцию ДВССЫЛ (INDIRECT) нужно записать так: =INDIRECT(«‘Sheet 1’!$А$1:$А$10») , в русской версии Excel: =ДВССЫЛ(«Sheet 1!$А$1:$А$10») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком. Апострофы ограничивают название листа.

    Преимущества и недостатки обоих методов

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

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

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

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

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

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

    Выборка значений.

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

    ВПР и ЕСЛИ.

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

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

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

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

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

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

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

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

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

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

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

    голоса
    Рейтинг статьи
    Читать еще:  Как восстановить фото в Android (Андроид)
Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector