Bạn có thể thực sự rút ngắn công thức truy vấn Google Sheets khi bạn muốn kiểm tra điều kiện trong nhiều cột. Điều này có thể bao gồm các toán tử logic AND, OR trong mệnh đề WHERE của truy vấn.
Tôi dành bài viết này cho tất cả những người yêu thích truy vấn Google Sheets. Ở đây, tôi sẽ cố gắng đưa ra một số ý kiến để giản lược công thức truy vấn Google Sheets của bạn trong trường hợp điều kiện ở nhiều cột liên quan đến nó.
Thực chất, việc giản lược công thức truy vấn phụ thuộc vào vấn đề bạn muốn giải quyết. Vì vậy, tôi sẽ đưa ra một số ví dụ để hiểu rõ hơn về nó.
Tôi sẽ kiểm tra các điều kiện sau đây trong nhiều cột trong truy vấn.
- Tôi sẽ sử dụng toán tử logic AND để kiểm tra xem tất cả các cột cụ thể (trong mỗi hàng) có trống không.
- Trong thử nghiệm thứ hai, tôi sẽ sử dụng toán tử logic OR để tìm xem bất kỳ trong các cột cụ thể trong mỗi hàng có giá trị nào không.
Trong những thử nghiệm này, tất cả các cột có câu hỏi phải chứa các giá trị chuỗi hoặc số. Điều đó có nghĩa là tôi muốn kiểm tra một điều kiện trong 5 cột và tất cả các cột phải chứa các giá trị chuỗi hoặc số.
Hãy xem các ví dụ dưới đây để hiểu nó một cách tốt hơn.
Các điều kiện OR trong nhiều cột trong truy vấn
Trong kiểm tra luận lý OR, chỉ cần đáp ứng bất kỳ trong các điều kiện, không phải tất cả các điều kiện.
Hãy xem ví dụ về giá trị số trong các cột để kiểm tra.
Thử nghiệm OR – Giá trị số là điều kiện trong nhiều cột trong truy vấn
Dưới đây là dữ liệu mẫu. Công thức truy vấn sau đó để giản lược.
=query(A1:K,"Chọn A nếu C không phải null hoặc E không phải null hoặc G không phải null hoặc I không phải null hoặc K không phải null",1)
Làm thế nào để giản lược điều kiện trong nhiều cột trong công thức truy vấn này?
Chúng ta có thể làm điều này với sự giúp đỡ của một cột trợ giả ảo cũng như một cột trợ giúp vật lý. Hãy bắt đầu với cột trợ giúp ảo trước.
Sử dụng cột trợ giúp ảo:
Trước hết, hãy xem phiên bản đơn giản hóa của Công thức Truy vấn. Sau đó, bạn có thể tìm hiểu giải thích.
=ArrayFormula(query({A1:K,C1:C+E1:E+G1:G+I1:I+K1:K},"Chọn Col1 nếu Col12>0",1))
Thành thật mà nói, đây không phải là phiên bản đơn giản nhất vì nó liên quan đến một cột trợ giúp ảo. Nhưng điều kiện trong nhiều cột trong mệnh đề WHERE của truy vấn đã được giản lược.
Công thức truy vấn sẽ trở nên đơn giản hơn nếu bạn sử dụng cột trợ giúp vật lý. Trước khi tiếp tục, hãy xem xét những gì tôi đã làm trong truy vấn này để giản lược nó.
Tôi đã rút gọn phần này của mệnh đề WHERE trong truy vấn.
where C is not null or E is not null or G is not null or I is not null or K is not null
Nó được rút gọn thành;
where Col12>0
Thực sự, có 11 cột trong dữ liệu trong phạm vi A1:K. Bạn có thể đếm từ A đến K. Cột thứ 12 là cột ảo được tạo ra trong dữ liệu Truy vấn như sau;
{C1:C+E1:E+G1:G+I1:I+K1:K}
Vì các cột C, E, G, I và K chứa các giá trị số, tôi đã thêm các giá trị đó và tạo thành một cột duy nhất.
Sử dụng cột trợ giúp vật lý:
Chỉ cần nhập công thức trợ giúp này vào ô L1. Chúng ta đã đã sử dụng đến cột K.
=ArrayFormula(if(len(A1:A),C1:C+E1:E+G1:G+I1:I+K1:K,))
Dưới đây là công thức truy vấn được rút gọn tối đa để giản lược việc sử dụng điều kiện trong nhiều cột trong truy vấn. Bạn có thể đặt công thức này vào ô M1.
=query(A1:L,"Chọn A nếu L>0",1)
Thử nghiệm OR – Chuỗi văn bản là điều kiện trong nhiều cột trong truy vấn
Ở đây, tôi cũng đang cung cấp cho bạn cả các giải pháp dựa trên cột trợ giúp ảo và cột trợ giúp vật lý.
Phương pháp cột trợ giúp ảo:
Ở đây, các cột tiêu chí chứa các giá trị chuỗi, vì vậy thay vì cộng, bạn có thể kết hợp các cột như sau.
{A1:K,C1:C&E1:E&G1:G&I1:I&K1:K}
Trong mệnh đề WHERE, điều kiện rút gọn sẽ là;
where Col12 is not null
Hãy xem cách bạn có thể sử dụng một cột trợ giúp vật lý để rút ngắn công thức.
Phương pháp cột trợ giúp vật lý:
Chỉ cần nhập công thức trợ giúp này vào ô L1. Đây là công thức trợ giúp của chúng tôi.
=ArrayFormula(if(len(A1:A),C1:C&E1:E&G1:G&I1:I&K1:K,))
Dưới đây là phiên bản rút gọn của Truy vấn để đi vào ô M1.
=query(A1:L,"Chọn A nếu L không phải null",1)
Như vậy, bạn có thể giản lược điều kiện trong nhiều cột trong truy vấn. Vậy phần OR luận lý đã kết thúc. Đây là phần tiếp theo.
Các điều kiện AND trong nhiều cột trong truy vấn
Trong kiểm tra luận lý AND, tất cả các điều kiện phải được đáp ứng.
Ở đây, tôi lại sử dụng cùng dữ liệu mẫu trên. Đây là công thức để giản lược.
=query(A1:K,"Chọn A nếu C không phải null và E không phải null và G không phải null và I không phải null và K không phải null",1)
Thử nghiệm AND – Giá trị số là điều kiện trong nhiều cột
Tôi sẽ giản lược các điều kiện AND trong nhiều cột trong công thức truy vấn này. Dưới đây là phiên bản rút gọn của công thức.
Không cần cột trợ giúp:
=ArrayFormula(query({A1:K,0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K},"Chọn Col1 nếu Col12=0",1))
Cột trợ giúp ảo làm gì?
Khi bạn chia 0 cho một số trong một cột, nó sẽ trả về 0. Điều đó có nghĩa là số 0 chia cho n trả về 0. Nhưng nếu bạn chia 0 cho 0 trong một cột khác, nó sẽ trả về lỗi #DIV/0! vì chia cho 0 không xác định.
Nếu tất cả các cột trong một hàng có giá trị, thì công thức cột ảo có giá trị 0 trong hàng đó.
=0/C2+0/E2+0/G2+0/I2+0/K2
Xem ảnh bên dưới để biết thêm chi tiết (đây chỉ là để giải thích).
Số 0 được làm nổi bật màu dương xanh lam cho thấy tất cả các cột trong hàng đó (được gạch dưới màu đỏ) có giá trị, nếu không, bạn có thể thấy lỗi #DIV/0!
Trong công thức rút gọn trên, phần dưới đây có;
where C is not null and E is not null and G is not null and I is not null and K is not null
Được rút gọn thành;
where Col12=0
Sử dụng cột trợ giúp vật lý:
Chỉ cần nhập công thức trợ giúp này vào ô L1.
=ArrayFormula(if(len(A1:A),0/C1:C+0/E1:E+0/G1:G+0/I1:I+0/K1:K,))
Sau đó, công thức đã được rút ngắn trong ô M1.
=query(A1:L,"Chọn A nếu L=0",1)
Còn chuỗi là tiêu chí trong trường hợp này?
Thử nghiệm AND – Chuỗi văn bản là điều kiện trong nhiều cột
Đây là công thức bình thường.
=query(A1:K,"Chọn A nếu C không phải null và E không phải null và G không phải null và I không phải null và K không phải null",1)
Không cần cột trợ giúp:
Ở đây, tôi đề xuất bạn chỉ sử dụng công thức trợ giúp. Nhưng ở đây, bạn cũng có thể sử dụng công thức rút gọn của tôi ở trên mặc dù nó không trông sạch sẽ.
Chỉ cần bao quanh các cột chúng ta muốn kiểm tra tiêu chí bằng chức năng ISTEXT.
=ArrayFormula(query({A1:K,0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K)},"Chọn Col1 nếu Col12=0",1))
Sử dụng cột trợ giúp vật lý:
Chỉ cần nhập công thức trợ giúp này vào ô L1.
=ArrayFormula(if(len(A1:A),0/istext(C1:C)+0/istext(E1:E)+0/istext(G1:G)+0/istext(I1:I)+0/istext(K1:K),))
Sử dụng Truy vấn này trong ô M1.
=query(A1:L,"Chọn A nếu L=0",1)
Đó là tất cả. Chúc bạn vui vẻ!
