excel常用函数及实用技巧PPT课件
Excel实用函数技巧
Excel实用函数
文本截取函数
=mid(A2,1,6) 截取A2单元格第1-6位数
=MID(A1,8,18) 截取A1单元格数字中8-18位数字
=================
去除A1单元格首尾空格函数
=TRIM(A1)
各种 trim 函数的语法如下：
LTRIM(字串):将字串左边的空格移除。
RTRIM(字串): 将字串右边的空格移除。
TRIM(字串): 将字串开头和末尾的空格移除。
ALLTRIM(字串)：将字串左右边两边的空格移除。
===================
EXCEL拖动拉杆公式，计算结果不正确了。比如A1-A4输入数字1、2、3、4，B1-B4输入数字1、1、1、1，在C1输入=A1*B1回车，C1为1。拖动C1拉杆到C4，问题出现了：C1-C4的值全部是1。单独选中C1-C4中任何一个单元格，里面的公式是对的。再回车，值才变成对的，然后如果再次拖动，就又变成错误值。比如在C4选中公式，敲回车，C4变为4，如果从C4拖动拉杆的话，其它的值就都变成4了。
解决方案：
工具-选项-重新计算-自动计算打勾
======================
文本显示科学计数等错误的解决
=text(A1,"0")
======================
A1列，超过15位数字序列的填充
例：从1234567890123451开始递增填充，在A1输入：
="1234567890"&123450+ROW(A1)
======================
查出EXCEL表格中两列重复的数据
C1=IF(A1=B1,"")
解释：如果A1和B1相同，则C1不显示，否则显示FASLE。
======================
excel中重复数据的筛选
要求从上下行筛选出重复数据
添加辅助列，用COUNTIF函数求取每个数据出现的次数
excel实用函数技巧大全
Excel 使用技巧集锦——163种技巧
目 录
一、 基本方法 7
1. 快速选中全部工作表 7
2. 快速启动Excel 7
3. 快速删除选定区域数据 7
4. 给单元格重新命名 7
5. 在Excel中选择整个单元格范围 7
6. 快速移动/复制单元格 8
7. 快速修改单元格式次序 8
8. 彻底清除单元格内容 8
9. 选择单元格 8
10. 为工作表命名 9
11. 一次性打开多个工作簿 9
12. 快速切换工作簿 9
13. 选定超级链接文本（微软Office技巧大赛获奖作品） 10
14. 快速查找 10
15. 修改默认文件保存路径 10
16. 指定打开的文件夹 10
17. 在多个Excel工作簿间快速切换 10
18. 快速获取帮助 11
19. 创建帮助文件的快捷方式 11
20. 双击单元格某边移动选定单元格 11
21. 双击单元格某边选取单元格区域 11
22. 快速选定不连续单元格 11
23. 根据条件选择单元格 11
24. 复制或移动单元格 12
25. 完全删除Excel中的单元格 12
26. 快速删除空行 12
27. 回车键的粘贴功能 12
28. 快速关闭多个文件 12
29. 选定多个工作表 12
30. 对多个工作表快速编辑 13
31. 移动和复制工作表 13
32. 工作表的删除 13
33. 快速选择单元格 13
34. 快速选定Excel区域（微软Office技巧大赛获奖作品） 13
35. 备份工件簿 14
36. 自动打开工作簿 14
37. 快速浏览长工作簿 14
38. 快速删除工作表中的空行 14 39. 绘制斜线表头 14
40. 绘制斜线单元格 15
41. 每次选定同一单元格 15
42. 快速查找工作簿 15
43. 禁止复制隐藏行或列中的数据 15
44. 制作个性单元格 16
二、 数据输入和编辑技巧
16
Excel常用函数公式大全(实用)
构思新颖，品质一流，适合各个领域，谢谢采纳
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单元格；
Excel常用(实用)函数公式大全
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单元格；
EXCEL函数常用技巧：IF函数浅析
技巧解析一：如果你觉得对你有帮助的话请点个赞。和加个关注吧，海量excel学习尽在QQ1007173511.
下表为某公司客户订购清单,现需把他转为一列,客户名如果相同,只取一个,所订购的产品在客户名的下面.
客户购买产品结果
客户1毛巾客户1
客户1洗涤精毛巾
客户1肥皂洗涤精
客户2肥皂肥皂
客户3毛巾客户2
客户3洗涤精肥皂
客户4毛巾客户3
毛巾
洗涤精
客户4
毛巾
=INDIRECT(TEXT(SMALL(IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2),ROW(A1)
公式解析:
第一步:去重取唯一客户名最小的的行列号,用IF函数判断客户名称是否相同,,如果不相同则取其行号乘以10再加上其列号,否则
为了最后的INDORECT函数取值时所用;=IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1IF函数的第一参数
41客户1客户FALSE
655361客户1客户1TRUE
655361客户1客户1TRUE
71客户2=客户1FALSE
81客户3客户2FALSE
655361客户3客户3TRUE
101客户4客户3FALSE
第二步:因为产品名称是要全部取出的,所以利用IF({1,0},)结构重新构造一个7行2列的数组,第一列为IF函数去重后唯一值的行列
=IF({1,0},IF($A$4:$A$10=$A$3:$A$9,4^8,ROW($4:$10))*10+1,ROW($4:$10)*10+2)
414241
65536152655361
65536162655361
717271
818281
65536192655361
101102101
IF函数的结果
第三步:利用SMALL函数依次取出构造好的列号,因为第二列的列号大于第一列的列号,所以每次取数时先取出客户名,再依次取41
42
52
62
71
72
81
82
92
101
EXCEL常用函数使用技巧及其应用
ＥＸＣＥＬ中几个常用函数
在统计工作中的应用
１．目的：了解函数的基本语法和在统计工作中的应用，提高统计工作的电子化程度、
提高工作效率、提高工作质量
２．函数的种类
２．１查找与引用函数ＶＬＯＯＫＵＰ：表达式：Ｓｈｅｅｔ１的单元格Ｃ１＝ＶＬＯＯＫＵＰ（Ａ１，Ｓｈｅｅｔ２Ａ：Ｓ
ｈｅｅｔ２Ｃ，３，ＦＡＬＳＥ）
意义与用途：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序
号，再进一步返回选定单元格的值，默认情况下，表是以升序排列。
函数语法的说明：＂Ｓｈｅｅｔ１的单元格Ｃ１＂是供显示查找结果的单元格，＂Ａ１＂是待检索
单元格，即即将在区域查找中必须搜索到或满足的值，可以是单元格引用或具体的参
数值，＂Ｓｈｅｅｔ２Ａ：Ｓｈｅｅｔ２Ｃ＂是搜索的区域，即Ａ列到Ｃ列为搜索区域，＂３＂是指检索到的
单元格所对应的列序号或即将需返回的查找值的列序号。＂ＦＡＬＳＥ＂是指如果检索不到对
应的值则返回＂ＦＡＬＳＥ＂以示查找搜索失败；
２．２数学函数ＳＵＭＩＦ：表达式：Ｓｈｅｅｔ１的单元格Ｃ１＝ＳＵＭＩＦ（Ｓｈｅｅｔ２Ａ：Ｓｈｅｅｔ２Ａ，Ａ１，Ｓ
ｈｅｅｔ２Ｃ：Ｓｈｅｅｔ２Ｃ）
意义与用途：对指定的若干单元格求和。
函数语法的说明：＂Ｓｈｅｅｔ１的单元格Ｃ１＂是供显示求和结果的单元格，＂Ｓｈｅｅｔ２Ａ：Ｓ
ｈｅｅｔ２Ａ＂是求和条件的搜索区域可以是整列或某列的某个区段，前者不需要加绝对引用
＂￥＂，后者必须加＂￥＂，＂Ａ１＂是求和条件，可以具体的参数值也可以是单元格引用，＂Ｓ
ｈｅｅｔ２Ｃ：Ｓｈｅｅｔ２Ｃ＂是求和数据区域，可以是整列或某列的某个区段，前者不需要加绝
对引用＂￥＂，后者必须加＂￥＂，但必须与求和条件区域相对应；
２．３数学函数ＲＯＵＮＤ：表达式：ＳＨＥＥＴ１单元格Ｃ１＝ＲＯＵＮＤ（Ａ１，Ｎ）
意义与用途：对数值进行四舍五入。
语法说明：＂ＳＨＥＥＴ１单元格Ｃ１＂结果显示单元格， ＂Ａ１＂原数值，可以引用或具体数
值，＂Ｎ＂是指定的小数位数。
Excel常用函数使用技巧
Excel常用函数使用技巧
目 录
一、EXCEL函数概述
二、逻辑函数
三、日期时间函数
四、文本函数
五、数学函数
六、统计函数
七、检视与参照函数
1
一、EXCEL函数概述
1、函数结构：
=函数名称(参数)
说明：
§函数以公式形式表达，输入公式应加引导符号 = ;
§参数：可以是数字、文本、逻辑值(TRUE 或FALSE)、数组、错误值(#N/A)、单元格引用等;
§圆括号 “()”尽量在英文状态下输入。
2、数据格式：
通用型 文本型（文字文本和数字文本） 数值型
百分比 日期型 时间型
货币型 科学计数
3、运算符
（1）算术运算符：+ - * / ^
（2）比较运算符：
= > < >= <= <>
▲比较运算的结果为逻辑值TRUE或FALSE
（3）文本运算符：
&（连接多个文本字符，产生一串新文本） 2
（4）引用运算符：
引用单元格，有相对引用（A5:B7）、绝对引用（$A$5:$B$7）和混合引用（$A5:B$7）。
（5）逻辑运算符：NOT AND OR
（6）通配符（特殊）：
§星号“*”： 可代替任意数目的字符（一个或多个）。
§问号“?”： 可代替任何单个字符。
§仅对字符型数据进行操作的(包括：Match()、Vlookup()、Countif()、Sumif()等函数)，对数值型数据无效的 。
4、函数嵌套
函数嵌套是指根据需要，在一个函数的使用过程中调用其他函数。
比如：=WEEKDAY(TODAY(),2);
=IF(MAX(A3:A8),IF(),“ ”)
5、公式运算结果出错信息（举例）：
（1）#NUM!：数字不能被运算
（2）#NAME!：引用了不能被识别的函数名或单元格
（3）#VALUE!：使用了错误的数据类型
（4）#####!：单元格显示不下内容（宽度不够）
excel常用函数技巧(附素材)
每到学期结束时，教师的一项重要工作就是要统计学生的成绩，在电脑逐步普及的今天，我们就把这项繁杂的工作交给Excel去自动完成吧。
涉及函数：AVERAGE、COUNTIF 、IF、ISERROR、MAX、MIN、RANK、SUM
1、启动Excel，同时选中A1至K1单元格，按“格式”工具条上的“合并及居中”按钮，将其合并成一个单元格，然后输入统计表的标题（参见图1）。
2、根据统计表的格式，将有关列标题及相关内容输入到相应的单元格中（参见图1）。
3、选中J3单元格，输入公式：=SUM(C3:I3)，用于计算第一位学生的总分。
4、选中K3单元格，输入公式：=RANK(J3,$J$3:$J$62)，计算出第一位学生总分成绩的名次（此处，假定共有60位学生）。
注意：如果将上述公式修改为：=COUNTIF($J$3:$J$62,">="&J3)，同样可以计算出第一位学生的名次。
5、同时选中J3和K3单元格，将鼠标移至K3单元格右下角的成“细十字”状时（通常称这种状态为“填充柄”状态），按住左键向下拖拉至K62单元格，完成其他学生的总分及名次的统计处理工作。
6、分别选中C63、C64单元格，输入公式：=MAX(C3:C62)和=MIN(C3:C62)，用于统计“语文”学科的最高分和最低分。
7、选中C65单元格，输入公式：=IF(ISERROR(AVERAGE(C3:C62)),"",AVERAGE(C3:C62))，用于统计“语文”学科的平均分。 注意：直接用“=AVERAGE(C3:C64)”公式也可以计算平均分，但是，如果成绩表中没有输入成绩时，这一公式将显示出一个错误的值，因此，我们用“ISERROR”函数来排除这一错误。
8、选中C66单元格，输入公式：=SUM(C3:C62)，用于统计“语文”学科的总分。
9、同时选中C63至C66单元格，用“填充柄”将上述公式复制到D63至J66单元格中，完成其它学科及总分的最高分、最低分、平均分和总分的统计工作。
Excel常用函数使用技巧 2
.
. Excel常用函数使用技巧
目 录
一、EXCEL函数概述
二、逻辑函数
三、日期时间函数
四、文本函数
五、数学函数
六、统计函数
七、检视与参照函数
.
. 一、EXCEL函数概述
1、函数结构：
=函数名称(参数)
说明：
§函数以公式形式表达，输入公式应加引导符号 = ;
§参数：可以是数字、文本、逻辑值(TRUE 或FALSE)、数组、错误值(#N/A)、单元格引用等;
§圆括号 “()”尽量在英文状态下输入。
2、数据格式：
通用型 文本型（文字文本和数字文本） 数值型
百分比 日期型 时间型
货币型 科学计数
3、运算符
（1）算术运算符：+ - * / ^
（2）比较运算符：
= > < >= <= <>
▲比较运算的结果为逻辑值TRUE或FALSE
（3）文本运算符：
&（连接多个文本字符，产生一串新文本） .
. （4）引用运算符：
引用单元格，有相对引用（A5:B7）、绝对引用（$A$5:$B$7）和混合引用（$A5:B$7）。
（5）逻辑运算符：NOT AND OR
（6）通配符（特殊）：
§星号“*”： 可代替任意数目的字符（一个或多个）。
§问号“?”： 可代替任何单个字符。
§仅对字符型数据进行操作的(包括：Match()、Vlookup()、Countif()、Sumif()等函数)，对数值型数据无效的 。
4、函数嵌套
函数嵌套是指根据需要，在一个函数的使用过程中调用其他函数。
比如：=WEEKDAY(TODAY(),2);
=IF(MAX(A3:A8),IF(),“ ”)
5、公式运算结果出错信息（举例）：
（1）#NUM!：数字不能被运算
（2）#NAME!：引用了不能被识别的函数名或单元格
（3）#VALUE!：使用了错误的数据类型
（4）#####!：单元格显示不下内容（宽度不够）
Excel中最常用的50条函数和使用技巧
Excel中最常用的50条函数和使用技巧 1. SUM函数：求和，对一个或多个单元格进行求和。 2. AVERAGE函数：求平均值，对一个或多个单元格进行平均值计算。 3. MAX函数：求最大值，对一个或多个单元格进行最大值计算。 4. MIN函数：求最小值，对一个或多个单元格进行最小值计算。 5. COUNT函数：计数，对一个或多个单元格进行计数。 6. COUNTIF函数：条件计数，对一个或多个单元格进行符合特定条件的计数。 7. IF函数：条件判断，根据条件对单元格进行不同的操作。 8. AND函数：逻辑与，判断多个条件是否同时成立。 9. OR函数：逻辑或，判断多个条件中是否存在至少一个成立。 10. NOT函数：逻辑非，对一个条件进行取反操作。 11. IFERROR函数：错误处理，如果上一个公式出现错误，则返回指定的值。 12. VLOOKUP函数：查找数据，在一个表格中查找特定数据并返回其值。 13. HLOOKUP函数：水平查找，在一个表格中按行查找特定数据并返回其值。 14. MATCH函数：匹配值，在一个数据数组中查找特定值并返回其位置。 15. INDEX函数：索引值，根据指定位置返回数组中的值。 16. ROW函数：返回行号，返回当前行的行号。 17. COLUMN函数：返回列号，返回当前列的列号。 18. ABS函数：绝对值，返回一个数的绝对值。 19. INT函数：取整，返回一个数的整数部分。 20. ROUND函数：四舍五入，对一个数进行四舍五入操作。 21. SIN函数：正弦函数，返回一个数的正弦值。 22. COS函数：余弦函数，返回一个数的余弦值。 23. TAN函数：正切函数，返回一个数的正切值。 24. LOG函数：对数，返回一个数的对数。 25. EXP函数：指数，返回e的n次方。 26. CONCATENATE函数：连接字符串，将两个或多个单元格中的字符串连接起来。 27. MID函数：提取字符串，从一个字符串中提取特定位置的子字符串。 28. FIND函数：查找字符串，在一个字符串中查找另一个字符串并返回其位置。 29. SUBSTITUTE函数：替换字符串，在一个字符串中替换指定的子字符串。 30. LEFT函数：返回左侧字符，从一个字符串中返回左侧指定数量的字符。 31. RIGHT函数：返回右侧字符，从一个字符串中返回右侧指定数量的字符。 32. CONCAT函数：连接文本，将两个或多个文本连接起来。 33. TEXT函数：文本格式，将一个数字转换为特定格式的文本。 34. AREAS函数：区域个数，返回引用中包含的区域个数。 35. TRANSPOSE函数：转置矩阵，将一个矩阵的行转换为列，或将列转换为行。 36. SUMIF函数：条件求和，对符合特定条件的单元格进行求和。 37. COUNTIFS函数：多条件计数，对符合多个条件的单元格进行计数。 38. AVERAGEIF函数：条件平均值，对符合特定条件的单元格进行平均值计算。 39. MAXIF函数：条件最大值，对符合特定条件的单元格进行最大值计算。 40. MINIF函数：条件最小值，对符合特定条件的单元格进行最小值计算。 41. IFERROR函数：错误处理，如果上一个公式出现错误，则返回指定的值。 42. IFS函数：多条件判断，根据多个条件判断真假并返回相应的值。 43. RANK函数：排名次，对一列数字进行排名次。 44. DATEDIF函数：日期差，计算两个日期之间的天数、月数或年数。 45. INT函数：取整，返回一个数的整数部分。 46. ROUND函数：四舍五入，对一个数进行四舍五入操作。 47. NOW函数：当前时间，返回当前的系统时间。 48. TODAY函数：当前日期，返回当前的日期。 49. IF函数：条件判断，根据条件对单元格进行不同的操作。