Cách sử dụng hàm INDIRECT trong Excel

Trong bài viết này, chúng ta sẽ tìm hiểu về cách sử dụng hàm INDIRECT trong Excel.

Hàm INDIRECT trong Excel dùng để làm gì

Tham chiếu ô và tham chiếu mảng là cách sử dụng phổ biến nhất đối với người dùng excel. Nó có nghĩa là chọn giá trị bằng cách sử dụng địa chỉ ô để chỉ cần thay đổi giá trị trong ô để có kết quả mới. Ví dụ: nếu bạn cần tìm tổng của 20 số đầu tiên trong cột A, bạn sẽ sử dụng =SUM(A1:A120) . Bây giờ nếu bạn cần cho 20 tiếp theo. Bạn phải chỉnh sửa toàn bộ công thức. Bây giờ bạn đã biết hàm INDIRECT sẽ giúp bạn hiểu cách sử dụng hiệu quả công thức excel.

Hàm INDIRECT trong Excel

Hàm INDIRECT là một hàm tham chiếu ô. Nó lấy giá trị trong ô làm địa chỉ và trả về giá trị trong ô địa chỉ.

Hàm INDIRECT Cú pháp:

=INDIRECT (ref_text, [a1])

vấn đề 1

Hãy hiểu chức năng này bằng cách sử dụng nó trong một ví dụ.

Ở đây Như bạn có thể thấy B1 có Công thức cho biết ô A1 và Văn bản trong A1 là D1. Vì vậy, hàm trả về giá trị trong ô D1 là 500.

Bạn có thể sử dụng nó dưới nhiều hình thức như được hiển thị ở đây

Ở đây & toán tử được sử dụng để nối văn bản để có địa chỉ đầy đủ của ô. Vì vậy, A1 có D và 1 được thêm vào D, nhận ô D1 ref.

Bạn có thể tham chiếu trực tiếp đến bất kỳ ô nào.

Ở đây D1 được cung cấp trực tiếp để nhận kết quả trả về từ địa chỉ D1.

SUM các giá trị trong phạm vi sử dụng hàm INDIRECT

Bạn có thể sử dụng nó với các hàm khác để lấy tổng của phạm vi được cung cấp bởi tham chiếu ô.

Ở đây Phạm vi được cung cấp cho các hàm Sum tham chiếu ô A2:A5. Hãy hiểu thêm về từ một vấn đề khác.

Làm thế nào để giải quyết vấn đề?

Đối với vấn đề này, chúng tôi sẽ xem xét cách chúng tôi sử dụng để chọn các phần tử từ cùng một trang tính, sau đó chúng tôi sẽ sử dụng công thức để trích xuất danh sách các giá trị từ các trang tính khác nhau. Hàm INDIRECT & CELL của Excel sẽ được sử dụng để tạo công thức để thực hiện như được giải thích bên dưới.

Công thức chung:

= INDIRECT ( “tên_bảng” & “!” & CELL (“địa chỉ”, ô_ref)

sheet_name : tên trang tính

cell_ref : tham chiếu ô không có tên trang tính.

Ví dụ :

Tất cả những điều này có thể gây nhầm lẫn để hiểu. Hãy lấy một ví dụ để hiểu cách sử dụng công thức để trích xuất một mảng giá trị từ các trang tính khác nhau. Ở đây chúng tôi có một bảng tính có các giá trị trong các trang tính khác nhau. Chúng ta cần trích xuất một danh sách các giá trị bằng công thức

Sử dụng công thức:

= INDIRECT ( $C$3 & “!” & CELL (“địa chỉ”,B1))

Giải trình:

1. Khi trích xuất một giá trị từ trang tính khác nhau. Excel chấp nhận cú pháp Sheet1!B1. (Ở đây Sheet1 là tên trang tính và B1 là tham chiếu ô).
2. Hàm CELL trả về tham chiếu ô và cung cấp tham chiếu cho hàm INDIRECT.
3. Toán tử & tham gia hoặc nối hai hoặc nhiều đối số.
4. Hàm INDIRECT trả về giá trị trong tham chiếu ô được cung cấp.

Ở đây B1 là một tham chiếu ô của cùng một trang tính nhưng giá trị được trích xuất từ ​​​​trang Dữ liệu.

Giá trị trong ô B1 của Bảng dữ liệu là 5800. Sao chép công thức để nhận danh sách các giá trị bắt đầu từ giá trị được trích xuất từ ​​kết quả đầu tiên.

Ở đây chúng ta có danh sách các giá trị bắt đầu từ ô B1 đến B7 của Data sheet.

Excel tăng tham chiếu ô từ trang tính khác bằng cách sử dụng tham chiếu ô.

Chúng tôi biết cách tăng tham chiếu ô từ cùng một trang tính. Hãy sử dụng một công thức để trích xuất giá trị từ cùng một trang tính. Công thức chung là =cell_ref .

Những gì công thức này làm là dán chọn giá trị từ địa chỉ là tham chiếu ô. Khi một tham chiếu ô được sử dụng trong một công thức. Nó có thể được sử dụng cho các tác vụ khác nhau và giá trị được cập nhật khi giá trị được cập nhật từ ô. Mở rộng công thức đến các ô bên dưới để nhận danh sách các giá trị.

Như bạn có thể thấy ở đây, chúng tôi có mảng cần thiết. Bây giờ chúng ta cần đưa các giá trị này vào trang tính chính. Excel chấp nhận cú pháp khi trích xuất các giá trị từ trang tính khác là =Reference!A1. (Ở đây Tham chiếu là tên trang tính và A1 là tham chiếu ô).

Sao chép công thức vào các ô còn lại bằng Ctrl + D hoặc kéo xuống từ dưới cùng bên phải của ô.

Như bạn có thể thấy, ở đây chúng ta có tất cả các giá trị cần thiết từ trang Tham khảo. Có thêm một phương pháp để lấy các giá trị từ các trang tính khác nhau.

Trích xuất danh sách các giá trị từ các trang tính khác nhau bằng cách sử dụng dải ô được đặt tên.

Chúng ta có thể sử dụng một phương thức để gọi trực tiếp toàn bộ mảng bằng cách sử dụng phạm vi đã đặt tên từ các trang tính khác nhau. Đối với điều này, chúng tôi sẽ chỉ chọn mảng hoặc bảng và đặt tên cho phần đã chọn trong hộp tên và gọi nó bằng tên bất cứ nơi nào được yêu cầu trong trang tính.

Ở đây đã chọn toàn bộ bảng và đặt tên là Data. Hộp tên được tìm thấy ở góc trên cùng bên trái của trang tính như được tô sáng trong trang tính trên.

Chúng tôi sẽ trích xuất bảng được đặt tên trong trang tính mới. Chuyển đến trang tính mới và chọn cùng một số ô và nhập công thức được nêu bên dưới.

Sử dụng công thức:

{ =DATA }

Lưu ý: sử dụng Ctrl + Shift + Enter thay vì Enter cho công thức làm việc cho mảng. Không sử dụng đặt dấu ngoặc nhọn thủ công.

Như chúng ta có thể thấy công thức hoạt động tốt. Bất kỳ phương pháp nào trong số này có thể được sử dụng bất cứ nơi nào cần thiết.

Dưới đây là tất cả các ghi chú quan sát liên quan đến việc sử dụng công thức.

Ghi chú:

1. Các công thức này chỉ trích xuất các giá trị chứ không phải định dạng. Bạn có thể áp dụng định dạng tương tự bằng cách sử dụng công cụ vẽ định dạng trên các trang tính.
2. Giá trị được cập nhật khi chúng tôi thay đổi giá trị trong tham chiếu ô.
3. Chúng ta có thể chỉnh sửa các giá trị bên trong công thức bằng cách sử dụng các toán tử khác nhau như toán tử toán học cho các số, toán tử “&” cho các giá trị văn bản.