I have a few hundred rows of data, and each has a number between 1 and 200, and I'd like to put them in categories of 1-5 depending on where that number is.
我有几百行数据,每个数据都有1到200之间的数字,我想根据数字的位置将它们分为1-5类。
The categories look like this:
类别如下所示:
Zones Min Max
1 0 35
2 35 60
3 60 85
4 85 110
5 110 200
I want to assign it a Zone if it is greater than the Min, but less than the Max.
我想为它分配一个区域,如果它大于最小值,但小于最大值。
I have 2 formulas I've been working with to solve it. One is a nested IF AND statement:
我有2个公式,我一直在努力解决它。一个是嵌套的IF AND语句:
=IF(A1<=35,1,IF(AND(A1<=60,A1>35),2,IF(AND(A1<=85,A1>60),3,IF(AND(A1<=110,A1>85),4,IF(AND(A1<=200,A1>110),2,"TOO BIG")))))
The 2nd formula attempts to use a SUMPRODUCT function:
第二个公式尝试使用SUMPRODUCT函数:
=INDEX($C$2:$C$6,SUMPRODUCT(--(A1<=$E$2:$E$6),-- (A1>$D2:$D$6),ROW($2:$6)))
Rather than have to continue to adjust the numeric values manually, I set them as absolutes, which is why this formula is slightly different. The E column is the Max value set, and the D is the Min value set.
我不必手动调整数值,而是将它们设置为绝对值,这就是为什么这个公式略有不同。 E列是最大值集,D是最小值集。
Any help would be appreciated!
任何帮助,将不胜感激!
2 个解决方案
#1
2
Use this:
=MATCH(A1,{0,35,60,85,110})
#2
1
Another way is to use VLOOKUP and you just need to set the min number:
另一种方法是使用VLOOKUP,你只需要设置最小数量:
=VLOOKUP(D2,$A$2:$B$6,2,1)
The key is the 4th parameter needs to set to 1 which means TRUE. It will find the closest value and return the zone for you.
关键是第4个参数需要设置为1,表示TRUE。它会找到最接近的值并为您返回区域。
But noticed that you have overlaps like 35 or 60 etc. that you will need to adjust your value column.
但是注意到你需要调整你的值列,如35或60等重叠。
#1
2
Use this:
=MATCH(A1,{0,35,60,85,110})
#2
1
Another way is to use VLOOKUP and you just need to set the min number:
另一种方法是使用VLOOKUP,你只需要设置最小数量:
=VLOOKUP(D2,$A$2:$B$6,2,1)
The key is the 4th parameter needs to set to 1 which means TRUE. It will find the closest value and return the zone for you.
关键是第4个参数需要设置为1,表示TRUE。它会找到最接近的值并为您返回区域。
But noticed that you have overlaps like 35 or 60 etc. that you will need to adjust your value column.
但是注意到你需要调整你的值列,如35或60等重叠。