Новости:

Новая редакция правил форума: 2.4. Если вопрос или ответ содержится во вложенном файле, все-равно кратко описывайте в сообщении вопрос или суть решения. Это необходимо, чтобы тему можно было найти через поиск.

Главное меню

CУММЕСЛИМН(...; Условие1 ИЛИ Условие2)

Автор arxon, 06.08.2014, 19:40

« назад - далее »

arxon

Уважаемые господа, приветствую!
Есть вопрос, на сайте подобная тема обсуждалась, но она мне не помогла.
Задача: посчитать сколько требуется транспортных средств для погрузки на него груза из таблицы.
У меня есть столбцы с параметрами груза, т.е. столбец с длиной, шириной, высотой, весом, а также количеством позиций.
Каждое наименование груза имеет свое количество единиц (позиций) груза.
Каждая единица груза должна быть погружена на транспортное средство исходя из своих габаритных параметров.

на тип транспорта№1 может быть погружен груз с длиной не более 20м, шириной не более 2,45, высотой не более 2,45, весом не более 25000кг:
=СУММЕСЛИМН(диапазон количества груза;$D$2:$D$178;"<20";$E$2:$E$178;"<2,45";$F$2:$F$178;"<2,45";$G$2:$G$178;"<25000")

А вот как написать формулу, если на тип транспорта№1 может быть погружен груз с длиной более 20м ИЛИ шириной более 2,45 ИЛИ высотой более 2,45 ИЛИ весом более 25000кг?

Этот расчет будет очень примитивный, т.к. каждое грузое место расположит на отдельную машину, но пока хотя бы так.
Заранее благодарю за посильную помощь!

iron priest

и в каком месте указываются критерии, в какой ячейке производить расчет?

zs

=СУММЕСЛИ($D$2:$D$178;"<20";$L$2:$L$178)+СУММЕСЛИ($E$2:$E$178;"<2,45";$L$2:$L$178)+СУММЕСЛИ($F$2:$F$178;"<2,45";$L$2:$L$178)+СУММЕСЛИ($G$2:$G$178;"<25000";$L$2:$L$178)
)

arxon

Господа, спасибо за участие, но задача не решена.
я немного исправил формулу и поставил ее в ОРАНЖЕВУЮ ячейку внизу (см.Вложение).

=СУММЕСЛИ($D$2:$D$178;">20";$H$2:$H$178)+СУММЕСЛИ($E$2:$E$178;">2,45";$H$2:$H$178)+СУММЕСЛИ($F$2:$F$178;">2,45";$H$2:$H$178)+СУММЕСЛИ(G2:G178;">25000";$H$2:$H$178)

Сумму ячейка выдает 447. Это абсурд, т.к. всего единиц груза 232 (ЗЕЛЕНАЯ ячейка) и больше единиц транcпорта, чем 232 физически быть не может.
Почему формула неправильно посчитала? Потому что если вы посмотрите на первую же позицию в таблице (СЕРЫЕ ячейки), то увидите, что и длина, и ширина, и высота, и вес превышает параметры в формуле. следовательно формула, обращаесь к столбцу "количество" считает как 1+1+1+1=4 транспортных средств для перевозки данного груза. Что неверно.
Если хоть один параметр у отдельногогруза превышает заявленный в формуле, тогда должено прибавиться 1 транспортное средство.
Резюмирая: мне кажется, что для решения вопросов по размещению груза на разных типах транспортных средств, мне нужно прояснить как применять в данной таблице условия "И" (тут все ясно), "ИЛИ".

Прикладываю измененный файл.

_Boroda_

#4
Цитата: arxon от 07.08.2014, 11:32Сумму ячейка выдает 447. Это абсурд
Конечно. Формулу вот так напишите
=СУММПРОИЗВ((($D$2:$D$178>20)+($E$2:$E$178>2,45)+($F$2:$F$178>2,45)+(G2:G178>25000)>0)*$H$2:$H$178)
и в G2 такую =ЕСЛИОШИБКА(L2/H2*1000;)
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

arxon

Цитата: _Boroda_ от 07.08.2014, 11:48
Цитата: arxon от 07.08.2014, 11:32Сумму ячейка выдает 447. Это абсурд
Конечно. Формулу вот так напишите
=СУММПРОИЗВ((($D$2:$D$178>20)+($E$2:$E$178>2,45)+($F$2:$F$178>2,45)+(G2:G178>25000)>0)*$H$2:$H$178)[code]
и в G2 такую [code]=ЕСЛИОШИБКА(L2/H2*1000;)


Славно, вроде работает!) спасибо тбе, добрый человек. Я правда не понял что за формула ">0" и почему появилась "=ЕСЛИОШИБКА".
Если ты обратил внимание, у меня еще в таблице два отдельных лота, поэтому я использовал следующую формулу (например в  L192):

=СУММЕСЛИМН(количество тонн; вес одной позиции;"<=25000";$P$2:$P$178;"=*FEBRUARY*")

Можете подсказать как совместить это
=СУММПРОИЗВ((($D$2:$D$178>20)+($E$2:$E$178>2,45)+($F$2:$F$178>2,45)+(G2:G178>25000)>0)*$H$2:$H$178)
и
=СУММЕСЛИМН(количество тонн; вес одной позиции;"<=25000";$P$2:$P$178;"=*FEBRUARY*")
?

_Boroda_

Цитироватьпочему появилась "=ЕСЛИОШИБКА"
это формула для столбца G
Цитироватьне понял что за формула ">0"
всю кучу условий ($D$2:$D$178>20)+($E$2:$E$178>2,45)+($F$2:$F$178>2,45)+(G2:G178>25000) мы складываем, получаем массив из нулей (где ничего не совпало) и чисел 1,2,3,4 (где совпало 1,2,3,4 условия). Сравниваем этот массив с нулем - где больше, там ИСТИНА, иначе - ЛОЖЬ. И умножаем эти ИСТИНА/ЛОЖЬ на массив с суммами. И складываем все полученное.
Добавив февраль, получим =СУММПРОИЗВ(((D$2:D$178>20)+(E$2:E$178>2,45)+(F$2:F$178>2,45)+(G$2:G$178>25000)>0)*H$2:H$178*ЕЧИСЛО(ПОИСК("FEBRUARY";P$2:P$178)))
ЦитироватьМожете подсказать как совместить это
=СУММПРОИЗВ((($D$2:$D$178>20)+($E$2:$E$178>2,45)+($F$2:$F$178>2,45)+(G2:G178>25000)>0)*$H$2:$H$178)
и
=СУММЕСЛИМН(количество тонн; вес одной позиции;"<=25000";$P$2:$P$178;"=*FEBRUARY*")
никак. Красные куски взаимоисключающи.
Скажи мне, кудесник, любимец ба'гов...



Яндекс-деньги: 41001632713405
Webmoney: R289877159277; Z102172301748; E177867141995

arxon

Господа, я пришел к выводу, что головоломка, которую мы совместно пытаемся решить, несколько интереснее и сложнее.  :)
я убрал все лишнее из excel документа.
Чтобы excel посчитал какай груз на какое транспортное средство ему положить, он должен выполнить ряд расчетов.
Дублировать здесь не буду, я описал логику в таблице.
Вроде ответ где-то рядом, но в голову пока не приходит... ???

IKor

Для решения Вашей задачи могла бы помочь функция массива.
посмотрите прилагаемый документ.

Обратите также внимание на то, что в базовой таблице я заменил бесконечности просто очень большими, но конечными числами.

Кроме того, сами по себе функции массива обладают большими возможностями, но они очень ресурсоемки и злоупотреблять ими не рекомендуется.

Альтернатива - создание вспомогательных таблиц - дополнить таблицу дополнительными колонками, в каждой строке которых проверить соответствует ли данная строка каждому из выбранного типа машин. А затем выбрать ту машину, которая больше нравится...

Альтернатива 2 - написать на VBA собственную функцию, производящую сравнение без использования доп. ячеек хоть реальных, хоть виртуальных...

zs

Цитата: arxon от 07.08.2014, 11:32
Резюмирая: мне кажется, что для решения вопросов по размещению груза на разных типах транспортных средств, мне нужно прояснить как применять в данной таблице условия "И" (тут все ясно), "ИЛИ".
Можно считать и анализировать не по столбцам, а по строкам,)

arxon

Цитата: zs от 08.08.2014, 14:36
Цитата: arxon от 07.08.2014, 11:32
Резюмирая: мне кажется, что для решения вопросов по размещению груза на разных типах транспортных средств, мне нужно прояснить как применять в данной таблице условия "И" (тут все ясно), "ИЛИ".
Можно считать и анализировать не по столбцам, а по строкам,)
Не понял Вас... можно пример?