Функция двссыл в excel

Параметры Parameters

Имя Name	Обязательный или необязательный Required/Optional	Тип данных Data type	Описание Description
RowAbsolute RowAbsolute	Необязательный Optional	Variant Variant	Значение True, чтобы возвратить часть строки ссылки в качестве абсолютной ссылки. True to return the row part of the reference as an absolute reference. Значение по умолчанию — True. The default value is True.
ColumnAbsolute ColumnAbsolute	Необязательный Optional	Variant Variant	Значение True, чтобы возвратить часть столбца ссылки в качестве абсолютной ссылки. True to return the column part of the reference as an absolute reference. Значение по умолчанию — True. The default value is True.
ReferenceStyle ReferenceStyle	Необязательный Optional	XlReferenceStyle XlReferenceStyle	Стиль ссылки. The reference style. Значение по умолчанию — xlA1. The default value is xlA1.
External External	Необязательный Optional	Variant Variant	Значение True, чтобы вернуть внешнюю ссылку. True to return an external reference. Значение False, чтобы вернуть локальную ссылку. False to return a local reference. Значение по умолчанию — False. The default value is False.
RelativeTo RelativeTo	Необязательный Optional	Variant Variant	Если RowAbsolute и ColumnAbsolute имеют значение False, а ReferenceStyle — xlR1C1, необходимо включить начальную точку для относительной ссылки. If RowAbsolute and ColumnAbsolute are False, and ReferenceStyle is xlR1C1, you must include a starting point for the relative reference. Этот аргумент является объектом Range, определяющим начальную точку. This argument is a Range object that defines the starting point.

ПРИМЕЧАНИЕ. Тестирование с помощью Excel VBA 7.1 показывает, что явная начальная точка необязательна. NOTE: Testing with Excel VBA 7.1 shows that an explicit starting point is not mandatory. По умолчанию отображается ссылка на $A$1. There appears to be a default reference of $A$1.

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования ) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

Пусть ячейки, которые должны содержать Выпадающий список , размещены на листе Пример,

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

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

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:

• выделяем А1:А4 ,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов , в поле Область выбираем Книга ;

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

• вызываем Проверку данных ;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10 . Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон . Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек ( A:A ), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон ).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример , выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных , в Источнике указываем =ДВССЫЛ(“список!A1:A4”) .

Недостаток : при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа .

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя , например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(“лист1!СписокВнеш”) ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на = ДВССЫЛ(“лист1!$A$1:$A$4”)

СОВЕТ: Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент Выделение группы ячеек ( Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

Использование обработки условия при помощи ЕСЛИ.

Рассмотрим на примере. Необходимо определить комиссионные для каждого из менеджеров с учетом объема их продаж и стажа работы. В зависимости от стажа существуют различные ставки комиссионных выплат, как это показано на рисунке ниже.

Для того, чтобы легче было работать с таблицами ставок, используем именованные диапазоны и обозначим их tabl1, tabl2 и tabl3.

В качестве условия для использования каждой из таблиц служит стаж работника. Таким образом, внутри функции ВПР (VLOOKUP) используем обработку условий при помощи ИЛИ (IF). В ячейке D2 запишем:

Если стаж более 3 лет, то используем tabl3. Если нет, то проверяем условие «стаж менее года». В этом случае данные будем извлекать из tabl1. Если и это не выполняется, тогда остается только второй вариант и диапазон tabl2.

Читайте подробнее: Функция ЕСЛИ: примеры с несколькими условиями

Таким образом, наш источник данных будет меняться в зависимости от величины стажа.

Также обратите внимание, что четвертый аргумент функции ВПР равен 1. Это значит, что мы используем неточный интервальный поиск

Он как раз и позволяет точно определить, в какой интервал попадает наше число. Дополнительную информацию об этом смотрите в конце статьи.

Сумма комиссионных находится простым произведением ставки комиссионных на объем продаж. Далее копируем эти формулы для всех менеджеров.

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

Как видите, все отлично работает, хотя диапазон поиска находится левее столбца с условиями. Своего рода «обратный» или «левый» ВПР, о котором мы уже рассказывали. (см. ссылки в конце статьи).

Синтаксис функции

ДВССЫЛ( ссылка_на_ячейку ;a1 )

Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е. указаны столбец и строка): = ДВССЫЛ(“B3”) или = ДВССЫЛ(“Лист1!B3”) или =ДВССЫЛ(“Лист1!B3”) . Первая формула эквивалентна формуле = B3 , вторая – = Лист1!B3 , третья = Лист1!B3 Если какая-либо ячейка (например, А1) содержит текстовую строку в формате ссылки (например, Лист1!B3 ), то в ДВССЫЛ() можно указать ссылку на эту ячейку = ДВССЫЛ(А1) Эта запись будет эквивалентна = ДВССЫЛ(“Лист1!B3”) , которая в свою очередь будет эквивалентна = Лист1!B3 . Зачем все это нужно – читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание: Формат ссылки = Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 – это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =RC, то это ссылка на ячейку С4. Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами ).

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Абсолютная и относительная ссылка в Excel. Как использовать?

​, то она изменится​ по столбцам. Такие​F$3​ крестик. После этого​ и перед цифрой.​ строка, а колонка​ A2 (то есть​В Excel существует несколько​4,5​ соответственно.​ таблицах и узнаем,​ нашем примере мы​ формулы, которые при​ ячейку, который будет​«=D3/$D$7»​ уже во второй​ при копировании, относительно​ на​ ссылки называют​и т.п. Давайте​ просто «протягиваем» формулу​

Определение

​Несмотря на копирования в​ изменяется.​ курса евро).​ типов ссылок: абсолютные,​2​Это и называется относительной​ что такое относительные,​ выделим ячейку D3.​ копировании ссылаются на​ изменяться в формуле​, то есть делитель​ строке таблицы формула​ других ячеек листа.​F7​смешанными:​ уже, наконец, разберемся​ вниз. Система автоматически​ другие ячейки и​

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

​Для сравнения: A2 –​Для решения данной задачи​ относительные и смешанные.​70​ адресацией. Пример кажется​ абсолютные и смешанные​Введите выражение для вычисления​ одну и ту​ ссылку.​ поменялся, а делимое​

​ имеет вид​Покажем, как это работает​и так далее.​Ну, а если к​ что именно они​ скопирует все значения.​ даже на другие​ это адрес относительный,​ нам нужно ввести​ Сюда так же​3​

​ простым и возникает​ ссылки. Их работа​ необходимого значения. В​ же ячейку. Это​Например, при копировании формулы​ осталось неизменным.​«=D3/D8»​ на примере. Возьмем​ А если мне​ ссылке дописать оба​ означают, как работают​ Данный инструмент называется​ листы, книги, абсолютная​ без фиксации. Во​ формулу в C2:​ относятся «имена» на​80​ естественный вопрос, а​ достаточно проста, поэтому​ нашем случае мы​ очень удобно, особенно,​= B4 * C4​Кроме типичных абсолютных и​, то есть сдвинулась​ таблицу, которая содержит​ нужна действительно жесткая​

​ доллара сразу (​ и где могут​ маркером автозаполнения.​ ссылка всегда будет​ время копирования формул​ =B2/A2 и скопировать​ целые диапазоны ячеек.​Итак, у нас имеется​ не проще ли​ проще всего будет​ введем​ когда приходится работать​на ячейку D4​ относительных ссылок, существуют​ не только ссылка​

Абсолютная ссылка

​ количество и цену​ ссылка, которая всегда​$C$5​ пригодиться в ваших​Или еще проще: диапазон​ ссылаться на один​ строка (2) и​ ее во все​ Рассмотрим их возможности​

​ таблица с некоторыми​ заново написать формулу?​ рассмотреть их на​=(B3*C3)*$E$1​ с большим количеством​ D5, формула в​ так называемые смешанные​ на ячейку с​ различных наименований продуктов.​ будет ссылаться на​) — она превратится​ файлах.​ ячеек, в которые​ и тот же​ столбец (A) автоматически​ ячейки диапазона C2:C4.​ и отличия при​ данными. В столбце​ Нет, в больших​ практике. Так что​.​ формул. В данном​ D5 регулирует вправо​

​ ссылки. В них​ суммой по строке,​ Нам нужно посчитать​С5​ в​Это обычные ссылки в​

Смешанная

​ нужно проставить формулы​ адрес столбца и​ изменяются на новые​ Но здесь возникает​ практическом применении в​ «А» у нас​ таблицах и объёмных​ такое абсолютная и​Нажмите​ уроке мы узнаем,​ по одному столбцу​ одна из составляющих​ но и ссылка​ стоимость.​и ни на​

	​абсолютную​	​ виде буква столбца-номер​	​ выделяем так чтобы​
​ строки.​		​ адреса относительно расположения​	​ проблема. Из предыдущего​
​ формулах.​	​ имеется некая величина,​
​ вычислениях, в которых​	​ относительная ссылка?​

​Enter​ что же такое​ и становится​ изменяется, а вторая​ на ячейку, отвечающую​Делается это простым умножением​ что другое ни​и не будет​ строки (​ активная ячейка была​Полезный совет! Чтобы не​ скопированной формулы, как​ примера мы знаем,​Абсолютные ссылки позволяют нам​ а в строке​

​ могут использоваться самые​Ссылки в Microsoft Excel​на клавиатуре. Формула​ абсолютные ссылки, а​= B5 * C5​ фиксированная. Например, у​ за общий итог.​ количества (столбец B)​ при каких обстоятельствах​ меняться никак при​А1​ на формуле и​ вводить символ «$»​ по вертикали, так​

• ​ что при копировании​
• ​ зафиксировать строку или​
• ​ «1», коэффициент, на​

​ разные функции и​ — это не​ будет вычислена, а​ также научимся использовать​. Если вы хотите​ смешанной ссылки $D7​D8 – это совершенно​ на цену (столбец​ или действиях пользователя?​ любом копировании, т.е.​,​ нажимаем комбинацию горячих​ вручную, при вводе​ и по горизонтали.​ относительные ссылки автоматически​ столбец (или строку​ который необходимо умножить.​ операторы намного надежнее​ то же самое,​ результат отобразится в​

​ их при решении​ сохранить исходный в​ строчка изменяется, а​ пустая ячейка, поэтому​ C). Например, для​

​Решение заключается в использовании​

fb.ru>

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

Пример 1: Создаем не сдвигающуюся начальную ссылку

В первом примере в столбцах C и E находятся одинаковые числа, их суммы, посчитанные при помощи функции SUM (СУММ), тоже одинаковы. Тем не менее, формулы немного отличаются. В ячейке C8 формула вот такая:

В ячейке E8 функция INDIRECT (ДВССЫЛ) создаёт ссылку на начальную ячейку E2:

Если вверху листа вставить строку и добавить значение для января (Jan), то сумма в столбце C не изменится. Изменится формула, отреагировав на прибавление строки:

Однако, функция INDIRECT (ДВССЫЛ) фиксирует E2 как начальную ячейку, поэтому значение января автоматически включается в подсчёт суммы по столбцу E. Конечная ячейка изменилась, но на начальную это не повлияло.

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

Синтаксис функции

ДВССЫЛ( ссылка_на_ячейку ;a1 )

Ссылка_на_ячейку — это текстовая строка в формате ссылки (т.е. указаны столбец и строка): = ДВССЫЛ(«B3») или = ДВССЫЛ(«Лист1!B3») или =ДВССЫЛ(«Лист1!B3») . Первая формула эквивалентна формуле = B3 , вторая – = Лист1!B3 , третья = Лист1!B3 Если какая-либо ячейка (например, А1) содержит текстовую строку в формате ссылки (например, Лист1!B3 ), то в ДВССЫЛ() можно указать ссылку на эту ячейку = ДВССЫЛ(А1) Эта запись будет эквивалентна = ДВССЫЛ(«Лист1!B3») , которая в свою очередь будет эквивалентна = Лист1!B3 . Зачем все это нужно – читайте ниже (см. раздел решение задач).

Второй аргумент а1 — это логическое значение (ИСТИНА или ЛОЖЬ), указывающее, какого типа ссылка содержится в аргументе Ссылка_на_ячейку .

• Если a1 имеет значение ИСТИНА или опущена, то ссылка_на_ячейку интерпретируется как ссылка в стиле A1.
• Если a1 имеет значение ЛОЖЬ, то ссылка_на_ячейку интерпретируется как ссылка в стиле R1C1.

Примечание: Формат ссылки = Лист1!B3 называется ссылкой в стиле А1, когда явно указывается адрес ячейки. Формат ссылки в стиле R1C1 – это относительная ссылка на ячейку (относительная относительно ячейки с формулой). Например, если в ячейке С5 имеется формула =RC, то это ссылка на ячейку С4. Чтобы записывать ссылки в стиле R1C1 необходимо переключить EXCEL в режим работы со ссылками в стиле R1C1 ( Кнопка Офис/ Параметры Excel/ Формулы/ Работа с формулами ).

Если ссылка_на_ячейку не является допустимой ссылкой, то функция ДВССЫЛ() возвращает значение ошибки #ССЫЛКА!

Комбинация СМЕЩ + ПОИСКПОЗ

Итак, у нас снова есть перечень марок и моделей автомобилей. Только записан он немного по-другому.

Вновь перед нами стоит задача создать двухуровневый выпадающий список. Сначала — выбор марки, затем — только модели этой марки.

Первое условие — исходные данные должны быть отсортированы по маркам, а внутри марок — по моделям. То есть, нужно отсортировать по столбцу А, а затем — по В.

Начнем с простого. В ячейке D1 создадим выпадающий список из марок автомобилей. Для этого в F1:F3 запишем их названия и затем употребим их в качестве источника. Напомню, что нужно нажать Меню — Данные — Проверка данных.

Далее нам нужно в D2 создать второй уровень, где будут только модели выбранной марки. В этот раз источник данных мы определим несколько иначе, чем ранее. Воспользуемся тем, что функция СМЕЩ может возвращать массив данных, который мы как раз и можем употребить в качестве наполнения нашего второго перечня. Но для этого ей нужно передать целых 5 параметров:

• координаты верхней левой ячейки,
• на сколько строк нужно сместиться вниз — A,
• на сколько столбцов нужно перейти вправо — B,
• высота массива (строк) — C,
• ширина массива (столбцов) D.

Зеленым цветом на рисунке выделен новый диапазон. Если параметры C и D не указать, то будет возвращено содержимое единственной ячейки, в которую мы переместились, сделав несколько шагов вниз и вправо.

Традиционно точкой отсчета для функции СМЕЩ возьмем ячейку A1. Теперь нам нужно решить, на сколько позиций вниз и вправо нужно перейти, чтобы указать левый верхний угол нового перечня с моделями. Предположим, первоначально мы выбрали Ford.

На сколько шагов сместиться вниз? Применим функцию ПОИСКПОЗ, которая возвратит нам номер позиции первого вхождения «Ford».

Если первый раз нужное нам слово встретилось, к примеру, в 7-й позиции, то вычтем 1, чтобы получить количество шагов. То есть, начиная с первого значения, нужно сделать 6 шагов.

Третий параметр установим равным 1, так как нужно перейти на один шаг вправо из A в B. Мы находимся в начальной точке нашего диапазона. Теперь рассчитаем, на сколько ячеек вниз он будет продолжаться. Для этого подсчитаем, сколько раз «Ford» встречается в нашем перечне. Столько и будет значений вниз.

А теперь объединяем все это в СМЕЩ:

=СМЕЩ($A$1;ПОИСКПОЗ($D$1;$A$1:$A$22;0)-1;1;СЧЁТЕСЛИ($A$1:$A$22;$D$1);1)

Последняя единичка означает, что массив состоит из одной колонки.

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

Аналогичным образом можно создать и третий уровень зависимого выпадающего списка.

Еще полезная дополнительная информация: