Chia sẻ bài viết tới bạn bè
Hàm INDIRECT trong excel là một hàm tương đối phức tạp đối với những bạn mới. Nhưng ứng dụng của hàm Indirect là vô cùng mạnh mẽ. Trong bài viết hôm nay, ADO sẽ hướng dẫn bạn cách dùng hàm INDIRECT dễ hiểu nhất thông qua nhiều ví dụ ứng dụng cụ thể.
Contents
Công thức hàm INDIRECT
=INDIRECT(ref_text, [a1])
Trong đó:
- Ref_text: Dễ hiểu nhất thì đối số này là một chuỗi ký tự có dạng địa chỉ ô trong excel hoặc là tham chiếu tới 1 ô mà trong đó chứa giá trị là chuỗi ký tự đó hoặc là tên được đặt cho một vùng dữ liệu. Nếu văn bản tham chiếu không phải là một tham chiếu ô hợp lệ, hàm INDIRECT trả về giá trị lỗi #REF! .
- A1: Một giá trị lô-gic chỉ rõ kiểu tham chiếu địa chỉ ô trong excel nào được chứa trong văn bản tham chiếu ô.
- Nếu a1 là TRUE hoặc được bỏ qua, thì văn bản tham chiếu được hiểu là tham chiếu kiểu A1.
- Nếu a1 là FALSE, thì văn bản tham chiếu được hiểu là tham chiếu kiểu R1C1.
Vậy thì kiểu A1 là gì gì, mà R1C1 là gì?
Đây là 2 cách lấy địa chỉ ô. A1 là cách thông thường mà chúng ta vẫn hay dùng với địa chỉ ô trong excel. Có nghĩa là giao của cột A và dòng 1 (Tên cột trước, tên dòng sau).
Còn kiểu R1C1 nghĩa là cách lấy địa chỉ ô theo số hàng, số cột. R1 nghĩa là Row1 và C1 nghĩa là Column1. Vậy R1C1 là gia của Row1 và Column1.
Có một lưu ý rất quan trọng để hiểu được bản chất của hàm là: Đối số Ref_text là là giá trị dạng ký tự. Các bạn phải nhớ điều này để trong các ví dụ chúng ta sẽ dễ nắm bắt được cách sử dụng.
Cách sử dụng hàm INDIRECT
Hàm Indirect thuộc nhóm hàm tìm kiếm tham chiếu nên ứng dụng chủ yếu của hàm là phục vụ mục đích tìm kiếm dữ liệu hoặc tham chiếu dữ liệu để tính toán khi kết hợp với các hàm tính toán thống kê.
Ví dụ hàm INDIRECT đơn giản
Tham chiếu luôn bằng chuỗi ký tự
Trong 2 công thức ở phàn này chúng ta thấy đối là các chuỗi ký tự được đặt trong cặp ngoặc kép. Nhưng bạn chú ý, ký tự trong chuỗi là F2 hoặc R2C6 đó là dạng địa chỉ ô theo 2 kiểu mà chúng ta đã được biết ở phần giải thích công thức.
Cả hai kiểu đều trả về kết quả giống nhau là giá trị cuối cùng tại ô F2.
Tham chiếu vào 1 ô excel chứa chuỗi ký tự
Trường hợp thứ hai là trường hợp thường được ứng dụng trong thực tế. Ở trong công thức INDIRECT này đối số không phải chuỗi ký tự mà là một tham chiếu tới ô D1. Và trong ô D1 lại là một chuỗi ký tự để hàm Indirect sử dụng và tìm kiếm dữ liệu.
Ở đây chuỗi ký tự đó là F2 (dạng A1), kết quả cuối cùng trả về là giá trị trong địa chỉ F2 – 1000.
Bản chất ở đây khi chúng ta nhập tham số D1 là khi hàm nhận thấy D1 nhập vào không phải chuỗi ký tự (chuỗi ký tự phải đặt trong dấu ngoặc kép), thì nó sẽ lấy giá trị ở trong ô D1. Nếu giá trị trong ô D1 là chuỗi ký tự đúng theo định dạng kiểu A1 hoặc R1C1 thì nó sẽ dựa vào đó để trả về kết quả.
Còn nếu chuỗi ký tự không thuộc 2 kiểu trên thì hàm sẽ trả về kết quả lỗi.
Hàm INDIRECT trong data validation
Hàm Indirect được dùng rất nhiều khi kết hợp với Data Validation để tạo danh sách phụ thuộc trong excel.
Trong ứng dụng này, chúng ta cùng quan sát 4 nhóm thương hiệu smartphone bên dưới.
Với mỗi một thương trong 1 bảng thì có các sản phẩm cụ thể. Việc đầu tiên là chúng ta sẽ đặt tên cho từng nhóm và tên bảng đặt sẽ trùng với tên của các thương hiệu của bảng Smartphone trên cùng.
- Nhóm 1: A8:A12 – đặt tên là “Samsung”
- Nhóm 2: D8:D12 – đặt tên là “Iphone”
- Nhóm 3: A15:A19 – đặt tên là “Oppo”
- Nhóm 4: D15:D19 – đặt tên là “Xiaomi”
Để thao tác đăt tên nhanh, bạn chỉ cần bôi đen vùng chọn và di chuyển nên ô Namebox để gõ luôn tên cho vùng muốn đặt. Tiếp theo chúng ta sẽ tạo danh sách lựa chọn các thương hiệu smartphone cho cột “Dòng Smartphone” trong bảng báo cáo bên cạnh.
Bạn có thể bôi đen tất cả các ô trong cột H của bảng báo cáo, sau đó chọn Data Validation, dạng List và nguồn/Source chính là vùng $D$2:$D$5.
Bây giờ chúng ta mới áp dụng Hàm INDIRECT trong Data Validation cho các mục ở cột “Tên sản phẩm” dựa vào chuỗi ký tự tham chiếu ở cột “Dòng Smartphone”.
Tại ô I2 chúng ta tiếp tục chọn Data Validation, dạng List. Tuy nhiên ở đây bạn sẽ không chọn vùng trực tiếp mà sẽ dùng công thức: =INDIRECT($H2).
Nghĩa là hàm sẽ tham chiếu gián tiếp vào ô H2. Trong ô này chứa giá trị là Iphone thì lúc này nó sẽ tạo danh sách lựa chọn từ vùng dữ liệu được đặt tên là “Iphone” ở trên.
Các bạn chú ý, khi dùng hàm Indirect và trỏ vào ô H2 thì công thức mặc định hiển thị ra là =INDIRECT($H$2). Nhưng chúng ta sẽ bỏ cố định hàng ($ trước số 2) để sau đó các bạn có thể copy luôn xuống các ô bên dưới mà không phải vào sửa lại công thức ở phần Source của Data Validation.
Đây là tư duy giúp chúng ta tối ưu, tiết kiệm thời gian hơn khi làm việc với Excel.
Như vậy là khi bạn thay đổi tên thương hiệu smartphone trong cột H, thì các danh sách phụ thuộc cũng sẽ tự động cập nhật theo. VD, bạn đổi ô H2 thành Xiaomi thì ở ô I2, danh sách sẽ đổi thành (Mi 11 5G; Mi 10T Pro 5G;…).
Ứng dụng hàm INDIRECT kết hợp hàm Vlookup
Tiếp tục với những dữ liệu ở ứng dụng trên, trong bảng báo cáo. Chúng ta cũng có thể kết hợp hàm INDIRECT và hàm VLOOKUP để tham chiếu dữ liệu động.
Ở bảng này chúng ta còn phần đơn giá cần hoàn thiện nốt để có thể xuất báo cáo doanh thu theo sản phẩm.
Câu hỏi đặt ra là: Làm thế nào để phần đơn giá có thể tự động nhảy ra theo sản tên sản phẩm được lựa chọn từ danh sách trên?
Thông thường chúng ta sẽ dùng ☛ hàm VLOOKUP ☚, với công thức có dạng: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]).
Ví dụ, nếu là sản phẩm Iphone 11 Pro Max thì vùng tham chiếu sẽ là bảng nhóm của Iphone với 2 cột là cột Tên SP và cột Giá.
Tuy nhiên nếu tham chiếu theo kiểu thông thường thì khi copy công thức xuống ngay ô J3 bên dưới (tương ứng với Oppo Reno6 5G) thì hàm sẽ lỗi, vì vùng tham chiếu của các sản phẩm Iphone không chưa tên sản phẩm cần dò tìm.
Vậy thì trong trường hợp này chúng ta sẽ phải tạo vùng dữ liệu động (table_array) theo tên của sản phẩm với hàm Indirect.
Trước tên chúng ta cũng cần phải đặt tên cho vùng dữ liệu của từng nhóm sản phẩm. Bởi vì vùng nay phải bao gồm cả trường giá nên không thể dùng tên của các vùng chọn đã đặt ở trên.
Các vùng đặt nên là một Tên dài bao gồm từ gốc của tên các thương hiệu Smartphone:
- Table_array Samsung: A8:B12 – được đặt tên là “GiaSamsung”
- Table_array Iphone: D8:E12 – được đặt tên là “GiaIphone”
- Table_array Oppo: A15:B19 – được đặt tên là “GiaOppo”
- Table_array Xiaomi: D15:E19 – được đặt tên là “GiaXiaomi”
Bây giờ chúng ta sẽ áp dụng công thức VLOOKUP kết hợp INDIRECT trong ô J2 như sau:
=VLOOKUP(I2,INDIRECT(“Gia”$H2),2,0)
Trong đó:
Một chú ý quan trọng là: Việc đặt tên Table_array như: GiaSamsung rất quan trọng. Bởi vì làm như vậy chúng ta mới có thể tạo vùng dữ liệu động dựa theo điều kiện tên thương hiệu ở cột H của bảng báo cáo.
Chỉ cần áp dụng 1 công thức cho ô J2 đầu tiên, sau đó chúng ta hoàn toàn có thể copy rất tất cả các ô còn lại mà không cần chỉnh sửa bất cứ thứ gì. Tất cả các vùng tham chiếu đầu có dạng: “Gia+Tên Dòng Smartphone”.
Kết quả ở ô J2 trả về là giá tương ứng của sản phẩm Iphoen 11 Pro Max – 17.000.000. Và khi đơn giá được tự động điền theo công thức thì báo cáo của chúng ta cũng hoàn thành 100%.
Hàm INDIRECT kết hợp hàm tính toán SUM, AVERAGE,…
Như đã nói ở trên, hàm INDIRECT kết hợp với các hàm SUM, AVERAGE,… còn giúp chúng ta tính toán dữ liệu động dựa theo điều kiện rất nhanh chóng và hiệu quả.
Bởi bản chất các hàm tính toán cần đối số đầu vào là các vùng tham chiếu dữ liệu. Và nó hoàn toàn có thể được tạo bởi hàm Indirect một cách dễ dàng.
Trong ứng dụng này, chúng ta cũng sẽ đặt tên cho các cột doanh thu của từng thương hiệu smartphone tương ứng với tên của các trường.
Tại ô I4, chúng ta có thể tính tổng Doanh thu tuỳ theo điều kiện TH smartphoen ở ô I3 với công thức: =SUM(INDIRECT($I43))
Các bạn chú ý cố định địa chỉ ô I3, để khi copy công thức xuống tính trung bình, min, max thì chúng ta chỉ cần đổi tên hàm mà không phải sửa ô tham chiếu.
- =AVERAGE(INDIRECT($I43))
- =MIN(INDIRECT($I43))
- =MAX(INDIRECT($I43))
Như vậy bạn có thể tính toán dữ liệu động cho báo cáo trong ví dụ này cho các thương hiệu khác như Samsung, Oppo hay Xiaomi bằng cách chọn trong Data Validation ở ô I3.
Một số lỗi có thể gặp khi dùng hàm INDIRECT trong excel
Trong quá trình thao tác với hàm INDIRECT trong excel, nếu bạn mới bắt đầu sử dụng thì có thể gặp một số lỗi như: #NAME?, #REF. Đây là hai lỗi phổ biến thường gặp.
Trong khi lỗi #NAME? thì đơn giản do bạn gõ sai tên hàm thì lỗi #REF! có thể do một số nguyên nhân:
- Đối số ref_text nhập vào không hợp lệ
- Nếu công thức Indirect tham chiếu tới một ô excel thuộc một file excel khác mà file đó lại đang đóng thì cũng sẽ gặp lỗi trên
Bạn có thể tìm hiểu thêm bài viết giải thích bản chất các lỗi trong excel và cách khắc phục.
☛ Tải BÀI TẬP THỰC HÀNH: TẠI ĐÂY
Với bài viết rất đầy đủ về hàm INDIRECT trong excel và các ứng dụng cụ thể của hàm này trong thực tế. Hy vọng bạn đã có thể nắm rõ những kiến thức này để vận dụng vào công việc hằng ngày một cách hiệu quả.
Chia sẻ bài viết tới bạn bè
