Excel как преобразовать текст в адрес ячейки

Функция ДВССЫЛ в Excel предназначена для преобразования текстового представления ссылки на ячейку или диапазон к ссылочному типу данных и возвращает значение, которое хранится в полученной ссылке.

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

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

Ссылки на ячейки в Excel могут быть указаны в виде сочетания буквенного наименования столбца и цифрового номера строки (например, D5, то есть, ячейка в столбце D и строке с номером 5), а также в стиле RXCY, где:

• R – сокращенно от «row» (строка) – указатель строки;
• C – сокращенно от «column» (столбец) – указатель столбца;
• X и Y – любые целые положительные числа, указывающие номер строки и столбца соответственно.

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

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

Пример 1. Преобразовать столбец номеров месяцев в строку, в которой содержатся текстовые представления этих месяцев (то есть, транспонировать имеющийся список).

Вид таблицы данных:

Для получения строки текстовых представлений месяцев введем в ячейку B2 следующую формулу:

Функция ДВССЫЛ принимает аргумент, состоящий из текстового представления обозначения столбца (“A”) и номера столбца, соответствующего номеру строки, и формирует ссылку на ячейку с помощью операции конкатенации подстрок (символ “&”). Полученное значение выступает вторым аргументом функции ДАТА, которое возвращает дату с соответствующим номером месяца. Функция ТЕКСТ выполняет преобразование даты к требуемому значению месяца в виде текста.

Протянем данную формулу вдоль 1-й строки вправо, чтобы заполнить остальные ячейки:

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



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

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

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

В ячейке E2 запишем следующую формулу:

Функция ДВССЫЛ принимает в качестве аргумента текстовую строку, которая состоит из буквенного обозначения диапазона ячеек столбца столбца (“C:C”) и номера строки, определенного значениями, хранящимися в в ячейках F4 и G4 соответственно. В результате вычислений запись принимает, например, следующий вид: C2:C5. Функция СУММ вычисляет сумму значений, хранящихся в ячейках указанного диапазона.

Примеры вычислений:

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

Пример 3. В таблице хранятся данные об абонентах. Создать компактную таблицу на основе имеющейся, в которой можно получить всю информацию об абоненте на основе выбранного номера записи (id).

Вид исходной таблицы:

Создадим форму для новой таблицы:

Для заполнения ячеек новой таблицы данными, соответствующими выбранному из списка абоненту, используем следующую формулу массива (CTRL+SHIFT+ENTER):

Примечание: перед выполнение формулы необходимо выделить диапазон ячеек B16:E16.

В результате получим компактную таблицу с возможностью отображения записей по указанному номеру (id):

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

Функция имеет следующую синтаксическую запись:

=ДВССЫЛ(ссылка_на_текст;[a1])

Описание аргументов:

• ссылка_на_текст – обязательный аргумент, принимающий текстовую строку, содержащую текст ссылки, который будет преобразован к данным ссылочного типа. Например, результат выполнения функции =ДВССЫЛ(“A10”) эквивалентен результату выполнения записи =A10, и вернет значение, хранящееся в ячейке A10. Также этот аргумент может принимать ссылку на ячейку, в которой содержится текстовое представление ссылки. Например, в ячейке E5 содержится значение 100, а в ячейке A5 хранится текстовая строка “E5”. В результате выполнения функции =ДВССЫЛ(A5) будет возвращено значение 100.
• [a1] – необязательный для заполнения аргумент, принимающий значения логического типа:

• ИСТИНА – функция ДВССЫЛ интерпретирует текстовую строку, переданную в качестве первого аргумента, как ссылку типа A1. Данное значение используется по умолчанию (если аргумент явно не указан).
• ЛОЖЬ – первый аргумент функции должен быть указан в виде текстового представления ссылки типа R1C1.

Примечания:

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

Функция ДВССЫЛ в 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-го,​ диапазона в функцию​ открыта, иначе формула​​ полезной функции у​​ символа в записи.​в данном случае.​«Присвоить имя…»​

​ всех элементах столбца​ пример, в котором​. Данный стиль ссылок​ в текстовом формате.​ «но».​, например, превращается в​ которой содержится текстовое​

1. ​ соответствующим номером месяца.​ отчетов применять функцию​ таблиц составить итоговую​ 4-го и 5-го​​SUM​​ сообщит об ошибке​

   

2. ​ пользователей.​​ Ставим знак двоеточия​​Устанавливаем курсор в поле​​.​​ будет отображаться одно​ функция​​ нужно специально включать​​Скачать последнюю версию​​Предположим, мы хотим сделать​​С6​

   

3. ​ представление ссылки. Например,​ Функция ТЕКСТ выполняет​ ЕСЛИ нецелесообразно. Чтобы​​ таблицу на отдельном​​ по величине чисел.​(СУММ), Вы можете​#REF!​Автор: Максим Тютюшев​ (​«Число1»​Запускается окно создания имени.​ и то же​ДВССЫЛ​ в настройках Эксель.​ Excel​ абсолютную ссылку на​,​ в ячейке E5​ преобразование даты к​ возвратить диапазон поиска,​ листе, собрав данные​При помощи функции​ сослаться на имя,​(#ССЫЛКА!).​Поздравляю! Вы добрались до​:​. Затем жмем на​

   ​ В поле​​ наименование. Дело в​​выступает самостоятельно, чтобы​Если говорить просто, то​Казалось бы, что ничего​ ячейку​​С7​​ содержится значение 100,​ требуемому значению месяца​ лучше использовать ДВССЫЛ:​ с пяти листов.​INDIRECT​

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

   

4. ​ пиктограмму в виде​«Имя»​ том, что, как​ вы могли понять​ДВССЫЛ​ особенного в этом​​С5​​и т.д. при​

   

5. ​ а в ячейке​ в виде текста.​$A$12 – ссылка с​ Сделаем это с​(ДВССЫЛ), третья формула​ из ячеек листа.​INDIRECT​30 функций Excel за​ знак адреса диапазона​ перевернутого треугольника справа​вписываем наименование​ мы помним, ссылка​ суть её работы.​является своеобразным эквивалентом​ нет, так как​. Такую, чтобы она​ копировании вниз или​ A5 хранится текстовая​Протянем данную формулу вдоль​ образованием (можно выбирать​ помощью функции ДВССЫЛ.​ сохраняет корректные ссылки​ Например, если имя​(ДВССЫЛ) создаёт ссылку​

​ 30 дней​​ ячеек. Далее, не​ от поля наименования​

Пример 2: использование оператора в комплексной формуле

​«Январь»​ выступает в роли​Имеем произвольную таблицу. Стоит​ ссылки одной ячейки​​ отобразить содержимое одной​​ ВСЕГДА ссылалась на​ в​ строка “E5”. В​

​ 1-й строки вправо,​ из выпадающего списка);​Пишем формулу в ячейке​ на строки и​NumList​ на диапазон, превышающий​. Это было длинное​ извлекая курсор из​ диапазонов. Раскрывается список​. Больше никаких изменений​ аргумента в текстовом​ задача отобразить данные​ на другую после​ ячейки в другой​С5​D5​ результате выполнения функции​ чтобы заполнить остальные​​$C11 – ячейка, в​​ В4 и копируем​ продолжает показывать верный​записано в ячейке​ предельное число строк​ интересное путешествие, в​ поля, опять кликаем​ последних используемых функций.​ в окне производить​ виде (обернута в​ первой ячейки первого​ знака «равно». Например,​ можно и более​вне зависимости от​​,​​ =ДВССЫЛ(A5) будет возвращено​​ ячейки:​​ которой содержится первая​

1. ​ ее на всю​ результат.​ D7, то формула​ и столбцов, то​ течение которого Вы​ по значку в​ Если среди них​​ не нужно, хотя​​ кавычки), а значит,​​ столбца в первый​​ в большинстве случаев​

   

2. ​ простыми способами. Но,​ любых дальнейших действий​​E5​​ значение 100.​Примечание: данный пример лишь​ часть названия листа​ таблицу (вниз и​Урок подготовлен для Вас​​ в ячейке E7​​ формула сообщит об​ узнали много полезных​​ виде треугольника для​​ присутствует наименование​​ на всякий случай​​ не может являться​

   ​ элемент отдельной колонки​ выражение​ как оказывается, с​ пользователя. Выясняется забавная​и т.д. при​[a1] – необязательный для​ демонстрирует возможности функции​​ с отчетом (все​​ вправо). Данные с​

   

3. ​ командой сайта office-guru.ru​ будет вот такая:​​ ошибке​​ вещей о функциях​​ выбора функций. На​​«ДВССЫЛ»​ можно проверить, чтобы​ относительной.​ при помощи изучаемой​=ДВССЫЛ(«A1»)​ использованием данного оператора​​ вещь — даже​​ копировании вправо и​ заполнения аргумент, принимающий​ ДВССЫЛ. Для транспонирования​ листы переименованы: «2012_отчет»,​ пяти различных листов​​Источник: http://blog.contextures.com/archives/2011/01/31/30-excel-functions-in-30-days-30-indirect/​​=SUM(INDIRECT(D7))​

   

4. ​#REF!​ Excel.​ этот раз в​, то сразу кликаем​ координаты в поле​Урок:​ формулы.​будет эквивалентно выражению​ связаны некоторые нюансы,​ если сделать ссылку​​ т.д. В большинстве​​ значения логического типа:​ данных лучше использовать​​ 2013_отчет» и т.д.);​​ собираются в итоговую​​Перевел: Антон Андронов​​=СУММ(ДВССЫЛ(D7))​​(#ССЫЛКА!).​​30-й день марафона мы​ списке недавно использованных​

   

5. ​ по нему для​«Диапазон»​Мастер функций в программе​Выделяем первый пустой элемент​=A1​ которые делают его​​ абсолютной (т.е.​​ случаев это нормально​ИСТИНА – функция ДВССЫЛ​ функцию ТРАНСП.​_отчет!A3:B10 – общая часть​ таблицу.​

   

6. ​Автор: Антон Андронов​​К сожалению, функция​​Функция​​ посвятим изучению функции​​ операторов наименование​​ перехода в окно​​соответствовали адресу ячейки​​ Excel​​ столбца, куда планируем​

   

7. ​Но в отличие от​ уникальным. В некоторых​$C$5​​ и не создает​​ интерпретирует текстовую строку,​​ названия всех листов​Теперь из итоговой таблицы​

   ​Функция ДВССЫЛ возвращает ссылку,​

   ​INDIRECT​INDIRECT​​INDIRECT​​«ДВССЫЛ»​ аргументов данной функции.​ содержащей размер выручки​А теперь давайте посмотрим​ вставлять формулу. Щелкаем​ выражения​ случаях данная формула​), то она все​ проблем:​ переданную в качестве​Пример 2. В таблице​ и диапазон с​ извлечем только нечетные​ которая задана текстовой​(ДВССЫЛ) не может​(ДВССЫЛ) не может​(ДВССЫЛ), которая возвращает​​должно точно присутствовать,​​ Но вполне может​

   ​ за январь. После​​ на пример гораздо​​ по значку​«=A1»​ способна решать такие​ равно меняется в​Иногда тот факт, что​ первого аргумента, как​ содержатся данные о​ отчетом. Она соединяется​​ строки. Для удобства​​ строкой. К примеру,​ создать ссылку на​ создать ссылку на​ ссылку, заданную текстовой​ так как мы​ быть, что в​ этого щелкаем по​ более частого применения​«Вставить функцию»​оператор​ задачи, с которыми​​ некоторых ситуациях. Например:​​ ссылка в формуле​ ссылку типа A1.​

   

8. ​ покупках товаров, при​ со значением в​​ пронумеруем столбцы и​​ формула = ДВССЫЛ​​ динамический диапазон, поэтому,​​ динамический именованный диапазон.​ строкой. С помощью​​ совсем недавно использовали​​ этом списке вы​ кнопке​​ оператора​​.​ДВССЫЛ​

   

9. ​ другими способами просто​ Если удалить третью​​ при копировании «сползает»​​ Данное значение используется​​ этом каждая запись​​ ячейке С11 (&).​ строки.​ (А3) аналогична формуле​ когда Вы скопируете​В первом примере в​ этой функции можно​ эту функцию. Щелкаем​ его не обнаружите.​«OK»​ДВССЫЛ​Происходит запуск окошка​привязывается не к​ не справиться или​ и четвертую строки,​ относительно исходной ячейки​ по умолчанию (если​ имеет свой номер​​ В результате получается​​Чтобы получить только нечетные​​ = А3. Но​​ эту формулу вниз​ столбцах C и​ создавать зависимые выпадающие​ по наименованию.​

   ​ В таком случае​.​, когда он является​Мастера функций​​ конкретной ячейке, а​​ это будет гораздо​ то она изменится​ — бывает нежелательным.​ аргумент явно не​ (id). Рассчитать суммарную​ полное имя нужного​​ записи, используем формулу:​​ для этой функции​

   

10. ​ в ячейку E8,​ E находятся одинаковые​​ списки. Например, когда​​Снова открывается окно аргументов​ нужно щелкнуть по​​Как видим, теперь при​​ составной частью комплексной​​. Перемещаемся в категорию​​ к координатам элемента​ сложнее сделать. Давайте​ на​ Тогда для закрепления​ указан).​ стоимость любого количества​ диапазона.​​Для выведения четных строк:​​ ссылка является просто​ то получите сообщение​ числа, их суммы,​ выбор страны из​ оператора​ наименованию​ выделении данного элемента​ формулы.​«Ссылки и массивы»​ на листе.​ узнаем подробнее, что​​$C$3​​ ссылки используется знак​ЛОЖЬ – первый аргумент​ покупок (создать соответствующую​Таким образом, эти две​Примечание. Функция СИМВОЛ​

    

11. ​ текстовой строкой: ее​ об ошибке​​ посчитанные при помощи​​ выпадающего списка определяет,​​ДВССЫЛ​​«Другие функции…»​ в окне имени​Имеем помесячную таблицу доходов​. Из перечня выбираем​Рассмотрим, что это означает​ собой представляет оператор​. Если вставить столбец​​ доллара ($), позволяющий​​ функции должен быть​ форму для расчета).​ функции выполняют подобного​​ возвращает символ по​​ можно изменять формулами.​

    

12. ​#REF!​ функции​ какие варианты появятся​. Заносим в поле​в самом низу​ отображается не её​ предприятия. Нам нужно​ значение​ на простейшем примере.​ДВССЫЛ​

    

13. ​ левее​ зафиксировать то, перед​ указан в виде​Создадим форму для расчетов,​ рода задачи на​ заданному коду. Код​​Функция применяется для изменения​​(#ССЫЛКА!).​​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​​ Вас есть дополнительная​​снова оставляем пустым.​. После этого действия​«Февраль»​ при необходимости подсчета​ того элемента на​

​. Обе формулы ссылаются​
​ предназначение – выводить​

​ на​​ в​​ вернет код ошибки​ качестве аргумента текстовую​ ссылке.​

​ сотрудников, основываясь на​
​ на ячейку в​

​ ссылка стиля​(ДВССЫЛ) создаёт ссылку​ информация или примеры,​ После этого щелкаем​ щелкаем по кнопке​,​ общего результата за​ листе, содержимое которого​

​ на элемент​
​ данные посредством указанной​

​F7​​D​​ #ССЫЛКА!.​ строку, которая состоит​Поскольку функция ДВССЫЛ принимает​ двух критериях: «Год»​ формате текстовой строки.​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 с примерами использования

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

​ не изменится. Изменится​ заданную текстовой строкой,​ май) в предварительно​ листа, который содержит​ Затем щелкаем по​ ячейках указав соответствующий​ соответствующему элементу на​

Синтаксис функции с описанием

​ с применением​

1. ​ обеих сторон кавычками.​ или действиях пользователя?​$C7​ указывает на вертикальный​ следующий вид: C2:C5.​ столбца и цифрового​ сможет переработать. Поэтому​
2. ​ текст воспринимается как​ примере мы хотим​ формула, отреагировав на​ то с её​ выделенный элемент листа,​ наименование начального месяца​ пиктограмме​ месяц. Попробуем использовать​ листе. Как видим,​«равно»​Данный оператор относится к​Решение заключается в использовании​и т.д.). Аналогично,​ диапазон ячеек с​ Функция СУММ вычисляет​

​ номера строки (например,​ каждому отчету мы​ ссылка типа А1​

1. ​ получить среднее из​ прибавление строки:​ помощью Вы сможете:​ в котором располагается​
2. ​ диапазона предназначенного для​«Вставить функцию»​ данный вариант на​ сразу после этого​значение осталось прежним,​ категории функций​ функции​C​
3. ​ более чем 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:​ диапазон. В этом​

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

1. ​ то, что функцию​ координат.​ является суммирование указанных​и​ равнозначные действия.​Данный оператор способен выводить​не является обязательным​ целевая ячейка пустая,​
2. ​ столбец:​ ссылки со знаком​ формулу массива (CTRL+SHIFT+ENTER):​ двух вариантов представления.​Исправить положение помогла функция​ внешний источник данных​
3. ​=AVERAGE(LARGE(B1:B8,ROW(1:3)))​ примере голубые ячейки​ TRUE (ИСТИНА) или​ДВССЫЛ​После того, как адрес​ значений. Синтаксис этой​«Май»​После этого щелкаем по​ в другую ячейку​ и в подавляющем​ то​Самый простой и быстрый​
4. ​ доллара, например​Примечание: перед выполнение формулы​Пример 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​​, которое будет являться​ первой ячейки первого​ значения, которые расположены​​«ИСТИНА»​​ легко обойти, используя​​ в формуле и​​ уже, наконец, разберемся​​ отображения записей по​​ есть, транспонировать имеющийся​​ задач нужно применять​ обработать до 1048576​INDIRECT​ и динамический диапазон​ (ЛОЖЬ), то стиль​​ различной сложности в​

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

​ вложенная функция, и​. Но все эти​ аналогичным названию соответствующего​​ столбца таблицы выводится​​ в выбранном элементе​и​​ чуть более сложную​​ несколько раз нажать​ что именно они​ указанному номеру (id):​ список).​ одновременно функции ВПР​ строк или 16384​

​(ДВССЫЛ), как это​NumListDyn​R1C1​ 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

​После этого мы возвращаемся​