Trong quá trình học Excel online chắc chắn bạn sẽ được tiếp xúc và biết đến các hàm thống kê trong Excel từ cách sử dụng đến công thức và những lỗi hàm thường gặp và cách giải quyết. Cùng khám phá để trở thành một cao thủ Excel nhé!
Xem thêm: Cách sử dụng hàm đếm có điều kiện trong Excel chi tiết
Xem thêm: Hướng dẫn cách sử dụng hàm Len trong Excel – kèm ví dụ
Mục lục bài viết
Mục đích khi sử dụng hàm thống kê là gì?
Định nghĩa
Các hàm thống kê là hệ thống các phương pháp được sử dụng để thu nhập, tổng hợp, trình bày và tính toán. Các hàm này được sử dụng nhằm mục đích để phân tích dữ liệu và đưa ra các kết luận cho người dùng một cách nhanh chóng.
Các hàm thống kê được sử dụng mục đích gì?
- Hàm được sử dụng để tính toán các giá trị một cách dễ dàng như: Tính tổng, giá trị lớn nhất, giá trị nhỏ nhất,…
- Người sử dụng Excel sẽ thao tác nhanh chóng và chuyên nghiệp hơn
- Kết quả trả về có độ chính xác cao
- Kết hợp điều nhiều hàm và nhiều điều kiện để hỗ trợ công việc và xử lý dữ liệu
Tổng hợp các hàm thống kê trong Excel
Hàm COUNT: Hàm đếm dữ liệu kiểu số
Định nghĩa
Trong Excel, hàm Count được sử dụng để thực hiện đếm các ô trong một vùng dữ liệu hoặc toàn bộ bảng dữ liệu.
Cú pháp hàm COUNT
=COUNT(Value1, Value2,….).
Trong đó:
- Value 1: Là giá trị tham số đầu tiên được sử dụng để tham chiếu hoặc lựa chọn vùng chọn để đếm ô giá trị.
- Value 2: Là giá trị tham số không bắt buộc nhưng chỉ có thể sử dụng để đếm tối đa 255 tham số. Với các ô tham chiếu hoặc vùng chọn bổ sung mà bạn muốn đếm giá trị ô mong muốn
Ví dụ minh hoạ
Bước 1: Lựa chọn ô trống trong bảng tính nhập công thức sau:
=COUNT(E4:E7)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm COUNTA: Hàm đếm tất cả các ô chứa dữ liệu
Định nghĩa
Hàm Counta được sử dụng để đếm những ô tham chiếu có chứa các dữ liệu trong một phạm vi dữ liệu nhất định
Cú pháp hàm COUNTA
=COUNTA(Value1, [value2], [value3],…)
Trong đó:
- Value1: Là Giá trị đối số bắt buộc phải có trong hàm và là vùng dữ liệu cần đếm trong bảng tính
- Value2 và Value3: Là các tùy chọn và được giới hạn tối đa 255 đối số
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện đếm số học sinh đạt điểm trong bảng.
Bước 1: Tạo thêm dòng Đếm số học sinh có xếp loại Đạt trên trang tính. Chọn ô trống liền kề nhập công thức sau:
=COUNTA(F2:F12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu có 7 học sinh xếp loại Đạt.
Hàm COUNTIF: Hàm đếm dữ liệu trong Excel theo điều kiện
Định nghĩa
Hàm Countif là một hàm có thể thực hiện để đếm có điều kiện đi kèm trong Excel. Hàm này thường được sử dụng với các bảng dữ liệu thống kê.
Cú pháp hàm COUNTIF
=COUNTIF(range;criteria)
Trong đó:
- Range: Vùng dữ liệu muốn đếm bắt buộc phải có. Giá trị có thể chứa số, mảng hoặc tham chiếu có chứa số trong hàm
- Criteria: Đếm các giá trị trong range (Bắt buộc phải có trong hàm). Giá trị này có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện đếm số học sinh có điểm đạt trung bình trên 8 trở lên.
Bước 1: Tạo thêm dòng Đếm số học sinh có cột Trung bình từ 8 trở lên trên trang tính. Chọn ô trống liền kề nhập công thức sau:
=COUNTIF(E2:E12,”>=8″)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu có 4 học sinh xếp loại đạt điều kiện.
Hàm COUNTBLANK: Hàm đếm các ô rỗng trong Excel
Định nghĩa
Hàm COUNTBLANK được sử dụng để đếm các số ô rỗng trong một vùng kín và lựa chọn mảng trước.
Cú pháp hàm COUNTBLANK
COUNTBLANK(Range)
Trong đó:
- Range: Là mảng dữ liệu được chọn để thực hiện đếm số các ô rỗng trong đó.
Ví dụ minh hoạ
Dựa vào bảng dữ liệu dưới đây tìm ra số ô rỗng trong bảng dữ liệu
Bước 1: Lựa chọn ô trống bất kỳ nhập công thức sau vào trang tính Excel
=COUNTBLANK(E4:E8)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm COUNTIFS: Hàm đếm ô tính với nhiều điều kiện
Định nghĩa
Hàm Countifs là một hàm được sử dụng trong Excel để đếm số ô trong phạm vi khớp với điều kiện được cung cấp và hoàn toàn có thể áp dụng nhiều hơn các điều kiện kết hợp đi kèm và nhiều hơn trong một phạm vi
Cú pháp hàm COUINTIFS
=COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],…)
Trong đó:
- range1: Là phạm vi đánh giá đầu tiên
- criteria1: Là phạm vi xác định đầu tiên áp dụng cho điều kiện đầu tiên.
- range2: Là phạm vi để đánh giá thứ hai, điều kiện bổ sung cho hàm
- criteria2: Là phạm vi xác định áp dụng điều kiện thứ 2
Ví dụ minh hoạ
Dựa vào bảng dữ liệu dưới đây tìm số học sinh có cả 6 điểm trong 2 phần thi vừa rồi được thống kê trong bảng.
Bước 1: Lựa chọn ô trống bất kỳ nhập công thức sau vào trang tính Excel
=COUNTIFS(C4:C8,6,D4:D8,6)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm SUM: Hàm tính tổng
Định nghĩa
Hàm SUM là hàm tính cơ bản được sử dụng rất nhiều để tính tổng tất cả các số có trong dãy ô dữ liệu
Cú pháp hàm
=SUM(number1, [number2],…)
Trong đó:
Number1 và Number2 là những số, dãy số hoặc phạm vi dữ liệu sẽ tính tổng
Ví dụ minh hoạ:
Sử dụng hàm SUM để tính tổng của 3 số: 20, 40, 60
Nhập công thức:
=SUM(20,60,40)
Nhấn Enter để xem kết quả hiển thị:
Hàm SUMIF: Hàm thống kê theo điều kiện
Định nghĩa
Hàm SUMIF là hàm tính tổng có điều kiện trong Excel. Hàm này được sử dụng khi cần tính tổng một vùng dữ liệu trong bảng tính kèm theo điều kiện đi kèm cho trước.
Cú pháp hàm SUMIF
=SUMIF(range, criteria,sum_range)
Trong đó:
- Range: Vùng dữ liệu có chứa các ô điều kiện thoả mãn
- Criteria: Điều kiện để thực hiện hàm này
- Sum_range: Vùng dữ liệu cần tính tổng
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện tính tổng giá trị học bổng dành cho học sinh có điểm toán là 10.
Bước 1: Tạo thêm dòng Tính tổng giá trị học bổng dành cho học sinh có điểm toán là 10 trên trang tính. Chọn ô trống liền kề nhập công thức sau:
=SUMIF(B2:B12,”=10″,G2:G12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm SUMIFS: Hàm thống kê theo nhiều điều kiện
Định nghĩa
Hàm SUMIFS là hàm được sử dụng để tính tổng các ô dữ liệu hoặc cách vùng, phạm vi dữ liệu thoả mãn một hoặc nhiều điều kiện đi kèm trong Excel
Cú pháp hàm SUMIFS
=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)
Trong đó:
- Sum_range: Ô hoặc vùng dữ liệu cần tính tổng. Bao gồm các số liệu hoặc tên, phạm vi dữ liệu hoặc ô tham chiếu có chứa các số cần tính tổng
- Criteria_range1: Phạm vi dữ liệu đánh giá bằng điều kiện đi kèm
- Criteria1: Điều kiện dưới dạng một số, biểu thức hoặc tham chiếu ô cho hàm
- Criteria_range2,criteria2,… : Các phạm vi và điều kiện bổ sung khác đi kèm
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện tính tổng giá trị học bổng dành cho học sinh có điểm của 3 môn toán, lý, hóa từ 8 trở lên.
Bước 1: Tạo thêm dòng Tính tổng giá trị học bổng dành cho học sinh có điểm của 3 môn từ 8 trở lên trên trang tính. Chọn ô trống liền kề nhập công thức sau:
=SUMIFS(G2:G12,B2:B12,”>=8″,C2:C12,”>=8″,D2:D12,”>=8″)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm AVERAGE: Hàm tính trung bình trong Excel
Định nghĩa
Hàm AVERAGE là hàm được sử dụng để tính trung bình trong Excel
Cú pháp hàm AVERAGE
=AVERAGE(number1;number2;…numbern)
Trong đó:
- Number 1: Số hoặc ô dữ liệu đầu tiên cần tính trung bình cộng
- Number 2: Số hoặc ô dữ liệu thứ 2 cần tính trung bình cộng
- Number n: Số hoặc ô dữ liệu cuối cùng cần tính trung bình cộng
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện tính điểm trung bình cột điểm lý.
Bước 1: Tạo thêm dòng Tính điểm trung bình cột điểm lý. Chọn ô trống liền kề nhập công thức sau:
=AVERAGE(C2:C12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm AVERAGEIF: Hàm tính trung bình có điều kiện
Định nghĩa
Hàm AVERAGEIF trong Excel được sử dụng để tính trung bình kèm theo một điều kiện của các ô dữ liệu dựa trên các điều kiện có sẵn.
Cú pháp hàm AVERAGEIF
=AVERAGEIF(range, criteria, [average_range])
Trong đó:
- Range: Đây là tham số bắt buộc gồm 1 ô hoặc vùng dữ liệu để thực hiện tính giá trị trung bình. Tham số có thể bao gồm: số hoặc tên, mảng hoặc tham chiếu có chứa số
- Criteria (Bắt buộc): Điều kiện cho hàm sử dụng dùng để tính trung bình.
- Average_range (Tùy chọn): Vùng dữ liệu được chọn nếu bỏ qua thì hàm mặc định chọn toàn bộ trong bảng tính
Ví dụ minh hoạ
Bước 1: Lựa chọn ô trống trong bảng tính nhập công thức sau:
=AVERAGEIF(D2:D7;”>5.000.000″)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm RANK: Hàm xếp hạng trong Excel
Định nghĩa
Hàm RANK trong Excel là hàm được sử dụng để sắp xếp và phần hàng dữ liệu, số liệu trong bảng tính
Cú pháp hàm RANK
=RANK(number,ref, [order])
Trong đó:
- Number: Giá trị số liệu cần xếp hạng
- Ref: Danh sách hoặc khối dữ liệu cần sắp xếp thứ hạng
- Order: Sắp xếp tăng dần hoặc giảm dần. Trong trường hợp Order = 0 (hoặc không có tham số này) thì hàm sẽ sắp xếp từ cao xuống thấp. Còn order = 1 sẽ sắp xếp từ thấp lên cao.
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện xếp hạng điểm 8 trong cột điểm trung bình.
Bước 1: Tạo thêm dòng Xếp hạng điểm 8 trong cột điểm trung bình. Chọn ô trống liền kề nhập công thức sau:
=RANK(E12,E2:E12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm AVERAGEIFS: Hàm tính trung bình có điều kiện trong Excel
Định nghĩa
Hàm AVERAGEIFS được sử dụng để tính giá trị trung bình theo nhiều điều kiện của tất cả các ô dữ liệu dựa theo các điều kiện có sẵn.
Cú pháp hàm AVERAGEIFS
=AVERAGEIFS(range, criteria, [average_range])
Trong đó:
- Range (Bắt buộc): Một ô hoặc nhiều ô (Vùng dữ liệu) để thực hiện tính giá trị trung bình. Có thể bao gồm các số, tên, mảng hoặc tham chiếu có chứa số trong bảng tính
- Criteria_range1 (Bắt buộc): Là các đối số nằm trong phạm vi 1 đến 127 để đánh giá các tiêu chí liên quan
- Criteria1 (Bắt buộc): Các đối số từ 1 đến 127 tiêu chí dưới dạng số, biểu thức, tham chiếu ô. Hoặc có thể là các văn bản xác định có các ô sẽ tính giá trị trung bình trong hàm.
Ví dụ minh hoạ:
Cho danh sách dữ liệu gồm có: Họ và tên, GIới tính, Điểm HK1 và Điểm HK2. Sử dụng các hàm tính trong Excel để thực hiện tính điểm trung bình HK1 của những học sinh có điểm HK1 lớn hơn 6 và nhỏ hơn 9.
Bước 1: Tạo thêm dòng Điểm trung bình trên bảng dữ liệu rồi nhập công thức:
=AVERAGEIFS(D2:D7;D2:D7;”>6″;D2:D7;”<>
Bước 2: Nhấn Enter để xem kết quả hiển thị:
Hàm ROW: Hàm tìm vị trí dòng trong Excel
Định nghĩa
Hàm ROW được sử dụng để tìm ra vị trí hàng của các ô đang được tham chiếu hoặc có thể là hàng đầu tiên của mảng tham chiếu dễ dàng thực hiện.
Cú pháp hàm ROW
=ROW([reference])
Trong đó:
- Reference: Là ô dữ liệu hoặc phạm vi ô (Vùng dữ liệu) mà bạn muốn trả về số hàng
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện xác định vị trí hàng đầu tiên của dãy ô A1:G12.
Bước 1: Tạo thêm dòng Xác định vị trí hàng đầu tiên trong bảng dữ liệu. Chọn ô trống liền kề nhập công thức sau:
=ROW(A1:G12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm ROWS: Hàm đếm số lượng dòng trong Excel
Định nghĩa
Hàm ROWS được sử dụng để tính nhanh toàn bộ các số lượng hàng có trong dữ liệu đang được tham chiếu trên bảng tính
Cú pháp hàm ROWS
=ROWS(array)
Trong đó:
- Array: Là ô dữ liệu hoặc phạm vi ô (Vùng dữ liệu) mà bạn muốn lấy số hàng
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện xác định tổng số hàng của dãy ô A1:G12
Bước 1: Tạo thêm dòng Xác định vị trí số hàng trong bảng dữ liệu. Chọn ô trống liền kề nhập công thức sau:
=ROWS(A1:G12)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm COLUMN: Hàm tìm giá trị cột
Định nghĩa
Hàm COLUMN trong Excel là hàm được sử dụng để tìm nhanh vị trí cột của ô hoặc vùng dữ liệu tham chiếu trong hàm tính
Cú pháp hàm COLUMN
=COLUMN([reference])
Trong đó:
- Reference: Là ô dữ liệu hoặc vùng dữ liệu mà bạn muốn trả về số cột trong trang tính
Ví dụ minh hoạ:
Cho bảng số liệu sau gồm danh sách các học sinh gồm có : Điểm thi, điểm trung bình, xếp loại học sinh. Sử dụng hàm trong Excel để thực hiện xác định vị trí cột của ô E1.
Bước 1: Tạo thêm dòng Xác định cột điểm trung bình. Chọn ô trống liền kề nhập công thức sau:
=COLUMN(E1)
Bước 2: Nhấn Enter để xem kết quả. Kết quả hiển thị trong bảng dữ liệu như sau:
Hàm COLUMNS: Hàm đếm số lượng cột
Định nghĩa
Hàm COLUMNS trong Excel là hàm trả về số cột trong mảng dữ liệu hoặc ô tham chiếu trên bảng tính.
Cú pháp hàm COLUMNS:
=COLUMNS(array)
Trong đó:
- Array: Ô hoặc phạm vi một ô, một vùng mà bạn muốn lấy số cột trong bảng tính
Ví dụ minh hoạ
Bước 1: Lựa chọn ô trống trong bảng tính nhập công thức sau:
=COLUMNS(A3:F7)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm MIN: Hàm tìm giá trị nhỏ nhất
Định nghĩa
Hàm MIN là hàm tìm giá trị nhỏ nhất trong vùng dữ liệu hoặc tập giá trị cho trước trong Excel.
Cú pháp hàm MIN
=MIN (number 1, number 2, …)
Trong đó:
- Number1, Number2, …: Là các đối số, các ô dữ liệu hoặc vùng dữ liệu trên bảng tính cần tìm giá trị nhỏ nhất
Ví dụ minh hoạ
Cho bảng số liệu dưới đây gồm điểm trung bình của các bộ môn Toán Lý Hoa. Sử dụng hàm MIN trong Excel để tìm ra lớp có điểm số thấp nhất.
Bước 1: Tạo dòng MIN trong bảng dữ liệu trên và nhập công thức
=MIN(B2:B5)
Bước 2: Nhấn Enter để xem kết quả hiển thị
Hàm MAX: Hàm tìm giá trị lớn nhất
Định nghĩa
Hàm MAX là hàm tìm giá trị lớn nhất trong vùng dữ liệu hoặc tập giá trị cho trước trong Excel.
Cú pháp hàm MAX
=MAX(number 1, number 2, …)
Trong đó:
- Number1, Number2, …: Là các đối số, các ô dữ liệu hoặc vùng dữ liệu trên bảng tính cần tìm giá trị lớn nhất
Ví dụ minh hoạ:
Cho bảng số liệu dưới đây gồm điểm trung bình của các bộ môn Toán Lý Hoa. Sử dụng hàm MAX trong Excel để tìm ra lớp có điểm số cao nhất.
Bước 1: Tạo dòng MAX trong bảng dữ liệu trên và nhập công thức
=MAX(B2:B4)
Bước 2: Nhấn Enter để xem kết quả hiển thị như sau:
Một số lỗi thường gặp khi sử dụng hàm thống kê trong Excel
Lỗi #N/A!
Lỗi #N/A! trong Excel là lỗi không tìm thấy giá trị phù hợp. Nói dễ hiểu hơn là lỗi được trả về khi công thức không tìm thấy giá trị phù hợp mà hàm đang tìm kiếm
Lỗi #DIV/0!
Lỗi #DIV/0! trong Excel là lỗi phép tính trong hàm khi thực hiện chia cho số 0 hay sai hàm khi thực hiện phép chia cho số 0 trong phép tính.
Lỗi #VALUE!
Lỗi #VALUE! trong Excel là lỗi hiển thị giá trị kết quả bị lỗi trong quá trình sử dụng hàm thống kê trong Excel tính toán thực hiện trên trang tính. Phần lớn lỗi thường xảy ra do quá trình nhập công thức của người dùng bị sau hoặc do các ô tham chiếu chưa dữ liệu lỗi.
Lỗi #NUM!
Lỗi #NUM! là kiểu dữ liệu số trong Excel thường xảy ra do 3 nguyên nhân chính bao gồm như sau:
- Công thức chứa đối số có dữ liệu là kiểu số không phù hợp với hàm tính
- Sử dụng hàm lặp đi lặp lại khiến cho hàm không tìm thấy kết quả trả về phù hợp
- Sử dụng hàm có kết quả trả về là số dữ liệu quá lớn hoặc quá nhỏ vượt ngoài khả năng tính toán của hàm Excel được cài đặt sẵn
Lỗi không viết được số 0
Lỗi không viết được số 0 xảy ra do không có ký tự phụ hoặc chưa chuyển được định dạng phù hợp
Để khắc phục lỗi trên bạn cần thêm ký tự dấu ‘ vào trước số 0 trong ô tính. Hoặc có thể chuyển sang định dạng text. Bạn chỉ cần làm theo từng bước giống ảnh minh hoạ dưới đây.
Lỗi sai dấu chấm, dấu phẩy, dấu chấm phẩy
Lỗi sai dấu chấm, dấu phẩy, dấu chấm phẩy xảy ra do sử dụng sai dấu phân cách giữa các phần tử khi sử dụng hàm thống kê trong Excel.
Để khắc phục được lỗi này người dùng cần sử dụng đúng dấu phân cách hoặc chỉnh sửa lại định dạng phù hợp cho hàm.
Tổng kết
Hy vọng bài viết đã chia sẻ cho bạn toàn bộ những kiến thức cần thiết về hàm thống kê trong Excel. Bài viết chia sẻ chia tiết về từng hàm cùng cách sử dụng và các ví dụ minh hoạ chi tiết giúp bạn dễ hiểu, nắm bắt. Ngoài ra còn rất nhiều những bài viết chia sẻ kiến thức đa dạng về tin học văn phòng khác giúp bạn nhanh chóng trở thành chuyên gia. Khám phá ngay hôm nay!
