Динамический диапазон с границами из других формул

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамический диапазон с границами из других формул (Формулы/Formulas)

nordri

Дата: Среда, 15.06.2022, 23:39 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

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

Код

=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ("*";$F:$F;-1);0);ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$F:$F;1);0))

В стобце F:F

Ну а после этого у меня возникла проблема: (файл прилагается)
У меня имеется в ячейке нужный мне диапазон для массива, что то, вроде этого:

Код

=СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))

конструкции вида:

Код

=ЯЧЕЙКА("столбец";СЦЕПИТЬ(M22;":";M20))

Код

=МАКС(СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10)))

Не работают... :'(

Что с этим можно сделать? Заменить эксель 2016 на новую версию, возможности нет. killed

Ну и в добавок, откуда это все началось: http://www.excelworld.ru/forum/2-50067-327709-16-1655301903

К сообщению приложен файл: __-.xlsx (22.7 Kb) · 7404174.jpg (68.7 Kb)

Сообщение отредактировал nordri - Среда, 15.06.2022, 23:55

Ответить

Gustav

Дата: Четверг, 16.06.2022, 00:30 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2738

Репутация: 1136 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата nordri, 15.06.2022 в 23:39, в сообщении № 1 (

)

=МАКС(СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10)))

Ну, так надо же функцию ДВССЫЛ (INDIRECT) применить к получившейся текстовой строке адреса:

Код

=МАКС( ДВССЫЛ( СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))))

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

МОИ: Ник, Tip box: 41001663842605

Ответить

nordri

Дата: Четверг, 16.06.2022, 00:48 | Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 37

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Цитата Gustav, 16.06.2022 в 00:30, в сообщении № 2 (

)

=МАКС( ДВССЫЛ( СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))))

Так просто??? Спасибо! Работает! (файл)

И это тоже!

Цитата nordri, 15.06.2022 в 23:39, в сообщении № 1 (

)

=ЯЧЕЙКА("столбец";СЦЕПИТЬ(M22;":";M20))

Ура!

Код

=ЯЧЕЙКА("столбец";ДВССЫЛ(СЦЕПИТЬ(M22;":";M20)))

Не опишете парой слов, что значит оживить?

К сообщению приложен файл: 8528250.xlsx (22.9 Kb)

Сообщение отредактировал nordri - Четверг, 16.06.2022, 00:54

Ответить

Gustav

Дата: Четверг, 16.06.2022, 01:39 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2738

Репутация: 1136 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Цитата nordri, 16.06.2022 в 00:48, в сообщении № 3 (

)

Не опишете парой слов, что значит оживить?

Хм... фигуру речи, конечно! Ну, ладно, попробую... Вот если отдельно посчитать в вашей первоначальной версии формулы значение функции СЦЕПИТЬ, то получится текстовая строка "F5:$F$23", которую можно подставить внутрь функции МАКС (да-да, именно так, с кавычками!):

Код

=МАКС("F5:$F$23")

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

МОИ: Ник, Tip box: 41001663842605

Ответить

Страница 1 из 1
1