Văn bản đến địa chỉ ô excel

Hàm ADDRESS trả về địa chỉ cho một ô dựa trên số hàng và cột đã cho. Ví dụ: =ADDRESS(1,1) trả về $A$1. ADDRESS có thể trả về tham chiếu tương đối, hỗn hợp hoặc tuyệt đối và có thể được sử dụng để tạo tham chiếu ô bên trong công thức. Điều quan trọng là phải hiểu rằng ADDRESS trả về một tham chiếu dưới dạng giá trị văn bản. Nếu bạn muốn sử dụng văn bản này bên trong một tham chiếu công thức, bạn sẽ cần ép buộc văn bản thành một tham chiếu thích hợp bằng hàm INDIRECT. Nếu bạn muốn cung cấp số hàng và số cột và lấy lại giá trị tại địa chỉ đó, hãy sử dụng hàm INDEX

Hàm ADDRESS nhận năm đối số. hàng, cột, abs_num, a1 và sheet_text. Hàng và cột là bắt buộc, các đối số khác là tùy chọn. Đối số abs_num kiểm soát xem địa chỉ được trả về là tương đối, hỗn hợp hay tuyệt đối, với giá trị mặc định là 1 cho địa chỉ tuyệt đối. Đối số a1 là một Boolean chuyển đổi giữa tham chiếu kiểu A1 và R1C1 với giá trị mặc định là TRUE cho tham chiếu kiểu A1. Cuối cùng, đối số sheet_text có nghĩa là giữ tên trang tính sẽ được thêm vào trước địa chỉ

tùy chọn ABS

Bảng bên dưới hiển thị các tùy chọn có sẵn cho đối số abs_num để trả về địa chỉ tương đối, hỗn hợp hoặc tuyệt đối.  

Hàm trang tính INDIRECT là một công cụ hữu ích để tạo các tham chiếu ô hoặc phạm vi "một cách nhanh chóng", khi một công thức được đánh giá, thay vì "mã hóa cứng" chúng vào công thức. Tài liệu Trợ giúp trực tuyến của Microsoft về chức năng này chỉ gợi ý về tính hữu dụng.   

Theo cách sử dụng đơn giản nhất, hàm INDIRECT cho phép bạn đặt địa chỉ của một ô vào một ô khác và lấy dữ liệu từ ô đầu tiên bằng cách tham chiếu ô thứ hai. Ví dụ: nếu ô A1 có giá trị "C3", thì =INDIRECT(A1) sẽ trả về giá trị trong C3.    

Sức mạnh thực sự của hàm INDIRECT là nó có thể biến bất kỳ chuỗi nào thành tham chiếu. Điều này bao gồm bất kỳ chuỗi nào bạn tạo bằng cách sử dụng các hằng chuỗi và giá trị của các ô khác trong công thức, được xâu chuỗi cùng với toán tử nối &. Ví dụ, công thức đơn giản

=SUM(A5. A10)

sẽ tính tổng các giá trị trong phạm vi A5. A10. Tuy nhiên, giả sử bạn muốn có thể chỉ định phạm vi hàng nào sẽ tính tổng "nhanh chóng" mà không phải thay đổi công thức. Hàm INDIRECT cho phép bạn làm điều này. Giả sử bạn đặt ô hàng bắt đầu là B1 và ​​hàng kết thúc ở ô C1. Sau đó, bạn có thể sử dụng công thức

=SUM(INDIRECT("A"&B1&". A"&C1))

Đối số của hàm INDIRECT là

"A"&B1&". MỘT"&C1

Nếu B1 chứa 5 và C1 chứa 10, giá trị này sẽ ước tính chuỗi  "A5. A10". Hàm INDIRECT chuyển đổi chuỗi này thành một tham chiếu phạm vi thực tế, tham chiếu này được chuyển đến hàm SUM

Để các công thức VBA trả về tên trang tính tương đối (e. g. , tên của trang tính tiếp theo hoặc trước đó), hãy xem trang Tham chiếu Trang tính Từ Công thức.  

Một tính năng hữu ích khác của hàm INDIRECT là vì nó nhận đối số chuỗi nên bạn có thể sử dụng nó để làm việc với các tham chiếu ô mà bạn không muốn Excel tự động thay đổi khi bạn chèn hoặc xóa hàng. Thông thường, Excel sẽ thay đổi tham chiếu ô khi bạn chèn hoặc xóa hàng hoặc cột, ngay cả khi bạn sử dụng tham chiếu tuyệt đối. Nếu bạn có công thức  =SUM($A$1. $A$10), rồi chèn một hàng ở hàng 5, Excel sẽ chuyển đổi công thức thành =SUM($A$1. $A$11). Nếu bạn không muốn điều này xảy ra, hãy sử dụng hàm INDIRECT để thay đổi chuỗi văn bản thành tham chiếu.  

=SUM(INDIRECT("A1. A10"))

Vì Excel thấy "A1. A10" dưới dạng chuỗi văn bản chứ không phải tham chiếu phạm vi, nó sẽ không thay đổi khi hàng hoặc cột bị xóa hoặc chèn.  

Tính năng này rất quan trọng khi làm việc với một số công thức mảng. Thông thường, một công thức mảng sẽ sử dụng hàm ROW() để trả về một mảng số. Ví dụ: công thức sau sẽ trả về giá trị trung bình cộng của 10 số lớn nhất trong phạm vi A1. A60

=AVERAGE(LỚN(A1. A60, HÀNG(1. 10)))

Tuy nhiên, nếu bạn chèn một hàng giữa hàng 1 và 10, Excel sẽ thay đổi công thức thành

=AVERAGE(LỚN(A1. A60, HÀNG(1. 11)))

sẽ trả về giá trị trung bình của 11 số lớn nhất. Nếu chúng ta sử dụng  hàm với một chuỗi, thì Excel sẽ không thay đổi tham chiếu, do đó, công thức sẽ vẫn đúng, bất kể các hàng được chèn hay xóa hay không và ở đâu

Hướng dẫn này giải thích cách sử dụng Hàm gián tiếp Excel để chuyển đổi văn bản thành Tham chiếu và cách sử dụng trong Xác thực dữ liệu

Hàm gián tiếp Excel

Hàm gián tiếp Excel là chuyển đổi văn bản thành Tham chiếu (hoặc giá trị ô). Ví dụ: công thức =A1 trả về giá trị A1 vì A1 được coi là Tham chiếu, nhưng =”A1″ được coi là Văn bản. Hàm gián tiếp Excel có thể chuyển đổi Văn bản “A1” thành Tham chiếu

Một cách sử dụng phổ biến của Hàm gián tiếp Excel là sử dụng trong Xác thực dữ liệu nơi bạn chuyển đổi Phạm vi được đặt tên thành Tham chiếu (xem ví dụ bên dưới)

Một cách sử dụng khác ít phổ biến hơn là khi bạn kết hợp một địa chỉ Phạm vi, chẳng hạn như “A1” từ “A”&”1”, thì bạn muốn chuyển đổi “A1” thành Tham chiếu để sử dụng trong công thức khác

Nhấp để xem ví dụ mà tôi đã trả lời trong Cộng đồng Microsoft

Cú pháp hàm gián tiếp Excel

INDIRECT( ref_text, [A1] )

ref_textTham chiếu ô trong văn bản, chẳng hạn như “A1”[A1]Tùy chọn, kiểu tham chiếuValueDes mô tảTRUE (mặc định)Tham chiếu tương đối
Ví dụ. A1 FALSE Tham chiếu kiểu R1C1 Ví dụ. R1C1

 

Ví dụ về hàm gián tiếp Excel

Ví dụ, A1 chứa 1

FormulaValueExplanation=INDIRECT(“A1”)1Chuyển đổi văn bản “A1” thành Tham chiếu, trả về giá trị trong A1=INDIRECT(“$A$1”)1Chuyển đổi văn bản “$A$1” thành Tham chiếu, trả về giá trị trong A1=INDIRECT(“R1C1”,

Chuyển đổi một phạm vi thành tham chiếu

Giả sử bạn muốn xác thực dữ liệu liệt kê các giá trị trong A1. A3

Đầu tiên, đặt tên cho phạm vi A1. A3 dưới dạng data_list

Chọn Ô B1, nhấp vào Dữ liệu > Xác thực dữ liệu > điền dữ liệu như bên dưới bằng Hàm gián tiếp Excel để chuyển đổi văn bản “data_list” thành Tham chiếu