Đánh số chứng từ tự động trong excel nhưng lập lại từ đầu theo tháng (1 người xem)

[tuananh89803]

Công ty mình đánh số phiếu thu - chi mỗi tháng được lập lại.
Ví dụ: tháng 1: phiếu chi là C001/01, C002/01, C003/01,...
tháng 2: phiếu chi là C001/02, C002/02, C003/02,...
tương tự cho các tháng tiếp theo,
Mình chỉ biết đánh tự động tăng dần, còn lập lại như vậy bằng công thức Excel 2003 thì mình không biết làm cách nào, nhờ mọi người chỉ giúp. Nếu dùng VBA thì mình chưa rành mong mọi người hướng dẫn kỹ chút nha!

 

[SA_DQ]

Mình hướng dẫn bạn xài hàm tự tạo sau

Bạn chép hàm này vô cửa sổ VBE

Option Explicit
Function SoFieu(Fieu As String, Optional Dat As Date) As String
 Const GX As String = "/"
 Dim VTr As Byte, Thg As Byte
 
 If Dat = 0 Then Dat = Date
 VTr = InStr(Fieu, GX)
 If VTr Then
    Thg = CByte(Right(Fieu, 2))
    If Thg <> Month(Dat) Then
        If Thg = 12 And Month(Dat) = 1 Then
            SoFieu = Left(Fieu, 1) & "001/01"
        Else
            SoFieu = Left(Fieu, 1) & "001/" & Right("0" & CStr(Month(Dat)), 2)
        End If
    ElseIf Thg = Month(Dat) Then
        SoFieu = Left(Fieu, 1) & Right("00" & CStr(CInt(Mid(Fieu, 2, 3)) + 1), 3) & GX _
            & Right("0" & CStr(Month(Dat)), 2)
    End If
 Else
    SoFieu = "Tao lao"
 End If
End Function

Kết quả xài hàm sẽ thể hiện như bảng sau:

A | B | C
| Số Fiếu |(Cú fáp hàm tự tạo)
2/17/2012|T035/02| <- Số fiếu đang có
3/1/2012|T036/02| <- =SoFieu(B2)
1/1/2013|T001/03| <- =Sofieu(B3,A3)
|T001/01| <- =SoFieu(B4,A4)

Mình tin là bạn sẽ suy luận ra vấn đề.

Chúc vui!

 

[hoangminhtien]

Bạn xem trong file đính kèm có đúng ý bạn không nhé!

 

[ChanhTQ@]

Bạn xem trong file đính kèm có đúng ý bạn không nhé!

Các mã chứng từ của bạn có độ dài không bằng nhau!

/(hì, Khì,. . . .

 

[hoangminhtien]

Các mã chứng từ của bạn có độ dài không bằng nhau!

/(hì, Khì,. . . .

Cám ơn bác, vậy thì chuyển

="C"&TEXT(COUNTIF($B$2:B2,"<="&DATE(YEAR(B2),MONTH(B2)+1,0))-COUNTIF($B$2:B2,"<="&DATE(YEAR(B2),MONTH(B2),0)),"000")&"/"&MONTH(B2))

thành

="C"&TEXT(COUNTIF($B$2:B2,"<="&DATE(YEAR(B2),MONTH(B2)+1,0))-COUNTIF($B$2:B2,"<="&DATE(YEAR(B2),MONTH(B2),0)),"000")&"/"&TEXT(MONTH(B2),"00")

 

[tuananh89803]

Bạn xem trong file đính kèm có đúng ý bạn không nhé!

Nhưng mình chạy 2 loại chứng từ thu và chi thì nó lấy số tiếp theo, ví dụ: chứng từ chi là C001/1, nhập tiếp chứng từ thu thì nó ra T002/1! Không biết giải quyết sao?

 

[hoangminhtien]

Nhưng mình chạy 2 loại chứng từ thu và chi thì nó lấy số tiếp theo, ví dụ: chứng từ chi là C001/1, nhập tiếp chứng từ thu thì nó ra T002/1! Không biết giải quyết sao?

bạn gửi file của bạn lên nhé!

 

[tuananh89803]

bạn gửi file của bạn lên nhé!

Gửi lên forum chưa gửi khi nào cả, có phải gửi qua trung gian ko tại chỉ thấy thêm link??

 

[tuananh89803]

Được rồi! đã thêm được rồi mà giới hạn có 97kb bực quá!

 

[hoangminhtien]

Bạn dùng công thức sau nhé!

=IF(J2="","C","T")&TEXT(COUNTIF($C$2:C2,"<="&DATE(YEAR(C2),MONTH(C2)+1,0))-COUNTIF($C$2:C2,"<="&DATE(YEAR(C2),MONTH(C2),0)),"000")&"/"&TEXT(MONTH(C2),"00")

 

[tuananh89803]

Ủa công thức có thiếu dấu ( hả bạn, đọc ko hiểu!

 

[hoangminhtien]

Công thức hoàn toàn bình thường mà. nếu không chạy được trên máy bạn thì bạn sử dấu phẩy "," thành dấu chấm phẩy ( trong công thức. gửi lại file cho bạn.

 

[tuananh89803]

bạn ơi coi lại đi, chứ C002/01 chuyển qua thành T003/01 nè, chứ không phải T001/01! Quan trọng là mình muốn nó chạy số tự động cho mỗi loại phiếu chứ không phải chữ C hay T

 

[ndu96081631]

Bạn dùng công thức sau nhé!

=IF(J2="","C","T")&TEXT(COUNTIF($C$2:C2,"<="&DATE( YEAR(C2),MONTH(C2)+1,0))-COUNTIF($C$2:C2,"<="&DATE(YEAR(C2),MONTH(C2),0))," 000")&"/"&TEXT(MONTH(C2),"00")

Có vài chổ có thể rút gọn, ví dụ:
DATE(YEAR(C2),MONTH(C2),0) là ngày cuối của tháng trước, ta sửa thành C2 - DAY(C2)
TEXT(MONTH(C2),"00") là định dạng tháng theo kiểu "mm", ta sửa thành TEXT(C2,"mm")

 

[tuananh89803]

Nhưng quang trọng là nó chạy 2 phiếu thì lấy số tiếp theo của phiếu trước ví dụ: C003/01 qua phiếu thu thì lại T004/01, chứ không phải T001/01 (đây là phiếu thu đầu tiên của tháng 1), nếu nó chạy tiếp thì đâu cần để chữ C hoặc T trong cú pháp chứ! Phải giải 2 bài toán nên mới rắc rối nè!

 

[ChanhTQ@]

Thêm 1 tham khảo cho bạn

Cảm ơn HM Tiến vì file.

 

[huuthang_bd]

Nhưng quang trọng là nó chạy 2 phiếu thì lấy số tiếp theo của phiếu trước ví dụ: C003/01 qua phiếu thu thì lại T004/01, chứ không phải T001/01 (đây là phiếu thu đầu tiên của tháng 1), nếu nó chạy tiếp thì đâu cần để chữ C hoặc T trong cú pháp chứ! Phải giải 2 bài toán nên mới rắc rối nè!

Bạn thử dùng công thức này cho ô B2

=IF(COUNTIF(H2:I2,1111),IF(H2=1111,"T","C")&TEXT(COUNTIF($B$1:B1,IF(H2=1111,"T","C")&"???/"&TEXT(C2,"mm"))+1,"000")&"/"&TEXT(C2,"mm"),"")

 

[tuananh89803]

cho mình hỏi

"???/" nghĩa là gì? nếu mình muốn cú pháp là C2012-001/01 (với 2012 là năm của ô C2) thì sao, mình thử vào mà nó chạy không được! Mình vẫn chưa hiểu công thức lắm! Hịc!
À đã sửa được!
=IF(COUNTIF(H2:I2,1111),IF(H2=1111,"T"&YEAR(B2)&"-","C"&YEAR(B2)&"-")&TEXT(COUNTIF($B$1:B1,IF(H2=1111,"T"&YEAR(B2)&"-","C"&YEAR(B2)&"-")&"???/"&TEXT(C2,"mm"))+1,"000")&"/"&TEXT(C2,"mm"),"")

Cám ơn mọi người giúp sức!​

 

[huuthang_bd]

cho mình hỏi

"???/" nghĩa là gì? nếu mình muốn cú pháp là C2012-001/01 (với 2012 là năm của ô C2) thì sao, mình thử vào mà nó chạy không được! Mình vẫn chưa hiểu công thức lắm! Hịc!
À đã sửa được!
=IF(COUNTIF(H2:I2,1111),IF(H2=1111,"T"&YEAR(B2)&"-","C"&YEAR(B2)&"-")&TEXT(COUNTIF($B$1:B1,IF(H2=1111,"T"&YEAR(B2)&"-","C"&YEAR(B2)&"-")&"???/"&TEXT(C2,"mm"))+1,"000")&"/"&TEXT(C2,"mm"),"")

Cám ơn mọi người giúp sức!
​

Cái nào là nhân tố chung thì đưa ra ngoài sẽ gọn hơn một chút.

=IF(COUNTIF(H2:I2,1111),IF(H2=1111,"T","C")&YEAR(C2)&TEXT(COUNTIF($B$1:B1,IF(H2=1111,"T","C")&YEAR(C2)&"-???/"&TEXT(C2,"mm"))+1,"-000")&"/"&TEXT(C2,"mm"),"")

 

[nhan.nguyenkt]

Trường hợp này thì giải quyết như thế nào ạ:
- Số phiếu thu, chi nhảy tự động theo cú pháp: CV.[Cửa hàng].[Tháng]số chứng từ
Trong đó: CV là mã chứng từ chi tiền VNĐ
Yêu cầu: Số chứng từ được quản lý theo tháng và cửa hàng.
Ví dụ: Cty có 02 cửa hàng thì số chứng từ đặt như:
CV.CH1.01.01
CV.CH1.01.02
...
CV.CH1.01.99

Cửa hàng 2:
CV.CH2.01.01
CV.CH2.01.02
....
CV.CH2.01.99

Thanks các anh/chị

 

[nhan.nguyenkt]

Thông tin cần điền tại cột C [Số chứng từ ] căn cứ vào cột hạch toán và cột ngày tháng. Mã cửa hàng là 03 ký tự cuối của tài khoản tiền. Nếu tài khoản 11111 thì không cần điền vì nó có sẵng.
Hình như tài khoản em nó bị chặn việc đính kèm file nên không thể nào upload lên được dù file chỉ nặng 148kb! hbkhuong cho địa chỉ mail em gửi file nhé!

 

[nhan.nguyenkt]

http://www.mediafire.com/view/o923iy1zbze1ock/Book1.xlsx
Đây là file của em.

 

[ChanhTQ@]

Thông tin cần điền tại cột C [Số chứng từ ] căn cứ vào cột hạch toán và cột ngày tháng. Mã cửa hàng là 03 ký tự cuối của tài khoản tiền. Nếu tài khoản 11111 thì không cần điền vì nó có sẵng.
Hình như tài khoản em nó bị chặn việc đính kèm file nên không thể nào upload lên được dù file chỉ nặng 148kb! hbkhuong cho địa chỉ mail em gửi file nhé!

Xin lỗi trước vì mình dốt lâu về khoản tài chánh này! Nhưng vẫn hỏi để biết

(1) Cột nào là 'cột hạch toán' vậy?

(2) Có 2 cột ngày, thì ta sẽ fải lấy cột nào [Ngày ghi sổ] hay [ngày chứng từ]?

(3) [Tài khoản tiền] ở đâu để lấy 3 kí tự cuối?

?????????????????????????

 

[nhan.nguyenkt]

@ChanhTQ sorry bạn mình upload mà không nói rõ:
(1) Cột hạch toán gồm có hai cột: TKGHINO (Cột J) và cột TKGHICO (Cột K)
(2) Mình sẽ lấy theo cột ngày chứng từ, trong trường hợp này ngày chứng từ và ngày hạch toán là 1.
(3) Tài khoản tiền là từ để chỉ các ô mà tại cột TKGHINO và TKGHICO có các ký 2 ký tự đầu là 11. Trong trường hợp này, mình đang lọc lấy tài khoản tiền mặt nên 4 ký tự đầu của ô tại cột TKGHINO và TKGHICO có giá trị là 1111.

Ở trong file đính kèm, tại Cột TKGHINO và TKGHICO mà ô nào có 2 ký tự đầu 11 thì đều là TK tiền. Ví dụ trong ô K2 có giá trị: 1111CNT thì đây là tài khoản tiền và mã Cửa hàng là 3 ký tự cuối là: CNT tương tự đối với K8 mã cửa hàng là VT1. Và ô J62 có giá trị là 11111 thì không cần phải đặt số chứng từ vì nó đã có sẵng tại ô C62.

 

[nhan.nguyenkt]

Cột số chứng từ sẽ được đặt theo cấu trúc: [Mã Chứng từ ].[Mã cửa hàng].[tháng][02 ký tự số thứ tự tăng dần của số chứng từ ]
Trong đó:

Nếu ô tại các cột TKGHINO và TKGHICO có giá trị là 11111 (Năm con số 1) thì bỏ qua còn không thì điền thông tin theo điều kiện sau:

. 1 [Mã chứng từ ] Nếu 04 ký tự đầu của cột TKGHINO là 1111 thì [Mã chứng từ ] là: TV còn nếu là 1112 là TU nếu 1113 là TC Còn nếu Cột TKGHINO có giá trị mà các ký tự đầu không phải là 11 thì xét cột TKGHICO nếu 04 ký tự đầu của cột TKGHICO là 1111 thì [Mã chứng từ ] là: CV còn nếu là 1112 là CU nếu 1113 là CC còn còn nếu không thõa các điều kiện trên thì để trống ô số chứng từ.

. 2 [Mã cửa hàng ] nếu ô có 04 ký tự đầu là 1111 là 03 ký tự cuối của ô đó.

. 3 [tháng] là 02 ký tự của tháng chứng từ.

. 4 [02 ký tự số chứng từ ]: Số chứng từ được đánh tăng theo từng mã cửa hàng và từng mã chứng từ và thay đổi bắt đầu lại 01 khi sang tháng mới.

 

[Ba Tê]

Cột số chứng từ sẽ được đặt theo cấu trúc: [Mã Chứng từ ].[Mã cửa hàng].[tháng][02 ký tự số thứ tự tăng dần của số chứng từ ]
Trong đó:

Nếu ô tại các cột TKGHINO và TKGHICO có giá trị là 11111 (Năm con số 1) thì bỏ qua còn không thì điền thông tin theo điều kiện sau:

. 1 [Mã chứng từ ] Nếu 04 ký tự đầu của cột TKGHINO là 1111 thì [Mã chứng từ ] là: TV còn nếu là 1112 là TU nếu 1113 là TC Còn nếu Cột TKGHINO có giá trị mà các ký tự đầu không phải là 11 thì xét cột TKGHICO nếu 04 ký tự đầu của cột TKGHICO là 1111 thì [Mã chứng từ ] là: CV còn nếu là 1112 là CU nếu 1113 là CC còn còn nếu không thõa các điều kiện trên thì để trống ô số chứng từ.

. 2 [Mã cửa hàng ] nếu ô có 04 ký tự đầu là 1111 là 03 ký tự cuối của ô đó.

. 3 [tháng] là 02 ký tự của tháng chứng từ.

. 4 [02 ký tự số chứng từ ]: Số chứng từ được đánh tăng theo từng mã cửa hàng và từng mã chứng từ và thay đổi bắt đầu lại 01 khi sang tháng mới.

??? nếu ô có 04 ký tự đầu là 1112 hay 1113 thì lấy cửa hàng nào?
Bạn cố giải thích cho "ai" cũng hiểu chứ không phải người nào cũng trong nghề kế toán mà hiểu được như bạn.
Bấm nút GPE xem kết quả có đúng ý bạn không, có sheet phụ tên "GPE". Tạm để đó, nếu đúng kết quả sẽ "xử" nó sau.

 

[SA_DQ]

Thêm 1 macro để bạn tham khảo & cho í kiến.

Tiền thì ham mà xử lý tiền thì hết ham!

 

[nhan.nguyenkt]

Thanks Anh Ba Tê và SA_DQ.
Các anh đã thực hiện đúng yêu cầu. File của Anh SA_DQ hình như là anh đang setup mã chứng từ theo định dạng khác giữa các tháng phải không anh?

Anh Ba Tê: . 2 [Mã cửa hàng ] nếu ô có 04 ký tự đầu là 1111 là 03 ký tự cuối của ô đó => Sorry Anh, để em nói rõ lại là: Nếu ô có 03 ký tự đầu là 111 thì mã cửa hàng là 03 ký tự cuối của ô đó.

Có thể lập bằng công thức hay hàm mảng bình thường được không anh? Ba cái vụ Macro hay Visual Basic gì đó em mù tịt.

Thanks các anh!

 

[SA_DQ]

Thanks các anh đã thực hiện đúng yêu cầu.
(1) File của Anh SA_DQ hình như là anh đang setup mã chứng từ theo định dạng khác giữa các tháng phải không anh?
(2) Có thể lập bằng công thức hay hàm mảng bình thường được không anh?
Ba cái vụ Macro hay Visual Basic gì đó em mù tịt!

(1) Tô màu chơi cho vui í mà!

(2) Dữ liệu hơn 2.000 dòng thì hãy quên cái vụ công thức mảng đi!

VBA có gì là đáng sợ cơ chứ? Mọi người đang còn muốn lao zô nhiều, rất nhiều nữa là đằng khác;

Nếu dịch được ngôn ngữ VBA thì trong bài có xài hàm tự tạo để tạo mã mới sau mỗi dòng đang duyệt mà!

Nó đây:

Function SoCT(mDat As Date, NgayCT As Date, TKN_C As Range, Optional SCTC As String = "")
 Dim CHg As String
 Dim Cls As Range
 
 SoCT = "CV" & "."
 For Each Cls In TKN_C
    If Left(Cls.Value, 4) = "1111" Or Left(Cls.Value, 3) = "111" Then
        SoCT = SoCT & Right(Cls.Value, 3) & "."
        Exit For
    End If
 Next Cls
 If SCTC = "" Then
    SoCT = SoCT & Right("0" & CStr(Month(NgayCT)), 2) & ".0001"
    Exit Function
 ElseIf CByte(Mid(SCTC, 8, 2)) <> Month(NgayCT) Then
    SoCT = SoCT & Right("0" & CStr(Month(NgayCT)), 2) & ".0001"
    Exit Function
 ElseIf CByte(Mid(SCTC, 8, 2)) = Month(NgayCT) Then
    SoCT = Left(SCTC, 10) & Right("000" & CStr(CInt(Right(SCTC, 4)) + 1), 4)
 End If
End Function

 

[Ba Tê]

Thanks Anh Ba Tê và SA_DQ.
Các anh đã thực hiện đúng yêu cầu. File của Anh SA_DQ hình như là anh đang setup mã chứng từ theo định dạng khác giữa các tháng phải không anh?

Anh Ba Tê: . 2 [Mã cửa hàng ] nếu ô có 04 ký tự đầu là 1111 là 03 ký tự cuối của ô đó => Sorry Anh, để em nói rõ lại là: Nếu ô có 03 ký tự đầu là 111 thì mã cửa hàng là 03 ký tự cuối của ô đó.

Có thể lập bằng công thức hay hàm mảng bình thường được không anh? Ba cái vụ Macro hay Visual Basic gì đó em mù tịt.

Thanks các anh!

- Do bạn nêu điều kiện không rõ nên tôi phải hỏi thôi, biết đâu chỉ có "1111" mới lấy 3 chữ cuối, ngoài ra thì không lấy.
- Trong code tôi đã "ngầm hiểu" là có "111" thì lấy 3 chữ cuối. Nó đây:

If Left(sArr(I, 1), 3) = "111" Then
                ..............................................
..........................
            ElseIf Left(sArr(I, 2), 3) = "111" Then

- Như "Lão ca" đã nói, dữ liệu lu bu hơn 1000 dòng thì "quên" công thức mảng đi.
- Nếu bạn trao đổi được toàn văn bằng tiếng nước ngoài thì nên viết toàn tiếng nước ngoài.
Giao tiếp nửa tây nửa ta nghe "khó chịu" quá.

 

[nhan.nguyenkt]

Cám ơn các anh HYen17 và anh Ba Tê. Dữ liệu gốc của em là khoản 70.000 dòng cơ. Vậy cái này phải sử dụng như thế nào vậy ạ? Ấn Alt+F11 và copy module của anh qua rồi làm thế nào nữa ạ? Em đã tạo thêm sheet GPE như file của anh mà khi ấn F5 thì nó báo lõi.

 

[nhan.nguyenkt]

Còn 1 vấn đề nữa là: Ngoài việc nó tự động điền số chứng từ vào ô tại Cột SỐ CHỨNG TỪ nó còn nhân tiện .. xóa luôn các số chứng từ khác hiện đang có trong các ô khác không thõa điều kiện ở tại cột SỐ CHỨNG TỪ này luôn...=> Như vậy là sao vậy????

 

[Ba Tê]

Còn 1 vấn đề nữa là: Ngoài việc nó tự động điền số chứng từ vào ô tại Cột SỐ CHỨNG TỪ nó còn nhân tiện .. xóa luôn các số chứng từ khác hiện đang có trong các ô khác không thõa điều kiện ở tại cột SỐ CHỨNG TỪ này luôn...=> Như vậy là sao vậy????

- Bạn ấn Atl+F11, Insert Module, chép cái Sub GPE() từ trong Mudule của File này vào trong Module của file kia.
- Trong file bạn phải sửa tên sheet cần ghi số CT là "Data", Sheet phụ "GPE" thì phải có dữ liệu A1:C4 như trong file.
- Copy 70.000 dòng của bạn vào file này rồi chạy thử xem sao. Tôi chưa thử chưa biết máy có chịu nỗi không.

 

[nhan.nguyenkt]

Em đã thử và kết quả rất ấn tượng. Cám ơn anh Ba Tê rất nhiều.