Tham chiếu ô tra cứu Excel

Tìm hiểu tất cả về các hàm tra cứu & tham chiếu của Excel, chẳng hạn như VLOOKUP, HLOOKUP, MATCH, INDEX và CHOOSE

vlookup

Hàm VLOOKUP [Tra cứu theo chiều dọc] tìm kiếm một giá trị ở cột ngoài cùng bên trái của bảng, sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định

1. Chèn hàm VLOOKUP như hình bên dưới

Giải trình. hàm VLOOKUP tìm kiếm ID [104] ở cột ngoài cùng bên trái của phạm vi $E$4. $G$7 và trả về giá trị trong cùng một hàng từ cột thứ ba [đối số thứ ba được đặt thành 3]. Đối số thứ tư được đặt thành FALSE để trả về kết quả khớp chính xác hoặc lỗi #N/A nếu không tìm thấy

2. Kéo hàm VLOOKUP ở ô B2 xuống ô B11

Ghi chú. khi chúng ta kéo hàm VLOOKUP xuống, tham chiếu tuyệt đối [$E$4. $G$7] giữ nguyên, trong khi tham chiếu tương đối [A2] thay đổi thành A3, A4, A5, v.v. Truy cập trang của chúng tôi về chức năng VLOOKUP để biết thêm thông tin và nhiều ví dụ

tra cứu

Tương tự, bạn có thể sử dụng hàm HLOOKUP [Tra cứu theo chiều ngang]

Ghi chú. nếu bạn có Excel 365 hoặc Excel 2021, bạn cũng có thể sử dụng XLOOKUP để thực hiện tra cứu theo chiều ngang

Trận đấu

Hàm MATCH trả về vị trí của một giá trị trong một phạm vi nhất định

Giải trình. Màu vàng được tìm thấy ở vị trí 3 trong phạm vi E4. E7. Đối số thứ ba là tùy chọn. Đặt đối số này thành 0 để trả về vị trí của giá trị chính xác bằng lookup_value [A2] hoặc lỗi #N/A nếu không tìm thấy. Sử dụng INDEX và MATCH trong Excel và gây ấn tượng với sếp của bạn

Mục lục

Hàm INDEX bên dưới trả về một giá trị cụ thể trong phạm vi hai chiều

Giải trình. 92 được tìm thấy tại giao điểm của hàng 3 và cột 2 trong phạm vi E4. F7

Hàm INDEX bên dưới trả về một giá trị cụ thể trong phạm vi một chiều

Giải trình. 97 được tìm thấy ở vị trí 3 trong phạm vi E4. E7. Sử dụng INDEX và MATCH trong Excel và gây ấn tượng với sếp của bạn

Các hàm này giúp bạn làm việc với các mảng dữ liệu và đặc biệt hữu ích khi bạn cần tham chiếu chéo giữa các tập dữ liệu khác nhau

Các chức năng đã được nhóm thành các danh mục để bạn có thể dễ dàng tìm thấy chức năng mình cần. Mỗi liên kết sẽ đưa bạn đến một mô tả đầy đủ về chức năng, với các ví dụ về cách sử dụng và chi tiết về các lỗi phổ biến

Khi sử dụng công thức tra cứu trong Excel [chẳng hạn như VLOOKUP,  XLOOKUP hoặc INDEX/MATCH], mục đích là để tìm giá trị phù hợp và lấy giá trị đó [hoặc một giá trị tương ứng trong cùng một hàng/cột] làm kết quả

Nhưng trong một số trường hợp, thay vì nhận giá trị, bạn có thể muốn công thức trả về địa chỉ ô của giá trị

Điều này có thể đặc biệt hữu ích nếu bạn có một tập dữ liệu lớn và bạn muốn tìm ra vị trí chính xác của kết quả công thức tra cứu

Có một số hàm trong Excel được thiết kế để thực hiện chính xác điều này

Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách bạn có thể tìm và trả về địa chỉ ô thay vì giá trị trong Excel bằng các công thức đơn giản

Hướng dẫn này bao gồm

  • Tra cứu và trả về địa chỉ ô bằng hàm ADDRESS
  • Tra cứu và trả về địa chỉ ô bằng hàm CELL

Tra cứu và trả về địa chỉ ô bằng hàm ADDRESS

Hàm ADDRESS trong Excel có nghĩa là chính xác điều này

Nó lấy số hàng và số cột và cung cấp cho bạn địa chỉ ô của ô cụ thể đó

Dưới đây là cú pháp của hàm ADDRESS

=ADDRESS[row_num, column_num, [abs_num], [a1], [sheet_text]]

ở đâu

  • row_num. Số hàng của ô mà bạn muốn có địa chỉ ô
  • cột_num. Số cột của ô mà bạn muốn có địa chỉ
  • [abs_num]. Đối số tùy chọn nơi bạn có thể chỉ định xem muốn tham chiếu ô là tuyệt đối, tương đối hay hỗn hợp
  • [a1]. Đối số tùy chọn nơi bạn có thể chỉ định xem bạn muốn tham chiếu theo kiểu R1C1 hay kiểu A1
  • [tờ_văn bản]. Đối số tùy chọn nơi bạn có thể chỉ định xem bạn có muốn thêm tên trang tính cùng với địa chỉ ô hay không

Bây giờ, hãy lấy một ví dụ và xem nó hoạt động như thế nào

Giả sử có một tập dữ liệu như hình dưới đây, trong đó tôi có id Nhân viên, tên của họ và bộ phận của họ và tôi muốn biết nhanh địa chỉ ô chứa bộ phận cho id nhân viên KR256

Dưới đây là công thức sẽ làm điều này

=ADDRESS[MATCH["KR256",A1:A20,0],3]

Trong công thức trên, tôi đã sử dụng hàm MATCH để tìm ra số hàng chứa id nhân viên đã cho

Và vì bộ phận nằm trong cột C, tôi đã sử dụng 3 làm đối số thứ hai

Công thức này hoạt động rất tốt, nhưng nó có một nhược điểm – nó sẽ không hoạt động nếu bạn thêm hàng phía trên tập dữ liệu hoặc một cột ở bên trái tập dữ liệu

Điều này là do khi tôi chỉ định đối số thứ hai [số cột] là 3, nó được mã hóa cứng và sẽ không thay đổi

Trong trường hợp tôi thêm bất kỳ cột nào vào bên trái của tập dữ liệu, công thức sẽ tính 3 cột từ đầu bảng tính chứ không phải từ đầu tập dữ liệu

Vì vậy, nếu bạn có một tập dữ liệu cố định và cần một công thức đơn giản, điều này sẽ hoạt động tốt

Nhưng nếu bạn cần cái này dễ hiểu hơn, hãy sử dụng cái được đề cập trong phần tiếp theo

Tra cứu và trả về địa chỉ ô bằng hàm CELL

Mặc dù hàm ADDRESS được tạo riêng để cung cấp cho bạn tham chiếu ô của số hàng và cột đã chỉ định, nhưng có một hàm khác cũng thực hiện điều này

Nó được gọi là hàm CELL [và nó có thể cung cấp cho bạn nhiều thông tin về ô hơn hàm ADDRESS]

Dưới đây là cú pháp của hàm CELL

=CELL[info_type, [reference]]

ở đâu

  • info_type. thông tin về ô bạn muốn. Đây có thể là địa chỉ, số cột, tên tệp, v.v.
  • [thẩm quyền giải quyết]. Đối số tùy chọn nơi bạn có thể chỉ định tham chiếu ô mà bạn cần thông tin ô

Bây giờ, hãy xem một ví dụ nơi bạn có thể sử dụng chức năng này để tra cứu và lấy tham chiếu ô

Giả sử bạn có một tập dữ liệu như hình bên dưới và bạn muốn biết nhanh địa chỉ ô chứa bộ phận cho id nhân viên KR256

Dưới đây là công thức sẽ làm điều này

=CELL["address", INDEX[$A$1:$D$20,MATCH["KR256",$A$1:$A$20,0],3]]

Công thức trên khá đơn giản

Tôi đã sử dụng công thức INDEX làm đối số thứ hai để lấy bộ phận cho id nhân viên KR256

Và sau đó chỉ cần bọc nó trong hàm CELL và yêu cầu nó trả về địa chỉ ô của giá trị này mà tôi nhận được từ công thức INDEX

Bây giờ đây là bí mật về lý do tại sao nó hoạt động – công thức INDEX trả về giá trị tra cứu khi bạn cung cấp cho nó tất cả các đối số cần thiết. Nhưng đồng thời, nó cũng sẽ trả về tham chiếu ô của ô kết quả đó

Trong ví dụ của chúng tôi, công thức INDEX trả về "Sales" làm giá trị kết quả, nhưng đồng thời, bạn cũng có thể sử dụng nó để cung cấp cho bạn tham chiếu ô của giá trị đó thay vì chính giá trị đó

Thông thường, khi bạn nhập công thức INDEX vào một ô, nó sẽ trả về giá trị vì đó là điều mà nó phải làm. Nhưng trong các trường hợp bắt buộc phải có tham chiếu ô, công thức INDEX sẽ cung cấp cho bạn tham chiếu ô

Trong ví dụ này, đó chính xác là những gì nó làm

Và phần hay nhất khi sử dụng công thức này là nó không bị ràng buộc với ô đầu tiên trong trang tính. Điều này có nghĩa là bạn có thể chọn bất kỳ tập dữ liệu nào [có thể ở bất kỳ đâu trong trang tính], sử dụng công thức INDEX để tra cứu thường xuyên và nó vẫn cung cấp cho bạn địa chỉ chính xác

Và nếu bạn chèn một hàng hoặc cột bổ sung, công thức sẽ điều chỉnh tương ứng để cung cấp cho bạn địa chỉ ô chính xác

Vì vậy, đây là hai công thức đơn giản mà bạn có thể sử dụng để tra cứu và tìm và trả về địa chỉ ô thay vì giá trị trong Excel

Xlookup có thể trả về tham chiếu ô không?

XLOOKUP chỉ có thể trả về tham chiếu đến một ô, hàng hoặc cột đơn lẻ , không thể trả về toàn bộ bảng bao gồm nhiều hàng và cột. Nó cũng chỉ sẵn dùng cho người dùng Excel for Microsoft 365.

3 loại tham chiếu ô trong Excel là gì?

Bây giờ có ba loại tham chiếu ô mà bạn có thể sử dụng trong Excel. .
Tham chiếu ô tương đối
Tham chiếu ô tuyệt đối
Tham chiếu ô hỗn hợp

Tra cứu & tham chiếu trong Excel là gì?

Hàm TÌM KIẾM . Tra cứu các giá trị trong một vectơ hoặc mảng . Hàm MATCH . Tra cứu giá trị trong tham chiếu hoặc mảng .

Xlookup so với VLOOKUP là gì?

VLOOKUP mặc định là khớp gần nhất trong khi XLOOKUP mặc định là khớp chính xác . Để sửa lỗi đó trong VLOOKUP, bạn phải nhập FALSE làm đối số thứ tư.

Chủ Đề