利用EXCEL公式制作具有自动日程提醒功能的表格系统

利用利用 EXCELEXCEL 公式制作具有自动日程提醒功能的表格系统公式制作具有自动日程提醒功能的表格系统 一 一 主要实现的功能 主要实现的功能 这个自动提醒表格能够根据当前的系统时间 去核对人员信息表中的人 员身份信息 演算出在当天过生日的人员 并可自动地呈现在第一个提示表 中 管理人员只须每天打开工作表中即可直接看到重要的提醒 二 二 实现的思路实现的思路 二个方面 一是要判别出基础的人员信息表中有哪些是当天过生 日 第二个就是要通过查询机制 将符合条件的信息展示到提示表中 三 三 制作步骤制作步骤 1 有如下图所示的人员信息表中 记录中企业 单位里各人员的 一些详细信息 2 在身份证号码字段所在列设置数据输入有效性规则 以防止信息的错误输入 如下图 有效性条件中的 允许 项选择自定义 由于身份证号码的长度只有 15 位和 18 位的两种 设置的公式如下图 并同时点 选掉 忽略空值 前的勾 以防止漏录身份证号的情况 3 余下的 出生时间 到 所在行号 都是根据 身份证号 演算得来 出生时间主要是利于文本函数 MID 从身份证号码里面提取年月日的数值 并用 连接成 EXCEL 认可的日期格式 使后面的日期计算能够得出正确的结果 IF 函数在这里的作用是结合 LEN 来读取身份证是 15 位的还是 18 位 以确定各自 的字符提取长度 如下图 年 月 日 三个字段的公式比较简单 分别用了 YEAR MONTH 和 DAY 三 个函数来计算 年龄 字段运用到了获取系统当前时间的函数 TODAY 计算出当前时间的 4 位 年份 与 年 字段值相减即可 4 是否今日生日 字段用来判断人员的生日与否 采用了判断函数 AND 当员工 的 月 字段值与当前系统月份值 MONTH TODAY 相同 并且 日 字段值与 系统日 DAY TODAY 相同 就可以得出该员工今天是生日 在单元格值中显示 是 在 是否今日生日 字段用条件格式设置 当值出现 是 的情况下会给出颜色 提示 利于核对 如果需要 也可以根据颜色来排序 但多数情况下由于基础表 有太多行记录 人为排序会给其他记录的呈现带来不利 不建议如此操作 5 现在看 所在行号 字段 主要是用来计算出现 是 的单元格所在行号 运用了 ROW 函数 公式不复杂 但却很关键 这个值要提供给前一个字段前一个字段 求 排名 也要提供给提示表的公式中作参数 6 求排名 位于 所在行号 前面 主要是考虑到它的值要提供给提示表中作 VLOOKUP 的查找区域参数 进而得到第二列 所在行号 的值 公式主要是运用了求秩序函数 RANK 对 所在行号 字段中有值的部分进行排序 看有多个符合条件的记录 用以得到规范化的数据 7 在人员信息表前再新增一个工作表 命名为 本日生日人员提示 复制表头 字段信息 在 A2 单元中输入 第 1 个 向下复制直到实际业务中需要用到的最 多个数 用作查询的参考值 8 最关键的一步 是要在此表中得到 人员信息表 中相并记录标记为 是 的行信息 B2 单元格的公式如下图 IFERROR 函数主要用来展示里面的值是否有错误值出现 如是的话就用空字符 来显示 不是则显示查找到的值 这里主要运用到的函数是 INDEX 查找某个区域中指定行号和列号相交叉部分的值 行号用 VLOOKUP 这个函数来得到与 A 列 第 N 个 相同的值在 人员信息表 中 所在行号 字段的对应值 由于这里采取了相同的列字