Excel在财务中的应用.ppt

Excel 在财务中的应用,一、编辑录入技巧,小写转换大写 智能ABC 讲义上的三种方法 P32-33 方法一：在三个连续的同行单元格中转换 方法二：用Excel提供的函数转换 方法三：特殊方法实现,一、编辑录入技巧,用特殊符号补齐位数 P33 在账面填充时一般会采用“安全填写法”，即将金额中的空位补齐或款项前加货币符号 REPT LEN 函数 快速填入相同内容 P33 CTRL+ENTER,一、编辑录入技巧,成组填充多张表格的固定单元格 P34 选中多张表格后操作 相同格式:编辑填充至同组工作表 快速复制上单元格 P34 CTRL+,案例,一、编辑录入技巧,分数输入技巧 P34 0 分子/分母 如：输入4/5，则需要输入0 4/5 注：此法输入的分母不能超过99，否则显示会出现错误 输入法快速切换技巧 P34 数据有效性输入法模式,案例,一、编辑录入技巧,信息快速添加技巧 P34 给单元格数据的尾部添加信息，可选中该单元格后按F2 单元格内换行 P34 Alt+Enter,案例,一、编辑录入技巧,在单元格区域内换行 P35 使很长的内容在用户选定的单元格区域内进行排列 编辑填充内容重排 单元格连续录入技巧 P35 逐列录入:选中区域 ENTER 逐行录入:TAB ENTER 工具选项编辑向右,案例,一、编辑录入技巧,建立下拉列表选择数据 P35 通过数据有效性功能，我们能够对单元格内可以输入的数据进行约束 日期录入技巧 P36 输入日期时，只有“/ -”这两个符号被系统认可 输入当前日期：CTRL+;,一、编辑录入技巧,拆分冻结编辑窗格 P36 拆分窗口 在垂直滚动条顶端和水平滚动条右端有拆分按钮，选中后拖动即可进行拆分 冻结窗口 选中某一行（列）时，冻结线出现在该行（列）的上端（左侧） 选中某个单元格时，冻结线以该单元格的左上角为中心出现,案例,二、格式设计技巧,数据整齐化 P36 格式单元格数字自定义 00000 位不足用0补足 #.# 四舍五入显示 ?.? 小数点对齐 文本数字 P37 +数字,案例,二、格式设计技巧,单元格信息隐藏 P37 保护信息不被浏览者查阅 边框、斜线 P37 格式单元格边框 边框工具栏 注：使用边框工具画斜线时，必须将线画到单元格的边或者角,二、格式设计技巧,行高和列宽 P38 同高同宽 最适合的行高列宽 格式刷的使用 P38 多次使用:双击格式刷 条件单元格的设置 P38 格式条件格式,案例,三、EXCEL使用技巧,建立“常用文档”新菜单 P38 视图管理器 P39 使用视图管理器可以解决我们在打印时快速隐藏那些我们不需要的内容，并且不需要像“隐藏行（列）”功能那样每次使用时都进行设置,三、EXCEL使用技巧,按需排序 P39 格式选项自定义序列 注：使用用户的“自定义序列”排序前，必须通过“工具”“选项”“序列”的方式将自定义序列添加到程序中 连字符“&”合并文本 连字符可以将不同单元格内的内容合并到一起，同时，也可配合函数公式一起使用,案例,三、EXCEL使用技巧,同时查看不同工作表中多个单元格内的数据 P40 主要可用于查看改变某个单元格内数据后对引用其数据的公式会产生的影响 视图工具栏监视窗口 熟悉Excel的“从文本到语音” P40 视图工具栏文本到语音,案例,三、EXCEL使用技巧,Excel中的“摄影”功能 P40 Excel中的摄影功能可以提供给用户想截哪儿就截哪儿的截图功能（与PrintScreen功能的全屏截图不同） 视图工具自定义命令类别工具照相机 打印工资条-用函数实现的简便方法 P41,案例,三、EXCEL使用技巧,公式审核 P41 公式审核的功能是，将任一单元格中数据的来源和计算结果的去处显示出来 工具公式审核显示“工具审核”工具栏,案例,三、EXCEL使用技巧,报表的保护 文件级保护 P41 打开权限密码 修改权限密码 只读方式 工作簿内保护 P42 工作簿级保护 工作表级保护 单元格保护 P43,四、函数,地址的引用 汇总计算函数 SUM (参数1，参数2，) 对1至30个数值、区域或引用进行求和 参数中的数字、逻辑值、数字文本表达式均可参加运算，其中逻辑值转换为1或0，数字文本转换成数字 SUMIF(条件数据区, “条件“ ,求和数据区) 根据指定条件对若干单元格、区域或引用进行求和,案例,四、函数,统计函数 COUNT 求数值型数据个数 Count（参数1，参数2，） COUNTIF 对满足条件的区域进行计数 Countif（条件数据区，“条件”）,案例,四、函数,逻辑函数 AND 逻辑与函数:AND(参数1，参数2) 当所有参数都为真时，返回值为1，否则为0 OR 逻辑或函数:OR(参数1，参数2) 当参数中有一个为真时，返回值为1，否则为0,四、函数,条件判断函数及应用 IF 条件函数IF（逻辑表达式，表达式1，表达式2） MOD 求余函数：MOD(被除数，除数): mod(7,4)=3 Mod与条件格式结合使用,案例,四、函数,VLOOKUP函数的实际应用 P45 四舍五入函数 P45 ROUND 财务函数 P45-54 自动筛选、高级筛选,Q&A,智能ABC,智能ABC可以将小写数字直接转换成大写中文 例如：1234567890转换为大写中文 只需输入：shift+i+对应数字,智能ABC,智能ABC也可以将人民币小写金额直接转换成大写格式 例如：将263986转换成大写 只需输入：shift+i+2s6w3q9b8s6,注：智能ABC无法转换小数点后金额,在三个连续的同行单元格中转换,自定义人民币显示格式 格式单元格数字自定义 “类型”编辑框中的内容改为：DBNum2(“人民币”)G/通用格式“元“ 输入公式,案例,用Excel提供的函数转换,转换时会遇到的情况： 个位： 为0：不显示 (70.5) 不为0：显示“XXX元” (82.1) 不为0，且角位和分位均为0：显示“XXX元整”(93) 元角分为均为0：显示“零元整”(0),用Excel提供的函数转换,转换时会遇到的情况： 小数位后面第一位： 为0，分位为0：不显示(71.00) 为0，分位不为0：显示“零”(32.04、30.04) 不为0：显示“X角”(33.15) 小数位后面第二位： 为0：显示“整”(81.5) 不为0：显示“X分”(85.61),用Excel提供的函数转换,公式：,案例,另类实现方法,输入公式： =TEXT(A3*100, “dbnum2(人民币)0佰0拾0万0仟0佰0拾0元0角0分“),案例,REPT LEN 函数,REPT函数：用于按照用户要求的次数填充固定的内容 LEN函数：计算文本长度 使用实例： A2单元格内数据为123，我们需将其补齐为16位的数据，符号使用“#”，从右侧开始补位 则公式为：=A2&REPT(“#“,16-LEN(A2),案例,Ctrl+Enter,选中多个单元格，输入字符，按Ctrl+Enter,案例,建立下拉列表选择数据,数据有效性设置允许(序列)来源(男,女),案例,单元格信息隐藏,为了工作表中的部分内容不让浏览者查阅，我们可以选择将其隐藏 方法： 1、选中要隐藏的单元格（区域） 2、格式单元格数字自定义; 3、“单元格格式”的“保护”标签下选择“隐藏” 4工具保护保护工作表设置密码,建立“常用文档”新菜单,为了能快速打开一些我们常用的文档，我们可以创建一个相应的菜单 方法： 1、工具栏空白处右击鼠标自定义“命令”标签中“类别”下选“新菜单”右侧“命令”框内的“新菜单”拖到菜单栏“更改所选内容”命名 2、“类别”下选择“插入”“命令”下选择“超链接”拖至新菜单中重命名 3、选中该新菜单项右击鼠标“分配超链接”定位到相应的工作簿,案例,建立“常用文档”新菜单,案例,视图管理器,方法： 1、选中要隐藏的行(列)右键隐藏 2、视图视图管理器添加输入视图名 3、将隐藏的行(列)显示出来 4、重复操作 5、打印时打开“视图管理器”选择需要打印的不同视图,案例,文件级保护,文件级保护是最高的工作簿保护机制 给文件加保护口令 每次打开或存取工作簿都必须键入口令，口令最多15个字符 “文件/另存为”“选项”“保存选项”键入口令（多用于文件从未保存过的情况） 或“工具”“选项”“安全性”键入口令（多用于已保存的文件的口令设置） 修改权限口令 指用户是否有权限修改工作簿的内容,案例,文件级保护,只读方式保存和备份文件的生成 以只读方式保存工作簿可以实现： 当多人同时使用某一工作簿时，如有人需要改变内容，那么其他用户应该以只读方式打开该工作簿；日常维护时，将工作簿设为只读方式可以防止无意中的更改 备份功能可以自动创建备份文件，扩展名为.xlk,案例,工作簿内保护,保护工作簿主要是为了禁止删除、移动、重命名或插入工作表，也可禁止执行移动、缩放、隐藏和关闭工作簿窗口等操作 保护工作表的操作： 工具保护保护工作簿 结构：不允许删除、移动、重命名和插入工作表等 窗口：不允许移动、缩放、隐藏和关闭工作簿窗口 输入/放弃输入密码，单击确定,案例,工作簿内保护,保护工作表的目的是不允许对工作表中某些或全部单元格进行修改操作，当然，也可根据需要选择是否允许插入行/列、删除行/列操作等。 设置保护单元格区域的条件： 被保护的单元格区域必须处于“锁定”状态 “工具”“保护”“保护工作表”命令,案例,单元格保护,保护单元格区域： 选定允许修改的单元格格式单元格保护放弃“锁定” 选定不允许修改的单元格格式单元格保护“锁定” “工具”“保护”“保护工作表”命令,案例,地址的引用,Excel单元格地址的引用分为： 相对地址：D2 绝对地址：$D$2 混合地址：$D2、D$2,VLOOKUP,功能:在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据 VLookUp(lookup_value,table_array,col_index_num , range_lookup) lookup_value: 需在数据表第一列中查找的数据 table_array: 需要在其中查找数据的数据表 Col_index_num ：中待返回的匹配值的列序号。 （Col_index_num 为 1 时，返回 table_array 第一列中的数值； Col_index_num 为 2 时，返回 table_array 第二列中的数值，以此类推 ） range_lookup 为 true或省略，则 table_array 的第一列中的数值必须按升序排列；true为可以近似匹配，