工资表的常用公式

excel 中的一些常用计算公式 一 年龄计算公式 IF YEAR E2 2008 MONTH E2 8 0 2008 YEAR E2 IF MONTH E2 8 0 1 其中 e2 为单元格 2008 为当前年份 二 身份证号中提取出生年月 MID J11 7 4 2 15 位身份证号从第 7 位到第 12 位是出生年月日 年份用的是 2 位数 18 位身份证号从第 7 位到第 14 位是出生的年月日 年份用的是 4 位数 从身份证号码中提取出表示出生年 月 日的数字 用文本函数 MID 可以达到目的 MID 从指定位置开始提取指定个数的字符 从左向右 对一个身份证号码是 15 位或是 18 位进行判断 用逻辑判断函数 IF 和字符个数计算 函数 LEN 辅助使用可以完成 综合上述分析 可以通过下述操作 完成形如 1978 12 24 样式的出生年月日自动提取 假如身份证号数据在 A1 单元格 在 B1 单元格中编辑公式 IF LEN A1 15 MID A1 7 2 方法二 这是根据身份证号码 15 位和 18 位通用 自动提取性别和出生年月的自编公式 供 需要的网友参考 说明 公式中的 B2 是身份证号 1 根据身份证号码求性别 IF LEN B2 15 IF MOD VALUE RIGHT B2 3 2 0 女 男 IF LEN B2 18 IF MOD VALUE MID B2 15 3 2 0 女 男 身份证错 2 根据身份证号码求出生年月 IF LEN B2 15 CONCATENATE 19 MID B2 7 2 MID B2 9 2 IF LEN B2 18 CONC ATENATE MID B2 7 4 MID B2 11 2 身份证错 3 根据身份证号码求年龄 IF LEN B2 15 year now 1900 VALUE MID B2 7 2 if LEN B2 18 year now VALUE MID B2 7 4 身份证错 在 Excel 电子表格中 我们需要到达某一单元格 一般是使用鼠标拖动滚动条来进行 但 如果数据范围超出一屏幕显示范围或数据行数非常多时 想快速定位到某一单元格可要有 点麻烦了 这时候我们可以使用 定位 快捷键 Ctrl G 功能迅速到达想要的单元格 定位是一种选定单元格的方式 主要用来选定 位置相对无规则但条件有规则的单元 格或区域 例 1 需要选中 Y2009 单元格 或快速移动到 Y2009 单元格 我们可以使用 编辑 定位 菜单 在引用位置里输入 Y2009 后按回车即可 例 2 需要选中 Y 列的 2004 2009 行的单元格 我们按照相同的方法 在引用位置里 输入 Y2004 Y2009 按回车即可 例 3 需要选中 2009 行的单元格 我们可以在引用位置里输入 2009 2009 按回车即 可 例 4 需要选中 2004 2009 行的单元格 我们可以在引用位置里输入 2004 2009 按 回车即可 这是 定位 功能中最简单的 引用位置 下面来看看 定位 的几个应用 定位条件 应用一 定位 公式 在实际工作中 我们会设许多公式 为防止一不小心修改了这些公式 我们会把公式 单元格字体设置为其他颜色 一个个去设置当然不符合高效办公的原则 定位就算帮上大 忙了 操作步骤如下 1 编辑 定位 定位条件 2 选择 公式 确定 所有的公式单元格被选中 3 格式 单元格 字体 颜色 红色 确定 应用二 定位 空值 定位 常量 在实际工作中 我们会为一个表格每隔一个固定的行数 本例中以一行为例 后面插 入一行 一行一行的插入当然也不符合高效办公的原则 二次定位就能解决此问题 操作 步骤如下 1 在表格第一列前面插入一列做为过渡列 在 A1 处输入 1 A2 位置为空 选中 A A2 当鼠标移到右下角变为 形状时 向下拉 拉的比数据行多 2 选中第一列 定位 定位条件 空值 确定 此时你会发现第一列的 偶数单元格全部被选中 3 插入 行 4 选中第一列 定位 定位条件 常量 确定 此时你会发现第一列的 有数值的单元格全部被选中 5 插入 行 这时你会发现除了过渡列的数据外 其他数据行每行下面都多出了一个空白行 这时 我们只需删除这个过渡列 预想的目的就达到了 应用三 定位 最后一个单元格 大的表格中 使用此功能 实现快速移动光标到 最后一个单元格 应用四 定位 可见单元格 在分类汇总下 选定我们需要的单元格后 选择 复制 按钮 到指定位置后 选择 粘贴 按钮 粘贴的结果不是显示的结果 而依然是原始数据 这是因为我们在选定的 时候 不仅选定了显示的单元格 而且隐藏的单元格也被选中了 我们使用 定位 功能 就能很好的解决这个问题 1 定位 定位条件 可见单元格 2 编辑 复制 3 到指定位置后 编辑 粘贴 4 选中粘贴后的数据 然后 数据 分类汇总 全部删除 我们要的可见单 元格即被成功的复制出来了 通过这几个例子 我们不难明白 定位 是选中单元格的一种方法 只不过它选中的 这些单元格区域不集中 用传统的鼠标选择效率低难度大 这些单元格虽然分散但本身具 有共性或分散的有规律 这些共性或规律就是定位条件窗口下的选择条件 下面来看一个关于定位空值的小 flash 教程 然后看一个定位空值的具体活用 设计工资条 再看一个活用定位 公式 禁止编辑已设定的公式的方法 当 EXCEL 中含有大量公式时 不仅但心自己或他人修改了这些公式 而且不想让他 人看见这些公式 而其他无公式单元格允许随意编辑 1 选中表格 当然也可以选中整张工作表 2 选择 格式 菜单的 单元格 功能的 保护 选项 然后取消 锁定 3 再打开 编辑 菜单的 定位 功能 或者直接按 Ctrl G 4 在弹出的对话框中选择 定位条件 5 选择 公式 然后点击 确定 6 此时 将看到所有带有公式的单元格都被选中了 7 再次打开 格式 菜单的 单元格 功能的 保护 选项 然后将 锁定 和 隐藏 全都选中 8 由于只有在工作表被保护的状态下 保护单元格和隐藏公式才能生效 因此