1、Excel案例 01第 1 部分 Excel 基础概念1. 名称名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：插入名称定义，选中需要删除的名称点击删除按钮，点确定。名称的引用：需要引用某单元格时输入该单元格的名称：名称第 1 步：选中需要命名的某个单元格或单元格区域。第 2 步：在左上角名称框输入命名后回车。删除已有命名：选择“插入”“名称”“定义” ；选中需要删除的名称，点击“删除”按钮。名称命名的优点：1) 避免绝对引用的错误2) 对公式进行文字化表述，让公式更加容易理解3) 可以在整个工
2、作簿中通用，引用方便2. 常用引用函数row: 返回指定单元格的行号column: 返回指定单元格的列标match: 返回查找值在查找范围中的序号=match(查找值，查找范围，0)其第三个参数为 0，表示查找精确值address: 返回单元格名称，其参数为行号列标或计算行号列标的表达式=address(行号，列标)=address(1,1) 此公式返回 A1indirect: 返回单元格的值.其参数为单元格名称=indirect(“A1”),假设 A1=10,则 indirect 返回 10index: 在某区域内查找某个位置的值=index(查找区域，查找值所在的行号，查找值所在的列号)o
3、ffset：指定基点，指定位移量，得到单元格引用单个单元格引用：=offset(基点单元格，向下移动的行，向右移动的列)区域的引用：=offset(基点单元格，向下移动的行，向右移动的列，区域包括的行数，区域包括的列数)向上和向左移动时，位移量为负值。=offset(A1,1,1) 将得到 B2 单元格的值3. 动态引用动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相似，但比简单的相对引用用途广泛。常用的动态引用的实现方法有：引用函数嵌套。比如 offset 和 row,column 嵌套；index 和 match，indirect,address 嵌套等；引用函
4、数与控件结合使用，此类控件包括组合框，滚动条。动态引用的作用：实现对于单元格的动态引用；进行动态分析；制作动态报表。4. 运算类型数值运算：1 + 1 = 2逻辑运算：1 0 = TRUE在逻辑运算中，TRUE=1，FALSE=0我们利用逻辑运算进行条件判断在 Excel 中常用的一些逻辑函数：if,and,or,not,iserror 等and: 只有当所有条件全部满足，才会返回 true 的逻辑值语法：and(条件 1，条件 2，)or: 满足其中任何一个条件，都会返回 true 的逻辑值语法：or(条件 1，条件 2，条件 3,)5. 函数调用的语法函数名称（参数 1，参数 2，）6.
5、绝对引用和相对引用：绝对引用：所引用的单元格不随着公式的复制而移动的引用方式。相对引用：所引用的单元格随着公式的复制而移动的引用方式。改变引用方式的方法：1) 在公式栏中选中需要改变引用方式的单元格，按 F4 键2) 给需要引用的单元格定义名称，然后在公式中引用该名称7. 数组公式对单元格区域进行多重计算的计算方式。与普通计算公式的区别是录入公式结束后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。如上图例，使用一个公式计算出所有产品的金额合计，引用的是所有的单价和所有的数量，执行的是多重计算。8. 错误提示Excel 中存在错误的类型，比如 1
6、/0=#DIV/0!, todas()=#NAMES!iserror 是一个逻辑函数，用以判断某个单元格内的值是否是一个错误，是错误则返回 TRUE,不是错误则返回 FALSE.iserror 有时可以和 if 函数嵌套进行一些较为复杂的判断。9. 有取值区间的随机数=最小值（最大值最小值）*rand()10. 循环引用是单元格引用其自身的引用方式。可以设置 Excel 允许进行循环引用：工具-选项-重新计算：将迭代计算选项打钩选中。行列互换（函数方式）：利用 transpose 函数+数组公式实现。首先选中行列数和原区域相反的一个区域；然后输入 transpose 函数最后按下组合键 ctr
7、l+shift+enter第 2 部分 Excel 设置1 显示当前文件的完整路径菜单区域右键菜单Web2 显示菜单项全部菜单视图工具栏自定义选项始终显示整个菜单3 鼠标移动方向工具选项编辑按 Enter 键后移动方向4 隐藏界面要素工具选项视图包括：网格线，滚动条，工作表标签，行号列标等。5 以显示值为准工具选项重新计算以显示精度为准行的合计与列的合计有时出现不相等的情况。可以采用以下方法解决。选择“工具”“选项”“重新计算”“以显示精度为准” ，选中该选项。6 自定义序列工具选项自定义序列7 改变文件保存位置工具选项常规默认文件位置8 改变文件用户名工具选项用户名9 单元格自动换行格式单元
8、格对齐自动换行第 3 部分 Excel 基础操作1. 快速选中数据表的整行或者整列ctrl + shift + 下箭头/右箭头2. 快速选中区域ctrl + shift + 83. 行列互换复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴选中“转置”选项4. 一个单元格内容输入为多行alt + 回车键5. 显示公式ctrl + 6. 冻结窗口选择需要进行冻结的单元格位置，选择窗口冻结窗格7. 自定义格式语法：大于条件值格式；小于条件值格式；等于条件值格式；文本格式8. 缩放数值0.00， ， 按百万缩放0“.”0，按万缩放0.00, 按千缩放9. 不复制隐藏的行或列a 首先
9、选中需要复制的被隐藏了一些行或列的表格区域；b 然后点击“编辑”-“定位”-“定位条件” ，在其中选择“可见单元格” ；c 复制表格区域，粘贴即可。案例 02 一 文本的处理1. 等长文本的分割从字符串的左边取字符：=Left (字符串，文本长度)从字符串的右边取字符：=Right (字符串，文本长度)从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)2. 不等长文本的分割第 1 步：选中要进行分割的字符串区域。第 2 步：点击“数据”“分列” ，在文本分列向导中选择适合的分隔符。第 3 步：设置需要导入的列以及放置该列的位置。3. 文本的合并=Concatenate(文本 1
10、，文本 2，)使用连接符红色“金额借贷不平衡“；蓝色”借贷平衡”Ctrl+拖拽标签来复制工作表编制损益表 创建损益表格式 创建 IS 嵌套函数IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。函数 如果为下面的内容，则返回 TRUEISBLANK 值为空白单元格。ISERR 值为任意错误值（除去 #N/A）。ISERROR值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL 值为逻辑值。ISNA 值为错误值 #N/A（值不存在）。ISNONTEXT 值为不是文本的任意项（注意此函数在值
11、为空白单元格时返回 TRUE）。ISNUMBER 值为数字。ISREF 值为引用。ISTEXT 值为文本。 创建财务比率销售毛利率=销售毛利/销售净额毛利率大，表示经营能力强。销售成本率=销售成本/销售净额成本率低，表示经营能力好。净利率率=净利润/销售净额净利润率越高，表示经营能力强。营业比率=（销售成本+营业费用）/销售净额，营业比率越低，表示经营能力越强营业费用率=销售费用/销售净额营业费用越低，表示经营绩效越好。 用 MAX、MIN 函数显示制表日期MAX，返回一组值中的最大值MIN，返回一组值中的最小值案例 13 多重合并计算数据如下图格式的即为二维表：现在我们需要将数个格式相同的二
12、维表汇总为一张表格，且可以区分不同表格属性进行分析，比如三张表分别为北京，上海，深圳分公司的表格。我们使用的方法是利用透视表多重合并计算数据区域的功能。选择数据-数据透视表和数据透视图选择第 3 个数据源类型：多重合并计算数据区域，点击下一步按钮：在出现的界面上选择：自定义字段选择需要被合并的表格区域，点击添加将其添加到所有区域；将页字段数据改为 1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。透视表生成后如下图所示，已经将多个表格合并成一个：双击字段名，可以对字段名进行修改：案例 14 回归与预测回归分析表明事物之间相互影响的关系，主要用于分析单个因变量如何受一个
13、或多个自变量影响的。比如某地空调的销量与气温的变化之间的关系。可以用统计获得的历史数据，对未来的数据进行预测。可以利用它帮助经营，财务，销售，营销等方面的决策。1. 趋势线预测1) 根据历史数据制作出折线图2) 选中图表中的折线，右键菜单添加趋势线3) 选择预测类型（可以先选择默认的线性）4) 切换到选项标签，根据需要预测的期间数，在“前推”处输入预测期间个数，并选中“显示公式”和“显示 R 平方值”选项5) 根据出现的回归系数（R 平方）判断预测类型是否适合，R 平方越接近 1 越好（多项式除外，需要考虑业务数据是否存在较大的波动，如果没有则不适用多项式分析类型）6) 根据回归方程求预测值7
14、) 移动平均没有 R 平方，不适用此分析方法。2. Correl 函数判断相关性。相关系数在 0.8 以上为强相关，0.6 以上为弱相关。案例 15-16 数据分析与图表分析日常工作中主要用到的数据分析有：构成：局部与整体比例关系。序列：在一个时间序列内进行的比较与分析。差异：预算与实际数的版本差异等。增长：增长率与增速，进行趋势预测。勾稽：数据之间固有的相互联系的属性，利用此属性进行数据合理性的判断。图表用于形象地展示数据。利用图表可以更好地对业务数据进行分析。1. 差异分析：利用上面的表格制作面积图，数据区域选择 C25:D31选中 B 公司数据系列，右键菜单 “数据系列格式”将其内部（填
15、充色）和边框色都改为“无”图表上将只显示差异部分，如下图：2. 双坐标图表：利用下面的数据制作双坐标图表：选择“插入”-“图表” ，进入图表向导，选择柱形图，第一种子图表类型簇状柱形图。选择“视图”-“工具栏”-“图表” ，调出图表工具条：点开图表工具条上下拉菜单，选择 系列“完成率”点击图表工具条上“数据系列格式”按钮：在数据系列格式界面上，切换到坐标轴标签，将系列绘制在由“主坐标轴”改为“次坐标轴”再次选中系列“完成率” ，右键菜单选择“图表类型”选择折线图，点确定：双坐标图表制作完成。3. 气泡图：利用下表制作气泡图：在图表向导中选择“气泡图”因为气泡图没有分类轴（其横坐标为序列号） ，我们需要添加数据标识，点击下一步，切换到数据标志标签，将系列名称打勾：
