Bạn có biết cách sử dụng Google Sheets Query để lọc chỉ số từ cột chứa dữ liệu hỗn hợp không? Với hàm Filter, việc này dễ dàng hơn. Tuy nhiên, Query có thể xử lý dữ liệu, vì vậy bạn nên học cách sử dụng nó.
Gần đây, tôi đã cố gắng thực hiện tính phần trăm bằng Query bằng cách sử dụng hàm Count. Nhưng tiếc thay, Count trong Query đếm cả văn bản và số.
Như bạn có thể biết, hàm Google Sheets Query hoạt động không đoán trước được khi dữ liệu hỗn hợp xuất hiện trong cùng một cột.
Trường hợp này, kiểu dữ liệu xuất hiện nhiều nhất sẽ xác định kiểu dữ liệu của cột trong Query. Giá trị khác, có nghĩa là các kiểu dữ liệu thiểu số, được coi là giá trị null.
Tại điểm này, việc lọc chỉ số từ một cột trong Query là liên quan.
Cách sử dụng Google Sheets Query để lọc chỉ số từ cột chứa dữ liệu hỗn hợp
Đầu tiên, hãy xem công thức và ví dụ. Sau đó, tôi sẽ cho bạn biết điều gì có thể xảy ra nếu chúng ta áp dụng hàm tính toán Count với công thức này.
Đầu tiên, chúng ta sẽ xem cách sử dụng công thức trên một cột duy nhất.
Lọc trên một cột
Công thức: =QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0)
Với ví dụ này, tôi hy vọng bạn có thể học cách sử dụng Google Sheets Query để chỉ lọc các số.
Ở đây, tôi đã sử dụng biểu thức chính quy Matches trong mệnh đề “Where”.
Bạn có thể áp dụng ví dụ này trên bảng tính của mình và lọc chỉ số từ cột chứa dữ liệu hỗn hợp.
Vui lòng lưu ý rằng giá trị trả về từ công thức trên có thể ở định dạng văn bản.
Vậy làm cách nào để chuyển đổi những số đó từ định dạng văn bản sang giá trị số trong Google Sheets Query? Xem công thức dưới đây.
=ARRAYFORMULA(VALUE(QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0)))
Tôi vừa sử dụng hàm Value và ArrayFormula để chuyển đổi các số từ định dạng văn bản sang định dạng số.
Tôi hy vọng bạn có thể học cách sử dụng Google Sheets Query để chỉ lọc các số khi một cột chứa dữ liệu hỗn hợp.
Query để lọc chỉ số từ cột dữ liệu hỗn hợp: Ứng dụng thực tế
Bây giờ, hãy xem hai công thức Query dưới đây. Cả hai công thức này đều đếm cột A với và không lọc các giá trị văn bản.
Các công thức ví dụ trên cho thấy cách sử dụng Query trong một cột dữ liệu hỗn hợp để có được kết quả mong muốn.
Công thức có đường viền màu đỏ đếm tất cả các giá trị trong cột A. Nhưng câu trả lời có thể đúng hoặc không đúng, vì Query đôi khi bỏ qua các giá trị trong các cột dữ liệu hỗn hợp như vậy.
Công thức 1: =QUERY(A3:A,"Select A,count(A) group by A label Count(A)''")
Nhưng công thức thứ hai có đường viền màu xanh lam (tôi đã cập nhật lại dưới đây) lọc cột cho các giá trị số và sau đó áp dụng hàm count.
Công thức 2: =QUERY( ARRAYFORMULA(VALUE(QUERY(A3:A,"select A where A matches '[0-9-.]+' ",0))), "Select Col1, Count(Col1) group by Col1 label Count(Col1)''" )
Lưu ý: Đối với biểu thức dữ liệu trong Query, các định danh cột nên là Col1, Col2, … thay vì A, B,… khi sử dụng.
Vui lòng kiểm tra kết quả của cả hai công thức trên ảnh chụp màn hình để hiểu sự khác biệt.
Query trên nhiều cột và lọc chỉ số
Thường chúng ta sử dụng Query với dữ liệu nhiều cột. Bạn có thể không thể áp dụng cách tiếp cận VALUE như trên trong trường hợp đó.
Vì vậy, bạn nên biết cách sử dụng Query để chỉ lọc các số từ một cột dữ liệu hỗn hợp khi có nhiều hơn một cột trong bảng.
Dưới đây, tôi có hai cột. Tôi muốn nhóm cột A và tổng cột B chỉ cho dữ liệu trong cột B có kiểu dữ liệu là số.
Công thức sau sẽ trả về lỗi “Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC”:
=query(A3:B,"Select A,sum(B) where A is not null group by A")
Chúng ta có thể sử dụng Query sau để lọc các hàng chỉ chứa số trong cột dữ liệu hỗn hợp B:
=QUERY(A3:B,"select * where B matches '[0-9-.]+' ",0)
Để thực hiện công thức tổng hợp, chúng ta phải định dạng các giá trị trong cột thứ hai thành số. Chúng ta có thể sử dụng HSTACK, LET và CHOOSECOLS để làm điều đó.
=LET( data, QUERY(A3:B,"select * where B matches '[0-9-.]+' ",0), HSTACK( CHOOSECOLS(data,1), ARRAYFORMULA(VALUE(CHOOSECOLS(data,2))) ) )
Làm thế nào để sử dụng nó?
Trong công thức trên, hãy thay thế công thức Query của bạn bằng công thức Query của bạn.
Sau đó, chỉ định các cột riêng lẻ được phân tách bằng dấu phẩy trong HSTACK.
Tôi nghĩ rằng CHOOSECOLS(data,1) sẽ trả về cột đầu tiên và ARRAYFORMULA(VALUE(CHOOSECOLS(data,2))) sẽ trả về cột thứ hai sau khi chuyển đổi giá trị thành số.
Nếu phạm vi của bạn là A3:C và bạn muốn trích xuất ba cột, hãy chỉ định CHOOSECOLS(data,3) trong HSTACK cho cột thứ ba.
Tôi để lại phần tổng hợp. Bây giờ bạn có một bộ dữ liệu đã được làm sạch. Vì vậy, bạn có thể thực hiện các nhiệm vụ khác trên nó.
Đó là tất cả cho bây giờ. Chúc bạn vui!
