Tra cứu Google Trang tính giữa hai giá trị

Nếu bạn đang làm việc với khối lượng dữ liệu có liên quan với nhau trong bảng tính, việc tìm kiếm và tham chiếu thông tin trên các bảng thường là một thách thức. Đó là nơi VLOOKUP xuất hiện. VLOOKUP là một chức năng mạnh mẽ nhưng đôi khi bị bỏ qua trong Google Trang tính để truy xuất dữ liệu khớp giữa hai bảng riêng biệt.  

Khi được sử dụng đúng cách, VLOOKUP có thể giải mã các quy trình bảng tính phức tạp và tăng tốc độ phân tích dữ liệu. Trong hướng dẫn này, chúng tôi sẽ đề cập đến các vấn đề cơ bản của VLOOKUP, bao gồm cả cách sử dụng nó trong Google Trang tính

VLOOKUP trong Google Trang tính là gì?

VLOOKUP là một chức năng tìm kiếm tìm dữ liệu trong một bảng và sử dụng dữ liệu này trong một bảng khác. Các bảng có thể chiếm cùng một trang tính, một tab khác, một trang tính riêng hoặc tệp bảng tính. Hàm tìm kiếm một cột được chỉ định theo chiều dọc cho một khóa tìm kiếm (do đó là VLOOKUP), sau đó trả về giá trị từ cùng một hàng.  

Google Trang tính VLOOKUP. Cú pháp và chức năng

Hãy chia nhỏ những điều cơ bản của công thức VLOOKUP để hiểu rõ hơn về cách thức hoạt động của nó. Dưới đây là tổng quan về cú pháp tiêu chuẩn.   

VLOOKUP(khóa_tìm_kiếm, phạm vi, chỉ mục, [được_sắp_xếp])

• chỉ mục là cột chứa giá trị bạn đang tìm kiếm. Chẳng hạn, nếu bạn muốn tìm giá sản phẩm trong một bảng, đây sẽ là cột giá. Giá trị chỉ mục phải nằm trong khoảng từ 1 (cột đầu tiên) đến tổng số cột
• search_key là mục hoặc giá trị bạn muốn tìm. Ví dụ: nếu bạn đang tìm kiếm một sản phẩm trong bảng kho hàng của mình, khóa_tìm_kiếm có thể là số mặt hàng hoặc tên sản phẩm trong cùng một hàng.  
• phạm vi đề cập đến phạm vi bạn muốn đưa vào hàm VLOOKUP hoặc các cột dữ liệu
• is_sorted đề cập đến giá trị cuối cùng và là đúng hoặc sai (đúng theo mặc định). Nó được sử dụng để cho biết cột chỉ mục đã được sắp xếp chưa.   

VLOOKUP trong Google Trang tính. Những điểm chính

Mặc dù VLOOKUP là một chức năng mạnh mẽ nhưng không phải không có giới hạn. Hãy xem xét các yếu tố này trước khi sử dụng VLOOKUP để tránh các lỗi tiềm ẩn.   

• VLOOKUP không phân biệt chữ hoa chữ thường. Hàm không phân biệt chữ hoa và chữ thường.  
• VLOOKUP không tìm kiếm các cột ở bên trái của phạm vi đã chỉ định. Để tìm kiếm các cột ở bên trái, bạn phải sử dụng công thức Khớp chỉ mục.  
• Khi is_sorted được đặt thành TRUE, bạn phải sắp xếp cột đầu tiên của dải ô theo thứ tự tăng dần.  
• VLOOKUP có thể tìm kiếm bằng cách sử dụng khớp một phần dựa trên các ký tự đại diện. dấu hoa thị (*) và dấu chấm hỏi (?)
• Đặt is_sorted thành FALSE để trả về kết quả khớp chính xác. Chẳng hạn, nếu VLOOKUP của bạn từ một trang tính khác trả về kết quả không chính xác.   

Cách sử dụng VLOOKUP trong Google Trang tính

Video hướng dẫn. Cách sử dụng VLOOKUP trong Google Trang tính

Bây giờ bạn đã hiểu rõ hơn về chức năng này, hãy thực sự tìm hiểu chi tiết về cách sử dụng VLOOKUP. Dưới đây là một số cách bạn có thể sử dụng VLOOKUP trong Google Trang tính. Ngoài ra, hãy xem hướng dẫn bằng video của chúng tôi ở trên để biết hướng dẫn đầy đủ về cách sử dụng VLOOKUP.  

1. VLOOKUP trong cùng một Trang tính Google

Giả sử bạn có một Trang tính chứa ID sản phẩm, lược đồ định giá, giá cả và đăng ký cho các sản phẩm SaaS của bạn.  

Bạn muốn tạo một bảng khác trong cùng một Trang tính chỉ bao gồm ID sản phẩm và giá. Để thực hiện việc này, bạn có thể sử dụng VLOOKUP để trích xuất giá từ bảng tính chỉ bằng số ID sản phẩm. Đây là cách.  

Bước 1. Tạo một cột khác cho ID sản phẩm và Giá.  

Nhập số ID sản phẩm bạn muốn tìm, sau đó trong trường ngay bên cạnh nó trong cột Giá, nhập =VLOOKUP và nhập các tham số của công thức.   

Bước 2. Nhập từ khóa tìm kiếm

Nhập giá trị ID sản phẩm vào công thức hoặc nhấp vào ô (F2) để tự động thêm nó. Sau đó thêm dấu phẩy

Bước 3. Thêm phạm vi (tham số thứ hai) và chỉ định tập hợp các cột bạn muốn đưa vào tìm kiếm.  

Nhấp vào tiêu đề cột đầu tiên và kéo chuột đến cột cuối cùng của bảng hoặc nhập A. D, sau đó gõ dấu phẩy.    

Bước 4. Nhập cột chứa giá trị bạn đang tìm kiếm

Nhập số của cột chứa giá trị bạn đang tìm kiếm. Giá là cột thứ ba trong chuỗi, vì vậy hãy nhập '3'. Sau đó thêm dấu phẩy sau.  

Bước 5. Nhập 'Sai' cho is_sorted

Thêm Sai sau dấu phẩy cuối cùng và bao gồm dấu ngoặc đơn đóng.  

Công thức cuối cùng của bạn sẽ trông như thế này

=VLOOKUP(F2,A. D,3,SAI)

Bước 6. Thực thi công thức VLOOKUP

Nhấn Enter và bạn sẽ thấy kết quả xuất hiện trong ô có công thức VLOOKUP.  

Bước 7. Kéo xuống công thức VLOOKUP

Kéo ô xuống để sao chép công thức vào các ô còn lại trong cột.  

Bạn sẽ nhanh chóng nhận được dữ liệu mình cần mà không cần tra cứu thủ công từng giá trị tương ứng và nhập chúng vào bảng mới.  

2. VLOOKUP với ký tự đại diện

Nếu bạn chỉ biết một phần của khóa_tìm_kiếm, hãy thực hiện VLOOKUP để khớp một phần bằng cách sử dụng các ký tự đại diện này

• Dấu chấm hỏi (?) để khớp với các ký tự đơn
• Dấu hoa thị (*) để khớp với chuỗi ký tự

Giả sử bạn muốn truy xuất thông tin về một sản phẩm cụ thể từ cùng một bảng

Nếu bạn không biết toàn bộ ID sản phẩm, nhưng nhớ ký tự đầu tiên “A”, hãy sử dụng dấu hoa thị để điền vào phần còn thiếu trong công thức của bạn

=VLOOKUP(“A*”,A. D,1,SAI)

Nhấn Enter và xem đầu ra.  

Tạo công thức VLOOKUP linh hoạt hơn bằng cách nhập phần đã biết của khóa tìm kiếm vào một ô. Sau đó xâu chuỗi ô đó với '*'. Tiếp theo, đặt chu vi is_sorted thành TRUE.    

Chẳng hạn, bạn có thể kéo gói giá tương ứng bằng công thức này

=VLOOKUP($F$1&”*”,A. Đ,2,Đúng)

Để kéo giá thực tế, hãy sử dụng công thức này.  

=VLOOKUP($F$1&”*”,A. Đ,3,Đúng)

3. VLOOKUP phân biệt chữ hoa chữ thường

Như đã nói, VLOOKUP không phân biệt chữ thường và chữ hoa

Nếu bạn cần thực hiện tra cứu phân biệt chữ hoa chữ thường, hãy sử dụng INDEX MATCH với các hàm EXACT và TRUE trong công thức mảng VLOOKUP trên Google Trang tính của bạn.  

ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))

Sử dụng bảng mẫu bên dưới, giả sử rằng khóa tìm kiếm nằm trong ô A3, phạm vi tìm kiếm là G3. G8 và phạm vi trả lại trong F3. F8.  

Công thức sẽ trông như thế này

=ArrayFormula(INDEX($F$3. $F$8, TRÙNG (TRUE,EXACT($G$3). $G$8, A3),0)))

Công thức có thể phân biệt giữa các ký tự chữ thường và chữ hoa (a-1001 và A-1001).  

4. VLOOKUP trên một Trang tính khác trong cùng một Tệp Bảng tính

Giả sử bạn có bảng 'Nhân viên' trong một Trang tính và bảng 'Bán hàng' trong một Trang tính khác.  

Dưới đây là cách truy xuất Tỷ lệ hàng giờ tương ứng với ID nhân viên E010 và E040 từ Trang tính nhân viên và hiển thị chúng trong các ô B3 và B4 của trang tính Bán hàng của bạn.  

Bước 1. Chọn ô mục tiêu

Nhấp vào ô đầu tiên của cột mục tiêu của bạn (ô B3 trong Bảng bán hàng).   

Bước 2. Xác định khóa tìm kiếm

Nhập cái này

=VLOOKUP(A3,

A3 là ô chứa giá trị muốn tra cứu.  

Bước 3. Đặt phạm vi tìm kiếm thành các cột cụ thể trên Trang tính khác

Nhập phạm vi tìm kiếm bằng cách nhấp và mở tab Nhân viên.  

Chọn phạm vi ô mà bạn muốn hàm VLOOKUP tìm kiếm (dải ô A3. C8).  

Khóa phạm vi ô bằng cách nhấn F4 (hoặc Ctrl + F4) trên bàn phím của bạn. Điều này đảm bảo các tham chiếu này sẽ không thay đổi khi công thức được sao chép sang các hàng khác trong cột.  

Công thức VLOOKUP bây giờ sẽ hiển thị dưới dạng

=VLOOKUP(A3,Nhân viên. $A$3. $C$8

Bước 4. Nhập chỉ số VLOOKUP

Thêm dấu phẩy sau số 8, theo sau là chỉ mục của cột chứa các giá trị bạn muốn truy xuất.  

Nhập số '3' vì chúng tôi đang truy xuất Tỷ lệ hàng giờ (cột thứ ba trong A3. phạm vi C8). Thêm dấu ngoặc đơn đóng.  

Nhấn Enter và bạn sẽ thấy Tỷ lệ hàng giờ tương ứng với ID nhân viên E010 trong ô B3 trong Bảng bán hàng.  

Bước 5. Kéo công thức xuống để điền vào các ô còn lại trong cột.  

5. VLOOKUP trên một Trang tính khác trong Tệp Bảng tính Riêng biệt

Thực hiện VLOOKUP trong một Trang tính khác trên một tệp bảng tính riêng sử dụng chức năng tương tự như ví dụ trên

Tuy nhiên, có một tham số thứ hai hơi khác — tham số này bao gồm hàm IMPORTRANGE.  

Hàm IMPORTRANGE thường được sử dụng để nhập các giá trị từ các ô trong một bảng tính riêng biệt vào trang tính hiện tại của bạn.  

Nó bao gồm hai thông số

• Spreadsheet_key là URL bảng tính mà bạn muốn nhập từ đó và nó phải được chỉ định trong dấu ngoặc kép (“ “).  
• range_string là tham chiếu đến phạm vi ô bạn muốn nhập. Nó phải chứa tên Trang tính, bao gồm các ô bạn muốn. Ví dụ: nếu bạn đang nhập các ô A3. C8 từ trang tính Nhân viên trong ví dụ trước, chuỗi_phạm vi của bạn phải là “Nhân viên. A3. C8”

Giả sử bạn có

• Bảng 'Nhân viên' trong Trang tính có tên 'Nhân viên' trong tệp bảng tính có tên 'Ss1'
• Bảng 'Bán hàng' trong Trang tính có tên 'Bán hàng' trong tệp bảng tính riêng có tên 'Ss2'

Trong ví dụ này, chúng tôi sẽ truy xuất Tỷ lệ hàng giờ tương ứng với ID nhân viên E010 và E014 từ Bảng nhân viên trong Ss1 và hiển thị chúng trong các ô B3 và B4 trên Bảng bán hàng trong Ss2.  

Đây là cách thực hiện.  

Bước 1. Nhấp vào B3 trên bảng Doanh số trong Ss2.  

Nhập =VLOOKUP(, và chọn ô A3 theo sau là dấu phẩy

Bước 2. Triển khai IMPORTRANGE trong công thức

Nhập hàm IMPORTRANGE làm tham số thứ hai, sau đó là dấu ngoặc đơn mở.  

Bước 3. Mở Ss1 và chọn Bảng nhân viên

Sao chép URL của Google Worksheet từ thanh địa chỉ của trình duyệt của bạn.    

Bước 4. Chèn URL vào QUAN TRỌNG

Quay lại bảng tính Ss2 và dán URL vào hàm IMPORTRANGE. Đảm bảo đặt URL trong dấu ngoặc kép.   

Thêm dấu phẩy sau dấu ngoặc kép theo sau tên của Trang tính nguồn - 'Nhân viên' trong trường hợp này.   

Bước 5. Phạm vi tra cứu đầu vào

Thêm dấu chấm than (. ) và nhập phạm vi ô ($A$3. $C$8) bạn muốn tra cứu từ Sheet nguồn.  

Đặt toàn bộ tham số (tên trang nguồn, dấu chấm than và phạm vi ô) trong dấu ngoặc kép theo sau là dấu phẩy và dấu ngoặc đơn đóng (để đóng hàm IMPORTRANGE).  

Công thức bây giờ là.  

=VLOOKUP(A3,IMPORTRANGE(“https. // tài liệu. Google. com/spreadsheets/d/1Od_qTN0ze4nvh4cfoDjYCHxPJmdjAqTEABrvLom0KM4/edit#gid=870777933″,”Nhân viên. $A$3. $C$8”)

Bước 6. Thêm chỉ số cột

Thêm dấu phẩy và chỉ mục cột chứa các giá trị bạn đang truy xuất — Mức lương theo giờ trong cột thứ ba của A3. phạm vi C8

Nhập số '3' theo sau là dấu ngoặc đơn đóng, sau đó nhấn Enter. Bây giờ công thức là

=VLOOKUP(A3,IMPORTRANGE(“https. // tài liệu. Google. com/spreadsheets/d/1Od_qTN0ze4nvh4cfoDjYCHxPJmdjAqTEABrvLom0KM4/edit#gid=870777933″,”Nhân viên. $A$3. $C$8”),3)

Bước 7. Giải #REF. lỗi

Nếu bạn thấy #REF. Lỗi, bấm vào nút Cho phép truy cập để kết nối các Trang tính.  

Bây giờ, bạn sẽ thấy mức lương theo giờ tương ứng với ID nhân viên E010 trong ô B3 của bảng Doanh số của bạn.  

Kéo xuống công thức để điền vào phần còn lại của các ô dưới cột.  

VLOOKUP có thể là một chức năng mạnh mẽ khi bạn tìm ra cách sử dụng nó để tham khảo thông tin trên bảng tính Google của mình. Các thay đổi dữ liệu trong Trang tính gốc được cập nhật tự động trong các ô được kết nối của bạn, giúp bạn dễ dàng tham chiếu dữ liệu trên nhiều bảng tính Google một cách nhanh chóng.  

6. Công thức so khớp chỉ mục cho VLOOKUP hướng trái

Hàm VLOOKUP không tìm kiếm được sang trái. Điều này có nghĩa là nếu cột tìm kiếm không phải là cột đầu tiên trong bảng tra cứu, Google Trang tính VLOOKUP của bạn sẽ không hoạt động.  

Tuy nhiên, bạn có thể giải quyết vấn đề này bằng cách sử dụng công thức Kết hợp chỉ mục mạnh hơn và bền hơn

INDEX (phạm vi trả về, MATCH(khóa_tìm kiếm, phạm vi tra cứu, 0))

Chẳng hạn, bạn có thể sử dụng công thức này.  

=INDEX($F$3. $F$8, MATCH (A3, $G$3. $G$8, 0))

Công thức giúp bạn tra cứu giá trị ô A3 (search_key) trong G3. G8 (lookup_range) và trả lại kết quả khớp từ F3. F8 (phạm vi quay lại).  

Không giống như VLOOKUP, công thức Khớp chỉ mục không bị ảnh hưởng bởi các thay đổi về cấu trúc trong Trang tính vì nó tham chiếu trực tiếp đến cột trả về.   

Chẳng hạn, việc xóa hoặc chèn một cột trong bảng tra cứu của bạn sẽ phá vỡ công thức VLOOKUP của bạn do số chỉ mục được mã hóa cứng trở nên không hợp lệ. Đây không phải là trường hợp với các công thức Đối sánh chỉ mục.   

VLOOKUP loại bỏ các tìm kiếm dữ liệu nheo mắt

VLOOKUP loại bỏ rắc rối khi tìm và tham chiếu các ô theo cách thủ công trong Google Trang tính. Đó là lý do tại sao người dùng Google Trang tính thuộc mọi thành phần, từ nhà khoa học đến quản trị viên tiền lương, coi VLOOKUP là không thể thiếu. Mặc dù VLOOKUP có nhiều lần lặp lại nhưng kết quả luôn giống nhau. bạn tiết kiệm thời gian và năng lượng bằng cách tránh làm việc càu nhàu. Giờ đây, bạn có thể tập trung vào các nhiệm vụ thực sự quan trọng.  

Bạn có thể VLOOKUP dựa trên hai giá trị không?

Có chức năng giữa trong Google Trang tính không?

Tương đương với Xlookup trong Google Trang tính là gì?

Xlookup có hoạt động trong Google Trang tính không?