考勤工資計(jì)算常用Excel函數(shù)

Excel函數(shù)應(yīng)用之邏輯函數(shù)編者語(yǔ)：Excel是辦公室自動(dòng)化中非常重要的一款軟件，很多巨型國(guó)際企業(yè)都是依靠Excel進(jìn)行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進(jìn)行圖形分析，其更強(qiáng)大的功能體現(xiàn)在對(duì)數(shù)據(jù)的自動(dòng)處理和計(jì)算，然而很多缺少理工科背景或是對(duì)Excel強(qiáng)大數(shù)據(jù)處理功能不了解的人卻難以進(jìn)一步深入。編者以為，對(duì)Excel函數(shù)應(yīng)用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份容的教學(xué)文章卻又很少見，所以特別組織了這一個(gè)《Excel函數(shù)應(yīng)用》系列，希望能夠?qū)xcel進(jìn)階者有所幫助?！禘xcel函數(shù)應(yīng)用》系列，將每周更新，逐步系統(tǒng)的介紹Excel各類函數(shù)及其應(yīng)用，敬請(qǐng)關(guān)注！用來(lái)判斷真假值，或者進(jìn)行復(fù)合檢驗(yàn)的Excel函數(shù)，我們稱為邏輯函數(shù)。在Excel中提供了六種邏輯函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)一、AND、OR、NOT函數(shù)這三個(gè)函數(shù)都用來(lái)返回參數(shù)邏輯值。詳細(xì)介紹見下：（一）AND函數(shù)所有參數(shù)的邏輯值為真時(shí)返回TRUE；只要一個(gè)參數(shù)的邏輯值為假即返回FALSE。簡(jiǎn)言之，就是當(dāng)AND的參數(shù)全部滿足某一條件時(shí)，返回結(jié)果為TRUE，否則為FALSE。.下載可編輯.語(yǔ)法為AND(logical1,logical2,...),其中Logicall,logical2,...表示待檢測(cè)的1到30個(gè)條件值，各條件值可能為TRUE，可能為FALSE。參數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用。舉例說(shuō)明：1、在B2單元格中輸入數(shù)字50，在C2中寫公式=AND(B2>30,B2<60)。由于B2等于50的確大于30、小于60。所以兩個(gè)條件值(logical)均為真，則返回結(jié)果為TRUE。圖1AND函數(shù)示例12、如果B1-B3單元格中的值為TRUE、FALSE、TRUE，顯然三個(gè)參數(shù)并不都為真，所以在B4單元格中的公式二AND(B1:B3)等于FALSE5二,二AMI(EL呼AIB「~CD|TRUEITRUE| |FALSE圖2AND函數(shù)示例2(二)OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為TRUE，即返回TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任.下載可編輯.何一個(gè)為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫為=OR(B1:B3)1則結(jié)果等于TRUE希J菱一…J：/尸*|」.二 二| ：|二。\B|C.TRUSTRUE|FALSEj | ITRUEI圖3OR函數(shù)示例(三)NOT函數(shù) Excel函數(shù)系列?Excel函數(shù)應(yīng)用之?dāng)?shù)學(xué)和三角函數(shù)NOT函數(shù)用于對(duì)參數(shù)值求反。當(dāng)要確保一個(gè)?Excel函數(shù)應(yīng)用之函數(shù)簡(jiǎn)介值不等于某一特定值時(shí)，可以使用NOT函數(shù)。簡(jiǎn)言之，就是當(dāng)參數(shù)值為TRUE時(shí)，NOT函數(shù)返回的結(jié)果恰與之相反，結(jié)果為FALSE.比如NOT(2+2=4)，由于2+2的結(jié)果的確為4，該參數(shù)結(jié)果為TRUE,由于是NOT函數(shù),因此返回函數(shù)結(jié)果與之相反，為FALSE。二、TRUE、FALSE函數(shù)TRUE、FALSE函數(shù)用來(lái)返回參數(shù)的邏輯值，由于可以直接在單元格或公式中鍵入值TRUE或者FALSE。因此這兩個(gè)函數(shù)通?？梢圆皇褂?。三、訐函數(shù)(一)IF函數(shù)說(shuō)明.下載可編輯.IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測(cè)試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應(yīng)用很廣泛，可以使用函數(shù)IF對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。它的語(yǔ)法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計(jì)算結(jié)果為TRUE或FALSE的任意值或表達(dá)式。本參數(shù)可使用任何比較運(yùn)算符。Value_if_true顯示在logical_test為TRUE時(shí)返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test為FALSE時(shí)返回的值。Value_if_false也可以是其他公式。簡(jiǎn)言之，如果第一個(gè)參數(shù)logical_test返回的結(jié)果為真的話，則執(zhí)行第二個(gè)參數(shù)Value_if_true的結(jié)果，否則執(zhí)行第三個(gè)參數(shù)Value_if_false的結(jié)果。IF函數(shù)可以嵌套七層，用value_if_false及value_if_true參數(shù)可以構(gòu)造復(fù)雜的檢測(cè)條件。Excel還提供了可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。(二)IF函數(shù)應(yīng)用1、輸出帶有公式的空白表單.下載可編輯.315 1|_口|三A4DEFG人事狀況分析表——部門;總計(jì)工程部辦公室財(cái)務(wù)部,宜鼎魚4-」----25歲以下02&飛。歲0-3「35歲0；3口45歲0-45歲以上0L.高級(jí)職稱■ 0：中級(jí)職稱Q：初級(jí)職前： 0：見習(xí)期」高級(jí)職稱6中級(jí)職稱0.初級(jí)職稱作見習(xí)期0圖5人事分析表1以圖中所示的人事狀況分析表為例，由于各部門關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫，在總計(jì)欄(以單元格G5為例)公式為：=SUM(C5:F5)我們看到計(jì)算為0的結(jié)果。如果這樣的表格打印出來(lái)就頁(yè)面的美觀來(lái)看顯示是不令人滿意的。是否有辦法去掉總計(jì)欄中的0呢？你可能會(huì)說(shuō)，不寫公式不就行了。當(dāng)然這是一個(gè)辦法，但是，如果我們利用了IF函數(shù)的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實(shí)現(xiàn)呢？只需將總計(jì)欄中的公式(僅以單元格G5為例)改寫成：=IF(SUM(C5:F5),SUM(C5:F5),"").下載可編輯.

通俗的解釋就是：如果SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則顯示字符串。幾點(diǎn)說(shuō)明：(1)SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；(2)""表示字符串的容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。G5-I=|二IF(日則版:阿，35仁5于5)尸A■B-CDEFG人事狀況分析表部門總計(jì)銷售部工程部辦公室財(cái)務(wù)部25歲以下2曠3。歲3廠35歲36~45歲45歲以上1嫁能級(jí)職稱中級(jí)職必■⑸初級(jí)職祚見習(xí)期高領(lǐng).工R-稱中級(jí)職孫i:初級(jí)職祚見習(xí)躺2、不同的條件返回不同的結(jié)果如果對(duì)上述例子有了很好的理解后，我們就很容易將IF函數(shù)應(yīng)用到更廣泛的領(lǐng)域。比如，在成績(jī)表中根據(jù)不同的成績(jī)區(qū)分合格與不合格?，F(xiàn)在我們就以某班級(jí)的英語(yǔ)成績(jī)?yōu)槔唧w說(shuō).下載可編輯.明用法。目haBL2?副y1宋體 ▼■=1二工F（EllMO「合相-12.廣〃二TBZU室強(qiáng)至左圉E，AB.,翳,豆.EFGHI1,3janyMikeHenyAnnieJackyAndy^mile且數(shù)學(xué).955675651-0068785語(yǔ)乂75608575959885英語(yǔ)6554諭■WO§5-9QSO物理100■557040加70- 65化學(xué)86: 78：508889二799政治7859= 8070S890SO10歷史9Q5086T585958511平均分S453SI6S92S67912券合評(píng)定座格!不合格合格合格合彳合格合格13某班級(jí)的成績(jī)?nèi)鐖D6所示，為了做出最終的綜合評(píng)定，我們?cè)O(shè)定按照平均分判斷該學(xué)生成績(jī)是否合格的規(guī)則。如果各科平均分超過(guò)60分則認(rèn)為是合格的，否則記作不合格。根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公式（以單元格B12為例）：=IF（B11>60,“合格","不合格”）語(yǔ)法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個(gè)參數(shù)即在單元格B12中顯示合格字樣，否則執(zhí)行第三個(gè)參數(shù)即在單元格B12中顯示不合格字樣。.下載可編輯.在綜合評(píng)定欄中可以看到由于C列的同學(xué)各科平均分為54分，綜合評(píng)定為不合格。其余均為合格。3、多層嵌套函數(shù)的應(yīng)用在上述的例子中，我們只是將成績(jī)簡(jiǎn)單區(qū)分為合格與不合格，在實(shí)際應(yīng)用中，成績(jī)通常是有多個(gè)等級(jí)的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來(lái)實(shí)現(xiàn)。仍以上例為例，我們?cè)O(shè)定綜合評(píng)定的規(guī)則為當(dāng)各科平均分超過(guò)90時(shí)，評(píng)定為優(yōu)秀。如圖7所示。30|川口一￡1?L赳 ?■日『口等三立口蹄％.■白勺-錄制酒昨caw中財(cái)ujv合1AcD?,1GHi J3MdctHeriyAjisuf「AjidjrSmiteq,S5.I5匕75651麗施’TK5，唾—..7560357595玳:近6&5的9?工的甑和BQ7物理1典砧?040綱70由8低手4。78E.U盟,麻_799政茹.73酩時(shí)IPBE其10第56S675二弼0511平均分劑5,■:8168品7312合節(jié)強(qiáng)根吉拄童鼠尚8_13說(shuō)明：為了解釋起來(lái)比較方便，我們?cè)谶@里僅做兩重嵌套的示例，您可以按照實(shí)際情況進(jìn)行更多重的嵌套，但請(qǐng)注意Excel的IF函數(shù)最多允許七重嵌套。根據(jù)這一規(guī)則，我們?cè)诰C合評(píng)定中寫公式（以單元格F12為例）:.下載可編輯.=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格)"不合格")語(yǔ)法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90(為了讓大家體會(huì)一下AND函數(shù)的應(yīng)用，寫成AND(F11>90)，實(shí)際上可以僅寫F11>90),如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(shù)即在單元格F12中顯示不合格字樣。在綜合評(píng)定欄中可以看到由于F列的同學(xué)各科平均分為92分，綜合評(píng)定為優(yōu)秀。(三)根據(jù)條件計(jì)算值在了解了IF函數(shù)的使用方法后，我們?cè)賮?lái)看看與之類似的Excel提供的可根據(jù)某一條件來(lái)分析數(shù)據(jù)的其他函數(shù)。例如，如果要計(jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。關(guān)于SUMIF函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為詳細(xì)的介紹。這里重點(diǎn)介紹COUNTIF的應(yīng)用。COUNTIF可以用來(lái)計(jì)算給定區(qū)域滿足特定條件的單元格的數(shù)目。比如在成績(jī)表中計(jì)算每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語(yǔ)法形式為COUNTIF(range,criteria)。其中Range為需要計(jì)算其中滿足條件的單元格數(shù).下載可編輯.目的單元格區(qū)域。Criteria確定哪些單元格將被計(jì)算在的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、"32"、">32"、"apples"。1、成績(jī)表這里仍以上述成績(jī)表的例子說(shuō)明一些應(yīng)用方法。我們需要計(jì)算的是：每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。規(guī)則為成績(jī)大于90分記做優(yōu)秀。如圖8所示B13二 ==CQUNTIF-CQ9Q")■ABCDE,,搖.HjanyMikeHerryAnnieTackyAndy-Smile數(shù)學(xué)955675:,65100687S語(yǔ)文75608575能施85一英語(yǔ)6554WW09500SO物理100557040907065訛學(xué)8640785。888979政若78598070889080歷史905536碼3505甌平均分S45481GS928679維,蕓評(píng)定合格不合格合格合格優(yōu)秀合格合格優(yōu)秀門數(shù)112!l00132圖8根據(jù)這一規(guī)則，我們?cè)趦?yōu)秀門數(shù)中寫公式(以單元格B13為例):=COUNTIF(B4:B10,">90")語(yǔ)法解釋為，計(jì)算B4到B10這個(gè)圍，即jarry的各科成績(jī)中有多少個(gè)數(shù)值大于90的單元格。.下載可編輯.在優(yōu)秀門數(shù)欄中可以看到j(luò)arry的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。2、銷售業(yè)績(jī)表銷售業(yè)績(jī)表可能是綜合運(yùn)用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計(jì)算銷售人員的訂單數(shù)，然后匯總每個(gè)銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應(yīng)獲得的獎(jiǎng)金。原始數(shù)據(jù)表如圖9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號(hào)排列）?A - C訂單號(hào)訂單金額銷售人員22QQ104015j000.00JARRY■20010402幻500.GOHELEN42001040325,.'000；00.MICHAEL520010404.200.00JARRY62.0010405500..00/ANNIE72001040S：.2/500,00ANNIEg20010407"i氏bo值66JARRY,92口。104/：95,m.00MICHAEL1020Q104091,000.00HELEN■20010410叫990.00HELEN12200104112,530'.00;JARRY11320010412.200.00ANNIE14乳耳蛇0.0T圖9原始數(shù)據(jù)表按銷售人員匯總表如圖10所示.下載可編輯.BIT 二| ==COI.1NTIF3C$2：-.$C$1^Al7)'ABICD銷售人員訂單數(shù)訂單總顫TEzuANNIETQ/ZDO.0C1J02Q.00-JARRY26,7B0.002.673.00HELEN355,50Q.00：:除羽工00MICHAEL2120,000.DO：18.000,00訂單總數(shù)12212,480-0030.餞3.00趾$8湖$13；A1，獨(dú)$2：部$1即=If.I；C17<50000,10%；15%>ClT圖10銷售人員匯總表如圖10所示的表完全是利用函數(shù)計(jì)算的方法自動(dòng)匯總的數(shù)據(jù)。首