VietNetwork.Vn

Bạn có thể đã quen với việc sử dụng tham chiếu ô trong Excel, nhưng bạn có biết rằng bạn có thể tiến xa hơn một bước và tạo tham chiếu ô của riêng mình không? Đây chính là lúc hàm INDIRECT trở nên hữu ích.


1. Hàm INDIRECT có tác dụng gì?

Hàm INDIRECT của Excel biến chuỗi văn bản thành tham chiếu. Hàm này có một số lợi ích thực tế:

• Nó cho phép bạn tạo tham chiếu động. Ví dụ, bạn có thể thay đổi tham chiếu đến một ô trong công thức mà không cần thay đổi chính công thức đó.
• Hàm INDIRECT là một cách tuyệt vời để tạo liên kết giữa các trang tính trong một bảng tính.
• Bạn có thể sử dụng thông tin đã có trong bảng tính để tạo tài liệu tham khảo.
• Tham chiếu được tạo bằng hàm INDIRECT sẽ giữ nguyên, ngay cả khi cấu trúc trang tính của bạn thay đổi.

2. Cú pháp INDIRECT

Theo mặc định, tham chiếu trong Excel sử dụng kiểu tham chiếu A1, nghĩa là nó tham chiếu đến cột trước, sau đó đến số hàng. Tuy nhiên, hàm INDIRECT cho phép bạn chuyển đổi chúng.

Thứ tự đảo ngược này được gọi là kiểu tham chiếu R1C1, trong đó "R" chỉ một hàng và "C" chỉ một cột. Vì lý do này, hàm INDIRECT chứa hai đối số. Sau đây là cú pháp:

=INDIRECT( x, y )
Trong đó x là văn bản tham chiếu và y (tùy chọn) là kiểu tham chiếu.

Cụ thể hơn, x (văn bản tham chiếu) có thể là tham chiếu theo kiểu A1, tham chiếu theo kiểu R1C1, tên được xác định trước hoặc tham chiếu ô. Nếu x tham chiếu đến một sổ làm việc khác, sổ làm việc thứ hai đó phải được mở. Cũng cần lưu ý rằng bạn không thể tham chiếu đến sổ làm việc khác nếu bạn đang sử dụng Excel cho web.

y (kiểu tham chiếu) là một đối số tùy chọn. Nếu bạn chọn không bao gồm đối số này, Excel sẽ tự động sử dụng tham chiếu kiểu A1 mặc định, mặc dù bạn cũng có thể nhập TRUE ở đây để buộc Excel sử dụng kiểu tham chiếu này. Nhập FALSE sẽ yêu cầu Excel sử dụng kiểu tham chiếu R1C1.

Trong bài viết này, tôi sẽ sử dụng tham chiếu theo kiểu A1 để chỉ cho bạn cách hàm INDIRECT hoạt động. Khi bạn đã nắm được các khái niệm của nó, bạn có thể thử nghiệm bằng cách sử dụng tham chiếu theo kiểu R1C1.

Kết quả của hàm INDIRECT là một tham chiếu.

3. Những điều cần lưu ý trước khi sử dụng hàm INDIRECT

Có một số điều bạn nên biết trước khi bắt đầu làm việc với INDIRECT trong bảng tính của mình:

• Đây là một hàm dễ bay hơi, nghĩa là nó liên tục cố gắng cập nhật. Sử dụng hàm này trong một bảng tính lớn có thể khiến nó chậm chạp hoặc thậm chí không hoạt động.
• Để sử dụng đầy đủ hàm INDIRECT, trước tiên bạn cần biết cách tạo tham chiếu có tên trong Excel.
• Toán tử dấu thăng (&) là một cách tuyệt vời để tạo chuỗi văn bản có thể dùng làm tham chiếu.

4. Cách sử dụng hàm INDIRECT

Để bạn có thể hiểu rõ cách sử dụng INDIRECT trong thực tế, chúng ta hãy xem cách thức hoạt động cơ bản nhất của nó.

4.1. Ví dụ cơ bản

Trong ví dụ này, hãy nhập

=INDIRECT(A1)
vào ô B2 sẽ biến ô A1 thành tham chiếu đến ô D1. Đây là lý do tại sao kết quả là 5.


Trong ví dụ này, hãy nhập

=SUM(INDIRECT(A2))
vào ô B2 trước tiên biến A2 thành tham chiếu ô, sau đó tính tổng các ô trong tham chiếu đó. Điều này là do hàm INDIRECT được nhúng trong hàm SUM.


Bây giờ, chúng ta hãy xem xét việc sử dụng hàm INDIRECT để tham chiếu đến một ô trong một trang tính khác. Ô A1 của Sheet2 chứa số 100 và chúng ta muốn tạo một tham chiếu INDIRECT đến ô đó trong trang tính hiện tại. Đầu tiên, chúng ta cần nhập Sheet2!A1 vào ô A3, sau đó chúng ta cần biến ô này thành một tham chiếu bằng cách sử dụng hàm INDIRECT bằng cách nhập

=INDIRECT(A3)
vào ô B3.


Tuy nhiên, chúng ta có thể làm cho việc này dễ dàng hơn nhiều bằng cách sử dụng tham chiếu có tên. Ví dụ, nếu chúng ta đổi tên ô A1 của Sheet2 thành TOTAL, chúng ta có thể sử dụng tham chiếu này trong tham chiếu INDIRECT của mình. Điều này đặc biệt hữu ích nếu bạn đang tạo tham chiếu đến một trang tính khác, vì nó giúp bạn tránh phải nhớ dấu chấm than trong cú pháp. Ngoài ra, nếu tên trang tính của bạn thay đổi, tham chiếu INDIRECT sẽ không thích ứng với thay đổi này, do đó, sử dụng tham chiếu có tên là giải pháp thay thế an toàn và lâu dài hơn.


4.2. Ví dụ thực tế 1

Bây giờ chúng ta đã hiểu cách thức hoạt động của INDIRECT, hãy cùng khám phá cách sử dụng nó để làm cho bảng tính của chúng ta hữu ích trong thế giới thực.

Như thường lệ trong Excel, có nhiều cách để đạt được cùng một kết quả. Ví dụ, bạn có thể đưa hàm TAKE vào để làm cho quy trình sau đây trở nên năng động hơn. Tuy nhiên, mục đích của chúng tôi ở đây là minh họa cách sử dụng hàm INDIRECT, để sau đó bạn có thể tiếp tục và sử dụng theo cách của riêng mình.

Ở đây, chúng tôi theo dõi số bàn thắng mà một đội bóng ghi được trong mỗi trận đấu và chúng tôi muốn tính số bàn thắng trung bình của ba trận trước đó.


Đầu tiên, chúng ta sẽ sử dụng hàm COUNT để tính toán số trận mà đội đã chơi cho đến nay.

=COUNT(B:B)
vào ô E1 sẽ đếm tổng số ô trong cột B có chứa số, do đó cho chúng ta biết có bao nhiêu trò chơi đã được chơi.

Nếu bạn muốn đếm cả văn bản và số, hãy sử dụng hàm COUNTA.


Bây giờ, chúng ta đã sẵn sàng sử dụng hàm INDIRECT để chuyển dữ liệu trong ô E1 thành tham chiếu. Tại thời điểm này, mục tiêu của chúng ta là tìm giá trị trung bình của các giá trị trong ô B6 đến B8. Sau đây là cách hàm INDIRECT sẽ giúp chúng ta thực hiện việc này. Bắt đầu bằng cách nhập

=AVERAGE(INDIRECT
trong ô E2, vì chúng ta đang cho Excel biết rằng mục đích cuối cùng của chúng ta là tìm giá trị trung bình và chúng ta cũng đang sử dụng hàm INDIRECT để cho Excel biết nơi cần tìm để tính giá trị trung bình này.

Ô đầu tiên chúng ta sẽ tham chiếu trong phép tính TRUNG BÌNH của mình là B8, vì vậy chúng ta sẽ thêm

=AVERAGE(INDIRECT ("B"&E1)
vào công thức của chúng tôi. Lưu ý cách chúng tôi sử dụng dấu ngoặc kép quanh tham chiếu cột, vì chúng tôi đang sử dụng văn bản đó để tạo tham chiếu ô.

Ô tiếp theo chúng ta sẽ tham chiếu trong phép tính TRUNG BÌNH của mình là B7, cách B8 một ô. Vì vậy, thêm

=AVERAGE(INDIRECT("B"&E1), INDIRECT("B"&E1-1)
với công thức của chúng tôi có nghĩa là Excel sẽ tạo một tham chiếu ô bắt đầu từ cột B, sau đó trừ 1 khỏi giá trị trong E1.

Cuối cùng, chúng ta muốn tham chiếu đến ô B6, cách ô B8 hai ô trở lên trong phép tính TRUNG BÌNH của chúng ta, rồi thêm dấu ngoặc đơn đóng:

=AVERAGE(INDIRECT("B"&E1),INDIRECT("B"&E1-1),INDIRECT("B"&E1-2))
Cách này tính toán chính xác giá trị trung bình của 3, 1 và 1, tức là tổng số bàn thắng được ghi trong ba trận đấu gần nhất.


Bây giờ, khi chúng ta thêm nhiều dữ liệu hơn vào cột B, giá trị trong ô E1 sẽ tăng lên, vì hàm COUNT nhận ra rằng có nhiều giá trị hơn trong cột B và các tham chiếu INDIRECT sẽ cập nhật tương ứng để luôn nắm bắt ba giá trị cuối cùng trong công thức AVERAGE của chúng ta. Tất cả điều này xảy ra mà không cần phải thay đổi công thức chúng ta đã nhập vào ô E2.


4.3. Ví dụ thực tế 2

Hãy xem thêm một ví dụ nữa. Lần này, chúng ta sẽ sử dụng tham chiếu được đặt tên để có thể sử dụng INDIRECT với hàm VLOOKUP của Excel.

Chúng tôi muốn Excel cho chúng tôi biết mỗi cá nhân trong số bốn cá nhân kiếm được bao nhiêu tiền trong tuần 1 hoặc tuần 2, với tùy chọn thay đổi số tuần đó nếu cần.


Để bắt đầu, chúng ta cần đặt tên các ô từ B1 đến C5 là "week_1" và các ô từ B7 đến B11 là "week_2". Sau đó, trong ô G2, chúng ta sẽ nhập

=VLOOKUP(E2,INDIRECT("week_"&F2),2,0)
vì chúng ta muốn Excel tra cứu giá trị của Tom trong phạm vi mà chúng ta đặt tên là "week_1" và trả về giá trị tương ứng trong cột thứ hai của phạm vi đó dưới dạng kết quả khớp chính xác.


Sau đó, chúng ta có thể sử dụng trình xử lý điền để hoàn thiện dữ liệu cho ba cá nhân còn lại, biết rằng các tham chiếu chúng ta tạo ra đều liên quan đến các hàng tương ứng của chúng và cũng an toàn do chúng ta sử dụng các tham chiếu được đặt tên.


Sau đó, bất kỳ thay đổi nào đối với giá trị trong cột C hoặc F sẽ tự động áp dụng cho công thức chúng ta vừa viết.

Để hoàn tất quá trình làm cho bảng tính của bạn trở nên năng động và gọn gàng, bạn có thể thêm menu thả xuống bằng công cụ Xác thực dữ liệu của Excel. Trong ví dụ trên, bạn có thể truy cập tên của từng cá nhân thông qua menu thả xuống, do đó bảng tra cứu chỉ chiếm một hàng duy nhất.