[已解决]如何利用函数或宏，根据前两列内容自动生成第三列内容？

brothersonline

fjmxwrs 发表于 2013-11-26 13:23
关键是放假不知道怎么写好了，没有特定的日期，如清明节，公历是不一样的

呵呵，是我表达不清楚，那就不要放假的条件了。

fjmxwrs

brothersonline 发表于 2013-11-26 13:24
呵呵，是我表达不清楚，那就不要放假的条件了。

我这里给你写了元旦三天假，国庆7天假的，你看下是不是这个意思，其他假期，你自己加进去就行了

1. Sub test()
   
2. Dim arr, brr(), iRow%, x%, y%, i%
   
3. With Sheets("记事本")
   
4. iRow = .Cells(Rows.Count, 1).End(xlUp).Row
   
5. arr = .Range("B4:C" & iRow)
   
6. ReDim brr(1 To UBound(arr))
   
7. For x = 1 To UBound(arr)
   
8. If arr(x, 2) = "张　健" Then
   
9. If arr(x, 1) >= DateSerial(Year(Date), 1, 1) And arr(x, 1) < DateSerial(Year(Date), 1, 4) Or arr(x, 1) >= DateSerial(Year(Date), 10, 1) And arr(x, 1) < DateSerial(Year(Date), 10, 8) Then
   
10. brr(x) = "放假、值班"
    
11. ElseIf Weekday(arr(x, 1), vbMonday) = 1 Then
    
12. brr(x) = "计划、巡诊、值班"
    
13. ElseIf Weekday(arr(x, 1), vbMonday) = 5 Then
    
14. brr(x) = "小结、值班"
    
15. Else
    
16. brr(x) = "值班"
    
17. End If
    
18. Else
    
19. If arr(x, 1) >= DateSerial(Year(Date), 1, 1) And arr(x, 1) < DateSerial(Year(Date), 1, 4) Or arr(x, 1) >= DateSerial(Year(Date), 10, 1) And arr(x, 1) < DateSerial(Year(Date), 10, 8) Then
    
20. brr(x) = "放假"
    
21. ElseIf Weekday(arr(x, 1), vbMonday) = 1 Then
    
22. brr(x) = "计划、巡诊"
    
23. ElseIf Weekday(arr(x, 1), vbMonday) = 5 Then
    
24. brr(x) = "小结"
    
25. Else
    
26. brr(x) = ""
    
27. End If
    
28. End If
    
29. Next x
    
30. .Range("E4").Resize(UBound(brr), 1) = Application.Transpose(brr)
    
31. End With
    
32. End Sub
    

复制代码

baksy

=IF(AND(WEEKDAY(B4,2)=1,C4<>"张　健"),"计划、巡诊",IF(WEEKDAY(B4,2)=5,"小结",IF(C4="张　健","值班",IF(AND(WEEKDAY(B4,2)=1,C4="张　健"),"计划、巡诊、值班",IF(AND(WEEKDAY(B4,2)=5,C4="张　健"),"小结、值班",IF(E4="放假","放假",""))))))

注：值班人姓名中间有空格

砂海

=IF(WEEKDAY(B4)=2,"计划、巡诊；",IF(WEEKDAY(B4)=6,"小结；",""))&IF(C4="张　健","值班；","")
"放假"未考虑

brothersonline

fjmxwrs 发表于 2013-11-26 13:56
我这里给你写了元旦三天假，国庆7天假的，你看下是不是这个意思，其他假期，你自己加进去就行了

正是如此，并且不影响E列的数据有效性。谢谢版主！

brothersonline

baksy 发表于 2013-11-26 14:20
=IF(AND(WEEKDAY(B4,2)=1,C4"张　健"),"计划、巡诊",IF(WEEKDAY(B4,2)=5,"小结",IF(C4="张　健","值班",IF( ...

用嵌套函数来实现，倒是可以，但是容易脑袋疼，呵呵，谢谢啦

brothersonline

砂海 发表于 2013-11-26 14:40
=IF(WEEKDAY(B4)=2,"计划、巡诊；",IF(WEEKDAY(B4)=6,"小结；",""))&IF(C4="张　健","值班；","")
"放假"未 ...

谢谢指教！