Trong hướng dẫn này, chúng ta sẽ xem xét cách thay đổi số cột Excel thành các ký tự chữ cái tương ứng.
Khi xây dựng các công thức phức tạp trong Excel, đôi khi bạn có thể cần lấy một chữ cái cột của một ô cụ thể hoặc từ một số nhất định. Điều này có thể được thực hiện theo hai cách: bằng cách sử dụng các chức năng sẵn có hoặc một chức năng tùy chỉnh.
Cách chuyển đổi số cột thành bảng chữ cái [cột một chữ cái]
Trong trường hợp tên cột bao gồm một chữ cái, từ A đến Z, bạn có thể lấy nó bằng cách sử dụng công thức đơn giản này:
Char [64 + col_number]
Ví dụ: để chuyển đổi số 10 thành chữ cái, công thức là:
=CHAR[64 + 10]
Cũng có thể nhập một số vào một số ô và tham khảo ô đó vào công thức của bạn:
=CHAR[64 + A2]
Công thức này hoạt động như thế nào:
Hàm Char trả về một ký tự dựa trên mã ký tự trong bộ ASCII. Các giá trị ASCII của các chữ cái viết hoa của bảng chữ cái tiếng Anh là 65 [a] đến 90 [z]. Vì vậy, để có được mã ký tự của chữ A, bạn thêm 1 đến 64; Để có được mã ký tự của chữ B, bạn thêm 2 đến 64, v.v.
Cách chuyển đổi số cột Excel thành chữ cái [bất kỳ cột nào]
Nếu bạn đang tìm kiếm một công thức đa năng hoạt động cho bất kỳ cột nào trong Excel [1 chữ cái, 2 chữ cái và 3 chữ cái], thì bạn sẽ cần sử dụng một cú pháp phức tạp hơn một chút:
Thay thế [địa chỉ [1, col_number, 4], "1", ""]
Với chữ cái trong A2, công thức có biểu mẫu này:
=SUBSTITUTE[ADDRESS[1, A2, 4], "1", ""]
Công thức này hoạt động như thế nào:
Hàm Char trả về một ký tự dựa trên mã ký tự trong bộ ASCII. Các giá trị ASCII của các chữ cái viết hoa của bảng chữ cái tiếng Anh là 65 [a] đến 90 [z]. Vì vậy, để có được mã ký tự của chữ A, bạn thêm 1 đến 64; Để có được mã ký tự của chữ B, bạn thêm 2 đến 64, v.v.
- Cách chuyển đổi số cột Excel thành chữ cái [bất kỳ cột nào]
- Nếu bạn đang tìm kiếm một công thức đa năng hoạt động cho bất kỳ cột nào trong Excel [1 chữ cái, 2 chữ cái và 3 chữ cái], thì bạn sẽ cần sử dụng một cú pháp phức tạp hơn một chút:
- Thay thế [địa chỉ [1, col_number, 4], "1", ""]
Với chữ cái trong A2, công thức có biểu mẫu này:
Đầu tiên, bạn xây dựng một địa chỉ ô với số lượng quan tâm. Đối với điều này, cung cấp các đối số sau cho chức năng địa chỉ:
1 cho ROW_NUM [số hàng không thực sự quan trọng, vì vậy bạn có thể sử dụng bất kỳ].
A2 [ô chứa số cột] cho cột_num.
4 cho đối số ABS_NUM để trả về một tham chiếu tương đối.
Với các tham số trên, hàm địa chỉ trả về chuỗi văn bản "A1" là kết quả. Function ColumnLetter[col_num] ColumnLetter = Split[Cells[1, col_num].Address, "$"][1] End Function
Vì chúng tôi chỉ cần một chữ cái cột, chúng tôi loại bỏ số hàng với sự trợ giúp của hàm thay thế, tìm kiếm "1" [hoặc bất kỳ số hàng nào bạn mã hóa bên trong hàm địa chỉ] trong văn bản "A1" và thay thế nó bằng một Chuỗi trống [""].
Nhận chữ cái cột từ số cột bằng chức năng tùy chỉnh tùy chỉnh chức năng
Nếu bạn cần chuyển đổi số cột thành các ký tự bảng chữ cái một cách thường xuyên, thì hàm do người dùng tùy chỉnh [UDF] có thể tiết kiệm thời gian của bạn rất nhiều.
ColumnLetter[col_num]
Mã của hàm khá đơn giản và đơn giản:
Cột công khai Cột [col_num] Cột
=ColumnLetter[A2]
Ở đây, chúng tôi sử dụng thuộc tính ô để tham khảo một ô trong hàng 1 và số cột được chỉ định và thuộc tính địa chỉ để trả về một chuỗi chứa một tham chiếu tuyệt đối cho ô đó [chẳng hạn như $ 1 $ 1]. Sau đó, hàm phân chia phá vỡ chuỗi được trả về thành các phần tử riêng lẻ bằng cách sử dụng dấu $ làm dấu phân cách và chúng ta trả về phần tử [1], đó là chữ cái cột.
Dán mã vào trình soạn thảo VBA và chức năng cột mới của bạn đã sẵn sàng để sử dụng. Để biết hướng dẫn chi tiết, vui lòng xem: Cách chèn mã VBA vào Excel.
Từ quan điểm của người dùng cuối, cú pháp của chức năng đơn giản như thế này:
Trong đó col_num là số cột mà bạn muốn chuyển đổi thành một chữ cái.
Công thức thực sự của bạn có thể trông như sau:
=SUBSTITUTE[ADDRESS[1, COLUMN[C5], 4], "1", ""]
Và nó sẽ trả về chính xác các kết quả giống như các hàm Excel gốc được thảo luận trong ví dụ trước:
Cách nhận chữ cái của một số tế bào nhất định
Để xác định một chữ cái cột của một ô cụ thể, hãy sử dụng chức năng cột để truy xuất số cột và phục vụ số đó cho hàm địa chỉ. Công thức hoàn chỉnh sẽ có hình dạng này:
=SUBSTITUTE[ADDRESS[1, COLUMN[], 4], "1", ""]
Thay thế [địa chỉ [1, cột [cell_address], 4], "1", ""]]
Ví dụ, hãy tìm một chữ cái của ô C5:
Rõ ràng, kết quả là "C" :]
Cách nhận chữ cái của ô hiện tại
Để tìm ra chữ cái của ô hiện tại, công thức gần giống như trong ví dụ trên. Sự khác biệt duy nhất là hàm cột [] được sử dụng với một đối số trống để chỉ ô có công thức:
Để thuận tiện, hãy chia toàn bộ quá trình thành 3 bước dễ theo dõi.
- Chuyển đổi số cột thành chữ cái
Với số cột trong H3, sử dụng công thức đã quen thuộc để thay đổi nó thành ký tự bảng chữ cái:
=SUBSTITUTE[ADDRESS[1, H3, 4], "1", ""]
Mẹo. Nếu số trong tập dữ liệu của bạn không khớp với số cột, hãy chắc chắn thực hiện hiệu chỉnh cần thiết. Ví dụ: nếu chúng ta có dữ liệu tuần 1 trong cột B, dữ liệu tuần 2 trong cột C, v.v., thì chúng ta sẽ sử dụng H3+1 để có được số cột chính xác. If the number in your dataset does not match the column number, be sure to make the required correction. For example, if we had the week 1 data in column B, the week 2 data in column C, and so on, then we'd use H3+1 to get the correct column number.
- Xây dựng một chuỗi đại diện cho một tham chiếu phạm vi
Để xây dựng một tham chiếu phạm vi dưới dạng một chuỗi, bạn kết hợp chữ cái cột được trả về bởi công thức trên với các số hàng đầu tiên và cuối cùng. Trong trường hợp của chúng tôi, các ô dữ liệu là trong các hàng từ 3 đến 8, vì vậy chúng tôi đang sử dụng công thức này:
=SUBSTITUTE[ADDRESS[1, H3, 4], "1", ""] & "3:" & SUBSTITUTE[ADDRESS[1, H3, 4], "1", ""] & "8"Cho rằng H3 chứa "3", được chuyển đổi thành "C", công thức của chúng tôi trải qua quá trình chuyển đổi sau:
________số 8
Và tạo ra chuỗi C3: C8.
- Tạo tham chiếu phạm vi động
Để chuyển đổi chuỗi văn bản thành một tham chiếu hợp lệ mà Excel có thể hiểu được, hãy tổ chức công thức trên trong hàm gián tiếp, sau đó chuyển nó đến đối số thứ 3 của Xlookup:
=XLOOKUP[H2, E3:E8, INDIRECT[H4], "Not found"]
Để loại bỏ một ô bổ sung chứa chuỗi phạm vi trả về, bạn có thể đặt công thức địa chỉ thay thế trong chính hàm gián tiếp:
=CHAR[64 + A2]0
Với chức năng cột tùy chỉnh của chúng tôi, bạn có thể nhận được một giải pháp nhỏ gọn và thanh lịch hơn:
=CHAR[64 + A2]1
Đó là cách tìm một chữ cái cột từ một số trong Excel. Tôi cảm ơn bạn đã đọc và mong được gặp bạn trên blog của chúng tôi vào tuần tới!
Thực hành sổ làm việc để tải xuống
Số cột Excel thành chữ cái - ví dụ [tệp .xlsm]