Thủ thuật

Hướng dẫn cách kết hợp hàm QUERY và hàm AND/OR trong Google Sheet đầy đủ và nhanh chóng nhất

Trương Nguyễn Anh Thư

08-05-2023, 9:52 am

Sử dụng hàm trong Google Sheet sẽ giúp các bạn xử lý các truy vấn, tính toán một cách nhanh chóng. Tuy nhiên, trong nhiều trường hợp sử dụng hàm đơn lẻ lại không thể giải quyết yêu cầu của bạn. Vậy nên, bài viết hôm nay sẽ hướng dẫn bạn cách kết hợp hàm QUERY và AND/OR trên Google Sheet.

Cách áp dụng hàm QUERY vào trong Google Sheet

Hàm QUERY là hàm gì?

Hàm QUERY là một hàm mang lại khả năng tìm kiếm kiểu cơ sở dữ liệu cho bảng tính, vì vậy bạn có thể tiến hành tra cứu và lọc dữ liệu theo bất kỳ định dạng nào mà bạn muốn.

Tương tự như một truy vấn SQL thông thường, hàm QUERY sẽ chọn các cột để hiển thị (SELECT) và xác định các tham số cho việc tìm kiếm (WHERE).

Cách áp dụng hàm QUERY vào trong Google Sheet

Công thức của hàm:

=QUERY(data, query, headers)

Trong đó:

+ Data là phạm vi của một ô, một vùng dữ liệu.

+ Headers là một tùy chọn đặt số lượng hàng tiêu đề sẽ được bao gồm ở đầu phạm vi dữ liệu.

+ Query là một câu lệnh select…. where mà bạn muốn.

Ví dụ minh họa cho hàm QUERY:

=QUERY('Sales Order'!A2:F12; "SELECT A, B, C, E, F WHERE F = 'United Kingdom'")

Ví dụ câu lệnh truy xuất query như hình trên

Cách áp dụng hàm AND/OR trong Google Sheet

Hàm AND là hàm gì?

Hàm AND là một hàm sẽ trả về TRUE nếu tất cả đối số đã cho là đúng theo logic và trả về kết quả là FALSE nếu bất kỳ đối số nào đã cho là sai theo logic.

Cách áp dụng hàm AND vào trong Google Sheet

Công thức của hàm:

= AND(biểu_thức_logic1; [biểu_thức_logic2; ...])

Ví dụ minh họa cho hàm AND:

=AND(C2>5;E2>5)

Ví dụ câu lệnh sử dụng AND như hình trên

Hàm OR là hàm gì?

Hàm OR là hàm sẽ trả về kết quả TRUE nếu bất kỳ đối số nào đã cho là đúng theo logic và trả về kết quả là FALSE nếu tất cả đối số đã cho là sai theo logic.

Cách áp dụng hàm OR vào trong Google Sheet

Công thức của hàm:

= OR(biểu_thức_logic1; [biểu_thức_logic2; ...])

Ví dụ minh họa cho hàm OR:

=OR(C2>20;F2="United Kingdom")

Ví dụ câu lệnh sử dụng OR như hình trên

Cách để kết hợp hàm QUERY và hàm AND/OR trong Google Sheet

Hàm AND/OR kết hợp với hàm QUERY

Công thức của hàm:

=AND(điều kiện 1;điều kiện QUERY(data,query, headers);.....)

=OR([điều kiện QUERY(data;query, headers)];điều kiện 2;....)

Điều kiện QUERY là một điều kiện của giá trị truy vấn ra của hàm QUERY.

Ví dụ minh họa cho hàm QUERY

=AND(QUERY(A2:F2;"SELECT A WHERE F='United Kingdom'")=536368; C2=3)

Ví dụ câu lệnh hàm AND lồng QUERY nhuư hình trên

=OR(QUERY(A2:F2;"SELECT A WHERE F='United Kingdom'")=536368; C2=3)

Hàm QUERY kết hợp với hàm AND/OR

Công thức của hàm:

=QUERY(data; "SELECT ... WHERE điều kiện 1 AND điều kiện 2 OR điều kiện 3"

+ Data là một phạm vi chứa vùng dữ liệu mà bạn cần truy vấn.

+ Headers là một tùy chọn đặt số lượng hàng tiêu đề sẽ bao gồm ở đầu phạm vi dữ liệu.

+ Query là một câu lệnh SELECT…. WHERE mà bạn muốn. SELECT là lựa chọn kết quả giá trị sẽ xuất ra, còn WHERE là một mệnh đề điều kiện. Hàm AND/OR thêm điều kiện cho mệnh đề WHERE.

Ví dụ minh họa cho hàm kết hợp cụ thể như sau:

=QUERY('Sales Order'!A2:F27; "SELECT A, B, C, E, F WHERE F = 'France' AND((C<10) OR (C>20))")

Ví dụ câu lệnh hàm QUERY lồng OR và AND như hình trên

Các lỗi thường gặp phải khi kết hợp hàm QUERY và hàm AND/OR trong Google Sheet

- Lỗi #N/A

Lỗi #N/A xảy ra khi trong công thức bạn sử dụng có chứa nội dung không có sẵn trong vùng dữ liệu, điều này dẫn tới việc hàm không thể tính toán, hoàn thành công thức được. Lỗi #N/A này cũng thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu. Và nếu đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng mà bạn cần tra cứu thì hàm cũng sẽ báo lỗi #N/A.

Lỗi #N/A không phải là lỗi do sai hàm, sai công thức mà chỉ là lỗi do hàm không tìm thấy được đối tượng cần tìm. Chính vì vậy mà trong nhiều trường hợp chúng ta chỉ cần ẩn lỗi này đi, biện luận trước những trường hợp mà dữ liệu không tồn tại trước khi thực hiện các hàm dò tìm hay tham chiếu.

Lỗi #REF

Lỗi #REF! là lỗi sẽ hiển thị khi công thức tham chiếu đến ô không hợp lệ. Điều này thường xuyên xảy ra nhất là khi các ô được công thức tham chiếu bị xóa hoặc bị dán đè lên.

Trong trường hợp này, bạn cần điều chỉnh công thức để công thức sử dụng tham chiếu đến dải ô thay vì ô riêng lẻ.

Lỗi #ERROR

Lỗi #ERROR này có nghĩa là lỗi do Google Sheet không thể hiểu được công thức mà bạn đã nhập vì nó không thể phân tích cú pháp công thức để thực thi.

Lỗi #VALUE

Lỗi #VALUE! xuất hiện là một cách để Excel thông báo: “Đã xảy ra lỗi với công thức bạn nhập” hoặc “Đã xảy ra lỗi với ô mà bạn đang tham chiếu tới”. Lỗi #VALUE này thông báo rất chung chung và khó có thể tìm thấy được nguyên nhân chính xác cho lỗi.

Một số lưu ý quan trọng khi kết hợp hàm QUERY và hàm AND/OR

- Khi sử dụng hàm QUERY kết hợp với hàm AND/OR thì bạn phải lưu ý đến các dấu đóng ngoặc ), dấu nháy kép " ", dấu nháy đơn ' ' đối với việc truy vấn dữ liệu là text. Nếu bạn không cẩn thận sẽ rất dễ gây ra lỗi hàm.

- Khi tiến hành truy vấn dữ liệu bạn nên lưu ý đến khoảng cách, nếu bạn viết sát nhau thì hàm cũng sẽ bị lỗi.

- Truy vấn các dữ liệu có dạng là text bị bắt buộc phải có dấu nháy đơn ' ', còn trường hợp dữ liệu là số thì không cần phải có dấu nháy.

Một số bài tập ví dụ về kết hợp giữa hàm QUERY và hàm AND/OR

Bài 1: Bạn hãy thực hiện truy vấn để lấy dữ liệu từ bảng tính ở trang Sales Order với điều kiện nằm ở cột F là United Kingdom và giá trị ở cột C lớn hơn 3.

Câu lệnh thực hiện:

=QUERY('Sales Order'!A2:F27; "SELECT A, B, C, E, F WHERE F = 'United Kingdom' AND(C>3)")

Trong đó:

+ 'Sales Order'!A2:F27 là giá trị mà bộ dữ liệu truy vấn đến.

+ A, B, C, E, F biểu thị các cột giá trị mà bạn cần truy vấn ra.

+ F = 'United Kingdom', (C>3) là các điều kiện truy vấn dữ liệu.

Truy vấn nhứng hóa đơn ở United Kingdom và có giá trị cột số lượng là 3 như hình trên

Bài 2: Hãy thực hiện truy vấn lấy ra dữ liệu từ bảng tính ở trang Sales Order với điều kiện nằm ở cột F là France và giá trị nằm ở cột C có thể nhỏ hơn 10 hoặc lớn hơn 20.

Câu lệnh: =QUERY('Sales Order'!A2:F27; "SELECT A, B, C, E, F WHERE F = 'France' AND((C<10) OR (C>20))")

Trong đó:

+ 'Sales Order'!A2:F27 biểu thị giá trị bộ dữ liệu truy vấn đến.

+ A, B, C, E, F biểu thị các cột giá trị cần truy vấn ra.

+ F = 'France', (C<10), (C>20) là các điều kiện truy vấn dữ liệu.

Truy vấn nhứng hóa đơn ở France và có giá trị cột số lượng nhỏ hơn 10 hoặc lớn hơn 20 như hình trên

Câu hỏi thường gặp phải khi kết hợp hàm QUERY và hàm AND/OR

Có lợi ích gì khi ta sử dụng hàm QUERY kết hợp với hàm OR/AND?

Hàm QUERY với chức năng chính của nó là truy vấn và trả về dữ liệu theo yêu cầu, tuy nhiên mệnh đề where lại không thể cùng lúc thể hiện nhiều điều kiện. Vì vậy, bạn lựa chọn kết hợp hàm AND/OR vào trong mệnh đề where sẽ giúp giải quyết được điều này.

Đối với các hàm OR hoặc hàm AND, bạn cần sử dụng điều kiện có giá trị cần truy vấn từ khung dữ liệu thì hàm QUERY là một sự lựa chọn tối ưu nhất.

Ngoài hàm AND/OR, hàm QUERY còn có thể kết hợp với những hàm nào?

Ngoài việc kết hợp với hàm AND/OR, hàm QUERY với chức năng chính là truy vấn, tham chiếu dữ liệu, hàm QUERY này còn có thể kết hợp với nhiều hàm hoặc câu lệnh có điều kiện để giúp bạn lấy ra dữ liệu cần thiết như: lệnh SELECT, điều kiện WHERE, phép toán tử so sánh, hàm IF, hàm SUM, IMPORTRANCE,...

Bài viết trên đây đã thông tin đến bạn “tất tần tật” các kiến thức về Hướng dẫn cách kết hợp hàm QUERY và hàm AND/OR trong Google Sheet đầy đủ và nhanh chóng nhất. Mong rằng bài viết trên sẽ giúp ích nhiều cho bạn!