Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Скользящая сумма массивов
 
#1
18.06.2018 12:06:50
Доброго дня! Столкнулся с проблемой поочередного добавления массивов в сумму по периодам

Интуиция: Есть временной период от 0 до 20 (Например, месяцы). Есть бинар: в определенные месяцы этого временного периода возникает событие (единица), остальные нули.
Есть показатель (Например, отдача/выручка). Он изменяется произвольными значениями (например, тысячи рублей) в течение более короткого временного периода (например от 0 до 8, то есть "Продолжительность" = 9 месяцев)

Событие (единица) — это начало движения выручки. И по большому периоду в зависимости от возникновения единицы массивы выручек складываются. Получаются такие скользящие по периодам массивы выручек.

Пытаюсь формулами массива реализовать это, но никак не выходит
Как это сделать формулой? Пример прикрепляю

Спасибо!
Изменено: exxecuz - 18.06.2018 12:24:16
 
 
 
#2
18.06.2018 12:13:23
exxecuz, логика непонятна ни в примере, ни в сообщении.
Интуитивно догадываюсь, что Вам могут пригодиться функции СМЕЩ() и ДВССЫЛ(), а также знание абсолютных/относительных/смешанных ссылок на диапазоны. Может, всё-таки, сделаете пример по логике: как есть и как надо?
 
 
 
#3
18.06.2018 12:15:20
поддерживаю, логика расчетов непонятна
Изменено: Stics - 18.06.2018 12:15:30
 
 
 
#4
18.06.2018 12:24:00
Прошу прощения, изменил
 
 
 
#5
18.06.2018 13:02:54
Так нужно?
=СУММПРОИЗВ(Ч(СМЕЩ($C8;;ПОДСТАВИТЬ(СТОЛБЕЦ()-СТОЛБЕЦ($C12:C12)*$C4:C4;СТОЛБЕЦ();-1))))
Добавлено
Попроще вариант
=СУММПРОИЗВ(Ч(СМЕЩ($B8;;(СТОЛБЕЦ()-СТОЛБЕЦ($B12:B12))*$C4:C4)))
Изменено: _Boroda_ - 18.06.2018 13:25:20
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#6
18.06.2018 13:08:39
exxecuz, примерно понял. Обычной формулой сделать такое затруднительно, разве что с доп. таблицей. Потому что количество "единиц" за время периода выручки (9 месяцев) неизвестно и должно определяться динамически (т.е. в цикле).
Вариант макросом или пользовательской функцией Вас устроит или принципиально формулами считать?

P.S. Формулой массива, вероятно, тоже можно ухитриться, но тут лучше подождать более опытного специалиста в них, чем я.
Изменено: Irregular Expression - 18.06.2018 13:13:32
 
 
 
#7
18.06.2018 13:14:27
Цитата
Irregular Expression написал:
Формулой сделать такое затруднительно

Irregular Expression, чуть выше посмотрите  :D
Уверен, что есть и еще варианты, я просто "в лоб" сделал, без хитростей.
Кстати, немассивная  :D  
Изменено: _Boroda_ - 18.06.2018 13:15:44
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#8
18.06.2018 13:18:23
Цитата
_Boroda_ написал:
чуть выше посмотрите  
Да, вижу... Но подобная формула как раз отлично иллюстрирует понятие "затруднительно", на мой взгляд :).
 
 
 
#9
18.06.2018 13:21:37
_Boroda_, кажется подошел вариант
Да, сам я долго бы до этого додумывался)

Спасибо всем!
 
 
 
#10
18.06.2018 13:24:41
Довложил там попроще вариант
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#11
18.06.2018 14:27:18
_Boroda_, а можете объяснить, что делает функция Ч() в Вашей формуле? Без неё не работает, но в документации подобного применения не нашёл.
 
 
 
#12
18.06.2018 14:50:44
Ч в число преобразовывает
Функция скрыта, ексель парой таких владеет, РАЗНДАТ например
 
 
 
#13
18.06.2018 15:01:09
Цитата
exxecuz написал:
Ч в число преобразовывает
Судя по этой формуле, Ваши сведения неактуальны :). Что она преобразовывает в число? Там в качестве аргумента произведение диапазонов ячеек, т.е. массив.
Кроме того, если результатом Ч() является число, то зачем тогда использовать это число как единственный аргумент СУММПРОИЗВ()?
 
 
 
#14
18.06.2018 15:31:48
Вы знаете, словами это объяснить довольно сложно. В данном случае она, конечно, преобразует текстовые значения в нули, а числовые оставляет как есть. Но не только. Иногда бывает так, что вроде бы внутри формулы получается нормальный массив, а вот агрегироваться он не хочет
Например, на том же файле пишем формулу =СМЕЩ(C8;;СТОЛБЕЦ(A1:F1)), выделяем ее в строке формул, жмем F9, видим массив {19;64;39;2;60;118}. Тогда формула =СУММПРОИЗВ(СМЕЩ(C8;;СТОЛБЕЦ(A1:F1))) по идее должна дать 302. Но не дает. А вот если СМЕЩ обернуть вовнутрь функции Ч =СУММПРОИЗВ(Ч(СМЕЩ(C8;;СТОЛБЕЦ(A1:F1)))), то тогда все нормально считает, хоть там внутри точно такой же массив {19;64;39;2;60;118}.
У меня лично более-менее связного объяснения этому нет. Принимаю как данность
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#15
18.06.2018 15:36:25
Цитата
exxecuz написал: Функция скрыта,
С чего Вы взяли? Это совершенно стандартная функция, живет в мастере функций - Проверка свойств и значений (это для 2013, для других может быть в иной категории, Майкрософт периодически их перетасовывает зачем-то)

Вот стандартная справка по ней
https://support.office.com/ru-ru/article/%D0%A7-%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D0%A7-a6...
И совершенно верно, она действительно преобразовывает в число. Но не только...
Изменено: _Boroda_ - 20.06.2018 01:35:27
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#16
18.06.2018 15:46:32
_Boroda_, спасибо за ответ! Да, можно предположить, что там какая-то неявная типизация массива происходит...
 
 
 
#17
18.06.2018 15:51:00
_Boroda_,
Присоединяюсь, спасибо. Интересное свойство. Достаточно часто встречаю ситуацию, которую Вы описали. Буду знать что Ч может выручить)
 
 
 
#18
18.06.2018 17:15:15
_Boroda_,
Всплыла, конечно, тут же задача усложнения. А если у нас не бинар, а количество возникновения событий? (Например, возникновение нескольких выручек в один период). Смещение умножать никак не хочет

Как тогда можно?

Спасибо!
 
 
 
#19
18.06.2018 17:26:40
Так?
=СУММПРОИЗВ(Ч(СМЕЩ($B8;;(СТОЛБЕЦ()-СТОЛБЕЦ($B12:B12))*ЕЧИСЛО($C4:C4)))*$C4:C4)
Скажи мне, кудесник, любимец ба’гов...
 
 
 
#20
18.06.2018 17:29:32
_Boroda_, да, кажется работает, спасибо! С проблемами, но разобрал зачем ЕЧИСЛО. Не хочет мой мозг пока массивы воспринимать)
 
 
 
Страницы: 1
Наверх