Изменение диапазонов суммирования/условий при продлении формулы, Как автоматически изменить диапазон суммирования/условий для функции СУМЕСЛИМН при ее продлении
Всем доброго дня! По сути задача очень простая - необходимо что бы в ячейке расчета площадей (Ар) для стен вычитались встроенные в них проемы (критерий "пр" при совпадение ориентации) с просмотром "ниже" и в пределах помещения.
Для помещения №1 - эта задача решена и Ар рассчитывается как нужно (см. пример для помещения №1 Q13-Q16).
Основная проблема в том, что в 1-м и любых других последующих помещениях могут добавляться/удаляться строки, соответственно продлевая формулу в столбце Q (пример Q17-Q19) - диапазоны смещаются за пределы текущего помещения. Жесткая привязка $ диапазонов в любом из вариантов не подходит, т.к. каждое последующее помещение создается из предыдущего. Тут наверно можно реализовать какое то смещение диапазонов в зависимости от строк в текущем помещении, но я посмотрев "похожие" темы понял что ни чего не понял =(((
Если ориентироваться на кол-во строк в помещении...то минимальное кол-во строк для каждого помещения =2, максимальное может быть и 15
Я правильно понимаю более сложной формулой с набором нескольких функций без именованных диапазонов вариантов - нет?
Просто не особо понимаю в этих именованных диапазонах и при добавлении доп.столбцов левее или между именованных - они смещаются, а это критично, учитывая, что переместить их на место при выделении формулы не получается.
Может возможно сделать какой то иной интервальный просмотр по столбцам с ячейками константами "номер помещения"/"тип"/"Ориентация"/"Ар"
В остальном этот вариант очень хорош! и самое главное без трехэтажных формул.
Может быть для каждого здания зарезервировать, например 15 строк, тогда диапазон будет фиксированный. Добавлять или убирать строки будет ненужно, и формулы сбиваться не будут. Именованные формулы находятся в диспетчере имен на вкладке формулы, там их можно посмотреть и скопировать для изучения. В функцию СУММЕСЛИМН() нельзя диапазоны задавать формулами, а именованными можно. Поэтому такое решение получилось.
По факту было бы достаточно диапазона в 2 строки (исходное помещение всегда не менее 2-х строк) с возможностью расширения этого диапазона вставкой строк между 2-мя исходными.... такое возможно?
Резервировать 15 строк на каждое помещение...без возможности удаления лишних строк однозначно не вариант...будет много бесполезных строк. И даже если можно будет удалять лишние...делать такое с каждым помещением не особо удобно...помещений может быть 50...100
если не использовать СУММЕСЛИ, а к примеру рассмотреть вариант ВПР и поискпоз - вроде бы и возможно, но как то многовато будет перебора и там то же желательно статичные диапазоны.
Как вариант можно добавить следующее правило после каждой нар.стены - проверять только те строки которые соотв ориентации и назначению "пр" до пустой ячейки после "пр" предполагая, что последующие элементы это либо следующая стена со своими проемами либо пол/потолок и в расчете не участвуют - может такое возможно организовать?. По сути в этом случае критерий ориентации можно даже исключить если подразумевать что - стена и все относящиеся к ней проемы (идущие ниже строками) будут одной и той же ориентации, затем след.стена с своими проемами и т.д.
Если при таком заполнении проще организовать вычитание проемов - то можно и так, но я не смог придумать как организовать такую проверку...за каждой стеной может следовать как 0 - проемов так и 5-6
Пример - на основе все той же СУММЕСЛИМН - для понимания структуры заполнения для варианта (стена-проемы)/(след.стена-проемы) - понятно что сумеслимн не подходит и формула будет выглядеть как то иначе
В таком виде все прекрасно работает! Большое спасибо за помощь! я несколько дней пытался это реализовать
Еще подскажите зачем используются столбцы с размерами, а не итоговая площадь? просто иногда не использую оба размера для определения площади, а сразу ввожу итоговую площадь в ячейке размера "а" или "в" и это значение идет в расчетную площадь
Насколько понял при таком подсчете если я убираю размер "а или в" то площадь уже не учитывается
АlехМ написал: В функцию СУММЕСЛИМН() нельзя диапазоны задавать формулами, а именованными можно.
Алексей, не совсем так. ИНДЕКСом можно - он ведь возвращает ссылку, т.е., тот же диапазон Твоя формула в безымянном виде прекрасно работает =ЕСЛИ($A$1=I13;СУММЕСЛИМН(ИНДЕКС($R:$R;ПРОСМОТР(2;1/$H$1:$H13;СТРОКА($H$1:$H13))):ИНДЕКС($R:$R;ЕСЛИОШИБКА(ПРОСМОТР(2;1/$H14:$H98;СТРОКА(A14:A98))-2;СТРОКА()+20));ИНДЕКС($L:$L;ПРОСМОТР(2;1/$H$1:$H13;СТРОКА($H$1:$H13))):ИНДЕКС($L:$L;ЕСЛИОШИБКА(ПРОСМОТР(2;1/$H14:$H98;СТРОКА(A14:A98))-2;СТРОКА()+20));L13;ИНДЕКС($P:$P;ПРОСМОТР(2;1/$H$1:$H13;СТРОКА($H$1:$H13))):ИНДЕКС($P:$P;ЕСЛИОШИБКА(ПРОСМОТР(2;1/$H14:$H98;СТРОКА(A14:A98))-2;СТРОКА()+20));"пр");0)
Еще вариант. =ЕСЛИ($A$1=I13;СУММПРОИЗВ(M$13:M$99*O$13:O$99*(L$13:L$99=L13)*(P$13:P$99="пр")*(ПРОСМОТР(СТРОКА($13:$99);СТРОКА($13:$99)/ЕЧИСЛО(H$13:H$99);H$13:H$99)=ПРОСМОТР(;-1/H$13:H13;H$13:H13)));0)
СПАСИБО за разнообразие вариантов! на первый взгляд все варианты работают как надо - буду тестировать. Еще бы понять который из них меньше всего нагружает эксель - что бы взять за основу?