Các cách tính Trung bình trong Microsoft Excel

Microsoft Excel có hàng trăm hàm có thể giúp bạn thực hiện các phép tính nhanh chóng và chính xác, trong đó có hàm AVERAGE. Bạn có thể muốn tính số liệu bán hàng trung bình, lấy giá trị trung bình của một nhóm dữ liệu chứa số và văn bản hoặc tính điểm trung bình của tất cả học sinh.

Có bao nhiêu hàm tính trung bình trong Excel?

Có 4 hàm AVERAGE và mỗi hàm có cách sử dụng khác nhau:

• AVERAGE: Điều này tạo ra giá trị trung bình số học (tổng của tất cả các số chia cho số giá trị) của một tập hợp dữ liệu, bỏ qua mọi thứ không phải là số.
• AVERAGEA: Hàm này trả về giá trị trung bình của một tập hợp số, văn bản và đối số logic.
• AVERAGEIF: Tính toán giá trị trung bình số học của một tập hợp dữ liệu số đáp ứng một tiêu chí duy nhất.
• AVERAGEIFS: Điều này cho bạn biết giá trị trung bình số học của một tập hợp dữ liệu số đáp ứng một số tiêu chí.

Hãy khám phá những hàm này một cách chi tiết hơn.

Cách sử dụng AVERAGE trong Excel

Để tính trung bình trong Excel, hãy sử dụng cú pháp sau:

=AVERAGE(A,B)

Trong đó A là số đầu tiên, tham chiếu ô hoặc phạm vi và B có tối đa 255 số, tham chiếu ô hoặc phạm vi bổ sung để đưa vào phép tính trung bình.

Ví dụ này có một nhóm 7 học sinh và muốn tính điểm thi trung bình của chúng.

Như bạn thấy, Student C đạt điểm 0 và Student F vẫn chưa tham gia kỳ thi. Do đó, chúng ta muốn Excel tính ra điểm của những người đã tham gia bài kiểm tra (tất cả học sinh ngoại trừ Student F).

Để thực hiện việc này, chúng ta sẽ nhập công thức sau vào ô D2:

=AVERAGE(B2:B8)

Hàm AVERAGE bao gồm 0 trong các phép tính của nó nhưng bỏ qua tất cả các ô trống, văn bản và giá trị logic (TRUE hoặc FALSE). Vì vậy, chúng ta có thể tin tưởng rằng phép tính này sẽ bao gồm điểm của Student C nhưng bỏ qua điểm của Student F.

Nếu bất kỳ giá trị nào được sử dụng trong phép tính AVERAGE chứa một trong các lỗi công thức của Excel thì phép tính sẽ không hoạt động.

Mẹo: Để tiết kiệm thời gian, thay vào đó bạn có thể tính giá trị trung bình thông qua một vài cú nhấp chuột đơn giản. Đầu tiên, chọn dữ liệu của bạn ở mức trung bình, nhấp vào tab "Home" trên ribbon và trong nhóm "Editing", nhấp vào mũi tên drop-down bên cạnh biểu tượng sigma (Σ). Từ đó, nhấp vào "Average". Kết quả sẽ xuất hiện ở cuối dữ liệu của bạn.

Cách sử dụng AVERAGEA trong Excel

AVERAGEA hoạt động theo cách rất giống với AVERAGE, nhưng không chỉ bao gồm các con số trong phép tính. Đây là cú pháp cho hàm này:

=AVERAGEA(A,B)

Trong đó A là giá trị đầu tiên (bao gồm số, giá trị logic như TRUE hoặc FALSE và văn bản) và B có tối đa 255 giá trị bổ sung để đưa vào phép tính trung bình.

Hàm AVERAGEA rất hữu ích nếu bạn có một tập hợp dữ liệu hỗn hợp chứa số, giá trị logic và văn bản và bạn muốn đưa tất cả chúng vào phép tính của mình.

Lấy cùng một bộ dữ liệu đã sử dụng trong ví dụ trên, bây giờ, chúng ta muốn tính giá trị trung bình bằng cách sử dụng AVERAGEA.

Trong ô D5, chúng ta sẽ gõ công thức sau:

=AVERAGEA(B2:B8)

Không giống như hàm AVERAGE bỏ qua các giá trị logic, phép tính AVERAGEA bao gồm FALSE trong phép tính là 0. Nếu thay vào đó là TRUE thì giá trị này sẽ được tính là 1. Do đó, trong ví dụ, điểm 0 của Student C được bao gồm, và Student F cũng được tính là đạt điểm 0. Đây là lý do tại sao kết quả của phép tính này thấp hơn kết quả của phép tính trước.

AVERAGEA tính bất kỳ văn bản nào khác là 0 (ví dụ, nếu bạn nhập FOUR, văn bản này vẫn được biểu thị là 0 chứ không phải 4) và bỏ qua các ô trống.

Giống như AVERAGE, nếu bất kỳ giá trị nào được sử dụng trong phép tính AVERAGEA chứa một trong các lỗi công thức của Excel thì phép tính sẽ trả về lỗi.

Cách sử dụng AVERAGEIF trong Excel

AVERAGEIF thực hiện hiệu quả hai phép tính trong một lần, trước tiên xác định dữ liệu đáp ứng một tiêu chí nhất định trước khi tìm giá trị trung bình của những dữ liệu này. AVERAGEIF sử dụng cú pháp sau:

=AVERAGEIF(A,B,C)

Trong đó A là phạm vi giá trị hoặc ô cần đưa vào mức trung bình, B là tiêu chí và C (tùy chọn) là tập hợp các ô thực tế để tính trung bình. Hãy xem ví dụ này.

Chúng ta muốn tìm ra hai điều từ bảng này. Đầu tiên, chúng ta muốn tìm điểm trung bình của tất cả học sinh đạt điểm trên 10 trong kỳ thi và thứ hai, chúng ta muốn tính điểm trung bình của tất cả học sinh trên 12 tuổi.

Để tính điểm trung bình của tất cả học sinh đạt điểm trên 10, chúng ta sẽ sử dụng công thức sau:

=AVERAGEIF(B2:B8,">10")

Hãy chú ý hai điều trong công thức này. Đầu tiên, tiêu chí phải luôn được đặt trong dấu ngoặc kép. Thứ hai, chúng tôi chỉ đưa vào hai đối số trong dấu ngoặc đơn vì không cần tham chiếu đến bất kỳ dữ liệu nào khác ở nơi khác trong phép tính.

Hàm này đã chọn chính xác điểm của Student A, B, D và E vì tất cả đều lớn hơn 10.

Để tính điểm trung bình của tất cả học sinh trên 12 tuổi, chúng ta sẽ sử dụng công thức sau:

=AVERAGEIF(C2:C8,">12",B2:B8)

Chú ý sự khác biệt giữa hai công thức. Trong đó phép tính trước chứa hai đối số thì phép tính này chứa ba đối số vì chúng ta đang đánh giá hai bộ dữ liệu.

Phần "C2:C8" của công thức yêu cầu Excel tìm tiêu chí trong phạm vi đó (độ tuổi của học sinh), phần ">12" yêu cầu Excel xác định bất kỳ giá trị nào trên 12 trong phạm vi C2:C8 (tuổi của học sinh) và "B2:B8" (điểm của học sinh) là phần được tính trung bình.

Hàm này đã xác định chính xác điểm của Student A, B, D và E vì các em đều trên 12 tuổi. Phép tính cũng bỏ qua các giá trị logic, đó là lý do tại sao nó không xét đến Studnet F, mặc dù các em đã trên 12 tuổi.

Lưu ý: Tiêu chí được sử dụng trong AVERAGEIF có thể sử dụng một trong 6 toán tử logic của Excel - đó là > (lớn hơn), < (nhỏ hơn), = (bằng), <= (nhỏ hơn hoặc bằng), >= (lớn hơn hoặc bằng) hoặc <> (không bằng) - và các ký tự đại diện (* và ?). Nếu bạn muốn bao gồm dấu chấm hỏi hoặc dấu hoa thị thực tế, hãy thêm dấu ngã (~) trước ký tự.

Cách sử dụng AVERAGEIFS trong Excel

Hàm AVERAGEIFs cho phép bạn đưa vào một số tiêu chí để đánh giá trước khi tính giá trị trung bình. Nó hoạt động bằng cú pháp sau:

=AVERAGEIFS(A,B,C)

Trong đó A xác định các ô có giá trị trung bình, B là các ô được sử dụng để xác định tiêu chí và C là tiêu chí. Có thể có tới 127 tiêu chí, vì vậy có thể sử dụng nhiều cặp ô (B) và tiêu chí (C).

Ví dụ này muốn tính điểm thi của học sinh ở Green House trên 10 tuổi.

Để làm điều này, chúng ta sẽ sử dụng công thức sau:

=AVERAGEIFS(B2:B8,C2:C8,"Green",D2:D8,">10")

“B2:B8” chứa dữ liệu cần tính trung bình (điểm của học sinh), “C2:C8” là phạm vi đầu tiên được kiểm tra với tiêu chí “Green” (nhà của học sinh) và “D2:D18” là phạm vi thứ hai cần kiểm tra với tiêu chí “>10” (tuổi học sinh).

Điều này đã tính điểm trung bình chính xác của Student D và G, vì cả hai đều ở Green House và trên 10 tuổi.

Những điều khác cần lưu ý về AVERAGEIFS:

• TRUE được tính là 1 và FALSE được tính là 0.
• Trong tiêu chí, bạn có thể sử dụng dấu chấm hỏi (?) làm ký tự đại diện để khớp với bất kỳ ký tự đơn nào hoặc dấu hoa thị (*) làm ký tự đại diện để khớp với bất kỳ chuỗi ký tự nào. Sử dụng dấu ngã (~) trước ký tự nếu bạn đang muốn xác định một dấu chấm hỏi hoặc dấu hoa thị thực sự.

Ngoài việc sử dụng AVERAGEIF và AVERAGEIFS, bạn có thể sắp xếp và lọc dữ liệu trong Excel để chỉ hiển thị một số số liệu nhất định trong bảng của mình.