庖丁解牛：學(xué)習(xí)自己需要的函數(shù)

庖丁解牛之：人力資源實(shí)戰(zhàn)應(yīng)用系列函數(shù)

對(duì)于常用EXCEL的人來(lái)說(shuō)，函數(shù)這個(gè)字眼也許并不陌生，但至于函數(shù)的用途與威力，了解或熟悉的人恐怕是少之又少。我們常見(jiàn)的函數(shù)中，有常用函數(shù)，統(tǒng)計(jì)函數(shù)，文本函數(shù)，信息函數(shù)，財(cái)務(wù)函數(shù)等等。但好像未有聽(tīng)說(shuō)過(guò)人力資源函數(shù)這個(gè)字眼。我在這里所說(shuō)的人力資源函數(shù)并不是一組專門的函數(shù)，而是根據(jù)人力資源實(shí)踐工作中，把經(jīng)常用到的一些函數(shù)集中起，以實(shí)現(xiàn)人力資源工作者在實(shí)踐中用極少數(shù)相關(guān)的函數(shù)在來(lái)完成大部分工作。我們?cè)贓H論壇中隨便看看，不泛好帖，特別是函數(shù)，每天數(shù)以千計(jì)的帖子中，光是精華帖就可以讓人目不接霞，如/viewth...p;extra=&page=1，/viewth...p;extra=&page=1，/viewth...p;extra=&page=1，，，/catalog.asp?cate=4

等等，都即細(xì)致，又全面，是函數(shù)初學(xué)者了良師益友。我也常常拜讀。但是，里邊好的東西太多了，而我們真正用到得雖然在其中，但要想把他歸納總結(jié)出來(lái)，為自己所用，恐怕是“冰凍三尺，非一日之寒?！币粋€(gè)人的精力是用限的，人一生要做的事太多，好的東西我們不可能把占完，特別是知識(shí)，夠用就好，或者說(shuō)，先解當(dāng)務(wù)之急，只知其然，待抽出時(shí)間，自己也有點(diǎn)底子是再知其所以然。閑話少說(shuō)，接下來(lái)就跟著我一起開始我們的EXCEL人力資源函數(shù)之旅吧。

當(dāng)然，我們要想學(xué)習(xí)函數(shù)，首先，一點(diǎn)點(diǎn)基礎(chǔ)還是要的，至少我們要知道函數(shù)是怎么回事吧。那下面我們就來(lái)看一看函數(shù)的一點(diǎn)點(diǎn)基礎(chǔ)知識(shí)，對(duì)公式和函數(shù)有個(gè)初步的了解。一、函數(shù)應(yīng)用基礎(chǔ)

（一）函數(shù)和公式

1.什么是函數(shù)

Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常用的求和函數(shù)SUM為例，它的語(yǔ)法是“SUM(number1,number2,)”。其中“SUM”稱為函數(shù)名稱，一個(gè)函數(shù)只有唯一的一個(gè)名稱，它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號(hào)，接著是用逗號(hào)分隔的稱為參數(shù)的內(nèi)容，最后用一個(gè)右括號(hào)表示函數(shù)結(jié)束。參數(shù)是函數(shù)中最復(fù)雜的組成部分，它規(guī)定了函數(shù)的運(yùn)算對(duì)象、順序或結(jié)構(gòu)等。使得用戶可以對(duì)某個(gè)單元格或區(qū)域進(jìn)行處理，如分析存款利息、確定成績(jī)名次、計(jì)算三角函數(shù)值等。按照函數(shù)的來(lái)源，Excel函數(shù)可以分為內(nèi)置函數(shù)和擴(kuò)展函數(shù)兩大類。前者只要啟動(dòng)了Excel，用戶就可以使用它們；而后者必須通過(guò)單擊“工具→加載宏”菜單命令加載，然后才能像內(nèi)置函數(shù)那樣使用。

2.什么是公式

函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說(shuō)前者是Excel預(yù)先定義好的特殊公式，后者就是由用戶自行設(shè)計(jì)對(duì)工作表進(jìn)行計(jì)算和處理的公式。以公式“=SUM(E1:H1)*A1+26”為例，它要以等號(hào)“=”開始，其內(nèi)部可以包括函數(shù)、引用、運(yùn)算符和常量。上式中的“SUM(E1:H1)”是函數(shù)，“A1”則是對(duì)單元格A1的引用(使用其中存儲(chǔ)的數(shù)據(jù))，“26”則是常量，“*”和“+”則是算術(shù)運(yùn)算符(另外還有比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符)。如果函數(shù)要以公式的形式出現(xiàn)，它必須有兩個(gè)組成部分，一個(gè)是函數(shù)名稱前面的等號(hào)，另一個(gè)則是函數(shù)本身。

（二）函數(shù)的參數(shù)

函數(shù)右邊括號(hào)中的部分稱為參數(shù)，假如一個(gè)函數(shù)可以使用多個(gè)參數(shù)，那么參數(shù)與參數(shù)之間使用半角逗號(hào)進(jìn)行分隔。參數(shù)可以是常量(數(shù)字和文本)、邏輯值(例如TRUE或FALSE)、數(shù)組、錯(cuò)誤值(例如#N/A)或單元格引用(例如E1:H1)，甚至可以是另一個(gè)或幾個(gè)函數(shù)等。參數(shù)的類型和位置必須滿足函數(shù)語(yǔ)法的要求，否則將返回錯(cuò)誤信息。

1.常量

常量是直接輸入到單元格或公式中的數(shù)字或文本，或由名稱所代表的數(shù)字或文本值，例如數(shù)字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式計(jì)算出的結(jié)果都不是常量，因?yàn)橹灰降膮?shù)發(fā)生了變化，它自身或計(jì)算出來(lái)的結(jié)果就會(huì)發(fā)生變化。

2.邏輯值

邏輯值是比較特殊的一類參數(shù)，它只有TRUE(真)或FALSE(假)兩種類型。例如在公式“=IF(A3=0,"",A2/A3)”中，“A3=0”就是一個(gè)可以返回TRUE(真)或FALSE(假)兩種結(jié)果的參數(shù)。當(dāng)“A3=0”為TRUE(真)時(shí)在公式所在單元格中填入“0”，否則在單元格中填入“A2/A3”的計(jì)算結(jié)果。

3.數(shù)組

數(shù)組用于可產(chǎn)生多個(gè)結(jié)果，或可以對(duì)存放在行和列中的一組參數(shù)進(jìn)行計(jì)算的公式。Excel中有常量和區(qū)域兩類數(shù)組。前者放在“{}”(按下Ctrl+Shift+Enter組合鍵自動(dòng)生成)內(nèi)部，而且內(nèi)部各列的數(shù)值要用逗號(hào)“,”隔開，各行的數(shù)值要用分號(hào)“;”隔開。假如你要表示第1行中的56、78、89和第2行中的90、76、80，就應(yīng)該建立一個(gè)2行3列的常量數(shù)組“{56,78,89;90,76,80}。

區(qū)域數(shù)組是一個(gè)矩形的單元格區(qū)域，該區(qū)域中的單元格共用一個(gè)公式。例如公式“=TREND(B1:B3,A1:A3)”作為數(shù)組公式使用時(shí)，它所引用的矩形單元格區(qū)域“B1:B3,A1:A3”就是一個(gè)區(qū)域數(shù)組。

4.錯(cuò)誤值

使用錯(cuò)誤值作為參數(shù)的主要是信息函數(shù)，例如“ERROR.TYPE”函數(shù)就是以錯(cuò)誤值作為參數(shù)。它的語(yǔ)法為“ERROR.TYPE(error_val)”，如果其中的參數(shù)是#NUM!，則返回?cái)?shù)值“6”。

5.單元格引用

單元格引用是函數(shù)中最常見(jiàn)的參數(shù)，引用的目的在于標(biāo)識(shí)工作表單元格或單元格區(qū)域，并指明公式或函數(shù)所使用的數(shù)據(jù)的位置，便于它們使用工作表各處的數(shù)據(jù)，或者在多個(gè)函數(shù)中使用同一個(gè)單元格的數(shù)據(jù)。還可以引用同一工作簿不同工作表的單元格，甚至引用其他工作簿中的數(shù)據(jù)。根據(jù)公式所在單元格的位置發(fā)生變化時(shí)，單元格引用的變化情況，我們可以引用分為相對(duì)引用、絕對(duì)引用和混合引用三種類型。以存放在F2單元格中的公式“=SUM(A2:E2)”為例，當(dāng)公式由F2單元格復(fù)制到F3單元格以后，公式中的引用也會(huì)變化為“=SUM(A3:E3)”。若公式自F列向下繼續(xù)復(fù)制，“行標(biāo)”每增加1行，公式中的行標(biāo)也自動(dòng)加1。如果上述公式改為“=SUM($A$3:$E＄3)”，則無(wú)論公式復(fù)制到何處，其引用的位置始終是“A3:E3”區(qū)域。

混合引用有“絕對(duì)列和相對(duì)行”，或是“絕對(duì)行和相對(duì)列”兩種形式。前者如“=SUM($A3:$E3)”，后者如“=SUM(A$3:E$3)”。上面的幾個(gè)實(shí)例引用的都是同一工作表中的數(shù)據(jù)，如果要分析同一工作簿中多張工作表上的數(shù)據(jù)，就要使用三維引用。假如公式放在工作表Sheet1的C6單元格，要引用工作表Sheet2的“A1:A6”和Sheet3的“B2:B9”區(qū)域進(jìn)行求和運(yùn)算，則公式中的引用形式為“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是說(shuō)三維引用中不僅包含單元格或區(qū)域引用，還要在前面加上帶“!”的工作表名稱。假如你要引用的數(shù)據(jù)來(lái)自另一個(gè)工作簿，如工作簿Book1中的SUM函數(shù)要絕對(duì)引用工作簿Book2中的數(shù)據(jù)，其公式為“=SUM([Book2]Sheet1!SAS1:SAS8,[Book2]Sheet2!SBS1:SBS9)”，也就是在原來(lái)單元格引用的前面加上“[Book2]Sheet1!”。放在中括號(hào)里面的是工作簿名稱，帶“!”的則是其中的工作表名稱。即是跨工作簿引用單元格或區(qū)域時(shí)，引用對(duì)象的前面必須用“!”作為工作表分隔符，再用中括號(hào)作為工作簿分隔符。不過(guò)三維引用的要受到較多的限制，例如不能使用數(shù)組公式等。

提示：上面介紹的是Excel默認(rèn)的引用方式，稱為“A1引用樣式”。如果你要計(jì)算處在“宏”內(nèi)的行和列，必須使用“R1C1引用樣式”。在這種引用樣式中，Excel使用“R”加“行標(biāo)”和“C”加“列標(biāo)”的方法指示單元格位置。啟用或

關(guān)閉R1C1引用樣式必須單擊“工具→選項(xiàng)”菜單命令，打開對(duì)話框的“常規(guī)”選項(xiàng)卡，選中或清除“設(shè)置”下的“R1C1引用樣式”選項(xiàng)。由于這種引用樣式很少使用，限于篇幅本文不做進(jìn)一步介紹。

6.嵌套函數(shù)

除了上面介紹的情況外，函數(shù)也可以是嵌套的，即一個(gè)函數(shù)是另一個(gè)函數(shù)的參數(shù)，例如“=IF(OR(RIGHTB(E2,1)="1"，RIGHTB(E2,1)="3"，RIGHTB(E2,1)="5"，RIGHTB(E2,1)="7"，RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函數(shù)使用了嵌套的RIGHTB函數(shù)，并將后者返回的結(jié)果作為IF的邏輯判斷依據(jù)。

7.名稱和標(biāo)志

為了更加直觀地標(biāo)識(shí)單元格或單元格區(qū)域，我們可以給它們賦予一個(gè)名稱，從而在公式或函數(shù)中直接引用。例如“B2:B46”區(qū)域存放著學(xué)生的物理成績(jī)，求解平均分的公式一般是“=AVERAGE(B2:B46)”。在給B2:B46區(qū)域命名為“物理分?jǐn)?shù)”以后，該公式就可以變?yōu)椤?AVERAGE(物理分?jǐn)?shù))”，從而使公式變得更加直觀。

給一個(gè)單元格或區(qū)域命名的方法是：選中要命名的單元格或單元格區(qū)域，鼠標(biāo)單擊編輯欄頂端的“名稱框”，在其中輸入名稱后回車。也可以選中要命名的單元格或單元格區(qū)域，單擊“插入→名稱→定義”菜單命令，在打開的“定義名稱”對(duì)話框中輸入名稱后確定即可。如果你要?jiǎng)h除已經(jīng)命名的區(qū)域，可以按相同方法打開“定義名稱”對(duì)話框，選中你要?jiǎng)h除的名稱刪除即可。

由于Excel工作表多數(shù)帶有“列標(biāo)志”。例如一張成績(jī)統(tǒng)計(jì)表的首行通常帶有“序號(hào)”、“姓名”、“數(shù)學(xué)”、“物理”等“列標(biāo)志”(也可以稱為字段)，如果單擊“工具→選項(xiàng)”菜單命令，在打開的對(duì)話框中單擊“重新計(jì)算”選項(xiàng)卡，選中“工作簿選項(xiàng)”選項(xiàng)組中的“接受公式標(biāo)志”選項(xiàng)，公式就可以直接引用“列標(biāo)志”了。例如“B2:B46”區(qū)域存放著學(xué)生的物理成績(jī)，而B1單元格已經(jīng)輸入了“物理”字樣，則求物理平均分的公式可以寫成“=AVERAGE(物理)”。

需要特別說(shuō)明的是，創(chuàng)建好的名稱可以被所有工作表引用，而且引用時(shí)不需要在名稱前面添加工作表名(這就是使用名稱的主要優(yōu)點(diǎn))，因此名稱引用實(shí)際上是一種絕對(duì)引用。但是公式引用“列標(biāo)志”時(shí)的限制較多，它只能在當(dāng)前數(shù)據(jù)列的下方引用，不能跨越工作表引用，但是引用“列標(biāo)志”的公式在一定條件下可以復(fù)制。從本質(zhì)上講，名稱和標(biāo)志都是單元格引用的一種方式。因?yàn)樗鼈儾皇俏谋荆褂脮r(shí)名稱和標(biāo)志都不能添加引號(hào)。

（三）函數(shù)輸入方法

對(duì)Excel公式而言，函數(shù)是其中的主要組成部分，因此公式輸入可以歸結(jié)為函數(shù)輸入的問(wèn)題。

1.“插入函數(shù)”對(duì)話框

“插入函數(shù)”對(duì)話框是Excel輸入公式的重要工具，以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”為例，Excel輸入該公式的具體過(guò)程是：

首先選中存放計(jì)算結(jié)果(即需要應(yīng)用公式)的單元格，單擊編輯欄(或工具欄)中的“fx”按鈕，則表示公式開始的“=”出現(xiàn)在單元格和編輯欄，然后在打開的“插入函數(shù)”對(duì)話框中的“選擇函數(shù)”列表找到“SUM”函數(shù)。如果你需要的函數(shù)不在里面，可以打開“或選擇類別”下拉列表進(jìn)行選擇。最后單擊“確定”按鈕，打開“函數(shù)參數(shù)”對(duì)話框。

對(duì)SUM函數(shù)而言，它可以使用從number1開始直到number30共30個(gè)參數(shù)。對(duì)上面的公式來(lái)說(shuō)，首先應(yīng)當(dāng)把光標(biāo)放在對(duì)話框的“number1”框中，單擊工作簿中的“Sheet2!”工作表標(biāo)簽，“Sheet2!”即可自動(dòng)進(jìn)入其中，接著鼠標(biāo)拖動(dòng)選中你要引用的區(qū)域即可。接著用鼠標(biāo)單擊對(duì)話框的“number2”框，單擊工作簿中的“Sheet3!”工作表標(biāo)簽，其名稱“Sheet3!”即可自動(dòng)進(jìn)入其中，再按相同方法選擇要引用的單元格區(qū)域即可。

上述方法的最大優(yōu)點(diǎn)就是引用的區(qū)域很準(zhǔn)確，特別是三維引用時(shí)不容易發(fā)生工作表或工作簿名稱輸入錯(cuò)誤的問(wèn)題。

2.編輯欄輸入

如果你要套用某個(gè)現(xiàn)成公式，或者輸入一些嵌套關(guān)系復(fù)雜的公式，利用編輯欄輸入更加快捷。

首先選中存放計(jì)算結(jié)果的單元格；鼠標(biāo)單擊Excel編輯欄，按照公式的組成順序依次輸入各個(gè)部分，公式輸入完畢后，單擊編輯欄中的“輸入”(即“√”)按鈕(或回車)即可。手工輸入時(shí)同樣可以采取上面介紹的方法引用區(qū)域，以公式“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”為例，你可以先在編輯欄中輸入“=SUM()”，然后將光標(biāo)插入括號(hào)中間，再按上面介紹的方法操作就可以引用輸入公式了。但是分隔引用之間的逗號(hào)必須用手工輸入，而不能像“插入函數(shù)”對(duì)話框那樣自動(dòng)添加。附件檔案附件.rar(158.43KB)二、人力資源建庫(kù)與建表的幾個(gè)函數(shù)

一般說(shuō)來(lái)，企業(yè)要對(duì)人員進(jìn)行管理，一個(gè)簡(jiǎn)單明了的花名冊(cè)與人員的電子檔案是必有可少的，但企業(yè)的人員是在不斷的流動(dòng)的，特別是針對(duì)一些低技能的勞動(dòng)密集型企業(yè)，員工的流動(dòng)量是相當(dāng)大的，每進(jìn)出一個(gè)員工，人事部門要人事部門都要做若干的資料，一些大型一點(diǎn)的企業(yè)，一個(gè)月一招就是好幾百，上千人。這樣的工作量相當(dāng)大。通常情況下，人事檔案主要是根據(jù)企業(yè)的一些基本事項(xiàng)與員工的身份證信息作為基礎(chǔ)來(lái)建立，而真正麻煩與肯出錯(cuò)的內(nèi)容也就是身份證，如姓名，性別，出生日期，籍貫，身份證號(hào)，年齡，工齡，等等，都是圍繞著身份證來(lái)的，特別是身份證號(hào)，最簡(jiǎn)單的18個(gè)數(shù)字，也是最容易出錯(cuò)的，對(duì)果用手工對(duì)的話，相當(dāng)枯燥無(wú)味，當(dāng)然正確率比較低，特別是一些假證，我們不能每一個(gè)都是靠肉眼去識(shí)別。如果用函數(shù)解決，就很多問(wèn)題都迎刃而解了。

（附件）

根據(jù)附件表來(lái)看，這張簡(jiǎn)單的人事檔案表有21項(xiàng)，除去我們要隱藏的輔助列也還有十七八列，如果是一項(xiàng)一項(xiàng)輸，就算是100個(gè)人，我們夠輸入一陣子的了，一名人事工作者不可能只做輸入檔案一項(xiàng)工作。如果把大量的時(shí)間花在檔案上，其它的工作也就無(wú)法展開了。接下來(lái)，我看就從第一個(gè)涉及函數(shù)的單元格開始講解：

=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))

在E3單元格中，我們看到了有這樣一個(gè)公式，她是由四個(gè)函數(shù)構(gòu)成的，其目的是根據(jù)身份證號(hào)的第17位數(shù)來(lái)判斷該編號(hào)人員的性別。

我們先來(lái)解析這四個(gè)函數(shù)中的第一個(gè)：IF函數(shù)。IF函數(shù)是一個(gè)條件判斷函數(shù)，我們來(lái)看下她的寫法與用途。

用途：執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達(dá)式的真假，返回不同的結(jié)果，從而執(zhí)行數(shù)值或公式的條件檢測(cè)任務(wù)。語(yǔ)法：IF(logical_test，value_if_true，value_if_false)。參數(shù)：Logical_test計(jì)算結(jié)果為TRUE或FALSE的任何數(shù)值或表達(dá)式；Value_if_true是Logical_test為TRUE時(shí)函數(shù)的返回值，如果logical_test為TRUE并且省略了value_if_true，則返回TRUE。而且Value_if_true可以是一個(gè)表達(dá)式；Value_if_false是Logical_test為FALSE時(shí)函數(shù)的返回值。如果logical_test為FALSE并且省略value_if_false，則返回FALSE。Value_if_false也可以是一個(gè)表達(dá)式。實(shí)例：公式“=IF(C2>=85，"A"，IF(C2>=70，"B"，IF(C2>=60，"C"，IF(C2<60，"D"))))”，其中第二個(gè)IF語(yǔ)句同時(shí)也是第一個(gè)IF語(yǔ)句的參數(shù)。同樣，第三個(gè)IF語(yǔ)句是第二個(gè)IF語(yǔ)句的參數(shù)，以此類推。例如，若第一個(gè)邏輯判斷表達(dá)式C2>=85成立，則D2單元格被賦值“A”；如果第一個(gè)邏輯判斷表達(dá)式C2>=85不成立，則計(jì)算第二個(gè)IF語(yǔ)句“IF(C2>=70”；以此類推直至計(jì)算結(jié)束，該函數(shù)廣泛用于需要進(jìn)行邏輯判斷的場(chǎng)合。

在本例中，IF函數(shù)主要起到了兩個(gè)作用，第一次出現(xiàn)（IF(M3＝””,””……，這是什么意思呢，它是起一個(gè)錯(cuò)誤控制的作用，意思為如果M3為空值，（即M3這個(gè)單元格沒(méi)有內(nèi)容的話，就不對(duì)它進(jìn)行任何的操作，其反回結(jié)果為空；第二次出現(xiàn)（……IF(MOD(……，在這里又是什么意思呢？在這個(gè)地方是前邊一個(gè)條件的補(bǔ)充，即如果M3單元格不為空呢，就執(zhí)行后邊的操作。

接下來(lái)，再分析=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))中的LEFT函數(shù)。

用途：根據(jù)指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。此函數(shù)用于雙字節(jié)字符。語(yǔ)法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。參數(shù)：Text是包含要提取字符的文本串；Num_chars指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。Num_bytes按字節(jié)數(shù)指定由LEFTB提取的字符數(shù)。實(shí)例：如果A1=電腦愛(ài)好者，則LEFT(A1，2)返回“電腦”，LEFTB(A1，2)返回“電”。

該函數(shù)在這里的作用是將M3中的18位身份證號(hào)的前17號(hào)提取出來(lái)，以供我們的MOD函數(shù)使用。為什么要把它取出來(lái)呢，我們都知道，按身份證編碼的規(guī)則，18位數(shù)的身份證前17位是性別位，它所記錄的是性別信息。所以，我們先將它提取出來(lái)。

接下來(lái)，要分析的是=IF(M3="","",IF(MOD(RIGHT(LEFT(M3,17)),2),"男","女"))中的RIGHT函數(shù)，RIGHT或RIGHTB

用途：RIGHT根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。RIGHTB根據(jù)所指定的字節(jié)數(shù)返回文本串中最后一個(gè)或多個(gè)字符。語(yǔ)法：RIGHT(text，num_chars)，RIGHTB(text，num_bytes)。參數(shù)：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字符數(shù)，它必須大于或等于0。如果num_chars大于文本長(zhǎng)度，則RIGHT返回所有文本。如果忽略num_chars，則假定其為1。Num_bytes指定欲提取字符的字節(jié)數(shù)。實(shí)例：如果A1=學(xué)習(xí)的革命，則公式“=RIGHT(A1，2)”返回“革命”，=RIGHTB(A1，2)返回“命”。

從邊來(lái)的解釋來(lái)看，RIGHT函數(shù)是把LEFT函數(shù)所提取出來(lái)的17位數(shù)最又邊的一位數(shù)取出來(lái)，當(dāng)然，這里的兩步如果換成MID(M3,17,1)其效果也是一樣的。至于MID,安排到另外的實(shí)例中講解。

接下來(lái)就是MOD出場(chǎng)了。公式中的MOD函數(shù)其用途為：返回兩數(shù)相除的余數(shù)，其結(jié)果的正負(fù)號(hào)與除數(shù)相同。語(yǔ)法：MOD(number，divisor)參數(shù)：Number為被除數(shù)，Divisor為除數(shù)(divisor不能為零)。實(shí)例：如果A1=51，則公式“=MOD(A1，4)”返回3；=MOD(-101，-2)返回–1。

看了上邊的解釋，我們應(yīng)該可以看得出，MOD在這里就是將前邊兩位函數(shù)所取出來(lái)的身份證編碼中的第17位數(shù)除以2，得出其余數(shù)，根據(jù)余數(shù)來(lái)驗(yàn)證第身份證的第17位數(shù)是奇數(shù)還是偶數(shù)。最后把任務(wù)交給公式中的第二個(gè)IF函數(shù)。

編碼規(guī)則中，如果第17位是奇數(shù)，則該身份證為男性，為偶數(shù)則是女性。這一個(gè)IF函數(shù)在這里就是根據(jù)MOD所求出來(lái)的余數(shù)確定，如有余數(shù)，則是男，反之，則是女，這就是后邊那的“男”，與“女”。

根據(jù)以上的步驟，我們就完成了第一個(gè)公式的編寫。

最后，我也用一句小學(xué)老師的語(yǔ)氣問(wèn)一下，大家是否明白了呢。如果明白，請(qǐng)自己動(dòng)手操作。我知道在EH論壇，這點(diǎn)東西也就是雕蟲小技，班門弄斧。如帖子有不妥之處，還敬請(qǐng)各位老師與朋友批評(píng)批評(píng)。接下來(lái)，應(yīng)該是第二/四個(gè)公式了，為什么把第二個(gè)和第四個(gè)一起分析呢，因?yàn)樗麄儽容^相似。先看一下公式吧：

=DATEDIF(F3,TODAY(),"y")

=IF(H3="","",DATEDIF(H3,TODAY(),"y"))

在這兩個(gè)公式當(dāng)中，都運(yùn)用了兩個(gè)日期函數(shù)，一個(gè)是DATEDIF，一個(gè)是TODAY函數(shù)，但是他們的功能確有差別。先看一下TODAY函數(shù)吧，函數(shù)：TODAY

用途：返回系統(tǒng)當(dāng)前日期的序列號(hào)。參數(shù)：無(wú)語(yǔ)法：TODAY()實(shí)例：公式“=TODAY()”返回2001-8-28(執(zhí)行公式時(shí)的系統(tǒng)時(shí)間)。這是一個(gè)比較特殊的函數(shù)，它沒(méi)有參數(shù)，或都說(shuō)它的參數(shù)就是一個(gè)空括號(hào)，從前邊的資料當(dāng)中，我們可以看出，TODAY函數(shù)在這兩個(gè)公式中都是作為DAETDIF函數(shù)的參數(shù)身份出現(xiàn)的。但可能有朋友要問(wèn)，為什么是用TODAY呢，難道用在DATEDIF的第二個(gè)參數(shù)位輸入今天的日期不行嗎？行，但是是有差距的，因?yàn)橹苯虞斎肴掌冢且粋€(gè)靜態(tài)數(shù)，也就是永遠(yuǎn)不會(huì)改變，但我們的檔案名冊(cè)不可能只用一天兩天或是一年半載。如輸入（2010-04-19）那么，它就永遠(yuǎn)是這個(gè)日期，而用TODAY函數(shù)呢，則會(huì)隨著你的系統(tǒng)日期變換，永遠(yuǎn)保持著最新日期。至于用那個(gè)，你自己可以選擇。

DATEDIF函數(shù)：這個(gè)函數(shù)可以計(jì)算兩個(gè)日期間的間隔，它能夠顯示周、月、年。其語(yǔ)法結(jié)構(gòu)為：=DATEDIF(第一日期,第二日期,"差異")

第一日期:兩個(gè)日期中較早的一個(gè)日期

第二日期：兩日期中距今天最近的日期。

"差異":這正是你想要計(jì)算的.（表為參數(shù)三內(nèi)容，不同的選擇會(huì)有不同的結(jié)果）

"d"

兩日期之間的天數(shù).

"m"

兩日期之間的月數(shù).

"y"

兩日期之間的年數(shù).

"yd"

把兩日期年成是同一年的相差月數(shù).

"ym"

把兩日期年成是同一年的相差天數(shù).

"md"

把兩日期年成是同一年、同一月的相差天數(shù).

從上面的資料我們可以看出，DATEDIF所執(zhí)行的就是用“出生日期”（即H3）與“當(dāng)前日期”（即TODAY求出來(lái)的）作比較，然后求出它們之間的整年差異數(shù)，即為我們想要求得的年齡。

最后一個(gè)IF同前一個(gè)公式第一次出現(xiàn)一樣，都為了屏閉錯(cuò)誤值。

好的，我們這個(gè)單元格的內(nèi)容也就分析完了。附件附件二.rar(2.96KB)品完茶后，我們繼續(xù)前邊的內(nèi)容，看一看第三個(gè)填有函數(shù)的單元格，即為出生日期單元格。=--TEXT(IF(LEN(M3)=15,19,"")&MID(M3,7,6+IF(LEN(M3)=18,2)),"#-00-00")

我們將該公式簡(jiǎn)化為：=--TEXT(MID，M3,7,8),"#-00-00")如果資料里的身份證號(hào)全是18號(hào)數(shù)，這樣寫是最方便的，但是，這樣寫的唯一缺點(diǎn)就是，如果資料里邊有15位的，就會(huì)出現(xiàn)錯(cuò)誤值。簡(jiǎn)化后的公式，只有嵌套兩個(gè)級(jí)別，一個(gè)是TEXT函數(shù)，另一個(gè)就是作為參數(shù)出現(xiàn)的MID函數(shù)，由于MID函數(shù)我們?cè)谇爸v中已經(jīng)講解過(guò)，相信朋友們已經(jīng)掌握，這里只說(shuō)明一下它的作用是將身份證信息中的第7—14位數(shù)提取出來(lái)。即出生日期字段提取給TEXT處理，讓其設(shè)置日期格式。接下來(lái)請(qǐng)看TEXT函數(shù)的基本定義與寫法。

.函數(shù)：TEXT，將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。

語(yǔ)法：TEXT(value,format_text)

Value

為數(shù)值、計(jì)算結(jié)果為數(shù)字值的公式，或?qū)Π瑪?shù)字值的單元格的引用。

Format_text

為“單元格格式”對(duì)話框中“數(shù)字”選項(xiàng)卡上“分類”框中的文本形式的數(shù)字格式。

說(shuō)明

?

Format_text不能包含星號(hào)(*)。

?

通過(guò)“格式”菜單調(diào)用“單元格”命令，然后在“數(shù)字”選項(xiàng)卡上設(shè)置單元格的格式，只會(huì)更改單元格的格式而不會(huì)影響其中的數(shù)值。使用函數(shù)TEXT可以將數(shù)值轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計(jì)算。

從上邊的信息，我們可以了解到，TEXT函數(shù)就是將數(shù)字或文本轉(zhuǎn)換為指定的文本。什么意思呢？如果不進(jìn)行轉(zhuǎn)換，MID所提取出來(lái)的8位日期值就不會(huì)以日期的格式出現(xiàn)，電腦會(huì)將其顯示為8位連續(xù)的數(shù)字（如：19790628，就不會(huì)顯示為：1979-06-28）。這樣不是我們想要的結(jié)果，所以，外套了一個(gè)TEXT文本函數(shù)。在這里，MID所提取出來(lái)的就是TEXT函數(shù)的第一個(gè)參數(shù)，即，value,(數(shù)值或是文本)，而“#-00-00”則是TEXT函數(shù)的第二個(gè)參數(shù)，是我們想要顯示的格式，它表為顯示為“年-月-日”格式，這樣就得到我們想要的日期格式了。

前邊那個(gè)較長(zhǎng)的公式，我們也來(lái)做個(gè)簡(jiǎn)單的解說(shuō)：即：用IF判斷身份證號(hào)所在單元格的位數(shù)（用LEN提取出來(lái)的）是否＝15位，如是，則加上19，否則就不加，即“”空值。

當(dāng)然，也可以用另一種笨辦法：=MID(M3,7,4)&MID(M3,11,2)&MID(M3,13,2)=VLOOKUP(MID(M3,1,2),Sheet1!A:D,2,0)

接下來(lái)我們共同來(lái)品一品VLOOKUP套MID的用法。MID在前面我們已經(jīng)品過(guò)了，在此不再贅述，這里我們主要是來(lái)看一下VLOOKUP函數(shù)。

函數(shù)VLOOKUP用途：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表首列時(shí)，可以使用函數(shù)VLOOKUP代替函數(shù)HLOOKUP。語(yǔ)法：VLOOKUP(lookup_value，table_array，

col_index_num，range_lookup)參數(shù)：Lookup_value為需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對(duì)區(qū)域或區(qū)域名稱的引用。Col_index_num為table_array中待返回的匹配值的列序號(hào)。Col_index_num為1時(shí)，返回table_array第一列中的數(shù)值；col_index_num為2，返回table_array第二列中的數(shù)值，以此類推。Range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時(shí)是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說(shuō)，如果找不到精確匹配值，則返回小于

lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯(cuò)誤值#N/A。實(shí)例：如果A1=23、A2=45、A3=50、A4=65，則公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。

VLOOKUP是一個(gè)相當(dāng)實(shí)用而且常用的函數(shù)，在論壇上有相當(dāng)多的帖子，我這里就對(duì)精帖做一個(gè)借用吧，現(xiàn)有資源利用。這里VLOOKUP的任務(wù)是利用MID提取出來(lái)的身份證地址編碼，去我們建立的身份證地址庫(kù)中查找相對(duì)應(yīng)的地址，并且引用到檔案的籍貫欄中來(lái)。

另注：此樓VLOOKUP附件為陳澤祥老師作品，因?yàn)榫帉懙脤?shí)在精彩，借來(lái)一用。謝謝了。附件VLOOKUP動(dòng)態(tài)圖解.rar(26.09KB)檔案附件.rar(158.43KB)=IF(LEN(M3)=15,M3,LEFT(REPLACE(M3,7,2,),15))

這一樓我們來(lái)談?wù)勆矸葑C的減位。為什么我們要對(duì)18位數(shù)身份證號(hào)碼減位呢，是想展示自己對(duì)函數(shù)的運(yùn)用嗎？絕對(duì)不是。我們都知道早期的身份證號(hào)是由15位數(shù)字編寫而成的。

1、第l一6位數(shù)為行政區(qū)劃代碼；

2、第7—12位數(shù)為出生日期代碼；

3、第1315位數(shù)為分配順序代碼；

(1)、行政區(qū)劃代碼，是指公民第一次申領(lǐng)居民身份證時(shí)的常住戶口所在地的行政地區(qū)。

(2)、出生日期代碼，第7—8位數(shù)代表年份(年份前面二位數(shù)省略)，第9—10位數(shù)代表月份(月份為l位數(shù)的前面加零)。第11一12位數(shù)代表日期(日期為1位數(shù)的前面加零)。

(3)、分配順序代碼，是按人口數(shù)統(tǒng)一合理分配以固定順序給予每個(gè)人的順序號(hào).

這是就是我們?cè)缙?5位身份證的編碼結(jié)構(gòu)，自1999年10月1日起在全國(guó)建立和實(shí)行公民身份證號(hào)碼制度.公民身份證號(hào)碼按照GB11643—1999《公民身份證號(hào)碼》國(guó)家標(biāo)準(zhǔn)編制，由18位數(shù)字組成：

前6位為行政區(qū)劃分代碼，第7位至14位為出生日期碼，第15位至17位為順序碼，第18位為校驗(yàn)碼。

第18位號(hào)碼是校驗(yàn)碼，目的在于檢測(cè)身份證號(hào)碼的正確性，是由計(jì)算機(jī)隨機(jī)產(chǎn)生的，所以不再是男性為單數(shù)，女性為雙數(shù),而以第十七位決定性別.

從上邊的內(nèi)容我們可以看出，18位數(shù)的身份證號(hào)是由15位升級(jí)而來(lái)的，升級(jí)的原則只是在原出生年份前增加了“19”，這樣就不至于1910年和2010年相重復(fù)（因?yàn)檫@兩年數(shù)取后邊兩位都是10年。）而第18位則是身份證號(hào)正確性的校驗(yàn)碼。也就是說(shuō)我們前邊的編碼輸入是否正確，或是該號(hào)碼本身是否正確，都可以通過(guò)校驗(yàn)碼來(lái)檢驗(yàn)出來(lái)。清楚了這個(gè)，我們就來(lái)看下這個(gè)函數(shù)公式吧：

=IF(LEN(M3)=15,M3,LEFT(REPLACE(M3,7,2,),15))

這里的IF函數(shù)因?yàn)榍斑呉呀?jīng)講過(guò)，不再贅述，這里我們主要來(lái)品一品LEN函數(shù)和REPLACE函數(shù)。

LEN也應(yīng)用于：LENB。LEN返回文本字符串中的字符數(shù)。LENB返回文本字符串中用于代表字符的字節(jié)數(shù)。此函數(shù)用于雙字節(jié)字符。語(yǔ)法：LEN(text)：LENB(text)。Text

是要查找其長(zhǎng)度的文本?？崭駥⒆鳛樽址M(jìn)行計(jì)數(shù)。

該函數(shù)在這里是以IF函數(shù)的參數(shù)出現(xiàn)的，它的主要功能是將我們?cè)袨?5位數(shù)的身份證號(hào)取15位，也就是將其原記錄保存下來(lái)。

REPLACE也應(yīng)用于：REPLACEB。REPLACE使用其他文本字符串并根據(jù)所指定的字符數(shù)替換某文本字符串中的部分文本。REPLACEB使用其他文本字符串并根據(jù)所指定的字符數(shù)替換某文本字符串中的部分文本。此函數(shù)專為雙字節(jié)字符使用。

語(yǔ)法：REPLACE(old_text,start_num,num_chars,new_text)

REPLACEB(old_text,start_num,num_bytes,new_text)

Old_text

是要替換其部分字符的文本。

Start_num

是要用new_text替換的old_text中字符的位置。

Num_chars

是希望REPLACE使用new_text替換old_text中字符的個(gè)數(shù)。

Num_bytes

是希望REPLACE使用new_text替換old_text中字節(jié)的個(gè)數(shù)。

New_text

是要用于替換old_text中字符的文本。

從前邊的解釋中，我們可以看出，REPLACE函數(shù)在這里的主要作用是將18位數(shù)編號(hào)的第7-8位數(shù)去掉，然后交給LEFT函數(shù)，從16位中提取左邊的15位，（即把驗(yàn)證碼去掉）。這樣就將原有的15位或是18位身份證號(hào)都統(tǒng)一成了15位，作為輔助列為我們后邊的驗(yàn)證作準(zhǔn)備。接下來(lái)，應(yīng)該看看身份證的主角，即身份證編碼的驗(yàn)證碼了。在這里，我們先做個(gè)前湊，先讓大家了解一下身份證編碼是怎么回事，特別是它的校驗(yàn)碼，有沒(méi)有什么規(guī)則，只有先了解了這個(gè)，才能清楚身份證校驗(yàn)碼公式的作用。

一、身份證號(hào)碼執(zhí)行標(biāo)準(zhǔn)：18位身份證標(biāo)準(zhǔn)在國(guó)家質(zhì)量技術(shù)監(jiān)督局于1999年7月1日實(shí)施的GB11643-1999《公民身份號(hào)碼》。

GB11643-1999《公民身份號(hào)碼》為GB11643-1989《社會(huì)保障號(hào)碼》的修訂版，其中指出將原標(biāo)準(zhǔn)名稱“社會(huì)保障號(hào)碼”更名為“公民身份號(hào)碼”，另外GB11643-1999《公民身份號(hào)碼》從實(shí)施之日起代替GB11643-1989。

二、編碼規(guī)則：公民身份號(hào)碼是特征組合碼，由十七位數(shù)字本體碼和一位校驗(yàn)碼組成。排列順序從左至右依次為：六位數(shù)字地址碼，八位數(shù)字出生日期碼，三位數(shù)字順序碼和一位校驗(yàn)碼，可以用字母表示如為ABCDEFYYYYMMDDXXXR。其含義如下：

1.地址碼（ABCDEF）：表示編碼對(duì)象常住戶口所在縣(市、旗、區(qū))的行政區(qū)劃代碼，按GB/T2260的規(guī)定執(zhí)行。

2.出生日期碼（YYYYMMDD）：表示編碼對(duì)象出生的年、月、日，按GB/T7408的規(guī)定執(zhí)行，年、月、日分別用4位、2位（不足兩位加0）、2（同上）位數(shù)字表示，之間不用分隔符。

3.順序碼（XXX）：表示在同一地址碼所標(biāo)識(shí)的區(qū)域范圍內(nèi)，對(duì)同年、同月、同日出生的人編定的順序號(hào)，順序碼的奇數(shù)分配給男性，偶數(shù)分配給女性。

4．校驗(yàn)碼（R），一位數(shù)字，通過(guò)前17位數(shù)字根據(jù)一定計(jì)算得出。

三、關(guān)于地址碼含義的詳細(xì)解釋：

身份證前六位是地區(qū)代碼，我們用ABCDEF表示。代碼的解釋規(guī)則如下：

A:國(guó)內(nèi)區(qū)域

1華北三省二市

2東北三省

3華東六省一市

4華南六省

5西南四省一市

6西北五省

7臺(tái)灣

8港澳

B（或者說(shuō)是AB，就是前2位）:省（直轄市，自治區(qū)，特別行政區(qū)）代碼

按照A劃定的分區(qū)定義省代碼，有直轄市的，直轄市列前，其余按離直轄市的距離排序，沒(méi)有直轄市的，按離北京的遠(yuǎn)近排序。

具體省（直轄市，自治區(qū)，特別行政區(qū)）代碼如下：

11-15京津冀晉蒙

21-23遼吉黑

31-37滬蘇浙皖閩贛魯

41-46豫鄂湘粵桂瓊

50-54渝川貴云藏

61-65陜甘青寧新

81-82港澳

CD:城市代碼

從01開始排，對(duì)于直轄市，CD=01表示市轄區(qū)，CD=02表示轄縣；省的城市代碼從省會(huì)開始排，比如2101=沈陽(yáng)2102=大連……只有地級(jí)城市有獨(dú)立的城市代碼，縣級(jí)市沒(méi)有。

EF:市轄區(qū)、郊區(qū)、郊縣、縣級(jí)市代碼

如果EF=00，指代這個(gè)城市，不特定區(qū)縣；對(duì)于非直轄市，如EF=01，指代市轄區(qū)（任意一個(gè)區(qū)），02開始指代特定的區(qū)。

其中：

E=0代表市轄區(qū)，

E=1代表郊區(qū)，

E=2代表郊縣，

E=8代表縣級(jí)市。

對(duì)于直轄市，從01開始就依次排區(qū)，沒(méi)有市區(qū)和郊區(qū)的代碼區(qū)分。：

有關(guān)更詳細(xì)的資料見(jiàn)附錄。

四、生日期碼（YYYYMMDD）表示編碼對(duì)象出生的年、月、日，其中