vba 在不同工作表中查找列的最大值并在结果表中报告

Question

提问by Mohsen

I want to check Column A(A1:A365) in Sheet2, Sheet3, Sheet4and Sheet5and find Maximumvalue for each cell. Compare A1in Sheet2, Sheet3, Sheet4and Sheet5, find maximum of it and report it in A1in result page. Also in cell B1report corresponding sheet for this maximum. This goes on to Column A(A1:A365)

我要检查Column A（A1：A365）中Sheet2，Sheet3，Sheet4和Sheet5，找到最大为每个单元格的值。比较A1中Sheet2，Sheet3，Sheet4和Sheet5，找到最大的，并在报告它A1在结果页面。同样在单元格B1报告中，此最大值对应的工作表。这继续Column A( A1:A365)

enter image description here

在此处输入图片说明

the following code i used:

我使用了以下代码：

    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

Answer 1

回答by Siddharth Rout

yes i have just 4 sheets – Mohsen 11 mins ago

是的，我只有 4 张纸——Mohsen 11 分钟前

Non VBA Solution

非 VBA 解决方案

In Sheet1, Cell A1, put this formula

在Sheet1，A1单元格中，输入这个公式

=MAX(Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)

In Sheet1, Cell B1, put this horrifying formula. I am sure there is a better way to find the sheet name though.

在 Sheet1 的 Cell 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)),""))))

A word of cautionthough. 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.

一个字谨慎虽然。要使用RIGHT(CELL("filename",Sheet2!A1),LEN(CELL("filename",Sheet2!A1))- FIND("]",CELL("filename",Sheet2!A1),1))，您需要保存工作簿。

My Sheet2, A1 has 1, Sheet3, A1 has 2, Sheet4, A1 has 2.5,Sheet5, A1 has 3

我的 Sheet2, A1 has 1, Sheet3, A1 has 2, Sheet4, A1 has 2.5,Sheet5, A1 has3

enter image description here

在此处输入图片说明

VBA Solution

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

Answer 2

回答by Peter Albert

Quick solution

快速解决方案

Based on Sidd's answer, here's my non-VBA solution without the horrifying formula:

根据 Sidd 的回答，这是我的非 VBA 解决方案，没有可怕的公式：

Place this formula in Sheet1!A1: =MAX(Sheet2:Sheet5!A1)
As the sheet name does not have to be flexible (I'd assume you don't change it that often), you can use this formula in B1:
```
=IF(Sheet2!A1=A1,"Sheet2",
IF(Sheet3!A1=A1,"Sheet3",
IF(Sheet4!A1=A1,"Sheet5",
"Sheet5")))
```

将此公式放在 Sheet1!A1 中： =MAX(Sheet2:Sheet5!A1)
由于工作表名称不必灵活（我假设您不会经常更改它），您可以在 B1 中使用此公式：
```
=IF(Sheet2!A1=A1,"Sheet2",
IF(Sheet3!A1=A1,"Sheet3",
IF(Sheet4!A1=A1,"Sheet5",
"Sheet5")))
```

More structural solution(better suited for many worksheets):

更多结构解决方案（更适合许多工作表）：

If you have many worksheets, you could consider this alternative.

如果你有很多工作表，你可以考虑这个替代方案。

Have a list of the relevant worksheets stored somewhere in your worksheet. (in the example, I place the list in E3:E7). Name this range Sheets. (Similar to Sidd's horrifying formula, I used the CELL formula to dynamically get each sheet name. However, this is not necessary in a static model)
(same as step 1 above): Place this formula in Sheet1!A1: =MAX(Sheet2:Sheet5!A1)
Place this formula in A2:
=INDEX(Sheets,MATCH(1,COUNTIF(INDIRECT("'"&Sheets&"'!A1"),A1),0))
Enter it as an array formula, i.e. press Ctrl-Shift-Enterinstead of Enter.

将相关工作表的列表存储在工作表中的某处。（在示例中，我将列表放在 E3:E7 中）。命名这个范围Sheets。（类似于Sidd的可怕公式，我使用CELL公式动态获取每个sheet名称。不过，这在静态模型中不是必须的）
（与上面的步骤 1 相同）：将此公式放在 Sheet1!A1 中： =MAX(Sheet2:Sheet5!A1)
将此公式放在 A2 中：
=INDEX(Sheets,MATCH(1,COUNTIF(INDIRECT("'"&Sheets&"'!A1"),A1),0))
将其作为数组公式输入，即按Ctrl- Shift-Enter而不是Enter。

I uploaded the second solution here.

我在这里上传了第二个解决方案。

Kudos to this instruction!

感谢这条指令！

Answer 3

回答by byundt

Since the formula is intended to be copied down, you don't want to hard-code a reference to cell A1in the INDIRECTin Peter Albert's formula. You can use CELL("address",A1)to get a reference to A1that can be copied down instead.

由于式意图被复制下来，你不想硬编码到单元格的引用A1在INDIRECT彼得·艾伯特的公式。您可以使用CELL("address",A1)来获取A1可以复制下来的引用。

You can also avoid the need to array-enter the formula by using LOOKUP to return the result instead of INDEX& MATCH. Note that LOOKUPwill return the name of the last worksheet with the max value in case of a tie.

您还可以通过使用 LOOKUP 而不是INDEX&来返回结果，从而避免需要对公式进行数组输入MATCH。请注意，LOOKUP如果出现平局，将返回具有最大值的最后一个工作表的名称。

The following formulas use a named range Sheetswith the names of each worksheet

下面的公式中使用命名范围表，每个工作表的名称

=MAX(Sheet2:Sheet5!A1)        returns max value (identical to Peter Albert's formula)
=LOOKUP(2,1/COUNTIF(INDIRECT("'" & Sheets & "'!" & CELL("address",A1)),A1),Sheets)      returns name of sheet with max value

Answer 4

回答by Jason Steward

Make two new sheets with one titled "First" and the other "Last". Make new sheets for your project originate through a button that contains a macro to add duplicate sheets between "Firstand Last". Then just put one of these simple formulas for example in your reporting cell: =SUM(First:Last!K28)or =MAX(First:Last!K28)

制作两张新纸，一张名为“ First”，另一张名为“ Last”。通过包含宏的按钮为您的项目创建新工作表，以在“第一个和最后一个”之间添加重复的工作表。然后只需将这些简单公式之一放在您的报告单元格中： =SUM(First:Last!K28)或 =MAX(First:Last!K28)

vba 在不同工作表中查找列的最大值并在结果表中报告

提问by Mohsen

回答by Siddharth Rout

回答by Peter Albert

回答by byundt

回答by Jason Steward

相关推荐

最近更新

标签

vba 在不同工作表中查找列的最大值并在结果表中报告

提问by Mohsen

回答by Siddharth Rout

回答by Peter Albert

回答by byundt

回答by Jason Steward

相关推荐

vba Excel 宏中的 Vlookup

VBA 转换和组合日期和时间

修剪工作簿中的所有单元格 (VBA)

基于单元格结果运行的 VBA 宏

相关推荐

最近更新

标签