请帮忙边个公式，关于公司加班与调休相抵消的。
附件上说明了具体要求，谢谢！
http://access911.net/csdn/FileDescription.asp?mdb=2010-2-5&id=4
[解决办法]
=C2*1.5-D2 然后往下拉 如果要变颜色 用条件格式 利润小于0的用红色
[解决办法]
寫在Workbook的Worksheet_Activate事件中
當Sheet3激活時自動計算
VBA代碼:
Private Sub Worksheet_Activate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(3)'改成你放數據的Sheet,此例為Sheet3
For i = 2 To ws.UsedRange.Rows.Count
If (ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4) < 0) Then
ws.Cells(i, 5) = Abs(ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4))
ws.Cells(i, 5).Interior.ColorIndex = 3
Else
ws.Cells(i, 5) = (ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4)) / 1.5
ws.Cells(i, 5).Interior.ColorIndex = 4
End If
Next
End Sub
效果:
注:紅色為調休,綠色為加班
[解决办法]
Private Sub Workbook_Activate()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1) '改成你放數據的Sheet,此例為Sheet3
For i = 2 To ws.UsedRange.Rows.Count
If (ws.Cells(i, 1) <> "" And ws.Cells(i, 6) <> "计件工资") Then
If (ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4) < 0) Then
ws.Cells(i, 5) = Abs(ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4))
ws.Cells(i, 5).Interior.ColorIndex = 3
Else
ws.Cells(i, 5) = (ws.Cells(i, 3) * 1.5 - ws.Cells(i, 4)) / 1.5
ws.Cells(i, 5).Interior.ColorIndex = 4
End If
End If
Next
End Sub
