Tham chiếu giữa hợp đồng và bản chấm công

[suzuki tadayoshi]

Hihi...
Tình hình là thế này, do thời gian gấp rút để giải quyết vấn đề kê khai thuế cho năm rồi nên mình đã làm bài toán ngược chi phí nhân công tại công trường (mấy đồng chí dưới công trường không có bảng công gì nộp về hết...):
-Chi phí nhân công công trường kê khai là 1.000.000.000 đ
-Mức giá mỗi công từ 200.000đ đến 300.000đ
-Công trường của mình có khoảng 50 nguời và nhiệm vụ của mình là lập 12 bảng công tương đương cho 12 tháng sao cho: Tổng chi phí nhân công đúng con số 1.000.000.000 mà mình đã kê khai ban đầu.
-Tính ngẫu nhiên ở đây là cho bảng công thêm hợp lý 1 chút.
Xin lỗi vì bài toán không giống ai

 

[Hoang2013]

Vô lý thế, sao lại rải công ngẫu nhiên? đi ngày nào, làm ngày nào thì chấm công ngày đó mới đúng
Hay là làm ngược để tính người lý thuyết?

Mình đã nêu ở bài nào đó ở đầu trong topic này: Đây là kiểu chấm công đối phó của các công ti "Xây dựng"
Ngõ hầu như trốn thuế doanh nghiệp, thuế thu nhập cá nhân hay TN doanh nghiệp,. . . .

(1) Theo bạn chủ đề bài toán ngược: Nghĩa là cho số công trong 1 tháng cụ thể ( ví dụ nhân viên A trong tháng 1 được 20 công), lập macro rải công ngẫu nhiên trong tháng đó
và (2) chủ đề rải công ngày lễ (chữ "L") ta có cần bàn tiếp ko

1. Mình đã hỏi bạn trước đây rằng cố định công hàng tháng hay sao; Nhưng bạn đang quan tâm đến vấn đề khác hơn nên còn bỏ ngỏ mà!

2. Chuyện này tiếp cũng không sao. Miễn là bạn cung cấp những thông số đầu vào (Cách mà bạn tiến hành lâu nay & v.v. . . )

 

[Hoang2013]

Theo bạn chủ đề (1) rải công ngày lễ (chữ "L") & bài toán ngược: (2) Nghĩa là cho số công trong 1 tháng cụ thể ( ví dụ nhân viên A trong tháng 1 được 20 công), lập macro rải công ngẫu nhiên trong tháng đó
ta có cần bàn tiếp ko

(1) Đến sáng nay mình đã viết xong cho bạn cách chấm công ngày lễ rồi đây; Bạn làm theo mình nha:

B1: Bạn đến trang 'Data': Nhập vô ô to tướng [AA2] nhóm từ "Ngày Lễ"
Tại ô dưới liền kề là công thức: =DATE('T01'!C4,1,1)
Tại ô [AA11] bạn lại nhập công thức tương tự để sao ô này thể hiện là ngày Quốc khách nước ta;
Các ô giữa 2 ô công thức này bạn cần rãi các ngày nghỉ lễ theo dương lịch; (Ví dụ, tại [AA8] là ngày 4/10/2016

B2: Bạn chép đè macro này lên toàn bộ cái cũ & chạy nó:

Sub NgayCQ()
 Dim Sh As Worksheet, Cls As Range
 Dim SoNgay As Integer, J As Integer, MyColor As Byte, Tmr As Double
 Dim fDat As Date, tDat As Date, lDat As Date
 Dim ShName As String
 On Error GoTo LoiCT
1 'Xóa Du Lieu Cu:'
 Tmr = Timer()
 For Each Sh In ThisWorkbook.Worksheets
    If Left(Sh.Name, 1) = "T" And IsNumeric(Right(Sh.Name, 1)) Then
        J = Sh.[c8].CurrentRegion.Rows.Count
        Rows("8:99").Hidden = False
        Sh.[c8].Resize(J, 32).Interior.ColorIndex = 0
        Sh.[B8].Resize(J, 33).Value = ""
        Sh.Rows("8:99").Hidden = False
    End If
 Next Sh
 Sheets("Data").Select:             [r3].Value = Timer() - Tmr
2 'Lap Danh Sách CNV:'
 For Each Cls In Range([B4], [B99].End(xlUp))
    fDat = Cls.Offset(, Col + 2).Value
    lDat = Cls.Offset(, Col + 3).Value
    ChepTen Cls, fDat, lDat
    If Cls.Offset(, Col + 4).Value > 0 Then
        fDat = Cls.Offset(, Col + 4).Value
        lDat = Cls.Offset(, Col + 5).Value
        ChepTen Cls, fDat, lDat
    End If
 Next Cls
 [r4].Value = Timer - Tmr
3 'Chép Công Vô Các Tháng'
 For Each Cls In Range([B4], [B99].End(xlUp))
    fDat = Cls.Offset(, Col + 2).Value
    lDat = Cls.Offset(, Col + 3).Value
    GPE Cls, fDat, lDat
    
    If Cls.Offset(, Col + 4).Value > 0 Then
        fDat = Cls.Offset(, Col + 4).Value
        lDat = Cls.Offset(, Col + 5).Value
        GPE Cls, fDat, lDat
    End If
 Next Cls
 [r5].Value = Timer - Tmr
 Application.ScreenUpdating = False
4 'Ân Dòng Tróng Cua Các Trang "T01..T12":'
  For Each Sh In ThisWorkbook.Worksheets
    If Left(Sh.Name, 1) = "T" And IsNumeric(Right(Sh.Name, 1)) Then
        J = Sh.[c99].End(xlUp).Row + 2
        Sh.Rows(J & ":99").Hidden = True
    End If
 Next Sh
 [r6].Value = Timer() - Tmr
5 'Dièn Nghi Le:'
 For Each Cls In Sheets("Data").Range("NLe")
    ShName = "T" & Right("0" & CStr(Month(Cls.Value)), 2)
    Set Sh = ThisWorkbook.Worksheets(ShName)
    MyColor = Sh.Cells(SNV, "C").End(xlUp).Row
    SoNgay = 3 + Day(Cls.Value)
    For J = 8 To MyColor
        With Sh.Cells(J, SoNgay)
            If .Interior.ColorIndex > 9 Then
                .Value = "L"
                .Interior.ColorIndex = .Interior.ColorIndex + 1
            End If
        End With
    Next J
 Next Cls
 [r7].Value = Timer() - Tmr
 Application.ScreenUpdating = True
 Exit Sub
LoiCT:
 If Err > 0 Then MsgBox Error, , Err
End Sub

B3: Kiểm tra & thêm râu ria cần thiết.

Chúc thành công!

 

[suzuki tadayoshi]

Hi Hoang2013, cám ơn bạn tiếp tục kiên nhẫn với mình.
Mình làm như bạn hướng dẫn và bị lỗi này (hình đính kèm), bạn xem giúp mình nhé:
1004
application-defined or object-defined error

 

[Hoang2013]

Mình còn thiếu 1 công đoạn cuối của bước 1 nên sẩy ra lỗi;

Đó là vùng [AA3:AA11] bạn gán cho nó 1 cái tên như ghi trong macro ("NLe")

 

[suzuki tadayoshi]

Bravo! Vậy là lời giải cho bài toán thuận đã hoàn thành. Quá trình đồng hành với bạn thật thú vị, cám ơn bạn rất nhiều.
Tiếp theo mình sẽ gửi dữ liệu đầu vào của bài toán nghịch lên cho bạn giải tiếp ha...
Thân!
PS:
1./ Giờ mới nhớ ra, Cột mức lương theo ngày làm sao chỉnh được vậy bạn? Mình thay đổi thế nào chạy macro xong cũng quay lại như cũ. Bạn giúp mình nhé.
2./ Do không biết cách vận hành ở sheet "CTLg" nên Muốn biết tổng lương cả công trường trong 12 tháng:
- mình lập 1 cột tổng số công từng nhân viên = công tháng 1+công tháng 2...
- làm 1 cột đơn giá. Nhân lại... Tự thấy mình nông dân ghê

 

[Hoang2013]

Hỏi sao trả lời vậy mà thôi:
Muốn chỉnh thì sửa trong câu lệnh này:

:     dArr(W, 3) = 26 * 10 ^ 4

Nhưng bạn muốn sửa mức lương ngàu đều khắp cho mọi người trong CTi, hay chỉ điều chỉnh cho 1 số ít người có bật thợ cao?

Ta sẽ có tổng quỹ tiền lương & tổng số công cũng sẽ có;

Vậy mức lương bình quân ngày cũng sẽ có; Hay bạn sẽ áp mức lương này cho tất thẩy mọi người?

 

[Hoang2013]

Chờ bạn lâu quá & mình đã kịp làm cho bạn đơn giá lương ngày rồi đây.

Tuy đã có nhiều trang tính, nhưng chắc trước tiên bạn nên tạo thêm trang tính có tên là 'FuTro' như hình dưới đây:

a) Bạn dời vùng có tên 'NLe' sang đây

b) Thiết lập 12 công thức để có số liệu như cột [I:I]
& công thức để có tổng số công trong năm của công trình (Tại [I15]

c) Nhập tổng quỉ tiền lương vô [I16]

d) Chép hàm tự tạo này vô Module1:

Function DGCong(TT As Double, TC As Double) As Long
 Dim Tmp As Integer
 Const M3 As Integer = 1000

 DGCong = ((TT / TC) \ M3)
 Tmp = DGCong Mod 10
 If Tmp > 2 And Tmp < 8 Then
    Tmp = 5
 ElseIf Tmp > 7 Then
    Tmp = 10
 Else
    Tmp = 0
 End If
 DGCong = (DGCong \ 10) * 10 + Tmp
 DGCong = DGCong * M3
End Function

e) Bạn áp công thức tại [I17] như trong hình

f) Sửa macro sự kiện trong trang 'CTLg' như sau

    For J = 1 To UBound(Arr())
        If Arr(J, 1) = "" Then Exit For
        If Arr(J, 33) > 0 And Arr(J, 33) < 53 Then
            W = W + 1:                  dArr(W, 1) = W
            dArr(W, 2) = Arr(J, 1):     dArr(W, 4) = Arr(J, 33)
            dArr(W, 3) = Sheets("FuTro").Range("DGCg")
        End If
    Next J

Chúc vui & thành công!

 

[suzuki tadayoshi]

Xin lỗi bạn vì sự chậm trế

Vì mình có việc để máy ở công ty không mang về nhà thành ra không phản hồi bạn sớm được...xin lỗi bạn!
1./ Mình làm theo ở sheet "Futro" theo mình hiểu ĐGCong là đơn giá trung bình phải không bạn? (thực ra đây không phải là đơn giá mình đã nói đến nhưng thực sự là 1 thông số rất hay)
2./ Ở trang "CTLg" đã sửa macro và chạy thì báo lỗi "run-time 1004" ở dòng như ở hình mình gửi... (Mình đã sửa được lỗi này khi nhìn thấy mũi tên nhắc nhở của bạn)
-Theo mình hiểu thì macro này sẽ ghi mức lương của tất cả NV = ĐGCong phải không bạn..
-nhưng ý mình mình là hiển thị đơn giá công mỗi NV là con số chính xác (có khoảng 6 đơn giá công cho 1 công trường) -> khi nhân với số công ra số lương chính xác.
-Đơn giá nhân công như mình thường làm là tạo thêm 1 cột đơn giá ở sheet DATA, rồi ghi đơn giá từng người vào. 1 lần nữa Xin lỗi vì mình chưa gửi file lên kịp cho bạn hiểu ý đồ
3./ Tổng quỹ tiền lương mình muốn biết là con số bằng tổng của ô tổng lương hàng tháng ở trang "CTLg" khi cập nhật số liệu tháng từ 1 đến 12 ấy bạn
Chúc bạn có ngày làm việc vui vẻ và hiệu quả.

 

[Hoang2013]

Bạn xem file này:

​

 

[suzuki tadayoshi]

Hi bạn. Bạn đã xử lý bài toán quá gọn gàng, sạch sẽ rồi, File chạy êm ru...

Mình chỉ còn yêu cầu chút xíu là ô tính tổng lương cả năm của toàn công trình. Để mình không phải cập nhật số liệu trang "CTLg" cho từng tháng, ghi số tổng ra máy rồi cộng lại thôi.

Thân,

 

[Hoang2013]

Bạn thêm 1 dòng lệnh sau vô macro sự kiện tại trang 'CTLg', như sau

Bạn đã xử lý bài toán quá gọn gàng, sạch sẽ rồi, File chạy êm ru...

Mình chỉ còn yêu cầu chút xíu là ô tính tổng lương cả năm của toàn công trình. Để mình không phải cập nhật số liệu trang "CTLg" cho từng tháng, ghi số tổng ra máy rồi cộng lại thôi.
Thân,

 [COLOR=#d3d3d3]   If W Then
        [b6].Resize(W, 4).Value = dArr()
        Rows(W + 6 & ":" & SNV).Hidden = True[/COLOR]
        [B]Sheets("FuTro").[G1].Offset(Target.Value).Value = [N101].Value[/B]
 [COLOR=#d3d3d3]   End If[/COLOR]

 

[suzuki tadayoshi]

 [COLOR=#d3d3d3]   If W Then
        [b6].Resize(W, 4).Value = dArr()
        Rows(W + 6 & ":" & SNV).Hidden = True[/COLOR]
        [B]Sheets("FuTro").[G1].Offset(Target.Value).Value = [N101].Value[/B]
 [COLOR=#d3d3d3]   End If[/COLOR]

Cám ơn bạn!
1./ Mình cập nhật tháng xong thì đã có đủ cột lương ở trang "FuTro"và tính sum tổng nữa là ra.

- Thế nhưng khi thay đổi thông số, chạy lại macro hay thêm bớt công (ghi thủ công) ở trang họ "T" thì giá trị trong cột lương ở trên vẫn chưa cập nhật theo mà vẫn phải cập nhật lại từng tháng ở trang "CTLg" mới được.

- Vì mình muốn ngay khi thêm bớt công thì biết luôn giá trị tổng lương đã hiệu chỉnh, đủ thì thôi...chưa đủ thì lại thêm bớt công tiếp (mình đã áp đặt con số tổng từ trước)

- Phải cập nhật lại thì rất bất tiện để điều chỉnh. Điều này bạn có cách nào giúp mình không?

2./ Có 1 bất cập là khi báo cáo thu nhập thì bên thuế họ cần con số thu nhập của từng người trong năm (mình gửi kèm hình mới rồi mình làm thủ công để báo cáo).
Có vẻ bài toán tính tổng này khi làm thủ công thì đơn giản nhưng khi dùng CODE thì không đơn giản chút nào phải không bạn?

Chúc một buổi tối vui vẻ.

 

[Hoang2013]

1./ Mình cập nhật tháng xong thì đã có đủ cột lương ở trang "FuTro"và tính sum tổng nữa là ra.

- Thế nhưng khi thay đổi thông số, chạy lại macro hay thêm bớt công . . . .

Điều này bạn hoàn toàn nên làm & bằng cách sau:

Với những trang tính họ "T", ngay tại ô [AJ8] của mỗi trang bạn xài 2 hàm VLOOKUP() lồng vô nhau để chúng cho bạn đơn giá người có mã ở cột [C] cùng dòng

& ô bên phải liền kề bạn áp CT phép nhân để có tiền lương của nhân viên đó trong tháng.

(Hai ô công thức này bạn có thể khoá lại

Sau đó bạn tiến hành chép xuống đến hàng cuối cột [C] có dữ liệu.

Động tác cuối cùng là: Lấy chuột tô chọn vùng (2 cột) vừa chép & Copy- dán Values; Động tác này ngỏ hầu giảm tải cho file của bạn. & bạn cũng nên làm thế vói cột ô bên trái của chúng.

2./ Có 1 bất cập là khi báo cáo thu nhập thì bên thuế họ cần con số th . . . .

Hình nhỏ quá, không xem được!

 

[suzuki tadayoshi]

Điều này bạn hoàn toàn nên làm & bằng cách sau:

Với những trang tính họ "T", ngay tại ô [AJ8] của mỗi trang bạn xài 2 hàm VLOOKUP() lồng vô nhau ....

- Mình cũng có ý định lập hàm như bạn nói: AJ8=VLOOKUP(VLOOKUP(C8;vlook;6;FALSE);GPE_;3) [vlook: vùng mình đặt trong sheet DATA thôi]
- Mình chỉ không biết là dùng Code có hay hơn không: một phát ăn liền, lại không phải copy công thức.
- Và nếu dùng cách lập hàm, bạn hướng dẫn mình bước nữa là sau khi tính lương NV cho từng tháng thì lập bảng tổng hợp lương tất cả NV cả năm thì làm thế nào?

(Hai ô công thức này bạn có thể khoá lại;
Sau đó bạn tiến hành chép xuống đến hàng cuối cột [C] có dữ liệu.)

-Bạn có thể chỉ mình khóa công thức không cho sửa, không cho coi...và mục đích của việc khóa CT rồi chép xuống là để làm gì không?

Động tác cuối cùng là: Lấy chuột tô chọn vùng (2 cột) vừa chép & Copy- dán Values; Động tác này ngỏ hầu giảm tải cho file của bạn. & bạn cũng nên làm thế vói cột ô bên trái của chúng.

- Động tác này mình chưa hiểu lắm, Copy values rồi khi cập nhật lại số liệu giá trị không cập nhật được nữa...?
PS: Hình cách mình làm thủ công gửi lại cho bạn coi

 

[Hoang2013]

Có vẻ như bạn đã khoái xài macro hơn CT rồi thì phải!

Vậy thì đây, xin mời:

Sub DonGiaNgayCong()
 Dim ShName As String
 
 ShName = ActiveSheet.Name
 If Left(ShName, 1) = "T" And IsNumeric(Right(ShName, 2)) Then
    DGNCg
 End If
End Sub

Bạn gán 1 tổ hợp phím cho macro này, ví du {CTRL}+{SHIFT} & D
sau đó ở bất kì trang tính nào họ "T" bấm tổ hợp phím qui ước ta sẽ có các kết quả.
Chú í: Công thức 2 hàm VLOOKUP() bạn chưa bẩy lỗi thì phải.

[B]Sub DGNCg(Optional ShName As String)[/B]
 Dim Rw As Integer
 
 Rw = ActiveSheet.Cells(SNV, "c").End(xlUp).Row
 Range("AJ8:AK8").Select
 Selection.AutoFill Destination:=Range("AJ8:AK" & Rw)
[B]End Sub[/B]

 

[suzuki tadayoshi]

Hii Bạn đọc vị đúng quá.
Theo bạn cách học VBA: 1. ngâm cứu các đoạn Code rồi tìm ra qui luật; 2.học đi lên từ cơ bản cách nào tốt hơn... mình thì nghĩ cách 2 chắc hơn nhưng lại không hứng thú cho lắm...
Có bảng lương cũ cần in ra báo cáo gấp, nãy giờ mò Code ẩn những NV không làm trong tháng nhưng còn bó tay
Bạn giúp mình với. (Rất nhiều bảng như vậy mà ẩn dòng thủ công thì chết...)
Thanks bạn.
PS: Đoạn Code bạn mới hướng dẫn, mình lại có cái ngâm cứu

 

[Hoang2013]

Có bảng lương cũ cần in ra báo cáo gấp, nãy giờ mò Code ẩn những NV không làm trong tháng nhưng còn bó tay
Bạn giúp mình với. (Rất nhiều bảng như vậy mà ẩn dòng thủ công thì chết...)

Bạn nên dịch đoạn Code này sang tiếng Việt:

4 'Ân Dòng Tróng Cua Các Trang "T01..T12":'
  For Each Sh In ThisWorkbook.Worksheets
    If Left(Sh.Name, 1) = "T" And IsNumeric(Right(Sh.Name, 1)) Then
        J = Sh.[c99].End(xlUp).Row + 2
        Sh.Rows(J & ":99").Hidden = True
    End If
 Next Sh
 Sheets("FuTro").[N5].Value = Timer() - Tmr

Trong VBE, nó cho phép ta đánh số các dòng lệnh (Như bạn thấy số 4 đó)
Hễ dịch gần được nghĩa là bạn đang trên đường chinh phục VBA

Theo bạn cách học VBA: 1. ngâm cứu các đoạn Code rồi tìm ra qui luật; 2.học đi lên từ cơ bản cách nào tốt hơn... mình thì nghĩ cách 2 chắc hơn nhưng lại không hứng thú cho lắm...

Theo mình, bạn không thể theo lớp/trường bài bảng được thì phải theo đường hướng tự học:

(.) Mua 1 quyễn sách co bản về VBA (Sách giành cho ~ người mới bắt đầu)

(.) Lấy công việc bạn đang làm ra mà làm đề tài thử nghiệm

(.a) Nhập liệu:

Xây dựng 1 CSDL như trang 'Data' bạn đang có
Thử lấy 1 trang tính trắng làm trang tính nhập liệu vô nó theo kiểu

Họ tên: . . . . .
Mã NV: . . . .
Ngày sinh: . . . .
. . . . .

Sau đó tìm trên diễn đàn để chuyển dữ liệu mới nhập này vô trang 'Data'
(Bạn nên biết 1 điều rằng: ta càng ít đụng vô trang 'Data' bao nhiêu càng trường thọ CSDL của ta bấy nhiêu. Có nghĩa là không bao giờ nhập trực tiếp dữ liệu lên trang 'Data')

Khâu nhập liệu tạm xong (vì chưa xong hẵn đâu) Ta chuyển sang vài biểu mẫu báo cáo đơn giản, như
- Lọc những người hợp đồng theo tháng;
- Lập danh sách nữ/nam,
. . . .

Nhiêu đó cái đã & chúc bạn kiên trì & thành công!

 

[suzuki tadayoshi]

Bạn nên dịch đoạn Code này sang tiếng Việt:

4 'Ân Dòng Tróng Cua Các Trang "T01..T12":'
  For Each Sh In ThisWorkbook.Worksheets
...[/QUOTE]
- Giờ mình đang lo in ấn ba cái trang tính để nộp nên tạm thời gác dịch Code lại sau vậy... +-+-+-+ 
- Mình thấy cách ẩn dòng trang họ "T" như trước đây khá tốt:
1. thêm [QUOTE]Sub AnDong(Rng As Range)
 Dim Cls As Range
 Dim Rws As Long
 
 ....[/QUOTE] vào modul1
2.Dán [QUOTE]Private Sub Worksheet_Activate()
 AnDong Range([c8], [c8].End(xlDown))
End Sub[/QUOTE] vào các trang họ "T"

Nên muốn Áp dụng cách tương tự vào trang họ "L" có được không bạn...?(mình đính kèm file bạn xem giúp)

[QUOTE]Theo mình, bạn không thể theo lớp/trường bài bảng được thì phải theo đường hướng tự học:

(.) Mua 1 quyễn sách co bản về VBA (Sách giành cho ~ người mới bắt đầu)

(.) Lấy công việc bạn đang làm ra mà làm đề tài thử nghiệm...
[/QUOTE] 
cám ơn bạn đã chỉ dẫn, mình sẽ có hy vọng 1 ngày nào đó có thể trao đổi VBA với bạn chứ ko phải cứ ăn rồi đòi hỏi như bây giờ;;;;;;;;;;;
Thân.

 

[suzuki tadayoshi]

Sub DonGiaNgayCong()
  ....

[B]Sub DGNCg(Optional ShName As String)[/B]
  ...

Cả 2 sub trên mình đều dán vào modul1, rồi chạy macro....nhưng không có gì xảy ra. Chắc mình chưa biết sử dụng rồi...

Chú í: Công thức 2 hàm VLOOKUP() bạn chưa bẩy lỗi thì phải.

- Ý bạn là có lỗi hả mình chưa hiểu lắm? mình thì thấy nó hiện đơn giá là chính xác.