甘苦人生2010 / execl文章 / EXCEL常用函数公式及技巧搜集之五

分享

   

EXCEL常用函数公式及技巧搜集之五

2017-01-29  甘苦人生2...

EXCEL常用函数公式及技巧搜集之五

将单元格设置为有“凸出”的效果或“凹进去”的效果

用条件格式=mod(row(),2)=mod(column(),2)

方法是设定单元格的边框

3楼的办法不错,但是要一个格一个格地设定,数据多了很麻烦

2楼的格式里设公式能不能搞成隔一行ao隔一行tu的形式呢?

格式—自动套用格式里就有。

凑个热闹。边框用黑白的就可以了

看来还是用条件格式更方便些!

用黑白双线边框是最简单的办法

在Excel中设计彩色数字

  用户在使用Excel处理数据时,经常需要将某些数据以特殊的形式显示出来,这样可以起到醒目的作用,使浏览者一目了然。如在某用户的Excel单元格中有“月工资”一栏,需要小于500的显示为绿色,大于500的显示为红色,则可以采用以下的方法来操作:选中需要进行彩色设置的单元格区域,选择“格式”→“单元格”,在弹出的对话框中单击“数字”选项卡。然后选择“分类”列表中的“自定义”选项,在“类型”框中输入“[绿色][<500;[红色][>=500]”,最后单击“确定”按钮即可。

  小提示

  除了红色和绿色外,用户还可以使用六种颜色,它们分别是黑色、青色、蓝色、洋红、白色和黄色。另外,“[>=120]”是条件设置,用户可用的条件运算符有:“>”、“<”、“>=”、“<=”、“=”、“<>”。当有多个条件设置时,各条件设置以分号“;”作为间隔。

定义名称的妙处

名称的定义是EXCEL的一基础的技能,可是,如果你掌握了,它将给你带来非常实惠的妙处!

1. 如何定义名称

插入-名称-定义

2. 定义名称

建议使用简单易记的名称,不可使用类似A1…的名称,因为它会和单元格的引用混淆。还有很多无效的名称,系统会自动提示你。

引用位置:可以是工作表中的任意单元格,可以是公式,也可以是文本。

在引用工作表单元格或者公式的时候,绝对引用和相对引用是有很大区别的,注意体会他们的区别 – 和在工作表中直接使用公式时的引用道理是一样的。

3. 定义名称的妙处1 – 减少输入的工作量

如果你在一个文档中要输入很多相同的文本,建议使用名称。例如:定义DATA = “I LOVE YOU, EXCEL!”,你在任何单元格中输入“=DATA”,都会显示“I LOVE YOU, EXCEL!”

4. 定义名称的妙处2 – 在一个公式中出现多次相同的字段

例如公式=IF(ISERROR(IF(A1>B1,A1/B1,A1)),””, IF(A1>B1,A1/B1,A1)),这里你就可以将IF(A1>B1,A1/B1,A1)定义成名称“A_B”,你的公式便简化为=IF(ISERROR(A_B),””,A_B)

5. 定义名称的妙处3 – 超出某些公式的嵌套

例如IF函数的嵌套最多为七重,这时定义为多个名称就可以解决问题了。也许有人要说,使用辅助单元格也可以。当然可以,不过辅助单元格要防止被无意间被删除。

6. 定义名称的妙处4 – 字符数超过一个单元格允许的最大量

名称的引用位置中的字符最大允许量也是有限制的,你可以分割为两个或多个名称。同上所述,辅助单元格也可以解决此问题,不过不如名称方便。

7. 定义名称的妙处5 – 某些EXCEL函数只能在名称中使用

例如由公式计算结果的函数,在A1中输入’=1+2+3,然后定义名称 RESULT = EVALUATE(Sheet1!$A1),最后你在B1中写入=RESULT,B1就会显示6了。

还有GET.CELL函数也只能在名称中使用,请参考相关资料。

8. 定义名称的妙处6 – 图片的自动更新连接

例如你想要在一周内每天有不同的图片出现在你的文档中,具体做法是:

8.1 找7张图片分别放在SHEET1 A1至A7单元格中,调整单元格和图片大小,使之恰好合适

8.2 定义名称MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)

8.3 控件工具箱 – 文字框,在编辑栏中将EMBED("Forms.TextBox.1","")改成MYPIC就大功告成了。

这里如果不使用名称,应该是不行的。

此外,名称和其他,例如数据有效性的联合使用,会有更多意想不到的结果。

零值问题

在工作表中隐藏所有零值

  在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)

算物价的函数

物价的那个三七作五,二舍八入的尾数处理,做一个函数。就是小数点后面第二位如果是12的就舍掉,如果是34567的都变为5,如果是89的小数点第一位加1,第二位就变为0。比如价格是3.323.31,作尾数处理就是3.3;价格是3.333.343.363.37,做尾数处理就是3.35;价格是3.383.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},{"一等奖","二等奖","三等奖","纪念奖","纪念奖"})

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

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

在数据后必须加入单位,到最后还要统计总和,请问该如何自动求和?(例如:A12KGA26KG.....,在最后一行自动计算出总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列求和,该公式如何做?

假设a1t1为数据(共有20列),在任意单元格中输入公式:=SUM(IF(MOD(TRANSPOSE(ROW(1:20)),3)=0,(a1:t1))

ctrlshiftenter结束即可求出每隔三行之和。

跳行设置:如有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……行相加。

隔列求和(AB列)

=SUM(A:A,B:B)

=SUM(A:A,B:B,C:C)   (统计ABC列)

隔列求和的公式

品种及日期

1月1日

1月2日

1月3日

1月4日

1月5日

余额

 

 

A

1

1

2

5

3

2

7

9

8

1

3

=SUMIF($B$2:$K$2,"",B3:K3)-SUMIF($B$2:$K$2,"",B3:K3)

=SUM(SUMIF(B$2:K$2,{"",""},B3:K3)*{1,-1})

隔列求和

类别

成品代码

 单价

安贞

北辰

长安

长春

合计

库存

销售

库存

销售

库存

销售

库存

销售

库存

销售

皮带

V19201

  270.00

1

2

1

2

1

2

1

2

 

 

库存合计=SUMIF($D$3:$BS$3,"库存",$D$4:$BT$4),
销售合计=SUMIF($D$3:$BS$3,"销售",$D$4:$BT$4)

=SUMIF($D$3:$BS$3,BT$3,$D4:$BS4)

=SUMPRODUCT((MOD(COLUMN($D4:$BS4),2)=0)*$D4:$BS4)

关于隔行、隔列求和的问题

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)

均为数组公式。

EXCEL中求两列的对应元素乘积之和

:a1*b1+a2*b2+b3*b3...的和

=SUM(A1:A3*B1:B3) (数组公式)

=SUMPRODUCT(A1:A10,B1:B10)

计算900~1000之间的数值之和

sumif函数的计算格式为: =sumif($a$1:$a$20,">1000")。即返回$a$1:$a$20中大于1000的数值的和,但如果想计算900~1000之间的数值之和,应该如何编写。

请参考:{=SUM(IF((A1:A20>900)*(A1:A20<1000),A1:A20))}{=SUM((900<1000)*A1:A20)}

2=SUMIF(A1:A20,">900")-SUMIF(A1:A20,">1000")

双条件求和

1 求一班女生的个数 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""""))

2、求一班成绩的和 :

=SUMIF(A2:A9,1,C2:C9) "                             

3、求一班男生成绩的和 :

=SUMPRODUCT((A2:A9=1)*(B2:B9=""""),C2:C9) "             

如何实现这样的条件求和

求型号中含BC但不含ABC的量:

A

  B

C

 

 

型号

数量

 

1

CRVABC12

100

 

2

CVABC13

102

 

3

CVBC12

104

 

4

CNVBC13

106

 

=SUMIF($A$2:$A$12,"*"&"bc"&"*",$B$2:$B$12)-SUMIF($A$2:$A$12,"*"&"abc"&"*",$B$2:$B$12)

=SUMPRODUCT((ISNUMBER(FIND("BC",A2:A12))<>ISNUMBER(FIND("ABC",A2:A12)))*B2:B12)

A1:A10数字显为文本格式时,如何求和

=SUMPRODUCT(A1:A10)

求和

所有本范例所使用的数据都为引用以下绿色区域,并定义为对应的标题          

Name

Sex

Age

Position

Salary

张无忌

26

主角

10000

韦小宝

16

主角

13000

灭绝

55

配角

3000

周芷若

22

主角

8000

鳌拜

62

普通演员

2000

仪琳

18

配角

5000

岳灵珊

19

配角

4500

令狐冲

27

主角

15000

性空

88

普通演员

2200

东方不败

不详

45

主角

9000

  求所有演员工资总额                          

71700    =SUM($G$7:$G$16)

简单求和

  求男演员工资总额  

42200    =SUMIF($D$7:$D$16,"",$G$7:$G$16)

       单条件求和.1

  求年龄在20岁以下的演员工资 

22500    =SUMIF($E$7:$E$16,"<20",$G$7:$G$16)

       单条件求和.2

  求主角和配角的工资(不是普通演员)

67500    =SUMIF($F$7:$F$16,"*",$G$7:$G$16)

       单条件求和.3

   20岁以下女演员工资      

9500      {=SUM(($D$7:$D$16="")*($E$7:$E$16<20)*$G$7:$G$16)}

       多条件求和-同时满足条件

   求男性或主角的工资     

59200    {=SUM(IF(($D$7:$D$16="")+($F$7:$F$16="主角"),$G$7:$G$16))}

       多条件求和-只须满足条件之一

  求男性非主角或主角非男性的工资(即除男主角外的男性和主角

g.1  21200    {=SUM(IF(($D$7:$D$16="")-($F$7:$F$16="主角"),$G$7:$G$16))}

g.2  21200    {=SUM(IF(($F$7:$F$16="主角")-($D$7:$D$16=""),$G$7:$G$16))}

       多条件求和-只满足条件之一而不能同时满足

   ~~~你不知道什么是数组函数啊,可是你有时候也要用多条件求和?  

       不要紧,教你用另外的方法:SUBTOTAL   

       20岁以下女演员工资      

71700    =SUBTOTAL(9,$G$7:$G$16)

       现在你看到的还不是最后结果,请按如下操作   

       1、把数据区域设置成可筛选     

       2、把SEX筛选成"=", 把年龄筛选成<20  

 

       3、你再看上面的公式结果…      

分享

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多