Tải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm INDIRECT trong Excel trong Excel để tạo tham chiếu ô từ văn bản.
Tổng quan về hàm INDIRECT
Hàm INDIRECT Tạo tham chiếu ô từ một chuỗi văn bản.
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm INDIRECT Cú pháp và đầu vào:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - Một chuỗi đại diện cho tham chiếu ô hoặc tham chiếu phạm vi. Chuỗi có thể ở định dạng R1C1 hoặc A1 hoặc có thể là một dải ô được đặt tên.
a1 - TÙY CHỌN: Cho biết tham chiếu ở định dạng R1C1 hay A1. FALSE cho R1C1 hoặc TRUE / Bỏ qua cho A1.
Hàm INDIRECT là gì?
Hàm INDIRECT cho phép bạn cung cấp một chuỗi văn bản và để máy tính diễn giải chuỗi đó như một tham chiếu thực tế. Điều này có thể được sử dụng để tham chiếu một phạm vi trên cùng một trang tính, một trang tính khác hoặc thậm chí một sổ làm việc khác.
THẬN TRỌNG: Hàm INDIRECT là một trong những hàm dễ thay đổi. Hầu hết thời gian khi bạn làm việc trong bảng tính của mình, máy tính sẽ chỉ tính toán lại một công thức nếu các đầu vào đã thay đổi giá trị của chúng. Tuy nhiên, một hàm dễ bay hơi sẽ tính toán lại mỗi thời gian bạn thực hiện thay đổi cho bất kỳ ô nào. Cần thận trọng để đảm bảo rằng bạn không gây ra thời gian tính toán lại lớn do sử dụng quá nhiều hàm biến đổi hoặc có nhiều ô phụ thuộc vào kết quả của hàm biến động.
Tạo tham chiếu ô
Giả sử rằng bạn muốn lấy giá trị từ A2, nhưng bạn muốn đảm bảo rằng công thức của bạn ở lại trên A2 bất kể hàng mới được chèn / xóa. Bạn có thể viết một công thức của
| 1 | = INDIRECT ("A2") |
Lưu ý rằng đối số bên trong hàm của chúng ta là chuỗi văn bản “A2” và không phải là tham chiếu ô. Ngoài ra, vì đây là một chuỗi văn bản, không cần phải chỉ ra một tham chiếu tuyệt đối như $ A $ 2. Văn bản sẽ không bao giờ thay đổi và do đó công thức này sẽ luôn trỏ đến A2, bất kể nó được chuyển đến đâu.
Số hàng INDIRECT
Bạn có thể nối các chuỗi văn bản và giá trị từ các ô với nhau. Thay vì viết “A2” như chúng ta đã làm trước đây, chúng ta có thể lấy một giá trị số từ ô B2 và sử dụng giá trị đó trong công thức của chúng ta. Chúng tôi sẽ viết ra công thức như
| 1 | = INDIRECT ("A" & B2) |
Biểu tượng “&” đang được sử dụng ở đây để nối chuỗi văn bản “A” với giá trị từ ô B2. Vì vậy, nếu giá trị của B2 hiện là 10, thì công thức của chúng tôi sẽ đọc là
| 123 | = INDIRECT ("A" & 10)= INDIRECT ("A10")= A10 |
Giá trị cột INDIRECT
Bạn cũng có thể nối trong tham chiếu cột. Lần này, hãy giả sử rằng chúng tôi biết chúng tôi muốn lấy một giá trị từ hàng 10, nhưng chúng tôi muốn có thể thay đổi cột sẽ lấy từ đó. Chúng tôi sẽ đặt ký tự cột mà chúng tôi muốn vào ô B2. Công thức của chúng tôi có thể trông giống như
| 1 | = INDIRECT (B2 & "10") |
Nếu giá trị của B2 là “G”, thì công thức của chúng tôi sẽ đánh giá như vậy
| 123 | = INDIRECT ("G" & 10)= INDIRECT ("G10")= G10 |
INDIRECT r1c1 kiểu
Trong ví dụ trước của chúng tôi, chúng tôi phải sử dụng một chữ cái để chỉ ra tham chiếu cột. Điều này là do chúng tôi đang sử dụng cái được gọi là tham chiếu kiểu A1. Trong kiểu A1, các cột được cung cấp bởi một chữ cái và các hàng được cung cấp bởi các số. Tham chiếu tuyệt đối được biểu thị bằng cách sử dụng “$” trước mục chúng tôi muốn giữ nguyên tuyệt đối.
Trong r1c1, cả hàng và cột đều được bắt đầu sử dụng số. Tham chiếu tuyệt đối đến a1 sẽ được viết là
| 1 | = R1C1 |
Bạn có thể đọc đây là “Hàng 1, Cột 1”. Tham chiếu tương đối được đưa ra bằng cách sử dụng dấu ngoặc, nhưng số chỉ vị trí liên quan đến ô có công thức. Vì vậy, nếu chúng tôi đang viết công thức trong ô A10 và chúng tôi cần tham chiếu đến ô A1, chúng tôi sẽ viết công thức
| 1 | = R [-9] C |
Bạn có thể đọc đây là “Ô 9 hàng lên, nhưng trong cùng một cột.
Lý do điều này có thể hữu ích là INDIRECT có thể hỗ trợ việc sử dụng ký hiệu r1c1. Hãy xem xét ví dụ trước, nơi chúng tôi đang tìm nạp một giá trị từ hàng 10 nhưng muốn có thể thay đổi cột. Thay vì đưa ra một lá thư, giả sử chúng ta đặt một số vào ô B2. Công thức của chúng tôi sau đó có thể trông giống như
| 1 | = INDIRECT ("R10C" & B2, FALSE) |
Chúng tôi đã bỏ qua 2NS tranh luận cho đến bây giờ. Nếu đối số này bị bỏ qua hoặc True, hàm sẽ đánh giá bằng kiểu A1. Vì nó là Sai, nó sẽ đánh giá trong r1c1. Giả sử giá trị của B2 là 5. Công thức của chúng tôi sẽ đánh giá điều này như vậy
| 12 | = INDIRECT ("R10C5", FALSE)= $ E $ 10 |
Sự khác biệt INDIRECT với A1 so với r1c1
Hãy nhớ rằng trước đây chúng tôi đã chỉ ra rằng vì nội dung của công thức này là một chuỗi văn bản nên nó không bao giờ thay đổi?
| 1 | = INDIRECT ("A2") |
Công thức này sẽ luôn nhìn vào ô A2, bất kể bạn di chuyển công thức ở đâu. Trong r1c1, vì bạn có thể chỉ ra vị trí tương đối bằng cách sử dụng dấu ngoặc, nên quy tắc này không nhất quán. Nếu bạn đặt công thức này trong ô B2
| 1 | = INDIRECT ("RC [-1]") |
Nó sẽ nhìn vào ô A2 (vì cột A là một bên trái của cột B). Nếu bạn sao chép công thức này vào ô B3, văn bản bên trong sẽ vẫn như cũ, nhưng INDIRECT bây giờ sẽ nhìn vào ô A3.
CHỈNH SỬA với tên trang tính
Bạn cũng có thể kết hợp tên trang tính vào các tham chiếu INDIRECT của mình. Một quy tắc quan trọng cần nhớ là bạn nên đặt dấu ngoặc kép xung quanh tên và bạn cần tách tên trang tính khỏi tham chiếu ô bằng dấu chấm than.
Giả sử chúng tôi đã thiết lập này, trong đó chúng tôi đang nêu tên trang tính, hàng và cột của mình.
Công thức của chúng tôi để kết hợp tất cả những thứ này thành một tham chiếu sẽ giống như sau:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Công thức của chúng tôi sau đó sẽ được đánh giá như vậy:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" Sheet2'! B5 ")= 'Trang tính2'! B5 |
Về mặt kỹ thuật, vì từ “Sheet2” không có bất kỳ khoảng trắng nào trong đó, chúng tôi không nhu cầu dấu ngoặc kép. Hoàn toàn hợp lệ khi viết một cái gì đó như
| 1 | = Trang tính2! A2 |
Tuy nhiên, bạn không cần đặt dấu ngoặc kép khi không cần. Cách tốt nhất là bao gồm chúng để công thức của bạn có thể xử lý các trường hợp cần thiết.
CHỈNH SỬA sang một sổ làm việc khác
Chúng tôi cũng sẽ đề cập rằng INDIRECT có thể tạo tham chiếu đến một sổ làm việc khác. Hạn chế là INDIRECT sẽ không tìm nạp các giá trị từ một sổ làm việc đã đóng, do đó, việc sử dụng cụ thể này có tính thực tế hạn chế. Nếu sổ làm việc mà INDIRECT trỏ đến chưa được mở, hàm sẽ ném ra “#REF!” lỗi.
Cú pháp khi viết tên sổ làm việc là nó cần phải nằm trong dấu ngoặc vuông. Hãy sử dụng thiết lập này và cố gắng tìm nạp một giá trị từ ô C7.
Công thức của chúng tôi sẽ là
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Một lần nữa, hãy chú ý đến vị trí của các dấu ngoặc kép, dấu ngoặc và dấu chấm than. Công thức của chúng tôi sau đó sẽ được đánh giá như vậy:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Tóm tắt" & "'! C7")= INDIRECT ("'[Sample.xslx] Tóm tắt'! C7")= '[Sample.xlsx] Tóm tắt'! C7 |
CHỈNH SỬA để tạo phạm vi động
Khi bạn có một tập dữ liệu lớn, điều quan trọng là phải thử và tối ưu hóa các công thức để chúng không hoạt động nhiều hơn mức cần thiết. Ví dụ: thay vì tham chiếu đến tất cả cột A, chúng ta có thể chỉ muốn tham chiếu đến số lượng ô chính xác trong danh sách của mình. Hãy xem xét bố cục sau:
Trong ô B2, chúng tôi đã đặt công thức
| 1 | = COUNTA (A: A) |
Hàm COUNTA rất dễ dàng để máy tính tính toán, vì nó chỉ kiểm tra xem có bao nhiêu ô trong cột A có một giá trị nào đó, thay vì phải thực hiện bất kỳ kiểm tra logic hoặc phép toán nào.
Bây giờ, hãy xây dựng công thức của chúng tôi sẽ tính tổng các giá trị trong cột A, nhưng chúng tôi muốn đảm bảo rằng nó chỉ xem xét phạm vi chính xác có các giá trị (A2: A5). Chúng tôi sẽ viết công thức của chúng tôi dưới dạng
| 1 | = SUM (INDIRECT ("A2: A" & B2)) |
INDIRECT của chúng tôi sẽ lấy số 5 từ ô B2 và sẽ tạo một tham chiếu đến phạm vi A2: A5. Sau đó, SUM có thể sử dụng phạm vi này để tính toán. Nếu chúng ta thêm một giá trị khác vào ô A6, thì số trong B2 sẽ cập nhật và công thức SUM của chúng ta cũng sẽ tự động cập nhật để bao gồm giá trị mới này.
THẬN TRỌNG: Với sự ra đời của Tables trong Office 2007, việc lưu trữ dữ liệu của bạn trong bảng và sử dụng tham chiếu cấu trúc sẽ hiệu quả hơn nhiều thay vì xây dựng công thức mà chúng tôi đã sử dụng trong ví dụ này do tính chất dễ bay hơi của INDIRECT. Tuy nhiên, chúng có thể là những trường hợp bạn cần tạo danh sách các mục và không thể sử dụng Bảng.
Biểu đồ động với INDIRECT
Hãy lấy ví dụ trước và đi thêm một bước nữa. Thay vì viết công thức để cung cấp cho chúng tôi tổng các giá trị, chúng tôi sẽ tạo một Dải ô được đặt tên. Chúng tôi có thể gọi phạm vi này là "MyData" và để nó tham chiếu đến
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Lưu ý rằng vì chúng tôi đang đặt giá trị này trong Dải ô được đặt tên, chúng tôi đã hoán đổi tham chiếu thành B2 và thay vào đó đặt trực tiếp hàm COUNTA vào đó.
Bây giờ chúng ta có phạm vi được đặt tên này, chúng ta có thể sử dụng nó trong biểu đồ. Chúng tôi sẽ tạo một biểu đồ đường trống, sau đó thêm một chuỗi dữ liệu. Đối với các giá trị của chuỗi, bạn có thể viết một cái gì đó như
| 1 | = Sheet1! MyData |
Biểu đồ bây giờ sẽ sử dụng tham chiếu này để vẽ các giá trị. Khi nhiều giá trị hơn được thêm vào cột A, INDIRECT sẽ tham chiếu đến một phạm vi lớn hơn và lớn hơn và biểu đồ của chúng tôi sẽ tiếp tục cập nhật với tất cả các giá trị mới được thêm vào.
Xác thực dữ liệu động với INDIRECT
Khi thu thập thông tin đầu vào từ người dùng, đôi khi cần phải đưa ra các tùy chọn của một lựa chọn để lựa chọn phụ thuộc vào lựa chọn trước đó. Hãy xem xét bố cục này, trong đó cột đầu tiên của chúng tôi cho phép người dùng chọn giữa Trái cây, Rau và Thịt.
Trong 2NS , chúng tôi không muốn có một danh sách lớn hiển thị mọi lựa chọn có thể, vì chúng tôi đã thu hẹp mọi thứ xuống một chút. Vì vậy, chúng tôi đã tạo 3 danh sách khác giống như sau:
Tiếp theo, chúng tôi sẽ chỉ định từng này danh sách cho một Dải ô được Đặt tên. Tức là, tất cả các loại trái cây sẽ nằm trong một phạm vi được gọi là "Trái cây" và rau trong "Rau", v.v.
Quay lại bảng của chúng tôi, chúng tôi đã sẵn sàng thiết lập xác thực dữ liệu trong 2NS cột. Chúng tôi sẽ tạo xác thực loại Danh sách, với đầu vào là:
| 1 | = INDIRECT (A2) |
INDIRECT sẽ đọc trong sự lựa chọn được thực hiện trong cột A và xem tên của một danh mục. Chúng tôi đã xác định phạm vi với những tên này, vì vậy INDIRECT sau đó sẽ lấy tên đó và tạo tham chiếu đến phạm vi mong muốn.
Ghi chú bổ sung
Sử dụng Hàm INDIRECT để tạo tham chiếu ô từ văn bản.
Đầu tiên, hãy tạo chuỗi văn bản đại diện cho một tham chiếu ô. Chuỗi phải ở dạng chữ cái & số hàng của cột A1-Style thông thường (M37) hoặc ở kiểu R1C1 (R37C13). Bạn có thể nhập trực tiếp giới thiệu, nhưng thông thường bạn sẽ tham chiếu các ô xác định các hàng và cột. Cuối cùng, nhập định dạng tham chiếu ô bạn chọn. TRUE hoặc Bỏ qua cho tham chiếu Kiểu A1 hoặc FALSE cho kiểu R1C1.
Trong khi làm việc với Công thức INDIRECT, bạn có thể muốn sử dụng Chức năng ROW để lấy số hàng của một tham chiếu hoặc Hàm COLUMN để lấy số cột (không phải chữ cái) của một tham chiếu.
Quay lại danh sách tất cả các hàm trong Excel
GIÁN TIẾP trong Google Trang tính
Hàm INDIRECT hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
