大作业中的EXCEL技巧-笔记整理.xls

大大作作业业要要用用到到的的EXCEL技技巧巧 笔笔记记整整理理 仅仅供供参参考考 每每一一个个问问题题都都有有很很多多解解法法工工具具 不不必必拘拘泥泥于于以以下下的的方方式式 2012 7 122012 7 12 李李老老师师 1 编编目目录录 在word里 按 引用 按住CTRL的同时点击所有一级标题 会变深 按 添加文字 接着按住CTRL的同时点击所有二级标题 变深 再按 添加文字 依此类推 完成后按 引用 目录 插入目录即可 2金金额额变变成成万万元元单单位位 先在该表下任意位置输入10000 按右键 复制 该框闪动2 84 框住要改变的数字6 47 右键 选择性粘贴 运算 按 除 273 85 10000 3EXCEL表表粘粘贴贴到到WORD里里 边边框框在在界界外外怎怎办办 在word里 框住从EXCEL复制过来的此表 右键 自动调整 根据窗口调整 即全部缩回页内 4 用用数数组组解解决决共共同同比比计计算算 先用鼠标框住想要算出数值的区域 再输入栏中 c3 c8 c8 同时按住SGIFT CTRL 再按ENTER 大括号出现 括住了公式 数值自动在已选的区域出现 2008年销售 地地区区销销售售 上海分公司34 DIV 0 23 北京分公司19 DIV 0 13 重庆分公司35 DIV 0 24 沈阳分公司42 DIV 0 28 广州分公司18 DIV 0 12 合合计计148 DIV 0 100 5 平平衡衡图图简简单单方方法法 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000240240 000 00 45000330360 0030 00 60000420480 0060 00 75000510600 0090 00 250000200000150000100000500000 200 0 200 400 600 800 1000 1200 1400 1600 1800 2000 成本 万元 收入 万元 利润 万元 盈亏平衡线 90000600720 00120 00 105000690840 00150 00 120000780960 00180 00 1350008701 080 00210 00 1500009601 200 00240 00 16500010501 320 00270 00440 00300 00 19500012301 560 00330 00 21000013201 680 00360 00 22500014101 800 00390 00 1 先做好上方的表格 2 框住那张表及表头 点 插入 平滑直线散点图 即出上方图形 3 在下面空白处 如66 67行 输入事先算好的盈亏平衡点的销售量和盈亏平衡点的收入 4 框住上方散点图 右键 选择数据 添加 系列名称 盈亏平衡线 X轴系列值 用鼠标选中C66到E66这三个数 再填Y轴系列值 用鼠标选中B67到E67 5 鼠标放在空白处 插入 文本框 在任意空白处出现小十字 拉出框 填好盈利区 拖至图上某处 6 X Y轴 也可用上面方法标注 或插入 形状 找出空心箭头 在里面填字 盈亏平衡量0 KG3000030000 盈亏平衡收入210 00万元2100 平衡点销售量 固定成本 单价 单位变动成本 平衡点销售额 单价 销量 6 IRR点点击击 公公式式 找找 财财务务 找找 Irr value填填 净净现现金金流流0年年 最最后后一一年年 GUESS 空空着着 NPV点击 公式 找 财务 找 NPV RATE填 0 1 valu1填 净现金流0年 VALUE2填 净现金流最后一年 RATE为10 ARR 平均收益 初始投资额 平均收益 十年利润合计 10年 年份是自定的 回回收收期期 累计净现金流量开始出现正值的年份数 1 上一年累计净现金流量的绝对值 出现正值年份的净现金流量 7流 流程程图图画画法法 在在POWERPOINT里里做做容容易易 点开POWERPOINT 在一个PPT里开始做 插入 下方有 表格 绘制表格 出现铅笔图标 可随意画框 开始 椭圆框过程 矩形框判断 菱形框 250000200000150000100000500000 200 0 200 400 600 800 1000 1200 1400 1600 1800 2000 成本 万元 收入 万元 利润 万元 盈亏平衡线 28394 64738 2738489 10000 每每一一个个问问题题都都有有很很多多解解法法工工具具 不不必必拘拘泥泥于于以以下下的的方方式式 2012 7 122012 7 12 李李老老师师 接着按住CTRL的同时点击所有二级标题 变深 再按 添加文字 依此类推 完成后按 引用 目录 插入目录即可 再输入栏中 c3 c8 c8 同时按住SGIFT CTRL 再按ENTER 大括号出现 括住了公式 250000200000150000100000500000 200 0 200 400 600 800 1000 1200 1400 1600 1800 2000 成本 万元 收入 万元 利润 万元 盈亏平衡线 这是盈亏平衡折线的三个坐标点 必须按此方式标注 250000200000150000100000500000 200 0 200 400 600 800 1000 1200 1400 1600 1800 2000 成本 万元 收入 万元 利润 万元 盈亏平衡线 3 在下面空白处 如66 67行 输入事先算好的盈亏平衡点的销售量和盈亏平衡点的收入 4 框住上方散点图 右键 选择数据 添加 系列名称 盈亏平衡线 X轴系列值 用鼠标选中C66到E66这三个数 5 鼠标放在空白处 插入 文本框 在任意空白处出现小十字 拉出框 填好盈利区 拖至图上某处 点点击击 公公式式 找找 财财务务 找找 Irr value填填 净净现现金金流流0年年 最最后后一一年年 GUESS 空空着着 点击 公式 找 财务 找 NPV RATE填 0 1 valu1填 净现金流0年 VALUE2填 净现金流最后一年 ARR 平均收益 初始投资额 平均收益 十年利润合计 10年 年份是自定的 回回收收期期 累计净现金流量开始出现正值的年份数 1 上一年累计净现金流量的绝对值 出现正值年份的净现金流量 盈利区 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据 300001800 30000210 300000 30000 100 售价 70 元 公斤 固定成本600 000 00 盈亏平衡量30 000 KG 盈亏平衡收入 210 00万元 成本 万元 收入 万元 利润 万元 盈亏平衡线 30000 250000200000150000100000500000 100 100 300 500 700 900 1100 1300 1500 1700 1900 产量 收入 万元 成本 万元 收入 万元 利润 万元 盈亏平衡线 单位成本 50 元 公斤 成本 万元 收入 万元 利润 万元 盈亏平衡线 30000 250000200000150000100000500000 100 100 300 500 700 900 1100 1300 1500 1700 1900 产量 收入 万元 成本 万元 收入 万元 利润 万元 盈亏平衡线 产量 KG 成本 万元 收入 万元 利润 万元 060 15000135 30000210 45000285 60000360 75000435 90000510 105000585 120000660 135000735 150000810 165000885 180000960 1950001035 2100001110 2250001185 盈亏平衡线辅助数据 售价 70 元 公斤 固定成本600 000元 盈亏平衡量 盈亏平衡收入 第一步 创建成本公式 选中C3单元格 输入公式 K 26 B3 C 28 10000 复制公式至C18 单位可变成本 50 元 公斤 量本利公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第一步 创建成本公式 选中C3单元格 输入公式 K 26 B3 C 28 10000 复制公式至C18 产量 KG 成本 万元 收入 万元 利润 万元 060030000210210 45000285315 60000360420 75000435525 90000510630 105000585735 120000660840 135000735945 1500008101050 1650008851155 1800009601260 19500010351365 21000011101470 22500011851575 盈亏平衡线辅助数据 售价 70 元 公斤 固定成本600 000元 盈亏平衡量 盈亏平衡收入 第二步 创建收入公式 选中D3单元格 输入公式 H 26 B3 10000 复制公式至D18 单位可变成本 50 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第二步 创建收入公式 选中D3单元格 输入公式 H 26 B3 10000 复制公式至D18 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据 售价 70 元 公斤 固定成本600 000元 盈亏平衡量 盈亏平衡收入 第三步 创建利润公式 选中E3单元格 输入公式 D3 C3 复制公式至E18 单位可变成本 50 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第三步 创建利润公式 选中E3单元格 输入公式 D3 C3 复制公式至E18 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据 30000 30000 30000 30000 售价 70 元 公斤 固定成本600 000元 盈亏平衡量 盈亏平衡收入 第四步 设置盈亏平衡线横坐标数据 选中C22单元格 输入公式 ROUND C28 H26 K26 2 算算出出盈盈亏亏平平衡衡量量 销销量量 C23 C25都输入 C 22 单位可变成本 50 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第四步 设置盈亏平衡线横坐标数据 选中C22单元格 输入公式 ROUND C28 H26 K26 2 算算出出盈盈亏亏平平衡衡量量 销销量量 C23 C25都输入 C 22 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据 300001000 30000210 300000 30000 50 售价 70 元 公斤 固定成本600 000元 盈亏平衡量 盈亏平衡收入 第五步 设置盈亏平衡线纵坐标数据 选中D22单元格 输入 1800 选中D23单元格 输入公式 C23 H26 10000 算算出出盈盈亏亏平平衡衡收收入入 选中D24单元格 输入 0 选中D25单元格 输入 100 单位可变成本 50 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第五步 设置盈亏平衡线纵坐标数据 选中D22单元格 输入 1800 选中D23单元格 输入公式 C23 H26 10000 算算出出盈盈亏亏平平衡衡收收入入 选中D24单元格 输入 0 选中D25单元格 输入 100 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据 300001800 30000210 300000 30000 100 售价 70 元 公斤 固定成本600 000元 盈亏平衡量30000 盈亏平衡收入 210 00万元 第六步 设置盈亏平衡量和盈亏平衡收入 选中C30单元格 输入公式 C23 选中C31单元格 输入公式 D23 单位可变成本 50 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第六步 设置盈亏平衡量和盈亏平衡收入 选中C30单元格 输入公式 C23 选中C31单元格 输入公式 D23 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000210210 000 00 45000285315 0030 00 60000360420 0060 00 75000435525 0090 00 90000510630 00120 00 105000585735 00150 00 120000660840 00180 00 135000735945 00210 00 1500008101 050 00240 00 1650008851 155 00270 00 1800009601 260 00300 00 19500010351 365 00330 00 21000011101 470 00360 00 22500011851 575 00390 00 盈亏平衡线辅助数据10000 300001800 30000210 300000 30000 100 售价 70 元 公斤 固定成本600 000元 盈亏平衡量30 000 KG 盈亏平衡收入 210 00万元 简简单单方方法法 第七步 添加窗体 1 点击菜单 视图 右键自定义 插入控件 滚动条窗体控件 2 在 窗体 工具栏中单击 滚动条窗体控件 按钮 3 在G28单元格拖出第一个滚动条 4 点击菜单 视图 工具栏 窗体 弹出 窗体 工具栏 5 在 窗体 工具栏中单击 滚动条 按钮 6 在J28单元格拖出第二个滚动条 单位可变成本 50 元 公斤 盈利区 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 盈利区 第七步 添加窗体 1 点击菜单 视图 右键自定义 插入控件 滚动条窗体控件 2 在 窗体 工具栏中单击 滚动条窗体控件 按钮 3 在G28单元格拖出第一个滚动条 4 点击菜单 视图 工具栏 窗体 弹出 窗体 工具栏 5 在 窗体 工具栏中单击 滚动条 按钮 6 在J28单元格拖出第二个滚动条 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 0000 30 00 30000240240 000 00 45000330360 0030 00 60000420480 0060 00 75000510600 0090 00 90000600720 00120 00 105000690840 00150 00 120000780960 00180 00 1350008701 080 00210 00 1500009601 200 00240 00 16500010501 320 00270 00440 00300 00 19500012301 560 00330 00 21000013201 680 00360 00 22500014101 800 00390 00 盈亏平衡线辅助数据 300001800 30000240 300000 30000 100 售价 80 元 公斤 固定成本600 000元 盈亏平衡量30 000 KG 盈亏平衡收入 240 00万元 第八步 设置滚动条格式 1 设置第一个滚动条最小值 60 点滚动条右键 设置控件格式 2 设置第一个滚动条最大值 80 步长1页步长10 3 单元格链接 H 26 售价 1 设置第二个滚动条最小值 35 2 设置第二个滚动条最大值 60 步长1页步长10 3 单元格链接 K 26 单位可变成本 单位可变成本 60 元 公斤 公式 成本 单位可变成本 产量 固定成本 收入 售价 产量 单位边际贡献 单位售价 单位可变成本 盈亏平衡量 固定成本 单位售价 单位可变成本 盈亏平衡收入 盈亏平衡量 售价 第八步 设置滚动条格式 1 设置第一个滚动条最小值 60 点滚动条右键 设置控件格式 2 设置第一个滚动条最大值 80 步长1页步长10 3 单元格链接 H 26 售价 1 设置第二个滚动条最小值 35 2 设置第二个滚动条最大值 60 步长1页步长10 3 单元格链接 K 26 单位可变成本 产量 KG 成本 万元 收入 万元 利润 万元 0600 00 60 00 15000112 5100 50 12 00 30000165201 0036 00 45000217 5301 5084 00 60000270402 00132 00 75000322 5502 50180 00 90000375603 00228 00 105000427 5703 50276 00 120000480804 00324 00 135000532 5904 50372 00 1500005851 005 00420 00 165000637 51 105 50468 00 1800006901 206 00516 00 195000742 51 306 50564 00 2100007951 407 00612 00 225000847 51 507 50660 00 盈亏平衡线辅助数据 187501800 18750125 625 187500 18750 100 售价 67 元 公斤 固定成本600 000元 盈亏平衡量18 750 KG 盈亏平衡收入 125 63万元 250000200000150000100000500000 500 0 500 1000 1500 2000 产量 KG 收入 万元 成本 万元 收入 万元 利润 万元 盈亏平衡线 250000200000150000100000500000 500 0 500 1000 1500 2000 产量 收入 万元 利润 万元 盈亏平衡线 单位可变成本 35 元 公斤 第九步 绘制散点图 选中B2 E18中任一单元格 点击 图表向导 按钮 选择平滑散点图 创建图表 XY散点图 带平滑线散 散点图 向导 步骤2 单击 系列 选项卡 添加新系列名 名称 盈亏平衡线 点击散点图 上方 图表工具 选择数据 添加 X值 选择C22 C25区域 拉框选择 Y值 选择D22 D25区域 向导 步骤3 数值X值 输入 产量 KG 数值Y值 输入 收入 万元 250000200000150000100000500000 500 0 500 1000 1500 2000 产量 KG 收入 万元 成本 万元 收入 万元 利润 万元