Excel-də statistika: orta, səpələnmə, korrelyasiya və reqressiya

Bu dərsdə öyrənəcəksən

Orta, median və moda arasında seçim etmək və nəticəni şərh etmək
Seçmənin və baş məcmunun standart kənarlaşmasını düzgün hesablamaq
Korrelyasiya əmsalını və reqressiya xəttini hesablayıb proqnoz vermək
Analysis ToolPak ilə reqressiya hesabatını oxumaq və histoqram qurmaq

Müəllim səkkiz tələbənin imtahana hazırlıq saatlarını və ballarını yazıb: saatlar 2, 4, 5, 6, 8, 9, 10, 12; ballar 52, 60, 58, 70, 75, 80, 83, 92. Orta bal nədir və ballar nə qədər səpələnib? Saatlarla bal arasında əlaqə varmı, 10 saat oxuyan tələbə neçə bal gözləyə bilər? Bu suallar statistikanın əsasıdır və Excel onları bir neçə funksiya ilə cavablandırır.

Mərkəzi meyl: AVERAGE, MEDIAN, MODE

Ölçü	Excel	Ballar üçün	Nə vaxt işlətməli
Orta (x̄ = ∑x / n)	`=AVERAGE(B2:B9)`	71,25	simmetrik, «kənar» qiymətsiz verilənlər
Median (ortadakı qiymət)	`=MEDIAN(B2:B9)`	72,5	maaşlar, qiymətlər — kənar qiymətlər olanda
Moda (ən çox təkrarlanan)	`=MODE.SNGL(…)`	#N/A	məktəb qiymətləri, geyim ölçüləri; bu ballarda təkrar yoxdur

Orta, yoxsa median?

Kiçik şirkətdə 7 işçinin aylıq maaşı: 800, 850, 900, 950, 1000, 1100 və direktorun 6000 ₼. «Tipik» maaşı hansı ölçü daha yaxşı göstərir? Sinifdə qiymətlər: 4, 5, 5, 3, 5, 4, 2, 5, 4, 5 — moda nədir?

Həllini göstər

Orta: 11 600 / 7 ≈ 1657,14 ₼ — yeddi nəfərdən altısı bundan az alır!
Median: sıralanmış siyahının ortası — 950 ₼. Kənar qiymət (6000) medianı dəyişmir, ona görə maaş statistikasında mediana üstünlük verilir.
Qiymətlər: =MODE.SNGL(A2:A11) → 5 (beş dəfə), orta 4,2, median 4,5.
Bir neçə moda ola bilərsə, =MODE.MULT(…) hamısını massiv kimi qaytarır.

Səpələnmə: STDEV.S və STDEV.P

s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )s = √( ∑(xᵢ − x̄)² / (n − 1) ); σ = √( ∑(xᵢ − μ)² / N )

burada:

sseçmə standart kənarlaşması — STDEV.S
σbaş məcmunun standart kənarlaşması — STDEV.P
n, Nseçmənin və baş məcmunun həcmi

n − 1 (Bessel düzəlişi): seçmənin ortası verilənlərə «ən yaxın» nöqtədir, ona görə kənarlaşmaların kvadratları sistematik olaraq lazım olduğundan kiçik çıxır; n − 1-ə bölmək bunu kompensasiya edir. Ballar üçün: s = 13,802, σ = 12,911.

Korrelyasiya və xətti reqressiya

r = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · xr = Sₓᵧ / √(Sₓₓ · Sᵧᵧ); b = Sₓᵧ / Sₓₓ; a = ȳ − b · x̄; ŷ = a + b · x

burada:

Sₓₓ, Sᵧᵧ∑(x − x̄)² və ∑(y − ȳ)²
Sₓᵧ∑(x − x̄)(y − ȳ) — birgə dəyişmə
rkorrelyasiya əmsalı, −1 ≤ r ≤ 1 — CORREL
b, ameyl (bal/saat) və kəsişmə (bal) — SLOPE, INTERCEPT

b və a ən kiçik kvadratlar üsulundan alınır: ∑(y − a − bx)² minimum olsun deyə a və b üzrə törəmələri sıfıra bərabər edirik. Nəticədə xətt həmişə (x̄, ȳ) nöqtəsindən keçir.

Saatlar və ballar

A2:A9-da saatlar, B2:B9-da ballar var. r, reqressiya xəttini, R²-ni hesabla və 10 saat oxuyan tələbənin balını proqnozlaşdır.

Həllini göstər

x̄ = 7, ȳ = 71,25; Sₓₓ = 78, Sᵧᵧ = 1333,5, Sₓᵧ = 318.
r = 318 / √(78 · 1333,5) ≈ 0,986 — çox güclü müsbət əlaqə; =CORREL(A2:A9,B2:B9).
b = 318 / 78 ≈ 4,077 bal/saat (=SLOPE(B2:B9,A2:A9)); a = 71,25 − 4,077 · 7 ≈ 42,71 (=INTERCEPT(B2:B9,A2:A9)).
Xətt: ŷ = 42,71 + 4,077 · x. R² = r² ≈ 0,972 (=RSQ(…)): balların dəyişkənliyinin 97,2%-i saatlarla izah olunur.
Proqnoz: =FORECAST.LINEAR(10,B2:B9,A2:A9) → 42,71 + 40,77 ≈ 83,48 bal.

İnteraktiv

Simulyasiya yüklənir…

8 tələbə: hazırlıq saatları və ballar; korrelyasiya, reqressiya və histoqram tezlikləri.

Analysis ToolPak: reqressiya hesabatı və histoqram

1
Əlavəni qoş
File › Options › Add-ins › Manage: Excel Add-ins › Go… › Analysis ToolPak-ı işarələ. Data › Analyze › Data Analysis düyməsi görünəcək.
2
Reqressiyanı işə sal
Data Analysis › Regression: Input Y Range B1:B9, Input X Range A1:A9, Labels-i işarələ, Output Range və ya New Worksheet Ply seç › OK.
3
Hesabatı oxu
Multiple R 0,986; R Square 0,972; Adjusted R Square 0,968; Standard Error 2,48 bal; Coefficients: Intercept 42,71, Hours 4,077; meylin P-value ≈ 0,0000068 < 0,05 — əlaqə statistik cəhətdən əhəmiyyətlidir.
4
Histoqram qur
Ən sürətli yol: B1:B9 › Insert › Charts › Insert Statistic Chart › Histogram; zolaq enini Format Axis › Bin width = 10 ilə təyin et. Alternativ: Data Analysis › Histogram və ayrıca Bin Range (59, 69, 79, 89, 99).

Əsas fikirlər

Kənar qiymətlər olanda median ortadan daha yaxşı «tipik» dəyər göstərir.
STDEV.S seçmə üçün (n − 1-ə bölür), STDEV.P bütün məcmu üçün (N-ə bölür).
r = Sₓᵧ / √(Sₓₓ Sᵧᵧ); b = Sₓᵧ / Sₓₓ, a = ȳ − b x̄; reqressiya xətti (x̄, ȳ)-dən keçir.
FORECAST.LINEAR və TREND xətt üzrə proqnoz verir — yalnız müşahidə aralığında etibarlıdır.
Analysis ToolPak reqressiya hesabatı (R², əmsallar, p-dəyərləri) və histoqram qurur.

Özünü yoxla

10 sual. Hər düzgün cavab XP qazandırır.

1 / 10

Maaşlar: 800, 850, 900, 950, 1000, 1100, 6000 ₼. MEDIAN nə qaytarır?