Страницы: 1
RSS
Суммирование сверху вниз до достижения статичного числа.
 
Добрый день.
Имеется проблема не могу понять как мне это сделать введу своей безграмотности.
Имеется таблица с учетом купли продажи разных валют или ценных бумаг.
Требуется чтобы при совершении продажи(валюты,Ценой бумаги) закрывались предыдущие позиции.
Т.е. "строка 2" "В столбце B = продажа", "В столбце C = название бумаги или валюты" "D = Количество проданных бумаг"
Так вот из столбца D Количество проданных что бы это количество заполнялось автоматически в столбец О, тем самым закрываю старые позиции. Как только "21 строка" заполняется, то кол-во переходит на строку 20. И так далее.

В ячейке B3 указано  вид Операции "Продажа" , в ячейке С3 "USD" , в ячейке D3 количество проданного.
В ячейку O21 должно попасть количество проданного. Т.е. В ячейке О21 должно происходит суммирование Проданного до достижения количества купленного в ячейке D21. При достижении данного значения в данном случае D21=10, суммирование переходит на ячейку выше т.е. О20 и т.д. Т.е. Если B3  = Продажа  С3 = "USD" D3 = 10 то О21 = 10 а О20=2

Какую функцию я должен использовать?
Спасибо большое за помощь.
 
Пример сделал как мог.
Проверяйте.
Код
=МИН(D2;СУММЕСЛИ(B$2:B$98;"Продажа";D$2:D$98)-СУММ(O3:O$99))
Изменено: АlехМ - 01.06.2020 17:51:16
Алексей М.
 
=IF(B2="Покупка";IF(SUMIFS(D$1:$D1;$B$1:B1;"Продажа";C$1:$C1;C2)>SUMIFS(D2:$D$21;B2:$B$21;B2;C2:$C$21;C2);D2;MAX(0;SUMIFS(D$1:$D1;$B$1:B1;"Продажа";C$1:$C1;C2)-SUMIFS(D2:$D$21;B2:$B$21;B2;C2:$C$21;C2)+D2));"")

Выше Алексей не учел название, но даже в его варианте это не сложно добавить.
Мой вариант не базруется на расчетах проделанных ранее. поэтому и длинннее.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал: не учел название
Хорошо догадался что нужно. ;)  
Алексей М.
 
было трудно, но вкурил  :D
По вопросам из тем форума, личку не читаю.
 
АlехМ,  БМВ, Спасибо Огромное. Большого Вам здоровья.
Изменено: Никита Федоров - 01.06.2020 18:16:57
 
Добрый день. Еще раз. Подскажите как решить еще одну проблему.

Есть функция =СУММЕСЛИМН(События!K:K;События!C:C;"USD";События!B:B;"Покупка")-СУММЕСЛИМН(События!K:K;События!C:C;"USD";События!B:B;"Продажа")
Как задать дополнительный параметр, что бы суммирование происходило с верху вниз до достижения статичного числа. Т.е. Сумма столбца J при вводе числи 112 будет равно 4975,04.

Во вложении пример. В ячейке P3 функция которую нужно поправить что бы при вводе в ячейку P6 получить результат из ячейки P7

 
Цитата
Никита Федоров написал:
Сумма столбца J при вводе числи 112 будет равно 4975,04.
почему? Продажи суммируются с покупками? 112 это сумма по указанному диапазону.
По вопросам из тем форума, личку не читаю.
 
Еще один пример делать не стану, а реальный файл при открытии глючит.
Суммирование до статичного числа. Может быть так
=МИН(статичное число;сумма столбца J)
Алексей М.
 
Я прошу прощения. Может из за того что у меня "офис 2019", вроде не глючит. На всякий случай  повторно сохранил файл.

Попробовал Ваш пример, увы не работает.
Формулу ввел в ячейку P8, статичное число в ячейке P6. При вводе в ячейку P6, в ячейке P8=P6
 
С именованными формулами для сокращения основной.
Основная формула
Код
=СУММПРОИЗВ((Вид="Покупка")*(Название="USD")*Соткр)-СУММПРОИЗВ((Вид="Продажа")*(Название="USD")*Соткр)
Сочетание клавиш CTRL+F3 выводит на экран окно Диспетчера имен
Алексей М.
 
Есть решение и по второму вопросу отличное от Алексей, но массивно мультяшное :-)
Код
=ПРОСМОТР(P6;МУМНОЖ(--(СТРОКА(D2:D21)>=ТРАНСП(СТРОКА(D2:D21)));D2:D21);МУМНОЖ(--(СТРОКА(D2:D21)>=ТРАНСП(СТРОКА(D2:D21)));(2*(B2:B21="Покупка")-1)*K2:K21*(C2:C21="USD")))


Не верьте, это проделки Модератора, Не мог  я такое написать, я и слов то таких не знаю.  :D
=LOOKUP(P6;MMULT(--(ROW(D2:D21)>=TRANSPOSE(ROW(D2:D21)));D2:D21);MMULT(--(ROW(D2:D21)>=TRANSPOSE(ROW(D2:D21)));(2*(B2:B21="Покупка")-1)*K2:K21*(C2:C21="USD")))
Изменено: БМВ - 02.06.2020 16:16:36
По вопросам из тем форума, личку не читаю.
 
БМВ, не знаю как вы поняли что нужно сделать, но эта формула работает как нужно
Теперь надо разобраться что за что отвечает.)

Огромное спасибо.
 
Ой... я формулу БМВ, немного сократил, на 26%
Код
=ВПР(P6;МУМНОЖ(Ч(СТРОКА(D2:D99)>=ТРАНСП(СТРОКА(D2:D99)));ЕСЛИ({1;0};D2:D99;(2*(B2:B99="Покупка")-1)*K2:K99*(C2:C99="USD")));2)
Изменено: АlехМ - 02.06.2020 18:51:45
Алексей М.
 
Ну тогда уж и дальше в избу можно входить
=VLOOKUP(P6;MMULT(N(ROW(D2:D21)>=TRANSPOSE(ROW(D2:D21)));IF({1\0};D2:D21;(2*(B2:B21<"Пр")-1)*K2:K21*(C2:C21="USD")));2)
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх