Có thể bạn quan tâm: Cách Căn Lề Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
1. Giới thiệu chung về hàm trung bình trong Excel
Trong môi trường làm việc hiện đại, việc xử lý dữ liệu nhanh chóng, chính xác và hiệu quả là một nhu cầu thiết yếu. Microsoft Excel, với hàng trăm hàm tích hợp, đã trở thành công cụ “đắc lực” cho mọi người làm việc với số liệu, từ nhân viên kế toán, nhà phân tích tài chính, cho tới giáo viên và sinh viên. Trong số các hàm thường được sử dụng, hàm trung bình (AVERAGE) và các biến thể của nó chiếm một vị trí quan trọng, bởi vì tính toán trung bình là bước đầu tiên để hiểu xu hướng, so sánh và đưa ra quyết định.
Bài viết sau sẽ cung cấp cho bạn một cái nhìn toàn diện về hàm trung bình trong Excel, bao gồm:
- Nguyên lý cơ bản và cách viết công thức.
- Các hàm trung bình phổ biến:
AVERAGE,AVERAGEIF,AVERAGEIFS,AVERAGEA,MEDIAN,MODE,GEOMEAN,HARMEAN. - Cách xử lý dữ liệu ngoại lệ (outliers) và dữ liệu trống.
- Mẹo tối ưu khi làm việc với dải ô lớn, bảng dữ liệu động và Power Query.
- Ứng dụng thực tế trong các lĩnh vực: tài chính, kinh doanh, giáo dục, y tế.
- Những lỗi thường gặp và cách khắc phục.
Mục tiêu là giúp bạn không chỉ biết cách “gõ công thức”, mà còn hiểu sâu về nguyên tắc thống kê đằng sau, để từ đó đưa ra những phân tích có giá trị và tránh những sai lầm phổ biến.
Có thể bạn quan tâm: Cách Copy Sheet Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Cơ sở lý thuyết: Trung bình là gì?
2.1. Định nghĩa trung bình cộng (Arithmetic Mean)
Trung bình cộng là tổng của một tập hợp các giá trị chia cho số lượng các giá trị đó:
\text{Mean} = \frac{\sum_{i=1}^{n} x_i}{n}
Trong Excel, hàm AVERAGE thực hiện phép tính này ngay lập tức. Ví dụ, nếu ô A1:A5 chứa các giá trị 10, 15, 20, 25, 30, công thức:
=AVERAGE(A1:A5)
sẽ trả về 20.
2.2. Khi nào nên dùng trung bình?
- Khi dữ liệu phân bố gần đồng đều và không có giá trị ngoại lệ quá lớn hoặc quá nhỏ.
- Khi muốn có một “điểm trung tâm” tổng quát cho một tập hợp số.
- Khi so sánh các bộ dữ liệu có cùng quy mô.
2.3. Các loại trung bình khác
- Trung bình trọng số (Weighted Mean): Mỗi giá trị có một trọng số riêng, công thức trong Excel thường dùng
SUMPRODUCTvàSUM. - Trung bình hình học (Geometric Mean): Thích hợp cho dữ liệu tỷ lệ tăng giảm (tỷ lệ phần trăm, lãi suất). Dùng hàm
GEOMEAN. - Trung bình hàm (Harmonic Mean): Dùng khi tính trung bình tốc độ hoặc tần suất. Dùng hàm
HARMEAN. - Trung vị (Median): Giá trị nằm giữa khi dữ liệu được sắp xếp. Dùng hàm
MEDIAN. - Mode (Giá trị xuất hiện nhiều nhất): Dùng hàm
MODE.SNGLhoặcMODE.MULT.
Hiểu đúng loại trung bình sẽ giúp bạn tránh việc “đánh đồng sai” khi phân tích dữ liệu.
Có thể bạn quan tâm: Các Hàm Cơ Bản Trong Excel: Hướng Dẫn Chi Tiết Và Thực Tế Cho Người Mới Bắt Đầu
3. Các hàm trung bình cơ bản trong Excel
3.1. Hàm AVERAGE
Cú pháp:
AVERAGE(number1, number2, …)
number1, number2, …là các số, ô hoặc dải ô.- Các ô trống, văn bản không phải số và các giá trị lỗi (
#DIV/0!,#N/A) sẽ bị bỏ qua. - Nếu toàn bộ đối số là ô trống hoặc không phải số, hàm trả về lỗi
#DIV/0!.
Ví dụ thực tế:
| A | B | C |
|---|---|---|
| 10 | 15 | 20 |
| 5 | 0 | 30 |
| 25 | 40 |
Công thức =AVERAGE(A1:C3) sẽ tính trung bình của 9 giá trị (không tính ô trống) và trả về 20.
3.2. Hàm AVERAGEA
Khác với AVERAGE, AVERAGEA đếm cả các giá trị văn bản và giá trị logic:
- Văn bản được tính là 0.
- TRUE được tính là 1, FALSE là 0.
Cú pháp:
AVERAGEA(value1, value2, …)
Ứng dụng: Khi bạn có một cột chứa các giá trị YES/NO (TRUE/FALSE) và muốn tính tỷ lệ “đúng” cùng với các số thực.
3.3. Hàm AVERAGEIF và AVERAGEIFS
Đây là các hàm điều kiện cho phép tính trung bình chỉ trên những ô thỏa mãn một hoặc nhiều tiêu chí.
3.3.1. AVERAGEIF
Cú pháp:
AVERAGEIF(range, criteria, average_range)
range: Dải ô cần kiểm tra tiêu chí.criteria: Tiêu chí (có thể là số, biểu thức, chuỗi, hoặc tham chiếu ô).average_range: (Tùy chọn) Dải ô thực tế được tính trung bình. Nếu bỏ qua, Excel sẽ dùngrange.
Ví dụ: Tính trung bình điểm của học sinh có điểm trung bình môn Toán > 8.
=AVERAGEIF(B2:B20,">8",C2:C20)
3.3.2. AVERAGEIFS
Cú pháp:
AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2, …)
- Cho phép nhiều tiêu chí cùng lúc.
- Lưu ý:
average_rangeluôn là đối số đầu tiên.
Ví dụ: Trung bình doanh thu của các khu vực “Miền Bắc” và “Miền Nam” trong năm 2026.
=AVERAGEIFS(D2:D500, B2:B500, "Miền Bắc", C2:C500, "2026") + AVERAGEIFS(D2:D500, B2:B500, "Miền Nam", C2:C500, "2026")
Bạn có thể dùng hàm SUM để cộng hai kết quả hoặc dùng AVERAGE lại nếu muốn tính trung bình chung.
3.4. Các hàm trung bình nâng cao
| Hàm | Mô tả | Công thức mẫu |
|---|---|---|
GEOMEAN | Trung bình hình học, thích hợp cho tỷ lệ tăng/giảm | =GEOMEAN(A1:A10) |
HARMEAN | Trung bình hàm, dùng cho tốc độ, mật độ | =HARMEAN(B1:B20) |
MEDIAN | Trung vị, không bị ảnh hưởng bởi ngoại lệ | =MEDIAN(C1:C30) |
MODE.SNGL | Giá trị xuất hiện nhiều nhất (đơn) | =MODE.SNGL(D1:D100) |
MODE.MULT | Trả về mảng các mode (nếu có) | =MODE.MULT(E1:E50) (nhập dưới dạng công thức mảng) |
PERCENTILE.INC / PERCENTILE.EXC | Tính phần trăm vị trí | =PERCENTILE.INC(F1:F200,0.75) |
4. Xử lý dữ liệu ngoại lệ và ô trống
4.1. Vấn đề “outlier” (giá trị ngoại lệ)
Giá trị ngoại lệ có thể làm lệch trung bình mạnh mẽ. Có hai cách thường dùng:
- Loại bỏ trực tiếp: Dùng
AVERAGEIFvới tiêu chí loại trừ (ví dụ, loại bỏ giá trị > 1000). - Sử dụng trung vị:
MEDIANít bị ảnh hưởng bởi outlier.
Ví dụ: Loại bỏ các giá trị > 500 trong dải A1:A100:
=AVERAGEIF(A1:A100,"<=500")
4.2. Xử lý ô trống
AVERAGEtự động bỏ qua ô trống.- Nếu muốn tính trung bình kèm ô trống như 0, dùng
AVERAGEAhoặc thay thế ô trống bằng 0 bằng công thứcIF(ISBLANK(A1),0,A1).
4.3. Xử lý lỗi trong ô
Nếu dải chứa lỗi (#DIV/0!, #N/A…), AVERAGE sẽ trả về lỗi. Giải pháp:
- Sử dụng hàm
IFERRORhoặcAGGREGATE(function number 1 – AVERAGE) để bỏ qua lỗi.
=AGGREGATE(1,6,A1:A100) // 1 = AVERAGE, 6 = bỏ qua lỗi
5. Mẹo tối ưu khi làm việc với dải ô lớn
Có thể bạn quan tâm: Gạch Ngang Chữ Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
5.1. Dùng Table (Bảng dữ liệu)
Khi dữ liệu được chuyển thành Table (Insert > Table), công thức có thể dùng cột tham chiếu:
=AVERAGE(Table1DoanhThu)
Lợi ích:
- Dải ô tự động mở rộng khi thêm dòng mới.
- Đọc dễ hiểu, bảo trì nhanh.
5.2. Dùng Dynamic Named Ranges
Sử dụng hàm OFFSET hoặc INDEX kết hợp với COUNTA để tạo dải động:
=AVERAGE(OFFSET(A1,0,0,COUNTA(A:A),1))
5.3. Power Query cho tính trung bình nhanh
Trong Power Query, bạn có thể:
- Load dữ liệu.
- Chọn Group By, chọn Average cho cột muốn tính.
- Load lại bảng đã tính trung bình.
Điều này giúp giảm tải tính toán trên sheet và giữ dữ liệu gốc sạch sẽ.
5.4. Sử dụng hàm ARRAY (Dynamic Array) trong Excel 365/2026
Excel 365 hỗ trợ công thức mảng động, cho phép bạn tính trung bình cho nhiều nhóm cùng lúc:
=AVERAGEIFS(D2:D100, B2:B100, UNIQUE(B2:B100))
Công thức trên sẽ trả về một mảng trung bình cho mỗi khu vực duy nhất trong cột B.
6. Ứng dụng thực tế trong các lĩnh vực
6.1. Tài chính – Tính trung bình lợi nhuận, lợi suất
=AVERAGEIF(LợiNhuận!C2:C1000,">0")
- Loại bỏ các tháng lỗ.
- Kết hợp với
STDEV.Pđể tính độ lệch chuẩn, đưa ra Sharpe Ratio.
6.2. Kinh doanh – Đánh giá hiệu suất bán hàng
- Trung bình đơn hàng:
=AVERAGEIF(OrdersGiá trị,">0") - Trung bình số lượng sản phẩm bán ra:
=AVERAGEIF(OrdersSố lượng,">0") - Trung bình doanh thu theo khu vực:
=AVERAGEIFS(OrdersDoanh thu, OrdersKhu vực, "Miền Bắc")
6.3. Giáo dục – Phân tích điểm số học sinh
- Trung bình lớp:
=AVERAGE(ScoreToán) - Trung bình cho từng nhóm học sinh (giới tính, lớp): sử dụng
AVERAGEIFS. - Đánh giá “outlier” (học sinh quá giỏi hoặc quá yếu) bằng cách so sánh với
MEDIANvàSTDEV.P.
6.4. Y tế – Trung bình chỉ số sinh học
- Trung bình huyết áp, nhịp tim:
=AVERAGEIF(MeasurementsHuyết áp,">0") - Khi dữ liệu có giá trị âm (lỗi đo), dùng
AVERAGEIFSđể lọc:=AVERAGEIFS(MeasurementsHuyết áp, MeasurementsHuyết áp,">0")
7. Những lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
#DIV/0! | Dải ô không có giá trị số (hoặc toàn bộ ô trống) | Kiểm tra dải, dùng IFERROR(AVERAGE(...),0) |
#VALUE! | Có dữ liệu không phải số trong dải và hàm không bỏ qua (ví dụ AVERAGEA với văn bản không phải số) | Dùng AVERAGE thay cho AVERAGEA hoặc chuyển văn bản sang số bằng VALUE |
| Kết quả sai lệch vì outlier | Giá trị ngoại lệ quá lớn hoặc quá nhỏ | Loại bỏ bằng AVERAGEIF hoặc dùng MEDIAN |
| Không cập nhật khi thêm dòng mới | Dải ô cố định | Chuyển sang Table hoặc Dynamic Named Range |
| Tốc độ chậm khi tính trên hàng nghìn dòng | Công thức mảng lớn, tính toán lặp lại | Sử dụng Power Query hoặc PivotTable để tính trung bình |
8. Bài tập thực hành (tự kiểm tra)
Bài 1: Trong sheet “Bán hàng”, cột B là “Doanh thu”. Tính trung bình doanh thu chỉ cho các ngày có doanh thu > 5000.
Gợi ý:=AVERAGEIF(B2:B1000,">5000")Bài 2: Dùng
AVERAGEIFSđể tính trung bình điểm Toán cho các học sinh lớp 10 có điểm trung bình môn Văn > 7.
Công thức mẫu:=AVERAGEIFS(ToanĐiểm, ToanLớp, "10", VanĐiểm Trung Bình, ">7")Bài 3: Sử dụng Power Query để nhóm dữ liệu bán hàng theo “Khu vực” và tính trung bình “Số lượng bán”.
Bài 4: Trong danh sách giá cổ phiếu, tính trung bình lợi suất (lợi nhuận/giá mua) chỉ cho những cổ phiếu có lợi nhuận dương.
Sau khi thực hiện, so sánh kết quả với cách tính thủ công (tổng / số lượng) để kiểm chứng.
9. Kết luận
Hàm trung bình trong Excel không chỉ đơn thuần là một công cụ tính toán số học; nó là nền tảng để phân tích dữ liệu, đánh giá xu hướng và ra quyết định. Bằng việc nắm vững:
- Các hàm cơ bản (
AVERAGE,AVERAGEA), - Các hàm có điều kiện (
AVERAGEIF,AVERAGEIFS), - Các loại trung bình đặc thù (
GEOMEAN,HARMEAN,MEDIAN,MODE), - Cách xử lý ngoại lệ, ô trống và lỗi,
- Các kỹ thuật tối ưu (Table, Dynamic Ranges, Power Query, Dynamic Arrays),
bạn sẽ có thể áp dụng chúng một cách linh hoạt vào bất kỳ lĩnh vực nào: tài chính, kinh doanh, giáo dục, y tế,… Đồng thời, việc tránh những lỗi phổ biến và sử dụng các công cụ hỗ trợ sẽ giúp bạn tiết kiệm thời gian, giảm lỗi và nâng cao độ tin cậy của báo cáo.
Hy vọng bài viết đã cung cấp cho bạn một bản đồ chi tiết để khai thác sức mạnh của hàm trung bình trong Excel. Hãy thực hành, thử nghiệm và dần dần xây dựng những mô hình phân tích dữ liệu chuyên nghiệp, từ đó tạo ra giá trị thực cho công việc và doanh nghiệp của mình.
Chúc bạn thành công!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.