Excel VBA-查找最小值列表? [英] Excel VBA - Find minimum of list of values?

问题描述

对于像这样的列表

Column1     Column2     Column3    
DataA       1           1234    
DataA       2           4678    
DataA       3           8910    
DataB       2           1112    
DataB       4           1314    
DataB       9           1516

我如何获得这样的列表:

How do I get a list like this:

Column4    Column5      Column6    
DataA      1            1234    
DataB      2            1112

关键是仅返回column2中的最小值及其对应的column3值.

The key is to only return the minimum value in column2 and its corresponding column3 value.

推荐答案

对不起，我首先误解了您的问题.这是一个工作代码，结果比我希望的复杂得多:D

Sorry I misunderstood your Question First. Here is a working code that ended up more complex than I wanted it to be :D

Option Explicit

Private Function inCollection(ByRef myCollection As Collection, ByRef value As Variant) As Boolean
    Dim i As Integer
    inCollection = False

    For i = 1 To myCollection.Count
        If (myCollection(i) = value) Then
            inCollection = True
            Exit Function
        End If
    Next i
End Function

Sub listMinimums()

    Dim source As Range
    Dim target As Range
    Dim row As Range
    Dim i As Integer
    Dim datas As New Collection
    Dim minRows As New Collection

    Set source = Range("A2:C5")
    Set target = Range("D2")
    target.value = source.value

    For Each row In source.Rows
        With row.Cells(1, 1)
            If (inCollection(datas, .value) = False) Then
                datas.Add .value
                minRows.Add row.row, .value
            End If
            If (Me.Cells(minRows(.value), 2) > row.Cells(1, 2)) Then
                minRows.Remove (.value)
                minRows.Add row.row, .value
            End If
        End With
    Next row

    'output'
    For i = 1 To minRows.Count
        target(i, 1) = Me.Cells(minRows(i), 1)
        target(i, 2) = Me.Cells(minRows(i), 2)
        target(i, 3) = Me.Cells(minRows(i), 3)
    Next i

    Set datas = Nothing
    Set minRows = Nothing
End Sub

注意:您可能要用工作表的名称替换Me.

Note: You might want to replace Me with the name of your sheet.

这篇关于Excel VBA-查找最小值列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！