Excel's Statistical Functions - Các Hàm Thống kê (1 người xem)

[BNTT]

Phần này được viết tặng riêng các bạn đam mê những bộ môn xác suất, toán thống kê, toán ứng dụng, v.v...

Do kiến thức về thống kê của tôi chỉ có hạn, nên sẽ có những bài tôi chỉ giới thiệu cú pháp hàm và một số ví dụ lấy từ phần Help của Excel 2007, chứ không thể mô tả đầy đủ cách thức sử dụng chúng, vì có một số hàm đòi hỏi người sử dụng phải có một khái niệm tối thiểu nào đó về thống kê, thì mới biết sử dụng chúng. Về những loại "phân phối" trong thống kê, nếu tìm được bài viết nào có liên quan đến chúng, tôi sẽ giới thiệu link để các bạn đọc thêm.

Nhân đây cũng xin giới thiệu bài viết "Tạo bảng tra các phương pháp tính xác suất thống kê trong Excel" của bạn ttphong2007.



Danh mục các Hàm Thống kê

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ến Tính


1. 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, ...) : Tính trung bình cộng

AVERAGEA (number1, number2, ...) : 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, ...) : Đếm số ô trong danh sách

COUNTA (value1, value2, ...) : Đếm số ô có chứa giá trị (không rỗng) trong danh sách

COUNTBLANK (range) : Đế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

COUNTIFS (range1, criteria1, range2, criteria2, …) : Đếm số ô thỏa nhiều điều kiện cho trước

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, ...) : Trả về trung bình điều hòa (nghịch đảo của trung bình cộng) của các số

KURT (number1, number2, ...) : 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

MAX (number1, number2, ...) : Trả về giá trị lớn nhất của một tập giá trị

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, ...) : Tính trung bình vị của các số.

MIN (number1, number2, ...) : Trả về giá trị nhỏ nhất của một tập giá trị

MINA (number1, number2, ...) : 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, ...) : Trả về giá trị xuất hiện nhiều nhất trong một mảng giá trị

PERCENTILE (array, k) : 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) : Trả về hoán vị của các đối tượng.

QUARTILE (array, quart) : 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ố

STDEV (number1, number2, ...) : Ước lượng độ lệch chuẩn trên cơ sở mẫu

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, ...) : Tính độ lệch chuẩn theo toàn thể tập hợp

STDEVPA (value1, value2, ...) : 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, ...) : Trả về phương sai dựa trên mẫu

VARA (value1, value2, …) : Trả về phương sai dựa trên mẫu, bao gồm cả các trị logic và text

VARP (number1, number2, ...) : Trả về phương sai dựa trên toàn thể tập hợp

VARPA (value1, value2, …) : 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.​

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

2. Nhóm hàm về Phân Phối Xác Suất ...

3. Nhóm hàm về Tương Quan và Hồi Quy Tuyến Tính ...

 

[BNTT]

Hàm Thống kê


Hàm COUNT() và COUNTA()

Đếm số ô có chứa số lẫn các số bên trong danh sách các đối số.
Thường dùng hàm COUNT() để lấy số mục trong một dãy hoặc trong một mảng các số.

Cú pháp: = COUNT(value1, value2, ...)

value1, value2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30), các đối số có thể chứa hoặc tham chiếu tới nhiều loại dữ liệu khác nhau, nhưng chỉ những đối số có chứa số mới được đếm​

Lưu ý:

• Những đối số là số, ngày tháng, hay các chữ thể hiện số sẽ được đếm; còn những đối số là giá trị lỗi hay các chữ không thể dịch thành số sẽ bị bỏ qua

• Nếu đối số là mảng hoặc tham chiếu, thì chỉ các số trong mảng hoặc tham chiếu đó mới được đếm; còn các ô rỗng, các giá trị logic, text, hay giá trị lỗi trong mảng hay tham chiếu đó sẽ bị bỏ qua

• Nếu cần đếm các giá trị logic, text, hay các giá trị lỗi, bạn sử dụng hàm COUNTA(), với cùng cú pháp. COUNTA() thường được dùng để đếm các ô "không rỗng" bên trong một danh sách.

Ví dụ:

​

 

[BNTT]

Hàm Thống kê


Hàm AVERAGE() và AVERAGEA()

Tính trung bình (trung bình cộng) của các số.

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

number1, number2, ... : Các số dùng để tính trung bình. Tối thiểu phải là 1 và tối đa là 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ần tính trung bình cả các giá trị logic và các giá trị text thể hiện số, bạn sử dụng hàm AVERAGEA(), với cùng cú pháp.

Với Excel 2007, để liếc nhanh qua giá trị trung bình, bạn chỉ cần chọn vùng cần tính, hoặc nếu bạn đang làm việc với một mảng dữ liệu kiểu table thì chỉ cần chọn một cột đơn trong table (single column), Excel sẽ thể hiện những gì nó tính được ở dưới thanh trạng thái (status bar).

Ví dụ:

​

 

[BNTT]

Danh mục các Hàm Thống Kê


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) : Tính khoảng tin cậy cho một kỳ vọng lý thuyết

CRITBINOM (trials, probability_s, alpha) : 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) : Trả về nghịch đảo của phân phối tích lũy gamma.

GAMMLN (x) : 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 đó…)

LOGINV (probability, 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)

LOGNORMDIST (x, mean, standard_dev) : 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) : Tính nghịch đảo phân phối tích lũy chuẩn

NORMSDIST (z) : 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) : Trả về trị chuẩn hóa từ phân phối biểu thị bởi mean và standard_dev

TDIST (x, degrees_freedom, tails) : 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) : Trả về giá trị t của phân phối Student.

TTEST (array1, array2, tails, type) : Tính xác xuất kết hợp với phép thử Student.

WEIBULL (x, alpha, beta, cumulative) : 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.​

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

1. Nhóm hàm về Thống Kê ...

3. Nhóm hàm về Tương Quan và Hồi Quy Tuyến Tính ...

 

[BNTT]

Hàm Thống kê


Hàm MEDIAN()

Dùng để tính trung bình vị của các số.
Trung bình vị là số nằm giữa một tập số, có nghĩa là, 50% tập số có giá trị lớn hơn số trung bình vị, và 50% tập số còn lại nhỏ hơn số trung bình vị.

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

number1, number2, ... : Là một dãy, một mảng, một danh sách các giá trị... Có thể có từ 1 đến 255 giá trị (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• Các đối số phải là số, tên, mảng, hoặc tham chiếu đến các vùng có chứa số. Nếu đối số là mảng hoặc tham chiếu, Excel sẽ kiểm tra tất cả các số có trong đó.

• Nếu đối số là mảng hoặc tham chiếu mà có chứa text, trị logic, ô rỗng... những 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ó một số chẵn các đối số, MEDIAN() sẽ tính trung bình vị hai số nằm ở giữa.

Cần phân biệt sự khác nhau giữa ba hàm tính trung bình:

• AVERAGE tính trung bình cộng của các số, là lấy tổng các số chia cho số lượng các số. Ví dụ, trung bình cộng của 2, 3, 3, 5, 7, 10 là 30 chia cho 6, bằng 5.

• MEDIAN tính trung bình vị của các số, là số nằm ở giữa của các số. Ví dụ, trung bình vị của 2, 3, 3, 5, 7, 10 là số 4.

• MODE tính số lần xuất hiện nhiều nhất của một số trong dãy số. Ví dụ, trong các số 2, 3, 3, 5, 7, 10 thì số 3 là số xuất hiện nhiều nhất.

Ví dụ:

 

[BNTT]

Hàm Thống kê


Hàm MODE()

Trả về giá trị xuất hiện nhiều nhất trong một mảng hoặc trong một dãy dữ liệu.
Giống như MEDIAN, MODE là thước đo vị trí giá trị.

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

number1, number2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30). Có thể dùng mảng đơn hoặc tham chiếu mảng làm đối 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.

• Trong trường hợp có nhiều số có số lần xuất hiện bằng nhau, thì MODE() sẽ lấy trả về số nào xuất hiện trước hết, tính từ number1 (từ trái sang phải).

• Nếu các giá trị trong các đối số không có giá trị nào lập lại, MODE() sẽ báo lỗi #NA!

Ví dụ:

Ví dụ 3:

MODE(1, 2, 3, 2, 3, 4, 5) = 2
MODE(1, 2, 3, 3, 4, 2, 5) = 3

Số 2 và số 3 đều có số lần xuất hiện là 2 lần (nhiều nhất), nhưng kết quả trả về sẽ phụ thuộc vào thứ tự của các đối số. Ở ví dụ trên, số lần xuất hiện (2 lần) của số 2 thì trước số lần xuất hiện của số 3, nên kết quả là 2; còn ở ví dụ dưới thì ngược lại, do đó kết quả là 3.​

​

 

[BNTT]

Tính giá trị trung bình


Phương pháp tính bình quân gia trọng (Calculating the Weighted Mean)

Trong một tập dữ liệu, có thể có một giá trị nào đó thì quan trọng hơn những giá trị khác. Ví dụ, một công ty kinh doanh có nhiều bộ phận, trong đó bộ phận đạt doanh thu cao nhất là 100 triệu một năm (với một tỷ lệ lợi nhuận nào đó, chưa chắc là cao nhất), và bộ phận có doanh thu thấp nhất chỉ là 1 triệu một năm (cũng với một tỷ lệ lợi nhuận nào đó, nhưng chưa chắc là thấp nhất). Nếu muốn tính tỷ lệ lợi nhuận trung bình cho cả công ty, thì sẽ không chính xác nếu ta lấy tổng tỷ lệ lợi nhuận chia cho tổng số bộ phận, vì nó có sự cách biệt quá lớn giữa bộ phận có doanh thu cao nhất và bộ phận có doanh thu thấp nhất. Ta cần phải dùng cách khác để tính.

Xin giới thiệu với bạn phương pháp tính Bình quân gia trọng. Đây cũng là một cách tính trung bình cộng, nhưng dựa trên mức phần trăm thành công (hoặc thất bại) của từng phần tử đối với toàn tập số liệu. Tôi xin lấy một ví dụ cụ thể để minh họa cho phương pháp này.

• Đây là bảng tính mà bạn đã thấy ở các bài trên:

Nó là một bảng thống kê những sản phẩm bị lỗi của các bộ phận.
Hai cột đầu (B và C) là tên của bộ phận và tên người đứng đầu. Cột E là tổng số sản phẩm của mỗi bộ phận làm được, và cột D là số sản phẩm bị lỗi trên tổng số sản phẩm làm ra của từng bộ phận. Cột F là số phần trăm sản phẩm bị lỗi so với số sản phẩm làm ra (= cột D / cột E)

Ví dụ, bộ phận A làm ra được 969 sản phẩm, trong đó có 8 sản phẩm bị lỗi, mức % sản phẩm bị lỗi của bộ phận A = 8/969 = 0.8%

Bây giờ người ta muốn tính trong toàn công ty, tỷ lệ trung bình của các sản phẩm bị lỗi ở các bộ phận là bao nhiêu (tức là tính mức trung bình của các giá trị nơi cột F)

Nếu chỉ đơn giản là dùng AVERAGE cho dãy F3:F22, thì chắc chắn kết quả chúng ta có được không phải là con số chính xác, bởi vì số sản phẩm làm ra được của từng bộ phận có sự cách biệt khá nhiều. Ở bảng tính này, ta thấy số sản phẩm làm được nhiều nhất là của bộ phận C (1,625 sản phẩm) trong khi bộ phận làm được ít sản phẩm nhất chỉ là 689 sản phẩm (bộ phận R).

Vậy ta phải làm như sau:

1. Với từng bộ phận, nhân tỷ lệ sản phẩm bị lỗi với số sản phẩm làm ra được (lấy từng giá trị ở cột F nhân với từng giá trị ở cột E)

2. Cộng tất cả kết quả của bước 1 (cộng tất cả số sản phẩm ở cộ E)

3. Cộng tất cả các sản phẩm làm ra được của tất cả các bộ phận (cộng các giá trị ở cột E)

4. Lấy kết quả ở bước 2 chia cho kết quả ở bước 3
​

Bạn có thể gộp tất cả các bước trên trong một công thức mảng (như công thức tại ô I7)

{=SUM(F3:F22 * E3:E22) / SUM(E3:E22))}​

Ta sẽ có tỷ lệ trung bình số sản phẩm bị lỗi trong toàn công ty là 0.8%
​

• Một ví dụ khác:

Mời bạn nghiên cứu bảng tính sau, là một ví dụ rất cụ thể về cách tính Bình quân gia trọng:

 

[BNTT]

Hàm Thống kê


Hàm MAX()

Trả về giá trị lớn nhất (maximum) của một tập giá trị.

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

number1, number2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• Các đối số có thể là số, ô rỗng, giá trị logic, hoặc các chữ thể hiện số... Nhưng không bao gồm các đối số bị lỗi hoặc chữ không thể chuyển thành số, những đối số này sẽ gây ra lỗi.

• Nếu đối số là mảng hay tham chiếu, thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được sử dụng. Những ô rỗng, giá trị logic, hay text, v.v... sẽ được bỏ qua, nếu muốn sử dụng cả những giá trị này, bạn có thể dùng hàm MAXA() với cú pháp tương đương.

• Nếu không có đối số nào chứa số, MAX() sẽ trả về kết quả là zero (0).

Ví dụ:

 

[BNTT]

Hàm Thống kê


Hàm MIN() và MINA()

Trả về giá trị nhỏ nhất (minimum) của một tập giá trị.

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

number1, number2, ... : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• Các đối số có thể là số, ô rỗng, giá trị logic, hoặc các chữ thể hiện số... Nhưng không bao gồm các đối số bị lỗi hoặc chữ không thể chuyển thành số, những đối số này sẽ gây ra lỗi.

• Nếu đối số là mảng hay tham chiếu, thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được sử dụng. Những ô rỗng, giá trị logic, hay text, v.v... sẽ được bỏ qua, nếu muốn sử dụng cả những giá trị này, bạn có thể dùng hàm MINA() với cú pháp tương đương.

• Nếu không có đối số nào chứa số, MIN() sẽ trả về kết quả là zero (0).

Ví dụ:

 

[BNTT]

Hàm Thống kê


Hàm LARGE()

Trả về giá trị lớn nhất thứ k trong một tập số liệu. Có thể dùng hàm này để chọn lựa giá trị dựa theo vị trí tương đối của nó. Ví dụ, bạn có thể dùng hàm LARGE() để tính số điểm cao nhất, cao thứ nhì, cao thứ ba, v.v...

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

array : Mảng hay dãy số liệu dùng để xác định giá trị lớn nhất thứ k

k : Vị trí (tính từ giá trị lớn nhất) trong mảng hay dãy số liệu.
​

Lưu ý:

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

• Nếu k < 0 hay k lớn hơn số lượng các số có trong array, LARGE() sẽ trả về giá trị lỗi #NUM!

• Giả sử n là số lượng các số có trong array, thì LARGE(array, 1) trả về giá trị lớn nhất (MAX), và LARGE(array, n) sẽ trả về giá trị nhỏ nhất (MIN).

Ví dụ:

 

[BNTT]

Hàm Thống kê


Hàm SMALL()

Trả về giá trị nhỏ nhất thứ k trong một tập số liệu. Có thể dùng hàm này để chọn lựa giá trị dựa theo vị trí tương đối của nó.

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

array : Mảng hay dãy số liệu dùng để xác định giá trị nhỏ nhất thứ k

k : Vị trí (tính từ giá trị nhỏ nhất) trong mảng hay dãy số liệu.
​

Lưu ý:

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

• Nếu k < 0 hay k lớn hơn số lượng các số có trong array, SMALL() sẽ trả về giá trị lỗi #NUM!

• Giả sử n là số lượng các số có trong array, thì SMALL(array, 1) trả về giá trị nhỏ nhất (MIN), và SMALL(array, n) sẽ trả về giá trị lớn nhất (MAX).

Ví dụ:

 

[BNTT]

Tính giá trị lớn nhất và nhỏ nhất


Tính toán với những giá trị lớn nhất hoặc nhỏ nhất

• Giả sử bạn có một danh sách các số, nếu muốn tính tổng của 3 số lớn nhất trong danh sách này, hoặc muốn tính trung bình của 10 số lớn nhất, v.v... bạn có thể kết hợp hàm LARGE() và một hàm khác thích hợp (SUM() chẳng hạn) trong một công thức.

Dạng tổng quát của công thức là:

=FUNCTION(LARGE(range, {1,2,3,...,k}))​

Ở đây, FUNCTION() là một hàm nào đó thích hợp, array là mảng chứa dữ liệu, và k là số lượng các giá trị trong mảng số liệu mà bạn muốn trích ra. Nói cách khác, công thức trên dùng hàm FUNCTION() để tính toán với k giá trị lớn nhất do hàm LARGE() lấy ra từ mảng số liệu array.

Ví dụ, công thức sau đây sẽ tính giá trị trung bình của 5 giá trị lớn nhất trong khối dữ liệu D322 (công thức tại ô I12)

=AVERAGE(LARGE($D$3:$D$22,{1,2,3,4,5}))

• Tương tự, dạng tổng quát của công thức dùng để tính toán với một vài giá trị nhỏ nhất trong một mảng số liệu là:

=FUNCTION(SMALL(range, {1,2,3,...,k}))​

Ví dụ, để tính tổng của 3 giá trị nhỏ nhất trong khối dữ liệu D322, ta dùng công thức sau tại ô I13:

=SUM(SMALL($D$3:$D$22,{1,2,3}))​

 

[BNTT]

Tính toán với sự biến thiên của các giá trị


Tính độ lệch giữa giá trị lớn nhất và giá trị nhỏ nhất

Bài toán đơn giản nhất của việc tính toán sự biến thiên của các giá trị là tính toán độ lệch giữa giá trị lớn nhất và giá trị nhỏ nhất.
Excel không có hàm nào để tính toán độ lệch này một cách trực tiếp. Thay vào đó, ta dùng hàm MAX() và MIN() để tìm ra giá trị lớn nhất và nhỏ nhất, và sau đó tính sự chênh lệch giữa hai giá trị này bằng phép tính trừ.

Ví dụ: Ở ô I14 trong bảng tính sau đây là công thức tính độ lệch giữa giá trị lớn nhất và giá trị nhỏ nhất trong danh sách các sản phẩm bị lỗi:

= MAX($D$3:$D$22) - MIN($D$3:$D$22)

​

 

[BNTT]

Hàm Thống kê


Hàm VAR() và VARA()

Trả về phương sai của một mẫu.

Phương sai, nói nôm na là "trung bình của bình phương khoảng cách của mỗi điểm dữ liệu tới trung bình".
Hay nói cách khác, phương sai là giá trị trung bình của bình phương độ lệch.

Hàm tính phương sai dựa theo một mẫu sẽ trả về kết quả là một con số ước lượng, được tính theo công thức:

Trong đó, n là tổng số các phần tử trong mẫu và X là trung bình cộng của các phần tử trong mẫu.

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

(number1, number2, ...) : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• VAR() giả định rằng các đối số của nó là mẫu của một tập hợp, do đó, nếu dữ liệu là toàn thể tập hợp, cần dùng hàm VARP() hoặc VARPA() để tính phương sai.

• Trong hàm VAR(), các giá trị logic như TRUE, FALSE và các giá trị text được bỏ qua; nếu muốn tính luôn các giá trị này, bạn có thể sử dụng hàm VARA() với cú pháp tương đương.

Ví dụ 1:

VAR(1, 2, 3, 4, 5) = 2.5

Thử tính lại công thức trên theo công thức:

Ta có AVERAGE(1, 2, 3, 4, 5) = (1+2+3+4+5)/5 = 3

Ví dụ 2:

 

[BNTT]

Hàm Thống kê


Hàm VARP() và VARPA()

Trả về phương sai dựa trên toàn thể một tập hợp.

Hàm tính phương sai dựa trên toàn thể một tập hợp sẽ trả về kết quả là một con số ước lượng, được tính theo công thức:

Trong đó, n là tổng số các phần tử trong tập hợp và X là trung bình cộng của các phần tử trong tập hợp. Chúng ta thấy VAR() và VARP() chỉ khác nhau ở chỗ 1/(n-1) và 1/n

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

(number1, number2, ...) : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• VARP() giả định rằng các đối số của nó là toàn thể tập hợp, do đó, nếu dữ liệu chỉ là một số mẫu của tập hợp, ta dùng hàm VAR() hoặc VARA() để tính phương sai.

• Trong hàm VARP(), các giá trị logic như TRUE, FALSE và các giá trị text được bỏ qua; nếu muốn tính luôn các giá trị này, bạn có thể sử dụng hàm VARPA() với cú pháp tương đương.

Ví dụ 1:

VARP(1, 2, 3, 4, 5) = 2

Thử tính lại công thức trên theo công thức:

Ta có AVERAGE(1, 2, 3, 4, 5) = (1+2+3+4+5)/5 = 3

Ví dụ 2:

 

[BNTT]

Hàm Thống kê


Hàm STDEV() và STDEVA()

Ước lượng độ lệch chuẩn dựa trên cơ sở các mẫu thử của một tập hợp.

Độ lệch chuẩn, trong chứng khoán thường được dùng để đo mức độ rủi ro. Ví dụ, một cổ phiếu có tỷ suất lợi nhuận trung bình là 10%, độ lệnh chuẩn là 12%. Theo đó sẽ có 68,2% xác suất để tỷ suất lợi nhuận biến thiên trong khoảng -2% cho đến 22% và có 95,4% xác suất để tỷ suất lợi nhuận nằm trong khoảng -14% cho đến 34%. Như vậy khi độ lệch chuẩn càng cao thì khả năng "lệch" của tỷ suất lợi nhuận càng cao so với tỷ suất lợi nhuận trung bình, tức là cổ phiếu có mức độ rủi ro càng cao.[/B][/I].

Hàm tính độ lệch chuẩn dựa theo một mẫu sẽ trả về kết quả là một con số ước lượng, được tính theo công thức:

​

​

Trong đó, n là tổng số các phần tử trong mẫu và X là trung bình cộng của các phần tử trong mẫu.

​

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

(number1, number2, ...) : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

​

Lưu ý:​

​

• STDEV() giả định rằng các đối số của nó là mẫu của một tập hợp, do đó, nếu dữ liệu là toàn thể tập hợp, cần dùng hàm STDEVP() hoặc STDEVPA() để tính độ lệch chuẩn.

• Trong hàm STDEV(), các giá trị logic như TRUE, FALSE và các giá trị text được bỏ qua; nếu muốn tính luôn các giá trị này, bạn có thể sử dụng hàm STDEVA() với cú pháp tương đương.

​

Ví dụ:​

​

Edit thêm hình bị mất - ptm0412

 

[BNTT]

Hàm Thống kê


Hàm STDEVP() và STDEVPA()

Tính độ lệch chuẩn dựa trên toàn thể một tập hợp.

Hàm tính độ lệch chuẩn dựa trên toàn thể một tập hợp được tính theo công thức:

Trong đó, n là tổng số các phần tử trong tập hợp và X là trung bình cộng của các phần tử trong tập hợp.

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

(number1, number2, ...) : Có thể có từ 1 đến 255 đối số (con số này trong Excel 2003 trở về trước chỉ là 30)​

Lưu ý:

• STDEVP() giả định rằng các đối số của nó là toàn thể tập hợp, do đó, nếu dữ liệu chỉ là một số mẫu của tập hợp, ta dùng hàm STDEV() hoặc STDEVA() để tính độ lệch chuẩn.

• Trong hàm STDEVP(), các giá trị logic như TRUE, FALSE và các giá trị text được bỏ qua; nếu muốn tính luôn các giá trị này, bạn có thể sử dụng hàm STDEVPA() với cú pháp tương đương.

Ví dụ:

 

[BNTT]

Tính toán với sự biến thiên của các giá trị

Bài viết sau đây không có tham vọng bày cho các bạn chơi chứng khoán, chỉ là để miêu tả rõ hơn về "độ lệch chuẩn" và cách sử dụng hàm STDEVP().

Cách tính giá đóng cửa điều chỉnh của các cổ phiếu (Thạc sĩ Lâm Minh Chánh)

Trong chứng khoán, giá đóng cửa điều chỉnh đóng vai trò hết sức quan trọng trong việc phân tích tỷ suất lợi nhuận của cổ phiếu. Nếu sử dụng giá đóng cửa chưa điều chỉnh, tức là giá được đăng trên bảng niêm yết giá tại sàn chứng khoán, hoặc được cung cấp bởi các công ty chứng khoán, chúng ta đã bỏ qua lợi nhuận mà nhà đầu tư thu được từ cổ tức và việc tách/thưởng cổ phiếu, vốn đóng vai trò rất quan trọng. Khi đó, việc phân tích hiệu quả đầu tư, cũng như việc so sánh giữa các cổ phiếu với nhau, việc thành lập danh mục đầu tư, hay ngay cả việc phân tích kỹ thuật, sẽ mất tính chính xác. Trong bài này, Thạc sĩ Lâm Minh Chánh sẽ trình bày tầm quan trọng và cách tính giá đóng cửa điều chỉnh của các cổ phiếu.

Cổ phiếu chúng ta lấy ra minh họa là một cổ phiếu ABC nào đó, có bảng giá đóng cửa chưa điều chỉnh trong 16 kỳ như bên dưới. Dùng 16 kỳ giá đóng cửa chưa điều chỉnh này, chúng ta tính ra kết quả tỷ suất lợi nhuận, độ lệch chuẩn của cổ phiếu như sau (xem Bảng 1):

Bảng 1: Giá đóng cửa chưa điều chỉnh của cổ phiếu ABC​

Việc tính toán như trên - vốn chỉ dựa vào giá đóng cửa chưa điều chỉnh - thật sự không chính xác và không thể hiện được tỷ suất lợi nhuận và độ lệch chuẩn, cũng như xu hướng giá của cổ phiếu ABC vì đã bỏ qua lợi nhuận mà nhà đầu tư nhận được việc chia cổ tức và tách thưởng cổ phiếu.

Cổ phiếu ABC có lịch chia cổ tức và cổ phiếu thưởng như sau: cuối kỳ 3, cổ tức tiền mặt 5.000; cuối đợt 7, thưởng bằng cổ phiếu: tách 1 cổ phiếu thành 2, cuối đợt 13, cổ tức tiền mặt 4.000; cuối đợt 14, thưởng bằng cổ phiếu; 2 cổ phiếu tặng 1 cổ phiếu (tức 2 thành 3).

Dựa vào những dữ liệu đó, chúng ta sẽ xác định giá đóng cửa điều chỉnh của ABC theo 2 bước như sau:

Bước 1: Tính tỷ suất lợi nhuận thật sự của cổ phiếu ABC theo từng kỳ

Thể hiện tất cả những hệ số này vào cột G (hệ số tách/thưởng cổ phiếu), cột J (cổ tức) và sử dụng những công thức thể hiện bằng chữ màu xanh trong các tiêu đề, chúng ta sẽ tìm ra được tỷ suất lợi nhuận chính xác theo từng kỳ, ở cột M. Từ đó sẽ tính được tỷ suất lợi nhuận trung bình và độ lệch chuẩn trong các ô M22 và M23 theo như bảng sau (xem bảng 2):

Bảng 2: Tính tỷ suất lợi nhuận thật của cổ phiếu ABC​

Rõ ràng tỷ lệ lãi suất thực tính theo giá điều chỉnh đóng cửa (13,10%) cao hơn nhiều so với tỷ lệ lãi suất chỉ tính theo giá đóng cửa chưa điều chỉnh (2,46%). Độ lệch chuẩn đo mức độ rủi ro tính theo giá điều chỉnh (8,96%) cũng thấp hơn so với độ lệch chuẩn khi tính theo giá đóng cửa chưa điều chỉnh (16,78%).

Như vậy chúng ta đã tính được tỷ lệ lãi suất thật sự từng kỳ của cổ phiếu ABC. Việc còn lại là chúng ta phải thể hiện giá đóng cửa điều chỉnh như thế nào? Chúng ta không thể dùng giá tại cột L để biểu diễn giá của cổ phiếu ABC. Tại kỳ 16, giá cổ phiếu này là 19.000 chứ đâu phải 57.000.
Chúng ta sẽ tính ra giá đóng cửa điều chỉnh của cổ phiếu ABC trong vòng 16 kỳ theo cách tính ngược như sau:

Bước 2: Tính giá đóng cửa điều chỉnh của cổ phiếu ABC

Trước hết, cho giá đóng cửa điều chỉnh (ĐCĐC) cuối kỳ 16 bằng với giá đóng cửa chưa điều chỉnh cuối kỳ 16. Trên Excel, cho R18=P18. Chúng ta biết tỷ suất lợi nhuận kỳ 16 được xác định bằng công thức:

Tỷ suất lợi nhuận kỳ 16 = (Giá ĐCĐC kỳ 16 – Giá ĐCĐC kỳ 15)/Giá ĐCĐC kỳ 15*100%​

Từ công thức đó ta suy ra:

Giá đóng cửa kỳ 15 = Giá ĐCĐC kỳ 16 *(1+tỷ suất lợi nhuận kỳ 16)​

Áp dụng công thức này, chúng ta sẽ tính được giá đóng cửa điều chỉnh của các kỳ trước đó theo bảng sau (xem bảng 3):

Bảng 3: Giá đóng cửa điều chỉnh của cổ phiếu ABC​

Như vậy chúng ta đã có giá đóng cửa điều chỉnh trong 16 kỳ.
Minh hoạ dưới nay sẽ giúp chúng ta một lần nữa nhận rõ sự khác nhau của giá đóng cửa điều chỉnh và chưa điều chỉnh:

Giả sử chúng ta có 1.000.000 và đầu tư vào cổ phiếu ABC trong đủ 16 kỳ. Với giá đóng cửa chưa điều chỉnh, chúng ta chỉ nhận được 1.117.647 sau 16 kỳ, trong khi đó với giá đóng cửa điều chỉnh, số tiền nhận được là 6.035.800 và đây mới là con số chính xác thu được từ khoản đầu tư này (xem bảng 4).

Bảng 4: Kết quả đầu tư theo 2 cách tính giá​

Trước khi kết thúc, xin lưu ý các bạn ba điểm: Thứ nhất, có một vài phương cách tính giá điều chỉnh khác, tuy vậy chúng cho kết quả tương tự. Thứ hai, giá đóng cửa điều chỉnh của một cổ phiếu sẽ thay đổi khi có sự kiện chia cổ tức hay tách/thưởng cổ phiếu. Tuy vậy, tỷ suất lãi suất của từng kỳ là không thay đổi và giá điều chỉnh cuối kỳ bao giờ cũng bằng với giá đóng cửa cuối kỳ chưa điều chỉnh. Thứ ba, trong khi chờ đợi một tổ chức tại Việt Nam cung cấp giá này, từng cá nhân chúng ta có thể tính giá đóng cửa điều chỉnh để sử dụng. Điều quan trọng cần phải để ý là chúng ta phải chọn điểm xuất phát. Tốt nhất là từ ngày đầu giao dịch của cổ phiếu. Nếu không có đủ số liệu trong quá khứ, thì có thể sử dụng một ngày nào đó gần hơn, chẳng hạn 2/1/2007. Điều cần ghi nhớ là nên chọn một điểm xuất phát giống nhau cho các cổ phiếu mà chúng ta định phân tích hay thành lập danh mục đầu tư...

Lâm Minh Chánh​

​

 

[BNTT]

Hàm Thống kê



Hàm FREQUENCY()

Dùng để 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ị, và trả về một mảng đứng các số. Trong giáo dục, FREQUENCY() thường được dùng để đếm số điểm thi nằm trong một dãy điểm nào đó, hoặc dùng để đếm (phân loại) học lực của học sinh dựa vào điểm trung bình, v.v...
FREQUENCY() 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: = FREQUENCY(data_array, bins_array)

data_array : Mảng hay tham chiếu của một tập hợp các giá trị dùng để đếm số lần xuất hiện. Nếu data_array không có giá trị, FREQUENCY() trả về một mảng các trị zero (0).​

bins_array : Mảng hay tham chiếu chứa các khoảng giá trị làm mẫu, và các trị trong data_array sẽ được nhóm lại theo các trị mẫu này. Nếu bins_array không có giá trị, FREQUENCY() sẽ trả về số phần tử trong data_array.​

Lưu ý:

• FREQUENCY() phải được nhập dưới dạng công thức mảng sau khi chúng ta đã chọn một dãy ô kề nhau để phân bổ sự xuất hiện của các phần tử trong mảng.

• Số phần tử trong data_array phải nhiều hơn số phần tử trong bins_array 1 phần tử. Phần tử dôi ra này chứa số lượng các giá trị lớn hơn khoảng lớn nhất. Ví dụ, khi đếm 3 khoảng giá trị đã nhập trong 3 ô, phải chắc chắn rằng FREQUENCY() được nhập vào 4 ô; ô thứ 4 này sẽ trả về số lượng các giá trị trong data_array khi các gía trị này lớn hơn giá trị trong khoảng thứ 3. Để dễ hiểu hơn, các bạn xem ở các ví dụ sau.

• FREQUENCY() sẽ bỏ qua các ô trống hoặc các chuỗi text.

Ví dụ:

 

[BNTT]

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


Hàm NORMDIST()

NORMDIST (= Normal Distribution) trả về phân phối chuẩn. Hàm này có ứng dụng rất rộng trong thống kê, bao gồm cả việc kiểm tra giả thuyết.

Cú pháp: = NORMDIST(x, mean, standard_dev, cumulative)

x : Giá trị để tính phân phối​

mean : Giá trị trung bình cộng của phân phối​

standard_dev : Độ lệch chuẩn của phân phối​

cumulative : Giá trị logic xác định dạng hàm.

• Nếu cumulative là TRUE, NORMDIST() trả về hàm tính phân phối tích lũy của phân phối chuẩn:
  
  

• Nếu cumulative là FALSE, NORMDIST() trả về hàm mật độ xác suất của phân phối chuẩn:
  
  

Lưu ý:

• Nếu mean và standard_dev không phải là số, NORMDIST() sẽ báo lỗi #VALUE!

• Nếu standard_dev nhỏ hơn hoặc bằng 0, NORMDIST() sẽ báo lỗi #NUM!

• Nếu mean = 0 và standard_dev = 1, cumulative = TRUE, NORMDIST() sẽ trả về phân phối tích lũy chuẩn tắc (standard normal distribution) - Xem hàm NORMSDIST()

Ví dụ:

* Bài đọc thêm: Phân phối chuẩn (theo Wikipedia)

 

[BNTT]

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


Hàm NORMINV()

Trả về nghịch đảo của phân phối tích lũy chuẩn.

Cú pháp: = NORMINV(probability, mean, standard_dev)

probability : Xác suất ứng với phân phối chuẩn​

mean : Giá trị trung bình cộng của phân phối​

standard_dev : Độ lệch chuẩn của phân phối​

Lưu ý:

• Nếu có bất kỳ đối số nào không phải là số, NORMINV() sẽ báo lỗi #VALUE!

• Nếu probability nhỏ hơn 0 hoặc lớn hơn 1, NORMINV() sẽ báo lỗi #NUM!

• Nếu standard_dev nhỏ hơn hoặc bằng 0, NORMDINV() sẽ báo lỗi #NUM!

• Nếu mean = 0 và standard_dev = 1, NORMINV() sẽ dùng phân bố chuẩn.

• NORMINV() sử dụng phương pháp lặp đi lặp lại để tính hàm. Nếu NORMINV() không hội tụ sau 100 lần lặp, hàm sẽ báo lỗi #NA!

Ví dụ:

 

[BNTT]

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


Hàm NORMSDIST()

Trả về hàm phân phối tích lũy chuẩn tắc của phân phối chuẩn, là hàm phân phối tích lũy có giá trị trung bình cộng bằng 0 và độ lệch chuẩn là 1:

Cú pháp: = NORMSDIST(z)

z : Giá trị để tính phân phối​

Lưu ý:

• Nếu z không phải là số, NORSMDIST() sẽ báo lỗi #VALUE!

Ví dụ:

NORMSDIST(1.333333) = 0.908789 (phân phối tích lũy chuẩn tại 1.333333)​

 

[BNTT]

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


Hàm NORMSINV()

Trả về nghịch đảo của hàm phân phối tích lũy chuẩn tắc.

Cú pháp: = NORMSINV(probability)

probability : Xác suất ứng với phân phối chuẩn tắc.​

Lưu ý:

• Nếu probability không phải là số, NORMSINV() sẽ báo lỗi #VALUE!

• Nếu probability nhỏ hơn 0 hoặc lớn hơn 1, NORMSINV() sẽ báo lỗi #NUM!

• NORMSINV() sử dụng phương pháp lặp đi lặp lại để tính hàm. Nếu NORMSINV() không hội tụ sau 100 lần lặp, hàm sẽ báo lỗi #NA!

Ví dụ:

NORMSINV(0.908789) = 1.3333 (nghịch đảo của phân phối tích lũy chuẩn tắc với xác suất là 0.908789)​

 

[BNTT]

Hàm Thống kê


Hàm AVEDEV()

Trả về sai số tuyệt đối trung bình của các điểm dữ liệu.

Để xác định được giá trị cần đo x theo một trị số trung bình, thường ta sẽ dùng một phép thử được thực hiện n lần, nhằm mục đích khắc phục những sai số ngẫu nhiên. Trong Excel, chúng ta dùng hàm AVERAGE() để tính trị số trung bình này, dựa theo công thức:

Mặc dù các sai số ngẫu nhiên của n lần thử có thể ngẫu nhiên bù trừ lẫn nhau và ta đã có được một giá trị lý tưởng x, nhưng về nguyên tắc, ta phải chấp nhận ước lượng sai số theo kiểu tối đa (sai số tuyệt đối trung bình), hay còn gọi là độ ngờ của kết quả, theo công thức:

Trong Excel, chúng ta dùng hàm AVEDEV() để tính công thức này.

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

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

Lưu ý:

• Đối số phải là số hoặc là tên, mảng, hoặc tham chiếu có chứa số.

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

• AVEDEV() luôn chịu ảnh hưởng bởi đơn vị đo lường của dữ liệu.

Ví dụ:

AVEDEV(4, 5, 6, 7, 5, 4, 3) = 1.020408 (sai số tuyệt đối trung bình của các đối số trong công thức)​

 

[BNTT]

Hàm Thống kê

Hàm AVERAGEIF()

Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn một điều kiện cho trước.

Cú pháp: = AVERAGEIF(range, criteria, average_range)

range : Là một hoặc nhiều ô cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...​

criteria : Là điều kiện dưới dạng một số, một biểu thức, địa chỉ ô hoặc chuỗi, để qui định việc tính trung bình cho những ô nào...​

average_range : Là tập hợp các ô thật sự được tính trung bình. Nếu bỏ trống thì Excel dùng range để tính.​

Lưu ý:

• Các ô trong range nếu có chứa những giá trị luận lý (TRUE hoặc FALSE) thì sẽ được bỏ qua.

• Những ô rỗng trong average_range cũng sẽ được bỏ qua.

• Nếu range rỗng hoặc có chứa dữ liệu text, AVERAGEIF sẽ báo lỗi #DIV/0!

• Nếu có một ô nào trong criteria rỗng, AVERAGEIF sẽ xem như nó bằng 0.

• Nếu không có ô nào trong range thỏa mãn điều kiệu của criteria, AVERAGEIF sẽ báo lỗi #DIV/0!

• Bạn có thể các ký tự đại diện như ?, * trong criteria (dấu ? thay cho một ký tự nào đó, và dấu * thay cho một chuỗi nào đó). Khi điều kiện trong criteria là chính các dấu ? hoặc *, thì bạn gõ thêm dấu ~ trước nó.

• average_range không nhất thiết phải có cùng kích thước với range, mà các ô thực sự được tính trung bình sẽ dùng ô trên cùng bên trái của average_range làm ô bắt đầu, và bao gồm thêm những ô tương ứng với kích thước của range. Xem ví dụ sau:

Ví dụ 1:

Ví dụ 2:

​

 

[BNTT]

Hàm Thống kê

Hàm AVERAGEIFS()

Trả về trung bình cộng (số học) của tất cả các ô được chọn thỏa mãn nhiều điều kiện cho trước.

Cú pháp: = AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

average_range : Vùng cần tính trung bình, có thể bao gồm các con số, các tên vùng, các mảng hoặc các tham chiếu đến các giá trị...​

criteria_range1, criteria_range2, ... : Vùng chứa những điều kiện để tính trung bình. Có thể khai báo từ 1 đến 127 vùng.​

criteria1, criteria2, ... : Là các điều kiện để tính trung bình. Có thể khai báo từ 1 đến 127 điều kiện, dưới dạng số, biểu thức, tham chiếu hoặc chuỗi...​

Lưu ý:

• Nếu average_range rỗng hoặc có chứa dữ liệu text, AVERAGEIFS sẽ báo lỗi #DIV/0!

• Nếu có một ô nào trong những vùng criteria_range rỗng, AVERAGEIFS sẽ xem như nó bằng 0.

• Những giá trị logic: TRUE sẽ được xem là 1, và FALSE sẽ được xem là 0.

• Mỗi ô trong average_range chỉ được tính trung bình nếu thỏa tất cả điều kiện quy định cho ô đó

• Không giống như AVERAGEIF(), mỗi vùng criteria_range phải có cùng kích thước với average_range

• Nếu có một ô nào trong average_range không thể chuyển đổi sang dạng số, hoặc nếu không có ô nào thỏa tất cả các điều kiện, AVERAGEIFS sẽ báo lỗi #DIV/0!

• Có thể các ký tự đại diện như ?, * cho các điều kiện (dấu ? thay cho một ký tự nào đó, và dấu * thay cho một chuỗi nào đó). Khi điều kiện trong criteria là chính các dấu ? hoặc *, thì bạn gõ thêm dấu ~ trước nó.

Ví dụ 1:

Ví dụ 2:

 

[BNTT]

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


Hàm BETADIST()

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.
Thông thường hàm này được dùng để nghiên cứu sự biến thiên về phần trăm các mẫu, ví dụ như khoảng thời gian mà người ta dùng để xem TV trong một ngày chẳng hạn.

Cú pháp: = BETADIST(x, alpha, beta, A, B)

x : Giá trị giữa A và B, dùng để tính mật độ hàm.

alpha & beta : Tham số của phân phối.

A : Cận dưới của khoảng x, mặc định là 0.

B : Cận trên của khoảng x, mặc định là 1.​

Lưu ý:

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

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

• Nếu x < A, x > B hay A = B, BETADIST() trả về giá trị lỗi #NUM!

• Nếu bỏ qua A và B, nghĩa là mặc định A = 0 và B = 1, BETADIST() sẽ sử dụng phân phối tích lũy beta chuẩn hóa.

Ví dụ:

BETADIST(2, 8, 10, 1, 3) = 0.6854706​

 

[BNTT]

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


Hàm BETAINV()

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.
Nghĩa là nếu xác suất = BETADIST(x, ...) thì x = BETAINV(xác suất, ...)
Thường dùng trong việc lên kế hoạch dự án, để mô phỏng số lần mở rộng xác suất, biết trước thời gian bổ sung kỳ vọng và độ biến đổi.

Cú pháp: = BETAINV(probability, alpha, beta, A, B)

Probability : Xác suất của biến cố x trong phân phối xác suất tích lũy beta.

alpha & beta : Tham số của phân phối.

A : Cận dưới của khoảng x, mặc định là 0.

B : Cận trên của khoảng x, mặc định là 1.​

Lưu ý:

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

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

• Nếu probability ≤ 0 hay probability > 1, BETAINV() trả về giá trị lỗi #NUM!

• Nếu bỏ qua A và B, nghĩa là mặc định A = 0 và B = 1, BETAINV() sẽ sử dụng phân phối tích lũy beta chuẩn hóa.

• BETAINV() sử dụng phương pháp lặp khi tính mật độ phân phối. Với probability cho trước, BETAINV() lặp cho tới khi kết quả chính xác trong khoảng ±0.0000003. Nếu BETAINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!

Ví dụ:

BETAINV(0.6854706, 8, 10, 1, 3) = 2​

 

[BNTT]

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


Hàm BINOMDIST()

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.
BINOMDIST() thường được dùng trong các bài toán có số lượng cố định các phép thử, khi kết quả của các phép thử chỉ là thành công hay thất bại, khi các phép thử là độc lập, và khi xác xuất thành công là không đổi qua các cuộc thử nghiệm.
Ví dụ, có thể dùng BINOMDIST() để tính xác suất khoảng hai phần ba đứa trẻ được sinh ra là bé trai.

Cú pháp: = BINOMDIST(number_s, trials, probability_s, cumulative)

Number_s : Số lần thử thành công trong các phép thử.

Trials : Số lần thử.

Probability_s : Xác suất thành công của mỗi phép thử.

Cumulative : Một giá trị logic để xác định hàm tính xác suất.

= 1 (TRUE) : BINOMDIST() trả về hàm tính xác suất tích lũy, là xác suất có số lần thành công number_s lớn nhất.
= 0 (FALSE) : BINOMDIST() trả về hàm tính xác suất điểm (hay là hàm khối lượng xác suất), là xác suất mà số lần thành công là number_s.​

Lưu ý:

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

• Nếu number_s, trials hay probability_s không phải là số, BINOMDIST() trả về giá trị lỗi #VALUE!

• Nếu number_s < 0 hay number_s > trials, BINOMDIST() trả về giá trị lỗi #NUM!

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

Ví dụ:

BINOMDIST(6, 10, 0.5, 0) = 0.2050781

BINOMDIST(6, 10, 0.5, 1) = 0.828125​

 

[BNTT]

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


Hàm CHIDIST()

Trả về xác xuất một phía của phân phối chi-squared.
Phân phối chi-squared kết hợp với phép thử chi-squared dùng để so sánh các giá trị quan sát với các giá trị kỳ vọng.
Ví dụ, một thí nghiệm về di truyền có thể giả thiết rằng thế hệ kế tiếp của các cây trồng sẽ thừa hưởng một tập hợp các màu sắc nào đó; bằng cách so sánh các giá trị quan sát được với các giá trị kỳ vọng, có thể thấy được giả thiết ban đầu là đúng hay sai.

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

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

degrees_freedom : Số bậc tự do.​

Lưu ý:

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

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

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

• Nếu degrees_freedom < 1 hay degrees_freedom > 10^10, CHIDIST() trả về giá trị lỗi #NUM!

• CHIDIST() được tính toán theo công thức: CHIDIST = P(X > x), với X là biến ngẫu nhiên chi-squared.

Ví dụ:

CHIDIST(18.307, 10) = 0.050001​

 

[BNTT]

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


Hàm CHIINV()

Trả về nghịch đảo của xác xuất một phía của phân phối chi-squared.
Nghĩa là nếu xác suất = CHIDIST(x, ...) thì x = CHIINV(xác suất, ...)

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

probability : Xác suất một phía của phân phối chi-squared.

degrees_freedom : Số bậc tự do.​

Lưu ý:

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

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

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

• Nếu degrees_freedom < 1 hay degrees_freedom > 10^10, CHIINV() trả về giá trị lỗi #NUM!

• CHIINV() sử dụng phương pháp lặp khi tính mật độ phân phối. Với probability cho trước, CHIINV() lặp cho tới khi kết quả chính xác trong khoảng ±0.0000003. Nếu CHIINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!

Ví dụ:

CHIINV(0.05, 10) = 18.307​

 

[BNTT]

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


Hàm CHITEST()

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.
Có thể dùng các phép thử chi-squared để xác định xem kết quả giả định có được kiểm chứng hay không trong một thí nghiệm.

Cú pháp: = CHITEST(actual_range, expected_range)

Actual_range : Dãy dữ liệu chứa các giá trị để đối chiếu với các giá trị kỳ vọng.

Expected_range : Dãy giá trị chứa tỷ lệ gồm một tích số (của tổng các dòng và tổng các cột) đối với tổng thành phần.​

Lưu ý:

• Nếu actual_range và expected_range có số điểm dữ liệu khác nhau, CHITEST() trả về giá trị lỗi #NA!

Ví dụ:

Đây là bản thăm dò ý kiến về một vấn đề với 2 bậc tự do (Men và Women), trong đó bao gồm các giá trị kỳ vọng và các giá trị thực tế:

Giá trị của xác xuất từ phân phối chi-squared của các số liệu trên là:

CHITEST(C57,C24) = 0.000308​

 

[BNTT]

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


Hàm CONFIDENCE()

Trả về khoảng tin cậy cho một kỳ vọng lý thuyết. Khoảng tin cậy là một dãy nằm ở một trong hai phía của trung bình mẫu.
Ví dụ, nếu đặt mua hàng qua mạng, dùng CONFIDENCE bạn có thể ước lượng thời hạn sớm nhất hoặc trễ nhất bạn nhận được hàng.

Cú pháp: = CONFIDENCE(alpha, standard_dev, size)

Alpha : Mức độ có nghĩa để tính mức độ tin cậy. Mức độ tin cậy sẽ bằng 100x(1-alpha)%; ví dụ, alpha = 0.05 cho biết có 95% mức độ tin cậy.

Standard_dev : Độ lệch chuẩn, được xem như là đã biết trước.

Size : Số lượng mẫu thử, hay kích thước mẫu thử.​

Lưu ý:

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

• Nếu alpha ≤ 0 hay alpha ≥ 1, CONFIDENCE() trả về giá trị lỗi #NUM!

• Nếu standard_dev ≤ 0, CONFIDENCE() trả về giá trị lỗi #NUM!

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

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

Ví dụ:

Giả sử chúng ta quan sát thời gian đi làm của 50 nhân viên, thấy rằng trung bình họ đi từ nhà đến nơi làm mất hết 30 phút, biết độ lệch chuẩn là 2.5, và có 95% độ tin cậy, hãy tính độ kỳ vọng lý thuyết của khoảng thời gian từ nhà đến nơi làm ?

CONFIDENCE(0.05, 2.5, 50) = 0.692952​

Nghĩa là độ kỳ vọng lý thuyết của khoảng thời gian từ nhà đến nơi làm sẽ bằng 30 ± 0.692952, tức là trong khoảng từ 29.3 đến 30.7 phút.​

 

[BNTT]

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


Hàm CORREL()

Trả về hệ số tương quan của hai mảng array1 và array2.
Thường được dùng để xác định mối quan hệ của hai đặc tính. Ví dụ, bạn có thể khảo sát mối quan hệ giữa nhiệt độ trung bình của một nơi với việc sử dụng các máy điều hòa nhiệt độ.
Hệ số tương quan chỉ ra mối quan hệ tuyến tính giữa hai mảng. Hệ số tương quan dương (> 0) có nghĩa là hai mảng sẽ đồng biến; hệ số tương quan âm (< 0) có nghĩa là hai mảng sẽ nghịch biến.

Cú pháp: = CORREL(array1, array2)

Array1, array2 : Các mảng dữ liệu để tính hệ số tương quan.​

Lưu ý:

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

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

• Nếu array1 và array2 có số lượng các điểm dữ liệu không bằng nhau, CORREL() sẽ trả về giá trị lỗi #NA!

• Nếu array1 hoặc array2 là rỗng, hoặc nếu độ lệch chuẩn có giá trị bằng 0, CORREL() sẽ trả về giá trị lỗi #DIV/0!

• CORREL() tính toán theo công thức sau:
  
  

Ví dụ:

Tính hệ số tương quan giữa hai mảng dữ liệu sau:
(A1:A5) = {3, 2, 4, 5, 6}
(B1:B5) = {9, 7, 12, 15, 17}

CORREL(A1:A5, B1:B5) = 0.997054​

 

[BNTT]

Hàm Thống kê


Hàm COUNTBLANK()

Đếm số ô rỗng trong một vùng.

Cú pháp: = COUNTBLANK(range)

Range : Dãy các ô để đếm số ô rỗng có trong đó.​

Lưu ý:

• Các ô có chứa công thức trả về là một chuỗi rỗng cũng sẽ được đếm như các ô rỗng bình thường. Nhưng các ô có chứa giá trị = 0 sẽ không được đếm.

Ví dụ:

Có bảng tính như sau:

COUNTBLANK(A2:C5) = 10​

 

[BNTT]

Hàm Thống kê


Hàm COUNTIF()

Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước.

Cú pháp: = COUNTIF(range, criteria)

Range : Dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua.

Criteria : Điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...​

Lưu ý:

• Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)

• Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa.

Ví dụ 1: Có bảng tính như sau

Đếm số ô có chữ "Táo" trong dãy A2:A5

COUNTIF(A2:A5, "Táo") = 2​

Đếm số ô có chữ "Lê" trong dãy A2:A5

COUNTIF(A2:A5, A4) = 1​

Đếm số ô có chữ "Táo" vào số ô có chứa chữ "Lê" trong dãy A2:A5)

COUNTIF(A2:A5, A3) + COUNTIF(A2:A5, A2) = 3​

Đếm số ô có giá trị > 55 trong dãy B2:B5

COUNTIF(B2:B5, "> 55") = 2​

Đếm số ô có giá trị khác 75 trong dãy B2:B5

COUNTIF(B2:B5, "<>" & B4) = 3​

Đếm số ô có giá trị lớn hơn hoặc bằng 32, nhưng không lớn hơn 85, trong dãy B2:B5

COUNTIF(B2:B5, ">=32") - COUNTIF(B2:B5, ">85") = 3​

Ví dụ 2: Có bảng tính như sau

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7

COUNTIF(A2:A7, "*es") = 2​

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:B7

COUNTIF(A2:B7, "*es") = 6​

Đếm số ô có 2 chữ cuối là "es" trong dãy A2:A7 mà có chính xác 7 chữ ký tự

COUNTIF(A2:A7, "?????es") = 2​

Đếm số ô có chữ (text) trong dãy A2:A7

COUNTIF(A2:A7, "*") = 4​

Đếm số ô không chứa chữ (text) trong dãy A2:A7

COUNTIF(A2:A7, "<>"&"*") = 2​

Tính tỷ lệ số ô chứa chữ "No" trong dãy ô B2:B7, bao gồm cả những ô rỗng

COUNTIF(B2:B7, "No") / ROWS(B2:B7) = 33%​

Tính tỷ lệ số ô chứa chữ "Yes" trong dãy ô B2:B7, không tính những ô rỗng

COUNTIF(B2:B7, "Yes") / (ROWS(B2:B7) - COUNTIF(B2:B7, "<>" & "*")) = 0.5 = 50%​

 

[BNTT]

Hàm Thống kê

Hàm COUNTIFS()

Đếm số lượng các ô trong một vùng thỏa nhiều điều kiện cho trước.

Cú pháp: = COUNTIFS(range1, criteria1, range2, criteria2, ...)

Range1, range2... : Có thể có từ 1 đến 127 dãy các ô để đếm. Chúng có thể là ô chứa số, text, tên, mảng, hay tham chiếu đến các ô chứa số, ô rỗng sẽ được bỏ qua.

Criteria1, criteria2... : Có thể có từ 1 đến 127 điều kiện để đếm. Chúng có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...​

Lưu ý:

• Mỗi ô trong range chỉ được đếm nếu tất cả các điều kiên tương ứng với ô đó đều đúng.

• Nếu criteria là một ô rỗng, Excel sẽ xem như ô đó chứa số 0.

• Có thể dùng các ký tự đại diện trong các điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc *, thì gõ thêm dấu ~ ở trước dấu ? hay *)

• Khi điều kiện để đếm là những ký tự, COUNTIF() không phân biệt chữ thường hay chữ hoa.

Ví dụ 1: Với bảng tính ở ví dụ 1 bài trên (hàm COUNTIF), nếu dùng hàm COUNTIFS thì ngắn hơn nhiều:

Đếm số ô có giá trị lớn hơn hoặc bằng 32, nhưng không lớn hơn 85, trong dãy B2:B5

COUNTIF(B2:B5, ">=32") - COUNTIF(B2:B5, ">85")= 3

COUNTIFS(B2:B5, ">=32", B2:B5, "<85") = 3​

Ví dụ 2: Có bảng tính như sau

Đếm số lần Davolio vượt hạn mức doanh thu ở Widgets, Gadgets và Doodads ?

COUNTIFS(B33, "=Yes") = 1 (ở Widgets)​

Có bao nhiên nhân viên bán hàng vượt hạn mức doanh thu ở cả Widgets và Gadgets ?

COUNTIFS(B3:B6, "=Yes", C3:C6, "=Yes") = 2 (Buchanan và Suyama)​

Leverling và Buchanan cùng vượt hạn mức doanh thu ở Widgets, Gadgets và Doodads mấy lần ?

COUNTIFS(B66, "=Yes", B44, "=Yes") = 1 (ở Gadgets)​

 

[BNTT]

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


Hàm COVAR()

Trả về hiệp phương sai (hay còn gọi là đồng phương sai - covariance).
Hiệp phương sai là trung bình của tích các cặp sai lệch, nghĩa là tính tính số các độ lệch của mỗi cặp dữ liệu, rồi tính trung bình của các tích đó.

Cú pháp: = COVAR(array1, array2)

Array1, array2: Là dãy thứ nhất và dãy thứ hai (chứa những số nguyên, và có số điểm dữ liệu giống nhau) để tính hiệp phương sai.​

Lưu ý:

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

• Nếu Array 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 array1 và array2 có số điểm dữ liệu khác nhau, COVAR() sẽ trả về giá trị lỗi #NA!

• Nếu array1 hay array2 rỗng, COVAR() sẽ trả về giá trị lỗi #DIV/0!

• COVAR() được tính theo công thức sau:
  
  

Ví dụ:

Có hai dãy sau: Data1 = {3, 2, 4, 5, 6} và Data2 = {9, 7, 12, 15, 17}

COVAR({3, 2, 4, 5, 6}, {9, 7, 12, 15, 17}) = 5.2​

​

 

[BNTT]

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


Hàm CRITBINOM()

Trả về giá trị nhỏ nhất sao cho phân phối nhị phân tích lũy tại đó lớn hơn hay bằng giá trị tiêu chuẩn alpha.
Hàm này thường được dùng trong bảo đảm chất lượng. Ví dụ, dùng để xác định số lượng lớn nhất các thành phần bị lỗi để loại ra khỏi lô hàng mà cần phải loại bỏ cả lô hàng.

Cú pháp: = CRITBINOM(trials, probability_s, alpha)

Trials : Số lần thử Bernoulli.

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

Alpha : Giá trị điều kiện.​

Lưu ý:

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

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

• Nếu trials < 0, CRITBINOM() sẽ trả về giá trị lỗi #NUM!

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

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

Ví dụ:

Với 6 lần thử Bernoulli, xác suất thành công trong một lần thử là 0.5, giá trị nhỏ nhất mà phân phối nhị phân tích lũy tại đó lớn hơn hay bằng giá trị tiêu chuẩn 0.75 được tính theo công thức:

CRITBINOM(6, 0.5, 0.75) = 4​

​

 

[BNTT]

Hàm Thống kê


Hàm DEVSQ()

Trả về tổng bình phương các sai lệch giữa 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.

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

Number1, number2, ... : Có thể có từ 1 đến 255 đối số. Cũng có thể dùng một mảng đơn hay một tham chiếu mảng.​

Lưu ý:

• Các đối số phải là số, tên, mảng, hay tham chiếu tới 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.

• DEVSQ() được tính theo công thức sau đây:
  
  

Ví dụ:

DEVSQ(4, 5, 8, 7, 11, 4, 3) = 48​

​

 

[BNTT]

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


Hàm EXPONDIST()

Tính phân phối mũ: trả về xác suất của phân phối xác suất mũ.
Thường được dùng để mô phỏng khoảng thời gian giữa các biến cố, như máy ATM sẽ mất khoảng bao lâu để xìa tiền ra; hay là tìm xác suất sao cho tiến trình đó chỉ tốn tối đa là 30 giây...

Cú pháp: = EXPONDIST(x, lambda, cumulative)

x : Giá trị của hàm mũ.

Lambda : Tham số lambda.

Cumulative : Một giá trị logic, cho biết dạng nào của hàm số mũ sẽ được sử dụng:

= 1 (TRUE) : EXPONDIST() trả về hàm phân phối tích lũy

= 0 (FALSE) : EXPONDIST() trả về hàm mật độ xác suất

Lưu ý:

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

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

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

Ví dụ:

Với x = 0.2 và lambda = 10, ta có:

EXPONDIST(0.2, 10, 1) = 0.864664717

EXPONDIST(0.2, 10, 0) = 1.353352832​

​

 

[BNTT]

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


Hàm FDIST()

Tính phân phối xác suất F.
Thường được dùng để tìm xem giữa hai tập số liệu có nhiều mức độ khác biệt hay không. Ví dụ, dùng để khảo sát điểm thi của nam sinh và của nữ sinh thi tuyển vào một trường trung học, rồi xác định xem độ biến thiên điểm của nam sinh có khác với độ biến thiên điểm của nam sinh hay không...

Cú pháp: = FDIST(x, degrees_freedom1, degrees_freedom2)

x : Giá trị để ước lượng hàm.

Degrees_freedom1 : Bậc tự do ở tử số.

Degrees_freedom2 : Bậc tự do ở mẫu số.​

Lưu ý:

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

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

• Nếu degrees_freedom1 hay degrees_freedom2 không phải là số nguyên, phần thập phân của nó sẽ bị cắt bỏ để trở thành số nguyên.

• Nếu degrees_freedom1 < 1 hay degrees_freedom1 ≥ 10^10, FDIST() trả về giá trị lỗi #NUM!

• Nếu degrees_freedom2 < 1 hay degrees_freedom2 ≥ 10^10, FDIST() trả về giá trị lỗi #NUM!

• FDIST() được tính ở dạng FDIST = P(F < x), với F là biến ngẫu nhiên có phân phối F với hai bậc tự do degrees_freedom1 và degrees_freedom2

Ví dụ:

Với x = 15.20675 và bậc tự do ở tử số là 6, bậc tự do ở mẫu số là 4, ta có:

FDIST(15.20675, 6, 4) = 0.010000141​

​

 

[BNTT]

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


Hàm FINV()

Tính nghịch đảo của phân phối xác suất F.
Nghĩa là, nếu xác suất = FDIST(x, ...) thì x = FINV(xác suất, ...)

Cú pháp: = FINV(probability, degrees_freedom1, degrees_freedom2)

Probability : Xác suất kết hợp với phân phối tích lũy F.

Degrees_freedom1 : Bậc tự do ở tử số.

Degrees_freedom2 : Bậc tự do ở mẫu số.​

Lưu ý:

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

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

• Nếu degrees_freedom1 hay degrees_freedom2 không phải là số nguyên, phần thập phân của nó sẽ bị cắt bỏ để trở thành số nguyên.

• Nếu degrees_freedom1 < 1 hay degrees_freedom1 ≥ 10^10, FDIST() trả về giá trị lỗi #NUM!

• Nếu degrees_freedom2 < 1 hay degrees_freedom2 ≥ 10^10, FDIST() trả về giá trị lỗi #NUM!

• FINV() được dùng để trả về các trị tiêu chuẩn từ phân phối F. Ví dụ, kết quả của phép tính ANOVA thường gồm số liệu cho thống kê F, xác suất F, và giá trị tiêu chuẩn F tại mức có nghĩa 0.05. Để trả về giá trị tiêu chuẩn F, người ta dùng mức có nghĩa này (0.05) làm đối số probabilty cho hàm FINV().

• FINV() sử dụng phương pháp lặp để tính hàm. Với probability cho trước, FINV() sẽ lặp cho tới khi kết quả chính xác trong khoảng ±0.0000003. Nếu FINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!

Ví dụ:

Với probability = 0.01 và bậc tự do ở tử số là 6, bậc tự do ở mẫu số là 4, ta có:

FINV(0.01, 6, 4) = 15.20675​

​

 

[BNTT]

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


Hàm FISHER()

Trả về phép biến đổi Fisher tại x.
Phép biến đổi này tạo ra hàm phân phối hơn là đối xứng lệch. Thường được dùng trong việc kiểm tra giả thuyết dựa trên hệ số tương quan.

Cú pháp: = FISHER(x)

x : Giá trị muốn chuyển đổi.​

Lưu ý:

• Nếu x khôing phải là số, FISHER() trả về giá trị lỗi #VALUE!

• Nếu x ≤ -1 hay x > 1, FISHER() trả về giá trị lỗi #NUM!

• Phương trình của phép biến đổi FISHER là:
  
  

Ví dụ:

FISHER(0.75) = 0.972955​

​

 

[BNTT]

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


Hàm FISHERINV()

Trả về nghịch đảo của phép biến đổi Fisher.
Nghĩa là, nếu y = FISHER(x) thì x = FISHERINV(y)

Cú pháp: = FISHERINV(y)

y : Giá trị để thực hiện phép biến đổi.​

Lưu ý:

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

• Phương trình của phép biến đổi FISHERINV là:
  
  

Ví dụ:

FISHERINV(0.972955) = 0.75​

​

 

[BNTT]

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


Hàm FORECAST()

Tính toán, hay dự đoán, ước lượng một giá trị tương lai bằng cách sử dụng các giá trị hiện có. Từ những giá trị hiện có, giá trị mới được dự đoán bằng phương pháp hồi quy tuyến tính. Có thể dùng hàm này để dự đoán mức bán hàng trong tương lai, nhu cầu đầu tư, hay khuynh hướng tiêu thụ.

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

x : Điểm dữ liệu dùng để dự đoán giá trị mới.

known_y's : Mảng hay dữ liệu phụ thuộc.

known_x's : Mảng hay dữ liệu độc lập.​

Lưu ý:

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

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

• Nếu known_x's = 0, FORECAST() trả về giá trị lỗi #DIV/0!

• Phương trình của FORECAST là:
  
  
  Với:
  
  

Ví dụ:

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 ?

Mức lợi nhuận tương ứng với giá thành = $270,000 sẽ là:

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

​

 

[BNTT]

Hàm FTEST()

Trả về kết quả của một phép thử F. FTEST() trả về xác suất một phía, trong đó phương sai của array1 và array2 khác nhau không đáng kể. Hàm này thường được dùng để xác định xem hai mẫu có các phương sai khác nhau hay không. Ví dụ, khi đã biết điểm kiểm tra của các trường công và của các trường tư, chúng ta có thể kiểm tra xem giữa hai loại trường này có nhiều cấp độ khác nhau về sự đa dạng của điểm thi hay không.

Cú pháp: = FTEST(array1, array2)

Array1, array2 : Là các mảng hay dãy số liệu.​

Lưu ý:

• Các đối số phải là số, tên, mảng, hay tham chiếu tới 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 số lượng các điểm dữ liệu trong các array nhỏ hơn 2, hay phương sai của chúng là zero (0), FTEST() trả về giá trị lỗi #DIV/0!

Ví dụ:

Tính kết quả của phép thử F cho hai tập hợp dữ liệu là {6, 7, 9, 15, 21} và {20, 28, 31, 38, 40}:

FTEST({6, 7, 9, 15, 21}, {20, 28, 31, 38, 40}) = 0.648318​

​

 

[BNTT]

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


Hàm GAMMADIST()

Trả về xác suất của phân phối gamma. Có thể dùng hàm này để nghiên cứu những biến có phân phối lệch. Phân phối gamma thường được sử dụng trong phân tích hàng đợi (queuing analysis).

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

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

Alpha và Beta : Tham số cho phân phối. Nếu beta = 0, GAMMADIST() trả về xác suất của phân phối gamma chuẩn.

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

Lưu ý:

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

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

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

• Phương trình của GAMMADIST() là:
  
  

• Phương trình của phân phối gamma chuẩn (beta = 0)
  
  

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

• Với số nguyên dương n, khi alpha = n/2, beta = 2, và cumulative = 1 (TRUE), GAMMADIST() trả về [1 - CHIDIST(x)] với n là bậc tự do.

Ví dụ:

Với x = 10 , alpha = 9 và beta = 2, ta có:

GAMMADIST(10, 9, 2, TRUE) = 0.68094

GAMMADIST(10, 9, 2, FALSE) = 0.32639​

​

 

[BNTT]

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


Hàm GAMMAINV()

Trả về nghịch đảo của phân phối gamma. Nghĩa là, nếu probability = GAMMADIST(x, ...) thì x = GAMMAINV(probability, ...)

Cú pháp: = GAMMAINV(probability, alpha, beta)

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

Alpha và Beta : Tham số cho phân phối. Nếu beta = 0, GAMMAINV() trả về phân phối gamma chuẩn.​

Lưu ý:

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

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

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

• GAMMAINV() sử dụng phương pháp lặp để tính hàm. Với probability cho trước, GAMMAINV() sẽ lặp cho tới khi kết quả chính xác trong khoảng ±0.0000003. Nếu GAMMAINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!

Ví dụ:

Với probability = 0.68094, alpha = 9 và beta = 2, ta có:

GAMMAINV(0.68094, 9, 2) = 10​

​

 

[BNTT]

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


Hàm GAMMALN()

Tính logarite tự nhiên của hàm gamma.

Cú pháp: = GAMMALN(x)

Lưu ý:

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

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

• Số e lũy thừa GAMMALN(i), với i là số nguyên, trả về cùng kết quả như (i-1)!

• GAMMALN được tính với công thức sau:
  
  
  với:
  
  

Ví dụ:

Logarite tự nhiên của hàm gamma tại 4:

GAMMALN(4) = 1.791759​

​

 

[BNTT]

Hàm Thống kê


Hàm GEOMEAN()

Trả về trung bình nhân của một mảng hoặc một dãy các số dương. Ví dụ, có thể dùng GEOMEAN() để tính mức tăng trưởng trung bình.

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

Number1, number2 ... : Có thể có từ 1 đến 255 đối số dùng để tính trung bình. Cũng có thể dùng một mảng đơn hay một tham chiếu đến các ô chứa số.​

Lưu ý:

• Các đối số phải là số, tên, mảng, hay tham chiếu tới 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 có bất kỳ đối số nào không phải là số dương, GEOMEAN() sẽ trả về giá trị lỗi #VALUE!

• GEOMEAN được tính bằng phương trình sau:
  
  

Ví dụ:

GEOMEAN({4, 5, 8, 7, 11, 4, 3}) = 5.476987
​

​

 

[BNTT]

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


Hàm GROWTH()

Tính toán sự tăng trưởng dự kiến theo hàm mũ bằng cách sử dụng dữ kiện hiện có. GROWTH() trả về các giá trị y từ các giá trị x được chỉ định bằng cách sử dụng các giá trị x hiện có.

GROWTH() 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: = GROWTH(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 = 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.

- Nếu có bất kỳ số nào trong known_y's là 0 hay là số âm, GROWTH() sẽ trả về giá trị lỗi #NUM!​

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 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 để GROWTH() 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 1 hay không (trong mối quan hệ y = b*m^x).

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

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

Lưu ý:

• 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ụ:

Đây mà một bảng mô tả mức tăng trưởng doanh thu của một đơn vị từ tháng thứ 11 đến tháng thứ 16.
Dựa theo mức tăng trưởng này, dự đoán doanh thu của tháng thứ 17 và 18 ?

Chọn cả hai ô B9:B10, nhập công thức mảng:

{= GROWTH(B2:B7, A2:A7, A9:A10)}​

Ta sẽ có kết quả doanh thu dự đoán của tháng thứ 17 (B9) = 320,197 và tháng thứ 18 (B10) = 468,536​

​

 

[BNTT]

Hàm Thống kê


Hàm HARMEAN()

Trả về trung bình điều hòa của một dãy các số dương. Trung bình điều hòa là nghịch đảo của trung bình cộng.

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

Number1, number2 ... : Có thể có từ 1 đến 255 đối số dùng để tính trung bình điều hòa. Cũng có thể dùng một mảng đơn hay một tham chiếu đến các ô chứa số.​

Lưu ý:

• Trung bình điều hòa luôn nhỏ hơn trung bình nhân, mà trung bình nhân là một số luôn nhỏ hơn trung bình cộng.

• Những đối số là giá trị lỗi hay giá trị text mà không thể chuyển đổi thành giá trị số sẽ gây ra lỗi.

• Các đối số phải là số, tên, mảng, hay tham chiếu tới 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 có bất kỳ đối số nào không phải là số dương, HARMEAN() sẽ trả về giá trị lỗi #VALUE!

• HARMEAN được tính bằng phương trình sau:
  
  

Ví dụ:

HARMEAN({4, 5, 8, 7, 11, 4, 3}) = 5.028376
​

​

 

[BNTT]

Hàm HYPGEOMDIST()

Trả về xác suất của phân phối siêu bội (hypergeometric distribution), là phân phối của biến ngẫu nhiên x biểu diễn số lần thành công trong m lần đầu tiên của một chuỗi n thực nghiệm độc lập, nếu cho trước tổng số lần thành công.

Cú pháp: = HYPGEOMDIST(sample_s, number_sample, population_s, number_population)

sample_s : Số lần thành công trong mẫu.

number_sample : Kích thước mẫu.

population_s : Số lần thành công trong tập hợp chính.

number_population : Kích thước tập hợp chính.​

Lưu ý:

• Tất cả các đối số nếu không phải là số nguyên, phần thập phân của chúng sẽ bị cắt bỏ để trở thành số nguyên.

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

• Nếu sample_s < 0 hoặc lớn hơn giá trị nhỏ nhất giữa number_sample và population_s, HYPGEOMDIST() trả về giá trị lỗi #NUM!

• Nếu sample_s nhỏ lớn hơn giá trị lớn nhất giữa 0 và (number_sample - number_population + population_s), HYPGEOMDIST() trả về giá trị lỗi #NUM!

• Nếu number_sample ≤ 0 hay number_sample > number_population, HYPGEOMDIST() trả về giá trị lỗi #NUM!

• Nếu population_s ≤ 0 hay population_s > number_population, HYPGEOMDIST() trả về giá trị lỗi #NUM!

• Nếu number_population ≤ 0, HYPGEOMDIST() trả về giá trị lỗi #NUM!

• Phương trình của HYPGEOMDIST() là:
  
  
  Với:
  
  x = sample_s
  n = number_sample
  M = population_s
  N = number_population​

Ví dụ:

Tính xác suất của phân phối siêu bội sau, biết rằng trong phép thử với 4 mẫu bất kỳ đầu tiên của một tập hợp gồm 20 phần tử thì có số lần thành công là 1, và có 8 lần thành công trong phép thử với toàn tập hợp ?

HYPGEOMDIST(1, 4, 8, 20) = 0.363261​

​

 

[BNTT]

Danh mục các Hàm Thống Kê


3. 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, const) : Tính toán sự tăng trưởng dự kiến theo hàm mũ, bằng 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) : 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

PEARSON (array1, array2) : 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
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.
​

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

1. Nhóm hàm về Thống Kê ...

2. Nhóm hàm về Phân Phối Xác Suất ...

 

[BNTT]

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


Hàm LOGINV()

Trả về nghịch đảo của phân phối tích lũy lognormal của x, trong đó ln(x) thường được phân phối với các tham số mean và standard_dev. Nếu probability = LOGNORMDIST(x, ...) thì x = LOGINV(probability, ...). Dùng phân phối lognormal để phân tích số liệu được chuyển đổi theo dạng logarite.

Cú pháp: = LOGINV(probability, mean, standard_dev)

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

Mean : Trung bình của ln(x).

Standard_dev : Độ lệch chuẩn của ln(x).​

Lưu ý:

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

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

• Nếu standard_dev ≤ 0, LOGINV() trả về giá trị lỗi #NUM!

• Nghịch đảo của hàm phân phối lognormal là:
  
  

Ví dụ:

Tính x khi biết xác suất đối với phân phối lognormal của x là 0.039084, trung bình của ln(x) là 3.5 và độ lệch chuẩn của ln(x) là 1.2 ?:

LOGINV(0.039084, 3.5, 1.2) = 4.000025​

​

 

[BNTT]

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


Hàm LOGNORMDIST()

Trả về xác suất của phân phối tích lũy lognormal của x, trong đó ln(x) thường được phân phối với các tham số mean và standard_dev. Dùng phân phối lognormal để phân tích số liệu được chuyển đổi theo dạng logarite.

Cú pháp: = LOGNORMDIST(x, mean, standard_dev)

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

Mean : Trung bình của ln(x).

Standard_dev : Độ lệch chuẩn của ln(x).​

Lưu ý:

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

• Nếu x ≤ 0 hay standard_dev ≤ 0, LOGNORMDIST() trả về giá trị lỗi #NUM!

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

Ví dụ:

Tính xác suất của phân phối lognormal tại 4, biết trung bình của ln(4) là 3.5 và độ lệch chuẩn của ln(4) là 1.2 ?:

LOGNORMDIST(4, 3.5, 1.2) = 0.039084​

​

 

[BNTT]

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


Hàm POISSON()

Trả về xác suất của phân phối Poisson. Ứng dụng phổ biến của phân phối Poisson là đoán số lượng biến cố sẽ xảy ra trong một thời gian xác định. Ví dụ: Số lượng xe hơi đi ngang qua 1 điểm trên con đường trong một khoảng thời gian cho trước; số lần gõ bị sai của khi đánh máy một trang giấy, số lần truy cập vào một máy chủ web trong mỗi phút...

Cú pháp: = POISSON(x, mean, cumulative)

x : Số lượng các biến cố.

Mean : Giá trị kỳ vọng.

Cumulative : Một giá trị logic xác định dạng phân phối xác suất được trả về:

- Nếu cumulative là TRUE (1), POISSON() trả về xác suất tích lũy Poisson, đây là số biến cố ngẫu nhiên xảy ra trong khoảng thời gian từ 0 đến x, kể cả x; và POISSON() được tính theo công thức:

- Nếu cumulative là FALSE (0), POISSON() trả về hàm khối lượng xác suất Poisson, trong đó số biến cố xảy ra chính là x; và POISSON() được tính theo công thức:

Lưu ý:

• Nếu x không nguyên, phần lẻ của nó sẽ được cắt bỏ để trở thành số nguyên.

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

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

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

Ví dụ:

Tính xác suất tích lũy và hàm khối lượng xác suất của phân phối Poisson nếu số lượng các biến cố là 2 và trung bình kỳ vọng là 5 ?:

Xác suất tích lũy Poisson:

POISSON(2, 5, 1) = 0.124652​

Hàm khối lượng xác suất Poisson:

POISSON(2, 5, 0) = 0.084224​

Bài đọc thêm: Phân phối Poisson (theo Wikipedia)

 

[BNTT]

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


Hàm PROB()

Tính xác suất xuất hiện của nhóm các biến cố (x_range) nằm giữa hai giới hạn (upper_limit và lower_limit). Nếu bỏ qua giới hạn trên (upper_limit) thì xem như nhóm các biến cố là bằng với giới hạn dưới (lower_limit).

Cú pháp: = PROB(x_range, prob_range, lower_limit, upper_limit)

x_range : Dãy các giá trị.

Prob_range : Tập hợp các giá trị xác suất xuất hiện tương ứng với các giá trị trong x_range, tổng các giá trị này phải bằng 1.

Lower_limit : Giới hạn trên của trị muốn tính xác suất.

Upper_limit : Giới hạn dưới của trị muốn tính xác suất.​

Lưu ý:

• Nếu có bất kỳ giá trị nào trong prob_range ≤ 0 hay bất kỳ giá trị nào trong prob_range > 1 , PROB() trả về giá trị lỗi #NUM!

• Nếu tổng các giá trị trong prob_range không bằng 1, PROB() trả về giá trị lỗi #NUM!

• Nếu x_range và prob_range có số lượng các giá trị không bằng nhau, PROB() trả về giá trị lỗi #NA!

Ví dụ:

Cho một dãy các giá trị x là 0, 1, 2, 3; và các xác suất tương ứng với x lần lượt là 0.2, 0.3, 0.1, 0.4.
Hãy tính xác suất xuất hiện của x khi x = 2 và khi x thuộc khoảng [1, 3] ?

Xác suất khi x = 2:

PROB({0, 1, 2, 3}, {0.2, 0.3, 0.1, 0.4}, 2) = 0.1​

Xác suất khi x thuộc khoảng [1, 3]:

PROB({0, 1, 2, 3}, {0.2, 0.3, 0.1, 0.4}, 1, 3) = 0.8​

 

[BNTT]

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


Hàm STANDARDIZE()

Trả về giá trị chuẩn hóa của x từ phân phối biểu thị bởi mean và standard_dev.

Cú pháp: = STANDARDIZE(x, mean, standard_dev)

x : Giá trị muốn chuẩn hóa.

Mean : Trung bình cộng của phân phối.

Standard_dev : Độ lệch chuẩn của phân phối.​

Lưu ý:

• Nếu standard_dev ≤ 0, STANDARDIZE() trả về giá trị lỗi #NUM!

• Phương trình tính trị chuẩn hóa là:
  
  

Ví dụ:

Tính giá trị chuẩn hóa của phân phối tại điểm = 42, biết trung bình cộng của phân phối là 40 và độ lệch chuẩn của nó là 1.5 ?

STANDARDIZE(42, 40, 1.5) = 1.333333​

​

 

[BNTT]

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]

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]

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]

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]

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]

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]

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]

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]

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, A313, 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]

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]

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]

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]

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]

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]

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]

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 ở D312 = 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]

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]

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]

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]

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​

​

 

[BNTT]

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


Hàm PEARSON()

Trả về hệ số tương quan momen tích Pearson, r, một đại lượng vô hướng nằm trong khoảng [-1, 1], phản ánh sự mở rộng quan hệ tuyến tính giữa hai tập số liệu.

Cú pháp: = PEARSON(array1, array2)

Array1: Là tập hợp các giá trị độc lập.

Array2: Là tập hợp các giá trị phụ thuộc.​

Lưu ý:

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

• Nếu đố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 array1 hay array2 rỗng hoặc có số điểm dữ liệu không bằng nhau, PEARSON() sẽ trả về giá trị lỗi #NA!

• PEARSON() được tính theo công thức sau:
  
  
  với:
  
  

Ví dụ:

Cho tập hợp các giá trị độc lập = {9, 7, 5, 3, 1}
và tập hợp các giá trị phụ thuộc = {10, 6, 1, 5, 3}

Hệ số tương quan tích momen Pearson đối với hai tập số liệu trên là:

r = PEARSON({9, 7, 5, 3, 1}, {10, 6, 1, 5, 3}) = 0.699379​

​

 

[BNTT]

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


Hàm RSQ()

Tính bình phương hệ số tương quan momen tích Pearson, thông qua các điểm dữ liệu trong known_y's và known_x's. Trị bình phương r có thể hiểu là tỷ lệ phương sai trong thuộc tính y với phương sai trong thuộc tính x. Để biết thêm thông tin, xem thêm hàm PEARSON().

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

known_y's, known_x's: Là mảng hay dãy các điểm dữ liệu.​

Lưu ý:

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

• Nếu đố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 hay known_x's rỗng hoặc có số điểm dữ liệu không bằng nhau, RSQ() sẽ trả về giá trị lỗi #NA!

• Phương trình tính trị r của đường hồi quy là:
  
  
  với:
  
  

Ví dụ:

Cho hai tập hợp các điểm dữ liệu là {2, 3, 9, 1, 8, 7, 5} và {6, 5, 11, 7, 5, 4, 4}

Bình phương hệ số tương quan tích momen Pearson đối với hai tập số liệu trên là:

= RSQ({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4}) = 0.05795​

​

 

[BNTT]

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


Hàm STEYX()

Trả về sai số chuẩn của trị dự đoán y đối với mỗi trị x trong hồi quy. Sai số chuẩn là thước đo lượng sai số trong dự đoán y đối với mỗi trị x.

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

known_y's: Là mảng hay dãy các điểm dữ liệu phụ thuộc.
known_x's: Là mảng hay dãy các điểm dữ liệu độc lập.​

Lưu ý:

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

• Nếu đố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 hay known_x's rỗng hoặc có số điểm dữ liệu không bằng nhau, STEYX() sẽ trả về giá trị lỗi #NA!

• Phương trình tính sai số chuẩn của trị dự đoán y là:
  
  
  với:
  
  

Ví dụ:

Cho hai tập hợp các điểm dữ liệu là {2, 3, 9, 1, 8, 7, 5} và {6, 5, 11, 7, 5, 4, 4}

Sai số chuẩn của trị dự đoán y đối với mỗi trị x trong hồi quy của hai tập số liệu trên là:

= STEYX({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4, 4}) = 3.305719​

​

---------------------- HẾT PHẦN EXCEL'S STATISTICAL FUNCTIONS ----------------------​