Cách sử dụng COUNTIF, SUMIF và AVERAGEIF trong Excel

Trong ảnh chụp màn hình ở trên, bạn có thể thấy rằng chúng tôi có một danh sách các giao dịch ở phía bên trái. Nếu tôi muốn theo dõi chi tiêu của mình, tôi có thể sử dụng ba công thức này để theo dõi nó.

Ở phía bên phải, bảng Dining Out Expense (Chi phí ăn uống) sử dụng ba công thức để giúp tôi theo dõi chi phí của mình:

• COUNTIF – Dùng để đếm số lần “Nhà hàng” xuất hiện trong danh sách.
• SUMIF – Tính tổng chi phí cho các mặt hàng có nhãn “Nhà hàng”.
• AVERAGIEF – Tính trung bình tất cả các chi phí “Nhà hàng” của tôi trong danh sách.

Tổng quát hơn, đây là những gì mỗi công thức đó làm cho bạn và cách bạn có thể sử dụng chúng để tạo lợi thế cho mình:

• SUMIF – Thêm giá trị nếu một điều kiện được đáp ứng, chẳng hạn như cộng tất cả các giao dịch mua từ một danh mục.
• COUNTIF – Đếm số mục đáp ứng một điều kiện, chẳng hạn như đếm số lần tên xuất hiện trong danh sách.
• AVERAGIEF – Giá trị trung bình có điều kiện; chẳng hạn, bạn có thể tính trung bình điểm của mình chỉ cho các bài kiểm tra.

Những công thức này cho phép bạn thêm logic vào bảng tính của mình. Hãy xem cách sử dụng từng công thức.

Cách sử dụng SUMIF trong Excel

Sử dụng tab có tiêu đề SUMIF trong  ví dụ miễn phí cho phần hướng dẫn này.

Hãy nghĩ về SUMIF như một cách để thêm các giá trị đáp ứng quy tắc. Chúng tôi có thể thêm danh sách các giá trị từ một danh mục nhất định hoặc tất cả các giá trị lớn hơn hoặc nhỏ hơn một số tiền cụ thể.

Đây là cách thức hoạt động của công thức SUMIF:

=SUMIF(Cells to check, what to check for, Sum of cells that meet the rules)

Hãy quay lại ví dụ về chi phí nhà hàng của tôi để tìm hiểu công thức SUMIF. Dưới đây, tôi hiển thị danh sách các giao dịch của tôi trong tháng.

Tôi muốn biết hai điều:

1. Tổng số tiền tôi đã chi tiêu tại các nhà hàng trong tháng.
2. Tất cả các giao dịch mua lớn hơn $50 trong tháng, từ bất kỳ danh mục nào.

Thay vì thêm dữ liệu theo cách thủ công, chúng ta có thể viết hai công thức SUMIF để tự động hóa quá trình. Tôi sẽ đặt kết quả trong màu xanh lá cây Chi phí nhà hàng ô bên phải. Hãy xem làm thế nào.

Tổng chi phí nhà hàng

Để tìm tổng chi phí cho nhà hàng của mình, tôi sẽ cộng tất cả các giá trị với loại chi phí là “Nhà hàng” nằm trong cột B.

Đây là công thức tôi sẽ sử dụng cho ví dụ này:

=SUMIF(B2:B17,"Restaurant",C2:C17)

Lưu ý rằng mỗi phần được phân tách bằng dấu phẩy. Công thức này thực hiện ba điều quan trọng:

• Xem những gì trong các ô từ B2 đến B17 cho danh mục chi phí
• Sử dụng “Nhà hàng” cho tiêu chí tổng hợp
• Sử dụng các giá trị trong các ô từ C2 đến C17 để tính tổng số tiền

Khi tôi nhấn enter, Excel sẽ tính toán tổng chi phí nhà hàng của tôi. Sử dụng SUMIF, thật dễ dàng để tạo các số liệu thống kê nhanh này để giúp bạn giám sát các loại dữ liệu nhất định.

Mua hàng lớn hơn $50

Chúng tôi đã kiểm tra một danh mục cụ thể, nhưng bây giờ hãy tổng hợp tất cả các giá trị lớn hơn một số tiền từ bất kỳ danh mục nào. Trong trường hợp này, tôi muốn tìm tất cả các giao dịch mua trên 50 đô la.

Hãy viết một công thức đơn giản để tìm tổng của tất cả các giao dịch mua lớn hơn $50:

=SUMIF(C2:C17,">50")

Trong trường hợp này, công thức đơn giản hơn một chút: vì chúng ta đang tính tổng các giá trị giống nhau mà chúng ta đang kiểm tra (C2 đến C17), nên chúng ta chỉ cần chỉ định các ô đó. Sau đó, chúng tôi sẽ thêm dấu phẩy và “>50” để chỉ tổng các giá trị lớn hơn $50.

Ví dụ này sử dụng dấu lớn hơn, nhưng đối với điểm thưởng: hãy thử tính tổng tất cả các giao dịch mua nhỏ, chẳng hạn như tất cả các giao dịch mua từ 20 đô la trở xuống.

Cách sử dụng COUNTIF trong Excel

Sử dụng tab có tiêu đề COUNTIF trong ví dụ miễn phí cho phần hướng dẫn này.

Trong khi SUMIF được sử dụng để thêm các giá trị đáp ứng một điều kiện nhất định, COUNTIF sẽ đếm số lần thứ gì đó xuất hiện trong một tập hợp dữ liệu nhất định.

Đây là định dạng chung cho công thức COUNTIF:

=COUNTIF(cells to count, criteria to count)

Sử dụng cùng một bộ dữ liệu, hãy đếm hai mẩu thông tin chính:

• Số lần mua quần áo tôi đã thực hiện trong một tháng
• Số lần mua từ $100 trở lên

Số lần mua quần áo

COUNTIF đầu tiên của tôi sẽ xem xét loại chi phí và đếm số lần mua “Quần áo” trong các giao dịch của tôi.

Công thức cuối cùng sẽ là:

=COUNTIF(B2:B17,"Clothing")

Công thức đó nhìn vào cột “Loại chi phí”, đếm số lần nó nhìn thấy quần áo và đếm chúng. Kết quả là 2.

Số lần mua hàng trên $100

Bây giờ, hãy đếm số giao dịch có giá trị từ 100 đô la trở lên trong danh sách của tôi.

Đây là công thức tôi sẽ sử dụng:

=COUNTIF(C2:C17,">100")

Đây là một công thức gồm hai phần đơn giản: chỉ cần trỏ Excel vào danh sách dữ liệu cần đếm và quy tắc cần đếm. Trong trường hợp này, chúng tôi đang kiểm tra các ô từ C2 đến C17, cho tất cả các giá trị lớn hơn $100.

Cách sử dụng AVERAGEIF trong Excel

Sử dụng tab có tiêu đề AVERAGEIF trong ví dụ miễn phí cho phần hướng dẫn này.

Cuối cùng, hãy xem cách sử dụng một TRUNG BÌNH công thức. Hiện tại, không có gì ngạc nhiên khi AVERAGEIF có thể được sử dụng để tính trung bình các giá trị cụ thể, dựa trên điều kiện mà chúng tôi sẽ cung cấp cho Excel.

Định dạng cho công thức AVERAGEIF là:

=AVERAGEIF(Cells to check, what to check for, Average of cells that meet the rules)

Định dạng của công thức AVERAGEIF gần giống nhất với công thức SUMIF.

Hãy sử dụng công thức AVERAGEIF để tính hai số liệu thống kê chính về chi tiêu của tôi:

1. Trung bình chi phí nhà hàng của tôi.
2. Trung bình của tất cả các chi phí ít hơn $25.

Trung bình số lần mua nhà hàng

Để tính trung bình chi phí nhà hàng của tôi, Tôi sẽ viết một công thức AVERAGEIF để tính trung bình tất cả các số tiền dựa trên danh mục.

=AVERAGEIF(B2:B17,"Restaurant",C2:C17)

Có ba phần trong công thức này, mỗi phần được phân tách bằng dấu phẩy:

• B2:B17 chỉ định các ô để kiểm tra điều kiện. Vì loại chi phí được chỉ định trong cột này.
• “Nhà hàng” cung cấp cho công thức một cái gì đó để tìm kiếm.
• Cuối cùng, C2:C17 là các ô tính trung bình trong tính toán của chúng tôi.

Cuối cùng, Excel tính trung bình chi phí cho các chuyến đi nhà hàng của tôi. Tôi đã cho nó công thức đó.

Bạn cũng có thể thử công thức này bằng cách thay thế “Nhà hàng” bằng một danh mục khác, chẳng hạn như “Quần áo”.

Chi phí trung bình Ít hơn $25

Nếu tôi đang theo dõi các giao dịch mua nhỏ hơn của mình và muốn biết mức trung bình của mình, tôi có thể viết AVERAGEIF cho tất cả các giao dịch mua ít hơn một số tiền.

Đây là công thức mà tôi sẽ sử dụng để làm điều đó:

=AVERAGEIF(C2:C17,"<25")

Công thức đơn giản này chỉ kiểm tra các giá trị trong cột C và tính trung bình tất cả các giá trị lớn hơn $25.

Công thức Excel AVERAGEIF đơn giản cho chi phí Ít hơn $25.

Tóm tắt và tiếp tục học

Trong hướng dẫn này, bạn đã học cách sử dụng ba công thức toán học có điều kiện để xem lại dữ liệu của mình. Cho dù bạn đang tính tổng, đếm hay tính trung bình cộng dữ liệu, các hàm này đều là những kỹ năng Excel nâng cao mà bạn có thể sử dụng thành thạo.

Đối với tất cả các công thức “IF” trong hướng dẫn này, điểm mấu chốt chính là bạn có thể áp dụng các điều kiện cho phép tính của mình trong Excel.