Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Динамический диапазон с границами из других формул - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамический диапазон с границами из других формул (Формулы/Formulas)
Динамический диапазон с границами из других формул
nordri Дата: Среда, 15.06.2022, 23:39 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 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
 
Ответить
СообщениеС трудом нагуглил формулу для позиции последнего заполненного значения в столбце, который бы учитывал и текст с числами, и пропуски (пустые значения), или другими словами последняя строка диапазона с пропусками с текстом и числами.
Код
=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ("*";$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

Автор - nordri
Дата добавления - 15.06.2022 в 23:39
Gustav Дата: Четверг, 16.06.2022, 00:30 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2079
Репутация: 828 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
=МАКС(СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10)))

Ну, так надо же функцию ДВССЫЛ (INDIRECT) применить к получившейся текстовой строке адреса:
Код
=МАКС( ДВССЫЛ( СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))))

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


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
=МАКС(СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10)))

Ну, так надо же функцию ДВССЫЛ (INDIRECT) применить к получившейся текстовой строке адреса:
Код
=МАКС( ДВССЫЛ( СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))))

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

Автор - Gustav
Дата добавления - 16.06.2022 в 00:30
nordri Дата: Четверг, 16.06.2022, 00:48 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 25
Репутация: 0 ±
Замечаний: 0% ±

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

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

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



Не опишете парой слов, что значит оживить?
К сообщению приложен файл: 8528250.xlsx(22.9 Kb)


Сообщение отредактировал nordri - Четверг, 16.06.2022, 00:54
 
Ответить
Сообщение
=МАКС( ДВССЫЛ( СЦЕПИТЬ(АДРЕС(СТРОКА(A5);M10;4);":";АДРЕС(M19;M10))))

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

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



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

Автор - nordri
Дата добавления - 16.06.2022 в 00:48
Gustav Дата: Четверг, 16.06.2022, 01:39 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2079
Репутация: 828 ±
Замечаний: 0% ±

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

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


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

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

Автор - Gustav
Дата добавления - 16.06.2022 в 01:39
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Динамический диапазон с границами из других формул (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!