Cách dùng hàm tính trung bình (Hàm AVERAGE) trong Excel

Bài viết này, Starttrain sẽ cung cấp cho bạn cái nhìn chi tiết nhất về các hàm tính trung bình trong Excel phổ biến nhất hiện nay bao gồm AVERAGE, AVERAGEIF, và AVERAGEIFS. Đi kèm với đó là các ví dụ thực tế trực quan giúp bạn áp dụng thành thạo ngay lập tức.

Các hàm tính trung bình trong Excel

Hàm AVERAGE

Hàm AVERAGE là hàm tính trung bình trong Excel phổ biến nhất, được sử dụng để tính trung bình cộng của một chuỗi số, một phạm vi ô hoặc một mảng dữ liệu mà không đi kèm bất kỳ điều kiện lọc nào.

Cú pháp: =AVERAGE(number1, [number2], ...)

• Các tham số:
  • number1 (Bắt buộc): Số thứ nhất, tham chiếu ô, hoặc phạm vi ô chứa dữ liệu số mà bạn muốn tính trung bình.
  • number2, … (Tùy chọn): Các số, tham chiếu ô hoặc phạm vi ô bổ sung mà bạn muốn đưa vào phép tính (tối đa có thể lên tới 255 đối số trong các phiên bản Excel hiện đại).
• Đặc điểm chi tiết cần lưu ý:
  • Bỏ qua giá trị rỗng và văn bản: Hàm AVERAGE sẽ tự động bỏ qua các ô trống (blank) hoặc các ô chứa văn bản (text) nằm trong phạm vi tham chiếu.
  • Xử lý giá trị logic: Các giá trị logic như TRUE và FALSE nếu được nhập trực tiếp vào đối số của hàm sẽ được tính (TRUE là 1, FALSE là 0). Tuy nhiên, nếu chúng nằm trong một phạm vi ô được tham chiếu, hàm sẽ bỏ qua chúng.
  • Lỗi chia cho 0 (#DIV/0!): Nếu toàn bộ phạm vi tham chiếu không chứa bất kỳ giá trị số nào hoặc chỉ chứa các ô rỗng/văn bản, hàm AVERAGE sẽ trả về lỗi #DIV/0!.

Hàm AVERAGEIF

Khi chỉ muốn tính trung bình cộng cho các ô thỏa mãn một tiêu chuẩn nhất định, hàm AVERAGEIF chính là giải pháp tối ưu.

Cú pháp: =AVERAGEIF(range, criteria, [average_range])

• Các tham số:
  • range (Bắt buộc): Phạm vi chứa các ô mà bạn muốn áp dụng điều kiện lọc. Các ô trong vùng này có thể chứa số, văn bản, hoặc mảng chứa số.
  • criteria (Bắt buộc): Điều kiện dưới dạng số, biểu thức, tham chiếu ô, hoặc văn bản để xác định ô nào sẽ được tính trung bình (Ví dụ: “>50”, “Nam”, A1).
  • average_range (Tùy chọn): Vùng thực tế chứa dữ liệu số cần tính trung bình. Nếu bạn bỏ qua tham số này, Excel sẽ tự động tính trung bình trực tiếp trên các ô thuộc vùng range thỏa mãn điều kiện.
• Đặc điểm chi tiết cần lưu ý:
  • Hỗ trợ ký tự đại diện (Wildcards): Bạn có thể sử dụng dấu chấm hỏi ? (thay thế cho 1 ký tự bất kỳ) hoặc dấu sao * (thay thế cho một chuỗi ký tự bất kỳ) trong phần criteria để tìm kiếm các dữ liệu tương đối.
  • Xử lý ô trống trong vùng tính trung bình: Nếu một ô trong average_range là ô trống hoặc chứa văn bản, nó sẽ bị bỏ qua trong phép tính trung bình.
  • Lỗi trả về: Nếu không có ô nào trong vùng range đáp ứng được điều kiện criteria, hàm sẽ trả về lỗi #DIV/0!.

Hàm AVERAGEIFS

Hàm AVERAGEIFS là phiên bản nâng cấp mạnh mẽ nhất trong nhóm hàm tính trung bình trong Excel. Hàm này cho phép bạn tính trung bình cộng của các ô thỏa mãn đồng thời nhiều điều kiện khác nhau (mối quan hệ logic AND).

Cú pháp: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• Các tham số:
  • average_range (Bắt buộc): Khác với hàm AVERAGEIF, ở hàm AVERAGEIFS, phạm vi chứa dữ liệu số cần tính trung bình luôn phải đứng đầu tiên.
  • criteria_range1 (Bắt buộc): Phạm vi thứ nhất dùng để đánh giá điều kiện thứ nhất.
  • criteria1 (Bắt buộc): Điều kiện áp dụng cho criteria_range1.
  • criteria_range2, criteria2, … (Tùy chọn): Các cặp phạm vi và điều kiện bổ sung tiếp theo (Excel cho phép thiết lập tối đa lên tới 127 cặp điều kiện).
• Đặc điểm chi tiết cần lưu ý:
  • Đồng nhất kích thước vùng dữ liệu: Tất cả các vùng criteria_range bắt buộc phải có cùng kích thước (số hàng và số cột) với vùng average_range. Nếu không đồng nhất, Excel sẽ trả về lỗi #VALUE!.
  • Logic tính toán: Chỉ những dòng/bản ghi nào thỏa mãn tất cả các điều kiện đưa ra mới được đưa vào phép tính trung bình cộng.
  • Xử lý giá trị không khớp: Tương tự các hàm trên, nếu không tìm thấy bất kỳ dòng dữ liệu nào thỏa mãn đồng thời các điều kiện, kết quả trả về sẽ là lỗi #DIV/0!.

Ví dụ cụ thể

Để giúp bạn hình dung trực quan và thực hành chính xác các hàm tính trung bình trong Excel, chúng ta sẽ cùng thực hiện trên bảng dữ liệu quản lý kết quả học tập thực tế dưới đây. Bảng dữ liệu gồm 10 học sinh với các thông tin về Lớp, Môn học, Giới tính, Điểm số và Xếp loại.

Bảng dữ liệu mẫu: Danh sách điểm thi của học sinh

Chúng ta sẽ lần lượt giải quyết 3 bài toán thực tế bằng cách sử dụng công thức tính trung bình cộng tương ứng với các bước minh họa chi tiết.

Ví dụ 1: Tính Điểm TB toàn lớp

Yêu cầu: Tính điểm trung bình cộng của toàn bộ 10 học sinh có mặt trong danh sách bảng điểm (vùng dữ liệu từ F2 đến F11).

Công thức áp dụng tại ô J1: =AVERAGE(F2:F11)

• Các bước thực hiện:
  • Bước 1: Nhấp chuột chọn ô hiển thị kết quả J1 (bên cạnh ô tiêu đề “Điểm TB toàn lớp”).
  • Bước 2: Nhập chính xác công thức: =AVERAGE(F2:F11).
  • Bước 3: Nhấn phím Enter trên bàn phím để hoàn thành.
• Giải thích kết quả: Excel sẽ cộng dồn điểm số của cả 10 học sinh trong vùng dữ liệu được chỉ định: Tổng điểm = 8.5 + 7.0 + 9.2 + 6.5 + 5.8 + 8.8 + 7.5 + 9.0 + 6.0 + 8.0 = 76.3 Sau đó, hệ thống chia cho tổng số lượng học sinh là 10 để trả về kết quả trung bình hiển thị tại ô J1 là 7.63.

Ví dụ 2: Tính Điểm TB lớp 10A

Yêu cầu: Chỉ tính điểm trung bình cho những học sinh thuộc lớp “10A”.

Công thức áp dụng tại ô J2: =AVERAGEIF(C2:C11, "10A", F2:F11)

• Các bước thực hiện:
  • Bước 1: Click chọn ô hiển thị kết quả J2.
  • Bước 2: Nhập công thức điều kiện: =AVERAGEIF(C2:C11, “10A”, F2:F11) (như minh họa trong ảnh)
  • Bước 3: Nhấn phím Enter để nhận kết quả.

• Giải thích kết quả: Excel tiến hành lọc ở cột Lớp (C2:C11) để tìm những hàng có giá trị là “10A”. Những học sinh thỏa mãn điều kiện bao gồm:
  • Nguyễn Anh (Lớp 10A): 8.5 điểm.
  • Lê Chi (Lớp 10A): 9.2 điểm.
  • Hoàng Em (Lớp 10A): 5.8 điểm.
  • Đặng Quân (Lớp 10A): 7.5 điểm.
  • Ngô Sơn (Lớp 10A): 6.0 điểm.
• Hàm tính toán tổng điểm của nhóm học sinh này: 8.5 + 9.2 + 5.8 + 7.5 + 6.0 = 37.0. Sau đó chia cho số lượng học sinh thỏa mãn điều kiện là 5. Kết quả cuối cùng trả về tại ô J2 là 7.40.

Ví dụ 3: Tính Điểm TB môn Toán của học sinh Nam

Yêu cầu: Tính điểm trung bình của học sinh đáp ứng đồng thời 2 điều kiện: Môn học phải là “Toán” Và Giới tính phải là “Nam”.

Công thức áp dụng tại ô J3: =AVERAGEIFS(F2:F11, D2:D11, "Toán", E2:E11, "Nam")

• Các bước thực hiện:
  • Bước 1: Click chọn ô hiển thị kết quả J3.
  • Bước 2: Nhập công thức đa điều kiện: =AVERAGEIFS(F2:F11, D2:D11, “Toán”, E2:E11, “Nam”) (như minh họa trong ảnh). Ở đây, vùng tính trung bình F2:F11 (Điểm) được đưa lên trước, theo sau là các cặp phạm vi điều kiện và tiêu chí tương ứng.
  • Bước 3: Nhấn phím Enter.
• Giải thích kết quả (Hình ảnh thực tế từ hình trên): Excel rà soát đồng thời cột Môn (D2:D11) và cột Giới tính (E2:E11) để lọc ra các bản ghi thỏa mãn cả 2 điều kiện (Môn = “Toán” AND Giới tính = “Nam”):
  • Nguyễn Anh (Môn Toán, Nam): Thỏa mãn -> Lấy điểm 8.5.
  • Phạm Dũng (Môn Toán, Nam): Thỏa mãn -> Lấy điểm 6.5.
  • Đặng Quân (Môn Toán, Nam): Thỏa mãn -> Lấy điểm 7.5. (Các học sinh khác như Hoàng Em – môn Văn, Dương Trúc – giới tính Nữ đều bị loại vì không thỏa mãn đồng thời cả 2 điều kiện trên).
• Hàm tính tổng số điểm được lọc: 8.5 + 6.5 + 7.5 = 22.5. Sau đó chia cho số lượng học sinh thỏa mãn là 3. Kết quả trả về vô cùng chính xác tại ô J3 là 7.50.

Những lưu ý và lỗi thường gặp khi dùng hàm tính trung bình trong Excel

Lỗi #DIV/0!

• Nguyên nhân: Lỗi này xảy ra khi phạm vi dữ liệu bạn chọn không chứa bất kỳ giá trị số nào, hoặc điều kiện bạn đặt ra trong hàm AVERAGEIF/AVERAGEIFS không khớp với bất kỳ dòng dữ liệu nào trong bảng (dẫn đến việc mẫu số của phép tính trung bình bằng 0).
• Cách khắc phục:
  • Kiểm tra lại định dạng của cột số xem có đang bị lưu dưới dạng văn bản (Text) hay không.
  • Sử dụng kết hợp hàm bọc lỗi IFERROR để trả về giá trị trống hoặc thông điệp dễ hiểu thay vì hiển thị lỗi xấu xí trên báo cáo: =IFERROR(AVERAGE(E3:E8), “Không có dữ liệu”)

Sự khác biệt giữa ô trống (Blank cell) và ô chứa số 0 (Zero)

• Bản chất:
  • Nếu một ô hoàn toàn trống, các hàm tính trung bình trong Excel sẽ tự động bỏ qua ô đó (không cộng vào tử số và cũng không đếm vào mẫu số).
  • Nếu ô đó chứa số 0, Excel vẫn tính số 0 đó vào tổng số và tăng số lượng đếm ở mẫu số lên thêm 1. Điều này sẽ làm giảm đáng kể giá trị trung bình cuối cùng của bạn.
• Lời khuyên: Hãy chắc chắn rằng bạn nhập dữ liệu chuẩn xác. Nếu học sinh bỏ thi (không có điểm), hãy để trống ô đó. Nếu học sinh bị điểm 0, hãy nhập số 0.

Lỗi lệch vùng tham chiếu

• Nguyên nhân: Khi viết hàm AVERAGEIFS, nếu vùng tính trung bình average_range và các vùng điều kiện criteria_range có độ dài hàng/cột lệch nhau (Ví dụ: Một vùng kéo dài từ dòng 3 đến dòng 8, vùng kia lại từ dòng 3 đến dòng 10), Excel lập tức báo lỗi #VALUE!.
• Cách khắc phục: Luôn rà soát kỹ chỉ số dòng và cột của tất cả các tham số truyền vào hàm AVERAGEIFS để đảm bảo chúng hoàn toàn khớp kích thước với nhau.

Ứng dụng của các hàm tính trung bình trong Excel đối với đời sống

Không chỉ đóng khung trong những bài tập lý thuyết hay công việc của các kế toán viên, các hàm tính trung bình trong Excel có tính ứng dụng cực kỳ cao và thực tế trong đời sống hàng ngày của chúng ta:

• Trong Giáo dục và Học tập:
  • Hỗ trợ giáo viên tính điểm trung bình môn học kỳ cho học sinh một cách tự động, nhanh chóng và chính xác tuyệt đối.
  • Giúp học sinh tự theo dõi, tính toán điểm số cá nhân để lên lộ trình học tập, đặt mục tiêu điểm số phù hợp cho các kỳ thi lớn.
• Trong Quản lý Tài chính Cá nhân và Gia đình:
  • Bạn có thể lập một bảng tính Excel đơn giản ghi lại chi tiêu hàng ngày, sau đó dùng hàm AVERAGE để biết trung bình mỗi tháng gia đình mình tiêu hết bao nhiêu tiền cho ăn uống, hóa đơn điện nước hay mua sắm giải trí. Từ đó đưa ra kế hoạch tiết kiệm hợp lý hơn.
• Trong Kinh doanh và Bán lẻ:
  • Chủ cửa hàng dễ dàng tính được doanh thu trung bình mỗi ngày trong tuần hoặc mỗi tháng trong năm.
  • Sử dụng hàm AVERAGEIF để tính xem mức chi tiêu trung bình của một khách hàng Nam là bao nhiêu so với khách hàng Nữ, giúp tối ưu hóa chiến lược marketing và nhập hàng hóa thích hợp.
• Trong Quản lý Nhân sự (HR) và Lương bổng:
  • Tính mức lương trung bình của các phòng ban khác nhau bằng hàm AVERAGEIF để cân đối ngân sách nhân sự.
  • Đánh giá hiệu suất làm việc (KPI) trung bình của toàn bộ nhân viên cuối năm một cách công bằng dựa trên các số liệu định lượng cụ thể.
• Trong Chăm sóc Sức khỏe và Thể thao:
  • Theo dõi chỉ số cân nặng, lượng calo nạp vào hàng ngày, hoặc thời gian chạy bộ trung bình mỗi tuần để điều chỉnh chế độ tập luyện lành mạnh.

Tổng kết

Việc nắm vững và hiểu rõ cách vận dụng các hàm tính trung bình trong Excel từ cơ bản như hàm AVERAGE cho đến các hàm lọc có điều kiện như AVERAGEIF hay đa điều kiện như AVERAGEIFS là chìa khóa vàng giúp bạn tối ưu hóa hiệu suất làm việc với số liệu. Việc này không chỉ giảm thiểu tối đa các sai sót tính toán thủ công mà còn giúp các báo cáo, bảng biểu của bạn trở nên chuyên nghiệp, thuyết phục hơn trong mắt sếp và đối tác.