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

1、利用EXCEL公式制作具有自動日程提醒功能的表格系統(tǒng)一、 主要實現(xiàn)的功能：這個自動提醒表格能夠根據(jù)當(dāng)前的系統(tǒng)時間，去核對人員信息表中的人員身份信息，演算出在當(dāng)天過生日的人員，并可自動地呈現(xiàn)在第一個提示表中，管理人員只須每天打開工作表中即可直接看到重要的提醒。二、 實現(xiàn)的思路二個方面，一是要判別出基礎(chǔ)的人員信息表中有哪些是當(dāng)天過生日第二個就是要通過查詢機制，將符合條件的信息展示到提示表中三、 制作步驟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,MONTH和DAY三個函數(shù)來計算、“年齡”字段運用到了獲取系統(tǒng)當(dāng)前時間的函數(shù)TODAY，計算出當(dāng)前時間的4位年份，與

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

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