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

[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)