Ứng Dụng Sử Dụng Hàm COUNTIF Nâng Cao Trong Excel

Ứng Dụng Sử Dụng Hàm COUNTIF Nâng Cao Trong Excel

Hoctin.vn đã có bài viết hướng dẫn sử dụng hàm COUNTIF đơn giản và chi tiết từ cơ bản đến nâng cao cho điều kiện là dữ liệu dạng số, văn bản, ngày tháng.

 

Ở bài viết này, hoctin.vn sẽ tiếp tục giới thiệu với các bạn các ứng dụng cách sử dụng hàm COUNTIF trong Excel với công thức mảng, bảng chấm công, kiểm tra khách hàng thân thiết, đếm các giá trị duy nhất, đếm trong nhiều dải ô không liền kề.

 

Cú pháp hàm COUNTIF:

=COUNTIF(range, criteria)

Trong đó:

  • Range: Phạm vi chứa các ô cần đếm

  • Criteria: Tiêu chí, điều kiện kiểm soát ô nào sẽ được đếm.

Tiêu chí có thể là một số, ngày, văn bản hoặc một phép so sánh.

Hàm COUNTIF chỉ có một điều kiện.

Mục Lục Bài Viết

1, Dùng Hàm COUNTIF Đếm Số Ngày Làm Việc Trên Bảng Chấm Công

Ứng dụng hàm COUNTIF vào bảng chấm công rất đơn giản:

  • Nguyên tắc: đặt ra các ký hiệu đại diện để theo dõi tình trạng làm việc của người lao động, ví dụ: Đ- làm đủ ngày, N- làm nửa ngày, …
  • Sử dụng công thức đếm có điều kiện trong Excel để đếm số ngày làm đủ 8h/ ngày, số ngày làm nửa ngày 4h/ ngày rồi cộng lại ra số ngày làm việc bằng Đ+ N/2
  • Có thể theo dõi những ngày nghỉ có phép và không phép để kiểm tra kỷ luật và tuân thủ nội quy của người lao động: đếm NP- nghỉ có phép, KP- nghỉ không phép.

Ví dụ một bảng chấm công tháng như sau:

Dùng Hàm COUNTIF Đếm Số Ngày Làm Việc Trên Bảng Chấm Công

Từ kết quả của hàm COUNTIF ở hình minh họa trên, có thể thấy chị Nguyễn Thị Minh đi làm đủ 18 ngày và 3 nửa ngày, các ngày còn lại không nghỉ. Như vậy người lao động này đã làm 18+ 3/2 = 19.5 ngày trong tháng.

 

Tùy vào quy định ngày công làm việc của doanh nghiệp mà bạn có thể điều chỉnh ký hiệu và cách tính ngày làm việc trong bảng chấm công.

2, Sử Dụng Hàm COUNTIF Đếm Có Điều Kiện Để Đếm Số Lần Mua Hàng Của Khách Hàng

Hầu hết thống kê mua/ bán hàng hóa hiện tại của các công ty đều được thực hiện trên phần mềm tự động, tuy nhiên việc theo dõi bằng file bảng tính Excel bên ngoài vẫn là một trong những việc cần thiết của kế toán/ nhân viên bán hàng để backup dữ liệu.

 

Có thể sử dụng hàm COUNTIF để đếm số lần mua hàng của khách hàng trong ngày, trong tháng, v.v…

 

Ví dụ có thống kê mua hàng một ngày của công ty. Cần đếm số lần mua hàng của từng khách hàng:

 

  • Range: vùng dữ liệu cần đếm là Cột “Tên khách hàng”

  • Criteria: điều kiện đếm, là tên của từng khách hàng.

Sử Dụng Hàm COUNTIF Đếm Có Điều Kiện Để Đếm Số Lần Mua Hàng Của Khách Hàng

Tại ô F4, công thức đếm số lần mua hàng của khách hàng có tên tại E4 là:

=COUNTIF($A$4:$A$13,E4).

 

Để copy công thức cho các ô từ F5 trở đi, chỉ cần cố định ô trong Excel $A$4:$A$13 là vùng chứa tên khách hàng trong thống kê mua hàng.

3, Đếm Các Giá Trị Duy Nhất Trong Một Phạm Vi Với Hàm COUNTIF

Ứng dụng hàm đếm dữ liệu không trùng hay đếm các giá trị duy nhất trong một phạm vi ô Excel:

Cách 1: Sử Dụng Nhiều Hàm COUNTIF Và Cột Phụ

Ví dụ một danh sách nhiều loại quả trong đó có một số quả trùng lặp xuất hiện nhiều lần trong danh sách (Ví dụ Cam, Xoài xuất hiện nhiều hơn 2 lần) như dưới đây.

 

Để đếm số loại quả riêng biệt (kiểm tra danh sách có bao nhiêu loại quả), tại cột B, hoctin.vn đếm số lần xuất hiện của loại quả đó trong khu vực từ đầu danh sách đến dòng đang đếm.

 

Kết quả số loại quả ở ô B20 là kết quả của hàm đếm số lượng các ô ở cột B có giá trị bằng 1, chi tiết như sau:

Đếm Các Giá Trị Duy Nhất Trong Một Phạm Vi Với Hàm COUNTIF Bằng Cách Sử Dụng Nhiều Hàm COUNTIF Và Cột Phụ
  • Tại cột B: xét riêng Cam: công thức ở cột B nhằm đếm Cam theo thứ tự xuất hiện
  • Ô B5 trả về 1 là số Cam từ đầu danh sách đến A5 tương ứng
  • Ô B9 trả về 2 là số Cam từ đầu danh sách đến A9 tương ứng...
  • Tương tự đến Ô B17 trả về 4 là số Cam từ đầu danh sách đến A17

Cột B có tác dụng loại các giá trị quả xuất hiện từ lần thứ 2 trở đi (có giá trị tại cột B >2).

 

Số loại quả trong danh sách là kết quả của lệnh đếm số giá trị bằng 1 ở cột B (đếm lần xuất hiện đầu tiên của các loại quả).

Cách 2: Đếm Loại Bỏ Các Giá Trị Giống Nhau Bằng Cách Kết Hợp Hàm COUNTIF và SUMPRODUCT

Ưu điểm của công thức này là không phải thêm cột phụ trong tính toán.

 

Hoctin.vn sử dụng công thức kết hợp hàm đếm có điều kiện cho cả trường hợp đếm ô trống là một giá trị riêng biệt và không đếm ô trống:

Đếm Các Giá Trị Duy Nhất Trong Một Phạm Vi, Đếm Loại Bỏ Các Giá Trị Giống Nhau Bằng Cách Kết Hợp Hàm COUNTIF và SUMPRODUCT

4, Sử Dụng Công Thức Mảng Để Dùng Hàm COUNTIF Với Dải Ô Không Liền Kề

Với các dải ô không liền kề mà không thể nhập chung làm một dải ô lớn, sử dụng nhiều lần hàm COUNTIF là cách dễ nhất để đếm nhiều vùng dữ liệu không liên tục:

Với các dải ô không liền kề mà không thể nhập chung làm một dải ô lớn, sử dụng nhiều lần hàm COUNTIF là cách dễ nhất để đếm nhiều vùng dữ liệu không liên tục

Một cách khác khá hay nhưng phức tạp hơn là sử dụng kết hợp hàm SUM, INDIRECT VÀ COUNTIF.

 

Hàm INDIRECT tham chiếu tới 1 vùng thông qua việc gọi tên bằng một chuỗi văn bản.

 

Nếu không sử dụng hàm SUM mà chỉ sử dụng COUNTIF kết hợp hàm INDIRECT, kết quả của ví dụ sau sẽ được trả về 3, là số ô có giá trị lớn hơn 10 trong mảng A5:A13 mà không cộng kết quả ở 2 mảng sau.

Sử Dụng Công Thức Mảng Để Dùng Hàm COUNTIF Với Dải Ô Không Liền Kề

Trên đây là các ví dụ và ứng dụng hay của hàm COUNTIF nâng cao trong Excel. Hi vọng bài viết của hoctin.vn đã mang lại cho các bạn các thông tin hữu ích.

 

Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm Excel:

Hàm COUNTIFS

Hàm SUMIF

Hàm IF

4 thoughts on “Ứng Dụng Sử Dụng Hàm COUNTIF Nâng Cao Trong Excel”

Leave a Comment