Excel содержимое ячейки по ссылке - Word и Excel - помощь в работе с программами

Содержание

Excel значение по адресу ячейки
30 функций Excel за 30 дней: АДРЕС (ADDRESS)
Функция 20: ADDRESS (АДРЕС)
Как можно использовать функцию ADDRESS (АДРЕС)?
Синтаксис ADDRESS (АДРЕС)
Ловушки ADDRESS (АДРЕС)
Пример 1: Получаем адрес ячейки по номеру строки и столбца
Абсолютная или относительная
A1 или R1C1
Название листа
Пример 2: Находим значение ячейки, используя номер строки и столбца
Пример 3: Возвращаем адрес ячейки с максимальным значением
ИНДЕКС (функция ИНДЕКС)
Форма массива
Описание
Синтаксис
Замечания
Примеры
Пример 1
Пример 2
Ссылочная форма
Описание
Синтаксис
Замечания
Примеры
30 функций Excel за 30 дней: ЯЧЕЙКА (CELL)
Функция 11: CELL (ЯЧЕЙКА)
Как можно использовать функцию CELL (ЯЧЕЙКА)?
Синтаксис CELL (ЯЧЕЙКА)
Ловушки функции CELL (ЯЧЕЙКА)
Пример 1: Формат числа в ячейке
Пример 2: Название листа
Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка
Примеры функции АДРЕС для получения адреса ячейки листа Excel
Функция АДРЕС в Excel: описание особенностей синтаксиса
Примеры использования функции АДРЕС в Excel
Как получить адрес ссылки на ячейку Excel?
Адрес по номерам строк и столбцов листа Excel в стиле R1C1
поиск адреса ячейки по значению
Узнать адрес ячейки по отобранному формулой значению
Как автоматически задать значение ячейки основываясь на данных других ячеек?

Excel значение по адресу ячейки

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

Смотрите также=АДРЕС(ПОИСКПОЗ(F4&МАКС(($A$2:$A$15=F4)*$B$2:$B$15);$A$2:$A$15&$B$2:$B$15;0)+1;2) — формула самом конце. Повторюсь:Ромич «»Так выглядит абсолютный тип записью; которое соответствует номеру файла и название левой верхней ячейки диапазона C1:C11. противном случае функцияЕсли в качестве аргумента чтобы видеть все ИНДЕКС является константной возвращаемый функцией

– если TRUEВчера в марафоне массива. неважно в каком: ну,дык, если вРомич ссылок по строкам«Курсы» — название листа, столбца, в котором листа. в ссылке.216 ИНДЕКС возвращает значение «ссылка» используется несмежный

данные. массива, используйте формуADDRESS (ИСТИНА) или вообще30 функций Excel заZorge столбце мы кликнем А2 вводить новое: а если не и столбцам при

Функция 20: ADDRESS (АДРЕС)

на котором находится расположена искомая ячейка;=CELL(«filename»,B2)filename=СУММ(B2:ИНДЕКС(A2:C6;5;2)) ошибки #ССЫЛКА!. Если диапазон, его необходимоДанные массива.(АДРЕС), как аргумент не указано, функция 30 дней: Извините, что пропал. на гиперссылку - значение,то предыдущее стоит в соседнюю ячейку? использовании стиля R1C1.

Как можно использовать функцию ADDRESS (АДРЕС)?

таблица с искомой[тип_ссылки] – число из=ЯЧЕЙКА(«имяфайла»;B2)(имяфайла) – имяСумма значений из диапазона, аргументы «номер_строки» и заключить в скобки

ДанныеИНДЕКС(массив; номер_строки; [номер_столбца]) для
возвращает ссылку вмы находили элементыПо хорошему, адрес
она всегда приведёт стереть?и,соответственно, адреса этого

Синтаксис ADDRESS (АДРЕС)

Юрий МРомич ячейкой. диапазона от 1

Из полученного результата при
файла и полный

 начинающегося с ячейки «номер_столбца» опущены, функцияЕсли каждая область вЯблокиАргументы функции ИНДЕКС описаныINDIRECT стиле массива при помощи ячейки мне нужен, к нужному адресу. имени тоже стираются?или: Тогда не Offset(0,: Народ, прошу прощенияДля расчета стоимости в до 4, соответствующее помощи других функций
 путь. B2 и заканчивающегося ИНДЕКС возвращает область ссылке содержит толькоЛимоны ниже.(ДВССЫЛ). Мы изучимA1 функции для того, чтобыZorge
 я что-то не 1) за назойливость, но рублях используем формулу: одному из типов Вы можете извлечьformat пересечением пятой строки

Ловушки ADDRESS (АДРЕС)

в аргументе «ссылка», одну строку илиБананыМассив функцию, если FALSE (ЛОЖЬ),MATCH можно было выбрать: И снова добрый так понял?Владимир вот интересно стало, =B3*ДВССЫЛ(D3). возвращаемой ссылки на

Пример 1: Получаем адрес ячейки по номеру строки и столбца

название листа. В(формат) – числовой и второго столбца заданную аргументом «номер_области». один столбец, аргументГруши — обязательный аргумент. ДиапазонINDIRECT то в стиле(ПОИСКПОЗ) и обнаружили, значение из другого день, товарищи бойцы.Юрий М: Пока я телился.

можно ли по
Функция ДВССЫЛ необходима для

Абсолютная или относительная

ячейку: формуле ниже, используя формат ячейки. диапазона A2:A6, т. е.Результатом вычисления функции ИНДЕКС «номер_строки» или «номер_столбца»

Формула ячеек или константа(ДВССЫЛ) позже вR1C1 что она отлично столбца, соответствующее тойИ снова проблема,: Не так. Ваш

=АДРЕС(МАКС(ЕСЛИ(Лист1!$A$1:$G$20=A2;(СТРОКА(Лист1!$1:$20))));МАКС(ЕСЛИ(Лист1!$A$1:$G$20=A2;СТОЛБЕЦ(Лист1!$A:$G)));4)
имени(значению) получать адрес,

A1 или R1C1

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

.
работает в команде

Название листа

самой максимальной величине. выеденного яйца не последний файл. РанееРомич а лучше даже хранимого в ячейке, например — $A$4MID

(скобки) – возвращает
диапазона B2:B6.

Пример 2: Находим значение ячейки, используя номер строки и столбца

интерпретируется в качестве Например, для ссылкиРезультатЕсли массив содержит только30 функций Excel заsheet с другими функциями,Вопрос мне показался стоящая. для АБВ нашлись: вместо 1 - ссылку на эту на которую указываетабсолютная только на строку,(ПСТР) и 1, если форматированием2,42 таковой другими функциями. на единственную строку=ИНДЕКС(A2:B3;2;2) одну строку или 30 дней

_text
такими как

простым потому, чтоИмею массив данных, три адреса. Затем число шагов до ячейку? Ну,например, на ссылка. В результате например — A$4;FIND ячейки предусмотрено отображениеК началу страницы В зависимости от нужно использовать формулуЗначение ячейки на пересечении один столбец, соответствующий

VLOOKUP в VB, если из которых отбираю в А3 ввели нужного столбца? одном листе есть

вычислений для остальных
абсолютная только на столбец,

(НАЙТИ), находим квадратные положительных или всехВ 4-ый день марафона формулы значение, возвращаемое

Пример 3: Возвращаем адрес ячейки с максимальным значением

ИНДЕКС(ссылка,,номер_столбца). второй строки и аргумент «номер_строки» или=INDIRECT(ADDRESS(C2,C3)) листа может быть(ВПР) и я назначаю переменной

уникальные значения из новое имя. ВЮрий М ячейка(А2) со значением товаров получим следующую

например — $A4;
скобки и возвращаем

чисел в круглых30 функций Excel за функцией ИНДЕКС, можетНомер_строки второго столбца в «номер_столбца» не является=ДВССЫЛ(АДРЕС(C2;C3)) указано, если ВыINDEX какую-либо ячейку я повторяющихся и формулой

В3 отобразился новый
: Точно: (0, 5)

123. А во таблицу:
относительная на всю ячейку,
32 символа, которые

скобках; во всех

ИНДЕКС (функция ИНДЕКС)

30 дней использоваться как ссылка — обязательный аргумент. Номер диапазоне A2:B3.

желаете видеть его(ИНДЕКС). могу посмотреть и МАКС нахожу из адрес. В В4

на пять столбцов втором листе приПример 2. В таблице например A4.

Форма массива

Описание

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

строки в диапазоне,ГрушиЕсли массив содержит большеINDIRECT

Синтаксис

в возвращаемом функцией

20-й день нашего марафона её адрес и

 них наибольшие. Это — осталось значение правее в той вводе этого значения,

содержатся данные о[a1] – логическое значение, (длина названия листа 0 (ноль). сведения о рабочей Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))

заданном аргументом «ссылка»,=ИНДЕКС(A2:B3;2;1) одной строки и(ДВССЫЛ) может работать результате. мы посвятим изучению её значение вне к тому, что для ячейки А2. же строке (ноль).

 например, в столбец цене товаров, отсортированные определяющее один из ограничена 31 символом).prefix среде, используя функцию эквивалентна формуле ЯЧЕЙКА(«ширина»;B1). из которого требуется

Значение ячейки на пересечении одного столбца, а и без функцииФункция функции зависимости от того уникальных значений много Если мы его

Замечания

Ромич ИМЯ, в соседнем в порядке возрастания двух типов ссылок:=MID(CELL(«filename»,C3),FIND(«]»,CELL(«filename»,C3))+1,32)(префикс) – текстовоеINFO

Функция ЯЧЕЙКА использует возвратить ссылку. второй строки и из аргументов «номер_строки»ADDRESSADDRESSADDRESS сам ли я и к каждому очистим — список: Юрий, огромное спасибо! столбце получаем ссылку стоимости. Необходимо получить A1 либо R1C1;=ПСТР(ЯЧЕЙКА(«имяфайла»;C3);НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»;C3))+1;32) значение, соответствующее префиксу(ИНФОРМ), – такие значение, возвращаемое функцией

Номер_столбца первого столбца в и «номер_столбца» задан(АДРЕС). Вот как

(АДРЕС) возвращает лишь(АДРЕС). Она возвращает назначаю эту ячейку из них выбирается адресов для А2 как и остальным на Лист1!А2

Примеры

Пример 1

ссылки на ячейки[имя_листа] – текстовое значение,Вместо того, чтобы вводить метки ячейки (показывает как версия Excel ИНДЕКС, как ссылку.

— необязательный аргумент. Номер диапазоне A2:B3. только один, функция можно, используя оператор адрес ячейки в адрес ячейки в или аона является максимальное из первоначального будет неполным. Поэтому форумчанам — сKuklP с минимальной и которое определяет имя

и режим пересчёта.

С другой стороны,

столбца в диапазоне,

 ИНДЕКС возвращает массив,

 виде текстовой строки.

текстовом формате, используя

результатом хитрых манипуляций. массива. и предлагаю в этим сайтом постигать

: Ромич, а Если(..ВПР)?

максимальной стоимостью товаров

листа в документеinfo_typeprotectОдиннадцатый день марафона мы

такая формула, как

Пример 2

заданном аргументом «ссылка»,В этом примере функция состоящий из целой& Если Вам нужно номер строки и За сим прилагаю

Собственно вопрос: Каким строку все адреса excel и проще, Ща наши киты соответственно. Excel. Используется для(тип_сведений) в функцию(защита) — 0 посвятим изучению функции 2*ИНДЕКС(A1:B2;1;2), преобразует значение, из которого требуется ИНДЕКС используется в

 строки или целого

«, слепить нужный адрес

 значение ячейки, используйте

столбца. Нужен ли файл с примером образом можно определить

для одного значения. и интереснее подключатся, думаю иИсходная таблица имеет следующий создания внешних ссылок.CELL = ячейка неCELL возвращаемое функцией ИНДЕКС,

формуле массива для столбца аргумента «массив». в стиле её в качестве

 более обширным. адрес ячейки, данные Тогда и путаницыи еще в Индекс порулит. Неисповедимы вид:Примечания:(ЯЧЕЙКА) в виде заблокирована, 1 =(ЯЧЕЙКА), которая сообщит в число вНомер_области поиска значений двухНомер_строкиR1C1

Ссылочная форма

Описание

Можно ли сделатьБлагодарю за оперативность. из которой мне не будет никакой.Теперь завершение. можно ли пути формулистов:-) ТолькоДля получения ссылки наСсылки типа R1C1 используются текстовой строки, Вы

Синтаксис

информацию о форматировании ячейке B1.

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

INDIRECT то же самоеvikttur показывает формула? Адрес,

понятно объяснил? сделать получаемый адрес Ким жаловался, что ячейку с минимальной для цифрового обозначения можете указать ссылкуrow ячейки, её содержимомПримечание: в аргументе «ссылка»,

 массиве с диапазоном Выбирает строку в получить значение ячейки:(ДВССЫЛ) или примените с помощью других: Например, номер договора:

 Индекс и ЯчейкаРомич гиперссылкой? формулисты не в стоимостью товара используем столбцов и строк.

 на ячейку, содержащую(строка) – номер и расположении. Она Функция ЯЧЕЙКА недоступна из которого требуется 2 x 2. массиве, из которой=INDIRECT(«R»&C2&»C»&C3,FALSE) одну из альтернативных функций?=ИНДЕКС($D$2:$D$15;ПОИСКПОЗ(МАКС(СМЕЩ(B1;ПОИСКПОЗ(F4;$A$2:$A$15;0);0;СЧЁТЕСЛИ($A$2:$A$15;F4)));$B$2:$B$15;)) действуют диаметрально проивоположным: нет.Имя по задумкеЮрий М фаворе. формулу: Для возвращения ссылок корректные значения. В строки ячейки. работает аналогично функции в Excel Web возвратить значение ячейкиВ этом случае нужно требуется возвратить значение.=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ) формул, показанных вДавайте обратимся к сведениямСМЕЩ() лучше заменить образом. вводится ТОЛЬКО в: в завершение. можноКи-им! Ваш выход.Функция АДРЕС принимает следующие

такого типа в этом примере вtypeINFO App. на пересечении строки выбрать любые две Если аргумент «номер_строки»Функция

Замечания

примере 2. по функции на ИНДЕКС (сообщениеЗаранее благодарю и ячейку А2. Ввел,посмотрел(в ли сделать получаемый Я за Вас параметры: качестве параметра a1 ячейке B4 находится(тип) – тип(ИНФОРМ), т.е. имеетСкопируйте образец данных из и столбца, заданных ячейки по вертикали, опущен, аргумент «номер_столбца»

INDEXПри помощи функцииADDRESS 29.07.2011, 18:52) удачной пятницы оригинальном файле еще адрес гиперссылкой?

переживаю.число, соответствующее номеру строки должно быть явно выпадающий список, а данных в ячейке список значений, которые следующей таблицы и аргументами «номер_строки» и например A1:A2, вставить является обязательным.(ИНДЕКС) также можетADDRESS

(АДРЕС) и изучимЕсли возможны совпаденияМихаил С. туча параметров выводится),Насчёт завершения -Ромич с минимальным значением указано логическое значение вместо аргумента (пустая, текст, другое). можно ввести в вставьте их в «номер_столбца». Первая введенная в первую ячейкуНомер_столбца вернуть значение ячейки,(АДРЕС) Вы можете примеры работы с максимумов для разных: Вроде уже около

 стер. Если надо,ввел сомнительно: начиналось с: впр не покатит цены (функция МИН

Примеры

ЛОЖЬ или соответствующееinfo_typewidth функцию, но содержит ячейку A1 нового или выделенная область формулу =ИНДЕКС(<1;2:3;4>;0;2) и — необязательный аргумент. если указан номер получить адрес ячейки ней. Если у названий, диапазон ИНДЕКС($D$2:$D$15;. 40 ответов, а

 соседнего столбца. Пока

выполняет поиск минимального

числовое значение 0.

(тип_сведений) указана ссылка

листа Excel. Чтобы

имеет номер 1, вторая —

нажать клавиши CTRL+SHIFT+ВВОД.

Выбирает столбец в

строки и столбца:

Вас есть дополнительная

тоже нужно определять

И еще вопрос.Можно

значениями разбросаны по

значения и возвращает

Стиль ссылок в Excel

отобразить результаты формул,

Если ввести формулу

массиве, из которого

 используя номер строки

 информация или примеры,

ли указать промежуткиЮрий М листу, а-ля А2, его, функция ПОИСКПОЗ

может быть изменен

Есть несколько моментов, заИтак, давайте рассмотрим информацию выделите их и Если аргумент «номер_области» как формулу массива,

требуется возвратить значение.

и столбца. Если пожалуйста, делитесь имиZorgeКто данные за

поиска(например, чтоб в

В5, С1 находит позицию ячейки, путем установки/снятия флажкаreference которыми нужно внимательно и примеры по нажмите клавишу F2, опущен, в функции

Excel автоматически вставит

Если аргумент «номер_столбца»

30 функций Excel за 30 дней: ЯЧЕЙКА (CELL)

1:5000 Вы введёте только в комментариях.: Спасибо всем огромное. Вас придумывать будет? столбце В отображались вопросов больше нетKuklP содержащей минимальное значение пункта меню «Стиль

(ссылка) ссылается на следить, используя функцию функции а затем — клавишу ИНДЕКС используется область 1. ее во вторую опущен, аргумент «номер_строки»– это первые эти два аргумента,ФункцияЗаработало.vikttur адреса в промежутках 🙂 Смотрите с: Ромич, честно каюсь

цены. К полученному ссылок R1C1», который ячейку B2.CELLCELL ВВОД. При необходимости Все указанные здесь ячейку. является обязательным. 5000 строк листа

Функция 11: CELL (ЯЧЕЙКА)

результатом будет абсолютныйADDRESSk1ava: И Вам удачных А2:К200 и АВ2:АХ200, гиперссылкой

Как можно использовать функцию CELL (ЯЧЕЙКА)?

— я не значению добавлено 2, находится в «ФайлКогда выбрано значение(ЯЧЕЙКА):

(ЯЧЕЙКА). Если у
измените ширину столбцов,
области должны находиться

Синтаксис CELL (ЯЧЕЙКА)

ФормулаЕсли используются оба аргумента Excel. адрес, записанный в

(АДРЕС) возвращает ссылку
: К примеру в

 выходных. Может, за а в столбцеGuest формулист. Как у

 поскольку ПОИСКПОЗ осуществляет – Параметры –protectЕсли аргумент Вас есть что чтобы видеть все на одном листе.
Описание — и «номер_строки»,В этом примере мы стиле ссылок на ячейку в столбце A в
 два дня справитесь С — адреса: Прекрайне благодарен! них в избушке поиск относительно диапазона Формулы – Работа(защита): результат равенreference
 добавить к нашим данные. Если указать области,Результат
 и «номер_столбца», — найдём ячейку сA1 виде текста, основываясь
 одном ряду значение с задачей :) промежутка L2:AA200)? Ну
Владимир побываю — в выбранных ячеек. с Формулами». 1, если ячейка(ссылка) опущен, то примерам и сведениям,Фрукты находящиеся на разных
=ИНДЕКС(<1;2 функция ИНДЕКС возвращает максимальным значением и. на номере строки
 одной ячейки xИли все-таки пример или что-то в: Как понять это шоке! Они там
2 – номер столбца,Если требуется ссылка на заблокирована, или 0
 результат возвращается для пожалуйста, делитесь этимЦена листах, произойдет ошибка
: значение ячейки на используем функцию

Ловушки функции CELL (ЯЧЕЙКА)

=ADDRESS($C$2,$C$3) и столбца. Она , значение другой покажете? этом роде

слово «прекрайне»? Бесконечно.  такое мутят, что в котором находится ячейку, которая находится (ноль), если не последней изменённой ячейки. в комментариях.Количество #ЗНАЧ! Если необходимо3;4>;0;2) пересечении указанных строкиADDRESS=АДРЕС($C$2;$C$3) может возвращать абсолютный y.Михаил С.
Юрий М . или крайность, в не пойму сам: искомая ячейка. в другом листе заблокирована.
Чтобы быть уверенным,ФункцияЯблоки использовать диапазоны, находящиесяЗначение ячейки на пересечении и столбца.(АДРЕС), чтобы получитьЕсли не указывать значение

Пример 1: Формат числа в ячейке

или относительный адресЗадача в том: И зачем Вам: Если нужно контролировать хорошем смысле слова.  то ли яАналогичным способом получаем ссылку данного документа Excel,=CELL(B4,B2) что результат получилсяCELL0,69 ₽ на разных листах, первой строки и

Если указать в качестве
её адрес.

Пример 2: Название листа

аргумента в стиле ссылок что бы дать именно адрес? только одну ячейкуЮрий М дурак(а это скорее!), на ячейку с

полезно использовать параметр
=ЯЧЕЙКА(B4;B2)

именно тот, что(ЯЧЕЙКА) показывает информацию40 рекомендуется применить функцию второго столбца в аргумента «номер_строки» илиФункцияabs_numA1 еще одной ячейкиZorge — зачем тогда: Через край, наверное. то ли они максимальной ценой товара.

[имя_листа], который принимает
Когда выбрано значение

Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка

Вам нужен, ссылку о форматировании, содержанииБананы ИНДЕКС в форме массиве. Массив содержит «номер_столбца» значение 0MAX(тип_ссылки) в формуле,или значение z: Благодарю за оперативность. обрабатываем диапазон? СформулируйтеРомич иноПланетяне. Ждем наших В результате получим: текстовое значение, соответствующееwidth желательно всегда указывать. и расположении ячейки0,34 ₽ массива и использовать

значения 1 и (ноль), функция ИНДЕКС(МАКС) находит максимальное то результатом будетR1C1Такое возможно вЯ так понимаю,

уже условие ЗАНОВО
: См. значение префикса

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

число в столбце абсолютная ссылка.
. К тому же
exel?

Примеры функции АДРЕС для получения адреса ячейки листа Excel

и ОДНОЗНАЧНО. ПРЕ- . макросом можно. содержится ячейка, данные например «Лист7». ширину столбца в даже ячейку, вФункцияЛимоны

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

Функция АДРЕС в Excel: описание особенностей синтаксиса

По второму вопросуВ общем, «через

из которой используются формате целого числа.

которой содержится сама

CELL0,55 ₽ массив. Например, определить 3 и 4
или целой строки=MAX(C3:C8) виде относительной ссылки, быть включено имя
как реализовать. а в возможности/невозможности — зачем распределять край» — это формулисты запинают:-). в другом программном

Пример 1. В таблице Единицей измерения в
функция(ЯЧЕЙКА) может сообщить
15 используемый диапазон можно
во второй строке. соответственно. Чтобы использовать

=МАКС(C3:C8) можно подставить в листа.Спасибо заранее за
выполнения. найденные значения-адреса по близкоП.С. 11979 - продукте. Для обеспечения

Excel содержится ячейка,

этом случае являетсяCELL о ячейке информациюАпельсины с помощью функции2 значения, возвращенные какДалее в игру вступает качестве аргументаФункция
любые ответы. Вот примерно такой разным столбцам (этоРомич это две пары? совместимости необходимо предоставить отображающая динамически изменяемые ширина одного символа(ЯЧЕЙКА).
следующего содержания:0,25 ₽ ВЫБОР.Значение ячейки на пересечении массив, введите функцию функцияabs_numADDRESSHaken

пример получился.

Примеры использования функции АДРЕС в Excel

сложнее), если в: в завершение. можно Никогда не играл ссылку на нее данные в зависимости при стандартном размереПри работе с функциейЧисловой формат ячейки.25Например, если аргумент «ссылка» второй строки и ИНДЕКС как формула

ADDRESS(тип_ссылки) значение(АДРЕС) может возвратить

: Встаете на ячейку,Нужен этот адрес, ячейках ГИПЕРССЫЛКИ? Неважно ли сделать получаемый в игры, это

в виде R1C1. от определенных условий. шрифта.CELLНазвание листа.Груши определяет ячейки (A1:B4,D1:E4,G1:H4), второго столбца в массива в горизонтальный

(АДРЕС) в сочетании

4 адрес ячейки или пишете
для работы с из какого столбца
адрес гиперссылкой? уже здесь подхватил.
Исходная таблица имеет следующий Для работы сУрок подготовлен для Вас
(ЯЧЕЙКА), иногда необходимоВыравнивание или ширину столбца.0,59 ₽ номер области 1

массиве, указанном выше. диапазон ячеек для с

. работать в сочетании=A1+A2 первоначальным массивом. Например, сработает гиперссылка?Насчёт завершения - От «Казанский». вид:

Как получить адрес ссылки на ячейку Excel?

актуальными данными в командой сайта office-guru.ru делать пересчёт листа,Функция40 соответствует диапазону A1:B4,4 строки и вMATCH

=ADDRESS($C$2,$C$3,4) с другими функциями,

ну или другую если яблоки иРомич сомнительно: начиналось с

Юрий МДля получения ссылки на

таблице, которая находитсяИсточник: http://blog.contextures.com/archives/2011/01/12/30-excel-functions-in-30-days-11-cell/ чтобы обновить результат,CELLМиндаль номер области 2Примечание: вертикальный — для столбца.(ПОИСКПОЗ), которая находит=АДРЕС($C$2;$C$3;4) чтобы: формулу, по которой груши от разных
: Хорошо,сейчас попробую. соседнего столбца. Пока: Вот так, например.

ячейку B6 используем на другом листеПеревел: Антон Андронов который возвращает функция.

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

(ЯЧЕЙКА) имеет вот2,80 ₽ — диапазону D1:E4, Это формула массива, которую Чтобы ввести формулу номер строки, иЧтобы задать стиль ссылокПолучить адрес ячейки, зная

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

Исходная таблица.» gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=» data-src=»https://img.my-excel.ru/excel-v-jachejku-nazvanie-lista_16_1.png» >

Есть таблица, при накопите вопросы.kim

следующую формулу: =АДРЕС(6;2;1;0).

документа требуется получитьАвтор: Антон Андронов
Если в качестве аргумента такой синтаксис:10
а номер области нужно вводить с
массива, нажмите сочетаниеCOLUMN

номер строки и третьей ячейки. MCH вводе в первуюЮрий, а что

: Дык автор конкретноАргументы функции: ссылку на даннуюФункция АДРЕС возвращает адресinfo_typeCELL(info_type,reference)Кешью 3 — диапазону помощью клавиш CTRL+SHIFT+ВВОД. клавиш CTRL+SHIFT+ВВОД.(СТОЛБЕЦ), которая определяет, вместо принятого по столбца.

Serge: И зачем Вам ячейку которой Имени нужно дописать в

поиск адреса ячейки по значению

 формулой вроде не6 – номер строки ячейку. определенной ячейки (текстовое(тип_сведений) выбрано значениеЯЧЕЙКА(тип_сведений;ссылка)3,55 ₽ G1:H4. Excel автоматически заключитПримечание: номер столбца. умолчанию стиляНайти значение ячейки, зная: k1ava, а теперь всетаки адрес? что мы должны получить этом макросе,чтобы он просил.

 искомой ячейки;На листе «Курсы» создана значение), на которуюfilenameinfo_type16После того как с формулу в фигурные В Excel Web
=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))A1 номер строки и

 можно ещё раз Вы с ним адреса расположения(гиперссылки) на выдавал не одинНу да ладно,2 – номер столбца,

 таблица с актуальными указывают номера столбца(имяфайла), а книга(тип_сведений) — этоАрахис помощью аргументов «ссылка» скобки <>. Если App невозможно создавать=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2)), Вы должны указать столбца. и по-русски? будете делать? схеме. адрес, а несколько,списком(если,например,имя вот формульный вариант в котором содержится
курсами валют: и строки. К Excel ещё не один из вариантов1,25 ₽ и «номер_области» выбран

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

Урок подготовлен для Вас значение FALSE (ЛОЖЬ)Возвратить адрес ячейки сПонятно и с
Михаил С.По поводу второй

 встречается неоднократно).Ромич ячейка;На отдельном листе «Цены» примеру, в результате

 сохранена, то результатом аргумента:20 диапазон, с помощью их вручную, ExcelАргументы «номер_строки» и «номер_столбца» командой сайта office-guru.ru для аргумента самым большим значением. примером в Excel?
: Или так части. Дело вЮрий М: Юрий, а можно
1 – тип ссылки создана таблица с выполнения функции =АДРЕС(5;7)
будет пустая строка.
addressГрецкие орехи

 аргументов «номер_строки» и отобразит формулу как должны указывать на

GuestМихаил С.
том, что как

: А каким образом поподробнее об этом (абсолютная); товарами, отображающая стоимость

 будет выведено значениеСо значением(адрес) – ссылка1,75 ₽ «номер_столбца» выбирается конкретная

 текст. Если при ячейку внутри заданногоПеревел: Антон Андронов.ADDRESS: Расчитать не получится.: Блин, не свой

раз-таки важно, из и куда выводить макросе?что там к0 – указание на

 в долларах США $G$5.format на первую ячейку
12 ячейка: номер строки вводе формулы не массива; в противном

Автор: Антон Андронов=ADDRESS($C$2,$C$3,1,FALSE)(АДРЕС) имеет вотНадо что бы файл сохранил .

 какого столбца работает этот список?

 чему? стиль R1C1. (USD):Примечание: наличие символов «$»(формат) Вы можете

 1 соответствует первой нажать клавиши CTRL+SHIFT+ВВОД, случае функция ИНДЕКС
Функция ИНДЕКС возвращает значение=АДРЕС($C$2;$C$3;1;ЛОЖЬ) такой синтаксис:

 значение «Да» напротив ;) гиперссылка — идетРомич
Юрий МВ результате получим ссылку:В ячейку D3 поместим в адресе ячейки

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

 или ссылку наПоследний аргумент – этоADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text]) Питания ставилось только

vikttur распределение по зонам.Но

: ну,например,так: Хм, подробнее. МакросПрактическое применение функции АДРЕС: ссылку на ячейку $G$5 свидетельствует о

CELL(ссылка) в текстовомРезультат столбца 1 — #ЗНАЧ! #ССЫЛКА!. значение из таблицы имя листа. ЕслиАДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа]) если в полях
: Нелетучая формула извлечения это не суть

А еще был отслеживает изменения в Поиск значения в таблицы, находящейся на том, что ссылка(ЯЧЕЙКА), чтобы показать формате.=ИНДЕКС(A2:C6;2;3)

 его первому столбцуК началу страницыВ этих примерах функция или диапазона. Вам необходимо этоabs_num «должность» и «График» МАКС: важно. Есть другой бы рад, если диапазоне А2:А1000, запускает диапазоне таблицы Excel листе «Курсы», в на данную ячейку числовой формат ячейки.colПересечение второй строки и и т. д.Возвращает ссылку на ячейку, ИНДЕКС используется дляФункцию ИНДЕКС можно использовать

 имя в полученном(тип_ссылки) – если стоят значения «Водитель»=МАКС(ИНДЕКС($B$2:$B$15;ПОИСКПОЗ(F4;$A$2:$A$15;)):ИНДЕКС($B$2:$B$15;ПОИСКПОЗ(F4;$A$2:$A$15;)+СЧЁТЕСЛИ(A$2:A$15;F4)-1)) вариант тут,если это бы Вы оставили по этому изменению по столбцам и

которой содержится информация является абсолютной, то Например, если ячейка(столбец) – номер третьего столбца в Ссылка, возвращаемая функцией расположенную на пересечении поиска значения ячейки, двумя способами: результате, укажите его равно

 и «Пятидневка», вИ все-таки, для слишком запарно в тексте программы поиск. Если находит, строкам. о курсе валюты
есть не меняется B7 имеет формат столбца ячейки в диапазоне A2:C6, т. е. ИНДЕКС, указывает на указанной строки и находящейся на пересеченииЕсли вы хотите возвращать

 в качестве аргумента1
противном случае поле чего адрес? ПоверьтеЮрий М комментарии) то в соседнююПримечание: при использовании стиля
USD. Для этого при копировании данных.General аргументе содержимое ячейки C3. пересечение строки «номер_строки» указанного столбца. Если заданных строки и значение указанной ячейкиsheet_textили вообще не

 остается пустым. опыту: АДРЕС() практически
: Уточняемся:Юрий М ячейку записывает адрес
R1C1 запись абсолютной введем следующую формулу:Функция АДРЕС имеет следующую
(Общий), то результатомreference
38
и столбца «номер_столбца». ссылка составлена из столбца. или массива ячеек,(имя_листа). указано, то функцияGuest не используется, эта1. Значение всегда

Узнать адрес ячейки по отобранному формулой значению

: А как быть, найденной ячейки, если ссылки не содержит
=АДРЕС(3;2;1;1;»Курсы»). синтаксическую запись: формулы будет
(cсылка).=ИНДЕКС((A1:C6;A8:C11);2;2;2)Если указать в качестве несмежных выделенных диапазонов,Скопируйте образец данных из см. раздел Форма=ADDRESS($C$2,$C$3,1,TRUE,»Ex02″) возвратит абсолютный адрес: Не прикрепилось )) функция покажет адрес, будет вводиться ТОЛЬКО если в А2
нет — орёт. знака «$». ЧтобыЗначение параметров:=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])GсolorПересечение второй строки и аргумента «номер_строки» или

можно указать, какой следующей таблицы и

 массива.=АДРЕС($C$2;$C$3;1;ИСТИНА;»Ex02″) ($A$1). Чтобы получить Прикрепленные файлы post_114193.jpg но в тексте,
в ячейку А2. введём новое имя?

Кстати, нужно добавить отличать абсолютные и3 – номер строки,Первые два аргумента данной:
(цвет) – возвращается второго столбца во

 «номер_столбца» значение 0 из них требуется вставьте их в

Если требуется возвращать ссылкуФункция
относительный адрес (A1), (17.37 КБ) и обработать его2. Список найденных Может лучше по

очистку «соседней» ячейки относительные ссылки используются
в которой содержится функции являются обязательными=CELL(«format»,C2) 1, если форматированием второй области (A8:C11), (ноль), функция ИНДЕКС

 использовать. ячейку A1 нового на указанные ячейки,ADDRESS используйте значение

kim в дальнейшем можно,

 адресов отображать в строке вправо? Тогда после неудачного поиска: квадратные скобки «[]».

 искомая ячейка; для заполнения.=ЯЧЕЙКА(«формат»;C2)
ячейки предусмотрено изменение

т. е. содержимое ячейки возвратит ссылку наИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области]) листа Excel. Чтобы см. раздел Ссылочная(АДРЕС) возвращает адрес4: =ЕСЛИ((должность=»водитель»)*(график=»пятидневка);»Да»;»») только преобразовав в столбце В. всё найденное будет после строки

 Например, если в2 – номер столбцаОписание аргументов:

Со значением цвета для отрицательных

 B9. целый столбец или
Аргументы функции ИНДЕКС описаны отобразить результаты формул, форма. ячейки в виде. Остальные варианты:k1ava ссылку. А это
3. «Старый» список относиться к одномуMsgBox «А нету данном примере в с искомой ячейкой;Номер_строки – числовое значение,filename значений; во всех1,25 целую строку соответственно. ниже. выделите их иВозвращает значение элемента таблицы текста, а не2: А если не

усложнение и добавление

 очищать. имени в одной
там такого. Совсем

качестве параметра тип_ссылки1 – тип ссылки соответствующее номеру строки,
(имяфайла) функция остальных случаях возвращается=СУММ(ИНДЕКС(A1:C11;0;3;1))Аргументы «номер_строки», «номер_столбца» иСсылка

 нажмите клавишу F2, или массив, заданного
как действующую ссылку.

Как автоматически задать значение ячейки основываясь на данных других ячеек?

=A$1, сложно, можно куда летучести (ДВССЫЛ(), например).Правильно? строке. нет.», 48, «Ошибка!» указать число 4,
– абсолютная; в которой находитсяCELL 0 (ноль).
Сумма третьего столбца в «номер_области» должны указывать
— обязательный аргумент. Ссылка а затем — клавишу
номером строки и Если Вам нужно

3 нибудь залить файлВладимир
В моём предыдущем
P.S. Комментарий задобавьте такую строку: ссылка на ячейку1 – выбор стиля

 требуемая ячейка;(ЯЧЕЙКА) покажет путьcontents первой области диапазона
на ячейку внутри на один или

 ВВОД. При необходимости номером столбца.
получить значение ячейки,=$A1. с простым примеров: Возможно совпадёт с сообщении ошибочно проставлен отдельную плату :-)Target.Offset(0, 1) = примет следующий вид: ссылок с буквенно-цифровой

Номер_столбца – числовое значение, к файлу, имя(содержимое) – значение (A1:C11) является суммой

 аргумента «ссылка»; в несколько диапазонов ячеек.

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

Источник

Как использовать функцию ДВССЫЛ – примеры формул

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

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

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

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

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

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

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

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

ссылка_на_ячейку – это адрес нужной ячейки в виде текстовой строки, либо названия именованного диапазона.

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

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

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

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

Как работает функция ДВССЫЛ

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

Предположим, у вас есть число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу =ДВССЫЛ(C1) в любую другую ячейку и посмотрите, что произойдет:

Функция ДВССЫЛ обращается к значению в ячейке C1. Там в виде текстовой строки записан адрес «A1».
Функция ДВССЫЛ направляется по этому адресу в ячейку A1, откуда извлекает записанное в ней значение, то есть число 555.

Итак, в этом примере функция ДВССЫЛ преобразует текстовую строку в ссылку на ячейку.

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

Формула ДВССЫЛ(C1&»:»&C2) извлекает адреса из указанных ячеек и превращается в =ДВССЫЛ(«A1:A5»).

В итоге мы получаем ссылку =A1:A5

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

Как использовать ДВССЫЛ в Excel — примеры формул

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

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

Создание косвенных ссылок из значений ячеек

Как вы помните, функция ДВССЫЛ в Excel позволяет использовать стили ссылок A1 и R1C1. Обычно вы не можете использовать оба стиля на одном листе одновременно. Вы можете переключаться между двумя типами ссылок только с помощью опции «Файл» > «Параметры» > «Формулы» > R1C1 . По этой причине пользователи Excel редко рассматривают использование R1C1 в качестве альтернативного подхода к созданию ссылок.

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

Стиль A1 — это обычный и привычный всем нам тип адресации в Excel, который указывает сначала столбец, за которым следует номер строки. Например, B2 обозначает ячейку на пересечении столбца B и строки 2.

Стиль R1C1 является обозначает координаты ячейки наоборот – за строками следуют столбцы, и к этому нужно привыкнуть:) Например, R5C1 относится к ячейке A5, которая находится в строке 5, столбце 1 на листе. Если после буквы не следует какая-либо цифра, значит, вы имеете в виду ту же строку или столбец, в которых записана сама формула.

А теперь давайте сравним на простом примере, как функция ДВССЫЛ обрабатывает адреса вида A1 и R1C1:

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

Формула в ячейке D1: =ДВССЫЛ(C1)

Это самый простой вариант. Формула обращается к ячейке C1, извлекает ее значение — текстовую строку «A2» , преобразует ее в ссылку на ячейку, переходит к ячейке A2 и возвращает ее значение, равное 456.

Формула в ячейке D3: =ДВССЫЛ(C3;ЛОЖЬ)

ЛОЖЬ во втором аргументе указывает, что указанное значение (C3) следует рассматривать как ссылку на ячейку в формате R1C1, т. е. сначала идет номер строки, за которым следует номер столбца. Таким образом, наша формула ДВССЫЛ интерпретирует значение в ячейке C3 (R2C1) как ссылку на ячейку на пересечении строки 2 и столбца 1, которая как раз и является ячейкой A2.

Создание ссылок из значений ячеек и текста

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

В следующем примере формула =ДВССЫЛ(«А»&C1) возвращает значение из ячейки А1 на основе следующей логической цепочки:

Функция ДВССЫЛ объединяет элементы в первом аргументе ссылка_на_ячейку — текст «А» и значение из ячейки C1. Значение в C1 – это число 1, что в результате формирует адрес А1. Формула переходит к ячейке А1 и возвращает ее значение – 555.

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

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

Предположим, у вас есть следующие именованные диапазоны на вашем листе:

Яблоки – С2:E2
Лимоны — C3: E3
Апельсины – C4:E4 и так далее по каждому товару.

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

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

=СУММ(ДВССЫЛ (H1))
=СРЗНАЧ(ДВССЫЛ (H1))
=МАКС(ДВССЫЛ (H1))
=МИН(ДВССЫЛ (H1))

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

ДВССЫЛ для ссылки на другой рабочий лист

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

Предположим, у вас есть важные данные на листе 1, и вы хотите получить эти данные на листе 2. На скриншоте ниже показано, как можно справиться с этой задачей.

Нам поможет формула:

=ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Давайте разбираться, как работает эта формула.

Как вы знаете, обычным способом сослаться на другой лист в Excel является указание имени этого листа, за которым следуют восклицательный знак и ссылка на ячейку или диапазон, например Лист1!A1:С10. Так как имя листа часто содержит пробелы, вам лучше заключить его (имя, а не пробел в одинарные кавычки, чтобы предотвратить возможную ошибку, например,

‘Лист 1!’$A$1 или для диапазона – ‘Лист 1!’$A$1:$С$10 .

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

записать имя листа в одну ячейку,
букву столбца – в другую,
номер строки – в третью,
объединить всё это в одну текстовую строку,
передать этот адрес функции ДВССЫЛ.

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

С учетом вышеизложенного получаем шаблон ДВССЫЛ для создания ссылки на другой лист:

ДВССЫЛ («‘» & имялиста & «‘!» & имя столбца нужной ячейки & номер строки нужной ячейки )

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

ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

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

Замечание.

Если какая-либо из ячеек, содержащих имя листа и адреса ячеек (A2, B2 и c2 в приведенной выше формуле), будет пуста, ваша формула вернет ошибку. Чтобы предотвратить это, вы можете обернуть функцию ДВССЫЛ в функцию ЕСЛИ :

ЕСЛИ(ИЛИ(A2=»»;B2=»»;C2-“”); «»; ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Чтобы формула ДВССЫЛ, ссылающаяся на другой лист, работала правильно, указанный лист должен быть открыт в Экселе, иначе формула вернет ошибку #ССЫЛКА. Чтобы не видеть сообщение об ошибке, которое может портить вид вашей таблицы, вы можете использовать функцию ЕСЛИОШИБКА, которая будет отображать пустую строку при любой возникшей ошибке:

ЕСЛИОШИБКА(ДВССЫЛ(«‘»&A2&»‘!»&B2&C2); «»)

Формула ДВССЫЛ для ссылки на другую книгу Excel

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

Чтобы упростить задачу, давайте начнем с создания ссылки на другую книгу обычным способом (апострофы добавляются, если имена вашей книги и/или листа содержат пробелы):
‘[Имя_книги.xlsx]Имя_листа’!Арес_ячейки

Но, чтобы формула была универсальной, лучше апострофы добавлять всегда – лишними не будут .

Предполагая, что название книги находится в ячейке A2, имя листа — в B2, а адрес ячейки — в C2 и D2, мы получаем следующую формулу:

=ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2)

Поскольку вы не хотите, чтобы ячейки, содержащие имена книг и листов, изменялись при копировании формулы в другие ячейки, вы можете зафиксировать их, используя абсолютные ссылки на ячейки – $A$2 и $B$2 соответственно.

Если адреса ячеек заменить их значениями, то мы получим такой промежуточный результат:

=ДВССЫЛ(«‘[INDIRECT.xlsx]Продажи’!D3»)

Ну а итоговый результат вы видите на скриншоте ниже.

Hbc6

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

=ДВССЫЛ(«‘[» & Название книги & «]» & Имя листа & «‘!» & Адрес ячейки )

Примечание. Рабочая книга, на которую ссылается ваша формула, всегда должна быть открыта, иначе функция ДВССЫЛ выдаст ошибку #ССЫЛКА. Как обычно, функция ЕСЛИОШИБКА может помочь вам избежать этого:

=ЕСЛИОШИБКА(ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2); «»)

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

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

Чтобы проиллюстрировать разницу, сделайте следующее:

Введите любое значение в любую ячейку, например, число 555 в ячейку A1.
Обратитесь к A1 из двух других ячеек тремя различными способами: =A1, =ДВССЫЛ(«A1») и ДВССЫЛ(С1), где в С1 записан адрес «А1».
Вставьте новую строку над строкой 1.

Видите, что происходит? Ячейка с логическим оператором =А1 по-прежнему возвращает 555, потому что ее формула была автоматически изменена на =A2 после вставки строки. Ячейки с формулой ДВССЫЛ теперь возвращают нули, потому что формулы в них не изменились при вставке новой строки и они по-прежнему ссылаются на ячейку A1, которая в настоящее время пуста:

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

Предположим, вы хотите просуммировать значения в ячейках A2:A5, и вы можете легко сделать это с помощью функции СУММ:

=СУММ(A2:A5)

Однако вы хотите, чтобы формула оставалась неизменной, независимо от того, сколько строк было удалено или вставлено. Самое очевидное решение — использование абсолютных ссылок — не поможет. Чтобы убедиться, введите формулу =СУММ($A$2:$A$5) в какую-нибудь ячейку, вставьте новую строку, скажем, в строку 3, и увидите формулу, преобразованную в =СУММ($A$2:$A$6).

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

Решение состоит в использовании функции ДВССЫЛ, например:

=СУММ(ДВССЫЛ(«A2:A5»))

Поскольку Excel воспринимает «A1: A5» как простую текстовую строку, а не как ссылку на диапазон, он не будет вносить никаких изменений при вставке или удалении строки (строк), а также при их сортировке.

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

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

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

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

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

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

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

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

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

Вот пример:

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

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

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

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

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

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

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

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

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

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

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

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

Использование ДВССЫЛ для создания выпадающих списков

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

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

В ячейке А1 вы создаете простой выпадающий список с названиями имеющихся именованных диапазонов. Для второго зависимого выпадающего списка в ячейке В2 вы используете простую формулу =ДВССЫЛ(A1), где A1 — это ячейка, в которой выбрано имя нужного именованного диапазона.

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

Рис9

Чтобы сделать более сложные трехуровневые меню или раскрывающиеся списки с многоуровневыми записями, вам понадобится немного более сложная формула ДВССЫЛ в сочетании с вложенной функцией ПОДСТАВИТЬ.

Подробное пошаговое руководство по использованию ДВССЫЛ с проверкой данных Excel смотрите в этом руководстве: Как создать зависимый раскрывающийся список в Excel.

Функция ДВССЫЛ Excel — возможные ошибки и проблемы

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

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

Ошибка #ССЫЛКА!

Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА! в следующих случаях:

Аргумент ссылка_на_ячейку не является допустимой ссылкой Excel. Если вы пытаетесь передать функции текст, который не может обозначать ссылку на ячейку (например, «A1B0»), то формула приведет к ошибке #ССЫЛКА!. Во избежание возможных проблем проверьте аргументы функции ДВССЫЛ .
Превышен предел размера диапазона. Если аргумент ссылка_на_ячейку вашей формулы ДВССЫЛ ссылается на диапазон ячеек за пределами строки 1 048 576 или столбца 16 384, вы также получите ошибку #ССЫЛКА в Excel 2007 и новее. Более ранние версии Excel игнорируют превышение этого лимита и действительно возвращают некоторое значение, хотя часто не то, что вы ожидаете.
Используемый в формуле лист или рабочая книга закрыты.Если ваша формула с ДВССЫЛ адресуется на другую книгу или лист Excel, то эта другая книга или электронная таблица должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Впрочем, это требование характерно для всех формул, которые ссылаются на другие рабочие книги Excel.

Ошибка #ИМЯ?

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

Ошибка из-за несовпадения региональных настроек.

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

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

В стандартной конфигурации Windows для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая.

В результате при копировании формулы между двумя разными языковыми стандартами Excel вы можете получить сообщение об ошибке « Мы обнаружили проблему с этой формулой… », поскольку разделитель списка, используемый в формуле, отличается от того, что установлен на вашем компьютере. Если вы столкнулись с этой ошибкой при копировании какой-либо НЕПРЯМОЙ формулы из этого руководства в Excel, просто замените все запятые (,) точками с запятой (;) (либо наоборот). В обычных формулах Excel эта проблема, естественно, не возникнет. Там Excel сам поменяет разделители исходя из ваших текущих региональных настроек.

Чтобы проверить, какие разделитель списка и десятичный знак установлены на вашем компьютере, откройте панель управления и перейдите в раздел «Регион и язык» > «Дополнительные настройки».

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

Вот еще несколько статей по той же теме:

Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,…

Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на…

Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются…

Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…

Источник

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.

Описание

Функция ГИПЕРССЫЛКА создает ярлык для перехода в другое место в текущей книге или открытия документа, сохраненного на сетевом сервере, в интрасеть или в Интернете. Если щелкнуть ячейку с функцией ГИПЕРССЫЛКА, Excel к указанному расположению или откроется указанный документ.

Синтаксис

ГИПЕРССЫЛКА(адрес;[имя])

Аргументы функции ГИПЕРССЫЛКА описаны ниже.

адрес — обязательный аргумент. Путь к документу и имя файла, который нужно открыть. Адрес может ссылаться на определенное место в документе, например на ячейку или именованный диапазон листа или книги Excel либо на закладку в документе Microsoft Word. Путь может быть к файлу, который хранится на жестком диске. Путь также может быть универсальным соглашением об именовке (UNC) на сервере (в Microsoft Excel для Windows) или URL-адресом в Интернете или интрасети.

Обратите Excel в Интернете что функция ГИПЕРССЫЛКА действительна только для веб-адресов (URL-адресов). Link_location может быть текстовая строка, заключенная в кавычка, или ссылка на ячейку, содержаную ссылку в виде текстовой строки.

Если ссылка, указанная в link_location, не существует или не может быть нажата, при щелчке ячейки появляется сообщение об ошибке.
Понятное_имя — необязательный аргумент. Текст ссылки или числовое значение, отображаемое в ячейке. Имя отображается синим цветом с подчеркиванием. Если этот аргумент опущен, в ячейке в качестве текста ссылки отображается аргумент «адрес».

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

Если аргумент «имя» возвращает значение ошибки (например, #ЗНАЧ!), вместо текста ссылки в ячейке отображается значение ошибки.

Замечания

Чтобы в Excel приложении выбрать ячейку с гиперссылкой, не переходя по ней, щелкните ячейку и удерживайте кнопку мыши, пока указатель не , а затем отпустите кнопку мыши. В Excel в Интернете выберите ячейку, щелкнув ее, когда указатель является стрелкой; перейти к пункту назначения гиперссылки, щелкнув, когда указатель является указателем руки.

Примеры

Пример	Результат
=ГИПЕРССЫЛКА(«http://example.microsoft.com/report/budget report.xlsx», «Щелкните, чтобы просмотреть отчет»)	Открывает книгу, сохраненную по адресу http://example.microsoft.com/report. В ячейке отображается текст «Щелкните, чтобы просмотреть отчет» в качестве текста ссылки.
=ГИПЕРССЫЛКА(«[http://example.microsoft.com/report/budget report.xlsx]Годовой!F10»; D1)	Создает гиперссылку на ячейку F10 листа Годовой книги, сохраненной по адресу http://example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве текста ссылки отображается содержимое ячейки D1.
=ГИПЕРССЫЛКА(«[http://example.microsoft.com/report/budget report.xlsx]’Первый квартал’!ИтогиОтдел», «Щелкните, чтобы просмотреть итоги по отделу за первый квартал»)	Создает гиперссылку на диапазон ИтогиОтдел на листе Первый квартал книги, сохраненной по адресу http://example.microsoft.com/report. В ячейке листа, содержащей гиперссылку, в качестве текста ссылки отобразится «Щелкните, чтобы вывести итоги по отделу за первый квартал».
=ГИПЕРССЫЛКА(«http://example.microsoft.com/Annual Report.docx]КвартПриб», «Квартальный отчет о прибыли»)	Чтобы создать гиперссылку на определенное место в файле Word, необходимо сначала с помощью закладки определить место в файле, куда должен выполняться переход. В приведенном ниже примере создается гиперссылка на закладку КвартПриб в файле Annual Report.doc, сохраненном по адресу http://example.microsoft.com.
=ГИПЕРССЫЛКА(«\FINANCEStatements1stqtr.xlsx», D5)	Отображает содержимое ячейки D5 в качестве текста ссылки и открывает книгу, сохраненную на сервере FINANCE в общей папке Statements. В данном примере используется путь в формате UNC.
=ГИПЕРССЫЛКА(«D:FINANCE1stqtr.xlsx»; H10)	Открывает книгу 1stqtr.xlsx, которая находится в каталоге Finance на жестком диске D. После этого выводится число, которое содержится в ячейке H10.
=ГИПЕРССЫЛКА(«[C:My DocumentsMybook.xlsx]Итоги»)	Создает гиперссылку на область Totals в другой (внешней) книге Mybook.xlsx.
=ГИПЕРССЫЛКА(«[Книга1.xlsx]Лист1!A10″;»Перейти на Лист1 > A10»)	Чтобы перейти в другое место на текущем листе, укажите имя книги и имя листа, как в этом примере, где «Лист1» — текущий лист.
=ГИПЕРССЫЛКА(«[Книга1.xlsx]Январь!A10″;»Перейти на лист Январь > A10»)	Чтобы перейти в место на другом листе, укажите имя книги и имя листа, как в этом примере, где «Январь» — другой лист в книге.
=ГИПЕРССЫЛКА(ЯЧЕЙКА(«адрес»,Январь!A1″;»Перейти на лист Январь > A1″)	Чтобы перейти в другое место на текущем листе без использования полной ссылки на лист ([Книга1.xlsx]), вы можете воспользоваться этой формулой, где ЯЧЕЙКА(«адрес») возвращает текущее имя книги.
=ГИПЕРССЫЛКА($Z$1)	Чтобы быстро обновить все формулы на листе, использующие функцию ГИПЕРССЫЛКА с теми же аргументами, можно поместить целевой объект ссылки на тот же или другой лист, а затем использовать абсолютную ссылку на эту ячейку в качестве аргумента «адрес» в формулах с функцией ГИПЕРССЫЛКА. Изменения целевого объекта ссылки при этом будут немедленно отражаться в формулах.

Нужна дополнительная помощь?

Источник

Содержание

Применение формулы ДВССЫЛ
- Пример 1: одиночное применение оператора
- Пример 2: использование оператора в комплексной формуле
Вопросы и ответы

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

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

Применение формулы ДВССЫЛ

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

Данный оператор относится к категории функций «Ссылки и массивы» и имеет следующий синтаксис:

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

Таким образом, формула имеет всего два аргумента.

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

Аргумент «A1» не является обязательным и в подавляющем большинстве случаев его вообще не нужно указывать. Он может иметь два значения «ИСТИНА» и «ЛОЖЬ». В первом случае оператор определяет ссылки в стиле «A1», а именно такой стиль включен в Excel по умолчанию. Если значение аргумента не указывать вовсе, то оно будет считаться именно как «ИСТИНА». Во втором случае ссылки определяются в стиле «R1C1». Данный стиль ссылок нужно специально включать в настройках Эксель.

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

=ДВССЫЛ("A1")

будет эквивалентно выражению

=A1

Но в отличие от выражения «=A1» оператор ДВССЫЛ привязывается не к конкретной ячейке, а к координатам элемента на листе.

Рассмотрим, что это означает на простейшем примере. В ячейках B8 и B9 соответственно размещена записанная через «=» формула и функция ДВССЫЛ. Обе формулы ссылаются на элемент B4 и выводят его содержимое на лист. Естественно это содержимое одинаковое.

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

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

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

Теперь давайте рассмотрим конкретные примеры применения оператора.

Пример 1: одиночное применение оператора

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

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

Выделяем первый пустой элемент столбца, куда планируем вставлять формулу. Щелкаем по значку «Вставить функцию».

Происходит запуск окошка Мастера функций. Перемещаемся в категорию «Ссылки и массивы». Из перечня выбираем значение «ДВССЫЛ». Щелкаем по кнопке «OK».

Происходит запуск окошка аргументов указанного оператора. В поле «Ссылка на ячейку» требуется указать адрес того элемента на листе, содержимое которого мы будем отображать. Конечно, его можно вписать вручную, но гораздо практичнее и удобнее будет сделать следующее. Устанавливаем курсор в поле, после чего щелкаем левой кнопкой мыши по соответствующему элементу на листе. Как видим, сразу после этого его адрес отобразился в поле. Затем с двух сторон выделяем ссылку кавычками. Как мы помним, это особенность работы с аргументом данной формулы.
В поле «A1», так как мы работает в обычном типе координат, можно поставить значение «ИСТИНА», а можно оставить его вообще пустым, что мы и сделаем. Это будут равнозначные действия.

После этого щелкаем по кнопке «OK».

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

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

Урок: Мастер функций в программе Excel

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

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

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

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

Теперь присвоим имя всем ячейкам в столбце «Доход», которое будет являться аналогичным названию соответствующего им месяца. То есть, первый элемент в столбце «Доход», который содержит размер выручки, следует назвать «Январь», второй – «Февраль» и т.д.
Итак, чтобы присвоить имя первому элементу столбца, выделяем его и жмем правую кнопку мыши. Открывается контекстное меню. Выбираем в нем пункт «Присвоить имя…».

Запускается окно создания имени. В поле «Имя» вписываем наименование «Январь». Больше никаких изменений в окне производить не нужно, хотя на всякий случай можно проверить, чтобы координаты в поле «Диапазон» соответствовали адресу ячейки содержащей размер выручки за январь. После этого щелкаем по кнопке «OK».

Как видим, теперь при выделении данного элемента в окне имени отображается не её адрес, а то наименование, которое мы ей дали. Аналогичную операцию проделываем со всеми другими элементами столбца «Доход», присвоив им последовательно имена «Февраль», «Март», «Апрель» и т.д. до декабря включительно.

Выбираем ячейку, в которую будет выводиться сумма значений указанного интервала, и выделяем её. Затем щелкаем по пиктограмме «Вставить функцию». Она размещена слева от строки формул и справа от поля, где отображается имя ячеек.

В активировавшемся окошке Мастера функций перемещаемся в категорию «Математические». Там выбираем наименование «СУММ». Щелкаем по кнопке «OK».

Вслед за выполнением данного действия запускается окно аргументов оператора СУММ, единственной задачей которого является суммирование указанных значений. Синтаксис этой функции очень простой:
=СУММ(число1;число2;…)

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

Устанавливаем курсор в поле «Число1». Затем жмем на пиктограмму в виде перевернутого треугольника справа от поля наименования диапазонов. Раскрывается список последних используемых функций. Если среди них присутствует наименование «ДВССЫЛ», то сразу кликаем по нему для перехода в окно аргументов данной функции. Но вполне может быть, что в этом списке вы его не обнаружите. В таком случае нужно щелкнуть по наименованию «Другие функции…» в самом низу списка.

Запускается уже знакомое нам окошко Мастера функций. Перемещаемся в раздел «Ссылки и массивы» и выбираем там наименование оператора ДВССЫЛ. После этого действия щелкаем по кнопке «OK» в нижней части окошка.

Происходит запуск окна аргументов оператора ДВССЫЛ. В поле «Ссылка на ячейку» указываем адрес элемента листа, который содержит наименование начального месяца диапазона предназначенного для расчета суммы. Обратите внимание, что как раз в этом случае брать ссылку в кавычки не нужно, так как в данном случае в качестве адреса будут выступать не координаты ячейки, а её содержимое, которое уже имеет текстовый формат (слово «Март»). Поле «A1» оставляем пустым, так как мы используем стандартный тип обозначения координат.
После того, как адрес отобразился в поле, не спешим жать на кнопку «OK», так как это вложенная функция, и действия с ней отличаются от обычного алгоритма. Щелкаем по наименованию «СУММ» в строке формул.

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

Снова открывается окно аргументов оператора ДВССЫЛ. Заносим в поле «Ссылка на ячейку» адрес элемента на листе, где расположено наименования месяца, который завершает расчетный период. Опять координаты должны быть вписаны без кавычек. Поле «A1» снова оставляем пустым. После этого щелкаем по кнопке «OK».

Как видим, после данных действий программа производит расчет и выдает результат сложения дохода предприятия за указанный период (март — май) в предварительно выделенный элемент листа, в котором располагается сама формула.

Если мы поменяем в ячейках, где вписаны наименования месяцев начала и конца расчетного периода, на другие, например на «Июнь» и «Ноябрь», то и результат изменится соответственно. Будет сложена сумма дохода за указанный период времени.

Урок: Как посчитать сумму в Экселе

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

Источник

Обычно мы используем формулу = A1 для ссылки на ячейку A1 на другую ячейку на листе. Но это может ссылаться только на значение ячейки. Если вы хотите указать значение ячейки, а также ее формат, вам нужно попробовать другой метод. В этой статье представлены два метода достижения этой цели.

Справочный формат и значение из другой ячейки с вставкой связанного изображения
Формат автоматической ссылки и значение из другой ячейки с VBA

Справочный формат и значение из другой ячейки с вставкой связанного изображения

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

1. Выберите ячейку (A1), на которую нужно сослаться, затем скопируйте ее, нажав Ctrl + C ключи.

2. Перейдите к ячейке, которую вы хотите связать со ссылочной ячейкой, щелкните ее правой кнопкой мыши и выберите > Специальная вставка > Связанное изображение. Смотрите скриншот:

Теперь формат и значение ячейки A1 привязаны к указанной ячейке. И формат и значение этих двух ячеек будут синхронными, как показано на скриншоте ниже.

Формат автоматической ссылки и значение из другой ячейки с VBA

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

1. Щелкните правой кнопкой мыши вкладку листа, содержащую нужную ячейку, а затем щелкните Просмотреть код из контекстного меню.

2. Во всплывающем Microsoft Visual для приложений окна, скопируйте и вставьте приведенный ниже код VBA в окно кода.

Код VBA: формат ссылки и значение из другой ячейки

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Worksheets("Sheet1")
        If .Range("A1").Value2 <> "" Then
            On Error Resume Next
            Range("A1").Copy (.Range("E2"))
        End If
    End With
End Sub

Внимание: В коде Sheet1 — это имя листа, содержащего ячейку, в которой необходимо указать как значение, так и формат. A1 и E2 означает, что ячейка A1 будет автоматически ссылаться на ячейку E2.

С этого момента, когда значение изменяется в ячейке A1 листа Sheet1, его значение и формат будут немедленно ссылаться на ячейку E2.

Статьи по теме:

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

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (9)

Оценок пока нет. Оцените первым!

Источник

Excel значение по адресу ячейки

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

Функция 20: ADDRESS (АДРЕС)

Как можно использовать функцию ADDRESS (АДРЕС)?

Синтаксис ADDRESS (АДРЕС)

Ловушки ADDRESS (АДРЕС)

Пример 1: Получаем адрес ячейки по номеру строки и столбца

Абсолютная или относительная

A1 или R1C1

Название листа

Пример 2: Находим значение ячейки, используя номер строки и столбца

Пример 3: Возвращаем адрес ячейки с максимальным значением

ИНДЕКС (функция ИНДЕКС)

Форма массива

Описание

Синтаксис

Замечания

Примеры

Пример 1

Пример 2

Ссылочная форма

Описание

Синтаксис

Замечания

Примеры

30 функций Excel за 30 дней: ЯЧЕЙКА (CELL)

Функция 11: CELL (ЯЧЕЙКА)

Как можно использовать функцию CELL (ЯЧЕЙКА)?

Синтаксис CELL (ЯЧЕЙКА)

Ловушки функции CELL (ЯЧЕЙКА)

Пример 1: Формат числа в ячейке

Пример 2: Название листа

Пример 3: Подстановка аргумента info_type (тип_сведений) из выпадающего списка

Примеры функции АДРЕС для получения адреса ячейки листа Excel

Функция АДРЕС в Excel: описание особенностей синтаксиса

Примеры использования функции АДРЕС в Excel

Как получить адрес ссылки на ячейку Excel?

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

поиск адреса ячейки по значению

Узнать адрес ячейки по отобранному формулой значению

Как автоматически задать значение ячейки основываясь на данных других ячеек?

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

Как работает функция ДВССЫЛ

Как использовать ДВССЫЛ в Excel — примеры формул

Создание косвенных ссылок из значений ячеек

Создание ссылок из значений ячеек и текста

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

ДВССЫЛ для ссылки на другой рабочий лист

Формула ДВССЫЛ для ссылки на другую книгу Excel

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

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

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

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

Использование ДВССЫЛ для создания выпадающих списков

Функция ДВССЫЛ Excel — возможные ошибки и проблемы

Ошибка #ССЫЛКА!

Ошибка #ИМЯ?

Ошибка из-за несовпадения региональных настроек.

Описание

Синтаксис

Замечания

Примеры

Нужна дополнительная помощь?

Применение формулы ДВССЫЛ

Пример 1: одиночное применение оператора

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

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

А вот еще интересные статьи: