Excel statistiska funktioner: RKV
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 RKV i Microsoft Office Excel 2003 och i senare versioner av Excel. I den här artikeln beskrivs hur funktionen används och resultaten av RKV i dessa senare versioner av Excel jämförs med resultaten av RQ i tidigare versioner Excel.
Mer information
Funktionen RKV(matris1; matris2) returnerar Kvadraten av Pearson Product-Moment Korrelationskoefficienten mellan två matriser med data.
Syntax
RSQ(array1, array2)
Argumenten matris1 och matris2 måste vara antingen tal eller namn, matriskonstanterna eller referenser som innehåller tal.
Den vanligaste användningen av RKV omfattar två cellområden som innehåller data, till exempel RKV(A1:A100, B1:B100).
Exempel på användning
Gör så här för att illustrera funktionen RKV:
Skapa ett tomt Excel kalkylblad och kopiera sedan följande tabell.
A B C D 1 = 3 + 10^$D$2 Kraften hos 10 för att lägga till i data 2 =4 + 10^$D$2 0 3 =2 + 10^$D$2 4 =5 + 10^$D$2 5 =4+10^$D$2 6 =7+10^$D$2 före Excel 2003 =RKV(A1:A6;B1:B6) när D2 = 7,5 =PEARSON(A1:A6;B1:B6)^2 RKV = PEARSON^2 0.492857142857143 =KORREL(A1:A6;B1:B6)^2 KORREL^2 0.509470304975923 när D2 = 8 RKV = PEARSON^2 #DIV/0! KORREL^2 0.509470304975923 Markera cell A1 i Excel kalkylblad och klistra sedan in posterna så att tabellen fyller cellerna A1:D13 i kalkylbladet.
När du har klistrat in tabellen i Excel 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.
Anteckning
Du kanske vill formatera cellerna B1:B6 som Tal med 0 decimaler.
Cellerna A1:A6 och B1:B6 innehåller de två datamatriser som används i det här exemplet för att anropa RKV, PEARSON och KORREL i cellerna A8:A10. RKV beräknas genom att pearson beräknas och resultatet kvartil beräknas. Eftersom PEARSON och KORREL både beräknar Pearson-Product-Moment korrelationskoefficienten bör resultaten vara överens. RKV kunde ha (men inte) implementerats som i huvudsak beräkna KORREL och kvartilera resultatet.
I tidigare Excel än Excel 2003 kan PEARSON ha avrundningfel. Det här beteendet leder till avrundningsfel i OSA. Pearsons beteende, och därmed RKV, har förbättrats i Excel 2003 och i senare versioner av Excel. KORREL har alltid implementerats med den förbättrade proceduren i Excel 2003 och i senare versioner av Excel. Därför är ett alternativ till RKV för en tidigare version av Excel att använda KORREL i stället och sedan kvadrera resultatet.
I tidigare Excel än Excel 2003 kan du använda kalkylbladet i den här artikeln för att köra ett experiment och för att upptäcka när fel avrundas. Om du lägger till en konstant för var och en av observationerna i B1:B6 påverkas inte värdena för RKV, PEARSON^2 och KORREL^2 i cellerna A7:A9. Om du ökar värdet i D2 läggs en större konstant till i B1:B6. Om D2 <= 7 finns det inga avrunda-fel som visas i A7:A9. Ändra nu värdet för 7,25, 7.5, 7.75 och sedan 8. KORREL^2 i A9 påverkas inte, men RKV och PEARSON^2 ( (dessa uttryck håller alltid med varandra) visar avrundningsfel i A7:A8. D6:D13 visar värden för RKV = PEARSON^2 och KORREL^2 när D2 = 7,5 respektive 8.
Observera att KORREL fortfarande fungerar bra, men avrundningsfel i PEARSON har blivit så allvarliga att division med 0 förekommer i RKV och PEARSON^2 när D2 = 8.
I tidigare Excel har felaktiga svar i dessa fall eftersom effekterna av avrundade fel är mer aktuella med den beräkningsformel som används av dessa versioner av Excel. De fall som används i det här experimentet kan ändå visas som extrem.
Om du har Excel 2003 eller en senare version av Excel, visas inga förändringar i värdena för RKV och PEARSON^2 om du provar experimentet. Men cellerna D6:D13 visar avrunda-fel som du skulle ha fått i tidigare versioner av Excel.
Resulterar i tidigare versioner av Excel
Om du namnar de två datamatriserna X och Y, använde tidigare versioner av Excel ett enda passning genom data för att beräkna summan av kvadraterna av X, summan av kvadraterna av Y,s, summan av X-tal, summan av Y-arna, summan av XY och antalet observationer i varje matris. Kvantiteterna kombinerades sedan i beräkningsformeln som ges i hjälpfilen i tidigare versioner av Excel. Hjälpfilen för RKV visar formeln för Pearson-Product-Moment Korrelationskoefficienten. Resultatet kvadreras för att erhålla RKV.
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 kan medelvärden (medelvärden) av X- och Y-observationer beräknas. I det andra passet hittas sedan kvadraten på skillnaden mellan varje X och X-medelvärdet, och dessa kvadrerade skillnader summeras. Den kvadrerade skillnaden mellan varje Y och Y-medelvärdet hittas och dessa kvadrerade skillnader summeras. Dessutom finns produkterna (X – X medelvärde) * (Y – Y-medelvärde) för varje par datapunkter och summeras. Dessa tre summor kombineras i formeln för PEARSON. Observera att ingen av de tre summorna påverkas om du lägger till en konstant för varje värde i Y-matrisen (eller i X-matrisen). Det här beteendet uppstår eftersom samma värde adderas till Y-medelvärdet (eller x-medelvärdet). I de numeriska exemplen påverkas inte de här tre summorna, även om de har en hög exkl. 10 i cell D12, och resultatet av det andra passet är oberoende av inmatningen i cell D2. Därför är resultaten i Excel 2003 och i senare versioner Excel mer stabila numeriskt.
Beslut
Om du ersätter en metod med ett godkänt resultat med två pass-garantier får du bättre numerisk prestanda för PEARSON och därför RSQ i Excel 2003 och i senare versioner av Excel. De resultat du får i Excel 2003 och i senare versioner av Excel blir aldrig mindre exakta än de resultat du fått i tidigare versioner Excel.
I de flesta praktiska exempel är det inte sannolikt att resultatet i senare versioner av Excel skiljer sig från resultatet i tidigare versioner Excel. Det här beteendet inträffar eftersom 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 beräkna summan av kvadrater på ett sampel medelvärde genom att hitta medelvärdet, genom att beräkna varje kvadratavvikelse och genom att summera kvadraterna på avvikelserna är mer exakta än den alternativa proceduren. Den här alternativa proceduren kallades ofta för "kalkylatorformel" eftersom den var lämplig för användning av en miniräknare på ett litet antal datapunkter. Den alternativa proceduren använde följande procedur:
- Hittade summan av kvadraterna för alla observationer, sampelstorleken och summan av alla observationer
- Beräknade 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 i Excel 2003 och för senare versioner av Excel. De här funktionerna förbättras eftersom senare versioner av Excel ersätter proceduren med ett pass med två passningsproceduren som hittar sampel medelvärdet på första passet och sedan beräknar summan av kvadrater på avvikelserna om sampel medelvärdet på den andra passet.
Följande lista är en lista över sådana 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.