Страницы: 1
RSS
Посчитать сумму только положительных чисел в определенных ячейках
 
Добрый день! Даны столбцы и строки, в них значения. Нужно посчитать сумму всех положительных значений белых ячеек. Формула =СУММЕСЛИ(BK6;BH6;BE6;BB6;AY6;AV6;AS6;AP6;AM6;AJ6;AG6;AD6;AA6;X6;U6;R6;O6;L6;I6;F6;">0") не работает потому, что для формулы СУММЕСЛИ нужен именно диапазон, а у меня ограниченное кол-во столбцов идущих один через три. Формула суммы считает все числа подряд, но зато работает как с диапазоном, так и с конкретными ячейками.

И ещё прошу помощи, как задать условие, что бы результат вычисления формулы в каждой белой ячейке округлялся к числу, кратному моему параметру, который указан в соседней ячейке? То есть допустим по формуле =((D5-E5)+(D5/2)) у меня вычисляется, что мне необходимо 20 единиц товара, но минимальная отгрузка этого товара 24 единицы, и это указано в соседней ячейке. Как это условие сразу заложить в формуле, что бы в ячейке выводился результат с кратным числом?
Изменено: Zukutoke - 18.03.2015 10:33:45
 
Так как у вас ячейки, которые нужно просуммировать, не имеют заголовков, то можно через функцию СУММЕСЛИМН
Код
=СУММЕСЛИМН($F2:$BK2;$F2:$BK2;">0";$F$1:$BK$1;"")
и протянуть вниз
Для округления с заданной кратностью - функция ОКРВВЕРХ или ОКРВВЕРХ.ТОЧН - выберите более удобную, у них есть различия (почитайте о них в справке).
F1 творит чудеса
 
Спасибо, но почему-то по этой формуле результат получается в первой строчке 36 а по факту 38, значит какие-то ячейки не суммирует??  
Изменено: Zukutoke - 17.04.2015 10:29:51
 
У Вас и есть 36,  а не 38 - проверьте сами еще раз.
 
Цитата
Влад написал: У Вас и есть 36,  а не 38 - проверьте сами еще раз.
Уже несколько раз пересчитал, всё равно получается 38, а не 36 О_о
 
Нет, 36. 0,5+0,5=1, а не 2 - Вы видите на экране округленные цифры.
 
=СУММЕСЛИМН(BK5:BK5;">0";BH5:BH5;">0";BE5:BE5;">0";BB5:BB5;">0";AY5:AY5;">0";AV5:AV5;">0";AS5:AS5;">0";AP5:AP5;">0";AM5:AM5;">0";AJ5:AJ5;">0";AG5:AG5;">0";AD5:AD5;">0";AA5:AA5;">0";X5:X5;">0";U5:U5;">0";R5:R5;">0";L5:L5;">0";I5:I5;">0";F5:F5;">0")

И такая формула тоже не помогает. Слишком много аргументов....
 
Вы не поняли. Поставьте в Ваши формулы округление, и предложенная Вам общая формула будет правильно считать.
 
Округление нужно после того, когда сумма положительных чисел в ячейках указанных будет посчитана, а там сумма считается не верно почему-то, посчитайте сами.
А как достичь округления результата в белой ячейке в каждом столбце на значение, кратное числу которое находится в указанной ячейке? При условии, что округление должно происходить только при положительном результате (">0";), а при отрицательном результате - переводить число в ноль (0)?

Имеем формулу =((((E9/22)*20)*2)-F9)
если эта формула выдаёт положительный результат - её нужно округлить на число, кратное числу указанному в соседней ячейке D9.  
Изменено: Zukutoke - 17.04.2015 10:30:31
 
Вам нужна сумма положительных чисел уже округленных по кратности или только подсчитаных? Плюс еще раз обращаю Ваше внимание, что при обычном расчете (без округления) у Вас на самом деле появляются дробные числа (0,5), которые на экране видны как целые (1), из-за этого сумма в первой строке 36, а не 38.
 
Цитата
Оба варианта нужны. В одном столбце чтоб добавить округленные числа по кратности,а  в другом подсчитанные.
Теперь я понял вас. Тогда у меня назрел следующий вопрос: а каким образом мне достичь таких результатов, что бы я распечатывал эту таблицу и если нужно на калькуляторе мог проверить сумму значений и она у меня сходилась с той, что в таблице?  
Изменено: Zukutoke - 18.03.2015 13:33:06
 
Цитата
Zukutoke написал: При условии, что округление должно происходить только при положительном результате (">0", а при отрицательном результате - переводить число в ноль (0)?
Если так, то в белых ячейках сделайте следующее изменение:
=МАКС(ОКРВВЕРХ(ваша_формула_расчета;нужная_кратность);0)
тогда в белых ячейках будет либо 0, либо положительное число, округленное вверх до нужной вам кратности (24, 12, или 20, какая нужна).
В итоговой ячейке тогда можно считать просто сумму белых - отрицательных там уже не будет. И сумма эта уже тоже сама по себе будет с нужной кратностью
F1 творит чудеса
 
Цитата
Вот результат. Сделал я такую формулу =МАКС(ОКРВВЕРХ(((((D84/22)*20)*2)-E84);D84);0)
Но теперь неважно результат отрицательный или положительный, он сразу округляет до числа указанного в ячейке D84 (число 12). Раньше без округления у меня был там результат -109 и он не учитывался в итоговой сумме, а после использования этой формулы, получается результат 24, то есть число кратное ячейке D84, только там  у меня был указан параметр 12.

В итоге мы имеем следующее: результат теперь в ячейке округляется, но не соблюдается условие, что если результат первой формулы меньше нуля (<0) то он должен отображаться в виде нуля, а если результат больше нуля, то он должен отображать число кратное указанному нам(ячейка D84 число 12);  
Изменено: Zukutoke - 18.03.2015 13:27:47
 
Цитата
Zukutoke написал:
...а каким образом мне достичь таких результатов, что бы я распечатывал эту таблицу и если нужно на калькуляторе мог проверить сумму значений и она у меня сходилась с той, что в таблице?
Все Ваши формулы для одного столбца (обычный расчет) написать в таком виде:
Код
=ОКРУГЛ(E8-F8+(E8/2);)
для другого (кратное округление):
Код
=ЕСЛИ(E8-F8+(E8/2)>0;ОКРВВЕРХ(E8-F8+(E8/2);F8);0) 
Изменено: Влад - 18.03.2015 12:33:29
 
Цитата
Zukutoke написал: Раньше без округления у меня был там результат -109 и он не учитывался в итоговой сумме, а после использования этой формулы, получается результат 24, то есть число кратное ячейке D84, только там  у меня был указан параметр 12.
У вас ошибка в формуле. Раньше там стояла такая формула: =((((E84/22)*20)*2)-F84)
а вы поставили =МАКС(ОКРВВЕРХ(((((D84/22)*20)*2)-E84);D84);0)
Короче, ссылки слетели.
Правильно будет
Код
=МАКС(ОКРВВЕРХ(((((E84/22)*20)*2)-F84);D84);0)
и тогда результат 0. Внимательнее, пожалуйста

Ок, меняем на
=ОКРВВЕРХ(МАКС(ваша_формула_расчета;0);нужная_кратность)
то есть для вашего примера
Код
=ОКРВВЕРХ(МАКС(((((E84/22)*20)*2)-F84);0);D84) 

тогда сначала посчитается функция, положительное останется, а отрицательное станет 0, и только потом будет округление.
Изменено: Максим Зеленский - 17.04.2015 10:30:56
F1 творит чудеса
 
Вы гений! Огромное спасибо!
Остался последний вопрос: а каким образом нам сделать так, что если при вычислении (E84/22)*20)*2)-F84) у нас будет ноль, то результат преобразуется в число кратное ячейке D84 ? У меня в тех ячейках, где параметры Продажа 0 и Остаток 0 результат соответственный высвечивается, а необходимо что бы в случае отсутствия продаж и остатков заполнялась ячейка числу кратному ячейке D84
Изменено: Zukutoke - 17.04.2015 10:31:15
 
Давайте сделаем так - вы сначала удалите излишнее цитирование из своих сообщений, а то модераторы нас заругают.
F1 творит чудеса
 
Цитата
Zukutoke написал:
если при вычислении (E84/22)*20)*2)-F84) у нас будет ноль, то результат преобразуется в число кратное ячейке D84
оберните эту формулу в проверку на 0:
Код
ЕСЛИ(формула=0;1;формула)

Код
=ОКРВВЕРХ(МАКС(ЕСЛИ(E84/22*20*2-F84=0;1;E84/22*20*2-F84);0);D84)

получится так:
формула = 0, результат = 1 округленный до кратности D84
формула <0, результат = 0
формула >0, результат = Х округленный до кратности D84

или еще вариант (не проверял, но должно сработать)
Код
=ОКРВВЕРХ(--ТЕКСТ(E84/22*20*2-F84;"0,00;\0;\1");D84)

но это уже извращение :)
F1 творит чудеса
 
Супер! Огромное спасибо!
Пытаюсь усовершенствовать эту таблицу.
Необходимо следующее-
=ОКРВВЕРХ(МАКС(ЕСЛИ(E9/22*20*2-F9=0;1;E9/22*20*2-F9);0);D9)
Это наша формула.
Нужно добавить  
Если  при условии Е9=0, а F9<(D9/2) то G9=D9
То есть должно получиться: если остаток F9 меньше половины(D9/2) минимальной партии D9 при  (E=0) нулевых продажах, то G9=D9
Изменено: Zukutoke - 17.04.2015 10:31:49
 
Код
=ОКРВВЕРХ(МАКС(ЕСЛИ(ИЛИ(E9/22*20*2-F9=0;И(E9=0;F9<D9/2));1;E9/22*20*2-F9);0);D9)

проверьте
F1 творит чудеса
 
Отлично работает! Вы не представляете как вы мне упростили жизнь)
У меня такой вопрос: при копировании этой формулы в следующую ячейку, то все переменные подстраиваются под значения нужные, кроме одного : это D9 т.к. этот столбец у нас единственный статичный и должен использоваться из любой ячейки. То есть эта формула, находясь в любом следующем столбце, должна статично ссылаться на столбец D

Можно ли это как то сделать? Ато замучался уже, у меня 20 столбцов и в каждом нужно после копирования менять подстроившийся столбец обратно на столбец D.  
 
"D9" заменяете на "$D9".
 
Невероятно, как всё просто! Боже, что бы я без вас делал добрые вы люди!)) Выкладываю сюда свой файл, со всеми используемыми формулами.
Расшифрую все переменные, уверен, что людям занимающимся закупками очень пригодится такая таблица куда можно вбивать нужные вам данные.

Во вложении и здесь пояснение к формуле расчёта заказа  =ОКРВВЕРХ(МАКС(ЕСЛИ(ИЛИ(E8/28*25*2-F8=0;И(E8=0;F8<$D8/2));1;E8/28*25*2-F8);0);$D8)
28 это период, за который у нас продавался товар
25 это период, на который мы планируем продажи
2 это коэффицент роста продаж (заказ впрок)
Мы кол-во товара проданного за наш период делим на кол-во дней которые он продавался
таким образом мы вычисляем средние продажи за каждый день
дальше мы этот результат умножаем на планируемый период, на который расчитывается заказ (от заказа до заказа)
вычитаем остаток на данный момент ( который хранится у нас)
И если продаж не было, но остаток меньше половины минимальной партии или т.п.
То значит нам формула в заказ отправляет кол-во товара
которое у нас указано в ячейке мин. Партии
Система расчитывает, исходя из средних продаж и планируемых дней продаж, хватит нам товара или нет. Если нет - то заказывает ещё товара столько, что бы хватило продать основываясь на предыдущих показателях, а так же прибавляет поправочный коэффицент роста продаж
Дальше формула подсчитывает сумму заказов со всех магазинов.
Будут вопросы - пишите на почту [Здесь была почта, а должна быть в профиле]


Всем огромное спасибо!
Изменено: Zukutoke - 18.03.2015 22:23:05
 
Zukutoke, не нужно цитировать без необходимости. Кнопка цитирования НЕ ДЛЯ ответа.
Почтовый адрес в сообщении удалён - читайте Правила.
Страницы: 1
Наверх