1.利用身份證號碼獲取公民出生日期
使用Excel進(jìn)行人事管理，常常需要輸入身份證號碼、出生年月日等數據。由于身份證號碼內含有相關(guān)信息，可以使用Excel函數從身份證號碼中提取出生年月日，從而避免重復勞動(dòng)以及可能的輸入錯誤。
現行的身份證號碼左起第7至12個(gè)字符表示出生年月日(存在Y2K問(wèn)題)，我們可以使用MIDB函數從身份證號碼的特定位置，分別提取出生年、月、日，再用CONCATENATE函數將提取出來(lái)的文字合并起來(lái)，就能得到對應的出生年月日。
具體公式為：=CONCATENATE(″19″,MIDB(A4,7,2),″年″,MIDB(A4,9,2),″月″,MIDB(A4,11,2),″日″)式中“19”是針對現行身份證號碼中存在Y2K問(wèn)題設置的，
Fe$d5F"fcHza+|$
它可以在提取的出生年份前加上19。三個(gè)MIDB函數的操作對象存放在A(yíng)4單元格，分別從左起第7、9、11個(gè)字符開(kāi)始提取2個(gè)字符，即得到出生年、月和日，最后由CONCATENATE函數將所有字符合并起來(lái)。
需要注意的是：CONCATENATE函數和MIDB函數的操作對象均為文本，所以存放身份證號碼的單元格必須事先設為文本格式，然后再輸入身份證號。
2.利用身份證號碼獲取公民性別
這個(gè)公式可以從工作表的身份證號碼中提取對應的性別。具體公式為：=IF(RIGHTB(a1,1)=″1″,″男″,IF(RIGHTB(a1,1)=″3″,″男″,IF(RIGHTB(a1,1)=″5″,″男″,IF(RIGHTB(a1,1)=″7″,″男″,IF(RIGHTB(a1,1)=″9″,″男″,″女″)))))，公式中C列存放文本格式的身份證號碼，函數RIGHTB提取字符串右端給定長(cháng)度的字符。根據身份證號碼編號規則，該字符為奇數時(shí)為男，否則為女。經(jīng)過(guò)IF函數五次判斷，凡RIGHTB(c1,1)值為“1”、“3”等奇數的均為男，否則為女。
需要注意的是：RIGHTB(c1,1)提取C1單元格存放的身份證號碼右起第一個(gè)字符，由于它是由文本格式的單字節數字組成的，一個(gè)字符占用一個(gè)字節，故以1為單位計算要提取的字符數。如果是普通漢字或雙字節數字，一個(gè)字符要占用兩個(gè)字節，應以2為單位計算要提取的字符數。
3.成績(jì)等第計算
學(xué)校采用等第評定考試成績(jì)，一般的標準為：考試分數高于或等于85分為A等；考試分數低于85分高于或等于70分為B等；考試分數低于70分高于或等于60分為C等；考試分數低于60分為D等；沒(méi)有參加考試的不劃等第。用Excel計算等第的公式如下(其中分數存放在C列，計算結果存入D列)：=IF(C2>=85,″A″,IF(C2>=70,″B″,IF(C2>=60,″C″,IF(ISNUMBER(C2),″D″,IF(ISBLANK(C2),″ ″)))))，這是一個(gè)典型的IF函數嵌套公式，式中第二個(gè)IF語(yǔ)句是第一個(gè) IF 語(yǔ)句的參數，第三個(gè) IF 語(yǔ)句則是第二個(gè)IF語(yǔ)句的參數，以此類(lèi)推。如果第一個(gè)邏輯判斷表達式C1>=85 為T(mén)RUE(真)，則D1單元格被填入“A”；如果第一個(gè)邏輯判斷表達式C1>=85不成立，則計算第二個(gè)IF語(yǔ)句“IF(C1>=70”；以此類(lèi)推直至計算結束。其中ISNUMBER函數在C1為空時(shí)返回FALSE(假)，接著(zhù)執行最后一個(gè)IF語(yǔ)句，否則在C1單元格中填入“D”。ISBLANK函數C1為空時(shí)返回TRUE(真)，則C1單元格被填入一個(gè)空格。使用ISNUMBER函數和ISBLANK函數，可防止某個(gè)學(xué)生沒(méi)有參加考試(即考試成績(jì)?yōu)榭?，但仍然給他評定為D等的情況發(fā)生。
如果成績(jì)等第劃分標準發(fā)生了變化，只須改變邏輯判斷式中的值(85、70、60)即可，故上式在需要劃分等第的場(chǎng)合具有一定的應用價(jià)值。IF函數常用的比較操作符如^00100005c^：
4.利用IF函數計算工資增加金額
IF函數是Excel中最有用的函數之一，
網(wǎng)v8N(網(wǎng)NH&6S:S0.z它可以解決辦公事務(wù)處理中的許多問(wèn)題。請看以下實(shí)例：
某單位要給職工增加工資，
9URXGMS管`pa絡(luò )8網(wǎng)'供教
其標準為：工齡少于等于5年為20元；工齡多于5年少于等于10年為40元；工齡多于10年少于等于15年為70元；工齡多于15年少于等于20年為100元；工齡多于20年少于等于25年為140元；工齡多于25年少于等于30年為180元；工齡多于30年為240元。
此問(wèn)題的處理方法是：建立工作表，其中A列輸入職工姓名，B列輸入工齡，C列存放計算出來(lái)的增資金額，A1、B1、C1單元格中輸入列標題。再讓單元格指針停留在C2單元格，按筆者以前介紹過(guò)的方法輸入公式：=IF(B2<=5,20,IF(B2<=10,40,IF(B2<=15,70,IF(B2<=20,100,IF(B2<=25,140,IF(B2<=30,180,IF(B2>30,240)))))))。
上式首先計算第一個(gè)邏輯判斷“B2<=5”，若其為“真”，則C2單元格被填入數值“20”；若其為“假”，則計算第二個(gè)IF語(yǔ)句“IF(B2<=10,40”，依次執行直至計算結束。這就是IF函數嵌套的基本形式，加以改造后可應用于其他需要多重邏輯判斷的場(chǎng)合。
需要注意的是：公式中的邏輯判斷條件“B2<=10”、“B2<=15”等不能寫(xiě)成“5<B2<=10”、“10<B2<=15”，否則Excel雖不報告錯誤。但計算中該條件會(huì )被忽略，導致最終計算結果錯誤。另外，IF函數最多只能嵌套七層(如本例)，再多就會(huì )出錯。
另外，IF函數邏輯判斷返回的結果可以是數值或文本等數據，也可以是一個(gè)表達式(如嵌套的下一個(gè)IF函數)。根據這一點(diǎn)，我們可以在判斷返回結果時(shí)進(jìn)行某些計算。仍用上面的例子，建立工作表，在D2單元格輸入以下公式：=IF(B2<=5,C2+20,IF(B2<=10,C2+40,IF(B2<=15,C2+70,IF(B2<=20,C2+100,IF(B2<=25,C2+140,IF(B2<=30,C2+180,IF(B2>30,C2+240)))))))，執行后可以立即算出增加工資后的總工資。當然你也可以將公式中的“C2+20”、“C2+40”等修改為SUM(C2，20)、SUM(C2，40)等，這樣可以執行一些更為復雜的求和運算。
5.SUM函數應用實(shí)例
SUM函數是Excel中使用最多的函數，利用它進(jìn)行求和運算可以忽略存有文本、空格等數據的單元格，語(yǔ)法簡(jiǎn)單、使用方便。
(1)行或列求和
以最常見(jiàn)的工資表為例，它的特點(diǎn)是需要對行內的若干單元格求和，并要自動(dòng)扣除“房電費”、“稅金”等。根據習慣，這些項目并不輸入負數。這時(shí)可在F2單元格輸入如下公式：=SUM(A2:C2，-D2，-E2)。其中A2:C2引用是收入，而D2、E2為支出。由于Excel不允許在單元格引用前面加負號，所以應在表示支出單元格前加負號，這樣即可計算出正確結果。若收入和支出所在的單元格不連續，可將公式寫(xiě)成“=SUM(A2:C2，-D2，E2:F2，-G2)”的形式。
(2)區域求和
區域求和常用于對一張工作表中的所有數據求總計。此時(shí)你可以讓單元格指針停留在存放結果的單元格，然后在Excel編輯欄輸入公式“=SUM()”，用鼠標在括號中間單擊，最后拖過(guò)需要求和的所有單元格。若這些單元格是不連續的，可以按住Ctrl鍵分別拖過(guò)它們。然后用手工在公式引用的單元格前加上負號。
(3)IF函數和SUM函數聯(lián)合應用
IF函數和SUM函數聯(lián)合起來(lái)，可以累計滿(mǎn)足多個(gè)條件的值出現的次數。例如下面的公式就可以統計考試成績(jì)及格的男生人數。每當 Excel 在 B1:B200 中找到“男”時(shí)，它就會(huì )檢查同一行中 C 列(C1:=SUM(IF(B1:B200=″男″,IF(C1:C200>=60,1,0)))。
需要說(shuō)明上式是一個(gè)數組公式，輸入結束時(shí)必須按 下Ctrl+SHIFT鍵然后敲回車(chē)，Excel 2000自動(dòng)在公式兩側加上大括號，成為“{=SUM(IF(B1:B200=″男″,IF(C1:C200>=60,1,0)))}”這是使用數組必不可少的步驟。
注意：SUM函數中的參數，即被求和的單元格或單元格區域不能超過(guò)30個(gè)。換句話(huà)說(shuō)，SUM函數括號中出現的分隔符(逗號)不能多于29個(gè)，否則Excel就會(huì )提示參數太多。對需要參與求和的某個(gè)常數，可用“=SUM(單元格區域，常數)”的形式直接引用，一般不必絕對引用存放該常數的單元格。
6.COUNTIF函數應用實(shí)例
COUNTIF函數可累計滿(mǎn)足某一條件的對象在單元格區域中出現的次數，該條件可以是數值、文本或表達式，在人事、工資和成績(jì)統計中有廣泛用途。請看以下實(shí)例：
某單位要統計工資報表中工資總額大于等于1000元的人數，男職工人數和女職工人數。假設工資總額存放在工作表的F列，職工性別存放在工作表B列，該單位有1000人。
具體計算公式為：統計工資總額大于等于1000元的人數可用公式：“=COUNTIF(F1:F1000,″>=1000″)”；統計男職工人數可用公式：“=COUNTIF(B1:B1000,″=男″)”，統計女職工人數可用公式：“=COUNTIF(B1:B1000,″=女″)”
7.SUMIF函數應用實(shí)例
SUMIF函數可對滿(mǎn)足某一條件的單元格區域求和，該條件可以是數值、文本或表達式，可以應用在人事、工資和成績(jì)統計中。例如：某單位要統計工資報表中職稱(chēng)為中級的員工的工資總額。假設工資總額存放在工作表的F列，員工職稱(chēng)存放在工作表B列。
具體計算公式為：“=SUMIF(B1:B1000,″中級″,F1:F1000)”，其中“B1:B1000”為提供邏輯判斷依據的單元格區域，“中級”為判斷條件即只統計B1:B1000區域中職稱(chēng)為“中級”的單元格，F1:F1000為實(shí)際求和的單元格區域。
另外，COUNTIF 函數和SUMIF函數結合起來(lái)可用于統計考試及格學(xué)生的平均分。具體計算公式為：=SUMIF(A4:A46,″>=60″,A4:A11)/COUNTIF(A4:A46,″>=60″)，式中兩個(gè)函數的參數、邏輯判斷條件的意義請參看上面的實(shí)例自行分析。
8.RANK函數應用實(shí)例
排名次是成績(jì)統計等領(lǐng)域常用的一種分析手段，利用Excel 2000提供的RANK函數可以輕松完成。假設6個(gè)數據存放在C列的C2:C7單元格，
vcdYN育育`BkZh
那么只要選中D2單元格，然后在編輯欄中輸入如下公式：=RANK(C2,$C$2:$C$6)。輸入結束回車(chē)或單擊“輸入”按鈕完成計算。RANK函數的最大優(yōu)點(diǎn)是可以處理重名次，
中V`=#$i網(wǎng)垠\-網(wǎng)xW
可使數值相同的單元格計算出的名次相同。如果你要排序的數據有40個(gè)，只須將以上兩式中的6改為41即可。
9.利用FREQUENCY函數統計成績(jì)分布
統計某次考試的成績(jì)分布，是教育和其他統計的一項常規工作，Excel 2000中的FREQUENCY函數是完成這類(lèi)工作的利器。
(1)打開(kāi)一個(gè)空白EXCEL工作表，在A(yíng)列輸入待統計的考試成績(jì)或其他數據，這里假設有A1:A50共50個(gè)成績(jì)。如果B列的B1:B5單元格分別輸入60、70、80、90、100，則表示統計小于60分、60—70、70—80、80—90和90—100分數段中的成績(jì)個(gè)數。
(2)選中C1:C5單元格存放各分數段的成績(jì)個(gè)數，在編輯欄內輸入公式：=FREQUENCY(A1:A50,B1:B5)
(3)讓光標停留在公式的末尾，按下Shift+Ctrl鍵敲回車(chē)(這是生成數組公式的關(guān)鍵環(huán)節)，C1:C5單元格立刻計算出各分數段中的成績(jì)個(gè)數。
10.＆、MONTH()和NOW()函數的使用
許多報表的標題均含有月份，例如“XX中學(xué)X月教師課時(shí)獎金表”，利用＆、MONTH()和NOW()函數可以實(shí)現月份自動(dòng)輸入。具體做法如下：
(1)將標題占用的所有單元格合并。
(2)在選中合并后的單元格，然后在編輯欄內輸入公式：=“XX中學(xué)”＆MONTH(NOW())＆“月教師課時(shí)獎金表”。
其中＆為連接符，它可以將其左右的字符連接成一個(gè)整體。NOW()函數可以返回電腦內部的系統日期與時(shí)間，MONTH()函數可以將序列數轉換為對應的月份數。這樣，每次打開(kāi)這個(gè)工作表，標題中的月份均會(huì )自動(dòng)更新，形如“XX中學(xué)12月教師課時(shí)獎金表”。如果你想在標題中顯示年份，可以將公式修改為：=“XX中學(xué)”＆ YEAR(NOW())＆“年”＆MONTH(NOW())＆“月教師課時(shí)獎金表”。
