Trong Excel, nếu muốn tính trung bình cộng của các số thì ta có thể dùng hàm AVERAGE[] để tính. Nhưng khi chúng ta muốn tính trung bình cộng mà lại muốn áp dụng điều kiện thì hàm AVERAGE[] lại không áp dụng được. Vậy có một hàm áp dụng rất hay, đó là hàm AVERAGEIF[].
Bài viết này mình sẽ giới thiệu các bạn sử dụng hàm AVERAGEIF[] trong Excel và kèm theo ví dụ thực tế về hàm AVERAGEIF[]. Hàm AVERAGEIF[] giúp chúng ta tính trung bình cộng có điều kiện trong Excel rất hay.
Hàm AVERAGEIF[] là hàm tính trung bình cộng cho các ô trong Excel nhưng phải thõa mãng điều kiện mà mình chỉ định trước đó.
Cú pháp hàm AVERAGEIF[] như sau: =AVERAGEIF[range, criteria, [average_range]]
Trong đó:
- Range: Một hoặc nhiều ô để tính giá trị trung bình, bao gồm các số hoặc tên, mảng hoặc tham chiếu có chứa số. Đây là tham số bắt buộc.
- Criteria: Tiêu chí ở 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. Điều kiện để lọc ra giá trị trung bình.Ví dụ, các tiêu chí có thể được biểu thị dưới dạng như 32, “32”, “>32”, “táo” hoặc B4. Đây là tham số bắt buộc.
- Average_range: Tùy chọn. Tập hợp các ô thực tế để tính giá trị trung bình. Nếu bỏ qua, phạm vi được dùng.
- Ô có giá trị trả về TRUE hoặc FALSE sẽ bị bỏ qua.
- Nếu ô trong vùng dữ liệu tính trung bình rỗng, thì hàm AVERAGEIF[] sẽ bỏ qua.
- Nếu vùng dữ liệu có giá trị trống hoặc là văn bản, hàm AVERAGEIF[] sẽ trả về lỗi #DIV0!.
- Nếu ô trong vùng điều kiện Criteria bị bỏ trống, thì hàm AVERAGEIF[] sẽ xem giá trị đó là số 0.
- Nếu không có ô nào trong vùng dữ liệu Range đáp ứng điều kiện Criteria, thì hàm AVERAGEIF[] sẽ trả về lỗi #DIV/0!
- Bạn có thể dùng các ký tự đại diện, dấu chấm hỏi [?] và dấu sao [*] trong tiêu chí. Dấu chấm hỏi sẽ khớp với bất kỳ ký tự đơn nào; dấu sao sẽ khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã [~] trước ký tự.
- Average_range không nhất thiết phải có cùng kích cỡ và hình dạng như phạm vi. Các ô thực tế được tính giá trị trung bình sẽ được xác định bằng cách dùng ô trái trên cùng trong average_range làm ô đầu tiên, sau đó gộp các ô tương ứng về kích cỡ và hình dạng với phạm vi.
Ở trên, mình đã giới thiệu cho các bạn về hàm AVERAGEIF[] để tính trung bình cộng có điều kiên trong Excel. Tiếp tục để hiểu rõ hơn về hàm này, mình có một ví dụ thực tế đơn giản để cho các bạn hiểu rõ hơn về hàm AVERAGEIF[].
Giả sử các bạn có một danh sách bảng điểm học sinh của nhiều lớp. Yêu cầu đặc ra là làm sao để tính trung bình cộng của từng lớp.
Để tính được điểm trung bình của tất cả sinh viên ở mỗi lớp ở bài tập trên thì không thể nào áp dụng được hàm AVERAGE[] được. Vì danh sách điểm trên có các sinh viên ở các lớp khác nhau. Nhưng chúng ta có thể tính trung bình cửa mỗi lớp trên bằng cách áp dụng hàm AVERAGEIF[].
Cú pháp hàm AVERAGEIF[]: =AVERAGEIF[range, criteria, [average_range]]
So với cú pháp ta có công thức như sau: =AVERAGEIF[C5:C19;”12A1″;D5:D19]
Trong đó, công thức có:
- C5:C19 là tham số Range, tức là mảng chứa các giá trị trong bảng từ ô C5 đến ô C19. Điều kiện sẽ dò ở đây để xác định giá trị trung bình.
- “12A1” là tham số Criteria, điền tên lớp vào. Là điều kiện để xác định kết quả trả về giá trị trung bình. giá trị này sẽ dựa vào Range để xác định.
- D5:D19 là tham số Average_range, chứa các ô dạng số để tính giá trị trung bình sau khi xác đinh được điều kiện từ ô D5 đến ô D19.
Ở ví dụ này, mình đang cần tính giá trị trung bình của mỗi lớp. Ảnh bên dưới là ảnh kết quả tính trung bình của lớp 12A1, nhưng điều kiện truyền vào là cố định.
Sau khi nhập công thức vào ô chúng ta sẽ nhận được kết quả như ảnh, nhưng giá trị điều kiện ở Criteria là giá trị cố trị.
Sau khi áp dụng công thức vào danh sách bảng điểm trên, nhưng có một vấn đề lớn cần giải quyết. Vấn đề là không lẽ cứ vào công thức sửa điều kiện, như vậy không ổn. Cách giải quyết là chúng ta sẽ thay thế tham số điều kiện Criteria bằng giá trị của một ô.
Ta có công thức sau khi thay đổi như sau: =AVERAGEIF[C5:C19;C21;D5:D19]
Trong đó: công thức có thay đổi mới là địa chỉ C21 là giá trị một của một ô trong bảng tính. giá trị đó sẽ là điều kiện để tính trung bình cộng.
Khi nhấn Enter, tại ô điền công thức sẽ xuất hiện lỗi #DIV/0!. Lý do xuất hiện lỗi này là giá trị ở ô C21 lấy giá trị điều kiên không có dữ liệu, hoặc điều kiện truyền vào không có kết quả.
Để không bị lỗi, các bạn phải điển giá trị vào ô C21 trong bảng tính.
Vậy là mình đã giới thiệu cho các bạn hàm AVERAGEIF[] để tính tổng có điều kiện trong Excel. Ở bài hướng dẫn này mình có ví dụ cụ thể về hàm này. Hàm này thường dùng để tính trung bình cộng khi dữ liệu có chứa điều kiện. Bên dưới là file thực hành trong bài hướng dẫn này, các bạn có thể tải về và làm theo để hiểu hơn về hàm tính trung bình cộng có điều kiện sử dụng hàm AVERAGEIF[].
Cảm ơn các bạn, chúc các bạn thành công!
Trong quá trình làm việc với các file thống kê trên file Execl thì công thức tính trung bình cộng sẽ là một trong những công thức toán học mà bất kỳ ai cũng sẽ phải sử dụng qua, từ một nhân viên văn phòng, kế toán thành thạo cho đến các bạn học sinh, sinh viên, giáo viên…
Chính vì thế, trong bài viết ngày hôm nay mình sẽ giới thiệu với các bạn 2 cách tính điểm trung bình cộng [và làm tròn] nhanh trên bảng tính Excel, hỗ trợ các bạn trong các công việc tính toán, thống kê trên Excel được nhanh chóng và chính xác hơn.
Đọc thêm:
#1. Cách sử dụng hàm SUM để tính trung bình cộng
Vâng, như chúng ta được học ở bậc tiểu học, thì công thức tính trung bình cộng là dùng phép cộng để cộng các chữ số lại với nhau => sau đó, dùng phép chia để chia đều cho nó [ví dụ có 7 số cộng với nhau thì chia cho 7] => là ra kết quả phải không ạ.
Trong trường hợp khi chúng ta tính toán trên Excel cũng vậy, chúng ta sẽ sử dụng hàm SUM để tính tổng, và sử dụng phép chia để tính trung bình cộng.
+ Bước 1: Bạn mở file Excel cần tính điểm trung bình cộng ra => sau đó đặt trỏ chuột vào ô kết quả mà bạn muốn xuất ra.
Tại đây bạn dùng công thức như sau =SUM[“quét chọn các ô chứa số cần tính”]/số lượng ô => và nhấn Enter để hoàn thành.
Ví dụ cụ thể: =SUM[B5:D5]/3
+ Bước 2: Sau khi công thức trả về kết quả trung bình cộng, bạn chọn vào dấu cộng mảnh và Fill công thức xuống các hàng phía dưới để copy công thức và áp dụng với các ô còn lại.
Tuy nhiên, tại đây chúng ta sẽ có một vấn đề nhỏ, đó là các số khi chia trung bình sẽ tăng số lượng chữ số do nó chưa được làm tròn số đơn vị, chục, hoặc trăm. Như các bạn có thể thấy ở hình bên dưới đây.
Chính vì thế chúng ta phải thực hiện làm tròn số trước đã..
+ Bước 3: Để làm tròn những con số này, các bạn cần sử dụng thêm hàm ROUND nữa.
Thực hiện: Đầu tiên, bạn click chuột vào ô kết quả điểm trung bình cộng đầu tiên => và thêm hàm ROUND phía trước hàm SUM => sau đó thêm số được làm tròn là 2 chữ số ở cuối công thức => rồi nhấn Enter.
- Công thức hàm Làm tròn: Round[Số cần làm tròn, làm tròn bao nhiêu số]
Áp dụng vào ví dụ trên ta được: =ROUND[SUM[B5:D5]/3;2]
// Có một chiêu cho các bạn là bạn không cần phải sử dụng dấu ] [dấu ngoặc đóng] ở cuối của công thức hàm, bạn cứ để thế là Excel sẽ tự động điền đầy đủ các dấu ngoặc đóng cho bạn. Sẽ rất hữu ích nếu công thức hàm đó dài và có nhiều ngoặc như vậy.
+ Bước 4: Tại hộp thoại Microsoft Excel, bạn click YES để xác nhận công thức thi hành trên Excel.
+ Bước 5: Sau khi công thức đã hoàn tất thì bạn Fill công thức vừa thực hiện xuống danh sách để áp dụng cho toàn bộ các kết quả còn lại.
Và đây là kết quả sau khi chúng ta đã làm tròn số sau khi tính điểm trung bình cộng với hàm SUM thành công !
#2. Cách dùng hàm AVERAGE tính trung bình cộng nhanh
// Đây là hàm tính trung bình cộng thường dùng trên Excel, cách trên là chúng ta chỉ tính trung bình cộng dựa trên công thức toán học thôi 😀
+ Bước 1: Bạn đặt trỏ chuột vào ô cần xuất kết quả trên file Excel.
+ Bước 2:
- Công thức hàm AVERAGE đó là: AVERAGE[Số thứ 1,Số thứ 2, Số thứ 3,…]
Tuy nhiên, chúng ta sẽ làm tròn luôn số nên tại đây chúng ta sẽ sử dụng kết hợp với hàm làm tròn [ROUND] luôn.
Thực hiện: Bạn nhập hàm =ROUND[
+ Bước 3: Sau đó nhập hàm AVERAGE => và chọn các đối số cần tính trung bình cộng [bạn quét chọn các số cần tính trung bình cộng] => sau đó nhập số 2 để làm tròn => rồi nhấn Enter để hoàn thành.
Công thức đầy đủ của ví dụ này sẽ là: =ROUND[AVERAGE[B5:D5];2]
Trong đó thì:
- Cả cụm AVERAGE[B5:D5] chính là hàm tính trung bình cộng, nếu như bạn làm riêng lẻ từng bước.
- Còn hàm ROUND là hàm làm tròn mà chúng ta kết hợp luôn.
+ Bước 4: Tiếp đó, bạn click Yes để xác nhận công thức hàm AVERAGE trên Excel.
+ Bước 5: Sau đó Fill công thức xuống toàn bộ bảng tính để hoàn thành công thức tính trung bình cộng.
Và đây là kết quả sau khi mình đã sử dụng hàm tính trung bình cộng AVERAGE.
#3. Lời kết
Ok, vậy là mình vừa hướng dẫn với các bạn hai cách tính trung bình cộng bằng hàm SUM và hàm AVERAGE trên bảng tính Excel rồi ha.
Nói chung là để tính điểm trung bình cộng thì rất là đơn giản và dễ dàng phải không nào các bạn. Hi vọng với thủ thuật nhỏ mình chia sẻ hôm nay sẽ hữu ích trong công việc, cũng như học tập của bạn.
Chúc các bạn thành công !
CTV: Lương Trung – Blogchiasekienthuc.com
Note: Bài viết này hữu ích với bạn chứ? Đừng quên đánh giá bài viết, like và chia sẻ cho bạn bè và người thân của bạn nhé !