Thông thường chúng ta hay sử dụng hàm COUNTIF để đếm số lần xuất hiện của 1 nội dung trong 1 vùng. Học Excel Online xin được hướng dẫn cách làm như sau:
Hàm chúng ta sử dụng là hàm COUNTIF:
Xem thêm: Cách sử dụng hàm count và counta để đếm số ô trong Excel
- Range: Vùng đếm ở đây cố định điểm đầu A2, bắt đầu vùng đếm sẽ là từ ô A2
- Criteria: Điều kiện đếm là nội dung ô A2
Khi filldown [sao chép công thức xuống phía dưới] chúng ta sẽ lấy được kết quả là số lần xuất hiện của các nội dung tên tương ứng theo cột A.
Đây là cách làm thông thường với đối tượng RANGE
Vậy với đối tượng TABLE thì sao?
Đây là vấn đề rất hay xảy ra khi chúng ta sử dụng hàm COUNTIF trong đối tượng TABLE:
- Ban đầu công thức hoạt động đúng từ dòng 2 tới dòng 4 [Các công thức tại H2, H3, H4]
- Khi thêm tiếp dữ liệu vào table ở các dòng 5, 6, 7 thì hàm COUNTIF tự động tạo ra ở các ô H5, H6, H7 nhưng không chính xác.
- Nguyên nhân là bởi khi thêm nội dung vào Table thì tọa độ của điểm cuối trong Table sẽ tự động thay đổi giới hạn. Chính vì thế công thức mới tự động tạo ra đã không đúng nữa.
Cách khắc phục việc này như thế nào? Hay cụ thể hơn là sử dụng hàm COUNTIF như thế nào để đảm bảo kết quả vẫn đúng khi thêm giá trị mới trong Table, chúng ta cùng tìm hiểu cách làm như sau:
Công thức =COUNTIF[[@[Họ tên]]:INDEX[[Họ tên],1],[@[Họ tên]]]
[@[Họ tên]] được hiểu là 1 giá trị cụ thể tại dòng đó. Nội dung này được lấy khi click chuột vào cột Họ tên, dòng ngang hàng với ô đặt công thức.
[Họ tên] được hiểu là toàn bộ cột Họ tên trong table. Nội dung này được lấy bằng cách chọn toàn bộ vùng nội dung cột Họ tên.
Hàm Index[[Họ tên],1] là lấy giá trị đầu tiên trong table [điểm bắt đầu]
vùng [@[Họ tên]]:INDEX[[Họ tên],1] chính là tính từ điểm bắt đầu tới dòng hiện tại trong vùng đếm => Tránh việc tự động xác định tới dòng cuối của table
=> Toàn bộ công thức được hiểu là: Đếm giá trị ở dòng hiện tại trong table nằm trong cột Họ tên, tính từ dòng bắt đầu tới dòng hiện tại.
Công thức này về bản chất là tương tự với công thức COUNTIF của range, nhưng cách diễn đạt trong Table và trong Range lại có sự khác nhau, để ra được kết quả giống nhau. Đây là một sự chú ý quan trọng khi chúng ta làm việc với Table trong excel.
Chúc các bạn học tốt cùng Học Excel Online!
Ví dụ: Nếu bạn có một danh sách học sinh và muốn nhanh chóng có được tổng số tất cả các học sinh đạt trên 75 điểm thì bạn có thể sử dụng hàm COUNTIF trong Google Sheets.
Trước khi Gitiho chỉ cho bạn một số ví dụ thú vị về việc sử dụng hàm COUNTIF trong thực tế, hãy xem qua cú pháp và một số điều quan trọng về hàm này.
Tổng hợp hàm COUNTIF trong Google Sheets
=COUNTIF[range, criterion]
Trong đó:
- Range - dải ô mà bạn muốn đếm.
- Criterion - điều kiện cần được đáp ứng để một ô được đếm.
Tiêu chí có thể là:
- Một số.
- Một chuỗi văn bản, chẳng hạn như “Apple” hoặc “Banana”.
- Một tham chiếu ô [chẳng hạn như A1 hoặc B1].
- Một biểu thức chẳng hạn như “=100” hoặc “>100” hoặc “, 75” làm tiêu chí. Hàm sẽ kiểm tra từng số trong 'phạm vi' cho tiêu chí này. Lưu ý rằng khi bạn sử dụng toán tử với một số, bạn cần đặt nó trong dấu ngoặc kép.
- Nếu phạm vi chứa chuỗi văn bản, tiêu chí có thể là chuỗi văn bản trong dấu ngoặc kép [ví dụ: “Apple”] hoặc có thể là tham chiếu ô có chứa chuỗi văn bản.
- Bạn cũng có thể sử dụng các ký tự đại diện trong trường hợp tiêu chí văn bản. Ví dụ: để đếm các ô chứa Apple hoặc Apples, bạn có thể sử dụng Apple* làm tiêu chí.
Bây giờ chúng ta hãy xem một số ví dụ về việc sử dụng hàm COUNTIF trong Google Sheets.
Xem thêm: Hướng dẫn cách dùng hàm COUNTIF trong công việc kế toán trên Excel
Hàm COUNTIF trong Google Sheets những ví dụ cụ thể
Dưới đây là sáu ví dụ về việc sử dụng hàm COUNTIF trong Google Sheets.
Ví dụ 1 - Đếm số học sinh có điểm số lớn hơn 75 điểm
Giả sử bạn có một danh sách các học sinh có điểm đã ghi trong một bài kiểm tra.
Đây là công thức sẽ cung cấp cho bạn số học sinh đạt trên 75 điểm.
=COUNTIF[$B$2:$B$14,“>75”]
Ghi chú:
- Điều kiện cần nằm trong dấu ngoặc kép.
- Bạn cũng có thể có giá trị tiêu chí trong một ô và sử dụng tham chiếu ô đó. Ví dụ: nếu tiêu chí nằm trong ô C2, thì bạn có thể sử dụng công thức: =COUNTIF[$B$2:$B$14,“>”&C2]. Khi bạn sử dụng tham chiếu ô, bạn vẫn cần có toán tử trong dấu ngoặc kép và sử dụng & để nối nó với tham chiếu ô.
Ví dụ 2 - Đếm tất cả các bản ghi giao dịch của Hoa Kỳ
Giả sử bạn có bảng dữ liệu giao dịch như hình dưới đây:
Đây là công thức sẽ cung cấp cho chúng ta số lượng giao dịch cho Hoa Kỳ:
=COUNTIF[$B$2:$B$13,“US”]
Ghi chú:
- Vì tiêu chí là văn bản nên không cần sử dụng dấu bằng. Như trong ví dụ này, điều kiện là quốc gia phải là Hoa Kỳ, vì vậy bạn có thể chỉ cần sử dụng "US".
- Bạn cũng có thể có tiêu chí trong một ô. Trong trường hợp đó, bạn có thể chỉ cần sử dụng tham chiếu ô. Ví dụ: nếu tiêu chí nằm trong ô D2, bạn có thể sử dụng công thức: =COUNTIF[$B$2:$B$13,D2]
Xem thêm: Cách sử dụng hàm COUNTIF thiết lập số thứ tự thành phần của bảng phân loại
Ví dụ 3 - Đếm tất cả các bản ghi ngoại trừ Hoa Kỳ
Giả sử bạn có cùng một bảng dữ liệu giao dịch [được sử dụng trong ví dụ 3] và muốn đếm tất cả các bản ghi giao dịch ngoại trừ các bản ghi của Hoa Kỳ.
Đây là công thức sẽ thực hiện điều này:
=COUNTIF[$B$2:$B$13,“US”]
Công thức trên sẽ tính tất cả các giao dịch ngoại trừ những giao dịch có quốc gia là 'Hoa Kỳ'.
Ví dụ 4 - Đếm tất cả các ô trống/trống trong một phạm vi
Nếu bạn có một tập dữ liệu khổng lồ và muốn đếm số bản ghi bị thiếu hoặc các ô trống hoặc trống, bạn có thể sử dụng hàm COUNTIF để thực hiện việc này.
Giả sử bạn có một tập dữ liệu như hình dưới đây:
Đây là công thức sẽ cung cấp cho bạn tổng số ô trống hoặc ô trống trong đó:
=COUNTIF[$C$2:$C$13,“”]
Trong ví dụ này, tiêu chí được sử dụng là “” [dấu ngoặc kép]. Khi chỉ sử dụng dấu ngoặc kép làm tiêu chí, nó chỉ đếm những ô trống/trống. Lưu ý rằng nếu có một ô nhìn giống như trống nhưng có ký tự khoảng trắng trong đó, thì ô đó sẽ không được tính theo công thức này.
Xem thêm: Cách đếm ký tự nằm trong một chuỗi bằng hàm COUNTIF trong Excel
Ví dụ 5 - Đếm tất cả các ô không trống trong một phạm vi
Lấy cùng dữ liệu từ ví dụ 4, trong ví dụ này chúng ta hãy xem cách đếm tất cả các ô không trống trong tập dữ liệu.
Đây là công thức sẽ đếm tất cả các ô không trống:
=COUNTIF[$C$2:$C$13,“”]
Trong ví dụ này, tiêu chí được sử dụng là “” [không bằng dấu trong dấu ngoặc kép]. Tiêu chí này có nghĩa là các ô không bằng một chuỗi rỗng. Lưu ý rằng nếu có một ô nhìn giống như trống nhưng có ký tự khoảng trắng trong đó, thì ô đó sẽ được tính theo công thức này.
Ví dụ 6 - Đếm tất cả các trường hợp mà từ khóa xuất hiện
Đôi khi, bạn có thể phải xử lý dữ liệu có chứa các biến thể của cùng một tên hoặc mục. Ví dụ, trong dữ liệu dưới đây, có các biến thể của Apple như Apple, Apples, Fruit - Apple.
Đây là công thức bạn có thể sử dụng để đếm tổng số ô với từ khóa Apple:
=COUNTIF[$A$2:$A$13,“*Apple*”]
Trong ví dụ trên, tiêu chí được sử dụng là *Apple*. Dấu hoa thị [*] là một ký tự đại diện cho bất kỳ số ký tự nào. Vì vậy, *Apple* sẽ đếm bất kỳ ô nào có văn bản Apple trong đó và nó có thể được đặt cạnh bởi bất kỳ số ký tự nào ở hai bên.
Trên đây Gitiho vừa hướng dẫn các bạn cách sử dụng hàm COUNTIF thông qua 6 ví dụ cơ bản. Nếu thấy bài viết hay và hữu ích hãy chia sẻ với bạn bè của mình nhé, trong quá trình sử dụng nếu các bạn có bất kỳ câu hỏi nào về việc sử dụng hàm COUNTIF hãy để lại comment cho chúng tôi ở dưới để chúng tôi giải đáp nhé.
Cuối cùng, đừng quên tham khảo khóa học Google Sheets của Gitiho bạn nhé! Hẹn gặp lại các bạn trong những bài viết tiếp theo.
Cùng tham gia cộng đồng hỏi đáp về chủ đề Tin học văn phòng