在两行中查找产品中的最大值

I am an excel beginner and I would like to do the following.

我是一名优秀的初学者,我想做以下几点。

Let row1= (a_1 a_2 a_3) and row2= (b_1 b_2 b_3).

设row1 =(a_1 a_2 a_3),row2 =(b_1 b_2 b_3)。

I want excel to calculate the largest number among the products (a_1b_1, a_2b_2, a_3b_3).

我想要excel来计算产品中的最大数量(a_1b_1,a_2b_2,a_3b_3)。

It is very difficult to look up these things for I am not sure what kind of calculation I am doing and it is hard to explain.

查找这些东西非常困难,因为我不确定我在做什么样的计算,而且很难解释。

3 个解决方案

#1

Take a third column, C and enter formula in C1 as $A1*$B1. Pull it down vertically to all other rows so that row number gets incremented for each.

取第三列C,然后在C1中输入$ A1 * $ B1的公式。将其垂直向下拉到所有其他行,以便每行的行数递增。

Then in the fourth column, use the formula MAX(C:C)

然后在第四列中,使用公式MAX(C:C)

#2

The following formula, array-entered, gives you the result of the largest number among the products:

以下公式(输入数组)为您提*品中最大数字的结果:

{=MATCH(A1:C1*A2:C2)}

(provided your data is in A1:C2 in the form you presented it).

(如果您的数据在A1:C2中以您提供的形式提供)。

For explanations on how to insert array formula in excel see e.g. this microsoft link; in short, you type the formula without the curly brackets and confirm with CTRL+SHIFT+ENTER instead of only ENTER.

有关如何在Excel中插入数组公式的说明,请参阅例如这个微软链接;简而言之,您键入没有大括号的公式,并使用CTRL + SHIFT + ENTER而不是ENTER确认。

If you want to find where this couple of numbers is (in your case: which column), I would try this:

如果你想找到这几个数字的位置(在你的情况下:哪一列),我会试试这个:

{=MATCH(MAX(A1:C1*A2:C2);A1:C1*A2:C2;0)}

(also array-entered).

#3

you can do that, or make a pivot with the raw data and get the MAX/MIN/AVG, based on the pivot options. I tend to use that instead and then vlookup the ID to the pivot to get whatever aggregate you need.

您可以这样做,或者使用原始数据制作轴,并根据枢轴选项获得MAX / MIN / AVG。我倾向于使用它,然后将ID查找到枢轴以获得您需要的任何聚合。

#1