EXCEL使用操作技巧
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
查找替换也可以对公式中的内容进行替换，在某些情况下可以实现快速批量修改公 式，如第105行的求和公式引用均为第5行到第100行，后根据需要，求和公式的引用需要修 改为从第5行到第104行，可以选中第105行，调出替换窗口，把100替换成104即可，此时一 定要选中第105行进行操作，并且保证范围为工作表而非工作簿，不然会把其他单元格的公 式或者字符中的100替换成104。
右图表示筛选王麻子首期日期为9月份或者张三首期日期为9 月份的数据
在条件区域需要以公式计算结果作为条件时：
1、公式计算结果必须为 TRUE 或 FALSE。 2、因为您正在使用公式，请像您平常那样输入公式，而不要以下列方式键入表达式： =‘’=条目‘’，即不需要按文本方式输入。 3、进行条件判断时，只需要输入第一行的引用，并且是相对引用，如下列的B10,公式中 的其他引用必须是绝对引用 ,如下列的$B$10:$B$21。附件中条件区域G3输入公式 =B10>AVERAGE($B$10:$B$21)，表示筛选定金金额大于平均值的数据。 4、用公式进行逻辑判断时，在条件区域的标题名称不能是数据库区域中的标题名称，如 上例中需要筛选定金金额大于平均值的数据时，作为条件区域的标题G2单元格不能输入 “定金金额”，只能输入其他标题或者空白，否则筛选不到结果。
打印顶端标题：适用于表格很很多行，需要在每一页都打印标题行，如下图所示操作， 左端标题行则适用于横向很多列的情况，即一页不能打印所有的列，分开多页打印。
分页预览：可以直观的看到打印每一页的范围，拖动蓝色虚线和实线，就可以手工调整 每页打印范围，分页预览一般配合页面设置的缩放比例进行调整，以达到最好的打印效 果。
对多个相同格式的工作表sheet进行批量操作：
先选择第一个sheet，按住shift键，点击最后一个sheet，这样就选中了多个工作 表，此时进行的任何操作都是对选中的所有表格进行批量操作，如编辑单元格，插入 行/列，输入公式等，适用于对多张表在相同的位置进行相同的操作，可以省去对多 张表逐一进行相同操作的麻烦。
4、公式求值：可以看到公式的计算过程，但对于过程单一的公式则直接显示结果。 5、计算选项：有时候输入公式后，计算结果不会自动更新，应该是“计算选项”设置成 了手动的原因，改为自动即可。
数据菜单简介 1、连接：适用于公式基本保持不变，但是引用的excel文件名有变化的情况。如附件中 的销售报表，2015年的报表公式已经全部设置完毕，即汇总表用VLOOKUP函数引用了1-3 月份的销售明细表。2016年年初复制2015年的文件夹，并更名为2016年销售报表，并把 里面的excel文件名中的年份全部更名为2016年，此时再打开汇总表，发现公式已经全 部不对了，此时点击数据菜单中的“编辑连接”，弹出编辑链接窗口，可以看到汇总表 所有的外部链接表格，发现文件名还是2015年的文件名：
查找和替换
在开始菜单最右边可以调出“查找和选择”菜单，如右上图所示。Ctrl+F调出窗口。 有时候在用vlookup等查找函数时目测有该项数据，但公式却没有返回结果，此时应考 虑关键字段是否有空格，查找内容输入一个空格，点击查找，如果找到，点击全部替 换，即完成批量删除空格，详见下图所示：
查找替换窗口中，如果没有选中特定区域，即光标停留在一个单元格内，默认是对当前 sheet所有单元格进行查找和替换的，如果选中了特定区域，则只对选中区域进行查找和替 换，如果需要对当前工作簿所有的工作表sheet进行查找和替换，需要点击选项按钮，在范 围选择：工作簿，此时还有更多选项，如区分大小写等。
选中需要更新外部工作簿链接的文件名，点击“更新源”，浏览到正确的文件即可，依次 更新完所有的链接后，公式则恢复正确。
高级筛选
常用的筛选，每列的条件都是逻辑“且”的关系，当筛选条件比较复杂时可以用高级 筛选。高级筛选必须有单独的条件区域
如附件的高级筛选.xlsx中，A2:H5单独的条件区域，条件区域的标题需要和数据库区 域标题保持一致，条件区域的标题可以重复（用于设置逻辑“或”条件）。第9行到第第 73行为数据库区域。
Ctrl+shift+回车 表示数组公式输入完毕
Alt+回车 ：在单元格内换行
Ctrl+home ：回到当前区域最顶端， Ctrl+end ：移动到当前区域最后一个单元格
F4 ：在输入公式时切换相对引用和绝对引用
F5 ：调用定位窗口
Alt+tab 切换工作簿
Ctrl+D 向下填充复制， Ctrl+R 向右填充复制，详见网页
工作表内的批量选择、冻结窗口：
批量选择：先选择第一个单元格，按住shift键，滚动滑轮点击最后一个单元格，则 完成操作。批量选择多列的区域时，最后点击需要选择区域的右下角最后一个单元格 即可。 冻结单元格：为了方便直观操作，可以冻结一个单元格的行或者列，冻结的方法：如 要需要冻结B3单元格，则需要先选择B4单元格，再点击 视图－冻结窗格－冻结拆分 窗格
将数值相加 计算数值的平均值（数学方法） 计算数值（包括字符串和逻辑值）的平均值（数学方法）。 计算包含数字的单元格个数 计算非空白单元格个数（包括字符串和逻辑值） 查找一组数值中的最大值 查找一组数值中的最大值（包括字符串和逻辑值） 查找一组数值中的最小值 查找一组数值中的最小值（包括文本和逻辑值） 将数字相乘
对多个工作表进行求和：如第一个工作表是汇总表，后面有3张按月份统计的明细 表，汇总表有一个单元格需要输入公式汇总后面3个表格某个数据，先在汇总表输 入公式，如＝SUM( 然后选中第一个明细表，按住shift键，选中最后一个表，再 在明细表中点击需要求和的单元格，输入公式的结尾右括号），回车完成公式的输 入。
工作簿和工作表的区别
一个工作簿就是一个EXCEL电子表格文件；工作表（sheet）是工作簿中的一张表， 是工作簿的一个组成部分。
选择性粘贴
用的最多的是“数值”，即去掉公式，只粘贴结果。 运算中最常用的是除，如需要把单位为元的数据在原单 元格转换成万元，则可以在一空白单元格输入10000， 复制这个单元格，然后选中需要转换的单元格，选择性 粘贴，选择“运算”中的“除”即可。 另外转置也会用到，效果是第一行的数据变成第一列， 以此类推。
EXCEL技巧
一、基础操作技巧
常用快捷键
Ctrl+A： 对当前单元格所在区域全选，遇到空白行或者列停止选中
Ctrl+S： 保存
Ctrl+P ： 打印
Ctrl+C：复制 Ctrl+V：粘贴
Ctrl+F： 调用查找和替换窗口
Ctrl+shift+方向箭头，表示按方向箭头方向选中区域，遇到空白行或者列停止
更多详细参考附件 2015年销售汇总.xlsx
条件格式
当希望在某种条件下，单元格会以特定的格式展现出来，此时就需要用到条件格式。 Excel提供了丰富的条件设置条件，如当单元格的值满足特定条件时，以特定的格式显示 出来，另外还可以设置复杂的公式来进行条件判断。详见下面2个附件：
数据透视表
Excel提供的数据透视表功能可以比较快捷对数据进行分类汇总，可以动态地改变它们的 版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改 变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生 更改，则可以通过刷新来更新数据透视表。
插入超链接
插入超链接可以是链接到某个文件、网页， 也可以链接到本工作簿中的某个表格，如右图所示 选中汇总表中的B2单元格，点击插入超链接→本文 档中的位置→1月，相当于在汇总表中制作了一 个目录，方便快速查看各个月份明细表。
插入对象
常用的插入对象操作：由文件创建→浏览到文件后，勾选“显示为图标”，然后点击 更改图标，在图标标题处输入希望显示的文件名，点击确定完成操作。双击该图标即可打 开该文件。如果勾选了链接到文件，则只提供该文件的链接，原文件更名或者被移动、删 除则无法打开。
条件区域简介： 1、输入包含运算符或者通配符的条件时，需要输入等号, 表达式需要用双引号，如在单 元格A3输入=“>=2014-9-1“,然后回车，单元格显示的是文本>=2014-9-1，用文本函数T 进行检测A3单元格，原因返回了>=2014-9-1，说明A3是文本指。
Fra Baidu bibliotek
2、逻辑表达方式：同一行并排输入表示逻辑“且”，分2行或者多行输入表示逻辑“或” 右图表示定金日期≥2014-9-1并且定金日期≤2014-9-30 ，输入 方式 =">=2014-9-1" ="<=2014-9-30"
右图表示商品包含“香”字或者“梨”字，此处字符前后都用了通配符*， 才能表达包含的逻辑，输入方式 ="*香*"
右图表示销售人员等于李四，此处没有用通配符，并且输入了等号，表示严 格等于的逻辑，不可以多或者少一个字符 此时必须输入2个等号，即输入="=王麻子"
右图表示筛选以字符“李四”开头的数据，可以筛选到“李四”、“李四 叔”、“李四嫂子”等以“李四”开头的数据，此时应直接输入李四，不 输入等号
此时公式为=SUM(‘1月:3月’!F9)， 1月:3月为第一个工作表名加上冒号接着是最后 一个工作表名，这个表达式表示是对1月，2月，3月3个工作表的引用。
事实上，不是所有的函数都支持对多个工作表进行引用，excel支持对多个工作表进行 引用的常用函数有：
SUM AVERAGE AVERAGEA COUNT COUNTA MAX MAXA MIN MINA PRODUCT
打印设置
页面设置可以在很多地方调用出来，如打印预览界面最下面、页面布局菜单里面的页 边距最下面、页面布局菜单的打印标题下面的角标处，如下图所示： 其中常用的是居中和上下左右页边距等设置。
页眉页脚选项卡里面可以设置页码，如在页脚的中间点击插入页码和总页数， 如左上图所示，再在中间输入“第”“ 页”“ 共” “ 页” 这几个字符，为了美 观分别在中间输入空格，效果如左下图所示，最终打印效果如右图所示。
3、筛选过程：
在条件区域输入完条件后，点击“数据”菜单的“高级”按钮弹出“高级筛选”窗口。 方式：勾选筛选结果的显示位置 列表区域：即数据库区域 条件区域：筛选需要的条件区域，如下图所示条件区域为D2:F4，表示要筛选王麻子或者 张三首期日期为9月份的数据。
在有条件区域和数据库区域时，可以使用一些数据库函数进行统计汇总，如求和函数 DSUM，
公式菜单简介：
1、追踪引用单元格：单击需要追踪的单元格，点击追踪引用单元格，即可直观的看 到该公式引用了哪些单元格，如果引用的其他的工作表，将会虚线箭头显示，双击虚 线，弹出定位窗口，选择需要追踪的单元格，点击确定则会跳转到对应的单元格位置。 2、追踪从属单元格：可以追踪本单元格被什么单元格的公式引用。 3、移去箭头：移去追踪的蓝色和黑色虚线箭头。
右图表示筛选王麻子首期日期为9月份或者张三首期日期为9 月份的数据
在条件区域需要以公式计算结果作为条件时：
1、公式计算结果必须为 TRUE 或 FALSE。 2、因为您正在使用公式，请像您平常那样输入公式，而不要以下列方式键入表达式： =‘’=条目‘’，即不需要按文本方式输入。 3、进行条件判断时，只需要输入第一行的引用，并且是相对引用，如下列的B10,公式中 的其他引用必须是绝对引用 ,如下列的$B$10:$B$21。附件中条件区域G3输入公式 =B10>AVERAGE($B$10:$B$21)，表示筛选定金金额大于平均值的数据。 4、用公式进行逻辑判断时，在条件区域的标题名称不能是数据库区域中的标题名称，如 上例中需要筛选定金金额大于平均值的数据时，作为条件区域的标题G2单元格不能输入 “定金金额”，只能输入其他标题或者空白，否则筛选不到结果。
打印顶端标题：适用于表格很很多行，需要在每一页都打印标题行，如下图所示操作， 左端标题行则适用于横向很多列的情况，即一页不能打印所有的列，分开多页打印。
分页预览：可以直观的看到打印每一页的范围，拖动蓝色虚线和实线，就可以手工调整 每页打印范围，分页预览一般配合页面设置的缩放比例进行调整，以达到最好的打印效 果。
对多个相同格式的工作表sheet进行批量操作：
先选择第一个sheet，按住shift键，点击最后一个sheet，这样就选中了多个工作 表，此时进行的任何操作都是对选中的所有表格进行批量操作，如编辑单元格，插入 行/列，输入公式等，适用于对多张表在相同的位置进行相同的操作，可以省去对多 张表逐一进行相同操作的麻烦。
4、公式求值：可以看到公式的计算过程，但对于过程单一的公式则直接显示结果。 5、计算选项：有时候输入公式后，计算结果不会自动更新，应该是“计算选项”设置成 了手动的原因，改为自动即可。
数据菜单简介 1、连接：适用于公式基本保持不变，但是引用的excel文件名有变化的情况。如附件中 的销售报表，2015年的报表公式已经全部设置完毕，即汇总表用VLOOKUP函数引用了1-3 月份的销售明细表。2016年年初复制2015年的文件夹，并更名为2016年销售报表，并把 里面的excel文件名中的年份全部更名为2016年，此时再打开汇总表，发现公式已经全 部不对了，此时点击数据菜单中的“编辑连接”，弹出编辑链接窗口，可以看到汇总表 所有的外部链接表格，发现文件名还是2015年的文件名：
查找和替换
在开始菜单最右边可以调出“查找和选择”菜单，如右上图所示。Ctrl+F调出窗口。 有时候在用vlookup等查找函数时目测有该项数据，但公式却没有返回结果，此时应考 虑关键字段是否有空格，查找内容输入一个空格，点击查找，如果找到，点击全部替 换，即完成批量删除空格，详见下图所示：
查找替换窗口中，如果没有选中特定区域，即光标停留在一个单元格内，默认是对当前 sheet所有单元格进行查找和替换的，如果选中了特定区域，则只对选中区域进行查找和替 换，如果需要对当前工作簿所有的工作表sheet进行查找和替换，需要点击选项按钮，在范 围选择：工作簿，此时还有更多选项，如区分大小写等。
选中需要更新外部工作簿链接的文件名，点击“更新源”，浏览到正确的文件即可，依次 更新完所有的链接后，公式则恢复正确。
高级筛选
常用的筛选，每列的条件都是逻辑“且”的关系，当筛选条件比较复杂时可以用高级 筛选。高级筛选必须有单独的条件区域
如附件的高级筛选.xlsx中，A2:H5单独的条件区域，条件区域的标题需要和数据库区 域标题保持一致，条件区域的标题可以重复（用于设置逻辑“或”条件）。第9行到第第 73行为数据库区域。
Ctrl+shift+回车 表示数组公式输入完毕
Alt+回车 ：在单元格内换行
Ctrl+home ：回到当前区域最顶端， Ctrl+end ：移动到当前区域最后一个单元格
F4 ：在输入公式时切换相对引用和绝对引用
F5 ：调用定位窗口
Alt+tab 切换工作簿
Ctrl+D 向下填充复制， Ctrl+R 向右填充复制，详见网页
工作表内的批量选择、冻结窗口：
批量选择：先选择第一个单元格，按住shift键，滚动滑轮点击最后一个单元格，则 完成操作。批量选择多列的区域时，最后点击需要选择区域的右下角最后一个单元格 即可。 冻结单元格：为了方便直观操作，可以冻结一个单元格的行或者列，冻结的方法：如 要需要冻结B3单元格，则需要先选择B4单元格，再点击 视图－冻结窗格－冻结拆分 窗格
将数值相加 计算数值的平均值（数学方法） 计算数值（包括字符串和逻辑值）的平均值（数学方法）。 计算包含数字的单元格个数 计算非空白单元格个数（包括字符串和逻辑值） 查找一组数值中的最大值 查找一组数值中的最大值（包括字符串和逻辑值） 查找一组数值中的最小值 查找一组数值中的最小值（包括文本和逻辑值） 将数字相乘
对多个工作表进行求和：如第一个工作表是汇总表，后面有3张按月份统计的明细 表，汇总表有一个单元格需要输入公式汇总后面3个表格某个数据，先在汇总表输 入公式，如＝SUM( 然后选中第一个明细表，按住shift键，选中最后一个表，再 在明细表中点击需要求和的单元格，输入公式的结尾右括号），回车完成公式的输 入。
工作簿和工作表的区别
一个工作簿就是一个EXCEL电子表格文件；工作表（sheet）是工作簿中的一张表， 是工作簿的一个组成部分。
选择性粘贴
用的最多的是“数值”，即去掉公式，只粘贴结果。 运算中最常用的是除，如需要把单位为元的数据在原单 元格转换成万元，则可以在一空白单元格输入10000， 复制这个单元格，然后选中需要转换的单元格，选择性 粘贴，选择“运算”中的“除”即可。 另外转置也会用到，效果是第一行的数据变成第一列， 以此类推。
EXCEL技巧
一、基础操作技巧
常用快捷键
Ctrl+A： 对当前单元格所在区域全选，遇到空白行或者列停止选中
Ctrl+S： 保存
Ctrl+P ： 打印
Ctrl+C：复制 Ctrl+V：粘贴
Ctrl+F： 调用查找和替换窗口
Ctrl+shift+方向箭头，表示按方向箭头方向选中区域，遇到空白行或者列停止
更多详细参考附件 2015年销售汇总.xlsx
条件格式
当希望在某种条件下，单元格会以特定的格式展现出来，此时就需要用到条件格式。 Excel提供了丰富的条件设置条件，如当单元格的值满足特定条件时，以特定的格式显示 出来，另外还可以设置复杂的公式来进行条件判断。详见下面2个附件：
数据透视表
Excel提供的数据透视表功能可以比较快捷对数据进行分类汇总，可以动态地改变它们的 版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改 变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生 更改，则可以通过刷新来更新数据透视表。
插入超链接
插入超链接可以是链接到某个文件、网页， 也可以链接到本工作簿中的某个表格，如右图所示 选中汇总表中的B2单元格，点击插入超链接→本文 档中的位置→1月，相当于在汇总表中制作了一 个目录，方便快速查看各个月份明细表。
插入对象
常用的插入对象操作：由文件创建→浏览到文件后，勾选“显示为图标”，然后点击 更改图标，在图标标题处输入希望显示的文件名，点击确定完成操作。双击该图标即可打 开该文件。如果勾选了链接到文件，则只提供该文件的链接，原文件更名或者被移动、删 除则无法打开。
条件区域简介： 1、输入包含运算符或者通配符的条件时，需要输入等号, 表达式需要用双引号，如在单 元格A3输入=“>=2014-9-1“,然后回车，单元格显示的是文本>=2014-9-1，用文本函数T 进行检测A3单元格，原因返回了>=2014-9-1，说明A3是文本指。
Fra Baidu bibliotek
2、逻辑表达方式：同一行并排输入表示逻辑“且”，分2行或者多行输入表示逻辑“或” 右图表示定金日期≥2014-9-1并且定金日期≤2014-9-30 ，输入 方式 =">=2014-9-1" ="<=2014-9-30"
右图表示商品包含“香”字或者“梨”字，此处字符前后都用了通配符*， 才能表达包含的逻辑，输入方式 ="*香*"
右图表示销售人员等于李四，此处没有用通配符，并且输入了等号，表示严 格等于的逻辑，不可以多或者少一个字符 此时必须输入2个等号，即输入="=王麻子"
右图表示筛选以字符“李四”开头的数据，可以筛选到“李四”、“李四 叔”、“李四嫂子”等以“李四”开头的数据，此时应直接输入李四，不 输入等号
此时公式为=SUM(‘1月:3月’!F9)， 1月:3月为第一个工作表名加上冒号接着是最后 一个工作表名，这个表达式表示是对1月，2月，3月3个工作表的引用。
事实上，不是所有的函数都支持对多个工作表进行引用，excel支持对多个工作表进行 引用的常用函数有：
SUM AVERAGE AVERAGEA COUNT COUNTA MAX MAXA MIN MINA PRODUCT
打印设置
页面设置可以在很多地方调用出来，如打印预览界面最下面、页面布局菜单里面的页 边距最下面、页面布局菜单的打印标题下面的角标处，如下图所示： 其中常用的是居中和上下左右页边距等设置。
页眉页脚选项卡里面可以设置页码，如在页脚的中间点击插入页码和总页数， 如左上图所示，再在中间输入“第”“ 页”“ 共” “ 页” 这几个字符，为了美 观分别在中间输入空格，效果如左下图所示，最终打印效果如右图所示。
3、筛选过程：
在条件区域输入完条件后，点击“数据”菜单的“高级”按钮弹出“高级筛选”窗口。 方式：勾选筛选结果的显示位置 列表区域：即数据库区域 条件区域：筛选需要的条件区域，如下图所示条件区域为D2:F4，表示要筛选王麻子或者 张三首期日期为9月份的数据。
在有条件区域和数据库区域时，可以使用一些数据库函数进行统计汇总，如求和函数 DSUM，
公式菜单简介：
1、追踪引用单元格：单击需要追踪的单元格，点击追踪引用单元格，即可直观的看 到该公式引用了哪些单元格，如果引用的其他的工作表，将会虚线箭头显示，双击虚 线，弹出定位窗口，选择需要追踪的单元格，点击确定则会跳转到对应的单元格位置。 2、追踪从属单元格：可以追踪本单元格被什么单元格的公式引用。 3、移去箭头：移去追踪的蓝色和黑色虚线箭头。