Hàm INDIRECT nâng cao trong Excel

INDIRECT() là một hàm có sẵn trong Excel. Chức năng này giúp người dùng khi họ muốn tập hợp giá trị văn bản có thể được sử dụng làm tham chiếu ô hợp lệ. Văn bản được truyền bên trong hàm này được chuyển thành tham chiếu ô. Sau đó, nếu ô tham chiếu được chuyển đổi đó chứa bất kỳ giá trị nào, kết quả là giá trị đó sẽ trả lại cho người dùng

Ví dụ: =INDIRECT("B5") Nó sẽ tham chiếu đến ô B5

Mục đích chính của việc sử dụng chức năng này là tạo tham chiếu ô từ văn bản trong trang tính Excel. Hàm INDIRECT() tạo tham chiếu ô động. Là một giá trị kết quả, nó trả về tham chiếu được chỉ định bởi một chuỗi văn bản. Đó là một chức năng rất thú vị

Sử dụng hàm INDIRECT() trong Excel

Chức năng hàm INDIRECT() không bị giới hạn; . Một trong những ưu điểm lớn nhất của công thức này là - nó không thay đổi tham chiếu ngay cả khi một hàng hoặc cột mới được chèn/xóa

Ghi chú. Hàm INDIRECT() tạo tham chiếu ô động

Hàm INDIRECT() rất hữu ích để tham chiếu một ô bằng văn bản. Nó không thực hiện bất kỳ phép tính số học hoặc logic nào trên dữ liệu Excel. Bạn có thể sử dụng nó bên trong bất kỳ chức năng nào khác và đạt được kết quả. Do chức năng tham chiếu ô động, chúng ta có thể thay đổi tham chiếu ô trong công thức mà không cần thay đổi chính công thức đó

cú pháp

Đơn giản như định nghĩa của nó, cú pháp hàm INDIRECT() cũng rất đơn giản -

=INDIRECT(ref_text, [a1])

Đây,

  1. Ref_text là chuỗi văn bản tham chiếu và
  2. a1 là một giá trị logic, TRUE hoặc FALSE

Tham số thứ hai ([a1]) là tham số tùy chọn. Khi nó không được thông qua, hàm INDIRECT() coi nó là TRUE

  • Loại tham chiếu trong ref_text được chỉ định bởi a1
  • Nếu a1 là TRUE hoặc không được cung cấp trong hàm INDIRECT(), ref_text được hiểu là tham chiếu ô Kiểu A1
  • Nếu A1 là FALSE, ref_text sẽ được coi là tham chiếu ô kiểu R1C1

Giá trị trả về

Hàm INDIRECT() trả về tham chiếu của ô được truyền dưới dạng văn bản trong dấu ngoặc kép bên trong hàm INDIRECT()

Kiểu tham chiếu ô

Có hai loại tham chiếu ô, tôi. e. ,

  1. Tham chiếu ô kiểu A1
  2. R1C1 Tham chiếu ô kiểu

Cả hai chỉ là đối diện với nhau. Bạn nên biết về các tham chiếu ô kiểu này để hiểu hàm INDIRECT() vì các giá trị tham số ref_text phụ thuộc vào nó

1. Tham chiếu ô kiểu A1

Tham chiếu ô kiểu A1 là kiểu tham chiếu thông thường trong Excel. Đó là một tham chiếu ô kiểu thích hợp hơn để sử dụng. Trong tham chiếu ô kiểu A1, một cột được theo sau bởi một số hàng. Ví dụ, F3. Ở đây, F là số cột và 3 là số hàng

2. R1C1 Tham chiếu ô kiểu

Tham chiếu ô kiểu R1C1 đối diện với tham chiếu ô kiểu A1. Không giống như tham chiếu A1, một hàng được theo sau bởi một số cột

Ghi chú. Theo kiểu này, bạn phải cung cấp thông số thứ hai

Như tên ngụ ý của R1C1, R đề cập đến hàng và C đề cập đến cột. Ví dụ: R2C4 sẽ tham chiếu đến cột thứ tư của hàng thứ hai, tôi. e. , ô D2. Nếu không có số sau một chữ cái (e. g. , R3C), có nghĩa là chúng ta đang đề cập đến cùng một hàng và cùng một cột

Ví dụ về hàm INDIRECT()

Có một số ví dụ cấp độ cơ bản và nâng cao về hàm INDIRECT(). Đầu tiên, chúng tôi sẽ cho bạn thấy cả hai ví dụ cơ bản về tham chiếu ô kiểu A1 và B1C1 để giải thích cách chúng hoạt động trên trang tính Excel

ví dụ 1. Tham chiếu ô kiểu A1

1. Chúng tôi có dữ liệu sau được lưu trữ trong một trang tính Excel. Ở đây, chúng ta sẽ sử dụng hàm INDIRECT() để tạo tham chiếu ô theo kiểu tham chiếu ô A1

Hàm INDIRECT nâng cao trong Excel

2. Bây giờ, hãy viết một công thức INDIRECT() với một tham số văn bản sẽ tham chiếu đến một ô. Ví dụ,

=INDIRECT("C5")

Hàm INDIRECT nâng cao trong Excel

Nó sẽ tham chiếu đến ô C5 của bảng tính này

3. Nhấn phím Enter và nhận giá trị kết quả được lưu trữ trong ô C5

Hàm INDIRECT nâng cao trong Excel

Nó trả về giá trị 7 được lưu trữ trong ô C5 khi hàm INDIRECT() chuyển đổi văn bản C5 thành tham chiếu ô C5

Ghi chú. Nếu tham chiếu ô được chuyển đổi không chứa bất kỳ giá trị nào, hàm INDIRECT() trả về 0

4. Thấy rằng chúng ta đã chuyển chuỗi văn bản E5 bên trong hàm INDIRECT(), đây là tham chiếu của ô E5. E5 là một ô trống

Hàm INDIRECT nâng cao trong Excel

5. Lấy kết quả bằng cách nhấn phím Enter và thấy nó sẽ trả về 0 vì ô E5 không chứa giá trị nào. Nó là một ô trống

Hàm INDIRECT nâng cao trong Excel

ví dụ 2. B1C1 Tham chiếu ô kiểu

1. Chúng tôi có cùng dữ liệu như trên được lưu trữ trong bảng tính Excel. Ở đây, chúng ta sẽ sử dụng hàm INDIRECT() để tạo tham chiếu ô theo kiểu tham chiếu ô R1C1

Hàm INDIRECT nâng cao trong Excel

2. Viết công thức INDIRECT() sau với tham số văn bản trong R1C1 và FALSE làm giá trị tham số thứ hai

=INDIRECT("R5C4", FALSE)

Hàm INDIRECT nâng cao trong Excel

Nó chỉ ra hàng thứ năm và cột thứ tư. Do đó, nó sẽ tham chiếu đến ô D5 của trang tính này

3. Nhấn phím Enter để lấy giá trị kết quả được lưu trong ô D5 và xem giá trị được trả về từ ô đó

Hàm INDIRECT nâng cao trong Excel

Hàm INDIRECT() đã trả về 3843 vì tham chiếu ô là D5. Ô D5 là chứa tổng giá thẻ nhớ, i. e. , 3843

Ghi chú. Tương tự như tham chiếu ô kiểu A1, nếu một ô không chứa bất kỳ giá trị nào, hàm INDIRECT() cũng sẽ trả về 0 trong tham chiếu ô kiểu R1C1

4. Thấy rằng chúng ta đã chuyển chuỗi văn bản R3C5 cho hàm INDIRECT(). Nó là một tham chiếu của ô E3 không chứa bất kỳ giá trị nào

Hàm INDIRECT nâng cao trong Excel

5. Lấy kết quả bằng cách nhấn phím Enter và thấy nó sẽ trả về 0 vì ô E3 không chứa giá trị nào. Nó là một ô trống

Hàm INDIRECT nâng cao trong Excel

Đây là cách sử dụng cơ bản và đơn giản nhất của hàm INDIRECT() sử dụng cả tham chiếu ô kiểu A1 và kiểu R1C1

Hàm INDIRECT() với ROW()

Excel cho phép người dùng sử dụng hàm INDIRECT() bên trong các hàm khác và các hàm khác được lồng bên trong hàm INDIRECT(). Cả hai cùng nhau giúp đạt được kết quả đặc biệt và làm việc năng động

Hãy hiểu thông qua một ví dụ -

Bước 1. Chúng tôi sẽ tìm nạp dữ liệu ô bằng cách tạo tham chiếu ô động với các hàm INDIRECT() và ROW()

Sử dụng công thức theo cách này

=INDIRECT("B" & ROW())

Hàm INDIRECT nâng cao trong Excel

Đầu tiên, hàm ROW() sẽ tìm nạp số hàng nơi điều khiển của bạn nằm trong trang tính Excel mà sau này sẽ nối với chuỗi văn bản được truyền bên trong hàm INDIRECT() để chuyển đổi trong tham chiếu ô

Bước 2. Nhận kết quả động cho công thức này. Thấy rằng nó có giá trị 749, được lưu trữ trong ô B4

Hàm INDIRECT nâng cao trong Excel

Ở đây, hàm ROW() đã trả về hàng số 4 nối với cột B và trả về giá trị được lưu trong ô B4 bằng cách chuyển đổi nó thành tham chiếu ô

Sử dụng trước hàm INDIRECT()

Hàm INDIRECT() không chỉ được sử dụng để chuyển đổi tham chiếu ô đơn giản. Ngoài ra, nó cung cấp chức năng nâng cao bằng cách cho phép sử dụng nó bên trong chức năng khác

Chúng ta có thể sử dụng hàm này với các hàm Excel khác hoặc lồng bên trong nó. Ví dụ: để tính tổng các số bằng cách tạo tham chiếu ô động. Bây giờ, chúng tôi sẽ cho bạn thấy một điều khác biệt đó là cách hàm INDIRECT() được sử dụng bên trong một hàm khác để đạt được kết quả

ví dụ 1. Tổng của ô động

Bước 1. Chúng tôi có dữ liệu sau, dữ liệu này cũng được sử dụng trong các ví dụ trên. Chúng tôi sẽ tính tổng các ô bằng cách tạo tham chiếu ô động

Hàm INDIRECT nâng cao trong Excel

Bước 2. Viết công thức sau để lấy tổng động của tổng số lượng sản phẩm. Số lượng của từng sản phẩm được lưu trữ trong cột C

=SUM(INDIRECT("C2. C8"))

Hàm INDIRECT nâng cao trong Excel

Bước 3. Bây giờ, lấy tổng của các ô có tham chiếu được tạo bởi hàm INDIRECT(). Thấy rằng tổng số sản phẩm là 54 được tính theo công thức này

Hàm INDIRECT nâng cao trong Excel

Tương tự, chúng ta có thể sử dụng công thức này để tính trung bình cộng của dữ liệu cột bằng cách thay thế SUM() bằng AVERAGE()

ví dụ 1. TRUNG BÌNH của ô động

Bây giờ, chúng ta sẽ tính trung bình của tổng giá bằng cách tạo tham chiếu ô động bằng hàm INDIRECT(). Tổng giá của từng sản phẩm được lưu trữ trong cột D

Bước 1. Viết công thức sau để lấy giá trung bình của tổng giá sản phẩm

=AVERAGE(INDIRECT("D2. D8"))

Hàm INDIRECT nâng cao trong Excel

Bước 2. Bây giờ, lấy mức trung bình của tổng giá có tham chiếu ô được tạo bởi hàm INDIRECT(). Thấy rằng trung bình của tổng giá sản phẩm là 4096. 142857 được tính theo công thức này

Hàm INDIRECT nâng cao trong Excel

Xem cách hàm INDIRECT() hoạt động với các hàm khác. Nhưng bây giờ, bạn đang nghĩ hàm INDIRECT() này sẽ giúp gì cho người dùng. Hàm INDIRECT() khóa tham chiếu ô

Tìm ví dụ tiếp theo, "khóa tham chiếu ô bằng hàm INDIRECT()"

Khóa tham chiếu ô bằng hàm INDIRECT()

Nói chung, chúng tôi thêm hoặc xóa các hàng và cột trong trang tính Excel. Nó gây ra - các tham chiếu ô mà chúng tôi đã sử dụng để thao tác dữ liệu, tự động thay đổi. Hàm INDIRECT() giúp bạn ngăn chặn việc thay đổi tham chiếu ô

Ở đây, chúng tôi có tổng số lượng của tất cả các sản phẩm. Một là tính đơn giản bằng hàm SUM() và một là tính trung bình bằng hàm SUM() với hàm INDIRECT(), tôi. e. , (SUM(INDIRECT())

Hàm INDIRECT nâng cao trong Excel

Bây giờ, nếu chúng ta chèn một cột mới trước cột Giá mà chúng ta đã tính trung bình. Xem cách nó sẽ ảnh hưởng đến kết quả của cả hai công thức

Hàm INDIRECT nâng cao trong Excel

Tại đây, bạn có thể thấy rằng một cột trống mới (cột B) đã được thêm vào trang tính và các cột được dịch chuyển sang bên phải

Hàm INDIRECT nâng cao trong Excel

Kết quả là tham chiếu cột Số lượng được thay đổi từ cột C sang cột D, nhưng tham chiếu mà chúng tôi đã tạo cho cột C không thay đổi

Ảnh hưởng đến tổng tính toán

Bây giờ hãy xem cách nó ảnh hưởng đến tổng được tính bằng cả hai công thức

Hàm INDIRECT nâng cao trong Excel

Theo cách này, chúng ta khóa tham chiếu ô bằng hàm INDIRECT(). Tham chiếu ô không thay đổi bằng cách thêm hoặc xóa hàng/cột được đặt thông qua hàm INDIRECT()

Ở đây, bạn có thể thấy rõ rằng -

TỔNG(GIẢI TIẾP("C2. C8")) - Tham chiếu ô được tạo bởi hàm INDIRECT() để tính tổng vẫn như cũ. Do tham chiếu ô động nên vẫn đang tính tổng cho cột C hiện đang lưu chi tiết cột Giá thay vì Số lượng

TỔNG ("C2. C8") - Tham chiếu ô trong công thức SUM() đơn giản đã thay đổi cùng với việc dịch chuyển cột. Tham chiếu ô bị thay đổi từ cột C sang cột D, và bây giờ nó vẫn đang tính tổng cho cột D. Cột đang lưu Chi tiết cột số lượng

Hàm @indirect trong Excel là gì?

Sử dụng INDIRECT khi bạn muốn thay đổi tham chiếu đến một ô trong công thức mà không thay đổi chính công thức đó .

Hàm Excel mạnh nhất là gì?

XLOOKUP là vua của các chức năng nâng cao (Microsoft chưa đặt danh hiệu này cho bất kỳ chức năng nào, nhưng tôi chắc chắn rằng không có chức năng nào xứng đáng hơn chức năng này). Đây là một chức năng mới sẵn dùng trong Excel 2021 và Excel for Microsoft 365. Điều này có nghĩa là những người sử dụng các phiên bản Excel cũ hơn sẽ không thể sử dụng nó.