想实现如下功能,怎么写公式:
当0<p2<30,Q2等于4,
当30≤p2<60,Q2等于6,
当60≤p2<90,Q2等于8,
当90≤p2<120,Q2等于10,
当120≤p2<150,Q2等于12,
当150≤p2<180,Q2等于14,
这个题目其实使用lookup是最 简单,使用IF写公式,是一个串联的关系
1,IF我们相对好理解,但是公式写起来比较长,我们先用IF来写
B1的公式是:=IF(A1<30,4,IF(A1<60,6,IF(A1<90,8,IF(A1<120,10,IF(A1<150,12,IF(A1<180,14,""))))))
这里将180以上的区间显示为空了,题目中没有描述≥180的情况
2、使用lookup来做这个公式
PQ作为基础数据区域,因≥180的情况没有描述,暂时就将其设置为空值,所以在Q7单元格内输入可以一个空格
B1公式:=LOOKUP(A1,P1:P7,Q1:Q7)
这里借助了辅助区域 ,我们也可以将辅助区域变成固定的,我们选中公式中的P1:P7,然后按下F9,就变成了固定了
同理我们将Q1:Q7也使用F9转换成固定的
最后公式变成更了=LOOKUP(A1,{0;30;60;90;120;150;180},{4;6;8;10;12;14;" "})
这样的过程描述了之后,应该很清晰了··
对于多个区间段的判断写法,Excel 2016以上版本用户使用IFS函数更为简单,定位到Q2单元格输入公式“=IFERROR(IFS(AND(0<P2,P2<30),4,AND(30<=P2,P2<60),6,AND(60<=P2,P2<90),8,AND(90<=P2,P2<120),10,AND(120<=P2,P2<180),12,AND(150<=P2,P2<180),14),"")”,然后下拉填充即可。
注意:在IFS函数的起始区间判断要嵌套AND函数来表述,比如“AND(0<P2,P2<30),4”,表示如果P2的值大于0,且P2<30,那么就显示为数字“4”,每个条件一组判断。在最外层嵌套IFERROR函数,表示在出现错误值(如P2=181)时显示为空值。
函数实例