Đếm các giá trị là một nhiệm vụ dễ dàng bằng cách sử dụng hàm COUNTIF() của Excel, về cơ bản là sự kết hợp của các hàm COUNT() và IF(). Nó được sử dụng để đếm số lượng giá trị trong một phạm vi đáp ứng một điều kiện cụ thể. Để làm cho nó hoạt động, bạn cung cấp phạm vi và điều kiện, có thể là một giá trị bằng chữ hoặc một biểu thức.
NHÌN THẤY: Google Workspace so với Microsoft 365: Phân tích song song với danh sách kiểm tra (TechRepublic cao cấp)
Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách triển khai hiệu quả hàm COUNTIF() bằng cách sử dụng một trang tính đơn giản về giá trị hoa hồng và doanh thu. Cụ thể, chúng tôi sẽ so sánh hoa hồng của năm hiện tại với năm ngoái để xem liệu có ai vượt qua hoa hồng cao nhất của năm ngoái hay không. Điều này có nghĩa là sử dụng các toán tử so sánh, đòi hỏi một chút kiến thức chuyên môn.
Trước khi chúng ta bắt đầu, điều quan trọng cần biết là những gì bạn đang học về cách thể hiện điều kiện theo cách thân thiện với Excel không chỉ áp dụng cho COUNTIF(). Bạn có thể sử dụng những gì bạn tìm hiểu ở đây để tối ưu hóa nhiều chức năng và biểu thức.
Đối với phần minh họa này, tôi sẽ sử dụng Microsoft 365 Desktop trên hệ thống Windows 10 64 bit nhưng bạn cũng có thể sử dụng các phiên bản Excel cũ hơn. Excel dành cho web hỗ trợ đầy đủ COUNTIF(). Để làm theo các ví dụ cụ thể của chúng tôi, bạn có thể tải xuống tệp trình diễn tại đây.
Chuyển đến:
Hàm COUNTIF có thể tham chiếu một ô không?
Câu trả lời đơn giản là có, COUNTIF() có thể được sử dụng để tham chiếu một ô. Tương tự như các hàm khác, COUNTIF() có thể tham chiếu các ô hoặc phạm vi. Tuy nhiên, điều kiện là một điều hoàn toàn khác. Nếu điều kiện ở trong một ô, bạn không sao, nhưng điều đó thường không xảy ra. Trước khi giải quyết vấn đề này, hãy xem đối số của COUNTIF():
COUNTIF(phạm vi, tiêu chí)
Ở đây, “phạm vi” là giá trị hoặc các giá trị bạn đang đếm và “tiêu chí” là điều kiện xác định xem hàm có bao gồm giá trị trong số đếm hay không. Hàm này yêu cầu cả hai đối số.
Đối số phạm vi có thể là số, mảng, phạm vi được đặt tên hoặc tham chiếu đến phạm vi chứa các giá trị cần đếm. Điều quan trọng cần lưu ý là hàm này bỏ qua các giá trị trống và văn bản.
Đối số thứ hai, tiêu chí, có thể là một số, biểu thức, chuỗi văn bản hoặc tham chiếu ô chỉ định các ô cần đếm. Đây là đối số điều kiện gây nhầm lẫn — hiếm khi một ô chứa điều kiện đã được biểu thị theo cách mà Excel có thể diễn giải. Chẳng hạn, giả sử bạn chỉ muốn đếm những giá trị lớn hơn hoặc bằng 1.500. Bạn có thể nhập 1.500 vào ô nhập liệu, nhưng làm cách nào để biểu thị phần lớn hơn?
Câu trả lời dài hơn cho câu hỏi đặt ra ở đây là có và không. Chúng tôi sẽ mở rộng về điều đó trong phần tiếp theo.
Làm cách nào để tham chiếu một ô bằng COUNTIF?
Những gì bạn sẽ học trong phần này không phải là duy nhất đối với COUNTIF(). Các hàm khác cũng đánh giá các đối số có điều kiện, vì vậy bạn có thể áp dụng tốt thông tin của phần tiếp theo này ngoài các hàm COUNTIF().
Giả sử bạn muốn đếm số hoa hồng vượt qua hoa hồng hàng đầu của năm trước. Hình A hiển thị một bảng đơn giản về số liệu bán hàng và hoa hồng cho đến năm 2022:
Hình A
Mặc dù khuất tầm nhìn nhưng vẫn có một tờ khác cho năm 2021. Biểu thức trong K3
=’2021′!K3
trả về mức hoa hồng cao nhất cho năm 2021. Hiện tại, chúng tôi có mọi thứ cần thiết để đếm số lượng khoản hoa hồng lớn hơn mức hoa hồng cao nhất của năm ngoái là $2.710,79. Trong ngôn ngữ Excel, đó là >2.710,79.
Với một cái nhìn nhanh vào Hình A, dễ dàng nhận thấy ba hoa hồng cao hơn hoa hồng hàng đầu của năm ngoái. Bây giờ, hãy sử dụng hàm COUNTIF() để trả về số đếm.
Bạn có thể thử những cách sau trước:
=COUNTIF(Doanh số2022[Comm],>L3)
Bán hàng2022[Comm] là phạm vi F3:F12, sử dụng tham chiếu có cấu trúc và >L3 là tiêu chí. Hãy nhớ rằng, L3 trả lại hoa hồng cao nhất so với năm trước.
Khắc phục sự cố toán tử so sánh
Thiết lập COUNTIF() của chúng ta hoàn toàn hợp lý, nhưng thật không may, Excel sẽ từ chối chức năng này vì >L3 không phải là tham chiếu hợp lệ. Nếu bạn xóa dấu >, Excel sẽ chấp nhận hàm này và trả về 0 vì không có giá trị nào bằng với khoản hoa hồng cao nhất từ năm ngoái, $2.710,79.
Vấn đề là toán tử so sánh, >. Excel đơn giản là không biết phải làm gì với nó. Excel yêu cầu bạn sử dụng dấu phân cách, vì vậy hãy thử lại với chức năng này:
=COUNTIF(Doanh số2022[Comm]“>L3”)
Thật không may, hàm này cũng trả về 0 vì không có giá trị nào trong cột Comm bằng >L3. Excel đánh giá mọi thứ giữa hai dấu phân cách là chuỗi phù hợp.
Hãy thử một cái gì đó khác. Điều gì xảy ra nếu bạn chuyển > lên mức hoa hồng cao nhất cho năm 2021 ở L3, như sau:
=>’2021′!K3
Excel cũng không thích điều đó. Một lần nữa, bạn có một ký tự chuỗi không giới hạn, “>”. Bạn có thể làm cho nó hoạt động, nhưng nói chung không phải là một cách hay để diễn đạt một điều kiện theo cách này vì nó quá dễ quên.
Để khắc phục sự cố này, chúng ta cần phân tách > trong đối số tiêu chí như sau:
=COUNTIF(Doanh số2022[Comm]“>”&L3)
Như bạn có thể thấy trong Hình B, cuối cùng chúng tôi đã đếm đúng; ba khoản hoa hồng năm 2022 cao hơn mức hoa hồng cao nhất của năm ngoái.
Hình B
Giới thiệu toán tử nối
Bây giờ có thêm một ký tự trong đối số tiêu chí mà bạn có thể không nhận ra: &. Đây là toán tử nối của Excel; thuật ngữ nối, trong trường hợp này, có nghĩa là kết hợp trong Excel. Đối với cuộc biểu tình của chúng tôi, lập luận
“>”&L3
đánh giá để
>&L3
> 2710,79
Giờ đây, hàm COUNTIF() chỉ có thể đếm những giá trị trong cột Comm lớn hơn 2710,79. Khi điều này được thiết lập chính xác, nó sẽ cho thấy rằng ba khoản hoa hồng cho đến nay cao hơn khoản hoa hồng cao nhất của năm trước.
Các hướng dẫn và tài nguyên Excel khác
Nhìn chung, hàm COUNTIF() sử dụng khá đơn giản — chính điều kiện được thể hiện trong tiêu chí có thể gây nhầm lẫn. Như đã đề cập, học cách sử dụng hiệu quả các tiêu chí trong COUNTIF() sẽ giúp ích cho bạn và áp dụng cho bất kỳ hàm nào có đối số loại tiêu chí.
Việc tham chiếu một ô hoặc phạm vi thường dễ dàng cho đến khi bạn cần đưa vào một điều kiện được biểu thị bằng toán tử so sánh. Sau đó, bạn cần biết cách sử dụng dấu phân cách và toán tử nối, &. Hướng dẫn này là một nơi tuyệt vời để bắt đầu nếu bạn muốn tìm hiểu thêm về chủ đề này: Cách nối các giá trị trong một cột Excel với một hàng.
Để biết các tài nguyên và hướng dẫn Excel khác do TechRepublic cung cấp, bạn có thể xem thư viện nội dung Microsoft của chúng tôi tại đây.
Đọc tiếp: 8 lựa chọn thay thế tốt nhất cho Microsoft Project (Miễn phí & Trả phí) (TechRepublic)
