Развернуть формулой массивную запись для подсчета суммы из диапазона.
Пользователь
Сообщений: Регистрация: 17.10.2013
(Win'11-Excel'21/365)
24.08.2022 07:20:54
Всем здравствуйте, господа эксперты.
1. Есть исходные данные во вкладке «ИсходныеДанные»
2. Название сметы есть всегда.
3. Раздел сметы и сумма может быть, а может и не быть.
4. Во вкладке «Итог» массивно-динамически создаю уникальную запись сметы и раздел сметы (при наличии)
5. Обращаю внимание: если раздел сметы есть, то от названия он отделяется пробелом с запятой « ,». Причем это разделитель не уникальный, может быть и в названии сметы (уникальным же разделителем является либо слово «раздел» либо «без раздела»)
Задача: во вкладке «Итог» получить формулой сумму в каждой строке.
Условия: без PQ, без VBA, без дополнительных столбцов и предварительных (где-либо) расчетов.
Имеет задача решение? Подкиньте хотя бы идею ))
P/S/ естественно, вся эта конструкция имеет гораздо большее количество строк/столбцов. Для удобства восприятия всё «лишнее» вырезал.
P/S/S/ Долго думал над названием темы. Не ругайте, пожалуйста, сильно, если не заходит формулировка. Согласен на любую )
Сергей Евдокимов, Чем SUMIFS не подошла? А, понял. Сперва создаем трудности, потом их преодалеваем. А именно 02-01-07 Канализация К1, НК1, К2, К3, Раздел 2. Канализация хоз-бытовая (К1) сперва объединили так что не просто понять что разделяет запятая, а потом нужно отделить мух от котлет. ну самое простое что приходит в голову при получении такого объединения в качестве разделителя добавить непечатный символ. Он виден не будет, но по нему будет просто разделить на раздел и .... Обработать отсутсвие и использовать SUMIFS для подсчета. Если без непечатного символа, то будут пляски с бубном. хотя тоже можно отделить всё что после последней зпятой и проверить есть ль такое в качестве раздела. Если есть то использовать его для условия по разделу и исключить из условия по названию, если нет то заменить на пусто.
написал: А, понял. Сперва создаем трудности, потом их преодалеваем.
Вы совершенно правы, так и развиваемся )) За идею спасибо. Толково! Сделал.
Behruz A.N., спасибо. Вижу работает. Осталось разобраться "как". Непростая формула у вас. Еще ис подменой запятых )
memo, спасибо. Решение ваше, если не ошибаюсь, немного не отвечает условию задачи (п.5), но суть понятна и это главное. А остальное - технические мелочи.
Так как не люблю данные исходные портить, то =SUMIFS(ИсходныеДанные!C:C;ИсходныеДанные!A:A;SUBSTITUTE(A2;IF(TRIM(A2)<>TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)); IF(COUNTIF(ИсходныеДанные!B:B;TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)));", "&TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));"");""););ИсходныеДанные!B:B;IF(TRIM(A2)<>TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)); IF(COUNTIF(ИсходныеДанные!B:B;TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)));TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));"");""))
для спорт инткреса ну то что FILTERXML - тормоз еще тот , а так как офис 2016 у меня, то проверять Behruz A.N. не могу. Сравнил свой и от _Boroda_ по скорости. на указанных обьемах опередил вариант Саши, но оптимизация и уход от COUNTIF =SUMIFS(ИсходныеДанные!C:C;ИсходныеДанные!A:A;SUBSTITUTE(A2;IF(TRIM(A2)<>TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)); IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));ИсходныеДанные!B:B;));", "&TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));"");""););ИсходныеДанные!B:B;IF(TRIM(A2)<>TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999)); IF(ISNUMBER(MATCH(TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));ИсходныеДанные!B:B;));TRIM(RIGHT(SUBSTITUTE(TRIM(A2);",";REPT(" ";999));999));"");"")) сразу подтянул и думаю при росте данных начнет опережать.
