Cách Sử Dụng COUNTIF, SUMIF, Và AVERAGEIF Trong Excel

Học Microsoft Excel chủ yếu xoay quanh các công thức và các hàm. Kết hợp đủ những cái này, và bạn có thể làm bất cứ điều gì với một bảng tính.

Trong hướng dẫn này, bạn sẽ tìm hiểu cách làm thế nào để sử dụng ba công thức Excel mạnh mẽ: SUMIF, COUNTIF, và AVERAGEIF.

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 tập trung vào chi tiêu của tôi, tôi có thể sử dụng ba công thức này để theo dõi nó.

Ở phía bên phải, hộp Dining Out Expense sử dụng ba công thức để giúp tôi theo dõi các chi phí của tôi:

• COUNTIF - được sử dụng để đếm số lần mà "Restaurant" xuất hiện trong danh sách.
• SUMIF - tính toán tất cả chi phí cho các mục có gắn nhãn "Restaurant."
• AVERAGIEF - Tính trung bình tất cả các chi phí "Restaurant" 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à làm thế nào bạn có thể sử dụng chúng:

• SUMIF - Cộng các giá trị nếu thoả mãn một điều kiện, chẳng hạn như cộng tất cả chi phí mua hàng từ một danh mục.
• COUNTIF - đếm số mặt hàng thoả một điều kiện, chẳng hạn như đếm số lần một cái tên xuất hiện trong danh sách.
• AVERAGEIF - Tính trung bình có điều kiện; ví dụ, bạn có thể tính trung bình các loại của bạn để kiểm tra.

Các công thức này cho phép bạn thêm logic vào bảng tính của bạn. Hãy xem xét cách làm thế nào để sử dụng mỗi công thức.

COUNTIF, SUMIF, và AVERAGEIF trong Excel (Video hướng dẫn nhanh)

Các video hướng dẫn là một trong những cách tốt nhất để xem và tìm hiểu một kỹ năng mới, bao gồm làm quen với ba công thức quan trọng này trong Microsoft Excel. Hãy xem video dưới đây để xem cách tôi làm việc trong Excel. Hãy chắc là bạn tải về bảng tính ví dụ miễn phí để sử dụng với hướng dẫn này.

Nếu bạn muốn tìm hiểu với bài viết, hướng dẫn từng bước, hãy đọc tiếp. Tôi sẽ chia sẻ những lời khuyên về cách sử dụng các công thức này và các ý tưởng cho thấy tại sao chúng hữu ích.

Cách sử dụng SUMIF trong Excel

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

Hãy hình dung về SUMIF như là một cách để cộng các giá trị đáp ứng quy tắt đặt ra. Chúng ta có thể cộng một danh sách các giá trị từ một thể loại 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ể.

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

=SUMIF(Các ô cần kiểm tra, điều kiện kiểm tra, tổng các ô đáp ứng các quy tắt)

Hãy trở 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 liệt kê một 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 cộng những gì tôi đã chi tiêu tại nhà hàng trong tháng.
2. Tất cả các chi tiêu lớn hơn $50 trong tháng, từ bất kỳ thể loại nào.

Thay vì thêm dữ liệu bằng tay, chúng ta có thể viết hai công thức SUMIF để tự động hoá quá trình. Tôi sẽ đặt kết quả trong hộp Restaurant Expense màu xanh lá cây ở bên phải. Hãy xem xét cách làm thế nào.

Tổng chi phí nhà hàng

Để tìm tổng chi phí nhà hàng của tôi, tôi sẽ tổng hợp tất cả các giá trị với các loại chi phí "Restaurant", nó là ở cột B.

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

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

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

• Nhìn vào những gì có trong ô B2 đến B17 cho các loại chi phí
• Sử dụng "Restaurant" làm tiêu chí cho những gì cần tổng hợp
• Sử dụng các giá trị trong ô C2 đến C17 để cộng tất cả số tiền

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

Chi phí lớn hơn $50

Chúng ta đã kiểm tra cho một thể loại cụ thể, nhưng bây giờ chúng ta 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ỳ thể loại. Trong trường hợp này, tôi muốn tìm thấy tất cả các chi phí lớn hơn $50.

Hãy viết một công thức đơn giản để tìm tổng của tất cả các chi phí lớn hơn $50:

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

Trong trường hợp này, công thức có một chút đơn giản hơn: vì chúng ta đang tổng hợp các giá trị tương tự như chúng ta đang thử nghiệm (C2-C17), chúng ta chỉ cần xác định các ô đó. Sau đó, chúng ta sẽ thêm một dấu phẩy và "> 50" để tổng hợp chỉ các giá trị lớn hơn 50$.

Ví dụ này sử dụng một ký hiệu lớn hơn, nhưng bổ sung thêm: hãy thử tổng hợp tất cả các chi phí nhỏ, chẳng hạn như tất cả các chi phí dưới hoặc bằng 20$.

Cách sử dụng COUNTIF trong Excel

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

Trong khi SUMIF được sử dụng để thêm giá trị thoả 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.

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

=COUNTIF(các ô cần đếm, tiêu chí để đếm)

Sử dụng cùng một tập hợp dữ liệu, hãy đếm hai mẩu thông tin quan trọng:

• Số lượng mua quần áo mà tôi đã thực hiện trong một tháng
• Số lượng mua 100$ hoặc lớn hơn

Số lượng mua quần áo

COUNTIF trước tiên sẽ tìm kiếm loại chi phí và đếm số lượng các chi phí "Clothing" trong các giao dịch của tôi.

Công thức sau cùng sẽ là:

=COUNTIF(B2:B17,"Clothing")

Công thức đó tìm kiếm cột "Expense Type", đếm số lần mà nó thấy Clothing. Kết quả là 2.

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

Bây giờ, chúng ta hãy đếm số lượng các giao dịch là 100$ hoặc lớn hơn trong danh sách của tôi.

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

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

Đây là một công thức đơn giản, công thức gồm hai phần: đơn giản là chỉ định cho Excel danh sách dữ liệu để đếm, và các quy tắc để đếm. Trong trường hợp này, chúng ta đang kiểm tra ô C2 đến C17, đối với 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 bảng tính ví dụ miễn phí cho phần này của hướng dẫn.

Tiếp theo, chúng ta hãy xem cách làm thế nào để sử dụng một công thức AVERAGEIF. Bây giờ, không ngạc nhiên khi AVERAGEIF có thể sử dụng để tính trung bình các giá trị cụ thể, dựa trên một điều kiện mà chúng ta sẽ cung cấp cho Excel.

Các định dạng cho một công thức AVERAGEIF là:

=AVERAGEIF(Ô cần kiểm tra, những thứ cần kiểm tra, các ô để tính trung bình thoả điều kiện)

Định dạng công thức AVERAGEIF là gần như tương tự công thức SUMIF.

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

1. Tính 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 chi phí 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ả số tiền dựa trên các thể loại.

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

Có ba phần cho công thức này, mỗi phần cách nhau bằng dấu phẩy:

• B2:B17 chỉ định các ô để kiểm tra thoả một điều kiện. Vì các loại chi phí được xác định trong cột này.
• "Restaurant" 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 ta.

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

Bạn cũng có thể thử công thức này bằng cách thay thế "Restaurant" với các thể loại khác, như "Clothing."

Tính trung bình của các chi phí ít hơn 25$

Nếu tôi muốn theo dõi chi phí mua hàng nhỏ hơn của tôi và muốn tính trung bình, tôi có thể viết một AVERAGEIF cho tất cả chi phí í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ày đơn giản chỉ cần kiểm tra các giá trị trong cột C, và tính trung bình tất cả giá trị lớn hơn 25$.

Tổng kết

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

Đối với tất cả các công thức "IF" trong hướng dẫn này, quan trọng là bạn có thể áp dụng các điều kiện để bạn tính toán trong Excel.

Học, học nữa, học mãi. Dưới đây là thêm ba hướng dẫn Excel để bạn tiếp tục nghiên cứu:

• Ngoài COUNTIF, SUMIF, và AVERAGEIF, còn có những câu lệnh "IF" chung có thể được sử dụng cho các điều kiện khác. Hãy theo dõi hướng dẫn của chúng tôi về Cách sử dụng các câu lệnh IF đơn giản.
• Học cách sử dụng Ngày và Giờ trong Excel kết hợp với các công thức này để làm việc với các giá trị dựa trên ngày tháng.
• Hàm Excel VLOOKUP có thể được sử dụng để tìm các giá trị phù hợp từ nhiều danh sách. Tìm hiểu thêm về Cách sử dụng hàm VLOOKUP trong hướng dẫn này.

Bạn có nghĩ ra những cách khác để sử dụng các công thức này để thêm logic và điều kiện vào bảng tính của bạn không? Hãy cho tôi biết với một bình luận ở dưới đây nhé.