EXCEL实用教程.ppt

《EXCEL实用教程.ppt》由会员分享，可在线阅读，更多相关《EXCEL实用教程.ppt（56页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、EXCEL实用教程实用教程目录认识EXCEL工作界面，了解EXCEL术语工作表标签单元格区域编辑栏工具栏与EXCEL合作多年，它们的名字叫得上来吗？目录分页预览设置及调整可轻松调整每页打印内容有了分页预览，你的页面你做主我们是不是经常会有这样的苦恼：1、打印的EXCEL时，一页打不下，两页阅读时又不方便2、打印时，空白行或列也打印了，浪费纸啊页面设置让打印出来的文件更加美观对它进行设置，你再也不用为打印清单时，次页无标题行而苦恼了。排序功能可以将数据信息按指定列进行排序排序功能最多可以实现三个条件的排序，现在想知道谁第一是不是太容易了自动筛选把你想要的项目选出来面对几千条数据清单，想找到自已想

2、要的，自动筛选可以帮你搞定。如果还有更高的要求，想在自动筛选的下拉框中一次选多项条件，还可以使用高级筛选下拉框目录条件格式可以让我们单元格自动变色跟踪KPI考核指标时，我们经常会用颜色把机构指标不同的状态用颜色标出来，有了条件格式就可以轻松搞定，而且一劳永逸保护工作表，他人无法修改你的工作表对于重要的工作表，防止他人修改或自已无意操作导致工作表修改，可以设置工作表保护。保护工作表还可以有一部分区域免于保护我们经常设计一个表格让机构填报，希望机构在指定的地方填写，而其它部分希望机构不要调整，通过取消单元格格式保护锁定可以实现利用数据有效性，做个下拉框就这么简单只要你喜欢，可以选右边任一一种方法做

3、下拉框，只是第一种方法，需要手工输入，第二方法可以利用现成的数据，但下拉框的内容也随数据的改变而改变利用自定义工具栏，将常用工具放在工具栏上自定义工具栏操作实例：在工具栏上添加粘贴格式工具按住鼠标左键移至工具栏实例：将单元格合并及粘贴加入工具栏选择性粘贴选择性粘贴粘贴格式粘贴格式性粘数值性粘数值合并及居中合并及居中撤消合并撤消合并单元格单元格跨行合并跨行合并格式编辑移动或复制工作表，其实很简单选择编辑区时，千万记住SHIFT与CTRL的妙用鼠标左键单击A4单元格，然后按下Shift键，再用鼠标左键单击D11单元格。再试试快速定位的CTRL哦！1 1、工作簿的、工作簿的“批处理批处理”打开多个工

4、作簿：在“文件”菜单中选择“打开文件”，按住“Shift”键或“Ctrl”键并配以鼠标操作，在弹出的对话框文件列表中选择彼此相邻或不相邻的多个工作簿，然后按“确定”按钮，就可以一次打开多个工作簿。关闭所有打开工作簿而不退出Excel：按下“Shift”键并在“文件”菜单中选择“关闭所有文件”，如无修改则工作簿被立即关闭；如果有未保存的修改，系统询问是否要保存修改。砍柴累了，来磨磨刀，看看批处理（1/5）在Windows操作系统中，按住Shift键可以选择多个彼此相邻的选项，按住Ctrl键可以选择彼此不相邻的选项。2 2、工作表的、工作表的“批处理批处理”按住“Shift”键或“Ctrl”键并配

5、以鼠标操作，在工作簿底部选择多个彼此相邻或不相邻的工作表标签，然后就可以对其实行多方面的批量处理，仅举几例进行说明。在选中的工作表标签上按右键弹出快捷菜单，进行插入和删除多个工作表的操作。在选中的工作表标签上按右键弹出快捷菜单，进行插入和删除多个工作表的操作。在在“文件文件”菜单中选择菜单中选择“页面设置页面设置”，将选中的多个工作表设成相同的页面模式。，将选中的多个工作表设成相同的页面模式。通过通过“编辑编辑”菜单中的有关选项，在多个工作表范围内进行查找、替换、定位操作。菜单中的有关选项，在多个工作表范围内进行查找、替换、定位操作。通过通过“格式格式”菜单中的有关选项，将选中的多个工作表的行

6、、列、单元格设成相同的样式以及进菜单中的有关选项，将选中的多个工作表的行、列、单元格设成相同的样式以及进行一次性全部隐藏操作。行一次性全部隐藏操作。在在“工具工具”菜单中选择菜单中选择“选项选项”，在弹出的菜单中选择，在弹出的菜单中选择“视窗视窗”和和“编辑编辑”按钮，将选中的按钮，将选中的工作表设成相同的视窗样式和单元格编辑属性。工作表设成相同的视窗样式和单元格编辑属性。选中上述工作表集合中任何一个工作表，并在其上完成我们所需要的表格，则其它工作表在相同选中上述工作表集合中任何一个工作表，并在其上完成我们所需要的表格，则其它工作表在相同的位置也同时生成了格式完全相同的表格。的位置也同时生成了

7、格式完全相同的表格。砍柴累了，来磨磨刀，看看批处理（2/5）3 3、单元格的、单元格的“批处理批处理”“批量”调整列宽或行高。 对于相邻的多列，在其列标号处用鼠标选中整列，并将鼠标移至选中区域内，单击右键弹出快捷菜单，进行多列的插入与删除操作；将鼠标移至选中区域内任何一列的列标号处，当鼠标变成十字形时，按下左键并拖动，则将选中所有列的宽度调成相同的尺寸。此时双击左键，则将选中的所有列的宽度调成最合适的尺寸，以和每列中输入最多内容的单元格相匹配。行的操作与上述列的操作基本相同。 对于不相邻的多列，先按下“Ctrl”键并配合鼠标左键单点或按下左键并拖动的操作，选中整列，并将鼠标移至选中的区域内任何

8、一列的列标号处，当鼠标变成十字形时，按下左键并拖动，则将选中所有列的宽度调成相同尺寸，而此时双击左键，则将选中的所有列的宽度调成最合适的尺寸。行的操作与上述列的操作基本相同。砍柴累了，来磨磨刀，看看批处理（3/5）4 4、“批量批量”设定格式设定格式 通过按下“Ctrl”键并配合鼠标左键单击或按下左键并拖动的操作选择不连续单元格，或者通过按下鼠标左键并拖动选择连续单元格，将鼠标移至选中的任何区域内，单击右键弹出快捷菜单，选择其中的“单元格格式”一项进行“批量”设定单元格格式，或者利用“格式”工具条上的图标进行格式设定。砍柴累了，来磨磨刀，看看批处理（4/5）5 5、“批量批量”求和求和 在Ex

9、cel上工作时，对数字求和是经常遇到的操作，除传统的输入求和公式并复制外，对于连续区域求和可以采取如下方法：假定求和的连续区域为mn的矩阵型，并且此区域的右边一列和下面一行为空白，用鼠标将此区域选中并包含其右边一列或下面一行，也可以同时两者均选中，单击“常用”工具条上的“”图标，则在选中区域的右边一列或下面一行自动生成了求和公式，并且系统能自动识别选中区域中的非数值型单元格，求和公式不会产生错误。砍柴累了，来磨磨刀，看看批处理（5/5）长号码录入-保单号码的输入问题：有时我们在向单元格中输入保单号或比较长的数字时，单元格有时会自动的把数字按科学计数法表示，如1.23457E+151.23457

10、E+15。 解决方法主要是将数字转为文本解决方法主要是将数字转为文本 解决方法一：在输入数字时在数字前加一个单引号，如：1234567890123，Excel会把前面加单引号的数字串按文本方式来处理。 解决方法二： 先将单元格设为文本格式，然后再略多输入数字。 解决方法三：使用函数 TEXTTEXT(valuevalue,format_textformat_text),value要转化为文本的单元格或数字，format_text为转化为文本的格式如：”0000000”。目录有钱能使鬼推磨，给“$”的就不动了当把一个含有单元格地址的公式拷贝到一个新的位置，公式当把一个含有单元格地址的公式拷贝到一

11、个新的位置，公式中的单元格地址会随着改变。如让你的中的单元格地址会随着改变。如让你的EXCELEXCEL中单元格地址按你中单元格地址按你的需要改变或不改变？的需要改变或不改变？你可以在列号或行号前添加符号你可以在列号或行号前添加符号 “$”$”来冻结单元格地址，使之在拷贝时来冻结单元格地址，使之在拷贝时保持固定不变。保持固定不变。哈哈！高手是这样的！能看懂公式的帮助，就又是一种境界了 查看公式提示的方法：查看公式提示的方法： 在输入公式时按下工具栏上的“= =”按钮。 office2003在手工书写公式时,会自动显示公式提示区单击其它所需的函数请使用EXCEL的帮助其实看帮助没有我们想象的那么

12、难，还有实例呢VLOOKUP的帮助，有没有晕掉，但它最实用用途用途：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首行时，可以使用函数行中指定列处的数值。当比较值位于数据表首行时，可以使用函数HLOOKUPHLOOKUP代代替函数替函数VLOOKUPVLOOKUP。语法语法：VLOOKUP(lookup_valueVLOOKUP(lookup_value，table_arraytable_array，col_index_numcol_index_num，range_lookup

13、)range_lookup)参数参数：Lookup_valueLookup_value为需要在数据表第一列中查找的数值，它可以是数值、引为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。用或文字串。Table_arrayTable_array为需要在其中查找数据的数据表，可以使用对区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。或区域名称的引用。Col_index_numCol_index_num为为table_arraytable_array中待返回的匹配值的列序号。中待返回的匹配值的列序号。Col_index_numCol_index_num为为1 1时，返回

14、时，返回table_arraytable_array第一列中的数值；第一列中的数值；col_index_numcol_index_num为为2 2，返回返回table_arraytable_array第二列中的数值，以此类推。第二列中的数值，以此类推。Range_lookupRange_lookup为一逻辑值，指为一逻辑值，指明函数明函数VLOOKUPVLOOKUP返回时是精确匹配还是近似匹配。如果为返回时是精确匹配还是近似匹配。如果为TRUETRUE或省略，则返回或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于近似匹配值，也就是说，如果找不到精确匹配值，则返回小于look

15、up_valuelookup_value的的最大数值；如果最大数值；如果range_valuerange_value为为FALSEFALSE，函数，函数VLOOKUPVLOOKUP将返回精确匹配值。如果将返回精确匹配值。如果找不到，则返回错误值找不到，则返回错误值#N/A#N/A。实例：实例：如果如果A1=23A1=23、A2=45A2=45、A3=50A3=50、A4=65A4=65，则公式，则公式“=VLOOKUP(23=VLOOKUP(23，A1:A4A1:A4，3 3，TRUE)”TRUE)”返回返回5050。 MATCH 无论你在哪，我都能找到你的位置用途：用途：返回在指定方式下与指

16、定数值匹配的数组中元素的相应位置。如返回在指定方式下与指定数值匹配的数组中元素的相应位置。如果需要果需要找出匹配元素的位置找出匹配元素的位置而不是匹配元素本身，则应该使用而不是匹配元素本身，则应该使用MATCHMATCH函数。函数。语法：语法：MATCH(lookup_valueMATCH(lookup_value，lookup_arraylookup_array，match_type)match_type)。参数参数：Lookup_valueLookup_value为需要在数据表中查找的数值，它可以是数值为需要在数据表中查找的数值，它可以是数值( (或或数字、文本或逻辑值数字、文本或逻辑值)

17、 )、对数字、文本或逻辑值的单元格引用。、对数字、文本或逻辑值的单元格引用。Lookup_arrayLookup_array是可能包含所要查找的数值的连续单元格区域，是可能包含所要查找的数值的连续单元格区域，Lookup_arrayLookup_array可以是数组或数组引用；可以是数组或数组引用；Match_typeMatch_type为数字为数字-1-1、0 0或或1 1，它，它说明说明ExcelExcel如何在如何在lookup_arraylookup_array中查找中查找lookup_valuelookup_value。如果。如果match_typematch_type为为1 1，函

18、数，函数MATCHMATCH查找查找小于或等于小于或等于lookup_valuelookup_value的最大数值。如果的最大数值。如果match_typematch_type为为0 0，函数，函数MATCHMATCH查找查找等于等于lookup_valuelookup_value的第一个数值。如果的第一个数值。如果match_typematch_type为为-1-1，函数，函数MATCHMATCH查找查找大于或等于大于或等于lookup_valuelookup_value的最小数值。的最小数值。注意：注意：MATCHMATCH函数返回函数返回lookup_arraylookup_array中

19、目标值的位置，而不是数值本身。中目标值的位置，而不是数值本身。如果如果match_typematch_type为为0 0且且lookup_valuelookup_value为文本，为文本，lookup_valuelookup_value可以包含通可以包含通配符配符(“(“* *”和和“?”)?”)。星号可以匹配任何字符序列，问号可以匹配单个。星号可以匹配任何字符序列，问号可以匹配单个字符。字符。实例实例：如果：如果A1=“1A1=“1月月“，A2= “2A2= “2月月“ “ ，A3= “3A3= “3月月“ “ ， A4= “4A4= “4月月“ “ ，则公式，则公式=MATCH(”3=MA

20、TCH(”3月月“，A1:A5A1:A5，0) 0) 返回返回3 3。IF让你更加逻辑，更加牛气用途：用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。从而执行数值或公式的条件检测任务。语法：语法：IF(logical_testIF(logical_test，value_if_truevalue_if_true，value_if_false)value_if_false)。参数参数：Logical_testLogical_test计算结果为计算结果为TRUETRUE或或FALSEFALSE

21、的任何数值或表达式；的任何数值或表达式；Value_if_trueValue_if_true是是Logical_testLogical_test为为TRUETRUE时函数的返回值，时函数的返回值，Value_if_falseValue_if_false是是Logical_testLogical_test为为FALSEFALSE时函数的返回值。时函数的返回值。 Value_if_trueValue_if_true、Value_if_falseValue_if_false也可以是一个表达式。也可以是一个表达式。实例实例：判断应收率的状态：判断应收率的状态=IF(C2=0.06=IF(C2=0.06

22、，“优秀优秀”，IF(C2=0.08IF(C2=0.08，“健康健康”，IF(C2=0.10IF(C2=0.10，”异常异常“，”危险危险“)”)”，其中第二个，其中第二个IFIF语语句同时也是第一个句同时也是第一个IFIF语句的参数。同样，第三个语句的参数。同样，第三个IFIF语句是第二个语句是第二个IFIF语句的语句的参数，以此类推。结果是：若应收率小于等于参数，以此类推。结果是：若应收率小于等于6%6%时，为优秀；应收率小于时，为优秀；应收率小于等于等于8%8%而在大于而在大于6%6%时，结果为健康；若应收率小于等于时，结果为健康；若应收率小于等于10%10%而大于而大于8%8%时，时，

23、结果为异常；若应收率大于结果为异常；若应收率大于10%10%，结果为危险。，结果为危险。RANK电脑排名，错不了用途：用途：返回一个数值在一组数值中的排位。数值的排位是与数据清单中返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他数值的相对大小其他数值的相对大小语法语法：RANK(number,ref,order)RANK(number,ref,order)NumberNumber 为需要找到排位的数字。为需要找到排位的数字。RefRef 为包含一组数字的数组或引用。为包含一组数字的数组或引用。Ref Ref 中的非数值型参数将被忽略。中的非数值型参数将被忽略。OrderOrder

24、为一数字，指明排位的方式。如果为一数字，指明排位的方式。如果 order order 为为 0 0 或省略，按降序或省略，按降序排列的数据清单进行排位。如果排列的数据清单进行排位。如果order order 不为零，按升序排列的数据清单不为零，按升序排列的数据清单进行排位。进行排位。 说明说明：函数函数 RANK RANK 对重复数的排位相同。但重复数的存在将影响后续数对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在一列整数里，如果整数值的排位。例如，在一列整数里，如果整数 10 10 出现两次，其排位为出现两次，其排位为 5 5，则，则 11 11 的排位为的排位为 7 7（

25、没有排位为（没有排位为 6 6 的数值）。的数值）。示例示例：如果如果 A1:A5 A1:A5 中分别含有数字中分别含有数字 7 7、3.53.5、3.53.5、1 1 和和 2 2，则：，则：RANK(A2,A1:A5,1) RANK(A2,A1:A5,1) 等于等于 3 3RANK(A1,A1:A5,1) RANK(A1,A1:A5,1) 等于等于 5 5SUMIF的掌握，说明你已经高了一个层级用途用途：根据指定条件对若干单元格、区域或引用求和。：根据指定条件对若干单元格、区域或引用求和。语法语法：SUMIF(rangeSUMIF(range，criteriacriteria，sum_ra

26、nge)sum_range)参数参数：RangeRange为用于条件判断的单元格区域，为用于条件判断的单元格区域，CriteriaCriteria是由数字、逻辑表是由数字、逻辑表达式等组成的判定条件，达式等组成的判定条件，Sum_rangeSum_range为需要求和的单元格、区域或引用。为需要求和的单元格、区域或引用。实例实例：统计应收清单中：统计应收清单中 “B02 B02 出口运输险出口运输险”应收保费折合人民币的总数。应收保费折合人民币的总数。条件判断的区域需要求和的区域判定条件YEAR、MONTH、TODAY现转年月日用途：用途：返回以系列数表示的日期中的月份。月份是介于返回以系列数

27、表示的日期中的月份。月份是介于 1 1（一月）（一月）和和 1212（十二月）之间的整数。（十二月）之间的整数。语法：语法：MONTH(serial_number)MONTH(serial_number)Serial_numberSerial_number 表示一个日期值，其中包含着要查找的月份。示表示一个日期值，其中包含着要查找的月份。示示例：示例：= =MONTH(“2004/04/01”) MONTH(“2004/04/01”) 返回返回4 4= =MONTH(“2004/04”) MONTH(“2004/04”) 返回返回4 4这个函数在我们计算保费计划时间进度达成率时很有用。这个函数

28、在我们计算保费计划时间进度达成率时很有用。LEFTRIGHTMID 你也用得着 用途用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。用于双字节字符。语法语法：LEFT(textLEFT(text，num_chars)num_chars)或或LEFTB(textLEFTB(text，num_bytes)num_bytes)。参数参数：TextText是包含要提取字符的文本串；是包含要提取字符的文本串；Num_charsNum_chars指定函数要提取的指定函数要提取的字符数，它必须大于或等于字符数，它必须大于

29、或等于0 0。Num_bytesNum_bytes按字节数指定由按字节数指定由LEFTBLEFTB提取的提取的字符数。字符数。实例实例：如果：如果A1=A1=电脑爱好者，则电脑爱好者，则LEFT(A1LEFT(A1，2)2)返回返回“电脑电脑”，LEFTB(A1LEFTB(A1，2)2)返回返回“电电”。 RIGHTRIGHT与与MIDMID与与LEFTLEFT类似，类似， RIGHTRIGHT返回文本串中的最后一个或后几个字符。返回文本串中的最后一个或后几个字符。 MIDMID返回文本串中的中间一个或几个字符。返回文本串中的中间一个或几个字符。到用时方恨公式掌握的少（ VALUE）用途用途：

30、将表示数字的文字串转换成数字。：将表示数字的文字串转换成数字。语法语法：VALUE(text)VALUE(text)。参数参数：TextText为带引号的文本，或对需要进行文本转换的单元格的引为带引号的文本，或对需要进行文本转换的单元格的引用。它可以是用。它可以是ExcelExcel可以识别的任意常数、日期或时间格式。如果可以识别的任意常数、日期或时间格式。如果TextText不属于上述格式，则不属于上述格式，则VALUEVALUE函数返回错误值函数返回错误值#VALUE!#VALUE!。注意注意：通常不需要在公式中使用：通常不需要在公式中使用VALUEVALUE函数，函数，ExcelExce

31、l可以在需要时自可以在需要时自动进行转换。动进行转换。VALUEVALUE函数主要用于与其他电子表格程序兼容。函数主要用于与其他电子表格程序兼容。实例实例：公式：公式“=VALUE(”=VALUE(”￥1 1，000“)”000“)”返回返回10001000；=VALUE(“2005/10/10”)-VALUE(“2005/8/10”)=VALUE(“2005/10/10”)-VALUE(“2005/8/10”)返回返回6161，即两者，即两者时间间隔为时间间隔为6161天。天。到用时方恨公式掌握的少（TEXT ）用途用途：将数值转换为按指定数字格式表示的文本。：将数值转换为按指定数字格式表示

32、的文本。语法语法：TEXT(valueTEXT(value，format_text)format_text)。参数参数：ValueValue是数值、计算结果是数值的公式、或对数值单元格的引是数值、计算结果是数值的公式、或对数值单元格的引用；用；Format_textFormat_text是所要选用的文本型数字格式，即是所要选用的文本型数字格式，即“单元格格式单元格格式”对话框对话框“数字数字”选项卡的选项卡的“分类分类”列表框中显示的格式，它不能包列表框中显示的格式，它不能包含星号含星号“* *”。注意注意：使用：使用“单元格格式单元格格式”对话框的对话框的“数字数字”选项卡设置单元格格选项卡

33、设置单元格格式，只会改变单元格的格式而不会影响其中的数值。使用函数式，只会改变单元格的格式而不会影响其中的数值。使用函数TEXTTEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。算。实例实例：如果：如果A1 = A1 = 1.23E+081.23E+08，则公式，则公式“=TEXT(A1=TEXT(A1，”0.00“)”0.00“)”返回返回123456789.00123456789.00。其它函数留给你们自已去查帮助吧ROW(reference)ROW(reference)COLUMN(reference)COLUMN(reference)ROUND(reference,int)ROUND(reference,int)目录图表的使用图表的使用图表的使用图表的使用图表的初稿图表的使用点图表区单击右键图表区单击右键绘图区单击右键绘图区单击右键图表的使用图表的使用数据区单击右键数据区单击右键图表的使用图表的使用最终图形示例最终图形示例目录目录数据表透视数据表透视数据表透视数据表透视数据表透视按住鼠标按住鼠标左键拖动左键拖动数据表透视数据透视结果再也不用这么辛苦了谢谢！谢 谢