Excel statistiska funktioner: SKÄRNINGSPUNKT
Anteckning
Office 365 ProPlus byter namn till Microsoft 365-appar för företag. Mer information om den här ändringen finns i det här blogginlägget.
Sammanfattning
I den här artikeln beskrivs funktionen SKÄRNINGSPUNKT i Microsoft Excel, hur du använder funktionen och jämför dess resultat för Excel 2003 och för senare versioner av Excel med resultaten från tidigare versioner av Excel.
Mer information
Funktionen **SKÄRNINGSPUNKT(**known_y, known_x) returnerar SKÄRNINGSPUNKT för den linjära regressionslinje som används för att förutsäga y-värden från x-värden.
Syntax
INTERCEPT(known_y's,known_x's)
Argumenten, known_y och known_x, måste vara matriser eller cellområden som innehåller lika många numeriska datavärden. SKÄRNINGSPUNKT omfattar ofta två cellområden som innehåller data, till exempel SKÄRNINGSPUNKT(A1:A100, B1:B100).
Exempel på användning
Illustrera funktionen SKÄRNINGSPUNKT genom att skapa ett tomt Excel-kalkylblad, kopiera följande tabell, markera cell A1 i det tomma kalkylbladet i Excel och sedan klistra in posterna så att följande tabell fyller cellerna A1:D13 i kalkylbladet.
| A | B | C | D |
|---|---|---|---|
| y-värden | x-värden | ||
| 1 | = 3 + 10^$D$3 | Kraften hos 10 för att lägga till i data | |
| 2 | =4 + 10^$D 3 kr | 0 | |
| 3 | =2 + 10^$D$3 | ||
| 4 | =5 + 10^$D$3 | ||
| 5 | =4+10^$D$3 | ||
| 6 | =7+10^$D$3 | Excel 2002 och tidigare | |
| när D3 = 7,5 | |||
| =LUTNING(A2:A7;B2:B7) | -23717082.0762629 | ||
| =SKÄRNINGSPUNKT(A2:A7;B2:B7) | -24516534.4029667 | ||
| = MEDEL(A2:A7) - A9*AVERAGE(B2:B7) | när D3 = 8 | ||
| =MEDEL(A2:A7) - 0,775280899*MEDEL(B2:B7) | #DIV/0! | ||
| -77528089.6303371 |
Anteckning
När du har klistrat in den här tabellen Excel det nya kalkylbladet klickar du på knappen Inklistringsalternativ och sedan på Matcha målformatering. När det klistrade området fortfarande är markerat använder du en av följande procedurer, efter vad som är lämpligt för den version Excel du kör:
- I Microsoft Office Excel 2007 klickar du på fliken Start, sedan på Format i gruppen Celler och sedan på Autopassa kolumnbredder.
- I Excel 2003 pekar du på Kolumn på Format-menyn och klickar sedan på Autopassa markering.
Du kanske vill formatera cellerna B2:B7 som Tal med 0 decimaler och cellerna A9:D13 som Tal med 6 decimaler.
Cellerna A2:A7 och B2:B7 innehåller de y-värden och x-värden som anropar SKÄRNINGSPUNKT i cell A10.
I versioner av Excel som är tidigare än Excel 2003 kan SKÄRNINGSPUNKT ha avrunda fel. Excel 2003 och senare versioner av Excel att förbättra beteendet hos SKÄRNINGSPUNKT. SKÄRNINGSPUNKT( known_y:s och known_x:s) är resultatet av utvärdering av MEDEL(known_y:s) – LUTNING(known_y:s och known_x:s) * AVERAGE(known_x's). Även om koden för SKÄRNINGSPUNKT inte har ändrats direkt för Excel 2003 och för senare versioner av Excel, förbättras beteendet för SKÄRNINGSPUNKT på grund av förbättrad kod för LUTNING.
Om du har en tidigare version av Excel kan du använda kalkylbladet du skapade tidigare för att köra ett experiment för att upptäcka när fel avrundas. Om du lägger till en positiv konstant för var och en av observationerna i B2:B7 ska detta inte påverka värdet för LUTNING. Om du ritar **x,**y-par med x på den vågräta axeln och y på den lodräta axeln, och sedan lägger till en positiv konstant för varje x-värde, flyttas data bara åt höger. Den bästa regressionslinjen har fortfarande samma lutning. Skiftade data har dock en annan skärningspunkt.
Med standardvärdet 0 i D3 är LUTNING i A9 0,775280899. Cell A10 visar värdet för SKÄRNINGSPUNKT och i cell A11 visas värdet för det uttryck som beräknas vid beräkning av SKÄRNINGSPUNKT:
AVERAGE(known_y)– LUTNING(known_y' och known_x s) * AVERAGE(known_x's)
Värden i cellerna A9 och A10 håller alltid med om att värdet i A10 är exakt det som SKÄRNINGSPUNKT returnerar. LUTNING ska inte variera eftersom du lägger till olika positiva konstanter known_x talets. Cell A11 visar MEDEL(known_y tal) – 0,775280899 *AVERAGE( known_x's). Eftersom LUTNING inte ska ändras, och 0,775280899 är värdet för LUTNING när D3 = 0, måste värdena i det här uttrycket i A11 också godkänna värdena i cellerna A9 och A10.
Om du ökar värdet i D3 lägger du till en större konstant i B2:B7. Om D3 <= 7 finns det inga avrundade fel som visas i de första 6 decimalerna av LUTNING. Men om du provar 7,25, 7,5, 7,75 och 8 ändras LUTNINGen i A9. Därför skiljer sig värdena i cellerna A11 (som håller med A10) och A12. Värdena i A11 (eller A10) och A12 bör dock vara desamma eftersom tillägg av en konstant till known_x påverkar LUTNING.
D7:D13 visar de värden som SKÄRNINGSPUNKT returnerar och de värden som SKÄRNINGSPUNKT skulle ha returnerat om LUTNING inte hade ändrats. Dessa värdepar visas i de fall där D3 = 7,5 respektive 8. Avrunda fel har blivit så allvarligt att division med 0 inträffar när D3 = 8.
Tidigare versioner av Excel ge felaktiga svar i dessa fall eftersom effekterna av avrundade fel är större med beräkningsformeln som dessa versioner använder. Det här experimentet visar fortfarande att felen är extrem.
Om du har Excel 2003 eller en senare version av Excel är det liten eller ingen skillnad mellan de gemensamma värdena i A10 och A11 och värdet i A12 om du provar experimentet. Men cellerna D7:D13 visar de avrunda-fel du får i tidigare versioner av Excel.
Resulterar i tidigare versioner av Excel
I artikeln LUTNING beskrivs den mindre numeriska formeln som tidigare versioner använder. Formeln kräver bara ett pass genom data. Endast grunderna av LUTNING i dessa versioner leder till att SKÄRNINGSPUNKT avrundar fel i extremfall.
Resulterar i Excel 2003 och i senare versioner av Excel
Excel 2003 och senare versioner av Excel förbättrad procedur för att beräkna LUTNING. Därför har prestandan för SKÄRNINGSPUNKT förbättras. Den förbättrade proceduren kräver två steg genom data. Igen, i följande artikel om LUTNING beskrivs förbättringen.
För mer information om förbättringarna i LUTNING för Excel 2003 och för senare versioner av Excel klickar du på följande artikelnummer för att visa artikeln i Microsoft Knowledge Base:
828142 Excel statistiska funktioner: LUTNING
Beslut
Eftersom Excel 2003 och senare versioner av Excel ersätter en metod med två passning, är den numeriska prestandan för LUTNING i Excel 2003 och i senare versioner av Excel bättre än i tidigare versioner av Excel. Därför är den numeriska prestandan för SKÄRNINGSPUNKT bättre. Resultatet i Excel 2003 och i senare versioner av Excel blir aldrig mindre exakt än resultatet i tidigare versioner av Excel.
Det är vanligtvis ingen skillnad mellan resultaten i Excel 2003 och i senare versioner av Excel och resultaten i tidigare versioner av Excel eftersom data inte ofta fungerar på det ovanliga sätt som det här experimentet illustrerar. Den numeriska instabiliteten kommer troligen att visas i tidigare versioner av Excel om data innehåller många signifikanta siffror och liten variation mellan datavärdena.
Följande procedur hittar summan av kvadraterna på avvikelserna för ett sampel medelvärde:
- Hitta sampel medelvärdet.
- Beräkna varje kvadratavvikelse.
- Summera kvadraterna på avvikelserna.
Den här proceduren är mer exakt än följande alternativa procedur (kallas även "kalkylatorformel" eftersom den var lämplig att använda på en miniräknare för ett litet antal datapunkter):
- Hitta summan av kvadraterna för alla observationer, sampelstorleken och summan av alla observationer.
- Beräkna summan av kvadraterna för alla observationer minus (( summan av alla observationer)^2)/sampelstorlek).
Genom att ersätta den här senare proceduren med tvåstegsproceduren som hittar sampel medelvärdet på det första passet och beräknar summan av kvadrater på avvikelserna om det andra passet, Excel 2003 och senare versioner av Excel förbättra många andra funktioner. En kort lista över sådana funktioner är VARIANS, VARIANSP, STDV, STDVP, DVARIANS, DVARIANSP, DSTDDV, DSTDVP, PROGNOS, LUTNING, SKÄRNINGSPUNKT, PEARSON, RSQ och STEYX. Microsoft har gjort liknande förbättringar i var och en av de tre analysverktygen för varians i Analysis ToolPak.