|
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) 算物价的函数 物价的那个三七作五，二舍八入的尾数处理，做一个函数。就是小数点后面第二位如果是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=基数 =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)}
隔列求和（A、B列） =SUM(A:A,B:B) =SUM(A:A,B:B,C:C) 隔列求和的公式
=SUMIF($B$2:$K$2,"进",B3:K3)-SUMIF($B$2:$K$2,"出",B3:K3) =SUM(SUMIF(B$2:K$2,{"进","出"},B3:K3)*{1,-1}) 隔列求和
库存合计=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的量:
=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) 求和 所有本范例所使用的数据都为引用以下绿色区域，并定义为对应的标题
。
A 71700 简单求和 B 42200 C 22500 D 67500 E 9500 F 59200 G g.1 g.2 H 71700
分享 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
