Excel statistiska funktioner: STDDX

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 STDFÖRD I Microsoft Office Excel 2003 och i senare versioner av Excel, hur funktionen används och resultatet av funktionen för Excel 2003 och för senare versioner av Excel jämförs med resultatet av STYX i tidigare versioner av Excel.

Mer information

Funktionen STDFELYX(known_y- och known_x-värden) returnerar standardfelet för Y givet X för en linjär regressionslinje med minsta kvadrater som används för att förutsäga y-värden från x-värden.

Syntax

STEYX(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.

Den vanligaste användningen av STDDX omfattar två cellområden som innehåller data, till exempel STDD(A1:A100; B1:B100).

Exempel på användning

För att illustrera funktionen STDFÖRD skapar du ett tomt Excel-kalkylblad, kopierar följande tabell, markerar cell A1 i ditt tomma Excel-kalkylblad och klistrar sedan in posterna så att tabellen fyller cellerna A1:D12 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 före Excel 2003
när D3 = 7,5
=STDDX(A2:A7;B2:B7) 1.48954691097662
när D3 = 8
#DIV/0!

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 kolumnbredd.
  • 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 cell A9:D9 som Tal med sex decimaler.

Cellerna A2:A7 och B2:B7 innehåller de y-värden och x-värden som används för att anropa STDFÖRD i cell A9.

Om du har en tidigare version Excel än version Excel 2003 bör du känna till att STDFELYX kan ha avrundningfel. STDX-beteendet har förbättrats i Excel 2003 och i senare versioner av Excel.

Om du har en tidigare version av Excel får du möjlighet att experimentera i kalkylbladet och upptäcka när fel avrundas. Om du lägger till en positiv konstant för var och en av observationerna i B2:B7 bör detta inte påverka värdet för STDDX. Om du ritar x,y-par med x på den vågräta axeln och y på den lodräta axeln flyttas data till höger om en positiv konstant läggs till för varje x-värde. Regressionslinjen som passar bäst skulle fortfarande ha samma lutning och god passform och bör ha samma värde som STDDX.

Om du ökar värdet i D3 läggs en större konstant till i B2:B7. Om D2 <= 7 finns det inga avrundade fel som visas i de första sex decimalerna av STDFELYX. Men prova sedan 7.25, 7.5, 7.75 och 8. D7:D12 visar värden för STDDX när D2 = 7,5 respektive 8. Avrundningfel har blivit så allvarliga att division med 0 inträffar när D3 = 8.

I tidigare versioner Excel fel svar i dessa fall eftersom effekterna av avrundade fel är mer aktuella med beräkningsformeln som används av dessa versioner. De fall som används i det här experimentet kan ändå visas som relativt extrem.

Om du har Excel 2003 eller en senare version av Excel, visas inga ändringar i värdena för STDREX om du provar det experiment som beskrivs tidigare. Cellerna D7:D12 visar emellertid avrunda-fel som du skulle ha fått via tidigare versioner av Excel.

Resulterar i tidigare versioner av Excel

Om du anropar de två datamatriserna X och Y, använde tidigare versioner av Excel ett enda pass genom data för att beräkna summan av kvadraterna av X, summan av kvadraterna av Y-ar, summan av X:er, summan av Y:er, summan av XY och antalet observationer i varje matris. Kvantiteterna kombinerades sedan i beräkningsformeln som finns i hjälpfilen i tidigare versioner av Excel.

Resulterar i Excel 2003 och i senare versioner av Excel

Proceduren som används i Excel 2003 och i senare versioner av Excel använder en process som går två steg genom data. Först beräknas summorna av X och Y och antalet observationer i varje matris, och utifrån dessa beräknas medelvärdet (medelvärden) av X- och Y-observationer. I det andra passet

  • kvadraten på skillnaden mellan varje X och X-medelvärdet och de kvadrerade skillnaderna summeras,
  • kvadraten på skillnaden mellan varje Y och Y-medelvärdet och de kvadratiska skillnaderna summeras och
  • produkterna (X – X medelvärde) * (Y – Y-medelvärde) hittas för varje par datapunkter och summeras.

STDDX beräknas sedan med hjälp av formeln i hjälpfilen för STDDX i Excel 2003 och i senare versioner av Excel. Observera att ingen av dessa tre summor påverkas om en konstant läggs till för varje X-värde, eftersom samma värde adderas till X-medelvärdet. I de numeriska exemplen påverkas inte de här tre summorna, även om de är 10 stora i cell D3, och resultatet av det andra passet är oberoende av inmatningen i cell D3. Därför är resultaten i Excel 2003 och i senare versioner Excel mer stabila numeriskt.

Beslut

Ersätta en metod med ett pass med två pass-garantier för högre numerisk prestanda hos STDF I Excel 2003 och i senare versioner av Excel. Resultatet i Excel 2003 och senare versioner Excel aldrig blir mindre exakt än resultatet i tidigare versioner.

I de flesta praktiska exempel är det dock inte sannolikt att resultatet i senare versioner av Excel skiljer sig från resultatet i tidigare versioner Excel. Det beror på att det är osannolikt att vanliga data visar vilken typ av ovanlig beteende som visas i experimentet. Den numeriska instabiliteten kommer troligen att visas i tidigare versioner av Excel när data innehåller ett stort antal signifikanta siffror i kombination med relativt liten variation mellan datavärden.

Proceduren för att hitta summan av kvadrater på avvikelser om ett sampel medelvärde med

  • hitta medelvärdet,
  • beräkna varje kvadratavvikelse och
  • summera kvadraterna på avvikelserna

är mer exakt än den alternativa proceduren. Den här proceduren kallas ofta "kalkylatorformel" eftersom den var lämplig för användning av en kalkylator på ett litet antal datapunkter. Kalkylatorns formel använder följande procedurer:

  • 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).

Det finns många andra funktioner som har förbättrats för Excel 2003 och för senare versioner av Excel. Dessa funktioner har förbättrats genom att ersätta proceduren med två passning som hittar sampel medelvärdet vid första passet och beräknar summan av kvadrater på avvikelserna för sampel medelvärdet på det andra passet.

Funktioner som har förbättrats på det här sättet för Excel 2003 och för senare versioner Excel innehåller följande funktioner:

  • VARIANS
  • VARP
  • STDDV
  • STDDVP
  • VARVAR
  • VARP
  • DSTDDV
  • DSTDDVP
  • PROGNOS
  • LUTNING
  • SKÄRNINGSPUNKT
  • PEARSON
  • RKV
  • STEYX

Liknande förbättringar har gjorts i var och en av de tre analysverktygen för varians i Analysis ToolPak.