具有多个IF的Excel比较 [英] Excel comparison with multiple IFs

问题描述

我有一个比例，可以据此确定乘法系数的值.比例尺如下所示:

I have a scale, based on which I decide the value of the coefficient for the multiplication. The scale looks as following:

这意味着:

• 表示Category1:当value>=1.000.000时coef是1，当value>=500.000时coef是0.8等.

• for Category1: when value>=1.000.000 then coef is 1, when value>=500.000 then coef is 0.8 and etc.

相同的逻辑适用于Category2;

然后我以以下格式输入数据:

Then I have input data in the following format:

Company !MainCat|Sales Amount|
Company1|T1     |   6.500.000|
Company2|T2     |      70.000|

我需要找到相应的系数，系数的比率和值(=ratio*MaxCoef).目前，我正在通过以下方式找到coef: -对于公司1:

I need to find corresponding coefficient, ratio of the coeffitient and the value (=ratio*MaxCoef). Currently, I am finding coef the following way: - for company1:

 =IF(C8>=$D2;$D$1;IF(C8>=$E2;$E$1;IF(C8>=$F2;$F$1;IF(C8>=$G2;$G$1;IF(C8>=$H2;$H$1;IF(C8>=$I2;$I$1))))))

这实际上是硬编码的，看起来不太好.也许有更好的方法吗?有什么建议吗?

That is literally hardcoded and doesn't look good. Maybe there is a better way of doing ? Any suggestions?

公式视图:

推荐答案

您可以COUNTIF(range, [criteria] < value) * 0.2作为每个coef阶段的添加0.2.

You can COUNTIF(range, [criteria] < value) * 0.2 as your add 0.2 per coef stage.

要进行数据处理，请执行以下操作:=COUNTIF(D2:H2, "<"&C8) * 0.2，计算值通过多少级*每级值.

To you data do: =COUNTIF(D2:H2, "<"&C8) * 0.2, count how many stages the value passes * the value per stage.

如果范围必须一直到H2，因为I2是0，则您的计数，因此不及value并得到计数.

Your count if range needs to be until H2 as I2 is 0, so inferior to value and gets counted.

要将COUNTIF()与基于MainCat的正确类别的动态搜索结合在一起，可以将MATCH() MainCat与Code结合使用，这将给出row所在的位置，并给出row利用INDIRECT()将其应用为range.

To combine the COUNTIF() with a dynamic search for the right category based on MainCat you can MATCH() the MainCat with Code which will give the row where the Code is located and utilize INDIRECT() to apply it as range.

=COUNTIF(INDIRECT("D"&MATCH(B8,B:B,0)&":H"&MATCH(B8,B:B,0)),"<"&C8)*0.2

MATCH(B8,B:B,0)-将与B8上的值匹配(让我们说T1)并返回row 2.

MATCH(B8,B:B,0) - will match the value on B8 (lets say T1) and return the row 2.

INDIRECT("D"&MATCH(B8,B:B,0)&":H"&MATCH(B8,B:B,0) = INDIRECT("D"&2&":H"&2)-将文本转换为实际的range，供COUNTIF()使用.

INDIRECT("D"&MATCH(B8,B:B,0)&":H"&MATCH(B8,B:B,0) = INDIRECT("D"&2&":H"&2) - will turn the text into an actual range to be use by the COUNTIF().

这篇关于具有多个IF的Excel比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！