In statistische analyse, de variantie onder de leden van een dataset wordt getoond hoe ver de gegevenspunten zich van een trendlijn bevinden, ook bekend als a regressie lijn. Hoe hoger de variantie, hoe meer verspreid de gegevenspunten zijn. De studie van de variantieanalyse laat zien welke delen van de variantie kunnen worden verklaard door kenmerken van de gegevens en die kunnen worden toegeschreven aan willekeurige factoren. Het deel van de variantie dat niet kan worden verklaard, wordt de restvariantie genoemd.
Excel-spreadsheets gebruiken om residuele variantie te berekenen
De formule om restvariantie te berekenen omvat talrijke complexe berekeningen. Voor kleine gegevenssets kan het proces van het berekenen van de resterende variantie met de hand vervelend zijn. Voor grote gegevenssets kan de taak uitputtend zijn. Als u een Excel-spreadsheet gebruikt, hoeft u alleen de gegevenspunten in te voeren en de juiste formule te selecteren. Het programma verwerkt de complexe berekeningen en levert snel een resultaat op.
Data punten
Open een nieuw Excel-werkblad en voer de gegevenspunten in twee kolommen in. Regressielijnen vereisen dat elk gegevenspunt twee elementen heeft. Statistici labelen deze elementen meestal 'X' en 'Y.' Generic Insurance Co. wil bijvoorbeeld de resterende variantie van de lengte en het gewicht van zijn werknemers vinden. De X-variabele vertegenwoordigt de hoogte en de Y-variabele vertegenwoordigt het gewicht. Voer de hoogte in kolom A en de gewichten in kolom B.
Het gemiddelde vinden
De gemiddelde vertegenwoordigt het gemiddelde voor elk element in de gegevensverzameling. In dit voorbeeld wil Generic Insurance het gemiddelde, de standaardafwijking en de covariantie van de lengte en het gewicht van 10 werknemers vinden. Het gemiddelde van de hoogtes in kolom A kan worden gevonden door de functie "= GEMIDDELDE (A1: A10)" in cel F1 in te voeren. Het gemiddelde van de gewichten in kolom B kan worden gevonden door de functie "= GEMIDDELD (B1: B10)" in cel F3 in te voeren.
De standaarddeviatie en -curariantie vinden
De standaardafwijking meet hoe ver de gegevenspunten zich van het gemiddelde verspreiden. De covariantie meet hoeveel de twee elementen van het datapunt samen veranderen. De standaardafwijking van de hoogte wordt gevonden door de functie "= STDEV (A1: A10)" in cel F2 in te voeren. De standaarddeviatie van de gewichten wordt gevonden door de functie "= STDEV (B1: B10)" in cel F4 in te voeren. De covariantie tussen de hoogten en gewichten wordt gevonden door de functie "= COVAR (A1: A10; B1: B10)" in cel F5 in te voeren.
De regressielijn vinden
De regressie lijn vertegenwoordigt een lineaire functie die de trend van de gegevenspunten volgt. De formule voor de regressielijn ziet er als volgt uit: Y = aX + b.
De gebruiker kan de waarden voor "a" en "b" vinden door de berekeningen te gebruiken voor de gemiddelden, standaarddeviaties en covariantie. De waarde voor "b" vertegenwoordigt het punt waar de regressielijn de Y-as onderschept. De waarde kan worden gevonden door de covariantie te nemen en deze te delen door het kwadraat van de standaarddeviatie van de X-waarden. De Excel-formule gaat naar cel F6 en ziet er als volgt uit: = F5 / F2 ^ 2.
De waarde voor "a" staat voor de helling van de regressielijn. De Excel-formule gaat naar cel F7 en ziet er als volgt uit: = F3-F6 * F1.
Om de formule voor de regressielijn te bekijken, voert u deze reeksaaneenschakeling in cel F8 in:
= CONCATENATE ("Y ="; ROND (F6; 2); "X"; ALS (TEKEN (F7) = 1; "+"; "-"); ABS (RONDE (F7; 2)))
Y-waarden berekenen
De volgende stap omvat het berekenen van de Y-waarden op de regressielijn voor de gegeven X-waarden in de gegevensverzameling. De formule om de Y-waarden te vinden, gaat in kolom C en ziet er als volgt uit:
= $ F $ 6 * A (i) + $ F $ 7
Waarbij A (i) de waarde is voor kolom A in rij (i). De formules zien er als volgt uit in de spreadsheet:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7, enzovoort
De vermeldingen in kolom D laten de verschillen zien tussen de verwachte en de werkelijke waarden voor Y. De formules zien er als volgt uit:
= B (i) -C (i)
Waarbij B (i) en C (i) de waarden in rij (i) in respectievelijk kolom B en C zijn.
De residuele afwijking vinden
De formule voor residuele variantie gaat naar Cell F9 en ziet er als volgt uit:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Waar SUMSQ (D1: D10) de som is van de vierkanten van de verschillen tussen de werkelijke en verwachte Y-waarden, en (COUNT (D1: D10) -2) is het aantal datapunten, minus 2 voor vrijheidsgraden in de gegevens.
