返回目录:excel表格制作
如何利用Excel函数对多条件区间进行判断取值?
首先我们来了解下INDEX函数:
=INDEX(单元格区域或数组常量,行序号,列序号)
返回行和列交叉位置的值
栗子1:查询满足I2,J2两个条件的值 公示详解如下图:
=INDEX(A1:G10,MATCH(I2,B1:B10,0),MATCH(J2,A1:G1,0))
栗子2:查询满足I2,J2,K2这三个条件的值
=INDEX(A1:G10,MATCH(I2&J2,A1:A10&B1:B10,0),MATCH(K2,A1:G1,0)) 在L2单元格输入公式后,Ctrl+Shift+Enter 后公式才有用!因为MATCH(I2&J2,A1:A10&B1:B10,0)这部分包含数组! 这个公式是就不多说了,和栗子1差不多! 不当之处请高手们指教!谢谢先!
excel多重条件公式请教?
大家好,我是@EXCEL实例视频网站长@欢迎关注我的头条,每天都会分享Excel实战技巧!
这个是典型的多条件取数,阶梯条件,你首先要重新整理条件为标准格式,按我下面方法操作即可
EXCEL--多条件取值终极解法SUMPRODUCT高级应用
上次介绍了一个lookup进行多条件判断取值的解法,不过今天突然发现那个解法有个严重的缺陷,就是只能解a<=A
实例如下
输入500>=a1>0 的数的时候,B1显示为A1*0.1
输入2000>=A1>500 B1显示为A1*0.2
输入5000>=A1>2000 B1显示为A1*0.3
输入20000>=A1>5000 B1显示为A1*0.4
有人会说 这还不简单那 几个if就可以了 恭喜你 答对了! 但是我现在要做的是 如果有N个以上这样的条件 你还能if么,那公式写出来只怕累死你 而且if只能嵌套7层的,其实我最想做的是将公式简化 简化成容易理解容易使用的形式 废话少说 开始分析
1、本问题核心是A1*X,X的取值根据A1的范围变化,好了 核心就是求X
2、抽出A1条件和X对应结果 简单得出以下数列,
123>0<=5000.1>500<=20000.2>2000<=50000.3>5000<=200000.4
到现在 事情就变得比较简单的 这个问题抽象成数学逻辑就变成了
对1,2列数据进行逻辑与运算 其结果再乘以3列数据 由于只有一行的数据会符合要求 而其他行注定会等于0 所以对每行的运算结果求和 就是最终的X取值了
说了一大堆理论 估计很多人看不懂 我表达能力实在有限啊 看不明白的就略过吧
上面的结论 最适合的公式就是SUMPRODUCT了 这个函数其本质就是数组求和 实际上可以分解成sum函数的数组公式 但是它的优点就是用普通公式完成了数组公式的效果 在这里它最大的作用就是进行逻辑与运算和数组求和 公式其实挺简单
纯数字版的:
=SUMPRODUCT((A1>{0,500,2000,5000})*(A1<={500,2000,5000,20000})*{0.1,0.2,0.3,0.4})
别看这觉得吓人 其实你几个括号的值和上面的数列一对比 就很简单了
实际使用的时候 可以用单元格区域代替{}例如
00.15000.220000.350000.420000
这是F9:G13的单元格区域 公式就相应改为
=SUMPRODUCT((A1>$F$9:$F$12)*(A1<=$F$10:$F$13)*($G$9:$G$12))
这里 当你的条件更多时 一样可以套用这个公式 只要把条件区域做相应改变即可
到此为止 这就算出了对应X的值 剩下的就是在公式前面加上A1*即可了
实例截图如下
SUMPRODUCT实乃excel函数中大神 经常可以用它来完成一些不可思议的任务