Excel基础到公式运用个人整理Word下载.docx
《Excel基础到公式运用个人整理Word下载.docx》由会员分享，可在线阅读，更多相关《Excel基础到公式运用个人整理Word下载.docx（18页珍藏版）》请在冰点文库上搜索。
1）在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
2）将单元格引用、公式或函数作为数组常量输入。
3）赋予需要单一数值的运算符或函数一个数值区域。
3、#DIV/0！
公式被零除时或公式中包含的除数零。
4、#NAME？
1）删除了公式中使用的名称，或者使用了不存在的名称2）名称拼写错误
3）在公式中输入文本时没有使用双引号4）在区域的引用中缺少冒号
5、#N/A出现原因：
函数或公式中没有可用数值
6、#REF！
单元格引用无效
7、#NUM！
公式或函数中某个数字有问题（由公式产生的数字太大或太小，Excel不能表示）
8、#NULL！
为两个并不相交的区域指定交叉点
公式：
通常以“=”开头“+”、“−”号开关也可使用Excel识别其内容为公式Ctrl+~可以在公式与值的显示方式间切换
输入数据：
当用户输入数据时在工作窗口底部状态栏左侧会显示“输入”字样，原有编辑栏左边会出现“×
””√”按钮，单击“×
”表示
取消输入，单击“√”表示确定输入，单击“√”时和按Enter有相同的效果，但按Enter键后会自动跳到下一单元格，按√
则不会。
Excel中默认按Enter键是向下激活单元格，也可设置成其它，方法：
文件——选项——高级——编辑选项——方向
编辑单元格内容：
双击或者按F2，也可激活目标单元格后在工作窗口编辑栏编辑可以在键盘上按Insert键切换“插入”或“改写”模式
日期输入：
系统默认将0~29之间的数字识别为2000年~2029年，将30~99之间的数字识别为1930年~1999年。
用分隔符“.”来输入日期不能进行运算。
数据输入技巧：
强制换行Alt+Enter
在多个单元格输入相同的数据：
同时选中要输入的单元格，输入数据，在输入结束时按Ctrl+Enter
分数输入：
整数+空格+分数可在编辑栏查看分数值
上下标：
选中要设置的上下标值，Ctrl+1设置单元格格式/字体
在列表中选择：
当要在目标单元格中输入此单元格上方的某一单元格内的内容时可按Alt+下方向键或点鼠标右键选从下拉列
表中选择
自动填充：
输入数据用填充柄下拉（顺序填充，可在填充完后点右下角的填充菜单进行类型选择）双击填充柄
按住Ctrl键下拉为复制填充Ctrl+D向下填充Ctrl+R向右填充（需框选出填充区域后使用）
使用填充菜单：
开始——编辑——填充——序列
粘贴：
粘贴选项，复制、粘贴后在右下角出现的粘贴选项选择性粘贴，复制后在粘贴前点击鼠标右键选择的选择性粘贴选项
查找/替换：
Ctrl+F/Ctrl+H（可设置格式）
查找通配符应用：
Excel中支持两个通配符*和？
例：
要查找以E开头以L结束可用E*L以EX开头L结束的五个字母单词可用EX?
?
L
隐藏单元格数据：
选中要隐藏的单元格——点击右键——设置单元格格式——数字——自定义——在类型处打上三个半角分号（到这一步虽隐藏了单元格表面显示的内容但在编辑栏还是可以看到它本身的内容）——再点右键——设置单元格格式——保护——隐藏——审阅——保护工作表——加密（即可完全隐藏单元格的内容）
禁止编辑表格中部分区域：
单击行号和列标交叉处的按钮（全选整张工作表Ctrl+A）——Ctrl+1——保护——取消勾选锁定OK——选中要禁止编辑的区域——Ctrl+1——保护——先勾选隐藏再勾选锁定——审阅——保护工作表——加密OK
批注中插入图片：
审阅——新建批注——选中批注外边框——点击右键设置批注格式——颜色与线条——填充——填充效果——图片
打印批注：
页面布局——页面设置（点右下角小箭头）——工作表——打印——批注
--公式、函数--
公式以”=“号以引号，必须且只能返回值。
函数按特定算法计算的产生一个或一个组结果的预定义的特殊公式。
公式组成
序号
公式
说明
1
=15*3+20*2
包含常量运算的公式
2
=A1*3+A2*2
包含单元格引用的公式
3
=单价*数量
包含名称的公式
4
=SUM（A1*3,A2*2）
包含函数的公式
在输入公式的状态下，鼠标选中其他单元格区域时，被选区域将作为引用自动输入到公式里。
运算符
符号
例
-
算术运算符：
负号
=8*-5=-40
%
百分号
=60*5%=3
^
乘幂
=3^2=9=16^（1/2）=4
*和/
乘和除
=3*2/4=1.5
+和-
加和减
=3+2+5=0
=、<
>
、>
、<
=、<
=
比较运算符（比较大小）：
等于、不等于、大于、小于、大于等于、
小于等于
=（A1=A2）判断A1与A2相等
=（B1<
”B2”）判断B1不等于B2
=（C1>
=5）判断C1大于等于5
&
文本运算符（将文本字符或字符串进行连接合并）：
连接文本
=”爱”&
”点点”返回结果”爱点点“
:
区域运算符：
冒号
=SUM（A1：
C10）引用冒号两边所引用的单元格左上角和右下角之间的所有单元格组成的矩形区域。
空格
交叉运算符：
单个空格
=SUM（A1:
B5A4:
D9）引用A1:
B5与A4:
D9的交叉区
域，公式相当于=SUM（A4:
B5）
，
联合运算符：
逗号
=SUM（A1,（A1:
A10,C1:
C10））第2参数引用A1:
A10
和C1:
C10两个不连续的单元格区域。
运算符优先顺序
（）
小括号（优先级高于其他运算符，如果公式内有多组括号进行嵌套，计算顺序则由最内层括号逐
级向外进行运算）
：
空格，
冒号、单个空格、逗号
百分比
5
乘和除（注意区别数学中的×
和÷
）
6
7
8
=、>
比较两个值（注意区别数学中的≤、≥、≠）
相对引用：
当复制公式到其他单元格时，Excel保持从属单元格与引用单元格的相对位置不变。
例=A1向下复制为=A2、=A3、=A4-----
绝对引用：
当复制公式到其他单元格时，Excel保持公式所引用的单元格绝对位置不变。
例=$A$1向下复制始终=$A$1，不随公式复制改变
混合引用：
当复制公式到其他单元格时，Excel仅保持所引用单元格的行或行方向之一的绝对位置不变，而另一方向位置发生变化。
例：
=$A5向下复制为=$A6、=$A7、=$A8……向右复制始终=$A5
=A$5向右复制为=B$5、=C$5、=D$5……向下复制始终=A$5
混合引用实例：
跨表引用：
在公式中引用其他工作表的单元格区域。
表示方式：
工作表名+半角感叹号+引用区域
方法：
在公式编辑状态下，通过鼠标单击相应的工作表标签，然后选取相应的单元格区域。
例：
要在工作表Sheet1中的A1单元格中引用工作表Sheet2中的A1:
B10区域，则在Sheet1表中A1单元格输入=SUM（后单击
Sheet2工作表标签，然后选取A1:
B10区域，按回车结束。
则公式将显示为=SUM（Sheet2！
A1:
B10）
引用连续多工作表相同区域：
当跨表引用多个相邻的工作表中相同的单元格区域进行汇总时，可以使用三维引用进行计算而无须逐个工作表
对单元格区域进行引用。
它的表示方式为：
按工作表排列顺序，使用冒号将起始工作表和终止工作表名进行连
接，作为跨表引用的工作表表名。
工作薄内有连续工作表1、2、3、4，要在另一汇总表内计算前4张表里的同一区域（假设为B5:
E10）数值，在需执行公式的单元格内
输入=SUM（然后单击1工作表标签，按住Shift键后再单击4工作表标签，然后选取引用区域，按Enter结束输入。
显示公式为=SUM（'
1:
4'
!
B5:
E10）如果汇总表在表2与表3之间，则公式为=SUM（'
2'
E10,'
3:
E10）
套用表格格式：
开始——套用表格格式方法：
选择某一区域——开始——套用表格格式（勾选表包含标题），可在设计选项下进行其
他操作，例如勾选汇总行，会在表下方自动出现汇总，在下拉列表中有各项汇总项。
--函数--
函数表示：
=号开头、函数名称、左括号、以半角逗号相间隔的参数、右括号。
公式中允许多个函数或计算式，通过运算符连接
函数的参数可以由数值、日期、文本等元素组成，可以使用常量、数组、单元格引用或其他函数。
当使用函数作为另一个函数的
参数时，称为嵌套函数。
有的函数没有参数或者不需要参数，如NOW（返回日期时间格式的当前日期和时间：
=NOW（））、TODAY（返回当前日期：
=TODAY（））、RAND（返回大于或等于0且小于1的平均分布随机数：
=RAND（））、PI（圆周率：
=PI（））等。
Alt+=：
插入求和函数Shift+F3：
打开插入函数对话框
生成26个大写字母序列：
=CHAR（64+COLUMN（））小写字母序列：
=CHAR（96+COLUMN（））
合并并换行：
先点自动换行按钮，再输入公式=G7&
CHAR（10）&
H710是自动换行符的ANSI编码
分离姓名和数字
LEFT：
从一个文本字符串的第一个字符开始返回指定个数的字符
LENB：
返回文本中包含的字符数
LEN：
返回文本字符串中的字符个数
RIGHT：
从一个文本字符串的最后一个字符开始返回指定个数
的字符。
LENB函数按照每个双字节字符（汉字姓名）为2个长度计算，单字节字符按1个长度计算，因此LENB（B4）-LEN（B4）可以求得单元格中双字节字符的个数。
2*LEN（B4）-LENB（B4）则可以求得单元格中单字节字符的个数。
LEFT=左RIGHT=右
计算职工人数
G列数据中带有“人”字，要在黄色填充部分求人数的和可以用SUBSTITUTE函数将“人”字删除后再求和。
SUBSTITUTE函数获得的结果为文本型数字，所以要使用减负（--）运算将基转换为数值，以便SUMPRODUCT函数求和。
也可用公式=（DAYS360（H66,TODAY（）））/360来计算年龄
=IF（MOD（RIGHT（LEFT（B3,17））,2）,"
男"
"
女"
MOD：
取余函数（返回两数相除的余数）
=MOD（34,9）结果为7余数结果符号与除数一致被除数是除数的整数倍时结果为零
判断奇偶性：
=IF（MOD（15，2）>
0，”奇数”,”偶数”）解释：
如果15除以2的余数大于0，则返回奇数，否则就返回偶数
IF（条件函数）：
判断是否满足某个条件，如果满足就返回这个值，如果不满足则返回另一个值。
INT：
将数值向下取最接近的整数例：
=INT（15.35）结果为15
TRUNC：
将数字截为整数或保留指定位数的小数例：
=TRUNC（-8.72）结果为-8=TRUNC（9.72）结果为9
ROUNDUP：
无条件向上舍入数字例：
=ROUNDUP（23.416,2）结果为23.42（和四舍五入类似）
ROUNDOWN：
无条件向下舍入数字例：
=ROUNDUP（23.416,2）结果为23.41（直接舍去）可用TRUNC函数代替使用。
CEILING：
向上舍入例：
=CEILING（123.456,0.2）结果为123.6
FLOOR：
向下舍去例：
=FLOOR（123.456,0.2）结果为123.4
利用取舍函数控制单元格录入数值：
数据有效性—在公式处输入=C3=FLOOR（C3,0.01）C3为可变更单元格。
ROUND：
按指定的位数对数值进行四舍五入例：
=ROUND（123.456,2）结果为：
123.46
日期和时间函数：
允许输入日期区间1900-1-1至9999-12-31
当年份缺省时默认为1900年，当月份缺省为0时表示上一年的12月，当日期缺省为0时表示上一个月的月末天数。
常用日期函数
函数名称
功能作用
TODAY和NOW
用于生成当前日期和时间
DATE
根据指定的年份、月份和日期数返回具体的日期值
YEAR、MONTH和DAY
用于从日期数据中提取年份、月份和日期值
YEARFRAC
用于计算两个日期序列相差的年数，结果以实数显示
EDATE
根据指定月份数返回指定日期之前或之后的日期
EOMONTH
从任意一个日期返回指定月份数的之前或之后的月末日期
WORKDAY或WORKDAY.INTL（可以随意指定周工作日）
根据源日期按指定工作日天数返回之前或之后的日期
NETWORKDAYS或NETWORKDAYS.INTL（可以随意指定周工作日）
计算两个日期差工作日天数的函数
基本日期函数：
快速生成当前日期和时间Ctrl+；
和Ctrl+Shift+；
这样生成的日期和时间格式是固定不变的
=Today（）和=Now（）生成的日期会自动更新
=DATE（A2,B2+1,C2）类似都OK
从特定日期提取年月日：
年份=YEAR（单元格名称）月份=MONTH（单元格名称）日期=DAY（单元格名称）
如=DATE（YEAR（TODAY（））+1,10,1）会返回明年的国庆节日期
日期之间的运算：
+或-1）计算相差天数：
日期-日期=数值2）计算之前的日期：
日期-数值=日期3）计算之后的日期：
日期+数值=日期
TODAY=2014年6月29日
判断一个日期所在的季度：
公式利用了DAY（A2）返回的日期数是A2所在月份从第1日起
的第几天的来进行处理，当日期减去天数后得出当前月第0天
的日期，即为上个月月末日期，再通过DAY函数得出天数。
可根据此公式算出当前日期的下一
个星期天的日期。
工作日函数：
NETWORKDAYS：
返回参数start_date和enddate之间完整
的工作日数值。
工作日不包括周末和专门指定的假期。
可以使用函数NETWORKDAYS，根据某一特定时期内雇员的
工作天数，计算其应计的报酬。
提示:
若要使用参数来指明周末的日期和天数,从而计算两
个日期间的全部工作日数,请使用NETWORKDAYS.INTL函数.
时间的计算：
Time：
用于按指定数字生成具体的时间hour：
用于提取时间系列值中的小时数
Minute：
用于提取时间系列值中的分钟数second：
用于提取时间系列值中的秒数
计算下班时间：
例上班时间为2014-7-68：
30计算工作8小时后的时间：
统计与求和
汇总大于1000的点点的合计类似
汇总指定商品的销量
计算退休日期：
计算工龄（年龄）：
小数位数
开始时期结束日期
开始--条件格式
利用VLOOKUP函数查询并汇总指定产品月销量和
1、选中区域创建数据表：
表1
2、输入内容：
3、制作查询表：
4、输入数组公式：
公式在输入结束后，按Ctrl+Shift+Enter完成数组公式的输入。
按Ctrl+Shift+Enter两边多了个大括号
随便输入条件自动查询
使用通配符（*?
二维区域的条件求和（SUMIF/SUMIFS）
高级图表
1、筛选法动态图表设置自动筛选是实现动态图表最简单的方法，只要选择全部数据制作图表，再设置自动筛选就好啦。
比如：
先建立数据表，再选中全部数据建立柱形图（其它的也可以）
再设置筛选数据----筛选
看看效果：
2、公式法动态图表
制表添加辅助行
A11
选中A11设置数据验证（又叫数据有效性）
列行（ROW）
确定后完成有效性设置，效果然后在B11中输入公式并将公式复制到C11和D11单元格中。
效果
再选中A11--D11建立图表
选任意一个部门查看图表效果
3、定义名称法动态图表
制表添加组合框窗体控件（开发工具—插入）点击组合框点键，选取设置控件格式
设置好相应数据后按确定效果是这样：
然后再定义名称：
公式---名称管理器
新建
输入定义名称：
及位置
工作表名
升级会员 