Nếu cần thao tác với dữ liệu trong Google Sheets, hàm QUERY có thể giúp ích cho bạn! Nó mang lại khả năng tìm kiếm kiểu cơ sở dữ liệu mạnh mẽ cho bảng tính, vì vậy bạn có thể tra cứu và lọc dữ liệu theo bất kỳ định dạng nào bạn muốn. Bài viết sau đây sẽ hướng dẫn bạn cách sử dụng hàm QUERY trong Google Sheets.
Sử dụng hàm QUERY
Hàm QUERY không quá khó để thành thạo nếu bạn đã từng tương tác với cơ sở dữ liệu bằng SQL. Định dạng của một hàm QUERY điển hình tương tự như SQL và mang lại sức mạnh của chức năng tìm kiếm cơ sở dữ liệu cho Google Sheets.
Định dạng của một công thức sử dụng hàm QUERY là:
=QUERY(data, query, headers)Thay thế “data” bằng phạm vi ô (ví dụ, “A2:D12” hoặc “A:D”).
Đối số “headers” tùy chọn đặt số lượng hàng tiêu đề sẽ bao gồm ở đầu phạm vi dữ liệu. Nếu bạn có một tiêu đề trải rộng trên hai ô, như First trong A1 và Name trong A2, thì QUERY sẽ sử dụng nội dung của hai hàng đầu tiên làm tiêu đề kết hợp.
Trong ví dụ bên dưới, một trang tính (được gọi là “Staff List”) của bảng tính Google Sheets bao gồm danh sách nhân viên. Nó chứa tên, số ID nhân viên, ngày sinh và liệu họ có tham dự buổi đào tạo nhân viên bắt buộc hay không.
Trên trang tính thứ hai, bạn có thể sử dụng công thức QUERY để lấy danh sách tất cả các nhân viên không tham dự buổi đào tạo bắt buộc. Danh sách này sẽ bao gồm số ID nhân viên, tên, họ và việc họ có tham gia buổi đào tạo hay không.
Để thực hiện việc này với dữ liệu được hiển thị ở trên, bạn có thể nhập:
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'")Điều này truy vấn dữ liệu từ phạm vi từ A2 đến E12 trên trang “Staff List”.
Giống như một truy vấn SQL thông thường, hàm QUERY chọn các cột để hiển thị (SELECT) và xác định các tham số cho tìm kiếm (WHERE). Nó trả về các cột A, B, C và E danh sách tất cả các hàng khớp, trong đó giá trị trong cột E (“Attended Training”) là một chuỗi văn bản có nội dung “No”.
Như được trình bày ở trên, 4 nhân viên trong danh sách đã không tham dự buổi tập huấn. Hàm QUERY đã cung cấp thông tin này, cũng như các cột khớp để hiển thị tên và số ID nhân viên trong một danh sách riêng.
Ví dụ này sử dụng một phạm vi dữ liệu rất cụ thể. Bạn có thể thay đổi để truy vấn tất cả dữ liệu trong cột A sang E. Điều này sẽ cho phép bạn tiếp tục thêm nhân viên mới vào danh sách. Công thức QUERY bạn đã sử dụng cũng sẽ tự động cập nhật bất cứ khi nào bạn thêm nhân viên mới hoặc khi ai đó tham gia buổi đào tạo.
Công thức chính xác để thực hiện điều này là:
=QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'")Công thức này bỏ qua tiêu đề ban đầu “Employees” trong ô A1.
Nếu bạn thêm nhân viên thứ 11, không tham gia khóa đào tạo vào danh sách ban đầu, như được hiển thị bên dưới (Christine Smith), thì công thức QUERY cũng sẽ cập nhật và hiển thị nhân viên mới.
Công thức QUERY nâng cao
Hàm QUERY rất linh hoạt. Nó cho phép bạn sử dụng các phép toán logic khác (như AND và OR) hoặc những hàm của Google (chẳng hạn COUNT) như một phần của tìm kiếm. Bạn cũng có thể sử dụng các toán tử so sánh (lớn hơn, nhỏ hơn, v.v…) để tìm giá trị giữa 2 số liệu.
Sử dụng toán tử so sánh với QUERY
Bạn có thể sử dụng QUERY với các toán tử so sánh (như nhỏ hơn, lớn hơn hoặc bằng) để thu hẹp và lọc dữ liệu. Để làm điều này, bài viết sẽ thêm một cột (F) vào bảng “Staff List”, với số lượng giải thưởng mà mỗi nhân viên đã giành được.
Sử dụng QUERY, ta có thể tìm kiếm tất cả các nhân viên đã giành được ít nhất một giải thưởng. Định dạng cho công thức này là:
=QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0")Điều này sử dụng một toán tử so sánh > để tìm kiếm các giá trị lớn hơn 0 trong cột F.
Ví dụ trên cho thấy hàm QUERY trả về danh sách 8 nhân viên đã giành được một hoặc nhiều giải thưởng. Trong tổng số 11 nhân viên, có 3 người chưa bao giờ giành được giải thưởng.
Sử dụng AND và OR với QUERY
Các hàm toán tử logic lồng nhau như AND và OR hoạt động tốt trong công thức QUERY lớn hơn, để thêm nhiều tiêu chí tìm kiếm vào công thức.
Một cách tốt để thử nghiệm AND là tìm kiếm dữ liệu giữa hai ngày. Nếu áp dụng cho ví dụ về danh sách nhân viên, bài viết có thể liệt kê tất cả các nhân viên sinh từ năm 1980 đến năm 1989. Điều này cũng tận dụng các toán tử so sánh, như lớn hơn hoặc bằng (>=) và nhỏ hơn hoặc bằng (<=).
Định dạng cho công thức này là:
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'")Công thức này cũng sử dụng hàm DATE bổ sung để phân tích timestamp ngày một cách chính xác và tìm kiếm tất cả các ngày sinh trong khoảng từ ngày 1 tháng 1 năm 1980 đến ngày 31 tháng 12 năm 1989.
Như đã trình bày ở trên, 3 nhân viên sinh năm 1980, 1986 và 1983 đáp ứng các yêu cầu này.
Bạn cũng có thể sử dụng OR để tạo ra kết quả tương tự. Nếu sử dụng cùng một dữ liệu, nhưng chuyển đổi ngày và sử dụng OR, ví dụ có thể loại trừ tất cả các nhân viên sinh vào những năm 1980.
Định dạng cho công thức này sẽ là:
=QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'")
Trong số 10 nhân viên ban đầu, có 3 người được sinh ra vào những năm 1980. Ví dụ trên cho thấy 7 người còn lại, những người được sinh ra trước hoặc sau những ngày đã loại trừ.
Sử dụng COUNT với QUERY
Thay vì chỉ đơn giản là tìm kiếm và trả lại dữ liệu, bạn cũng có thể trộn QUERY với các hàm khác, như COUNT, để thao tác với dữ liệu. Giả sử, ví dụ muốn xóa một số nhân viên trong danh sách những người đã và đã tham gia khóa đào tạo bắt buộc.
Để thực hiện việc này, bạn có thể kết hợp QUERY với COUNT như sau:
=QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E")
Nhìn vào cột E (“Attended Training”), hàm QUERY đã sử dụng COUNT để đếm số lần mỗi loại giá trị (chứa chuỗi văn bản Yes hoặc No). Từ danh sách ví dụ, có 6 nhân viên đã hoàn thành khóa đào tạo và 4 người vẫn chưa.
Bạn có thể dễ dàng thay đổi công thức này và sử dụng nó với các loại hàm khác của Google, như hàm SUM trong Google Sheet.
- Đây là hàm vô cùng hữu ích trong Google Sheets nhưng không nhiều người biết tới
- Cách sử dụng hàm MEDIAN trong Google Sheets
- Cách dùng hàm AVERAGE trong Google Sheets
- Cách tính tổng trên nhiều sheet Google Sheets
- Cách sử dụng hàm lọc Filter trên Google Sheets
- 6 hàm hữu ích trong Google Sheets có thể bạn chưa biết
