Regressionsanalys i Excel

... eller: Glassförsäljning i Lillstad

Lillstad ligger mitt Bergslagen och är en typisk turiststad, med hela kvarter av välbevarade trähus och helt utan den för större städer typiska höghusbebyggelsen. Trafiken är mycket måttlig till och med under turistsäsongen och i staden finns många små, trevliga butiker av äldre typ, men inget varuhus.

Nära torget, intill Systembolaget, ligger en glassbutik med egen glasstillverkning, och glassen heter förstås Lillstadsglass.

Under en vecka i början av turistsäsongen gör man i glassbutiken anteckningar om utetemperaturen klockan tolv på dagen – det är lätt att komma ihåg tidpunkten, eftersom kyrkklockan i det mycket höga kyrktornet ringer då.

Så här ser anteckningarna om temperaturen ut – de finns inskrivna i ett Excel-ark:

Glassbutiken har öppet från 9.00 till 18.00 alla dagar i
veckan under turistsäsongen.

Veckan börjar ganska svalt, men på onsdag eftermiddag
stiger temperaturen och det ser ut att kunna bli en
klassisk svensk värmebölja.

Värmeböljan som började mitt i veckan kommer enligt SMHI att hålla i sig. Eftersom glass är en färskvara – Lillstadsglass säljs alltid nytillverkad – frågar man sig då: "Finns det ett samband mellan utetemperaturen och glassförsäljningen, och hur ser i så fall detta samband ut?"

Alltså: "Hur har glassförsäljningen sett ut under veckan?"
Detta finns det redan anteckningar om – även dessa
uppgifter finns i Excel-arket.

Nu har man således två uppsättningar data – temperatur och glassförsäljning. Med dessa uppgifter vill man se om det finns en matematisk formel som visar ett samband mellan värmen och glassåtgången.

Punktdiagram

Det första vi nu gör är att markera de båda kolumnerna – Kg glass och Temp °C – i Excel-arket. Med kolumnerna markerade klickar vi sedan i verktygsraden på "Diagramguiden" och väljer "Punkt". Därefter klickar vi på "Slutför".

Nu har vi fått ett "rådiagram", som vi sedan snyggar till en aning. Genom att högerklicka i de olika delarna av diagrammet, kan vi formatera delarna.

Värdena från de båda uppsättningarna kan – när de prickas in punkt för punkt i ett diagram – bilda en rät linje eller en kurva av något slag.

Om punkterna i det här diagrammet hade varit spridda som en hagelsvärm, hade vi dragit slutsatsen att det inte finns ett samband mellan värmen och glassåtgången.

Men nu ser punkterna ut att ligga längs en ganska rät linje. Ju varmare det är, desto mer glass säljs det. Vi ser alltså att det tycks finnas ett samband mellan glassförsäljning och temperatur. Nu vill vi veta vilket detta samband är.

Det vill säga: Hur kommer punkterna i diagrammet att ligga vid andra temperaturer än de redan uppmätta?

Regressionsanalys

En linje har en ekvation med ett värde för X-axeln (vågrätt) och ett för Y-axeln (lodrätt).

Med regressionsanalys kan vi få fram den linje som bäst ansluter till punkterna i vårt diagram. Samtidigt får vi då ekvationen för linjen.

Denna ekvation, som är en formel, kan vi utnyttja för att på ett ungefär förutsäga hur punkterna kommer att ligga i diagrammet om vi stoppar in andra värden än de som redan finns i tabellen.

Men hur rätt har vi när vi tycker att punkterna ligger längs en tämligen rät linje? Låt oss undersöka det.

Markera diagrammet, klicka på Diagram och välj "Infoga trendlinje". Här kan vi nu välja den linjetyp som vi tror passar bäst. Eftersom vi tyckte att punkterna nästan bildade en rät linje, väljer vi Linjär.

Under fliken Alternativ markerar vi sedan Automatisk, "Visa ekvation i diagrammet" samt "Visa R-kvadratvärde i diagrammet".

R-kvadratvärdet är ett mått på styrkan i sambandet, alltså på hur bra linjen ansluter till punkterna. Värdet ligger mellan 0 och 1, där 1 är det bästa värdet och anger att punkterna ligger på linjen. Ju närmare 0 värdet kommer, desto svagare blir sambandet – punkterna blir mer och mer spridda som en hagelsvärm.

Vi klickar på OK och linjen, ekvationen och R-kvadratvärdet visas i diagrammet. Ett högerklick på linjen gör att vi kan formatera den och till exempel välja en tunnare linje än det tjocka streck som är standard.

Ekvationen och R-kvadratvärdet kan vi markera och flytta till en annan plats i diagrammet, så att de inte ligger mitt i diagrammet och stör. Vi kan även högerklicka på dem och formatera utseendet.

Ekvationen "y= 11,258x - 92,791" betyder att glassförsäljningen ökar med 11,258 kg för varje grads temperaturökning – y = 11,258 * x.

Talet -92,791 innebär att vid utetemperaturen 0 grader, skulle man sälja
-92,791 kg glass! Här får man helt enkelt väga in sitt eget sunda förnuft.

Nu har vi bara sju värden att utgå från, men låt oss för exemplets skull säga att de här värdena är relevanta – att de verkligen representerar glassförsäljningens allmänna förhållande till utetemperaturen. Vi har i alla händelser ett R-kvadratvärde som ansluter ganska bra till den räta linjen.

Då kan vi till exempel beräkna glassåtgången om temperaturen ökar till 32 grader:

32 * 11,258 - 92,791 = 267,465

Vi skulle i så fall sälja ungefär 270 kg glass.

I menyn Verktyg – Dataanalys finns alternativet Regression tillsammans med en lång rad andra analysmöjligheter. Om Dataanalys inte finns under Verktyg, lägger vi till detta genom att välja Tillägg, och i dialogrutan Tilläggsmakron markera "Analysis ToolPak".

I den här dialogrutan finns också en rad andra funktioner som man kan lägga till i menyerna. Eventuellt måste man sätta in installationsskivan för att lägga till funktionerna.

© SupportData.Net