Thủ thuật

Hướng dẫn cách kết hợp hàm COUNTIF và hàm AND/OR trong Google Sheet nhanh chóng

Trương Nguyễn Anh Thư

10-05-2023, 9:42 pm

Có phải bạn đang muốn học thêm kiến thức về các hàm tính toán trong Google Sheet trên máy tính? Mỗi ngày các bạn phải thực hiện làm việc với một khối lượng dữ liệu lớn, và để có thể kiểm tra dữ liệu đó có chính xác hay không chúng ta cần phải kết hợp các hàm lại với nhau. Hãy cùng với mình tìm hiểu cách kết hợp hàm COUNTIF và hàm AND/OR trong Google Sheet ngay dưới đây nhé!

Hướng dẫn cách sử dụng hàm COUNTIF trong Google Sheet chi tiết

Hàm COUNTIF là gì?

Hàm COUNTIF là một hàm thông dụng dùng để đếm có điều kiện trong Google Sheet. Và có thể dùng hàm COUNTIF để đếm số lần lặp lại của một giá trị dựa vào điều kiện mà bạn đã cho trước mà bạn không cần phải đếm thủ công nữa.

Cách sử dụng hàm COUNTIF

Công thức là: =COUNTIF(range;criteria)

Trong đó:

+ Range: Là vùng dữ liệu mà các bạn muốn đếm bắt buộc phải có. Và vùng dữ liệu này có thể là kiểu văn bản, là kiểu số hay là kiểu ngày tháng.

+ Criteria: Là điều kiện để đếm các dữ liệu trong vùng dữ liệu mà bạn đã chọn. Và có thể là kiểu văn bản, kiểu số, biểu thức hay tham chiếu ô.

Ví dụ minh họa:

Cho bảng dữ liệu gồm các trường sau: SẢN PHẨM, NHÀ CUNG CẤP, SỐ LƯỢNG SP. Bảng dữ liệu này sẽ được sử dụng xuyên suốt cho các ví dụ dưới đây nhé.

Bảng dữ liệu thông tin sản phẩm như hình trên

Hãy đếm số sản phẩm có nhà cung cấp là Bánh kẹo. Kết quả cho ra 4 sản phẩm thuộc nhà cung cấp Bánh kẹo.

Công thức là: =COUNTIF(B2:B15;"Bánh kẹo")

Ví dụ minh họa hàm COUNTIF như hình trên

Hướng dẫn cách sử dụng hàm AND/OR trong Google Sheet chi tiết

Cách sử dụng hàm AND

- Hàm AND là gì?

Hàm AND là hàm dùng để kiểm tra các biểu thức logic 1, biểu thức logic 2,... có cùng đúng hay không. Nếu đúng thì sẽ trả về TRUE. Và nếu 1 trong các biểu thức logic đó sai hoặc tất cả đều sai thì sẽ trả về FALSE.

- Cách sử dụng hàm AND

Công thức là:= AND(biểu_thức_logic1; [biểu_thức_logic2; …])

Trong đó:

+ biểu_thức_logic1: là biểu thức hay tham chiếu đến một ô mà có chứa biểu thức đại diện cho một giá trị logic nào đó.

+ [biểu_thức_logic2;...] (sẽ không bắt buộc): là biểu thức hay tham chiếu bổ sung đến một ô mà có chứa biểu thức đại diện cho một giá trị logic nào đó.

Ví dụ minh họa:

Hãy kiểm tra xem B2 có phải là nhà cung cấp bánh kẹo và C2 có phải có số lượng là 49 không. Kết quả hai biểu thức đều đúng nên sẽ trả về là TRUE.

Công thức là: =AND(B2="Bánh kẹo";C2=49)

Ví dụ minh họa hàm COUNTIF như hình trên

Cách sử dụng hàm OR

- Hàm OR là gì?

Hàm OR là hàm dùng để kiểm tra các biểu thức logic 1, biểu thức logic 2,... có đúng hay không. Nếu 1 hoặc nhiều biểu thức logic đúng thì sẽ trả về TRUE. Và nếu tất cả đều sai thì sẽ trả về FALSE.

- Cách sử dụng hàm OR

Công thức là: =OR(biểu_thức_logic1; [biểu_thức_logic2; …])

Trong đó:

+ biểu_thức_logic1: là biểu thức hay tham chiếu đến một ô mà có chứa biểu thức đại diện cho một giá trị logic nào đó.

Ví dụ minh họa:

Hãy kiểm tra xem B7 có phải là nhà cung cấp Nước ngọt và C7 có phải có số lượng là 50 không. Kết quả hai biểu thức đều sai nên sẽ trả về là FALSE.

Công thức là: =OR(B7="Nước ngọt";C7="50")

Hướng dẫn cách kết hợp hàm COUNTIF và hàm AND/OR trong Google Sheet chi tiết

Hướng dẫn cách kết hợp hàm COUNTIF và hàm AND

Công thức là: =AND(COUNTIF(range;criteria);COUNTIF(range;criteria);...)

Trong đó:

+ COUNTIF(range;criteria): là biểu_thức_logic

Ví dụ minh họa:

Hãy kiểm tra xem số sản phẩm có nhà cung cấp là Bánh kẹo có lớn hơn 3 và số lượng sản phẩm trên 40 có lớn hơn 4.

- Công thức là: =AND(COUNTIF(B2:B15;"Bánh kẹo")>3;COUNTIF(C2:C15;">40")>4)

- Giải thích:

+ COUNTIF(B2:B15;"Bánh kẹo")>3: sẽ đếm số sản phẩm mà có nhà cung cấp là Bánh kẹo có lớn hơn 3.

+ COUNTIF(C2:C15;">40")>4: sẽ đếm số lượng sản phẩm trên 40 có lớn hơn 4.

Kết hợp hàm COUNTIF và hàm AND như hình trên

Cách kết hợp hàm COUNTIF và hàm OR như sau

Công thức là: =OR(COUNTIF(range;criteria);COUNTIF(range;criteria);...)

Trong đó:

+ COUNTIF(range;criteria): là biểu thức logic.

Ví dụ minh họa:

Hãy kiểm tra xem số sản phẩm có nhà cung cấp là Gia vị có lớn hơn 3 hoặc là số lượng sản phẩm trên 33 có lớn hơn 2.

- Công thức là: =OR(COUNTIF(B2:B15;"Gia vị")>3;COUNTIF(C2:C15;33)>2)

- Giải thích:

+ COUNTIF(B2:B15;"Gia vị")>3: sẽ đếm số sản phẩm mà có nhà cung cấp là Gia vị có lớn hơn 3.

+ COUNTIF(C2:C15;33)>2: sẽ đếm số lượng sản phẩm là 33 có lớn hơn 2.

Kết hợp hàm COUNTIF và hàm OR như hình trên

Các lỗi thường gặp khi bạn kết hợp hàm COUNTIF và hàm AND/OR

Lỗi #N/A

Lý do trả kết quả lỗi: là do bạn đã khai báo thiếu đối số dự kiến của hàm COUNTIF.

Cách khắc phục: bạn cần kiểm tra lại hàm COUNTIF đã đủ 2 đối số hay chưa.

Lỗi #REF

Lý do trả kết quả lỗi: là do ô trả về kết quả đã đè lên dải ô tham chiếu dẫn đến lỗi quay vòng.

Cách khắc phục: bạn cần kiểm tra lại dải ô tham chiếu đã đúng hay chưa hoặc bạn có thể di chuyển ô trả về kết quả ra khỏi dải ô đã tham chiếu.

Lỗi #ERROR

Lý do trả kết quả lỗi: là do bạn đã nhập sai cú pháp hàm.

Cách khắc phục: Bạn hãy kiểm tra các đối số và dấu ngăn cách các đối số đã nhập đúng quy cách hay không. Như ví dụ trong hình từ Gia vị thì phải được đặt trong dấu nháy kép.

Lỗi #VALUE

Lý do trả kết quả lỗi: là do bạn đã nhập sai kiểu dữ liệu của các đối số trong hàm.

Cách khắc phục: bạn cần kiểm tra các đối số đã nhập đúng kiểu dữ liệu hay chưa. Đối số trong hàm OR thì phải là kiểu boolean.

Một số lưu ý cần thiết khi kết hợp hàm COUNTIF và hàm AND/OR

- Sẽ trả về giá trị sai đối với chuỗi dài.

+ Nguyên nhân: Khi các bạn dùng hàm COUNTIF để khớp các chuỗi dài hơn 255 ký tự thì chắc chắn hàm sẽ trả về kết quả sai.

+ Cách khắc phục là: Để khớp các chuỗi dài hơn 255 ký tự các bạn có thể sử dụng hàm CONCATENATE hoặc là dùng ký hiệu &. Ví dụ như: =AND(COUNTIF(D1:D10,"chuỗi dài"&"chuỗi dài khác")>1;COUNTIF(D1:D10,"chuỗi")=4).

- Mỗi công thức của hàm COUNTIF bạn chỉ có thể thực hiện đếm có điều kiện theo 1 tiêu chí duy nhất. Và bạn không thể sử dụng hàm COUNTIF để đếm cùng một lúc có bao nhiêu nhà cung cấp là “Bánh kẹo” và bao nhiêu nhà cung cấp là “Gia vị”.

- Khi nhập công thức hàm COUNTIF, hàm AND và hàm OR các bạn có thể viết chữ hoa hay thường tùy ý. Ví dụ bạn có thể ghi 1 trong 2 cách sau nhưng hàm vẫn sẽ hiểu tiêu chí mà bạn đã nhập, đó là chuỗi “BÁNH KẸO” hoặc là “bánh kẹo”.

- Khi nhập hàm kết hợp bạn cần lưu ý các dấu như "", ;, () để tránh xảy ra lỗi trong hàm.

Một số bài tập ví dụ cơ bản về kết hợp hàm COUNTIF và hàm AND/OR

Cho bảng dữ liệu về thông tin sản phẩm gồm các trường sau: MÃ SP, SẢN PHẨM, HỆ ĐIỀU HÀNH, HÃNG, GIÁ BÁN, GIẢM GIÁ.

Bảng dữ liệu thông tin sản phẩm như hình trên

Bài tập 1: Hãy sử dụng hàm COUNTIF và hàm AND để kiểm tra số sản phẩm có giảm giá trên 10 triệu có lớn hơn hoặc bằng 1 và hãng Oppo có lớn hơn 3 hay không.

Công thức là: =AND(COUNTIF(F2:F10;">10000000")>=1;COUNTIF(D2:D10;"Oppo")>3)

Giải thích:

+ COUNTIF(F2:F10;">10000000")>=1: Sẽ so sánh số sản phẩm mà có giảm giá trên 10 triệu có lớn hơn hoặc bằng 1.

+ COUNTIF(D2:D10;"Oppo")>3: Sẽ so sánh số sản phẩm của hãng Oppo có lớn hơn 3 hay không.

Bài tập kết hợp hàm COUNTIF và hàm AND như hình trên

Bài tập 2: Hãy sử dụng hàm COUNTIF và hàm OR để kiểm tra xem số sản phẩm có giá bán trên 20 triệu có lớn hơn 2 hoặc hệ điều hành Android có lớn hơn 10 hay không.

Công thức là: =OR(COUNTIF(E2:E10;">20000000")>2;COUNTIF(C2:C10;"Android")>10)

Giải thích:

+ COUNTIF(E2:E10;">20000000")>2: Sẽ so sánh số sản phẩm mà có giá bán trên 20 triệu có lớn hơn 2.

+ COUNTIF(C2:C10;"Android")>10: Sẽ so sánh số sản phẩm của hệ điều hành Android có lớn hơn 10 hay không.

Bài tập kết hợp hàm COUNTIF và hàm OR như hình trên

Những câu hỏi thường gặp khi bạn kết hợp hàm COUNTIF và hàm AND/OR

Hàm COUNTIF còn có thể kết hợp với hàm nào nữa hay không?

- Trả lời: Ngoài hàm AND/OR thì hàm COUNTIF còn có thể kết hợp với nhiều hàm khác nữa như: hàm LEFT, RIGHT, MID, MATCH, INDEX, SUM, VLOOKUP,... và có thể nhiều hơn nữa.

Lợi ích khi bạn kết hợp hàm COUNTIF với hàm AND/OR là gì?

- Trả lời: Hàm COUNTIF kết hợp với hàm AND/OR sẽ giúp các bạn có thể kiểm tra, so sánh những thông tin dữ liệu có chính xác hay không. Và việc kết hợp các hàm này sẽ thường được sử dụng nhiều trong việc kiểm tra số lượng.

Trên đây là bài viết hướng dẫn cách kết hợp hàm COUNTIF và hàm AND/OR trong Google Sheet đơn giản và hiệu quả nhất. Mong rằng bài viết này sẽ mang lại những thông tin bổ ích cho các bạn. Cảm ơn các bạn đã theo dõi và chúc bạn sẽ sớm thành công nhé!