Trong Excel có rất nhiều hàm tính, bạn biết sử dụng những hàm nào? Bạn đã bao giờ sử dụng hàm countif chưa, hay bạn có biết sử dụng hàm countif nhiều điều kiện. Nếu chưa biết hãy cùng chúng tôi tìm hiểu qua những thông tin dưới đây.
Excel cung cấp cho người dùng một số hàm phục vụ việc đếm các loại ô khác nhau như đếm ô trống, ô không trống, hay ô chứa giá trị số và những ô chứa ký tự cụ thể nào đó. Trong bài viết học Excel cơ bản này, chúng ta sẽ chỉ tập trung vào tìm hiểu hàm COUNTIF cơ bản. Hàm Countifs là một trong rất nhiều hàm tính được tích hợp trong bảng tính excel để hỗ trợ các thao tác của người dùng một cách hiệu quả. So với hàm SUM, MIN hay MAX thì Countifs là hàm không quá phổ biến và quen thuộc với nhiều người dùng. Chính vì vậy, không phải ai cũng biết cách sử dụng hàm này. Hàm countif trong excel là công cụ làm việc gần như bắt buộc phải có đối với việc làm kế toán và kiểm toán, giúp kế toán và kiểm toán viên hoàn thành công việc một cách nhanh chóng nhất.
Vậy sau đây, chúng tôi sẽ hướng dẫn bạn học Excel cơ bản qua cách sử dụng hàm countif trong excel hàm countif nâng cao, hàm đếm countif, hàm countif nhiều điều kiện và những lưu ý quan trọng.
Mục Lục Bài Viết
I. Cấu trúc cơ bản của hàm Countif
Để biết cách sử dụng hàm countif trong excel (hàm đếm countif), hay hàm countif nâng cao, hàm countif nhiều điều kiện thì việc trước tiên chúng ta phải hiểu rõ hàm countif là gì? Trong phần này chúng tôi sẽ giúp bạn có cái nhìn chi tiết về hàm countif.
1. Hàm countif là gì?
Đầu tiên, chúng ta sẽ tìm hiểu đôi chút về hàm Countifs để có thể hiểu rõ hơn về ý nghĩa của hàm này.
Mỗi một hàm tính trong excel đều có những ý nghĩa nhất định để hỗ trợ cho người dùng. Chẳng hạn như, AVERAGE là hàm tính trung bình, SUM thì để tính tổng… Vậy hàm Countifs được dùng để làm gì? Count được dịch nghĩa là đếm. Vì thế, ý nghĩa của Countifs là dùng để đếm. Tuy nhiên việc đếm này phụ thuộc vào một vài điều kiện được đưa ra chứ không phải đếm tổng số ô đơn thuần.
Hàm Countif là hàm dùng để đếm số lần xuất hiện của một nội dung trong 1 vùng.
Công thức hàm Countif: COUNTIF(range, criteria). Trong đó: Range: Cột số chứng từ trong sổ NKC và Criteria: số chứng từ trên từng dòng dữ liệu.
Công thức tính hàm countifs
Công thức hàm countifs được tính như sau:
=COUNTIFS (Criteria_range1, Criteria1, [Criteria_range2, Criteria2]…)
Trong đó:
– Criteria_range1 là xác định phạm vi đầu tiên áp dụng điều kiện đầu tiên (criteria1), bắt buộc. Criteria1 là đặt điều kiện cho dạng số, ô tham chiếu, một chuỗi văn bản, một mảng hoặc một hàm Excel khác bắt buộc. Tiêu chí này sẽ xác định những ô tính nào sẽ được đếm và có thể biểu diễn là 10, “
– [Criteria_range2, Criteria2]… là những điều kiện bổ sung, tùy chọn. Chúng ta có thể nhập tới 127 cặp điều kiện trong công thức. Trên thực tế, bạn không phải thuộc lòng cú pháp của hàm COUNTIFS. Microsoft Excel sẽ hiển thị đối số của hàm ngay khi bạn bắt đầu gõ. Đối số đã được nhập vào hiện tại sẽ được tô đậm.
2. Những lưu ý khi sử dụng hàm countif
– Mặc dù nói hàm Countifs được sử dụng để đếm ô thỏa mãn cùng lúc nhiều tiêu chí. Tuy nhiên, trên thực tế bạn vẫn có thể áp dụng hàm này để đếm ô thỏa mãn 1 tiêu chí cũng được. Điều này giống như bạn sử dụng nhiều hàm Countif cùng một lúc. Để đếm ô thỏa mãn các tiêu chí thay cho Countifs.
– Những vùng tham số điều khiển Criteria_range1, Criteria_range2… đều phải có phạm vi, dung lượng dãy ô tương đương.
– Các vùng dữ liệu bạn nhập có thể là liền kề hoặc cách nhau đều được.
– Nếu tiêu chí điều kiện bạn nhập là ô trống. Countifs sẽ tự mặc định nó là giá trị 0.
– Bên cạnh đó, bạn có thể dùng dấu hỏi chấm (?) để thay cho bất kỳ ký tự nào.
– Ký tự (*) để thay cho một chuỗi ký tự. Và khi bạn sử dụng dấu hỏi chấm hay dấu sao thì phải nhập thêm dấu (~) ở phía trước. Còn nếu dùng số hay văn bản thông thường thì chỉ cần để trong dấu “ ” là được.
Qua những thông tin trên, chắc hẳn bạn cũng đã biết rất rõ hàm countif và những lưu ý trong khi sử dụng. Vậy ứng dụng của hàm countif trong excel như nào? Hãy tiếp tục đi đến phần tiếp theo của bài viết.
II. Ứng dụng của hàm Countif
Sau khi tìm hiểu hàm countif là gì, chúng ta hãy cùng xem hàm đếm countif có những ứng dụng gì hỗ trợ người dùng.
1. Ứng dụng hàm Countif cơ bản với chữ cái và số trùng lặp
Hãy tham khảo ví dụ sau đây:
Hàm đếm countif trong học Excel cơ bản
Tại đây, chúng ta sẽ sử dụng hàm COUNTIF để đếm những ô chứa giá trị thỏa mãn điều kiện. Cú pháp tương ứng trong ví dụ:
=COUNTIF($C$2:$C$31,$I$8) hoặc =COUNTIF($C$2:$C$31,”Nam”) – đếm số lượng ô chứa giá trị “Nam” trong vùng C2 đến C31
=COUNTIF($D$2:$D$31,I11) hoặc =COUNTIF($D$2:$D$31,18) – đếm số lượng ô chứa giá trị “18” trong vùng C2 đến C31.
Lưu ý: Criteria không phân biệt được chữ hoa và chữ thường. Bởi vậy, Nam hay NAM, nam thì cũng giống nhau thôi. Trong một số trường hợp, nó rất tiện vì bạn không phải xử lí chính xác dữ liệu, nhưng trong nhiều trường hợp, nó lại ảnh hưởng nhiều tới việc tìm kiếm. Vậy xử lý như thế nào? Có rất nhiều cách, mình sẽ gợi ý một hướng đi, đó là thay vì đếm “Nam”, “NAM”, “nam”, hãy đếm TRUE và FALSE kết hợp cùng hàm EXACT.
Không hề khó nhớ phải không? Giờ ta sẽ đến với những “biến thể” khác ở những ví dụ tiếp theo.
2. Sử dụng hàm countif kết hợp những ký tự đại diện (wildcard)
Ký tự đại diện (Wildcard) được dùng để thay thế các ký tự khác trong ô. Có 3 ký tự đại diện là dấu sao (*), dấu hỏi (?), dấu ngã (~).
Hãy cùng xem ví dụ:
Cách sử dụng hàm countif cơ bản trong học Excel cơ bản
Ở ví dụ này, ta sẽ đếm cho 2 trường hợp: những người có họ Lê; những người có tên Anh.
Đầu tiên, ta sử dụng dấu (*) thay thế cho cụm ký tự. Đối với những người họ Lê, bạn sử dụng cú pháp “Lê*” tại vùng điều kiện; hoặc $I$8 & “*” nếu tham chiếu tới ô I8.
Kết hợp hàm COUNTIF cơ bản, ta có thể đếm được số lượng ô thỏa mãn được điều kiện.
3. Sử dụng hàm countif đếm với các phép so sánh lớn, nhỏ, bằng
Hàm countif nhiều điều kiện, hàm countif nâng cao
Đối với việc đếm sử dụng điều kiện là các phép so sánh lớn hơn (>), nhỏ hơn (
Với điều kiện trực tiếp bạn đặt toàn bộ trong dấu nháy kép như sau: “>=a” với >= là phép so sánh, a là giá trị số được so sánh.
Với điều kiện tham chiếu ta đặt phép so sánh trong dấu nháy kép, kết hợp cùng ký tự &: “>=”& I8 với >= là phép so sánh, I8 là ô được tham chiếu tới.
Qua các ví dụ trên, chắc hẳn các bạn đã bắt đầu hiểu rõ hơn về hàm COUNTIF rồi phải không? Giờ hãy tiếp tục tìm hiểu ứng dụng của hàm này một chút với ví dụ cuối cùng nhé.
4. Ứng dụng hàm Countif lọc giá trị trùng lặp
Cách sử dụng hàm countif trong học Excel cơ bản
Ví dụ trên là một ứng dụng đơn giản của hàm COUNTIF trong nhiều hoạt động như lọc email khách hàng, lọc tên… bị trùng lặp. Chắc hẳn tới đây, các bạn đều đã nhận ra cách làm rồi phải không? Bạn hãy tính số lần xuất hiện của giá trị muốn tìm trong cột tổng và chuyển bài toán về lọc các giá trị trả về theo số tự nhiên.
Đừng quên trên đây chỉ là những ví dụ cơ bản nhất của hàm đếm COUNTIF. Còn vô số ứng dụng của hàm countif đang chờ các bạn khám phá, bởi vậy nên bạn hãy luyện tập thường xuyên nhé! Tiếp theo chúng ta cùng đi đến những lỗi mà người dùng thường hay gặp phải khi sử dụng hàm đếm countif, và chúng tôi sẽ gợi ý cho bạn một số biện pháp khắc phục các lỗi đó.
III. Các lỗi thường gặp khi sử dụng hàm countif
Khi sử dụng hàm countif bạn hay gặp phải thông báo lỗi #VALUE! mà không biết cách xử lý như nào. Vậy sau đây chúng tôi đã tổng hợp lại những lỗi cơ bản và sẽ đưa ra cho bạn các giải pháp khắc phục để sửa lỗi.
1. Lỗi công thức tham chiếu các ô trong bảng tính đã đóng
Hàm COUNTIF/COUNTIFS tham chiếu đến ô trong vùng dữ liệu của bảng tính bị đóng và kết quả trả về là thông báo lỗi #VALUE!.
Lưu ý: Lỗi này cũng hay xảy ra tương tự với một số hàm Excel khác như hàm SUMIF, SUMIFS, COUNTBLANK…
Giải pháp sửa lỗi: Để khắc phục lỗi này, bạn chỉ cần mở bảng tính chứa vùng dữ liệu được được tham chiếu trong công thức, và nhấn phím F9 để làm mới công thức.
Ngoài ra, bạn cũng có thể áp dụng cách trên để sửa lỗi #VALUE! khi sử dụng kết hợp với hàm SUM và IF trong công thức mảng.
2. Lỗi chuỗi dài hơn 255 ký tự
Giải pháp: Rút ngắn chuỗi nếu bạn có thể. Nếu không thể rút gọn chuỗi hãy sử dụng hàm CONCATENATE hoặc toán tử (&) để chia giá trị thành nhiều chuỗi. Ví dụ: =COUNTIF(B2:B12,”chuỗi dài”&”chuỗi dài khác”)
Chúng ta cùng đi đến lỗi cơ bản thứ 3 mà bạn hay mắc phải.
3. Lỗi hàm Countif không tính giá trị trên vùng dữ liệu không liên tục
Giải pháp: Hàm COUNTIF không hoạt động trên các vùng dữ liệu không liên tục, và cú pháp của hàm COUNTIF cho phép xác định một ô riêng làm tham số đầu tiên. Thay vào đó, có thể sử dụng kết hợp nhiều hàm COUNTIF:
Công thức sai: =COUNTIF(A2,B3,C4,”>0″)
Công thức đúng: =COUNTIF(A2,”>0″) + COUNTIF(B3,”>0″) + COUNTIF(C4,”>0″)
Bên cạnh đó, bạn hãy thử sử dụng hàm INDIRECT để tạo các dữ liệu mảng. Ví dụ như 2 công thức dưới đây đều trả về một kết quả giống nhau:
=SUM(COUNTIF(INDIRECT({“B2:B8″,”D2:C8″}),”=0”))
=COUNTIF($B2:$B8,0) + COUNTIF($C2:$C8,0)
Hàm countif nâng cao
4. Lỗi sử dụng dấu và dấu ngoặc kép trong công thức Countif
Giải pháp: Cú pháp hàm COUNTIF cực kỳ khó hiểu. Dấu (&) và dấu ngoặc kép (“”) được sử dụng trong công thức để tạo chuỗi văn bản cho đối số. Bạn có thể tuân thủ theo các quy tắc dưới đây:
– Nếu bạn sử dụng một số hoặc tham chiếu ô trong điều kiện so sánh chính xác, không cần dấu (%) và dấu ngoặc kép.
Ví dụ: =COUNTIF(A1:A10,10) hoặc là =COUNTIF(A1:A10,C1).
– Nếu phần điều kiện của bạn bao gồm cả văn bản, ký tự đại diện hoặc toán tử kèm theo số, bạn phải đặt trong dấu ngoặc kép.
Ví dụ như công thức: = COUNTIF (A2: A10, “chanh”) hoặc = COUNTIF (A2: A10, “*”) hoặc = COUNTIF (A2: A10, “> 5”)
– Nếu bạn sử dụng biểu thức có tham chiếu ô hoặc hàm Excel khác, phải sử dụng dấu ngoặc kép (“”) để bắt đầu chuỗi văn bản và dấu và (&) để nối và kết thúc chuỗi.
Ví dụ như: =COUNTIF (A2: A10, “>” & D2) hoặc =COUNTIF (A2: A10, “
– Nếu bạn nghi ngờ và băn khoăn không biết có phải thêm dấu ngoặc kép hoặc dấu &, thử thêm các dấu này. Trong hầu hết mọi trường hợp, dấu & đề hoạt động.
Ví dụ các công thức: = COUNTIF (C2: C8, “
5. Lỗi khi sử dụng hàm Countif để đếm các ô định dạng màu
Giải pháp: Cú pháp hàm COUNTIF không cho phép sử dụng các điều kiện định dạng làm điều kiện. Cách duy nhất để đếm và tính tổng các ô định dạng màu là sử dụng macro, hay chính xác hơn là sử dụng hàm Excel User-Defined. Ngoài ra, bạn cũng có thể tìm thấy mã hoạt động trong các ô định dạng màu theo cách thủ công.
6. Lỗi hàm Excel Countif không hoạt động
Giải đáp: Giả sử như viết công thức đúng nhưng hàm COUNTIF không hoạt động, hoặc trả về kết quả sai, thử kiểm tra lại các điều kiện như phạm vi, điều kiện, tham chiếu ô, sử dụng dấu & và dấu ngoặc kép (“”).
Cẩn thận khi sử dụng dấu cách trong công thức hàm COUNTIF. Khi tạo công thức tính nên chú ý dấu cách, nếu công thức có dấu cách có thể gây ra lỗi.
Ví dụ như công thức: =COUNTIF(B2:B13,” chanh”).
Nhìn qua thì thấy công thức không có gì sai, ngoại trừ có dấu cách sau dấu ngoặc kép. Microsoft Excel sẽ nuốt công thức này mà không hiển thị cảnh báo thông báo lỗi hoặc bất kỳ dấu hiệu nào khác. Giả sử trường hợp này bạn muốn đếm các ô có chứa từ “chanh”.
Nếu bạn sử dụng hàm COUNTIF nhiều điều kiện, hãy chia công thức thành nhiều phần khác nhau và kiểm tra từng hàm một.
Tương tự, bạn có thể xem thêm cách thức sử dụng Hàm COUNTIFS, hàm thống kê có điều kiện .Trên đây chỉ là hai trong số rất nhiều ví dụ cụ thể mà đôi lúc bạn cần phải nhờ hàm COUNTIF để tính toán số lượng các vấn đề trong nhiều công việc cụ thể.
Hàm đếm là một trong rất nhiều hàm của Excel, hàm tìm kiếm cũng tương tự vậy, và nếu bạn đang tìm kiếm cho mình một hàm tìm kiếm thì Vlookup là sự lựa chọn tốt nhất dành cho bạn. Hàm Vlookup sẽ giúp bạn tìm kiếm các kết quả tốt nhất và với thời gian nhanh nhất.
Qua đây chúng tôi đã tổng hợp những lỗi mà người dùng khi sử dụng hàm countif hay gặp phải, và dựa vào đó chúng tôi đã gợi ý cho bạn một vài giải pháp để khắc phục tình trạng này. Các bạn đã biết mình hay gặp phải lỗi nào chưa, và hãy thử làm theo những hướng dẫn trên để khắc phục nhé!
IV. Kết luận
Trên đây là toàn bộ những thông tin chi tiết liên quan đến hàm Countifs trong học Excel cơ bản, cách sử dụng hàm countif trong excel, hàm countif nâng cao, hàm đếm countif, hàm countif nhiều điều kiện mà chúng tôi muốn giới thiệu cho bạn đọc. Cùng với những thông tin và những ví dụ cụ thể ở đây sẽ giúp bạn có thêm những hiểu biết rõ hơn về Countifs và vận dụng chúng vào công việc một cách hiệu quả. Chúc bạn thành công!
