Excel's Statistical Functions - Các Hàm Thống kê

BNTT · 14/3/08

Hàm Phân phối xác suất

Hàm NEGBINOMDIST()

Trả về xác suất của phân phối nhị thức âm, là 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. Hàm này làm việc giống phân phối nhị phân, trừ một điều là số lần thành công là cố định, và số phép thử có thể thay đổi; các phép thử được giả định là độc lập nhau.
Ví dụ, bạn cần tìm 10 người có phản xạ khéo léo, và bạn biết xác suất mà một ứng cử viên có khả năng này là 0.3. NEGBINOMDIST() sẽ tính xác suất mà bạn sẽ gặp được một số chắc chắn các ứng cử viên không đạt yêu cầu, trước khi tìm được 10 ứng cử viên đạt yêu cầu.

Cú pháp: = NEGBINOMDIST(number_f, number_s, probability_s)

Number_f : Số lần thất bại.

Number_s : Số ngưỡng thành công.

Probability_s : Xác suất của một lần thành công.

Lưu ý:

Nếu number_f và number_s không nguyên, chúng sẽ được cắt bỏ phần thập phân để trở thành số nguyên.

Nếu có bất kỳ đối số nào không phải là số, NEGBINOMDIST() trả về giá trị lỗi #NUM!

Nếu probability_s < 0 hay probability_s > 1, NEGBINOMDIST() trả về giá trị lỗi #NUM!

Nếu number_f < 0 hay number_s < 1, NEGBINOMDIST() trả về giá trị lỗi #NUM!

Phương trình của phân phối nhị thức âm là:

Trong đó: x = number_f, r = number_s và p = probability_s.

Ví dụ:

Tính xác suất của một phân phối nhị thức âm, biết số lần thất bại là 10, số ngưỡng thành công là 5 và xác suất cho một lần thành công là 0.25 ?

NEGBINOMDIST(10, 5, 0.25) = 0.55049

BNTT · 14/3/08

Hàm Phân phối xác suất

Hàm TDIST()

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. Phân phối Student được dùng trong việc kiểm tra giả thuyết của các tập hợp dữ liệu mẫu có số lượng nhỏ. Hàm này được dùng thay cho bảng các trị tiêu chuẩn của phân phối Student.

Cú pháp: = TDIST(x, degrees_freedom, tails)

x : Giá trị dùng để tính phân phối.

Degrees_freedom : Bậc tự do, là một số nguyên.

Tails : Là 1 hoặc 2, cho biết phần dư của phân phối được trả về như thế nào. Nếu tails = 1, TDIST() trả về phân phối một phía; nếu tails = 2, TDIST() trả về phân phối hai phía.

Lưu ý:

Nếu có bất kỳ đối số nào không phải là số, TDIST() trả về giá trị lỗi #VALUE!

Nếu degrees_freedom < 1, TDIST() trả về giá trị lỗi #NUM!

Nếu degrees_freedom và tails không phải là số nguyên, chúng sẽ được cắt bỏ phần thập phân để trở thành số nguyên.

Nếu tails khác 1 hoặc 2, TDIST() trả về giá trị lỗi #NUM!

Nếu Tails = 1, TDIST() = P(X > x); nếu tails = 2, TDIST() = P(|X| > x) = P(X > x) hay = P(X < x); với X là biến ngẫu nhiên phụ thuộc vào phân phối t.

Nếu muốn dùng TDIST() với x < 0, nên biết rằng TDIST(-x,df,1) = 1 – TDIST(x,df,1) = P(X > -x), và TDIST(-x,df,2) = TDIST(x,df,2) = P(|X| > x).

Ví dụ:

Tính xác suất của phân phối Student (t) với giá trị x = 1.96 và số bậc tự do bằng 60 ?

Phân phối một phía:

TDIST(1.96, 60, 1) = 0.027322

Phân phối hai phía:

TDIST(1.96, 60, 2) = 0.054645

BNTT · 14/3/08

Hàm Phân phối xác suất

Hàm TINV()

Trả về giá trị t của phân phối Student.

Cú pháp: = TINV(probability, degrees_freedom)

Probability : Xác suất kết hợp với phân phối Student.

Degrees_freedom : Bậc tự do, là một số nguyên.

Lưu ý:

Nếu có bất kỳ đối số nào không phải là số, TINV() trả về giá trị lỗi #VALUE!

Nếu probablity < 0 hay probablity > 1, TDIST() trả về giá trị lỗi #NUM!

Nếu degrees_freedom < 1, TDIST() trả về giá trị lỗi #NUM!

Nếu degrees_freedom không phải là số nguyên, nó sẽ được cắt bỏ phần thập phân để trở thành số nguyên.

TINV() = P(|X| > t); với X là biến ngẫu nhiên phụ thuộc vào phân phối Student, P(|X| > t) = P(X < -t hoặc X > t).

Một giá trị t một phía có thể được trả về bằng cách thay thế probability bằng 2*probability. Với probability = 0.05 và bậc tự do là 10, giá trị t hai phía được tính là TINV(0.05, 10) = 2.28139; trong khi giá trị t một phía với cùng xác suất và bậc tự do như vậy sẽ là TINV(2*0.05, 10) = 1.812462.

TINV() sử dụng phương pháp lặp để tính hàm. Với probability cho trước, TINV() sẽ lặp cho tới khi TDIST(x, degree_freedom, 2) = probability. Nếu TINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!

Ví dụ:

Tính giá trị t của phân phối Student (t) biết xác suất = 0.054645 và số bậc tự do là 60 ?

TINV(0.054645, 60) = 1.959997462 = 1.96

BNTT · 14/3/08

Hàm Phân phối xác suất

Hàm TTEST()

Trả về xác suất kết hợp với phép thử của phân phối Student. Thường dùng để xác định xem hai mẫu thử co xuất phát từ hai tập hợp có cùng giá trị trung bình hay không.

Cú pháp: = TTEST(array1, aray2, tails, type)

Array1, array2 : Tập hợp số liệu thứ nhất và thứ hai.

Tails : Là 1 hoặc 2, cho biết phần dư của phân phối được sử dụng. Nếu tails = 1, TTEST() sử dụng phân phối một phía; nếu tails = 2, TTEST() sử dụng phân phối hai phía.

Type : Loại phép thử t được thực hiện

= 1 : Phép thử từng cặp

= 2 : Hai mẫu thử cùng phương sai (phương sai có điều kiện không đổi)

= 3 : Hai mẫu thử khác phương sai (phương sai có điều kiện thay đổi)

Lưu ý:

Nếu array1 và array2 không có cùng số phần tử, và type = 1, TTEST() trả về giá trị lỗi #NA!

Nếu tails và type không phải là số nguyên, chúng sẽ được cắt bỏ phần thập phân để trở thành số nguyên.

Nếu tails và type không phải là số, TTEST() trả về giá trị lỗi #VALUE!

Nếu tails khác 1 hoặc 2, TTEST() trả về giá trị lỗi #NUM!

Ví dụ:

Cho hai tập hợp sau:

A = 3, 4, 5, 8, 9, 1, 2, 4, 5
B = 6, 19, 3, 2, 14, 4, 5, 17, 1

Hãy tính xác suất kết hợp với phép thử Student từng cặp, phân phối 2 phía ?

TTEST({3, 4, 5, 8, 9, 1, 2, 4, 5}, {6, 19, 3, 2, 14, 4, 5, 17, 1}, 2, 1) = 0.196016

BNTT · 14/3/08

Hàm Phân phối xác suất

Hàm WEIBULL()

Trả về xác suất của phân phối Weibull. Phân phối này thường được sử dụng trong phân tích độ tin cậy, ví dụ như tính tuổi thọ trung bình của một thiết bị.

Cú pháp: = WEIBULL(x, alpha, beta, cummulative)

x : Giá trị để tính hàm.

Alpha và Beta : Tham số cho phân phối.

Cumulative : Giá trị logic xác định dạng hàm. Nếu cumulative là TRUE (1), WEIBULL() trả về hàm tính phân phối tích lũy của phân phối Weibull; nếu cumulative là FALSE (0), WEIBULL() trả về hàm mật độ xác suất của phân phối Weibull.

Lưu ý:

Nếu x, alpha hay beta không phải là số, WEIBULL() trả về giá trị lỗi #VALUE!

Nếu x < 0, WEIBULL() trả về giá trị lỗi #NUM!

Nếu alpha ≤ 0 hay beta ≤ 0, WEIBULL() trả về giá trị lỗi #NUM!

Phương trình của hàm phân phối tích lũy WEIBULL là:

Phương trình của hàm mật độ xác suất WEIBULL là:

Khi alpha = 1, WEIBULL() trả về xác suất của hàm phân phối mũ, với:

Ví dụ:

Với x = 105 , alpha = 20 và beta = 100, tính hàm phân phối tích lũy Weibull và hàm mật độ xác suất Weibull ?

Hàm phân phối tích lũy Weibull:

WEIBULL(105, 20, 100, 1) = 0.929581

Hàm mật độ xác suất Weibull:

WEIBULL(105, 20, 100, 0) = 0.035589

BNTT · 14/3/08

Hàm ZTEST()

Trả về xác suất một phía của phép thử z. Với một giá trị kỳ vọng cho trước (x), phép thử z trả về xác suất của một phân phối chuẩn mà ở đó trung bình của tập hợp lớn hơn trung bình của những quan trắc trong tập hợp đó.

Cú pháp: = ZTEST(array, x, sigma)

Array : Tập hợp số liệu để kiểm tra giá trị kỳ vọng x.

x : Giá trị kỳ vọng dùng để kiểm tra.

Sigma : Độ lệch chuẩn của tập hợp. Nếu bỏ qua, hàm sẽ dùng độ lệch chuẩn mẫu.

Lưu ý:

Nếu array rỗng, ZTEST() trả về giá trị lỗi #NA!

Khi có sigma, ZTEST() được tính theo công thức sau:

Khi bỏ qua sigma, ZTEST() được tính theo công thức sau:

Với:

ZTEST() trả về xác suất một phía của một phân phối chuẩn mà ở đó trung bình của tập hợp lớn hơn trung bình của những quan trắc trong tập hợp, với giá trị kỳ vọng x. Do tính chất đối xứng của phân phối chuẩn, nếu trung bình của mẫu nhỏ hơn giá trị kỳ vọng x, ZTEST() sẽ trả về một giá trị lớn hơn 0.5

Excel dùng công thức sau đây để tính toán xác suất hai phía, khi số trung bình mẫu cách xa giá trị kỳ vọng x (về cả hai phía):

= 2 * MIN(ZTEST(array, x, sigma), 1 - (ZTEST(array, x, sigma))

Ví dụ:

Với mảng dữ liệu (array) = 3, 6, 7, 8, 6, 5, 4, 2, 1, 9
Dùng ZTEST() để kiểm tra giá trị kỳ vọng x = 4 với độ lệch chuẩn mẫu, ta có các kết quả như sau:

Xác suất một phía tại 4:

= ZTEST({3, 6, 7, 8, 6, 5, 4, 2, 1, 9}, 4) = 0.090574

Xác suất hai phía tại 4:

= 2 * MIN(ZTEST({3, 6, 7, 8, 6, 5, 4, 2, 1, 9}, 4), 1 - ZTEST({3, 6, 7, 8, 6, 5, 4, 2, 1, 9}, 4)) = 0.181148

BNTT · 14/3/08

Hàm Thống kê

Hàm KURT()

Trả về độ nhọn của tập dữ liệu. Độ nhọn được tính bằng cách lấy moment thứ tư của trị trung bình chia cho độ lệch chuẩn lũy thừa 4. Độ nhọn biểu thị mức nhọn hay mức phẳng tương đối của một phân phối so với phân phối chuẩn. Đô nhọn dương cho biết phân phối là nhọn tương đối, độ nhọn âm cho biết phân phối là phẳng tương đối.

Cú pháp: = KURT(number1, number2,...)

Number1, number2, ... : Có thể có từ 1 đến 255 đối số (với Excel 2003 trở về trước thì con số này là 30) dùng cho KURT(). Cũng có thể dùng mảng đơn hay tham chiếu mảng làm đối số cho hàm.

Lưu ý:

Nếu có ít hơn 4 điểm số liệu, hay nếu độ lệch chuẩn của mẫu bằng 0, KURT() trả về giá trị lỗi #DIV/0!

KURT() được tính theo công thức:

với s là độ lệch chuẩn.

Ví dụ:

KURT(3, 4, 5, 2, 3, 4, 5, 6, 4, 7) = -0.1518

BNTT · 15/3/08

Hàm Tương quan & Hồi quy tuyến tính

Hàm LINEST()

Trong phân tích hồi quy, LINEST() 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 được cung cấp, rồi trả về một mảng các giá trị mô tả đường thẳng đó. Do kết quả trả về là một mảng, nên LINEST() thường được nhập với dạng công thức mảng.

LINEST() thường được dùng cho phương pháp hồi quy tuyến tính đơn hoặc hồi quy tuyến tính bội.

Phương trình của đường thẳng trong hồi quy tuyến tính đơn là:

Phương trình của đường thẳng trong hồi quy tuyến tính bội là:

Trong đó, trị phụ thuộc y là hàm của các trị độc lập x, các trị m là các hệ số tương ứng với mỗi giá trị x, và b là hằng số (const). Nhớ rằng y, x, m cũng có thể là các vectơ. Mảng mà LINEST() trả về là:

LINEST() cũng có thể trả về thống kê hồi quy phụ.

Cú pháp: = LINEST(known_y's, known_x's, const, stats)

Known_y's : Một tập hợp các giá trị y đã biết, trong mối quan hệ y = mx + b.

- Nếu mảng known_y's nằm trong một cột, thì mỗi cột của known_x's được hiểu như là một biến độc lập.

- Nếu mảng known_y's nằm trong một dòng, thì mỗi dòng của known_x's được hiểu như là một biến độc lập.

Known_x's : Một tập hợp tùy chọn các giá trị x đã biết, trong mối quan hệ y = mx + b.

- Mảng known_x's có thể bao gồm một hay nhiều biến. Nếu chỉ một biến được sử dụng, known_x's và known_y's có thể có hình dạng bất kỳ, miễn là chúng có kích thước bằng nhau. Nếu có nhiều biến được sử dụng, known_y's phải là một vectơ (là một dãy, với chiều cao là một dòng, hay với độ rộng là một cột)

- Nếu bỏ qua known_x's, known_x's sẽ được giả sử là một mảng {1, 2, 3, ...} với kích thước bằng với known_y's.

Const : Là một giá trị logic cho biết có nên cho hằng số b bằng 0 hay không

- Nếu const là TRUE (1) hoặc bỏ qua, b được tính bình thường.

- Nếu const là FALSE (0), b được gán bằng 0, và các giá trị m sẽ được điều chỉnh để y = mx.

Stats : Là một giá trị logic cho biết có trả về thống kê hồi quy phụ hay không

- Nếu stats là FALSE (0) hoặc bỏ qua, LINEST() chỉ trả về các hệ số m và hằng số b.

- Nếu stats là TRUE (1), LINEST() trả về thống kê hồi quy phụ, và mảng được trả về sẽ có dạng:

Thống kê hồi quy phụ như sau:

Bảng minh họa sau đây cho biết thứ tự thống kê hồi quy phụ trả về:

Lưu ý:

Có thể mô tả đường thẳng bằng hệ số góc m và một điểm cắt b trên trục y:

- Hệ số góc = (y2-y1)/(x2-x1), với (x1,y1) và (x2,y2) là hai điểm trên đường thẳng;

- Điểm cắt b trên trục y là giá trị của y tại điểm mà đường thẳng cắt trục y.
Phương trình của đường thằng là y= mx + b. Một khi đã biết được giá trị m và b, chúng ta có thể tính bất kỳ điểm nào thuộc đường thằng bằng cách thêm giá trị y hay x vào phương trình đó. Bạn cũng có thể sử dụng hàm TREND().

Khi chỉ có một biến độc lập x, có thể tìm hệ số góc m và trị b trên trục y một cách trực tiếp bằng cách dùng các công thức sau đây:

Hệ số góc m: = INDEX(LINEST(known_y's, known_x's), 1)

Điểm cắt b: = INDEX(LINEST(known_y's, known_x's), 2)

Độ chính xác của đường thẳng do LINEST() tính ra còn tùy thuộc vào độ tán xạ trong dữ liệu. Dữ liệu càng tuyến tính, hàm LINEST() mô phỏng đường thẳng càng chính xác. LINEST() dùng phương pháp bình phương tối thiểu để xác định các điểm thích hợp nhất cho dữ liệu. Khi chỉ có một biến độc lập x, những tính toán để tìm m và b dựa vào công thức sau:

Với:

Hàm LINEST() có thể tính được đường thẳng tốt nhất từ dữ liệu được cung cấp; hàm LOGEST() có thể tính được hàm mũ tốt nhất từ dữ liệu được cung cấp. Tuy nhiên chúng ta cần xác định xem trong hai kết quả nhận được, kết quả nào thích hợp với dữ liệu được cung cấp hơn. Có thể tính TREND(known_y's, known_x's) cho đường thẳng và GROWTH(known_y's, known_x's) cho đường hàm mũ. Những hàm này, không có đối số new_x's, trả về một mảng giá trị dự đoán y. Từ đó chúng ta có thể so sánh các trị dự đoán được với các trị thực; có thể vẽ lên biểu đồ hai loại đường này để so sánh trực quan hơn.

Trong phân tích hồi quy, Excel tính cho mỗi điểm một sai phân bình phương giữa trị ước lượng x và trị thực y của điểm đó. Tổng các sai phân này gọi là tổng bình phương thặng dư. Sau đó Excel tính tổng các sai phân bình phương giữa các trị thực y và trung bình các trị y, kết quả này gọi là tổng bình phương toàn phần (= tổng bình phương hồi quy + tổng bình phương thặng dư). So với tổng bình phương toàn phần, nếu tổng bình phương thặng dư càng nhỏ, thì hệ số định trị r2 càng lớn. Đây là cách mà kết quả nhận được từ phân tích hồi quy giải thích mối quan hệ giữa các biến.

Khi nhập hằng mảng cho đối số, như known_y's chẳng hạn, dùng dấu phẩy để phân cách các trị trên cùng một dòng, và dấu chấm phẩy để phân cách các dòng khác nhau. Nhưng cần chú ý là các ký tự phân cách (dấu phẩy và dấu chấm phẩy) còn tùy thuộc vào các thiết lập trong hệ thống bạn đang sử dụng (các thiết lập cho List seperator trong Customize Regional Opitions của Control Panel).

Chú ý rằng các trị y dự đoán được từ phương trình hồi quy có thể không đúng nếu vượt ra ngoài dãy giá trị dùng để xác định hàm.

(xem các ví dụ ở bài sau)

BNTT · 15/3/08

Hàm Tương quan & Hồi quy tuyến tính

Hàm LINEST()

Phần lý thuyết

Ví dụ 1: Hồi quy tuyến tính đơn

Giả sử một doanh nghiệp có các số liệu Doanh thu từ tháng thứ nhất đến tháng thứ sáu như bảng sau:

Dùng hàm LINEST() kết hợp với SUM(), có thể ước lượng doanh thu của tháng thứ chín với công thức sau:

= SUM(LINEST(B2:B7, A2:A7)*{9,1}) = 11,000,000

Nhìn chung, SUM({m, b}*{x, 1}) = mx + b = y : giá trị y được ước lượng từ giá trị x cho trước.

Ví dụ 2: Hồi quy tuyến tính bội

Có một nhà đầu tư đang xem xét mua một số văn phòng cỡ nhỏ, đặt trong một khu thương mại. Nhà đầu tư có thể dùng phân tích hồi quy tuyến tính để ước lượng giá trị của một văn phòng, bằng cách chọn ngẫu nhiên 11 văn phòng trong số 1500 văn phòng của khu thương mại đó, và thu được những dữ liệu sau đây (xem bảng).

Ý đồ của nhà đầu tư là sẽ dựa vào phân tích này để ước lượng giá cho một văn phòng diện tích 2500 m2, có 3 phòng, 2 cửa ra vào và tuổi đời chừng 25 năm (số liệu ở hàng 14)

(ở đây giả sử rằng có một mối quan hệ trực tiếp giữa các biến độc lập x (x1, x2, x3, x4) và biến phụ thuộc y)

Để lấy thống kê hồi quy phụ của bảng tổng hợp trên, ta chọn các ô A15:E19 (5 hàng, còn số cột thì bằng bảng dữ liệu ở trên), và nhập công thức mảng:

{= LINEST(E3:E13, A3

13, 1, 1)}

Tại sao chọn 5 hàng, xin xem lại cách lấy thống kê hồi quy phụ ở phần lý thuyết, khi cho tham số stats = TRUE (hay bằng 1).

Kết quả có được sau khi nhập công thức:

Dựa vào bảng kết quả trả về của thống kê hồi quy phụ (đã nói ở phần lý thuyết):

Xác định được các thông số sau (ở hàng 16):

b = 52,317,831
m1 = 27,641
m2 = 12,529,768
m3 = 2,553,211
m4 = -234,237

Áp dụng công thức

với các giá trị vừa có được, và các giá trị x1, x2, x3, x4 ở hàng 14, ta có:

y = 27,641*2500 + 12,529,768*3 + 2,553,211*2 - -234,237*25 + 52,317,831 = $158,261,096

Hoặc tại ô E14, nhập công thức:

E14 = D16*A14 + C16*B14 + B16*C14 + A16*D14 + E16 = $158,261,096

$158,261,096 chính là giá ước lượng của văn phòng diện tích 2500 m2, có 3 phòng, 2 cửa ra vào và tuổi đời chừng 25 năm, mà nhà đầu tư muốn mua.

BNTT · 15/3/08

Hàm Tương quan & Hồi quy tuyến tính

Hàm INTERCEPT()

Tìm điểm giao 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 hiện có. Trong dự báo hồi quy tuyến tính đơn, đường thằng này gọi là Đường thẳng hồi quy, được vẽ theo các trị x và y đã biết, và giao điểm dựa vào cơ sở trên đường thẳng hồi quy này.

Hàm INTERCEPT() thường được dùng khi muốn xác định một biến phụ thuộc khi biến độc lập bằng zero (0). Ví dụ, dùng để dự đoán điện trở kim loại tại 0 độ C khi các điểm dữ liệu được lấy từ nhiệt độ phòng hay cao hơn.

Cú pháp: = INTERCEPT(known_y's, known_x's)

Known_y's : Tập hợp các dữ liệu phụ thuộc.

Known_x's : Tập hợp các dữ liệu độc lập.

Lưu ý:

Đối số phải là số, tên, mảng, hay tham chiếu đến các ô chứa số.

Nếu các đối số là mảng hay tham chiếu có chứa các giá trị text, logic, hay ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa giá trị zero (0) thì vẫn được tính.

Nếu known_y's, known_x's là rỗng hay chứa số điểm dữ liệu khác nhau, INTERCEPT() trả về giá trị lỗi #NA!

Phương trình giao điểm của đường hồi quy là (trong đó b là hệ số góc, xem hàm SLOPE):

Với:
Giải thuật của hàm INTERCEPT() và hàm SLOPE() thì khác với giải thuật của hàm LINEST(). Sự khác nhau giữa chúng là có thể dẫn đến những kết quả khác nhau đối với những dữ liệu cùng nằm trên một đường thẳng và chưa được xác định. Ví dụ, nếu những điểm dữ liệu của đối số known_y's là 0 và của known_x's là 1:

* INTERCEPT() và SLOPE() sẽ trả về lỗi #DIV/0! bởi vì giải thuật của INTERCEPT() và SLOPE() được thiết kế để tìm ra một và chỉ một đáp án, mà trong trường hợp này thì kết quả trả về có nhiều hơn một đáp án.

* LINEST() trả về kết quả là 0 bởi vì giải thuật của LINEST() được thiết kế để tìm ra tất cả những đáp án đúng với những dữ liệu , mà trong trường hợp này thì kết quả trả về có nhiều hơn một đáp án cho những dữ liệu cùng nằm trên một đường thẳng, và trong trường hợp này thì có ít nhất một đáp án được tìm thấy.

Ví dụ 1:

Với tập hợp known_y's = {2, 3, 9, 1, 8} và known_x's = {6, 5, 11, 7, 5}. Không cần dùng đồ thị, tính tọa độ của điểm mà đường thẳng hồi quy sẽ cắt trục tung (trục y) ?

INTERCEPT({2, 3, 9, 1, 8}, {6, 5, 11, 7, 5}) = 0.04387097

Tọa độ của điểm mà đường thẳng hồi quy sẽ cắt trục tung (trục y) là (0.04387097, 0)

Ví dụ 2: (xem Ví dụ 2 của bài Hàm SLOPE)

BNTT · 15/3/08

Hàm SLOPE()

Tìm hệ số góc của đường thẳng hồi quy bằng cách sử dụng các điểm dữ liệu trong known_y's và known_x's.

Ở bài hàm INTERCEPT(), tôi có viết: phương trình giao điểm của đường thẳng hồi quy là: (trong đó b là hệ số góc):

Với:

Hàm SLOPE() chính là hàm để xác định cái b ở trên.

Cú pháp: = SLOPE(known_y's, known_x's)

Known_y's : Tập hợp các dữ liệu phụ thuộc.

Known_x's : Tập hợp các dữ liệu độc lập.

Lưu ý:

Đối số phải là số, tên, mảng, hay tham chiếu đến các ô chứa số.

Nếu các đối số là mảng hay tham chiếu có chứa các giá trị text, logic, hay ô rỗng, thì các giá trị đó sẽ được bỏ qua; tuy nhiên, ô chứa giá trị zero (0) thì vẫn được tính.

Nếu known_y's, known_x's là rỗng hay chứa số điểm dữ liệu khác nhau, SLOPE() trả về giá trị lỗi #NA!

Giải thuật của hàm SLOPE() và hàm INTERCEPT() thì khác với giải thuật của hàm LINEST(). Sự khác nhau giữa chúng là có thể dẫn đến những kết quả khác nhau đối với những dữ liệu cùng nằm trên một đường thẳng và chưa được xác định. Ví dụ, nếu những điểm dữ liệu của đối số known_y's là 0 và của known_x's là 1:

* SLOPE() và INTERCEPT() sẽ trả về lỗi #DIV/0! bởi vì giải thuật của SLOPE() và INTERCEPT() được thiết kế để tìm ra một và chỉ một đáp án, mà trong trường hợp này thì kết quả trả về có nhiều hơn một đáp án.

* LINEST() trả về kết quả là 0 bởi vì giải thuật của LINEST() được thiết kế để tìm ra tất cả những đáp án đúng với những dữ liệu , mà trong trường hợp này thì kết quả trả về có nhiều hơn một đáp án cho những dữ liệu cùng nằm trên một đường thẳng, và trong trường hợp này thì có ít nhất một đáp án được tìm thấy.

Ví dụ 1:

Với tập hợp known_y's = {2, 3, 9, 1, 8} và known_x's = {6, 5, 11, 7, 5}. Không cần dùng đồ thị, tính hệ số góc của đường thẳng hồi quy ?

SLOPE({2, 3, 9, 1, 8}, {6, 5, 11, 7, 5}) = 0.305555556

Ví dụ 2: Đây là ví dụ đã nói đến ở bài Hàm FORECAST()

Dựa vào bảng phân tích lợi nhuận dựa theo giá thành ở bảng sau. Hãy ước lượng mức lợi nhuận khi giá thành = $270,000 ?

Ta sẽ dùng hàm SLOPE() kết hợp với hàm INTERCEPT() để tính, bằng phương pháp dự báo hồi quy tuyến tính đơn (y = ax + b), với các dữ liệu phụ thuộc là Lợi nhuận, và các dự liệu độc lập là Giá thành:

a = SLOPE(A2:A10, B2:B10) = -0.24021693

b = INTERCEPT(A2:A10, B2:B10) = 353,669.9277

x = 270,000

y = (ax + b) = (-0.24021693)*(270,000) + (353,669.9277) = 288,811 (làm tròn không lấy số lẻ)

Vậy, khi giá thành bằng $270,000 thì mức lợi nhuận (ước lượng) là $288,811

Để ý rằng, kết quả này bằng với kết quả của hàm FORECAST()

BNTT · 15/3/08

Hàm Tương quan & Hồi quy tuyến tính

Hàm TREND()

Trả về các trị theo xu hướng tuyến tính. Làm cho một đường thẳng (dùng phương pháp bình phương tối thiểu) thích hợp với các mảng known_y's và known_x's, và TREND() trả về các giá trị y theo đường thẳng đó.

TREND() là một hàm cho ra kết quả là một mảng, do đó nó phải được nhập ở dạng công thức mảng.

Cú pháp: = TREND(known_y's, known_x's, new_x's, const)

Known_y's : Một tập hợp các giá trị y đã biết, trong mối quan hệ y = mx + b.

- Nếu mảng known_y's nằm trong một cột, thì mỗi cột của known_x's được hiểu như là một biến độc lập.

- Nếu mảng known_y's nằm trong một dòng, thì mỗi dòng của known_x's được hiểu như là một biến độc lập.

Known_x's : Một tập hợp tùy chọn các giá trị x đã biết, trong mối quan hệ y = mx + b.

- Mảng known_x's có thể bao gồm một hay nhiều tập biến. Nếu chỉ một biến được sử dụng, known_x's và known_y's có thể có hình dạng bất kỳ, miễn là chúng có kích thước bằng nhau. Nếu có nhiều biến được sử dụng, known_y's phải là một vectơ (là một dãy, với chiều cao là một dòng, hay với độ rộng là một cột)

- Nếu bỏ qua known_x's, known_x's sẽ được giả sử là một mảng {1, 2, 3, ...} với kích thước bằng với known_y's.

New_x's : Là các giá trị x mới, dùng để TREND() trả về các giá trị y tương ứng.

- New_x's phải gồm một cột (hay một dòng) cho mỗi biến độc lập, giống như known_x's. Vì thế, nếu known_y's nằm trong một cột đơn, thì known_x's và new_x's phải có cùng số lượng các cột; nếu known_y's nằm trên một dòng đơn, thì known_x's và new_x's phải có cùng số lượng các dòng.

- Nếu bỏ qua new_x's, new_x's sẽ được giả sử giả sử là giống như known_x's.

- Nếu bỏ qua cả known_x's và new_x's sẽ được giả sử là mảng {1, 2, 3, ...} với kích thước bằng với known_y's.

Const : Là một giá trị logic cho biết có nên ép hằng số b để nó bằng 0 hay không (trong mối quan hệ y = mx + b).

- Nếu const là TRUE (1) hoặc bỏ qua, b được tính bình thường.

- Nếu const là FALSE (0), b được gán bằng 0, khi đó các giá trị m sẽ được điều chỉnh để y = mx.

Lưu ý:

Có thể dùng TREND() để làm thích hợp các đường cong đa thức bằng việc quy vào biến có nhiều lũy thừa khác nhau. Ví dụ, giả sử cột A chứa các trị y và cột B chứa các trị x. Khi đó có thể nhập x^2 trong cột C, x^3 trong cột D, v.v... và tính hồi quy các cột B, C, D... theo cột A.

Khi nhập hằng mảng cho đối số, như hằng mảng cho known_y's chẳng hạn, dùng dấu phẩy để phân cách các trị trên cùng dòng, và dấu chấm phẩy để phân cách các dòng.

Ví dụ 1:

Đây mà một bảng dữ liệu về lợi tức từ tháng thứ nhất đến tháng thứ mười hai của một đơn vị. Xem hai ví dụ về hàm TREND() như sau:

Để tìm giá trị tương ứng với các giá trị đã có, chọn cả khối cell C2:C13, nhập công thức mảng:

{=TREND(B2:B13, A2:A13)}

Để tìm các giá trị lợi tức dự đoán cho các tháng từ 13 đến 17, chọn khối cell B15:B19, nhập công thức mảng:

{=TREND(B2:B13, A2:A13, A15:A19)}

Ví dụ 2: Đây là ví dụ đã nói đến trong bài Hàm FORECAST()

Dựa vào bảng phân tích lợi nhuận dựa theo giá thành ở bảng sau. Hãy ước lượng mức lợi nhuận khi giá thành = $270,000 ?

Thay vì dùng hàm FORECAST(), ta có thể dùng TREND():

A11 = TREND(A2:A10, B2:B10, B11) = $288,811

Qua ví dụ này, ta thấy rằng, dùng hàm TREND() hay hàm FORECAST() cũng sẽ có kết quả tương đương. Hai hàm này chỉ khác nhau ở chỗ đặt giá trị dùng để dự đoán trong công thức, TREND() thì đặt giá trị này (new_x's) ở cuối, còn FORECAST() thì đặt giá trị này (x) ở đầu.

BNTT · 16/3/08

Hàm Tương quan & Hồi quy tuyến tính

Hàm LOGEST()

Trong phân tính thống kê, LOGEST tính đường cong hàm mũ phù hợp với dữ liệu được cung cấp, rồi trả về một mảng các giá trị mô tả đường cong đó. Do kết quả trả về là một mảng, nên LOGEST() thường được nhập với dạng công thức mảng.

Phương trình của đường cong trong hồi quy tuyến tính đơn là:

Phương trình của đường cong trong hồi quy tuyến tính bội là:

Trong đó, trị phụ thuộc y là hàm của các trị độc lập x, các trị m là các hệ số tương ứng với mỗi giá trị x, và b là hằng số (const). Nhớ rằng y, x, m cũng có thể là các vectơ. Mảng mà LOGEST() trả về là:

Cú pháp: = LOGEST(known_y's, known_x's, const, stats)

Known_y's : Một tập hợp các giá trị y đã biết, trong mối quan hệ y = b*m^x.

- Nếu mảng known_y's nằm trong một cột, thì mỗi cột của known_x's được hiểu như là một biến độc lập.

- Nếu mảng known_y's nằm trong một dòng, thì mỗi dòng của known_x's được hiểu như là một biến độc lập.

Known_x's : Một tập hợp tùy chọn các giá trị x đã biết, trong mối quan hệ y = b*m^x.

- Mảng known_x's có thể bao gồm một hay nhiều biến. Nếu chỉ một biến được sử dụng, known_x's và known_y's có thể có hình dạng bất kỳ, miễn là chúng có kích thước bằng nhau. Nếu có nhiều biến được sử dụng, known_y's phải là một vectơ (là một dãy, với chiều cao là một dòng, hay với độ rộng là một cột)

- Nếu bỏ qua known_x's, known_x's sẽ được giả sử là một mảng {1, 2, 3, ...} với kích thước bằng với known_y's.

Const : Là một giá trị logic cho biết có nên cho hằng số b bằng 1 hay không

- Nếu const là TRUE (1) hoặc bỏ qua, b được tính bình thường.

- Nếu const là FALSE (0), b được gán bằng 0, và các giá trị m sẽ được điều chỉnh để y = m^x.

Stats : Là một giá trị logic cho biết có trả về thống kê hồi quy phụ hay không

- Nếu stats là FALSE (0) hoặc bỏ qua, LOGEST() chỉ trả về các hệ số m và hằng số b.

- Nếu stats là TRUE (1), LOGEST() trả về thống kê hồi quy phụ, và mảng được trả về sẽ có dạng:

Thống kê hồi quy phụ như sau:

Bảng minh họa sau đây cho biết thứ tự thống kê hồi quy phụ trả về:

Lưu ý:

Đồ thị dữ liệu càng giống đường cong hàm mũ, đường tính được càng giống với dữ liệu. Như hàm LINEST(), hàm LOGEST cũng trả về một mảng các giá trị để mô tả mối quan hệ giữa các giá trị đó; sự khác biệt giữa hai hàm này là, LINEST() dùng cho đường thẳng, còn LOGEST() dùng cho đường cong hàm mũ.

Khi chỉ có một biến độc lập x, có thể tìm hệ số góc m và trị b trên trục y (tung độ) một cách trực tiếp bằng cách dùng các công thức sau đây:

Hệ số góc m: = INDEX(LOGEST(known_y's, known_x's), 1)

Điểm cắt (hay tung độ) b: = INDEX(LOGEST(known_y's, known_x's), 2)

Cũng có thể dùng phương trình y = b*m^x để dự đoán giá trị tương lai của y, tuy nhiên Excel đã cung cấp hàm GROWTH() để làm điều này rồi.

Khi nhập hằng mảng cho đối số, như known_y's chẳng hạn, dùng dấu phẩy để phân cách các trị trên cùng một dòng, và dấu chấm phẩy để phân cách các dòng khác nhau. Nhưng cần chú ý là các ký tự phân cách (dấu phẩy và dấu chấm phẩy) còn tùy thuộc vào các thiết lập trong hệ thống bạn đang sử dụng (các thiết lập cho List seperator trong Customize Regional Opitions của Control Panel).

Chú ý rằng các trị y dự đoán được từ phương trình hồi quy có thể không đúng nếu vượt ra ngoài dãy giá trị dùng để xác định hàm.

Các phương pháp kiểm tra phương trình bằng LOGEST() cũng tương tự như các phương pháp dùng cho LINEST(). Tuy nhiên, thống kê mà LOGEST() trả về lại dựa vào mô hình tuyến tính sau:

Nên nhớ điều này khi tính toán các thống kê hồi quy phụ, đặc biệt là các trị sei và seb, vì chúng được so sánh với ln mi và ln b, chứ không phải là so sánh với mi và b.

Ví dụ:

Có một bảng dữ liệu sau. Với số liệu này, dự báo giá trị y khi x1 = 12 và x2 = 25 ?

Ở đây giả sử các đại lượng y, x1 và x2 có mối quan hệ hàm mũ với nhau:

Cách giải:

Chọn khối cell A15:C19, gõ công thức mảng:

= LOGEST(A2:A12, B2:C12, 1, 1)

Ta sẽ có kết quả như hình sau:

Dựa vào bảng minh họa cho biết thứ tự thống kê hồi quy phụ trả về, suy ra được các trị m1, m2 và b như ở các ô E15:F17.

Áp dụng phương trình của đường cong trong hồi quy tuyến tính bội, với x1 = 12 và x2 = 25, bằng công thức tại ô A13:

A13 = F17 * (F16^B13) * (F15^C13) = 279.720291 ≈ 280

Vậy khi x1 = 12 và x2 = 25 thì có thể dự báo được y = 280

BNTT · 16/3/08

Hàm Thống kê

Hàm PERCENTILE()

Tính phân vị thứ k của các giá trị trong một dãy (trả về nhóm tính theo phần trăm của các giá trị trong một dãy).

Cú pháp: = PERCENTILE(array, k)

Array : Là mảng hay dãy dữ liệu định nghĩa vị trí tương đối.

k : Là giá trị phân vị trong khoảng 0 đến 1.

Lưu ý:

Nếu array rỗng hay chứa hơn 8191 điểm dữ liệu, PERCENTILE() sẽ trả về giá trị lỗi #NUM!

Nếu k không phải là số, PERCENTILE() sẽ trả về giá trị lỗi #VALUE!

Nếu k < 0 hay k > 1, PERCENTILE() sẽ trả về giá trị lỗi #NUM!

Nếu k không phải là bội số của 1/(n-1), PERCENTILE() sẽ nội suy để xác định giá trị tại phân vị thứ k.

Ví dụ:

Sau khi thi học kỳ, một lớp học nọ có số điểm bài thi như sau: thấp nhất là 6.5 điểm, và cao nhất là 9.25 điểm. Vậy, để muốn lọt vào top 10 của lớp, thì số điểm tối thiểu phải đạt là bao nhiêu ?

Để lọt vào top 10, nghĩa là nằm trong nhóm 10% đạt điểm cao của lớp, hay nói cách khác, phải có điểm thi lớn hơn hoặc bằng 90% số điểm thi, ta sẽ dùng hàm PERCENTILE với công thức như sau:

= PERCENTILE({6.5, 9.25}, 0.9) = 8.975

Vậy, điểm bài thi phải đạt được 8.975 điểm trở lên thì mới nằm trong top 10 của lớp.
Nói cách khác, điểm số 8.975 nằm trong nhóm "90 Percentile"

Từ ví dụ trên, ta thấy con số X_percentile được hiểu là lớn hơn hay bằng X phần trăm...

Chẳng hạn, nếu nói như vầy:

"...Về mặt kinh tế, với 11 triệu dân, tổng sản lượng quốc gia (GDP) được ước lượng là 342 tỉ mỹ kim, và cho mỗi đầu người, khoảng 31 ngàn mỹ kim. Lợi tức trung bình hàng năm cho mỗi đầu người khoảng 27 ngàn mỹ kim, Hy-lạp đứng hạng 93 percentile của Âu Châu..."

Thì chúng ta sẽ hiểu rằng Hy-lạp được xếp hạng cao hơn (hoặc bằng) 93% tổng số nước của Châu Âu, hay nói cách khác, Hy-lạp nằm trong nhóm 27% quốc gia dẫn đầu châu Âu.

BNTT · 16/3/08

Hàm Thống kê

Hàm PERCENTRANK()

Trả về hạng của một trị trong một tập dữ liệu, là số phần trăm của tập dữ liệu đó, hay nói cách khác là xếp hạng một trị trong một tập dữ liệu theo phần trăm của nó trong tập dữ liệu. Hàm này có thể được dùng để tính vị trí tương đối của một trị trong tập dữ liệu. Ví dụ, tính vị trí của điểm kiểm tra môn toán trong tất cả các điểm kiểm tra.

Đừng nhầm lẫn hàm này với hàm RANK(). Hàm RANK() cũng trả về hạng của một trị trong một tập dữ liệu, nhưng đây là độ lớn của trị này so với các trị khác trong danh sách.

Cú pháp: = PERCENTRANK(array, x, significance)

Array : Là mảng hay dãy dữ liệu định nghĩa vị trí tương đối.

k : Là giá trị muốn xếp hạng theo phần trăm.

Significance : Là một giá trị định nghĩa số ký số có nghĩa (ở phần thập phân) cho số phần trăm được trả về. Nếu bỏ qua, PERCENTRANK() dùng giá trị mặc định là 3 ký số.

Lưu ý:

Nếu array rỗng, PERCENTRANK() sẽ trả về giá trị lỗi #NUM!

Nếu significance < 1, PERCENTRANK() sẽ trả về giá trị lỗi #NUM!

Nếu x không khớp với một trong các trị trong array, PERCENTRANK() sẽ nội suy để trả về hạng đúng của số phần trăm.

Ví dụ:

Có bảng dữ liệu sau đây:

Hạng của số 2 tính theo phần trăm trong dữ liệu trên (là 0.333, bởi vì có 3 giá trị trong dữ liệu nhỏ hơn 2, và có 6 giá trị trong dữ liệu lớn hơn 2: 3/(3+6) = 0.333):

= PERCENTRANK(A2:A11, 2) = 0.333 = 33.3%

Hạng của số 4 tính theo phần trăm trong dữ liệu trên, lấy 2 số lẻ thập phân:

= PERCENTRANK(A2:A11, 4, 2) = 0.55 = 55%

Hạng của số 8 tính theo phần trăm trong dữ liệu trên, lấy 4 số lẻ thập phân:

= PERCENTRANK(A2:A11, 8, 4) = 0.6666 = 66.7%

Hạng của số 5 tính theo phần trăm trong dữ liệu trên (là 0.583, bằng 1/4 khoảng cách giữa PERCENTRANK của 4 và PERCENTRANK của 8):

= PERCENTRANK(A2:A11, 5) = 0.583 = 58.3%

BNTT · 17/3/08

Hàm Thống kê

Hàm RANK()

Trả về thứ hạng của một trị trong một tập dữ liệu, là độ lớn của trị này so với các trị khác trong danh sách. Với một danh sách đã sắp xếp, thứ hạng của một số chính là vị trí của số đó trong danh sách. Hàm này thường được dùng để xếp vị thứ cho học sinh dựa vào bảng điểm trung bình.

Đừng nhầm lẫn hàm này với hàm PERCENTRANK(). Hàm PERCENTRANK()() cũng trả về hạng của một trị trong một tập dữ liệu, nhưng đây là số phần trăm của tập dữ liệu đó, hay nói cách khác là xếp hạng một trị trong một tập dữ liệu theo phần trăm của nó trong tập dữ liệu.

Cú pháp: = RANK(number, ref, order)

Number : Là số muốn tìm thứ hạng của nó.

Ref : Là mảng, là tham chiếu hay là danh sách các số. Các giá trị không phải là số trong ref sẽ được bỏ qua.

Order : Là một trị logic (0 hay 1) cho biết cách thức sắp xếp các số hạng trong ref.

- Nếu order là 0 hoặc bỏ qua, Excel sẽ tính thứ hạng các số như thể danh sách đã được sắp xếp theo thứ tự giảm dần (từ lớn tới nhỏ)

- Nếu order là 1, Excel sẽ tính thứ hạng các số như thể danh sách đã được sắp xếp theo thứ tự tăng dần (từ nhỏ tới lớn)

Lưu ý:

RANK() sắp xếp các số giống nhau với cùng một thứ hạng. Tuy nhiên, sự có mặt của những số giống nhau sẽ làm ảnh hưởng đến hạng của các số theo sau. Ví dụ, trong danh sách các số nguyên, nếu số 10 có hai lần và được xếp hạng 7, thì số 11 sẽ xếp hạng 9 (không có hạng 8).

Muốn RANK() sắp xếp các số giống nhau với nhưng không cùng một thứ hạng (thứ hạng sẽ chạy liên tục không mất số nào), dùng cú pháp sau đây (kết hợp hàm RANK với hàm COUNT và COUNTIF):

- Với danh sách xếp từ lớn đến nhỏ:

= RANK(number, ref) + COUNTIF(ref, number) - 1
-Với danh sách xếp từ nhỏ đến lớn:

= COUNT(ref) - (RANK(number, ref) + COUNTIF(ref, number)) + 2

Ví dụ 1: So sánh một số cách dùng hàm RANK

Công thức ở B3:B12 = RANK(A3, $A$3:$A$12)

Công thức ở C3:C12 = RANK(A3, $A$3:$A$125) + COUNTIF(A3:A$12, A3) - 1

Công thức ở D3

12 = RANK(A3, $A$3:$A$12, 1)

Công thức ở E3:E12 = COUNT($A$3:$A$125) - (RANK(A3, $A$3:$A$12) + COUNTIF(A3:A$12, A3)) + 2

Ví dụ 2: Sắp xếp lại một danh sách theo thứ tự từ thấp đến cao

Công thức ở C3:C12 = COUNT($B$3:$B$125) - (RANK(B3, $B$3:$B$12) + COUNTIF(B3:B$12, B3)) + 2

Công thức ở E3:E12 = OFFSET(A$3, MATCH(SMALL(C$3:C$12, ROW() - ROW(E$3)+1), C$3:C$12, 0) - 1, 0)

Ví dụ 3: Sắp xếp lại một danh sách theo thứ tự từ cao đến thấp

Công thức ở C3:C12 = RANK(B3, $B$3:$B$125) + COUNTIF(B3:B$12, B3) - 1

Công thức ở E3:E12 = OFFSET(A$3, MATCH(SMALL(C$3:C$12, ROW() - ROW(E$3) + 1), C$3:C$12, 0) - 1, 0)

Ví dụ 4: Sắp xếp lại một danh sách theo hai bảng dữ liệu

Công thức ở I5:I14 = VLOOKUP(H5, $B$4:$C$14, 2, 0) + (VLOOKUP(H5, $E$5:$F$14, 2, 0) / 1000) + (ROW() / 1000000)

Công thức ở J5:J14 = RANK(I5, $I$5:$I$14) + COUNTIF($I$5:I5, I5) - 1

Công thức ở L5:L14 = OFFSET(B$5, MATCH(SMALL(J$5:J$14, ROW() - ROW(L$5) + 1), J$5:J$14, 0) - 1, 0)

---------------------------------------------------------------------------

Xem thêm các hàm: COUNT(), COUNTIF(), OFFSET(), MATCH(), SMALL(), ROW(), VLOOKUP()

BNTT · 17/3/08

Hàm Thống kê

Hàm PERMUT()

Trả về số hoán vị có thể có được của một tập hợp các đối tượng. Hoán vị có nghĩa là đảo lộn vị trí các đối tượng. Một hoán vị là một tập con với số lượng các đối tượng trong mỗi tập con này đã được cho trước. Hàm này thường được dùng trong các phép tính xác suất kiểu xổ số.

Cú pháp: = PERMUT(number, number_chosen)

Number : Là tổng số lượng các đối tượng, là một số nguyên.

Number_chosen : Là số các đối tượng trong mỗi hoán vị, là một số nguyên.

Lưu ý:

Các đối số sẽ bị cắt bỏ phần thập phân (nếu có) để trở thành số nguyên.

Nếu các đối số không phải là số, PERMUT() sẽ trả về giá trị lỗi #VALUE!

Nếu number ≤ 0 hay number_chosen < 0, PERMUT() sẽ trả về giá trị lỗi #NUM!

PERMUT() tính toán theo công thức sau đây:

Ví dụ:

Với 3 chữ số (1, 2 và 3), có thể viết ra được bao nhiêu chữ số có 2 chữ số và 3 con số ?

= PERMUT(3, 2) = 6 (đó là 12, 21, 13, 31, 23 và 32)

= PERMUT(3, 3) = 6 (đó là 123, 132, 213, 231, 312 và 321)

BNTT · 17/3/08

Hàm Thống kê

Hàm QUARTILE()

Tính điểm tứ phân vị của một tập dữ liệu (hay là trả về số tứ phân). Hàm này thường được dùng trong bán hàng và khảo sát dữ liệu để chia các tập hợp thành nhiều nhóm. Ví dụ, có thể dùng QUARTILE() để tìm ra 25% những thu nhập cao nhất.

Cú pháp: = QUARTILE(array, quart)

Array : Là mảng hay dãy ô gồm các trị số để tính điểm tứ phân vị.

Quart : Là một số, cho biết điểm tứ phân vị nào được trả về:

= 0 : Giá trị nhỏ nhất (kết quả trả về tương đương với kết quả của hàm MIN)

= 1 : Điểm tứ phân vị thứ nhất (phân vị thứ 25).

= 2 : Điểm tứ phân vị thứ hai, là điểm trung bình (phân vị thứ 50; kết quả trả về tương đương với kết quả của hàm MEDIAN)

= 3 : Điểm tứ phân vị thứ ba (phân vị thứ 75)

= 4 : Giá trị lớn nhất (kết quả trả về tương đương với kết quả của hàm MAX)

Lưu ý:

Nếu array rỗng, QUARTILE() sẽ trả về giá trị lỗi #NUM!

Nếu quart không nguyên, phần lẻ thập phân sẽ bị cắt bỏ.

Nếu quart < 0 hay quart > 4, QUARTILE() sẽ trả về giá trị lỗi #NUM!

Ví dụ:

Có tập hợp gồm những con số sau: {1, 2, 4, 7, 8, 9, 10, 12}
Điểm nhỏ nhất:

= QUARTILE({1, 2, 4, 7, 8, 9, 10, 12}, 0) = MIN(1, 2, 4, 7, 8, 9, 10, 12) = 1

Điểm tứ phân vị thứ nhất:

= QUARTILE({1, 2, 4, 7, 8, 9, 10, 12}, 1) = 3.5

Điểm tứ phân vị thứ hai:

= QUARTILE({1, 2, 4, 7, 8, 9, 10, 12}, 2) = MEDIAN(1, 2, 4, 7, 8, 9, 10, 12) = 7.5

Điểm tứ phân vị thứ ba:

= QUARTILE({1, 2, 4, 7, 8, 9, 10, 12}, 3) = 9.25

Điểm lớn nhất:

= QUARTILE({1, 2, 4, 7, 8, 9, 10, 12}, 4) = MAX(1, 2, 4, 7, 8, 9, 10, 12) = 12

BNTT · 17/3/08

Hàm Thống kê

Hàm SKEW()

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ó. Độ lệch (hay còn gọi là độ bất đối xứng) được tính bằng cách lấy moment thứ ba của trị trung bình chia cho độ lệch chuẩn lũy thừa 3. Độ lệch dương cho biết phân phối có phần đuôi mở rộng sang hướng các trị dương; độ lệch âm cho biết phân phối có phần đuôi mở rộng sang hướng các trị âm.

Cú pháp: = SKEW(number1, number2, ...)

number1, number2, ... : Có thể có từ 1 tới 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30). Các đối số có thể là số, là tên, là mảng hay tham chiếu đến các giá trị số.

Lưu ý:

Nếu đối số là một mảng hay là một tham chiếu có chứa text, giá trị logic, ô rỗng, các giá trị lỗi, v.v... thì các giá trị đó sẽ được bỏ qua; tuy nhiên, các ô chứa giá trị là zero (0) thì vẫn được tính.

Nếu có ít hơn 3 điểm dữ liệu, hay độ lệch chuẩn là zero (0), SKEW() trả về giá trị lỗi #DIV/0!

Phương trình tính độ lệch như sau:

với s là độ lệch chuẩn.

Ví dụ:

SKEW(3, 4, 5, 2, 3, 4, 5, 6, 4, 7) = 0.359543071

BNTT · 17/3/08

Hàm Thống kê

Hàm TRIMMEAN()

Trả về trung bình phần trong của một tập dữ liệu. Trung bình phần trong là giá trị trung bình của một tập dữ liệu mà đã loại bớt một tỷ lệ phần trăm nào đó các giá trị tại hai biên.

Cú pháp: = TRIMMEAN(array, percent, ...)

Array : Mảng hoặc dãy các giá trị để cắt bỏ bớt một tỷ lệ phần trăm phần biên và tính trung bình.

Percent : Tỷ lệ phần trăm phần biên sẽ bị loại ra khỏi việc tính toán. Ví dụ, nếu percent = 0.2 (20%), sẽ có 2 điểm dữ liệu được loại bỏ khỏi tập số liệu có 10 điểm dữ liệu (một ở đầu và một ở cuối).

Lưu ý:

Nếu percent < 0 hay percent > 1, TRIMMEAN() sẽ trả về giá trị lỗi #NUM!

TRIMMEAN() làm tròn số điểm dữ liệu xuống bội số gần nhất của 2. Nếu percent = 0.1, thì 10% của 30 điểm dữ liệu là 3 điểm, nhưng để cân đối, TRIMMEAN() chỉ loại bỏ một trị ở đầu và một trị ở cuối tập dữ liệu.

Nếu percent = 0, kết quả trả về của TRIMMEAN() giống như kết quả trả về của AVERAGE().

Ví dụ:

Có dữ liệu gồm 20 chữ số như sau:

Tính trung bình sau khi bỏ bớt 20% dữ liệu (= 4 điểm dữ liệu, 2 ở đầu và 2 ở cuối):

= TRIMMEAN(A1:A20, 0.2) = AVERAGE(A3:A18) = 4.875

Excel's Statistical Functions - Các Hàm Thống kê

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường

Bùi Nguyễn Triệu Tường