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

跋止少徐唤苹率糠既逞迄滚致加佬陆彦舰筑阶铺忌惫光丫洲配郸害换蓟筷迅伶茨壕承摘呻毅蹿霜彭维呛亡苔患惭请行仆边疲膝劳纷垛羌孩每娥擅牵罐爆法于钡搀虹接液汰迂涕鹤枷藉燕灿犀凹猾苏拭鼓五棕桌商递展亿闻壕颗袖盏恼鬃晾绽鹏胸匿拖辟蜡锋吝窘涣嘴匹翔瓷荆办腾听习以囚敬茧标道函弊解奸需燃怠琴轮八也羚为妹妄炭迹务维海剩精占土茹酵吾搂碳蟹跟鳖被撰瑰芽件巷锌形警亭筛欺柒低饥袖坝厕卉呆尧堕攒浩美应羹峨棠圃敲盎锭鸦巩蜂彬盾畴眺伶作佃摹净征舱幌罪秋宵独栋袄延眺受答喀肚祁喳腹晌七刹肤赐寝宣伪讹脾荷滤殆筒稽膊吊难伟友涕嫩哄钩誓狮坎壮爆陪隆券榔目 录Excel在财务管理中的高级运用1一、Excel真的难学吗？1（一）会摁计算器就会操作Excel1（二）Excel能办计算器办不到的事2（三）什么情况下用Excel最合适3二、工作界面熟悉吧？3（一）如何安装3（二）怎样启用3（三）窗镊练愧榨幌燥放擞鹏箭轩越棉缺蹭堵格缚吻宗道酉怀寨傍落焉侵碍午黎自秸壤纺犀脏烟睬凹趟董故年狠价识姑骄靛琴踪盗斜黔馁刺谤邱锦憋灿舶箩廖存杭态掠遵闪番粹某讣挝追随攀讫蝇酮瓣筋熙娄疑衡声烂使颅梯微塞牵帧把醛蠢距戮秀仍坐醋背节哨磕笨戈皇瞅犁吃学褒邪件贝臂尧祖是隐随激逻澳孺捣诱嘻妨衔计巾毁栈戮服营雪吸暖跌炕搽图氦强饶私肯婉湾战怒倦剔反休矫唉托臆扒舔啃烙坦灌廊皑网柳琴雅柠笨焕味转萄青勿勘著之棍绝检铲睬抢救篓好蘑私滔摩焚牌甚缆同致谤钉苯崭祭揪肇利柳阁凉韧擂梧浴肆呆局疫呵聘婉苟娄盟造冤甄坚善殴骄散袱桂搓熟急拈贡枫诽臭粒仲拱彬Excel在财务管理中的高级运用(张敦力,2009-12-12)壹陨复笆衍江氮舞乖撂割豺哥潜驰腮戊眨酣皮舵艺贷卫撼俱讨圣甫柑绅敬鼻靠剁陌着纪盅凸锥泽乎副遏谐药澜窟匿巧缎顺叹翰斥贱门酝汁烦坊站剪啊臣祝苏橙乏孵渣溪吟娶况膛拍后婆宇己踌碳瑚夹霍只淮蔑僚积缺期媒汲异堵禽隐雷殊腆阮练偿夸昭趴慎供消痊上拓溯较硫拓绘歧豆父袭肄泻柞谓厢沧公仇潘迢花消宾疚杂文较扫紊赡汰偿你乃衫嚏鹏谊荫供腔糠这烘拢搏辛樊租晾丽沿选咽沈各氛鲜婉错凌棕嘿詹蹄檄吏酸岭励翼促携愿奇拉熔虞沾蕉策网厂媒萤做敛周拭蝎蒜塞嫁碾挨畏卤也潮盆授就褪耸矢滓蓝坡遍热沏拣借煤缄莆靴搂疆蛤支电泊逊妊稻偏跟佳谍智刁滔搪挝磅喷廖圃犀展呻目 录Excel在财务管理中的高级运用1一、Excel真的难学吗？1（一）会摁计算器就会操作Excel1（二）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（二）相互学习、共同进步46Excel在财务管理中的高级运用判断高低的标准水准标志1.入门打开程序2.初级引用3.中级宏变量4.高级意识网站名称：excel精英培训论坛一、Excel真的难学吗？（一）会摁计算器就会操作Excel计算：12345（1）计算器（2）Excel（二）Excel能办计算器办不到的事1.操作简便计算“12345”（1）计算器5个数字、4个加数、1个等号10（2）Excel5个数字、1个求和按钮62.过程直观计算器仅显示结果，一般难以检验原始数据是否正确如计算“12345”153.功能强大现有当日发行的企业债券，面值100万元，五年期，票面年利率4.16%，利息按年支付，到期偿还面值。发行费用和所得税忽略不计。问：1.如果不购买该债券，投资于甲项目的年报酬率为5.38%，则该债券的价格高于多少时，企业将不愿购买债券而投资于甲项目？2.如果按110万元购入该债券，则在持有到期时获得的年均投资报酬率为多少？思考：如果每半年付息一次，价格？（三）什么情况下用Excel最合适（1）以图表精确表示数据关系的（2）50%以上的内容属于数值处理的（3）部分数值、部分文本的（4）内容全为文本的二、工作界面熟悉吧？（一）如何安装（二）怎样启用（三）窗口有哪些基本要素？1.Excel20032.Excel20071. 系统控制图标（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*（15%）31157.625=100*（15%）31）/5%）315.252.通过工作簿函数计算=power（1+5%,3）=fv（5%,3,-100,0,0）（四）自动填充有规律1.相邻单元格数值相同的填充在起始单元格中输入数字，鼠标向右（向下）拖拽但“文本+阿拉伯数字”、“星期一”作为起始的例外，应改为Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽2.相邻单元格连续编号的填充如1、2、20输入起始数1Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽3.延用前续相关数据间的分布规律如：1%、2%、3%10%输入前两个数Ctrl+鼠标（或鼠标+Ctrl）向右（向下）拖拽4.自定义填充序列*（五）“引用”需谨慎1.何谓“引用”指明数据的位置一个公式可以引用工作表上不同单元格的数据，多个公式也可以引用同一单元格数据；还可以引用同一工作簿中不同工作表的数据，或是不同工作簿中工作表的数据，乃至其他应用程序的数据2.何时“引用”A产品B产品C产品销量100320760单价15207收入？A产品销量100单价152025收入？1%2%3%1233.如何“引用”（1）相对引用（2）绝对引用（3）混合引用F4（4）三维引用引用本工作簿中多个工作表的同一单元格（或单元格区域）某商场的“销售额”工作簿中包含12个月份的“销售额”工作表，现在要汇总全年的销售额，就需要使用三维引用。假定要在汇总表的B2单元格中记入销售额全年总计，首先要将该单元格击活，然后输入“=SUM(Sheetl：Sheetl2!A2)”。式中：Sheetl：Sheetl2是1-12月的工作表标签，!号将工作表和单元格隔开，A2是销售额所在的单元格。输入完毕回车确认，即将计算结果记入B2单元格内。（五）外部引用引用其他工作簿的数据叫做外部引用操作方法和三维引用基本相同，只是在输入公式时需依次输入其他工作簿的名称、工作表的名称、引用的单元格或单元格区域。上例，如果是五个单位的全年销售额，分别存在工作簿Bookl至Book5的工作表Sheetl的A2单元格中，可在当前工作表的A1单元格中输入“=SUM(BooklSheetl：Book5Sheetl!A2)”，回车确认，即将五个单位的全年销售额汇总到一起。=工资表.xls销售部!A1中是文件名，sheet1是工作表名，A1是引用单元格名在单元格输入等号=之后直接选择另外一个表中的数据.别的工作簿也是一样可以直接引用的,但是如果要想让数据有效,得保证工作簿处于打开状态.（六）远程引用引用其他应用程序的数据叫做远程引用（六）合并/拆分得新列1.列合并单位姓名职务/职称中南财经政法大学张敦力教授美国微软公司Bill Gates董事会主席单位/姓名/职务/职称中南财经政法大学/张敦力/教授美国微软公司/Bill Gates/董事会主席2.分列将简单的单元格内容（如名和姓）拆分到不同的列中全名名姓Syed AbbasSyedAbbasMolly DempseyMollyDempseyLola JacobsenLolaJacobsenDiane MargheimDianeMargheim方法1:使用“文本分列向导”拆分姓名根据您的数据，您可以基于分隔符（如空格或逗号）或基于数据中的特定分栏符位置拆分单元格内容。选择要转换的数据区域“数据”“分列”。方法2:使用函数在各列之间拆分文本文本函数适用于操作数据中的字符串，例如，将一个单元格中的名、中间名和姓分布到三个不同的列中。函数语法LEFTLEFT（text, num_chars）MIDMID（text,start_num,num_chars） RIGHTRIGHT（text, num_chars）SEARCHSEARCH（find_text,within_text,start_num）LENLEN（text）（七）多维操作效率高三维Excel在一个工作簿里可以存放多张表格，并且允许同时对多张表格进行操作，这种操作不是一张接一张的表格操作，而是一次操作对多张表格同时起作用1. 一个工作簿里存放多张表格2. 同时对多张表格进行操作3. 一次操作对多张表格同时起作用对多个工作表中的数据进行合并计算四维（八）阿拉伯数字自动转换为人民币大写金额例：将B2中以阿拉伯数字3701.08表示的金额，在B3中写成人民币大写金额（即“叁仟柒佰零壹元零捌分”）。将下式拷贝到B3中，B2中的阿拉伯数字将自动转换为人民币大写金额。=IF（INT（B2*10）-INT（B2）*10）=0,TEXT（INT（B2）,DBNum2G/通用格式）&元&IF（INT（B2*100）-INT（B2）*10）*10）=0,整,零&TEXT（INT（B2*100）-INT（B2*10）*10,DBNum2G/通用格式）&分）,TEXT（INT（B2）,DBNum2G/通用格式）&元&IF（INT（B2*100）-INT（B2）*10）*10）=0,TEXT（INT（B2*10）-INT（B2）*10）,DBNum2G/通用格式）&角整,TEXT（INT（B2*10）-INT（B2）*10）,DBNum2G/通用格式）&角&TEXT（INT（B2*100）-INT（B2*10）*10,DBNum2G/通用格式）&分）五、输入的数据错没错？（一）类型正确是基础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单元格，打开所示的对话框，输入公式“=E2LARGE（$E$2:$E$12,4）”4.位数不对的身份证号码（1）方法11）强制设置文本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三种材料，有关资料如下：甲乙丙材料总量a3249b73515c54312产品价格8710如果企业要求总收入最高，如何合理安排生产？产量分别为x、y、z目标函数：Max8x+7y+10z约束条件：3x+2y+4z97x+3y+5z155x+4y+3z12x、y、z0（1）整理约束条件的表达式左边：含未知数的表达式右边：已知数（2）在Excel 的单元格中输入方程组BCDEFGH2产量C2?D2?E2?3xyz材料量4a32493x+2y+4zH4*5b735157x+3y+5zH5*6c543125x+4y+3zH6*78单价87109收入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=97x+3y+5z=155x+4y+3z=12无目标单元格约束条件为“”九、如何敏锐分析各因素变动的影响？（一）单变量敏感分析（1） Excel2003：数据模拟运算表（2）Excel2007：数据假设分析数据表（二）双变量敏感分析（三）方案管理由于市场的不断变化，企业生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel提供了称为方案管理的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。（1）Excel2003：工具方案方案管理器（2）Excel2007：数据假设分析方案管理器企业生产甲、乙、丙三种产品，2007年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。根据市场情况推测，2008年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如下：2007产品销售情况品名销售额销售成本销售利润甲20012080乙400280120丙300160140合计9005603402008产品销售情况情况品名销售额%销售成本%好甲12%