Giới thiệu 10 hàm thống kê trong Excel cơ bản và các ví dụ cụ thể

Các thao tác thống kê trong Excel luôn bắt đầu từ các hàm cơ bản nhất. Chính vì vậy, trong bài viết này, Gitiho sẽ cùng bạn khám phá 10 hàm thống kê trong Excel cơ bản bạn nhất định phải biết để sử dụng trong các phân tích định lượng, thống kê mô tả và xác suất thống kê.

Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu

Hàm thống kê trong Excel: Hàm COUNT

Hàm thống kê trong Excel đầu tiên chúng ta tìm hiểu là hàm COUNT. Đây là hàm đếm các giá trị định dạng số trên bảng tính.

Cú pháp hàm COUNT

Hàm COUNT trong Excel hoạt động với cú pháp đơn giản như sau:

=COUNT(value1,[value2],...)

Trong đó:

• value1 - Giá trị đầu tiên đưa vào đếm. Đây là tham số bắt buộc.
• value2 - Giá trị thứ hai đưa vào đếm. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

Bạn có thể thêm tối đa 255 tham số vào công thức hàm COUNT để thực hiện thống kê trong Excel. Các tham số này phải có thể ở định dạng số, công thức, ngày tháng, tham chiếu ô hoặc một phạm vi dữ liệu bất kỳ trên trang tính.

Lưu ý với hàm COUNT

Quy tắc hoạt động của hàm COUNT trong Excel như sau: Hàm COUNT chấp nhận các giá trị số, bao gồm các giá trị được đưa vào công thức hàm với định dạng số, hoặc các giá trị có thể chuyển đổi về định dạng số, bao gồm định dạng ngày tháng (do Excel lưu trữ ngày tháng dưới dạng số sê-ri), thời gian, giá trị Boolean của TRUE và FALSE, giá trị số dưới định dạng văn bản (giá trị số đặ trong cặp dấu ngoặc kép như "20").

Nếu bạn sử dụng tham chiếu ô cho đối số hàm thống kê trong Excel này, hãy lưu ý rằng hàm sẽ chỉ chấp nhận các ô chứa giá trị số, và loại bỏ các ô không chứa giá trị số, cũng như các ô trống.

Kết luận lại, hãy lưu ý những điểm dưới đây khi bạn áp dụng cách thống kê trong Excel bằng hàm COUNT:

• Hàm COUNT chỉ đếm các ô chứa giá trị ở định dạng số, ngày tháng hoặc định dạng văn bản của số.
• Bất kỳ định dạng nào có thể chuyển đổi về định dạng số đều được đếm.
• Hàm COUNT không đếm các tham số chứa giá trị ở định dạng văn bản hoặc giá trị lỗi không thể chuyển đổi thành số.
• Nếu bạn điền một phạm vi tham chiếu hoặc đối số mảng vào hàm COUNT, hàm sẽ lọc ra các ô chứa giá trị số và chỉ đếm những ô đó.

Ví dụ sử dụng hàm COUNT

Dưới đây là một ví dụ đơn giản nhưng tổng hợp tất cả những gì bạn cần biết về cách thống kê trong Excel sử dụng hàm COUNT.

 

Như vậy, bạn có thể thấy hàm COUNT cho ta biết số lượng các ô chứa giá trị định dạng số và giá trị có thể chuyển đổi về định dạng số trong bảng dữ liệu. Ngoài ra, khi thêm các tham số khác vào hàm COUNT, hàm sẽ tự động cộng thêm vào kết quả nếu các tham số đó thỏa mãn điều kiện là giá trị số.

Một ví dụ khác về ứng dụng của hàm COUNT với công việc thống kê trong Excel như sau:

 

Trong bảng, chúng ta có một yêu cầu đếm số lượng các sản phẩm được giảm giá. Bằng cách sử dụng hàm thống kê trong Excel tên COUNT, cho phạm vi cột Giảm giá (Cột D), chúng ta đã nhận được kết quả là 6 sản phẩm.
 

Hàm thống kê trong Excel: Hàm COUNTA

Nếu bạn cần đếm tất cả các giá trị tồn tại trong bảng dữ liệu, thay vì sử dụng hàm COUNT, hãy nhờ đến sự trợ giúp của hàm COUNTA.

Cú pháp hàm COUNTA

Tương tự như hàm COUNT ở trên, hàm COUNTA có cú pháp như sau:

=COUNTA(value1, [value2],...)

Trong đó:

• value1 - Giá trị đầu tiên đưa vào đếm. Đây là tham số bắt buộc.
• value2 - Giá trị thứ hai đưa vào đếm. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

Nếu như các tham số của hàm COUNT được yêu cầu bắt buộc phải là giá trị số, thì đối với hàm COUNTA, bạn có thể nhập tham số là bất kỳ loại dữ liệu nào. Vì hàm thống kê trong Excel này sẽ thực hiện đếm tất cả các giá trị trong tham số hoặc phạm vi xác định.

Ví dụ sử dụng hàm COUNTA

Thay vì đếm số sản phẩm được giảm giá, chúng ta sẽ đếm số lượng sản phẩm được bán ra. Lúc này, chúng ta không thể sử dụng hàm COUNT vì nó không hoạt động với các ô chứa giá trị văn bản. Thay vào đó, chúng ta sẽ dùng hàm COUNTA như sau.

 

Chúng ta thu được kết quả thống kê trong Excel là 8, tương ứng với 8 tên mặt hàng xuất hiện trong cột Sản phẩm (cột A).

 

Với hàm COUNTA, đôi khi bạn nhận về kết quả sai lệch so với những gì mắt thường nhìn thấy. Giả sử như trong hình dưới đây bạn nhìn thấy 6 ô chứa dữ liệu tại cột Dữ liệu. Tuy nhiên, hàm COUNTA lại đếm ra 7 giá trị tồn tại trong phạm vi này. Vậy lý do là gì?
 

 

 

Như mình đã đề cập ở trên, hàm COUNTA là hàm thống kê trong Excel chấp nhận tất cả các giá trị, kể cả giá trị rỗng nằm trong cặp dấu ngoặc kép " ". Do đó, công thức hàm COUNTA trong hình đếm cả giá trị tại ô A8 và chỉ loại bỏ ô trống duy nhất là ô A9. Đó là lý do tại sao chúng ta nhận được kết quả là 7 thay vì 6 như những gì ta nhìn thấy bằng mắt.

Hàm thống kê trong Excel: Hàm COUNTBLANK

Ngược lại với hàm COUNT và hàm COUNTA, hàm COUNTBLANK là hàm thống kê trong Excel với mục đích đếm các ô trống và ô chứa giá trị rỗng.

Cú pháp hàm COUNTBLANK

Hàm COUNTBLANK có công thức vô cùng đơn giản và dễ nhớ với duy nhất một tham số như dưới đây:

=COUNTBLANK(range)

Trong đó: range là phạm vi dữ liệu bạn muốn đếm số ô trống.

Ví dụ sử dụng hàm COUNTBLANK

Chúng ta sẽ sử dụng hàm COUNTBLANK để thống kê trong Excel số lượng mặt hàng không được hưởng ưu đãi giảm giá. Tất cả những gì chúng ta cần làm là nhập phạm vi cột Giảm giá (cột D) vào tham số hàm COUNTBLANK.

 

Hàm thống kê trong Excel: Hàm COUNTIFS

Hàm COUNTIFS, hay còn gọi là hàm đếm dữ liệu có điều kiện, là sự kết hợp của hàm COUNT và hàm IFS. Đây là một trong những hàm thống kê được sử dụng nhiều nhất trên Excel.

Cú pháp hàm COUNTIFS

Hàm đếm dữ liệu COUNTIFS có cú pháp như sau:

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],...)

 

Trong đó:

• criteria_range1 - Phạm vi dữ liệu đầu tiên xét đến.
• criteria1 - Điều kiện đếm dành cho phạm vi dữ liệu đầu tiên (criteria_range1).
• criteria_range2, criteria2 - Cặp phạm vi - điều kiện thứ hai.

Bạn có thể thêm tối đa 127 cặp phạm vi - điều kiện vào công thức hàm COUNTIFS. Các tham số có thể là các giá trị định dạng số, văn bản hoặc ngày tháng, cũng có thể là các biểu thức logic hoặc tham chiếu ô.

 

Lưu ý với hàm COUNTIFS

Ngoài các giá trị kể trên, hàm COUNTIFS trong Excel có thể làm việc với các ký tự đại diện (wildcard), ví dụ như ký tự "?" đại diện cho một ký tự đơn, hoặc ký tự "*" đại diện cho một chuỗi ký tự.

Hàm COUNTIFS mặc định không đếm các ô trống. Tuy nhiên, bạn vẫn có thể đếm các ô này bằng cách đặt điều kiện cho tham số của hàm. Lúc đó, bạn sẽ thấy hàm thống kê trong Excel đếm cả các ô trống và các ô chứa giá trị rỗng.

Ví dụ sử dụng hàm COUNTIFS

Chúng ta sẽ ứng dụng hàm COUNTIFS để tìm câu trả lời cho câu hỏi: Có bao nhiêu loại bút đạt doanh thu lớn hơn 3.000.000?

Đầu tiên, chúng ta cần xác định các điều kiện sẽ đưa vào công thức hàm COUNTIFS:

• Phạm vi 1: Cột Sản phẩm
• Điều kiện 1: Sản phẩm là các loại bút
• Phạm vi 2: Cột Thành tiền
• Điều kiện 2: Doanh thu trên 3.000.000

Với 2 cặp phạm vi - điều kiện này, chúng ta tạo lập được công thức hàm COUNTIFS như sau:

=COUNTIFS(E2:E9,">3000000",A2:A9,"Bút*")
 

 

Như vậy, chúng ta nhận được kết quả 2 loại bút đạt doanh thu trên 3.000.000, bao gồm bút xóa và bút dạ.

Xem thêm:  Hướng dẫn sử dụng các hàm COUNT trong Excel kèm theo ví dụ

Hàm thống kê trong Excel: Hàm AVERAGE

Hàm AVERAGE là một hàm Excel cơ bản được sử dụng phổ biến trong rất nhiều bảng tính Excel, với mục đích tính trung bình một dải số liệu.

Cú pháp hàm AVERAGE

Hàm AVERAGE trong Excel có cú pháp như sau:

=AVERAGE(number1, [number2],...)

Trong đó:

• number1 - Giá trị đầu tiên đưa vào phép tính trung bình. Đây là tham số bắt buộc.
• number2 - Giá trị thứ hai đưa vào phép tính trung bình. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

 

 

Lưu ý với hàm AVERAGE

Tương tự như hàm COUNT, hàm AVERAGE chỉ chấp nhận các giá trị số hoặc các giá trị có thể chuyển đổi về giá trị số. Điều này chắc chắn không có gì khó hiểu vì chúng ta chỉ có thể thực hiện phép tính với các giá trị số thôi, đúng không nào?

Nếu bạn nhập một tham chiếu đến một phạm vi dữ liệu cần thống kê trong Excel, hàm AVERAGE sẽ tự động bỏ qua các ô không chứa giá trị hợp lệ. Tuy nhiên, các ô có giá trị 0 vẫn sẽ được chấp nhận.

Ví dụ sử dụng hàm AVERAGE

Chúng ta sẽ tìm hiểu cách thống kê trong Excel sử dụng hàm AVERAGE để tính trung bình dữ liệu. Giả sử chúng ta muốn tìm bình quân doanh thu trong bảng tổng hợp.

 

Như vậy, bạn có thể thấy hàm AVERAGE đã trả về giá trị trung bình doanh thu là 3.341.250 dựa vào cột Thành tiền (cột E). Tuy nhiên, khi áp dụng hàm thống kê trong Excel này, bạn hãy lưu ý một điều về giá trị trung bình. Đó là giá trị trung bình sẽ bị ảnh hưởng bởi các điểm dị biệt trong dải dữ liệu, do đó bức tranh thống kê của bạn có thể xuất hiện các nét vẽ sai lệch.

Xem thêm: Tải về bài tập Excel có lời giải (Hướng dẫn cách dùng hàm IF, ROUND, SUM, MIN, MAX, AVERAGE)

Để loại bỏ các sai lệch này, giải pháp cơ bản nhất là tính trung vị thay vì trung bình. Vậy làm thế nào để tính trung vị trong Excel?
 

Hàm thống kê trong Excel: Hàm MEDIAN

Hàm MEDIAN chính là công cụ bạn cần để tính trung vị trong Excel. Nó sẽ giúp bạn thoát khỏi nguy cơ sai lệch dữ liệu đến từ các điểm dị biệt.

Cú pháp hàm MEDIAN

Giống như hàm AVERAGE tính trung bình trong Excel, hàm MEDIAN tính trung vị có cú pháp như sau:

=MEDIAN(number1, [number2],...)

Trong đó:

• number1 - Giá trị đầu tiên đưa vào phép tính trung vị. Đây là tham số bắt buộc.
• number2 - Giá trị thứ hai đưa vào phép tính trung vị. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

Toàn bộ các lưu ý cho hàm AVERAGE phía trên cũng được áp dụng với hàm MEDIAN. Do đó, các bạn hãy lưu ý nhé.
 

Ví dụ sử dụng hàm MEDIAN

Tiếp tục với ví dụ thống kê trong Excel, chúng ta sẽ áp dụng hàm MEDIAN để tính trung vị cho số liệu doanh thu trong bảng tổng hợp như sau:

Bạn có thể thấy rõ sự khác biệt giữa trung bình và trung vị doanh thu sau khi thực hiện các phép tính. Mặc dù cả 2 đại lượng này đều được sử dụng để đo lường xu hướng tập trung của tập dữ liệu, nhưng mỗi đại lượng lại có các ưu điểm và nhược điểm riêng.

Hãy chắc chắn những gì cần tính để lựa chọn đúng hàm thống kê trong Excel nhé.

Hàm thống kê trong Excel: Hàm MODE

Chúng ta vẫn thường dùng các hàm MEAN và MEDIAN để tính các giá trị số. Vậy còn việc phân loại chúng thì sao? Hãy tìm hiểu cách dùng hàm MODE để biết được giá trị nào xuất hiện nhiều nhất trong tập dữ liệu nhé.

Cú pháp hàm MODE

Kể từ sau phiên bản Excel 2007, Microsoft đã loại bỏ hàm MODE, thay vào đó là 2 hàm MODE.SNGL và MODE.MULT. Về cú pháp, không có sự khác biệt gì giữa các hàm.

=MODE.SNGL(number1, [number2],...)

=MODE.MULT(number1, [number2],...)

Trong đó:

• number1 - Giá trị đầu tiên đưa vào dãy giá trị. Đây là tham số bắt buộc.
• number2 - Giá trị thứ hai đưa vào dãy giá trị. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

Sự khác biệt giữa hàm MODE.SNGL và MODE.MULT

Bạn có thể hiểu sự khác biệt giữa 2 hàm thống kê trong Excel này đơn giản như sau:

• Hàm MODE.SNGL (Single) chỉ trả về duy nhất 1 giá trị xuất hiện nhiều nhất trong tập dữ liệu.
• Hàm MODE.MULT (Multiple) trả về tất cả các giá trị xuất hiện nhiều nhất trong tập dữ liệu.

Điều này có nghĩa là: Nếu tập dữ liệu của bạn xuất hiện 2 giá trị với số lần xuất hiện bằng nhau và đứng đầu, hàm MODE.SNGL sẽ chỉ trả về giá trị đầu tiên nó tìm thấy. Trong khi đó, hàm MODE.MULT sẽ cho bạn biết cụ thể cả 2 giá trị này.

Ví dụ sử dụng hàm MODE

Hãy cùng tìm hiểu cách thống kê trong Excel với các hàm MODE nhé. Yêu cầu đề bài đưa ra là xác định số phần trăm giảm giá được áp dụng nhiều lần nhất cho các sản phẩm.

Đầu tiên, chúng ta dùng hàm MODE.SNGL như sau:

Vậy là mức ưu đãi được áp dụng nhiều nhất là 5%. Vậy nhưng nếu nhìn vào bảng, bạn sẽ thấy con số 10% cũng xuất hiện 2 lần như giá trị 5%. Để kiểm tra lại, chúng ta sử dụng hàm MODE.MULT như sau:

Bước 1: Điền công thức =MODE.MULT(D2:D9)

Bước 2: Chọn dải ô dọc H2:H3

 

Bước 3: Nhấn tổ hợp phím Ctrl+Shift+Enter

Do công thức hàm MODE.MULT là công thức mảng nên bạn cần sử dụng tổ hợp Ctrl+Shift+Enter để kết thúc công thức. Sau đó, bạn sẽ thấy hàm trả về 2 kết quả 5% và 10%, nghĩa là 2 giá trị này đều xuất hiện nhiều lần nhất trong phạm vi D2:D9.

Hãy ghi nhớ trình tự các bước trên nhé, nếu không thì hàm sẽ chỉ trả về một giá trị như hàm MODE.SNGL mà thôi.

Xem thêm: Cách dùng hàm MODE tìm giá trị có tần suất xuất hiện nhiều nhất trong Excel

Hàm thống kê trong Excel: Hàm STDEV

Độ lệch chuẩn là một đại lượng được sử dụng để đo lường mức độ phân tán của dữ liệu. Khi áp dụng cách thống kê trong Excel, chúng ta có thể dùng hàm STDEV.P để tính toán đại lượng này.

Cú pháp hàm STDEV

Cú pháp hàm STDEV.P như sau:

=STDEV.P(number1, [number2],...)

Trong đó:

• number1 - Giá trị đầu tiên đưa vào phép tính. Đây là tham số bắt buộc.
• number2 - Giá trị thứ hai đưa vào phép tính. Đây là tham số tùy chọn, nghĩa là bạn không bắt buộc phải nhập thêm giá trị thứ hai vào công thức hàm.

Hàm STDEV.P coi tham số được nhập vào trong cú pháp hàm là toàn bộ tổng thể. Do đó, nếu như bạn giả định các tham số nhập vào hàm thống kê trong Excel chỉ là mẫu tổng thể, hãy sử dụng hàm STDEV.S với cú pháp tương tự.

Toàn bộ các lưu ý cho hàm AVERAGE và hàm MEDIAN phía trên cũng được áp dụng với hàm STDEV.P và STDEV.S. Bạn hãy lưu ý nhé.

Ví dụ sử dụng hàm STDEV

Chúng ta sẽ tính độ lệch chuẩn cho các giá trị doanh thu trong bảng tổng hợp bằng công thức hàm STDEV.P như dưới đây:

Xem thêm: Cách tạo số ngẫu nhiên theo giá trị trung bình cộng và độ lệch chuẩn trong Excel

Hàm thống kê trong Excel: Hàm QUARTILE

Nếu bạn cần một hàm thống kê trong Excel phân loại các giá trị trong tập dữ liệu thành các nhóm 25%, hãy sử dụng hàm QUARTILE.

Cú pháp hàm QUARTILE

Hàm QUARTILE có cú pháp như sau:

=QUARTILE(array,quart)

Trong đó:

• array - Phạm vi dữ liệu
• quart - Loại giá trị cần trả về
  • quart = 0: Giá trị tối thiểu trong phạm vi dữ liệu
  • quart = 1: Phần tư thứ nhất (25%) trong phạm vi dữ liệu
  • quart = 2: Phần tư thứ hai (50%) trong phạm vi dữ liệu
  • quart = 3: Phần tư thứ ba (75%) trong phạm vi dữ liệu
  • quart = 4: Giá trị tối đa trong phạm vi dữ liệu

 

 

Lưu ý với hàm QUARTILE

Khi sử dụng hàm QUARTILE để thực hiện thống kê trong Excel, bạn cần lưu ý một số điểm dưới đây về tham số của hàm:

• Nếu để trống tham số array, hàm QUARTILE trả về lỗi #NUM!.
• Nếu tham số quart không phải số nguyên, hàm QUARTILE trả về giá trị sai lệch.
• Nếu tham số quart < 0 hoặc quart > 4, hàm QUARTILE trả về lỗi #NUM!.
• Nếu tham số quart = 0, hàm QUARTILE trả về giá trị bằng hàm MIN.
• Nếu tham số quart = 2, hàm QUARTILE trả về giá trị bằng hàm MEDIAN.
• Nếu tham số quart = 4, hàm QUARTILE trả về giá trị bằng hàm MAX.

Ví dụ sử dụng hàm QUARTILE

Chúng ta có thể sử dụng hàm QUARTILE để tìm ra các điểm 25%, 50% và 75% trong mức doanh thu như sau:

Hàm thống kê trong Excel: Hàm CORREL

Hàm thống kê trong Excel cuối cùng chúng ta tìm hiểu trong bài viết ngày hôm nay là hàm CORREL - hàm tìm hệ số tương quan giữa 2 ô hoặc 2 phạm vi dữ liệu.

Cú pháp hàm CORREL

Hàm CORREL trong Excel được sử dụng với cú pháp như sau:

=CORREL(array1, array2)

Trong đó:

• array1 - Phạm vi dữ liệu thứ nhất
• array2 - Phạm vi dữ liệu thứ hai

Kết quả của hàm CORREL có thể được hiểu như sau:

• Kết quả gần +1:Tương quan dương, nếu các giá trị trong mảng thứ nhất tăng thì các giá trị trong mảng thứ hai cũng tăng.
• Kết quả gần -1: Tương quan âm, nếu các giá trị trong mảng thứ nhất tăng thì các giá trị trong mảng thứ hai giảm.
• Kết quả gần 0: Không có tương quan hoặc tương quan yếu.

Lưu ý với hàm CORREL

Với cách thống kê trong Excel sử dụng hàm CORREL, bạn nhất định phải ghi nhớ các điều sau đây:

• Tương tự như các hàm tính toán phía trên, hàm CORREL chỉ chấp nhận các giá trị số, bao gồm các giá trị 0. Toàn bộ các giá trị còn lại trong phạm vi dữ liệu sẽ bị bỏ qua.
• Kích thước array1 và array2 cần phải trùng khớp, nếu không, hàm CORREL sẽ trả về lỗi #N/A.
• Nếu tham số array1 hoặc array2 trống, hoặc độ lệch chuẩn của 2 phạm vi dữ liệu này bằng 0, hàm CORREL sẽ trả về lỗi #DIV/0!.

Ví dụ sử dụng hàm CORREL

Chúng ta sẽ áp dụng cách thống kê trong Excel với hàm CORREL để trả lời câu hỏi về mối tương quan giữa mức ưu đãi giảm giá và số lượng mặt hàng tương ứng bán ra.

 

Hệ số tương quan hàm CORREL trả về xấp xỉ 0,9 nghĩa là càng áp dụng mức ưu đãi cao với nhiều sản phẩm, số lượng hàng bán ra cũng sẽ tăng lên.

Tổng kết

Như vậy, qua bài viết ngày hôm nay, chúng ta đã cùng nhau tìm hiểu 10 hàm thống kê trong Excel cơ bản nhất. Hy vọng các bạn đã nắm rõ cách dùng các hàm này để xử lý dữ liệu trên trang tính một cách chính xác và hiệu quả.

Để tích lũy thêm kiến thức về các công cụ Excel, hãy tham khảo ngay các bài viết khác trên blog Gitiho nhé. Chắc chắn các bài viết này có thể giúp bạn đơn giản hóa công việc trên Excel của mình đấy.

Gitiho xin cảm ơn bạn đọc và chúc bạn thành công!