Функция indirect (двссыл) в excel. как использовать?

Функции ссылки и поиска (справка)

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

Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.

Возвращает ссылку на отдельную ячейку листа в виде текста.

Возвращает количество областей в ссылке.

Выбирает значение из списка значений.

Возвращает номер столбца, на который указывает ссылка.

Возвращает количество столбцов в ссылке.

Фильтрует диапазон данных в соответствии с определенными критериями

Ф.ТЕКСТ

Возвращает формулу в заданной ссылке в виде текста.

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Возвращает данные, хранящиеся в отчете сводной таблицы.

Выполняет поиск в первой строке массива и возвращает значение указанной ячейки.

Создает ссылку, открывающую документ, который находится на сервере сети, в интрасети или в Интернете.

Использует индекс для выбора значения из ссылки или массива.

Возвращает ссылку, заданную текстовым значением.

Ищет значения в векторе или массиве.

Ищет значения в ссылке или массиве.

Возвращает смещение ссылки относительно заданной ссылки.

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

Возвращает количество строк в ссылке.

Получает данные реального времени из программы, поддерживающей автоматизацию COM.

Сортирует содержимое диапазона или массива.

Сортирует содержимое диапазона или массива на основе значений в соответствующем диапазоне или массиве.

Возвращает транспонированный массив.

Возвращает список уникальных значений в списке или диапазоне.

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

Осуществляет поиск в диапазоне или массиве и возвращает элемент, соответствующий первому найденному совпадению. Если совпадение не существует, КСЛУКУП может вернуть ближайшее (приближенное) соответствие.

Возвращает относительное расположение элемента в массиве или диапазоне ячеек.

Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке)

голоса

Рейтинг статьи

Разбор функции ДВССЫЛ (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) или “летучей” функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается

Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, , ), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

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

Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.

Пример 1. Функции ДВССЫЛ и СТРОКА

Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать ), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13

{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}

Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.

Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:

={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.

Вот пример:

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

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

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}

Согласитесь, что получается достаточно гибкий расчёт.

Пример 2. Функции ДВССЫЛ и АДРЕС

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

Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.

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

=ДВССЫЛ(АДРЕС(1;3))

Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье Как преобразовать строки в столбцы в Excel .

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

• ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
• Excel ДВССЫЛ и СЧЁТЕСЛИ — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.

Функция ДВССЫЛ позволяет преобразовать текст в ссылку Excel

​ чисел.​и​ будут выступать не​«Математические»​и​В поле​ с тем, что​представлен в виде​то она всегда будет​

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

​ ссылке дописать оба​ другие книги) приведет​ можно получить всю​C – сокращенно от​ появляться число сотрудников.​ ошибки:​

​ побольше, то Вы​ столбцу E. Конечная​Функция​«Ноябрь»​ координаты ячейки, а​. Там выбираем наименование​ДВССЫЛ​«A1»​ он ссылается не​ ссылки на элемент​ указывать на ячейку​ доллара сразу (​

• ​ к возникновению ошибки​ информацию об абоненте​ «column» (столбец) –​
• ​Если мы используем только​Значение аргумента «ссылка на​ вряд ли захотите​
• ​ ячейка изменилась, но​INDIRECT​, то и результат​ её содержимое, которое​«СУММ»​

​.​, так как мы​ на элемент листа,​ листа, данные содержащиеся​

Как преобразовать число в месяц и транспонировать в Excel

​ с адресом​$C$5​ #ССЫЛКА!, если требуемая​ на основе выбранного​ указатель столбца;​ функцию ВПР, появится​ ячейку» является недопустимой​

​ вписывать в формулу​

​ на начальную это​(ДВССЫЛ) имеет вот​ изменится соответственно. Будет​ уже имеет текстовый​

​. Щелкаем по кнопке​Прежде всего, в отдельных​ работает в обычном​ а на координаты.​ в котором нужно​C5​) — она превратится​ книга не открыта​ номера записи (id).​X и Y –​ ошибка:​ ссылкой – ошибка​ все числа. Второй​ не повлияло.​ такой синтаксис:​ сложена сумма дохода​ формат (слово​«OK»​

​ элементах на листе​ типе координат, можно​ После добавления строки​ отобразить. При этом​

​вне зависимости от​ в​ в приложении Excel.​Вид исходной таблицы:​ любые целые положительные​

Как преобразовать текст в ссылку Excel?

​ #ССЫЛКА!.​ вариант – это​=SUM(INDIRECT(«E2»):E8)​INDIRECT(ref_text,a1)​ за указанный период​«Март»​.​ вносим наименования месяцев​ поставить значение​

​ адрес​ указанная ссылка должна​ любых дальнейших действий​абсолютную​Если вы работаете в​Создадим форму для новой​ числа, указывающие номер​

​ D2 как ссылку​Обязательный аргумент ссылается на​

​ использовать функцию​=СУММ(ДВССЫЛ(«E2»):E8)​ДВССЫЛ(ссылка_на_ячейку;a1)​ времени.​). Поле​Вслед за выполнением данного​ начала и конца​«ИСТИНА»​B4​ иметь текстовый вид,​ пользователя, вставки или​и не будет​ Excel не второй​ таблицы:​ строки и столбца​ на именованный диапазон,​ другую книгу (является​

​ROW​

Как вставить текст в ссылку на ячейку Excel?

​Функция​ref_text​Урок:​«A1»​ действия запускается окно​ периода, за который​, а можно оставить​содержит другой элемент​ то есть, быть​

​ удаления строк и​

​ меняться никак при​ день, то, наверняка​

​Для заполнения ячеек новой​ соответственно.​ где и находится​ внешней ссылкой), которая​(СТРОКА), как это​

​INDIRECT​(ссылка_на_ячейку) – это​Как посчитать сумму в​

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

Особенности использования функции ДВССЫЛ в Excel

​ листа. Его содержимое​ «обернута» кавычками.​

​ т.д. Единственная небольшая​

​ любом копировании, т.е.​

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

• ​, единственной задачей которого​«Март»​ сделаем. Это будут​ выводит на лист.​«A1»​ том, что если​ и строка и​ и функциях Excel​ абоненту, используем следующую​
• ​ любого из этих​ в ячейке текстом.​ создании подобных формул​ ячейку D5:​ диапазон. В этом​

​– если равен​

1. ​ то, что функцию​ координат.​ является суммирование указанных​и​ равнозначные действия.​Данный оператор способен выводить​не является обязательным​ целевая ячейка пустая,​
2. ​ столбец:​ ссылки со знаком​ формулу массива (CTRL+SHIFT+ENTER):​ двух вариантов представления.​Исправить положение помогла функция​ внешний источник данных​
3. ​=AVERAGE(LARGE(B1:B8,ROW(1:3)))​ примере голубые ячейки​ TRUE (ИСТИНА) или​ДВССЫЛ​После того, как адрес​ значений. Синтаксис этой​«Май»​После этого щелкаем по​ в другую ячейку​ и в подавляющем​ то​Самый простой и быстрый​
4. ​ доллара, например​Примечание: перед выполнение формулы​Пример 1. Преобразовать столбец​ ДВССЫЛ, которая возвращает​ должен быть открыт.​=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))​ составляют диапазон​

exceltable.com>

ТРАНСП

Синтаксис:

ТРАНСП(массив) 

Результат: Транспонированный массив. Функция ТРАНСП должна быть введена как формула массива в интервал, который имеет столько же строк и столбцов, сколько столбцов и строк имеет аргумент массив. Функция ТРАНСП используется для того, чтобы поменять ориентацию массива на рабочем листе или листе макросов с вертикальной на горизонтальную и наоборот. Например, некоторые функции, такие как ДОКУМЕНТЫ, возвращают горизонтальные массивы. Следующая формула возвращает вертикальный массив — результат работы функции ДОКУМЕНТЫ: ТРАНСП(ДОКУМЕНТЫ()).

Аргументы:

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

ПРИМЕЧАНИЕ

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

Трюк №64. Как сделать, чтобы формула ссылалась на следующие строки при копировании по столбцам

Автоматическое приращение ссылок на ячейки в Excel в большинстве случаев работает прекрасно, но иногда нужно изменить принцип работы этого средства.

Если создать ссылку на одну ячейку, например, А1, а затем скопировать ее вправо по столбцам, то в результате эта ссылка будет меняться: =В1, =С1, =D1 и т. д.

, но на самом деле вы хотите получить другой результат. Нужно, чтобы приращение формулы шло по строкам, а не по столбцам, то есть =А1, =А2, =А3 и т. д. К сожалению, в Excel такая возможность не предусмотрена. Но можно обойти это ограничение с помощью функции ДВССЫЛ (INDIRECT), в которую вложена функция АДРЕС (ADDRESS).

Наверное, лучший способ объяснить, как создать нужную функцию, — использовать пример с предсказуемыми результатами. В ячейках А1:А10 по порядку введите числа от 1 до 10.

Выделите ячейку D1 и введите в ней следующую формулу: =INDIRECT(ADDRESS(COLUMN()-3;1), в русской версии Excel =ДВССЫЛ(АДРЕС(СТОЛБЕЦ()-3;1)). Когда вы сделаете это, в ячейке D1 должно появиться число 1. Это происходит потому, что формула ссылается на ячейку А1.

Это достаточно понятный процесс, если вы ссылаетесь только на одну ячейку. Однако часто приходится ссылаться на диапазон ячеек, который используется как аргумент функции.

Чтобы продемонстрировать, о чем идет речь, будем использовать необходимую всем функцию СУММ (SUM). Предположим, вы получили длинный список чисел, и необходимо просуммировать столбцы чисел в режиме промежуточной суммы: =SUM($A$1:$A$2). =SUM($A$1:$A$3). =SUM($A$1:$A$4), в русской версии Excel =СУММС$А$1:$А$2). =СУММ($А$1:$А$3). =СУММ($А$1:$А$4).

Возникает проблема, так как результат должен быть динамическим и распространяться по 100 столбцам всего в одной строке, а не вниз по 100 строкам в одном столбце (как это часто бывает).

Конечно же, вы могли бы вручную ввести нужные функции в отдельные ячейки, но на это потребовалось бы огромное количество времени.

Вместо этого можно применить тот же принцип, что и ранее, при ссылке на одну ячейку.

Заполните диапазон А1:А100 числами от 1 до 100 по порядку. В ячейку А1 введите 1, выделите ячейку А1 и, удерживая клавишу Ctrl, щелкните маркер заполнения и протащите «го вниз на 100 строк.

Выделите ячейку D1 и введите следующую формулу: =SUM(INDIRECT(ADDRESS(1;1)&»:»&ADDRESS(COLUMN()-2;1))), в русской версии Excel =СУММ(ДВССЫЛ(АДРЕС(1;1)&»:»&АДРЕС(СТОЛБЕЦ()-2;1))). В результате вы получите 3 как сумму ячеек А1:А2.

Переменная функция СТОЛБЕЦ (COLUMN) заставляет последнюю ссылку на ячейку увеличиваться на 1 каждый раз, когда вы копируете формулу в новый столбец.

Функция СТОЛБЕЦ (COLUMN) всегда возвращает номер столбца (а не букву) в ту ячейку, в которой находится, если только вы не ссылаетесь на другую ячейку. Иначе, можно было бы воспользоваться средством Excel Специальная вставка → Транспонировать (Paste Special → Transpose).

Введите формулу =SUM($A$1:$A2) в ячейку В1 (обратите внимание на относительную ссылку на строку и абсолютную ссылку на столбец в ссылке $А2) и скопируйте эту формулу вниз до ячейки В100. Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose)

Если необходимо, можно удалить формулы в диапазоне В2:В100

Выделив диапазон В2:В100, скопируйте его, выделите ячейку D1 (или любую другую, справа от которой есть 100 или более столбцов) и выберите команду Специальная вставка → Транспонировать (Paste Special → Transpose). Если необходимо, можно удалить формулы в диапазоне В2:В100.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Данные
B2	1,333
B3	45
Владимир	10
5	62
Формула	Описание	Результат
‘=ДВССЫЛ(A2)	Значение ссылки в ячейке A2. Ссылка на ячейку B2, содержащая значение 1,333.	1,333
‘=ДВССЫЛ(A3)	Значение ссылки в ячейке A3. Ссылка на ячейку B3, содержащая значение 45.	45
‘=ДВССЫЛ(A4)	Поскольку для ячейки B4 определено имя “Владимир”, ссылка на это имя указывает на ячейку B4, содержащую значение 10.	10
‘=ДВССЫЛ(“B” и A5)	Объединяет “B” со значением в ячейке A5, т. е. 5. Она, в свою очередь, ссылается на ячейку B5, содержащую значение 62.	62

Типы ссылок на ячейки в формулах Excel

39315 27.10.2012 Скачать пример

Если вы работаете в Excel не второй день, то, наверняка уже встречали или использовали в формулах и функциях Excel ссылки со знаком доллара, например $D$2 или F$3 и т.п. Давайте уже, наконец, разберемся что именно они означают, как работают и где могут пригодиться в ваших файлах.

Относительные ссылки

Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. «морской бой»), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е.

C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д.

В большинстве случаев это нормально и не создает проблем:

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании «сползает» относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е.

С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может «гулять» по столбцам.

Такие ссылки называют смешанными:

Абсолютные ссылки

Ну, а если к ссылке дописать оба доллара сразу ($C$5) — она превратится в абсолютную и не будет меняться никак при любом копировании, т.е. долларами фиксируются намертво и строка и столбец:

Самый простой и быстрый способ превратить относительную ссылку в абсолютную или смешанную — это выделить ее в формуле и несколько раз нажать на клавишу F4. Эта клавиша гоняет по кругу все четыре возможных варианта закрепления ссылки на ячейку: C5 → $C$5 → $C5 → C$5 и все сначала.

Все просто и понятно. Но есть одно «но».

Предположим, мы хотим сделать абсолютную ссылку на ячейку С5. Такую, чтобы она ВСЕГДА ссылалась на С5 вне зависимости от любых дальнейших действий пользователя. Выясняется забавная вещь — даже если сделать ссылку абсолютной (т.е.

$C$5), то она все равно меняется в некоторых ситуациях. Например: Если удалить третью и четвертую строки, то она изменится на $C$3. Если вставить столбец левее С, то она изменится на D.

Если вырезать ячейку С5 и вставить в F7, то она изменится на F7 и так далее.

А если мне нужна действительно жесткая ссылка, которая всегда будет ссылаться на С5 и ни на что другое ни при каких обстоятельствах или действиях пользователя?

Действительно абсолютные ссылки

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

• Если ввести в ячейку формулу:
• =ДВССЫЛ(«C5»)
• =INDIRECT(«C5»)

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

Единственная небольшая сложность состоит в том, что если целевая ячейка пустая, то ДВССЫЛ выводит 0, что не всегда удобно.

Однако, это можно легко обойти, используя чуть более сложную конструкцию с проверкой через функцию ЕПУСТО:

=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))

=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»))

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

Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.

Пример 1. Функции ДВССЫЛ и СТРОКА

Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать ), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13

{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}

Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.

Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:

={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.

Вот пример:

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

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

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}

Согласитесь, что получается достаточно гибкий расчёт.

Пример 2. Функции ДВССЫЛ и АДРЕС

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

Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.

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

=ДВССЫЛ(АДРЕС(1;3))

Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье  .

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

• ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
•  — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ref_Text Argument Examples

This technical section isn’t required reading — it shows examples for the ref_text argument in the INDIRECT function. Keep reading if you’d like to learn more about these options

In most cases, a cell reference or combination of text string and cell reference works best with the INDIRECT function.

Text string — address is typed into the formula (not flexible)

• A1 style: =INDIRECT(«A4») or =INDIRECT(«Demo!B3») or =INDIRECT(«Budget»)
• R1C1 style: =INDIRECT(«R4C1»,FALSE)
• Note: If you use a simple text string, the range is «hard coded» in the formula, which has limited usefulness, except for .

Создание гиперссылок

В отличие от ссылок, которые мы описали выше, гиперссылки нужны не только для того, чтобы “вытягивать” данные из других ячеек. Помимо этого, они также позволяют осуществлять переход к тому элементу, на который ссылаются.

1. Чтобы создать гиперссылку, нужно перейти к специальному окну, позволяющему его создать. Сделать это можно по-разному:
2. отмечаем нужную ячейку, переключаемся во вкладку “Вставка”, где нажимаем на кнопку “Ссылка”.
3. либо для вставки гиперссылки можно использовать сочетание клавиш CTRL+K.
4. Появится окно, позволяющее настроить гиперссылку. На выбор предлагаются следующие объекты для связки:
5. новый документ;
6. место в документе;
7. электронная почта;
8. Давайте попробуем связать ячейку с документом. Для этого в основной части окна открываем папку с требуемым файлом и отмечаем его. В качестве документа может служить файлы как с расширением “xls” (“xlsx”), так и других форматов. После выбора нужного документа щелкаем OK.
9. В ситуациях, когда нужно создать ссылку на страницу в Интернете, выбирав тот же самый пункт, пишем в поле “Адрес” ссылку на веб-страницу, после чего жмем OK.
10. Когда нужно создать гиперссылку на конкретное место в текущем документе, выбираем пункт “Место в документе”. В основной области окна отмечаем нужный лист и координаты именно той ячейки, связь с которой нужно создать. Как обычно, по завершении щелкаем OK.
11. В тех случаях, когда нам нужно создать связь с новым документом Excel, выбираем соответствующий пункт. Затем придумываем имя новой книги, выбираем место для сохранения и щелкаем OK.
12. В определенных ситуациях возникает потребность связать какой-то элемент книги с e-mail. Помочь в этом может пункт “Электронная почта”. Здесь в поле “Адрес эл. почты” пишем, соответственно, требуемый e-mail, после чего щелкаем OK.
13. Как только мы выбрали тип гиперссылки, заполнили нужные поля и щелкнули OK, в выбранной ячейке появится активная ссылка синего цвета, нажатие на которую осуществит переход к связанному с ней объекту.

Применение функции “ГИПЕРССЫЛКА”

Также, в программе Эксель предусмотрена возможность создания гиперссылки с помощью функции, которая так и называется – “ГИПЕРССЫЛКА”. Формула оператора выглядит следующим образом:

=ГИПЕРССЫЛКА(Адрес;Имя), где:

• “Адрес” – это, собственно говоря, конкретный адрес страницы в Интернете или путь к файлу, с которым нужно связать выбранный элемент.
• “Имя” – текстовое значение, отображаемое в выбранном элементе, при нажатии на которое активируется гиперссылка.

Как пользоваться оператором “ГИПЕРССЫЛКА”:

1. Выбираем нужную ячейку и нажимаем кнопку “Вставить функцию”.
2. В категории “Ссылки и массивы” выбираем оператор “ГИПЕРССЫЛКА” и щелкаем OK.
3. Заполняем аргументы функции и жмем OK.
4. в поле “Имя” прописываем текстовое значение, которое будет показываться в выбранной ячейке.
5. Получаем в выбранной ячейке активную ссылку, ведущую на веб-страницу, адрес которой мы указали.