Excel Column Number To Letter: Hướng Dẫn Chi Tiết, Thuật Toán Và Ứng Dụng Thực Tiễn

Trong môi trường làm việc với Microsoft Excel, việc chuyển đổi giữa số cột (column number) và định dạng chữ cái (column letter) là một thao tác thường gặp, đặc biệt khi làm việc với các macro VBA, Power Query, Python, hoặc các ngôn ngữ lập trình khác như JavaScript, C#, Java. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết từ cơ bản đến nâng cao, bao gồm:

1. Giải thích cơ chế đặt tên cột trong Excel
2. Thuật toán chuyển đổi số cột sang chữ cái (Number → Letter) và ngược lại (Letter → Number)
3. Cài đặt ví dụ thực tế bằng VBA, Python, JavaScript, C# và Power Query (M)
4. Các trường hợp đặc biệt: cột vượt quá “ZZZ”, xử lý lỗi, và tối ưu hiệu suất
5. Ứng dụng thực tiễn: tự động tạo công thức, tạo báo cáo động, xây dựng UI tùy chỉnh, và tích hợp với các hệ thống ERP/CRM.

Mục tiêu: Sau khi đọc xong, bạn sẽ nắm vững cách chuyển đổi một cách đúng đắn, nhanh chóng và an toàn trong mọi ngôn ngữ lập trình và công cụ hỗ trợ Excel.

Có thể bạn quan tâm: Học Excel Cho Người Mới Bắt Đầu: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

1. Cơ chế Đặt Tên Cột trong Excel

1.1. Hệ thống đánh số “A‑Z” (Base‑26)

Excel sử dụng một hệ thống đánh số dạng base‑26 (cơ số 26) để biểu diễn các cột. Tuy nhiên, không giống như hệ thống thập phân (0‑9) hay hệ thống hexa (0‑F), Excel không có ký tự “0”. Thay vào đó, các ký tự A‑Z đại diện cho các giá trị 1‑26.

Khi giá trị vượt quá 26, Excel bắt đầu tạo chuỗi ký tự: AA, AB, …, AZ, BA, …, ZZ, AAA, …

1.2. Quy tắc Đổi “Số → Chữ” (Number → Letter)

• Bước 1: Trừ 1 khỏi số cột (để chuyển từ 1‑based sang 0‑based).
• Bước 2: Lấy phần dư (mod 26) để xác định ký tự cuối cùng.
• Bước 3: Chia nguyên (int division) cho 26 để tiếp tục tính các ký tự phía trước.
• Bước 4: Lặp lại cho đến khi phần nguyên = 0.
• Bước 5: Đảo ngược chuỗi ký tự thu được (vì ký tự tính được lần cuối là ký tự bên phải).

1.3. Quy tắc Đổi “Chữ → Số” (Letter → Number)

Đối với ngược lại, ta thực hiện phép tính trong hệ base‑26:

value = 0
for each character c in string (từ trái sang phải): value = value 26 + (ord(c) - ord('A') + 1)

Kết quả là số cột (1‑based).

Có thể bạn quan tâm: Cách Chuyển Dòng Thành Cột Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Thuật Toán Chi Tiết (Number → Letter)

2.1. Pseudocode

function columnNumberToLetter(n): if n <= 0: raise Error "Column number must be positive" result = "" while n > 0: n = n - 1 // chuyển sang 0‑based remainder = n % 26 letter = chr(ord('A') + remainder) result = letter + result // prepend n = n // 26 return result

2.2. Giải Thích Bước

Kết quả: 28 → AB.

2.3. Độ Phức Tạp

• Thời gian: O(log₍₂₆₎ n) – số vòng lặp tỉ lệ log cơ số 26 của n.
• Không gian: O(log₍₂₆₎ n) – chuỗi ký tự trả về.

Có thể bạn quan tâm: Excel Cannot Run The Macro – Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết

3. Thuật Toán Ngược (Letter → Number)

3.1. Pseudocode

function columnLetterToNumber(s): s = s.upper() result = 0 for each character c in s: if c < 'A' or c > 'Z': raise Error "Invalid column letter" result = result 26 + (ord(c) - ord('A') + 1) return result

3.2. Ví dụ

• Input: “AB”
• A → result = 026 + 1 = 1
• B → result = 126 + 2 = 28

Kết quả: AB → 28.

4. Cài Đặt Thực Tế trong Các Ngôn Ngữ

4.1. VBA (Macro trong Excel)

'--- Number → Letter -------------------------------------------------
Function ColNumToLetter(ByVal colNum As Long) As String Dim result As String Dim remainder As Long If colNum <= 0 Then Err.Raise vbObjectError + 1, , "Column number must be > 0" Do While colNum > 0 colNum = colNum - 1 remainder = colNum Mod 26 result = Chr(65 + remainder) & result ' 65 = Asc("A") colNum = colNum \ 26 Loop ColNumToLetter = result
End Function '--- Letter → Number -------------------------------------------------
Function ColLetterToNum(ByVal colLetter As String) As Long Dim i As Long, result As Long, ch As Long colLetter = UCase(colLetter) For i = 1 To Len(colLetter) ch = Asc(Mid(colLetter, i, 1)) If ch < 65 Or ch > 90 Then Err.Raise vbObjectError + 2, , "Invalid column letter" result = result 26 + (ch - 64) ' 64 = Asc("A") - 1 Next i ColLetterToNum = result
End Function

Lưu ý: VBA sử dụng Long (32‑bit) có giới hạn 2,147,483,647, tương đương cột XFD (16384). Nếu cần vượt quá, dùng Double hoặc Currency.

4.2. Python

def num_to_letter(n: int) -> str: if n < 1: raise ValueError("Column number must be positive") result = while n: n -= 1 n, rem = divmod(n, 26) result.append(chr(65 + rem)) # 65 = ord('A') return ''.join(reversed(result)) def letter_to_num(s: str) -> int: s = s.upper() result = 0 for ch in s: if not ('A' <= ch <= 'Z'): raise ValueError("Invalid column letter") result = result 26 + (ord(ch) - 64) # 64 = ord('A') - 1 return result # Demo
print(num_to_letter(28)) # AB
print(letter_to_num('AB')) # 28

4.3. JavaScript (Node.js / Browser)

function colNumToLetter(n) { if (n < 1) throw new Error('Column number must be > 0'); let result = ''; while (n > 0) { n--; // 0‑based result = String.fromCharCode(65 + (n % 26)) + result; n = Math.floor(n / 26); } return result;
} function colLetterToNum(str) { str = str.toUpperCase(); let result = 0; for (let i = 0; i < str.length; i++) { const code = str.charCodeAt(i); if (code < 65 || code > 90) throw new Error('Invalid column letter'); result = result 26 + (code - 64); } return result;
} // Example
console.log(colNumToLetter(702)); // ZZ
console.log(colLetterToNum('AAA')); // 703

4.4. C# (.NET)

public static class ExcelColumnHelper
{ // Number → Letter public static string NumberToLetter(int number) { if (number < 1) throw new ArgumentOutOfRangeException(nameof(number)); var sb = new System.Text.StringBuilder(); while (number > 0) { number--; // 0‑based sb.Insert(0, (char)('A' + (number % 26))); number /= 26; } return sb.ToString(); } // Letter → Number public static int LetterToNumber(string letters) { if (string.IsNullOrWhiteSpace(letters)) throw new ArgumentException("Column letters cannot be empty.", nameof(letters)); int result = 0; foreach (char c in letters.ToUpper()) { if (c < 'A' || c > 'Z') throw new ArgumentException("Invalid character in column letters.", nameof(letters)); result = result 26 + (c - 'A' + 1); } return result; }
}

4.5. Power Query (M Language)

// Number → Letter
NumberToLetter = (n as number) as text =>
let fn = (x as number, acc as text) => if x = 0 then acc else let y = Number.IntegerDivide(x - 1, 26), r = Number.Mod(x - 1, 26), letter = Character.FromNumber(65 + r) in @fn(y, letter & acc)
in fn(n, ""); // Letter → Number
LetterToNumber = (s as text) as number =>
let chars = Text.ToList(Text.Upper(s)), fn = (list as list, acc as number) => if List.IsEmpty(list) then acc else let head = List.First(list), value = Character.ToNumber(head) - 64, newAcc = acc 26 + value in @fn(List.Skip(list,1), newAcc)
in fn(chars, 0);

5. Xử Lý Các Trường Hợp Đặc Biệt

Có thể bạn quan tâm: Excel Color Based On Value: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

5.1. Cột Vượt Quá “ZZZ”

Excel phiên bản hiện tại (Office 365/2026) hỗ trợ tối đa 16,384 cột, tương đương XFD (cột 16384). Tuy nhiên, thuật toán trên không có giới hạn nội tại; nó có thể tạo ra các chuỗi vô hạn như AAAA, AAAB, … cho các số lớn hơn. Khi tích hợp vào các công cụ ngoài Excel, bạn cần:

• Kiểm tra giới hạn: nếu colNumber > 16384 → trả về lỗi hoặc cắt ngắn.
• Tùy chỉnh: trong hệ thống nội bộ, có thể mở rộng lên 26^k cột tùy theo yêu cầu.

5.2. Kiểm Tra Đầu Vào

• Số âm hoặc 0 → không hợp lệ, ném ngoại lệ.
• Chuỗi rỗng, ký tự không phải A‑Z → lỗi định dạng.
• Khoảng trắng → trim trước khi xử lý.

5.3. Tối Ưu Hiệu Suất

• Cache: Nếu bạn phải thực hiện hàng nghìn chuyển đổi trong một vòng lặp, hãy caching các kết quả đã tính (dictionary).
• Vectorization (Python/Numpy): Khi làm việc với mảng lớn, dùng np.vectorize hoặc list comprehension để tận dụng C‑level loops.

import numpy as np
vec_num_to_letter = np.vectorize(num_to_letter)
vec_letter_to_num = np.vectorize(letter_to_num)

6. Ứng Dụng Thực Tiễn

6.1. Tự Động Tạo Công Thức Động

Giả sử bạn có một bảng dữ liệu và muốn tạo công thức =SUM(A2:A100) cho cột bất kỳ dựa trên chỉ số cột được nhập vào. Bạn có thể:

Dim col As Long: col = 5 'E
Dim formula As String
formula = "=SUM(" & ColNumToLetter(col) & "2:" & ColNumToLetter(col) & "100)"
Range("G1").Formula = formula

6.2. Đọc Dữ Liệu Từ CSV/DB

Khi nhập dữ liệu từ nguồn không phải Excel (CSV, SQL), các hệ thống thường trả về số cột. Khi cần ghi lại vào Excel, chuyển sang letter để đặt Header hoặc sử dụng Range("A1").Offset(0, colNum-1).

6.3. Tích Hợp Với ERP/CRM

Nhiều ERP xuất báo cáo dưới dạng Excel, trong đó các định danh cột được lưu dưới dạng số. Việc chuyển đổi sang chữ giúp:

• Đánh dấu cột trong UI (ví dụ: “Cột D – Doanh thu”).
• Tạo macro tự động cập nhật dữ liệu dựa trên cấu hình lưu trong DB (cột 12 → L).

6.4. Xây Dựng UI Tùy Chỉnh trong Excel

Bạn có thể tạo UserForm cho phép người dùng nhập “Cột B” hoặc “2”. Sử dụng hàm ColLetterToNum và ColNumToLetter để chuẩn hoá đầu vào và hiển thị lại một cách nhất quán.

6.5. Kiểm Tra Tính Đúng Đắn Khi Tự Động Hoá

Khi viết script tự động (Python + openpyxl, pandas), việc sai lệch chuyển đổi có thể gây:

• Ghi dữ liệu vào cột sai → Mất dữ liệu.
• Lỗi công thức → #REF!.

Vì vậy, luôn kiểm thử với các giá trị biên: 1, 26, 27, 52, 702, 703, 16384.

7. Kiểm Thử và Ví Dụ Thực Hành

7.1. Bảng Kiểm Thử (Số → Chữ)

7.2. Bảng Kiểm Thử (Chữ → Số)

7.3. Unit Test (Python – pytest)

import pytest @pytest.mark.parametrize("num,letter", (1, "A"), (26, "Z"), (27, "AA"), (52, "AZ"), (53, "BA"), (702, "ZZ"), (703, "AAA"), (16384, "XFD")
)
def test_num_to_letter(num, letter): assert num_to_letter(num) == letter @pytest.mark.parametrize("letter,num", ("A",1), ("Z",26), ("AA",27), ("AZ",52), ("BA",53), ("ZZ",702), ("AAA",703), ("XFD",16384)
)
def test_letter_to_num(letter, num): assert letter_to_num(letter) == num

Chạy pytest để xác nhận tính đúng đắn.

8. Khi Nào Nên Tránh Sử Dụng Chữ Cột?

• Khi làm việc với bảng dữ liệu lớn (hàng > 1 triệu), việc dựa vào địa chỉ A1 có thể gây giảm hiệu suất trong một số API (ví dụ: Range("A1") vs Cells(row, col)).
• Trong công thức: INDEX($A$1:$Z$1000, row, col) thường sử dụng số cột để tính nhanh hơn.
• Với thư viện: openpyxl cho phép truy cập bằng ws.cell(row=row, column=col). Không cần chuyển sang chữ.

9. Tổng Kết

• Excel sử dụng hệ thống base‑26 không có 0, dẫn tới thuật toán trừ 1 trước khi tính phần dư.
• Thuật toán Number → Letter và Letter → Number đều có độ phức tạp O(log₍₂₆₎ n) và dễ triển khai trong mọi ngôn ngữ.
• Mã mẫu đã được cung cấp cho VBA, Python, JavaScript, C#, Power Query, kèm ví dụ thực tiễn và kiểm thử.
• Xử lý ngoại lệ và giới hạn cột là những yếu tố quan trọng để tránh lỗi trong môi trường sản xuất.
• Ứng dụng: tự động tạo công thức, xây dựng UI, tích hợp ERP, và tối ưu hiệu suất khi làm việc với dữ liệu lớn.

Hy vọng bài viết này đã cung cấp cho bạn công cụ toàn diện để chuyển đổi Excel column number ↔ letter một cách nhanh chóng, an toàn và hiệu quả trong mọi dự án. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.