Làm thế nào để tự động tính tổng cho thành phần Chi phí trực tiếp

[nguyenmanhnam]

Mỗi công việc của em ở file đính kèm thì tại cột B, những dòng Cộng chi phí trực tiếp nó được xác định:

Cộng chi phí trực tiếp = Vật liệu + Nhân công + Máy thi công.

Em xin nhờ các anh, chị trên diễn đàn giúp em làm thế nào để tự động điền giá trị của các dòng Cộng chi phí trực tiếp (các ô em tô màu vàng) có thể bằng:
- Công thức Excel đơn thuần
- hoặc Bằng VBA
(nếu bằng VBA xin hãy giúp em cách tạo Sub để mỗi khi chạy VBA nó sẽ tự động điền công thức vào như ô mẫu J11).
Em xin cảm ơn.

 

[huuthang_bd]

Mỗi công việc của em ở file đính kèm thì tại cột B, những dòng Cộng chi phí trực tiếp nó được xác định:

Cộng chi phí trực tiếp = Vật liệu + Nhân công + Máy thi công.

Em xin nhờ các anh, chị trên diễn đàn giúp em làm thế nào để tự động điền giá trị của các dòng Cộng chi phí trực tiếp (các ô em tô màu vàng) có thể bằng:
- Công thức Excel đơn thuần
- hoặc Bằng VBA
(nếu bằng VBA xin hãy giúp em cách tạo Sub để mỗi khi chạy VBA nó sẽ tự động điền công thức vào như ô mẫu J11).
Em xin cảm ơn.

Bạn dùng Code này thử xem sao:

Sub Test()
Application.Calculation = xlCalculationManual
Dim Cll As Range, Str As String
For Each Cll In Range([C1], [C65536].End(xlUp))
    If Cll.Value = "VËt liÖu" Or Cll.Value = "Nh©n c«ng" Or Cll.Value = "M¸y thi c«ng" Then
        Str = Str & "+" & Cll.Offset(, 7).Address
    ElseIf Cll.Value = "Céng chi phÝ trùc tiÕp" Then
        Cll.Offset(, 7).Value = Replace(Str, "+", "=", , 1)
        Str = ""
    End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Nếu bạn muốn dùng công thức thì dùng công thức này cho ô J11:

=SUMPRODUCT($J$4:J10,($C$4:C10="VËt liÖu")+($C$4:C10="Nh©n c«ng")+($C$4:C10="M¸y thi c«ng"))-SUMIF($C$4:C10,"Céng chi phÝ trùc tiÕp",$J$4:J10)

Sau đó copy công thức này cho các dòng Cộng chi phí nhân công trực tiếp

 

[nguyenmanhnam]

Bạn dùng Code này thử xem sao:

Sub Test()
Application.Calculation = xlCalculationManual
Dim Cll As Range, Str As String
For Each Cll In Range([C1], [C65536].End(xlUp))
If Cll.Value = "VËt liÖu" Or Cll.Value = "Nh©n c«ng" Or Cll.Value = "M¸y thi c«ng" Then
Str = Str & "+" & Cll.Offset(, 7).Address
ElseIf Cll.Value = "Céng chi phÝ trùc tiÕp" Then
Cll.Offset(, 7).Value = Replace(Str, "+", "=", , 1)
Str = ""
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Sau đó copy công thức này cho các dòng Cộng chi phí nhân công trực tiếp

Vâng kết quả ra rất chuẩn, nhưng em muốn nó hiện ở công thức dưới dạng tham chiếu tương đối thì làm thế nào ah? Ví dụ khi em chạy kết quả ở ô J11 = $J$4+$J$9, khi muốn chuyển sang tương đối (=J4+J9) thì phải làm sao hả anh?

 

[MinhCong]

Tại dòng này

Str = Str & "+" & Cll.Offset(, 7).Address[COLOR=red](0, 0)[/COLOR]

Hoặc

Str = Str & "+" & Cll.Offset(, 7).Address[COLOR=red](False, False)[/COLOR]

 

[huuthang_bd]

Vâng kết quả ra rất chuẩn, nhưng em muốn nó hiện ở công thức dưới dạng tham chiếu tương đối thì làm thế nào ah? Ví dụ khi em chạy kết quả ở ô J11 = $J$4+$J$9, khi muốn chuyển sang tương đối (=J4+J9) thì phải làm sao hả anh?

Cái đó cũng đâu có ảnh hưởng gì đâu nhỉ. Nhưng nếu bạn muốn thì sửa lại như thế này:

Sub Test()
Application.Calculation = xlCalculationManual
Dim Cll As Range, Str As String
For Each Cll In Range([C1], [C65536].End(xlUp))
    If Cll.Value = "VËt liÖu" Or Cll.Value = "Nh©n c«ng" Or Cll.Value = "M¸y thi c«ng" Then
        Str = Str & "+" & Cll.Offset(, 7).Address(0, 0)
    ElseIf Cll.Value = "Céng chi phÝ trùc tiÕp" Then
        Cll.Offset(, 7).Value = Replace(Str, "+", "=", , 1)
        Str = ""
    End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

 

[nguyenmanhnam]

Cái đó cũng đâu có ảnh hưởng gì đâu nhỉ.

Bởi vì...em chưa biết tương đối hay tuyệt đối trong VBA phân biệt như thế nào hihi, em hỏi để học lỏm anh ah.

 

[Akiac]

Các anh chị có biết làm sao để VBA hoạt động thực hiện được đối với các dòng có chữ tiếng việt có dấu không? Vì mình phải viết không dấu chỉnh trong file excel để chạy thử thì mới được. xin cảm ơn

Sub Test2()
Application.Calculation = xlCalculationManual
Dim Cll As Range, Str As String
For Each Cll In Range([C1], [C65536].End(xlUp))
   If Cll.Value = "Vat Lieu" Or Cll.Value = "Nhan cong" Or Cll.Value = "May thi cong" Then
        Str = Str & "+" & Cll.Offset(, 7).Address(0, 0)
    ElseIf Cll.Value = "Cong chi phi truc tiep" Then
        Cll.Offset(, 7).Value = Replace(Str, "+", "=", , 1)
        Str = ""
    End If
Next
Application.Calculation = xlCalculationAutomatic

End Sub

 

[SA_DQ]

Có 1 cách rất thô là:
Đưa chuỗi 'Vật liệu' vô 1 Name; 2 chuỗi còn lại wính trực tiếp được mà;

Cách 'hàm lăm' hơn là phải tìm trên diễn đàn (Tìm hàm CHRWin() í)
. . . Có thể còn cách khác nhưng mình chưa biết

 

[Maika8008]

Các anh chị có biết làm sao để VBA hoạt động thực hiện được đối với các dòng có chữ tiếng việt có dấu không? Vì mình phải viết không dấu chỉnh trong file excel để chạy thử thì mới được. xin cảm ơn

Sub Test2()
Application.Calculation = xlCalculationManual
Dim Cll As Range, Str As String
For Each Cll In Range([C1], [C65536].End(xlUp))
   If Cll.Value = "Vat Lieu" Or Cll.Value = "Nhan cong" Or Cll.Value = "May thi cong" Then
        Str = Str & "+" & Cll.Offset(, 7).Address(0, 0)
    ElseIf Cll.Value = "Cong chi phi truc tiep" Then
        Cll.Offset(, 7).Value = Replace(Str, "+", "=", , 1)
        Str = ""
    End If
Next
Application.Calculation = xlCalculationAutomatic

End Sub

Có 2 cách:
1. Gõ các cụm chữ cần thiết vào các ô nào đó của trang tính, rồi lấy giá trị của ô để so sánh với Cll.Value
2. Dùng hàm sau để chuyển sang mã Unicode của ký tự có dấu rồi dùng:

Function TaoChuoiMa(ByVal text As String) As String
Dim k As Long, start As Long, kytu As String, s As String, result As String
    s = text
    start = 1
    For k = 1 To Len(s)
        kytu = Mid(s, k, 1)
        If AscW(kytu) > 127 Then
            If start < k Then
                result = result & " & " & Chr(34) & Mid(s, start, k - start) & Chr(34) & " & ChrW(" & AscW(kytu) & ")"
            Else
                result = result & " & ChrW(" & AscW(kytu) & ")"
            End If
            start = k + 1
        End If
    Next k
    If start < k Then result = result & " & " & Chr(34) & Mid(s, start, k - start) & Chr(34)
    If Left(result, 2) = " &" Then result = Mid(result, 4)
    TaoChuoiMa = result
End Function

Ví dụ sẽ không gõ được chữ Vật Liệu thì thay vì: If Cll.Value = "Vật Liệu" thì hãy dùng: If Cll.Value = "V" & ChrW(7853) & "t Li" & ChrW(7879) & "u"