Hàm AVERAGEIF kết hợp hàm IF và hàm AVERAGE trong Excel. Sự kết hợp này cho phép bạn tìm trung bình hoặc số học trung bình của các giá trị đó trong một phạm vi dữ liệu được chọn đáp ứng các tiêu chí cụ thể.
Phần IF của hàm xác định dữ liệu nào đáp ứng các tiêu chí được chỉ định và phần AVERAGE tính toán giá trị trung bình hoặc trung bình.
Thông thường, AVERAGE IF được sử dụng với các hàng dữ liệu được gọi là các bản ghi. Trong một bản ghi , tất cả dữ liệu trong mỗi ô trong hàng có liên quan - chẳng hạn như tên, địa chỉ và số điện thoại của công ty.
AVERAGE NẾU tìm kiếm các tiêu chí cụ thể trong một ô hoặc trường trong bản ghi và, nếu nó tìm thấy một kết quả phù hợp, nó sẽ tính trung bình dữ liệu hoặc dữ liệu trong trường được chỉ định khác trong cùng một bản ghi.
Cách hoạt động của hàm AVERAGEIF
Hướng dẫn này sử dụng hàm AVERAGE IF để tìm doanh số trung bình hàng năm cho khu vực bán hàng phía Đông trong một tập hợp các bản ghi dữ liệu.
Thực hiện theo các bước trong các chủ đề hướng dẫn dưới đây hướng dẫn bạn tạo và sử dụng hàm AVERAGE IF được thấy trong hình trên để tính toán doanh thu hàng năm trung bình.
Chủ đề hướng dẫn
- Nhập dữ liệu hướng dẫn
- Cú pháp của hàm AVERAGE IF
- Bắt đầu chức năng AVERAGE IF
- Nhập đối số phạm vi
- Nhập đối số tiêu chí
- Nhập đối số Average_range
- Và câu trả lời là ..
Nhập dữ liệu hướng dẫn
Bước đầu tiên để sử dụng hàm AVERAGE IF trong Excel là nhập dữ liệu .
Nhập dữ liệu vào các ô C1 đến E11 của trang tính Excel như trong hình trên.
Lưu ý: Các hướng dẫn hướng dẫn không bao gồm các bước định dạng cho trang tính.
Điều này sẽ không can thiệp vào việc hoàn thành hướng dẫn. Bảng tính của bạn sẽ trông khác với ví dụ được hiển thị, nhưng hàm AVERAGE IF sẽ cho bạn kết quả tương tự.
Thông tin về các tùy chọn định dạng tương tự như các tùy chọn ở trên có sẵn trong Hướng dẫn định dạng Excel cơ bản này.
Cú pháp của hàm AVERAGEIF
Trong Excel, cú pháp của hàm tham chiếu đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm .
Cú pháp cho AVERAGEIF là:
= AVERAGEIF (Phạm vi, Tiêu chí, Average_range)
Các đối số của hàm AVERAGEIF
Các đối số của hàm cho biết hàm nào điều kiện đang được kiểm tra và phạm vi dữ liệu nào trung bình khi điều kiện đó được đáp ứng.
Phạm vi - nhóm ô mà chức năng tìm kiếm.
Tiêu chí - giá trị này được so sánh với dữ liệu trong Phạm vi. Nếu tìm thấy kết quả phù hợp thì dữ liệu tương ứng trong Average_range sẽ được tính trung bình. Dữ liệu thực tế hoặc tham chiếu ô đến dữ liệu có thể được nhập cho đối số này.
Average_range (tùy chọn) - dữ liệu trong phạm vi ô này được tính trung bình khi các kết hợp được tìm thấy giữa các đối số Phạm vi và Tiêu chí. Nếu đối số Average_range bị bỏ qua, dữ liệu được đối sánh trong đối số Phạm vi sẽ được tính trung bình thay thế.
Bắt đầu chức năng AVERAGEIF
Mặc dù có thể chỉ cần gõ hàm AVERAGE IF vào một ô , nhiều người thấy dễ dàng hơn khi sử dụng hộp thoại của hàm để thêm hàm vào một trang tính .
Các bước hướng dẫn
- Bấm vào ô E12 để biến nó thành ô hiện hoạt . Đây là nơi chúng ta sẽ nhập hàm AVERAGE IF.
- Nhấp vào tab Công thức của ruy-băng .
- Chọn Các hàm khác> Thống kê từ ruy-băng để mở danh sách thả xuống chức năng.
- Nhấp vào AVERAGE IF trong danh sách để hiển thị hộp thoại của hàm AVERAGE IF.
Dữ liệu mà chúng ta nhập vào ba hàng trống trong hộp thoại sẽ tạo thành các đối số của hàm AVERAGE IF.
Các đối số này cho biết hàm nào chúng ta đang kiểm tra điều kiện nào và phạm vi dữ liệu nào là trung bình khi điều kiện được đáp ứng.
Nhập đối số phạm vi
Trong hướng dẫn này, chúng tôi đang tìm kiếm để tìm ra doanh thu hàng năm trung bình cho khu vực bán hàng Đông.
Đối số Phạm vi cho hàm AVERAGE IF biết nhóm ô nào cần tìm khi cố gắng tìm tiêu chí được chỉ định - Đông.
Các bước hướng dẫn
- Trong hộp thoại , nhấp vào dòng Range .
- Đánh dấu ô C3 đến C9 trong trang tính để nhập các tham chiếu ô này làm phạm vi tìm kiếm theo hàm.
Nhập đối số tiêu chí
Trong ví dụ này nếu dữ liệu trong phạm vi C3: C12 bằng với East thì tổng doanh thu cho bản ghi đó sẽ được tính trung bình theo hàm .
Mặc dù dữ liệu thực tế - chẳng hạn như từ Đông có thể được nhập vào hộp thoại cho đối số này, tốt nhất là thêm dữ liệu vào một ô trong trang tính và sau đó nhập tham chiếu ô đó vào hộp thoại.
Các bước hướng dẫn
- Nhấp vào dòng tiêu chí trong hộp thoại .
- Nhấp vào ô D12 để nhập tham chiếu ô đó. Hàm sẽ tìm kiếm phạm vi được chọn trong bước trước đó cho dữ liệu khớp với tiêu chí này.
- Cụm từ tìm kiếm (Đông) sẽ được thêm vào ô D12 ở bước cuối cùng của hướng dẫn.
Tham khảo ô Tăng tính linh hoạt
Nếu tham chiếu ô, chẳng hạn như D12, được nhập làm Đối số tiêu chí, hàm AVERAGE IF sẽ tìm kiếm các kết quả khớp với bất kỳ dữ liệu nào đã được nhập vào ô đó trong trang tính.
Vì vậy, sau khi tìm thấy doanh thu trung bình cho khu vực phía Đông, sẽ dễ dàng tìm thấy doanh thu trung bình cho một khu vực bán hàng khác đơn giản bằng cách thay đổi Đông sang Bắc hoặc Tây. Chức năng sẽ tự động cập nhật và hiển thị kết quả mới.
Nhập đối số Average_range
Đối số Average_range là nhóm các ô mà hàm đó là trung bình khi nó tìm thấy một đối sánh trong đối số Phạm vi được xác định trong bước 5 của hướng dẫn.
Đối số này là tùy chọn và, nếu bỏ qua, Excel tính trung bình các ô được chỉ định trong đối số Phạm vi.
Vì chúng tôi muốn doanh thu trung bình cho khu vực bán hàng Đông, chúng tôi sử dụng dữ liệu trong cột Tổng doanh số làm đối số Average_range.
Các bước hướng dẫn
- Nhấp vào dòng Average_range trong hộp thoại.
- Đánh dấu các ô từ E3 đến E9 trên bảng tính. Nếu các tiêu chí được chỉ định trong bước trước khớp với bất kỳ dữ liệu nào trong phạm vi đầu tiên (C3 đến C9), hàm sẽ tính trung bình dữ liệu trong các ô tương ứng trong dải ô thứ hai này.
- Nhấn OK để đóng hộp thoại và hoàn thành hàm AVERAGE IF.
- A # DIV / 0! lỗi sẽ xuất hiện trong ô E12 - ô mà chúng tôi đã nhập hàm vì chúng tôi chưa thêm dữ liệu vào trường Tiêu chí (D12).
Thêm tiêu chí tìm kiếm
Bước cuối cùng trong hướng dẫn này là thêm tiêu chí mà chúng tôi muốn hàm phù hợp.
Trong trường hợp này, chúng tôi muốn tìm doanh số trung bình hàng năm cho đại diện bán hàng ở khu vực phía Đông, do đó chúng tôi sẽ thêm cụm từ Đông vào D12 - ô được xác định trong hàm có chứa đối số tiêu chí.
Các bước hướng dẫn
- Trong ô D12 loại Đông và nhấn phím Enter trên bàn phím.
- Câu trả lời $ 59,641 sẽ xuất hiện trong ô E12. Vì tiêu chuẩn của phương Đông bằng nhau được đáp ứng trong bốn ô (C3 đến C6), các số trong các ô tương ứng trong cột E (E3 đến E6) được tính trung bình.
- Khi bạn bấm vào ô E12, hàm hoàn chỉnh
= AVERAGEIF (C3: C9, D12, E3: E9) xuất hiện trong thanh công thức phía trên trang tính . - Để tìm mức trung bình bán hàng cho các khu vực bán hàng khác, hãy nhập tên của khu vực, chẳng hạn như Bắc trong ô E12 và nhấn phím Enter trên bàn phím.
- Mức trung bình cho khu vực bán hàng đó sẽ xuất hiện trong ô E12.