Excel培训资料.ppt

原理篇基础篇函数篇图表篇 目录 我们关注什么 如何管理你的excel 1 选择合适的数据源 2 拆分你的基础数据表 计算表 3 为每个分析要素设计单独的列 并最少设定一个主键列 4 不要轻易合并数据 基础数据表格单元切不可合并 5 允许重复的行 不允许重复的列 6 收集数据用一维表格 呈现数据用多维表格 7 千万不要乱涂颜色 原理篇基础篇函数篇图表篇 目录 excel的界面 excel的局限性 行数限制 65536行列数限制 256列列宽限制 255个字符单元格限制 1024个字符工作簿限制 256个单元格样式 4000个工作簿的颜色数 56种 单元格的结构 41255 日期 2012 12 12 单元格的引用 相对引用 a2b2 d4相对地址在使用时 会随着引用单元格的变化而变化 绝对引用 a 10 b8绝对地址在行标或者列标上添加 符号 绝对地址表示引用时 被绝对限制的位置不随着公式位置的变化而变化相对地址与绝对地址快速转换键 f4名称引用 名称引用是将某一单元格或区域用特定的名称来表示 名称引用的名称不能与现有单元格地址重复 并且不能以数字和特殊字母开头 操作 插入 名称 定义 excel中的错误值 div 0 不能除以0 为了避免这样的错误可使用if函数 n a数据无效或没有定义 name 包含了没有定义的范围或单元格 null 试图引用两个没有重叠区域的2个单元格区域 num 无效参数 或者返回的数值超出excel定义范围 ref 无效的单元格引用 value 数据类型错误 或在输入函数时出现输入错误 数据的有效性 数据的有效性验证是统计分析的基本保障 原理篇基础篇函数篇图表篇 目录 运算符 函数和公式的定义 函数 excel内部预先定义并按照特定的顺序 结构来执行计算 分析等数据处理任务的功能模块 公式 用户自行设计并结合常量数据 单元格引用 运算符号等元素进行的数据处理和计算的算式 例如 公式 a1 a2 a3 a4 a5 5函数 average a1 a5 函数的易失性 每次打开工作表 都需要重新计算函数的返回值 文本 数值 逻辑 日期 文本函数 将多个文本连接生成新的文本字符串 可以利用concatenate函数进行处理 concatenate aaa bbb aaabbb concatenate的参数不能多于30个 可以用 来处理 aaa bbb aaabbb concatenate 字符串连接函数 exact 文本比较函数 h9 我的数字 i9 我的数字 exact h9 i9 返回false 文本函数 大小写转换 lower 转换成小写upper 转换成大写proper 转换成首字母大写全角半角转换 asc 将所有字符转换为半角字符widchar 将所有字符转换为全角字符 常用提取字符的函数left 左侧提取mid 从字符串中间任意位置提取字符串right 右侧提取指定长度的字符串len 取字符串的长度f12 我的数字 left f12 2 返回 我的 文本查找 替换 find 用于定位某一字符串在指定的字符串中的起始位置search 用于定位某一字符串在指定的字符串中的起始位置substitute 用于替换指定的字符串 数学计算函数 查找引用函数 vlookup 主要的查询函数vlookup lookup value table array col index num range lookup lookup value 为需要在数组第一列中查找的数值 table array 为需要在其中查找数据的数据表 如果range lookup为true 则table array的第一列中的数值必须按升序排列 2 1 0 1 2 z false true 否则 函数vlookup不能返回正确的数值 如果range lookup为false table array不必进行排序 table array的第一列中的数值可以为文本 数字或逻辑值 文本不区分大小写 col index num为table array中待返回的匹配值的列序号 range lookup为一逻辑值 指明函数vlookup返回时是精确匹配还是近似匹配 如果为true或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 如果range value为false 函数vlookup将返回精确匹配值 如果找不到 则返回错误值 n a match 查找定位函数row 行号column 列号 条件格式 条件格式是指当指定条件为真时 excel自动应用于单元格的格式 例如 单元格底纹或字体颜色 如果想为某些符合条件的单元格应用某种特殊格式 使用条件格式功能可以比较容易实现 如果再结合使用公式 条件格式就会变得更加有用 格式 条件格式 原理篇基础篇函数篇图表篇 目录 图表区域命名 图表标题 y轴 x轴 图例 数据点 绘图区 图表区 柱形图 柱形图显示一段时间内数据的变化 或者显示不同项目之间的对比 柱形图具有下列子图表类型 簇状柱形图 这种图表类型比较类别间的值 它还可以以三维效果显示 如以下图表所示 水平方向表示类别 垂直方向表示各类别的值 从而强调值随时间的变化 柱形图 堆积柱形图这种图表类型显示各个项目与整体之间的关系 从而比较各类别的值在总和中的分布情况 它也可以以三维效果显示 百分比堆积柱形图这种图表类型以百分比形式比较各类别的值在总和中的分布情况 它也可以以三维效果显示 三维柱形图这种图表类型沿着两个数轴比较数据点 例如 在下面的三维图表中 您可以比较欧洲的四个销售部门和其他两个地区的业绩 条形图 条形图显示各个项目之间的对比簇状条形图这种图表类型比较类别间的值 它也可以以三维效果显示 在下面的图表中 水平方向表示类别 垂直方向表示各类别的值 从而关注值的对比情况 堆积条形图这种图表类型显示各个项目与整体之间的关系 它还可以以三维效果显示 百分比堆积条形图这种图表类型以百分比形式比较各类别的值在总和中的分布情况 它也可以以三维效果显示 折线图 折线图按照相同间隔显示数据的趋势 它具有以下子图表类型 折线图这种图表类型显示随时间或类别的变化趋势 在每个数据值处还可以显示标记 堆叠折线图这种图表类型显示各个值的分布随时间或类别的变化趋势 在每个数据值处还可以显示标记 百分比堆叠折线图这种图表类型以百分比方式显示各个值的分布随时间或类别的变化趋势 在每个数据值处还可以显示标记 三维折线图这是具有三维效果的折线图 饼图 饼图显示组成数据系列的项目在项目总和中所占的比例 饼图通常只显示一个数据系列 当您希望强调数据中的某个重要元素时可以采用饼图 饼图具有下列子图表类型 饼图这种图表类型显示各个值在总和中的分布情况 它也可以以三维效果显示 如以下图表所示 分离型饼图这种图表类型显示各个值在总和中的分布情况 同时强调各个值的重要性 它也可以以三维效果显示 饼图 复合饼图这是一种将用户定义的值提取出来并显示在另一个饼图中的饼图 例如 为了看清楚细小的扇区 您可以将它们组合成一个项目 然后在主图表旁的小型饼图或条形图中将该项目的各个成员分别显示出来 复合条饼图这是一种将用户定义的数据提取处理出来并显示在另一个堆叠条形图中的饼图 xy散点图 xy散点图显示若干数据系列中各数值之间的关系 或者将两组数绘制为xy坐标的一个系列 散点图通常用于科学数据 具有下列子图表类型 散点图这种图表类型比较成对的数值 例如 以下散点图显示的是两组数据的不规则间隔 簇 折线散点图显示这种图表时 数据点之间可以显示也可以不显示直的或平滑的连接线 显示连接线时可以显示标记 也可以不显示标记 面积图 面积图强调大小随时间发生的变化 它具有下列子图表类型 面积图这种图表类型显示数值随时间或类别的变化趋势 它也可以以三维效果显示 通过显示已绘制的值的总和 面积图还可以显示部分与整体的关系 在下面的示例中 面积图强调了北京地区增长的销售额 并显示了每个地区对销售总额的贡献 堆叠面积图这种图表类型显示各个值的分布随时间或类别的变化趋势 它也可以以三维效果显示 百分比堆叠面积图这种图表类型以百分比方式显示各个值的分布随时间或类别的变化趋势 它也可以以三维效果显示 园环图 像饼图一样 圆环图显示部分和整体之间的关系 但是它可以包含多个数据系列 它具有下列子图表类型 圆环图圆环图的每个环分别代表一个数据系列 例如在下图中 内环代表汽油税的收入 外环代表财产税的收入 分离型圆环图分离型圆环图与分离型饼图类似 但可以包含多个数据系列 雷达图 雷达图比较大量数据系列的合计值 它具有下列子图表类型 雷达图这种图表类型显示数值相对于中心点的变化情况 显示时可以为每个数据点显示标记 例如 在下面的雷达图中 覆盖最大区域的数据系列 a品牌 表示该品牌含有最丰富的维生素 填充雷达图在这种类型的图表中 数据系列覆盖的区域填充有颜色 曲面图 如果您希望找到两组数据之间的最佳组合 可以通过曲面图来实现 就像在地形图中一样 颜色和图案表示具有相同取值范围的区域 曲面图具有下列子图表类型 三维曲面图这种图表类型在连续曲面上跨两维显示数值的变化趋势 例如 下图表显示产生相同抗拉强度值的温度和时间的各种组合 图表中的颜色表示特定的数值范围 不带颜色的三维曲面图称为框架式三维曲面图 曲面图 俯视 俯视曲面图 其中的颜色代表数值范围 不