培培訓訓成成績績統統計計分分析析表表 IFIF函函數數 函函數數用用途途 此函數又稱條件函數 改函數用于對目標區域進行判斷 返回真假邏輯值 據此輸出相應的結果 函函數數語語法法 IF logical test value if true value if false IF 條件表達式 看也是一個比較式或邏輯式 當條件表達式的邏輯值 為TRUE時的返回值 當條件表達式的邏輯值為FALSE是的返回值 函函數數說說明明 利用IF函數可以構造IF函數嵌套來完成復雜的功能 但是IF函數最多只能 嵌套7層 IF函數還可以喝數組相結合 借此能夠進行工作所需的計算 函函數數簡簡單單示示例例 現假設在A1單元格里輸入數字15 在B1單元格里進行如下的操作 觀察出 現的結果 公公式式說說明明 結結果果 IF A1 10 真 假 A1單元格里的數字為 15 大于 10 因此邏輯值為真 Y IF A1 SUM 2 3 Y N SUM 2 3 的計算結果為 5 小于A1 單元格里的 15 因此邏輯值為假 N RANKRANK函函數數 函函數數用用途途 次函數的功能是在某個區域對目標數字進行排位計算 函函數數語語法法 RANK number ref order RANK 目標數字 要進行排行的區域 何種排位的方式 order為0或者省略 系統會按降序排列目標數字排位 相反 當order不是0 系統會按升序排 列對目標數字排位 函函數數說說明明 在應用RANK函數的過程中 當存在重復時數會影響后面的排位 函函數數簡簡單單示示例例 先在假設A1到A7單元格里分別輸入如下數字 3 1 2 3 5 7 6 在B1 單元格里進行如下的操作 觀察出現的結果 公公式式說說明明 結結果果 RANK A4 A1 A7 1 數字3在列表里排列第3 但因為出現了兩 次 影響了數字5的排位 因此數字5在A1 到A7單元格列表里升序排位為5 5 RANK A5 A1 A7 0 7在A1到A7單元格的數字列表里降序排位 為1 1 員員工工銷銷售售獎獎金金計計算算表表 VLOOKUPVLOOKUP函函數數 函函數數用用途途 此函數的功能是在給定區域的首列里查找目標數值 然后返回目標數值所 在行里某一列的相關數值 函函數數語語法法 VLOOKUP lookup value table array col index num range lookup VLOOKUP 目標數值 給定區域 制定返回給定區域中某一列的序號 用來 規定VLOOKUP函數查找類型 函函數數說說明明 range lookup為TRUE或省略 VLOOKUP函數將進行近似匹配查找 range lookup為FALSE或0 VLOOKUP函數將進行精確匹配查找 range lookup為TRUE或省略時 給定區域內首列值要以生序排列 注意要查找的目標數值的大小應該是比查找區域內的最小值大 否則應用 VLOOKUP函數時會顯示錯誤值 函函數數簡簡單單示示例例 在A1至A5單元格里分別輸入了 1 4 3 2 9 5 和 2 在B1至B5單元格里分別輸入了 第一名 第四名 第三名 第 五名 和 第二名 在C1只C5單元格里分別輸入了 張 李 陳 王 和 滕 接著在D5單元格里輸入如下公式 觀察出現結果 公公式式說說明明 結結果果 VLOOKUP 1 A1 C5 2 FALSE 使用精確匹配查找A1 C5單元格區域中首 列 即A列中的數值 1 然后返回同一 行中B列的值 第一名 VLOOKUP 3 A1 C5 3 TRUE 使用近似匹配查找A列中的數值 3 在 A列中找到小于3的最大值2 9 然后返回同 一行中C列的值 陳 LOOKUPLOOKUP函函數數 函函數數用用途途 此函數的功能是事先確定兩個單行或單列區域 將其中的一個作為查找區 域 然后進行目標數值查找 最后返回目標數值所在列或行與另一個區域香 蕉的單元格內容 LOOKUP函數具有兩種語法形式 即向量形式和數組形式 函函數數語語法法 向量形式 LOOKUP lookup value lookup vector result vector LOOKUP 目標數值 查找區域 輸出結果的區域 其構成和查 找區域相同 即同為單行或單列 數組形式 LOOKUP lookup value array LOOKUP 查找的目標數值 查找區域 函函數數說說明明 不同于VLOOKUP函數實現需要規定近似或精確匹配查找 LOOKUP函數默認為 精確匹配查找 當查找不到目標數值是LOOKUP函數開始近似匹配查找 函函數數簡簡單單示示例例 向量形式 現假設在A1至A5單元格里分別輸入了 1 4 3 2 9 5 和 2 在B1至B5單元格里分別輸入了 第一名 第四名 第三名 第五名 和 第二名 在C1只C5單元格里分別輸入了 張 李 陳 王 和 滕 接著在D5單元格里輸入如下公式 觀察出現結果 公公式式說說明明 結結果果 LOOKUP 1 1 A1 C5 B1 B5 由于A列中沒有1 1 故找到小于1 1的最 大值1 然后返回同一行中B列的值 張 LOOKUP 5 A1 C5 B1 B5 查找A列中的5 然后返回同一行中B列的 值 王 數組形式 數組中的值必須以升序順便放置 2 1 0 1 2 A Z FALSE TRUE 否則LOOKUP函數無法提供正確的值 現在在D5單元格里輸入如 下公式 觀察出現結果 公公式式說說明明 結結果果 LOOKUP c a b c d 1 2 3 4 在數組找到第一行中查找 c 查找小 于或等于它 c 的最大值 然后返 回同一列內最后一行中的值 3 LOOKUP bump a 1 b 2 c 3 在數組第一行中查找 c 查找小于或 等于它 b 的最大值 然后返回同 一行內最后一列中的值 2 HLOOKUPHLOOKUP函函數數 函函數數用用途途 此函數的功能是在給定區域的首行里查找目標數值 然后返回目標數值所 在列某一行的相關值 顯然該函數與VLOOKUP函數相似 無非一個是在首行里 查找 一個是在首列里查找 類似于LOOKUP函數 HLOOKUP函數也具有兩種語法形式 即向量形式和數組 形式 下面簡答介紹一下向量形式的函數語法 至于數組形式的函數語法 可以參閱LOOKUP函數的數組形式函數語法 函函數數語語法法 HLOOKUP lookup value table array row index num range lookup HLOOKUP 要查找的目標數值 給定區域 系統將在給定區域的首行中查找 目標數值 返回給定區域中某一行的序號 函函數數說說明明 range lookup為TURE或省略 HLOOKUP函數將進行近似匹配查找 range lookup為FALSE或0 HLOOKUP函數將進行精確匹配查找 注意要查找的目標數值的大小應該是在查找區域數值的范圍內 否則應用 HLOOKUP函數是會顯示錯誤值 函函數數簡簡單單示示例例 現假設在A1至E1單元格里分別輸入了 1 4 3 2 9 5 和 2 在A2至E2單元格里分別輸入了 張 李 陳 王 和 滕 接著在A3單元格里輸入如下公式 觀察出現結果 公公式式說說明明 結結果果 HLOOKUP 1 1 A1 E2 2 TRUE 由于1列中沒有1 1 故找到小于1 1的最 大值1 然后返回同一列中第2行的值 張 HLOOKUP 1 1 A1 E2 2 FALSE 查找A列中的5 然后返回同一列中第2行 的值 王 HLOOKUP 3 1 2 3 a b c d e f 2 TRU E 該例為HLOOKUP函數在數組形式里的應用 在數組常量的第一行中查找3 并返回 同列中第2行的值 c 帶帶薪薪年年假假天天數數統統計計表表 DATEDIFDATEDIF函函數數 函函數數用用途途 此函數的功能計算起始日期和截止日期之間的天數 月數或年數 函函數數語語法法 DATEDIF start date end date unit DATEDIF 起始日期 截止日期 返回類型 函函數數簡簡單單示示例例 在B3單元格里輸入下列公式 觀察出現結果 公公式式說說明明 結結果果 DATEDIF 2001 1 1 2003 1 1 Y 在起始日期和截止日期中有兩個整年 2 DATEDIF 2001 1 1 2003 1 2 M 在起始日期和截止日期中有24個月 24 DATEDIF 2001 6 1 2002 8 15 D 在2001年6月1日和2002年8月15日之間有 440天 440 DATEDIF 2001 6 1 2002 8 16 YD 在6月1日與8月15日之間有75天 忽略日 期中的年 75 DATEDIF 2001 6 1 2002 8 17 MD 計算開始日期1和結束日期15之間的差 忽略日期中的年和月 14 加加班班統統計計表表 TEXTTEXT函函數數 函函數數用用途途 此函數的功能是一句需要目標數字轉換為指定的文本格式 函函數數語語法法 TEXT value format text TEXT 目標數值 可以為數字可以為計算結果 需要設定的文本格式 函函數數簡簡單單示示例例 在A2和A3單元格里分別輸入 工業 和 服務業 在B2和B3單元格里分 別輸入 45 和 1000億 然后在C4和C5單元格里分別輸入下面表格左側 的公式 按鍵確認 觀察出現結果 公公式式說說明明 結結果果 A2 總占GDP比重 TXET B2 0 將A2和B2單元格里的內容以及引號間的內 容合并為一句 工業占總GDP比重的45 A3 的GDP為 TEXT B3 將A3和B3單元格里的內容以及引號間的內 容合并為一句 服務業的GDP為1000億 ROUNDROUND函函數數 函函數數用用途途 此函數的功能是設置指定位數 按此位數對數字進行四舍五入去整 然后 返回相應的結果 函函數數語語法法 ROUND number num digits TEXT 目標數字 指定的位數 系統按此位數進行四舍五入 函函數數說說明明 如果num digits小于 等于或者大于0 都會有不同的結果 函函數數簡簡單單示示例例 在Excel任意一個單元格輸入以下公式 觀察出現的結果 公公式式說說明明 結結果果 ROUND 3 1415 1 將3 1415四舍五入到一位小數 3 1 ROUND 3 1415 0 將3 1415四舍五入到整數 3 ROUND 314 15 1 將3 1415四舍五入到小數點左側以為 310 YEARYEAR函函數數 函函數數用用途途 此函數的功能是返回給定日期所屬的年份 函函數數語語法法 YEAR serial number YEAR 給定日期 函函數數簡簡單單示示例例 在A1單元格里輸入 2007 8 2 在D5單元格里輸入以下公式 觀察出現 的結果 公公式式說說明明 結結果果 YEAR A1 A1單元格內日期的年份 2007 MONTHMONTH函函數數 函函數數用用途途 此函數的功能是返回給定日期所屬的月份 函函數數語語法法 MONTH serial number MONTH 給定日期 函函數數說說明明 應用MONTH函數時 應注意給定日期的輸入格式 函函數數簡簡單單示示例例 在A1單元格里輸入 DATE 2007 8 2 在D4單元格里輸入以下公式 觀 察出現的結果 公公式式說說明明 結結果果 MONTH A1 A1單元格內日期的月份 8 在A2單元格里輸入 2007 9 2 在D5單元格里輸入以下公式 觀察出現 的結果 公公式式說說明明 結結果果 MONTH A2 A2單元格內日期的月份 9 DAYDAY函函數數 函函數數用用途途 此函數的功能是返回給定日期的具體天數 函函數數語語法法 DAY serial number DAY 給定日期 函函數數簡簡單單示示例例 在A1單元格里輸入 2007 9 2 在D5單元格里輸入以下公式 觀察出現 的結果 公公式式說說明明 結結果果 DAY A1 A1單元格內日期的天數 2 NOWNOW函函數數 函函數數用用途途 此函數的功能是顯示系統當前的時間 函函數數語語法法 NOW 函函數數簡簡單單示示例例 在Excel任意一個單元格里輸入以下公式 觀察出現的結果 公公式式說說明明 結結果果 NOW 返回當前的系統日期 2012 12 7 16 45 TODAYTODAY函函數數 函函數數用用途途 此函數的功能是顯示系統當前日期 函函數數語語法法 TODAY 函函數數簡簡單單示示例例 在Excel任意一個單元格里輸入以下公式 觀察出現的結果 公公式式說說明明 結結果果 TODAY 返回當前的系統日期 2012 12 7 NETWORKDAYSNETWORKDAYS函函數數 函函數數用用途途 此函數的功能是計算兩個給定參數間的工作日數值 函函數數語語法法 NETWORKDAYS start date end date hoildays NETWORKDAYS 起始時間 終止時間 假日 函函數數說說明明 應用NETWORKDAYS函數時應注意給定日期的輸入格式 操作NETWORKDAYS函數時 若該函數不可用 則顯示 NAME 錯誤值 此時 需分要加載相應的宏 具體方法如下 依次單擊菜單 工具 加載宏 淡出一個對話框 勾選 分析工具庫 復選框 然后單擊 確定 按鈕 函函數數簡簡單單示示例例 在A1單元格里輸入 DATE 2007 8 2 作為開始日期 在A2單元格里輸入 DATE 2008 3 2 作為結束日期 在A3單元格里輸入 DATE 2007 10 13 作為假日在D4單元格里輸入以下公式 觀察出現的結 果 公公式式說說明明 結結果果 NETWORKDAYS A1 A2 計算開始日期和終止日期之間間隔工作日 的數值 152 NETWORKDAYS A1 A2 A3 計算開始日期和終止日期之間間隔工作日 的數值 同時扣除A3單元格里的假日 151 EOMONTHEOMONTH函函數數 函函數數用用途途 此函數的功能是給定一個起始日期 再輔以一個指定數字 將一個指定數 字 將起始日期的月份向前或向后撥動該數字大小距離 最后返回所得月份 的最后一天日期 函函數數語語法法 EOMONTH start date months EOMONTH 起始日期 制定數字 正數表示向未來移動的月數 負數表示向 過去移動的月數 函函數數說說明明 應用EOMONTH函數時應注意給定日期的輸入格式 操作EOMONTH函數一樣 若EOMONTH函數第一次不能使用 也要進行加載宏 的操作 函函數數簡簡單單示示例例 在A1單元格里輸入 DATE 2007 8 2 在D4單元格里輸入以下公式 觀察 出現的結果 注 若顯示的結果為 39386 此時則可通過調整單元格格式來獲得日期格式 公公式式說說明明 結結果果 EOMONTH A1 2 計算 2007年8月2日 該日期兩個月后所 在月份的最后一天的日期 2007 10 31 WORKDAYWORKDAY函函數數 函函數數用用途途 此函數的功能是返回某一個日期前后相隔制定工作日的日期 函函數數語語法法 WORKDAY start date days holiday WORKDAY 起始日期 自起始日期起推算的天數 當該值為正值時意味著將 來的日期 當該值為負值時意味著過去的日期 并且推算中是不包含共假日 的 要剔除的日期 可以自定義的日期 也可以定義公休假日 函函數數說說明明 應用WORKDAY函數時應注意給定日期的輸入格式 操作WORKDAY函數一樣 若WORKDAY函數第一次不能使用 也要進行加載宏 的操作 函函數數簡簡單單示示例例 在B1單元格里輸入 DATE 2007 8 2 在B2單元格里輸入 179 作為工作 天數 在B3單元格里輸入 DATE 2007 10 17 作為假日 在D4單元格里輸 入以下公式 觀察出現的結果 注 若顯示的結果為 39547 此時則可通過調整單元格格式來獲得日期格式 公公式式說說明明 結結果果 WORKDAY B1 B2 從起始日期開始計算179個工作日后的日 期 2008 4 9 WORKDAY B1 B2 B3 從起始日期開始計算179個工作日后的日 期 期間還要扣除B3單元格里的假日 2008 4 10 TIMETIME函函數數 函函數數用用途途 此函數的功能是返回某一個特定時間的小數值 函函數數語語法法 TIME hour minute second TIME 小時 分鐘 秒 三個數值都在在區間 0 32767 內取值 函函數數簡簡單單示示例例 在E1單元格里輸入 8 作為小時 在F1單元格里輸入 12 作為分鐘 在 G1單元格里輸入 0 作為秒 在G4單元格里輸入以下公式 觀察出現的結果 公公式式說說明明 結結果果 TIME E1 F1 G1 將E1 F1和G1單元格里的數字構成時間形 式 8 12AM 單擊任意一個單元格 然后輸入表格里的任意一個公式 按鍵確認 系統將自動顯示對應日期 公公式式說說明明 結結果果 YEAR NOW 返回當前年份 MONTH NOW 返回當前月份 DAY NOW 返回當前日期 TODAY 返回當前日期 NOW 返回當前時間 NETWORKDAY TODAY EOMONTH TO DAY 0 放回當前時間距離月末還有多少工作人 哦 WORKDAY TODAY 15 返回當前時間15天以后的工作時間 NOW TIME 3 30 0 返回當前時間喪再加3個半小時 人事信息數據表 TEXTTEXT函函數數 函函數數用用途途 此函數的功能是一句需要目標數字轉換為指定的文本格式 函函數數語語法法 TEXT value format text TEXT 目標數值 可以為數字可以為計算結果 需要設定的文本格式 函函數數簡簡單單示示例例 在A2和A3單元格里分別輸入 工業 和 服務業 在B2和B3單元格里分 別輸入 45 和 1000億 然后在C4和C5單元格里分別輸入下面表格左側 的公式 按鍵確認 觀察出現結果 公公式式說說明明 結結果果 A2 總占GDP比重 TXET B2 0 將A2和B2單元格里的內容以及引號間的內 容合并為一句 工業占總GDP比重的45 A3 的GDP為 TEXT B3 將A3和B3單元格里的內容以及引號間的內 容合并為一句 服務業的GDP為1000億 MIDMID函函數數 函函數數用用途途 此函數的功能是在目標數字符串中指定一個開始位置 按設定的數值返回 該字符串中的相應數目字符內容 函函數數語語法法 MID text start num num chars MID 目標字符串 字符串開始的位置 通常start num是從字符串的首個 字符編號 依次為1 2 3 start num有取值范圍 不能大于字符串的長 度 不能小于1 設定的數目 MID函數將按次數目返回相應的字符個數 顯 然該數值不為負 否則函數將輸出錯誤值 函函數數簡簡單單示示例例 在A2輸入 wonderful 然后在B3單元格里輸入下列公式 觀察出現結果 公公式式說說明明 結結果果 MID A2 1 5 提取A2單元格里字符串中的5個字符 且 從第一個字符開始提取 wonde MID A2 5 20 提取A2單元格里字符串中的20個字符 且 從第7個字符開始提取 因為A2單元格中的 字符串中總字符數是9 所以最多只能從 第7個字符開始 將余下的所有字符都提 取 erful wonde MID A2 10 2 因為A2單元格中的字符串中總字符數是9 此時要從第10個字符開始 所以系統返 回空格 MID A2 0 2 因為start num小于1 所以系統返回錯 誤值 VALUE LENLEN函函數數 函函數數用用途途 此函數的功能是計算目標字符中的字符數 函函數數語語法法 LEN text LEN 目標字符串 函函數數說說明明 空格也視為字符 加到字符串的字符數中 函函數數簡簡單單示示例例 在A3單元格里輸入 wonderful 在A4單元格里輸入 Excel word 然后 在B4單元格里輸入下列的公式 觀察出現結果 公公式式說說明明 結結果果 LEN A3 返回A3單元格里的字符串的長度 9 LEN A4 返回A4字符串的長度 包含一個空格的位 置 10 LEFTLEFT函函數數 函函數數用用途途 此函數的功能是從字符串的左側開始 按照指定的數值返回相應的字符內 容 函函數數語語法法 LEFT text num chars LEFT 目標字符串 指定的數值 該數值必須大于等于0 函函數數簡簡單單示示例例 在A3單元格里輸入 wonderful 然后在B4單元格里輸入下列的公式 觀 察出現結果 公公式式說說明明 結結果果 LEFT A3 4 對A3單元格里的字符串 按從左到右提取 前4位字符 wond LEFT A3 因為省略了num chars 因此系統假定其 為1 將提取A3單元格里字符串左側的首 字符 w LEFT A3 11 因為指定指數大于目標字符串的總數 所 以系統提取目標字符串的所有字符 wonderful RIGHTRIGHT函函數數 函函數數用用途途 此函數的功能是從字符串的右側開始 按照指定的數值返回相應的字符內 容 該函數恰好與LEFT函數的功能相反 函函數數語語法法 RIGHT text num chars RIGHT 目標字符串 指定的數值 該數值必須大于等于0 函函數數簡簡單單示示例例 在A3單元格里輸入 wonderful 然后在B5單元格里輸入下列的公式 觀 察出現結果 公公式式說說明明 結結果果 RIGHT A3 6 對A3單元格里的字符串 按從右到右提取 6位字符 derful RIGHT A3 因為省略了num chars 因此系統假定其 為1 將提取A3單元格里字符串右側的首 字符 l MODMOD函函數數 函函數數用用途途 此很熟的功能是求解對目標數值除以指定數后的余數 余數的符號和除數 相同 函函數數語語法法 MOD number divisor MOD 目標數值 作為被除數 指定數 作為除數 函函數數說說明明 當指定數為0時 系統將顯示錯誤值 DIV 0 函函數數簡簡單單示示例例 在A1 B1和C1單元格里分別輸入數字 3 2 和 2 再在C5單元 格里輸入下列任意公式 觀察出現的結果 公公式式說說明明 結結果果 MOD A1 B1 3除以2余數為1 又因為除數2的符號為正 1 MOD A1 C1 3除以 2余數為1 又因為除數 2的符號為 負 1 MOD A1 0 除數為0 DIV 0 TODAYTODAY函函數數 函函數數用用途途 此函數的功能是顯示系統當前日期 函函數數語語法法 TODAY 函函數數簡簡單單示示例例 在Excel任意一個單元格里輸入以下公式 觀察出現的結果 公公式式說說明明 結結果果 TODAY 返回當前的系統日期 2012 12 7 DATEDIFDATEDIF函函數數 函函數數用用途途 此函數的功能計算起始日期和截止日期之間的天數 月數或年數 函函數數語語法法 DATEDIF start date end date unit DATEDIF 起始日期 截止日期 返回類型 函函數數簡簡單單示示例例 在B3單元格里輸入下列公式 觀察出現結果 公公式式說說明明 結結果果 DATEDIF 2001 1 1 2003 1 1 Y 在起始日期和截止日期中有兩個整年 2 DATEDIF 2001 1 1 2003 1 2 M 在起始日期和截止日期中有24個月 24 DATEDIF 2001 6 1 2002 8 15 D 在2001年6月1日和2002年8月15日之間有 440天 440 DATEDIF 2001 6 1 2002 8 16 YD 在6月1日與8月15日之間有75天 忽略日 期中的年 75 DATEDIF 2001 6 1 2002 8 17 MD 計算開始日期1和結束日期15之間的差 忽略日期中的年和月 14 ExcelDExcelD 字字符符查查找找和和替替換換技技術術 在實際工作中 有時可能需要從姓名字符串中提取姓或者名 如外籍員工 的姓名等 此時需要用到Excel的字符查找和替換技術 姓姓名