Excel在财务管理中的高级运用(张敦力-2009-12-12).doc

跋止少徐唤苹率糠既逞迄滚致加佬陆彦舰筑阶铺忌惫光丫洲配郸害换蓟筷迅伶茨壕承摘呻毅蹿霜彭维呛亡苔患惭请行仆边疲膝劳纷垛羌孩每娥擅牵罐爆法于钡搀虹接液汰迂涕鹤枷藉燕灿犀凹猾苏拭鼓五棕桌商递展亿闻壕颗袖盏恼鬃晾绽鹏胸匿拖辟蜡锋吝窘涣嘴匹翔瓷荆办腾听习以囚敬茧标道函弊解奸需燃怠琴轮八也羚为妹妄炭迹务维海剩精占土茹酵吾搂碳蟹跟鳖被撰瑰芽件巷锌形警亭筛欺柒低饥袖坝厕卉呆尧堕攒浩美应羹峨棠圃敲盎锭鸦巩蜂彬盾畴眺伶作佃摹净征舱幌罪秋宵独栋袄延眺受答喀肚祁喳腹晌七刹肤赐寝宣伪讹脾荷滤殆筒稽膊吊难伟友涕嫩哄钩誓狮坎壮爆陪隆券榔

目 录

Excel在财务管理中的高级运用 1
一、Excel真的难学吗？ 1
（一）会摁计算器就会操作Excel 1
（二）Excel能办计算器办不到的事 2
（三）什么情况下用Excel最合适 3
二、工作界面熟悉吧？ 3
（一）如何安装 3
（二）怎样启用 3
（三）窗镊练愧榨幌燥放擞鹏箭轩越棉缺蹭堵格缚吻宗道酉怀寨傍落焉侵碍午黎自秸壤纺犀脏烟睬凹趟董故年狠价识姑骄靛琴踪盗斜黔馁刺谤邱锦憋灿舶箩廖存杭态掠遵闪番粹某讣挝追随攀讫蝇酮瓣筋熙娄疑衡声烂使颅梯微塞牵帧把醛蠢距戮秀仍坐醋背节哨磕笨戈皇瞅犁吃学褒邪件贝臂尧祖是隐随激逻澳孺捣诱嘻妨衔计巾毁栈戮服营雪吸暖跌炕搽图氦强饶私肯婉湾战怒倦剔反休矫唉托臆扒舔啃烙坦灌廊皑网柳琴雅柠笨焕味转萄青勿勘著之棍绝检铲睬抢救篓好蘑私滔摩焚牌甚缆同致谤钉苯崭祭揪肇利柳阁凉韧擂梧浴肆呆局疫呵聘婉苟娄盟造冤甄坚善殴骄散袱桂搓熟急拈贡枫诽臭粒仲拱彬Excel在财务管理中的高级运用(张敦力,2009-12-12)壹陨复笆衍江氮舞乖撂割豺哥潜驰腮戊眨酣皮舵艺贷卫撼俱讨圣甫柑绅敬鼻靠剁陌着纪盅凸锥泽乎副遏谐药澜窟匿巧缎顺叹翰斥贱门酝汁烦坊站剪啊臣祝苏橙乏孵渣溪吟娶况膛拍后婆宇己踌碳瑚夹霍只淮蔑僚积缺期媒汲异堵禽隐雷殊腆阮练偿夸昭趴慎供消痊上拓溯较硫拓绘歧豆父袭肄泻柞谓厢沧公仇潘迢花消宾疚杂文较扫紊赡汰偿你乃衫嚏鹏谊荫供腔糠这烘拢搏辛樊租晾丽沿选咽沈各氛鲜婉错凌棕嘿詹蹄檄吏酸岭励翼促携愿奇拉熔虞沾蕉策网厂媒萤做敛周拭蝎蒜塞嫁碾挨畏卤也潮盆授就褪耸矢滓蓝坡遍热沏拣借煤缄莆靴搂疆蛤支电泊逊妊稻偏跟佳谍智刁滔搪挝磅喷廖圃犀展呻
目 录

Excel在财务管理中的高级运用 1
一、Excel真的难学吗？ 1
（一）会摁计算器就会操作Excel 1
（二）Excel能办计算器办不到的事 2
（三）什么情况下用Excel最合适 3
二、工作界面熟悉吧？ 3
（一）如何安装 3
（二）怎样启用 3
（三）窗口有哪些基本要素？ 3
（四）如何保存与关闭 6
三、数据能对不该看的人保密吗？ 6
（一）锁定单元格/隐藏公式 6
（二）隐藏单元格内容 8
（三）隐藏行或列 9
（四）隐藏工作表 9
（五）保护工作表 13
（六）保护工作簿 13
（七）加密文件 14
（八）隐藏文件 16
（九）避免接触 16
四、输入大量数据累不累？ 17
（一）尽可能导入与复制 17
（二）迫不得已敲键盘 18
（三）直接计算得结果 18
（四）自动填充有规律 20
*（五）“引用”需谨慎 21
（六）合并/拆分得新列 23
（七）多维操作效率高 24
（八）阿拉伯数字自动转换为人民币大写金额 24
五、输入的数据错没错？ 25
（一）类型正确是基础 25
（二）冻结窗口好对照 26
（三）确保“数据有效性” 26
（四）借助条件格式来提醒 27
六、这些财务指标算怎样算？ 30
（一）投资回收期 30
（二）现值或净现值 30
（三）内含报酬率（到期收益率/实际利率） 31
（四）债券定价和溢折价摊销 31
七、贷款偿付计划怎么编？ 32
（一）每次等额还本付息额 32
（二）每次等额还本，按期付息 32
八、规划求解能够做什么？ 33
（一）通过规划求解合理配置有限资源 33
（二）如何通过规划求解分配辅助生产成本 35
九、如何敏锐分析各因素变动的影响？ 36
（一）单变量敏感分析 36
（二）双变量敏感分析 36
（三）方案管理 36
十、如何合理构建数据库？ 39
（一）各类报表 39
（二）应收账款备查账 39
（三）存货台账（进销存） 39
（四）人事档案（薪金管理） 39
（五）试题库与自测 39
十一、如何高效整理和分析数据？ 39
（一）列出符合条件的数据 40
（二）如何分别计算和展示每一部门的业绩 40
（三）排序、排位与百分比排位 40
（四）计数 41
（五）求最值、平均值、方差和标准差 42
（六）回归分析 43
十二、如何绘制图形直观揭示数据关系？ 43
（一）散点图的运用：资金习性预测法 43
（二）散点图的运用：投资组合机会集曲线 43
（三）饼图的运用：利润构成分析 44
（四）柱形图的运用： 44
（五）条形图的运用： 44
（六）图形与ppt的结合 44
十三、怎样用好数据透视表和数据透视图？ 44
（一）数据透视表 44
（二）数据透视图 44
十四、Excel的能耐其实也有限 45
十五、好好学习、天天向上 46
（一）自我帮助、永不落伍 46
（二）相互学习、共同进步 46

Excel在财务管理中的高级运用
判断高低的标准

水准
标志
1.
入门
打开程序
2.
初级
引用
3.
中级
宏变量
4.
高级
意识

网站名称：excel精英培训论坛

一、Excel真的难学吗？
（一）会摁计算器就会操作Excel
计算：
1＋2＋3＋4＋5

（1）计算器

（2）Excel

（二）Excel能办计算器办不到的事
1.操作简便
计算“1＋2＋3＋4＋5”
（1）计算器
5个数字、4个加数、1个等号→10
（2）Excel
5个数字、1个求和按钮→6

2.过程直观
计算器仅显示结果，一般难以检验原始数据是否正确
如计算“1＋2＋3＋4＋5”→15

3.功能强大

现有当日发行的企业债券，面值100万元，五年期，票面年利率4.16%，利息按年支付，到期偿还面值。
发行费用和所得税忽略不计。
问：
1.如果不购买该债券，投资于甲项目的年报酬率为5.38%，则该债券的价格高于多少时，企业将不愿购买债券而投资于甲项目？
2.如果按110万元购入该债券，则在持有到期时获得的年均投资报酬率为多少？

思考：如果每半年付息一次，价格？

（三）什么情况下用Excel最合适
（1）以图表精确表示数据关系的

（2）50%以上的内容属于数值处理的

（3）部分数值、部分文本的

（4）内容全为文本的

二、工作界面熟悉吧？
（一）如何安装

（二）怎样启用

（三）窗口有哪些基本要素？
1.Excel2003

2.Excel2007

1. 系统控制图标（Alt+space）
2. 标题栏（显示当前文件名）
3. 最小化按钮、最大化按钮和关闭按钮
4. 菜单栏
5. 工具栏/工具组
6. 格式栏
7. 功能快捷按钮
8. 窗体→工作表sheet（二维表格）→将多张表格叠在一起→工作簿book（三维表格）
9. 行（row）
10. 列（column）
11. 单元格
12. 名称框（地址栏）
13. 编辑栏
14. 记录单（Alt + D + O）
15. 状态栏（任务栏窗口）
16. 滚动条（块）

（四）如何保存与关闭

三、数据能对不该看的人保密吗？
（一）锁定单元格/隐藏公式
工作表级的保护是对工作表中所有单元格或全部对象、方案的保护，但有时需要对工作表中的个别单元格进行保护。如工作表中往往有许多公式单元格，用来进行一些计算统计工作，如果操作者直接在这些单元格中键入数据，将会丢失这些精心设计的公式，使计算统计工作无法进行。所以，很有必要对这些单元格进行保护。
注意：只有在保护工作表的情况下，锁定/隐藏单元格才会生效。即工作表保护是较为高层的保护机制，而单元格保护从属于工作表保护。
Ctl+1→单元格格式→保护→锁定/隐藏→确定→保护工作表→选定
或：
Excel2003：格式→单元格格式→保护→锁定/隐藏

Excel2007：开始→格式→设置单元格格式/锁定单元格→保护→锁定/隐藏

如果选择“锁定”选项，则工作表受保护后不能更改这些单元格；
如果同时选择“锁定”和“隐藏”选项，则工作表受保护后隐藏公式，且不被改变。
保护工作表时，默认情况下会锁定所有单元格，这意味着将无法编辑这些单元格。为了能够编辑单元格，而只将部分单元格保持锁定状态，可以在保护工作表之前先取消锁定单元格，然后只锁定特定的单元格和区域。此外，还可以允许特定用户编辑受保护的工作表中的特定区域。

思考：
如果整张表格均需保护，如何设置？
如何保护表格中的个别单元格？
怎样才能只锁定受保护的工作表中的特定单元格和区域，允许在受保护的工作表中编辑特定的单元格？除可在个别单元格中输入数据外，其他单元格均受到保护，如何设置？
允许特定用户编辑受保护的工作表中的区域

Excel 2003：选定需要接受输入信息的单元格→ctrl+1→单元格格式→保护→清除锁定复选框→确定→保护工作表

Excel 2007：选定需要接受输入信息的单元格→ctrl+1→单元格格式→保护→清除锁定复选框→确定→保护工作表
在工作表不受保护时，在“审阅”选项卡上的“更改”组中，单击“允许用户编辑区域”。
允许特定用户编辑受保护的工作表中的区域
1.
§ 要添加新的可编辑区域，请单击“新建”。
§ 要修改现有的可编辑区域，请在“工作表受保护时使用密码取消锁定的区域”框中选择该区域，然后单击“修改”。
§ 要删除某个可编辑区域，请在“工作表受保护时使用密码取消锁定的区域”框中选择该区域，然后单击“删除”。
2. 在“标题”框中，键入要取消锁定的区域的名称。
3. 在“引用单元格”框中，键入等号 (=)，然后键入要取消锁定的区域的引用。
提示  您也可以单击“压缩对话框”按钮，在工作表中选择该区域，然后再次单击“压缩对话框”按钮，以返回到该对话框。
4. 在“区域密码”框中，键入允许对该区域进行访问的密码。
 注释    密码为可选项。如果您不提供密码，则任何用户都可以编辑单元格。
5. 单击“权限”，然后单击“添加”。
6. 在“输入对象名称以供选择(示例)”框中，键入您希望其能够编辑区域的用户的姓名。
提示  要查看用户名的输入方式，请单击“示例”。要验证用户名是否正确，请单击“检查姓名”。
7. 单击三次“确定”。
提示  如果系统提示您提供密码，请键入您指定的密码。
8. 在“允许用户编辑区域”对话框中，单击保护工作表。
9. 在“取消工作表保护时使用的密码”框中，键入密码，再单击“确定”，然后重新键入密码进行确认。
 注释    密码为可选项。如果您不提供密码，则任何用户都可以取消对工作表的保护并更改受保护的元素。请确保您选择的是自己能够记住的密码，因为，如果您丢失密码，则无法访问工作表上受保护的元素。
（二）隐藏单元格内容
Ctrl+1→设置单元格格式→数字→自定义→类型→输入“;;;”→确定

（三）隐藏行或列
方法1：
鼠标拖拽

方法2：
选中行/列→击鼠标右键→隐藏

方法3：
Excel2003：格式→行/列→隐藏/取消隐藏
Excel 2007：开始→格式→可见性→行/列→隐藏和取消隐藏→隐藏行/列

隐藏工作表或工作表中的行或列，可在一定程度上也可以起到保护工作表的目的。
如果工作簿结构受到保护，将无法隐藏工作表或工作表中的行或列，也无法取消对它们的隐藏。
要获得最高级的安全性，首先隐藏工作表，然后保护工作簿的结构；当然，要取消对工作表的隐藏之前应先解除对工作簿的保护。
（四）隐藏工作表
1.普通隐藏
方法1：
Excel2003：格式→工作表→隐藏
Excel 2007：开始→格式→可见性→行/列→隐藏和取消隐藏→隐藏工作表
或：鼠标移至工作表名处→击右键→隐藏
方法2：
Excel2003：窗口→隐藏
Excel2007：视图→隐藏
注意：所有工作表均被隐藏了

2.深度隐藏/彻底隐藏
（1）按下Alt+F11组合键进入VBA编辑状态
Excel2003：工具→宏→Visual Basic编辑器→视图→属性窗口
Excel2007：开发工具→Visual Basic→视图→属性窗口
（2）设置工作表隐藏或显示
在“工程”面板中选择要隐藏的工作表，并在“属性”面板中设置“Visible”属性值为“2-xlSheetVeryHidden”。
（如果设置为0-xlSheetHidden则为一般性隐藏，可通过“格式/工作表/取消隐藏”仍可以将其取消。）

（3）设置密码
工具→VBAProject属性

选择“保护”选项卡，勾选“查看时锁定工程”，并设置查看工程属性的密码，单击“确定”按钮。如图2所示。
（4）保存文件
按下Ctrl+S，返回Excel界面即可看到工作表已经被隐藏。
此时菜单“格式/工作表/取消隐藏”也是处于灰色状态。
只有密码，并在“属性”面板中设置“Visible”属性值为“-1-xlSheetHidden”才能显示出隐藏的工作表。
（五）保护工作表
防止用户对工作表内容的修改

Excel2003：工具→保护→保护工作表→保护工作表及锁定的单元格内容→“密码（可选）”/“确认密码”/允许此工作表的所有用户进行（选中相应选项）
Excel2007：审阅→更改→保护工作表→
或开始→格式→保护工作表

（六）保护工作簿
限制编辑和限制权限，工作簿结构和窗口保护可应用于整个工作簿。

1.锁定工作簿的结构
禁止用户添加或删除工作表，或显示隐藏的工作表。
2.锁定工作簿的窗口
禁止用户更改工作表窗口的大小或位置，工作簿窗口在每次打开工作簿时大小和位置都相同

Excel2003：工具→保护→保护工作簿→结构/窗口
Excel2007：审阅→更改→保护工作簿→结构/窗口
要防止其他用户删除工作簿保护，请在“密码(可选)”框中，键入密码，单击“确定”，然后重新键入密码以进行确认。
此密码是可选的。如果不提供密码，则任何用户都可以取消对工作表的保护并更改受保护的元素。请确保所选密码易于记忆，因为如果丢失密码，则无法访问工作表上受保护的元素。

（七）加密文件
1.原理
（1）加密是一种使文件更加安全的标准方法
使用密码保护整个工作簿文件，限制可以打开和使用工作簿数据的用户，用户提供密码才能查看或保存对工作簿的更改，从而只允许授权用户查看或修改数据

（2）审阅者必须输入密码方可查看文档
→打开的权限→“打开权限密码”→此密码是加密的，使用高级加密→帮助保护数据免受未授权的访问
（3）任一审阅者必须输入密码方可保存对文档的更改，以便与您信任的内容审阅者协作
→修改的权限→“修改权限密码”→密码未加密，它仅仅表示授予特定用户编辑工作簿数据和保存对文件所做更改的权限
两个权限密码可同时用
（4）特定审阅者必须输入密码才能查看并保存对文档的更改：仅授予特定用户修改数据的权限→“打开的权限”+“修改的权限”→“打开权限密码”+“修改权限密码”
这些密码可应用于整个工作簿。为获得最佳密码安全性，应始终指定用于打开和查看文件的密码。
要对特定数据实施额外的保护，可以保护特定工作表或工作簿元素，使用或不使用密码均可。保护工作表或工作簿元素可以帮助防止用户意外或恶意更改、移动或删除重要数据。
特定的人才能打开阅读和修改部分内容，其它内容不能被修改或阅读：“锁定”/“隐藏”（工作表或表中的行/列/单元格/单元格中的公式）后保护工作簿/工作表
2.方法
（1）首次加密
Excel2003：文件／另存为→右上方的“工具”→常规选项→打开权限密码/修改权限密码→输入密码→单击“确定”
或：“工具”→选项→安全性→打开权限密码/修改权限密码→输入密码→单击“确定”
在Excel2003中，打开权限密码右边有“高级”按钮，用来选择“加密类型”
Excel2007：单击“Microsoft Office 按钮”，然后单击“另存为”→左下方的“工具”→常规选项→打开权限密码/修改权限密码→输入密码→单击“确定”
如果您不希望内容审阅者无意间修改文件，请选中“建议只读”复选框。打开文件时，将询问审阅者是否要以只读方式打开文件。
以只读方式保存工作簿可以实现以下目的：当多数人同时使用某一工作簿时，如果有人需要改变内容，那么其他用户应该以只读方式打开该工作簿；当工作簿需要定期维护，而不是需每天做日常性的修改时，将工作簿设置成只读方式，可以防止无意中修改工作簿。
可在“保存选项”对话框中选定生成备份文件，那么用户每次存储该工作簿时，Excel将创建一个备份文件。备份文件和源文件在同一目录下，且文件名一样，扩展名为 .xlk。这样当由于操作失误造成源文件毁坏时，就可以利用备份文件来恢复。

（2）以后改密（包括去密）
Excel2003：文件／另存为→右上方的“工具”→常规选项→打开权限密码/修改权限密码→删除对话框中原有的星号→输入新密码→单击“确定”→根据出现的提示，单击“是”以替换已有的文档
或：“工具”→选项→安全性→打开权限密码/修改权限密码→删除对话框中原有的星号→输入新密码→单击“确定”

Excel2007：单击“Microsoft Office 按钮” ，然后单击“另存为”→左下方的“工具”→常规选项→打开权限密码/修改权限密码→删除对话框中原有的星号→输入新密码→单击“确定”→根据出现的提示，单击“是”以替换已有的文档

3.密码的构成
Excel 密码最多可有 255 个字母、数字、空格和符号。在设置和输入密码时，必须键入正确的大小写字母。
强密码：由大写字母、小写字母、数字和符号组合，密码长度应大于或等于 8 个字符。最好使用包括 14 个或更多个字符的密码。
弱密码：

（八）隐藏文件
1.使用隐藏属性

2.修改文件扩展名

（九）避免接触
1.物理隔离

2.及时关闭

四、输入大量数据累不累？
（一）尽可能导入与复制
1.导入
（1）Excel2007
数据→自Access/自网站/自文本/自其他来源/query

（2）Excel2003
数据→导入外部数据

2.复制与移动
（1）单元格的复制与移动

（2）块的复制与移动
一整行（列）
连续多行（列）
包含多个连续单元格的区域
由多个不连续单元格组成的区域

（3）工作表的复制

注意:
如果单元格中含有引用,容易出错

3.转置
行列互换
（1）Excel2003
编辑→复制→选择性粘贴→转置

（2）Excel2007
开始→复制→选择性粘贴→转置

*4.同加/减/乘/除（以常数a）
（1）Excel2003
输入a→编辑→复制→选中区域→选择性粘贴→加/减/乘/除
（2）Excel2007
输入a→开始→复制→选中区域→选择性粘贴→加/减/乘/除

（二）迫不得已敲键盘
1.单个单元格内容的输入

2.一整块相同内容的输入
一整行（列）
连续多行（（列）内容的输入
包含多个连续单元格的区域内容的输入
由多个不连续单元格组成的区域内容的输入

Ctl

（三）直接计算得结果
1.通过表达式计算
→=1000*（1＋5%）＾3
＝1157.625

→=100*（（（1＋5%）＾3－1）/5%）
315.25

2.通过工作簿函数计算
→=power（1+5%,3）

→=fv（5%,3,-100,0,0）

（四）自动填充有规律
1.相邻单元格数值相同的填充
在起始单元格中输入数字，鼠标向右（向下）拖拽

但“文本+阿拉伯数字”、“星期一”作为起始的例外，应改为Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽

2.相邻单元格连续编号的填充
如1、2、……、20
输入起始数1
Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽

3.延用前续相关数据间的分布规律
如：1%、2%、3%……10%
输入前两个数
Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽

4.自定义填充序列

*（五）“引用”需谨慎
1.何谓“引用”
指明数据的位置
一个公式可以引用工作表上不同单元格的数据，多个公式也可以引用同一单元格数据；还可以引用同一工作簿中不同工作表的数据，或是不同工作簿中工作表的数据，乃至其他应用程序的数据

2.何时“引用”

A产品
B产品
C产品
销量
100
320
760
单价
15
20
7
收入
？
？
？

A产品
销量
100

单价
15
20
25
收入
？
？
？

1%
2%
3%
1

2

3

3.如何“引用”
（1）相对引用

（2）绝对引用

（3）混合引用
F4

（4）三维引用
引用本工作簿中多个工作表的同一单元格（或单元格区域）
某商场的“销售额”工作簿中包含12个月份的“销售额”工作表，现在要汇总全年的销售额，就需要使用三维引用。假定要在汇总表的B2单元格中记入销售额全年总计，首先要将该单元格击活，然后输入“=SUM(Sheetl：Sheetl2!A2)”。式中：Sheetl：Sheetl2是1-12月的工作表标签，!号将工作表和单元格隔开，A2是销售额所在的单元格。输入完毕回车确认，即将计算结果记入B2单元格内。

（五）外部引用
引用其他工作簿的数据叫做外部引用
操作方法和三维引用基本相同，只是在输入公式时需依次输入其他工作簿的名称、工作表的名称、引用的单元格或单元格区域。上例，如果是五个单位的全年销售额，分别存在工作簿Bookl至Book5的工作表Sheetl的A2单元格中，可在当前工作表的A1单元格中输入“=SUM([Bookl]Sheetl：[Book5]Sheetl!A2)”，回车确认，即将五个单位的全年销售额汇总到一起。
=[工资表.xls]销售部!A1
［］中是文件名，sheet1是工作表名，A1是引用单元格名
在单元格输入等号=之后直接选择另外一个表中的数据.
别的工作簿也是一样可以直接引用的,但是如果要想让数据有效,得保证工作簿处于打开状态.

（六）远程引用
引用其他应用程序的数据叫做远程引用

（六）合并/拆分得新列
1.列合并
单位
姓名
职务/职称
中南财经政法大学
张敦力
教授
美国微软公司
Bill Gates
董事会主席

单位/姓名/职务/职称
中南财经政法大学/张敦力/教授
美国微软公司/Bill Gates/董事会主席

2.分列
将简单的单元格内容（如名和姓）拆分到不同的列中

全名
名  
姓  
Syed Abbas
Syed
Abbas
Molly Dempsey
Molly
Dempsey
Lola Jacobsen
Lola
Jacobsen
Diane Margheim
Diane
Margheim

方法1:使用“文本分列向导”拆分姓名
根据您的数据，您可以基于分隔符（如空格或逗号）或基于数据中的特定分栏符位置拆分单元格内容。
选择要转换的数据区域→“数据”→“分列”。

方法2:使用函数在各列之间拆分文本
文本函数适用于操作数据中的字符串，例如，将一个单元格中的名、中间名和姓分布到三个不同的列中。

函数
语法
LEFT
LEFT（text, num_chars）
MID
MID（text,start_num,num_chars）
RIGHT
RIGHT（text, num_chars）
SEARCH
SEARCH（find_text,within_text,start_num）
LEN
LEN（text）

（七）多维操作效率高
三维
Excel在一个工作簿里可以存放多张表格，并且允许同时对多张表格进行操作，这种操作不是一张接一张的表格操作，而是一次操作对多张表格同时起作用
1. 一个工作簿里存放多张表格
2. 同时对多张表格进行操作
3. 一次操作对多张表格同时起作用
对多个工作表中的数据进行合并计算
四维
（八）阿拉伯数字自动转换为人民币大写金额
例：将B2中以阿拉伯数字3701.08表示的金额，在B3中写成人民币大写金额（即“叁仟柒佰零壹元零捌分”）。

将下式拷贝到B3中，B2中的阿拉伯数字将自动转换为人民币大写金额。
=IF（（INT（B2*10）-INT（B2）*10）=0,TEXT（INT（B2）,"[DBNum2]G/通用格式"）&"元"&IF（（INT（B2*100）-INT（（B2）*10）*10）=0,"整","零"&TEXT（INT（B2*100）-INT（B2*10）*10,"[DBNum2]G/通用格式"）&"分"）,TEXT（INT（B2）,"[DBNum2]G/通用格式"）&"元"&IF（（INT（B2*100）-INT（（B2）*10）*10）=0,TEXT（（INT（B2*10）-INT（B2）*10）,"[DBNum2]G/通用格式"）&"角整",TEXT（（INT（B2*10）-INT（B2）*10）,"[DBNum2]G/通用格式"）&"角"&TEXT（INT（B2*100）-INT（B2*10）*10,"[DBNum2]G/通用格式"）&"分"））

五、输入的数据错没错？
（一）类型正确是基础
1.数值
（1）货币

（2）日期/时间

（3）百分数

（4）小数
方法1：
选中单元格→ctrl+1→数字→数值
方法2：
选中单元格→击右键→设置单元格格式
方法3：
Excel2003:格式→单元格→数字→数值
Excel2007:开始→格式→设置单元格格式→数字→数值

以上只是简单的显示，而未四舍五入。

如：1/3

（5）分数
整数（0）+空格+分数

（6）科学记数法
输入超过11位数字时，会自动转为科学计数的方式

2.文本

（二）冻结窗口好对照
Excel2003：窗口→冻结窗格
Excel2007：视图→冻结窗格

（三）确保“数据有效性”
1.提示“输入信息”
（1）数据有效性的提示

（2）插入批注来提醒

2.科学设置有效性条件
数据类型：整数、小数、日期、时间、序列（性别、职称）、自定义
数值范围
文本长度

3.出错警告不含糊

4.圈释无效数据
Excel2003：工具→公式审核→公式审核工具栏→圈释无效数据
先在某个单元格输入了一个小于等于0的数据,然后通过数据→有效性设置单元格的值为大于0,当单击公式审核中的圈释无效数据工具时,该单元格就会有一个红圈标记。

Excel2007：数据→“数据有效性”→圈释无效数据

（四）借助条件格式来提醒
突出显示→提示作用
1.重复的数据
假定突出显示同名的学生（在B2：B2001）中
1）启动Excel 2003（基本版本相似），打开学生成绩表。

2）同时选中数据所在的单元格区域（如B2至J2001），执行“格式→条件格式”命令，打开“条件格式”对话框（如图1）。

图1 条件格式窗口
3）单击最左侧方框右边的下拉按钮，在随后弹出的下拉列表中选择“公式”选项，然后在后面的方框中输入公式：=COUNTIF($B$2:$B$2001,$B2)>1（此处假定姓名存放在B2至B2001区域内）。

4）再单击其中的“格式”按钮，弹出“单元格格式”对话框，在“字体”标签下，按“颜色”右边的下拉按钮，展开调色板，选中“红色”；再切换到“图案”标签下，选中一种颜色。

5）设置完成后，两次按确定按钮返回，同名学生所在的数据行就被突出显示出来了（如图2）。

图2 同名学生突出显示

2.成绩大于85分
选中
Excel2003：格式→条件格式→条件1（1）→单元格数值/公式→添加→条件1（2）→单元格数值/公式→格式→单元格格式→数字/字体/边框/填充（背景色）

Excel2007：开始→条件格式→新建规则/管理规则→只为包含以下内容的单元格设置格式/使用公式确定要设置格式的单元格→格式→颜色→设置单元格格式→数字/字体/边框/填充（背景色）→确定

3.销售前3名
先选中E2:E12单元格，打开所示的对话框，输入公式“=E2>LARGE（$E$2:$E$12,4）”

4.位数不对的身份证号码
（1）方法1
1）强制设置文本
2）数据有效性
=OR(LEN(G2)=15,LEN(G2)=18)

（1）通过设定数据有效性，在单元格中无法输入15位以下和18位以上的号码
（2）通过设定条件格式，大于15位且小于18位变成与其他单元格不一样颜色
“=and（LEN（A8）＞15,LEN（A8）＜18）”

5.让符合特殊条件的日期突出显示
希望符合特殊条件的日期所在的单元格突出显示，如星期六或星期天。这时我们可以先选中日期所在的单元格，如A2:A12，然后打开单元格，输入公式“=OR（WEEKDAY（A2,2）=6,WEEKDAY（A2,2）=7）”，然后设置符合条件的单元格填充色为阴影即可

6.让工作表间隔固定行显示阴影
让工作表间隔固定行显示阴影的公式：“=MOD（ROW（）,2）=0”
间隔两行显示阴影则用公式：“=MOD（ROW（）,3）=0”

Excel2003：选定内容→格式→条件格式→条件1（1）→公式→单元格数值/公式→格式→单元格格式→图案/字体/边框

Excel2007：选定内容→开始→条件格式→新建规则/管理规则
→使用公式确定要设置格式的单元格→格式→颜色→设置单元格格式→数字/图案/字体/边框→确定

→=MOD（ROW（）,2）=0

六、这些财务指标算怎样算？
（一）投资回收期
=LOOKUP(0,B9:G9,B3:G3)+(-LOOKUP(LOOKUP(0,B9:G9,B3:G3),B3:G3,B9:G9))/LOOKUP(LOOKUP(0,B9:G9,B3:G3)+1,B3:G3,B4:G4)

（二）现值或净现值
1.Pv函数法

2.NPV函数法
容易出错

3.XNPV函数法

4.列表求解法

（三）内含报酬率（到期收益率/实际利率）
1.Rate函数法

2.Irr函数法

3.Mirr函数法

4.单变量求解法
解一元（n次）方程

Excel2007：数据→假设分析→单变量求解
Excel2003：工具→单变量求解

（四）债券定价和溢折价摊销
1.债券定价

2.溢折价摊销
现有当日按1018万元发行的企业债券，面值1000万元，三年期，票面年利率4%，利息按年支付，到期偿还面值。
发行费用和所得税忽略不计。
问：
1.实际年利率为多少？
2.采用实际年利率法摊销溢折价。

七、贷款偿付计划怎么编？
（一）每次等额还本付息额
1.每次偿付的总金额
Pmt（r，n，pv，fv，t）

2.利息

3.本金

（二）每次等额还本，按期付息
1.每次偿付的总金额

2.利息

3.本金

八、规划求解能够做什么？

（一）通过规划求解合理配置有限资源
（1）Excel2003：工具→规划求解

（2）Excel2007：数据→规划求解

生产甲、乙、丙产品分别耗用a、b、c三种材料，有关资料如下：

甲
乙
丙
材料总量
a
3
2
4
9
b
7
3
5
15
c
5
4
3
12
产品价格
8
7
10

如果企业要求总收入最高，如何合理安排生产？

产量分别为x、y、z
目标函数：
Max＝8x+7y+10z

约束条件：
3x+2y+4z≤9
7x+3y+5z≤15
5x+4y+3z≤12
x、y、z≥0
（1）整理约束条件的表达式
左边：含未知数的表达式
右边：已知数

（2）在Excel 的单元格中输入方程组

B
C
D
E
F
G
H
2
产量
C2?
D2?
E2?

3

x
y
z
材料量

4
a
3
2
4
9
3x+2y+4z
H4**
5
b
7
3
5
15
7x+3y+5z
H5**
6
c
5
4
3
12
5x+4y+3z
H6**
7

8
单价
8
7
10

9
收入
C9**

（3）分别在C9、H4、H5和H6中输入 “=8*C2+7*D2+10*E2”、“=3*C2+2*D2+4*E2”、“=7*C2+3*D2+5*E2”和“=5*C2+4*D2+3*E2”

（4）规划求解

数据→规划求解→规划求解参数
→设置目标单元格
→可变单元格
→约束→添加
→求解

（二）如何通过规划求解分配辅助生产成本
方程组求解
代数分配法
3x+2y+4z=9
7x+3y+5z=15
5x+4y+3z=12
无目标单元格
约束条件为“＝”

九、如何敏锐分析各因素变动的影响？
（一）单变量敏感分析
（1） Excel2003：数据→模拟运算表

（2）Excel2007：数据→假设分析→数据表

（二）双变量敏感分析

（三）方案管理
由于市场的不断变化，企业生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel提供了称为方案管理的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。
（1）Excel2003：工具→方案→方案管理器
（2）Excel2007：数据→假设分析→方案管理器

企业生产甲、乙、丙三种产品，2007年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。根据市场情况推测，2008年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如下：
2007产品销售情况
品名
销售额
销售成本
销售利润
甲
200
120
80
乙
400
280
120
丙
300
160
140
合计
900
560
340
2008产品销售情况
情况
品名
销售额%
销售成本%
好
甲
12%