Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функции ДВССЫЛ в Microsoft Excel.
Описание
Возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Функция ДВССЫЛ используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.
Синтаксис
ДВССЫЛ(ссылка_на_текст;[a1])
Аргументы функции ДВССЫЛ описаны ниже.
-
Ссылка_на_текст — обязательный аргумент. Ссылка на ячейку, которая содержит ссылку в стиле А1 или R1C1, имя, определенное как ссылка, или ссылку на ячейку в виде текстовой строки. Если значение аргумента «ссылка_на_текст» не является допустимой ссылкой, функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.
-
Если значение аргумента «ссылка_на_ячейку» является ссылкой на другую книгу (внешней ссылкой), другая книга должна быть открыта. В противном случае функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!. Примечание.Поддержка внешних ссылок в Excel Web App отсутствует.
-
Если значение аргумента «ссылка_на_текст» является ссылкой на диапазон ячеек, превышающий предельное число строк 1 048 576 или столбцов 16 384 (XFD), функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.
Примечание. Этот результат отличается от результата, получаемого в более ранних версиях приложения Excel, чем Microsoft Office Excel 2007, в которых превышение предела игнорируется и возвращается значение.
-
-
A1 — необязательный аргумент. Логическое значение, определяющее тип ссылки, содержащейся в ячейке «ссылка_на_текст».
-
Если аргумент «a1» имеет значение ИСТИНА или опущен, «ссылка_на_ячейку» интерпретируется как ссылка в стиле A1.
-
Если аргумент «a1» имеет значение ЛОЖЬ, «ссылка_на_ячейку» интерпретируется как ссылка в стиле R1C1.
-
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку 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 |
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку — в полноценную ссылку. Т.е. если нам нужно сослаться на ячейку А1, то мы можем либо привычно сделать прямую ссылку (ввести знак равно в D1, щелкнуть мышью по А1 и нажать Enter), а можем использовать ДВССЫЛ для той же цели:
Обратите внимание, что аргумент функции — ссылка на А1 — введен в кавычках, поэтому что, по сути, является здесь текстом.
«Ну ОК», — скажете вы. «И что тут полезного?».
Но не судите по первому впечатлению — оно обманчиво. Эта функция может выручить вас в большом количестве ситуаций.
Пример 1. Транспонирование
Классика жанра: нужно превратить вертикальный диа
пазон в горизонтальный (транспонировать). Само-собой, можно использовать специальную вставку или функцию ТРАНСП (TRANSPOSE) в формуле массива, но можно обойтись и нашей ДВССЫЛ:
Логика проста: чтобы получить адрес очередной ячейки, мы склеиваем спецсимволом «&» букву «А» и номер столбца текущей ячейки, который выдает нам функция СТОЛБЕЦ (COLUMN).
Обратную процедуру лучше проделать немного по-другому. Поскольку на этот раз нам нужно формировать ссылку на ячейки B2, C2, D2 и т.д., то удобнее использовать режим ссылок R1C1 вместо классического «морского боя». В этом режиме наши ячейки будут отличаться только номером столбца: B2=R1C2, C2=R1C3, D2=R1C4 и т.д.
Тут на помощь приходит второй необязательный аргумент функции ДВССЫЛ. Если он равен ЛОЖЬ (FALSE), то можно задавать адрес ссылки в режиме R1C1. Таким образом, мы можем легко транспонировать горизонтальный диапазон обратно в вертикальный:
Пример 2. Суммирование по интервалу
Мы уже разбирали один способ суммирования по окну (диапазону) заданного размера на листе с помощью функции СМЕЩ (OFFSET). Подобную задачу можно решить и с помощью ДВССЫЛ. Если нам нужно суммировать данные только из определенного диапазона-периода, то можно склеить его из кусочков и превратить затем в полноценную ссылку, которую и вставить внутрь функции СУММ (SUM):
Пример 3. Выпадающий список по умной таблице
Иногда Microsoft Excel не воспринимает имена и столбцы умных таблиц как полноценные ссылки. Так, например, при попытке создать выпадающий список (вкладка Данные — Проверка данных) на основе столбца Сотрудники из умной таблицы Люди мы получим ошибку:
Если же «обернуть» ссылку нашей функцией ДВССЫЛ, то Excel преспокойно ее примет и наш выпадающий список будет динамически обновляться при дописывании новых сотрудников в конец умной таблицы:
Пример 4. Несбиваемые ссылки
Как известно, Excel автоматически корректирует адреса ссылок в формулах при вставке или удалении строк-столбцов на лист. В большинстве случаев это правильно и удобно, но не всегда. Допустим, что нам нужно перенести имена из справочника по сотрудникам в отчет:
Если ставить обычные ссылки (в первую зеленую ячейку ввести =B2 и скопировать вниз), то потом при удалении, например, Даши мы получим в соответствующей ей зеленой ячейке ошибку #ССЫЛКА! (#REF!). В случае применения для создания ссылок функции ДВССЫЛ такой проблемы не будет.
Пример 5. Сбор данных с нескольких листов
Предположим, что у нас есть 5 листов с однотипными отчетами от разных сотрудников (Михаил, Елена, Иван, Сергей, Дмитрий):
Допустим, что форма, размеры, положение и последовательность товаров и месяцев во всех таблицах одинаковые — различаются только числа.
Собрать данные со всех листов (не просуммировать, а положить друг под друга «стопочкой») можно всего одной формулой:
Как видите, идея та же: мы склеиваем ссылку на нужную ячейку заданного листа, а ДВССЫЛ превращает ее в «живую». Для удобства, над таблицей я добавил буквы столбцов (B,C,D), а справа — номера строк, которые нужно взять с каждого листа.
Подводные камни
При использовании ДВССЫЛ (INDIRECT) нужно помнить про ее слабые места:
- Если вы делаете ссылку в другой файл (склеивая имя файла в квадратных скобках, имя листа и адрес ячейки), то она работает только пока исходный файл открыт. Если его закрыть, то получим ошибку #ССЫЛКА!
- С помощью ДВССЫЛ нельзя сделать ссылку на динамический именованный диапазон. На статический — без проблем.
- ДВССЫЛ является волатильной (volatile) или «летучей» функцией, т.е. она пересчитывается при любом изменении любой ячейки листа, а не только влияющих ячеек, как у обычных функций. Это плохо отражается на быстродействии и на больших таблицах ДВССЫЛ лучше не увлекаться.
Ссылки по теме
- Как создать динамический диапазон с автоподстройкой размеров
- Суммирование по диапазону-окну на листе функцией СМЕЩ (OFFSET)
Функция ДВССЫЛ самостоятельно не производит вычислений, а использует в качестве донора другие, более используемые, например, СУММ или СРЗНАЧ, при этом, делает составленные формулы более гибким.
Описание функции ДВССЫЛ
Возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого. Данная функция используется, если требуется изменить ссылку на ячейку в формуле без изменения самой формулы.
Синтаксис
=ДВССЫЛ(ссылка_на_текст; [a1])
Аргументы
ссылка_на_текстa1
Обязательный аргумент. Ссылка на ячейку, которая содержит ссылку в стиле А1 или R1C1, имя, определенное как ссылка, или ссылку на ячейку в виде текстовой строки. Если значение аргумента «ссылка_на_текст» не является допустимой ссылкой, функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.Если значение аргумента «ссылка_на_ячейку» является ссылкой на другую книгу (внешней ссылкой), другая книга должна быть открыта. В противном случае функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.
Примечание: Excel Web App не поддерживает внешние ссылки.
Если значение аргумента «ссылка_на_текст» является ссылкой на диапазон ячеек, превышающий предельное число строк 1 048 576 или столбцов 16 384 (XFD), функция ДВССЫЛ возвращает значение ошибки #ССЫЛКА!.
Примечание: этот результат отличается от результата, получаемого в более ранних версиях приложения Excel, чем Microsoft Office Excel 2007, в которых превышение предела игнорируется и возвращается значение.
Необязательный аргумент. Логическое значение, определяющее тип ссылки, содержащейся в ячейке «ссылка_на_текст».
- Если аргумент «a1» имеет значение ИСТИНА или опущен, «ссылка_на_ячейку» интерпретируется как ссылка в стиле A1.
- Если аргумент «a1» имеет значение ЛОЖЬ, «ссылка_на_ячейку» интерпретируется как ссылка в стиле R1C1.
Пример
Видео работы функции
Использование функции ДВССЫЛПреобразование вертикального диапазона в таблицу Excel
Дополнительные материалы
Использование функции ДВССЫЛ (INDIRECT) в Excel
Преобразование вертикального диапазона в таблицу Excel
Функция ДВССЫЛ возвращает ссылку, которая задана текстовой строкой. К примеру, формула = ДВССЫЛ (А3) аналогична формуле = А3. Но для этой функции ссылка является просто текстовой строкой: ее можно изменять формулами.
Функция применяется для изменения ссылки на ячейку без изменения самой формулы, выведения значений лишь из четных/ нечетных строк, транспонирования таблиц и др.
Синтаксис функции с описанием
Аргументы:
- Ссылка на текст. Обязательный аргумент, содержащий ссылку на ячейку в формате текстовой строки. Например, =ДВССЫЛ («А1») или =ДВССЫЛ («Лист 2!А3»).
- А1 – логическое значение для определения типа ссылки. Необязательный аргумент. Если имеет значение ИСТИНА или опущен, то ссылка на текст воспринимается как ссылка типа А1 (адрес ячейки указан явно). Значение ЛОЖЬ – как ссылка в стиле R1C1 (ссылка на ячейку с формулой, отсылающей к другой ячейке).
Почему при работе функции ДВССЫЛ возникают ошибки:
- Значение аргумента «ссылка на ячейку» является недопустимой ссылкой – ошибка #ССЫЛКА!.
- Обязательный аргумент ссылается на другую книгу (является внешней ссылкой), которая недоступна (закрыта) – ошибка #ССЫЛКА!. При создании подобных формул внешний источник данных должен быть открыт.
- Значение обязательного аргумента ссылается на превышающий предел диапазон – ошибка #ССЫЛКА!. Функция может обработать до 1048576 строк или 16384 столбцов.
Функция ДВССЫЛ в Excel: примеры
Начнем с хрестоматийного примера, чтобы понять принцип работы функции.
Имеется таблица с данными:
Примеры функции ДВССЫЛ:
Рассмотрим практическое применение функции. На листах 1, 2, 3, 4 и 5 в одних и тех же ячейках расположены однотипные данные (информация об образовании сотрудников фирмы за последние 5 лет).
Нужно на основе имеющихся таблиц составить итоговую таблицу на отдельном листе, собрав данные с пяти листов. Сделаем это с помощью функции ДВССЫЛ.
Пишем формулу в ячейке В4 и копируем ее на всю таблицу (вниз и вправо). Данные с пяти различных листов собираются в итоговую таблицу.
Теперь из итоговой таблицы извлечем только нечетные строки. Для удобства пронумеруем столбцы и строки.
Чтобы получить только нечетные записи, используем формулу:
Для выведения четных строк:
Примечание. Функция СИМВОЛ возвращает символ по заданному коду. Код 65 выводит английскую букву A. 66 – B. 67 – С.
Допустим, у пользователя имеется несколько источников данных (в нашем примере – несколько отчетов). Нужно вывести количество сотрудников, основываясь на двух критериях: «Год» и «Образование». Для поиска определенного значения в базе данных подходит функция ВПР.
Чтобы функция сработала, все отчеты поместим на один лист.
Но ВПР информацию в таком виде не сможет переработать. Поэтому каждому отчету мы дали имя (создали именованные диапазоны). Отдельно сделали выпадающие списки: «Год», «Образование». В списке «Год» – названия именованных диапазонов.
Задача: при выборе года и образования в столбце «Количество» должно появляться число сотрудников.
Если мы используем только функцию ВПР, появится ошибка:
Программа не воспринимает ссылку D2 как ссылку на именованный диапазон, где и находится отчет определенного года. Excel считает значение в ячейке текстом.
Исправить положение помогла функция ДВССЫЛ, которая возвращает ссылку, заданную текстовой строкой.
Функции ВПР и ДВССЫЛ в Excel
Теперь формула работает корректно. Для решения подобных задач нужно применять одновременно функции ВПР и ДВССЫЛ в Excel.
Предположим, нужно извлечь информацию в зависимости от заданного значения. То есть добиться динамической подстановки данных из разных таблиц. К примеру, указать количество сотрудников с незаконченным высшим образованием в 2015 и в 2016 году. Сделать так:
В отношении двух отчетов сработает комбинация функций ВПР и ЕСЛИ:
Но для наших пяти отчетов применять функцию ЕСЛИ нецелесообразно. Чтобы возвратить диапазон поиска, лучше использовать ДВССЫЛ:
- $A$12 – ссылка с образованием (можно выбирать из выпадающего списка);
- $C11 – ячейка, в которой содержится первая часть названия листа с отчетом (все листы переименованы: «2012_отчет», 2013_отчет» и т.д.);
- _отчет!A3:B10 – общая часть названия всех листов и диапазон с отчетом. Она соединяется со значением в ячейке С11 (&). В результате получается полное имя нужного диапазона.
Таким образом, эти две функции выполняют подобного рода задачи на отлично.
Функция ДВССЫЛ в Microsoft Excel
Смотрите такжеC5 строки ( содержащую текст ссылки, состоящий из текстового подстановки данных изПримеры функции ДВССЫЛ: формул будет одинаковым: имя в качестве Это может сильно формул, в которых в окно аргументов
выступать в виде«Январь»
данную функцию вНужно отметить, что оператор стиль включен вОдной из встроенных функций→А1 который будет преобразован представления обозначения столбца разных таблиц. КРассмотрим практическое применение функции.Однако, если сверху листа аргумента для функции замедлить работу Вашей он является составнойСУММ встроенной формулы, которая, второй – ячейках, которые располагаются применим для ссылок Excel по умолчанию. программы Excel является$C$5, к данным ссылочного (“A”) и номера
Применение формулы ДВССЫЛ
примеру, указать количество На листах 1, вставить строки, втораяSUM книги, если функция частью выражения. Но. Как видим, в рассчитывает нужное число«Февраль» ниже, то в на другие листы Если значение аргументаДВССЫЛ→С5 типа. Например, результат столбца, соответствующего номеру сотрудников с незаконченным
2, 3, 4 формула возвратит не(СУММ), как это используется во многих все-таки нужно отметить,
поле
или указывает наи т.д.
этом случае придется и даже на не указывать вовсе,. Её задача состоит$C, т.е. «морской бой»), выполнения функции =ДВССЫЛ(“A10”) строки, и формирует высшим образованием в и 5 в верный результат из-за
можно увидеть в формулах. что все возможности«Число1» адрес элемента листа,Итак, чтобы присвоить имя вводить в каждый содержимое других книг то оно будет в том, чтобы5 встречающиеся в большинстве эквивалентен результату выполнения ссылку на ячейку 2015 и в одних и тех того, что ссылки ячейках E3 иЕсли функция операторауже отобразился оператор где оно размещается. первому элементу столбца, элемент формулу отдельно. Excel, но в считаться именно как возвращать в элемент→ файлов Excel. Их записи =A10, и
с помощью операции 2016 году. Сделать же ячейках расположены в формуле изменятся E4.INDIRECTДВССЫЛДВССЫЛ
Именно в этом
выделяем его и
Если мы попытаемся
этом случае они«ИСТИНА» листа, где онаC особенность в том, вернет значение, хранящееся конкатенации подстрок (символ так: однотипные данные (информация
вместе со сдвигом=SUM(NumList) или =СУММ(NumList)(ДВССЫЛ) создаёт ссылкудовольно тяжелы длясо своим содержимым. качестве встроенной функции жмем правую кнопку скопировать её при должны быть запущены.. Во втором случае расположена, содержимое ячейки,$5 что они смещаются в ячейке A10. “&”). Полученное значениеВ отношении двух отчетов об образовании сотрудников строк. Теперь, вместо
=SUM(NumListDyn) или =СУММ(NumListDyn) на другую книгу понимания. Это как Устанавливаем курсор в и будет использоваться мыши. Открывается контекстное помощи маркера заполненияТеперь давайте рассмотрим конкретные ссылки определяются в на которую указанаи все сначала. при копировании формул. Также этот аргумент выступает вторым аргументом сработает комбинация функций фирмы за последние среднего значения трёхВместо того, чтобы вводить Excel, то эта раз и объясняет это же поле нами оператор меню. Выбираем в или другим способом примеры применения оператора. стиле
в ней вВсе просто и понятно. Т.е. может принимать ссылку функции ДАТА, которое ВПР и ЕСЛИ: 5 лет). максимальных чисел, формула с клавиатуры имя книга должна быть малую популярность данной сразу после последнегоДВССЫЛ нем пункт
копирования, то воДля начала рассмотрим простейший«R1C1» виде аргумента ссылка Но есть одноC5 на ячейку, в возвращает дату с
Но для наших пятиНужно на основе имеющихся
Пример 1: одиночное применение оператора
возвращает среднее 3-го, диапазона в функцию открыта, иначе формула полезной функции у символа в записи.в данном случае.«Присвоить имя…»
всех элементах столбца пример, в котором. Данный стиль ссылок в текстовом формате. «но»., например, превращается в которой содержится текстовое
- соответствующим номером месяца. отчетов применять функцию таблиц составить итоговую 4-го и 5-гоSUM сообщит об ошибке
- пользователей. Ставим знак двоеточияУстанавливаем курсор в поле. будет отображаться одно функция нужно специально включатьСкачать последнюю версиюПредположим, мы хотим сделатьС6
- представление ссылки. Например, Функция ТЕКСТ выполняет ЕСЛИ нецелесообразно. Чтобы таблицу на отдельном по величине чисел.(СУММ), Вы можете#REF!Автор: Максим Тютюшев («Число1»Запускается окно создания имени. и то жеДВССЫЛ в настройках Эксель. Excel абсолютную ссылку на, в ячейке E5 преобразование даты к возвратить диапазон поиска, листе, собрав данныеПри помощи функции сослаться на имя,(#ССЫЛКА!).Поздравляю! Вы добрались до:. Затем жмем на
В поле наименование. Дело ввыступает самостоятельно, чтобыЕсли говорить просто, тоКазалось бы, что ничего ячейкуС7 содержится значение 100, требуемому значению месяца лучше использовать ДВССЫЛ: с пяти листов.INDIRECT
записанное в однойЕсли функция финального дня марафона). Данный символ означает
- пиктограмму в виде«Имя» том, что, как вы могли понятьДВССЫЛ особенного в этомС5и т.д. при
- а в ячейке в виде текста.$A$12 – ссылка с Сделаем это с(ДВССЫЛ), третья формула из ячеек листа.INDIRECT30 функций Excel за знак адреса диапазона перевернутого треугольника справавписываем наименование мы помним, ссылка суть её работы.является своеобразным эквивалентом нет, так как. Такую, чтобы она копировании вниз или A5 хранится текстоваяПротянем данную формулу вдоль образованием (можно выбирать помощью функции ДВССЫЛ. сохраняет корректные ссылки Например, если имя(ДВССЫЛ) создаёт ссылку
30 дней ячеек. Далее, не от поля наименования
Пример 2: использование оператора в комплексной формуле
«Январь» выступает в ролиИмеем произвольную таблицу. Стоит ссылки одной ячейки отобразить содержимое одной ВСЕГДА ссылалась на в строка “E5”. В
1-й строки вправо, из выпадающего списка);Пишем формулу в ячейке на строки иNumList на диапазон, превышающий. Это было длинное извлекая курсор из диапазонов. Раскрывается список. Больше никаких изменений аргумента в текстовом задача отобразить данные на другую после ячейки в другойС5D5 результате выполнения функции чтобы заполнить остальные$C11 – ячейка, в В4 и копируем продолжает показывать верныйзаписано в ячейке предельное число строк интересное путешествие, в поля, опять кликаем последних используемых функций. в окне производить виде (обернута в первой ячейки первого знака «равно». Например, можно и болеевне зависимости от, =ДВССЫЛ(A5) будет возвращено ячейки: которой содержится первая
- ее на всю результат. D7, то формула и столбцов, то течение которого Вы по значку в Если среди них не нужно, хотя кавычки), а значит, столбца в первый в большинстве случаев
- простыми способами. Но, любых дальнейших действийE5 значение 100.Примечание: данный пример лишь часть названия листа таблицу (вниз иУрок подготовлен для Вас в ячейке E7 формула сообщит об узнали много полезных виде треугольника для присутствует наименование на всякий случай не может являться
элемент отдельной колонки выражение как оказывается, с пользователя. Выясняется забавнаяи т.д. при[a1] – необязательный для демонстрирует возможности функции с отчетом (все вправо). Данные с
- командой сайта office-guru.ru будет вот такая: ошибке вещей о функциях выбора функций. На«ДВССЫЛ» можно проверить, чтобы относительной. при помощи изучаемой=ДВССЫЛ(«A1») использованием данного оператора вещь — даже копировании вправо и заполнения аргумент, принимающий ДВССЫЛ. Для транспонирования листы переименованы: «2012_отчет», пяти различных листовИсточник: http://blog.contextures.com/archives/2011/01/31/30-excel-functions-in-30-days-30-indirect/=SUM(INDIRECT(D7))
- #REF! Excel. этот раз в, то сразу кликаем координаты в полеУрок: формулы.будет эквивалентно выражению связаны некоторые нюансы, если сделать ссылку т.д. В большинстве значения логического типа: данных лучше использовать 2013_отчет» и т.д.); собираются в итоговуюПеревел: Антон Андронов=СУММ(ДВССЫЛ(D7))(#ССЫЛКА!).30-й день марафона мы списке недавно использованных
- по нему для«Диапазон»Мастер функций в программеВыделяем первый пустой элемент=A1 которые делают его абсолютной (т.е. случаев это нормальноИСТИНА – функция ДВССЫЛ функцию ТРАНСП._отчет!A3:B10 – общая часть таблицу.
- Автор: Антон АндроновК сожалению, функцияФункция посвятим изучению функции операторов наименование перехода в окносоответствовали адресу ячейки Excel столбца, куда планируем
- Но в отличие от уникальным. В некоторых$C$5 и не создает интерпретирует текстовую строку, названия всех листовТеперь из итоговой таблицы
Функция ДВССЫЛ возвращает ссылку,
INDIRECTINDIRECTINDIRECT«ДВССЫЛ» аргументов данной функции. содержащей размер выручкиА теперь давайте посмотрим вставлять формулу. Щелкаем выражения случаях данная формула), то она все проблем: переданную в качествеПример 2. В таблице и диапазон с извлечем только нечетные которая задана текстовой(ДВССЫЛ) не может(ДВССЫЛ) не может(ДВССЫЛ), которая возвращаетдолжно точно присутствовать, Но вполне может
за январь. После на пример гораздо по значку«=A1» способна решать такие равно меняется вИногда тот факт, что первого аргумента, как содержатся данные о отчетом. Она соединяется строки. Для удобства строкой. К примеру, создать ссылку на создать ссылку на ссылку, заданную текстовой так как мы быть, что в этого щелкаем по более частого применения«Вставить функцию»оператор задачи, с которыми некоторых ситуациях. Например: ссылка в формуле ссылку типа A1.
- покупках товаров, при со значением в пронумеруем столбцы и формула = ДВССЫЛ динамический диапазон, поэтому, динамический именованный диапазон. строкой. С помощью совсем недавно использовали этом списке вы кнопке оператора.ДВССЫЛ
- другими способами просто Если удалить третью при копировании «сползает» Данное значение используется этом каждая запись ячейке С11 (&). строки. (А3) аналогична формуле когда Вы скопируетеВ первом примере в этой функции можно эту функцию. Щелкаем его не обнаружите.«OK»ДВССЫЛПроисходит запуск окошкапривязывается не к не справиться или и четвертую строки, относительно исходной ячейки по умолчанию (если имеет свой номер В результате получаетсяЧтобы получить только нечетные = А3. Но эту формулу вниз столбцах C и создавать зависимые выпадающие по наименованию.
В таком случае., когда он являетсяМастера функций конкретной ячейке, а это будет гораздо то она изменится — бывает нежелательным. аргумент явно не (id). Рассчитать суммарную полное имя нужного записи, используем формулу: для этой функции
- в ячейку E8, E находятся одинаковые списки. Например, когдаСнова открывается окно аргументов нужно щелкнуть поКак видим, теперь при составной частью комплексной. Перемещаемся в категорию к координатам элемента сложнее сделать. Давайте на Тогда для закрепления указан). стоимость любого количества диапазона.Для выведения четных строк: ссылка является просто то получите сообщение числа, их суммы, выбор страны из оператора наименованию выделении данного элемента формулы.«Ссылки и массивы» на листе. узнаем подробнее, что$C$3 ссылки используется знакЛОЖЬ – первый аргумент покупок (создать соответствующуюТаким образом, эти двеПримечание. Функция СИМВОЛ
- текстовой строкой: ее об ошибке посчитанные при помощи выпадающего списка определяет,ДВССЫЛ«Другие функции…» в окне имениИмеем помесячную таблицу доходов. Из перечня выбираемРассмотрим, что это означает собой представляет оператор. Если вставить столбец доллара ($), позволяющий функции должен быть форму для расчета). функции выполняют подобного возвращает символ по можно изменять формулами.
- #REF! функции какие варианты появятся. Заносим в полев самом низу отображается не её предприятия. Нам нужно значение на простейшем примере.ДВССЫЛ
- левее зафиксировать то, перед указан в видеСоздадим форму для расчетов, рода задачи на заданному коду. КодФункция применяется для изменения(#ССЫЛКА!).SUM в выпадающем списке«Ссылка на ячейку» списка. адрес, а то подсчитать сумму дохода
«ДВССЫЛ» В ячейкахи как его
С чем он стоит. текстового представления ссылки в которой id отлично. 65 выводит английскую ссылки на ячейкуВы легко можете создать(СУММ), тоже одинаковы. городов.адрес элемента наЗапускается уже знакомое нам наименование, которое мы за определенный период. Щелкаем по кнопкеB8 можно использовать на, то она изменится Таким образом, например, типа R1C1. записи могут бытьФункция ДВССЫЛ в Excel букву A. 66 без изменения самой ссылку, опираясь на Тем не менее,Итак, давайте внимательно посмотрим листе, где расположено окошко
ей дали. Аналогичную
lumpics.ru
30 функций Excel за 30 дней: ДВССЫЛ (INDIRECT)
времени, например март«OK»и практике. на ссылкаПримечания: выбраны из соответствующих предназначена для преобразования – B. 67
формулы, выведения значений номера строк и формулы немного отличаются. теоретическую часть по наименования месяца, которыйМастера функций операцию проделываем со – май или.B9Само наименование данного оператораD$CЕсли в качестве первого
значений в списках. текстового представления ссылки – С. лишь из четных/ столбцов, а также В ячейке C8 функции завершает расчетный период.. Перемещаемся в раздел всеми другими элементами июнь – ноябрь.
Функция 30: INDIRECT (ДВССЫЛ)
Происходит запуск окошка аргументовсоответственно размещена записаннаяДВССЫЛ. Если вырезать ячейку
Как можно использовать функцию INDIRECT (ДВССЫЛ)?
5 аргумента функции был Вид исходной и на ячейку илиДопустим, у пользователя имеется нечетных строк, транспонирования
- используя значение FALSE формула вот такая:
- INDIRECT Опять координаты должны
- «Ссылки и массивы» столбца Конечно, для этого
- указанного оператора. В через
Синтаксис INDIRECT (ДВССЫЛ)
расшифровывается, какС5не будет изменяться передан текст, не
результативной таблицы:
диапазон к ссылочному
- несколько источников данных таблиц и др. (ЛОЖЬ) для второго
- =SUM(C2:C7)(ДВССЫЛ) и изучим быть вписаны бези выбираем там«Доход» можно воспользоваться формулой поле«=»«Двойная ссылка»и вставить в по столбцам (т.е.
Ловушки INDIRECT (ДВССЫЛ)
- содержащий ссылку илиВ ячейке E2 запишем типу данных и (в нашем примереАргументы: аргумента функции=СУММ(C2:C7) практические примеры её кавычек. Поле наименование оператора
- , присвоив им последовательно простого суммирования, но«Ссылка на ячейку»формула и функция. Собственно, это иF7С ссылка на пустую следующую формулу: возвращает значение, которое
- – несколько отчетов).Ссылка на текст. ОбязательныйINDIRECTВ ячейке E8 функция применения. Если у«A1»ДВССЫЛ имена в этом случаетребуется указать адрес
- ДВССЫЛ указывает на его, то она изменитсяникогда не превратится ячейку, функция ДВССЫЛ
Пример 1: Создаем не сдвигающуюся начальную ссылку
Функция ДВССЫЛ принимает в хранится в полученной Нужно вывести количество аргумент, содержащий ссылку(ДВССЫЛ). Так создаетсяINDIRECT Вас есть дополнительнаяснова оставляем пустым.. После этого действия«Февраль» при необходимости подсчета того элемента на
. Обе формулы ссылаются
предназначение – выводить
на в вернет код ошибки качестве аргумента текстовую ссылке.
сотрудников, основываясь на
на ячейку в
ссылка стиля(ДВССЫЛ) создаёт ссылку информация или примеры, После этого щелкаем щелкаем по кнопке, общего результата за листе, содержимое которого
на элемент
данные посредством указанной
F7D #ССЫЛКА!. строку, которая состоитПоскольку функция ДВССЫЛ принимает двух критериях: «Год» формате текстовой строки.R1C1 на начальную ячейку пожалуйста, делитесь ими по кнопке
«OK»
«Март»
Пример 2: Создаем ссылку на статичный именованный диапазон
каждый период нам мы будем отображать.B4 ссылки из однойи так далее.,Результат выполнения функции ДВССЫЛ из буквенного обозначения ссылки в качестве и «Образование». Для Например, =ДВССЫЛ («А1»). В этом примере E2: в комментариях.«OK»в нижней части
, все время придется Конечно, его можнои выводят его ячейки в другую. А если мнеE будет пересчитан при диапазона ячеек столбца текстовых строк, входные
поиска определенного значения
или =ДВССЫЛ («Лист
мы дополнительно добавили=SUM(INDIRECT(«E2»):E7)Функция. окошка.«Апрель» менять эту формулу. вписать вручную, но содержимое на лист. Причем, в отличие нужна действительно жесткаяили любом изменении данных столбца (“C:C”) и
данные могут быть
в базе данных
2!А3»). к ссылке имя=СУММ(ДВССЫЛ(«E2»):E7)INDIRECTКак видим, после данныхПроисходит запуск окна аргументови т.д. до А вот при гораздо практичнее и Естественно это содержимое от большинства других ссылка, которая всегда
Пример 3: Создаём ссылку используя информацию о листе, строке и столбце
F на листе и номера строки, определенного модифицированы для получения подходит функция ВПР.А1 – логическое значение листа – ‘MyLinks’!R2C2Если вверху листа вставить(ДВССЫЛ) возвращает ссылку, действий программа производит оператора декабря включительно. использовании функции удобнее будет сделать одинаковое.
функций, работающих со
будет ссылаться на
Пример 4: Создаём не сдвигающийся массив чисел
), но может смещаться во время открытия значениями, хранящимися в динамически изменяемых значений.Чтобы функция сработала, все для определения типа=INDIRECT(«‘»&B3&»‘!R»&C3&»C»&D3,FALSE) строку и добавить заданную текстовой строкой. расчет и выдаетДВССЫЛ
Выбираем ячейку, в которую
ДВССЫЛ
следующее. Устанавливаем курсорДобавляем в таблицу ещё ссылками, она должнаС5 по строкам (т.е. книги. в ячейках F4Ссылки на ячейки в отчеты поместим на ссылки. Необязательный аргумент.=ДВССЫЛ(«‘»&B3&»‘!R»&C3&»C»&D3;ЛОЖЬ) значение для января
Поскольку функция
результат сложения дохода
. В поле будет выводиться суммаможно будет производить в поле, после один пустой элемент. быть указана ви ни на может сдвинуться наЕсли переданная в качестве
и G4 соответственно.
Excel могут быть
один лист. Если имеет значение
Иногда в формулах Excel (Jan), то суммаINDIRECT предприятия за указанный«Ссылка на ячейку» значений указанного интервала, изменение суммированного диапазона, чего щелкаем левой Как видим, строки текстовом формате, то что другое ни$C6 первого аргумента ссылка
В результате вычислений указаны в видеНо ВПР информацию в ИСТИНА или опущен, необходимо использовать массив в столбце C(ДВССЫЛ) возвращает ссылку,
период (март —указываем адрес элемента
и выделяем её.
просто в отдельных
кнопкой мыши по
office-guru.ru
Функция ДВССЫЛ в Excel с примерами использования
сдвинулись. В формуле есть, выделена с при каких обстоятельствах, в виде текста запись принимает, например, сочетания буквенного наименования таком виде не то ссылка на чисел. В следующем
не изменится. Изменится заданную текстовой строкой, май) в предварительно листа, который содержит Затем щелкаем по ячейках указав соответствующий соответствующему элементу на
Синтаксис функции с описанием
с применением
- обеих сторон кавычками. или действиях пользователя?$C7 указывает на вертикальный следующий вид: C2:C5. столбца и цифрового сможет переработать. Поэтому
- текст воспринимается как примере мы хотим формула, отреагировав на то с её выделенный элемент листа, наименование начального месяца пиктограмме месяц. Попробуем использовать листе. Как видим,«равно»Данный оператор относится кРешение заключается в использованиии т.д.). Аналогично, диапазон ячеек с Функция СУММ вычисляет
номера строки (например, каждому отчету мы ссылка типа А1
- получить среднее из прибавление строки: помощью Вы сможете: в котором располагается
- диапазона предназначенного для«Вставить функцию» данный вариант на сразу после этогозначение осталось прежним, категории функций функцииC
- более чем 1048576 сумму значений, хранящихся D5, то есть, дали имя (создали (адрес ячейки указан 3-х наибольших чисел=SUM(C3:C8)
Создать не сдвигающуюся начальную
Функция ДВССЫЛ в Excel: примеры
сама формула. расчета суммы. Обратите. Она размещена слева
практике сначала для
его адрес отобразился
так как она«Ссылки и массивы»ДВССЫЛ (INDIRECT)$5 строк или горизонтальный в ячейках указанного ячейка в столбце именованные диапазоны). Отдельно явно). Значение ЛОЖЬ в столбце B.
=СУММ(C3:C8) ссылку.Если мы поменяем в внимание, что как от строки формул вычисления суммы за в поле. Затем
ссылается на конечнуюи имеет следующий, которая формирует ссылку- не будет диапазон с более диапазона. D и строке сделали выпадающие списки:
– как ссылка Числа могут бытьОднако, функцияСоздать ссылку на статичный ячейках, где вписаны
раз в этом и справа от
период с марта
с двух сторон ячейку, пусть даже синтаксис: на ячейку из смещаться по строкам, чем 16384 столбцов,Примеры вычислений:
с номером 5), «Год», «Образование». В в стиле R1C1 вписаны в формулу,INDIRECT именованный диапазон. наименования месяцев начала случае брать ссылку поля, где отображается по май. При выделяем ссылку кавычками.
её координаты и=ДВССЫЛ(ссылка_на_ячейку;[a1]) текстовой строки.
но может «гулять» результатом выполнения функцииПример 3. В таблице а также в списке «Год» – (ссылка на ячейку как это сделано(ДВССЫЛ) фиксирует E2Создать ссылку, используя информацию и конца расчетного
в кавычки не имя ячеек. этом будет использована Как мы помним,
изменились, а вотТаким образом, формула имеетЕсли ввести в ячейку
по столбцам. Такие будет код ошибки хранятся данные об стиле RXCY, где: названия именованных диапазонов. с формулой, отсылающей в ячейке D4:
как начальную ячейку, о листе, строке периода, на другие, нужно, так как
Функции ВПР и ДВССЫЛ в Excel
В активировавшемся окошке формула с комбинацией это особенность работы данные выводимые оператором всего два аргумента. формулу:
ссылки называют #ССЫЛКА!. абонентах. Создать компактнуюR – сокращенно отЗадача: при выборе года к другой ячейке).=AVERAGE(LARGE(B1:B8,{1,2,3})) поэтому значение января и столбце. например на в данном случаеМастера функций
операторов с аргументом даннойДВССЫЛ
Аргумент=ДВССЫЛ(«C5»)смешанными:Использование текстовых представлений внешних таблицу на основе
- «row» (строка) – и образования вПочему при работе
- =СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3})) автоматически включается вСоздать не сдвигающийся массив«Июнь» в качестве адресаперемещаемся в категорию
- СУММ формулы.поменялись. Это связано«Ссылка на ячейку»=INDIRECT(«C5»)Ну, а если к ссылок (ссылки на имеющейся, в которой указатель строки;
столбце «Количество» должно функции ДВССЫЛ возникаютЕсли Вам потребуется массив подсчёт суммы по
exceltable.com
Функция ДВССЫЛ позволяет преобразовать текст в ссылку 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: диапазон. В этом
– если равен
- то, что функцию координат. является суммирование указанныхи равнозначные действия.Данный оператор способен выводитьне является обязательным целевая ячейка пустая,
- столбец: ссылки со знаком формулу массива (CTRL+SHIFT+ENTER): двух вариантов представления.Исправить положение помогла функция внешний источник данных
- =AVERAGE(LARGE(B1:B8,ROW(1:3))) примере голубые ячейки TRUE (ИСТИНА) илиДВССЫЛПосле того, как адрес значений. Синтаксис этой«Май»После этого щелкаем по в другую ячейку и в подавляющем тоСамый простой и быстрый
- доллара, напримерПримечание: перед выполнение формулыПример 1. Преобразовать столбец ДВССЫЛ, которая возвращает должен быть открыт.=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3))) составляют диапазон
exceltable.com
Типы ссылок на ячейки в формулах Excel
не указан, тонельзя назвать одной отобразился в поле, функции очень простой:. кнопке не только числа, большинстве случаев егоДВССЫЛ способ превратить относительную$D$2 необходимо выделить диапазон номеров месяцев в ссылку, заданную текстовойЗначение обязательного аргумента ссылаетсяТретий вариант – этоNumList будет использован стиль
Относительные ссылки
из наиболее популярных не спешим жать=СУММ(число1;число2;…)Теперь присвоим имя всем«OK» но и текст, вообще не нужновыводит 0, что ссылку в абсолютнуюили ячеек B16:E16. строку, в которой строкой. на превышающий предел использовать функцию. Кроме этого, из ссылки у пользователей, тем на кнопкуВ целом количество аргументов ячейкам в столбце. результат вычисления формул указывать. Он может не всегда удобно. или смешанную -F$3В результате получим компактную содержатся текстовые представленияТеперь формула работает корректно.
Смешанные ссылки
диапазон – ошибкаROW значений в столбцеA1 не менее, она«OK» может достигать значения«Доход»Как видим, теперь содержимое и любые другие иметь два значения Однако, это можно это выделить ееи т.п. Давайте таблицу с возможностью этих месяцев (то Для решения подобных #ССЫЛКА!. Функция может(СТРОКА) вместе с B создан еще; а если FALSE помогает решить задачи, так как это255, которое будет являться первой ячейки первого значения, которые расположены«ИСТИНА» легко обойти, используя в формуле и уже, наконец, разберемся отображения записей по есть, транспонировать имеющийся задач нужно применять обработать до 1048576INDIRECT и динамический диапазон (ЛОЖЬ), то стиль различной сложности в
Абсолютные ссылки
вложенная функция, и. Но все эти аналогичным названию соответствующего столбца таблицы выводится в выбранном элементеи чуть более сложную несколько раз нажать что именно они указанному номеру (id): список). одновременно функции ВПР строк или 16384
(ДВССЫЛ), как этоNumListDynR1C1 Excel гораздо проще, действия с ней аргументы являются однородными. им месяца. То в том элементе листа. Но на«ЛОЖЬ» конструкцию с проверкой на клавишу F4. означают, как работаютФункция имеет следующую синтаксическуюВид таблицы данных: и ДВССЫЛ в столбцов. сделано с помощью, зависящий от количества. чем это можно отличаются от обычного Они представляют собой
есть, первый элемент листа, в котором практике данная функция
. В первом случае через функцию Эта клавиша гоняет и где могут запись:Для получения строки текстовых Excel. формулы массива в чисел в этомФункция было бы сделать алгоритма. Щелкаем по число или координаты в столбце расположена формула редко когда применяется оператор определяет ссылкиЕПУСТО по кругу все пригодиться в ваших=ДВССЫЛ(ссылка_на_текст;[a1]) представлений месяцев введемПредположим, нужно извлечь информациюНачнем с хрестоматийного примера, ячейке D6: столбце.INDIRECT при помощи других наименованию ячейки, в которой«Доход»ДВССЫЛ самостоятельно, а гораздо в стиле: четыре возможных варианта файлах.Описание аргументов: в ячейку B2 в зависимости от чтобы понять принцип=AVERAGE(LARGE(B1:B8,ROW(INDIRECT(«1:3»))))Сумма для обоих диапазонов(ДВССЫЛ) пересчитывается при
Действительно абсолютные ссылки
инструментов. Более всего«СУММ» это число содержится., который содержит размер. чаще бывает составной
«A1»=ЕСЛИ(ЕПУСТО(ДВССЫЛ(«C5″));»»;ДВССЫЛ(«C5»))
закрепления ссылки на
Это обычные ссылки в
ссылка_на_текст – обязательный аргумент, следующую формулу: заданного значения. То работы функции.=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ(«1:3»)))) может быть вычислена, любом изменении значений данный оператор полезенв строке формул. Также они могут выручки, следует назватьЕсли мы захотим применить частью сложных формул., а именно такой=IF(ISBLANK(INDIRECT(«C5″));»»;INDIRECT(«C5»)) ячейку: виде буква столбца-номер принимающий текстовую строку,Функция ДВССЫЛ принимает аргумент, есть добиться динамическойИмеется таблица с данными:Результат для всех 3-х просто задав его
на листе Excel.
в составе сложных
planetaexcel.ru
После этого мы возвращаемся