Страницы: 1
RSS
Подставление баллов для получения нужного среднего значения
 
Добрый день.
Ищу помощи в такой странной проблеме.
На примере
Есть 5 точек, им каждый день проставляется балл (целое число), первые дни проставляет по факту, а потом из офиса приходит план к которому нужно придти (в таблице Итог). Есть ли какая то функция / макрос, которая может высчитать/проставить недостающие числа в голубую зону из примера, чтоб среднее от среднего стало равно или чуть больше итога.

Понимаю что сумбурно, но как смогла=(

Эксель 2007
Изменено: Skittish - 19.03.2018 12:34:51
 
Доброе время суток.
Цитата
Skittish написал:
которая может высчитать/проставить недостающие числа в голубую зону
А почему забыли упомянуть, что подставляемые числа должны быть случайными?
 
Об обсуждении темы следует сообщать самостоятельно.
Цитата
Публикуя один и тот же вопрос в разных форумах и на дружественных сайтах вы заставляете сразу нескольких людей параллельно думать над вашей задачей и обесцениваете усилия тех, кто даст ответ вторым-третьим и т.д.

Замыливание глаз руководству филькиной грамотой? :)

Отдавать решение на откуп последней строке не совсем корректно.
Алгоритм.
Заполняем случайными числами в диапазоне от... и до.... Находим среднее по каждой строке. Определяем общее среднее. Находим коэффициент (процент) отклонения общего среднего от нужной величины. Корректируем средние каждой строки на коэффициент с учетом + или - . Для сумм на этом и закончилось бы. Но общее среднее зависит нелинейно от составляющих средних, поэтому придется производить еще корректировку. Возможно, в цикле
 
Цитата
Андрей VG написал: А почему забыли упомянуть, что  подставляемые числа должны быть случайными ?
Ну они могут быть и одинаковыми по идее, но в определенном пределе. от 1 до 8 например
Каюсь упустила момент, уже 3 дня думаю как это объяснить просто)
плюс на том вы примере меня не поняли. Вот попытка зайти с другого края

Цитата
vikttur написал: Замыливание глаз руководству филькиной грамотой?
Это руководство придумывает ненужную фигню( раньше был план 300 балов в месяц допустим и ставь сколько хочешь, увидишь сколько останется на конец и добьешь.
А тут же среднее из среднего и есть план,  вот как высчитать не представляю.
Если заполнять правду, то будет больше, а следующий месяц еще на 15% увеличат -  и все - без зарплаты.
 
Андрей VG Вам мягко намекнул на то, о чем я прямо написал в сообщении №3 - ссылку давать надо
 
Ясно, спасибо
Просто в экселе столько еще мной неизученного, надеялась что найдется какая то автоматизация.Хотела помочь другу не потерять зарплату, и нарушила правила случайно, больше такое не повторится.

Закройте тему тогда или как у вас положено делать?
 
Цитата
Skittish написал:
Хотела помочь другу
Сударыня, дело в том, что вы своё участие в помощи другу свели только к выбору походит или не подходит, а не к пониманию и обсуждению, что и как делать. Вполне возможно кого-то задача и заинтересует, как цель получить полное решение. Мне же интересно помочь в решении, а не решать за.
Для случая, когда все ячейки области баллов должны быть заполнены, решение достаточно простое (вы, правда, так и не удосужились дать ответ, так это или нет). В этой ситуации – нет никакого среднего из среднего. В этом частном случае итоговое среднее равно сумме всех баллов, делённое на количество этих баллов. Тогда алгоритм решения следующий:
1.            Умножаем итоговое среднее 5,28 на количество баллов и округляем до целого значения, получаем контрольную сумму.
2.            Находим сумму уже заполненных ячеек и вычитаем из контрольной суммы, получаем целевую сумму.
3.            Делим целевую сумму на число незаполненных ячеек, округляем до целого, получаем целевое среднее.
4.            По уже заполненным ячейкам находим стандартное отклонение и округляем до целого. Если целое 0, то используем 1. Ну, или выбираем некоторое целевое +/- значение разброса.
5.            По целевому среднему и целевому значению разброса находим диапазон от до для формулы СЛУЧМЕЖДУ, и генерируем значения для незаполненных ячеек. Получаем расчётный диапазон значений.
6.            Подсчитываем расчётную сумму по этому диапазону значений.
7.            Вычитаем из расчётной суммы целевую сумму.
8.            Если разность больше 0, то в расчётном диапазоне уменьшаем наибольшие и расчётную сумму на 1, пока расчётная сумма не станет равна целевой.
9.            Если разность меньше 0, то в расчётном диапазоне увеличиваем наименьшие и расчётную сумму на 1, пока расчётная сумма не станет равной целевой.
10.          Если разность 0, всё хорошо.

Макросом, в принципе, сделать это не сложно. Формулисты, а можно ли сделать формулами?
 
Может выглядело что я как капризная барышня выбираю что подойдет а что нет, но это не так. МНе просто нужно разобраться как это делать, исходные данные то будут менятся. Сейчас вот пробую по вашу алгоритму.

Цитата
Андрей VG написал: Делим целевую сумму на число незаполненных ячеек, округляем до целого, получаем целевое среднее.
вот тут вопрос. целевую сумму мы берем исходя из того что рассчиталось за 5 дней или из планового итога?
5,28 это просто среднее полученное за 5 дней. тогда для контрольной суммы получается нужно взять 7,5?
Изменено: Skittish - 19.03.2018 14:40:47
 
Пример. Естественно, что результат для СЛУЧМЕЖДУ был скопирован и вставлен как значения. Может быть имеет смысл вместо СЛУЧМЕЖДУ использовать комбинацию ОКРУГЛ(M16+M15*ЕСЛИ(СЛЧИС()>=0,5;1;-1);0), тогда целевое среднее и +/- оставляем как есть без округления и получения целого, возможно меньше будет ручного добавления или уменьшения на 1 в случае невязки. По экспериментируйте.
Изменено: Андрей VG - 19.03.2018 14:31:21
 
Да, вариант с СЛЧИС имеет лучшую сходимость, меньше рукопашных изменений.
 
Ааа. вот как, я просто целевую и расчетную сумму делала для каждой строки) в итоге получилась ерунда
Там где +/- это стандартное отклонение? попробовала таблицу увеличить и она сломалась, хотя вроде все верно диапазон увеличила?

В обоих вариантах при увеличение количества строк она перестает работать.
И если хоть какое то число в массиве чисел меняешь тоже ошибка. Вот единственное во всем это что осталось не понятно, это как починить эту функцию
Изменено: Skittish - 19.03.2018 14:59:51
 
Цитата
Skittish написал:
В обоих вариантах при увеличение количества строк она перестает работать.
Это естественно, если не редактировать формулы  :)
 
Цитата
Андрей VG написал:
Это естественно, если не редактировать формулы  
а как ее правильно редактировать? я до нее только дотрагиваюсь и ошибка ИМЯ. даже когда просто в адресной строке на нее тыкну.. что я делаю не так? :cry:  
 
Цитата
Skittish написал:
а как ее правильно редактировать?
Её - это какую? На листе несколько формул. Да и я не телепат.
 
да все ту же. Только про нее голова и болит и все слетает. =_xlfn.STDEV.P(C3:G10)
пробую через строку формул, непосредственно в ячейке, только зайду в редактирование и чуть что сразу ошибка "Недопустимое имя"
Изменено: Skittish - 19.03.2018 16:37:15
 
Цитата
Skittish написал: голова и болит и все слетает. =_xlfn.STDEV.P(C3:G10)
OFF Правила, между прочим, рекомендуют извещать о вашей версии офиса/XL'я. А, мо быть, у вас и excel, но без обновлений, из кривой сборки ... ;)
И ошибки, огрехи - принято подкреплять примером.
Изменено: Z - 19.03.2018 16:42:14
"Ctrl+S" - достойное завершение ваших гениальных мыслей!.. ;)
 
Цитата
Z написал:
И ошибки, огрехи - принято подкреплять примером.
19 Мар 2018 14:45:13 к этому сообщению прикреплен пример с ошибкой.
Эксель 2007

Скрин если вдруг ошибка только у меня отображается
Изменено: Skittish - 19.03.2018 16:59:09
 
Цитата
Skittish написал:
Эксель 2007
И почему бы не сказать об этом раньше? Используйте тогда СТАНДОТКЛОНП
 
прошу прощения, подумала раз работало когда открыла файл то и будет работать, а ошибка из-за кривых рук

спасибо большое, теперь все работает, извините что столько времени на меня потратили
Страницы: 1
Наверх