Можно ли решить эту систему уравнений?

Пытаюсь рассчитать трудоемкость работ по типографии
Y- фонд рабочего времени
Х1.....Хn - трудозатраты по видам продукции
Есть ли решения этой системы уравнений???

Вложения

система уравнений.xls (39.5 Кб, 26 просмотров)

0

решение системы линейных уравнений в Excel можно искать, например, с помощью надстройки Поиск решения, приняв первое уравнение системы за целевую функцию, а остальные – за ограничения... для этого нужно задать произвольные значения искомых параметров и вычислить левые части уравнений с помощью функции МУМНОЖ()... дальнейшее, полагаю, понятно... но, конечно же, далеко не для каждой системы можно найти решение с достаточной точностью... здесь, видимо, именно такой случай...

0

Что-то не получается найти поиском решения в Excel....Помогите пожалуйста, очень нужно....

Вложения

система уравнений2.rar (17.0 Кб, 21 просмотров)

0

Сообщение от Александр 1980 Что-то не получается найти поиском решения в Excel

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

0

Я уже понял что единственного решения у системы нет. Речь идет о нахождении решения с минимально возможной погрешностью

0

Сообщение от Александр 1980 Речь идет о нахождении решения с минимально возможной погрешностью

как вариант:

Minerr(var1, var2, ...) Returns the values of var1, var2... that satisfy the equations and inequalities in a Solve Block. Minerr differs from Find or Minimize in that, if the chosen algorithm fails to converge, whatever answer found on the last allowable iteration is returned, even if it doesn't meet the convergence criteria. If you are solving for n variables, the solve block must have at least n equations. Matrix notation is allowed, as is solving for matrix variables.

но в любом случае погрешность будет очень большая...

в Excel можно поэкспериментировать с параметрами Поиска решения:

Вложения

leastsquares.xls (45.0 Кб, 13 просмотров)

1

Большое спасибо Buckminster за уделенное внимание и время ! Пересмотрев и упростив систему, получилось найти решение через поиск решения...однако результаты не совсем корректные получились. Хотелось бы попробовать найти решение через МНК. К сожалению ни яндекс ни учебники не помогли вспомнить что делать дальше (по Вашему примеру). Большая просьба подскажите что делать дальше....
За ранее огромное спасибо!

Вложения

МНК.xls (55.0 Кб, 11 просмотров)

0

Сообщение от Александр 1980 Хотелось бы попробовать найти решение через МНК

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

Вложения

МНК.xls (62.0 Кб, 9 просмотров)

0

Спасибо за ответ Buckminster! Есть реальная типография на неком крупном промышленном предприятии, на основе статистики объемов произведенной продукции (статистика есть за два года, с дискретизацией почти в смену), есть фактические выхода работников которые непосредственно заняты в производстве этой продукции....В первом варианте брал всю номенклатуру продукции, во втором варианте классифицировал ее по формату, сложности изготовления и т.д. Таким образом получил некую СЛУ:
Кол-во продукции1*Х1(трудоемкость, чел.-часы)+...+кол-во продукции n*Хn=отработано часов в январе (сотрудники типографии);
........................................ ............
Кол-во продукции1*Х1(трудоемкость, чел.-часы)+...+кол-во продукции n*Хn=отработано часов в декабре (сотрудники типографии).
Значение трудоемкости в данном случае вряд-ли может принимать отрицательное значение.
При решении при помощи поиска.... результат коррелирует с кол-вом того или иного вида продукции, иными словами чем больше объем изделий тем выше в результате его трудоемкость
Во вложении отрывок выгрузки для примера.
В универе в свое время решал разные задачки по МНК, но на уровне у=а+b*х, а сейчас по работе столкнулся с реальной задачкой, а сообразить не могу, подскажите что делать с "суммами и модулями" дальше, что бы получить коэффициенты.....
Спасибо!

Вложения

Бланки 1-12 2011(пример).xls (18.5 Кб, 9 просмотров)

0

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

0

Кол-во продукции1*Х1(трудоемкость, чел.-часы)+...+кол-во продукции n*Хn=отработано часов в январе (сотрудники типографии) <------явочный ФРВ всех сотрудников.
=>совокупные затраты рабочего времени не занижаются и не завышаются (они есть какие есть по факту явки).
Задача не состоит в определении производительности (загрузки) отдельных сотрудников, задача в расчете необходимой численности работников для выполнения заданного объема той или иной номенклатуры (в разные месяца на выходе неодинаковое кол-во работников на выходе, а объемы производства соизмеримые). При этом не важно чистую мы получили трудоемкость или включающую в себя затраты на подготовительно-заключительные работы (ПЗ) или что-то иное....В любом случае за счет большей выборки большее значение "простоев" уйдет из полученной таким образом "трудоемкости"....

Добавлено через 4 минуты
Александр, подскажи что дальше делать с моей моделью? чтобы получить коэффициенты (ход решения СЛУ по МНК в Excel)!

0

Сообщение от Александр 1980 что дальше делать с моей моделью? чтобы получить коэффициенты

корень проблемы немного в другом: выраженная мультиколлинеарность многофакторных моделей и, как следствие, слабая обусловленность соответствующих матриц, для которых вычисляются обратные, что приводит к очень большим (астрономическим) погрешностям и лишает полученные результаты экономического смысла... поэтому любое решение, полученное "в лоб", как правило, не будет удовлетворительным... здесь всё гораздо сложнее, чем в ситуации с моделями типа у=а+b⋅х... вот статья, в которой подробно исследуются возникающие проблемы и предлагаются возможные методы их решения (взято отсюда)... если же нужно просто найти коэффициенты, то вот явная формула: A = (X^TX)^–1X^TY – она, собственно, и реализует МНК...

Вложения

33.pdf (259.5 Кб, 149 просмотров)

1

Огромное спасибо за внимание к моему вопросу! Александр, я понял что не все так просто, буду изучать матчасть, ну все таки, пусть этот вариант и дает огромную погрешность, каков дальнейший алгоритм решения СЛУ МНК в Excel (мне на будущее).

Добавлено через 49 минут

Сообщение от Buckminster A = (XTX)–1XTY

как это реализуется в моем случае, извиняюсь за свою неграмотность......

0

Сообщение от Александр 1980 каков дальнейший алгоритм решения СЛУ МНК в Excel

Сообщение от Buckminster если же нужно просто найти коэффициенты, то вот явная формула: A = (XTX)–1XTY – она, собственно, и реализует МНК

именно это формула (в матричной записи) – больше ничего считать не нужно...

т.е.

{= МУМНОЖ(МОБР(МУМНОЖ(ТРАНСП(X);X));МУМНОЖ( ТРАНСП(X);Y))}

или

{= МУМНОЖ(МУМНОЖ(МОБР(МУМНОЖ(ТРАНСП(X);X)); ТРАНСП(X));Y)}

1

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

Вложения

МНК2.xls (56.5 Кб, 7 просмотров)

0

Сообщение от Александр 1980 зачем мы тогда перемножали матрицы,вычисляли суммы квадратов и т.д.?

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

Сообщение от Александр 1980 поиском решения коэффициенты более правдоподобные получаются

опять же, модель для поиска решения можно задавать по-разному...

Сообщение от Александр 1980 значения по первой формуле и по второй различаются

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

1

Сообщение от Buckminster Сообщение от Александр 1980 зачем мы тогда перемножали матрицы,вычисляли суммы квадратов и т.д.? у Поиска решений есть параметры, управляя которыми, можно влиять на получаемый результат...

Стоп....тогда и поиском решения я посчитал неверно? Я не вычислял суммы квадратов, модули и т.д.

Вложения

Поиск решения.xls (49.5 Кб, 8 просмотров)

0

Сообщение от Александр 1980 тогда и поиском решения я посчитал неверно?

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

0