vba 一个程序会返回一行中最小值的单元格地址？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11638712/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A program that will return the cell address of the minimum value in a row?
提问by TheTreeMan
So I have a chart that looks something like this. Assume that the top left value, 1, is in cell A1:
所以我有一个看起来像这样的图表。假设左上角的值 1 位于单元格 A1 中：
x= 1 2 3 4 5 6 7 8
4 3 2 1 2 3 4 5
9 8 7 6 7 8 9 10
8 7 6 5 4 3 2 1
Sum= 21 18 15 12 13 14 15 16
There are x values from 1 to 8, and a three columns of values resulting from using it an equation or something below it. The sum is the sum of the three values below their corresponding x-value.
有从 1 到 8 的 x 值，以及使用它一个方程或它下面的东西产生的三列值。总和是低于其对应 x 值的三个值的总和。
I'm stuck trying to figure something out that will go through the row of sums, find the smallest value, and then assign it's corresponding x-value to a variable. I also need to assign the values to the left and right of that x-value to other variables.
我被困在试图找出将通过总和行的东西，找到最小值，然后将其对应的 x 值分配给一个变量。我还需要将该 x 值左侧和右侧的值分配给其他变量。
For this particular chart, 12 is the smallest of the sums, so I would assign variable1 = 4, since that is that column's corresponding x-value. Then my second variable, which is called lowerbound, would equal 3, since it is to the left of x = 4, and my third variable, which is called upperbound, would equal 5, since it is to the right of x = 4.
对于这个特定的图表，12 是最小的总和，所以我会分配variable1 = 4，因为那是该列对应的 x 值。然后，我的第二个变量（称为lowerbound）将等于 3，因为它位于 x = 4 的左侧，而我的第三个变量（称为upperbound）将等于 5，因为它位于 x = 4 的右侧。
If I could get the cell address returned of the x-value that corresponds to the smallest sum, then I could assign it to a variable, and then simply offset from that cell to assign the other variables. Even if I could make a program that will return me the cell of the minimum sum value, I could offset to the x-row, and go from there.
如果我可以得到对应于最小总和的 x 值返回的单元格地址，那么我可以将它分配给一个变量，然后简单地从该单元格偏移以分配其他变量。即使我可以制作一个程序来返回最小总和值的单元格，我也可以偏移到 x 行，然后从那里开始。
How would I do something like that?
我怎么会做这样的事情？
TL:DR: To ask more clearly, since that's a lot of words: What would a program look like that detects the smallest value in the sum row, and returns the cell address of that value?
TL:DR：问得更清楚，因为那是很多词：检测和行中的最小值并返回该值的单元格地址的程序是什么样的？
The length of the rows are an unknown, and vary a lot, but the length of the columns are given. They do change depending on the problem, but they will always be known. So I will always know how many rows are in a column, but I will not know how many columns are in a row.
行的长度是未知的，并且变化很大，但是列的长度是给定的。它们确实会根据问题而改变，但它们将永远为人所知。所以我总是知道一列有多少行，但我不知道一行有多少列。
This is the most confusingly-worded thing I've ever written in my entire life, but I hope I've explained it well enough to make some sense.
这是我一生中写过的最令人困惑的措辞，但我希望我已经解释得足够好，使之有意义。
You guys really are amazing, by the way. I've gotten so far on this program, and it's all because of how helpful you are. I honestly think I would still be stuck at the beginning with you guys! You're willing to tolerate a newbie's incessant questions.
顺便说一句，你们真的很棒。我已经在这个程序上取得了如此大的进展，这完全是因为你的帮助。老实说，我想一开始我还是会被你们困住！你愿意容忍新手不断提出的问题。
回答by Siddharth Rout
I am assuming that the sum is in A4:H4. Please change as applicable
我假设总和在A4:H4. 请根据情况更改
You can use a formula like
你可以使用这样的公式
=CELL("address",INDEX(A4:H4,MATCH(MIN(A4:H4),A4:H4,0)))
If you want to use VBA then you can use this
如果你想使用 VBA 那么你可以使用这个
Sub Sample()
MsgBox Application.Evaluate("=CELL(""address"",INDEX(A4:H4,MATCH(MIN(A4:H4),A4:H4,0)))")
End Sub
回答by JimmyPena
Using your example, the following formula returns the cell address in row 1 whose value in row 5 is the lowest:
使用您的示例，以下公式返回第 1 行中第 5 行中的值最低的单元格地址：
=ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0))
=ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0))
And if you want that cell's value, use INDIRECT. It takes the address as a string.
如果您想要该单元格的值，请使用INDIRECT. 它将地址作为字符串。
=INDIRECT(ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0)))
=INDIRECT(ADDRESS(1,MATCH(MIN(A5:H5),A5:H5,0)))
回答by er_Hyman
If you sum the columns by taking the sum of the array. Here is the VBA version:
如果通过对数组求和来对列求和。这是 VBA 版本：
For j = 1 To 8
For i = 1 To 3
sum(j) = sum(j) + Cells(i + 1, j + 1)
Next i
Cells(5, j + 1) = sum(j)
Next j
