.st0{fill:#FFFFFF;}

Hướng dẫn sử dụng hàm INDIRECT trong Excel, ứng dụng của hàm INDIRECT 

 Tháng Chín 19, 2021

By  Lê Đình Chi

Hàm INDIRECT là một hàm rất đặc biệt trong Excel. Trong bài viết này, zik.vn sẽ hướng dẫn về các bạn cách sử dụng hàm INDIRECT trong Excel. Chúng ta hãy cùng xem hàm này có tác dụng như thế nào nhé.

Đặc điểm của hàm INDIRECT trong Excel

Tác dụng của hàm INDIRECT

INDIRECT có nghĩa là “gián tiếp”. Hàm INDIRECT cho phép bạn tham chiếu tới 1 ô hoặc vùng ô trong Sheet khác mà không phải bấm chọn trực tiếp tới các vị trí đó, chỉ cần thông qua một đoạn text đại diện cho vùng cần thham chiếu. Hơn nữa, các tham chiếu trong hàm INDIRECT sẽ không thay đổi khi bạn chèn / xóa dòng, cột tại nơi bạn tham chiếu tới.

Cú pháp của hàm INDIRECT

Cú pháp của hàm như sau:

INDIRECT(ref_text, [a1])

Trong đó:

  • Ref_text – là đối tượng đại diện cho tham chiếu (có thể là một tọa độ ô, vùng ô, một chuỗi văn bản, hoặc một mảng mà đã được đặt tên
  • a1 – là một giá trị logic xác định loại tham chiếu của ref_text (không bắt buộc phải nhập)
    • Nếu nhập chữ TRUE hoặc không nhập (mặc định sẽ là TRUE), thì ref_text được hiểu dạng tham chiếu tọa độ ô thông thường
    • Nếu nhập chữ FALSE thì ref_text được coi như tham chiếu dạng R1C1.

Thông thường chúng ta ít khi sử dụng dạng tham chiếu R1C1 nên không cần nhập tham số thứ 2 trong hàm INDIRECT. Bạn chỉ cần ghi nhớ tác dụng của tham số này để sử dụng khi cần.

Một số ứng dụng của hàm INDIRECT

Dùng hàm INDIRECT tham chiếu tới 1 sheet khác

Ví dụ chúng ta có 1 bảng chấm công như sau:

ví dụ về bảng chấm công trong excel

Trong đó yêu cầu là tính tổng số công nhưng tính tại 1 sheet khác

bài tập sử dụng hàm indirect để chấm công

Thông thường chúng ta hay làm bằng cách viết tọa độ tham chiếu trực tiếp như sau:

  • Sử dụng hàm COUNITF để đếm số ký hiệu X trong vùng chấm công của nhân viên:
  • Với NV01 thì tham chiếu vùng B9:AF9 trong Sheet BCC

ta có công thức tại ô B6 của sheet VD1 được viết như sau

=COUNTIF(BCC!B9:AF9, “X”)

cách viết tham chiếu trực tiếp trong công thức Excel

Khi tham chiếu tới 1 sheet khác, tọa độ bạn nhận được thường có dạng:

Tên sheet + dấu chấm than + tọa độ điểm đầu + dấu hai chấm + tọa độ điểm cuối

Chú ý: Khi tên sheet có dấu cách hoặc tên tiếng việt có dấu, tên sheet sẽ có hai dấu nháy đơn ở trước và sau, dạng ‘bang cham cong’!B9:AF9

Nhưng khi tính cho NV04, bạn sẽ phải đổi tham chiếu thành vùng BCC!B12:AF12, với nhân viên NV06 thì vùng tham chiếu là BCC!B14:AF14. Điều đó nghĩa là bạn không thể sao chép công thức từ B6 xuống B7, B8 một cách bình thường được, mà phải chỉ định lại tham chiếu cho đúng vị trí thì kết quả mới chính xác.

Hãy hình dung nếu phải tính cho hàng nghìn người với thứ tự đảo lộn không giống như trong bảng chấm công thì bạn sẽ vất vả như thế nào trong cách làm này.

Khi đó bạn có thể dùng hàm INDIRECT để sử dụng phương pháp tham chiếu gián tiếp như sau:

Với NV01, bạn sẽ cần tham chiếu tới vùng BCC!B9:AF9. Trong đó tên sheet, từ cột, đến cột đều đã được xác định rõ ràng và giống nhau với các nhân viên khác. Chỉ có dòng là khác.

Vậy chỉ cần tìm được dòng trong bảng chấm công tương ứng với từng mã nhân viên là được phải không nào? Việc này lại rất đơn giản với hàm MATCH:

  • Tìm theo mã nhân viên (A6, A7, A8)
  • Tìm trong cột mã nhân viên trong bảng chấm công, vùng A9:A15
  • Phương pháp tìm kiếm là tìm chính xác theo tên

Vì bắt đầu tìm từ dòng thứ 9, do đó phải cộng thêm 8 để ra kết quả là Số dòng tại sheet BCC (không phải số thứ tự dòng trong phần bảng chấm công)

Ta có hàm MATCH tại ô G6 như sau (áp dụng tương tự cho G7, G8):

=MATCH(A6, BCC!$A$9:$A$15, 0)+8

Kết quả là:

hàm match xác định số dòng của từng nhân viên trong BCC

Đây chính xác là vị trí số dòng của từng mã nhân viên. Nhưng làm sao để ghép các ký tự BCC, B, AF, 9 thành tọa độ dạng BBC!B9:AF9?

Bạn có thể sử dụng dấu & để nối các ký tự này, chú ý thêm các dấu ! và : tại vị trí thích hợp. Ví dụ tọa độ cho NV01 được ghép như sau:

=D6&“!”&E6&G6&“:”&F6&G6

Kết quả này sẽ cho bạn 1 đoạn text là “BBC!B9:AF9” nhưng không có ý nghĩa rằng nó là 1 tham chiếu. Nếu sử dụng:

=COUNTIF(D6&“!”&E6&G6&“:”&F6&G6, “X”) thì bạn sẽ không thu được kết quả như ý muốn

công thức countif bị lỗi vì tham chiếu chưa đúng

Excel sẽ báo lỗi cho công thức này, bởi đoạn D6&“!”&E6&G6&“:”&F6&G6 không được coi là tham chiếu, nên viết như vậy là sai cú pháp của Excel.

Nhưng khi đưa đoạn tọa độ gián tiếp kia vào trong hàm INDIRECT, chúng ta có:

=COUNTIF(INDIRECT(D6&”!”&E6&G6&”:”&F6&G6), “X”)

Hãy xem kết quả:

kết quả chấm công sử dụng hàm countif kết hợp hàm indirect

Như vậy kết quả đã chính xác rồi. Bạn có thể thấy hàm INDIRECT đã chuyển đoạn text sang dạng tọa độ tham chiếu gián tiếp và hàm COUNTIF đã có thể hiểu đúng và tính đúng.

Dùng hàm INDIRECT tham chiếu tới 1 tên vùng (Name Range)

Không chỉ dùng với các đoạn text thể hiện tọa độ, hàm INDIRECT còn có thể sử dụng 1 đoạn text đại diện cho 1 Name Range mà đã được đặt tên từ trước. Ví dụ như sau:

bài tập dùng hàm indirect theo name range

Trong ví dụ này, chúng ta có:

  • Vùng G5:G8 là các nhân viên trong chi nhánh Hà Nội, vùng này được đặt tên là CN_HaNoi
  • Vùng G10:G12 là các nhân viên tỏng chi nhánh TP HCM, vùng này được đặt tên là CN_HCM

Tham khảo: Cách đặt tên vùng trong Excel

Làm thế nào để khi chọn chi nhánh trong ô B4 thì:

  • Khi chọn chi nhánh là Hà Nội, tại ô B6 sẽ có danh sách chọn là những nhân viên thuộc chi nhánh Hà Nội
  • Khi chọn chi nhánh là TP HCM, tại ô B6 sẽ có danh sách chọn là những nhân viên thuộc chi nhánh TP HCM

Hay nói cách khác là danh sách chọn tại ô B6 phụ thuộc theo giá trị được chọn tại ô B4?

Cách làm như sau:

Bước 1: Đầu tiên bạn tạo danh sách chọn tại ô B4 gồm 2 nội dung là Hà Nội và TP HCM bằng cách:

  • (1) Chọn ô B4
  • (2) Tại thẻ Data trên thanh công cụ, bạn chọn mục Data Validation (3)
  • Trong cửa sổ Data Validation chọn mục List (4), nhập vào Source gồm 2 nội dung (5) là Hà Nội, TP HCM như hình bên dưới (chú ý sau chữ Hà Nội sẽ có dấu phẩy)

Tham khảo: Cách tạo danh sách chọn trong Excel với chức năng Data Validation

các bước tạo danh sách chọn chi nhánh bằng data validation

Bước 2: Biện luận logic:  Khi chọn chi nhánh là Hà Nội, chúng ta sẽ lấy giá trị là CN_HaNoi, còn nếu chi nhánh là TP HCM thì sẽ lấy giá trị là CN_HCM (giống với tên vùng đã đặt)

Bạn có thể viết hàm IF xét logic này tại ô D4 như sau:

=IF(B4=”Hà Nội”, “CN_HaNoi”, “CN_HCM”)

hàm if xét logic chọn chi nhánh

Bây giờ chúng ta có giá trị tại ô D4 là đoạn text giống với tên vùng đã đặt, tuy nhiên đoạn text này không dùng trực tiếp trong việc tạo danh sách chọn tại ô B6 được. Nếu bạn tạo Data Validation > List tại ô B6 là:

thiết lập danh sách chọn thông thường

thì kết quả tại ô B6 bạn chỉ thu được chữ giống như ở ô D4

kết quả thiết lập tạo danh sách chọn thông thường

do đó để có thể tạo được danh sách chọn tại ô B4 là nhân viên theo chi nhánh, chúng ta cần sử dụng hàm INDIRECT như sau:

=INDIRECT($D$4)

thiết lập danh sách chọn bằng hàm indirect

bây giờ trong ô B6 đã có thể chọn theo danh sách nhân viên trong chi nhánh được rồi:

kết quả thiết lập danh sách chọn bằng hàm indirect

Khi thay đổi nội dung trong ô B4 là Hà Nội thì tại ô B6 chúng ta sẽ thấy kết quả danh sách chọn là những nhân viên tại chi nhánh Hà Nội:

kết quả thiết lập danh sách chọn bằng hàm indirect 2

Kết luận

Như vậy qua bài viết này chúng ta đã biết được cách dùng hàm INDIRECT trong Excel rồi. Một số kết luận có thể rút ra như sau:

  • Hàm INDIRECT dùng cho phương pháp tham chiếu gián tiếp: từ 1 đoạn text hoặc 1 tên đại diện cho 1 Name range đã có.
  • Hàm INDIRECT dùng độc lập (công thức chỉ có 1 hàm) khi muốn tạo ra 1 mảng dữ liệu để đưa vào các chức năng như Data Validation > List
  • Hàm INDIRECT dùng để kết hợp với các hàm tính toán khác như SUM, COUNT, SUMIF, COUNTIF… để tính toán trên các vùng tọa độ gián tiếp.
  • Hàm INDIRECT rất hữu ích khi tính toán trên nhiều Sheet, nhiều File cùng lúc hoặc các vùng dữ liệu không liên tiếp.

Hy vọng bài viết này sẽ hữu ích trong việc nâng cao khả năng viết công thức tính trong Excel của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của zik.vn. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.

Bạn có thể tham khảo thêm một số bài viết khác cùng chủ đề:

Kết hợp hàm INDIRECT với VLOOKUP để tạo tham chiếu động

Cách chuyển tên cột thành số trong Excel có sử dụng hàm INDIRECT

Tải về file mẫu trong bài viết

Bạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới:

Tài liệu kèm theo bài viết

Lê Đình Chi


Your Signature

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>