“0”活多变的函数公式与格式

第一篇：“0”活多变的函数公式与格式

“0”活多变的函数公式与格式

如果你问一个学前班或者一年级的小朋友，0表示什么？他会毫不犹豫的告诉你，0表示没有，比如草地上一只羊也没有，老师就叫我们用0表示。早上爸爸给我买了两个苹果，我吃了一个，弟弟也吃了一个，现在一个也没有，就用0表示。这样的例子小朋友还可以说得很多。

小朋友说的没错，0表示“没有”可能是0最早的意思吧，也就是0的本义。古时候的人最初完全没有数量这个概念，后来由于记事和分配生活用品等方面的需要，才逐渐产生了数的概念。比如捕获了一头野兽，就用1块石子代表。捕获了3头，就放3块石子。假如什么都没有捕获，当然是0头了。这样就产生了数，各国的人们也学会了用不同的符号表示不同的数字，但人们最后学会的是怎么表示0，因为其他的数字都比较好表示，所以后来有人把铜钱摆在空位上，以免弄错，这就表示0。不过多数人认为，“0”这一数学符号的发明应归功于公元6世纪的印度人。他们最早用黑点（·）表示零，后来逐渐变成了“0”。

那E氏函数家族中的“0”也真像小朋友所说的那样表示什么也没有吗？不然，0的活用与不用蕴藏着很多意想不到的玄机。到底有怎样的玄机呢？那我就0机一动开处方，虽然不算什么0当妙药”，闲话少说，E切从0开始，一起来看看0牙利齿吧！

一、活“0”活现

（一）简单文本求和中0的作用（+0或-0）

例子：将A列A1:A10的数字相加，其中可能还有文本型的数字也需要相加。公式： =SUMPRODUCT（A1:A10+0）或者：

=SUMPRODUCT（A1:A10-0）

解析：初级用户会觉得+0,-0不就等于没有增加，没有减少嘛，为何要这样呢?是啊，要的就是这个效果，既要改变原数据的性质（文本转变为数值），又要准确计算，所以只有用+0,-0，这一“+”或“-”符号就是改变原数据的性质的。这一带符号的0犹如一个“小石头”，从后面抛出去将昏睡中的“大石头”（数字）砸醒。

参考文章:文本转数值的十一种方法（百度一下可查询到）

（二）“0”嶺先锋

例①、如：单元格A1中输入数字12304568579213（15位以下，文本或数值型均可）要将这个数的每一位相加，公式：

=SUM(--(0&MID(A1,COLUMN(1:1),1)))解析:因单元格字符串长度只有14位提取长度为1至256位的长度，所以从15位开始，只能提取到空值。效果如下：

=SUMPRODUCT(--(0&{“1”,“2”,“3”,“0”,“4”,“5”,“6”,“8”,“5”,“7”,“9”,“2”,“1”,“3”,“",……,”“}))前面补0后的效果如下：

=SUMPRODUCT(--{”01“,”02“,”03“,”00“,”04“,”05“,”06“,”08“,”05“,”07“,”09“,”02“,”01“,”03“,”0“,……,”0“})

此时没有空值，只有14个文本数字和文本0，前面加2个负号后，转化为数值，看效果：

=SUMPRODUCT({1,2,3,0,4,5,6,8,5,7,9,2,1,3,0,……,0})没有空值，且全部为数值就可以相加了，结果为56。

例②、单元格A1输入：123大理789，要将这个单元格的每一位数字相加，公式： =SUMPRODUCT(--(0&MIDB(A1,COLUMN(1:1),1)))与上例不同的是，MIDB会将每个双字节（如汉字就是双字节）字符按2计数，否则，函数MIDB会将每个字符按1计数。当只提取1个字节时，遇到汉字（双字节），只能提取到半个汉字（也就是空值），效果如下：

=SUMPRODUCT(--(0&{”1“,”2“,”3“,” “,” ……,“"}))0&后填补空值。

二、脱胎换骨—化“文”为“0”

单元格A1输入123abcABC789,要将这个单元格的每一位数字相加，公式： =SUMPRODUCT(--TEXT(MID(A1,COLUMN(1:1),1),”0;;0;“))解析：由于字符串中有“abcABC”，是单字节字符，所以不能象上例那样用MIDB提取半个汉字的办法来处理。此时，我们仍用MID来提取的基础上，再请出“霸道，聪明”的TEXT函数，将非数字字符强行改为0，若为数值则不变。条件参数”0;;0;“中第一个0神通广大，代表了除0之外的任意正整数，也就是假0（是通配数值的0），第二个则是“苍蝇嘴巴狗鼻子—真0”，第三个0是强行做“变性”手术后的0。

三、“0”补队员

①单元格A1输入数字12378945600123，如何将单元格内数字按顺序去重。公式：

=MID(SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&”00“,3,COUNT(FIND(ROW($1:$10)-1,A1)))或者：

=MID(SUM(MID(A1&56^7,SMALL(FIND(ROW($1:$10)-1,A1&56^7),ROW($1:$10)),1)/10^ROW($1:$10))&0,3,COUNT(FIND(ROW($1:$10)-1,A1)))解析：

”(0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))”中，前面补0，是为了填补空值，这里不再赘述，式子：SUM((0&MID(A1,SMALL(FIND(ROW($1:$10)-1,A1&5^19),ROW($1:$10)),1))/10^ROW($1:$10))&”00“中&”00”的作用有两个，一是防止计算出的0在最后被忽略；二是单元格中仅输入一个或多个0时，最后能提取到一个0。

四、忘我(“0”)牺牲

①如：单元格A1:A5中有字符串，也有文本数字。01 03 0 #VALUE!大理789 问题：统计A1：A5中非0数字（非0文本型数字和数值都算）有几个？ 数组公式：

=COUNT(0/A1:A5)解析：

由于0和文字不能做除数，我们将违背这一原理，把A1:A5作为除数，让0和文字出现错误值。效果：

{0;0;#DIV/0!;#VALUE!;#VALUE!} 按我兄弟顺溜的话来说，让他们（0和文字）都死球。这活下来的“英雄”就是我们要数的“人”（非0数字个数）了。于是我们让SUM，SUMPRODUCT，ISERR，ISERROR，ISNUMBER等几位大侠先“下岗”，只聘请数数高手“COUNT”大侠。

=COUNT({0;0;#DIV/0!;#VALUE!;#VALUE!})=2

五、隐居山“0”

如单元格A2中输入字符串”☯ABC❀wshcw中国云南大理abc♦OWY♥Excelhome☀”

问题: 如何提取汉字：”中国云南大理“ 公式：

=MID(LEFT(A2,MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“))),MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“),),99)没有用简写的原公式：=MID(LEFT(A2,MATCH(0,0/(MID(A2,COLUMN(2:2),1)>=”吖“))),MATCH(0,0/(MID(A2,COLUMN(2:2),1)>=”吖“),0),99)解析：

1、公式中“0/(MID(A2,COLUMN(2:2),1)>=”吖“)”由于汉字最小是”吖“，只要大小等于”吖“，就说明它是汉字，这部分的作用是将小于“吖”的字符经判断后作为分母（分母为0），继而出错（也就排除了小于“吖”的部分，换句话说，也就是牺牲非0的字符），由于分子为0，继而赢得汉字演变为0的胜利。

2、MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“))这部分是定位最后一个汉字的位置，值得注意的是：前一个英文“,”前省略了一个0，作用是定位最后一个0的位置。

3、MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“),)这部分是定位最前一个汉字的位置，值得注意的是：最后一个反括号前“)”前省略了一个0，作用是定位最前一个0的位置。

MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“))与MATCH(,0/(MID(A2,COLUMN(2:2),1)>=”吖“),)看上去只有一逗(“,”)之差,但“差以毫厘，谬以千里”。前者：目标远大，把潜力发挥到极限，后者因被眼前的”,”号所诱惑，目标只定位在眼前，目光短浅。这函数也象人生一样，只有小智慧与大智慧的结合，才能使函数家族兴旺发达。

六、居高“0”上（0次方的用法）

例A2:A7输入： 字符串

bbbccew-58人ＬＫ民

ＡＹＵBMMM主人123９６５ ABCR（空白）

lBMMM主人-1 mc76yk 中国

问题：要将A2:A7的单元格数据汇总求和。公式：

=SUM(-TEXT(MID(A2:A7&”@“,COLUMN(1:1),MMULT(1-ISERR(-MID(A2:A7&”a1“,COLUMN(1:1),2)),ROW(1:256)^0)),”-0%;0%;0;!0“))解析：

1、先算出每个单元格含有的数字个数，再按这个个数分别逐个提取。

2、公式中：MMULT(1-ISERR(-MID(A2:A7&”a1“,COLUMN(1:1),2)),ROW(1:256)^0)就是算出每个单元格含有的数字个数，那么” ^0”为何爬得如此高呢？这是因为ROW(1:256)^0)是常量数组{1;„„;1;1}的缩写。是序列数1到256的0次幂，也就是256个1的数组。这0次方的妙用是E友对EXCEL不断开拓创新的结果。

七、“0”的伪装（TRUE，FALSE）

例①A1单元格是785，B1单元格是358017。如何从B1中将A1的7、8、5替换掉，在C1得出301 =SUM(MID(0&B1,LARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1,1)*10^COLUMN(1:1))/10

公式分析：

1、FIND(MID(B1,COLUMN(1:1),1),A1)=FIND(MID(B1,{1,2,3,4,5,6,7,……,256},1),A1)=FIND({”3“,”5“,”8“,”0“,”1“,”7“,”“,……,”“},785)={#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1}

用MID分解字符串，得到一个数组，大家已经很熟悉了，由B1单元格数字得到一个数组：{”3“,”5“,”8“,”0“,”1“,”7“,”“,„„,”“}。然后用FIND查找数组中每个数据在A1单元格数字中的位置，先查找”3“，A1中没有3，那么结果是错误值#VALUE!，接下来找”5“，在A1单元格数字的第3个位置，结果便是3，再找”8“，结果是2，依次找下去，当查找空值”“时，结果都是1，这可以理解为用FIND找空值，空值永远在字符串第1个位置。

2、ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))=ISERR({#VALUE!,3,2,#VALUE!,#VALUE!,1,……,1})={TRUE,FALSE,FALSE,TRUE,TRUE,FALSE,……,FALSE}

我们又遇到一个信息函数ISERR，它是检测一个数据是否为错误值（#N/A以外），如果是错误值返回TRUE，不是错误值返回FALSE，形象地理解为：错的就是对的，对的成了错的，真是“真亦假来假亦真，假亦真来真亦假”。

3、ARGE(ISERR(FIND(MID(B1,COLUMN(1:1),1),A1))*COLUMN(1:1),COLUMN(1:1))+1 这一步是算出查找不到的第1至256个最大值。运行后的效果： {8,7,4,3,1,……,1} 代入公式：

=SUM(MID(0&B1,{8,7,4,3,1,……,1},1)*10^COLUMN(1:1))/10 进一步提取得到： =SUM({”1“,”0“,”3“,”0“……”0“}*10^COLUMN(1:1))/10 再运算：

=SUM({10,0,3000,0,……,0})/10 =301 例②A2:A7单元格输入: ★★★★★★★★★欢迎光临我的百度空间★★★★★★★★★★ ∽∽∽∽∽∽∽∽∽∽我的函数主题与大家分享∽∽∽∽∽∽∽∽∽∽ 1235云南

【】☆‟℃丂丮云南大理镕DAW12 123ＯＰ4ABYTＱＲＴＯＮٷ 【】龥县丮云南大理丂☆‟℃% 问题：要求取出单元格内的汉字字符串： =MID(A2,MATCH(TRUE,MID(A2&”咗“,ROW($1:$50),1)>=”吖“,),SUM(N(MID(A2,ROW($1:$50),1)>=”吖“)))变通为：

=MID(A2,MATCH(1>0,MID(A2&”咗“,ROW($1:$50),1)>=”吖“,),SUM(N(MID(A2,ROW($1:$50),1)>=”吖“)))例③分数评级

假定考分>=85的为”A”,>=70的为”B”, >=60的为”C” 其余的为”D” 则公式为（当然有好多公式可写，但这是本文需要这样写）: =CHAR((A1<=100)+(A1<85)+(A1<70)+(A1<60)+64)解析: 假定A1中输入成绩80,则公式在运算中演变成: =CHAR(TRUE+TRUE+FALSE+FALSE+64),试中TRUE参与计算则为1，FALSE参与运算则0，由于我们知道大写字母从”A”开始，它的字符集数字代码是从65开始的。因此当满足一个条件时是1，再加64刚好就是65，然后用CHAR函数返回字母。

八、瞒天过海（ISERR与ISERROR）

IS类函数的运用，诸如： =ISERROR(#N/A)=ISERROR(DATE(2006,1,9))=ISERR(-”Good“)例如：将单元格A1:A10求和: 150 #N/A #VALUE!#REF!#DIV/0!#NUM!#NAME?

#NULL!9 北京 对于不能求和的项目，系统显示#N/A，但这样说给上司算不出来，未免显得太菜了。用什么方法，可以算出正确值呢？对了，先来一招投石问路，对各单元格返回的值做一个判断，看看系统到底能不能作出正确的判断。再来一招左右逢源(IF)，对于满足的就显示原值，不满足(出错的)的，就干脆让它为0,（当然，这个0也能省略），岂不妙哉？

因此，常规的求和是绝对不能解决问题的，单元格区域中本身就是EXCEL认为的错误字符，所以可以结合IF和IS函数来使用。大家可能已学习过，对于投石问路（IS类函数），共有九种变化，其中第三式（ISERROR）或第二式（ISERR）是比较常用的，可以使用。因此，组合后的公式就变成：

公式:

=SUM(IF(ISERROR(A1:A10+0)，A1:A10+0))以上数字如为数值型,则可简化为: =SUM(IF(ISERROR(A1:A10)，A1:A10))或者(干脆避开IS类函数)： =SUMIF（A:A,”<9E307”）

（注公式中的”，”是将两个英文逗号间的0省略了，不省略就写为 “,0,”）

ISERROR如果为真,说明真的出错,返回0,如果为假(没有出错),返回原值.这里又是0和1的变戏法。

九、“0”的消失（遇到&”“时）

0作为分母可以让0消失，TEXT中的条件参数也可以让0真正消失，T函数可以让数值型数字真正消失。N函数则可以让字符变身为0。空值（真空）当与数值大小比较时显示值为0，与字符比较时为空（””）例如：单元格A2：A10输入：

名称 A A B C B C A D B 要列所有的字母“A”，公式为：

=INDEX(A:A,SMALL(IF($A$2:$A$10=”A“,ROW($2:$10),4^8),ROW(A1)))查询区域中只有3个A，当往下填充时显示为0，因65536行为空，所以返回数值0，当公式末尾加上” &”“”后，返回文本””，所以0消失。完美公式如下：

=INDEX(A:A,SMALL(IF($A$2:$A$10=”A“,ROW($2:$10),4^8),ROW(A1)))&”“

十、“0”蛋的安全

例①单元格A1输入: 大理aw白族ws自00124.36hcw治州 问题：想在B1单元格中提取出数字00124.36 公式：

=MID(LOOKUP(1,-(1&MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$15)))),3,15)解析：常规的公式如：=-LOOKUP(1,-MID(A1,MIN(FIND(ROW($1:$10)-1,A1&1/17)),ROW($1:$15)))，会使有效数字前面的0丢失，我们不得不聘请“1”来做“安全卫士”，防止“0”逃走，用“1&”之后，将它们一并提出来，由于数字是负值，后面又有1，所以从第3个字符提取到15位。

例②单元格A2:A8输入： 数字串 593670 012690 12789.3648 0.36998（空白）12789.3648（空白）

问题：要求将各单元格的数字反转。公式：

=RIGHT(REPLACE(SUM((0&MID(SUBSTITUTE(A2,”.“,)&1,ROW($1:$15),1))*10^ROW($2:$16))%,LEN(A2)-FIND(”.“,A2&”.“)+2，”.“),LEN(A2))解析：公式中的“&1”，就是怕原数字串的最后一个0丢失，因反转后，最后的0变成了最前面的，因数值前面的0无效而丢失，“&1”中的1就象护栏一样（根据问题的具体情况，有时用“1&”），防止边缘上的0滚蛋，滚蛋了不就成了“卖鸡蛋的跌倒—没有一个好的”，这“云南十八怪中的鸡蛋用草串着卖”也就怕滚蛋了。

十一、眼见为虚，验证为实（单元格格式简单运用）

单元格格式的设置顺序excel默认:”正数;负数;零;文本“，中间用英文分号相隔。并且还可以设置颜色（颜色是TEXT函数不具备的），如单元格格式：”[红色]我;[绿色]爱;[黄色]excel;[蓝色]home“设置后分别输入”正数;负数;零;文本“试试，是不是很有趣哦！输入正数时显示红色的”我“;负数时显示绿色的”爱“;零时显示黄色的”excel“;文本时显示蓝色的”home“，显示结果不是真实存在的，是你的眼睛在欺骗你，它并没有改变其本身的值或字符，所以眼见为虚，验证为实。是每个excel 人所必须弄清的。

1、单元格格式中的”0“，往往是通配数字。如：

输入19980823，要显示为日期格式，则单元格格式：”0-00-00“（即月份和日期都是两位数，剩下的位数为年份），单元格格式也可以写成：” 0年00月00日“

2、单元格格式中的隐藏大法

①隐藏0值，单元格格式：”[=]g“ ②隐藏负值：”G/通用格式;“ ③隐藏小于0的值：”G/通用格式;;“ ④隐藏正值：”;-G/通用格式;0“ ⑤隐藏0值和正值：”;-G/通用格式“ ⑥隐藏数值：”;;;@“(仅显示文本)另外，经测试，隐藏数值的单元格格式还可以写成：”“"”(只输入“"),但缺陷是当输入负值时会显示”-“号没有完全隐藏负值。

⑦隐藏文本：” [<>]G/通用格式;;0;“，也可以:”G/通用格式;-G/通用格式;0;“ ⑧全部隐藏：”;;;“

3、改变默认设置法： 例如：

学生成绩<60的为不及格，<85的为及格，>=85的为优秀，则单元格格式为：” [<60]不及格;[<85]及格;优秀“，单元格格式还可以再加上颜色：” [红色][<60]不及格;[黄色][<85]及格;[蓝色]优秀“，还可以按照条件只设置颜色,不显示等级，单元格格式：” [红色][<60];[黄色][<85];[蓝色]“，如果中间再加一个等级，>=70且<85的为良，单元格格式就无能为力了，因为单元格格式最多能将数值分为三段，第四段是文本格式.4、占位符（””和”!”）

是指EXCEL规定有特殊含义或者说有特殊用途的字符而言的，当在单元格格式中（或者说在TEXT条件参数中）输入这些字符时，EXCEL赋予“她”特殊使命，当不需要“她”完成特殊使命，只作“平民”身份出现时，就用占位符（””和”!”）命令“她”，使“她”显示出本来面目。具有特殊使命的字符有：

A星期 B佛历年份 D日期

E科学计数,小”e"是年份(使用时要注意区分)H小时

M月份和分钟 S秒 Y年份 @文本 #数字 0数字

如：A1:A3单元格中都输入40000（日期：2009-7-6的序列数），单元格格式分别设置为：e,m,d，是不是分别显示为：2009（年），7（月），6（日）了，如果要将“她”还原为“平民”身份，就分别用e,m,d（用!e,!m,!d也是一样的），结果就显示字母本身了。

如上述，学生成绩<60的为不及格，<85的为及格，>=85的为优秀，单元格格式：” [<60]不及格;[<85]及格;优秀”，就是人为的定义格式，当然还可以” [>=85]优秀;[<60]不及格;及格”和” [>=85]优秀;[>=60]及格;不及格”这些格式的顺序由用户自己定义，改变EXCEL的默认设置，也就是我们EXCEL人自己赋予“她”的用途。由于这些字符EXCEL没有特殊含义或者说没有特殊用途，所以不需要用占位符。

EXCEL博大精深，仅就一个0，也只写了冰山一角，如果没有养成独立思考习惯，平时又不知道积累知识点，只会发一堆废铁（贴），缺乏探索创新，是永远成不了好钢的。在困难面前誓不低头，世上才有了徒手攀岩的“蜘蛛人”，作为合格的EXCEL人应该具备“蜘蛛人”应有的品质，面对技术难题，从0开始，不畏艰险，敢于“亮剑”，勇攀“E”峰。只要坚持，坚持，再坚持，才能享受到“举头红日白云低”的成功喜悦。

第二篇：Excel函数公式

Excel函数公式

在会计同事电脑中，保保经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理....看来再完备的会计系统也取代不了Excel表格的作用。

于是，小呀尽可能多的收集会计工作中的Excel公式，所以就有了这篇本平台史上最全的Excel公式+数据分析技巧集。

员工信息表公式

1、计算性别(F列)

=IF(MOD(MID(E3,17,1),2),“男”,“女”)

2、出生年月(G列)

=TEXT(MID(E3,7,8),“0-00-00”)

3、年龄公式(H列)

=DATEDIF(G3,TODAY,“y”)

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3=“男”)+55)),“yyyy/mm/dd aaaa”)

5、籍贯(M列)

=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)注：附带示例中有地址库代码表

6、社会工龄(T列)=DATEDIF(S3,NOW,“y”)

7、公司工龄(W列)

=DATEDIF(V3,NOW,“y”)&“年”&DATEDIF(V3,NOW,“ym”)&“月”&DATEDIF(V3,NOW,“md”)&“天”

8、合同续签日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY,“[<0]过期0天;[<30]即将到期0天;还早”)

10、工龄工资(AA列)

=MIN(700,DATEDIF($V3,NOW,“y”)*50)

11、生肖(AB列)=MID(“猴鸡狗猪鼠牛虎兔龙蛇马羊”,MOD(MID(E3,7,4),12)+1,1)

1、本月工作日天数(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、调休天数公式(AI列)=COUNTIF(B9:AE9,“调”)

3、扣钱公式(AO列)

婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元

=SUM((B9:AE9={“事”;“旷”;“病”;“丧”;“婚”})*{30;50;20;10;10})

1、本科学历人数

=COUNTIF(D:D,“本科”)

2、办公室本科学历人数

=COUNTIFS(A:A,“办公室”,D:D,“本科”)3、30~40岁总人数

=COUNTIFS(F:F,“>=30”,F:F,“<40”)

1、提成比率计算

=VLOOKUP(B3,$C$12:$E$21,3)

2、个人所得税计算

假如A2中是应税工资，则计算个税公式为：

=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

3、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3)，),“")注：

A3:标题行的行数+2,如果标题行在第3行，则A3改为A5

 工资数据源!A$1:工资表的标题行的第一列位置

4、Countif函数统计身份证号码出错的解决方法

由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &”*“转换为文本型即可正确统计。=Countif(A:A,A2&”*")

1、各部门人数占比

统计每个部门占总人数的百分比

2、各个年龄段人数和占比

公司员工各个年龄段的人数和占比各是多少呢？

3、各个部门各年龄段占比

分部门统计本部门各个年龄段的占比情况

4、各部门学历统计

各部门大专、本科、硕士和博士各有多少人呢？

5、按年份统计各部门入职人数 每年各部门入职人数情况.今天分享的Excel公式虽然很全，但实际和会计实际要用到的excel公式相比，很多遗漏。欢迎做会计的同学们补充你工作中最常用到的公式。

还会有

第三篇：Excel函数公式

文本函数：

UPPER 将一个字符串中所有小写字母转换为大写字母； LOWER 是都变成小写，PROPER是首字母大写；

LEFT(text,num_chars)提取一个文本字符串的前num_chars个字母，如图。

日期和时间函数：

TODAY 返回当前日期；

NOW 返回当前日期和时间，如图。

逻辑函数：

IF(logical_test, value_if_true, value_ if_false)判断logical_test是否是真，如果真则返回值value_if_true,否则返回值value_ if_false，如图，这里是判断是否2>1，如果是真则返回1，如果是假则返回0；AND OR NOT 分别是 所有值为真、至少一个为真、求反。

数学与三角函数： SUM 求和；

ROUND(number,num_digits)四舍五入，保留num_digits位小数，如图，这里如果给-2的话，表示从小数点向前数2位； MOD 求余数； INT 取整函数；

SIN COS TAN ASIN ACOS ATAN 就是基本的三角函数了。

统计函数：

AVERAGE平均值； MAX 最大值； MIN 最小值； COUNT 计数；

RANK(number,ref,order)排名函数, 将数值number在一组数ref中的排名算出，数值order为0为降序，1为升序，如图。

第四篇：常用的excel函数公式

一、数字处理

1、取绝对值 =ABS(数字)

2、取整 =INT(数字)

3、四舍五入

=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空 公式：C2 =IFERROR(A2/B2,“")

说明：如果是错误值则显示为空，否则正常显示。

2、IF多条件判断返回值 公式：C2 =IF(AND(A2<500,B2=”未到期“),”补款“,”“)

说明：两个条件同时成立用AND,任一个成立用OR函数。

三、统计公式

1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

2、统计不重复的总人数 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

四、求和公式

1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3)或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明：如果标题行没有规则用第2个公式

2、单条件求和 公式：F2 =SUMIF(A:A,E2,C:C)说明：SUMIF函数的基本用法

3、单条件模糊求和 公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如”*A*“就表示a前和后有任意多个字符，即包含A。

4、多条件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&”*“,B2:B7,B11)说明：在sumifs中可以使用通配符*

5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

6、按日期和产品求和 公式：F2 =SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)说明：SUMPRODUCT可以完成多条件求和

五、查找与引用公式、单条件查找公式 公式1：C11 =VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

2、双向查找公式 公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))说明：利用MATCH函数查找位置，用INDEX函数取值

3、查找最后一条符合条件的记录。公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

4、多条件查找 公式:详见下图

说明:公式原理同上一个公式

5、指定区域最后一个非空值查找 公式;详见下图 说明：略

6、按数字区域间取对应的值

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

六、字符串处理公式、多单元格字符串合并 公式：c2 =PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

2、截取除后3位之外的部分 公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分 公式:B2 =Left(A1,FIND(”-“,A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

4、截取字符串中任一段的公式 公式:B1 =TRIM(MID(SUBSTITUTE($A1,” “,REPT(” “,20)),20,20))

说明:公式是利用强插N个空字符的方式进行截取

5、字符串查找 公式：B2 =IF(COUNT(FIND(”河南“,A2))=0,”否“,”是“)

说明: FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

6、字符串查找一对多 公式：B2 =IF(COUNT(FIND({”辽宁“,”黑龙江“,”吉林“},A2))=0,”其他“,”东北“)说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

1、两日期相隔的年、月、天数计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算： 相隔多少天？=datedif(A1,B1,”d“)结果：557 相隔多少月? =datedif(A1,B1,”m“)结果：18 相隔多少年? =datedif(A1,B1,”Y“)结果：1 不考虑年相隔多少月？=datedif(A1,B1,”Ym“)结果：6 不考虑年相隔多少天？=datedif(A1,B1,”YD“)结果：192 不考虑年月相隔多少天？=datedif(A1,B1,”MD“)结果：9 datedif函数第3个参数说明： ”Y“ 时间段中的整年数。”M“ 时间段中的整月数。”D“ 时间段中的天数。

”MD“ 天数的差。忽略日期中的月和年。”YM“ 月数的差。忽略日期中的日和年。”YD" 天数的差。忽略日期中的年。

2、扣除周末天数的工作日天数 公式：C2 =NETWORKDAYS.INTL(IF(B2

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

第五篇：Excel函数公式

1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,“重复”,“")。

2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,”2009/8/30“,FALSE))/360,0)。

3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),”/“,MID(E2,11,2),”/“,MID(E2,13,2))。

4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男“,”女

“),IF(MOD(MID(C2,17,1),2)=1,”男“,”女“))公式内的“C2”代表的是输入身份证号码的单元格。

1、求和： =SUM(K2:K56)——对K2到K56这一区域进行求和；

2、平均数： =AVERAGE(K2:K56)——对K2 K56这一区域求平均数；

3、排名： =RANK(K2，K$2:K$56)——对55名学生的成绩进行排名；

4、等级： =IF(K2>=85,”优“,IF(K2>=74,”良“,IF(K2>=60,”及格“,”不及格“)))

5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；

6、最高分： =MAX(K2:K56)——求K2到K56区域（55名学生）的最高分；

7、最低分： =MIN(K2:K56)——求K2到K56区域（55名学生）的最低分；

8、分数段人数统计：

（1）=COUNTIF(K2:K56,”100“)——求K2到K56区域100分的人数；假设把结果存放于K57单元格；（2）=COUNTIF(K2:K56,”>=95“)－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；

（3）=COUNTIF(K2:K56,”>=90“)－SUM(K57:K58)——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；

（4）=COUNTIF(K2:K56,”>=85“)－SUM(K57:K59)——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；

（5）=COUNTIF(K2:K56,”>=70“)－SUM(K57:K60)——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；

（6）=COUNTIF(K2:K56,”>=60“)－SUM(K57:K61)——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；

（7）=COUNTIF(K2:K56,”<60“)——求K2到K56区域60分以下的人数；假设把结果存放于K63单元格；

说明：COUNTIF函数也可计算某一区域男、女生人数。

如：=COUNTIF(C2:C351,”男“)——求C2到C351区域（共350人）男性人数；

9、优秀率： =SUM(K57:K60)/55*100

10、及格率： =SUM(K57:K62)/55*100

11、标准差： =STDEV(K2:K56)——求K2到K56区域(55人)的成绩波动情况（数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化）；

12、条件求和： =SUMIF(B2:B56,”男“，K2:K56)——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和；

13、多条件求和：｛=SUM(IF(C3:C322=”男“,IF(G3:G322=1,1,0)))｝ ——假设C列（C3:C322区域）存放学生的性别，G列（G3:G322区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人数；这是一个数组函数，输完后要按Ctrl＋Shift＋Enter组合键(产生“｛……｝”)。“｛｝”不能手工输入，只能用组合键产生。

14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW()))/360,0)———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。

15、在Word中三个小窍门： ①连续输入三个“~”可得一条波浪线。②连续输入三个“-”可得一条直线。

连续输入三个“=”可得一条双直线。

一、excel中当某一单元格符合特定条件，如何在另一单元格显示特定的颜色比如： A1〉1时，C1显示红色 0

1、单元击C1单元格，点“格式”>“条件格式”，条件1设为： 公式 =A1=1

2、点“格式”->“字体”->“颜色”，点击红色后点“确定”。条件2设为：

公式 =AND(A1>0,A1<1)

3、点“格式”->“字体”->“颜色”，点击绿色后点“确定”。条件3设为： 公式 =A1<0 点“格式”->“字体”->“颜色”，点击黄色后点“确定”。

4、三个条件设定好后，点“确定”即出。

二、EXCEL中如何控制每列数据的长度并避免重复录入

1、用数据有效性定义数据长度。用鼠标选定你要输入的数据范围，点”数据“->”有效性“->”设置“，”有效性条件“设成”允许“"文本长度”“等于”“5”（具体条件可根据你的需要改变）。

还可以定义一些提示信息、出错警告信息和是否打开中文输入法等，定义好后点“确定”。

2、用条件格式避免重复。

选定A列，点“格式”->“条件格式”，将条件设成“公式=COUNTIF($A:$A,$A1)>1”，点“格式”->“字体”->“颜色”，选定红色后点两次“确定”。

这样设定好后你输入数据如果长度不对会有提示，如果数据重复字体将会变成红色。

三、在EXCEL中如何把B列与A列不同之处标识出来？

（一）、如果是要求A、B两列的同一行数据相比较：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “单元格数值” “不等于”=B2

点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设置。

（二）、如果是A列与B列整体比较（即相同数据不在同一行）：

假定第一行为表头，单击A2单元格，点“格式”->“条件格式”，将条件设为: “公式”=COUNTIF($B:$B,$A2)=0 点“格式”->“字体”->“颜色”，选中红色，点两次“确定”。用格式刷将A2单元格的条件格式向下复制。B列可参照此方法设置。

按以上方法设置后，AB列均有的数据不着色，A列有B列无或者B列有A列无的数据标记为红色字体。

四、EXCEL中怎样批量地处理按行排序

假定有大量的数据(数值)，需要将每一行按从大到小排序，如何操作？

由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以，这一问题不能用排序来解决。解决方法如下：

1、假定你的数据在A至E列，请在F1单元格输入公式：

=LARGE($A1:$E1,COLUMN(A1))用填充柄将公式向右向下复制到相应范围。

你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。

注：第1步的公式可根据你的实际情况（数据范围）作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1))

五、巧用函数组合进行多条件的计数统计 例：第一行为表头，A列是“姓名”，B列是“班级”，C列是“语文成绩”，D列是“录取结果”，现在要统计“班级”为“二”，“语文成绩”大于等于104，“录取结果”为“重本”的人数。统计结果存放在本工作表的其他列。公式如下：

=SUM(IF((B2:B9999=“二”)*(C2:C9999>=104)*(D2:D9999=“重本”),1,0))输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号“{}”。

六、如何判断单元格里是否包含指定文本？

假定对A1单元格进行判断有无“指定文本”,以下任一公式均可: =IF(COUNTIF(A1,“*”&“指定文本”&“*”)=1,“有”,“无”)=IF(ISERROR(FIND(“指定文本”,A1,1)),“无”,“有”)求某一区域内不重复的数据个数

例如求A1:A100范围内不重复数据的个数，某个数重复多次出现只算一个。有两种计算方法：

一是利用数组公式：

=SUM(1/COUNTIF(A1:A100,A1:A100))输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号“{}”。二是利用乘积求和函数：

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))七、一个工作薄中有许多工作表如何快速整理出一个目录工作表

1、用宏3.0取出各工作表的名称，方法：

Ctrl+F3出现自定义名称对话框，取名为X，在“引用位置”框中输入： =MID(GET.WORKBOOK(1),FIND(“]”,GET.WORKBOOK(1))+1,100)确定

2、用HYPERLINK函数批量插入连接，方法：

在目录工作表（一般为第一个sheet）的A2单元格输入公式： =HYPERLINK(“#'”&INDEX(X,ROW())&“'!A1”,INDEX(X,ROW()))将公式向下填充，直到出错为止，目录就生成了。