Excel在财务中的实用技巧.ppt

Excel在财务中的实用技巧 Excel在财务中的实用技巧 一 基本概述二 Excel的常用快捷键介绍三 Excel常用公式和函数的使用四 数据透视表的建立和应用 一 基本概述 1 1Excel的初始设置 Excel选项 精度的设置 快速访问栏的设置保存 选择性粘贴为数值 选取可见单元格 1 2 工作簿和工作表的运用 1 在屏幕上同时看到一个工作簿的多个工作表 比如sheet1和sheet2 只需打开该工作簿并且显示其中一个工作表sheet1 然后 选择 视图 新建窗口 单击新建窗口中的sheet2 然后选择 窗口 重排窗口 选择 水平平铺 选项2 选取工作范围在工作簿里 如果要选择几片不相连的区域或单元格 可以按住Ctrl键 再选择单个或多个单元格即可选定所需的工作范围 同样适用于工作表间的选定 3 拆分和冻结工作表 1 3数据的快速填充及有限性 Ctrl Enter在多个单元格中输入相同的数据 只需要在最后一个单元格输入数据 复制后同时按下Ctrl Enter键即可 同样也适用于某一区域内的相同的公式填充 F4键重复上一次操作 例如 要将多个不连续的单元格改为填充绿色 可以选择第一个单元格 设置填充绿色 然后依次选择其余的单元格 每次按一下F4 1 3数据的快速填充及有限性 数据的有限性 限制数值的可输入位数对于固定位数的数据 例如订单号 身份证号 手机号等 可以设置该行的数据有限性 防止输入的数字少一位或者多一位 选择 数据 有效性 菜单 打开 数据有效性 对话框 在 设置 选项卡的有效条件 允许 处选择 文本长度 在出现的 数据 下面的方框中输入数字 再选择 出错警告 选项卡并设置一个 出错警告 确定返回 这样 但输入的号码位数不正确时 系统会给出提示 限定输入值的范围 圈出指定数据实例描述 想把本公司员工考勤中属于全勤的员工在Excel表格中用红圈突出显示出来 以方便查阅核对 首先 选中表格中考勤天数所在的单元格 如C2 C62 选择 数据 有效性 菜单 在弹出的 数据有效性 对话框中 在 设置 选项卡的有效条件 允许 处选择 小数 在数据处选择 介于 然后在下面分别输入最小值 1 和最大值 21 5 假设当月全勤是22天 点击确定 然后 单击 圈释无效数据 按钮 这样考勤天数绩在21天以上的就被圈上了一个红圈 起到了突出显示的作用 二 Excel常用快捷键介绍 Shift F11新建空白的工作表 sheet1 SHIFT 空格键选定当前单元格所在的整行Ctrl N新建工作簿 book1 Ctrl 1设置单元格格式Ctrl 2加粗Alt enter一个单元格里输入两行数据 换行 Alt 分号 定位于可见单元格 在筛选的状态下删除数据很方便 Ctrl 减号 删除某一行或某一列 先选择后按快捷键Ctrl 移动到当前数据区域的边缘Ctrl X剪切选定的单元格Ctrl Z撤销上一次操作Ctrl A选定整张工作表 Ctrl PageDown移动到工作簿中的下一张工作表Ctrl PageUp移动到工作簿中的上一张工作表Ctrl shift L筛选 再用一次取消筛选其他常用快捷键 Windows D显示桌面Windows E打开 计算机 Windows F在 计算机 里搜索ALT Tab切换不同窗口 三 Excel常用公式与函数的使用 1 公式的引用 F4键 1 相对引用 2 绝对引用 3 混合引用2 运算符 1 算术运算符 2 比较运算符 3 文本运算符 4 引用运算符 3 函数的使用 3 1数学与三角函数Sum number1 number2 求和Round number 小数位数 将参数引用的数舍入到指定的小数位数如 round 123 456 1 3 2统计函数Average number1 number2 求平均值Count value1 value2 返回参数中数字的个数3 3文本函数Concatenate text1 text2 返回将给出的几个字符串合并的一个字符串Value text 将以文本形式输入的数字转换成数值 Len text 返回输入项中的字符个数 文字或者数值的长度 3 5查找及引用函数 Vlookup 查找所构造的表格中存放的信息Vlookup lookup value table array col index num range lookup lookup value需要以它为索引的数值table array查找范围col index num查找值所在列号range lookup查找时是精确匹配还是近似匹配 0 精确匹配 1 近似匹配 3 6财务中常用的公式函数运用 1 个税计算公式ROUND MAX A1 3500 0 03 0 1 0 2 0 25 0 3 0 35 0 45 0 105 555 1005 2755 5505 13505 0 2 A1为应发工资扣除五险一金后的工资余额2 隐藏身份证信息 电话信息具体方式如下 第一步 先将身份证信息输入到sheet1的单元格A1中 第二步 在sheet2中A1单元格输入下面公式 隐藏中间四位 CONCATENATE LEFT A1 7 REPT 1 RIGHT A1 7 隐藏前四位 CONCATENATE REPT 1 RIGHT A1 14 隐藏后四位 CONCATENATE LEFT A1 14 REPT 1 第三步 将sheet1隐藏 选中此表点击右键进行操作即可 第四 避免在数据清单中出现合并单元格 第五 避免在单元格的开始和末尾输入空格 第六 尽量避免在一张工作表中建立多个数据清单 每张工作表最好仅使用一个数据清单 第七 工作表的数据清单应与其他数据之间至少留出一个空列和一个空行 以便于检测和选定数据清单 在制作数据透视表之前 应该按照以上7点来检查数据区域 如果不满足上面的要求 需要先进行整理工作表数据从而使之规范