excel实用技巧及函数的应用.ppt

Excel实用技巧及函数应用 内容 整理规范数据和表格 使用自定义数字美化表格 使用条件格式标识特殊数据 常用函数的应用 一、整理规范数据和表格 您是否碰到过这样的问题： - 怎样快速把很乱的表格整理成规范的表格？ - 如何将文本型数字转换为纯数字？ - 如何修改非法日期？ - 如何把从其他软件导入的数据进行分列？ - 如何去消合并单元格并填充数据？ - 等等 1、数据分列 我们一般要对软件中导出数据或从外部获取的数据进行对比分析，有时这些数据并非是我们想要的格式，因此第一步我们就要对其进行整理。 数据分列的具体操作： 选中要分列的区域——单击菜单栏“数据”——“分列…”——弹出“文本分列向导”对话框，按照对话框提示操作 注：选中的区域可以为多行，但不能为多列 2、修改非法日期 Excel日期格式的下限是1999/1/1，上限为9999/12/31 日期的正确输入方式有： 2010-7-30、2010/7/30、30-jul-2010、30/jul/2010 快捷修改非法日期的方式： 通过“分列”对非法日期进行修改，具体操作为： 选中非法日期区域——点击菜单栏“数据”——“分列…”——在弹出的“文本分列向导”（该向导对话框有3个步聚，在第3个步聚对话框中：常规栏选择非法日期列，再在列数据格式中选择日期选项——点击完成 3、将文本转换为数值 单元格的数字格式有： 数值、文本、日期、时间、百分比、分数… 常用到的为前面三种即数值、文本、日期 方式一、单元格中带有“智能标识”的，直接点击单元格左上角“智能标识”的下拉菜单——选择“转换为数字” 方式二、在任一空白单元格输入数字“1”——复制该单元格——选中要转变的区域——右键——在弹出的菜单 4、取消合并单元格并快速填充数据 合并单元格虽表格视觉上美观，但在数据分析时存在很多的隐患，里面包含了很多的空白单元格，不利于公式的计算和引用 案列快捷操作技巧如下： 取消合并单元格——按“F5”键，弹出“定位”选框——单击“定位条件”——选择“空值”——键盘输入“=当前单元格的上一格”（注：千万别用鼠标点击别的单元格）——按“ctrl+enter” 二、使用自定义数字美化表格 当表格的数字很大时，会使表格很凌乱，很难看 可以自定义数字格式，把数字显示为需要的格式，使表格一目了然，即美观、又实用 1、数字代码格式的组成 1）、 有四部分组成：正数；负数；零；文本（每一部分用“ ；”隔开 如格式：_-* #,##0_-;-* #,##0_-;_-* -_-;_-@_- 2、常用的格式条件设计 1）、缩小至1000倍：#,##0.00,（意思为数字带千分符号，保留两位小数，缩小至1000倍） 注：条件后的1个“，”表示缩小1000倍，2个“，”表示缩小1000000倍 2）、缩小至10000倍：0!.0, 3、自定义格式条件 如：[红色]× 0!.0,;○ 0!.0, 其表示意思为： 若数值为正数，显示红色，数字前加上符号×，并缩小至10000倍； 若数值为负数，数字前加上符号○ ，并缩小至10000倍； 三、使用条件格式标识特殊数据 员工的合同即将到期了，应收账款的合同也要到期了，如何在表上自动标识出那些将到期的员工和客户，以便提前作准备 库存在不断变化，怎样快速了解哪些物料的库存已经低于最低保险库存，便及时补充进货？ 条件格式的具体操作： 选中区域——单击菜单栏“格”式——“条件格式”——弹出“条件格式”对话框，在对话框中设定相应参数——确定 三、常用函数的应用 1、常用的函数 求和：sumproduct、sumif 查找：vlookup、index、match、indirect 日期：today、year、month、day、date、edate、weekday 文本：len、left、mid、right、find 逻辑：if、and、or 2、具体函数的应用 1）、sumif——条件加总 =sumif(判断区域绝对引用,判断值,加总区域) 2）、sumproduct——返回相应的数组或区域乘积的和 =sumproduct(数组1、数组2、数组3、…) 注：各数组的维数必须一样 例：= sumproduct(A2:B4, C2:D4) 两个数组的所有元素对应相