ExcelSkill解析.ppt

Excel Skill Vincent Shen 常用功能快捷键 Ctrl+鼠标滚轮 F4 Ctrl+Shift+Enter 数据行列转换 需要将一列数据变成一行数据 相对引用与绝对引用 相对引用 “=A1” 绝对引用 “=$A$1” 混合引用 “=$A1”, “=A$1” 区别 快速切换 F4 名称命名与引用 对某一单元格或者区域命名, 并直接引用 需要查找的话,可以直接在名称栏输入 优点在于在庞大数据表内可以按喜好自己划分数据区域并快速在公式中引用 引用函数 行:HLOOKUP 列:VLOOKUP 限制:不同工作表 HLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value 表示要查找的值，它必须位于自定义查找区域的最左列。Lookup_value 可以为数值、引用或文字串。Table_array 查找的区域，用于查找数据的区域，上面的查找值必须位于这个区域的最左列。可以使用对区域或区域名称的引用。 Row_index_num 为 table_array 中待返回的匹配值的行序号。Row_index_num 为 1 时，返回 table_array 第一行的数值，row_index_num 为 2 时，返回 table_array 第二行的数值，以此类推。Col_index_num 为相对列号。最左列为1，其右边一列为2，依此类推.Range_lookup 为一逻辑值，指明函数 HLOOKUP 查找时是精确匹配，还是近似匹配 数组函数 数组进阶 现在要从几百条记录中,计算符合条件的总个数 需要计算满足ID=1008,Location=C的总数 突出显示符合条件的数据 如何突出显示符合定义条件的数据 下拉列表快速输入数据 当想要指定输入内容时,一般会设计成下拉列表供选择 时间计算 单元格日期直接计算结果单位为天 现要计算并得到如下结果 数据有效性的其他功能 限定单元格数据格式 限定单元格数据范围 个性化单元格输入提示信息 个性化单元格错误提示信息 限定单元格中英文切换 到期判断 对于工具,设备等有实效性的数据维护,当数据量很大的时候, 如何自动提醒过期的或将要到期的 Today() 作用：返回当前日期 可建立IF判断, If(A1=(Today()-X), “还X 天过期”, “ ”) 可以通过筛选选择 还可以自己设定条件格式，对过期的highlight 出来 缺点：无自动跳出提醒，无法分断判断 高级筛选 需要显示或复制满足多个筛选要求的数据，可以使用高级筛选 将条件列标题和条件（可多项）输入到某个单元格区域中 数据-筛选-高级筛选 注意：此时，系统自动将数据区域填入到“列表区域”右侧的方框中，如果区域不正确，请重新填写。 按“条件区域”右侧的红色按钮，选中条件单元格区域 可复制，可显示，显示的话隐藏不符合要求的数据 取消，数据-筛选-全部显示 频率分布图 如何对一组数据进行分析并查看简单的正台分布 命名这组数据为Data, 方便公式输入 计算平均值D1=AVERAGE(data), 标准差D2=STDEV(data) 输入分段区间C5:C17 选择D5:D17，输入=frequency(data,C5:C17)并按CTRL+SHIFT+ENTER输入该公式 计算总频率数，D18=sum(d5:d17) 计算期望频率，E5=$d$18*normdist(c5,mean,stdev,true), E6=$d$18*(normdist(c6,mean,stdev,true)-normdist(c5,mean,stdev,true)), 使用向下填充直到E16,在E17=$d$18*(1-normdist(c16,mean,stdev,true)) 计算期望频率的总和,D18=sum(e5:e17) 制作频率分布图：选中D5:E17,做一个线-柱图，单击系列标签，在X轴中选中C5:C17,等图形完成后，选中实际数据线，单击鼠标右键，在弹出的右键菜单中选中数据系列格式-选项，设置分类间距为0；并设置曲线为平滑曲线。 数据透视表和图 对大量同类型的数据，需要识别其内在规律和趋势，就可以使用数据透视表和图 数据按标准的范畴分类 数据-数据透视表/图，选择类型，选择区域，下一步，选项，布局设置。这里可以随意挑选字段，以后可以更改的。 如果需要对数据进行不断添加，并且是在同一张表上的话，可以选择整张表，这样每次更新后只需“刷新数据”即可 例：N:\(6)Share Folder\Quality Department\Customer DMR\DMR,8D RCCA