Thủ thuật

Hướng dẫn cách kết hợp hàm IMPORTRANGE và INDEX/MATCH trong Google Sheet đơn giản, hiệu quả nhất

Trương Nguyễn Anh Thư

26-04-2023, 10:39 am

Sử dụng hàm trong Google Sheet sẽ giúp bạn xử lý các truy vấn, tính toán một cách nhanh chóng hơn. Bài viết này sẽ hướng dẫn cho các bạn cách kết hợp hàm IMPORTRANGE và INDEX/MATCH trên Google Sheet. Hãy cùng với mình theo dõi ngay bài viết dưới đây để hiểu rõ hơn nhé!

Hướng cách sử dụng hàm IMPORTRANGE trong Google Sheet đơn giản

Hàm IMPORTRANGE là gì?

Hàm IMPORTRANGE là một hàm giúp tham chiếu dữ liệu nhập một dải ô từ một bảng tính được chỉ định.

Cách sử dụng hàm IMPORTRANGE

Công thức:

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

Trong đó:

+ Spreadsheet url: chính là đường link địa chỉ của một file bảng tính nhất định. Spreadsheet url sẽ luôn nằm trong dấu “ “.

+ Range string: Còn được gọi là “Chuỗi dải ô” là tên chính xác của bảng tính lấy dữ liệu (Ví dụ: Sheet 1, Sheet 2,... hoặc có thể đặt tên là Data 1, Data 2,...), theo sau đó là '!' và phạm vi của các ô bạn muốn lấy dữ liệu.

Ví dụ minh họa:

Đưa dữ liệu từ một nguồn khácđến trang tính hiện tại

Hướng dẫn cách sử dụng hàm INDEX/MATCH trong Google Sheet nhanh chóng

Cách sử dụng hàm INDEX

Hàm INDEX là một trong những hàm nâng cao giúp cho người dùng Google Sheet được thuận tiện hơn trong việc xử lý các số liệu. Hàm INDEX là hàm trả về mảng, và giúp lấy các giá trị tại một ô trong bảng.

- Cách sử dụng hàm INDEX

Công thức:

=INDEX(Array;Row_num;[Column_num])

Trong đó:

+ Array: chính là vùng ô hoặc một hàng số mảng nào đó bắt buộc.

+ Row_num: chính là chọn hàng trong mảng từ đó trả về một giá trị.

+ Column_num: chính là chọn cột trong mảng từ đó trả về một giá trị.

- Ví dụ minh họa: Khi lấy số tiền phòng có mã số là L1B.

=INDEX(A3:H12;5;6)

Lấy giá trị của ô tính bằng hàm INDEX

Cách sử dụng hàm MATCH

Hàm MATCH sẽ là hàm tìm một mục được chỉ định trong phạm vi của ô, sau đó sẽ trả về vị trí tương đối của mục đó trong phạm vi này.

Cách sử dụng hàm MATCH

Công thức:

=MATCH(giá trị tìm kiếm, mảng tìm kiếm, [kiểu khớp])

Trong đó:

+ Giá trị tìm kiếm: (Bắt buộc) là giá trị mà bạn muốn so khớp trong mảng tìm kiếm. Ví dụ, khi các bạn tra cứu số điện thoại của một ai đó trong sổ điện thoại, bạn sẽ dùng tên của người đó làm giá trị tra cứu nhưng chính số điện thoại mới là giá trị mà bạn muốn tìm.

Đối số trong giá trị tra cứu có thể là một giá trị (số, văn bản hoặc giá trị logic) hoặc một tham chiếu ô đến một số, văn bản hoặc có thể là giá trị logic.

+ Mảng tìm kiếm: (Bắt buộc) là phạm vi ô được tìm kiếm.

+ Kiểu khớp: (Tùy chọn) Số -1, 0 hoặc 1. Đối số kiểu khớp sẽ chỉ rõ cách Excel so khớp giá trị tìm kiếm với các giá trị trong mảng tìm kiếm. Giá trị mặc định cho đối số này chính là 1.

Ví dụ minh họa: Hãy lấy giá trị L1B đầu tiên.

=MATCH(D16;B3:B12)

Lấy vị trí giá trị bằng hàm MATCH

Hướng dẫn cách kết hợp hàm IMPORTRANGE và hàm INDEX/MATCH trong Google Sheet hiệu quả

Công thức

=INDEX(IMPORTRANGE("Spreadsheet url";Range string);MATCH(Giá trị sẽ tìm kiếm;IMPORTRANGE(Spreadsheet url;Mảng tìm kiếm)))

Trong đó:

+ Spreadsheet url: chính là đường link địa chỉ của một file bảng tính nhất định. Spreadsheet url sẽ luôn nằm trong dấu “ “.

+ Range string: Còn được gọi là “Chuỗi dải ô” là tên chính xác của bảng tính lấy dữ liệu (Ví dụ Sheet 1, Sheet 2 hoặc có thể được đặt tên là Data 1, Data 2,...), theo sau sẽ là '!' và phạm vi của các ô bạn muốn lấy dữ liệu.

+ Giá trị tìm kiếm: chính là giá trị mà bạn muốn so khớp trong mảng tìm kiếm.

+ Mảng tìm kiếm: là phạm vi ô được tìm kiếm.

Ví dụ minh họa

Hãy lấy ra doanh thu đầu tiên của loại phòng L1A bằng cách sử dụng sự kết hợp của ba hàm IMPORTRANGE, INDEX và MATCH.

Công thức:

Lấy giá trị doanh thu của dòng đầu tiên kết hợp ba hàm như hình trên

Trong đó:

+https://docs.google.com…2ReKEL9wDusb3wzgl/edit#gid=0:được hiểu là đường link địa chỉ của một file bảng tính nguồn.

+ Trang tính1'!H3:H12: là phạm vi của các ô muốn lấy dữ liệu.

+ B3: chính là giá trị gốc cần so sánh để lấy ở file nguồn.

+ Trang tính1'!B3:B12: là phạm vi dải ô file nguồn dùng để so sánh giá trị.

Các lỗi thường gặp khi bạn tiến hành kết hợp hàm IMPORTRANGE và hàm INDEX/MATCH

Lỗi #N/A

Lỗi #N/A nghĩa là trong công thức bạn đã sử dụng có chứa nội dung không có sẵn trong vùng dữ liệu, dẫn tới không thể tính toán được, hoàn thành công thức được. Lỗi này thường xảy ra khi bạn sử dụng các hàm dò tìm, tham chiếu. Đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng cần tra cứu nên sẽ báo lỗi #N/A.

Ví dụ mẫu

Lỗi #N/A không phải là lỗi sai hàm, sai công thức mà chính là không tìm thấy đối tượng cần tìm. Vì vậy trong nhiều trường hợp bạn chỉ cần ẩn lỗi này đi, biện luận trước những trường hợp các dữ liệu không tồn tại trước khi thực hiện các hàm dò tìm, tham chiếu.

Lỗi #REF

Lỗi #REF! sẽ hiển thị khi công thức tham chiếu đến ô không hợp lệ. Điều này sẽ thường xuyên xảy ra nhất khi các ô được công thức tham chiếu bị xóa hoặc bị dán đè.

Bạn cần phải điều chỉnh công thức để công thức sử dụng tham chiếu dải ô thay vì ô riêng lẻ.

Ví dụ mẫu

Lỗi #ERROR

Lỗi này có nghĩa là Google Sheet không thể hiểu công thức mà các bạn đã nhập vì nó không thể phân tích cú pháp công thức để thực thi.

Ví dụ mẫu

Lỗi #VALUE

Lỗi #VALUE! chính là cách để Excel thông báo: “Đã xảy ra lỗi với công thức bạn vừa nhập” hoặc “Đã xảy ra lỗi với ô mà bạn đang tham chiếu tới”. Lỗi này thật sự báo rất chung chung và khó có thể tìm thấy nguyên nhân chính xác cho lỗi.

Một số lưu ý khi bạn tiến hành kết hợp hàm IMPORTRANGE và hàm INDEX/MATCH

Khi bạn sử dụng hàm INDEX và SUMIF thì phải lưu ý các dấu như: dấu đóng ngoặc ), dấu nháy kép " ", dấu nháy đơn ' ' đối với việc bạn muốn truy vấn dữ liệu text. Nếu bạn không cẩn thận sẽ gây ra lỗi hàm.

Trong quá trình truy vấn dữ liệu bạn nên lưu ý khoảng cách, nếu bạn viết sát nhau thì hàm sẽ bị lỗi.

Khi truy vấn các dữ liệu có dạng là text bị bắt buộc bài phải có dấu nháy đơn ' ', còn dữ liệu là số thì không cần dấu nháy nữa.

Một số bài tập ví dụ cơ bản về kết hợp hàm IMPORTRANGE và hàm INDEX/MATCH

Đề bài 1: Hãy lấy ra dòng doanh thu đầu tiên của đặt phòng theo ngày cho trước.

Lấy ra doanh thu của ngày 03/06/2021 kèm với đơn đầu tiên, như sau:

- Công thức:

Lấy ra doanh thu của ngày 03/06/2021 kèm với đơn đầu tiên

- Trong đó:

+ https://docs.google.com...2ReKEL9wDusb3wzgI/edit#gid=0: chính là đường link địa chỉ của một file bảng tính nguồn.

+ Trang tính1'!H3:H12: là phạm vi của các ô muốn lấy dữ liệu.

+ B20: chính là giá trị gốc cần so sánh để lấy ở file nguồn.

+ Trang tính1'!C3:C12: là phạm vi dải ô file nguồn dùng để so sánh giá trị.

Đề bài 2: Hãy lấy ra giá trị giá tiền thuê phòng tháng của loại phòng cho trước.

- Công thức:

Lấy ra giá trị giá tiền thuê phòng tháng của loại phòng L1A như hình trên

- Trong đó:

+ https://docs.google.com...2ReKEL9wDusb3wzgI/edit#gid=0: chính là đường link địa chỉ của một file bảng tính nguồn.

+ Trang tính1!C17:C20: là phạm vi của các ô muốn lấy dữ liệu.

+ B2: chính là giá trị gốc cần so sánh để lấy ở file nguồn.

+ Trang tính1!B17:B20: là phạm vi dải ô file nguồn dùng để so sánh giá trị.

Những câu hỏi thường gặp khi tiến hành kết hợp hàm IMPORTRANGE và hàm INDEX/MATCH

Vì sao cần phải kết hợp hàm IMPORTRANGE và hàm INDEX, MATCH?

Trả lời: Trong một số trường hợp làm việc với bảng tính, bạn cần sử dụng một số giá trị nhất định nằm ở trong một bảng tính khác hoàn toàn với file hiện tại. Ngay lúc này, bạn cần sử dụng kết hợp giữa các hàm IMPORTRANGE để cho phép lấy giá trị từ trang tính nguồn, và kết hợp INDEX/MATCH để lấy đúng giá trị cần thiết.

Cảm ơn các bạn đã quan tâm theo dõi bài viết này. Hy vọng bài viết sẽ mang lại cho các bạn những thông tin hữu ích, giúp các bạn sử dụng phần mềm môt cách thông minh và tiện lợi nhất.