Excel技巧-IT电脑知识分析课件

1、IT电脑知识 - Excel高阶篇 -第1页，共57页。特别感谢汤思量主任友情赞助第2页，共57页。Excel基础知识1Excel格式修改细节2Excel打印得心应手3Excel常用函数4目 录特别补充-日期计算5第3页，共57页。思考：我们为什么要学习该项技能？第4页，共57页。Excel基础知识1第5页，共57页。一、打开和关闭 打开Excel方式：右键 - 新建- microsoft excel工作表开始 - 所有程序 - Microsoft Office Excel开始 - 运行 - 输入excel双击桌面快捷方式直接在程序安装位置C盘找到程序文件打开 关闭Excel方式：直接点右上角

2、的关闭按钮 点“文件”菜单中的“退出”按钮 在任务栏上点文件鼠标右键，选择“关闭”双击工作簿左上角图标Alt+F4或ctrl+F4Ctrl+W直接切断计算机电源关闭还有更绝的吗？_ 第6页，共57页。二、工作簿和工作表工作簿：【Book】 一个Excel文件就是一个工作簿，可由一个或多个工作表组成，默认名字为“book1”。工作表：【Sheet】 用于存储和管理数据的主要文档，又称电子表格，默认名字为“sheet1”,用列号（A、B、C)和行号（1、2、3)命名。单元格 是Excel的基本元素，是整体操作的最小单位，可以存放文字、数字和公式标记：用行标和列标一起，如B5，连续的单元格区域用冒号

3、隔开，如A3:F8。第7页，共57页。三、行和列 【列】“竖”者为列，excel中用大写英文表示Excel中最大的列数为：256列 【行】“横”者为行，excel中用阿拉伯数字表示Excel中最大的行数为：65536行第8页，共57页。Excel格式修改细节2第9页，共57页。一、单元格的选定选定单独的单元格选定整行或整列选定连续的多单元格：选定不连续的多单元格：选定只有内容的单元格：选定所有的单元格鼠标拖曳ctrlshift+ctrl+箭头第10页，共57页。二、提高输入效率“记忆式”键入 单击“工具”菜单中的“选项”命令，在“编辑”选项卡中选中“记忆式键入”快速输入当天日期：快速输入当前时

4、间：当输入数字太多时，以科学记数或以“#”表示，表明该列没有足够的宽度，需调整。ctrl+；Ctrl+Shift+；第11页，共57页。三、合适列宽、行高点击工作表左上角的“全选”按钮，选中全部单元格然后把鼠标移到任何两列的交界线处，待鼠标指针变成带两个方向箭头的竖线时，双击鼠标，就可以得到合适的列宽了(正好使单元格的内容得到完全显示) 同样，我们将鼠标移到行与行的交界线处双击，也可以得到合适的行高。 第12页，共57页。四、“有效性”输入目的：限定输入数据的有效范围，如指定为特定数据类型、取值范围、限定输入的字符数选定需要设置有效性的单元格区域操作方法：数据 有效性设置 允许 序列在“来源”

5、处填写选择内容注意两个选项之间必须用 英文格式下的逗号隔开第13页，共57页。五、移动单元格直接拖动 移动Ctrl拖动 复制Shift拖动 插入第14页，共57页。六、设置条件格式用途：设定单元格在符合某条件时的显示方法：第15页，共57页。七、设置单元格保护用途：设定保护可防止对单元格进行移动、修改、删除及隐藏，还可隐藏公式。方法：只有工作表被保护，锁定单元格或隐藏公式才有效第16页，共57页。八、更改表格线颜色方法一：“工具选项”，打开“选项”对话框，点击“视图”选项卡 在 “网格线颜色”下拉列表中，可以为单元格边框线重新指定一种颜色 第17页，共57页。方法二：选定单元格区域后，右键选择

6、“设置单元格格式”，打开“单元格格式”对话框 点击“边框”选项卡，然后指定边框线的颜色、线型，为单元格的四个边框分别指定边框线 八、更改表格线颜色两种有何区别？第18页，共57页。“工具选项”在打开的“选项”对话框中点击“视图”选项卡 去掉“窗口选项”中的“零值”复选项的选择 九、零值不显示第19页，共57页。让每一页都显示标题行单击表格标题行下边那行最前端的单元格 “窗口冻结窗口”十、冻结窗口第20页，共57页。十一、表头斜线在A1单元格中，输入“项目名称”，然后按两次Alt+回车键，再输入“日期”“格式单元格”，打开“单元格格式”对话框点击“边框”选项卡单击左斜线按钮点击“对齐”选项卡，将

7、文本的水平和垂直对齐方式都设置为“两端对齐” “确定”双击A1单元格，将插入点定位到“项目名称”左边，并用空格键调整“项目名称”的位置至合适为止第21页，共57页。十二、加密“文件另存为”在弹出的“另存为”对话框中，点击“工具”栏下的“常规选项”按钮在弹出的“保存选项”对话框中输入密码，确定第22页，共57页。十三、行列转换粘贴复制行或列内容右键-“选择性粘贴”选择“数值”和“转制”第23页，共57页。Excel打印得心应手3第24页，共57页。一、固定标题打印让每一页打印输出后都有标题行“文件页面设置” “工作表”选项卡 点击“顶端标题行”框后面的红色箭头按钮打开“页面设置-顶端标题行”对话

8、框在工作表中选择好需要在每一页都打印输出的标题行退出“页面设置-顶端标题行”对话框后，确定 第25页，共57页。二、选择希望打印的区域先在工作表中选择要打印的区域 “文件打印” 在打开的“打印内容”对话框中，选中“选定区域”单选项 第26页，共57页。三、打印整页显示打印预览选择“设置”页面“纵向”或“横向”调整“缩放比例”第27页，共57页。四、同时对多个工作表设置打印要求 一个工作簿中往往有多个工作表，而在打印时往往这些工作表的设置又基本相同。如果逐份工作表设置，那就会很麻烦，怎么办？方法一：先单击第一张工作表的标签，然后按住“Ctrl”键再单击其它工作表的标签，就可以将这些工作表同时选中

9、。然后再对它们进行相应的页面设置。方法二：如果这些工作表是相邻的，那么，我们可以单击第一份工作表标签后，再按住“Shift”键，单击最后一份工作表标签，将它们之间的工作表同时选中。然后再对它们进行相应的页面设置。 第28页，共57页。五、同时打印多个工作簿“文件 打开” 在“打开”对话框中，按住“Ctrl”键，逐一选中要打印的工作簿然后点击对话框中“工具”菜单中的“打印”命令即可第29页，共57页。六、信函套打准备工作：一个word文档：续期缴费通知书模版一个excel文档：客户信息（对应通知书上的缺失信息）例：续期缴费通知书批量打印第30页，共57页。六、信函套打建立word文档与excel

10、文档的联系在word文档中，进行“视图工具栏邮件合并”操作点击“邮件合并工具栏”中的“打开数据源”按钮，在弹出的“选择数据源”对话框中打开客户信息的excel文档，并在接下来的“选择表格”对话框中选择对应的sheet调用数据将鼠标点在“投保人姓名”前面的横线上点击“合并邮件工具栏”中的“插入域”按钮，在弹出的“插入合并域”对话框中选择“投保人姓名”，插入用同样的方法，插入其他的信息，如地址、邮编、应缴日期等查看数据点击“合并邮件工具栏”中的“查看合并数据”按钮，即可查看数据打印“合并邮件工具栏”中的“合并到打印机”按钮，即可打印第31页，共57页。Excel常用函数4第32页，共57页。一、求

11、和函数【SUM】行或列求和 H3单元格 =SUM(B3:G3)B11单元格 =SUM(B3:B10)区域求和 H11单元格 =SUM(B3:G10)第33页，共57页。鼠标拖拉法选中存放求和结果的单元格，点击工具栏中的“”（自动求和）按钮用鼠标拖过需要求和的所有单元格，然后回车即可注：如果需要求和的单元格是不连续的，则可按住Ctrl键分别拖过它们。一、求和函数【SUM】第34页，共57页。2、平均数函数【AVERAGE】AVERAGE函数用途：求算数平均数请注意下面两个函数的书写=AVERAGE(A14:A19)=AVERAGEA(A14:A19)第35页，共57页。三、计数函数【COUNT】

12、用途：计数COUNT统计所选中的单元格区域中数字的个数COUNTA统计所选中的单元格区域中非空单元格的个数（包括数字、文字等）例，第36页，共57页。四、常用函数快捷方式在excel表格的右下角有一个常用函数快捷方式的区域（如同所示），可以右键点击该区域，使用六种常用函数这六种常用函数，分别是：平均值（AVERAGE）计数（COUNTA）计数值（COUNT）最大值（MAX）最小值（MIN）求和（SUM）第37页，共57页。五、IF函数【COUNTIF/SUMIF】 IF函数在B12单元格中输入： =IF(B11=60,”合格”,”不合格”)逻辑判断式当逻辑判断式为真时，返回的值当逻辑判断式为假

13、时，返回的值第38页，共57页。COUNTIF函数功能：统计符合条件要求的单元格的数量例，求出在B2到B4这三个单元格中大于20的数字有几个五、IF函数【COUNTIF/SUMIF】第39页，共57页。SUMIF函数功能：统计符合条件要求的单元格中数字之和例，可以求出在B2到B4这三个单元格中大于20的数字之和五、IF函数【COUNTIF/SUMIF】第40页，共57页。六、索引函数【Vlookup/Hlookup】Vlookup用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值Hlookup用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行

14、中指定列处的数值当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用Hlookup当比较值位于数据表的最左列，并且要查找右面给定列中的数据时，请使用Vlookup第41页，共57页。七、美化表格的函数【MOD】功能：设置间隔式底纹例，如何实现？第42页，共57页。选中需要设置间隔式底纹的区域“格式条件格式”，弹出的“条件格式”对话框（如图）在左侧的下拉菜单中选择“公式”，并在右侧区域输入=MOD(ROW()，2),然后点击“格式”按钮七、美化表格的函数【MOD】第43页，共57页。八、字段取数函数【mid】从左边取 =left(A6,3）从右边取 =right(A6,3)从中间取

15、=mid(A6,4,3) 从第四个字段开始共取3个数字第44页，共57页。九、文本函数【text】例如保全批量上载模版需要的是文本格式，如何将数字转换为文本格式？ =TEXT(A2,0)注意左上角部分的绿色标志保单总数为15位，前面不足位数需补零，如何操作？ =TEXT(A2,000000000000000)第45页，共57页。特别补充-日期计算5第46页，共57页。一、显示当前日期显示当天日期（即能够自动更新） 函数TODAY显示工作时的日期Ctrl + ;显示当前日期第47页，共57页。二、快速输入日期先在工作表的A2单元格中输入日期“2008-4-1”在末尾右格处往下拖曳右尾格点下拉选项

16、可进行多种方式的填充输入第48页，共57页。三、计算对应的星期数先在日期一列输入日期，如“2007-4-21”,并将格式设置为日期格式；在B2单元格中输入公式： =CHOOSE(WEEKDAY(A4,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日)第49页，共57页。四、计算年数在B5单元格中输入公式： =YEAR(B3)-YEAR(B2)第50页，共57页。五、计算天数第51页，共57页。六、计算工作日安装分析工具库选择菜单“工具加载宏”命令，打开“加载宏”对话框，勾选“可用加载宏”下方列表框内的“分析工具库”复选框，单击“确定”按钮完成安装。NETWORKDAYS (start_date,end_date)将单元格格式设置为“常规”即可注意：仅排除双周日第52页，共57页。六、计算工作日将期间的非周末的节假日排除NETWORKDAYS (start_date,end_date, holidays)第53页，共57页。七、计算若干年、月、日后的日期DATE函数例，计划用1年2个月零15天来完成一本图书的编写工作，开工日期为2004年7月28日，那么什么时候能完成图书的编写呢？ 在B8单元格中输入公式： =DATE(YEAR(B2)+B4,MONTH(B2)+B5,DAY(B2)+B6)第54