Cách Sử Dụng Hàm Indirect Trong Excel, Công Thức Và Ví Dụ Minh Hoạ

Cập nhật thông tin chi tiết về Cách Sử Dụng Hàm Indirect Trong Excel, Công Thức Và Ví Dụ Minh Hoạ mới nhất ngày 26/11/2020 trên website Zdungk.com. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Cho đến thời điểm hiện tại, bài viết này đã đạt được 8,811 lượt xem.

Trong bài viết này, blog.hocexcel.online sẽ hướng dẫn về các bạn cách sử dụng hàm , cũng như các chức năng cơ bản và cung cấp một số công thức ví dụ nhằm minh họa cách sử dụng hàm INDIRECT trong Excel.

Hàm INDIRECT trong Excel – cú pháp và các ứng dụng cơ bản:

Hàm INDIRECT chỉ có hai đối số, đối số thứ nhất là bắt buộc trong khi đối số thứ hai thì không:

INDIRECT (ref_text, Sheet_name’!Range

Giả sử rằng tên sách nằm trong ô A2, tên bảng là B2, và địa chỉ ô trong C2, chúng ta lấy công thức sau:

= INDIRECT (“‘ “& $A$1 &” ‘! “& B1),” “)

Sử dụng hàm INDIRECT để cố định một tham chiếu ô:

Thông thường, Microsoft Excel thay đổi các tham chiếu ô khi bạn chèn mới hoặc xoá các hàng hoặc cột hiện có trong một trang tính. Để ngăn chặn điều này xảy ra, bạn có thể sử dụng chức năng Hàm INDIRECT để làm việc với các tham chiếu ô cần giữ nguyên trong mọi trường hợp.

Để minh họa sự khác biệt, vui lòng thực hiện các bước sau:

  1. Nhập bất kỳ giá trị nào vào ô bất kỳ, ví dụ, số 20 trong ô A1.
  2. Tham chiếu A1 từ hai ô khác bằng nhiều cách khác nhau: = A1 và = INDIRECT (“A1”)
  3. Chèn một hàng mới phía trên hàng 1.

Và hãy xem điều gì sẽ xảy ra? Giá trị của ô được gán giá trị ô khác, vẫn trả về 20, vì công thức của nó đã được tự động thay đổi thành = A2. Ô với công thức INDIRECT sẽ bây giờ trả về 0, vì công thức đã không thay đổi khi có một dòng mới được chèn vào và nó vẫn còn tham chiếu đến ô A1, hiện đang được để trống:

Sau khi nghe tôi giải thích, thì bạn có thể cảm thấy rằng hàm công thức INDIRECT có vẻ là gây phiền toái nhiều hơn là giúp đỡ. Được thôi, hãy thử một ví dụ khác:

Giả sử, bạn muốn lấy tổng các giá trị trong dải A2: A5, và bạn có thể dễ dàng thực hiện việc này bằng cách sử dụng hàm SUM:

= SUM (A2: A5)

Tuy nhiên, bạn muốn công thức vẫn giữ nguyên, cho dù có bao nhiêu hàng được xóa hoặc chèn vào. Giải pháp rõ ràng nhất – sử dụng tham chiếu cố định – sẽ không giúp ích gì. Để chắc chắn, hãy nhập công thức = SUM ($A$2:$A$5) vào một số ô, chèn một hàng mới, giả sử ở dòng 3, và … phát hiện công thức được chuyển thành = SUM ($A$2:$A$6) .

Tất nhiên, như vậy Microsoft Excel làm việc tốt trong nhiều trường hợp. Tuy nhiên, có thể bạn không muốn công thức tự động thay đổi. Giải pháp là sử dụng chức năng INDIRECT, như sau:

= SUM (INDIRECT (“A2: A5”))

Vì Excel nhận ra rằng “A1: A5” chỉ là một chuỗi văn bản chứ không phải là một dải tham chiếu, nó sẽ không thực hiện bất kỳ thay đổi nào khi bạn chèn hoặc xóa một hàng.

Sử dụng hàm INDIRECT với các hàm Excel khác

Ngoài SUM, INDIRECT thường được sử dụng với các chức năng Excel khác như ROW, COLUMN, ADDRESS, VLOOKUP, SUMIF, đó chỉ là một vài đại diện tiêu biểu.

Thường thì hàm ROW được sử dụng trong Excel để trả về một mảng các giá trị. Ví dụ, bạn có thể sử dụng công thức mảng sau (nhớ nhấn Ctrl + Shift + Enter) để trả lại trung bình của 3 số nhỏ nhất trong dải A1: A10:

= AVERAGE(SMALL (A1: A10, ROW (1: 3)))

Tuy nhiên, nếu bạn chèn một hàng mới trong bảng tính của bạn, bất cứ nơi nào giữa các hàng 1 và 3, dải trong công thức hàm ROW sẽ được thay đổi thành ROW (1:4) và công thức sẽ trả lại trung bình của 4 con số nhỏ nhất, thay vì 3 .

Để tránh điều này xảy ra, hãy lồng hàm INDIRECT trong hàm ROW và công thức trên của bạn sẽ luôn luôn chính xác, cho dù có bao nhiêu dòng được chèn vào hoặc bị xóa:

Như bạn đã biết, hàm ADDRESS được sử dụng trong Excel để lấy một địa chỉ ô theo số hàng và cột. Ví dụ, công thức = ADDRESS (1,3) trả về chuỗi $C$1 vì C1 là ô ở giao điểm của hàng 1 và cột thứ 3.

Để tạo một tham chiếu ô INDIRECT, bạn chỉ cần lồng hàm ADDRESS vào công thức INDIRECT như sau:

= INDIRECT (ADDRESS (1,3))

Sử dụng INDIRECT với danh sách tùy chọn trong Excel

Bạn có thể sử dụng hàm INDIRECT với danh sách tùy chọn để tạo các danh sách theo mục dạng xếp chồng lên nhau để hiển thị các lựa chọn khác nhau tùy thuộc vào giá trị mà người dùng đã chọn trong danh sách theo mục đầu tiên.

Một danh sách tùy chọn, thực sự rất dễ làm. Những gì nó cần là một vài dải ô được đặt tên, để lưu trữ thành các mục và một công thức đơn giản = INDIRECT (A2), trong đó A2 là ô hiển thị danh sách tùy chọn của bạn.

Để tạo các danh sách 3 cấp phức tạp hơn hoặc thả xuống với các mục nhập nhiều từ, thì bạn sẽ cần một công thức phức tạp hơn một chút với một hàm SUBSTITUTE lồng vào nhau.

Để có hướng dẫn từng bước chi tiết về cách sử dụng INDIRECT với danh sách tùy chọn trong Excel, vui lòng xem hướng dẫn này: Cách tạo danh sách các tùy chọn trong Excel.

Hàm INDIRECT, các lỗi và sự cố có thể xảy ra:

Như đã trình bày trong các ví dụ trên, hàm INDIRECT khá hữu ích khi dùng với tham chiếu ô và dãy. Tuy nhiên, không phải tất cả người sử dụng Excel nào cũng háo hức đón nhận nó, chủ yếu là vì việc sử dụng INDIRECT rộng rãi trong các công thức Excel sẽ dẫn đến thiếu minh bạch. Hàm INDIRECT khó đánh giá vì ô mà nó tham chiếu đến không phải là vị trí cuối cùng của giá trị được sử dụng trong công thức, điều này khá rắc rối, đặc biệt khi làm việc với các công thức dài và phức tạp.

INDIRECT #REF! lỗi

Thông thường, công thức INDIRECT sẽ trả về #REF! lỗi trong ba trường hợp:

  1. không phải là một tham chiếu ô hợp lệ: Vì nếu tham số ref_text trong công thức INDIRECT của bạn không phải là tham chiếu ô hợp lệ, thì công thức sẽ cho kết quả là Giá trị lỗi. Để tránh các vấn đề có thể xảy ra, vui lòng kiểm tra các đối số của hàm INDIRECT.
  2. Đã vượt quá giới hạn dải: Nếu đối số của công thức của bạn tham chiếu đến một loạt các ô vượt quá giới hạn của hàng là 1,048,576 hoặc giới hạn cột là 16,384, bạn cũng sẽ gặp lỗi #REF trong Excel 2007, 2010 và Excel 2013. Các phiên bản Excel sớm hơn thì đã bỏ qua quá mức giới hạn và trả lại một số giá trị, mặc dù, kết quả này thường không phải là một trong những bạn mong đợi.
  3. Một trang tham chiếu hoặc một bảng tính đã được đóng lại: Nếu công thức INDIRECT của bạn tham chiếu đến một bảng tính hoặc một tính Excel khác, thì bảng tính / trang tính khác phải được mở, nếu không thì INDIRECT trả về #REF! lỗi.

INDIRECT #NAME? lỗi

Đây là trường hợp rõ ràng nhất, ngụ ý rằng có một số lỗi trong tên của hàm công thức, và điều này sẽ dẫn chúng ta đến mục tiếp sau đây 🙂

Sử dụng chức năng INDIRECT trong các ngôn ngữ không phải tiếng Anh

Bạn có thể tò mò muốn biết rằng tên tiếng Anh của hàm INDIRECT đã được dịch sang 14 ngôn ngữ như thế nào, hãy nhìn danh sách bên dưới:

Một vấn đề thường gặp đối với những vùng khu vực không sử dụng tiếng anh thì tên của hàm này không còn là INDIRECT nữa, mà sẽ có các thiết lập khu vực (Regional Settings) khác nhau cho List Separator. Trong cấu hình Windows chuẩn cho Bắc Mỹ và một số quốc gia khác, List Separator mặc định là một dấu phẩy. Trong khi ở các nước Châu Âu, với dấu phẩy được giữ như biểu thức thập phân (Decimal Symbol) thì khi đó List Separator được đặt thành dấu chấm phẩy.

Do đó, khi sao chép một công thức giữa hai vị trí Excel khác nhau, bạn có thể nhận được thông báo lỗi “We found a problem with this data …” bởi vì List separator được sử dụng trong công thức sẽ khác với những gì được đặt trên máy của bạn. Nên nếu bạn gặp lỗi này khi sao chép một số công thức INDIRECT từ hướng dẫn này vào Excel của bạn, chỉ cần thay thế tất cả các dấu phẩy (,) bằng dấu chấm phẩy (;) để làm cho nó cố định.

Nguồn: Ablebits, dịch và biên tập bởi Blog học Excel Online

Bạn đang xem bài viết Cách Sử Dụng Hàm Indirect Trong Excel, Công Thức Và Ví Dụ Minh Hoạ trên website Zdungk.com. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!