Cách dùng hàm IMPORTRANGE Google Sheets và cách khắc phục lỗi

Hướng dẫn hàm IMPORTRANGE trong Google Sheets và lỗi phổ biến

Giới thiệu hàm IMPORTRANGE

Hàm IMPORTRANGE là một công cụ mạnh mẽ trong Google Sheets, giúp bạn lấy dữ liệu từ một bảng tính khác trong Google Drive của bạn hoặc từ bất kỳ liên kết chia sẻ công khai nào của ai đó chia sẻ. Đây là một công cụ hữu ích để hợp nhất dữ liệu hoặc quản lý dữ liệu từ nhiều nguồn khác nhau. Hàm IMPORTRANGE sẽ không hoạt động nếu 1 trong 2 file đích và file nguồn là file Excel (đuôi XLSX).

Cú pháp:

IMPORTRANGE(URL; Tên sheet!Dải ô cần lấy)

Ví dụ:

=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1l_KU_-6Pyhr7jV51IvUSSnXJe0KCbdgjLWsJwU1QToc/edit?gid=624371541“;”Toán!A1:G5“)

hoặc viết theo ID (là phầm nằm giữa cụm từ /d/ và /edit trong URL)

=IMPORTRANGE(“1l_KU_-6Pyhr7jV51IvUSSnXJe0KCbdgjLWsJwU1QToc“;”Toán!A1:G5“)

URL là có thể ở dạng Link đầy đủ hoặc ID của Link (trường hợp này bạn phải để trong nháy kép) hoặc trỏ đến một ô có chứa liên kết (ở tại ô chứa liên kết, Link có thể ở dạng đầy đủ, ID, dạng khối; lưu ý nếu Link dạng đường liên kết sẽ không sử dụng được). Bạn có thể lấy URL ở vị trí 1 hoặc bấm vào Sao chép đường liên kết ở vị trí 2

Tên sheet!Dải ô cần lấy là một chuỗi văn bản xác định bảng (sheet) và phạm vi ô bạn muốn lấy dữ liệu. Ví dụ DATA!A2:Z100. Chuỗi này có thể được ghép giữa “Tên Sheet” và “Dải ô cần lấy”. Cần lưu ý rằng, nếu bạn không có tên sheet, hàm IMPORTRANGE sẽ trả về sheet đầu tiên của file gốc.

Những lưu ý khi sử dụng hàm IMPORTRANGE

Cấp quyền truy cập: Lần đầu tiên sử dụng hàm với một bảng tính nguồn, bạn sẽ thấy một thông báo yêu cầu cấp quyền. Người dùng cần bấm “Allow access” để cho phép. Lưu ý rằng, khi bạn dùng file với nhiều người dùng, nếu bạn không chia sẻ File Gốc, thì ở file Báo cáo, chỉ bạn mới có quyền bấm “Cho phép truy cập”, khi đấy dữ liệu từ File gốc vẫn đổ sang File báo cáo cho tất cả mọi người dùng chung. Ngoài ra, bạn phải mồi hàm IMPORTRANGE (cấp quyền) trước khi lồng vào các hàm phức tạp hơn.

Cách tránh lỗi REF!: Đảm bảo rằng bạn đã nhập đúng URL và phạm vi. Nếu không, hàm sẽ trả về lỗi #REF!. Lỗi này cũng có thể xảy ra nếu bảng tính nguồn không được chia sẻ đúng quyền (công khai hoặc với bạn).
Giới hạn về hiệu năng: IMPORTRANGE có thể gặp vấn đề hiệu năng nếu sử dụng với lượng dữ liệu lớn hoặc khi kết hợp với nhiều hàm khác. Hãy cân nhắc giảm phạm vi dữ liệu được nhập. Nếu dữ liệu bạn quá lớn, thì không nên nghĩ đến việc lồng hàm IMPORTRANGE vào để tính toán, nên để dữ liệu IMPORTRANGE ở một sheet riêng biệt, rồi tính toán dựa trên sheet riêng biệt này. Hoặc chuyển sang dùng code.
Cập nhật dữ liệu: Dữ liệu từ IMPORTRANGE không tự động cập nhật ngay lập tức mà cần một khoảng thời gian nhất định.

Thay thế IMPORTRANGE bằng code khi dữ liệu lớn

Thực hành

đang làm

Lỗi hàm IMPORTRANGE không hoạt động hoặc Lỗi “Bạn không có quyền truy cập bảng tính đó”

Trường hợp này do file đích hoặc file nguồn của bạn là file Excel, khi dùng IMPORTRANGE thì bắt buộc cả 2 file đích và file nguồn phải là file Google Sheets (không có đuối .XLSX)

Hàm IMPORTRANGE báo “Lỗi nhập nội bộ” (Import Internal Error)

Lỗi này xảy ra có thể hàm IMPORTRANGE bị thiếu thông số.
Hoặc là lỗi tạm thời của IMPORTRANGE, bởi vì hôm qua vẫn công thức đấy chạy bình thường, hôm nay báo lỗi. Bạn chỉ việc xóa dấu bằng của công thức -> Enter -> Rồi đánh lại dấu bằng -> Enter để chạy lại công thức.
Lồng IMPORTRANGE với IFERROR: =IFERROR(IMPORTRANGE(…))
Chèn công thức =NOW() vào một ô ngẫu nhiên của bảng tính nguồn và đích. Sau đó, dùng IMPORTRANGE tham chiếu đến ô chứa công thức NOW của bảng tính khác.
Vào File => Cài đặt bảng tính => Tính toán và chọn Tính toán lại “Khi thay đổi và mỗi phút”

Nếu các giải pháp trên không được, bạn vui lòng đợi một khoảng thời gian nhất định để IMPORTRANGE cập nhật dữ liệu, hoặc chuyển dùng code.

Hàm IMPORTRANGE báo lỗi “Kết quả quá lớn” (Results too large)

Bạn sẽ thấy lỗi này khi bạn nhập quá nhiều ô. Số lượng ô chính xác mà bạn có thể nhập bằng IMPORTRANGE hiện chưa được Google công bố. Tôi đã thử nhập 60 cột và 6000 hàng (360.000 ô) thì xuất hiện lỗi này.

Nếu dữ liệu đầu vào lớn, phải chia nhỏ dữ liệu:

=LET(link;”https://docs.google.com/spreadsheets/d/……………….”;data;VSTACK(
IMPORTRANGE(link;”DATA!A1:BW10000″); IMPORTRANGE(link;”DATA!A10001:BW20000″); IMPORTRANGE(link;”DATA!A20001:BW30000″); IMPORTRANGE(link;”DATA!A30001:BW40000″); IMPORTRANGE(link;”DATA!A40001:BW50000″)))

Nếu dữ liệu bạn lớn, chia làm 3 file tách biệt, Báo cáo phải được tạo ngay ở file Trung tâm xử lý. Trên file báo cáo chỉ dùng 2 hàm IMPORTRANGE đảo chiều:

IMPORTRANGE báo lỗi #REF! dù cả file đích và file nguồn đều là file Google Sheets

Lỗi này có thể xảy ra do tên sheet của bạn đặt là number.

Để khắc phục lỗi này, bạn nên đặt tên sheet là text, hoặc nếu bạn vẫn muốn đặt tên sheet là number thì bạn nên tạo 1 tên sheet là text và chạy test thử hàm IMPORTRANGE ở sheet này trước, khi test thử hàm ở sheet có tên text thành công, thì các sheet với tên number sẽ chạy.

Ghép nhiều hàm IMPORTRANGE bị báo lỗi “ARRAY_LITERAL, một giá trị cố định của mảng thiếu giá trị của một hoặc nhiều hàng”

Lỗi này xảy ra khi bạn dùng {} để ghép mảng, và trong số các hàm IMPORTRANGE có 1 hoặc vài hàm bị lỗi không lấy được dữ liệu (có thể lỗi “Dữ liệu quá lớn” hoặc “Lỗi nội bộ”)

Bạn có thể dùng Vstack để thay thế việc dùng {} để ghép mảng, nhưng cách này nó sẽ bỏ qua các IMPORTRANGE bị lỗi.

Bạn phải xử lý dứt điểm hàm IMPORTRANGE lỗi. Để biết hàm IMPORTRANGE nào lỗi, bạn hãy làm ô mồi cho các hàm IMPORTRANGE, để xem hàm IMPORTRANGE bị lỗi