Cách sử dụng hàm QUERY trong Google Sheets

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ó đem lại khả năng kiếm tìm kiểu cơ sở dữ liệu mạnh mẽ cho bảng tính, vì thế 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 hơn 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 1 hàm QUERY tiêu biểu tương tự như SQL và đem lại sức mạnh của chức năng kiếm tìm cơ sở dữ liệu cho Google Sheets.

Định dạng của 1 công thức sử dụng hàm QUERY là:

  =QUERY(data, query, headers)  

Thay thế “data” bằng độ rộng ô (ví dụ, “A2:D12” hoặc “A:D” ).

Đối số “headers” tùy chọn đặt con số hàng tiêu đề sẽ kể cả ở đầu độ rộng dữ liệu. Nếu bạn có 1 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 thí dụ bên dưới, một trang tính (được coi là “Staff List” ) của bảng tính Google Sheets cho dù là 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 chuyên viên bắt buộc hay không.

Trên trang tính thứ hai, bạn cũng có thể sử dụng công thức QUERY để lấy danh sách mọi thứ các nhân viên không tham dự buổi huấn luyện bắt buộc. Danh sách này sẽ cho dù là số ID nhân viên, tên, họ và việc họ có tham dự buổi đào tạo hay không.

Để thi hành vấn đề này với dữ liệu được hiển thị ở trên, bạn cũng có thể 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à định vị các tham số cho kiếm tìm ( WHERE ). Nó trả về các cột A, B, C và E danh sách mọi thứ các hàng khớp, trong đấy 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 bản kê đã 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 1 bản kê riêng.

Ví dụ này sử dụng một độ rộng 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 chuyê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 chuyên viên mới hoặc khi ai đó tham dự buổi đào tạo.

Công thức chuẩn xác để thực hành điều đó là:

  =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'")  

Công thức này bỏ lỡ tiêu đề mới đầu “Employees” trong ô A1.

Nếu bạn thêm nhân viên thứ 11, không tham gia khóa huấn luyện 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ị chuyên viên mới.

Công thức QUERY nâng lên

Hàm QUERY rất linh hoạt. Nó cấp 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ư 1 phần của tìm kiếm. Bạn cũng có thể có thể sử dụng các toán tử đối chiếu (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ử đối chiếu (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 1 cột ( F ) vào bảng “Staff List” , với con số giải thưởng mà mỗi chuyên viên đã giành được.

Sử dụng QUERY, ta có thể tìm kiếm tất cả các chuyê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 biết hàm QUERY trả về danh sách 8 nhân viên đã giành được 1 hoặc nhiều giải thưởng. Trong tổng số 11 nhân viên, có 3 người chưa lúc nào 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 chuẩn kiếm tì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ề bản kê nhân viên, bài viết cũng đều có thể liệt kê tất cả những 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ả những ngày sinh trong khoảng từ ngày một tháng 1 năm 1980 đến ngày 31 tháng 12 năm 1989.

Như đã trình bày ở trên, 3 chuyên viên sinh năm 1980, 1986 và 1983 đáp ứng các đòi hỏi này.

Bạn cũng đều có thể sử dụng OR để xong xuôi được kết quả tương tự. Nếu sử dụng và một dữ liệu, nhưng chuyển đổi ngày và sử dụng OR , thí dụ cũng có thể có thể loại trừ mọi thứ 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 chuyên viên ban đầu, có 3 người được ra đời 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 chào đời 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à hoàn trả dữ liệu, bạn cũng cũng đều có thể trộn QUERY với những 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ố chuyê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 hành việc này, bạn cũng có thể 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ừ bản kê ví dụ, có 6 nhân viên đã xong xuôi khóa huấn luyện và 4 người vẫn chưa.

Bạn có thể đơn giản 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.

Google Sheets, hàm trong Google Sheets, hàm QUERY, hàm QUERY trong Google Sheets, cách sử dụng hàm QUERY, sử dụng hàm QUERY

Nội dung Cách sử dụng hàm QUERY trong Google Sheets được tổng hợp sưu tầm biên tập bởi: Tin Học Trường Tín. Mọi ý kiến vui lòng gửi Liên Hệ cho truongtin.top để điều chỉnh. truongtin.top tks.