Microsoft Excel

Microsoft Excel (Bài 111): Hàm INDIRECT trong Excel

Posted on by HKT

Như tên hàm đã gợi ý, hàm Indirect được sử dụng để tham chiếu tới vùng dữ liệu một cách gián tiếp. Điều này có nghĩa là, trong trường hợp, bạn có 1 chuỗi “A1” chẳng hạn, thì hàm Indirect có thể sử dụng chuỗi này làm tham số, và trả về tham chiếu tới ô A1. Đây là cách hình dung đơn giản nhất về cách sử dụng hàm Indirect. Và sau đây, chúng ta sẽ đi tìm hiểu về cú pháp của hàm Indirect.

HÀM INDIRECT, CÚ PHÁP VÀ CÁC THAM SỐ

Hàm Indirect chỉ có hai tham số, tham số đầu tiên là bắt buộc, tham số thứ hai không bắt buộc:

INDIRECT(ref_text, [a1])

ref_text– là một tham chiếu tới một ô hoặc một vùng dữ liệu trên bảng tính Excel, hoặc một named range được lưu dưới dạng chuỗi.

[a1] – nhận giá trị TRUE (mặc định) hoặc FALSE để thiết lập cách tham chiếu, tham chiếu theo kiểu A1 (kiểu chúng ta vẫn thường dùng) khi bỏ qua hoặc để giá trị là TRUE, hoặc tham chiếu kiểu R1C1 khi chúng ta cho giá trị FALSE

Mặc dù tham chiếu kiểu R1C1 sẽ có ích trong một số trường hợp, nhưng trong hầu hết các trường hợp chúng ta sẽ sử dụng tham chiếu kiểu A1, trong bài này, các ví dụ sử dụng hàm Indirect sẽ sử dụng tham chiếu kiểu A1, vậy nên chúng ta có thể bỏ qua tham số thứ hai của hàm Indirect.

CÁCH SỬ DỤNG HÀM INDIRECT CƠ BẢN

Để có thể hiểu được cách sử dụng hàm Indirect cơ bản, chúng ta hãy đến với một công thức đơn giản trong Excel minh hoạ cách hàm Indirect hoạt động.

Giả sử chúng ta có số 3 trong ô A1, và chuỗi “A1” trong ô C1. Chúng ta có thể sử dụng công thức =INDIRECT(C1) trong ô D1 để xem điều gì sẽ xảy ra:

  • Đầu tiên, hàm Indirect sẽ chuyển tham chiếu C1 thành giá trị “A1”, do vậy chúng ta có công thức trong ô D1 là =INDIRECT("A1")
  • A1 là một tham chiếu hợp lệ trong Excel, vậy nên ô D1 có thể được hiểu là =A1 do hàm Indirect đã biến từ chuỗi “A1” thành tham chiếu A1.
  • Do tham chiếu A1 này, nên khi công thức được Excel tính toán, sẽ trả về kết quả là 3

Như vậy, qua ví dụ này, bạn có thể thấy rằng hàm Indirect chuyển từ tham chiếu hợp lệ ở dạng chuỗi sang tham chiếu bình thường của Excel.

Tới đây, nếu bạn nghĩ rằng, điều này chắc không có nhiều ý nghĩa với công việc của bạn, thì xin hãy nán lại thêm một chút nữa. Chúng ta sẽ cùng nhau đi tìm hiểu một vài ví dụ rất hữu ích và các bạn sẽ thấy được sức mạnh của hàm Indirect trong Excel.

HƯỚNG DẪN SỬ DỤNG HÀM INDIRECT – CÁC VÍ DỤ

Ở phần trước, chúng ta vừa xem một ví dụ cực kỳ đơn giản minh hoạ cho cách sử dụng hàm Indirect. Ở phần này, chúng ta hãy cùng nhau đi tìm hiểu một số ứng dụng thực tế của hàm Indirect trong công việc hàng ngày của chúng ta.

SỬ DỤNG HÀM INDIRECT ĐỂ TỔNG HỢP DỮ LIỆU TỪ NHIỀU BẢNG TÍNH

Trong một số công việc, bạn có thể lưu dữ liệu của nhiều sản phẩm vào nhiều sheets khác nhau trên Excel, mỗi sheet tương ứng với một sản phẩm. Mỗi sản phẩm lại có một bảng thuộc tính riêng của từng sản phẩm. Đây là một cách lưu trữ rất tốt, và khoa học. Nhu cầu bây giờ của bạn cần tổng hợp dữ liệu từ các sheet sản phẩm đó vào một bảng tổng hợp, làm sao để làm được điều này khi chúng ta có tới cả trăm sheets? Trong ví dụ sau đây, mình sẽ minh hoạ với trường hợp có 3 sheets

Giả sử các sheets đều được đặt tên một cách có quy luật là “Item” + số thứ tự: “Item 01”, “Item 02”, …, “Item 10”. Vậy chúng ta có thể sử dụng hàm Indirect như sau để điền vào bảng dữ liệu của chúng ta:

Trong ô C48, chúng ta sử dụng hàm Text và kỹ thuật nối chuỗi trong Excel để tạo ra tên sheet. Tên sheet này sẽ được sử dụng trong công thức Indirect ở ô D48 để lấy dữ liệu từ ô F23 của các Sheet như trên.

Một lưu ý ở đây đối với tên Sheet có dấu cách là chúng ta cần viết tên sheet này trong dấu nháy đơn ‘. Khi nối chuỗi để tạo ra tham chiếu sử dụng cho hàm Indirect thì chúng ta có công thức:

=INDIRECT("'" & C48 &"'!F23")

SỬ DỤNG HÀM INDIRECT ĐỂ TÍNH TỔNG N GIÁ TRỊ NHỎ NHẤT TRONG CỘT

Trong ví dụ này, bạn có thể sử dụng hàm Indirect để tính tổng 5 giá trị nhỏ nhất trong một cột:

Hàm Indirect được kết hợp và sử dụng trong ví dụ trên là:

=SUMPRODUCT(
  SMALL(
    B25:B34,
    ROW(INDIRECT("1:5"))
  )
)

Trong hàm trên:

  • Phần ROW(INDIRECT("1:5")) sẽ tạo ra mảng {1,2,3,4,5} – vì chúng ta đang muốn tính tổng của 5 giá trị nhỏ nhất, nên chúng ta tạo ra mảng từ 1 tới 5. Nếu bạn muốn tính tổng của 3 giá trị nhỏ nhất thôi, thì bằng cách sử dụng hàm Indirect này, bạn có thể tạo ra một mảng số từ 1 tới 3.
  • Hàm SMALL sẽ lấy ra 5 giá trị nhỏ nhất
  • Hàm SUMPRODUCT cuối cùng sẽ đi tính tổng của 5 giá trị nhỏ nhất này.

CÁCH SỬ DỤNG HÀM INDIRECT ĐỂ CHUYỂN CHỈ SỐ CỘT THÀNH SỐ THỨ TỰ CỘT

Có bao giờ bạn muốn biết một cột cụ thể ở trong Excel là cột thứ mấy? Đối với các cột chúng ta thường dùng hoặc trong bảng chữ cái thì chúng ta có thể dễ dàng biết được điều này, ví dụ cột A sẽ ở vị trí thứ nhất, cột H ở vị trí thứ 8, tuy nhiên, một ví dụ vui trong trường hợp này: mình tên là Nguyễn Đức Thanh, viết tắt là NDT, mình muốn biết cột NDT là cột có số thứ tự bao nhiêu trên bảng tính, thì mình có thể sử dụng công thức sau đây để có thể tìm ra được giá trị đó:

=COLUMN(INDIRECT(B43&"1"))

SỬ DỤNG HÀM INDIRECT ĐỂ TÁCH SỐ RA KHỎI CHỮ

Trong quá trình xử lý dữ liệu, chắc hẳn bạn cũng gặp phải trường hợp muốn tách riêng số ra khỏi một đoạn text. Thật may mắn, hàm Indirect có thể giúp bạn làm điều này với công thức sau đây

{=TEXTJOIN("",TRUE,IFERROR(MID(B60,ROW(INDIRECT("1:100")),1)+0,""))}

Trong trường hợp này, chúng ta sử dụng một công thức mảng, vậy nên các bạn lưu ý khi nhập công thức xong, chúng ta cần nhấn tổ hợp phím Ctrl + Shift + Enter để có thể nhập đúng công thức (Dấu ngoặc nhọn {} trong công thức trên thể hiện đây là một công thức mảng, khi nhập công thức vào Excel bạn không cần nhập phần ngoặc nhọn này)