Excel địa chỉ GIÁN TIẾP
|
Trước đây chúng ta đã đề cập đến cách chúng ta có thể sử dụng bất kỳ hàm nào để tạo văn bản có thể là tham chiếu ô hợp lệ, cả phần cột và hàng của địa chỉ, sau đó sử dụng hàm INDIRECT để chuyển đổi văn bản đó thành tham chiếu ô. Quá trình này có thể được tăng cường đáng kể bởi chức năng ADDRESS Show
Hàm ADDRESS trả về một chuỗi văn bản đại diện cho địa chỉ của một ô cụ thể Hàng, cột, loại tham chiếu (bị khóa hoặc tuyệt đối), kiểu tham chiếu (kiểu A1 hoặc R1C1) và trang tính có thể được chỉ định trong các đối số
Cú pháp hàm ADDRESS như sau = ADDRESS ( row_num ; column_num ; Reference_type ; Reference_style ; “[workboook]worksheet” ) Số hàng là một giá trị số xác định hàng sẽ được sử dụng trong tham chiếu ô Số cột là một giá trị số xác định cột sẽ được sử dụng trong tham chiếu ô Loại tham chiếu là một giá trị số chỉ định loại tham chiếu sẽ được sử dụng trong tham chiếu ô, với 1 biểu thị tham chiếu tuyệt đối. Nếu bỏ qua, tham chiếu tuyệt đối cũng sẽ được sử dụng Kiểu tham chiếu là một giá trị số chỉ định kiểu tham chiếu sẽ được sử dụng trong tham chiếu ô, với 1 đại diện cho kiểu tham chiếu A1. Nếu bỏ qua, kiểu tham chiếu A1 cũng sẽ được sử dụng Trang tính và phần sổ làm việc có khả năng của địa chỉ được nhập vào đối số cuối cùng, bên trong dấu ngoặc kép, dưới dạng văn bản. Các ví dụ hợp lệ là “Sheet1” hoặc “[Book1]Sheet1”. Tuy nhiên, bạn sẽ không thể tham chiếu các sổ làm việc đã đóng theo cách này. Nếu bỏ qua, trang tính hiện tại sẽ được tham chiếu
Nếu ô được nhắm mục tiêu nằm trong cùng một trang tính, cũng có thể sử dụng cú pháp rút gọn = ĐỊA CHỈ ( row_num ; column_num )
Xem xét ví dụ sau Trong cột E, chúng tôi đã tạo một số chuỗi văn bản đại diện cho địa chỉ ô, cả bên trong và bên ngoài trang tính/sổ làm việc hiện tại Trước tiên, chúng tôi luôn chỉ định hàng, sau đó là cột, sau đó là kiểu tham chiếu, sau đó là kiểu tham chiếu và cuối cùng là trang tính và cuối cùng là sổ làm việc Cho rằng đó là các tham chiếu hợp lệ, chúng ta có thể đặt các kết quả trả về làm đối số cho hàm INDIRECT và trả về các số liệu trong các ô được tham chiếu Lưu ý #REF. lỗi trong ô H15 – vì Book3 hiện đang đóng, tham chiếu đó do đó không hợp lệ
Chúng ta có thể kết hợp trực tiếp các hàm đó bằng cách lồng hàm ADDRESS bên trong hàm INDIRECT, như được hiển thị trong ô H17
Chúng ta cũng có thể kết hợp hai hàm ADDRESS trong một chuỗi văn bản để tạo tham chiếu đến một phạm vi ô. Sau khi được tạo, chuỗi văn bản đó có thể được lồng vào bên trong hàm INDIRECT và các thao tác được thực hiện với nó Lưu ý rằng khi tính toán ô cuối cùng trong phạm vi (hàm ADDRESS thứ hai), chúng tôi không tham chiếu đến trang tính
Bây giờ hãy xem xét ví dụ thực tế này Hàng tháng, chúng tôi nhận được dữ liệu YTD trong cấu trúc này, tôi. e. , hàng tháng, một cột mới và có thể một số hàng sẽ được thêm vào nếu người dùng và nhóm mới xuất hiện Thời gian lần cuối cùng YTD của chúng ta là bao nhiêu? MTD cuối cùng của chúng ta là gì? Cái cuối cùng YTD của chúng tôi đối với một người dùng cụ thể là bao nhiêu? MTD cuối cùng của chúng tôi cho một nhóm cụ thể là gì? Câu trả lời cho những câu hỏi như vậy có thể dễ dàng được tự động hóa và có sẵn trong giây lát với sự trợ giúp của các kết quả trả về ĐỊA CHỈ GIÁN TIẾP kết hợp với các chức năng cần thiết khác Giả sử cùng một cấu trúc dữ liệu, bằng cách đếm các hàng và cột được lấp đầy, chúng tôi có thể tạo tham chiếu gián tiếp đến khoảng thời gian cuối . Sau khi được thiết lập, các loại công thức phức tạp này có thể cung cấp cho chúng tôi các câu trả lời cần thiết vô thời hạn. Trong mọi giai đoạn sau, công việc bổ sung duy nhất của chúng tôi là dán bản xuất mới vào bảng "dữ liệu". Đương nhiên, có nhiều cách khác để đạt được những kết quả này, nhưng trong tình huống, sự kết hợp ĐỊA CHỈ GIÁN TIẾP sẽ là giải pháp đơn giản nhất hoặc thậm chí mạnh mẽ nhất Hàm INDIRECT trong Excel có thể được sử dụng khi bạn có tham chiếu của một ô hoặc một phạm vi dưới dạng chuỗi văn bản và bạn muốn lấy các giá trị từ các tham chiếu đó Tóm lại – bạn có thể sử dụng công thức gián tiếp để trả về tham chiếu được chỉ định bởi chuỗi văn bản Trong hướng dẫn Excel này, tôi sẽ chỉ cho bạn cách sử dụng hàm gián tiếp trong Excel bằng một số ví dụ thực tế Nhưng trước khi tôi đi vào các ví dụ, trước tiên chúng ta hãy xem cú pháp của nó Hàm GIÁN TIẾP Cú pháp=INDIRECT(ref_text, [a1]) Đối số đầu vào
Ghi chú bổ sung
Ví dụ về cách sử dụng hàm gián tiếp trong ExcelBây giờ, hãy đi sâu vào và xem một số ví dụ về cách sử dụng hàm INDIRECT trong Excel ví dụ 1. Sử dụng tham chiếu ô để lấy giá trịNó lấy tham chiếu ô dưới dạng chuỗi văn bản làm đầu vào và trả về giá trị trong tham chiếu đó (như minh họa trong ví dụ bên dưới) Công thức trong ô C1 là =INDIRECT("A1")Công thức trên lấy tham chiếu ô A1 làm đối số đầu vào (trong dấu ngoặc kép dưới dạng chuỗi văn bản) và trả về giá trị trong ô này là 123 Bây giờ nếu bạn đang nghĩ, tại sao tôi không đơn giản sử dụng =A1 thay vì sử dụng hàm INDIRECT, bạn có một câu hỏi hợp lệ Đây là lý do tại sao… Khi bạn sử dụng =A1 hoặc =$A$1, nó sẽ cho bạn kết quả tương tự. Nhưng khi bạn chèn một hàng phía trên hàng đầu tiên, bạn sẽ nhận thấy rằng các tham chiếu ô sẽ tự động thay đổi thành tài khoản cho hàng mới Bạn cũng có thể sử dụng hàm INDIRECT khi muốn khóa các tham chiếu ô theo cách nó không thay đổi khi bạn chèn các hàng/cột vào trang tính ví dụ 2. Sử dụng tham chiếu ô trong một ô để lấy giá trịBạn cũng có thể sử dụng hàm này để lấy giá trị từ một ô có tham chiếu được lưu trữ trong chính ô đó Trong ví dụ trên, ô A1 có giá trị 123 Ô C1 có tham chiếu đến ô A1 (dưới dạng chuỗi văn bản) Bây giờ, khi bạn sử dụng hàm INDIRECT và sử dụng C1 làm đối số (do đó có địa chỉ ô dưới dạng chuỗi văn bản trong đó), nó sẽ chuyển đổi giá trị trong ô A1 thành tham chiếu ô hợp lệ Đến lượt nó, điều này có nghĩa là hàm sẽ tham chiếu đến ô A1 và trả về giá trị trong đó Lưu ý rằng bạn không cần sử dụng dấu ngoặc kép ở đây vì C1 chỉ lưu trữ tham chiếu ô ở định dạng chuỗi văn bản Ngoài ra, trong trường hợp chuỗi văn bản trong ô C1 không phải là tham chiếu ô hợp lệ, hàm Gián tiếp sẽ trả về lỗi #REF. lỗi ví dụ 3. Tạo một tham chiếu sử dụng giá trị trong một ôBạn cũng có thể tạo tham chiếu ô bằng cách sử dụng kết hợp bảng chữ cái cột và số hàng Ví dụ: nếu ô C1 chứa số 2 và bạn sử dụng công thức =INDIRECT(“A”&C1) thì nó sẽ tham chiếu đến ô A2 Một ứng dụng thực tế của điều này có thể là khi bạn muốn tạo tham chiếu động đến các ô dựa trên giá trị trong một số ô khác Trong trường hợp chuỗi văn bản bạn sử dụng trong công thức đưa ra tham chiếu mà Excel không hiểu, nó sẽ trả về lỗi tham chiếu (#REF. ) Ví dụ 4. Tính SUM của một dãy ôBạn cũng có thể tham chiếu đến một dải ô giống như cách bạn tham chiếu đến một ô duy nhất bằng cách sử dụng hàm INDIRECT trong Excel Ví dụ: =INDIRECT(“A1. A5”) sẽ đề cập đến phạm vi A1. A5 Sau đó, bạn có thể sử dụng hàm SUM để tìm tổng hoặc hàm LARGE/SMALL/MIN/MAX để thực hiện các phép tính khác Cũng giống như hàm SUM, bạn cũng có thể sử dụng các hàm như LARGE, MAX/MIN, COUNT, v.v. Ví dụ 5. Tạo tham chiếu đến một trang tính bằng hàm INDIRECTCác ví dụ trên bao gồm cách tham chiếu một ô trong cùng một trang tính. Bạn cũng có thể sử dụng công thức GIÁN TIẾP để tham chiếu đến một ô trong một số trang tính khác hoặc sổ làm việc khác Đây là điều bạn cần biết về việc tham khảo các trang tính khác
Nhưng mà
Trường hợp nhiều từ thì Excel tự động chèn dấu nháy đơn vào đầu và cuối tên Sheet Bây giờ hãy xem cách tạo một hàm GIÁN TIẾP để tham chiếu đến một ô trong một trang tính khác Giả sử bạn có một trang tính có tên Bộ dữ liệu và ô A1 trong đó có giá trị 123 Bây giờ để tham chiếu đến ô này từ một trang tính khác, hãy sử dụng công thức sau =INDIRECT("'Data Set'!A1")Như bạn có thể thấy, tham chiếu đến ô cũng cần chứa tên trang tính Nếu bạn có tên của trang tính trong một ô (giả sử là A1), thì bạn có thể sử dụng công thức sau =INDIRECT("'"&A1&"'!A1")Nếu bạn có tên của trang tính trong ô A1 và địa chỉ ô trong ô A2, thì công thức sẽ là =INDIRECT("'"&A1&"'!"&A2)Tương tự, bạn cũng có thể sửa đổi công thức để tham chiếu đến một ô trong sổ làm việc khác Điều này có thể hữu ích khi bạn cố gắng tạo một trang tóm tắt lấy dữ liệu từ nhiều trang khác nhau Ngoài ra, hãy nhớ rằng khi sử dụng công thức này để tham chiếu đến một sổ làm việc khác, sổ làm việc đó phải được mở Ví dụ 6. Đề cập đến một phạm vi được đặt tên bằng cách sử dụng công thức GIÁN TIẾPNếu bạn đã tạo một dải được đặt tên trong Excel, bạn có thể tham khảo dải được đặt tên đó bằng hàm INDIRECT Ví dụ: giả sử bạn có điểm của 5 học sinh trong ba môn học như hình bên dưới Trong ví dụ này, hãy đặt tên cho các ô
Để đặt tên cho một phạm vi ô, chỉ cần chọn các ô và đi đến hộp tên, nhập tên và nhấn enter Bây giờ bạn có thể tham khảo các phạm vi được đặt tên này bằng công thức =INDIRECT("Named Range")Ví dụ, nếu bạn muốn biết điểm trung bình cộng của môn Toán, hãy sử dụng công thức =AVERAGE(INDIRECT("Math"))Nếu bạn có tên dải ô đã đặt tên trong một ô (F2 trong ví dụ bên dưới có tên là Math), thì bạn có thể sử dụng tên này trực tiếp trong công thức Ví dụ dưới đây cho thấy cách tính giá trị trung bình bằng cách sử dụng các phạm vi được đặt tên Ví dụ 7. Tạo danh sách thả xuống phụ thuộc bằng hàm Excel INDIRECTĐây là một cách sử dụng tuyệt vời của chức năng này. Bạn có thể dễ dàng tạo danh sách thả xuống phụ thuộc bằng cách sử dụng nó (còn được gọi là danh sách thả xuống có điều kiện) Ví dụ: giả sử bạn có một danh sách các quốc gia liên tiếp và tên các thành phố của từng quốc gia như hình bên dưới Bây giờ để tạo danh sách thả xuống phụ thuộc, bạn cần tạo hai phạm vi được đặt tên, A2. A5 với tên Mỹ và B2. B5 với tên Ấn Độ Bây giờ, chọn ô D2 và tạo danh sách thả xuống cho Ấn Độ và Hoa Kỳ. Đây sẽ là danh sách thả xuống đầu tiên mà người dùng có tùy chọn để chọn một quốc gia Bây giờ để tạo danh sách thả xuống phụ thuộc
Bây giờ, khi bạn nhập Hoa Kỳ vào ô D2, trình đơn thả xuống trong ô E2 sẽ hiển thị các tiểu bang ở Hoa Kỳ Và khi bạn nhập Ấn Độ vào ô D2, trình đơn thả xuống trong ô E2 sẽ hiển thị các tiểu bang ở Ấn Độ Vì vậy, đây là một số ví dụ để sử dụng hàm INDIRECT trong Excel. Những ví dụ này sẽ hoạt động trên tất cả các phiên bản Excel (Office 365, Excel 2019/2016/2013/2013) =@ gián tiếp nghĩa là gì trong Excel?Hàm INDIRECT trong Excel là gì? . Hàm INDIRECT không đánh giá các bài kiểm tra hoặc điều kiện logic. Ngoài ra, nó sẽ không thực hiện tính toán. Về cơ bản, hàm này giúp khóa ô được chỉ định trong công thức. returns a reference to a range. The INDIRECT function does not evaluate logical tests or conditions. Also, it will not perform calculations. Basically, this function helps lock the specified cell in a formula.
Bạn có thể sử dụng gián tiếp với một phạm vi trong Excel không?Hàm INDIRECT trong Excel có thể được sử dụng khi bạn có tham chiếu của một ô hoặc một phạm vi dưới dạng chuỗi văn bản và bạn muốn lấy các giá trị từ các tham chiếu đó . Tóm lại – bạn có thể sử dụng công thức gián tiếp để trả về tham chiếu được chỉ định bởi chuỗi văn bản. |
