Formules achter excel; programmeren.

[gast0182]

Beste Guru's,

In de excelfunctie Regression (Data > Data Analysis > Regression) zit een "onhandigheidje" welke ik zeer graag verhelpen zou willen hebben. Dit in verband met enorme tijdbesparing aangezien ik de functie vrijwel dagelijks gebruik.

Het onhandigheidje is iets wat we niet willen; de cellen kun je aanpassen maar de uiteindelijke waarden veranderen niet mee.

Om het voorbeeld kort te schetsen heb ik een dataset gecreeërd (omdat het waarsschijnlijk onduidelijk is, heb ik een excel-file in de bijlage geupload);

Y X X^2 X^3 X^4 X^5
0,719107639 1 1 1 1 1
0,391216773 2 4 8 16 256
0,210852382 3 9 27 81 6561
0,551896725 4 16 64 256 65536
0,200933866 5 25 125 625 390625
0,059877316 6 36 216 1296 1679616
0,666493728 7 49 343 2401 5764801
0,507736442 8 64 512 4096 16777216
0,751365703 9 81 729 6561 43046721
0,936826685 10 100 1000 10000 100000000

Doe je over deze dataset Regression, dan volgt: R square = 0,674550736721719, zo zijn er vele andere waarden als R adj square, SS, etc etc.

Daar ligt exact het probleem, er staat een getal (0,6745..) in plaats van een formulevorm.
Dit zorgt ervoor dat, mocht je een getal in je waarden willen veranderen, het regressiemodel niet mee veranderd. Erg vervelend als je 25 verschillende parameters hebt en alles dus opnieuw zou moeten doen.

Dat kan beter, formulevorm of een soort add-in met de juiste regressie-formules erbij.
Nu zit ik met de twee hoofdzaken te knoeien, de regressie-formules ken ik lang niet allemaal en ik zou geen idee hebben hoe ik deze formules in excel kan implementeren mocht ik ze allen hebben.

Hulp is zeer welkom :]

Met vriendelijke groeten,
gast0182

 

[snb]

In mijn Excel 2010 zie ik geen data/data analysis/regression.

Heb jij een AddIN geladen die dit bevat ?

 

[Warme bakkertje]

@ snb

Is blijkbaar een onderdeel van ATP.
Daarna op tab Gegevens (Data) Data Analysis knop, scroll down naar Regression

 

[gast0182]

In mijn Excel 2010 zie ik geen data/data analysis/regression.

Heb jij een AddIN geladen die dit bevat ?

Beste snb,

De analysis toolpack is hetgeen je moet aanvinken om tabblad data tevoorschijn te toveren. Voor zover ik weet een standaard add-in van excel.

http://office.microsoft.com/en-ca/excel-help/load-the-analysis-toolpak-HP001127724.aspx

In bovenstaande link kun je lezen hoe je de toolpak aan zet.


Met vriendelijke groeten,
gast0182

 

[WHER]

@ gast0182,

Probeer deze bijlage eens.
Verander een van je waarden in C3:H12, klik vervolgens op "OK" wanneer je gevraagd wordt of je bestaande gegevens (in J2: P23) wil overschrijven.

 

[gast0182]

Beste Wher,

Dit is exact hoe ik het bedoel, een stap in de goede richting! Alvast heel erg bedankt!

Maar, als ik nu een tweede tabel aanmaak omdat één van mijn parameters door regressie onjuist blijken te zijn, werkt het niet. Logisch, want je hebt een selectie gemaakt in VBA. Opzich slim bedacht.

Echter is het nu nog niet volgeautomatiseerd aangezien ik dan constant de data in VBA moet aanpassen of voor elk tabel een nieuwe code moet maken. Is dit ook nog op te lossen zonder dat ik constant naar VBA zou moeten?

Met vriendelijke groeten,
gast0182

 

[WHER]

Volgende poging waarbij je zelf de bereiken moet opgeven in Inputboxen:

 

[gast0182]

Beste Wher,


Hm, dit is nog niet wat ik bedoel. Het is de bedoeling dat deze sheet gedeeld wordt met andere studenten. Nu, is het een excel-file die altijd voor regressie gebruikt wordt, aangezien de klikbutton er staat.


Ik heb een nieuwe excel-file gemaakt welke in de bijlage staat, ik hoop dat je hierdoor exact mijn bedoelingen begrijpt.

Het betreft een regressiemodel van twee aparte zaken:
Viscocity air versus temperature.
Viscocity water versus temperature.

Bij de regressie van lucht zijn er twee regressiemodelen gemaakt, dan is al duidelijk welk model het beste is (exponentiëel of lineair).
Bij de regressie van water zijn er daarentegen vier regressiemodellen gemaakt alvorens er een antwoord uit kwam rollen.

Nu komt het telkens mooi uit, de laatste of laatste twee x-waarden zijn verkeerde parameters en zijn dus rood gekleurd (rest is groen). Logisch, want dit is een zelf gemaakte dataset waardoor het mooi uitkomt.

Er zijn echter, vele praktijk voorbeelden waarbij de x^2 en x^3 verkeerde parameters zijn maar de x^4 een juiste parameter, om een voorbeeld te noemen.
Als dat het geval is kopieer je de dataset (zonder de verkeerde parameters).
Je maakt dan een nieuw regressiemodel met je nieuwe dataset.


Als ik gebruik maak van de door jou gemaakte excelfile, kan ik niet meerdere "outputs" kiezen. Dus als ik de dataset aanpas veranderen de gemaakte regressiemodellen niet mee. Daarnaast, wil je geen "regressieknop" in je excel welke je ook voor andere doeleinden gebruikt. Mooi zou dus zijn, als er een soort add-in gemaakt kon worden met de formules (of update tables) waardoor alle regressiemodellen meevariëren zonder dit elke keer te moeten aangeven. Volautomatisch.

Ik hoop dat ik het verduidelijkt heb voor je en je me hiermee uit de brand kan helpen (of iemand anders).


Met vriendelijke groeten,
gast0182

 

[huijb]

@gast0182 Onnodige quotes verwijderd. Wanneer je direct reageert op een reactie is het niet nodig om dat bericht te quoten. Onnodig quoten heet dat, hetgeen niet gewenst is.

 

[WHER]

Een oplossing met regressieformules gaat mijn wiskundepetje ver te boven, vrees ik.

 

[gast0182]

dat is jammer, toch bedankt voor je excel bestanden. Daar ga ik zeker gebruik van maken!

Iemand anders die me uit de brand kan helpen met het regressieprobleem?

 

[ExcelAmateur]

gast0182,

Heb je al met Google gezocht op "Excel regressie formules" ?
Er staan daar veel voorbeelden zo te zien, ik heb er geen verstand van.

 

[gast0182]

Zeker heb ik google al doorzocht, er staan zeker enkele voorbeelden in. Maar, nog té veel wat gemist wordt. Mooiste zou dus zijn als ik het excel-bestandje (add-in) kon inzien en de formules rechtstreeks eruit halen.

Eenmaal iemand weet hoe we daarin aanpassingen kunnen maken moet het niet extreem moeilijk zijn om de formules "gewoon te voorschijn" te halen in plaats van cijfers

 

[snb]

Misschien heb je al wat aan de bijlage:

 

[gast0182]

Bedankt snb, ga ik zeker gebruiken!

Echter, het probleem is nog niet opgelost.. welke guru kan me uit de brand helpen voor dit moeilijke probleem?

 

[gast0182]

Zijn er nog mensen met ideeën/oplossingen?