查找不同工作表中的列的最大值,并在结果表中报告 [英] Find Maximum Value for a column in different sheets and report it in result sheet
问题描述
我想在 Sheet2
中查看列A
( A1:A365 ), Sheet3
, Sheet4
和 Sheet5
并找到最大每个单元格的值。比较中的
, A1
Sheet2 Sheet3
, Sheet4
和 Sheet5
,找到最多并在结果 A1
中报告页。在单元格 B1
中也报告此最大值的相应工作表。这将继续到列A
( A1:A365 )
I want to check Column A
(A1:A365) in Sheet2
, Sheet3
, Sheet4
and Sheet5
and find Maximum value for each cell. Compare A1
in Sheet2
, Sheet3
, Sheet4
and Sheet5
, find maximum of it and report it in A1
in result page. Also in cell B1
report corresponding sheet for this maximum. This goes on to Column A
(A1:A365)
我使用的以下代码:
Worksheets("sheet2").Range("A1").Value = a
Worksheets("sheet3").Range("A1").Value = b
Worksheets("sheet4").Range("A1").Value = c
Worksheets("sheet5").Range("A1").Value = d
MaxValue = Application.Max(a, b, c, d)
Range("A1").Value = MaxValue
推荐答案
是的,我只有4张 - Mohsen 11分钟前
yes i have just 4 sheets – Mohsen 11 mins ago
非VBA解决方案
在Sheet1,单元格A1中,放这个公式>
In Sheet1, Cell A1, put this formula
=MAX(Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)
在Sheet1,Cell B1中,放这个可怕的公式。我确定有一个更好的方式来找到工作表名称。
In Sheet1, Cell B1, put this horrifying formula. I am sure there is a better way to find the sheet name though.
= 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) )),IF(Sheet1!A1 = Sheet4!A1,RIGHT(CELL(filename,Sheet4!A1),LEN(CELL(filename,Sheet4!A1)) - FIND(],CELL ,Sheet4!A1),1)),IF(Sheet1!A1 = Sheet5!A1,RIGHT(CELL(filename,Sheet5!A1)),LEN(CELL(filename,Sheet5!A1)) - FIND ],CELL(filename,Sheet5!A1),1)),)))))
谨慎。要使用 RIGHT(CELL(filename,Sheet2!A1),LEN(CELL(filename,Sheet2!A1)) - FIND(],CELL(filename,Sheet2! A1),1))
,您需要保存工作簿。
A word of caution though. To use the RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))- FIND("]",CELL("filename",Sheet2!A1),1))
, you need to have the workbook saved.
我的Sheet2,A1有 1
,Sheet3,A1有 2
,Sheet4,A1有 2.5
,Sheet5,A1有 3
My Sheet2, A1 has 1
, Sheet3, A1 has 2
, Sheet4, A1 has 2.5
,Sheet5, A1 has 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
这篇关于查找不同工作表中的列的最大值,并在结果表中报告的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!