工地最常用的Excel函数公式齐全,计算能飞起来。

第一,数字处理

1.取绝对值

=ABS(数字)

2.舍入

=INT(数字)

3.舍入

=ROUND(数字,小数位)

二、判断公式

1.公式生成的误差值显示为null。

公式:C2

=IFERROR(A2/B2,& # 8221;”)

注意:如果是错误值,则显示为空白;否则会正常显示。

工地最常用Excel函数公式大全,算量算得飞起

2.如果多条件判断返回值

公式:C2

= IF(AND(A2 & lt;500,B2 = & # 8221;过期& # 8221;),”付款& # 8221;,””)

注意:这两个条件同时成立,并且其中一个成立或起作用。

工地最常用Excel函数公式大全,算量算得飞起

三。统计公式

1.计算两个表的重复内容。

公式:B2

=COUNTIF(Sheet15!答:答,答2)

注意:如果返回值大于0,则存在于另一个表中,0不存在。

工地最常用Excel函数公式大全,算量算得飞起

2.计算非重复的总数。

公式:C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

注:用COUNTIF统计每个人的出现次数,出现次数除以分母1,然后相加。

工地最常用Excel函数公式大全,算量算得飞起

四。求和公式

1.每隔一列求和

公式:H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或者

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

注:如果题头行中没有规则,请使用第二个公式。

工地最常用Excel函数公式大全,算量算得飞起

2.单一条件的和

公式:F2

=SUMIF(A:A,E2,C:C)

描述:SUMIF函数的基本用法

工地最常用Excel函数公式大全,算量算得飞起

3.单条件模糊求和

公式:详见下图。

注意:如果需要模糊求和,需要掌握通配符的使用,其中星号表示任意数量的字符,如& # 8221;* A * & # 8221意味着A前后有任意数量的字符,即包含A。

工地最常用Excel函数公式大全,算量算得飞起

4.多条件模糊求和

配方:C11

=SUMIFS(C2:C7,A2:A7,A11 & amp”*”,B2:B7,B11)

注意:在sumifs中可以使用通配符*。

5.同一位置的多个仪表的总和。

配方:b2

=SUM(Sheet1:Sheet19!B2)

注意:在表格中间删除或增加表格后,公式结果会自动更新。

工地最常用Excel函数公式大全,算量算得飞起

6.按日期和产品求和。

公式:F2

=SUMPRODUCT((月$ A $ 2:$ A $ 25)= F $ 1)*($ B $ 2:B $ 25 = $ E2)* $ C $ 2:C $ 25)

说明:SUMPRODUCT可以完成多条件求和。

工地最常用Excel函数公式大全,算量算得飞起

动词 (verb的缩写)查找和引用公式

1.单条件搜索公式

1: c11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明:查找是VLOOKUP最擅长的基本用法。

工地最常用Excel函数公式大全,算量算得飞起

2.双向搜索公式

公式:

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

注意:使用MATCH函数查找位置,使用INDEX函数取值。

工地最常用Excel函数公式大全,算量算得飞起

3.查找最后一条符合条件的记录。

公式:详见下图。

注意:0/(条件)可以把不合格的变成错误值,而lookup可以忽略错误值。

工地最常用Excel函数公式大全,算量算得飞起

4.多条件搜索

公式:详见下图。

注意:公式原理与上一个公式相同。

5.在指定区域中查找最后一个非空值。

公式;见下图。

注:省略

工地最常用Excel函数公式大全,算量算得飞起

6.根据数字区域取相应的值。

公式:详见下图。

公式:VLOOKUP和LOOKUP函数都可以按间隔取值。必须注意,销售一栏中的数字必须按升序排列。

工地最常用Excel函数公式大全,算量算得飞起

不及物动词字符串处理公式

1.多单元格字符串合并

公式:c2

=拼音(A2:A7)

注意:语音功能只能合并字符,不能合并数字。

2.截取除最后3位以外的部分。

公式:

=左(D1,莱恩(D1)-3)

注意:LEN计算总长度,LEFT从左边开始剪切总长度-3。

3.截取-前一部分

公式:B2

=Left(A1,FIND(& # 8220;-“,A1)-1)

说明:用FIND函数找到位置,用左截距。

4.截取字符串中任意段落的公式。

公式:B1

=TRIM(MID(替换$A1,& # 8221;“报告(& # 8221;“,20)),20,20))

注意:公式被强行插入N个空字符截取。

5.字符串搜索

公式:B2

= IF(COUNT(FIND(& # 8220;河南& # 8221;,A2))=0,& # 8221;没有& # 8221;,”是的& # 8221;)

注意:FIND成功,返回字符的位置;否则返回错误值,而COUNT可以统计出数字的个数,用来判断搜索是否成功。

6、字符串搜索一对多

公式:B2

= IF(COUNT(FIND({ & # 8220;辽宁& # 8221;,”黑龙江& # 8221;,”吉林& # 8221;},A2))=0,& # 8221;其他& # 8221;,”东北& # 8221;)

注意:将FIND的第一个参数设置为常量数组,使用COUNT函数对查找结果进行计数。

七。日期计算公式

1.计算两个日期之间的年、月和日。

A1是开始日期(2011年12月1日),B1是结束日期(2013年6月10日)。计算:

相隔几天?=datedif(A1,B1,& # 8221;d & # 8221)结果:557

相隔几个月?=datedif(A1,B1,& # 8221;m & # 8221)结果:18

相隔多少年?=datedif(A1,B1,& # 8221;Y & # 8221)结果:1

不管一年相隔几个月。=datedif(A1,B1,& # 8221;Ym & # 8221)结果:6

不考虑年与年之间的天数。=datedif(A1,B1,& # 8221;YD & # 8221)结果:192

不考虑年和月之间的天数。=datedif(A1,B1,& # 8221;MD & # 8221)结果:9

datedif函数第三个参数的解释:

“Y & # 8221时间段中的整年数。

“M & # 8221时间段中的整月数。

“D & # 8221时间段中的天数。

“MD & # 8221日子的差别。忽略日期中的月份和年份。

“YM & # 8221;月份的差异。忽略日期中的日期和年份。

“YD & # 8221日子的差别。忽略日期中的年份。

2.扣除周末后的工作日数。

公式:C2

=网络天数。国际(如果(B2 & lt;日期(2015,1,1),日期(2015,1,1),B2),日期(2015,1,31),11)

描述:返回两个日期之间的所有工作日的数量,使用参数来指示哪一天是周末以及有多少天是周末。周末和任何指定为假日的日期不被视为工作日。