30 функций excel за 30 дней: двссыл (indirect)

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

Нам известно имя листа (Март) и оно введено в отдельную ячейку B1. Решить данную задачу легко опираясь на формулу, которая содержит 2 простые функции:

Для примера изобразим эту ситуацию и ее решение на рисунках:

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

Чтобы отобразить первое значение из листа «Март» и его ячейки C3 в ячейку B2 главного листа введите формулу:

В результате мы получили значение ячейки C3 из листа «Март»:

Можно так же в аргументах функции СЦЕПИТЬ указать не только ссылку на ячейку с именем листа, но и ссылку на ячейку, которая в своем значении содержит имя ячейки этого листа. Тогда целый адрес можно динамически изменять при необходимости по названиям листов и адресов их ячеек. Чтобы получить такой эффект необходимо немного модифицировать нашу формулу:

Добавьте новую строку между первой и второй. Для этого выделите целую строку 2 и нажмите комбинацию горячих клавиш CTRL+SHIFT+=. Или щелкните по заголовку второй строки правой кнопкой мышки, а потом из появившегося контекстного меню выберите опцию «Вставить».
Для ячейки B2 введите значение C3.
В ячейке B3 измените формулу, а точнее аргументы ее функции СЦЕПИТЬ:

Теперь формула работает в полном динамическом режиме:

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

Разбор принципа действия формулы динамической ссылки на лист Excel:

Функция ДВССЫЛ позволяет преобразовать текстовое значение в ссылку. При этом в первом ее аргументе указана функция СЦЕПИТЬ. Она позволяет собрать несколько разных текстов в одну динамическую ссылку используя несколько аргументов внутри функции. Во втором не обязательном аргументе ДВССЫЛ мы имеем возможность указывать номером стиль адресации:

1-классический (например, А1);
0-нумерированый по строкам и столбцам (например, R1С1).

Вернемся к функции СЦЕПИТЬ – предназначена для сложения нескольких частей текста в одну текстовую строку. В этом примере функция СЦЕПИТЬ собирает нам конструкцию ссылки с трех частей текста (в данном случаи). Каждая часть текстовой строки указывается в отдельном аргументе. Функция СЦЕПИТЬ позволяет создавать максимально до 255 аргументов.

Имя листа (Март).
Знак восклицания (!) обязательный символ для создания адреса указывающего на другой лист.
Необходимая ячейка (C3).

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

Полезный совет! Для многих пользователей Excel часто удобнее использовать в место функции СЦЕПИТЬ символ амперсанд (&). Тогда наша формула будет содержать только одну функцию:

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

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

Выпадающий список можно создать с помощью Проверки данных

В этой статье создадим Выпадающий список с помощью Проверки данных ( Данные/ Работа с данными/ Проверка данных ) с типом данных Список .

Выпадающий список можно сформировать по разному.

Панель инструментов в Google Sheets

Если до этого вы работали только в Excel, есть вероятность, что к Гугл Таблицам придется привыкать заново. Хотя элементы управления в них действительно гораздо проще.

Давайте быстро пробежимся по основным инструментам, которые предоставляет сервис Google.

Отменить и повторить последнее действие, распечатать документ, скопировать форматирование. Если вам нужно применить одинаковое форматирование сразу к нескольким ячейкам, очень удобно использовать именно этот инструмент. Сначала выберете ячейку, после чего кликайте по инструменту на панели, а потом — по ячейке, которую нужно отформатировать.
Изменить масштаб. С помощью этой функции можно сделать таблицу крупнее или мельче в диапазоне от 50 до 200%.
Изменить формат данных в ячейках. В этом разделе можно выбрать формат исчисления в ячейке. Например, денежный или процентный. Также можно увеличить или уменьшить количество знаков после запятой в числовом формате.
Шрифт. В выпадающем списке можно выбрать любой из предложенных шрифтов.
Размер шрифта.
Форматирование текста. Выбор начертания слов, цвет текста или фона для него.
Форматирование ячеек. Здесь на ячейки можно применить заполнение цветом, выбрать их границы, объединить друг с другом.
Выравнивание текста. Его можно настроить по горизонтали и вертикали, выбрать функции переноса или поворота текста в каждой ячейке или столбце.
Прочие инструменты: добавить ссылку, добавить комментарий, вставить диаграмму, создать фильтр, использовать функции.
Отображение листа и направления ввода.
Способы ввода. Есть варианты включения экранной клавиатуры или поля для рукописного ввода.
Скрыть меню. Кликнув на стрелку можно свернуть главное меню вместе с заголовков таблицы, иконкой аккаунта и настройками доступа. Таким образом, останется только панель инструментов.

Внизу на страницы мы видим панель со вкладками для переключения между таблицами. Таким образом, в одном файле можно работе сразу с несколькими листами с таблицами. Чтобы создать новый Лист, нужно кликнуть на кнопку «+» в левом нижнем углу. Если нажать на стрелку рядом с названием вкладки, развернется меню. В нем можно удалить, скопировать, переместить, переименовать или скрыть лист. Там же можно защитить его от редактирования и изменить цвет ярлыка. Скрытый лист не будет виден на общей панели. Чтобы посмотреть его, нужно будет перейти в меню «Вид», потом «Скрытые листы» и найти его в предложенном списке.

Синтаксис

ГИПЕРССЫЛКА(адрес;)

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

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

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

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

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

Абсолютная и относительная ссылка на ячейку в Excel

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

Относительная ссылка дозволяет изменять адресок ячеек по строчкам и столбцам при копировании формулы в другое пространство документа. Другими словами, если скопировать формулу из ячейки А3 в ячейку С3, то для расчета суммы возьмутся новейшие адреса ячеек: С1 и С2.

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

Разглядим последующий пример. Есть таблица, в которую внесены: наименование продукта, его стоимость и количество проданных единиц. Посчитаем итоговую сумму для каждой единицы. В ячейку D6 пишем формулу: =В6*С6. Видите ли, ссылки на ячейки в формуле относительные.

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

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

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

Чтоб создать абсолютную ссылку на ячейку в Excel, необходимо добавить символ «$» в адресок ячейки перед заглавием столбца и строчки. Либо же поставить курсор в строке формул опосля адреса подходящей ячейки и надавить «F4». В примере, для расчета суммы в ячейке А3, употребляется сейчас абсолютная ссылка на ячейку А1.

Давайте посчитаем сумму для ячеек D1 и D2. В ячейку D3 скопируем формулу из А3. Видите ли, итог заместо 24 – 25. Все из-за того, что в формуле была применена абсолютная ссылка на ячейку $A$1. Потому в расчете использовались не ячейки D1 и D2, а ячейки $A$1 и D2.

Разглядим для примера такую таблицу: есть наименование продукта и его себестоимость. Чтоб найти стоимость продукта для реализации, необходимо посчитать НДС. НДС – 20%, и значение написано в ячейке В9. Вписываем формулу для расчета в ячейку С6.

Если мы скопируем формулу в другие ячейки, то не получим итог. Потому что в расчете будут употребляться ячейки В10 и В11, которые не заполнены значениями.

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

Если в строке формул поставить курсор опосля адреса ячейки и надавить «F4» 2-ой и 3-ий раз, то получится смешанная ссылка в Excel. В этом случае, при копировании может не изменяться либо строчка – А$1, либо столбец – $А1.

Диспетчер имен в Excel.

АДРЕС(5;2;1) – фиксирует, как столбец, так и строку, и возвращает $B$5;
АДРЕС(5;2;1) – фиксирует только строку, и возвращает B$5;
АДРЕС(5;2;1) – фиксирует только столбец, и возвращает $B5;
АДРЕС(5;2;1) – оставляет обе ссылки относительными, и возвращает B5.

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

Как использовать диспетчер имен в Excel?

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

Перейдите на вкладку «Формулы»> группа «Определенные имена», затем щелкните «Диспетчер имен». В качестве альтернативы мы можем просто нажать Ctrl + F3 (ярлык для диспетчера имен)

Для нового именованного диапазона нажмите кнопку «Новый».

При нажатии кнопки «Создать» вы увидите окно, показанное ниже.

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

Вариант 3: Использование макроса

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

Для начала через главное окно перейдите в меню «Параметры».
Выберите раздел «Настроить ленту» и поставьте галочку возле основной вкладки «Разработчик». Так вы обеспечите ее отображение в верхней панели программы, что нам сейчас и пригодится.
Примените изменения, вернитесь к таблице, перейдите на соответствующую вкладку и запустите оснастку «Visual Basic».
Дважды щелкните по файлу книги, чтобы справа отобразилось окно редактирования кода.

Вставьте туда оставленный ниже код, заменив значения «a2» на номер ячейки, с которой начинается активация ссылок, а «а» – на букву столбца.

Sub HyperSsylka()
Dim cell As Range, ra As Range: Application.ScreenUpdating = False
Set ra = Range(, Range("a" & Rows.Count).End(xlUp))
For Each cell In ra.Cells
If Len(cell) Then cell.Hyperlinks.Add cell, cell
Next cell
End Sub

Sub HyperSsylkaSelect()
Dim cell As Range: Application.ScreenUpdating = False
For Each cell In Selection
If Len(cell) Then cell.Hyperlinks.Add cell, cell
Next cell
End Sub

Строку для редактирования вы видите на следующем изображении.

Сохраните результат, нажав по соответствующей кнопке на панели сверху или используя сочетание клавиш Ctrl + S.
Закройте редактор кода и запустите окно «Макрос». Дважды щелкните по строке с созданным макросом для активации ссылок.
Все изменения выполняются автоматически, а далее вам остается только убедиться в том, что вместо обычного текста появились активные ссылки, переход по которым возможен (если в их содержимом действительно находится веб-адрес).

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

Excel

Ссылки в Excel

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

относительной

или

абсолютной.

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

Использование относительных ссылок в Excel аналогично указанию направления движения
по улице — «идти три квартала на север, затем два квартала на запад». Следование
этим инструкциям из различных начальных мест будет приводить в разные места
назначения.

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

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

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

В Excel абсолютная ссылка на ячейку или область ячеек будет всегда ссылаться на один
и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет
примерно следующее: «Идите на пересечение Арбата и Бульварного кольца». Вне
зависимости от места старта это будет приводить к одному и тому же месту.

Если формула требует, чтобы адрес ячейки
оставался неизменным при копировании, то должна использоваться абсолютная ссылка
(формат записи $А$1). Например, когда формула вычисляет доли от общей суммы,
ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.

Абсолютная ссылка может быть создана только при наборе формулы, перед адресом
строки и столбца вводится знак доллара — $.

Для создания абсолютной ссылки в Excel удобно использовать клавишу абсолютной ссылки
F4, которая осуществляет преобразование относительной ссылки в абсолютную и
наоборот.

Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой
на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или
убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 — так
называемые смешанные ссылки).

Упражнение

Создание абсолютной ссылки Excel

1. Создайте таблицу, аналогичную представленной
ниже.

Таблица 1. Расчет зарплаты

	A	B	C
1	Часовая ставка	100р.
2	Ф. И. 0 .	Часов	Зарплата
3	Иванов	40
4	Петров	30
5	Сидоров	25

2. В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.

При тиражировании формулы данного примера с относительными ссылками в ячейке
С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный
адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;

3. Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на
В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.

4. Скопируйте формулу в ячейки С4 и С5.

5. Сохраните файл (табл. 2) под именем

Зарплата.xls.

Таблица 2. Итоги расчета зарплаты

	A	B	C
1	Часовая ставка	100р.
2	Ф. И. О.	Часов	Зарплата
3	Иванов	40	4000р.
4	Петров	30	3000р.
5	Сидоров	25	2500р.

Функция ВПР

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

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; ), где

ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
- ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
- ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

Важно не путать, что номер столбца указывается не по индексу на листе, а по порядку в указанном диапазоне. На изображении приведено 3 таблицы

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

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

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

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

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение

Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук»

Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения «*» & «иван» & «*» могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;;2;ЛОЖЬ) – результат выполнения строка «Два».

Как извлечь текст из ячейки с помощью Ultimate Suite

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

Вы просто переходите на вкладку Ablebits Data > Текст, выбираете и в выпадающем списке нажимаете Извлечь (Extract) :

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

Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
Щелкните Вставить результаты (Insert Results). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула (Insert as formula) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

Чтобы извлечь имя пользователя:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения?

Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст».

Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по , которая доступна только для наших русскоязычных пользователей.

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

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

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

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

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

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

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

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

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

Вот пример:

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

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

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

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

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

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

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

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

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

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

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

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