суммировать если

Автор DragoN1111, 26 января 2016, 16:23

0 Пользователи и 1 гость просматривают эту тему.

DragoN1111

Задача: суммировать числа из первого столбца до первого встреченного нуля результат заносить во второй столбец.
Нули переносим как есть.
В приложенном файле простенький пример что должно получится.
Существует возможность сделать это формулами или нужен макрос.

JohnSUN

#1
Вообще-то,
=IF(B2;IF(B2=B1;0;MATCH(1-B2;B3:$B$10000;0));0)
Но двойка в C7 и ноль в C9 сбивают с толку - поэтому в формуле 1-B2, а не просто 0
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DragoN1111

только почему то после  Вашей формулы появляются лишние нули ???
Т.е. результирующий столбец должен укорачиваться, вместо нескольких встреченных подряд единиц должна стоять их сумма ,1,1,1,=3, а нули переносим как есть 0,0,0=0,0,0
С уважением.
Прицепил файл с зависимостями.

JohnSUN

А, теперь становится понятными эти пары нулей между значащими значениями... То есть столбец результата короче исходного не просто так? Ну, тогда макро-функция будет проще и быстрее, чем формула из встроенных функций...
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DragoN1111

Да столбец укорачивается специально, а с макро-функцией не поможете? В программировании не силен к сожалению. :(

JohnSUN

Часа через два-три, ок? (И не "выкай" больше - тут у нас демократия  ;D)
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DragoN1111

огромное спасибо заранее :)

JohnSUN

#7
Как-то так, наверное...
Надеюсь, уровень безопасности макросов (меню Сервис-Параметры-Безопасность и там кнопка Безопасн.макросов...) выставлен в Средний или Низкий? А то ведь не взлетит...

Пишешь в C2 (или в какую хочешь ячейку) формулу
=CLCSRS(B2:B51)
или не B2:B51, а какой там тебе диапазон обрабатывать надо.
Ввод формулы заканчиваешь не просто Enter или Tab, а Ctrl+Shift+Enter - это важно! Эта комбинация клавиш покажет Calc'у, что речь идёт о "формуле массива", которая возвращает значение не для одной ячейки, а для нескольких
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DragoN1111

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

JohnSUN

#9
А, ну тогда перед последним циклом можно добавить еще один - задом наперёд, снизу вверх пробежаться от n до 1 и скопировать результаты в хвост aInp, а пробелами (или если так хочется - нулями) протереть начало массива...

То есть окончание функции теперь будет выглядеть так:
REM Начиная с ячейки n+1 остались первоначальные нолики и единички. Затрем их полученными результатами
REM (сдвинем участок массива от 1 до n к хвосту массива):
For i = n To 1 Step -1
aInp(UBound(aInp) - n + i, 1) = aInp(i, 1)
Next i
REM А начало массива затрём просто нулями
For i = 1 To UBound(aInp)-n
aInp(i, 1) = 0
Next i
REM И присвоим значению функции этот массив:
clcSrs = aInp
End Function
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне

DragoN1111

ОК РАБОТАЕТ :)
А можно еще один вопрос как сделать так чтобы при добавлении значений в столбец В, столбец С автоматически удлинялся?

rami

Цитата: DragoN1111 от 27 января 2016, 13:53А можно еще один вопрос как сделать так чтобы при добавлении значений в столбец В, столбец С автоматически удлинялся?
Любая функция пересчитывается при изменении исходных данных в диапазоне ссылки. В формуле CLCSRS() увеличьте диапазон, например=CLCSRS(B2:B99)

DragoN1111

я использую формулу =CLCSRS(L1.N1:INDIRECT(CONCATENATE("$N";"$";$P4))) где $P4 это ссылка на формулу =COUNTA(L1.A1:A70000) которая выдает количество заполненных ячеек на листе L1, но при добавлении в столбцы A-N новых данных приходится заново пересчитывать макросом, т.е. после предыдущего расчета макросом длинна заполнения столбца была равна  на некоторое количество меньше а формула массива автоматически не удлиняется к сожалению. если же использовать заведомо больший диапазон то конец опять заполняется нулями.
Т.е. идея была какая: берем массив с листа 1 на листе 2 производим обработку макросом, получаем результат если в массиве добавились данные результат автоматически пересчитался. и дальше идет обработка на основе последних данных для чего и нужна была сортировка обратная.

DragoN1111

можно это как-то сделать формулами что бы не напрягать

JohnSUN

Цитата: DragoN1111 от 27 января 2016, 17:03
Т.е. идея была какая: берем массив с листа 1 на листе 2 производим обработку макросом, получаем результат если в массиве добавились данные результат автоматически пересчитался. и дальше идет обработка на основе последних данных для чего и нужна была сортировка обратная.
Вообще-то, эта идея была изложена несколько другими словами, нет?

Давай начнём с самого начала.
Столбец с ноликами-единичками может меняться по высоте. Ты обещаешь, что "дырок" (ячеек без 0 или 1) в этом столбце не будет.
Всё что справа-слева от этого столбца нас не интересует и не будет интересовать.
В какое-то определённое место этой же книги ты хочешь в реальном времени получать столбец той же высоты, вначале нужное количество нулей - затем результаты подсчета.
Ниже столбца-результата нет ничего важного - если что-то попадётся, то можно смело стереть.
Так что ли?
Владислав Орлов aka JohnSUN
Благодарить-не зазорно.
Подарить благо создателям офиса, нашему ресурсу, мне