EXCEL日常使用方法和函数.doc

EXCEL日常使用方法简介EXCEL日常使用方法简介11.Excel文件的加密22.单元格的保护与隐藏23.快速填充相同数据34.公式中的引用45.直接输入数字型字符串46.文本变为数值57.输入公式58.在单元格有数据区域间快速移动59.格式刷510.使用Excel状态栏查看数据511.行列转置512.制作表格613.数据比对614.删除内容相同的重复行715.单元格中数字的自定义格式716.所见非所得717.根据公民身份号码求出生时间718.函数的使用719.数据透视表820.工作中的函数821.网上申报8本次培训所涉及的内容基本是我们在日常工作中的总结，适用对象是工作中使用过Excel 2003、但不是十分精通的人员。其中一些操作方法未必是最好、最方便的。学习目标是了解日常用法和网上申报。1. Excel文件的加密如果不愿意自己的Excel文件被别人查看，可以设置密码保护。具体方法为：A、单击“文件”菜单栏中的“保存或者（另存为）”命令后，在弹出的“保存或者（另存为）”的对话框中输入文件名； B、再单击这个对话框中“工具”栏下的“常规选项”按钮，在弹出的“保存选项”的对话框中输入自己的密码； 注意，它提供了两层保护，如果也设置修改权限密码的话，那么即使文件被打开也还需要输入修改权限的密码才能修改。C、单击“确定”按钮，在弹出的密码确认窗口中重新输入一遍密码，再点击“确认”，最后点“保存” 完成文件的加密工作。当重新打开文件时就要求输入密码，如果密码不正确，文件将不能打开。 若在打开文件的过程中设置密码保护或修改密码，可以通过“工具”菜单中的“选项”、“安全性”进行设置。说明：建议将密码设置为包括数字、字母和特殊符号的6位组合；多个文档之间（与快捷键Ctrl+F6不同，可以为不同类型的文件）切换使用Windows切换程序的快捷键Alt+Tab来实现：按住Alt键不放，多按几次Tab键，选中想要编辑的文件后放开键盘，就能切换到想要的文件。2. 单元格的保护与隐藏Excel单元格的读写保护是对数据进行安全管理的基础。对单元格的保护分为写保护和读保护两类，所谓写保护就是对单元格中输入信息加以限制，读保护是对单元格中已经存有信息的浏览和查看加以限制。对单元格的写保护有两种方法： A、对单元格的输入信息进行有效性检测。首先选定要进行有效性检测的单元格或单元格集合，然后从“数据”菜单中选择“有效性”选项，通过设定有效条件、显示信息和错误警告，控制输入单元格的信息要符合给定的条件。我们在下发的模板中经常采用。打开文件“001离休人员统筹项目待遇类别.xls”介绍设置最大值和最小值，控制输入的信息范围。具体方法在本节后介绍。B、保持单元格的锁定属性，以保护存入单元格的内容不能被改写（保护工作表时默认全部锁定单元格，因此允许修改的单元格要取消其锁定属性）。可分为以下步骤：（1）选定需要取消锁定的单元格或单元格集合； （2）从格式菜单中选择“单元格”选项； （3）在设置单元格格式的弹出菜单中选择“保护”标签，在弹出的窗口中，取消“锁定”； （4）从工具菜单中选择“保护”选项，设置保护密码，即完成了对单元格的锁定设置。 对单元格的读保护有三种方法： A、通过对单元格颜色的设置进行读保护。例如：将选定单元格或单元格集合的背景颜色与字体颜色同时设为白色，这样，从表面看起来单元格中好像是没有输入任何内容，用户无法直接读出单元格中所存储的信息。 在制作考试卷中常用。B、用其他画面覆盖在需要保护的单元格之上，遮住单元格的本来面目，以达到读保护目的。例如：使用绘图工具，画一不透明矩形覆盖在单元格之上，从格式菜单中选定矩形的“锁定”选项（默认就是选中），然后保护工作表，以保证矩形不能被随意移动。这样，用户所看到的只是矩形，而看不到单元格中所存储的内容。C、通过设置单元格的行高和列宽，隐藏选定的单元格，然后保护工作表，使用户不能直接访问被隐藏的单元格，从而起到读保护的作用。D、输入“1和2”以及求和公式，增加背景色。设置单元格格式，在“自定义”中输入“;”（三个英文状态下的分号），此时不能直接看到，但能够在编辑区显示。若设置单元格格式为“隐藏”，把隐藏钩上，再保护工作表，则不能看到任何数据和公式。实际上，将单元格格式设置为“隐藏”，再保护工作表，可以隐藏所有公式。这种方法是“最彻底”的隐藏。有效性的简单设置方法。为了控制数据的录入，可以设置数据有效性。包括输入数据的范围、输入提示等。A、新建工作簿，单击A1单元格，在“数据”菜单中选择“有效性”命令，在“允许”下拉框中有“整数”等8个选项，选择“整数”，输入最小值“1”和最大值“2”，按“确定”按钮。在单元格A1中输入1、2，可以正常操作；输入0.1后回车，出现“输入值非法”的提示，输入a、3也会出现提示。B、重新输入有效性条件。单击A1单元格，在“数据”菜单中选择“有效性”命令，在“允许”下拉框中选择“文本长度”，最小值和最大值全部设置为“5”，按“确定”按钮。在单元格A1中输入5个1或a，可以正常操作；输入4个1后回车，出现“输入值非法”的提示。C、单击A1单元格，在“数据”菜单中选择“有效性”命令，单击“输入信息”选项卡，在“标题”中输入“*的提示”，在“输入信息”中输入“辽宁省行业养老保险”。确定后单击A1单元格，会出现刚才设置的提示。这个功能可以取代批注。D、单击A1单元格，在“数据”菜单中选择“有效性”命令，单击“出错警告”选项卡，在“样式”中选择“停止”，在“标题”中输入“输入错误”，在“出错信息”中输入“辽宁省行业养老保险提示录入5！”。确定后单击A1单元格，输入123后回车，会出现错误提示，直至输入5位文本！E、单击A1单元格，在“数据”菜单中选择“有效性”命令，单击“出错警告”选项卡，在“样式”中选择“警告”，确定后单击A1单元格，输入123后回车，会出现错误提示，与第4条不同的是可以选择接受输入的内容。也可以将“样式”中修改为“信息”，可以选择接受输入的内容。提供下拉箭头式输入方法(见“002数据有效性.xls”)：这时无须键盘输入，只需点选。实现方法为：输入一列数据作为“下拉箭头”中的选项，在“数据”菜单中选择“有效性”命令，在“允许”下拉框中有“整数”等8个选项，选择“序列”，再选择这列数据即可。若想把数据方在其它工作表中，需要先定义名称(如aaa4)，然后输入“=aaa4”。也可以直接输入数据，用英文的“逗号”分开。3. 快速填充相同数据 如果需要在不同的单元格中输入大量相同的数据信息，那么不必逐个单元格一个一个地输入，可以通过下面的操作方法在多个相邻或不相邻的单元格中快速填充同一个数据，具体方法为： A、先给不连续的单元格增加背景色，同时选中需要填充数据的单元格。若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中； B、输入要填充的某个数据。按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据。 另外，连续两次在同一单元格内输入数据的方法：我们有时需要在某个单元格内连续输入多个数值，以查看引用此单元格的其他单元格的效果。但每次输入一个值后按回车键，活动单元格均默认下移一个单元格，非常不便。解决此问题的一般做法：单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格。此时，单元格周围将出现实线框。 4. 公式中的引用打开工作表 “003绝对引用.xls”。输入公式引用单元格时加了“$”符号就表示绝对引用，不加“$”符号的单元格引用就是相对引用，所引用的单元格随公式单元格的位置变化而变化（即：公式的位置变化，所引用单元格位置也在变化的是相对引用；随着公式位置的变化所引用单元格位置不变化的就是绝对引用）。公式中涉及缴费比例、收缴率、增长率时一般使用绝对引用。例如“C4”、“$C4”、“C$4”和“$C$4”之间的区别：在工作表 “003绝对引用.xls” C4、C5中的数据分别是60、50。如果在D4单元格中输入“=C4”，那么将D4向下拖动到D5时，D5中的内容就变成了50，里面的公式是“=C5”，将D4向右拖动到E4，E4中的内容是60，里面的公式变成了“=D4”。在D4单元格中输入“=$C4”（鼠标定位在编辑区，按F4键可以循环切换），将D4向右拖动到E4，E4中的公式还是“=$C4”，而向下拖动到D5时，D5中的公式就成了“=$C5”。即在$符号后的C不变。在D4单元格中输入“=C$4”，将D4向右拖动到E4时，E4中的公式变为“=D$4”，而将D4向下拖动到D5时，D5中的公式还是“=C$4”。 即在$符号后的4不变。在D4单元格中输入“=$C$4”，将D4向任何方向拖动，自动填充的公式都是“=$C$4”。前面带上了“$”号，在进行拖动时就不变。都带上了“$”，在拖动时两个位置都不变。5. 直接输入数字型字符串想直接输入数字型字符串，先输入英文单引号、再输入输入数字型字符串即可。如18位公民身份号码。但仅是显示数字，实际包括其他字符。因此输入公民身份号码的正确方法是：先将单元格的格式设置为“文本”，然后再输入。若已经设置其他属性再改为“文本”，有时不能用，可以在其他表中输入，再插入新列，拷贝过来即可。如果列窄，输入15位数字出现，则可以在“格式”菜单中选择“单元格”命令，在“数字”选项卡中选择“自定义”，在“类型”中选择“0”，单击“确定”按钮，数据显示正常。6. 文本变为数值如果数字在单元格中左对齐，而且修改过默认的对齐方式（默认方式为常规），即使改为右对齐，数字也被当作文本。从数据库中导出数据或我们下发的数据，经常是文本形式，如“004文本变数值.xls”，我们需要把文本变为数值，需进行如下操作：在空单元格键入 1，复制1，然后选中需要转换的区域，单击“编辑”菜单中的“选择性粘贴”命令，再单击“乘”，然后单击“确定”。利用此方法可以同时将数据乘、除、加、减，如将单位由元变为万元。介绍数值的特点：居右、选中后在状态栏中出现结果、可以用求和公式求和（所见非所得）。7. 输入公式向下粘贴公式：拖拉；复制，选择区域，粘贴；出现黑十字后双击，但要求相临的左侧单元格不为空；复制，选择区域，选择性粘贴，选中“公式”，不影响格式。8. 在单元格有数据区域间快速移动按Ctrl键和方向键，按End键激活END方式，然后使用方向键移动。打开文件“012单位基本信息.xls”进行演示。9. 格式刷打开“004文本变数值.xls”进行演示，包括颜色、框线、合并单元格等。选中要复制格式的单元格，单击工具栏上的“格式刷”按钮，然后在要复制到的单元格上单击，鼠标变成时，可以把格式复制过来。重复使用时，双击“格式刷”按钮，复制完毕后单击“格式刷”按钮或按Esc键。在实际工作中，这是一个常用的按钮，如用于修改框线、保留小数位数等。选中要删除格式的单元格，打开“编辑”菜单，单击“清除”项，选择子菜单中的“格式”命令单击，选中的单元格就变成默认的格式。10. 使用Excel状态栏查看数据右键点击状态栏，可以选择均值，计数，计数值（只计包含数值的单元格），最大值，最小值或求和。当在工作表中选定单元格后，在状态栏就会出现相应的提示。11. 行列转置选中需要转置的区域，复制，选择性粘贴，转置，行列即可转置。打开文件“013数据表转置.xls”进行演示。12. 制作表格打开文件“016制作报表.xls”。看输入报表的基本内容。提示若经常用到同一甲栏（文件中黄色部分），可以自己定义一个序列。方法为：在“工具”菜单中选择“选项”命令，选择“自定义序列”选项卡，在“输入序列（E）”框中，输入“铁路煤炭有色”，单击屏幕右下角的输入法按钮，在出现的对话框中选择英文，在“铁路”、“煤炭”、“有色”间输入英文逗号“,”，单击“添加”按钮后单击“确定”按钮。也可以在表中输入，再导入（可以演示一个，如“你我他”）。然后排版：A、将标题居中、字号设置为14或16号、宋体加粗或黑体。B、除标题外，报表的其他内容可以设置为仿宋或保留宋体。因此演示过程中不再设置字体。选中“制表单位”单元格，设置为11号或10号；选中同行的“单位”单元格，设置为11号或10号，右对齐。C、选中表体第一行，设置为最适合的列宽。若某项目（如“离退休人数”）太宽，可以通过“Alt+回车”调整。再选中本行，通过“单元格格式”设置水平和垂直居中。D、选中表体中“序号”行，设置居中。E、选中表体中甲栏，设置水平和垂直居中。F、选中“说明”单元格，设置为右对齐。技巧：说明后的内容从后续单元格开始输入，容易将各行排成左对齐。将说明设置为11号或10号。G、增加表格线。其他说明：表体一般为12或14号字，标题最大，其他内容的字号小于表体。要调某个工作表列宽或行高，选中后表格所在的行或列后，拖动某列或某行线，即可改变整个工作表的列宽或行高，形成全部一样的列宽或行高。演示设置页码（视图中的页眉页脚）、缩小到一页（页面设置），打开“015有色离退休人员基本信息.xls”，演示设置每页的标题。13. 数据比对“基础数据”表中是从数据库中导出同一单位的数据，第一部分是应付人数，第二部分是实付人数。但应付人数大于实付人数，要求找出未做实付的人数,或者已做实付的人数。（见“005查找不重复记录或重复记录.xls”）A、将实付人数的数据（G、H、I、J列，不包括标题行）复制到应付人数所在列（A、B、C、D列）的后面，其中用于比对的“个人社会保险编号”必须在同一列。B、在E2单元格中输入公式“=COUNTIF(A:A,A2)”。C、在E列中复制公式，删除实付人数后公式。选择E列，打开自动筛选，选择“1”即可列出未做实付的人数；选择“2”即可列出已做实付的人数。若想将未做实付人员信息复制到其他地方，按以下步骤操作：选中后直接复制。或者拖动鼠标，选择筛选出的数据；在“编辑”菜单中选择“定位”，在出现的选项卡中单击“定位条件按钮”，选择“可见单元格”，确定,粘贴即可。14. 删除内容相同的重复行用高级筛选,选择不重复的记录,将筛选结果通过定位选择复制到其他地方即可。如编号有重复，可以仅对编号列进行筛选，不管其他内容。见“006删除内容相同的重复行.xls”。同时符合两个条件进行筛选的方法见“007筛选同时符合姓名和编号的人的值.xls”。15. 单元格中数字的自定义格式选定包含数字的单元格，单击“格式”菜单中的“单元格”命令，选择“数字”选项卡，可以自己定义格式，具体见“008自定义格式类型.xls”。16. 所见非所得自定义格式和一些常规的格式设置在最后的打印输出方式表现得很有用。但有时，这个优点会害人，因为它只是一种格式，一点都不影响单元格本身贮存的数据。（在工具栏里“增加小数位数”和“减少小数位数”这两个按钮也是这样的）如果要用这些数据进行累加等进一步处理，而忘记了它实际的贮存内容，那么可能最后会为了有一分钱的出入，而找得满头包。（具体实例见“008自定义格式类型.xls”）因此可以用函数处理数据：四舍五入函数ROUND(),取整函数INT()等。17. 根据公民身份号码求出生时间具体见“009身份证号码.xls”。18. 函数的使用函数是对一个值或多个值进行运算，并返回一个值或多个值的预定义公式。从事社会保险的业务人员、统计人员和财务人员应熟悉几个基本函数的使用，掌握如何通过帮助学习新函数的用法。A、最简单和最常用的是自动求和函数。单击要求和的单元格，单击工具栏上的“输入公式”按钮，从工作表中选择要将数值相加的单元格，如果要求和单元格不连续，用Ctrl键配合鼠标进行选取，按回车键。修改求和范围：双击求和单元格，或选中求和单元格后在编辑区操作，如原公式为“=SUM(E6:E7)”，可以选中“E6:E7”，再用鼠标重新从E3拖动到E7，也可以直接将E6改为E3。B、求平均数的函数：选中要放置平均数的单元格，单击“粘贴函数”按钮，在“函数分类”下拉列表框中选择统计，在“函数名”下拉列表框中选择平均数函数“AVERAGE”项（注意：选择不同的函数名，下方会出现函数的作用，详细了解单击帮助按钮，这是非常重要的方法，强烈建议大家要熟悉），单击“确定”按钮。在出现的公式选项板中，单击Number1输入框的拾取按钮，从工作表中选择要计算平均数的单元格，按回车键或单击输入框中的“返回”按钮，单击“确定”按钮。C、非数值不计算：=IF(Isnumber(B1),A1/B1,)，Isnumber用于测试B1单元格的内容，如果为数值，则返回 TRUE，计算A1/B1，否则不显示任何内容。非数值相加时显示“非数值”：=IF(ISNUMBER(A4*B4),SUM(A4:B4),非数值)。（见“010部分EXCEL函数说明.xls”，这个文件将函数的说明进行了整理）19. 数据透视表对于业务和统计工作人员，数据透视表功能是必须熟练掌握的，也是Excel提供的最强大的数据分析工具。打开文件“015