是的,我只有 4 张 – Mohsen 11 分钟前
非 VBA 解决方案
在 Sheet1 的 A1 单元格中,输入此公式
=MAX(Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)
在 Sheet1 的 B1 单元格中,输入这个可怕的公式。我确信有更好的方法来查找工作表名称。
=IF(Sheet1!A1=Sheet2!A1,RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))- FIND("]",CELL("filename",Sheet2!A1),1)),IF(Sheet1!A1=Sheet3!A1,RIGHT(CELL("filename",Sheet3!A1),LEN(CELL("filename",Sheet3!A1))- FIND("]",CELL("filename",Sheet3!A1),1)),IF(Sheet1!A1=Sheet4!A1,RIGHT(CELL("filename",Sheet4!A1),LEN(CELL("filename",Sheet4!A1))- FIND("]",CELL("filename",Sheet4!A1),1)),IF(Sheet1!A1=Sheet5!A1,RIGHT(CELL("filename",Sheet5!A1),LEN(CELL("filename",Sheet5!A1))- FIND("]",CELL("filename",Sheet5!A1),1)),""))))
一句话caution尽管。要使用RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))- FIND("]",CELL("filename",Sheet2!A1),1))
,您需要保存工作簿。
我的Sheet2,A1有1
,Sheet3,A1有2
,Sheet4,A1有2.5
,Sheet5,A1有3
VBA解决方案
Sub Sample()
Dim ws As Worksheet
Dim i As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
.Range("A1:A365").Formula = "=MAX(Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)"
.Range("A1:A365").Value = .Range("A1:A365").Value
For i = 1 To 365
Select Case .Range("A" & i)
Case ThisWorkbook.Sheets("Sheet2").Range("A" & i).Value: .Range("B" & i).Value = "Sheet2"
Case ThisWorkbook.Sheets("Sheet3").Range("A" & i).Value: .Range("B" & i).Value = "Sheet3"
Case ThisWorkbook.Sheets("Sheet4").Range("A" & i).Value: .Range("B" & i).Value = "Sheet4"
Case ThisWorkbook.Sheets("Sheet5").Range("A" & i).Value: .Range("B" & i).Value = "Sheet5"
End Select
Next i
End With
End Sub