Hàm INDIRECT trong Excel
Hàm gián tiếp trong excel là một hàm có sẵn được sử dụng để tham chiếu và lấy giá trị ô từ một chuỗi văn bản, công thức này lấy tham chiếu từ ô được tham chiếu, nó có hai đối số, đối số đầu tiên không phải là tùy chọn trong khi đối số thứ hai là tùy chọn tham chiếu đến loại đối sánh, hàm này cũng có thể được sử dụng với một công thức khác.
Hàm INDIRECT trong excel có nhiều cách sử dụng. Nó trả về tham chiếu ô được chỉ định bởi một chuỗi văn bản. Ví dụ: nếu ô B1 chứa một chuỗi, giả sử phạm vi c4 và c4 có giá trị “Jessica”. Bây giờ, nếu chúng ta sử dụng hàm INDIRECT và chuyển văn bản tham chiếu là B1, nó sẽ trả về giá trị chứa trong phạm vi ô c4 là “Jessica”.
Hàm INDIRECT của Excel cho phép bạn chỉ định một địa chỉ ô INDIRECTly. Như bạn có thể làm trong ví dụ INDIRECT Excel ở trên, nếu ô B1 chứa văn bản C4, công thức excel gián tiếp này trả về nội dung của ô C4 ( Jessica ). Chức năng này là một chức năng cực kỳ hữu ích. Bạn có thể sử dụng hàm INDIRECT bất cứ khi nào bạn muốn thay đổi tham chiếu đến một ô trong công thức excel gián tiếp mà không cần thay đổi chính công thức đó.
CHỈNH SỬA Công thức Excel
Giải trình
Ref_text là tham chiếu đến ô có chứa tham chiếu kiểu A1, tham chiếu kiểu R1C1, tên được xác định là tham chiếu hoặc tham chiếu đến ô dưới dạng chuỗi văn bản.
đối số a1 là tùy chọn
| a1 | Sai hoặc 0, khi ref_text ở kiểu R1C1 |
| a1 | Bị bỏ qua hoặc Đúng (1) khi ref_text ở kiểu A1 |
Làm thế nào để sử dụng hàm INDIRECT trong Excel?
Hãy lấy một ví dụ INDIRECT excel trước khi sử dụng hàm địa chỉ trong sổ làm việc Excel:
Giả sử, đối với một trang web www.xyz.com, chúng tôi có dữ liệu phân tích của Google cho khách truy cập vào trang web từ các kênh đối tượng khác nhau cho năm quốc gia khác nhau như được hiển thị bên dưới trong bảng:
Chúng ta có thể sử dụng hàm INDIRECT trong Excel với mục đích chuyển đổi một chuỗi văn bản thành một tham chiếu, vì vậy hãy tham chiếu đến ô H4 và sử dụng tên đó làm tham chiếu. Việc sử dụng hàm INDIRECT trong excel, trong trường hợp này, là để tham chiếu các phạm vi tên bằng cách sử dụng các giá trị của một ô, vì vậy trong ô I4, chúng ta có thể chỉ cần sử dụng hàm sum để tính tổng số khách truy cập từ một quốc gia Canada.
Giờ đây, chúng ta có thể trực tiếp tính tổng số khách truy cập từ mỗi quốc gia bằng cách sử dụng hàm sum, nhưng bằng cách sử dụng hàm INDIRECT trong excel, chúng tôi có thể tính tổng số khách truy cập từ các quốc gia khác nhau bằng cách thay đổi tên tham chiếu chứ không phải thay đổi chính công thức excel gián tiếp.
Chúng tôi đã tạo phạm vi tên cho từng khách truy cập quốc gia; Điều này có thể được thực hiện dễ dàng bằng cách nhập phạm vi tên vào khoảng trống ngay bên dưới khay nhớ tạm, như được hiển thị bên dưới.
Một phương pháp khác là bạn có thể vào công thức-> quản lý tên -> chọn phạm vi bạn muốn đặt tên -> nhấp vào trình quản lý tên -> nhập phạm vi tên.
Tương tự, nó cũng tạo ra một phạm vi được đặt tên trong excel cho khách truy cập từ các quốc gia khác.
Bây giờ, trong I4, tính tổng khách truy cập từ quốc gia Canada bằng cách sử dụng hàm INDIRECT và sử dụng phạm vi tên trong H4 (Canada, tên ở trên cho F3: F7), chúng tôi nhận được tổng số khách truy cập.
Nếu chúng tôi tạo danh sách dữ liệu bằng cách sử dụng xác thực dữ liệu cho các quốc gia và áp dụng danh sách đó cho H4
Và thay đổi các tên quốc gia khác nhau từ Canada thành Ấn Độ, Hoa Kỳ, Úc hoặc Singapore, chúng tôi nhận được tổng số khách truy cập trong I4 bằng cách sử dụng hàm INDIRECT trong Excel.
Vì vậy, đối với mỗi trường hợp, chúng tôi không thay đổi công thức excel gián tiếp, nằm trong ô I4; chúng tôi đang thay đổi tham chiếu tên trong H4 và tính toán tổng số khách truy cập cho mỗi quốc gia.
Chúng ta có thể tiến thêm một bước nữa và có thể tham chiếu các tên cấp trang tính bằng cách sử dụng hàm INDIRECT trong excel. Ví dụ: bây giờ chúng ta sẽ sử dụng hàm INDIRECT để tham chiếu đến các trang tính khác nhau trong sổ làm việc.
Chúng tôi đã tạo các trang tính khác nhau với tên quốc gia cho tổng số lần bán hàng được thực hiện với mỗi kênh tìm kiếm cho các quốc gia khác nhau.
Đối với Ấn Độ,
Đối với Hoa Kỳ,
Tương tự, đối với các quốc gia khác.
Một lần nữa, chúng tôi sẽ tính toán tổng doanh số được thực hiện qua mỗi kênh bằng cách sử dụng hàm INDIRECT trong Excel cho các trang tính khác nhau có tên quốc gia làm tham chiếu.
Trong trường hợp này, vì chúng tôi có dữ liệu bán hàng trong các trang tính khác nhau, chúng tôi sẽ cần nối các phần tử khác nhau là phạm vi dữ liệu, vì vậy ref_text sẽ bắt đầu bằng tên của trang tính, trong trường hợp này là Ấn Độ trong B4, sau đó chúng tôi sẽ sử dụng toán tử & được gọi là ký hiệu và được sử dụng để nối.
Vì vậy, chúng tôi nhận được tổng số tiền bán hàng cho Ấn Độ; bây giờ, nếu chúng tôi thay đổi tên quốc gia trang tính trong B4 từ Ấn Độ sang các quốc gia khác như Hoa Kỳ, Singapore hoặc Canada, chúng tôi sẽ nhận được tổng giá trị bán của các quốc gia đó.
Một lần nữa, trong trường hợp này, chúng tôi chỉ cần thay đổi các giá trị ref_text trong ô B4 mà không thay đổi công thức excel gián tiếp chính, nằm trong C4, để lấy tổng doanh số cho các quốc gia khác nhau nằm trong các trang tính khác nhau bằng cách sử dụng hàm INDIRECT.
Bây giờ, chúng ta sẽ sử dụng hàm INDIRECT bằng cách sử dụng tham chiếu R1C1 để nhận giá trị bán hàng của tháng trước. Chúng tôi có doanh số bán hàng cho các tháng khác nhau ở các cột B, C và D trong ba tháng từ tháng 1, tháng 2 và tháng 3 có thể tăng lên các tháng khác như tháng 4, tháng 5, tháng 6, v.v. Vì vậy, bảng bán hàng sẽ luôn tăng lên tùy thuộc vào việc bổ sung đợt giảm giá trong tháng sắp tới. Vì vậy, trong ví dụ INDIRECT Excel này, chúng tôi muốn tính doanh thu của tháng trước.
Vì vậy, ở đây, tháng trước là tháng 3 và chúng tôi muốn tính tổng doanh thu của tháng 3, là $ 249.344, bằng cách sử dụng hàm INDIRECT.
Trong ví dụ INDIRECT Excel này, chúng ta sẽ tìm thấy cách sử dụng đối số thứ hai của hàm INDIRECT
INDIRECT (ref_text, a1)
a1: là một đối số tùy chọn, nó là một giá trị logic (một giá trị Boolean) chỉ định loại tham chiếu (ref_text) được chứa trong ô
nếu a1 là true (1) hoặc bị bỏ qua, ref_text được coi là một tham chiếu kiểu A1
nếu a1 là false (0), ref_text được hiểu là kiểu R1C1
Kiểu dáng A1 khác với Kiểu dáng R1C1 như thế nào?
Thông thường, hàm INDIRECT trong Excel sử dụng ký hiệu A1. Mỗi địa chỉ ô bao gồm một ký tự cột và một số hàng. Tuy nhiên, hàm INDIRECT trong Excel cũng hỗ trợ ký hiệu R1C1. Trong hệ thống này, ô A1 được gọi là ô R1C1, ô A2 là R2C1, v.v.
Để thay đổi thành ký hiệu R1C1, hãy chọn tùy chọn Tệp-> để mở hộp thoại tùy chọn excel, nhấp vào tab công thức và dấu kiểm cho tùy chọn kiểu tham chiếu R1C1. Bây giờ, bạn sẽ nhận thấy rằng các chữ cái của cột đều thay đổi thành số. Và tất cả các tham chiếu ô trong công thức của bạn cũng điều chỉnh.
Vì vậy, khi chúng ta sử dụng kiểu R1C1 trong hàm INDIRECT trong excel, chúng ta chuyển giá trị a1 là false và nếu chúng ta sử dụng kiểu A1-, thì chúng ta chuyển giá trị true cho a1.
Bây giờ, trong ví dụ INDIRECT Excel này, chúng ta sẽ sử dụng kiểu R1C1 do yêu cầu là các cột có tháng sẽ tăng lên.
Sử dụng kiểu R1C1, tổng giá trị bán được ở hàng 10 và giá trị cột cuối cùng, trong cột D, sẽ được tính bằng cách sử dụng hàm COUNTA , hàm này sẽ cho số cột cuối cùng chứa một giá trị. Vì vậy, đếm (10:10) trong trường hợp này sẽ cho ra 4, là số cột trong kiểu R1C1. Vì vậy, chúng tôi đã liên hệ với giá trị đó để nhận được R10C4 có chứa giá trị mong muốn là tổng giá trị bán hàng của tháng trước ($ 249.344).
Và, vì chúng ta đang sử dụng kiểu R1C1 ở đây, chúng ta sẽ chuyển một giá trị sai cho đối số a1.
Bây giờ, chúng ta hãy thêm một cột nữa với tháng Giảm giá tháng Tư.
Vì vậy, hãy nhận các giá trị bán hàng mới của tháng trước tương ứng với giá trị của tháng cuối cùng của tháng Tư. Tương tự, nếu chúng ta thêm nhiều tháng hơn, chúng ta sẽ luôn nhận được giá trị bán hàng mới của tháng trước mà không cần thay đổi công thức excel gián tiếp cho mỗi tháng. Đây là đặc điểm của hàm INDIRECT trong excel.
Làm cách nào để tùy chỉnh mảng bảng trong hàm INDIRECT Excel VLOOKUP?
Chúng tôi có năm quốc gia và một kênh tìm kiếm và việc bán hàng mà trang web thực hiện thông qua các kênh khác nhau này từ các quốc gia khác nhau. Bây giờ, trong ô B3, chúng tôi muốn bán được hàng thông qua các tìm kiếm Không phải trả tiền từ quốc gia Hoa Kỳ.
Đối với mỗi bảng cho các quốc gia khác nhau, chúng tôi đã đặt tên cho mỗi bảng với một tên ngắn cho đất nước, Ind cho Ấn Độ, Hoa cho Mỹ, Cần cho Canada, và vân vân .
Nếu chúng ta trực tiếp sử dụng hàm INDIRECT Excel vlookup để lấy giá trị, như thực hiện bên dưới
Chúng tôi gặp lỗi vì hàm INDIRECT Excel vlookup không thể nhận dạng mảng bảng được truyền dưới dạng giá trị của ô B2, vì vậy chúng tôi sẽ sử dụng hàm INDIRECT trong excel để làm cho vlookup nhận được table_array là đối số hợp lệ.
Nếu chúng tôi thay đổi giá trị của B1 và B2, chúng tôi sẽ nhận được các giá trị Bán khác nhau trong B3, theo yêu cầu.
Lưu ý: Phạm vi tên không phân biệt chữ hoa chữ thường. Do đó - Ind, IND và InD đều giống với Excel.
Trong ví dụ INDIRECT Excel tiếp theo, chúng ta sẽ sử dụng hàm INDIRECT trong excel để tạo danh sách phụ thuộc. Chúng tôi có một danh sách các vị trí của một nhân viên trong một công ty; sau đó, đối với mỗi vị trí, chúng tôi có tên của các nhân viên.
Bây giờ, trong ô I2, chúng tôi sẽ tạo một danh sách cho các cấp bậc nhân viên khác nhau làm vị trí của họ và trong I3, chúng tôi sẽ hiển thị một danh sách phụ thuộc vào giá trị trong I2. Nếu I2 là giám đốc, thì I3 nên phản ánh tên của các giám đốc mà Andrew và Micheal; nếu I2 là Người quản lý, thì I3 phải phản ánh tên của người quản lý là Camille, David, Davis và William và theo cách tương tự đối với người giám sát.
Trong trường hợp này, một lần nữa, chúng ta sẽ sử dụng hàm INDIRECT trong excel để tạo danh sách phụ thuộc. Xác thực dữ liệu, trong cài đặt chọn tiêu chí xác thực dưới dạng danh sách và trong trường nguồn, chúng ta sẽ sử dụng hàm INDIRECT trong excel như sau.
= CHỈ ĐỊNH ($ I $ 2)
Khi chúng tôi chọn tên giám đốc trong I3 phản ánh dưới dạng danh sách có tên Andrew và Micheal, khi I2 là Quản lý, thì I3 phản ánh tên của người quản lý Camille, David, Davis và William và tương tự cho người giám sát như thể hiện bên dưới trong hình.
Những điều cần ghi nhớ
- Đối số ref_text phải là một tham chiếu ô hợp lệ, nếu không INDIRECT sẽ trả về giá trị lỗi #REF! Lỗi. Đối số ref_text có thể tham chiếu đến một sổ làm việc khác.
- Nếu ref_text tham chiếu đến một sổ làm việc khác (tham chiếu bên ngoài), thì sổ làm việc kia phải được mở. Nếu sổ làm việc nguồn không mở, hàm INDIRECT trả về lỗi #REF! Giá trị lỗi.
- Nếu ref_text đề cập đến một phạm vi ô nằm ngoài giới hạn hàng là 1.048.576 hoặc giới hạn cột là 16.384 (XFD), hàm INDIRECT trả về lỗi #REF! Lỗi.
