工作中最常用的excel函数公式大全

工作中最常用的excel函数公式的大全一、数字处理取绝对值=ABS (数字)2 .整理=INT (数字)3 .四舍五入=ROUND (数值、小数点后的位数)二、判断式1 .将表达式中发生的错误值显示为空公式： C2=IFERROR(A2/B2，)说明：如果是错误值，则显示为空白，否则显示为正常。2、IF多条件判定返回值公式： C2=IF(AND(A2500，B2=未过期)，补助金，)说明：两个条件同时成立的是AND，其中之一成立的是OR函数。三、统计公式1 .统计两个表格的重复内容式：B2=COUNTIF(Sheet15！ A:A，A2)说明：如果另一个表中存在返回值大于0的说明，则不存在0。2、统计不重复的总人数公式： C2=sumproduct (1/countif (a 2: a 8，a2:a8) ):在COUNTIF中合计每人的出现次数，除以1以出现次数为分母进行相加。四、总式1、间隔列加法式： H3=SUMIF($A$2:$G$2，H$2，A3:G3)或者=sumproduct (mod (列(b 3: g3)，2)=0)*B3:G3)说明：如果标题行没有规则，请使用第二个表达式2 .单一条件共计公式： F2=SUMIF(A:A，E2，C:C )说明： SUMIF函数的基本用法3 .单条件模糊合计公式：有关详细信息，请参见下图说明：如果需要模糊合计，则需要掌握通配符的使用情况。 星号表示任意数量的字符，如*A*，表示a前后有任意数量的字符。 换句话说，它包含了a。4 .多条件模糊合计式： C11=SUMIFS(C2:C7，A2:A7，a1*， B2:B7，b1 )说明：在sumifs中可以使用通配符*5、多个表中相同位置的总和式： b2=SUM(Sheet1:Sheet19！ B2 )说明：如果您在表格中间删除或新增表格，公式结果会自动更新。6 .日期和产品总额公式： F2=sumproduct ($ a $ 2: $ a $ 25 )=f $1) * ($ b $ 2: $ b $ 25=$ e2) * $ c $ 2: $ c $ 25 )说明： SUMPRODUCT可以完成多个条件的总和五、公式的查找和引用1 .单条件搜索表达式式1:C11=VLOOKUP(B11，b 3: f 7，4，假)说明：搜索最擅长VLOOKUP，是基本的用法2 .双向检索式公式：=INDEX(C3:H7，MATCH(B10，b : b 7，0 )，MATCH(C10，c 23360 h 2，0 ) )说明：使用MATCH函数查找位置，并使用INDEX函数检索值3 .查找满足最后条件的记录。公式：有关详细信息，请参见下图说明：0/(条件)不满足条件可以是错误值，lookup可以忽略错误值4 .多条件检索式：详情请参阅下图与说明：式原理之前的式相同5 .指定区域的最后一个null以外的值检索官方详情请参阅下图说明：省略6 .在数字区域之间取对应的值公式：有关详细信息，请参见下图公式的说明： VLOOKUP和LOOKUP函数都可以按区间取值，所以销售排列的数字必须按升序排列。六、字符串处理式1、连接多个单元格字符串公式： 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 (计数(河南，A2)=0，否，是 )说明： FIND搜索成功，返回字符的位置。 否则，返回错误值。 COUNT可以计数数字的个数。 您可以在这里确定搜索是否成功。6 .字符串检索一对多式： B2=IF(COUNT(FIND( 辽宁，黑龙江，吉林 ，A2)=0，其他，东北)说明：将FIND的第一个参数设置为常量数组，并使用COUNT函数统计FIND的结果七、日期计算公式计算相隔一两天的年、月、日数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 )结果： 9datedif函数的第三个参数说明：y期间的年数。m期间的月数。d期间的天数。“MD”天数之差。 忽略日期的月份和年份。“YM”月数之差。 忽略日期和年份。“YD”天数之差。 忽略日期的年份。2、工作日天数减去周末天数公式： C2=NETWORKDAYS.INTL(IF(B21，重复， )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) 对K56这一区域进行合计2、求出平均值：=average(k2:k56)k2k56这一地区的平均值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 列，假设在m列、n列分别存储有学生的平日总评价、中间、期末这3项成绩6、最高点：=从=MAX(K2:K56) 求出K56区域(55名学生)最高点7、最低点：=根据=MIN(K2:K56) 求出K56区域(55名学生)最低点8、分数段人数统计：(1)=COUNTIF(K2:K56， 100 )将从到K56区域的100点人数的计算结果保存在K57单元格中(2)=COUNTIF(K2:K56，=95 ) -假设将根据k 573354 k 2求出K56区域的9599.5分钟的人数的结果存储在K58单元中(3)=COUNTIF(K2:K56、=90 ) -假设将从sum (k 573360 k 58 ) 求出K56区域的9094.5点人数的结果存储在K59单元格中(4)=COUNTIF(K2:K56，=85 ) -假设将从sum (k 573360 k 59 ) k 2到K56的区域8589.5点的人数的计算结果保存在K60单元格中(5)=COUNTIF(K2:K56、=70 ) -假设将从sum (k 573360 k 60 ) 求出K56区域的7084.5点的人数的结果存储在K61单元中(6)=COUNTIF(K2:K56、=60 ) -假设将从sum (k 573360 k 61 ) 求出K56区域的6069.5点的人数的结果存储在K62单元中(7)=COUNTIF(K2:K56， 60 )将从到K56区域的60分以下的人数的计算结果保存在K63单元格中说明： COUNTIF函数还可以计算某个地区的男性、女性的人数。例如，=COUNTIF(C2:C351，男 ) 求出从C2到c33的地区(合计350人)的男性数9、优秀率：=SUM(K57:K60)/55*10010、合格率：=SUM(K57:K62)/55*10011、标准偏差：=求出从=STDEV(K2:K56) 到K56的地区(55人)的成绩变动情况(数值越小，该班级的学生间的成绩差越小，相反，说明该班级有两极分化)。12、条件之和：=SUMIF(B2:B56、“男人”，K2:K56) 假设在b列中加上学生性别，在k列中加上学生的分数，则该函数返回求出该班男生成绩之和的结果13、多条件合计： =SUM(IF(C3:C322=男人，if (g3:g322=1，1，0 ) ) 3354表示学生在c列(C3:C322区域)的性别，表示学生在g列(g : g 322区域)的类代码(1，2，3，4， 如果存储5 )，则此函数返回的结果是数组函数，表示要求类的男生人数。输入后按Ctrl Shift Enter键将生成“.”。 无法手动输入，只能通过键组合生成。14、根据生日自动计算满1岁：=TRUNC(DAYS360(D3，now () ) ) )/360，0 )假设您将学生的生日存入d列。 在e列中输入此函数将生成该学生的生日。在Word有三个窍门接着输入3个“”，即可形成波浪线。连续输入3个“-”，可获得直线。连续输入三个=，得到两条直线。另一方面，在excel中，当一个单元格满足特定条件时，如何在另一个单元格中显示特定颜色：A11时，C1表示红色0“条件格式”，条件1设置如下：表达式=A1=12、点格式-字体-颜色，按一下红色，然后按一下确定。设定为条件2 :公式=AND(A10，A11 )3、点按一下格式字体颜色，按一下绿色，然后按一下确定。设定为条件3 :公式=A10单击格式字体颜色，单击黄色，然后单击确定。4、设置了三个条件后，单击“确定”。二、EXCEL如何控制各列数据的长度，避免重复输入1 .用数据的有效性定义数据长度。用鼠标选择要输入的数据范围，在“数据-”中设置“有效性-”，在“文本长度”中设置“有效性条件”(可以根据需要更改条件)。定义提示信息、错误警告信息、中文输入方式是否有效等，定义完毕后也可以“确定”。2 .以条件格式避免重复。选择a列，选择点“格式”-“条件格式”，选择条件为=COUNTIF($A:$A，$A1)1，选择点“格式”-“字体”-“颜色”，红色后选择点“确定”，两次。这样，如果数据长度不同，则会显示提示，数据的重复字体为红色。三、用EXCEL怎样显示b列和a列的区别？(1)需要比较a、b两列的同一行数据时：假定第一行标题，单击A2单元格，然后单击格式条件格式，将条件设置为：单元格值不等于=B2单击格式字体颜色，选择红色，然后单击确定两次。用格式刷子向下复制A2单元格的条件格式。b列可以用这种方法设定。(2) a列和b列整体比较的情况(也就是说，同一数据不在同一行的情况)；假定第一行标题，单击A2单元格，然后单击格式条件格式，将条件设置为：公式=COUNTIF($B:$B，$A2)=0单击格式字体颜色，选择红色，然后单击确定两次。用格式刷子向下复制A2单元格的条件格式。b列可以用这种方法设定。按照以上方法进行设定后，AB列中有未着色的数据，a列中有b列，或b列中没有a列的数据以红色字体显示。四、如何在EXCEL中按行排序假设有大量的资料(数字)，您必须从大到小排序每一列。 我该怎么办？无论是按行排序还是按列排序，只有一个主键，因此如果主键相同，则可以按辅助键进行排序。 因此，排序不能解决这个问题。 解决办法如下：1、请在F1单元格中输入公式，假设您的