Excel:写入与特定值相关联的列和行名称?

I've been trying to write a formula for the next problem but I never get it correct. So:

我一直在试着写出下一个问题的公式，但是我从来没有把它写对。所以:

I have to find Top n values in 2D array and write down row and column names which are connected to that value (without the duplicates!). As an example, here's an array:

我必须在2D数组中找到前n个值，并写下与该值相连接的行名和列名(没有重复!)例如，这里有一个数组:

           A        B       C       D        E        F
    1             Aron    Jamie    Matt   Taylor    Zedd
    2    Aron      -        5       7       6         8
    3    Jamie     5        -       2       4         3
    4    Matt      7        2       -       15        12
    5    Taylor    6        4       15      -         26
    6    Zedd      8        3       12      26        -

What should be written down by formula(s), in this example Top 3 values and the names connected with those values:

公式(s)应该写什么，在本例中前3个值以及与这些值相关的名称:

A        B     C
Taylor  Zedd  26
Taylor  Matt  15
Matt    Zedd  12

Thanks!

谢谢!

2 个解决方案

#1

With your data, running this macro:

使用您的数据，运行这个宏:

Sub dural()
    Dim i As Long, j As Long, K As Long
    i = 3: j = 2: K = 1

    Do
        Cells(K, "G") = Cells(i, 1)
        Cells(K, "H") = Cells(1, j)
        Cells(K, "I") = Cells(i, j)
        K = K + 1
        j = j + 1
        If Cells(i, j).Value = "-" Then
            j = 2
            i = i + 1
            If i = 7 Then Exit Do
        End If
    Loop

    Range("G1:I10").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("I1:I10") _
            , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("G1:I10")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
End Sub

will get you complete results in columns G, H, and I.

将得到G、H和I列中的完整结果。

Pick the top three rows to get the top three results:

选择前三行，得到前三行结果:

#2

Remove the duplicates, then you can use Large() to work out the three largest numbers like this:

删除重复的数据，然后可以使用Large()计算出三个最大的数字:

=LARGE($B$2:$F$6,ROW(A1))

copied down. With these top 3 in column C you can use these two array formulas for the names:

复制下来。在C列的前三列中，可以使用这两个数组公式来命名:

=INDEX($A$1:$A$6,MIN(IF($B$2:$F$6=C10,ROW($B$2:$F$6))))

=INDEX($A$1:$F$1,MIN(IF($B$2:$F$6=C10,COLUMN($B$2:$F$6))))

Confirmed with Ctrl-Shift-Enter.

与Ctrl-Shift-Enter证实。

#1