Các hàm tính trung bình trong Excel cơ bản như AVERAGE đã quá quen thuộc với người dùng. Tuy nhiên, khi xử lý dữ liệu phức tạp, bạn có thể cần phải sử dụng đến các hàm trung bình nâng cao trong Excel để phân tích chi tiết hơn. Những hàm này không chỉ giúp tính toán trung bình cộng thông thường mà còn có thể áp dụng điều kiện, trọng số và lọc ra các giá trị đặc biệt. Trong bài viết này, HỌC TIN sẽ hướng dẫn bạn 7 hàm tính trung bình nâng cao kèm công thức và ví dụ cụ thể, giúp bạn tận dụng tối đa khả năng xử lý dữ liệu trong Excel.
5 Hàm tính trung bình cộng nâng cao trong Excel
Để khai thác tối đa tiềm năng của hàm trung bình trong Excel, bạn không chỉ dừng lại ở hàm AVERAGE mà còn có thể áp dụng các công thức khác nhằm đáp ứng nhiều yêu cầu tính toán phức tạp hơn. Dưới đây là 5 hàm tính trung bình cộng trong Excel nâng cao, giúp bạn dễ dàng xử lý dữ liệu có điều kiện, chọn lọc giá trị theo tiêu chí và tính toán trung bình cho các nhóm giá trị đặc biệt trong bảng dữ liệu của mình.
Hàm tính trung bình cộng không dùng AVERAGE
Khi cần tính trung bình cộng mà không sử dụng hàm AVERAGE, bạn có thể dùng kết hợp hàm SUM và hàm COUNT để đạt kết quả tương tự.
Công thức: =SUM(range)/COUNT(range). Trong đó, range là phạm vi các ô chứa giá trị cần tính trung bình.
Ví dụ: Bạn có một bảng danh sách học sinh kèm theo điểm số của từng môn học. Để tính điểm trung bình của học sinh bằng hàm SUM và COUNT, bạn thực hiện như sau:
- Bước 1: Chọn vào ô trống nơi bạn muốn hiển thị kết quả tính điểm trung bình của học sinh đầu tiên. Ví dụ: ô I8.
- Bước 2: Trong ô I8, nhập công thức sau: =SUM(E8:H8)/COUNT(E8:H8). Trong đó:
- E8:H8: Phạm vi điểm số của 4 môn học để tính trung bình môn.
- SUM(E8:H8): Tính tổng tất cả các giá trị trong các ô từ E8 đến H8.
- COUNT(E8:H8): Đếm số ô có chứa giá trị số trong phạm vi từ E8 đến H8, bỏ qua các ô trống.
- Bước 3: Nhấn Enter để Excel tính toán và hiển thị kết quả trung bình cộng cho các giá trị trong phạm vi E8 đến H8.
- Bước 4: Để tính điểm trung bình cho các học sinh còn lại trong danh sách, bạn chỉ cần di chuột đến góc dưới bên phải của ô I8 cho đến khi xuất hiện dấu cộng (+). Sau đó, nhấn giữ chuột và kéo thả xuống các ô còn lại trong cột. Excel sẽ tự động tính trung bình cộng cho các phạm vi tương ứng.
Xem thêm: 6 Cách Gộp Ô Trong Excel Đảm Bảo Không Mất Dữ Liệu
Hàm tính trung bình cộng với một điều kiện
Hàm AVERAGEIF trong Excel là một công cụ hữu ích để tính trung bình của các giá trị trong một phạm vi dựa trên một điều kiện cụ thể. Đây là một trong những hàm trung bình nâng cao trong Excel, giúp bạn dễ dàng tính toán trung bình chỉ khi các giá trị thỏa mãn điều kiện nhất định.
Cú pháp hàm AVERAGEIF: =AVERAGEIF(range, criteria, [average_range]). Trong đó:
- range: Là phạm vi các ô mà bạn muốn kiểm tra điều kiện.
- criteria: Là điều kiện mà các giá trị trong phạm vi phải thỏa mãn.
- [average_range] (tùy chọn): Là phạm vi chứa các giá trị bạn muốn tính trung bình. Nếu không chỉ định, hàm AVERAGEIF sẽ tính trung bình trực tiếp trên phạm vi range.
Ví dụ: Giả sử bạn có một bảng danh sách điểm của các học sinh trong nhiều môn học. Bạn muốn tính điểm trung bình môn của mỗi học sinh, với điều kiện điểm số mỗi môn phải lớn hơn 5. Trong trường hợp, bạn sẽ áp dụng hàm trung bình cộng trong Excel thỏa một điều kiện cụ thể là AVERAGEIF. Các bước thực hiện như sau:
- Bước 1: Chọn vào ô trống nơi bạn muốn hiển thị kết quả tính điểm trung bình của học sinh đầu tiên. Ví dụ: ô H10.
- Bước 2: Trong ô H10, nhập công thức sau: =AVERAGEIF(D10:G10, “>5”). Trong đó:
- D10:H10: Phạm vi điểm số của 4 môn học để tính trung bình môn.
- “>5”: Điều kiện mà các giá trị trong phạm vi D10:G10 phải thỏa mãn. Cụ thể là điểm số mỗi môn học lớn hơn 5.
- Bước 3: Nhấn Enter để Excel tính toán và hiển thị kết quả trung bình cộng cho các điểm số lớn 5 trong phạm vi D10 đến H10.
- Bước 4: Để tính điểm trung bình cho các học sinh tiếp theo trong danh sách, bạn chỉ cần di chuột đến góc dưới bên phải của ô H10 cho đến khi xuất hiện dấu cộng (+). Sau đó, nhấn giữ chuột và kéo thả xuống để áp dụng công thức cho các ô còn lại trong cột. Excel sẽ tự động tính trung bình cộng cho các phạm vi tương ứng.
Hàm tính trung bình cộng với nhiều điều kiện
Để tính trung bình cộng với nhiều điều kiện trong Excel, bạn có thể sử dụng hàm AVERAGEIFS. Đây là một trong những hàm trung bình nâng cao trong Excel, cho phép bạn tính trung bình các giá trị trong phạm vi chỉ khi các giá trị đó thỏa mãn tất cả các điều kiện được chỉ định.
Cú pháp hàm AVERAGEIFS: =AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
Trong đó:
- average_range: Phạm vi chứa các giá trị mà bạn muốn tính trung bình.
- criteria_range1, criteria_range2,…: Các phạm vi chứa các giá trị mà bạn muốn kiểm tra điều kiện.
- criteria1, criteria2,…: Các điều kiện tương ứng với các phạm vi kiểm tra.
Ví dụ: Giả sử, bạn có danh sách bảng điểm trung bình của học sinh với các môn học Toán, Lý, Hóa và Văn như dưới đây. Hãy tính điểm trung bình điểm của các học sinh có điểm môn Toán lớn hơn 5 và điểm môn Văn lớn hơn 5. Trong trường hợp, bạn sẽ áp dụng hàm tính trung bình có điều kiện trong Excel AVERAGEIFS. Các bước thực hiện như sau:
- Bước 1: Chọn vào ô trống nơi bạn muốn hiển thị kết quả. Ví dụ: ô F17.
- Bước 2: Nhập vào ô F17 công thức sau: =AVERAGEIFS(H8:H14,D8:D14,”>5″,G8:G14,”>5″). Trong đó:
- H18:H14: Phạm vi điểm số mà bạn muốn tính trung bình.
- D8:D14: Phạm vi điểm Toán của mỗi học sinh.
- G8:G14: Phạm vi điểm Văn của mỗi học sinh.
- “>5”: Điều kiện mà các giá trị trong phạm vi D8:D14 và G8:G14 phải thỏa mãn. Cụ thể là điểm số môn Toán và Văn phải lớn hơn 5.
- Bước 3: Nhấn Enter. Excel sẽ tính điểm trung bình điểm của các học sinh mà điểm Toán và điểm Văn đều lớn hơn 5.
Xem thêm: 5 Cách Cố Định Dòng Và Cột Trong Excel Dễ Dàng Và Hiệu Quả
Hàm tính trung bình cộng cho top N giá trị lớn nhất
Trong Excel, để tính trung bình của các giá trị lớn nhất từ một dãy số, bạn có thể sử dụng kết hợp hàm AVERAGE và hàm LARGE. Hàm LARGE sẽ giúp bạn lấy ra các giá trị lớn nhất trong một phạm vi nhất định và sau đó hàm AVERAGE sẽ tính trung bình các giá trị đó.Đây là một trong những hàm trung bình nâng cao trong Excel, giúp bạn dễ dàng tính toán các giá trị trung bình cho các số liệu lớn nhất mà không cần phải lọc thủ công.
Cú pháp hàm LARGER: =LARGE(array, k). Hàm này trả về giá trị lớn thứ k trong một phạm vi dữ liệu.
Trong đó:
- array: Là phạm vi các ô chứa dữ liệu mà bạn muốn tính toán.
- k: Là thứ hạng của giá trị mà bạn muốn lấy.
Ví dụ:
- Hàm LARGE(A1:F1,1) sẽ trả về giá trị lớn nhất trong phạm vi từ A1 đến F1.
- Hàm LARGE(A1:F1,5) sẽ trả về giá trị lớn thứ 5 trong phạm vi từ A1 đến F1.
- Hàm LARGE(A1:F1,{1,2,3}) trả về 3 giá trị lớn nhất trong phạm vi từ A1 đến F1.
Giả sử bạn có một bảng điểm của các học sinh theo từng môn học. Bạn muốn tính trung bình cộng của 3 giá trị điểm số cao nhất trong mỗi hàng. Khi đó, bạn áp dụng công thức sau: =AVERAGE(LARGE(B11:H11,{1,2,3})).
Trong đó:
- B11:H11: Phạm vi điểm số mà bạn muốn tính trung bình.
- {1,2,3}: Là danh sách các thứ hạng mà hàm LARGE sẽ lấy ra, từ đó tính trung bình của 3 giá trị lớn nhất.
Hàm tính trung bình cộng cho top N giá trị nhỏ nhất
Ngoài việc tính trung bình của các giá trị lớn nhất, Excel cũng cho phép bạn tính trung bình của các giá trị nhỏ nhất trong một phạm vi dữ liệu. Để thực hiện điều này, bạn có thể sử dụng kết hợp hàm SMALL và hàm AVERAGE. Đây là một trong những hàm trung bình nâng cao trong Excel, giúp bạn dễ dàng tính trung bình cộng cho các giá trị nhỏ nhất trong dãy số.
Cú pháp hàm SMALL: =SMALL(array, k). Hàm này trả về giá trị nhỏ thứ k trong một phạm vi dữ liệu.
Trong đó:
- array: Là phạm vi các ô chứa dữ liệu mà bạn muốn tính toán.
- k: Là thứ hạng của giá trị mà bạn muốn lấy.
Ví dụ:
- Hàm SMALL(A1:F1, 1) sẽ trả về giá trị nhỏ nhất trong phạm vi từ A1 đến F1.
- Hàm SMALL(A1:F1, 5) sẽ trả về giá trị nhỏ thứ 5 trong phạm vi từ A1 đến F1.
- Hàm SMALL(A1:F1, {1,2,3}) trả về 3 giá trị nhỏ nhất trong phạm vi từ A1 đến F1.
Giả sử bạn có một bảng điểm của các học sinh theo từng môn học. Bạn muốn tính trung bình cộng của 3 giá trị điểm số nhỏ nhất trong mỗi hàng. Khi đó, bạn áp dụng công thức sau: =AVERAGE(SMALL(B11:H11,{1,2,3})).
Trong đó:
- B11:H11: Phạm vi điểm số mà bạn muốn tính trung bình.
- {1,2,3}: Là danh sách các thứ hạng mà hàm LARGE sẽ lấy ra, từ đó tính trung bình của 3 giá trị nhỏ nhất.
Xem thêm: 4 Cách Khóa Ô Trong Excel Để Bảo Vệ Dữ Liệu Chỉ Với Vài Bước
Hàm tính trung bình nhân trong Excel
Trong Excel, ngoài hàm tính trung bình cộng, bạn còn có thể sử dụng hàm trung bình nhân (geometric mean) để tính trung bình của một dãy số theo một cách khác. Hàm GEOMEAN giúp bạn tính trung bình nhân của các giá trị trong một phạm vi dữ liệu. Đây là một trong những hàm trung bình nâng cao trong Excel, hữu ích khi bạn cần tính trung bình của các giá trị có sự thay đổi tỷ lệ theo dạng nhân (ví dụ như tỷ suất lợi nhuận hay tỷ lệ tăng trưởng).
Cú pháp hàm GEOMEAN: =GEOMEAN(number1, [number2], [number3],…)
Trong đó:
- number1: Là giá trị đầu tiên và bắt buộc. Đây là phạm vi dữ liệu mà bạn muốn tính trung bình nhân.
- number2, number3,…: Là các giá trị tùy chọn và không bắt buộc. Bạn có thể thêm các phạm vi dữ liệu khác nếu muốn tính trung bình nhân cho nhiều nhóm số liệu.
Ví dụ: Giả sử bạn có một bảng dữ liệu từ C3 đến C10 và muốn tính trung bình nhân của các giá trị này.
Bạn có thể sử dụng công thức sau: =GEOMEAN(C3:C10). Công thức này sẽ tính giá trị trung bình nhân của tất cả các số liệu trong phạm vi từ C3 đến C10. Kết quả của hàm GEOMEAN sẽ cho bạn giá trị trung bình nhân, thường là một giá trị thập phân, giúp bạn phân tích dữ liệu có tính chất nhân hoặc tỷ lệ.
Xem thêm: 3 Cách Thêm Đầu 84 Vào Số Điện Thoại Trong Excel Nhanh Gọn
Hàm tính trung bình trọng số trong Excel
Trong Excel, khi bạn muốn tính giá trị trung bình nhưng với trọng số khác nhau cho từng thành phần, bạn có thể sử dụng hàm trung bình trọng số với công thức SUMPRODUCT. Đây là một dạng tính toán hàm trung bình nâng cao trong Excel, rất hữu ích trong các tình huống như tính điểm tổng kết của học sinh, phân tích tỷ lệ lợi nhuận, hay các trường hợp có sự khác biệt về trọng số giữa các thành phần trong bộ dữ liệu.
Cú pháp hàm SUMPRODUCT: =SUMPRODUCT(array1, array2, array3,…)
Trong đó:
- array1: Mảng bắt buộc chứa các giá trị bạn muốn nhân với trọng số.
- array2, array3,…: Các mảng tùy chọn (tối đa 255 mảng) chứa các giá trị tương ứng với array1 để thực hiện phép nhân rồi cộng lại.
Để tính trung bình trọng số, bạn áp dụng công thức như sau:
- Nếu trọng số ở định dạng phần trăm, bạn có thể sử dụng công thức sau: =SUMPRODUCT(mảng giá trị, mảng trọng số).
- Nếu trọng số không ở định dạng phần trăm mà là giá trị số, bạn phải chia tổng các giá trị tích số cho tổng trọng số: =SUMPRODUCT(mảng giá trị, mảng trọng số)/SUM(mảng trọng số).
Ví dụ: Giả sử bạn có bảng điểm của học sinh với các môn học và trọng số của mỗi môn. Để tính điểm trung bình của học sinh, bạn sử dụng công thức: =SUMPRODUCT(B2:B4,C2:C4).
Trong đó:
- B2:B4: Là mảng chứa điểm thi của sinh viên.
- C2:C4: Là mảng chứa trọng số tương ứng của từng môn học.
Nếu trọng số không ở dạng phần trăm, bạn có thể sử dụng công thức sau để tính trung bình trọng số: =SUMPRODUCT(B2:B4, C2:C4)/SUM(C2:C4). Trong đó, SUM(C2:C4) sẽ tính tổng trọng số.
Như vậy, trong bài viết này, chúng ta đã cùng khám phá các hàm trung bình nâng cao trong Excel. Những hàm này không chỉ giúp tính toán các giá trị trung bình thông thường mà còn mở rộng khả năng tính toán trong những tình huống yêu cầu điều kiện, trọng số hoặc các giá trị đặc biệt. Việc hiểu và áp dụng đúng các hàm này sẽ giúp bạn tối ưu hóa hiệu quả công việc và nâng cao khả năng xử lý dữ liệu trong Excel.
Xem thêm: 3 Cách Xóa Ô Trống Trong Excel Nhanh Chóng Và Đơn Giản
