一、数字处理
1、取绝对值 =ABS(数字)
2、取整 =INT(数字)
3、四舍五入 =ROUND(数字, 小数位数)
二、判断公式
1、把公式出现的错误值显示为空
公式：=IFERROR(A2/B2,)
说明：假设是错误值则显示为空，不然正常显示。
2、IF 多条件判断返回值
公式：=IF(AND(A2500,B2= 未到期), 补款,)
说明：两个条件同时成立用 AND, 任一个成立用 OR 函数。
三、统计公式
1、统计两个表格重复的主要内容
公式：=COUNTIF(Sheet15!A:A,A2)
说明：假设返回值大于 0 说明在另一个表中存在，0 则不存在。
2、统计不重复的总人员数量
公式：=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
说明: 用 COUNTIF 统计出每人的产生次数，用 1 除的方法把产生次数变成分母，然后相加。
四、求和公式
1、隔列求和
公式：=SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
说明：假设标题行没有规则用第 2 个公式。
2、单条件求和
公式：=SUMIF(A:A,E2,C:C)
说明：SUMIF 函数的基本用法
3、多条件模糊求和
公式：=SUMIFS(C2:C7,A2:A7,A11*,B2:B7,B11)
说明：在 sumifs 中可以使用通配符 *
5、多表一样位置求和
公式：=SUM(Sheet1:Sheet19!B2)
说明：在表中间删除或添加表后，公式结果会自动更新。
6、按日期和产品求和
公式：=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
说明：SUMPRODUCT 可以完成多条件求和
五、查找与引用公式
1、单条件查找公式
公式：=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、按数字区域间取对应的值
公式：
公式说明：VLOOKUP 和 LOOKUP 函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。
六、字符串处理公式
1、多单元格字符串合并
公式：=PHONETIC(A2:A7)
说明：Phonetic 函数只可以对字符型内容合并，数字不可以。
2、截取左部分
公式：=LEFT(D1,LEN(D1)-3)
说明：LEN 计算出总长度,LEFT 从左边截总长度 - 3 个
3、截取 - 前的部分
公式：=Left(A1,FIND(-,A1)-1)
说明：用 FIND 函数查找位置，用 LEFT 截取。
4、截取字符串中任一段的公式
公式：=TRIM(MID(SUBSTITUTE($A1,,REPT(,20)),20,20))
说明: 公式是利用强插 N 个空字符的方法进行截取
5、字符串查找
公式：=IF(COUNT(FIND( 河南,A2))=0, 否, 是 )
说明：用来判断查找是不是成功。
6、字符串查找一对多
公式：=IF(COUNT(FIND({ 辽宁, 黑龙江, 吉林},A2))=0, 其他, 东北 )
说明：设置 FIND 第一个参数为常量数组，用 COUNT 函数统计 FIND 查找结果
七、日期计算公式
1、两日期相隔的年、月、天数计算
A1 是启动日期（2023-12-1），B1 是结束的最后一天期 (2023-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 天数的差。忽视日期中的月和年。
YD 天数的差。忽视日期中的年。
2、扣除星期六和星期天天数的工作日天数
公式：=NETWORKDAYS.INTL(IF(B2DATE(2023,1,1),DATE(2023,1,1),B2),DATE(2023,1,31),11)
说明：返回两个日期当中的全部工作日数，使用参数指示什么天是星期六和星期天，还有有多少天是星期六和星期天。星期六和星期天和任何指定为假期的日期不被默认为工作日
1、查找重复内容公式：=IF(COUNTIF(A:A,A2)1, 重复,)。
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,2023/8/30,FALSE))/360,0)。
3、从输入的 18 位居民身份证号的出生年月计算公式：=TENATE(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”代表的是输入居民身份证号码的单元格。
5、求和：=SUM(K2:K56) —对 K2 到 K56 这一区域进行求和；
6、平均数：=AVERAGE(K2:K56) —对 K2K56 这一区域求平均数；
7、排名：=RANK(K2，K$2:K$56) —对 55 名学生的成绩进行从高到底的排序；
8、等级：=IF(K2=85, 优,IF(K2=74, 良,IF(K2=60, 及格, 不及格)))
9、学期总评：=K2*0.3+M2*0.3+N2*0.4 —假设 K 列、M 列和 N 列分别存放着学生的“平日间总评”、“期中”、“期末”三项成绩；
10、最高分：=MAX(K2:K56) —求 K2 到 K56 区域（55 名学生）的最高分；
11、最低分：=MIN(K2:K56) —求 K2 到 K56 区域（55 名学生）的最低分；
12、成绩段人员数量统计：
（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 人）男性人员数量；
13、优秀率：=SUM(K57:K60)/55*100
14、及格率：=SUM(K57:K62)/55*100
16、条件求和：=SUMIF(B2:B56, 男，K2:K56)—假设 B 列存放学生的性别，K 列存放学生的成绩，则此函数返回的结果表示求该班男生的成绩之和；
17、多条件求和：{=SUM(IF(C3:C322= 男,IF(G3:G322=1,1,0))) }—假设 C 列（C3:C322
区域）存放学生的性别，G 列（G3:G322 区域）存放学生所在班级代码（1、2、3、4、5），则此函数返回的结果表示求一班的男生人员数量；这是一个数组函数，输完后要按 Ctrl+Shift+Enter 组合键 (出现“{ }”。)“{}”不可以手工输入，只可以用组合键出现。
18、按照出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW()))/360,0)—假设 D 列存放学生的出生日期，E 列输入该函数后则出现该生的周岁。
19、在 Word 中三个小窍门：
连续输入三个“~”可得一条波浪线。
连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
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
按以上方式设置后，AB 列均有的数据不着色，A 列有 B 列无或者 B 列有 A 列无的数据标记为红色字体。
假定有非常多的数据 (数值)，需将每一行按从大到小排序，如何操作？
因为按行排序与按列排序都是只可以有一个主重要字 , 主重要字一样时才可以按次重要字排序。故此这一问题不可以用排序来处理。处理方式请看下方具体内容：
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())) 将公式向下填充，直到出错为止，目录就生成了。
快捷键
上标：Ctrl+Shift+”=”
下标：Ctrl+”=”
插入商标符号：Ctrl+Shift+”T”
插入注册商标符号：Ctrl+Shift+”R”
插入版权符号：Ctrl+Shift+”C”
原文链接：https://www.china-share.com/jijincongye/tiku/202211141140817.html
