常用函数公式及操作技巧之六,excel多重条件公式

作者: 金沙澳门官网  发布:2019-10-09

问题:怎么实现在B2输入排量,在C8自动显示车船税的金额?(二次费改后的金额)谢谢赐教!

 (Excel)常用函数公式及操作技巧之六:

回答:

汇总计算与统计(一)

大家好,我是@EXCEL实例视频网站长@欢迎关注我的头条,每天都会分享Excel实战技巧!

——通过知识共享树立个人品牌。


 

这个是典型的多条件取数,阶梯条件,你首先要重新整理条件为标准格式,按我下面方法操作即可

 

EXCEL--多条件取值终极解法SUMPRODUCT高级应用

金沙澳门官网 ,上次介绍了一个lookup进行多条件判断取值的解法,不过今天突然发现那个解法有个严重的缺陷,就是只能解a<=A<b,就是当条件都是大于等于较小值且小于较大值的情况,今天又有人问了一个a<A<=b 及大于较小值而小于等于较大值的多条件 那个公式在节点上就判断不准了,我又实在不想用if函数去做,嵌套一大堆得if 看着都累,先后试验了vlookup,Hlookup,match,发现它们的内在原理都是一样的 没办法只好请出SUMPRODUCT大神了

实例如下

输入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*即可了

实例截图如下

金沙澳门官网 1

SUMPRODUCT实乃excel函数中大神 经常可以用它来完成一些不可思议的任务

回答:

金沙澳门官网 2

不知道怎么图被吃了一张…

回答:

用vlookup匹配应该可以解决问题。

说实在的,需要的话私信,发表格给我,方便交流问题。这样抛出几张图,录入数据的时间都需要好长。

欢迎关注我的头条号,如果有excel方面的问题,可以私信交流,为你答疑解惑。

个调税公式

=MAX(($A1-1900)*{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45}
-{0,0,25,125,375,1375,3375,6375,10375,15375})
{0,0.05,0.1,0.15,0.2,0.25,0.3,0.35,0.4,0.45} 为税率
{0,0,25,125,375,1375,3375,6375,10375,15375}  为税收扣除数

上列公式的简化式 :

=MAX(应纳税所得额*0.05*{1,2,3,4,5,6,7,8,9}
-25*{0,1,5,15,55,135,255,415,615},0)

算物价的函数

物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果是1,2的就舍掉,如果是3,4,5,6,7的都变为5,如果是8,9的小数点第一位加1,第二位就变为0。比如价格是3.32、3.31,作尾数处理就是3.3;价格是3.33、3.34、3.36、3.37,做尾数处理就是3.35;价格是3.38、3.39,做尾数处理就是3.4。

=CEILING(A1-0.02,0.05)

都是二位小数 

B2=ROUND(2*A2,1)/2

超过二位小数 

B2=ROUND(2*ROUNDDOWN(A2,2),1)/2

自动计算应收款滞纳金

要求在给定的应收日期、滞纳金率、当前日期(自动取)的基础上自动计算出应收滞纳金。

解答:

=(DATEDIF(应收日期,NOW(),"d"))*滞纳金率(每天)*应收金额

淘汰率

题目如下:这个工厂有1000人,今天抽出十人来做调查,这十人一天的产量分别为101 102 105 106 98 95 96 104 110 103 (A3-A12)。

1000人当中淘汰率为5%,以这十人为标准那么这1000人他们的生产应该为多少才不会被淘汰,看看函数的帮助就知道了呀,返回数组K百分比值点,你要1000人淘汰5人就是5/1000=0.5%=0.005,就是你以这10个抽样调查的数据为基准,只要产量达到这个数就不会被淘汰了。(95.45)

公式=PERCENTILE(A3:A12,G1)

应用公积金的一个函数

我公司职工公积金比例为26% 也就是个人和单位各13%,给公积金投缴人员制作了一个函数。直接用基数乘以比例 基数*比例=投缴额, 对于投缴额的要求是:取最接近“投缴额”的偶数。

我制作的函数是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),CEILING(A1*B1,2))

注:A1=基数  B1=投缴比例

    也可以改成这样

=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),INT(A1*B1) 1)
或=IF(MOD(INT(A1*B1),2)=0,INT(A1*B1),EVEN(A1*B1))

如何利用公式将数值转为百分比格式

如用公式将1.289675显示为128.97%,不是用格式来达到的。

=ROUND(B1*100,1)&"%"

比高得分公式

=RANK(B4,$B$4:$B$26,1)

自动评定奖级

=VLOOKUP(L179,IF({1,0},$D$204:$D$207,$B$204:$B$207),2)
=LOOKUP(L179,{0,4,7,12,24},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

对带有单位的数据如何进行求和

在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如:A1:2KG,A2:6KG.....,在最后一行自动计算出总KG数)。

=SUMPRODUCT(--LEFT(A1:A5,(LEN(A1:A5)-2)))&”KG”

对a列动态求和

可以随着a列数据的增加,在“b1”单元格=sum(x)对a列动态求和。 

=SUM(OFFSET(A1,0,0,COUNTA(A:A),1))

动态求和公式

自A列A1单元格到当前行前面一行的单元格求和。

=SUM(INDIRECT("A1:A"&ROW()-1))

列的跳跃求和

若有20列(只有一行),需没间隔3列求和,该公式如何做?

假设a1至t1为数据(共有20列),在任意单元格中输入公式:

=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

按ctrl+shift+enter结束即可求出每隔三行之和。

跳行设置:如有12行,需每隔3行求和

=SUM(IF(MOD((ROW(1:12)),3)=0,(A1:A12)))

有规律的隔行求和

要求就是在计划、实际、差异三项中对后面的12个月求和。

=SUMPRODUCT(--(MOD(COLUMN(F3:AO3)-CELL("Col",F3) 0,3)=0),F3:AO3)
=SUMIF($F$2:$AO$2,C$2,$F3:$AO3)
=SUMPRODUCT((MOD(COLUMN($F3:$AO3),3)=MOD(COLUMN(F3),3))*$F3:$AO3)

也可以拖动填充,插入行、列也不影响计算结果。

如何实现奇数行或偶数行求和

假设数据在A1:A100

奇数行:=SUMPRODUCT(MOD(ROW($A$1:$A$100),2)*$A$1:$A$100)

偶数行:=SUMPRODUCT((MOD(ROW($A$1:$A$100),2)=0)*($A$1:$A$100))

奇数行求和 =SUMPRODUCT((A1:A100)*MOD(ROW(A1:A100),2))

偶数行求和 =SUMPRODUCT((A1:A100)*NOT(MOD(ROW(A1:A100),2)))

单数行求和

隔行求和用什么函数,即:A1 A3 A5 A7 A9…公式如何用。

{=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))}

{=SUM(IF(MOD(ROW(A1:A100),2)=1,A1:A100,0))}

本文由金沙澳门官网发布于金沙澳门官网,转载请注明出处:常用函数公式及操作技巧之六,excel多重条件公式

关键词: 金沙澳门官网