Страницы: 1
Рациональное использование формулы СУММПРОИЗВ
 
Добрый день!

Возникла проблема с использованием формулы СУММПРОИЗВ
Кратко поясню смысл действий и что нужно сделать.

Есть нормы расходы и объемы распределения, а также маркер, который обозначает информация из каких ячеек необходимо взять.
В нашем случае это 1.
Причем в реале объемы с маркерами находятся на одном листе, а нормы с маркерами на другом, но маркеры совпадают.

Поскольку файл не мой я решил проверить итоговые данные они выделены голубым.
Поскольку итоговая норма является средневзвешенной, то первое что пришло в голову это перемножить нормы и объемы под нужным маркером 1.
Проверяю и вывожу результат в фиолетовую ячейку и вижу, что в одном случае (Вариант 1) с имеющимися данными не совпадает, а во втором случае (Вариант 2) совпадает.
Начинаю разбираться в итоговых нормах (голубые ячейки) стоит формула, которую я привел в оранжевых ячейках.
Логику, которую закладывали в нее я понимаю, хотели посчитать средневзвешанную, но вот исполение не могу понять и тем более во Варианте 1 она не работает, а во втором работает. Почему понять не могу.

Пишу свой вариант формулы, оба работают но есть нюанс (приведены в сером квадрате в красных ячейках).
В первом варианте формула выдает ошибку ЗНАЧ если в объемах стоит текст, к примеру как в желтой ячейке.
Второй вариант, просто добавил условие чтобы убрать ошибку с текстом, т.к. исходные данные исправить довольно проблематично.

Теперь несколько вопросов:
1. Как лучше и более красиво использовать формулу СУММПРОИЗВ для подобного расчета средневзвешенной в данном контексте.
2. Почему в исходном варианте в варинте 1 формула работает, а во втором нет (оранжевые и голубые ячейки)
3. В чем суть исходного варианта (оранжевые и голубые ячейки)
3. Как лучше и более красиво обойти проблему текста при котором СУММПРОИЗВ выдает ошибку.

Файл во вложении.

Буду рад и благодарен любым комментариям.
Заранее спасибо!!!
 
Часто рациональнее использовать другие функции.
Название темы должно отражать суть задачи. Предложите новое. Модераторы заменят.
 
Цитата
vikttur написал:
Часто рациональнее использовать другие функции.
Название темы должно отражать суть задачи. Предложите новое. Модераторы заменят.
Расчет средневзвешенной при помощи СУММПРОИЗВ
Так подойдет? Или нужно по-другому?
 
Цитата
Askofen432 написал:
В первом варианте формула выдает ошибку ЗНАЧ если в объемах стоит текст
Правильно, потому что, условие типа : (CT5:DA5=1) возвращает ИСТИНА или ЛОЖЬ, что соответствует 1 и 0, то есть при выражении (CT5:DA5=1)*CT6:DA6 получается что там где истина 1*значение соответствующей ячейки из диапазона CT6:DA6, а там где ложь 0*значение соответствующей ячейки из диапазона CT6:DA6. А если в ячейке из диапазона на который умножаете стоит текст, то вы получаете ошибку. Далее все суммы произведений складываются, следовательно вы получаете к примеру 10 + 20 +10 + #ЗНАЧ! , и снова получаете ошибку, поэтому формула возвращает вам в итоге ошибку.
Вдобавок ко всему, алгоритм по которому считает формула в оранжевой ячейке, не соответствует тому которым вы считаете в красной. Из за чего как вы могли заметить, в первом варианте у вас разные числа в красной и оранжевой ячейках. Во втором варианте, разница не возникает, поскольку если вы посмотрите внимательно, то в строке 27 по которой вы отбираете суммы соответствующие маркеру 1, нет других маркеров, кроме 1 и 0, а ноль при умножении на любое число дает 0. Так что если я правильно понял, то формулы в оранжевых ячейках неправильно считают. следовательно в первом варианте неверный ответ в оранжевой и синей ячейках. верный будет какраз в красной ячейке.
Изменено: Александр - 28.07.2017 05:44:30
 
Спасибо!
Я так примерно и думал.
А скажите, как более рационально это сделать?
Может другую формулу использовать?
Мне что-то кроме как я сделал во втором варианте в красной ячейке ничего больше в голову не идет.
 
Askofen432, Если не отходить от предложенного вами названия темы, после замечания модератора.То есть через =СУММПРОИЗВ(). Можно так например, см Вариант 1, красные ячейки, для второго аналогично.
Изменено: Александр - 28.07.2017 07:08:48
 
Спасибо!
Вариант короче чем у меня.
И не используется массив.

А если отходить от темы, подскажите какой формулой попробовать я поковыряюсь.
 
А если отходить от темы, то тема закроется.
Вы не знаете, как можно коротко, одним предложением, описать Вашу задачу, не применяя в названии функции?
 
Цитата
vikttur написал:
А если отходить от темы, то тема закроется.
Вы не знаете, как можно коротко, одним предложением, описать Вашу задачу, не применяя в названии функции?
К примеру,
Самый рациональный вариант расчета средневзвешенной по заданным условиям.
Страницы: 1
Читают тему
Наверх