跨多个工作表获取范围内的最大最小值Excel

Question

我有以下公式，可以在多个工作表中的F列数据中找到最大值。

=MAX('Aug242018LB3STRDF$000'!F5:F39004,'Aug242018LB3STRDF$001'!F5:F39004,'Aug242018LB3STRDF$002'!F5:F39004,'Aug242018LB3STRDF$003'!F5:F39004,'Aug242018LB3STRDF$004'!F5:F39004,'Aug242018LB3STRDF$005'!F5:F39004,'Aug242018LB3STRDF$006'!F5:F39004,'Aug242018LB3STRDF$007'!F5:F39004,'Aug242018LB3STRDF$008'!F5:F39004,'Aug242018LB3STRDF$009'!F5:F39004,'Aug242018LB3STRDF$010'!F5:F39004,'Aug242018LB3STRDF$011'!F5:F39004,'Aug242018LB3STRDF$012'!F5:F39004,'Aug242018LB3STRDF$013'!F5:F39004,'Aug242018LB3STRDF$014'!F5:F39004,'Aug242018LB3STRDF$015'!F5:F39004,'Aug242018LB3STRDF$016'!F5:F39004,'Aug242018LB3STRDF$017'!F5:F39004,'Aug242018LB3STRDF$018'!F5:F39004,'Aug242018LB3STRDF$019'!F5:F39004,'Aug242018LB3STRDF$020'!F5:F39004,'Aug242018LB3STRDF$021'!F5:F39004,'Aug242018LB3STRDF$022'!F5:F39004,'Aug242018LB3STRDF$023'!F5:F39004,'Aug242018LB3STRDF$024'!F5:F39004,'Aug242018LB3STRDF$025'!F5:F39004,'Aug242018LB3STRDF$026'!F5:F39004,'Aug242018LB3STRDF$027'!F5:F39004,'Aug242018LB3STRDF$028'!F5:F39004,'Aug242018LB3STRDF$029'!F5:F39004)

它的效果非常好，但是不够灵活。经常情况下我有不同数量的工作表或工作表名称不同，因此这个公式只能在一个工作簿中使用。我希望有一种在VBA中构建宏或应用程序的方法，能够检测工作表的数量，并可能能够选择我的自定义范围进行比较（不同的列）。

- Chris

---

请点击此处查看有关在Excel中使用3D MAX公式的内容：https://chandoo.org/wp/3d-max-formula-for-excel/ - Scott Craner

此外，该链接讨论了用于循环遍历所有工作表的VBA代码：https://support.microsoft.com/en-gb/help/142126/macro-to-loop-through-all-worksheets-in-a-workbook - Martin Cook

Answer 1

您可以在开头创建一个名为“first”的工作表并将其隐藏。在系列结束时，重复此过程创建一个名为“last”的工作表。公式如下：

=max(first:last!f5:f39004)

- user4039065

---

如果您要使用此解决方案，则建议将“第一个”和“最后一个”工作表设置为非常隐藏。这样可以防止普通用户显示它们。https://www.officetooltips.com/excel_2016/tips/making_a_spreadsheet_very_hidden.html - Frank Ball

@FrankBall 使用VBA将工作表“非常隐藏”有点违背了非VBA解决方案的初衷。如果您无论如何都要使用VBA，那么最好自己编写一个函数来完成此操作，而不依赖于隐藏工作表。 - ImaginaryHuman072889

Answer 2

解决了循环所有工作表和选择范围/列的两个问题。我创建了两个函数（可以在下面找到函数），一个用于最小值，另一个用于最大值。您只需要像下图所示选择范围即可。这两个函数即使在选定范围跨工作表存在空白或文本的情况下也能正常工作，但如果出现错误，则无法正常工作。

要使用这些函数，您需要将函数源代码（在下面找到）复制并粘贴到模块中。这些函数仅在您复制函数的工作簿中可用。

如果您希望在打开任何工作簿时都可以使用这些函数。您需要将包含该函数的工作簿保存为加载项，然后激活该加载项。这是一个非常简单的步骤 点击此处查看如何进行加载项设置 请注意，如果您在“sheet1”中键入函数，然后转到不同的工作表，例如“sheet2”，并更改数字，则函数不会自动计算。您需要进入公式栏并按Enter键。如果您更改的数字与键入函数的工作表相同，则会自动更新。

最小跨表函数

Public Function Minimum_Across_Sheets(rngSelection As Range) As Double

Dim dMinimum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

    For Each rng In rngSelection

        If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then

            If Not bFirst_Value_Obtained Then
                dMinimum_Value = wks.Cells(rng.Row, rng.Column)
                bFirst_Value_Obtained = True
            End If

            If wks.Cells(rng.Row, rng.Column) < dMinimum_Value Then
                dMinimum_Value = wks.Cells(rng.Row, rng.Column)
            End If

        End If

    Next rng

Next wks

Minimum_Across_Sheets = dMinimum_Value
End Function

跨表格最大值函数

Public Function Maximum_Across_Sheets(rngSelection As Range) As Double

Dim dMaximum_Value As Double
Dim bFirst_Value_Obtained As Boolean
Dim rng As Range
Dim wks As Worksheet

For Each wks In ActiveWorkbook.Worksheets

    For Each rng In rngSelection

        If IsNumeric(wks.Cells(rng.Row, rng.Column)) And Len(wks.Cells(rng.Row, rng.Column)) > 0 Then

            If Not bFirst_Value_Obtained Then
                dMaximum_Value = wks.Cells(rng.Row, rng.Column)
                bFirst_Value_Obtained = True
            End If

            If wks.Cells(rng.Row, rng.Column) > dMaximum_Value Then
                dMaximum_Value = wks.Cells(rng.Row, rng.Column)
            End If

        End If

    Next rng

Next wks

Maximum_Across_Sheets = dMaximum_Value
End Function

希望这对您有用。

- Khalid Saifaldeen

---

这太完美了，正是我一直在寻找的，非常感谢！ - Chris