Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Правильное распределение веса по динамически обновляемым диапазонам (округление без хвостов)
 
#1
16.07.2024 16:36:42
Добрый день. Подскажите, пожалуйста, есть ли идеи, какой формулой можно устранить расхождение 0,01 в распределении общего веса брутто по пропорции брутто/нетто?

Полагаю, что-то нужно думать в сторону скользящего округления, но не получается правильно сформулировать.  
Изменено: art_TD - 16.07.2024 16:39:20
 
 
 
#2
16.07.2024 17:16:17
art_TD, если расхождение небольшое, то можно его запихать в какую-нибудь конкретную позицию (разумеется с ней надо определиться)
вариант с модой по № места
=ОКРУГЛ(K2*E2;2)+(СЧЁТЕСЛИ(H$2:H2;МОДА(H$2:H$37))=1)*(СУММ(F$2:F$37)-СУММПРОИЗВ(ОКРУГЛ(K$2:K$37*E$2:E$37;2)))
Изменено: Павел \Ʌ/ - 16.07.2024 17:27:00
 
 
 
#3
16.07.2024 17:21:38
Павел \Ʌ/, обычно я вручную потом корректирую (таких случаев много в рамках одной таблицы бывает, тут просто так получилось, что один такой диапазон), но хочется уже это как-то автоматизировать.

Пробовал через такую формулу, но работает только на первый диапазон (логично). См. скрин в приложении.  
Изменено: art_TD - 16.07.2024 17:22:54
 
 
 
#4
17.07.2024 11:55:11
Возможно, есть какие-то еще идеи с простой формулой?  
 
 
 
#5
17.07.2024 12:04:30
art_TD, не получилось разобраться?
вариант погруппного распределения погрешности (для первого в группе):
...не стал прессовать всё в одну формулу ) , и немного упростил ваши
 
 
 
#6
17.07.2024 12:27:20
Павел \Ʌ/, все гениальное - просто. То, что надо, пусть и не в одной формуле (это оставим на потом). Думал, что у меня относительно простое решение с функцией ПРОСМОТР, а Ваш вариант просто замечательный. Спасибо большое!  
 
 
 
#7
19.07.2024 11:02:24
Павел \Ʌ/, добрый день. Снова к Вам за помощью, развиваю шаблон дальше в сторону усложнения.

Есть ситуации, когда вес упаковки выделен отдельной строкой (например, 21 паллета общим весом 505 кг). Находим вес 1 паллеты и распределяем на соответствующий диапазон. Пока что получилось только со вспомогательными столбцами.

Но при этом предыдущая более простая ситуация с распределением веса нарушается, так как в этом новом примере вес брутто построчно заполнен, а не диапазонами.

Возможны ли варианты унификации шаблона как на диапазоны с общий весом брутто (предыдущий расчет), так и с построчным (приложение к этому сообщению)?

Из косяков: вес паллет 505 кг, а с округлением до 2 знаков получается 505,05 кг.

ОБН. Файл перезалил.  
Изменено: art_TD - 19.07.2024 11:26:06
 
 
 
#8
20.07.2024 13:18:21
art_TD, пожалуста
Цитата
вес паллет 505 кг, а с округлением до 2 знаков получается 505,05 кг.
ещё один вариант распределения
в J2 :
=ОКРУГЛ(G2*(2*J$1-СУММ(J$1:J1))/МАКС(I$1-СУММ(G$1:G1);1);2)
 
 
 
#9
22.07.2024 08:54:17
Павел \Ʌ/, спасибо большое!  
 
 
 
#10
24.07.2024 09:58:16
Павел \Ʌ/, добрый день. Сделал по формуле полное распределение веса брутто, который подтягивается с другой вкладки, но формула какая-то слишком навороченная получилась. Подскажите, пожалуйста, можно ли как-то ее упростить или решить иным способом?

Смысл в том, что есть разбивка товаров по паллетам, но в этой разбивке нет весов - они указаны в другой сводной таблице. Вот оттуда надо подтянуть данные по артикулу и корректно распределить. По идее должно также быть сопоставление количества между двумя таблицами для правильного распределения веса из одной в другую, но это пока не рассматриваем, так как не увидел дублей в сводной таблице с весами. Надеюсь, не будет таких (но кто знает).
Изменено: art_TD - 24.07.2024 12:18:21
 
 
 
#11
24.07.2024 16:55:20
art_TD, пожалуста
Цитата
формула какая-то слишком навороченная получилась. Подскажите, пожалуйста, можно ли как-то ее упростить
=ОКРУГЛ(ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))/СЧЁТЕСЛИ($B$2:$B$9999;B2)+ЕСЛИ(СЧЁТЕСЛИ($B$1:B2;B2)=1;ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))-СЧЁТЕСЛИ($B$2:$B$9999;B2)*ОКРУГЛ(ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))/СЧЁТЕСЛИ($B$2:$B$9999;B2);2);0);2)
первое, что бросается в глаза: ПРОСМОТР() здесь лишний (от слова совсем)
потом, советую использовать имена листов покороче )
а если разберетесь с диспетчером имен,то может получится что то вроде:
=ОКРУГЛ(впр_/сч_ +(впр_-сч_*ОКРУГЛ(впр_/сч_;2))*сч_1_;2)
 
 
 
#12
24.07.2024 17:23:22
Павел \Ʌ/, спасибо! Попробую разобраться.

В целом формула более или менее приемлемая у меня была?  
 
 
 
#13
25.07.2024 06:15:08
art_TD, приветствую.
Формула в целом логичная.
Я бы вот так переписал (убрал мешающие ограничения в строках типа $B$9999, разбил на строки Alt-Enter и работаю на копии листа ВЕС):
Код
=ОКРУГЛ(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) / СЧЁТЕСЛИ($B:$B;$B2) + ЕСЛИ(СЧЁТЕСЛИ($B$1:$B2;$B2)=1;
ВПР($B2;ВЕС_Тест!$A:$F;6;0) - СЧЁТЕСЛИ($B:$B;$B2) * ОКРУГЛ(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) / СЧЁТЕСЛИ($B:$B;$B2); 2); 0); 2)
И у меня такой вопрос возник.
На остатках в 1 коп. формула работает хорошо (10/3): 10-3*3,33 = 0,01. Результат: 3.34, 3.33, 3.33.
А если будет такой вариант?
9/8 = 1,125 ≈ 1,13
9-8*1,13 = -0,04.
Результат: 1,09 + 7 раз по 1,13.
Или даже так:
270/240 = 1,125 ≈ 1,13
270-240*1,13 = -1,2.
Первый результат будет отрицательным?
Если такие варианты возможны в вашей задаче, давайте попробуем по-другому распределять копейки.
UPD. Тогда вот так можно:
Код
=ОКРУГЛ(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) / СЧЁТЕСЛИ($B:$B;$B2) + ЕСЛИ(СЧЁТЕСЛИ($B$1:$B2;$B2) <= ОКРУГЛ(ABS(100*(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) - СЧЁТЕСЛИ($B:$B;$B2) * ОКРУГЛ(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) / СЧЁТЕСЛИ($B:$B;$B2); 2))); 0); ЗНАК(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) - СЧЁТЕСЛИ($B:$B;$B2) * ОКРУГЛ(
ВПР($B2;ВЕС_Тест!$A:$F;6;0) / СЧЁТЕСЛИ($B:$B;$B2); 2)) / 100; 0); 2)
Изменено: andypetr - 25.07.2024 08:00:58
 
 
 
#14
07.08.2024 12:20:35
andypetr, добрый день. Я, честно сказать, немного недопонял. На вкладке "ВЕС_Тест" количество товара одно (102000), а на вкладке "Тест" - другое (12796). Это ведь наверно не совсем корректно для чистоты эксперимента?  
 
 
 
#15
08.08.2024 08:59:57
art_TD, добрый день.
Количество товара (ВЕС_Тест!B:B, Тест!D:D) в моих формулах не учитывается.
На листе ВЕС_Тест учитываю только 2 красных столбца (A и F - Артикул и Брутто ОБЩИЙ).
Точно так же и в вашей формуле в столбце I, которую я подробно рассмотрел - на листе 'ВЕС для ВПР из упак.листа' используется 6-й столец, F (ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ)):
Код
=ОКРУГЛ(ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))/СЧЁТЕСЛИ($B$2:$B$9999;B2)+ЕСЛИ(СЧЁТЕСЛИ($B$1:B2;B2)=1;ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))-СЧЁТЕСЛИ($B$2:$B$9999;B2)*ОКРУГЛ(ПРОСМОТР(9^9;ВПР(B2;'ВЕС для ВПР из упак.листа'!A:F;6;ЛОЖЬ))/СЧЁТЕСЛИ($B$2:$B$9999;B2);2);0);2)
 
 
 
#16
16.09.2024 17:13:45
Добрый день. Не знаю, почему, но моя связка ПРОСМОТР и ВПР распределяет вес 25160 кг полностью. А если оставить только ВПР, то вес будет 25159,98 кг, что в моем случае является ошибкой.

Касательно разницы больше/меньше, чем 0,01 (например, 0,04 и т.п.) все так, случаи такие встречаются. И, конечно, хотелось бы избегать того, чтобы вся разница уходила на какую-то одну строку. Применил Вашу формулу, но, как указал выше с функцией ПРОСМОТР.  
 
 
 
#17
17.09.2024 08:16:00
Добрый день.
А можете прислать файл, где связка ПРОСМОТР+ВПР дает результат, отличающийся от ВПР?
 
 
 
#18
17.09.2024 09:47:08
Файл большой, сюда не влезет.  
 
 
 
#19
17.09.2024 11:16:55
Del
Изменено: asesja - 17.09.2024 11:52:33
 
 
 
#20
24.09.2024 15:10:44
Добрый день. Есть вот такая задачка.

Нужно найти вес брутто каждой строки, известны веса нетто каждой строки и общий вес брутто. Нахожу коэффициент между брутто и нетто, умножаю на нетто строки, получаю брутто строки.

Проблема в том, что при обратном суммировании веса брутто отдельных строк (для проверки), вылезает хвост (на примере это 0,02 кг). А вторая проблема - у некоторых строк нетто и брутто не отличаются - например, 0,16 кг (а такого не должно быть).

Файл прилагается.

П.С. Хвост 0,02 кг я могу отнять от первой строки, например, через нахождение разницы в отдельном столбце. Но какое-то кривое решение немного.

П.П.С. Формулами, пожалуйста  :)  Excel 2016
Изменено: art_TD - 24.09.2024 15:34:05
 
 
 
#21
25.09.2024 07:42:56
art_TD, приветствую.
Цитата
Проблема в том, что при обратном суммировании веса брутто отдельных строк (для проверки), вылезает хвост (на примере это 0,02 кг).
См. колонки Q и R.
В Q распределяется хвост (M) по 0.01 для первых строк, где "Разница между Н и Б" отлична от нуля.

Цитата
А вторая проблема - у некоторых строк нетто и брутто не отличаются - например, 0,16 кг (а такого не должно быть).
0,16 * 1,030264005 = 0,1648422408 = 0,16 (в столбце K стоит округление до 2-х знаков).
 
 
 
#22
25.09.2024 08:51:28
andypetr, здравствуйте! Спасибо за помощь!

Округление так и требуется - до 2 знаков после запятой. При этом построчные нетто и брутто не должны быть равны, то есть брутто всегда хотя бы на 0,01 должен быть больше нетто. Тут пока не знаю, как решить с распределением.

В принципе хвост распределить на те строки, где нетто и брутто совпадают. Но тут могут быть случаи, что либо хвоста не будет, либо строк для увеличения брутто будет больше, чем сам хвост (например, хвост 0,01, а строк 2 или 3).
Изменено: art_TD - 25.09.2024 09:30:03
 
 
 
#23
25.09.2024 13:19:35
Исправил столбцы K (чтобы минимум 0.01 разница была) и Q.
 
 
 
#24
25.09.2024 16:15:07
andypetr, посмотрите, пожалуйста, столбец U, там проверка разницы между новым брутто и нетто. Ячейка U5 показывает равенство.

Из вариантов вижу - вычитать хвост из первой строчки диапазона (но вдруг вообще в минус будет?), либо из наибольшего значения диапазона.

Либо еще как-то распределять 0,01 на значения от большего к меньшему...
Изменено: art_TD - 25.09.2024 16:20:56
 
 
 
#25
26.09.2024 08:04:46
Исправил столбец Q (чтобы минимум 0.01 разница была), формула даже проще стала, до этого чуть перемудрил.
 
 
 
#26
26.09.2024 08:44:12
andypetr, спасибо большое! То, что надо.
 
 
 
#27
14.10.2024 14:45:11
Добрый день!

Возник еще такой вопрос. Надеюсь, донесу мысль правильно.

Есть ситуации, когда построчные веса брутто заполнены и расчет построчных весов не требуется. Моя формула деления брутто/нетто и умножения на нетто построчный дает сбой. Веса расходятся.

Файл для ознакомления прилагается.

Добавил свое видение решения.
Изменено: art_TD - 14.10.2024 15:34:15
 
 
 
#28
15.10.2024 07:38:36
Добрый день!
В K2:
Код
=ЕСЛИ(СЧЁТЕСЛИМН($G:$G;$G2;$D:$D;">0") = СЧЁТЕСЛИМН($G:$G;$G2); $D2; ОКРУГЛ($J2*$C2;2))
Если позиций с заполненным вес-брутто (столбец D) столько же, сколько всего позиций (столбец G), то берём готовый вес-брутто, иначе вычисляем.
 
 
 
#29
15.10.2024 16:59:05
andypetr, спасибо, изящное решение!  
 
 
 
Страницы: 1
Читают тему
Наверх