函数高级应用技巧展现.ppt

Office职场应用书库 Excel2010实用技巧集锦 720招 函数高级应用技巧展现 日期和时间函数技巧展现查找和引用函数技巧展现账务函数和文本函数技巧展现统计函数技巧展现嵌套函数技巧展现 将日期转换为星期 具体的操作方法如下 打开工作表将 时间 列中的日期转换为星期 选中单元格I3 在编辑栏中输入公式 TEXT WEEKDAY H3 aaaa 即可将单元格H3中的日期转换为星期 中文 如图7 1所示 拖动填充柄填充公式 即可将 时间 列中的所有日期转换为星期 如图7 2所示 将日期转换为星期 具体的操作方法如下 上述方法可以得到中文的星期 若要将日期转换为英文的星期 需在单元格I3中输入公式 TEXT WEEKDAY H3 dddd 如图7 3所示 计算员工年龄 具体的操作方法如下 步骤1 选中单元格K4 在编辑栏中输入公式 YEAR TODAY J4 1900 如图7 10所示 单击 开始 选项卡下 数字 组中的 数字格式 下拉按钮 在其下拉列表中选择 常规 选项 即可使单元格K4中的值显示为年龄 如图7 11所示 步骤2 拖动填充柄填充公式 即可计算出所有员工的年龄 如图7 12所示 计算跨年份两个日期之间的月数 具体的操作方法如下 步骤1 在单元格L4中输入公 YEAR K4 YEAR J4 12 MONTH K4 MONTH J4 即可计算出 参加工作时间 和 辞职时间 之间的月数 如图7 16所示 步骤2 拖动填充柄填充公式 即可得到所有员工的工作月数 如图7 17所示 使用函数巧查询 具体的操作方法如下 步骤1 打开工作表 若要查找 编号 为7的员工的职务 在工作表中输入相应数据 在单元格C20中输入公式 VLOOKUP B20 B4 J17 6 FALSE 如图7 19所示 步骤2 按下 Enter 键 即可在单元格C20中显示 编号 为7的员工对应职务 如图7 20所示 快速定位到空单元格 具体的操作方法如下 步骤1 打开工作表并快速定位到C列的空单元格中 先单击 公式 选项卡下 定义的名称 组中的 定义名称 按钮 即可打开 新建名称 对话框 在 名称 文本框中输入 空单元格 在 引用位置 文本框中输入公式 OFFSET C 1 COUNTA B B 1 如图7 40所示 快速定位到空单元格 具体的操作方法如下 步骤2 单击 确定 按钮关闭对话框 在单元格C12中输入公式 HYPERLINK 空单元格 快速定位 即可在单元格C12中显示链接文字 快速定位 如图7 41所示 步骤3 单击单元格C12中的 快速定位 文本 即可定位到C列中的空单元格中 如图7 42所示 使用FV 函数计算投资现值 具体的操作方法如下 步骤1 打开下面的工作表 要计算不同投资项目 定期定额支付且利率固定 的收回金额 在单元格E3中输入公式 FV C3 12 B3 D3 如图7 54所示 步骤2 按下 Enter 键 即可计算出该投资项目的收回金额 填充公式 得到不同投资项目的收回金额 如图7 55所示 使用RATE 函数计算年收益率 具体的操作方法如下 步骤1 在工作表中输入以下数据 要计算该保险的收益率 可在单元格D17中输入公式 RATE 如图7 75所示 步骤2 打开 函数参数 对话框 在其中设置函数的各项参数 其中参数Fv的值忽略 表示最后一次年底返还后账面上的现金余额为零 如图7 76所示 步骤3 单击 确定 按钮 即可计算出该保险的年收益率 如图7 77所示 该收益率高于银行的存款利息 说明这种保险有利可图 可以购买 使用NPER 函数计算投资期数 具体的操作方法如下 步骤1 在工作表中输入银行利率 现有存款 每月存款和未来存款 要计算存款时间 在单元格D8中输入公式 NPER 再在 函数参数 对话框中设置函数的各项参数 如图7 78所示 使用NPER 函数计算投资期数 具体的操作方法如下 步骤2 单击 确定 按钮 双击单元格D8中的计算结果 将显示的公式更改为 NPER D3 12 D5 D4 D6 1 12 若不除以12 则得到的存款时间是以月计算 然后按下 Enter 键 即可计算出存款时间 如图7 79所示 统计单词个数 利用函数统计英文单词个数的方法如下 步骤1 在工作表中输入几个英文句子 要统计句子中单词的个数 可在单元格B19中输入公式 LEN A19 LEN SUBSTITUTE A19 1 如图7 101所示 步骤2 按下 Enter 键 统计出单元格A19中单词的个数 拖动填充柄填充公式 即可计算出其他英文句子中单词的个数 如图7 102所示 将手机号码的后4位替换为特定符号 具体的操作方法如下 步骤1 打开下面的工作表 添加 屏蔽后的号码 列 在单元格C3中输入公式 REPLACE B3 8 4 按 Enter 键 即可得到公式结果 如图7 105所示 步骤2 拖动填充柄填充公式 即可得到其他屏蔽后的手机号码 如图7 106所示 快速为员工年终评估打分 具体的操作方法如下 步骤1 打开 年度考核表 若根据员工的 出勤量 工作态度 工作能力 为员工年终评估打分 在单元格G3中输入公式 TRIMMEAN D3 F20 2 3 如图7 164所示 步骤2 按下 Enter 键 即可得到公式的结果 拖动填充柄填充公式 即可求得各员工的年终评估的最后得分 如图7 165所示 使用公式制作九九乘法表 具体的操作方法如下 步骤1 在一个空白工作表第1行和第1列中分别输入1 9数字 根据乘法表特点填充单元格区域 在单元格B2中输入公式 B 1 A2 B 1 A2 如图7 168所示 步骤2 按下 Enter 键 即可得到公式的结果 选中单元格B2 复制公式 在按 Ctrl 键时选择单元格C3 D4 E5 F6 G7 H8 I9 J10 如图7 169所示 使用公式制作九九乘法表 具体的操作方法如下 步骤3 单击 开始 选项卡