Giải toán Xác suất thống kê bằng Excel
XỬ LÝ THỐNG KÊ BẰNG EXCEL
Các hàm thống kê có thể chia thành 3 nhóm nhỏ sau: Nhóm hàm về Thống Kê, nhóm hàm về Phân
Phối Xác Suất, và nhóm hàm về Tương Quan và Hồi Quy TuyếnTính
NHÓM HÀM VỀ THỐNG KÊ
AVEDEV (number1, number2, ...)
Tính trung bình độ lệch tuyệt đối các điểm dữ liệu theo trung
bình của chúng. Thường dùng làm thước đo về sự biến đổi
của tập số liệu
AVERAGE (number1, number2, ...)
AVERAGEA (number1, number2, ...)
Tính trung bình cộng
Tính trung bình cộng của các giá trị, bao gồm cả những giá
trị logic
AVERAGEIF (range, criteria1)
Tính trung bình cộng của các giá trị trong một mảng theo
một điều kiện
AVERAGEIFS (range, criteria1, criteria2,
...)
Tính trung bình cộng của các giá trị trong một mảng theo
nhiều điều kiện
COUNT (value1, value2, ...)
COUNTA (value1, value2, ...)
COUNTBLANK (range)
Đếm số ô trong danh sách.
Đếm số ô có chứa giá trị (không rỗng) trong danh sách.
Đếm các ô rỗng trong một vùng.
COUNTIF (range, criteria)
Đếm số ô thỏa một điều kiện cho trước bên trong một dãy
Đếm số ô thỏa nhiều điều kiện cho trước.
COUNTIFS (range1, criteria1,
range2,criteria2,…)
DEVSQ (number1, number2, ...)
Tính bình phương độ lệch các điểm dữ liệu từ trung bình mẫu
của chúng, rồi cộng các bình phương đó lại.
FREQUENCY (data_array, bins_array)
Tính xem có bao nhiêu giá trị thường xuyên xuất hiện bên
trong một dãy giá trị, rồi trả về một mảng đứng các số. Luôn
sử dụng hàm này ở dạng công thức mảng
GEOMEAN (number1, number2, ...)
Trả về trung bình nhân của một dãy các số dương. Thường
dùng để tính mức tăng trưởng trung bình, trong đó lãi kép có
các lãi biến đổi được cho trước…
HARMEAN (number1, number2, ...)
KURT (number1, number2, ...)
Trả về trung bình điều hòa (nghịch đảo của trung bình cộng)
của các số
Tính độ nhọn của tập số liệu, biểu thị mức nhọn hay mức
phẳng tương đối của một phân bố so với phân bố chuẩn
LARGE (array, k)
Trả về giá trị lớn nhất thứ k trong một tập số liệu.
Trả về giá trị lớn nhất của một tập giá trị.
MAX (number1, number2, ...)
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
1
MAXA (number1, number2, ...)
Trả về giá trị lớn nhất của một tập giá trị, bao gồm cả các giá
trị logic và text
MEDIAN (number1, number2, ...)
MIN (number1, number2, ...)
MINA (number1, number2, ...)
Tính trung bình vị của các số.
Trả về giá trị nhỏ nhất của một tập giá trị.
Trả về giá trị nhỏ nhất của một tập giá trị, bao gồm cả các
giá trị logic và text.
MODE (number1, number2, ...)
PERCENTILE (array, k)
Trả về giá trị xuất hiện nhiều nhất trong một mảng giá trị.
Tìm phân vị thứ k của các giá trị trong một mảng dữ liệu.
PERCENTRANK (array, x, significance)
Trả về thứ hạng (vị trí tương đối) của một trị trong một
mảng dữ liệu, là số phần trăm của mảng dữ liệu đó
PERMUT (number, number_chosen)
QUARTILE (array, quart)
Trả về hoán vị của các đối tượng.
Tính điểm tứ phân vị của tập dữ liệu. Thường được dùng
trong khảo sát dữ liệu để chia các tập hợp thành nhiều
nhóm…
RANK (number, ref, order)
Tính thứ hạng của một số trong danh sách các số.
SKEW (number1, number2, ...)
Trả về độ lệch của phân phối, mô tả độ không đối xứng của
phân phối quanh trị trung bình của nó.
SMALL (array, k) :
Trả về giá trị nhỏ nhất thứ k trong một tập số.
Ước lượng độ lệch chuẩn trên cơ sở mẫu.
STDEV (number1, number2, ...)
STDEVA (value1, value2, ...)
Ước lượng độ lệch chuẩn trên cơ sở mẫu, bao gồm cả những
giá trị logic.
STDEVP (number1, number2, ...)
STDEVPA (value1, value2, ...)
Tính độ lệch chuẩn theo toàn thể tập hợp.
Tính độ lệch chuẩn theo toàn thể tập hợp, kể cả chữ và các
giá trị logic.
VAR (number1, number2, ...)
VARA (value1, value2, …)
Trả về phương sai dựa trên mẫu.
Trả về phương sai dựa trên mẫu, bao gồm cả các trị logic và
text.
VARP (number1, number2, ...)
VARPA (value1, value2, …)
Trả về phương sai dựa trên toàn thể tập hợp.
Trả về phương sai dựa trên toàn thể tập hợp, bao gồm cả các
trị logic và text.
TRIMMEAN (array, percent)
Tính trung bình phần trong của một tập dữ liệu, bằng cách
loại tỷ lệ phần trăm của các điểm dữ liệu ở đầu và ở cuối tập
dữ liệu.
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
2
NHÓM HÀM VỀ PHÂN PHỐI XÁC SUẤT
BETADIST (x, alpha, beta, A, B)
Trả về giá trị của hàm tính mật độ phân phối xác suất
tích lũy beta.
BETAINV (probability, alpha, beta, A, B)
Trả về nghịch đảo của hàm tính mật độ phân phối xác
suất tích lũy beta
BINOMDIST (number_s, trials, probability_s,
cumulative)
Trả về xác suất của những lần thử thành công của
phân phối nhị phân.
CHIDIST (x, degrees_freedom)
Trả về xác xuất một phía của phân phối chi-squared.
CHIINV (probability, degrees_freedom)
Trả về nghịch đảo của xác xuất một phía của phân
phối chi-squared.
CHITEST (actual_range, expected_range)
Trả về giá trị của xác xuất từ phân phối chi-squared
và số bậc tự do tương ứng.
CONFIDENCE (alpha, standard_dev, size)
CRITBINOM (trials, probability_s, alpha)
Tính khoảng tin cậy cho một kỳ vọng lý thuyết
Trả về giá trị nhỏ nhất sao cho phân phối nhị thức
tích lũy lớn hơn hay bằng giá trị tiêu chuẩn. Thường
dùng để bảo đảm các ứng dụng đạt chất lượng…
EXPONDIST (x, lambda, cumulative) :
Tính phân phối mũ. Thường dùng để mô phỏng thời
gian giữa các biến cố…
FDIST (x, degrees_freedom1, degrees_freedom2) Tính phân phối xác suất F. Thường dùng để tìm xem
hai tập số liệu có nhiều mức độ khác nhau hay
không…
FINV (probability, degrees_freedom1,
degrees_freedom2)
Tính nghịch đảo của phân phối xác suất F. Thường
dùng để so sánh độ biến thiên trong hai tập số liệu.
FTEST (array1, array2) :
Trả về kết quả của một phép thử F. Thường dùng để
xác định xem hai mẫu có các phương sai khác nhau
hay không…
FISHER (x)
Trả về phép biến đổi Fisher tại x. Thường dùng để
kiểm tra giả thuyết dựa trên hệ số tương quan…
FISHERINV (y)
Tính nghịch đảo phép biến đổi Fisher. Thường dùng
để phân tích mối tương quan giữa các mảng số liệu…
GAMMADIST (x, alpha, beta, cumulative)
Trả về phân phối tích lũy gamma. Có thể dùng để
nghiên cứu có phân bố lệch.
GAMMAINV (probability, alpha, beta)
GAMMLN (x)
Trả về nghịch đảo của phân phối tích lũy gamma.
Tính logarit tự nhiên của hàm gamma.
HYPGEOMDIST (number1, number2, ...)
Trả về phân phối siêu bội (xác suất của một số lần
thành công nào đó…)
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
3
LOGINV (probability, mean, standard_dev)
LOGNORMDIST (x, mean, standard_dev)
Tính nghịch đảo của hàm phân phối tích lũy lognormal
của x (LOGNORMDIST)
Trả về phân phối tích lũy lognormal của x, trong đó
logarit tự nhiên của x thường được phân phối với các
tham số mean và standard_dev.
NEGBINOMDIST (number_f, number_s,
probability_s)
Trả về phân phối nhị thức âm (trả về xác suất mà sẽ
có number_f lần thất bại trước khi có number_s lần
thành công, khi xác suất không đổi của một lần thành
công là probability_s)
NORMDIST (x, mean, standard_dev, cumulative)
Trả về phân phối chuẩn (normal distribution). Thường
được sử dụng trong việc thống kê, gồm cả việc kiểm
tra giả thuyết.
NORMINV (probability, mean, standard_dev)
NORMSDIST (z)
Tính nghịch đảo phân phối tích lũy chuẩn.
Trả về hàm phân phối tích lũy chuẩn tắc (standard
normal cumulative distribution function), là phân phối
có trị trung bình cộng là zero (0) và độ lệch chuẩn là
1.
NORMSINV (probability)
Tính nghịch đảo của hàm phân phối tích lũy chuẩn
tắc.
POISSON (x, mean, cumulative)
Trả về phân phối poisson. Thường dùng để ước tính
số lượng biến cố sẽ xảy ra trong một khoảng thời gian
nhất định.
PROB (x_range, prob_range, lower_limit,
upper_limit)
Tính xác suất của các trị trong dãy nằm giữa hai giới
hạn.
STANDARDIZE (x, mean, standard_dev)
TDIST (x, degrees_freedom, tails)
Trả về trị chuẩn hóa từ phân phối biểu thị bởi mean
và standard_dev.
Trả về xác suất của phân phối Student (phân phối t),
trong đó x là giá trị tính từ t và được dùng để tính xác
suất.
TINV (probability, degrees_freedom)
TTEST (array1, array2, tails, type)
WEIBULL (x, alpha, beta, cumulative)
Trả về giá trị t của phân phối Student.
Tính xác xuất kết hợp với phép thử Student.
Trả về phân phối Weibull. Thường sử dụng trong phân
tích độ tin cậy, như tính tuổi thọ trung bình của một
thiết bị.
ZTEST (array, x, sigma)
Trả về xác suất một phía của phép thử z.
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
4
NHÓM HÀM VỀ TƯƠNG QUAN VÀ HỒI QUY TUYẾN TÍNH
CORREL (array1, array2)
Tính hệ số tương quan giữa hai mảng để xác định mối
quan hệ của hai đặc tính.
COVAR (array1, array2)
Tính tích số các độ lệch của mỗi cặp điểm dữ liệu, rồi
tính trung bình các tích số đó.
FORECAST (x, known_y's, known_x's)
Tính toán hay dự đoán một giá trị tương lai bằng cách
sử dụng các giá trị hiện có, bằng phương pháp hồi
quy tuyến tính.
GROWTH (known_y's, known_x's, new_x's,
Tính toán sự tăng trưởng dự kiến theo hàm mũ, bằng
const)
cách
sử
dụng
các
dữ
kiện
hiện
có.
INTERCEPT (known_y's, known_x's)
Tìm điểm giao nhau của một đường thẳng với trục y
bằng cách sử dụng các trị x và y cho trước
LINEST (known_y's, known_x's, const, stats)
Tính thống kê cho một đường bằng cách dùng phương
pháp bình phương tối thiểu (least squares) để tính
đường thẳng thích hợp nhất với dữ liệu, rồi trả về
mảng mô tả đường thẳng đó. Luôn dùng hàm này ở
dạng công thức mảng.
LOGEST (known_y's, known_x's, const, stats)
PEARSON (array1, array2)
Dùng trong phân tích hồi quy. Hàm sẽ tính đường
cong hàm mũ phù hợp với dữ liệu được cung cấp, rồi
trả về mảng gía trị mô tả đường cong đó. Luôn dùng
hàm này ở dạng công thức mảng.
Tính hệ số tương quan momen tích pearson (r), một
chỉ mục không thứ nguyên, trong khoảng từ -1 đến 1,
phản ánh sự mở rộng quan hệ tuyến tính giữa hai tập
số liệu.
RSQ (known_y's, known_x's)
Tính bình phương hệ số tương quan momen tích
Pearson (r), thông qua các điểm dữ liệu trong
known_y's và known_x's.
SLOPE (known_y's, known_x's)
Tính hệ số góc của đường hồi quy tuyến tính thông
qua các điềm dữ liệu.
STEYX (known_y's, known_x's)
Trả về sai số chuẩn của trị dự đoán y đối với mỗi trị x
trong hồi quy.
TREND (known_y's, known_x's, new_x's, const)
Trả về các trị theo xu thế tuyến tính
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
5
Ngoài cách dùng các hàm trên ta còn dùng menu Analysis ToolPak cài đặt như sau: Trong Excel
chọn menu Tools/Add-Ins …/Analysis ToolPak / Ok
Khi chọn menu Tools / Data Analysis …
Chọn các mục cần thiết trong các thực đơn trên để giải các bài toán dưới đây:
I. THỐNG KÊ MÔ TẢ (Descriptive Statistics)
1) Bảng phân phối tần số - Bảng phân phối tần suất
§ Nhập dữ liệu
§ Dùng hàm: FREQUENCY (data_array, bins_array)
§ data_array : Địa chỉ mảng dữ liệu
§ bins_array: Địa chỉ mảng các giá trị khác nhau của dữ liệu.
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
6
Ví dụ : Lập bảng và vẽ biểu đồ dữ liệu sau:
12
13
11
13
15
12
11
10
14
13
12
15
§ Lập bảng phân phối tần số:
o
o
Nhập cột giá trị khác nhau vào C3:C8
Đánh dấu khối cột tần số ở D3:D8 , nhấn F2 nhập công thức
= frequency(A2: A13 , C3:C8) và ấn CTRL+SHIFT +ENTER
§ Lập bảng phân phối tần suất:nhập vào G2 công thức =D3/$D$9 ,copy các ô còn lại.
§
Vẽ biểu đồ
o
o
o
Chọn menu: Insert/ Chart…/ Line/ Next
Nhập vào Data Range : $G$3:$G$8 và chọn mục Column
Chọn Tab Series , nhập địa chỉ cột giá trị: $F$3:$F$8 vào Category (X) axis
labels
o
Chọn Next , Finish
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
7
0.3
0.25
0.2
0.15
0.1
Series1
0.05
0
10
11
12
13
14
15
2) Đặc trung mẫu
Ví dụ: Tính đặc trưng mẫu của dữ liệu sau:
12
13
11
13
15
12
11
10
14
13
12
15
· Nhập dữ liệu trong cột A1:A12
· Chọn menu Tools/Data Analysis…/Descriptive Statistics
· Nhập các mục:
§
§
§
Input Range: địa chỉ tuyệt đối chứa dữ liệu $A$1:$A$12
Output Range: địa chỉ xuất kết quả
Confidence Level for Mean (Độ tin cậy cho trung bình)
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
8
· Kết quả bao gồm: Kỳ vọng (trung bình), phương sai, trung vị, mode, độ lệch chuẩn, độ
nhọn, độ nghiêng (hệ số bất đối xứng so với phân phối chuẩn), khoảng biến thiên, max,
min, sum, số mẫu (count), khoảng tin cậy của trung bình ở mức 95% .
Tính theo các hàm
Column1
Giá trị trung bình
Sai số mẫu
AVERAGE(A1:A12)
x = 12.58333
Mean
Sx
= 0.451569
Standard Error
n
Trung vị
MEDIAN(A1:A12)
MODE(A1:A12)
STDEV(A1:A12)
VAR(A1:A12)
Median
12.5
12
Mode
Mode
Độ lệch chuẩn
Phương sai mẫu
Standard Deviation
Sample Variance
Kurtosis
sx= 1.564279
2.44697
Độ nhọn của đỉnh KURT(A1:A12)
Độ nghiêng SKEW(A1:A12)
Khoảng biến thiên MAX()-MIN()
-0.61768
Skewness
Range
0.157146
5
10
Tối thiểu
Tối đa
MIN(A1:A12)
MAX(A1:A12)
SUM(A1:A12)
COUNT(A1:A12)
Minimum
Maximum
Sum
15
Tổng
151
Số lượng mẫu
Count
n= 12
Sx
ta
= 0.993896
Độ chính xác
CONFIDENCE(0,05;Sx;n)
Confidence Level(95.0%)
n
Sx
Sx
Chú ý : Khi mẫu lớn (n ³ 30) ta thay ta
bằng za
trong ñoù: Za = NORMSINV(1- a/2)
n
n
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
9
II. ƯỚC LƯỢNG THAM SỐ
Để ước lượng trung bình đám đông a ta thực hiện các bước sau:
§
§
Nhập dữ liệu mẫu và xử lý mẫu bằng thống kê mô tả (Descriptive Statistics)
Sx Sx
Tính khoảng ước lượng trung bình a theo: x ± za
; x ± ta
n
n
Ví dụ: Khảo sát sức bền chịu lực của mộ loại ống công nghiệp người ta đo 9 ống và thu được
các số liệu sau:
4500 6500 5000 5200 4800 4900 5125 6200 5375
Ví dụ: Tiến hành xem trong một tháng trung bình một sinh viên tiêu hết bao nhiêu tiền gọi
điện thoại. Khảo sát ngẫu nhiên 59 sinh viên thu được kết quả:
14
95
30
29
22
18
16
147
73
36
22
27
72
26
60
30
111
37
15
41
36
37
25
26
35
28
63
7
31
26
42
127
33
57
20
79
23
29
40
58
36
31
35
18
33
52
70
41
85
23
15
27
48
28
35
47
11
15
32
Hãy ước lượng khoảng tin cậy của số tiền gọi điện thoại trung bình hàng tháng của một
sinh viên với độ tin cậy 95%.
Đs
33.96481
48.23858
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
10
III. KIỂM ĐỊNH GIẢ THIẾT
1) So sánh 2 trung bình với phương sai đã biết hay mẫu lớn (n³30)
v
v
Dùng menu: Tools/ Data Analysis… / z-test:Two Sample for Means
x1 - x2
Tiêu chuẩn kiểm định: z=
s12
s22
+
n1
n2
v
v
Phân vị 2 phía za/2 là: z Critical two-tail
Nếu ïzï > za/2 thì bác bỏ H0 , chấp nhận H1
Nếu ïzï £ za/2 thì chấp nhận H0 , bác bỏ H1
Ví dụ: Người ta chọn 2 mẫu, mỗi mẫu 10 máy, từ hai lô (I và II được sản xuất với phương
sai biết trước tương ứng là 1 và 0,98) để khảo sát thời gian hoàn thành công việc
(phút) của chúng:
I
6
8
9
10
6
15
9
7
13 11
II
5
5
4
3
9
9
6
13 17 12
Hỏi khả năng hoàn thành công việc của hai máy có khác nhau hay không? a=0,05
Nhập và xử lý dữ liệu
§
§
§
§
§
Variable 1 Range , Variable 2 Range: địa chỉ tuyệt đối của vùng dữ liệu của I, II
Variable 1 Variance(known), Variable 2 Variance(known): phương sai của I,II
Labels: chọn khi có tên biến ở đầu cột hoặc hàng
Alpha : mức ý nghĩa a
Output options: chọn cách xuất kết quả
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
11
Kết quả:
H0: a1=a2 “Khả năng hoàn thành công việc của 2 máy như nhau”
H1: a1¹a2 “Khả năng hoàn thành công việc của 2 máy khác nhau”
I
II
Mean
9.4
1
8.3
0.98
10
¬ Trung bình mẫu
Known Variance
Observations
¬ phương sai mẫu đã biết
¬ số quan sát (cỡ mẫu)
10
0
Hypothesized Mean Difference
z
2.472066162
0.006716741
1.644853476
0.013433483
1.959962787
¬ Tiêu chuẩn kiểm định
¬ Xác suất 1 phía
¬ phân vị 1 phía
P(Z<=z) one-tail
z Critical one-tail
P(Z<=z) two-tail
z Critical two-tail
¬ Xác suất 2 phía
¬ phân vị 2 phía
Þ ïzï=2.472066162 > za/2=1.959962787 nên bác bỏ H0 , chấp nhận H1
Vậy: “Khả năng hoàn thành công việc của 2 máy khác nhau”
2) So sánh 2 trung bình với dữ liệu từng cặp
v
v
Được dùng khi mẩu bé, phụ thuộc, phương sai 2 mẫu không bằng nhau và mỗi phần tử
khảo sát có 2 chỉ tiêu X (trước), Y (sau) khi thay đổi điều kiện thí nghiệm.
Chọn menu: Tools/Data Analysis…/ t-test:Paired Two Sample for Means
n
n
(X -Y )
(D - D)2
å
å
i
i
i
D
i=1
i=1
v
Tiêu chuẩn kiểm định: t=
, D =
, SD =
n
n -1
SD
n
v
v
Phân vị 2 phía ta/2 là: t Critical two-tail
Nếu ïtï > ta/2 thì bác bỏ H0 , chấp nhận H1
Nếu ïtï £ ta/2 thì chấp nhận H0 , bác bỏ H1
Ví dụ: Để nghiên cứu của một loại thuốc ngủ, người ta cho 10 bệnh nhân uống thuốc. Lần
khác họ cũng cho bệnh nhân uống thuốc nhưng là thuốc giả (thuốc không có tác
dụng). Kết quả thí nghiệm như sau:
Bệnh nhân
Số giờ ngủ có thuốc
Số giờ ngủ với thuốc giả 5,2 7,9
1
2
3
8,2
3,9
4
5
6
7
8
9
10
5,8
6,3
6,1 7,0
7,6 6,5
4,7 5,3
8,4 6,9 6,7 7,4
5,4 4,2 6,1 3,8
Giả sử số giờ ngủ của các bệnh nhân có qui luật chuẩn. Với mức ý nghĩa a=0,05 hãy kết
luận về ảnh hưởng của loại thuốc ngủ trên?
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
12
§ Nhập và xử lý dữ liệu
§ Kết quả
H0: a1=a2 “Thuốc ngủ trên không có tác dụng đến số giờ ngủ”
H1: a1¹a2 “Thuốc ngủ trên có tác dụng đến số giờ ngủ”
t-Test: Paired Two Sample for Means
Số giờ ngủ có thuốc
Số giờ ngủ với thuốc giả
Mean
7.06
0.720444444
10
5.28
1.577333333
10
Variance
Observations
Pearson Correlation
Hypothesized Mean Difference
df
-0.388571913
0
9
t Stat
3.183538302
0.005560693
1.833113856
0.011121385
2.262158887
P(T<=t) one-tail
t Critical one-tail
P(T<=t) two-tail
t Critical two-tail
Þ ïtï= 3,1835 > ta/2= 2,2622 nên chấp nhận H1
Vậy loại thuốc ngủ trên có ảnh hưởng làm tăng số giờ ngủ trung bình.
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
13
3) So sánh 2 trung bình với phương sai bằng nhau
v
v
v
Được dùng khi 2 mẩu bé , độc lập và phương sai 2 mẫu bằng nhau.
Chọn menu:Tools/Data Analysis…/ t-test:Two-Sample Assuming Equal Variances
X1 - X 2
(n1 -1)S12 + (n2 -1)S22
Tiêu chuẩn kiểm định: t=
, S 2p =
S p2
(
+
)
n1 + n2 - 2
1
1
n2
n1
v
v
Phân vị 2 phía ta/2 là: t Critical two-tail
Nếu ïtï > ta/2 thì bác bỏ H0 , chấp nhận H1
Nếu ïtï £ ta/2 thì chấp nhận H0 , bác bỏ H1
Ví dụ: Người ta cho 10 bệnh nhân uống thuốc hạ cholesterol đồng thời cho 10 bệnh nhân
khác uống giả dược, rồi xét nghiệm về nồng độ cholesterol trong máu (g/l)của cả 2
nhóm:
Thuốc
Giả dược
1,10 0,99 1,05 1,01 1,02 1,07 1,10 0,98 1,03 1,12
1,25 1,31 1,28 1,20 1,18 1,22 1,22 1,17 1,19 1,21
Với a=0,05 hãy cho biết thuốc có tác dụng hạ cholesterol trong máu không?
§
Nhập và xử lý dữ liệu
§
Kết quả
H0: a1=a2 “Thuốc và giả dược có tác dụng như nhau”
H1: a1<a2 “Thuốc có tác dụng hạ cholesterol trong máu”
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
14
t-Test: Two-Sample Assuming Equal Variances
Thuốc
1.047
0.002401111 0.002001111
Giả dược
Mean
Variance
1.223
Observations
Pooled Variance
Hypothesized Mean Difference
df
10
0.002201111
0
10
18
t Stat
-8.388352782
6.19807E-08
1.734063062
1.23961E-07
2.100923666
P(T<=t) one-tail
t Critical one-tail
P(T<=t) two-tail
t Critical two-tail
Þ t= -8,3884 < -ta= -1,7341 nên chấp nhận H1
Vậy thuốc trên có tác dụng hạ cholesterol trong máu.
4) So sánh 2 trung bình với phương sai khác nhau
v
v
v
Được dùng khi mẩu bé , độc lập và có phương sai khác nhau (2 mẫu phân biệt)
Chọnmenu:Tools/Data Analysis…/ t-test:Two-Sample Assuming Equal Variances
X1 - X 2
Tiêu chuẩn kiểm định: t=
S12
S22
+
n1
n2
v
v
Phân vị 2 phía ta/2 là: t Critical two-tail
Nếu ïtï > ta/2 thì bác bỏ H0 , chấp nhận H1
Nếu ïtï £ ta/2 thì chấp nhận H0 , bác bỏ H1
Ví dụ: Thời gian tan rã (phút) của một loại viên bao từ 2 xí nghiệp dược phẩm (XNDP) khác
nhau được kiểm nghiệm như sau:
XNDP I
XNDP II
61 71 68 73 71 70 69 74
62 69 65 65 70 71 68 73
Thời gian tan rã của viên bao thuộc hai XNDP có giống nhau không?
Nhập, xử lý dữ liệu và kết quả
§
H0 : a1=a2 “Thời gian tan rã của viên bao 2 XNDP như nhau”
H1 : a1 ¹ a2 “Thời gian tan rã của viên bao 2 XNDP khác nhau”
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
15
XNDP I
69.625
XNDP II
67.875
Mean
Variance
15.98214286
8
13.26785714
8
Observations
Hypothesized Mean Difference
df
0
14
t Stat
0.915208631
0.187788433
1.76130925
0.375576865
2.144788596
P(T<=t) one-tail
t Critical one-tail
P(T<=t) two-tail
t Critical two-tail
Þ ïtï=0,9152 £ 2,1448 nên chấp nhận H0
Vậy thời gian tan rã của viên bao thuộc 2 XNDP như nhau.
5) So sánh 2 tỉ số
v
Đối với thí nghiệm có 2 kết quả, để so sánh 2 tỉ số của 2 kết quả đó, ta dùng
(nij - npi )2
r
c
toång haøng x toång coät
kiểm định c2 (chi-quared) : c2=
, npi =
åå
npi
n
i=1 j=1
nij: tần số thực nghiệm, npij: tần số lý thuyết của ô (i,j) ; r : số hàng ; c : số cột
v
v
Dùng hàm CHITEST( actual_range , expected_range).
Tính giá trị: P(X>c2) =CHITEST
Nếu P(X>c2) > a thì chấp nhận H0 và ngược lại.
Ví dụ: Kết quả điều trị trên 2 nhóm bệnh nhân: một nhóm dùng thuốc và một nhóm dùng
giả dược được tóm tắt như sau:
Điều trị
Thuốc
Giả dược
Số khỏi bệnh
Số không khỏi bệnh
24
20
15
23
Tỉ lệ khỏi bệnh do thuốc và do giả dược có khác nhau không?
§ Nhập và xử lý dữ liệu
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
16
§ Kết quả
Þ P(X>c2)= 0,17295 > a = 0,05 , nên chấp nhận H0
Vậy tỷ lệ khỏi bệnh do thuốc và do giả dược không khác nhau.
6. So sánh 2 phương sai
v
So sánh 2 phương sai được áp dụng để so sánh độ chính xác của 2 phương pháp
định lượng khác nhau.
v
v
Chọn menu:Tools/Data Analysis…/F-Test Two-Samplefor Variances
2
Tính tiêu chuẩn kiểm định F= S
1
2
2
S
2
2
v
Nếu F < Fa thì chấp nhận H0: s = s và ngược lại.
1 2
Ví dụ: Một được phân tích bởi hai phương pháp A và B với kết quả sau:
A
6,4 5,2 4,8 5,2 4,3 4,4 5,1 5,8
B
2,6 3,5 3,4 3,2 3,4 2,8 2,9 2,8
Cho biết phương pháp nào chính xác hơn?
§ Nhập và xử lý dữ liệu
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
17
§ Kết quả
H0: s A2 = sB2 “Hai phương pháp có độ chính xác như nhau”
H1: s A2 > s B2 “Độ chính xác của phương pháp B cao hơn”
F-Test Two-Sample for Variances
A
B
Mean
5.15
3.075
Variance
0.485714286 0.116428571
Observations
8
7
8
7
df
F
4.171779141
0.039514317
3.787050673
P(F<=f) one-tail
F Critical one-tail
Þ F= 4,1718 > 3,7870 nên chấp nhận H1
Vậy phương pháp B chính xác hơn phương pháp A.
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
18
IV. PHÂN TÍCH PHƯƠNG SAI (ANOVA)
1. Phân tích phương sai 1 nhân tố
Giả sử nhân tố A có k mức X1, X2 , … , Xk với Xj có phân phối chuẩn N(a,s2) có mẫu điều tra
X1
x11
x21
:
X2
x12
x22
:
---
Xk
x1k
x2k
:
:
:
:
…
:
x
x
nk k
n11
x
n2 2
Với mức ý nghĩa a , hãy kiểm định giả thiết :
H0 : a1 = a2 = … = ak
H1 : “Tồn tại j1¹j2 sao cho aj1≠aj2
“
·
Đặt:
k
§
Tổng số quan sát: n =
n
j
å
j=1
nj
nj
Tj
nj
1
§
§
§
Trung bình mẫu nhóm j ( j =1, .. , k ): x j =
x =
với T =
x
ij
å
å
ij
j
nj
i=1
i=1
nj
ni
k
k
k
1
T
n
Trung bình mẫu chung: x =
x =
v ới T =
x =
T
j
n åå
åå
å
ij
ij
j=1 i=1
j=1 i=1
j=1
nj
1
Phương sai hiệu chỉnh nhóm j: S 2j =
(x - x )2
ij j
å
nj -1 i=1
nj
k
§
§
SST =
SSA =
(x - x )2 Tổng bình phương các độ lệch.
åå
ij
j=1 i=1
k
n (x - x)2 Tổng bình phương độ lệch riêng của các nhóm so với x
å
j
j
j=1
Tj2
nj
T 2
n
T 2
n
k
k
SST =
x2 -
SSA=
-
SSE =SST - SSA
åå
å n
j=1
j
ij
j=1 i=1
SSA
SSE
MSA=
MSE =
k -1
n - k
MSA
MSE
·
·
Nếu H0 đúng thì F =
có phân phối Fisher bậc tự do k-1; n-k
Miền Ba : F > Fk-1; n-k ; 1-a
Bảng ANOVA
Nguồn sai số
Tổng bình
phương SS
Bậc tự do Bình phương trung bình Giá trị thống kê
df
MS
F
Yếu t ố
(Between Group)
SSA
MSA
SSA
k-1
MSA =
F =
k -1
SSE
MSE
Sai số
(Within Group)
SSE = SST - SSA
SST
n-k
n-1
MSE =
n - k
Tổng cộng
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
19
Ví dụ:
Hàm lượng Alcaloid (mg) trong một loại dược liệu được thu hái từ 3 vùng khác nhau
được số liệu sau:
Vùng 1 : 7,5
Vùng 2 : 5,8
Vùng 3 : 6,1
6,8
5,6
6,3
7,1
6,1
6,5
7,5
6,0
6,4
6,8
5,7
6,5
6,6
7,8
6,3
Hỏi hàm lượng Alcaloid có khác nhau theo vùng hay không?
Dùng Excel
1. Nhập dữ liệu theo cột
2. Chọn mục : Anova: Single Factor
3. Chọn các mục như hình:
Giải toán XSTK bằng EXCEL
(ĐaTaDa – ĐHNL 10/10/2009)
20
Tải về để xem bản đầy đủ
38 trang
23160
Free
Bạn đang xem 20 trang mẫu của tài liệu "Giải toán Xác suất thống kê bằng Excel", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- giai_toan_xac_suat_thong_ke_bang_excel.pdf
