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 ADDRESS trong Excel trong Excel để trả về địa chỉ ô dưới dạng văn bản.
Tổng quan về chức năng ADDRESS
Hàm ADDRESS Trả về địa chỉ ô dưới dạng văn bản.
Để sử dụng ADDRESS Excel Worksheet Function, hãy chọn một ô và nhập:
(Chú ý cách các đầu vào công thức xuất hiện)
Hàm ADDRESS Cú pháp và đầu vào:
| 1 | = ADDRESS (row_num, column_num, abs_num, C1, sheet_text) |
row_num - Số hàng để tham khảo. Ví dụ: 5 cho hàng 5.
col_num - Số cột để tham khảo. Ví dụ: 5 cho Cột E. Bạn không thể nhập “E” cho cột E
abs_num - [tùy chọn] Một số biểu thị nếu tham chiếu phải có tham chiếu hàng / cột tuyệt đối hoặc tương đối. 1 cho tuyệt đối. 2 cho hàng tuyệt đối / cột tương đối. 3 cho hàng tương đối / cột tuyệt đối. 4 cho tương đối.
a1 - [không bắt buộc]. Một số cho biết sử dụng định dạng tham chiếu ô chuẩn (A1) hay định dạng R1C1. 1 / TRUE cho Standard (Mặc định). 0 / FALSE cho R1C1.
sheet_text - [tùy chọn] Tên của trang tính sẽ sử dụng. Mặc định cho trang tính hiện tại.
Chức năng ADDRESS là gì?
Chức năng ADDRESS là một chức năng độc đáo. Hầu hết thời gian trong bảng tính, chúng tôi cho máy tính biết một tham chiếu ô và nó cung cấp cho chúng tôi giá trị từ ô đó. Với ADDRESS, thay vào đó, chúng tôi sẽ xây dựng tên của ô. Địa chỉ này có thể là tương đối hoặc tuyệt đối, theo kiểu A1 hoặc R1C1 và có thể có hoặc không bao gồm tên trang tính. Phần lớn, chức năng này không có nhiều công dụng thực tế, nhưng nó có thể là một công cụ thú vị để tìm hiểu. Khi nó được sử dụng, nó thường kết hợp với các chức năng khác như chúng ta sẽ thấy bên dưới.
Ví dụ cơ bản
Giả sử rằng chúng tôi muốn tạo một tham chiếu đến ô trong 4NS cột và 1NS hàng, hay còn gọi là ô D1. Chúng ta có thể sử dụng bố cục trong hình ở đây:
Công thức của chúng tôi trong A3 chỉ đơn giản là
| 1 | = ĐỊA CHỈ (B1, B2) |
Lưu ý rằng bằng cách không nêu một đối số cụ thể cho tương đối / tuyệt đối, chúng tôi nhận được tất cả tuyệt đối. Chúng tôi cũng có kiểu mặc định của kiểu A1 tham chiếu w / no sheet name.
Kết hợp với INDIRECT
Như chúng tôi đã nói, bản thân chức năng ADDRESS không thực sự cung cấp cho chúng tôi bất kỳ điều gì hữu ích. Tuy nhiên, chúng tôi có thể kết hợp nó với hàm INDIRECT để có được một số chức năng. Hãy xem xét bố cục này, nơi chúng tôi có danh sách các mục trong cột D. Nếu chúng tôi giữ nguyên công thức như trước đây, chúng tôi sẽ tạo một tham chiếu đến D1 như vậy
| 12 | = ĐỊA CHỈ (B1, B2)= $ D $ 1 |
Bằng cách đặt hàm địa chỉ bên trong hàm INDIRECT, chúng tôi sẽ có thể sử dụng tham chiếu ô đã tạo và sử dụng nó theo cách thực tế. INDIRECT sẽ lấy tham chiếu là “$ D $ 1” và sử dụng nó để lấy giá trị từ ô đó.
| 123 | = INDIRECT (ĐỊA CHỈ (B1, B2)= INDIRECT ($ D $ 1)= "Quả táo" |
Ghi chú: Mặc dù phần trên đưa ra một ví dụ điển hình về việc làm cho hàm ADDRESS trở nên hữu ích, nhưng đây không phải là một công thức tốt để sử dụng bình thường. Nó yêu cầu hai chức năng, và vì INDIRECT, nó sẽ dễ bay hơi về bản chất. Một giải pháp thay thế tốt hơn sẽ là sử dụng INDEX như thế này: = INDEX (1: 1048576, B1, B2)
Địa chỉ của giá trị cụ thể
Đôi khi, khi bạn có một danh sách lớn các mục, bạn cần biết vị trí của một mục trong danh sách. Hãy xem xét bảng điểm này từ các sinh viên. Chúng tôi đã đi trước và tính toán các giá trị tối thiểu, trung bình và tối đa của những điểm số này trong các ô E2: G2.
Chúng tôi có thể lọc bảng của mình cho từng mục này để tìm vị trí của nó (một lần nữa, hãy tưởng tượng đây là một danh sách lớn hơn nhiều) hoặc thậm chí chúng tôi có thể áp dụng định dạng có điều kiện để nó hiển thị trực quan cho người dùng. Tuy nhiên, nếu danh sách có hàng nghìn hàng, chúng tôi không muốn phải cuộn đến mức đó để xem những gì chúng tôi muốn. Thay vào đó, chúng tôi sẽ sử dụng các giá trị trong E2: G2 để xác định địa chỉ của các ô chứa giá trị của chúng tôi. Để làm điều này, chúng tôi sẽ sử dụng chức năng MATCH với ADDRESS. Hãy nhớ rằng MATCH sẽ trả về vị trí tương đối của một giá trị trong một phạm vi.
Công thức của chúng tôi trong E3 sau đó là:
| 1 | = ĐỊA CHỈ (TRẬN ĐẤU (E2, $ B: $ B, 0), 2) |
Chúng tôi có thể sao chép cùng một công thức này sang G3 và chỉ tham chiếu E2 sẽ thay đổi vì nó là tham chiếu tương đối duy nhất. Nhìn lại E3, hàm MATCH có thể tìm thấy giá trị của 98 trong 5NS hàng của cột B. Sau đó, hàm ADDRESS của chúng tôi đã sử dụng nó để tạo địa chỉ đầy đủ của “$ B $ 5”.
Dịch các chữ cái trong cột từ các số
Cho đến thời điểm này, tất cả các ví dụ của chúng tôi đều cho phép hàm ADDRESS trả về một tham chiếu tuyệt đối. Ví dụ tiếp theo này sẽ trả về một tham chiếu tương đối. Trong bảng này, chúng tôi muốn nhập một số vào cột A và trả về tên ký tự cột tương ứng.
Để đạt được mục tiêu của mình, chúng tôi sẽ có hàm ADDRESS trả về một tham chiếu trên hàng 1 ở định dạng tương đối và sau đó chúng tôi sẽ xóa “1” khỏi chuỗi văn bản để chúng tôi chỉ còn lại (các) chữ cái. Hãy xem xét trong bảng hàng 3 của chúng tôi, trong đó đầu vào của chúng tôi là 13. Công thức của chúng tôi trong B3 là
| 1 | = SUBSTITUTE (ADDRESS (1, A3, 4), "1", "") |
Lưu ý rằng chúng tôi đã đưa ra 3rd đối số trong hàm ADDRESS, điều khiển tham chiếu tương đối so với tham chiếu tuyệt đối. Hàm ADDRESS sẽ xuất ra “M1”, sau đó hàm SUBSTITUTE loại bỏ “1” để chúng ta chỉ còn lại “M”.
Tìm địa chỉ của Named Ranges
Trong Excel, bạn có thể đặt tên cho dải ô hoặc các dải ô, cho phép bạn chỉ cần tham chiếu đến dải ô đã đặt tên thay vì tham chiếu ô.
Hầu hết các phạm vi được đặt tên là tĩnh, có nghĩa là chúng luôn tham chiếu đến cùng một phạm vi. Tuy nhiên, bạn cũng có thể tạo phạm vi được đặt tên động thay đổi kích thước dựa trên (các) công thức.
Với phạm vi được đặt tên động, bạn có thể cần biết địa chỉ chính xác mà phạm vi được đặt tên của bạn đang trỏ đến. Chúng ta có thể thực hiện điều này với Chức năng ĐỊA CHỈ.
Trong ví dụ này, chúng ta sẽ xem xét cách xác định địa chỉ cho phạm vi được đặt tên của chúng ta được gọi là "Lớp".
Hãy quay trở lại bàn của chúng ta từ trước:
Để lấy địa chỉ của một dải ô, bạn cần biết ô trên cùng bên trái và ô dưới cùng bên trái. Phần đầu tiên đủ dễ dàng để thực hiện với sự trợ giúp của chức năng ROW và COLUMN. Công thức của chúng tôi trong E1 có thể là
| 1 | = ĐỊA CHỈ (ROW (Lớp), COLUMN (Lớp)) |
Hàm ROW sẽ trả về hàng của ô đầu tiên trong phạm vi của chúng ta (sẽ là 1) và COLUMN sẽ thực hiện điều tương tự đối với cột (cũng là 1).
Để lấy ô dưới cùng bên phải, chúng tôi sẽ sử dụng hàm ROWS và COLUMNS. Vì chúng tôi có thể tìm ra điểm bắt đầu của phạm vi của mình, nếu chúng tôi tính toán độ cao của phạm vi và trừ điểm xuất phát của chúng tôi, chúng tôi sẽ nhận được điểm kết thúc chính xác. Công thức cho điều này trông giống như
| 1 | = ADDRESS (ROWS (Lớp) -ROW (Lớp) +1, COLUMNS (Lớp) -COLUMN (Lớp) +1) |
Cuối cùng, để tổng hợp tất cả lại thành một chuỗi duy nhất, chúng ta có thể chỉ cần nối các giá trị với nhau bằng dấu hai chấm ở giữa. Công thức trong E3 có thể là
| 1 | = E1 & ":" & E2 |
Ghi chú: Mặc dù chúng tôi có thể xác định địa chỉ, của phạm vi, nhưng hàm ADDRESS của chúng tôi xác định xem nên liệt kê các tham chiếu là tương đối hay tuyệt đối. Phạm vi động của bạn sẽ có các tham chiếu tương đối mà kỹ thuật này sẽ không nhận.
2NS Ghi chú: Kỹ thuật này chỉ hoạt động trên một phạm vi tên liên tục. Nếu bạn có một phạm vi đã đặt tên được xác định là một cái gì đó giống như công thức này
| 1 | = A1: B2, A5: B6 |
thì kỹ thuật trên sẽ dẫn đến lỗi.
Ghi chú bổ sung
Sử dụng Hàm ADDRESS để tạo địa chỉ từ một số hàng và cột nhất định. Quan trọng: Bạn phải nhập cột con số. Nhập ký tự cột sẽ tạo ra lỗi. Nếu cần, bạn có thể sử dụng Hàm Cột để tính số cột cho một tham chiếu ô.
Abs_num cho phép bạn chuyển đổi các tham chiếu ô tuyệt đối và tương đối.
1,2,3,4 a1, $ a $ 2… tương đối / tuyệt đối, v.v…
Sau đó cho biết sử dụng a1 hay R1C1. Chế độ a1 là chế độ tiêu chuẩn trong đó các ô được tham chiếu bởi ký tự cột & số hàng của chúng (ví dụ: a4). Chế độ R1C1 là nơi các ô được tham chiếu bởi số hàng và cột của chúng (ví dụ: R4C1). a1 là chế độ mặc định. Sử dụng điều này trừ khi bạn có lý do chính đáng để không.
Trong đối số cuối cùng, bạn có thể nhập tên trang tính nếu tham chiếu ô nằm trên một trang tính khác. Nhập tên trang tính được bao quanh bởi dấu ngoặc đơn (ví dụ: “sheet3”).
Quay lại danh sách tất cả các hàm trong Excel
ĐỊA CHỈ chức năng trong Google Trang tính
Hàm ADDRESS hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel.
