.st0{fill:#FFFFFF;}

Hướng dẫn cách viết hàm COUNTIFS thống kê theo nhiều điều kiện 

 Tháng Chín 19, 2021

By  Lê Đình Chi

Để thực hiện yêu cầu thống kê (đếm) theo một hay nhiều điều kiện trong Excel, chúng ta thường sử dụng tới các hàm COUNTIF, COUNTIFS. Trong bài viết này, Zik.vn sẽ hướng dẫn các bạn cách sử dụng các hàm COUNTIFS một cách chi tiết và dễ hiểu nhất.

Cú pháp của hàm COUNTIFS trong Excel

Hàm COUNTIFS có cú pháp như sau:

=COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)

Trong đó:

  • criteria_range1 – đây là vùng ô có chứa điều kiện cần tìm (tìm ở đâu).
  • criteria1 – đây là điều kiện cần tìm (tìm cái gì)
  • [criteria_range2, criteria2]… – đây là các điều kiện bổ sung, không bắt buộc. Nếu tìm theo nhiều hơn 1 điều kiện thì mỗi điều kiện phải kèm theo 1 vùng điều kiện.

Khi viết hàm COUNTIFS, bạn hãy chú ý rằng Excel có phần gợi ý công thức (ở ngay bên dưới vị trí ô mà bạn đặt công thức). Khi viết bạn có thể dựa theo gợi ý này để nhớ mình đang viết cho thành phần nào, có ý nghĩa gì, sử dụng dấu nào để ngăn cách các thành phần trong hàm:

cấu trúc hàm countifs trong excel

Gợi ý khi viết hàm COUNTIFS của Excel

Một số lưu ý khi sử dụng hàm COUNTIFS

Khi sử dụng hàm COUNTIFS trong Excel, bạn cần chú ý những nội dung sau:

  1. Bạn có thể sử dụng hàm COUNTIFS để thống kê theo 1 điều kiện duy nhất hoặc cho nhiều điều kiện cùng lúc. Trong trường hợp thống kê theo nhiều điều kiện thì tại những dòng thỏa mãn đồng thời tất cả các điều kiện sẽ được tính là 1 lần.
  2. Mỗi điều kiện phải kèm theo 1 vùng điều kiện. Các vùng điều kiện phải có độ rộng bằng nhau.
  3. Bạn có thể đếm trên những vùng dữ liệu liền kề hoặc không liền kề (tức là không bắt buộc phải liền kề nhau, nhưng bắt buộc phải có độ rộng bằng nhau).
  4. Nếu điều kiện đếm là ô trống, hàm COUNTIFS coi đó như là giá trị bằng 0.
  5. Bạn có thể sử dụng các ký tự đặc biệt (wildcard characters) trong các các điều kiện như dấu hoa thị (*) và dấu chấm hỏi (?) để thống kê dựa theo một số ký tự đại diện, thay vì cả đoạn text cụ thể.

Ví dụ về cách sử dụng hàm COUNTIFS nhiều điều kiện

Để giúp bạn hình dung rõ hơn về các ứng dụng của hàm COUNTIFS, chúng ta hãy xem các tình huống trong bài tập dưới đây. Các bạn có thể tải file ở cuối bài viết này.

Cách viết hàm COUNTIFS nhiều điều kiện đồng thời (dạng AND)

Trong yêu cầu đầu tiên, bạn cần thống kê số mặt hàng có tồn kho đầu tháng >0 và có số bán ra trong tháng bằng 0. Điều này có nghĩa là việc thống kê phải đồng thời theo 2 điều kiện:

bai-tap-dung-ham-countifs-thong-ke-theo-nhieu-dieu-kien

  • Điều kiện 1: tồn kho đầu tháng > 0
  • Điều kiện 2: số bán ra trong tháng bằng 0

Tức là thống kê có bao nhiêu mặt hàng (trong số 9 mặt hàng) có tồn kho nhưng không bán được sản phẩm nào trong tháng.

Với 2 điều kiện này, bạn phải sử dụng hàm COUNTIFS (chứ không phải hàm COUNTIF, vì hàm COUNTIF chỉ viết được với 1 điều kiện). Cách viết như sau:

Bước 1: Xác định các cặp: vùng chứa điều kiện và điều kiện

  • Điều kiện 1: tồn kho đầu tháng >0. Trong đó:
    • Điều kiện là giá trị >0 (ô H4 – nếu lớn hơn 0 thì phải viết thêm dấu > kèm theo số 0)
    • vùng chứa điều kiện này là cột tồn kho đầu tháng, vùng ô B2:B9
  • Điều kiện 2: số bán ra trong tháng = 0, trong đó:
    • Điều kiện là giá trị 0 (ô H6 – nếu bằng 0 thì không cần viết dấu bằng, chỉ cần viết số 0)
    • vùng chứa điều kiện này là cột Bán trong tháng, vùng ô C2:C9

Bước 2: đưa các cặp Vùng điều kiện, điều kiện vào công thức theo đúng vị trí các thành phần ta có:

=COUNTIFS(B2:B9, H4, C2:C9, H6)

Kết quả là:

ket-qua-bai-tap-dung-ham-countifs-thong-ke-theo-nhieu-dieu-kien

Giải thích ý nghĩa của hàm COUNTIFS

Bạn có biết hàm COUNTIFS đã hoạt động như thế nào để ra được kết quả bằng 2 như trong ví dụ trên không? Cách giải thích như sau:

Đầu tiên hàm sẽ xét điều kiện 1: tồn kho đầu tháng >0

Khi đó các ô chứa giá trị lớn hơn 0 trong vùng B2:B9 sẽ được hàm ghi nhận vào bộ nhớ:

giai-thich-y-nghia-ham-countifs-hinh-1

Tương tự như vậy, với điều kiện 2, các ô trong vùng C2:C9 có giá trị bằng 0  sẽ được ghi nhận vào bộ nhớ:

giai-thich-y-nghia-ham-countifs-hinh-2

Tại những dòng mà đồng thời thỏa mãn cả 2 điều kiện sẽ được đếm là 1 lần, tổng số lần đếm được là kết quả của hàm.

giai-thich-y-nghia-ham-countifs-hinh-3

Hãy chú ý là trong trường hợp này, các điều kiện được thỏa mãn đồng thời thì mới tính. Do đó đây là cách viết khi hàm COUNTIFS xét các điều kiện diễn ra đồng thời (dạng AND)

Cách viết hàm COUNTIFS với điều kiện không đồng thời (dạng OR)

Trong thực tế không phải lúc nào yêu cầu thống kê theo nhiều điều kiện cũng là dạng AND, mà có thể chấp nhận nhiều khả năng: Hoặc thỏa mãn điều kiện X, hoặc thỏa mãn điều kiện Y thì đều được thống kê. Hãy xét vị dụ sau đây:

bai-tap-ham-countifs-viet-dieu-kien-dang-OR

Trong ví dụ này chúng ta cần thống kê theo 2 điều kiện, nhưng trên cùng 1 cột:

  • Điều kiện 1: nhân viên tên là Nam
  • Điều kiện 2: nhân viên tên là Hoa

2 điều kiện này không phải diễn ra đồng thời trên cùng 1 dòng, mà chỉ cần dòng nào có tên nhân viên là Nam hoặc tên là Hoa thì đều được tính. Như vậy trường hợp này là thống kê theo điều kiện dạng không đồng thời (dạng Hoặc). Dạng này còn được gọi là tổng của nhiều khả năng.

Cách làm:

Cách 1: viết nhiều hàm COUNTIFS riêng lẻ rồi cộng lại

Đây là một cách đơn giản và dễ hiểu. Nếu nhiều khả năng xảy ra thì bạn xét từng khả năng, sau đó cộng lại:

  • Điều kiện 1: nhân viên tên là Nam, ta có 2 cách viết như sau:

Bạn có thể viết trực tiếp chữ “Nam” vào điều kiện của hàm, nhưng phải đặt chữ này trong cặp dấu nháy kép.

=COUNTIFS(B2:B9, “Nam”)

Hoặc bạn viết đạng tham chiếu tới ô H4 là ô đang có sẵn chữ Nam

=COUNTIFS(B2:B9, H4)

  • Điều kiện 2: nhân viên tên là Hoa, ta có thể sử dụng cách viết tham chiếu theo điều kiện trong ô H6 như sau:

=COUNTIFS(B2:B9, H6)

Kết quả là tổng của 2 hàm COUNTIFS cộng lại với nhau, ta được:

ket-qua-bai-tap-ham-countifs-voi-dieu-kien-dang-or

kết quả hàm countifs với điều kiện dạng OR

Cách 2: viết hàm SUM kết hợp hàm COUNTIFS dạng công thức mảng

Có một cách viết khác sẽ nhanh hơn nhiều mà bạn có thể dùng trong trường hợp này, đó là viết hàm COUNTIFS dưới dạng nhiều điều kiện trong 1 mảng. Cách viết này chỉ áp dụng được khi bạn đặt hàm COUNTIFS lồng bên trong hàm SUM như sau:

Cú pháp:

=SUM( COUNTIFS (criteria_range, {“criteria1”, “criteria2”, “criteria3”, …} ) )

Trong đó:

  • criteria_range: là vùng chứa điều kiện
  • criteria1, criteria2, criteria3… là các điều kiện có thể nằm trong vùng chứa điều kiện
  • Các criteria1, criteria2, criteria3… phải đặt bên trong cặp dấu ngoặc móc { } để thể hiện đó là 1 mảng.

Chú ý:

  • Các điều kiện phải đặt bên trong cặp dấu ngoặc móc { } để thể hiện đó là 1 mảng.
  • Nội dung các điều kiện phải viết dạng trực tiếp và đặt trong cặp dấu nháy kép
  • Cách này chỉ áp dụng khi các điều kiện cùng nằm trong 1 vùng. Nếu khác vùng thì viết dạng:

=SUM(COUNTIFS(criteria_range1, {“criteria1”, “criteria2”, …}, criteria_range2, {“criteria3”, “criteria4”, …}) )

Áp dụng cách viết trên vào ví dụ trong bài, chúng ta có:

SUM(COUNTIFS(B2:B10, {“Nam”, “Hoa”}))

Hãy xem kết quả qua hình ảnh dưới đây:

su-dung-ham-sum-ket-hop-countifs-dem-theo-dang-OR

Hai cách làm đều ra kết quả giống nhau. Khi áp dụng vào thực tế bạn có thể lựa chọn cách viết phù hợp theo ý muốn nhé.

Tham khảo: Hàm SUM trong Excel

Cách viết điều kiện của hàm COUNTIFS

Trong Excel có 3 dạng dữ liệu thường gặp: Dạng Số (Number), dạng Văn bản (Text) và dạng Ngày tháng (Date). Với 3 dạng này chúng ta cũng có các cách viết điều kiện riêng:

Cách viết điều kiện dạng Số (Number)


Cách viết điều kiện dạng Ngày (Date)


Cách viết điều kiện dạng Văn bản (Text)

Như vậy với bài viết này, zik.vn đã chia sẻ cũng như hướng dẫn cho các bạn một cách sử dụng hàm CountIFS trong Excel một cách chi tiết nhất. Nếu có bất kỳ thắc mắc nào các bạn hãy bình luận, góp  ý ngay ở bên dưới bài viết để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.

Xem thêm:

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Zik.vn. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này. Hãy tham gia ngay nhé!

Các bạn có thể tải file mẫu sử dụng trong bài viết để thực hành về cách viết hàm COUNTIFS tại đường link bên dưới:


Đánh giá bài viết này

Tài liệu kèm theo bài viết

Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>