BỘ GIÁO DỤC VÀ ĐÀO TẠO
TRƯỜNG ĐẠI HỌC VINH
……………………………

MAI THỊ HOA

ỨNG DỤNG PHẦN MỀM MICROSOFT EXCEL
ĐỂ GIẢI BÀI TOÁN THỐNG KÊ

KHÓA LUẬN CỬ NHÂN KHOA HỌC
NGÀNH TOÁN – TIN ỨNG DỤNG

VINH 2011


BỘ GIÁO DỤC VÀ ĐÀO TẠO
TRƯỜNG ĐẠI HỌC VINH
……………………………

MAI THỊ HOA

ỨNG DỤNG PHẦN MỀM MICROSOFT EXCEL
ĐỂ GIẢI BÀI TOÁN THỐNG KÊ

NGƯỜI HƯỚNG DẪN KHOA HỌC
Th S. NGUYỄN THỊ THANH HIỀN

VINH 2011


MỤC LỤC

hiện được điều đó là ta đã đi tìm lời giải cho bài toán thống kê. Thống kê là thu
thập dữ liệu, nghiên cứu định lượng, tóm tắt thông tin nhằm hỗ trợ tìm hiểu về
một vấn đề hay một đối tượng nào đó, đưa ra những kết luận dựa trên những số
liệu, ước lượng hiện tại hoặc dự báo tương lai. Việc chọn lựa phương pháp để
giải các bài toán thống kê sao cho các dự báo gần đúng nhất là một việc hết sức
quan trọng. Trong đó phương pháp hồi quy tương quan đã được Francis Galton
sử dụng vào năm 1886. Trong một bài báo nổi tiếng của mình, ông đã cho rằng
một xu hướng về chiều cao của những đứa trẻ do cha mẹ cao không bình thường
hoặc thấp không bình thường sinh ra, người ta gọi xu hướng này là luật Galton.
Trong bài báo của mình Galton dùng cụm từ “regression to mediocrity” qui về
trung bình. Từ đó, vấn đề hồi quy được nhiều người quan tâm và hoàn thiện, các
ứng dụng của phân tích hồi quy đã có nội dung rộng hơn nhiều.
Phân tích hồi quy nghiên cứu mối phụ thuộc của một biến (gọi là biến phụ
thuộc hay biến được giải thích) với một hay nhiều biến khác (được gọi là (các)
biến độc lập hay biến giải thích có giá trị đã biết) nhằm ước lượng và dự báo
trung bình của biến phụ thuộc với các giá trị đã biết của (các) biến độc lập.
Để giải quyết các bài toán thống kê một cách nhanh chóng, thuận lợi cho
những đối tượng quan tâm đến nó, trong phần mềm ứng dụng Microsoft Office
Excel có sử dụng các hàm và sử dụng thêm công cụ phân tích dữ liệu Data
Analysis. Data Analysis là một chức năng tính toán trong Microsoft Office
Excel, là công cụ hữu hiệu trong việc giải các bài toán thống kê. Data Analysis
là sự kết hợp của một số lượng rất nhiều các chương trình. Các chương trình
chức năng của nó bao gồm giao diện người dùng đồ họa, một số ngôn ngữ mô
hình đại số. Data Analysis thực sự là một công cụ mạnh mẽ có thể giải quyết tốt , nhanh


5
chóng các bài toán thống kê. Để hiểu biết rõ hơn về phương pháp cũng như cách thức
giải quyết một bài toán thống kê trên phần mềm Microsoft Office Excel, chúng
tôi lựa chọn đề tài luận văn Ứng dụng phần mềm Microsoft Office Excel để

Xét đồng thời biến phụ thuộc y với nhiều biến độc lập x1, x2, …, xk. Giả sử ta
có bộ số liệu có kích thước n (yi, xi1, …, xik), i = 1, n . Kí hiệu X là ma trận số
liệu của các biến x1, x2 , …, xk
1

1
X= 


1

x11 

x1 j



x21 

x2 j



 
xn1 

 
xnj 

x1k 

7
và ε i được gọi là các nhiễu trắng (ký hiệu δ ii = 0 nếu i ≠ j và =1 nếu i = j). Giả
thiết (i) cho thấy ε i có dạng sai số ngẫu nhiên , còn (ii) yêu cầu chúng ta tạo dãy
không tương quan.
Các giá trị quan sát của các biến có thể bố trí theo dạng cột hoặc dạng hàng.
1.1.2.2. Ước lượng tham số hồi quy
Sử dụng phương pháp bình phương cực tiểu
n

2
Min Q(m0, m1, …, mk) = Min ∑ ( yi − m0 − m1 xi1 − ... − mk xik ) .
i =1

Dưới dạng ma trận, ta có thể viết hàm mục tiêu
Q(m) = (y-Xm)t (y-Xm) = yty – mtXty – ytXm + mtXtXm

(2)

(dấu t chỉ phép chuyển vị). Như vậy, nếu kí hiệu û là véc tơ các ước lượng của
m0, m1, m2, . . ., mk. Lấy đạo hàm (2) theo véc tơ m và cho bằng 0 ta được :
-2Xty + 2XtXm = 0.

(3)

û = (XtX)-1Xty.

(4)

Từ đó
Giả sử ma trận XtX không suy biến, với k =2 việc tính m0, m1, …, mk đưa về

+ Hệ số xác định r2 thuộc [0, 1] nếu r2=1 thì có quan hệ hoàn hảo trong mẫu,
nếu r2 = 0 thì phương trình hồi quy không có tác dụng dự đoán y, sai số của giá
trị y sey.
+ Hệ số F là thống kê F, dùng để xác định liệu giữa biến phụ thuộc và các
biến độc lập có quan hệ với nhau hay đó chỉ là thể hiện của tác động ngẫu nhiên.
Hệ số df là bậc tự do, dùng để xác định mức tin cậy của mô hình hồi quy.
+ Các hệ số ssreg (regression sum of square) tổng bình phương giá trị hồi quy
và ssresid (residual sum of square ) tổng bình phương độ lệch.
+ Hàm TREND dùng để tính giá trị y theo hàm ước lượng (1) với các bộ giá
trị cho trước ( x1, x2, . . ., xk ), các bộ giá trị này có thể là các quan sát cũ hoặc các
dự báo mới. Cú pháp hàm:
=TREND( know_y’s, know_x’s, new_x’s, const).
+ Trong đó know_y’s, know_x’s là vùng chỉ chứa giá trị tương ứng đã biết
của y, x, new_x’s là giá trị mới của x. Const là hằng số có giá trị logic. Ngầm
định nếu const =1 (True) thì tính toán hệ số tự do m0, nếu const =0 (False) thì
m0 = 0.
1.1.3. Hồi quy tuyến tính đơn
Khi hai biến x và y phụ thuộc, ta quan tâm đầu tiên đến quan hệ hàm y = f(x).
Nếu hàm f tùy ý, đây là quan hệ rất phức tạp. Trong phần này ta giới hạn f có
dạng tuyến tính
y = mx + b,

(5)

trong đó m, b là các hằng số thực cần xác định, x là biến độc lập (để đơn giản ta
tất định hóa biến X và chuyển cách viết thành x), y là biến phụ thuộc và là biến


9
ngẫu nhiên thể hiện của nó yi là đáp ứng đối với giá trị xi. Ta vẫn có bộ mẫu cặp

i
i
i
Ta sẽ có

m=

∑ ( xi − X )( yi − Y )
i

∑ ( xi − X ) 2

.

i

b = Y − mX .
Ví dụ. Kết quả nghiên cứu thực nghiệm trên 8 người đàn ông như sau :
Trọng lượng (kg) 58

70

74

63,5

62

70,5


∑ xi = 535; ∑ yi = 24,02;
∑ xi2 = 35983,5; ∑ yi2

X = 66,875; Y = 3,0025.

= 72,798;

∑ xi yi = 1615,295.

Từ đó

∑ ( xi − X )( yi − Y ) = 1615,295 – 535.24,02/8 = 8,96;
∑ ( xi − X ) 2

= 35983,5 – 5352/8 = 205,38.

∑ ( yi − Y ) 2 = 72,798 – 20,022/8

= 0,678.

và
m=

8,96
= 0,043615.
205,38

b = 3,0025 – 0,043615.66,875 = 0,0857.
Vậy sự phụ thuộc của lượng huyết tương vào trọng lượng cơ thể được mô tả bằng
y = 0,0436x + 0,0857.


(7)

Nếu chỉ có một biến độc lập phương trình sẽ là y= b*mx .
Hàm sử dụng :
Hàm LOGEST dùng để ước lượng các hệ số của phương trình (3), nó làm
việc giống như hàm LINEST (các đối số và mảng kết quả hoàn toàn giống nhau).
Cú pháp :
=LOGEST( known_y’s, known_x’s, const, stats ).
Trong đó, known_y’s, known_x’s là các giá trị hoặc vùng địa chỉ chứa giá trị
đã biết của y, x tương ứng. Const là hằng số. Nếu const =1 (True) tính hệ số tự
do, nếu const = 0 (False) bỏ qua hệ số b. Stats có giá trị logic nếu không in các
thống kê hồi quy, nhập True hoặc bỏ trống thì hàm sẽ in các thống kê hồi quy
giống hàm LINEST.
+ Hàm GROWTH dùng để tính các giá trị y theo (3) với các bộ giá trị (x1, x2, ..., xn)
cho trước làm việc hoàn toàn giống hàm TREND. Cú pháp :
=GROWTH( known_y’s, known_x’s, new_x’s, const ).
Trong đó, known_y’s, known_x’s, new_x’s, là các giá trị hoặc vùng địa chỉ đã
biết của y, x tương ứng và giá trị mới của x.


12
Const là hằng số. Nếu const =1 (True) tính hệ số tự do b, nếu const = 0
(False) bỏ qua hệ số b.


13

Chương 2
GIẢI CÁC BÀI TOÁN THỐNG KÊ TRÊN



15

Hãy tính hàm hồi quy tuyến tính bội dự báo lợi nhuận của doanh nghiệp và
ước lượng giá trị lợi nhuận của doanh nghiệp khi giá thành x 1 = 0,6 (triệu đồng),
chi phí quản lý x2 = 35(triệu đồng), chi phí bán hàng x3 =25(triệu đồng).
Các bước thực hiện để giải bài toán :
Bước 1: Nhập dữ liệu bài toán vào bảng tính dưới dạng sau:

Hình 2.4. Tổ chức bài toán trên bảng tính.
Bước 2: Đánh dấu khối vùng từ C15: F19 để đưa kết quả ra bảng tính bằng
cách nhập công thức:
=LINEST(B5:B13,C5:E13,1,1).


16
Sau đó, kết thúc bằng tổ hợp phím Ctrl + Shift + Enter.
Hàm LINEST dùng để tính hệ số của hàm hồi quy tuyến tính bội.Ta được kết
quả ở bảng sau:

Hình 2.5. Xuất kết quả.
Sử dụng hàm TREND để ước lượng giá trị lợi nhuận y.
Bước 3: Tính lợi nhuận y tại ô B14 bằng công thức:
=TREND(B5:B13,C5:E13,C14:E14,1).
Sau đó, kết thúc bằng tổ hợp phím Ctrl + Shift + Enter:


17



Các bước thực hiện giải bài toán:
Buớc 1: Nhập dữ liệu vào bảng tính ở dạng sau:

22
60

24
68

26
74

32
80


18

Hình 2.7. Tổ chức bài toán trên bảng tính.
Bước 2: Tính hệ số m của phương trình (5) tại ô B15 bằng công thức:
=SLOPE(C3:C12,B3:B12).
Tính hệ số tự do b của phương trình (2) tại ô C15 bằng công thức:
=INTERCEPT(C3:C12,B3:B12).
Sau đó, kết thúc bằng tổ hợp phím Ctrl + Shift +Enter ta được kết quả ở
bảng sau:


19


13
69000

14
10200
0

Hãy ước lượng hàm hồi quy mũ.

15
15000
0

16
220000


21
Các bước thực hiện để giải bài toán:
Bước 1: Nhập dữ liệu vào bảng tính như hình 2.10.

Hình 2.10. Tổ chức bài toán trên bảng tính.
Bước 2: Đánh dấu khối vùng từ C10:D14 để đưa kết quả ra bảng tính bằng
cách nhập công thức:
=LOGEST(C3:C8,B3:B8,1,1).
Sau đó kết thúc bằng tổ hợp phím Ctrl + Shift + Enter ta được kết quả ở
bảng sau:


22

hàm hồi quy tuyến tính b=0.
Độ tin cậy của hồi quy (mặc định là 95%) bằng 1-

Confidence Level

a với a là mức ý nghĩa hay xác suất mắc sai lầm
loại một bác bỏ H0 trong khi H0 đúng.


24
• Các lựa chọn kết xuất kết quả Output Option.
Output Range

Vùng hoặc ô phía trên bên trái của vùng chứa kết quả.

New Worksheet Ply
New Workbook
Residuals
Standardarlized Residuals
Residual plots
Line fit plots
Normal Probability Plots

In kết quả ra một sheet khác.
In kết quả ra một file Excel.
Sai số do ngẫu nhiên.
Chuẩn hóa sai số
Đồ thị sai số
Đồ thị hàm hồi quy tuyến tính
Đồ thị xác suất phân phối chuẩn

259,600
28,093
702,600
3,851
63,100
256,800
31,121
714,000
4,170
66,300
259,300
32,759
717,630
4,378
62,900
263,400
34,556
750,000
5,000
66,700
273,100
36,788
Trong đó, Y là thu nhập quốc dân, X 1 là sản lượng điện. X2 là sản lượng than,
X3 là sản lượng lương thực, X 4 là sản lượng thép. Sử dụng lệnh Tool / Data
Anylysis.
Các bước thực hiện để giải bài toán :
Bước 1 : Nhập dữ liệu bài toán vào bảng tính dưới dạng sau :


25