导读:使用公式将零值显示为空白,还可以使用IF函数来判断单元格是否为零值,例如公式“=IF(A2-A3=0,个调税公式,上列公式的简化式:,算物价的函数,做一个函数,看看函数的帮助就知道了呀,公式=PERCENTILE(A3:A12,G1),应用公积金的一个函数,给公积金投缴人员制作了一个函数,我制作的函数是“=IF(MOD(INT(A1*B1),2)=0,INT(A1*B,如何利用公式将数值转为百
零值问题
在工作表中隐藏所有零值
在Excel默认情况下,零值将显示为0,这个值是一个比较特殊的数值。如果工作表中包含了大量的零值,会使整个工作表显得十分凌乱。如果要隐藏工作表中所有的零值,可以这样操作:选择“工具”→“选项”,打开“选项”对话框,单击“视图”标签,在“窗口选项”里把“零值”复选框前面的对号去掉,单击“确定”按钮。此时,可以看到原来显示有0的单元格全部变成了空白单元格。
小提示
若要在单元格里重新显示0,用上述方法把“零值”复选框前面的打上对号即可。 隐藏部分零值
有些时候可能需要有选择地隐藏部分零值,使隐藏的零值只会出现在编辑栏或正在编辑的单元格中,而不会被打印,这时候就要通过设置自定义数字格式来实现:先按住Ctrl键用鼠标左键一一选定需要隐藏零值的单元格,然后选择“格式”→“单元格”,在“单元格格式”对话框选择“数字”选项卡,在“分类”列表框中选择“自定义”选项,然后在右边的“类型”文本框中输入“0;_0;;@”,单击“确定”按钮。
要将隐藏的零值重新显示出来,可选定单元格,然后在“单元格格式”对话框的“数字”选项卡中,单击“分类”列表中的“常规”选项,这样就可以应用默认的格式,隐藏的零值就会显示出来。
条件隐藏零值
利用条件格式也可以实现有选择地隐藏部分零值:首先选中包含零值的单元格,选择“格式”→“条件格式”,在“条件1”的第一个框中选择“单元格数值”,第二个框中选择“等于”,在第三个框中输入0,然后单击“格式”按钮,设置“字体”的颜色为“白色”即可。 如果要显示出隐藏的零值,请先选中隐藏零值的单元格,然后选择“格式”菜单中“条件格式”,单击“删除”按钮,在弹出的“选定要删除的条件”对话框中选择“条件1”即可。
使用公式将零值显示为空白
还可以使用IF函数来判断单元格是否为零值,如果是的话就返回空白单元格,例如公式“=IF(A2-A3=0,"",A2-A3)”,如果A2等于A3,那么它们相减的值为零,则返回一个空白单元格;如果A2不等于A3,则返回它们相减的差值。
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【汇总计算与统计】
个调税公式
=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))}
统计偶数单元格合计数值
统计F4到F62的偶数单元格合计数值 。{=SUM(IF(MOD(ROW(F4:F62),2)=0,F4:F62))} 隔行求和公式设置
均为数组公式:
=SUM(IF(MOD(ROW(A1:A110),2),A1:A110,0))
=SUM(N(OFFSET($A$1,ROW(1:55)*2-2,,,)))
=SUM((MOD(ROW(A1:A100),2)=1)*(A1:A100))
=SUM((MOD(ROW(A1:A100),2)=0)*(A1:A100))
=SUMPRODUCT((MOD(ROW(A1:A100),2)=0)*A1:A100)
隔列将相同项目进行求和
隔列将出勤日和工资分别进行求和
数组公式=SUM(IF(($B$4:$B$25)=B26,($C$4:$C$25),0))
或;
=SUMPRODUCT(--(MOD(ROW(C5:C25),2)<>0),C5:C25)
隔行或隔列加总
隔2列加总
=SUM((MOD(ROW($A$1:$A$25),2)=0)*$A$1:$A$25)
隔2栏加总
=SUM((MOD(COLUMN($B$1:$T$1),2)=0)*$B$1:$T$1)
请问如何在一百行内做隔行相加
数组公式
A1+A3+……+A99 单
=SUM(N(OFFSET(A1,ROW(1:50)*2-2,)))
A2+A4+……+A100 双
=SUM(N(OFFSET(A1,ROW(1:50)*2-1,)))
如何将间隔一定的列的数据相加呢
碰到100多列的数据将间隔一定的数据用手工相加太烦了,也容易出错。如果需要相加的数据均有相同的名称(字段),可以用Sumif()来求解,如果没有,就需要用数组公式来解决了。{=SUM((MOD(ROW(A1:A18),3)=1)*A1:A18)} 1、4、7??行相加。
隔列求和(A、B列)
=SUM(A:A,B:B)
=SUM(A:A,B:B,C:C) (统计A、B、C列)
隔列求和的公式
=SUM(SUMIF(B$2:K$2,{"进","出"},B3:K3)*{1,-1})
联系客服