55excel

2008年7月计划调度部核算组培训材料----关于EXCEL和ACCESS的应用培训时间：2008年7月30日培训内容第一部分----EXCEL中常用函数第一节----数据汇总与核对第二节----筛选第三节----EXCEL中常用的函数第二部分----ACCESS中常用查询的设计第三部分----VISIO的操作第一部分----EXCEL中常用函数Excel的优点：A、Excel是个人电脑普及以来用途最广泛的办公软件之一，也是MicrosoftWindows平台下最成功的应用软件之一。说它是普通的软件可能已经不足以形容它的威力，事实上，在很多公司，Excel已经完全成为了一种生产工具，在各个部门的核心工作中发挥着重要的作用。无论用户身处哪个行业、所在公司有没有实施信息系统，只要需要和数据打交道，Excel几乎是不二的选择。B、在电子表格软件领域，Excel软件唯一的竞争对手就是自己。基于这样的绝对优势地位，Excel已经成为事实上的行业标准。因此，您大可不必花时间去关注别的电子表格软件。即使需要，以Excel的功底去学习其他同类软件，学习成本会非常低。如此，学习Excel的综合优势就很明显了。C、Excel一向具有良好的向下兼容特性，特别是自从进入97版本的成熟期以来，历经9年时间中3个版本的升级，每一次升级都能带来新的功能，但却几乎可以不费力的掌握这些新功能并同时沿用原有的功能。而且，这4个版本的功能有99%的部分都是通用的，文件格式也可以完全通用。所以，无论你现在正在使用哪个版本的Excel，都不必担心现有的知识会很快被淘汰掉。从这个角度上讲，把时间投资在学习Excel上，是相当保值的。第一节----数据汇总与核对一、分类汇总的应用二、数据透视表的应用一、分类汇总的应用1、分类汇总的基本功能1）按字段属性排序，求和，求平均，求个数等2）局限性：最多按三个字段排序2、分类汇总的操作方法1）选中需要汇总的任意一个单元格，按一下“常用”工具栏上的“升序排序”或“降序排序”按钮，对数据进行一下排序。注意：使用“分类汇总”功能时，一定要按分类对象进行排序！2）执行“数据→分类汇总”命令，打开“分类汇总”对话框。3）将“分类字段”设置为“大区”；“汇总方式”设置为“求和”；“选定汇总项”为“省区”和“品类”。最后，确定返回。注意：可以在分类汇总的基础上，在进行汇总，只要取消“替换当前汇总”就OK！第一节----数据汇总与核对二、数据透视表的应用（一）1、数据透视表的基本功能1）按任意字段进行求和、求平均、求个数等。2）灵活性（字段可任意选择）3）能够将筛选、排序和分类汇总等操作依次完成，并自动生成汇总表格，同时还可以美化汇总表格。注意：有数据的必须有标题，有标题的必须有数据，同时数据项为合法数据项。第一节----数据汇总与核对2、数据透视表的操作方法1）在Excel的菜单栏上选择“数据”*“数据透视表和数据透视图”，系统弹出“数据透视表和数据透视图向导--3步骤之1”对话框。在此对话框的“指定待分析数据的数据源类型”项目中，选择“MicrosoftExcel数据列表或数据库”选项，在“所需创建的报表类型”项目中选择“数据透视表”选项，然后单击“下一步”按钮。2）进入“数据透视表和数据透视图向导--3步骤之2”对话框，在“键入或选定要建立数据透视表的数据源区域”项目中，设置选定区域为“$？$？:$？$？”，然后单击“下一步”按钮。3）进入“数据透视表和数据透视图向导--3步骤之3”对话框，在此对话框中的“数据透视表显示位置”项目中，选中“新建工作表”选项。然后单击“布局”按钮，在弹出的“数据透视表和数据透视图向导--布局”对话框中定义数据透视表布局，步骤为将“新大区”字段拖入“页”栏目;将“省区”字段拖入“行”栏目;将“本日销售额等”字段拖入“数据”栏目。4）在数据透视表中打开“新大区”下拉列表，选中“华北”选项，单击“确定”按钮后，系统即计算产生华北各省的日销售额列表。采用同样方法可以获得其他大区其他省的日销售额表。二、数据透视表的应用（二）第一节----数据汇总与核对第二节----筛选1、自动筛选---简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。注意：在筛选数据时，充分利用自定义的各类条件，大于、小于、包含等，便捷的达到目的。2、高级筛选---较复杂的条件筛选，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来。也可以在新的位置显示筛选结果，不符合条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的对比了。注意：条件区域中，条件放在同一行表示“与”的关系，条件不在同一行表示“或”的关系。在实际操作中解决数据筛选问题时，只要我们把握了问题的关键，选用简便、正确的操作方法，问题都能迎刃而解。第三节----EXCEL中常用的函数一、文本型数据的函数操作二、日期与时间型数据的函数操作三、求和和计数的函数操作四、查询的函数操作五、其他常用的函数操作一、文本型数据的函数操作（一）1、如何确定两单元格是否相同？1）用运算符“=”判断；不区分大小写返回结果：“TURE”、“FALSE”2）用EXACT函数比较；区分大小写语法：exact（text1,text2）返回的结果：“TURE”、“FALSE”3）用IF函数判断，区分大小写语法：IF(logical_test,value_if_true,value_if_false)返回的结果：“TURE”、“FALSE”2、如何连接多个字符串？1）用运算符“&”连接；2）用CONCATENATE函数连接；语法：concatenate（text1,text2……text30）第三节----EXCEL中常用的函数3、如何转换文本大小写？1）LOWER：所有大写转换为小写；语法：lower（text）2）UPPER：所有小写转换为大写；语法：upper（text）3）PROPER：首字母及任何非字母后的首字段转换大写，其他字母转换为小写语法：proper（text）4、如何取出单元格中的部分字符？1）Left：从左边开始截取指定数目的字符；语法：left（text,num_chars）2）Right：从右边开始截取指定数目的字符；语法：right（text,num_chars）3）Mid：从文本指定位置开始截取指定数目的字符语法：mid（text,start_num,num_chars）第三节----EXCEL中常用的函数一、文本型数据的函数操作（二）二、对日期与时间型数据的函数操作（一）1、显示时间1）Now：显示当前系统的日期和时间;如何只显示当前时间？=now()-today()2）Ctrl+;：插入当前系统的日期Ctrl+Shift+;:插入当前系统的时间2、显示日期1）Today：显示当前系统的日期;2）Date：显示指定数值的日期；3）Year：得到日期的年数4）Month：得到日期的月数5）Day：得到日期的天数第三节----EXCEL中常用的函数3、如何确定指定日期星期几Weekday函数1）语法：weekday(serial_number,return_type)2）return_type类型1或省略：返回数字1（周日）到数字7（周六）2：返回数字1（周一）到数字7（周日）3：返回数字0（周一）到数字6（周日）第三节----EXCEL中常用的函数二、对日期与时间型数据的函数操作（二）三、求和和计数的函数操作（一）1、常用的求和函数1）Sum：进行基本数值求和;语法：sum（number1,number2…）2）Sumif：根据条件求和；语法：sumif（range,criteria,sum_range…）2、分类汇总求和（计算）Subtotal函数1）语法：subtotal(function_num,ref1,ref2…)2）Function_num的常用类型1求平均(average)4求最大值(max)2计数(count)5求最小值(min)3计数(counta)9求和(sum)第三节----EXCEL中常用的函数三、求和和计数的函数操作（二）3、常用的计数函数1）Count：统计时不包括空白、文本、逻辑值和错误;语法：count（value1,value2……）2）Counta：统计时仅不包括空白单元格；语法：counta（value1,value2……）4、指定条件的计数函数Countif语法：countif（range,criteria）第三节----EXCEL中常用的函数第三节----EXCEL中常用的函数四、查询的函数操作1、常用的查找函数-Vlookup函数1）当比较值位于要查找的数据左边的一列时，请使用函数VLOOKUP，其语法为：vlookup(lookup_value,table_array,col_index_num,range_lookup)2）range_lookup的类型True或省略返回近似值False返回精确值2、查询函数-Hlookup函数当比较值位于数据表的首行，并且要查找下面给定行中的数据时，请使用函数HLOOKUP，其语法为：hlookup(lookup_value,table_array,row_index_num,range_lookup)五、其他常用的函数操作（一）1、在客户对帐单里，每个客户前插入相同的行。按客户排序—IF函数—自动下排行—筛选2、在每一行插入相同的行。自动下排行—复制行号—排序—筛选—替换3、贷款偿还计算函数PMT。语法：PMT（rate，nper，pv，fv，type）参数：如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷款的重要依据，ExcelXP提供的PMT函数是完成这一任务的好工具。语法中的rate是贷款利率；nper为贷款偿还期限；pv是贷款本金；fv为最后一次付款后剩余的贷款金额，如果省略fv，则认为它的值为零；type为0或1，用来指定付款时间是在月初还是月末。如果省略type，则假设其值为零。应用实例：购房还款金额假如你为购房贷款十万元，如果年利率为7%，每月末还款。采用十年还清方式时，月还款额计算公式为“=PMT（7%/12，120，-100000）”。其结果为￥-1，161.08，就是你每月须偿还贷款1161.08元。第三节----EXCEL中常用的函数五、其他常用的函数操作（二）4、零存整取收益函数FV语法：FV（rate，nper，pmt，pv，type）。参数：rate为存款利率；nper为总的存款时间，对于三年期零存整取存款来说共有3*12=36个月；Pmt为各期所应支付的金额，其数值在整个年金期间保持不变；Pv为现值，即从该项投资开始计算时已经入账的款项，或一系列未来付款的当前值的累积和；type为数字0或1，它指定存款时间是月初还是月末。应用实例：零存整取收益函数FV假如你每月初向银行存入现金500元，如果年利2.15%（按月计息，即月息2.15%/12）。如果你想知道5年后的存款总额是多少，可以使用公式“=FV（2.15%/12，60，-500，0，1）”计算，其结果为￥31，698.67。式中的2.15%/12为月息；60为总的付款时间，在按月储蓄的情况下为储蓄月份的总和；-500为每月支出的储蓄金额（-表示支出）；0表示储蓄开始时账户上的金额，如果账户上没有一分钱，则为0否则应该输入基础金额；1表示存款时间是月初还是月末，1为月初0或忽略为月末。上述计算结果包括本金和利息，但不包括利息税等其他费用。第三节----EXCEL中常用的函数第二部分----ACCESS中常用查询的设计（一）1、通过简单向导设计查询简单查询向导只能生成一些简单的查询，在所查询的表中只能设计简单计算。2、通过设计视图进行较复杂的查询设计视图能充分发挥数据库函数的强大功能，完成复杂的查询要求。介绍几个常用的数据库日期函数全英文状态输入日期段的输入：between6-1and6-30显示系统日期的前一天：DateAdd(y,-1,