一、数字处理


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。

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、单条件查找公式
公式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函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。

六、字符串处理公式

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(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
说明:返回两个日期之间的所有工作日数,使用参数指示什么是天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

 
三。跨越不同列的匹配和返回值

8.1 
= IF(COUNTIF(“服务器”!A:A,A3)= 0,“否”,“是”)
检查工作表“服务器”列A中的A3值是否存在。如果找到,则显示“是”,否则,显示“否”


8.2 
= VLOOKUP(A3,’Z:\ 0 Operation \ 1 Scan \ Servers \ Jan 2019 \ [Scan_Report_Server_Urgent_Vuls.xlsx] APP IP'!A:H,8,)
Check if A3 value found in the file “Z:\ 0操作\ 1扫描\服务器\ 2019年1月\ [Scan_Report_Server_Urgent_Vuls.xlsx” –工作表“ APP IP” –列A至H。如果找到,则返回同一行的第八列的值。 

四。 Excel公式:将文本转换为数字从其他资源获得Excel文件。列A设置为文本。在数字的末尾和开头处填充了一些空格,例如,单元格A1为“ 4 ‘.

我的挑战是转换 整列为可用于求和或其他数学计算的数字。

通过一些研究,我为新的列构建了此公式:
= VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),”“)))))

从下面的屏幕截图可以看出,它完美地解决了这一挑战。

注意:修剪不适用于唯一的空间单元。谢谢 reply from Hari Krishna.



五。 Excel公式:搜索一列字符串以匹配另一列
有时,我必须手动处理Excel工作表。今天,我必须在预定义的Excel电子表格中搜索导出的数百个IP地址,以查看是否存在匹配项。看起来很容易,但是我花了近一个小时才找到正确的公式。

O列包括所有导出的IP地址。我必须手动搜索那些O列的IP地址是否出现在B列的文本中。如果是,那是哪一行?

Formula is set at Column N.

=MATCH(“*”&(O6)&”*”,B:B,0)

从N6单元格可以看到,数字是7,这意味着B7中的文本包含O6的字符串。
N12的数字为5,表示B5中的测试包括O12的字符串。 


By john

Leave a Reply