利用EXCEL公式制作具有自動日程提醒功能的表格系統(tǒng)

1、利用 EXCEL公式制作具有自動日程提醒功能的表格系統(tǒng)一、 主要實現(xiàn)的功能：這個自動提醒表格能夠根據(jù)當前的系統(tǒng)時間，去核對人員信息表中的人員身份信息，演算出在當天過生日的人員，并可自動地呈現(xiàn)在第一個提示表中，管理人員只須每天打開工作表中即可直接看到重要的提醒。二、 實現(xiàn)的思路二個方面，一是要判別出基礎(chǔ)的人員信息表中有哪些是當天過生日第二個就是要通過查詢機制，將符合條件的信息展示到提示表中三、 制作步驟1、有如下圖所示的人員信息表中，記錄中企業(yè)、單位里各人員的一些詳細信息。2、在身份證號碼字段所在列設(shè)置數(shù)據(jù)輸入有效性規(guī)則，以防止信息的錯誤輸入。如下圖：有效性條件中的“允許”項選擇自定義：由于身份

2、證號碼的長度只有 15 位和 18 位的兩種，設(shè)置的公式如下圖，并同時點選掉“忽略空值”前的勾，以防止漏錄身份證號的情況。3、余下的“出生時間”到“所在行號”都是根據(jù)“身份證號”演算得來出生時間主要是利于文本函數(shù) MID 從身份證號碼里面提取年月日的數(shù)值，并用“/ ”連接成 EXCEL認可的日期格式， 使后面的日期計算能夠得出正確的結(jié)果。IF 函數(shù)在這里的作用是結(jié)合 LEN來讀取身份證是 15 位的還是 18 位，以確定各自的字符提取長度。如下圖 :“年”、“月”、“日”三個字段的公式比較簡單，分別用了 YEAR,MONT和H DAY三個函數(shù)來計算、 、“年齡” 字段運用到了獲取系統(tǒng)當前時間的

3、函數(shù) TODA，Y 計算出當前時間的 4 位年份，與“年”字段值相減即可4、“是否今日生日”字段用來判斷人員的生日與否，采用了判斷函數(shù) AND,當員工的“月”字段值與當前系統(tǒng)月份值 MONTH(TODAY(相) 同，并且“日”字段值與系統(tǒng)日 DAY(TODAY()相同，就可以得出該員工今天是生日， 在單元格值中顯示 “是”。在“是否今日生日”字段用條件格式設(shè)置，當值出現(xiàn)“是”的情況下會給出顏色提示，利于核對。如果需要，也可以根據(jù)顏色來排序，但多數(shù)情況下由于基礎(chǔ)表有太多行記錄，人為排序會給其他記錄的呈現(xiàn)帶來不利，不建議如此操作。5、現(xiàn)在看“所在行號”字段，主要是用來計算出現(xiàn)“是”的單元格所在行號

4、，運用了 ROW函數(shù)， 公式不復(fù)雜， 但卻很關(guān)鍵， 這個值要提供給 前一個字段 “求排名”，也要提供給提示表的公式中作參數(shù)。6、“求排名”位于“所在行號”前面，主要是考慮到它的值要提供給提示表中作VLOOKU的P 查找區(qū)域參數(shù)，進而得到第二列“所在行號”的值。公式主要是運用了求秩序函數(shù) RANK對“所在行號”字段中有值的部分進行排序，看有多個符合條件的記錄，用以得到規(guī)范化的數(shù)據(jù)。7、在人員信息表前再新增一個工作表，命名為“本日生日人員提示” ，復(fù)制表頭字段信息，在 A2 單元中輸入“第 1 個”，向下復(fù)制直到實際業(yè)務(wù)中需要用到的最多個數(shù)，用作查詢的參考值。8、最關(guān)鍵的一步，是要在此表中得到“人員信息表”中相并記錄標記為“是”的行信息。 B2單元格的公式如下圖：IFERROR函數(shù)主要用來展示里面的值是否有錯誤值出現(xiàn)， 如是的話就用空字符”來顯示，不是則顯示查找到的值。這里主要運用到的函數(shù)是 INDEX,查找某個區(qū)域中指定行號和列號相交叉部分的值，行號用 VLOOKU這P 個函數(shù)來得到與 A 列“第 N個”相同的值在“人員信息表”中“所在行號”字段的對應(yīng)值。由于這里采取了相同的列字段，列