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