Cách sử dụng COUNTIFS, SUMIFS, AVERAGEIFS trong Excel

Ba công thức được sử dụng phổ biến nhất trong Excel thực hiện các phép tính toán học đơn giản là ĐẾM, TÓM TẮT và TRUNG BÌNH CỘNG. Cho dù bạn đang quản lý ngân sách tài chính trong Excel hay chỉ đơn giản là theo dõi kỳ nghỉ tiếp theo của mình, bạn có thể đã sử dụng một trong các chức năng này trước đây.

Trong bài viết này, chúng ta sẽ đi qua những điều cơ bản của ba chức năng này và các đối tác hữu ích và có liên quan của chúng: COUNTIFS, SUMIFS và AVERAGEIFS.

Giả sử chúng tôi đang bắt đầu kinh doanh trực tuyến mới bán điện thoại di động và chúng tôi có một bảng liệt kê doanh số mà chúng tôi đã thực hiện trong hai tháng đầu. Tải xuống ví dụ bảng tính Excel tại đây.

QUẬN Excel, SUM và AVERAGE

Để biết chúng tôi đã bán được bao nhiêu điện thoại di động, chúng tôi có thể nhanh chóng sử dụng ĐẾM công thức như hình dưới đây:

= COUNT (E 2: E16)

Mặt khác, để có được tổng số tiền bán hàng mà chúng tôi đã thực hiện, chúng tôi có thể sử dụng TÓM TẮT công thức như hình dưới đây:

= SUM (E 2: E16)

Cuối cùng, để tìm ra doanh số trung bình mà chúng tôi đã tạo cho tất cả các điện thoại, chúng tôi có thể sử dụng TRUNG BÌNH CỘNG công thức như sau:

= AVERAGE (E 2: E16)

Kết quả sẽ như sau:

Các công thức COUNT, SUM và AVERAGE sẽ chỉ hoạt động đối với các bản ghi có giá trị ô ở định dạng số. Bất kỳ bản ghi nào trong phạm vi công thức (nghĩa là. E 2: E16 trong ví dụ này) không ở định dạng số sẽ bị bỏ qua.

Vì vậy, hãy đảm bảo rằng tất cả các ô trong công thức COUNT, SUM và AVERAGE đều được định dạng là Con số, không phải Bản văn. Cố gắng sử dụng cùng một công thức, nhưng với E: E như phạm vi thay vì E 2: E16. Nó sẽ trả về kết quả như trước vì nó bỏ qua tiêu đề (tức là. Giá bán), ở định dạng văn bản.

Bây giờ, điều gì sẽ xảy ra nếu chúng ta muốn biết số lượng bán hàng, tổng số lượng bán hàng và số lượng bán hàng trung bình trên mỗi điện thoại, chỉ dành cho những người bán ở Hoa Kỳ? Đây là nơi mà COUNTIFS, SUMIFS và AVERAGEIFS đóng vai trò quan trọng. Quan sát công thức dưới đây:

QUẬN

Phân tích công thức:

1. = COUNTIFS ( - Các Ăn ý chỉ ra sự bắt đầu của một công thức trong ô và QUẬN là phần đầu tiên của hàm Excel mà chúng ta đang sử dụng.
2. Đ2: D16 - Đề cập đến phạm vi dữ liệu để kiểm tra xem liệu nó có thỏa mãn các tiêu chí được đưa vào công thức đếm không.
3. "HOA KỲ" - Tiêu chí cần tìm trong phạm vi dữ liệu được chỉ định (Đ2: D16)
4. ) - Dấu đóng cho biết kết thúc công thức.

Công thức trả về 6 là số lượng bán cho các sản phẩm được vận chuyển từ kho của Hoa Kỳ.

TÓM TẮT

Phân tích công thức:

1. = TỔNG HỢP ( - Các Ăn ý chỉ ra sự bắt đầu của công thức một lần nữa.
2. E 2: E16 - Đề cập đến phạm vi dữ liệu mà chúng tôi muốn tổng cộng, ví dụ: giá bán trong ví dụ của chúng tôi.
3. Đ2: D16 - Đề cập đến phạm vi dữ liệu để kiểm tra xem liệu nó có thỏa mãn các tiêu chí được đưa vào tổng số tiền không.
4. "HOA KỲ" - Tiêu chí cần tìm trong phạm vi dữ liệu được chỉ định (Đ2: D16)
5. ) - Dấu đóng cho biết kết thúc công thức.

Công thức cho thấy $ 6.050 tổng doanh số được thực hiện cho các sản phẩm được vận chuyển từ kho của Hoa Kỳ.

AVERAGEIFS

Phân tích công thức:

1. = AVERAGEIFS ( - Các Ăn ý chỉ ra sự khởi đầu của công thức.
2. E 2: E16 - Đề cập đến phạm vi dữ liệu mà chúng tôi muốn trung bình. Trong ví dụ này, chúng tôi muốn có được doanh số trung bình cho tất cả các điện thoại được bán ở Hoa Kỳ.
3. Đ2: D16 - Đề cập đến phạm vi dữ liệu để kiểm tra xem liệu nó có thỏa mãn các tiêu chí được đưa vào công thức trung bình không.
4. "HOA KỲ" - Tiêu chí cần tìm trong phạm vi dữ liệu được chỉ định
5. ) - Dấu đóng cho biết các đầu của công thức.

Công thức cho thấy chúng tôi đã bán sản phẩm cho khoảng $ 1,008 mỗi điện thoại ở Mỹ.

Tất cả ba công thức có thể mất nhiều hơn một tiêu chí. Ví dụ: nếu chúng ta muốn biết các số liệu tương tự (ví dụ:. ĐẾM, TÓM TẮT và TRUNG BÌNH CỘNG) cho các sản phẩm được bán trong Hoa Kỳ, nhưng đặc biệt chỉ dành cho Samsung thương hiệu, chúng ta chỉ cần thêm phạm vi dữ liệu để được kiểm tra theo tiêu chí của nó.

Vui lòng xem ví dụ bên dưới nơi tiêu chí thứ hai được thêm vào kiểm tra tiêu chí ban đầu. (Văn bản màu xanh biểu thị tiêu chí thứ nhất và màu đỏ biểu thị tiêu chí thứ hai)

= COUNTIFS (D2: D16, "Hoa Kỳ", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "Hoa Kỳ", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "Hoa Kỳ", B2: B16, "Samsung")

Bạn sẽ nhận thấy rằng Excel cũng có QUẬN, TÓM TẮT và AVERAGEIF công thức không có hậu tố "S". Chúng được sử dụng tương tự như QUẬN, TÓM TẮT và AVERAGEIFS. Tuy nhiên, những người không có hậu tố "S" trong công thức có giới hạn chỉ cho phép một tiêu chí cho mỗi công thức.

Vì cú pháp hơi khác nhau, tôi khuyên bạn nên sử dụng QUẬN, TÓM TẮT và AVERAGEIFS chỉ khi nó có thể được sử dụng cho một trong hai tiêu chí trở lên, nếu cần thiết. Thưởng thức!