最全EXCLE公式大全

我想很多人都会遇到我曾经遇到过的问题 我总结了一些小技巧 和大家分享 1 双击的妙用 A 对某一选定的单元格 依次双击该单元格的四个边 上下左右 可以迅速转到 有连续数据的顶部 底部 最左和最右 等同功能健为 Ctrl B 输出的数据一般情况都会有单元格宽度不够的情况 要快速使所有的列数据都 能完整地显示 可以做以下操作 选定需要调整的列 比如选定 A 列到 G 列 在已选定区 域的任意一列的右边沿双击 C 公式纵向填充 如 F2 D2 E2 需要将公式从 F2 填充到 F20 在 F2 单元格输 入公式后 选定该单元格 鼠标指针停在该单元格的右下角时指针显示为一个小的十字 双击 等同功能的操作有 选定 F2 F20 在 F2 输入公式后按 Ctrl Enter 则公式往下填 充 在 F2 F20 区间的任意一格输入公式 都一样可以实现填充 另一等同功能健为 选定 F2 F20 在 F2 输入公式后如果不小心敲了 Enter 光标停留在 F3 此时用 Ctrl Enter 不能实现向下填充 此时按 Ctrl D 一样可以实现公式快速填充 D 不用双击 如果需要改一个单元格的数据 一般情况都会双击该单元格转入编 辑状态 或者将鼠标直接放在编辑栏的单元格内容处后修改 快捷健为 F2 停留在需要修 改的单元格 按 F2 后直接可以转入编辑状态 2 快速选定打印区域 设置打印 A 范围的选定 光标停留在需要打印范围的左上角 先 Ctrl Shift 再 Ctrl Shift 需要调整则用 Shift 来增加没有文字区域的行或列 B 将该范围定义为打印区域 连续按 ALT F T S 免用鼠标 C 设置打印页数和自动调整比例 打印预览 设置 页面设置 设置为 1 页宽 页高 页边距 水平居中 D 将表头设置为每页打印 Alt F U 工作表 顶端标题行选表头的行标识 E 宽度超宽的 Excel 文档的打印设置 可能遇到需将左边两列设置为固定格式的情 况 可以在页面设置中将顶端行和左边列同时设定 将文档定义为多页宽 一页高 就可 以打印出漂亮的宽文档了 小知识 如果数据区域中有公式得出得结果为 N A DIV 0 等信息 可以在页 面设置的时候将错误信息定义为 或者 N A 另一种处理方法是 用组合函数判断 如果出现错误信息则用零或者表示 IF ISERROR 公式 true 0 公式 3 U8 数据导出 EXCEL 后不能运算 一些工资软件导出数据后不能自动重算 U8 数据导出 EXCEL 表后格式为文本 切换成编辑状态后数据格式自动转换为数据型 简 单的处理方法是利用 查找 替换 将所有的小数点替换成为小数点 当数据是整数则需要 通用整列乘 1 或者整列加 0 的方式 将计算的结果选择性粘贴成为数据就 OK 了 从朗新工资系统导出的数据出现不能自动重算的情况 此时需要在 工具 选项 重新计算 选择 自动重算 之后我们就可以设公式了 4 如何美化数据透视表获得的表格 刚开始使用数据透视表时 对其强大的功能我叹为用止 而它的格式却不能随心所欲 解 决苦恼的方法当时用粘贴成为文本后再美化 后来学会了高招 与您共享 行字段每增加 一个项目会出现一个汇总 如果有些小计不需要 则可以选择改单元格 右键 字段设置 下方有 自动 自定义 无 选择 无 则同一类型的 小计 都会消失 在列字段上会出现 求和项 的格式 我不喜欢这样的 我想将 求和项 去掉 可以将求和项替换为 必须为 不能替换为空 否则透视表会提示数据表里字段已经存在 以上的快捷键为 选择其中一个将 求和项 ctrl x 按键 CTRL A 选定整个工作表 ctrl H 查找 替换 ctrl v 粘贴 求和项 按 TAB 键换到 替换为 按 alt a 全部替 换 你用熟悉了会觉得这些快捷键给你节省 2 3 的时间 5 巧妙将数字转换成为货币大写假设 F5 单元格数值为 18 21 按照以下步骤你可以成功将它转化为货币金额大写 在单元格 E6 输入公式 int F5 设置单元格 E6 在 数字 下选择 特殊 中文大写数字 设置单元格 E6 在 数字 下选择 自定义 DBNum2 804 G 通用格式 在后加 圆 即 DBNum2 804 G 通用格式圆 上方显示 壹拾捌圆 在 F6 单元格录入 F5 E6 100 得到值 21 设置单元格 F6 第一步设置同 E6 设置单元格 F6 在 数字 下选择 自定义 修改为 DBNum2 804 0 角 0 分 上方 显示贰角壹分 6 计算按揭买房 我不知道怎么将 EXCEL 上传 只好将数据和公式列出类罗 项目 调整 数据 1 单元格 银行利率 0 0558 E4 面 积 126 00 E5 单 价 2 600 00 E6 手 续 费 20 000 00 E7 年 限 10 E8 首 期 90 000 00 E9 总 房 价 347 600 00 E10 每月付款 2 805 86 E11 其中 付银行利息 79 103 12 E12 E12 PMT E4 12 E8 12 E10 E9 0 7 如何计算员工上班的月份数 计算年终奖的时候都会遇到要计算员工当年出勤的月份数 许多公司规定 入厂在 15 号以 后则当月不计算 在 15 号前进厂则当月计算出勤月份 说明 DATEDIF 开始日期 结束日期 单位 函数用于计算两个日期相差的月份 公 式 DATEDIF B5 C 2 B 2 IF DAY B5 16 1 0 IF DAY B5 16 0 1 函数用于识别进厂日期是否在入厂月的 15 号前 15 号前当月 计算一个月 否则不算 如果超过 12 个月 员工进厂日期在以前年份 需要用 IF DATEDIF B5 C 2 B 2 IF DAY B5 12 12 DATEDIF B5 C 2 B 2 IF DAY B5 16 1 0 将出勤月份大于 12 个月的调整为 12 个月 8 常用函数 sum average rank search iserror if vlookup 8 1 sum 函数是一个求和函数 可以对一个区域 一列 一行进行求和 如 SUM A2 B8 SUM A2 A8 SUM A2 G2 也可以与其它函数组合起来用 如 SUM LARGE A2 A8 1 2 3 4 即对 A2 A8 中最大的 4 个数求和 注意事项 如果数据区域中有文本型字段 sum 函数不能对文本进行求和 必须将文本转 换为数值才可以用函数 实践中有人遇到用 号连加得到的结果与用 sum 函数求和结果不 一致的情况就是因为数据区域有文本型单元格造成 使用技巧 如果要对 A2 A8 进行求和 鼠标停留在 A9 单元格 按 ALT 可以快速录入 求和函数 将 A2 A8 区域选定或者将 A2 A9 区域选定按 ALT 快捷键也可以实现 直接 按工具栏的 当然也是一种快捷方式 8 2 average 求平均值函数 函数功能 返回参数的平均值 算术平均值 选定区域可以是用逗号隔开的 30 个以内的数值 也可以是一个数据区域 注意 如果数组或引用参数包含文本 逻辑值或空白单元格 则这些值将被忽略 但包含 零值的单元格将计算在内 8 3 rank 排序函数 函数功能 返回一个数字 单元格数值在多个数据中或者数字列表中的排位 数字 单元格 数值的排位是该数字在多个数据或者数字列表中排名 次序 使用及技巧 RANK number ref order number 也可以是活动单元格 如将 b2 b40 的数 据进行排名 则公式为 rank b2 b 2 b 40 0 如果是升序排列则 rank b2 b 2 b 40 也是一样的效果 技巧 快速将选定区域转换为绝对引用 选定 b2 b40 区域 按 F4 键 可变为绝对引用 每多按一次会有不同 别忘了 ctrl 向下键一起使用哟 8 4 VLOOKUP 函数 财务人员非学不可的函数 在表格数组的首列查找指定列的值 并由此返回表格数组满足行条件的指定列的值 非 常不好描述 即使看不懂也没关系 假设有一张成绩表 第一列为学号 第二列为姓名 第三列之后为各科成绩 第二行开始 有学生的成绩信息 信息区域为 A2 A41 我需要找出指定学号 放置在一个新工作表的 A2 A10 的几个人的数学成绩 假设在第 5 列 语法 VLOOKUP A2 成绩表的数据区域 5 0 在新工作薄 b2 录入 vlookup A2 成绩表 A 2 A 41 5 0 假设 A2 学号为 S008 函数 会将成绩表里的 S008 找到并将第 5 列的数学成绩返回 9 1 替换公式中的一部分 财务人员和文员每月都要做一些相同的报表 每月都需要从不同的基础数据入手 需 要花费较多的时间和精力 技巧 将公式中的一部分替换成为另外的字符串 就可以轻松完成公式 链接 的更新了 9 2 快速输入日期 时间 快速输入日期可用 ctrl 快速输入当前时间可用 ctrl shift 9 3 打开文件提示的日期为当天 时间 在单元格录入 today 则显示为当天的日期 在单元个录入 now 则显示当 天的日期和时间 9 4 需要删除字符串中的 空 格 可能遇到的情况 录入的姓名中间有 或者字符串中的多余 影响数据的查找和透视等等 解决方法 1 用 substitute 函数实现 解决方法 2 ctrl a ctrl h 查找 替换为 空 在查找栏录入一个 在 替换为 栏 不用录 入任何字符 alt a 完成全部替换 几秒钟搞定 9 5 EXCEL 表格数据的横竖互换 转置 选定表格需要互换的区域 选择性粘贴 选择 转置 即可完成横竖互换 9 6 选定数据区域的可见单元格 有时候在筛选出来所需要的数据之后 copy 到另外一个表里不需要的 已隐藏的行 列又出 现了 有一个快捷的方式 在选择你想 copy 的区域的时候先按 alt 键 即实现了选定可 见区域 再 copy ctrl c 到另外的表页 ctrl v 得到的数据就不会有你不想要的数据了 10 ROUND 四舍五入与以显示值为准的用法 排除计算出现的误差 问题 用 EXCEL 制作的工资表和其它一些表格往往会涉及到公式计算的结果 由于计算 的结果一般显示为两位小数 而将单个数据相加与合计数相差 1 2 分 对策 1 凡是涉及公式计算的数据 用 round 函数将其四舍五入 如函数 round A2 B2 2 就是将 A2 B2 的结果保留两位小数 对策 2 将工作标设定为以显示值为准 即数据后面的小数位没有显示的系统默认为零 ALT T 工具 O 选项 重新计算 工作薄选项 以显示值为准 以显示精度为准 高级版本 excel 的术语 建议使用 round 函数 11 如何处理日期型数据不方便查询的问题 问题 想通过日期列来筛选出来某个月内的数据 对策 1 选定日期数据 点击工具栏的逗号 转换成为数值 可要记住你想要选择的起止 日期转换成的数值 然后按照数值来筛选就可以了 对策 2 插入一列 通过 year month 将日期字段转换成 年 月 格式 即可执行筛选 12 ALT 相关的快捷键用法一二 有很多快捷键需要用到 ALT 进行组合 如我们执行工具栏的编辑可用 ALT E 要打开工 具用 ALT O 现在介绍两个绝对用的上用法 用法 1 插入一张新的工作表 ALT SHIFT F1 或者 ALT F9 用法 2 将一块数据移到另外一个已经打开的表页或者工作薄 用鼠标选定了需要移动的 区域 按住 ALT 键 鼠标可以将该区域拖到你想要去的地方 用法 3 参考 9 6 选定数据区域的可见单元格 13 CTRL 相关的快捷键用法一二 用法 1 将上方公式的值复制成为数值 CTRL SHIFT 双引号 类似功能键 CTRL C ALT E S ALT V 用法 2 将显示公式切换为显示数值 CTRL 单引号 类似功能键 选定公式部分 按 F9 14 快速