Excel技巧_IT电脑知识

1、ITIT电脑知识电脑知识 - Excel高阶篇 -特别感谢特别感谢汤思量主任汤思量主任友情赞助友情赞助Page 3ExcelExcel基础知识基础知识1ExcelExcel格式修改细节格式修改细节2ExcelExcel打印得心应手打印得心应手3ExcelExcel常用函数常用函数4目目 录录特别补充特别补充- -日期计算日期计算5Page 4思考：思考：我们为什么要学习该项技能？我们为什么要学习该项技能？Page 5ExcelExcel基础知识基础知识1Page 6一、打开和关闭一、打开和关闭 打开打开ExcelExcel方式：方式： 右键右键 - - 新建新建- microsoft exce

2、l- microsoft excel工作表工作表 开始开始 - - 所有程序所有程序 - Microsoft Office - Microsoft Office Excel Excel 开始开始 - - 运行运行 - - 输入输入excelexcel 双击桌面快捷方式双击桌面快捷方式 直接在程序安装位置直接在程序安装位置C C盘找到程序文件打开盘找到程序文件打开 关闭关闭ExcelExcel方式：方式： 直接点右上角的关闭按钮直接点右上角的关闭按钮 点点“文件文件”菜单中的菜单中的“退出退出”按钮按钮 在任务栏上点文件鼠标右键，选择在任务栏上点文件鼠标右键，选择“关闭关闭” 双击工作簿左上角图

3、标双击工作簿左上角图标 Alt+F4Alt+F4或或ctrl+F4ctrl+F4 Ctrl+WCtrl+W 直接切断计算机电源关闭直接切断计算机电源关闭还有更绝的吗？还有更绝的吗？_ Page 7二、工作簿和工作表二、工作簿和工作表工作簿：工作簿：【BookBook】 一个一个ExcelExcel文件就是一个工作簿，可由一个文件就是一个工作簿，可由一个或多个工作表组成，默认名字为或多个工作表组成，默认名字为“book1”book1”。工作表：工作表：【SheetSheet】 用于存储和管理数据的主要文档，又称电子用于存储和管理数据的主要文档，又称电子表格，默认名字为表格，默认名字为“sheet

4、1”,sheet1”,用列号（用列号（A A、B B、C)C)和行号（和行号（1 1、2 2、3)3)命名。命名。单元格单元格 是是ExcelExcel的基本元素，是整体操作的最小单的基本元素，是整体操作的最小单位，可以存放文字、数字和公式标记：用行标和位，可以存放文字、数字和公式标记：用行标和列标一起，如列标一起，如B5B5，连续的单元格区域用冒号隔开，连续的单元格区域用冒号隔开，如如A3:F8A3:F8。Page 8三、行和列三、行和列 【列列】“竖竖”者为列，者为列，excelexcel中用大写英文表示中用大写英文表示ExcelExcel中最大的列数为：中最大的列数为：256256列列

5、【行行】“横横”者为行，者为行，excelexcel中用阿拉伯数字表示中用阿拉伯数字表示ExcelExcel中最大的行数为：中最大的行数为：6553665536行行Page 9ExcelExcel格式修改细格式修改细节节2Page 10一、单元格的选定一、单元格的选定选定单独的单元格选定单独的单元格选定整行或整列选定整行或整列选定连续的多单元格：选定连续的多单元格：选定不连续的多单元格：选定不连续的多单元格：选定只有内容的单元格：选定只有内容的单元格：选定所有的单元格选定所有的单元格鼠标拖曳鼠标拖曳ctrlctrlshift+ctrl+shift+ctrl+箭头箭头Page 11二、提高输入效

6、率二、提高输入效率“记忆式记忆式”键入键入 单击单击“工具工具”菜单中的菜单中的“选项选项”命令，在命令，在“编辑编辑”选项卡中选中选项卡中选中“记忆式键入记忆式键入”快速输入当天日期：快速输入当天日期：快速输入当前时间：快速输入当前时间：当输入数字太多时，以科学记数或以当输入数字太多时，以科学记数或以“# #”表示，表明该列没有足够的宽度，需调整。表示，表明该列没有足够的宽度，需调整。ctrl+ctrl+；Ctrl+Shift+Ctrl+Shift+；Page 12三、合适列宽、行高三、合适列宽、行高点击工作表左上角的“全选”按钮，选中全部单元格然后把鼠标移到任何两列的交界线处，待鼠标指针变

7、成带两个方向箭头的竖线时，双击鼠标，就可以得到合适的列宽了(正好使单元格的内容得到完全显示) 同样，我们将鼠标移到行与行的交界线处双击，也可以得到合适的行高。 Page 13四、四、“有效性有效性”输入输入目的：目的： 限定输入数据的有效范围，如指定为特定数限定输入数据的有效范围，如指定为特定数据类型、取值范围、限定输入的字符数选定据类型、取值范围、限定输入的字符数选定需要设置有效性的单元格区域需要设置有效性的单元格区域操作方法：操作方法： 数据数据 有效性有效性 设置设置 允许允许 序列序列 在在“来源来源”处填写选择内容处填写选择内容 注意两个选项之间必须用注意两个选项之间必须用 英文格式

8、下的英文格式下的逗号逗号隔开隔开Page 14五、移动单元格五、移动单元格直接拖动直接拖动 移动移动CtrlCtrl拖动拖动 复制复制ShiftShift拖动拖动 插入插入Page 15六、设置条件格式六、设置条件格式用途：设定单元格在符合某条件时的显示用途：设定单元格在符合某条件时的显示方法：方法：Page 16七、设置单元格保护七、设置单元格保护用途：设定保护可防止对单元格进行移动、用途：设定保护可防止对单元格进行移动、修改、删除及隐藏，还可隐藏公式。修改、删除及隐藏，还可隐藏公式。方法：只有工作表被保护，锁定单元格或方法：只有工作表被保护，锁定单元格或隐藏公式才有效隐藏公式才有效Page

9、 17八、更改表格线颜色八、更改表格线颜色方法一：“工具选项”，打开“选项”对话框，点击“视图”选项卡 在 “网格线颜色”下拉列表中，可以为单元格边框线重新指定一种颜色 Page 18方法二：选定单元格区域后，右键选择“设置单元格格式”，打开“单元格格式”对话框 点击“边框”选项卡，然后指定边框线的颜色、线型，为单元格的四个边框分别指定边框线 八、更改表格线颜色八、更改表格线颜色Page 19“工具选项”在打开的“选项”对话框中点击“视图”选项卡 去掉“窗口选项”中的“零值”复选项的选择 九、零值不显示九、零值不显示Page 20让每一页都显示标题行单击表格标题行下边那行最前端的单元格 “窗口

10、冻结窗口”十、冻结窗口十、冻结窗口Page 21十一、表头斜线十一、表头斜线在A1单元格中，输入“项目名称”，然后按两次Alt+回车键，再输入“日期”“格式单元格”，打开“单元格格式”对话框点击“边框”选项卡单击左斜线按钮点击“对齐”选项卡，将文本的水平和垂直对齐方式都设置为“两端对齐” “确定”双击A1单元格，将插入点定位到“项目名称”左边，并用空格键调整“项目名称”的位置至合适为止Page 22十二、加密十二、加密“文件另存为”在弹出的“另存为”对话框中，点击“工具”栏下的“常规选项”按钮在弹出的“保存选项”对话框中输入密码，确定Page 23十三、行列转换粘贴十三、行列转换粘贴复制行或列

11、内容右键-“选择性粘贴”选择“数值”和“转制”Page 24ExcelExcel打印得心应手打印得心应手3Page 25一、固定标题打印一、固定标题打印让每一页打印输出后都有标题行“文件页面设置” “工作表”选项卡 点击“顶端标题行”框后面的红色箭头按钮打开“页面设置-顶端标题行”对话框在工作表中选择好需要在每一页都打印输出的标题行退出“页面设置-顶端标题行”对话框后，确定 Page 26二、选择希望打印的区域二、选择希望打印的区域先在工作表中选择要打印的区域 “文件打印” 在打开的“打印内容”对话框中，选中“选定区域”单选项 Page 27三、打印整页显示三、打印整页显示打印预览打印预览选择

12、选择“设置设置”页面页面“纵向纵向”或或“横向横向”调整调整“缩放比例缩放比例”Page 28四、同时对多个工作表设置打印要求四、同时对多个工作表设置打印要求 一个工作簿中往往有多个工作表，而在打印时往往这些工作表的设置又基本相同。如果逐份工作表设置，那就会很麻烦，怎么办？方法一：先单击第一张工作表的标签，然后按住“Ctrl”键再单击其它工作表的标签，就可以将这些工作表同时选中。然后再对它们进行相应的页面设置。方法二：如果这些工作表是相邻的，那么，我们可以单击第一份工作表标签后，再按住“Shift”键，单击最后一份工作表标签，将它们之间的工作表同时选中。然后再对它们进行相应的页面设置。 Pag

13、e 29五、同时打印多个工作簿五、同时打印多个工作簿“文件 打开” 在“打开”对话框中，按住“Ctrl”键，逐一选中要打印的工作簿然后点击对话框中“工具”菜单中的“打印”命令即可Page 30六、信函套打六、信函套打准备工作：一个一个wordword文档：文档：续期缴费通知书模版续期缴费通知书模版一个一个excelexcel文档：文档：客户信息（对应通知书客户信息（对应通知书上的缺失信息）上的缺失信息）Page 31六、信函套打六、信函套打建立word文档与excel文档的联系在在wordword文档中，进行文档中，进行“视图视图工具栏工具栏邮件合并邮件合并”操作操作点击点击“邮件合并工具栏邮

14、件合并工具栏”中的中的“打开数据源打开数据源”按钮，在弹出的按钮，在弹出的“选选择数据源择数据源”对话框中打开客户信息的对话框中打开客户信息的excelexcel文档，并在接下来的文档，并在接下来的“选择表格选择表格”对话框中选择对应的对话框中选择对应的sheetsheet调用数据将鼠标点在将鼠标点在“投保人姓名投保人姓名”前面的横线上前面的横线上点击点击“合并邮件工具栏合并邮件工具栏”中的中的“插入域插入域”按钮，在弹出的按钮，在弹出的“插入合插入合并域并域”对话框中选择对话框中选择“投保人姓名投保人姓名”，插入，插入用同样的方法，插入其他的信息，如地址、邮编、应缴日期等用同样的方法，插入其

15、他的信息，如地址、邮编、应缴日期等查看数据点击点击“合并邮件工具栏合并邮件工具栏”中的中的“查看合并数据查看合并数据”按钮，即可查看数按钮，即可查看数据据打印“合并邮件工具栏合并邮件工具栏”中的中的“合并到打印机合并到打印机”按钮，即可打印按钮，即可打印Page 32ExcelExcel常用函数常用函数4Page 33一、求和函数一、求和函数【SUMSUM】行或列求和 H3单元格 =SUM(B3:G3)B11单元格 =SUM(B3:B10)区域求和 H11单元格 =SUM(B3:G10)Page 34鼠标拖拉法选中存放求和结果的单元格，点击工具栏中的“”（自动求和）按钮用鼠标拖过需要求和的所有

16、单元格，然后回车即可注：如果需要求和的单元格是不连续的，则可按住Ctrl键分别拖过它们。一、求和函数一、求和函数【SUMSUM】Page 352 2、平均数函数、平均数函数【AVERAGEAVERAGE】AVERAGE函数用途：求算数平均数请注意下面两个函数的书写=AVERAGE(A14:A19)=AVERAGEA(A14:A19)分数平均分15065.6=AVERAGE(A14:A19)（328/5=65.6）无效64平均分28554.7=AVERAGEA(A14:A19)（328/6=54.7）3495Page 36三、计数函数三、计数函数【COUNTCOUNT】用途：计数COUNT统计所

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

18、式为真当逻辑判断式为真时，返回的值时，返回的值当逻辑判断式为当逻辑判断式为假时，返回的值假时，返回的值Page 39COUNTIFCOUNTIF函数函数功能：统计符合条件要求的单元格的数量例，求出在B2到B4这三个单元格中大于20的数字有几个五、五、IFIF函数函数【COUNTIF/SUMIFCOUNTIF/SUMIF】Page 40SUMIFSUMIF函数函数功能：统计符合条件要求的单元格中数字之和例，可以求出在B2到B4这三个单元格中大于20的数字之和五、五、IFIF函数函数【COUNTIF/SUMIFCOUNTIF/SUMIF】Page 41六、索引函数六、索引函数【Vlookup/Hl

19、ookupVlookup/Hlookup】Vlookup用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值Hlookup用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用Hlookup当比较值位于数据表的最左列，并且要查找右面给定列中的数据时，请使用VlookupPage 42七、美化表格的函数七、美化表格的函数【MODMOD】功能：设置间隔式底纹例，Page 43选中需要设置间隔式底纹的区域“格式条件格式”，弹出的“条件格式”对话框（如图）在左侧的下拉菜单中选择

20、“公式”，并在右侧区域输入=MOD(ROW()，2),然后点击“格式”按钮七、美化表格的函数七、美化表格的函数【MODMOD】Page 44八、字段取数函数八、字段取数函数【midmid】从左边取从左边取 =left(A6,3=left(A6,3）从右边取从右边取 =right(A6,3)=right(A6,3)从中间取从中间取 =mid(A6,4,3)=mid(A6,4,3) 从第四个字段开始共取3个数字Page 45九、文本函数九、文本函数【texttext】例如保全批量上载模版需要的是文本格式，例如保全批量上载模版需要的是文本格式，如何将数字转换为文本格式？如何将数字转换为文本格式？ =

21、TEXT(A2,0) =TEXT(A2,0)注意左上角部分的绿色标志注意左上角部分的绿色标志保单总数为保单总数为1515位，前面不足位数需补零，位，前面不足位数需补零，如何操作？如何操作？ =TEXT(A2,000000000000000) =TEXT(A2,000000000000000)Page 46特别补充特别补充- -日期计日期计算算5Page 47一、显示当前日期一、显示当前日期显示当天日期显示当天日期（即能够自动（即能够自动更新）更新） 函数函数TODAY显示工作时的显示工作时的日期日期Ctrl + ;显示当前日期显示当前日期Page 48二、快速输入日期二、快速输入日期先在工作表的A2单元格中输入日期“2008-4-1”在末尾右格处往下拖曳右尾格点下拉选项可进行多种方式的填充输入Page 49三、计算对应的星期数三、计算对应的星期数先在日期一列输入日期，如“2007-4-21”,并将格式设置为日期格式；在B2单元格中输入公式： =CHOOSE(WEEKDAY(A4,2),星期一,星期二,星期三,星期四,星期五,星期六,星期日)Page 50四、计算年数四、计算年数在B5单元格中输入公式： =YEAR(B3)-YEAR(B2)Page 51五、计算天数五、计算天数Page 52六、计算工作日六