Cách sử dụng hàm QUERY kết hợp IMPORTRANGE trong Google Sheets

Hàm QUERY kết hợp IMPORTRANGE trong Google Sheets cho phép truy vấn và lấy dữ liệu từ nhiều bảng tính khác nhau. Từ đó, người dùng có thể quản lý thông tin linh hoạt và hiệu quả hơn. Trong bài viết này, GCS Việt Nam sẽ hướng dẫn các bạn cách sử dụng hàm QUERY kết hợp IMPORTRANGE này cụ thể và chi tiết nhất. Cùng tham khảo ngay nhé. 

Hàm QUERY và cách sử dụng

Hàm QUERY cho phép bạn thực hiện các truy vấn dữ liệu phức tạp tương tự như ngôn ngữ SQL. Với hàm QUERY, bạn có thể chọn các cột cụ thể, lọc dữ liệu theo nhiều điều kiện, sắp xếp kết quả, nhóm các hàng và tạo bảng tổng hợp. 

Công thức của hàm QUERY 

 =QUERY(data, query, [headers])

Trong đó:

• data: Đây là phạm vi ô chứa dữ liệu mà bạn muốn truy vấn. Phạm vi này có thể nằm trong cùng một sheet hoặc một sheet khác trong cùng một bảng tính.
• query: Chứa các mệnh đề phổ biến bao gồm SELECT (chọn cột), WHERE (lọc hàng), ORDER BY (sắp xếp), GROUP BY (nhóm hàng), PIVOT (tạo bảng chéo), LIMIT (giới hạn số lượng hàng trả về), và OFFSET (bỏ qua một số hàng).
• [headers]: Đây là một đối số tùy chọn chỉ định số lượng hàng tiêu đề ở đầu phạm vi data. Nếu bỏ qua, Google Sheets sẽ cố gắng tự động phát hiện hàng tiêu đề. Nếu dữ liệu của bạn không có hàng tiêu đề, bạn có thể đặt giá trị này là 0.

Ví dụ hàm QUERY

Bảng dữ liệu bao gồm Mã sản phẩm, Tên sản phẩm, Giá và Số lượng

Để lọc dữ liệu Sản phẩm có giá trị dưới 8 triệu đồng

Câu lệnh QUERY truy vấn như sau 

=QUERY(A1:D9;’SELECT * WHERE C <= 8000000′;1)

Hàm IMPORTRANGE và cách thức sử dụng

Hàm IMPORTRANGE trong Google Sheets giúp bạn lấy dữ liệu từ bảng tính khác mà không cần sao chép thủ công. Do đó, bạn có thể tổng hợp dữ liệu từ nhiều nguồn, tạo báo cáo tổng quan với nhiều bảng tính. 

Công thức của hàm IMPORTRANGE

=IMPORTRANGE(“spreadsheet_url”, “range_string”)

Trong đó:

• spreadsheet_url: Đây là địa chỉ URL (đường link) của bảng tính Google Sheets mà bạn muốn nhập dữ liệu từ đó.

URL thường có dạng https://docs.google.com/spreadsheets/d/your_spreadsheet_id/edit. 

Phần “your_spreadsheet_id” là một chuỗi ký tự duy nhất xác định bảng tính.

• range_string: Đây là một chuỗi văn bản chỉ định phạm vi dữ liệu bạn muốn nhập. Ví dụ: “Sheet1!A1:C10” (nhập dữ liệu từ ô A1 đến C10 của sheet có tên “Sheet1”)

Ví dụ hàm IMPORTRANGE

Từ 1 bảng tính, sao chép URL

Mở trang Sheets mới, nhập công thức

=IMPORTRANGE(‘https://docs.google.com/spreadsheets/d/1dFKQANk3M7EthC7y3Cb-vmmtvn-MhCfnVtkBzBN6I68/edit#gid=1424610541′,’Dữ liệu!A:O’)

Cách sử dụng hàm QUERY kết hợp IMPORTRANGE trong Google Sheets

Khi hàm QUERY kết hợp IMPORTRANGE sử dụng trong Google Sheets sẽ mở ra khả năng truy vấn dữ liệu mạnh mẽ từ các bảng tính. Đồng thời, sự kết hợp này còn giúp tự động hóa việc tổng hợp, lọc, sắp xếp dữ liệu từ nhiều nguồn khác nhau. Cũng như giải quyết triệt để tình trạng sao chép thủ công, giúp người dùng tiết kiệm thời gian và đảm bảo dữ liệu luôn được cập nhật chính xác. 

Dưới đây là công thức cụ thể của hàm QUERY kết hợp IMPORTRANGE khi áp dụng trong Google Sheets:

Công thức kết hợp của hàm QUERY và IMPORTRANGE

=QUERY(IMPORTRANGE(spreadsheet_url ; chuỗi_dải_ô); QUERY)

Trong đó:

• IMPORTRANGE(spreadsheet_url ; chuỗi_dải_ô) đóng vai trò là nguồn dữ liệu đầu vào cho hàm QUERY.
• QUERY một chuỗi văn bản chứa các mệnh đề truy vấn.

Bạn có thể tùy chỉnh mệnh đề QUERY để thực hiện nhiều thao tác khác nhau, chẳng hạn như:

• Chọn các cột cụ thể: Sử dụng SELECT Col1, Col3, Col5.
• Lọc dữ liệu theo nhiều điều kiện: Sử dụng WHERE Col4 = ‘Giá trị’ AND Col2 < 50.
• Sắp xếp dữ liệu theo nhiều cột: Sử dụng ORDER BY Col5 DESC, Col1 ASC.
• Nhóm dữ liệu: Sử dụng GROUP BY Col1.
• Tính toán tổng, trung bình, số lượng: Sử dụng các hàm tổng hợp như SUM(Col3), AVG(Col2), COUNT(Col1) kết hợp với GROUP BY.

Vài ví dụ cụ thể về hàm QUERY và IMPORTRANGE kết hợp

Để hình dung rõ hơn về tính ứng dụng của việc kết hợp hàm QUERY và IMPORTRANGE, các bạn có thể xem xét một số ví dụ sau đây:

Kéo dữ liệu từ các file khác và lọc theo điều kiện ngày, tháng, năm

Giả sử bạn có bảng “Dữ liệu điểm học sinh” chứa thông tin bao gồm “Ngày tháng năm sinh, Tên học sinh, Giới tính, Điểm”. Bạn muốn tạo báo cáo tổng hợp trong một bảng tính khác về học sinh sinh năm 1998. 

Công thức cụ thể như sau:

=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′ ‘)

Kéo dữ liệu từ file khác và lọc ra định dạng văn bản

Sử dụng hàm QUERY kết hợp IMPORTRANGE để lọc thông tin nhóm đối tượng học sinh là nữ

Công thức cụ thể như sau:

=QUERY(IMPORTRANGE(‘https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747′;’Class B!A:F’);’SELECT * WHERE Col4 = ‘Nữ”)

Kéo dữ liệu từ một số file khác và lọc định dạng số

Sử dụng hàm hàm QUERY kết hợp IMPORTRANGE để lọc để lọc đối tượng học sinh có điểm từ 5 trở lên.

Công thức cụ thể như sau:

=QUERY(IMPORTRANGE(‘https://docs.google.com/spreadsheets/d/1a6n7LRioci5sm1MNQ1pXM9hzWCRnCZfC50iZySTeZkc/edit#gid=449113747′;’Class B!A:F’);’SELECT * WHERE Col6 >= 5′)

Hàm QUERY kết hợp IMPORTRANGE mang lại ưu điểm gì?

Vậy, việc kết hợp hàm QUERY kết hợp IMPORTRANGE mang lại những lợi ích vượt trội nào? Dưới đây là những ưu điểm cụ thể của hai hàm QUERY và IMPORTRANGE khi kết hợp với nhau.

Truy vấn từ đa dạng nguồn dữ liệu khác nhau

Thay vì phải mở từng file, sao chép và dán dữ liệu thủ công, việc kết hợp QUERY và IMPORTRANGE cho phép bạn trực tiếp truy vấn dữ liệu từ các bảng tính này một cách liền mạch. Bạn có thể chỉ định URL của từng bảng tính nguồn và phạm vi dữ liệu cần thiết, sau đó sử dụng sức mạnh của QUERY để hợp nhất và xử lý dữ liệu này trong một bảng tính duy nhất. Điều này giúp bạn tiết kiệm đáng kể thời gian và công sức, đồng thời giảm thiểu nguy cơ sai sót do thao tác thủ công.

Lọc và sắp xếp dữ liệu linh hoạt theo nhu cầu

Bạn có thể dễ dàng thiết lập các điều kiện phức tạp để chỉ giữ lại những thông tin quan trọng nhất. Cụ thể như là loại bỏ dữ liệu không liên quan, hoặc sắp xếp dữ liệu theo bất kỳ cột nào (ví dụ: sắp xếp đơn hàng theo ngày, lọc khách hàng ở một khu vực cụ thể, hoặc hiển thị các sản phẩm có doanh số cao nhất). Khả năng này giúp bạn nhanh chóng tìm ra thông tin chi tiết và đưa ra các quyết định dựa trên dữ liệu đã được tinh lọc.

Tạo báo cáo và tổng hợp dữ liệu hiệu quả

Việc hàm QUERY kết hợp IMPORTRANGE  còn giúp tạo ra các báo cáo tổng hợp và trực quan. Bạn có thể sử dụng các mệnh đề như GROUP BY trong hàm QUERY để nhóm dữ liệu theo các tiêu chí khác nhau (ví dụ: nhóm doanh số theo tháng, tổng hợp chi phí theo phòng ban), và sau đó sử dụng các hàm tổng hợp như SUM, AVERAGE, COUNT để tính toán các giá trị cần thiết. Điều này giúp bạn có được cái nhìn tổng quan về tình hình hoạt động, dễ dàng theo dõi các chỉ số quan trọng và đưa ra các phân tích sâu sắc hơn.

Tiết kiệm tối đa thời gian và công sức

Với hàm QUERY kết hợp IMPORTRANGE, quá trình sao chép và dán dữ liệu thủ công được tự động hóa hoàn toàn. Bạn chỉ cần thiết lập công thức một lần, và dữ liệu sẽ tự động được cập nhật khi có bất kỳ thay đổi nào ở bảng tính nguồn. Điều này giúp bạn giải phóng thời gian để tập trung vào các công việc phân tích và đưa ra quyết định quan trọng hơn, thay vì mất hàng giờ đồng hồ cho việc xử lý dữ liệu thủ công.

Dữ liệu luôn được cập nhật khi bảng tính nguồn thay đổi

Khi dữ liệu trong các bảng tính gốc được cập nhật, những thay đổi này sẽ tự động được phản ánh trong bảng tính mà bạn đang sử dụng hàm QUERY để truy vấn. Điều này đảm bảo rằng bạn luôn có trong tay những thông tin mới nhất và chính xác nhất để đưa ra các quyết định kịp thời và hiệu quả. Bạn không cần phải lo lắng về việc dữ liệu bị lỗi thời hoặc phải thực hiện các thao tác cập nhật thủ công thường xuyên.

Lỗi và cách khắc phục khi kết hợp hàm IMPORTRANGE và QUERY

Việc sử dụng đồng thời hàm QUERY kết hợp IMPORTRANGE đôi khi có thể dẫn đến một số lỗi. Dưới đây là những lỗi phổ biến nhất và cách bạn có thể khắc phục chúng:

Lỗi #REF!

Lỗi #REF! thường xuất hiện khi có vấn đề liên quan đến việc tham chiếu dữ liệu không hợp lệ. Cụ thể như là:

• File chia sẻ Google Sheets chưa được cấp quyền truy cập. Do đó hàm IMPORTRANGE không có quyền truy cập bảng tính khác. Để khắc phục, bạn chỉ cần di chuột qua ô hiển thị lỗi #REF! và cấp quyền cho phép truy cập. Sau khi cấp quyền, lỗi sẽ biến mất và dữ liệu sẽ được hiển thị.
• Sai URL của bảng tính nguồn cung cấp cho hàm IMPORTRANGE không chính xác hoặc bị sai sót (ví dụ: thiếu ký tự, thừa ký tự), hàm sẽ không thể tìm thấy bảng tính và trả về lỗi #REF! Kiểm tra kỹ URL của bảng tính nguồn. Sao chép lại URL trực tiếp từ thanh địa chỉ của bảng tính nguồn và dán vào công thức IMPORTRANGE, đảm bảo không có bất kỳ sai sót nào.

Lỗi #VALUE

Lỗi #VALUE! thường chỉ ra rằng có vấn đề với kiểu dữ liệu hoặc đối số được cung cấp cho hàm. Khi hàm QUERY kết hợp IMPORTRANGE, lỗi này có thể xuất hiện do:

• Sai cú pháp trong câu lệnh QUERY như:
  • Thiếu hoặc thừa dấu nháy đơn (‘) bao quanh câu lệnh QUERY.
  • Sử dụng sai dấu nháy (ví dụ: nháy kép ” thay vì nháy đơn ‘) cho toàn bộ câu lệnh QUERY.
  • Tham chiếu cột không tồn tại hoặc viết sai tên cột (nếu sử dụng tiêu đề) trong mệnh đề SELECT, WHERE, ORDER BY, v.v.
  • Sử dụng sai toán tử hoặc từ khóa trong câu lệnh QUERY.
  • Không nhất quán trong kiểu dữ liệu khi so sánh trong mệnh đề WHERE (ví dụ: so sánh số với chuỗi mà không có dấu nháy đơn).
  • Lỗi chính tả trong các từ khóa của QUERY (ví dụ: SELEKT thay vì SELECT).
  • Sử dụng dấu chấm phẩy (;) thay vì dấu phẩy (,) để phân tách các mệnh đề trong QUERY (điều này thường không gây ra lỗi #VALUE! mà là lỗi cú pháp trực tiếp, nhưng vẫn cần lưu ý).
• Đối số headers không hợp lệ trong hàm QUERY: Ví dụ một số âm hoặc một giá trị lớn hơn số hàng dữ liệu, hàm QUERY có thể trả về lỗi #VALUE!. Cách khắc phục đảm bảo đối số headers là một số nguyên không âm, biểu thị số lượng hàng tiêu đề ở đầu dữ liệu nhập từ IMPORTRANGE. Nếu dữ liệu của bạn không có tiêu đề, hãy đặt giá trị này là 0. Nếu có một hàng tiêu đề, đặt là 1.

Lỗi #ERROR

Lỗi #ERROR! là một lỗi chung cho biết có vấn đề không xác định hoặc một lỗi cú pháp nghiêm trọng trong công thức. Khi kết hợp hàm QUERY và IMPORTRANGE, lỗi này có thể xuất hiện do:

• Lỗi cứ pháp trong hàm IMPORTRANGE ví dụ thiếu dấu ngoặc kép quanh URL hoặc phạm vi) có thể gây ra lỗi #ERROR!. Cách khắc phục là kiểm tra kỹ cú pháp của hàm IMPORTRANGE. Đảm bảo URL và chuỗi phạm vi được đặt trong dấu nháy kép (“) và được phân tách bằng dấu chấm phẩy (;) theo đúng cú pháp.
• Vấn đề về kết nối. Hãy kiểm tra kết nối internet của bạn. Sau một khoảng thời gian ngắn có thể thử lại. Nếu vấn đề vẫn tiếp diễn, có thể có vấn đề từ phía Google Sheets.

Lưu ý quan trọng khi kết hợp hàm QUERY và IMPORTRANGE

Để tránh những vấn đề không mong muốn khi hàm QUERY kết hợp IMPORTRANGE, bạn cần lưu ý:

• Quản lý quyền truy cập cẩn thận

Nếu quyền truy cập vào bảng tính nguồn bị thay đổi hoặc thu hồi, công thức IMPORTRANGE sẽ ngừng hoạt động và trả về lỗi #REF!. Nên xem xét việc sử dụng các phương pháp chia sẻ bảng tính an toàn và chỉ cấp quyền truy cập cần thiết.

• Đảm bảo tính ổn định của bảng tính nguồn

Công thức hàm QUERY kết hợp IMPORTRANGE sẽ thay đổi hoặc trả về kết quả lỗi khi cấu trúc của bảng tính nguồn thay đổi. Ví dụ: cột bị xóa, tên sheet đổi, vị trí cột thay đổi.

• Sử dụng tiêu đề cột nhất quán

Nếu bạn định sử dụng tên cột trong câu lệnh QUERY hãy đảm bảo rằng bảng tính nguồn có hàng tiêu đề rõ ràng và nhất quán. Tránh sử dụng các ký tự đặc biệt hoặc khoảng trắng thừa trong tiêu đề cột để tránh gây ra lỗi khi truy vấn.

FAQ – Câu hỏi thường gặp

Dưới đây là một số câu hỏi thường gặp liên quan đến việc sử dụng hàm QUERY và IMPORTRANGE:

1 – Tôi có thể kết hợp QUERY và IMPORTRANGE để lấy dữ liệu từ nhiều bảng tính khác nhau không?

Có, bạn có thể lồng nhiều hàm IMPORTRANGE bên trong một hàm QUERY bằng cách sử dụng dấu ngoặc nhọn {} để tạo một mảng dữ liệu ảo. 

*Ví dụ: =QUERY({IMPORTRANGE(“URL1”, “Sheet1!A:B”); IMPORTRANGE(“URL2”, “Sheet2!C:D”)}, “SELECT Col1, Col4 WHERE Col2 > 10”). Tuy nhiên, cần lưu ý về hiệu suất khi làm việc với nhiều nguồn dữ liệu lớn.

2 – Tại sao tôi cần cấp quyền truy cập khi sử dụng IMPORTRANGE?

IMPORTRANGE đang cố gắng truy cập dữ liệu từ một bảng tính khác mà bạn sở hữu hoặc có quyền xem. Google Sheets yêu cầu bạn cấp quyền để đảm bảo bảo mật và quyền riêng tư cho dữ liệu của bạn.

3 – Tôi có thể sử dụng các hàm khác bên trong QUERY khi làm việc với dữ liệu từ IMPORTRANGE không?

Tôi có thể sử dụng các hàm khác bên trong QUERY khi làm việc với dữ liệu từ IMPORTRANGE không? Ví dụ như SUM(), AVG(), COUNT(), MAX(), MIN(), DATE(), YEAR(), v.v.

4 – Tôi có thể cập nhật dữ liệu trong bảng tính nguồn thông qua bảng tính đích sử dụng QUERY và IMPORTRANGE không?

Không, IMPORTRANGE chỉ cho phép bạn nhập dữ liệu từ một bảng tính khác

Lời kết 

Tóm lại, việc nắm vững hàm QUERY kết hợp IMPORTRANGE mở ra giải pháp mạnh mẽ để xử lý dữ liệu liên trang trong Google Sheets. Từ đó giúp bạn dễ dàng truy vấn, lọc và tổng hợp thông tin từ nhiều nguồn, nâng cao hiệu quả công việc. Nếu có thắc mắc, hoặc bất cứ thông tin gì cần giải đáp, bạn đừng quên ngần ngại liên hệ qua các kênh phía dưới để GCS Việt Nam hỗ trợ và tư vấn được tốt nhất.

• Fanpage: GCS – Google Cloud Solutions 
• Hotline: 024.9999.7777