Jump to content

SUBIECTE NOI
« 1 / 5 »
RSS
Intrerupator cu N - doza doar cu ...

Incalzire casa fara gaz/lemne

Incalzire in pardoseala etapizata

Suprataxa card energie?!
 Cum era nivelul de trai cam din a...

probleme cu ochelarii

Impozite pe proprietati de anul v...

teava rezistenta panou apa calda
 Acces in Curte din Drum National

Sub mobila de bucatarie si sub fr...

Rezultat RMN

Numar circuite IPAT si prindere t...
 Pareri brgimportchina.ro - teapa ...

Lucruri inaintea vremurilor lor

Discuții despre TVR Sport HD.

Cost abonament clinica privata
 

Optimizare cu Excel Solver si metoda celor mai mici patrate

- - - - -
  • Please log in to reply
50 replies to this topic

#1
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
Nu ma stiu deloc la Excel

As avea nevoie de o optimizare in Excel cu Solver si cu metoda celor mai mici patrate ( least squares method - RSS - residual sum of squares ) care trebuie sa tinda spre minim , sa aiba o valoare cat mai mica ( Deviation = 122.1 in imaginea atasata ) si cu ajutorul R2 - coeficientul de determinatie ( Coefficient of Determination R2 ) care trebuie sa tinda spre valoarea : 1 ( 100% in cazul ideal ) , in imaginea atasata : R2 = 0.995207

Am formulele ce se folosesc la optimizare si : si un exemplu de rezultat ce ar trebui sa se obtina .

Scopul optimizarii e de a se obtine un grafic ( o curba ) cat mai apropiata de curba ideala ( o parabola )

In graficul atasat se poate vedea : parabola ideala : Target Function si distributia de particule ( cu linie intrerupta ) : Composed Mix

Adica : Composed Mix ar trebuie sa aiba un grafic cat mai apropiat de : Target Function .

Datele de intrare in Excel se obtin de la analiza granulometrica a agregatelor ( nisip , margaritar , pietris ) si se introduc asemanator cu softul Elkem EMMA de mai jos .

Doar ca , cu softul EMMA nu se poate realiza optimizarea numerica , adica o portrivire maxima a curbei distributiei de particule cu , curba ideala ( parabola ) , doar la nimereala , prin incercari repetate , si daca n-ai experienta , doar cu noroc poti obtine un grafic cat de cat apropiat de graficul ideal .

Dar cu RSS ( metoda celor mai mici patrate ) si cu R2 ( coeficientul de determinatie ) se poate obtine o optimizare numerica cat mai apropiata de ideal .

Se folosesc si : constrangeri

EMMA - Concrete Mix Particle Packing Program
https://www.youtube.com/watch?v=Tyy4bFMGyXM

Mixture proportioning
https://www.youtube.com/watch?v=LMVikTevEQg#t=22m0s

Attached Files


Edited by luk2011, 30 December 2018 - 19:24.


#2
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
De fapt ceea ce vrei tu sa faci e o regresie catre o parabola, care se poate face in excel (si) fara VBA.

Daca vrei si constrangeri, folosesti metoda multiplicatorilor lui Lagrange.

Te pot ajuta eu cu partea matematica a problemei, insa nu stiu sa interpretez datele alea despre agregate.

Edited by maccip, 30 December 2018 - 19:31.


#3
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004

 maccip, on 30 decembrie 2018 - 19:26, said:

De fapt ceea ce vrei tu sa faci e o regresie catre o parabola, care se poate face in excel (si) fara VBA.
Daca vrei si constrangeri, folosesti metoda multiplicatorilor lui Lagrange.
Te pot ajuta eu cu partea matematica a problemei, insa nu stiu sa interpretez datele alea despre agregate.

Da, e o regresie .

Ceea ce stiu iti explic eu , pe langa documentatia pe care am gasit-o pe net .

Attached Files


Edited by luk2011, 30 December 2018 - 19:38.


#4
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
Deci.. ne bagam?
Iti zic eu de ce date are nevoie aparatul matematic, si-l construim impreuna.

In primul rand, metoda foloseste legatura dintre doi parametri X si Y.

La modul cel mai general, pentru rezolvare prin metoda lui Lagrange, putem scrie o singura ecuatie de minim(sau maxim) si mai multe constrangeri(daca ne dorim) asupra setului de variabile X, respectiv Y astfel...

0. Imi trebuie un set de perechi de valori (X1 Y1) (X2 Y2).... (Xr Yr)
1. Suma patratelor abaterilor fata de Y=X2sa fie minim. (parabola)
2. Putem pune un set de conditionari asupra parametrului X astfel.
F1(X1, X2, ...Nn)=0
F2(X1, X2, ...Nn)=0
F3(X1, X2, ...Nn)=0
.......
Fi(X1, X2, ...Nn)=0
3. Putem pune un set de conditionari asupra parametrilor Y astfel:
G1(Y1, Y2, ...Ym)=0
G2(Y1, Y2, ...Ym)=0
G3(Y1, Y2, ...Ym)=0
.......
Gi(Y1, Y2, ...Ym)=0

#5
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
Ecuatia [1] din imaginea atasata e : ecuatia ideala a parabolei

Cu ajutorul ecuatiei [2] si [3] din imaginea atasata : Composed Mix trebuie sa se apropie cat mai mult de parabola ideala a ecuatiei [1]

Attached Files


Edited by MarianG, 30 December 2018 - 22:53.
fara quote integral la postul anterior


#6
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
Pai partea cu parabola am inteles-o.
Am si enuntat-o deja.
Dar eu nu stiu cine-s Di aia ai tai, eu nu ma pricep la agregate sau la constructii.
Acum ma intereseaza sa stiu cine-s parametrii X si Y (densitatea, volumul.. etc..)
Ideea e ca imi vor trebui acele perechi de date (X Y), fara alea nu putem face nimic.

Apoi, trebuie sa vad cum sunt puse conditionarile (daca exista). Pentru ca functie de asta, stabilim mai departe formulele.

Nu ma pune sa inteleg formulele tale, nu pricep parametrii aia. Tu trebuie sa-i pricepi, sa-i intelegi si sa-i furnizezi metodei.


Mai am si o mica problema. Eu sunt acum la o cabana la munte si nu am nici pix, nici foaie. Deh.. revelionul.
Si de-aia tre sa facem astea impreuna, sa nu gresim. Dar mai intai tre sa avem un limbaj comun.

Edited by maccip, 30 December 2018 - 19:52.


#7
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
Di sint particulele din : Particle Size Distribution

Adica acolo apare de la 10nm ( 0.01um ) pana la 125mm , si ratia e de radical din 2 . Daca te uiti in imaginea atasata , poti vedea ce scrie .

Ideea e in felul urmator : daca Composed Mix are graficul cat mai apropiat de parabola ideala , atunci mixajul de particule are cea mai mare densitate ( mixajul are compactitate maxima )

Cu cat e mai compact mixajul cu atat ramane mai putin spatiu ( aer ) intre particule .

Imaginea : Ecuatie.jp este un exemplu de mixaj de particule in care s-a ales : Dmin = 0.275um si Dmax = 16mm

Daca te uti in imginea : Ecuatie.jpg vei vedea ca acolo apare asa :

Dmin ( cea mai mica particula ) = 0.275um
Dmax ( cea mai mare particula ) = 16mm

Si in imagine se poate vedea : i = 9 ... 38

Deci pe scala de la 0.01um ( in imaginea Optimization_Algorithm.jpg ) la 125mm , se alege particula minima si maxima cu ratia de : radical din 2

iar Di pentru particula Dmin = 0.275um , unde i-ul va fi = 9 ( daca calculezi de la 0.01um la 0.275um cu ratia : radical din 2 , vei vedea ca i = 9 )

iar Di pentru particula Dmax = 16mm , i-ul va fi = 38 ( tot dupa acelasi rationament cu ratia de : radical din 2 )

==================================

X = dimensiunea particulelor ( de la 0.01um la 125mm , se poate alege oricare Dmin si Dmax )

Y = P(D) din ecuatia [1] , si in functie de Dmin si Dmax ales rezulta P(D) din exuatia [1] , iar D-ul dimensiunea particulei se introduce ca si o variabila in urma analizei granulometrice .

Attached Files


Edited by MarianG, 30 December 2018 - 22:53.


#8
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
Mixture proportioning
https://www.youtube.com/watch?v=LMVikTevEQg#t=22m0s


EMMA - Concrete Mix Particle Packing Program
https://www.youtube.com/watch?v=Tyy4bFMGyXM

Daca te uti la filmuletele astea doua , intelegi mai bine despre ce-i vorba .

#9
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
Imi pare rau, nu cred ca ne intelegem.
Eu nu vreau sa intru in intestinele problemei tale. Sunt absolut convins ca ma bag in ceva ce nu stapanesc. Trebuie sa intelegi tu metoda si s-o poti folosi.

Eu imi fac datoria sa scriu in continuare cum se face regresia poate iti va ajuta la ceva. Nu ma pune sa invat agregate ca n-am s-o fac, e aproape revelionul.


Conditia de parabola se pune in urmatorul fel...
dx dy sunt abaterile.

Ecuatia se obtine prin liniarizarea ecuatiei parabolei astfel...

Y+dy=X^2+2X*dx
sau dy=X^2-Y+2X*dx

Cazul fara conditionari e simplu, pentru ca se poate rezolva fara matrici.

Dar in cazul general, poti forma o matrice coloana V cu valorile corectiilor dy, X cu valorile abaterilor dx, A cu valorile diferentialelor (valorile de 2X in cazul tau va fi o simpla matrice diagonala daca n-ai constrangeri), apoi T cu valorile neinchiderilor in ecuatie, adica T=X^2-Y.

Ecuatia matriceala va fi in cazul asta V=AX+T unde A si T se poate scrie imediat (ai tot ce-ti trebuie pentru a le calcla)
Conditia de minim va fi VTV=min, adica suma (dy2)=minim. Asta daca te intereseaza ca asta sa fie minimizat. Daca te intereseaza DX, tre sa scrii altfel, din pacate nu-mi dai informatiile in timp util sa-ti pot spune precis.
Poti pune conditia de minim si pentru dx si dy (suma patratelor tuturor dx si dy sa fie minim), caz in care sunt mici abateri de la metoda expusa aici, la fel, nu-mi dai informatiile necesare sa-ti pot spune precis cum se face.

Apoi... ai o rezolvare matriceala
VTV=minim insamna ca (AX+T)(AX+T)T=min.
Nu mai intru in detalii, dar matriceal asta se obtine cand X=- (ATA)-1ATT
Dupa care calculezi din V=AX+T.
In felul asta ai calculat atat vectorul corectiilor lui X cat si a lui Y, pentru ca...
-> Conditia VT V=minim (echivalenta cu suma de patrate dX e minim)
-> punctele (corectate cu dx si dy) sa fie pe o parabola Conditia e asigurata din modul in care e conceputa matricele A si T cu care ai intrat in rezolvarea matriceala.
Sper sa te ajute
In excel poti crea usor matricele A si T prin "tragere cu mausul"
Iar formulele matriceale le scrii cu CONTROL SHIFT ENTER  cauti pe net cum anume se scriu formule cu CONTROL SHIFT ENTER,
Sau cu VBA.

Daca-mi spuneai de la inceput cine-s X si Y si care-s conditionarile iti dadeam precis formulele, eventual si bagate in excel ca nu-i mare branza.

Poti calcula eroarea medie patratica sigma= redical(VTV / r)

Apoi poti estima si valorile dispersiilor, matricea (ATA)-1 contine informatii despre valorile dispersiilor si corelatiilor intre valorile necunoscutelor X.
Poti estima si erorile unei alte functii F(X) liniarizata intr-o matrice F astfel:
Eroarea= sigma2*F(ATA)-1FT


Bafta la calcule, sper sa te ajute cu ceva, mai mult de  atat nu te pot ajuta.

Edited by maccip, 30 December 2018 - 20:28.


#10
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
Masini de sitat materiale pulverulente - cernut - granule - pulberi - agregate
https://www.multilab.ro/sitare/masina_sitare.html

Quote

Masinile de sitat cernut pulberi si granule sunt utilizate pentru sortarea, fractionarea, separarea si determinarea marimii particulelor; au o gama larga de aplicatii in laboratoare de cercetarii si analiza, biologie, agricultura, materiale de constructii,

X -ul se obtine in urma analizei granulometrice , si sint dimensiunile particulelor din agregate

Y-ul se obtine din ecuatia P(D) in functie de Dmin si Dmax , si in functie de D-ul din analiza granulometrica .

Iar formulele sint acele formule ce apar in imaginile atasate .

Te inteleg si pe tine , ca pana nu intelegi concret despre ce-i vorba , tu ai nevoie strict de variabile matematice : X si Y , dar formulele nu le inteleg nici eu , fiindca nu eu le-am scris .

==============================================

In imaginea atasata se poate vedea X si Y

Attached Files


Edited by luk2011, 30 December 2018 - 20:57.


#11
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
In imaginea atasata se poate vedea X si Y

In imaginile atasate se pot vedea constrangerile si formulele constrangerilor .

Te inteleg perfect de ce ai nevoie , dar nu sint nici eu 100% in clar cum se realizeaza practic curba granulometrica in functie de analiza granulometrica .

Stiu ca se folosesc mai multe site pentru a cerne aregatele si se contabilizeaza procentul de agregate care trece prin sita respectiva , si asta va fi : Y-ul ( cel putin asa cred )

Attached Files


Edited by luk2011, 30 December 2018 - 21:20.


#12
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
In primul rand nu ai perechi X Y, nu orice Y are X.

Apoi, cele mai mici patrate se scriu intr-un fel anume.
Poti sa pui conditii asupra lui dX, asupra lui dY, ba chiar si amandorura (adica suma de dx patrat + dy patrat egal minim)
Retine, eu n-am de unde sa stiu ce urmaresti, care-i contextul general. De cele mai multe ori, ai o dependenta functionala intre parametri de tip cauza efect.
In cazul asta, conditia de cele mai mici patrate se scriu asupra efectului adica asupra lui DY.
Dar poate sa fie si asupra lui DX, caz in care trebuie sa exprimi X functie de Y adica X=radical(Y)
.Apoi.. parabolele alea, poa sa fie cu un numar de max 3 parametri, care intra si ei in calcul, daca e cazul.
Aduca Y=aX^2 + bY+C, eu n-am de unde sa visez care e conditia exacta. Am presupus ca vrei Y=X^2 ca ai zis parabola, doar ca vad ca n-ai aceleasi unitati de masura, deci ecuatia trebuie sa fie minim de forma Y=aX^2, nu are cum sa fie doar Y=X^2
Caz in care intra si da ca necunoscuta alaturi de valorile lui dX, parcurgand aceleasi etape, doar ca mai intra un termen in ecuatie.

dy=aX^2-Y+2aX*dx +X^2*da

In rest matricele A si T se construiesc la fel, ecuatia se rezlva la fel rezultand X, apoi V. Metoda functioneaza perfect in continuare, este o metoda generala de rezolvare a unor astfel de probleme. functioneaza si pentru agregate, dar si pentru orice alta chestie de prin inginerie, eu o folosesc frecvent la compensarea unor erori de masurare.

De remarcat e ca parametrul a trebuie estimat dintr-un esantion mic de valori. Pentru ca metoda celor mai mici patrate in fapt calculeaza o corectie.

Adica facem parabole, dar si alea sunt de mai multe feluri, tu tre sa intelegi metoda., ti-am expus in prima postare care-s parametrii si contitiile cerute de catre metoda. Daca reusesti sa te mulezi pe metoda, eu te pot ajuta. Invers ti-am zis, nu merge, ar trebui sa invat eu toata povestea ta pentru ca sa stiu exact ce vrei. Lucru pe care nu sunt dispus sa-l fac.
Ti-am zis ca te pot ajuta strict pe metoda matematica si formulele in excel (desi sunt banale odata ce rezolvam problema pe foaie)

Edited by maccip, 30 December 2018 - 21:23.


#13
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
In imaginea atasata se poate vedea un exemplu de reteta de beton .

Ideea e ca datele de intrare care vor genera curbele mixajului de beton se obtin in urma analizei granulometrice , cu ajutorul acelor site care vor cerne agregatele .

Concret : de exemplu sint 7 site de diferite dimensiuni : prim prima sita vor trece 95% din agregate , prin a doua 80% , prin a 3-a sita 70% si tot asa pana la partea cea mai fina .

Si procentele astea alcatuiesc Y-ul REAL , rezultat in urma analizei granulometrice . Daca te uiti in grafic vei vedea pe axa Y , procente .

Iar Y-ul IDEAL va fi CPFT = " Cumulative ( Volume ) Percent Finer than " sau rezultatul ecuatiei P(D) din ecuatia [1] ( parabola ideala  din imaginea atasata )

Iar scopul e ca : curba reala sa se apropie cat mai mult de curba ideala .


In imaginea atasata , cu rosu e curba ideala , iar cu albastru curba reala.


Scopul ar fi ca : linia albastra REALA sa fie cat mai apropiata de linia rosie IDEALA .

Imi cer scuze , in mod sigur nu stiu sa-ti ofer datele de care ai nevoie pentru a putea realiza partea de matematica .

Iti multumesc oricum pentru intentia de a ma ajuta .

Attached Files


Edited by luk2011, 30 December 2018 - 21:43.


#14
maccip

maccip

    45 ani

  • Grup: Senior Members
  • Posts: 33,066
  • Înscris: 06.01.2007
Ok, daca nu vrei sa pui tu intrebari asupra aparatului matematic, sa ma apuc sa pun eu intrebari cu privire la problema ta, s-o aducem unde trebuie.

Ce inteleg eu pana acum.

1. Ai N producatori de agregate (sau retete de agregate, sau cum le zice), fiecare producator are o anumita compozitie (distribuire) a agregatelor dupa dimensiunea pietrelor din agregat.
2. Ai M site cu care poti verifica fiecare agregat (compozitie, reteta, etc), ce distributie are dupa dimensiunea pietrelor, adica dupa tipul gaurilor din sita.
3. Pentru fiecare producator  i∈{1..N}, treci agregatul ala prin sita j∈{1..M}, cantaresti ce a trecut si obtii proportia Pij∈[0,1], pentru fiecare i∈{1..N} si fecare j∈{1..M}
4. Ai o solutie(sau reteta) ideala U={U1, U2, ..., UM}  care-ti spune exact cate pietre Uj (in procent masic) trebuie sa treaca prin fiecare sita j, cu j∈{1..M}
5. Te intereseaza sa afli compozitia ideala procentuala, exprimata prin p1, p2.. pN, cu  (p1+p2+...+pN=1), si pi>=0, i∈{0..N}. astfel incat
6 ... diferentele procentuale cumulate intre solutia ideala dorita si cea care se poate obtine prin mixarea celor N producatori, eij=Uj-pi*Pij
7. ... sa fie minima, in termeni de suma patratelor. Adica e112+e122+...eNM2=minim.

Adica, centralizand matematic:
Total N*M termeni in aceasta suma/conditie de minim.
Total N-1 proportii independente (necunoscute) p1, p2, pN-1, pozitive si subunitare, ultima fiind PN=1- suma(pi, i∈1..N-1)


1. E OK ce am inteles?
2. Erorile sunt in final distribuite pe site si producatori. Si au pondere egala. Sitele are importanta cum sunt? Se pastreaza de la producator la producator si la solutia finala sa faci diferente directe? Sau sunt si ele distribuite cumva(exponential) pe un interval? Nu mi-e clar din problema ta care-i treaba cu sitele alea. Putem fi agnostici la acest aspect, dar nu stiu daca e corect.  Adica una e sa ai site egal distribuite, alta e sa ai doua site identice, care evident vor produce acelasi rezultat, el in final in aparatul matematic va primi o pondere dubla, ceea ce nu pare corect. Banuiesc ca exista niste norme de alegere a sitelor.
3. M-ai zapacit cu cimentul, pietrele si apa. Sigur e ceva gresit.


4. Daca e ceva gresit, sau trebuie completat la subpunctele de mai sus, corecteaza tu acuma, pentru ca functie de ce corectezi, depinde aparatul matematic pe care il vom utiliza si forma finala a rezultatului.
5. Andersen ala nu a fost in stare sa produca o metoda suficient de clara incat sa o aplici direct in excel? Adica.. au trecut 4 ani de cand ai venit cu problema si vad ca nu i-ai dat de cap. Nu pare ceva greu, dar partea usoara e cea matematica, partea grea e atribuirea de semnificatie cifrelor. Firesc ar fi sa vii tu cu problema inspre matematica, nu invers, sa vii cu matematica inspre problema.

#15
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004
A new mix design concept for earth-moist concrete: A theoretical and experimental study
https://citeseerx.is...p=rep1&type=pdf

Se pricepe cineva la Exel foarte foarte bine sa introduca formulele din linkul de mai sus si sa faca un Solver in Excel ?

@maccip ti-am trimis prin mesaj privat un Excel si acolo e ecuatia modificata a lui Andreasen ( adica Funk&Dinger )

Iti dau un exemplu concret de analiza granulometrica , reala , publica , nu povesti .

Iti dau perechi de X-Y si trebuie doar ca perechile de X-Y sa le combini in asa fel incat curba rezultata din combinatiile de perechi X-Y sa fie cat mai apropiata de curba target ( ideala 0 , adica de curba ecuatiei modificate a lui Andreasen ( Funk&Dinger )

Edited by luk2011, 18 December 2021 - 10:42.


#16
KiloW

KiloW

    Guru Member

  • Grup: Senior Members
  • Posts: 12,050
  • Înscris: 08.01.2021
E foarte ușor dacă ai înțeles ce vrei sa faci.

Edited by KiloW, 18 December 2021 - 10:45.


#17
luk2011

luk2011

    Active Member

  • Grup: Members
  • Posts: 1,799
  • Înscris: 05.12.2004

View PostKiloW, on 18 decembrie 2021 - 10:44, said:

E foarte ușor dacă ai înțeles ce vrei sa faci.

Eu inteleg ce vreau sa fac , dar nu stiu sa fac Solverul in Excel .

#18
KiloW

KiloW

    Guru Member

  • Grup: Senior Members
  • Posts: 12,050
  • Înscris: 08.01.2021
Dacă explici exact ce vrei sa faci, pot sa încerc să-ți dau o idee.
Excel are deja solver incorporat, nu-i nevoie să-l faci tu. Nu-i solver pentru cele mai mici pătrate ci unul generic însă îl poți utiliza.
Eu de ex l-am folosit pentru regresii. Am curbe măsurate și voiam sa le aproximez cu niște polinomiale (gr3, cu 4 coeficienți deci) . Calculezi suma pătrățelor direct în excel, și apoi folosești solver ul ca să o minimizezi calculând valoarea celor 4 coeficienți ai polinomului.
Cum se folosește exact solver ul incorporat e destul de intuitiv, nu prea ai nevoie de prea multe instrucțiuni.

Anunturi

Bun venit pe Forumul Softpedia!

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Forumul Softpedia foloseste "cookies" pentru a imbunatati experienta utilizatorilor Accept
Pentru detalii si optiuni legate de cookies si datele personale, consultati Politica de utilizare cookies si Politica de confidentialitate