Lập trình VBA với Column: chuyển chỉ số cột thành chữ cái

levanduyet · 17/10/08

Đôi khi trong lập trình VBA các bạn cần chuyển số cột sang chữ. Hàm sau sẽ giúp các bạn:

Mã:

Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

Sưu tầm

Lê Văn Duyệt

rollover79 · 18/10/08

ndu96081631 đã viết:
Với cách tính toán trên, cho dù mai này bác Bill có phát triển bảng tính lên đến cở 1 triệu cột thì vẩn tính ra được như thường (Nếu như bác vẩn giữ cách đánh tên cột theo thứ tự như hiện nay)
Bài toán này không chỉ dùng để lấy Column Letter mà còn có thể ứng dụng vào nhiều việc khác (ví dụ đánh MÃ tự động) tùy theo khả năng phát triển của mổi người
Ghi chú: Nếu các bạn cãm thấy dùng hàm Log quá phức tạp thì có thể IF, chẳng hạn:

PHP:

If ColIndex < 27 then Qty = 1 ElseIf: ColIndex < 703 then Qty = 2 Else: Qty = 3 End If

(Tuy nhiên tôi chả bao giờ khoái mấy vụ IF này, trừ những trường hợp bất khả kháng)
Các bạn xem file đính kèm và góp ý thêm nhé (File được xây dựng trên Excel2007 để có thể test với chỉ số cột > 256)

Bác chơi khó người khác thế, up file xlsm mà không có OFF2007 thì khóc à? Bài toán này giờ đâu còn liên quan đến cột nữa đâu. Không rõ OFF2007 có gì khác không, nhưng ở trên OFF2003 thì code của bác chỉ có thể tối đa 3 ký tự số mà thôi, không nhiều hơn được. Cụ thể nhé, 2 công thức sau sẽ cho ra cùng 1 kết quả

Mã:

=ColLetter(26*26*26)
=ColLetter(26*26*26*26)

Nó đều cho kết quả là YYZ, và những số lớn hơn cũng thế.
(Hình như hạn chế là ở cái Ch3,Ch2,Ch1)

ndu96081631 · 18/10/08

rollover79 đã viết:
Bác chơi khó người khác thế, up file xlsm mà không có OFF2007 thì khóc à? Bài toán này giờ đâu còn liên quan đến cột nữa đâu. Không rõ OFF2007 có gì khác không, nhưng ở trên OFF2003 thì code của bác chỉ có thể tối đa 3 ký tự số mà thôi, không nhiều hơn được. Cụ thể nhé, 2 công thức sau sẽ cho ra cùng 1 kết quả

Mã:

=ColLetter(26*26*26) =ColLetter(26*26*26*26)

Nó đều cho kết quả là YYZ, và những số lớn hơn cũng thế.

Thì có code đó mà bạn! Tự đưa vào file là xong mà
Vì hiện tại ta đang xét có 3 ký tự nên tôi làm vậy, nếu thêm nữa thì cứ thêm... Ẹc... Ẹc... (Ch3 hoặc Ch4 hoặc Ch5 gì đó... )
Test thì phải có bằng chứng cụ thể... Mình chỉ nói ColIndex(10000) = "cái gì đó" <--- Ai mà tin, nên đưa file Excel2007 lên chủ yếu để minh họa
quả đúng mấy cái Ch1, Ch2, Ch3 chính là hạn chế (vì trình độ tôi mới tới đó... Thông cãm)

phamduylong · 19/10/08

ndu96081631 đã viết:
Tôi cũng có 1 cách theo thuật toán hoàn toàn khác: "Chuyển đổi ColIndex từ hệ đếm cơ số 10 sang hệ đếm cơ số 26"

Lúc đầu, tôi cũng nghĩ đơn giản là bài này có gì khó, chỉ chuyển từ cơ số 10 sang cơ số 26 (với cơ số 26 chữ số không phải là số mà là 26 chữ cái A > Z). Nhưng không phài như vậy !
Cơ số 10: gồm các chữ số : 1 2 3 4 5 6 7 8 9 0
Cơ số 26 (26 chữ cái): gồm các ký tự : A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Vai trò của số 0 và ký tự Z như nhau.
Sự khác nhau giữa CS26 (26 chữ cái) và tên cột

Số hệ 10

| 1

| 10

| 20

| 25

| 26

| 27

| 51

| 52

| 53

|
Số hệ 26 (chữ cái)|A|J|T|Y|AZ|AA|AY|BZ|BA|
Tên cột|A|J|T|Y|Z|AA|AY|AZ|BA|
Số hệ 26 <> Tên cột| | | | |AZ<>Z| | |BZ<>AZ| |

ndu96081631 · 19/10/08

phamduylong đã viết:
Lúc đầu, tôi cũng nghĩ đơn giản là bài này có gì khó, chỉ chuyển từ cơ số 10 sang cơ số 26 (với cơ số 26 chữ số không phải là số mà là 26 chữ cái A > Z). Nhưng không phài như vậy !
Cơ số 10: gồm các chữ số : 1 2 3 4 5 6 7 8 9 0
Cơ số 26 (26 chữ cái): gồm các ký tự : A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Vai trò của số 0 và ký tự Z như nhau.
Sự khác nhau giữa CS26 (26 chữ cái) và tên cột

Số hệ 10
| 1
| 10
| 20
| 25
| 26
| 27
| 51
| 52
| 53
|
Số hệ 26 (chữ cái)|A|J|T|Y|AZ|AA|AY|BZ|BA|
Tên cột|A|J|T|Y|Z|AA|AY|AZ|BA|
Số hệ 26 <> Tên cột| | | | |AZ<>Z| | |BZ<>AZ| |

Không phải đâu anh ơi... chuyển sang cơ số 26 thì anh phải xem chử A=0, B=1... Z=25 chứ
Chính vì vậy mà trong code có đoạn ColIndex - 1, mục đích để chuyển dảy số 1,2,... ,26 thành 0,1... ,25
Cơ số 10 thì số 0 nằm đầu tiên, số 9 cuối cùng ... tương tự thế cho tất cả các cơ số
Em thấy rất bình thường, đâu có khó khăn gì chứ

phamduylong · 19/10/08

ndu96081631 đã viết:
Không phải đâu anh ơi... chuyển sang cơ số 26 thì anh phải xem chử A=0, B=1... Z=25 chứ
Chính vì vậy mà trong code có đoạn ColIndex - 1, mục đích để chuyển dảy số 1,2,... ,26 thành 0,1... ,25
Cơ số 10 thì số 0 nằm đầu tiên, số 9 cuối cùng ... tương tự thế cho tất cả các cơ số
Em thấy rất bình thường, đâu có khó khăn gì chứ

Đúng là thuật toán của các bạn rất hay, gọn và vận dụng được vào bài toán chuyển stt cột thành tên cột. Thuật toán có điểm giống như chuyển cơ số nhưng không phải chuyển cơ số được vì cách chuyển tên cột khi đến bội 26 khác với chuyển cơ số 26.
Nếu là CS10 thì đến 10 là 2 chữ số, CS26 thì đến 26 là 2 chữ số. Nhưng tên cột 26 là Z. Nếu hiểu Z là 25 thì AA là cột thứ mấy khi hiểu A=0 > Z=25?
Có lẽ đi hơi lạc đề. Tôi đặt vấn đề này vì đã dùng hàm chuyển cơ số n sang cơ số m nhưng riêng bài toán này thì sai !
Kết thúc câu hỏi của tôi các bạn nhé !

ndu96081631 · 19/10/08

phamduylong đã viết:
Đúng là thuật toán của các bạn rất hay, gọn và vận dụng được vào bài toán chuyển stt cột thành tên cột. Thuật toán có điểm giống như chuyển cơ số nhưng không phải chuyển cơ số được vì cách chuyển tên cột khi đến bội 26 khác với chuyển cơ số 26.
Nếu là CS10 thì đến 10 là 2 chữ số, CS26 thì đến 26 là 2 chữ số. Nhưng tên cột 26 là Z. Nếu hiểu Z là 25 thì AA là cột thứ mấy khi hiểu A=0 > Z=25?
Có lẽ đi hơi lạc đề. Tôi đặt vấn đề này vì đã dùng hàm chuyển cơ số n sang cơ số m nhưng riêng bài toán này thì sai !
Kết thúc câu hỏi của tôi các bạn nhé !

Uh.. đúng.. Em nhầm... Nó giống với chuyển đổi cơ số nhưng không phải là chuyển cơ số... Vì nếu đúng thế thì sau A rồi đến B... sau Y rồi đến Z và sau Z phải là BA chứ không phải AA
Cãm ơn anh!

ptm0412 · 19/10/08

Chính xác quá, Thầy ạ. Em cứ nhìn thuật toán, rồi thử, thấy đúng, nên không để ý tên gọi của phương pháp. Té ra cái học của mình, ẹ quá. bây giờ thì hiểu:

Nếu hiểu Z là 25 thì AA là cột thứ mấy khi hiểu A=0 > Z=25?

BA sẽ là cột 10 theo cơ số 26. Còn AA = A = 0. Hị hị.

ndu96081631 · 19/10/08

Giờ để ý kỹ thấy trò này hao hao giống với hệ đếm BCD: Số 10 của BCD đúng bằng số 10 của hệ thập lục phân, nhưng sau số 9 là nó "nhãy cóc" lên 10 luôn (bỏ qua A, B, C, D, E và F)... Vậy nếu cho rằng thuật toán em làm ở trên là 1 hệ đếm thì nó đã bỏ qua số 0... tương đương A = 1, B = 2 và Z = 26
Nếu đúng thế thì:
AA đổi sang CS10 sẽ là: 1*26^1 + 1*26^0 = 27 ---> OK
Gần giống như khi ta đếm từ 1 đến 9 rồi "nhãy cóc" luôn đến 11 (bỏ qua 10)
Dù sao vẩn thấy cái vụ "đệ quy" gì đó của bạn Rollover79 rất độc chiêu, đơn giãn mà hiệu quả... Xem thuật toán thì hiểu, nhưng nếu nói "đệ quy" thì chẳng hiểu nó nghĩa là cái giống gì
(thổng cãm cho em bé chưa được đến trường)
Ẹc... Ec...

ptm0412 · 19/10/08

nhưng nếu nói "đệ quy" thì chẳng hiểu nó nghĩa là cái giống gì
(thổng cãm cho em bé chưa được đến trường)

Buồn quá, buồn quá! Đệ quy có giải thích sơ bộ ở đây, bài 132.
http://www.giaiphapexcel.com/forum/showthread.php?t=6354&page=7

Vậy đệ quy là gì?
Các đoạn code khi chuyển sang Function thì gọi là hàm. Nếu trong hàm có dòng lệnh tự gọi chính hàm này ra thực thi lần 2, lần 3, . . . thì gọi là đệ quy.
Trường hợp dùng đệ quy:
Trong quá trình tính toán, sau một vài lệnh ta có 1 kết quả hoặc kết quả tạm đang lưu giữ trong 1 biến nào đó, thí dụ biến Tam.
Ta lại thấy rằng để đi đến kết quả cuối cùng, ta phải dùng lại các câu lệnh trên 1 lần nữa nhưng không phải tính toán trên các tham số ban đầu mà là tính toán trên cái biến Tam này. Vậy ta dùng 1 câu lệnh gọi chính hàm này ra chạy, nhưng tham số lần này của hàm là biến Tam.

Lý thuyết là như vậy nhưng thực sự trong công việc hàng ngày của phần lớn nhân viên công sở, kể cả nhân viên kỹ thuật, cũng ít dùng.

Nói ngắn lại: Đệ quy là phương pháp dùng câu lệnh gọi lại chính mình (function)

Buồn quá, buồn quá! ai nói chưa học thì được chứ ndu ....

ndu96081631 · 19/10/08

ptm0412 đã viết:
Buồn quá, buồn quá! Đệ quy có giải thích sơ bộ ở đây, bài 132.
http://www.giaiphapexcel.com/forum/showthread.php?t=6354&page=7
Buồn quá, buồn quá! ai nói chưa học thì được chứ ndu ....

Em nói thật đấy! Vì em chưa từng bước chân vào đại học, chưa từng học tin học 1 ngày nào cho đến nơi đến chốn nên có cái đã biết nhưng "thuật ngữ" thì có lẽ chưa hề nghe qua bao giờ
Cũng giống như ngày xưa nghe nói Method, Properties... vân vân, chả biết nó là cái giống gì (dù đã có từng xài)
Ý em muốn nói vấn đề "thuật ngữ" đã gây khó khăn không ít cho những tay mới vào nghề như em... Nhiều khi các cao thủ nói 1 câu mà mình nghe giống y như họ đang bàn "đại sự" (biết trời trăng mây nước gì đâu mà hiểu)

cadafi · 19/10/08

ndu96081631 đã viết:
Em nói thật đấy! Vì em chưa từng bước chân vào đại học, chưa từng học tin học 1 ngày nào cho đến nơi đến chốn nên có cái đã biết nhưng "thuật ngữ" thì có lẽ chưa hề nghe qua bao giờ
Cũng giống như ngày xưa nghe nói Method, Properties... vân vân, chả biết nó là cái giống gì (dù đã có từng xài)
Ý em muốn nói vấn đề "thuật ngữ" đã gây khó khăn không ít cho những tay mới vào nghề như em... Nhiều khi các cao thủ nói 1 câu mà mình nghe giống y như họ đang bàn "đại sự" (biết trời trăng mây nước gì đâu mà hiểu)

Em cũng như anh thôi, thực sự không biết đệ quy là gì? Nhưng nhìn thuật toán hay quá! Đang nghiên cứu, thực sự chưa hiểu lắm! Cũng phải ráng học thôi! Việc học là vô bờ bến mà. Ẹc ... Ẹc...

jack nt · 16/9/09

tigertiger đã viết:
Tuy nhiên vấn đề lấy tên cột làm gì?

Vì nếu đã viết trong VBA thì chúng ta xử lý theo số hay hơn, và tiện lợi hơn ký tự nhiều, đó cũng lý do trong VBA các hàm các phép xử lý liên quan nhận dạng cột đều thể hiện ở số thứ tự.

1/ Tất nhiên là ta thường xử lý theo số, nhưng cũng có một số đoạn mã VBA dùng theo địa chỉ nên việc lấy tên cột là cần thiết.
2/ Mấy hàm sử dụng đến Cells... chỉ gọn khi viết trong sheet còn nếu đặt hàm trong Module (khai báo Public để dùng cho nhiều sheet) nếu không nói rõ sẽ hiểu là ActiveSheet do vậy phải khai báo thêm Sh as Object, ... khá là bất tiện
3/ Việc viết hàm ngắn gọn chưa chắc đã chạy nhanh hơn!
Vài lời chia sẻ
Jack NT

jack nt · 16/9/09

ndu96081631 đã viết:
Tuy nhiên tôi chả bao giờ khoái mấy vụ IF này, trừ những trường hợp bất khả kháng)

Bài của bác rất hay chỉ xin góp ý chút xíu:
Xem ra có vẻ bác vốn là dân chuyên toán hay dân toán luôn.
Bác không khoái mấy cái IF mà đi xài log có lẽ vì vẻ đẹp của công thức chăng. Nhưng tui lại e là chỉ gọi mỗi chữ log thôi nhưng máy phải chạy nhiều vòng hơn cái IF bác ạ.

ndu96081631 · 16/9/09

jack nt đã viết:
Bài của bác rất hay chỉ xin góp ý chút xíu:
Xem ra có vẻ bác vốn là dân chuyên toán hay dân toán luôn.
Bác không khoái mấy cái IF mà đi xài log có lẽ vì vẻ đẹp của công thức chăng. Nhưng tui lại e là chỉ gọi mỗi chữ log thôi nhưng máy phải chạy nhiều vòng hơn cái IF bác ạ.

Bạn nói cũng rất có lý ---> Có điều tôi rất ngại phải viết dài dòng ---> Càng ngắn tôi càng khoái
Còn về tốc độ thì đương nhiên tôi ưu tiên hàng đầu rồi, tuy nhiên chưa có điều kiện test thử ---> Bạn rảnh rồi làm thử 1 bài test xem kết quả thế nào nhé

minhtu · 17/9/09

Góp vui bằng 1 hàm sưu tầm (chắc của Tây):

Mã:

Function ExcelColNonRec(ByVal intCol As Long) As String
    While (intCol > 0)
        intCol = intCol - 1
        ExcelColNonRec = Chr(65 + (intCol Mod 26)) + ExcelColNonRec
        intCol = intCol \ 26
    Wend
End Function

Mình chuyển sang đệ quy để thấy đệ quy bản chất cũng là lặp (lâu rồi nên cũng quên định nghĩa đệ quy)

Mã:

Function ExcelNumberToColumnName(ByVal intCol As Long) As String
    If (intCol > 0) Then
        intCol = intCol - 1
        ExcelNumberToColumnName = ExcelNumberToColumnName(intCol \ 26) + Chr(65 + (intCol Mod 26))
    End If
End Function

Đại diện cho giải thuật đệ quy có lẽ là bài toán tính giai thừa cho dễ hình dung:

Mã:

Function Giaithua(n As Long) As Long
    If n > 1 Then Giaithua = n * Giaithua(n - 1) Else Giaithua = 1

ngochienbmt · 12/9/23

Nhờ các bác chỉ giúp ak, mình có 1 dòng (các ô trên dòng đó chứa giá trị có thể là 1 số hoặc là 1 chuỗi ký tự cố định ko thay đổi) mình muốn xác định gia trị đó đang nằm ở cột nào thì code như thế nào vậy ak.( em cần lấy tên cột vì có thể dữ liệu cột bị thay đổi do chèn cột nên mình ko thể gán vào 1 cột cố định được)
Mong mọi người giúp đỡ

Maika8008 · 13/9/23

ngochienbmt đã viết:
Nhờ các bác chỉ giúp ak, mình có 1 dòng (các ô trên dòng đó chứa giá trị có thể là 1 số hoặc là 1 chuỗi ký tự cố định ko thay đổi) mình muốn xác định gia trị đó đang nằm ở cột nào thì code như thế nào vậy ak.( em cần lấy tên cột vì có thể dữ liệu cột bị thay đổi do chèn cột nên mình ko thể gán vào 1 cột cố định được)
Mong mọi người giúp đỡ

Bạn dùng phương thức Find().Column để lấy số cột rồi chuyển sang tên cột hoặc Find().Address để lấy địa chỉ dạng $C$R rồi tách lấy tên cột bằng hàm Mid và InstrRev

bebo021999 · 13/9/23

Một cách nữa là dùng Application.Match(SearchValue,SearchRow,0)

cantl · 23/9/23

Em không hiểu cái này:
Các hàm UDF trên, khi mà em sửa khai báo biến lên Double hết, nhưng giá trị biến tối đa cũng chỉ là 2147483648 (Long).
Tại sao vậy ạ?

VetMini · 23/9/23

cantl đã viết:
Em không hiểu cái này:
Các hàm UDF trên, khi mà em sửa khai báo biến lên Double hết, nhưng giá trị biến tối đa cũng chỉ là 2147483648 (Long).
Tại sao vậy ạ?

Tôi thì khong hiểu cái này:
Bạn là thành viên lâu năm, nhiều kinh nghiệm, số bài hỏi và trả lời của bạn không ít. Thế mà bạn hỏi một câu không biết đâu mà mò.
các hàm UDF trên là hàm nào, dẫm chứng 1 cái. Bạn sửa như thế nào và test như thế nào? Bạn có biết cách mặc định một hằng số là Double? 123 được mặc định là Integer, 123& là Long.

Chú thích: trong mấy cái hàm tôi xem sơ qua, có mấy cái dùng toán tử \. Đây là toán tử chia số nguyên, khi áp dụng toán tử này, VBA ép kiểu cả hai vế phải trái của \ về kiểu Long. Kết quả trả về là kiểu Long. Vì việc ép kiểu này cho nên sử dung \ với Double rất nguy hiểm:

*** Hiểu biết về kiểu dữ liệu của nhiều người trên diễn đàn này chưa đủ để viết những code liên quan đến kiểu. Tôi biết vậy nhưng lười viết bài quá, đợi bạn nào cần thì hỏi.

Lập trình VBA với Column: chuyển chỉ số cột thành chữ cái

Hãy để gió cuốn đi.

Thành viên tiêu biểu

Huyền thoại GPE

File đính kèm

-

Huyền thoại GPE

-

Huyền thoại GPE

Bad Excel Member

Huyền thoại GPE

Bad Excel Member

Huyền thoại GPE

Hành động từ trái tim

Thành viên thường trực

Thành viên thường trực

Huyền thoại GPE

Thành viên mới

Thành viên chính thức

Thành viên gạo cội

Thành viên gạo cội

!!! Giải thoát !!!

Ăn cùng góc phố

Đếm ngược thời gian