人力資源管理中的EXCEL函數(shù)應(yīng)用.xls

第 1 頁 共 20 頁 人人力力資資源源及及行行政政管管理理中中的的EXCELEXCEL函函數(shù)數(shù)應(yīng)應(yīng)用用 序序號(hào)號(hào)函函數(shù)數(shù)類類別別分分序序號(hào)號(hào)函函數(shù)數(shù)名名稱稱用用 途途 1數(shù)學(xué) 1 1SUM number1 number2 計(jì)算某個(gè)目標(biāo)區(qū)域中單元格里數(shù)字的總和 1 2ROUND number num digits 設(shè)定指定位數(shù) 按此位數(shù)對(duì)目標(biāo)數(shù)字進(jìn)行四舍五入取整 1 3MOD number divisor 對(duì)目標(biāo)值除以指定數(shù)后的余數(shù) 余數(shù)的符號(hào)和除數(shù)相同 1 4INT number 對(duì)目標(biāo)數(shù)字進(jìn)行舍入處理 處理的結(jié)果是得到小于目標(biāo)數(shù)的最大整數(shù) 1 5ROUNDUP number num digits 對(duì)目標(biāo)數(shù)字按照指定的條件進(jìn)行相應(yīng)的舍入處理 1 6SUMPRIDUCT array1 array2 對(duì)幾組給定的數(shù)組對(duì)應(yīng)的元素相乘 所得結(jié)果再進(jìn)行加總 2查詢和引用 2 1VLOOKUP lookup value table array col index num range lookup 在給定區(qū)域的首列里查找目標(biāo)數(shù)值 然后返回目標(biāo)數(shù)值所在行里某一列的 相關(guān)值 2 2HLOOKUP lookup value table array row index num range lookup 在給定區(qū)域的首行里查找目標(biāo)數(shù)值 然后返回目標(biāo)數(shù)值所在列里某一行的 相關(guān)值 2 3 向量形式 LOOKUP lookup value lookup vector result vector 事先確定兩個(gè)單行或單列區(qū)域 將其中的一個(gè)作為查找區(qū)域 然后進(jìn)行目 標(biāo)數(shù)值查找 最后返回目標(biāo)數(shù)值所在列或行與吃一塹一個(gè)區(qū)域相關(guān)的單元 格內(nèi)容 數(shù)組形式 lOOKUP lookup value array 2 4COLUMN reference 返回目標(biāo)單元格或單元格區(qū)域 可返回列序號(hào) 2 5ROW reference 返回目標(biāo)單元格或單元格區(qū)域 的行序號(hào) 2 6 數(shù)組形式 INDEX array row num column num 按照相關(guān)條件返回目標(biāo)區(qū)域里的值 引用形式 INDEX reference row num column num area num 2 7OFFSET reference rows cols height width 將給定的區(qū)域作為目標(biāo)引用區(qū)域 同時(shí)設(shè)定一個(gè)偏移量 然后依據(jù)該偏移 量得到新的引用 最終的結(jié)果包括單元格和單元格區(qū)域的形式 3文本 3 1TEXT value format text 依據(jù)需要將目標(biāo)數(shù)字轉(zhuǎn)換為指定的文本格式 3 2MID text start num num chars 在目標(biāo)字符串中指定一個(gè)開始位置 按設(shè)定的數(shù)值返回該字符串中的相應(yīng) 數(shù)目字符內(nèi)容 3 3LEN text 計(jì)算目標(biāo)字符中的字符數(shù) 3 4LEFT text num chars 從字符串的左側(cè)開始 按照指定的數(shù)值返回相應(yīng)數(shù)目的字符內(nèi)容 3 5RIGHT text num chars 從字符串的右側(cè)開始 按指定的數(shù)值返回相應(yīng)數(shù)目的字符內(nèi)容 3 6FIND find text within text start num 查找字符串中首字符編號(hào)為1 接著對(duì)余下的字符按順序依次進(jìn)行編號(hào) 然后在查找字符串中查找目標(biāo)字符 并且返回目標(biāo)字符在查找字符串中的 編號(hào) 3 7SUBSTITUTE text old text new text instance num 將目標(biāo)字符或字符串替換到需要被替換的字符串中 4統(tǒng)計(jì) 4 1RANK number ref order 在某個(gè)區(qū)域內(nèi)對(duì)目標(biāo)數(shù)字進(jìn)行排位計(jì)算 4 2COUNTIF range criteria 在目標(biāo)區(qū)域中統(tǒng)計(jì)滿足預(yù)設(shè)條件的單元格數(shù)目 4 3FREQUENCY data array bins array 計(jì)算目標(biāo)數(shù)值在某個(gè)區(qū)域內(nèi)出現(xiàn)的次數(shù) 然后返回一個(gè)重直組 5日期和時(shí)間 5 1YEAR serial number 返回給定日期所屬的年份 5 2MONTH serial number 返回給定日期所屬的月份 5 3DAY serial number 返回給定日期所屬的目體天數(shù) 第 2 頁 共 20 頁 人人力力資資源源及及行行政政管管理理中中的的EXCELEXCEL函函數(shù)數(shù)應(yīng)應(yīng)用用 序序號(hào)號(hào)函函數(shù)數(shù)類類別別分分序序號(hào)號(hào)函函數(shù)數(shù)名名稱稱用用 途途 5日期和時(shí)間 5 4NOW 顯示系統(tǒng)當(dāng)前的日期和時(shí)間 5 5TODAY 顯示系統(tǒng)當(dāng)前的日期 5 6NETWORKDAYS start date end date holidays 計(jì)算兩個(gè)給定參數(shù)間的工作日數(shù)值 5 7EOMONTH start date months 給定一個(gè)起始日期 再輔以一個(gè)指定數(shù)字 將起始日期的月份向前或向后 撥運(yùn)該數(shù)字大小距離 最后返回所得月份的最后一天日期 5 8WORKDAY start date days holidays 返回某個(gè)日期前后相隔指定工作日的日期值 5 9TIME hour minute second 返回某一個(gè)特定時(shí)間的小數(shù)值 5 9 1DATEDIF start date end date unit 計(jì)算起始日期和截止日期之間的天數(shù) 月數(shù)和年數(shù) 6邏輯 6 1IF logical test value if true value if false 用于對(duì)目標(biāo)區(qū)域進(jìn)行判斷 返回真假邏輯值 據(jù)此輸出相應(yīng)的結(jié)果 6 2NOT logical 對(duì)單元格或單元格區(qū)域內(nèi)的邏輯值求反 6 3OR logical1 logical2 對(duì)指定區(qū)域內(nèi)的邏輯值進(jìn)行判斷 當(dāng)邏輯值為TRUE時(shí)返回TRUE 若邏輯 值為FALSE則返回FALSE 7信息ISERROR value 判斷測(cè)試內(nèi)容是否為錯(cuò) 8數(shù)據(jù)庫DSUM database field criteria 在指定區(qū)域查找符合條件2的區(qū)域 然后在查找到的區(qū)域內(nèi)計(jì)算符合條件1 的數(shù)值之和 9行政管理 9 1Excel抽獎(jiǎng)器 9 2公議室使用安排表 9 3辦公室布局平面圖 第 3 頁 共 20 頁 數(shù)數(shù)學(xué)學(xué)函函數(shù)數(shù)的的應(yīng)應(yīng)用用 1 1 SUMSUM函函數(shù)數(shù) 用途 計(jì)算某個(gè)目標(biāo)區(qū)域中單元格里數(shù)字的總和 語法 sum number1 number2 number1 number2 為需要計(jì)算的參數(shù) number數(shù)一共可以有30個(gè) 說明 對(duì)于計(jì)算區(qū)域里的內(nèi)容 其表達(dá)形式可以是數(shù)字也可以是邏輯值 若計(jì)算的參數(shù)為數(shù)組形式 那么只計(jì)算數(shù)組中的數(shù)字 同樣 若計(jì)算的參數(shù)為引用類型 那也只計(jì)算引用類型中的數(shù)字 簡(jiǎn)單示例 假設(shè)在B2 B5區(qū)域里分別輸入了 2 55 24 和 12 然后在C5單元格里輸入下列公式 觀察出現(xiàn)的結(jié)果 公式說明 結(jié)果 sum 12 3 將數(shù)字12和數(shù)字3相加 15 sum B2 B5 將B2至B5單元格里的數(shù)據(jù)相加 93 擴(kuò)展知識(shí)點(diǎn)講解 一 快速獲得簡(jiǎn)單合計(jì)的另外幾種方法 1 單擊 格式 工具欄中的 自動(dòng)求和 按鈕 此時(shí)在求和目標(biāo)列的下方單元格自動(dòng)顯示求和公式 按鍵確認(rèn)即可獲得合計(jì)的結(jié)果 2 在求和目標(biāo)列的下方單元格按組合鍵 即顯示求和公司 按鍵確認(rèn)即可獲得合計(jì)的結(jié)果 3 選中求和目標(biāo)列 在狀態(tài)欄里 右下方 已顯示合計(jì)的結(jié)果 Excel 2003在默認(rèn)狀態(tài)下此功能打開且為 求和 二 SUM函數(shù)計(jì)算區(qū)域的交叉合計(jì) 非連續(xù)區(qū)域合計(jì) 1 交叉合計(jì)操作 指定區(qū)域公共部份求和 1234指定區(qū)域 B24 E26指定區(qū)域如方框示 2345C23 C27公共區(qū)域如黃色部份 3456公式 sum B24 E26 C23 C27 4567注 公式里 B24 E26 和 C23 C27 之間留有一個(gè)空格 5678結(jié)果 12 2 非連續(xù)區(qū)域合計(jì)操作 指定區(qū)域求和 指定區(qū)域 B31 B33指定區(qū)域如方框示 1234D30 E34 2345公式 sum B31 B33 D30 E34 3456注 指定的區(qū)域 B31 B33 和 D30 E34 之間要用逗號(hào)隔開 4567結(jié)果 64 5678 三 SUM函數(shù)應(yīng)用 1 SUM函數(shù)直接求和 2 SUM結(jié)合IF COUNTIF函數(shù)編制中國式排名公式例 P100 3 應(yīng)用內(nèi)存數(shù)組公式計(jì)算帶薪年假天數(shù)例 P142 2 2 ROUNDROUND函函數(shù)數(shù) 第 4 頁 共 20 頁 用途 設(shè)置指定位數(shù) 按此位數(shù)對(duì)目標(biāo)數(shù)字進(jìn)行四舍五入取整 然后返回相應(yīng)的結(jié)果 語法 ROUND number num digits number為目標(biāo)數(shù)字 num digits為指定的位數(shù) 系統(tǒng)將按此位數(shù)進(jìn)行四舍五入 說明 如果num digits小于 等于或大于0 都會(huì)有不同的結(jié)果 簡(jiǎn)單示例 公式說明 結(jié)果 ROUND 3 1415 1 將3 1415四舍五入到一位小數(shù) 3 1 ROUND 3 1415 0 將3 1415四舍五入到整數(shù) 3 ROUND 314 15 1 將314 15四舍五入到小數(shù)點(diǎn)左側(cè)一位 310 ROUND函數(shù)的應(yīng)用 1 應(yīng)用ROUND結(jié)合TEXT函數(shù)舍入計(jì)算加班時(shí)間例 P109 2 應(yīng)用ROUND函數(shù)計(jì)算應(yīng)發(fā)工資例 P134 3 3 MODMOD函函數(shù)數(shù) 用途 求對(duì)目標(biāo)值除以指定數(shù)后的余數(shù) 余數(shù)的符號(hào)和除數(shù)相同 語法 MOD number divisor number為目標(biāo)數(shù)值 作為被除數(shù) divisor為指定數(shù) 作為除數(shù) 說明 當(dāng)指定數(shù)為0時(shí) 系統(tǒng)將顯示錯(cuò)誤值 DIV 0 簡(jiǎn)單示例 32 2 公式說明 結(jié)果 MOD B61 C61 3除以2余數(shù)為1 又因?yàn)槌龜?shù)2的符號(hào)為正 1 MOD B61 D61 3除以 2余數(shù)為1 又因?yàn)槌龜?shù) 2的符號(hào)為負(fù) 1 MOD B61 0 除數(shù)為0 DIV 0 MOD函數(shù)的應(yīng)用 1 應(yīng)用MOD函數(shù)編制工資條例 P162 4 4 INTINT函函數(shù)數(shù) 用途 對(duì)目標(biāo)數(shù)字進(jìn)行舍入處理 處理的結(jié)果是得到小于目標(biāo)數(shù)的最大整數(shù) 語法 INT number number為需要處理的目標(biāo)數(shù)字 也可以是含數(shù)字的單元格引用 說明 向下舍入 簡(jiǎn)單示例 公式說明 結(jié)果 INT 3 1415 將數(shù)字3 1415向下舍入到最接近的整數(shù) 3 INT 3 1415 將數(shù)字 3 1415向下舍入到最接近的整數(shù) 4 INT 3 1415 INT 3 1415 先將第二個(gè)INT函數(shù)內(nèi)的數(shù)字3 1415向下舍入到最接近的整數(shù)3 然后用數(shù)字3 1415減去該數(shù) 字3 得到數(shù)字0 1415 最后當(dāng)數(shù)字0 1415由第一個(gè)INT函數(shù)進(jìn)行向下舍入 0 INT函數(shù)的應(yīng)用 1 應(yīng)用INT結(jié)合ROUNDUP SUM函數(shù)計(jì)算零鈔例 P170 5 5 ROUNDUPROUNDUP函函數(shù)數(shù) 第 5 頁 共 20 頁 用途 對(duì)目標(biāo)數(shù)字接照指定的條件進(jìn)行相應(yīng)的舍入處理 不要求四舍五入的數(shù)字進(jìn)位 語法 ROUNDUP number num digits number為需要處理的目標(biāo)數(shù)字 num digits為指定的條件 將決定目標(biāo)數(shù)字處理后的結(jié)果位數(shù) 說明 num digits小于 等于或大于0 都會(huì)有不同的結(jié)果 簡(jiǎn)單示例 公式說明 結(jié)果 ROUNDUP 3 1415 0 向上舍入到最接近的整數(shù) 4 ROUNDUP 3 1415 3 舍入到小數(shù)點(diǎn)千位上 3 142 ROUNDUP 314 15 2 在小數(shù)點(diǎn)左側(cè)舍入兩位 400 ROUNDUP 314 15 1 在小數(shù)點(diǎn)左側(cè)舍入一位 320 ROUNDUP 3 1415 0 向上舍入到最接近的整數(shù) 4 ROUNDUP函數(shù)的應(yīng)用 1 應(yīng)用ROUNDUP函數(shù)計(jì)算零鈔例 P170 6 6 SUMPRODUCTSUMPRODUCT函函數(shù)數(shù) 用途 將幾組給定的數(shù)組對(duì)應(yīng)的元素相乘 所得結(jié)果再進(jìn)行加總 語法 SUMPRODUCT array1 array2 array3 array1 array2 為給定數(shù)組 可以包含30個(gè)數(shù)組 說明 給定的數(shù)組應(yīng)當(dāng)是含用相同維數(shù)的數(shù)組 否則將返回錯(cuò)誤值 VALUE 對(duì)于不是數(shù)值形式 比如文本形式的元素 該函數(shù)將其統(tǒng)統(tǒng)視為0 簡(jiǎn)單示例 5623 3255 6732 公式說明 結(jié)果 SUMPRODUCT B103 B105 C103 C105 D103 D105 3個(gè)數(shù)組的所有元素對(duì)應(yīng)相乘 然后將乘積相加 即5X6X2 3X2X5 6X7X3 216 SUMPRODUCT B103 C105 C103 E105 兩組數(shù)組的所有元素對(duì)應(yīng)相乘 然后將乘積相加 即5X2 3X5 6X3 6X3 2X5 7X2 85 SUMPRODUCT函數(shù)的應(yīng)用 1 應(yīng)用SUMPRODUCT函數(shù)查詢某年齡階段例 P246 第 6 頁 共 20 頁 查查詢?cè)兒秃鸵糜煤瘮?shù)數(shù)的的應(yīng)應(yīng)用用 1 1 VLOOKUPVLOOKUP函函數(shù)數(shù) 用途 在給定區(qū)域的首列里查找目標(biāo)數(shù)值 然后返回目標(biāo)數(shù)值所在行里某一列的相關(guān)值 語法 VLOOKUP lookup value table array col index num range lookup lookup value為目標(biāo)數(shù)值 table array為給定區(qū)域 即查找區(qū)域 系統(tǒng)將在給定區(qū)域的首列中查找目標(biāo)數(shù)值 col index num為指定返回給定區(qū)域中某一列的序號(hào) 比如該數(shù)值取3時(shí) 意思是返回給定區(qū)域中第三列的值 range lookup規(guī)定查找類型 說明 range lookup為TRUE或省略 VLOOKUP函數(shù)將進(jìn)行近似匹配查找 range lookup為FALSE或0 VLOOKUP函數(shù)將進(jìn)行精確匹配查找 range lookup為TRUE或省略時(shí) 給定區(qū)域內(nèi)首列值要以升序排列 注意要查找的目標(biāo)數(shù)值的大小應(yīng)該是比查找區(qū)域內(nèi)的最小值大 否則應(yīng)用VLOOKUP函數(shù)時(shí)會(huì)顯示錯(cuò)誤值 簡(jiǎn)單示例 1 第一名張公式運(yùn)算結(jié)果 第一名第一名 4 3 第四名李陳 2 9 第三名陳 5 第五名王 2 第二名滕 公式說明 結(jié)果 VLOOKUP 1 B11 D15 2 FLASE 使用精確匹配查找B11 D15單元格區(qū)域中首列 即B列中的數(shù)值 1 然后返回同一行中C列 的值 第一名 VLOOKUP 3 B11 D15 3 TRUE 使用近似匹配查找A列中的數(shù)值 3 在A列中找到小于3的最大值2 9 然后返回同一行中 D列的值 陳 VLOOKUP函數(shù)的應(yīng)用 1 應(yīng)用VLOOKUP函數(shù)查找銷售額所符合獎(jiǎng)金比例例 P126 2 應(yīng)用VLOOKUP函數(shù)制作工資條例 P159 2 2 HLOOKUPHLOOKUP函函數(shù)數(shù) 用途 在給定區(qū)域的首行里查找目標(biāo)數(shù)值 然后返回目標(biāo)數(shù)值所在列里某一行的相關(guān)值 功能相似 VLOOKUP是首列查找 HLOOKUP是首行查找 語法 HLOOKUP lookup value table array row index num range lookup lookup value為要查找的目標(biāo)數(shù)值 table array為給定區(qū)域 即查找區(qū)域 系統(tǒng)將在給定區(qū)域的首行中查找目標(biāo)數(shù)值 row index num為指定 返回給定區(qū)域中某一行的行號(hào) 比如該數(shù)值取2時(shí) 意味著返回給定區(qū)域中第二行的值 說明 range lookup為TRUE或省略 HLOOKUP函數(shù)將進(jìn)行近似匹配查找 range lookup為FALSE或0 HLOOKUP函數(shù)將進(jìn)行精確匹配查找 注意要查找的目標(biāo)數(shù)值的大小應(yīng)該是在查找區(qū)域數(shù)值的范圍內(nèi) 否則應(yīng)用HLOOKUP函數(shù)時(shí)會(huì)顯示錯(cuò)誤值 簡(jiǎn)單示例 14 32 952公式運(yùn)算結(jié)果 張王 張李陳王滕 公式說明 結(jié)果 HLOOKUP 1 1 B35 F36 2 TRUE 由于行1中沒有1 1 故找到小于1 1的最大值1 然后返回同一列中第2行的值 張 HLOOKUP 5 B35 F36 2 FALSE 查找A列中的5 然后返回同一列中第2行的值 王 第 7 頁 共 20 頁 HLOOKUP 3 1 2 3 a b c d e f 2 TRUE 該列為HLOOKUP函數(shù)在數(shù)組形式里的應(yīng)用 在數(shù)組常量的第一行中查找3 并返回同列中第2行的值 C 3 3 LOOKUPLOOKUP函函數(shù)數(shù) 用途 功能是事先確定兩個(gè)單行或單列區(qū)域 將其中的一個(gè)作為查找區(qū)域 然后進(jìn)行目標(biāo)數(shù)值查找 最后返回目標(biāo)數(shù)值所在列或行與另一個(gè)區(qū)域相關(guān)的單元格內(nèi)容 語法 1 向量形式 LOOKUP lookup value lookup vector result vector lookup value為要查找的目標(biāo)數(shù)值 lookup vector為查找區(qū)域 result vector為輸出結(jié)果的區(qū)域 其構(gòu)成和查找區(qū)域相同 即同為單行或單列 2 數(shù)組形式 LOOKUP lookup value array lookup value為要查找的目標(biāo)數(shù)值 array為查找區(qū)域 為數(shù)組形式 具體構(gòu)成內(nèi)容可以多樣化 說明 不同于VLOOKUP函數(shù)事先需規(guī)定近似或精確匹配查找 LOOKUP函數(shù)默認(rèn)為精確匹配查找 當(dāng)查找不到目標(biāo)數(shù)值時(shí)LOOKUP函數(shù)開始近似匹配查找 簡(jiǎn)單示例 1 向量形式 1 張運(yùn)算結(jié)果 張 4 3 李王 2 9 陳 5 王 2 滕 公式說明 結(jié)果 LOOKUP 1 1 B50 B54 C50 C54 由于B列中沒有1 1 故找到小于1 1的最大值1 然后返回同一行中C列的值 張 LOOKUP 5 B50 B54 C50 C54 查找B列中的5 然后返回同一行中C列的值 王 2 數(shù)組形式 注 數(shù)組中的值必須以升序順序設(shè)置 公式說明 結(jié)果 LOOKUP c a b c d 1 2 3 4 在數(shù)組的第一行中查找 C 查找小于或等于它 C 的最大值 然后返回同一列內(nèi)最后一行中的值 3 LOOKUP bump a 1 b 2 c 3 在數(shù)組的第一行中查找 bump 查找小于或等于它 b 的最大值 然后返回同一行內(nèi)最后一列中的值 2 4 4 COLUMNCOLUMN函函數(shù)數(shù) 用途 返回目標(biāo)單元格或單元格區(qū)域 語法 COLUMN reference reference為目標(biāo)單元格或單元格區(qū)域 說明 若reference省略 此時(shí)系統(tǒng)返回COLUMN函數(shù)所在單元格的列序號(hào) 簡(jiǎn)單示例 運(yùn)算結(jié)果 37 公式說明 結(jié)果 COLUMN C70 C列是第3列 3 COLUMN 當(dāng)前列的列序號(hào) 結(jié)果選中區(qū)域 第 8 頁 共 20 頁 COLUMN函數(shù)應(yīng)用 應(yīng)用COLUMN函數(shù)得到引用的列序號(hào)例 P159 5 5 ROWROW函函數(shù)數(shù) 用途 返回目標(biāo)單元格或單元格區(qū)域的行序號(hào) 該函數(shù)與COLUMN函數(shù)的功能恰好相反 前者是返回行序號(hào) 后者是返回列序號(hào) 語法 ROW reference reference為目標(biāo)單元格或單元格區(qū)域 說明 若reference省略 此時(shí)系統(tǒng)返回ROW函數(shù)所在單元格的行序號(hào) 簡(jiǎn)單示例 運(yùn)算結(jié)果 86 公式說明 結(jié)果 ROW 返回當(dāng)前行的行序號(hào) ROW C86 返回 86 結(jié)果選中區(qū)域 91運(yùn)算過程及結(jié)果 選中B91 B94 然后輸入公式 ROW C91 C94 然后按組合鍵確認(rèn) 92 93 94 ROW函數(shù)應(yīng)用 應(yīng)用ROW函數(shù)編制工資條例 P162 6 6 INDEXINDEX函函數(shù)數(shù) 用途 按照相關(guān)條件返回目標(biāo)區(qū)域里的值 分為數(shù)組形式和引用形式 數(shù)組形式返回由行和列序號(hào)索引選定的值 引用形式返回特定行和列交叉處單元格的引用 數(shù)組形式語法 INDEX array row num column num array是目標(biāo)單元格區(qū)域或數(shù)組 Row num為目標(biāo)區(qū)域中待返回值的行序號(hào) column為目標(biāo)區(qū)域中待返回的列序號(hào) 例 當(dāng)row num為2 column為1時(shí) 返回目標(biāo)區(qū)域第二行第一列中的數(shù)值 說明 若省略row num INDEX函數(shù)將返回整列的元素 若省略column num INDEX函數(shù)將返回整行的元素 簡(jiǎn)單示例 張第一名運(yùn)算結(jié)果 第一名 陳第二名孫 孫第三名 公式說明 結(jié)果 INDEX B105 C107 1 2 系統(tǒng)將返回B105 B107區(qū)域里的第一行與第2列交叉單元格里的元素 即C105單元格里的內(nèi)容 第一名 INDEX B105 C107 3 1 系統(tǒng)將返回B105 B107區(qū)域里的第三行與第1列交叉單元格里的元素 即B107單元格里的內(nèi)容 孫 引用形式語法 INDEX reference row num column num area num reference是目標(biāo)單元格區(qū)域里的引用 row num是要從中返回目標(biāo)單元格區(qū)域中的行序號(hào) column num是要從中返回目標(biāo)單元格區(qū)域中的列序號(hào) 例 當(dāng)row num為2 column num為1時(shí) 則返回目標(biāo)區(qū)域第二行第一列中的數(shù)值 因?yàn)樵谝眯问较?INDEX函數(shù)可以對(duì)若干個(gè)區(qū)域進(jìn)行引用 所以area num用來指明從第幾個(gè)區(qū)域進(jìn)行引用 說明 若省略row num INDEX函數(shù)將返回整列的元素 若省略column num INDEX函數(shù)將返回整行的元素 將INDEX函數(shù)和其他的公式組合使用 比如利用INDEX函數(shù)的結(jié)果作為另一個(gè)函數(shù)的控制條件 就可以滿足工作中復(fù)雜功能的需求 第 9 頁 共 20 頁 簡(jiǎn)單示例 張第一名12 運(yùn)算結(jié)果 第一名 陳第三名12 5陳 孫第二名12 3 公式說明 結(jié)果 INDEX B122 C124 B122 C123 1 2 2 系統(tǒng)將返回第2個(gè)區(qū)域 即B122 C123區(qū)域里的第1行與第2列交叉單元格里的元素 即C122單元格里的內(nèi)容 第一名 INDEX B122 C124 B122 C123 2 1 1 系統(tǒng)將返回第1個(gè)區(qū)域 即B122 C124區(qū)域里的第2行與第1列交叉單元格里的元素 即B123單元格里的內(nèi)容 陳 7 7 OFFSETOFFSET函函數(shù)數(shù) 用途 將給定的區(qū)域作為目標(biāo)引用區(qū)域 同時(shí)設(shè)定一個(gè)偏移量 然后依據(jù)該偏移量得到新的引用 最終的結(jié)果包括單元格和單元格區(qū)域的形式 語法 OFFSET reference rows cols height width reference是目標(biāo)引用區(qū)域 可以是單元格 也可以是單元格區(qū)域 rows是相對(duì)于目標(biāo)引用區(qū)域里的左上角單元格上 下 偏移的行數(shù) 行數(shù)可為正數(shù) 代表在起始引用的下方 或負(fù)數(shù) 代表在起始引用的上方 cols是相對(duì)于目標(biāo)引用區(qū)域里的左上角單元格左 右 偏移的列數(shù) 列數(shù)可為正數(shù) 代表在起始引用的右邊 或負(fù)數(shù) 代表在起始引用的左邊 height為高度 即返回的引用區(qū)域的行數(shù) Height必須為正數(shù) width為寬度 即返回的引用區(qū)域的列數(shù) Width必須為正數(shù) 說明 如果行數(shù)和列數(shù)偏移量超出了工作表邊緣 OFFSET函數(shù)將返回錯(cuò)誤值 REF 如果省略了height或width 則假設(shè)其高度或?qū)挾扰creference相同 OFFSET函數(shù)實(shí)際上并不移動(dòng)任何一個(gè)單元格或更改選取定區(qū)域 它只是返回一個(gè)引用 OFFSET函數(shù)可用于任何需要將引用作為參數(shù)的函數(shù) 簡(jiǎn)單示例 161116運(yùn)算結(jié)果 18 27121737 381318 491419 5101520 公式說明 結(jié)果 OFFSET C141 3 2 1 1 從C141單元格出發(fā) 先向下偏移3個(gè)單元格單位 再向右偏移2個(gè)單元格單位 因?yàn)閔eight和 width都為1 所以返回E144單元格里的內(nèi)容 18 SUM OFFSET C141 3 2 2 1 從C141單元格出發(fā) 先向下偏移3個(gè)單元格單位 再向右偏移2個(gè)單元格單位 因?yàn)閔eight為2 width為1 所以返回E4 E5單元格區(qū)域 然后函數(shù)SUM對(duì)該區(qū)域里的數(shù)值進(jìn)行加總 37 OFFSET函數(shù)應(yīng)用 應(yīng)用OFFSET函數(shù)得到新引用 第 10 頁 共 20 頁 文文本本函函數(shù)數(shù)的的應(yīng)應(yīng)用用 1 1 TEXTTEXT函函數(shù)數(shù) 用途 依據(jù)需要將目標(biāo)數(shù)字轉(zhuǎn)換為指定的文本格式 語法 TEXT value format text value為目標(biāo)數(shù)值 可以為數(shù)字 也可以為計(jì)算結(jié)果 format text為讀者需要設(shè)定的文本格式 該格式設(shè)置方式 按組合鍵彈出 單元格格式 對(duì)話框 單擊 數(shù)字 選項(xiàng)卡 在 分類 列表框中選中文本形式的數(shù)字格式 最后單擊 確定 按鈕即可 簡(jiǎn)單示例 工業(yè)45 運(yùn)算結(jié)果 工業(yè)占總GDP比重45 服務(wù)業(yè)1000億服務(wù)業(yè)的GDP為1000億 公式說明 結(jié)果 B7 占總GDP比重 TEXT C7 0 將B7和C7單元格里的內(nèi)容以及引號(hào)間的內(nèi)容合并為一句 工業(yè)占總GDP比重的45 B8 的GDP為 TEXT C8 將B8和C8單元格里的內(nèi)容以及引號(hào)間的內(nèi)容合并為一句 服務(wù)業(yè)的GDP為1000億 TEXT函數(shù)的應(yīng)用 結(jié)合TEXT函數(shù)舍入計(jì)算加班時(shí)間例 P109 2 2 MIDMID函函數(shù)數(shù) 用途 在目標(biāo)字符串中指定一個(gè)開始位置 按設(shè)定的數(shù)值返回該字符串中的相應(yīng)數(shù)目字符內(nèi)容 語法 MID text start num num chars text是目標(biāo)字符串 start num是字符串中開始的位置 通常start num是從字符串的首個(gè)字符編號(hào) 依次為1 2 3 start num有取值范圍 不能大于字符串的長度 不能小于1 num chars指設(shè)定的數(shù)目 MID函數(shù)將按此數(shù)目返回相應(yīng)的字符個(gè)數(shù) 顯然該數(shù)值不為負(fù) 否則函數(shù)將輸出錯(cuò)誤值 簡(jiǎn)單示例 wonderful運(yùn)算結(jié)果 wondeerful VALUE 公式說明 結(jié)果 MID B22 1 5 提取B22單元格里字符串中的5個(gè)字符 且從第一個(gè)字符開始提取 wonde MID B22 5 20 提取B22單元格里字符串中的20個(gè)字符 且從第5個(gè)字符開始提取 因?yàn)锽22單元格中的字符串 中總字符數(shù)是9 所以最多只能從第5個(gè)字符開始 將余下的所有字符都提取 erful MID B22 10 2 因?yàn)锽22單元格中的字符串中總字符數(shù)是9 此時(shí)要從第10個(gè)字符開始提取 所以系統(tǒng)返回空格 MID B22 0 2 因?yàn)閟tart num小于1 所以系統(tǒng)返回錯(cuò)誤值 VALUE 3 3 LENLEN函函數(shù)數(shù) 用途 計(jì)算目標(biāo)字符中的字符數(shù) 語法 LEN text text是目標(biāo)字符串 說明 空格也視為字符 加到字符串的字符數(shù)中 簡(jiǎn)單示例 wonderfulExcel word運(yùn)算結(jié)果 公式說明 結(jié)果 LEN C34 返回C34單元格里字符串的長度 9 LEN D34 返回D34單元格里字符串的長度 10 應(yīng)用TEXT結(jié)合MID LEN函數(shù)從身份證號(hào)中提出生日信息例 P232 4 4 LEFTLEFT函函數(shù)數(shù) 第 11 頁 共 20 頁 用途 從字符串的左側(cè)開始 按照指定的數(shù)值返回相應(yīng)數(shù)目的字符內(nèi)容 語法 LEFT text num chars text是目標(biāo)字符串 num chars是指定的數(shù)值 該數(shù)值必須大于或等于0 簡(jiǎn)單示例 wonderful運(yùn)算結(jié)果 wondwwonderful 公式說明 結(jié)果 LEFT B43 4 對(duì)A3單元格里的字符串 按從左到右提取前4位字符 wond LEFT B43 因?yàn)槭÷粤薾um chars 因此系統(tǒng)假定其為1 將提取A3單元格里字符串左側(cè)的首字符 W LEFT B43 11 因?yàn)橹付〝?shù)值大于目標(biāo)字符的總數(shù) 所以系統(tǒng)將提取目標(biāo)字符的所有字符 wonderful 5 5 RIGHTRIGHT函函數(shù)數(shù) 用途 從字符串的右側(cè)開始 按指定的數(shù)值返回相應(yīng)數(shù)目的字符內(nèi)容 該函數(shù)恰好與LEFT函數(shù)的功能相反 語法 RIGHT text num chars text是目標(biāo)字符串 num chars是指定的數(shù)值 該數(shù)值必須大于或等于0 簡(jiǎn)單示例 wonderful運(yùn)算結(jié)果 derfull 公式說明 結(jié)果 RIGHT B53 6 對(duì)B53單元格里的字符串 按從右到左提取6位字符 derful RIGHT B53 因?yàn)槭÷粤薾um chars 因此系統(tǒng)假定其為1 將提取A3單元格里字符串右側(cè)的首字符 1 6 6 FINDFIND函函數(shù)數(shù) 用途 查找字符串中首字符編號(hào)為1 接著對(duì)余下的字符按順序依次進(jìn)行編號(hào) 然后在查找字符串中查找目標(biāo)字符 并且返回目標(biāo)字符在查找字符串中的編號(hào) 語法 FIND find text within text start num find text是目標(biāo)字符 within text是查找字符串 start num是查找字符串中開始查找的起始位置 Start num有取值范圍 要大于0 同時(shí)又不能大于查找字符串的長度 說明 FIND函數(shù)區(qū)分大小寫 簡(jiǎn)單示例 subsititute運(yùn)算結(jié)果 2 VALUE VALUE 9 公式說明 結(jié)果 FIND u B65 因?yàn)楹雎粤藄tart num 故而系統(tǒng)假設(shè)start num為1 所以查找的是字符串里第一個(gè) u 的位置 2 FIND u B65 0 因?yàn)閟tart num為0 所以系統(tǒng)會(huì)顯示錯(cuò)誤值 VALUE FIND A B65 3 因?yàn)槟繕?biāo)字符在查找字符串中沒有出現(xiàn) 所以FIND函數(shù)會(huì)顯示錯(cuò)誤值 VALUE FIND u B65 5 從字符串的第5個(gè)字符開始查找第一個(gè) u 的位置 9 7 7 SUBSTITUTESUBSTITUTE函函數(shù)數(shù) 第 12 頁 共 20 頁 用途 將目標(biāo)字符或字符串替換到需要被替換的字符串中 語法 SUBSTITUTE te