Bạn đang loay hoay với hàng tá dữ liệu trong Excel? Bạn muốn thống kê, phân tích thông tin một cách chuyên nghiệp và hiệu quả hơn? Đừng lo, hàm thống kê trong Excel chính là “vị cứu tinh” cho bạn! Hãy cùng Tingame360 khám phá bộ hàm thống kê Excel cực chi tiết và dễ hiểu cho người mới bắt đầu qua bài viết dưới đây nhé!
I. Thống kê trong Excel là gì? Ứng dụng “thần thánh” của hàm thống kê Excel?
1. Định nghĩa
Thống kê là phương pháp thu thập, xử lý, trình bày và phân tích dữ liệu nhằm đưa ra kết luận chính xác và khách quan. Trong Excel, thống kê được thực hiện dễ dàng và nhanh chóng nhờ vào bộ hàm đa dạng và mạnh mẽ.
2. Ứng dụng của hàm thống kê trong Excel
- Thực hiện các phép tính toán cơ bản như tính tổng (SUM), tìm giá trị lớn nhất (MAX), giá trị nhỏ nhất (MIN),… một cách nhanh chóng và chính xác.
- Nâng cao hiệu quả làm việc với Excel, thao tác chuyên nghiệp và tiết kiệm thời gian.
- Kết hợp linh hoạt với các hàm khác để giải quyết các bài toán phức tạp hơn.
Ví dụ minh họa hàm COUNTIFS
II. Tổng hợp các hàm thống kê trong Excel
1. Hàm COUNT: Đếm dữ liệu kiểu số
Hàm COUNT dùng để đếm số ô chứa dữ liệu kiểu số trong một vùng hoặc toàn bộ bảng dữ liệu.
Cú pháp: =COUNT(Value1, Value2,....)
Trong đó:
- Value 1: Giá trị đầu tiên hoặc vùng chọn để đếm ô giá trị.
- Value 2: Tham số không bắt buộc, có thể đếm tối đa 255 tham số, ô tham chiếu hoặc vùng chọn bổ sung.
Ví dụ: Để đếm số ô chứa số trong vùng A1:A10, ta sử dụng công thức =COUNT(A1:A10).
2. Hàm COUNTA: Đếm tất cả các ô có chứa dữ liệu
Hàm COUNTA sẽ đếm tất cả các ô chứa dữ liệu (bao gồm cả số, văn bản, ngày tháng…) trong một phạm vi nhất định.
Cú pháp: =COUNTA(Value1, [value2], [value3],...)
Trong đó:
- Value1: Vùng dữ liệu cần đếm (bắt buộc).
- Value2, Value3: Các tùy chọn bổ sung, tối đa 255 đối số.
Ví dụ: Để đếm số ô chứa dữ liệu trong vùng A1:B10, ta sử dụng công thức =COUNTA(A1:B10).
3. Hàm COUNTIF: Đếm dữ liệu theo 1 điều kiện
COUNTIF là hàm đếm có điều kiện, cực kỳ hữu ích khi bạn muốn thống kê số lượng dữ liệu thỏa mãn một tiêu chí nhất định.
Cú pháp: =COUNTIF(range;criteria)
Trong đó:
- Range: Phần vùng muốn đếm dữ liệu (bắt buộc).
- Criteria: Điều kiện để đếm các giá trị trong vùng (bắt buộc), có thể là số, biểu thức, tham chiếu ô hoặc chuỗi văn bản.
Ví dụ: Để đếm số ô chứa giá trị “Apple” trong vùng A1:A10, ta sử dụng công thức =COUNTIF(A1:A10,"Apple").
4. Hàm COUNTIFS: Đếm ô tính với nhiều điều kiện
COUNTIFS là phiên bản nâng cao của COUNTIF, cho phép bạn đếm số ô thỏa mãn đồng thời nhiều điều kiện.
Cú pháp: =COUNTIFS(Criteria_range1,Criteria1,[Criteria_range2,Criteria2],...)
Trong đó:
- Criteria_range1: Phạm vi đầu tiên để đánh giá.
- Criteria1: Điều kiện áp dụng cho phạm vi đầu tiên.
- Criteria_range2, Criteria2: Các phạm vi và điều kiện bổ sung (tùy chọn).
Ví dụ: Để đếm số học sinh đạt điểm Toán và Văn đều từ 8 trở lên, ta dùng công thức =COUNTIFS(C2:C10,">=8",D2:D10,">=8") (giả sử cột C là điểm Toán, cột D là điểm Văn).
5. Hàm COUNTBLANK: Đếm các ô rỗng trong Excel
COUNTBLANK giúp bạn nhanh chóng xác định số lượng ô rỗng trong một vùng dữ liệu.
Cú pháp: COUNTBLANK(Range)
Trong đó:
- Range: Mảng được chọn để đếm số các ô rỗng.
Ví dụ: Để đếm số ô rỗng trong vùng A1:A10, ta dùng công thức =COUNTBLANK(A1:A10).
6. Hàm SUM: Tính tổng trong Excel
Hàm SUM – “anh cả” trong làng tính toán, cho phép bạn cộng tất cả các giá trị số trong một vùng dữ liệu.
Cú pháp: =SUM(number1, [number2],...)
Trong đó:
- Number1, Number2: Những số hoặc dãy số cần tính tổng.
Ví dụ: Để tính tổng giá trị các ô từ A1 đến A10, ta dùng công thức =SUM(A1:A10).
7. Hàm SUMIF: Tính tổng theo một điều kiện
SUMIF giúp bạn tính tổng các giá trị trong một vùng dữ liệu thỏa mãn một điều kiện nhất định.
Cú pháp: =SUMIF(range, criteria,sum_range)
Trong đó:
- Range: Vùng chứa các ô điều kiện.
- Criteria: Điều kiện để thực hiện hàm.
- Sum_range: Vùng cần tính tổng.
Ví dụ: Để tính tổng doanh thu từ các sản phẩm có tên là “Laptop”, ta sử dụng công thức =SUMIF(A2:A10,"Laptop",B2:B10) (giả sử cột A là tên sản phẩm, cột B là doanh thu).
8. Hàm SUMIFS: Thống kê tổng theo nhiều điều kiện
SUMIFS là phiên bản mở rộng của SUMIF, cho phép bạn tính tổng các ô thỏa mãn nhiều điều kiện cùng lúc.
Cú pháp: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
Trong đó:
- Sum_range: Các ô cần tính tổng.
- Criteria_range1: Phạm vi cần được đánh giá bằng điều kiện.
- Criteria1: Điều kiện dưới dạng một số, biểu thức, tham chiếu ô.
- Criteria_range2,criteria2,…: Tùy chọn các phạm vi và điều kiện bổ sung.
Ví dụ: Để tính tổng doanh thu từ các sản phẩm là “Laptop” và có màu “Silver”, ta sử dụng công thức =SUMIFS(C2:C10,A2:A10,"Laptop",B2:B10,"Silver") (giả sử cột A là tên sản phẩm, cột B là màu sắc, cột C là doanh thu).
9. Hàm AVERAGE: Tính trung bình trong Excel
Hàm AVERAGE giúp tính toán giá trị trung bình của một dãy số.
Cú pháp: =AVERAGE(number1;number2;...numbern)
Trong đó:
- Number 1: Số đầu tiên cần tính trung bình.
- Number 2: Số thứ hai cần tính trung bình.
- Number n: Số cuối cùng cần tính trung bình.
Ví dụ: Để tính điểm trung bình của 3 môn Toán, Lý, Hóa trong các ô A1, A2, A3, ta dùng công thức =AVERAGE(A1:A3).
10. Hàm AVERAGEIF: Tính trung bình có điều kiện trong Excel
AVERAGEIF giúp tính toán giá trị trung bình của các ô thỏa mãn một điều kiện nhất định.
Cú pháp: =AVERAGEIF(range, criteria, [average_range])
Trong đó:
- Range: Một hoặc nhiều ô để tính giá trị trung bình.
- Criteria: Điều kiện lấy các số dùng để tính trung bình.
- Average_range: Vùng dữ liệu được chọn để dùng hàm AVERAGEIF (nếu bỏ qua thì mặc định chọn toàn bộ).
Ví dụ: Để tính điểm trung bình môn Toán của các bạn nữ trong lớp, ta sử dụng công thức =AVERAGEIF(C2:C10,"Nữ",D2:D10) (giả sử cột C là giới tính, cột D là điểm Toán).
11. Hàm AVERAGEIFS: Tính trung bình có nhiều điều kiện trong Excel
Tương tự như SUMIFS, AVERAGEIFS giúp tính toán giá trị trung bình của các ô thỏa mãn nhiều điều kiện cùng lúc.
Cú pháp: =AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)
Trong đó:
- Average_range: Một hoặc nhiều ô để tính giá trị trung bình.
- Criteria_range1: Phạm vi 1 đến 127 để đánh giá các tiêu chí liên quan.
- Criteria1: 1 đến 127 tiêu chí dưới dạng số, biểu thức, tham chiếu ô hoặc văn bản xác định các ô sẽ tính giá trị trung bình.
Ví dụ: Để tính điểm trung bình môn Toán của các bạn nữ học lớp 10A1, ta sử dụng công thức =AVERAGEIFS(D2:D10,C2:C10,"Nữ",B2:B10,"10A1") (giả sử cột B là lớp, cột C là giới tính, cột D là điểm Toán).
12. Hàm RANK: Xếp hạng trong Excel
Hàm RANK giúp bạn sắp xếp và phân hạng dữ liệu một cách nhanh chóng.
Cú pháp: =RANK(number,ref, [order])
Trong đó:
- Number: Giá trị cần xếp hạng trong khối.
- Ref: Danh sách, khối dữ liệu cần sắp xếp.
- Order: Thứ tự cần sắp xếp, kiểu sắp xếp (tăng dần hoặc giảm dần).
- Order = 0 (hoặc không có tham số này): xếp hạng từ cao xuống thấp.
- Order = 1: xếp hạng từ thấp lên cao.
Ví dụ: Để xếp hạng điểm Toán của học sinh trong cột A, ta sử dụng công thức =RANK(A1,$A$1:$A$10,0) (giả sử dữ liệu điểm nằm trong vùng A1:A10).
13. Hàm ROW: Tìm vị trí dòng trong Excel
Hàm ROW giúp xác định số thứ tự của một dòng cụ thể trong bảng tính.
Cú pháp: =ROW([reference])
Trong đó:
- Reference: Ô hoặc phạm vi ô mà bạn muốn trả về số hàng.
Ví dụ: Để biết ô A1 nằm ở dòng nào, ta dùng công thức =ROW(A1). Kết quả trả về là 1.
14. Hàm ROWS: Đếm số lượng dòng trong Excel
Hàm ROWS giúp bạn xác định số lượng dòng trong một vùng dữ liệu được chọn.
Cú pháp: =ROWS(array)
Trong đó:
- Array: Tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số hàng.
Ví dụ: Để đếm số dòng trong vùng A1:A10, ta sử dụng công thức =ROWS(A1:A10). Kết quả trả về là 10.
15. Hàm COLUMN: Tìm vị trí cột trong Excel
Hàm COLUMN giúp xác định số thứ tự của một cột cụ thể trong bảng tính.
Cú pháp: =COLUMN([reference])
Trong đó:
- Reference: Ô hoặc vùng mà bạn muốn trả về số cột.
Ví dụ: Để biết ô A1 nằm ở cột nào, ta dùng công thức =COLUMN(A1). Kết quả trả về là 1.
16. Hàm COLUMNS: Đếm số lượng cột trong Excel
Hàm COLUMNS giúp bạn xác định số lượng cột trong một vùng dữ liệu được chọn.
Cú pháp: =COLUMNS(array)
Trong đó:
- Array: Tham chiếu đến phạm vi một ô hoặc một vùng mà bạn muốn lấy số cột.
Ví dụ: Để đếm số cột trong vùng A1:C1, ta sử dụng công thức =COLUMNS(A1:C1). Kết quả trả về là 3.
17. Hàm MIN: Tìm giá trị nhỏ nhất
Hàm MIN giúp tìm giá trị nhỏ nhất trong một tập hợp các số.
Cú pháp: =MIN (number 1, number 2, ...)
Trong đó:
- Number1, Number2,…: Các đối số hoặc vùng dữ liệu.
Ví dụ: Để tìm giá trị nhỏ nhất trong các ô A1, B1 và C1, ta sử dụng công thức =MIN(A1,B1,C1).
18. Hàm MAX: Tìm giá trị lớn nhất
Hàm MAX giúp tìm giá trị lớn nhất trong một tập hợp các số.
Cú pháp: =MAX(number 1, number 2, ...)
Trong đó:
- Number1, Number2,…: Các đối số hoặc vùng dữ liệu.
Ví dụ: Để tìm giá trị lớn nhất trong các ô A1, B1 và C1, ta sử dụng công thức =MAX(A1,B1,C1).
III. Một số lỗi thường gặp khi sử dụng hàm trong Excel và cách khắc phục
1. Lỗi sai dấu chấm, dấu phẩy, dấu chấm phẩy
Nguyên nhân: Sử dụng sai dấu phân cách giữa các phần tử trong hàm.
Cách khắc phục: Sử dụng đúng dấu phân cách theo tiêu chuẩn của Excel hoặc chỉnh sửa lại định dạng vùng dữ liệu.
2. Lỗi không viết được số 0
Nguyên nhân: Do format của ô là kiểu số nhưng lại có chứa ký tự chữ cái, hoặc do chưa chuyển định dạng.
Cách khắc phục: Thêm ký tự dấu ‘ vào trước số 0 hoặc chuyển sang định dạng Text.
3. Lỗi #NUM!
Nguyên nhân:
- Công thức chứa đối số không phải là kiểu số.
- Hàm lặp đi lặp lại dẫn đến không thể tìm ra kết quả trả về.
- Hàm trả về kết quả là số quá lớn hoặc quá nhỏ vượt ngoài khả năng tính toán của Excel.
Cách khắc phục: Kiểm tra lại dữ liệu đầu vào, công thức và giới hạn của Excel.
4. Lỗi #VALUE!
Nguyên nhân: Lỗi hiển thị giá trị kết quả do sai công thức hoặc do các ô đang tham chiếu bị lỗi.
Cách khắc phục: Kiểm tra lại công thức, dữ liệu đầu vào và các ô được tham chiếu.
5. Lỗi #DIV/0!
Nguyên nhân: Lỗi phép tính khi chia cho số 0 hoặc sai hàm khi thực hiện phép chia cho số 0.
Cách khắc phục: Kiểm tra lại dữ liệu và công thức, đảm bảo không chia cho số 0.
6. Lỗi #N/A!
Nguyên nhân: Lỗi không tìm thấy giá trị hoặc công thức không tìm thấy giá trị.
Cách khắc phục: Kiểm tra lại dữ liệu, điều kiện và phạm vi tìm kiếm của công thức.
Kết luận
Trên đây là tổng hợp các hàm thống kê trong Excel cực chi tiết, có bài tập minh họa dễ hiểu. Hy vọng bài viết sẽ giúp bạn tự tin chinh phục “núi” dữ liệu trong Excel. Đừng quên chia sẻ bài viết nếu bạn thấy hữu ích và ghé thăm Tingame360 thường xuyên để cập nhật thêm nhiều thủ thuật hay ho khác nhé!
