Buzz
Hàm QUERY trong Google Sheet giúp bạn tìm kiếm và lọc dữ liệu linh hoạt theo ý muốn. Hàm IMPORTRANGE cho phép bạn chuyển dữ liệu từ một bảng tính sang bảng tính khác. Kết hợp cả hai hàm này mang lại nhiều ưu điểm. Hãy khám phá cùng chúng tôi!
Xem ngay video hướng dẫn cơ bản về cách sử dụng hàm QUERY kết hợp với hàm IMPORTRANGE:
1. Hướng dẫn cách sử dụng hàm QUERY trong Google Sheet
- Hàm QUERY có chức năng gì?
Hàm Query trong Google Sheet giúp bạn xử lý dữ liệu linh hoạt (tra cứu, lọc, kết hợp dữ liệu từ nhiều sheet thành một).
- Hướng dẫn sử dụng hàm QUERY
Ví dụ với bảng sau đây:
Danh sách Sản Phẩm
Bảng dữ liệu bao gồm các trường: Mã sản phẩm, Tên sản phẩm, Giá, và Số lượng.
Lọc ra những điện thoại có Giá dưới 8 triệu đồng từ dữ liệu trên.
Để thực hiện, câu lệnh Query truy vấn sẽ là:
=QUERY(A1:D9;'SELECT * WHERE C <= 8000000')
Hướng dẫn cách sử dụng hàm QUERY cơ bản
Để hiểu rõ hơn về cách sử dụng hàm, bạn có thể đọc bài viết chi tiết về hàm QUERY trong Google Sheet
2. Thực hành hàm IMPORTRANGE trong Google Sheet
- Hàm IMPORTRANGE làm gì?
Hàm IMPORTRANGE được sử dụng để truyền dữ liệu với số lượng lớn giữa các trang tính.
- Hướng dẫn cách sử dụng hàm IMPORTRANGE
Công thức thực hiện:
=IMPORTRANGE(đường_link_bảng_tính; dải_ô_chuỗi)
Ví dụ minh họa:
Chọn một trang tính bất kỳ bạn muốn trích dữ liệu, sao chép URL của trang tính đó.
Hướng dẫn cách sử dụng hàm IMPORTRANGE
Ghi nhớ hoặc sao chép sẵn tên của trang tính bạn muốn trích dữ liệu.
Ghi nhớ hoặc sao chép sẵn tên của trang tính bạn muốn trích dữ liệu
Mở trang tính mới sheet.new trên Google, nhập công thức sau:
=IMPORTRANGE('https://docs.google.com/spreadsheets/d/1dFKQANk3M7EthC7y3Cb-vmmtvn-MhCfnVtkBzBN6I68/edit#gid=1424610541','Dữ liệu!A:O')
Trong đó:
+ spreadsheet_url: Liên kết của trang tính bạn muốn trích dữ liệu.
+ chuỗi_dải_ô: Là tên trang tính và dải ô bạn muốn trích dữ liệu (ở đây là từ cột A đến cột O)
Chú ý: Hãy đặt dấu ' ' ở đầu và cuối địa chỉ url, chuỗi dải ô để trích dữ liệu thành công.
Mở một trang tính mới trên Google bằng cách sử dụng sheet.new, nhập công thức IMPORTRANGE
Để hiểu rõ cách sử dụng hàm, bạn nên tham khảo hướng dẫn chi tiết về hàm IMPORTRANGE trong Google Sheet
3. Cách kết hợp hàm QUERY và hàm IMPORTRANGE trong Google Sheet
QUERY lồng IMPORTRANGE
Công thức:
=QUERY(IMPORTRANGE(spreadsheet_url ; chuỗi_dải_ô); QUERY)
Ví dụ: Nếu bạn chỉ quan tâm đến cột Ghi chú, sử dụng công thức sau:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1dFKQANk3M7EthC7y3Cb-vmmtvn-MhCfnVtkBzBN6I68/edit#gid=1424610541';'Dữ liệu!A:O');'SELECT Col13')
Trong đó:
+ IMPORTRANGE(spreadsheet_url ; chuỗi_dải_ô): Sử dụng để rút trích dữ liệu từ trang tính bạn muốn truy vấn.
+ QUERY: Chỉ lấy cột Col13 - Đây là cột bạn quan tâm (ví dụ, cột Ghi chú).
Lưu ý: Dữ liệu truy vấn phải được bao trong dấu ' ', và phải viết hoa chữ C khi truy vấn.
Ví dụ truy vấn QUERY lồng IMPORTRANGE
4. Ứng dụng thực tế của hàm QUERY và hàm IMPORTRANGE
Xem ngay video hướng dẫn cách lọc dữ liệu theo ngày, văn bản, số:
- Kéo dữ liệu từ file khác và lọc theo điều kiện ngày, tháng, năm
Dưới đây là ví dụ về bảng điểm trung bình của các học sinh.
Bảng minh họa điểm trung bình của học sinh
Sử dụng hàm QUERY kết hợp hàm IMPORTRANGE để truy vấn dữ liệu của những học sinh sinh năm 1998.
Công thức truy vấn:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747';'Class B!A:F');'SELECT * WHERE Col5 > DATE'1997-12-31' AND Col5 < DATE'1999-01-01' ')
Trích xuất thông tin của những người có năm sinh năm 1998.
- Truy vấn dữ liệu từ tệp khác và lọc theo định dạng văn bản
Sử dụng hàm QUERY kết hợp hàm IMPORTRANGE để lấy thông tin của những học sinh là nữ.
Công thức:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747';'Class B!A:F');'SELECT * WHERE Col4 = 'Nữ'')
Trích xuất thông tin của những học sinh giới tính là nữ.
- Truy vấn dữ liệu từ tệp khác và lọc theo định dạng số
Lọc những học sinh có điểm trung bình cao hơn 5 sử dụng hàm QUERY lồng hàm IMPORTRANGE.
Công thức:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747';'Class B!A:F');'SELECT * WHERE Col6 >= 5')
Lọc thông tin của những học sinh có điểm trung bình trên 5.
5. Những sai lầm thường gặp khi kết hợp hàm QUERY và hàm IMPORTRANGE
- Lỗi #VALUE
Lỗi #VALUE xuất hiện khi bạn tham chiếu đến địa chỉ ô (ở đây là cột A) không đúng. Nếu bạn tham chiếu từ một file trang tính khác, hãy sử dụng địa chỉ ô tuyệt đối để tránh lỗi này.
Xử lý lỗi #VALUE khi sử dụng hàm QUERY và hàm IMPORTRANGE
Để khắc phục lỗi, hãy sử dụng địa chỉ ô tương đối (tương ứng với địa chỉ là Col1).
Sửa chữa địa chỉ ô tương đối
- Lỗi #REF!
Nếu bạn gặp lỗi #REF! khi nhập công thức, điều này có nghĩa là bạn cần cấp quyền truy cập cho file của mình.
Lỗi #REF!
Để khắc phục, hãy nhấp vào ô và chọn 'Allow Access' (cho phép truy cập), sau đó dữ liệu sẽ hiển thị.
- Lỗi #ERROR
Lỗi #ERROR xuất hiện khi nhập sai cú pháp công thức, thường là do quên dấu ' '. Bạn có thể nhận biết lỗi này khi màu dữ liệu chuyển sang đen như hình dưới.
Lỗi #ERROR
Để khắc phục lỗi này, hãy chú ý đến các dấu nháy kép ' ' và kiểm tra màu của hàm, nếu màu xanh thì công thức là đúng.
Lưu ý kỹ các dấu
6. Một số lưu ý khi kết hợp hàm QUERY và hàm IMPORTRANGE
- Khi sử dụng hàm QUERY kết hợp với IMPORTRANGE, hãy chú ý đến các dấu đóng ngoặc ), dấu nháy kép ' ', và dấu nháy đơn ' ' khi truy vấn dữ liệu text. Thiếu sót trong cú pháp có thể gây lỗi hàm.
- Khi truy vấn dữ liệu, hãy lưu ý đến khoảng cách. Nếu bạn viết gần nhau, có thể dẫn đến lỗi hàm. Xem ví dụ ở hình dưới.
Lưu ý về khoảng cách
- Truy vấn dữ liệu có dạng text yêu cầu sử dụng dấu nháy đơn ' ', trong khi dữ liệu là số không cần dấu nháy.
Cẩn thận khi truy vấn dữ liệu text và số
7. Bài tập thực hành với hàm QUERY và hàm IMPORTRANGE
Nhấn vào đây để truy cập bài tập: hàm QUERY kết hợp hàm IMPORTRANGE. Bạn sẽ thực hành các hàm theo yêu cầu dưới đây!
Đề 1: Sử dụng hàm QUERY lồng IMPORTRANGE để truy vấn các dữ liệu: surname, forename, collegename.
Bài tập thực hành với hàm QUERY lồng IMPORTRANGE
Sử dụng hàm QUERY lồng IMPORTRANGE, ta có công thức như sau:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1xOr9O7YfttM8BBWF8k5thiSqPiCsTxrnAFcR0uzYI1k/edit#gid=2144150526';'dataList!A:H');'SELECT Col2, Col3, Col5')
Trong đó:
Thứ tự cột Col2, Col3, Col5 tương ứng với surname, forename, collegename.
Sử dụng hàm QUERY lồng IMPORTRANGE
Đề 2: Sử dụng hàm QUERY để truy vấn những người tên Sam có điểm lớn hơn hoặc bằng 50.
Sử dụng hàm QUERY lồng IMPORTRANGE để truy vấn những người tên Sam có điểm lớn hơn 50. Nhập công thức sau đây:
=QUERY(IMPORTRANGE('https://docs.google.com/spreadsheets/d/1xOr9O7YfttM8BBWF8k5thiSqPiCsTxrnAFcR0uzYI1k/edit#gid=2144150526';'dataList!A:H');'SELECT * WHERE Col3 ='Sam' AND Col6 >=50')
Trong đó:
WHERE Col3 ='Sam': Truy vấn những người tên Sam ở cột thứ 3 (Cột C).
Col6 >=50: Kết hợp hàm AND để truy vấn những người tên Sam có điểm lớn hơn hoặc bằng 50, ở cột 6 (Cột F).
Truy vấn những người tên Sam có điểm lớn hơn hoặc bằng 50
8. Những câu hỏi thường gặp khi kết hợp hàm QUERY và hàm IMPORTRANGE
Câu hỏi 1: Cho em hỏi là em viết công thức như thế này nhưng khi chạy ra thì kết quả lại là #REF! Em đang sai ở đoạn nào ạ?
=query({importrange('160ALyj2ecuB6DHrlFirEdRNklpPvhwndsdTammAWpLM','MS!A2:O2'); importrange('160ALyj2ecuB6DHrlFirEdRNklpPvhwndsdTammAWpLM','ALL HS!A2:O2')},'SELECT * WHERE Col1 is not null')
Trả lời: Để sử dụng Query với nhiều Importrange, hãy đảm bảo bạn đã cho phép truy cập từng Importrange ra bên ngoài trước.
Câu hỏi 2: Mình đặt lệnh là =QUERY({CPC;ANPHAT;KHOIDONG;AGRI;GAS};'select * where J is null';0). Báo lỗi #value: Không thể phân tích cú pháp chuỗi truy vấn đối với Hàm QUERY tham số 2: NO_COLUMN: J
Trả lời: Nếu bạn sử dụng cấu trúc ; thay vì \, và kiểm tra lại cú pháp truy vấn.
Câu hỏi 3: Cùng 1 URL, làm thế nào để nối các bảng dữ liệu trong các sheet khác nhau vào cùng 1 sheet tổng?
Trả lời: Sử dụng công thức ={'Sheet 1'!B1:F8;'Sheet 2'!A1:E16} để nối các bảng vào sheet tổng hợp.
Nối dữ liệu từ các sheet khác nhau
Khám phá NGAY các chương trình khuyến mãi hấp dẫn, mã giảm giá HOT tại Mytour:
- Ưu đãi đặc biệt cho mọi ngành hàng
- Mã giảm giá, khuyến mãi tại Mytour
Chúc bạn gặt hái thành công sau khi áp dụng hàm QUERY và kết hợp với IMPORTRANGE theo hướng dẫn ở trên!
