Có rất nhiều tính năng trong Google Trang tính, hầu hết đều khá dễ hiểu. Tuy nhiên, hàm INDIRECT là một trong những hàm của Google Sheets mà thoạt đầu có thể không dễ “hiểu”.
Để hiểu toàn bộ tiềm năng của tính năng này, bạn cũng cần xem qua một số ví dụ trong thế giới thực (cũng được xem xét trong hướng dẫn này).
Hàm INDIRECT làm gì?
Nói một cách đơn giản, hàm INDIRECT trả về một tham chiếu đến phạm vi đã cho.
Hãy để tôi giải thích bằng một ví dụ:
Giả sử bạn có giá trị 5 trong ô A1 và bạn đặt nó sử dụng công thức = INDIRECT (“A1”) thì kết quả là bạn sẽ nhận được 5.
Bây giờ bạn có thể đang nghĩ tại sao bạn lại sử dụng công thức ĐÚNG CÁCH khi bạn chỉ có thể tham chiếu đến một ô. Vì vậy, thay vì sử dụng công thức, bạn chỉ có thể sử dụng = A1 trong ô A2 và nó sẽ cho bạn kết quả tương tự.
Bạn đúng rồi!
Trong trường hợp này, sử dụng công thức INDIRECT không có ý nghĩa.
Nhưng trong một số trường hợp, tham chiếu ô hoặc dải ô không có sẵn. Trong trường hợp như vậy, một hàm gián tiếp sẽ rất hữu ích.
Tôi chắc chắn rằng tính hữu ích của tính năng này sẽ trở nên rõ ràng hơn khi tôi chỉ cho bạn một số ví dụ trong thế giới thực (ở phần sau của hướng dẫn này).
Cú pháp của hàm INDIRECT
Cú pháp cho một hàm gián tiếp như sau:
INDIRECT (ref_text; [ref_style])
Hàm nhận hai tham số:
- ref_text: Đây là một chuỗi văn bản chứa tham chiếu đến một ô hoặc phạm vi ô.
- ref_style: Tham số này là tùy chọn. Nó được đặt thành TRUE / FALSE. Giá trị TRUE chỉ ra rằng ref_text được chỉ định trong ký hiệu “A1” và giá trị FALSE cho biết nó được chỉ định trong ký hiệu “R1C1”. Nếu giá trị này không được chỉ định, giá trị mặc định được giả định là TRUE.
Lưu ý rằng tham số ref_text cũng có thể chứa một tham chiếu đến một ô, từ đó chứa một chuỗi có tham chiếu đến một ô khác.
Nếu tất cả điều này nghe có vẻ khó hiểu, nó sẽ trở nên rõ ràng hơn với một vài ví dụ.
Hiểu về hàm INDIRECT
Trong tập dữ liệu sau, chúng ta thấy có 4 cách khác nhau để tham chiếu đến giá trị trong ô A2, trong đó một trong số chúng là không chính xác.
- Trong dòng đầu tiên, chúng tôi đã sử dụng phương pháp tham chiếu ô thông thường (sử dụng ký hiệu A1): “= A2”. Điều này trả về giá trị trong ô A2 là 25.
- Trong dòng thứ hai, chúng tôi đã sử dụng hàm INDIRECT để tham chiếu đến ô A2. Ở đây chúng tôi đã chỉ định chuỗi văn bản “A2” làm tham số ref_text: = INDIRECT (“A2”). Lưu ý rằng chúng tôi đã đặt tham chiếu ô trong dấu ngoặc kép (vì đó là một chuỗi văn bản). Điều này trả về giá trị trong ô A2 là 25.
- Trong dòng thứ ba, chúng tôi đã sử dụng hàm INDIRECT để tham chiếu đến ô A2. Ở đây chúng tôi đã chỉ định A2 là ref_text một lần nữa, nhưng không đặt nó trong dấu ngoặc kép: = INDIRECT (A2). Điều này có nghĩa là chúng tôi không truyền chuỗi “A2”. Vì vậy, điều này trả lại lỗi #REF.
- Trong dòng thứ tư, chúng tôi đã sử dụng hàm INDIRECT để tham chiếu đến ô A5. Ở đây chúng tôi đã chỉ định A5 làm tham số ref_text mà không có dấu ngoặc kép: = INDIRECT (A5). Điều này có nghĩa là chúng tôi không chuyển chuỗi “A5”, nhưng chúng tôi đang chuyển một tham chiếu đến ô A5. Ô A5 trong tập dữ liệu chứa chuỗi “A2” mà chúng tôi sử dụng làm tham chiếu cho hàm INDIRECT của chúng tôi. Vì vậy, điều này trả về giá trị trong ô A2 (không phải A5) là 25.
Hàm INDIRECT. Các ví dụ
Chúng tôi hy vọng bây giờ bạn đã hiểu cách hoạt động của hàm INDIRECT. Tuy nhiên, sự hiểu biết của bạn về điều này sẽ chỉ được củng cố sau khi bạn xem một vài ví dụ về thời điểm sử dụng tính năng này.
Vì vậy, đây là một vài trường hợp sử dụng mà hàm INDIRECT có thể khá hữu ích.
Sử dụng hàm INDIRECT để chặn tham chiếu ô
Hàm INDIRECT cung cấp một cách tuyệt vời để “khóa” các ô cụ thể hoặc một dải ô trong công thức. Để chứng minh điều này, hãy xem ví dụ sau:
Giả sử danh sách tên trên là danh sách vô địch và danh sách này được cập nhật liên tục và tên của nhà vô địch dẫn đầu luôn được liệt kê ở trên cùng. Điều này có nghĩa là tên trong ô A2 là nhà vô địch dẫn đầu hiện tại.
Hãy tạo một liên kết đến ô này cả khi có và không có hàm gián tiếp. Trong hình ảnh bên dưới, chúng tôi đã tạo một tham chiếu ô A2 bằng cách sử dụng tham chiếu ô bình thường “= A2” trong ô B9.
Tuy nhiên, trong ô B10, chúng tôi đã sử dụng hàm INDIRECT để tham chiếu đến ô A2: “= INDIRECT (” A2 “)”
Vì John Smith hiện đang chiếm ô A2 nên cả hai phương pháp đều hiển thị cùng một nhà vô địch hàng đầu, “John Smith”.
Bây giờ, giả sử tìm thấy một người chiến thắng mới có tên “Samuel Johnson” và một hàng mới được tạo trong ô A2 cho tên của anh ta. Lưu ý rằng điều này sẽ chuyển tên của John Smith sang hàng tiếp theo (A3).
Hãy xem điều gì sẽ xảy ra với giá trị của những nhà vô địch hàng đầu.
Sử dụng phương pháp liên kết ô truyền thống, chúng tôi vẫn có người chiến thắng ở John Smith. Điều này là do tham chiếu ô A2 hiện đã thay đổi thành A3 khi hàng mới được thêm vào.
Tuy nhiên, khi sử dụng hàm INDIRECT, giá trị của nhà vô địch được cập nhật thành Samuel Johnson vì chuỗi được tham chiếu trong hàm không thay đổi ngay cả sau khi thêm một chuỗi mới.
Như bạn có thể thấy, hàm INDIRECT giúp “chặn” các tham chiếu ô. Vì vậy, điều này rất hữu ích khi bạn muốn giá trị tham chiếu đến vị trí ô cố định thay vì dựa vào giá trị tại vị trí của ô.
Sử dụng hàm INDIRECT để tham chiếu đến một ô trong trang tính khác
Một tình huống khác mà hàm INDIRECT có ích là khi bạn cần lấy dữ liệu từ một số trang tính khác.
Hãy lấy một ví dụ khác.
Giả sử bạn có ba trang tính có tên “Đỏ”, “Cam” và “Xanh lam” (như hình bên dưới):
Trên trang thứ tư, chúng tôi muốn hiển thị giá trị trong ô B2 từ mỗi trong ba trang đầu tiên:
Thông thường, bạn sẽ phải nhập tên của mỗi trang tính, cùng với tham chiếu đến ô B2, như được hiển thị bên dưới: = SheetName! B2
Hãy tưởng tượng làm điều này khi bạn có 30 tờ!
Một cách dễ dàng hơn sẽ là:
- Trong ô B2 của một trang tính mới, hãy nhập công thức: = INDIRECT (A2 & “! B2”)
- Nhấn phím quay lại
- Bấm đúp vào ô điều khiển điền vào ô B2.
- Công thức được sao chép vào tất cả các ô trong cột B
Bây giờ bạn có giá trị của ô B2 từ mỗi trang tính được hiển thị trong cột B!
Điều này giúp đơn giản hóa công việc rất nhiều so với phương pháp thông thường.
Giải thích công thức
Khi chúng tôi nhấp vào ô điều khiển điền, tham chiếu ô trước dấu ‘&’ trong mỗi ô được cập nhật thành tên trang tính tiếp theo.
Cho nên,
- trong ô B2, hàm là = INDIRECT (A2 &! “! B2”), tham chiếu đến ô Red! B2
- trong ô B3, hàm là = INDIRECT (A3 &! “! B2”), tham chiếu đến ô Orange! B2
- trong ô B4, hàm là = INDIRECT (A4 &! “! B2”), tham chiếu đến ô Blue! B2
Như vậy, chỉ với một cú nhấp đúp chuột, chúng tôi đã cập nhật tất cả các ô của hàng B với dữ liệu cần thiết.
Ghi chú. Nếu bạn cần truy xuất dữ liệu từ các ô khác nhau trong các trang tính khác nhau, bạn có thể tạo một cột riêng chứa tham chiếu ô cho mỗi trang tính và sau đó sử dụng công thức INDIRECT như được hiển thị bên dưới:
Sử dụng Hàm INDIRECT để truy cập động một phạm vi được đặt tên
Đây là một công dụng tuyệt vời của hàm INDIRECT.
Giả sử bạn có ba cột cho ba màu như hình dưới đây.
Bạn muốn hiển thị tổng tổng cho một màu tùy thuộc vào tên cột được chọn từ danh sách thả xuống trong C6.
Điều này có nghĩa là số lượng hiển thị trong ô C11 trong tập dữ liệu trên sẽ khác nhau tùy thuộc vào màu được chọn.
Xin lưu ý rằng không thể biết trước màu nào sẽ được chọn. Vì vậy, để nhận được số tiền chính xác mỗi lần, bạn cần phải làm như sau:
- Tạo một phạm vi được đặt tên cho mỗi màu. Hãy bắt đầu với màu đỏ.
- Để tạo một dải ô đã đặt tên cho các giá trị màu đỏ, hãy chọn các ô trong phần Màu đỏ.
- Chọn Dữ liệu-> Dải ô đã Đặt tên.
- Thanh bên Dãy Tên mở ra ở phía bên phải của cửa sổ.
- Nhập “Màu đỏ” vào trường nhập phía trên phạm vi ô.
- Nhấp vào Xong
- Thao tác này sẽ tạo một phạm vi được đặt tên cho nhóm ô dưới “màu đỏ”.
- Theo cách tương tự, hãy tạo các phạm vi được đặt tên cho mỗi màu.
- Chọn ô C11
- Nhập công thức: = SUM (INDIRECT (C6))
- Bấm phím quay lại.
- Bây giờ hãy thử chọn một màu từ danh sách thả xuống. Bạn sẽ thấy rằng số tiền được cập nhật tương ứng trong ô C11.
Giải thích công thức
Hãy phân tích công thức chúng tôi đã sử dụng:
= SUM (INDIRECT (C6))
- Ở đây, hàm INDIRECT nhận làm tham số cho chuỗi nằm trong ô C6. Do đó, nếu văn bản “Red” được chọn, hàm INDIRECT sẽ trả về một tham chiếu đến phạm vi được đặt tên là “Red”.
- Hàm SUM nhận các giá trị số trong phạm vi được đặt tên đã chọn và trả về tổng.
Do đó, với sự trợ giúp của một công thức nhỏ, hoàn toàn đơn giản, chúng ta có thể tự động thu được lượng chúng ta cần. Nếu không, chúng ta sẽ phải dùng đến các công thức phức tạp liên quan đến một số hàm IF lồng nhau.
Có một số cách khác để sử dụng hàm INDIRECT. Trong hướng dẫn này, chúng tôi đã giải thích cú pháp của hàm INDIRECT cũng như một số ví dụ về công thức.
Để giúp bạn hiểu rõ hơn, chúng tôi đã cung cấp một số trường hợp sử dụng cơ bản có thể áp dụng tính năng này.
Chúng tôi khuyến khích bạn tìm nhiều cách sáng tạo hơn để sử dụng tính năng INDIRECT trong Google Trang tính và giúp công việc của bạn dễ dàng hơn.
Hy vọng bạn thấy hướng dẫn này hữu ích.
