Với sự trợ giúp của hàm LET, chúng ta có thể tăng tốc xử lý khi sử dụng XLOOKUP với IMPORTRANGE trong Google Sheets. Kết hợp này cho phép sử dụng một tệp IMPORTRANGE trong một phép toán XLOOKUP duy nhất.
Tuy nhiên, nếu bạn muốn bỏ qua LET và sử dụng một tệp IMPORTRANGE duy nhất, bạn sẽ cần một phạm vi trợ giúp để lưu trữ dữ liệu đã được nhập.
Một số người có thể tự hỏi, tại sao không chỉ sử dụng VLOOKUP với IMPORTRANGE để sửa điều này?
Thực sự, VLOOKUP cần một phạm vi, vì vậy bạn sẽ không cần nhiều IMPORTRANGE cho việc tìm kiếm. Tuy nhiên, vấn đề là nó chỉ tìm kiếm khóa trong cột đầu tiên của phạm vi.
Để khắc phục điều này, bạn có thể sử dụng CHOOSECOLS. Tuy nhiên, VLOOKUP thiếu một số tính năng của XLOOKUP, chẳng hạn như tìm kiếm từ giá trị cuối cùng đến giá trị đầu tiên và chỉ định một ‘giá trị bị thiếu’, và nhiều tính năng khác.
Bây giờ, hãy đi sâu vào cách sử dụng XLOOKUP với một tệp IMPORTRANGE duy nhất mà không cần sử dụng các cột hoặc phạm vi trợ giúp trong Google Sheets.
XLOOKUP với Single IMPORTRANGE cho Vertical Lookup
Chúng ta có một bảng Google Sheets trong phạm vi A:F, minh họa dữ liệu bán hàng cho các sản phẩm khác nhau trên các khu vực khác nhau.
Trong bảng này, nhãn trường trong ô A1:F1 như sau: Sản phẩm, Khu vực, Q1, Q2, Q3 và Q4. Các cột tương ứng chứa các dữ liệu quan trọng sau:
- “Sản phẩm” trong Cột A chỉ ra loại sản phẩm đang được bán.
- “Khu vực” trong Cột B chỉ định khu vực địa lý của doanh số bán hàng.
- Các Cột C đến F chứa dữ liệu bán hàng cho mỗi “Q1”, “Q2”, “Q3” và “Q4” tương ứng.
Hãy xem ảnh chụp màn hình dưới đây và ghi nhớ tên tab, đó là ‘sales q1_q4’ vì khi nhập dữ liệu vào một tệp Google Sheets khác, chúng ta sẽ sử dụng tên tab với phạm vi, chẳng hạn như ‘sales q1_q4!A:F’.
Các yêu cầu tiên quyết
Trước khi sử dụng công thức XLOOKUP với một tệp IMPORTRANGE để tăng hiệu suất, chúng ta phải đảm bảo rằng IMPORTRANGE nhập dữ liệu đúng cách. Vì vậy, ban đầu, chúng ta sẽ nhập dữ liệu và sau đó xóa nó. Dưới đây là các bước:
- Sao chép URL của bảng mẫu chứa dữ liệu bán hàng từ thanh địa chỉ trình duyệt.
- Trong tệp Google Sheets mới, nhập công thức sau vào ô A1:
=IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F")Thay thế spreadsheet_url bằng URL đã sao chép. Sau khi nhập công thức này, có thể trả về lỗi #REF! . Di chuột qua lỗi này và bạn sẽ thấy một tùy chọn “Cho phép Truy cập”. Bấm vào nó để nhập dữ liệu.
Trong bước tiếp theo, chúng ta sẽ kết hợp công thức này trong LET, gán cho nó một tên, sau đó sử dụng tên đó trong XLOOKUP. Hãy tiếp tục với các bước này trong các ví dụ dưới đây.
Single Search Key và Single Output (Khoảng kết quả một cột duy nhất)
Thay vì cung cấp công thức trực tiếp, chúng ta hãy phân tích mã từng bước để nâng cao khả năng sử dụng XLOOKUP với IMPORTRANGE của bạn.
Giả sử bạn muốn tìm kiếm sản phẩm “Smartphone” trong cột A và lấy số lượng bán Q4 từ cột F. Thông thường, chúng ta sử dụng công thức XLOOKUP sau đây:
=XLOOKUP("Smartphone", A:A, F:F)Chúng ta có thể viết lại công thức như sau để sử dụng một phạm vi thay vì các cột cụ thể:
=XLOOKUP("Smartphone", CHOOSECOLS(A:F, 1), CHOOSECOLS(A:F, 6))Hàm CHOOSECOLS trích xuất cột đầu tiên (A:A) trong phạm vi tìm kiếm và cột thứ sáu (F:F) trong phạm vi kết quả.
Thay vì sử dụng phạm vi A:F hai lần trong công thức (như đã thấy ở hai CHOOSECOLS), chúng ta có thể gán cho nó một tên như “data” và sử dụng tên đó thay thế. Để làm điều này, chúng ta cần sử dụng hàm LET. Đây là công thức đó:
=LET(data, A:F, XLOOKUP("Smartphone", CHOOSECOLS(data, 1), CHOOSECOLS(data, 6))Bây giờ, để XLOOKUP với một tệp IMPORTRANGE duy nhất, hãy thay thế A:F bằng công thức IMPORTRANGE chính nó:
=LET(data, IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F"), XLOOKUP("Smartphone", CHOOSECOLS(data, 1), CHOOSECOLS(data, 6)))Nhập khóa tìm kiếm “Smartphone” vào ô A1 và sử dụng công thức trên với sự chỉnh sửa sau đây trong ô B1:
=LET(data, IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F"), XLOOKUP(A1, CHOOSECOLS(data, 1), CHOOSECOLS(data, 6)))
Single Search Key và Multiple Column Output (Khoảng kết quả nhiều cột)
Làm thế nào chúng ta kiểm tra số lượng bán của “Smartphone” trong Q1, Q2, Q3 và Q4?
Trong công thức trên, phạm vi tìm kiếm là A:A, tức là CHOOSECOLS(data, 1). Bạn không cần thay đổi nó. Tuy nhiên, bạn nên thay đổi phạm vi kết quả, tức là CHOOSECOLS(data, 6).
Nó sẽ là C:F và bạn có thể tham khảo nó bằng cách sử dụng CHOOSECOLS(data, {3, 4, 5, 6}).
Vì vậy, công thức XLOOKUP với một tệp IMPORTRANGE sẽ trở thành:
=LET(data, IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F"), XLOOKUP(A1, CHOOSECOLS(data, 1), CHOOSECOLS(data, {3, 4, 5, 6})))Công thức sẽ trả về số lượng bán Q1, Q2, Q3 và Q4 của “Smartphone”, tức là 30.000 đô la, 40.000 đô la, 35.000 đô la và 45.000 đô la, tương ứng.
XLOOKUP với IMPORTRANGE: Tìm kiếm dọc sử dụng nhiều khóa tìm kiếm
Khoảng kết quả một cột duy nhất
Giả sử bạn muốn giá bán Q4 của các sản phẩm Smartphone và Tablet, nằm trong ô A1 và A2.
Bạn có thể chỉ định phạm vi A1:A2 trong XLOOKUP và nhập công thức như một công thức mảng:
=ArrayFormula(LET(data, IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F"), XLOOKUP(A1:A2, CHOOSECOLS(data, 1), CHOOSECOLS(data, 6))))Multiple Column Result Range:
Như thế nào về việc trích xuất số lượng bán Q1, Q2, Q3 và Q4 của cả “Smartphone” và “Tablet” trong A1:A2?
Thay thế CHOOSECOLS(data, 6) bằng CHOOSECOLS(data, {3, 4, 5, 6}) sẽ không giúp ở đây. Bạn cần một bước bổ sung.
Đầu tiên, thay thế CHOOSECOLS(data, 6) bằng CHOOSECOLS(data, {3, 4, 5, 6}) trong công thức XLOOKUP với IMPORTRANGE ở trên. Sau đó, loại bỏ hàm ArrayFormula:
=LET(data, IMPORTRANGE("spreadsheet_url", "sales q1_q4!A:F"), XLOOKUP(A1:A2, CHOOSECOLS(data, 1), CHOOSECOLS(data, {3, 4, 5, 6})))Để lại nó.
Chúng ta có thể sử dụng hàm MAP để lặp qua các khóa tìm kiếm và thu được kết quả 2D từ công thức XLOOKUP với một tệp IMPORTRANGE.
Phạm vi khóa tìm kiếm là A1:A2. Trong MAP, bạn nên chỉ định nó như đối số mảng, ví dụ =MAP(A1:A2, LAMBDA(val,
trong đó val đại diện cho phần tử hiện tại trong mảng A1:A2.
Bây giờ, hãy sao chép công thức ở trên chúng ta đã giữ bên ngoài và dán nó trong hàm lambda để làm nhiệm vụ làm việc. Đừng quên thay thế A1:A2 trong XLOOKUP bằng val.
=MAP(A1:A2, LAMBDA(val, LET(data, IMPORTRANGE("spreadsheet-url", "sales q1_q4!A:F"), XLOOKUP(val, CHOOSECOLS(data, 1), CHOOSECOLS(data, {3, 4, 5, 6})))))
Tìm kiếm ngang ít phổ biến hơn, vì hầu hết chúng ta thường làm việc với dữ liệu dọc. Vì công thức trên yêu cầu chỉnh sửa nhỏ để thực hiện tìm kiếm ngang, hãy giải thích nó nữa.
Lần này, dữ liệu mẫu nằm trong dải 1:5, trong đó A1:A5 chứa các nhãn trường: Quốc gia, Dân số, GDP, HDI và Diện tích (km vuông). Dữ liệu liên quan được phân tán trên mỗi hàng tương ứng.
Tên tab là “Country_Indicators”. Vì vậy, bạn có thể nhập dữ liệu như trước đây bằng công thức sau:
=IMPORTRANGE("spreadsheet_url", "Country_Indicators!1:5")Thay thế spreadsheet_url bằng URL của tệp chứa dữ liệu trên.
Single Search Key và Single Output (Khoảng kết quả một hàng duy nhất)
Để tìm kiếm quốc gia “Anh” trong hàng đầu tiên và trả về giá trị từ cột tương ứng trong hàng thứ năm, chúng ta có thể sử dụng công thức sau:
=XLOOKUP("UK", 1:1, 5:5)Chúng ta có thể thay thế phạm vi tìm kiếm và phạm vi kết quả bằng CHOOSEROWS như sau:
=XLOOKUP("UK", CHOOSEROWS(1:5, 1), CHOOSEROWS(1:5, 5))Hàm CHOOSEROWS truy xuất hàng đầu tiên (1:1) trong phạm vi tìm kiếm và hàng thứ năm (5:5) trong phạm vi kết quả.
Hãy sử dụng LET để đặt tên cho phạm vi 1:5 là “data” và thay thế 1:5 trong CHOOSEROWS bằng tên đó.
=LET(data, 1:5, XLOOKUP("UK", CHOOSEROWS(data, 1), CHOOSEROWS(data, 5))Bây giờ, chúng ta đã sẵn sàng để kết hợp IMPORTRANGE trong sự kết hợp này của LET, CHOOSEROWS và XLOOKUP. Để làm điều đó, hãy thay thế phạm vi 1:5 bằng công thức IMPORTRANGE chính nó.
=LET(data, IMPORTRANGE("spreadsheet_url", "Country_Indicators!1:5"), XLOOKUP("UK", CHOOSEROWS(data, 1), CHOOSEROWS(data, 5)))Chỉ định tiêu chí “UK” trong ô A1 và thay thế “UK” trong công thức bằng tham chiếu đến ô A1:
=LET(data, IMPORTRANGE("spreadsheet_url", "Country_Indicators!1:5"), XLOOKUP(A1, CHOOSEROWS(data, 1), CHOOSEROWS(data, 5)))Single Search Key và Multiple Row Output (Khoảng kết quả nhiều hàng)
Nếu bạn muốn tìm kiếm ngang tên quốc gia trong hàng đầu tiên và trả về Dân số, GDP, HDI và Diện tích (km vuông) từ hàng 2 đến 5, thực hiện các thay đổi sau trong công thức XLOOKUP với IMPORTRANGE trên:
Phạm vi kết quả trong công thức trên là CHOOSEROWS(data, 5). Bạn nên thay thế nó bằng CHOOSEROWS(data, {2, 3, 4, 5})
=LET(data, IMPORTRANGE("spreadsheet_url", "Country_Indicators!1:5"), XLOOKUP(A1, CHOOSEROWS(data, 1), CHOOSEROWS(data, {2, 3, 4, 5})))XLOOKUP với IMPORTRANGE: Tìm kiếm ngang sử dụng nhiều khóa tìm kiếm
Đây tương tự như tìm kiếm dọc, nơi chúng ta đã chỉ định nhiều tiêu chí trong một phạm vi cột, tức là trong A1:A2. Nhưng lần này, chúng ta phải chỉ định chúng trong phạm vi hàng, ví dụ trong A1:B1.
Nhập “UK” vào ô A1 và “Australia” vào ô B1. Nếu phạm vi kết quả chứa một hàng duy nhất, hãy sử dụng A1:B1 như là khóa tìm kiếm và nhập công thức như công thức mảng.
Ví dụ:
=ArrayFormula(LET(data, IMPORTRANGE("spreadsheet_url"), XLOOKUP(A1:B1, CHOOSEROWS(data, 1), CHOOSEROWS(data, 5))))Nếu phạm vi kết quả chứa nhiều hàng, thì bạn phải sử dụng hàm MAP như sau.
Chỉ định mảng A1:B1 trong MAP và đặt tên là “val”. Phần MAP này sẽ là:
=MAP(A1:B1, LAMBDA(val, ...))Hàm trong Lambda là công thức XLOOKUP với IMPORTRANGE ở trên. Bạn cần thay thế A1:B1 trong công thức đó bằng val, loại bỏ ArrayFormula và thay thế CHOOSEROWS(data, 5) bằng CHOOSEROWS(data, {2, 3, 4, 5}). Đây là công thức:
=MAP(A1:B1, LAMBDA(val, LET(data, IMPORTRANGE("spreadsheet_url", "Country_Indicators!1:5"), XLOOKUP(val, CHOOSEROWS(data, 1), CHOOSEROWS(data, {2, 3, 4, 5})))))
Tài nguyên
Chúng ta đã thấy các ví dụ về cách sử dụng XLOOKUP với IMPORTRANGE trong cả tập dữ liệu dọc và ngang. Đây là một số mẹo và thủ thuật XLOOKUP khác:
- XLOOKUP Dữ liệu hiển thị (đã lọc) trong Google Sheets
- XLOOKUP Kết quả thứ N trong Google Sheets
- Hàm XLOOKUP lồng nhau trong Google Sheets
- VLOOKUP và XLOOKUP: Sự khác biệt chính trong Google Sheets
- XLOOKUP với Nhiều Tiêu chí trong Google Sheets
- XLOOKUP cho Kết quả Nhiều Cột trong Google Sheets
- XLOOKUP với Ngày và Giờ trong Google Sheets
