Gán số cho chữ excel

Trong ví dụ này, mục tiêu là chuyển đổi một số bình thường thành tham chiếu cột được biểu thị bằng chữ cái. Ví dụ: số 1 sẽ trả về "A", số 2 sẽ trả về "B", số 26 sẽ trả về "Z", v.v. Thách thức là Excel có thể xử lý trên 16.000 cột nên số lượng tổ hợp chữ lớn. Một cách để giải quyết vấn đề này là xây dựng một địa chỉ hợp lệ với số và chỉ trích xuất cột từ địa chỉ. Đây là cách tiếp cận được giải thích dưới đây. Để tham khảo, công thức trong C5 là

=SUBSTITUTE(ADDRESS(1,B5,4),"1","")

hàm ĐỊA CHỈ

Làm việc từ trong ra ngoài, bước đầu tiên là tạo địa chỉ chứa tham chiếu cột chính xác. Chúng ta có thể làm điều này với hàm ADDRESS, hàm này sẽ trả về địa chỉ của một ô dựa trên số hàng và cột đã cho. Ví dụ

=ADDRESS(1,1) // returns "$A$1"
=ADDRESS(1,2) // returns "$B$1"
=ADDRESS(1,26) // returns "$Z$1"

Bằng cách cung cấp 4 cho đối số abs_num tùy chọn, chúng ta có thể nhận được một tham chiếu tương đối

=ADDRESS(1,1,4) // returns "A1"
=ADDRESS(1,2,4) // returns "B1"
=ADDRESS(1,26,4) // returns "Z1"

Lưu ý rằng kết quả từ ADDRESS luôn là một chuỗi văn bản. Chúng tôi không đặc biệt quan tâm đến số hàng, chúng tôi chỉ quan tâm đến số cột, vì vậy chúng tôi sử dụng 1 cho row_num trong mọi trường hợp. Trong bảng tính được hiển thị, chúng tôi lấy số cột từ cột B và sử dụng 1 cho số hàng như thế này

ADDRESS(1,B5,4)

Khi công thức được sao chép xuống, ADDRESS tạo một địa chỉ hợp lệ bằng cách sử dụng mỗi số trong cột B. Số lượng cột tối đa trong một trang tính Excel là 16.384, vì vậy, cột cuối cùng trong một trang tính là "XFD"

hàm THAY THẾ

Bây giờ chúng tôi có một địa chỉ với tham chiếu cột mà chúng tôi muốn, chúng tôi chỉ cần xóa số hàng. Một cách để thực hiện việc này là sử dụng hàm SUBSTITUTE. Ví dụ: giả sử chúng tôi có một địa chỉ như "A1", chúng tôi có thể sử dụng SUBSTITUTE như thế này

=SUBSTITUTE("A1","1","") // returns "A"

Chúng tôi yêu cầu SUBSTITUTE tìm kiếm "1" và thay thế nó bằng một chuỗi trống (""). Chúng tôi có thể tự tin làm điều này trong mọi trường hợp, vì chúng tôi đã mã hóa cứng số hàng là 1 bên trong hàm ADDRESS. Công thức cuối cùng trong C5 là

=SUBSTITUTE(ADDRESS(1,B5,4),"1","")

Tóm lại, ADDRESS tạo tham chiếu ô và trả kết quả về SUBSTITUTE, loại bỏ "1"

Hàm TEXTBEFORE

Một cách rõ ràng hơn để trích xuất tham chiếu cột từ địa chỉ là sử dụng hàm TEXTBEFORE như thế này

=TEXTBEFORE(ADDRESS(1,B5,4),"1")

Ở đây, chúng tôi coi "1" là dấu phân cách và yêu cầu TEXTBEFORE cho tất cả văn bản trước dấu phân cách. Kết quả từ công thức này giống như trên

Đây là một điều quan trọng cần làm khi làm việc với các công thức phức tạp trong Excel, đặc biệt là hàm INDIRECT()

phần

Công thức cơ bản - Hoạt động từ A đến Z

công thức

A1 là ô chứa số cột mà bạn muốn lấy ký tự

Kết quả

Bạn cũng có thể viết công thức này như thế này

Bạn chỉ cần nhập số cho cột trực tiếp trong công thức

Hàm CHAR() nhận một chữ cái dựa trên số của nó như được xác định bởi bộ ký tự của máy tính của bạn;

Sử dụng COLUMN()

Nếu bạn đang tạo một công thức động, bạn có thể muốn tự động tìm ra chữ cái của một cột cụ thể để sử dụng trong một hàm khác, chẳng hạn như hàm INDIRECT()

Trong trường hợp này, bạn có thể sử dụng hàm COLUMN() để lấy số của một cột cụ thể hoặc cột hiện tại

Điều này sẽ nhận được chữ cái cho cột cho ô A1, đó là A

Xóa tham chiếu ô khỏi hàm COLUMN() và bạn sẽ nhận được ký tự của cột hiện tại

Kết quả

Tuy nhiên, hãy nhớ rằng điều này sẽ chỉ hoạt động đối với các cột từ A đến Z và trong một số trường hợp, điều này có thể không hoạt động tùy thuộc vào cài đặt cục bộ của máy tính. Ví dụ tiếp theo không có những hạn chế này

Công thức phức tạp hơn - Hoạt động cho tất cả các cột

Ví dụ này linh hoạt hơn ở chỗ nó hoạt động cho tất cả các cột trong Excel nhưng phức tạp hơn một chút

công thức

=SUBSTITUTE(ADDRESS(1,A3,4),"1","")

A3 là ô ví dụ ở đây chứa số cột mà chúng tôi muốn nhận (các) chữ cái

Kết quả

Công thức này sử dụng các hàm SUBSTITUTE() và ADDRESS() để lấy các ký tự cột. Đây là một mẹo nhỏ gọn gàng cho phép bạn lấy các chữ cái cho bất kỳ cột nào chỉ bằng cách sử dụng một số làm tham chiếu cho cột đó

Hàm ADDRESS() cung cấp cho chúng ta địa chỉ của một ô dựa trên số cột và hàng và hàm SUBSTITUTE() thay thế phần số của tham chiếu ô bằng không, hàm này sẽ loại bỏ số. Nếu bạn muốn thấy điều này trong Excel, chỉ cần tách hai hàm này thành ô riêng của chúng và xem kết quả

Sử dụng COLUMN()

(cái này gần giống như phần COLUMN() ở trên, chỉ khác ở công thức mới)

Nếu bạn đang tạo một công thức động, bạn có thể muốn tự động tìm ra chữ cái của một cột cụ thể để sử dụng trong một hàm khác, chẳng hạn như hàm INDIRECT()

Trong trường hợp này, bạn có thể sử dụng hàm COLUMN() để lấy số của một cột cụ thể hoặc cột hiện tại

________số 8

Điều này sẽ nhận được chữ cái cho cột cho tham chiếu ô A1, đó là A

Bạn cũng có thể xóa tham chiếu ô khỏi hàm COLUMN() để lấy (các) chữ cái cho cột chứa công thức hiện tại

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

Điều này sẽ trả về D vì công thức nằm trong cột D

Một vài ví dụ cuối cùng này có vẻ tầm thường, nhưng chúng rất hữu ích khi bạn sao chép công thức vào một ô và cột không xác định, vì bạn không biết công thức sẽ ở cột nào khi nó được sử dụng hoặc sao chép

ghi chú

Đây là một điều mạnh mẽ để có thể thực hiện trong Excel, lấy (các) ký tự cột từ số của chúng. Bạn sẽ không sử dụng công cụ này hàng ngày và nó có thể không giúp ích cho những người dùng Excel cơ bản, nhưng đối với tất cả các bạn muốn hoặc cần nâng cấp Excel lên một tầm cao mới, hãy lưu hướng dẫn này, đặc biệt là đối với công thức thứ hai, công thức sử dụng SUBSTITUTE