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

1、考勤工資計算常用Excel函數(shù)Excel函數(shù)應用之邏輯函數(shù)編者語：Excel是辦公室自動化中非常重要的一款軟件，很多巨型國際企業(yè)都是依靠Excel進行數(shù)據(jù)管理。它不僅僅能夠方便的處理表格和進行圖形分析，其更強大的功能體現(xiàn)在對數(shù)據(jù)的自動處理和計算，然而很多缺少理工科背景或是對Excel強大數(shù)據(jù)處理功能不了解的人卻難以進一步深入。編者以為，對Excel函數(shù)應用的不了解正是阻擋普通用戶完全掌握Excel的攔路虎，然而目前這一部份內(nèi)容的教學文章卻又很少見，所以特別組織了這一個Excel函數(shù)應用系列，希望能夠?qū)xcel進階者有所幫助。Excel函數(shù)應用系列，將每周更新，逐步系統(tǒng)的介紹Excel各類函數(shù)

2、及其應用，敬請關(guān)注！用來判斷真假值，或者進行復合檢驗的Excel函數(shù)，我們稱為邏輯函數(shù)。在Excel中提供了六種邏輯函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)。、AND、OR、NOT函數(shù)這三個函數(shù)都用來返回參數(shù)邏輯值。詳細介紹見下：(一)AND函數(shù)所有參數(shù)的邏輯值為真時返回TRUE;只要一個參數(shù)的邏輯值為假即返回FALSE。簡言之，就是當AND的參數(shù)全部滿足某一條件時，返回結(jié)果為TRUE,否則為FALSE。語法為AND(logical1,logical2,.)其中Logicall,logical2,表示待檢測的1至IJ30個條件值，各條件值可能為TRUE,可能為FALSEo參

3、數(shù)必須是邏輯值，或者包含邏輯值的數(shù)組或引用。舉例說明：1、在B2單元格中輸入數(shù)字50,在C2中寫公=AND(B230,B260)o由于B2等于50的確大于30、小于60。所以兩個條件值(logical)均為真，則返回結(jié)果為TRUE。2、如果B1-B3單元格中的值為TRUE、FALSE、TRUE,顯然三個參數(shù)并不都為真，所以在B4單元格中的公式=AND(B1:B3)等于(二)OR函數(shù)OR函數(shù)指在其參數(shù)組中，任何一個參數(shù)邏輯值為TRUE,即返回TRUE。它與AND函數(shù)的區(qū)別在于，AND函數(shù)要求所有函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個為真即可為真。比如，上面的示例2,如果在B4單

4、元格中的公式寫為=OR(B1:B3)則結(jié)果等于TRUEAATJ.，二二J:二OR（B三B3）kBICDTRUE|TRUEFALSEIlTRUEIExcel函數(shù)系列 Excel函數(shù)應用之數(shù) 學和三角函數(shù) Excel函數(shù)應用之函 數(shù)簡介圖3OR函數(shù)示例（三）NOT函數(shù)NOT函數(shù)用于對參數(shù)值求反。當要確保一個值不等于某一特定值時，可以使用NOT函數(shù)。簡言之，就是當參數(shù)值為TRUE時，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、

5、FALSE函數(shù)用來返回參數(shù)的邏輯值，由于可以直接在單元格或公式中鍵入值TRUE或者FALSE。因此這兩個函數(shù)通?？梢圆皇褂谩Ｈ?、IF函數(shù)（一）IF函數(shù)說明IF函數(shù)用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。它的應用很廣泛，可以使用函數(shù)IF對數(shù)值和公式進行條件檢測。它的語法為IF（logical_test,value_if_true,value_if_false）其中Logical_test表示計算結(jié)果為TRUE或FALSE的任意值或表達式。本參數(shù)可使用任何比較運算符。Value_if_true顯示在logical_test為TRUE時返回的值，Val

6、ueiftrue也可以是其他公式。Value_if_falselogical_test為FALSE時返回的值。Value_if_false也可以是其他公式。簡言之，如果第一個參數(shù)logical_test返回的結(jié)果為真的話）則執(zhí)行第二個參數(shù)Value_if_true的結(jié)果）否則執(zhí)行第三個參數(shù)Valueiffalse的結(jié)果。IF函數(shù)可以嵌套七層）用valueiffalse及value_if_true參數(shù)可以構(gòu)造復雜的檢測條件。Excel還提供了可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如，如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文

7、本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。（二）IF函數(shù)應用1、輸出帶有公式的空白表單G15二|=三 SUM(C15:F15)ABCDEFG人事狀況分析表1部門總計銷售都工程部辦公室財務部25歲以下026-3。歲035歲045歲045歲以上0I高級職稱0中級職稱0初級職稱0見習期0Jg高級職稱0中級職稱o1初級職稱0P見習期0圖5人事分析表1以圖中所示的人事狀況分析表為例，由于各部門關(guān)于人員的組成情況的數(shù)據(jù)尚未填寫，在總計欄(以單元格G5為例)公式為:=SUM(C5:F5)我們看到計算為0的結(jié)果。如果這樣的表格打印出來就頁面的美觀來看顯示是不令人滿意的。是否有辦法去掉總計欄中的0呢？你可能

8、會說，不寫公式不就行了。當然這是一個辦法，但是，如果我們利用了IF函數(shù)的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實現(xiàn)呢？只需將總計欄中的公式(僅以單元格G5為例)改寫成：=IF(SUM(C5:F5),SUM(C5:F5),)通俗的解釋就是：如果SUM(C5:F5)不等于零，則在單元格中顯示SUM(C5:F5)的結(jié)果，否則顯示字符串。幾點說明：(1)SUM(C5:F5)不等于零的正規(guī)寫法是SUM(C5:F5)0,在EXCEL中可以省略0;(2)”表示字符串的內(nèi)容為空，因此執(zhí)行的結(jié)果是在單元格中不顯示任何字符。G5，=hIF(SUM(C5:F5).SUM(C5:F5),)ABCDEFG人

9、事狀況分析表|部門心H工程部辦公室財務部-IJ-25歲以下2口3。學335歲3645歲45歲以上-J高級職稱中級職稱初級職稱見習期高級職稱中級職祚初級職科見習期2、不同的條件返回不同的結(jié)果如果對上述例子有了很好的理解后，我們就很容易將IF函數(shù)應用到更廣泛的領(lǐng)域。比如，在成績表中根據(jù)不同的成績區(qū)分合格與不合格?，F(xiàn)在我們就以某班級的英語成績?yōu)槔唧w說明用法。ABCDIEFGHI23janyMikeHenyAnnieJackyAndySmileq數(shù)學95567565100687Sr-語文756085759598856英語6554901009590807物理1005570409070658：化學864

10、0785088S9799政治7859807028908010歷史90503675IS5958511平均分8453816892S67912綜合評定合格1不合格合格合格合格合格合格彥0mle國川東體.翼產(chǎn).“IH/更樂富鎏囹|B12d=IF（Bll60含褶丁示吾褶513圖6某班級的成績?nèi)鐖D6所示，為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規(guī)則。如果各科平均分超過60分則認為是合格的，否則記作不合格。根據(jù)這一規(guī)則，我們在綜合評定中寫公式（以單元格B12為例）:=IF（B1160,合格,不合格）語法解釋為，如果單元格B11的值大于60,則執(zhí)行第二個參數(shù)即在單元格B12中顯示合格

11、字樣，否則執(zhí)行第三個參數(shù)即在單元格B12中顯示不合格字樣。在綜合評定欄中可以看到由于C列的同學各科平均分為54分，綜合評定為不合格。其余均為合格。3、多層嵌套函數(shù)的應用在上述的例子中，我們只是將成績簡單區(qū)分為合格與不合格，在實際應用中，成績通常是有多個等級的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來實現(xiàn)。仍以上例為例，我們設定綜合評定的規(guī)則為當各科平均分超過90時，評定為優(yōu)秀。如圖7所示。F12二J-：FF：i.fT噸??；丁ABcDE,1GHIJjvryMiceKerryAnfueMk/一一Aa加4數(shù)學95三67565IM叫7g5何文75$Q85759585

12、$T英語65Cd-,4190100958fl為理1QQ55T(J40901705589化學864078_小,；聊tS芯5734g30TOs%3010歷史90r8675S5j5585；11ikl13平螞分94rJaiQ9g79Ennameti縹蓊相定而&J1.4= it呂,U h器聿圖7說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行更多重的嵌套，但請注意Excel的IF函數(shù)最多允許七重嵌套。根據(jù)這一規(guī)則，我們在綜合評定中寫公式(以單元格F12為例)：=IF(F1160,IF(AND(F1190),優(yōu)秀,合格),“不合格)語法解釋為，如果單元格F11的值大于60,

13、則執(zhí)行第二個參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格F11的值是否大于90(為了讓大家體會一下AND函數(shù)的應用，寫成AND(F1190),實際上可以僅寫F1190),如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個參數(shù)即在單元格F12中顯示不合格字樣。在綜合評定欄中可以看到由于F列的同學各科平均分為92分，綜合評定為優(yōu)秀。(三)根據(jù)條件計算值在了解了IF函數(shù)的使用方法后，我們再來看看與之類似的Excel提供的可根據(jù)某一條件來分析數(shù)據(jù)的其他函數(shù)。例如，如果要計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根據(jù)

14、單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。關(guān)于SUMIF函數(shù)在數(shù)學與三角函數(shù)中以做了較為詳細的介紹。這里重點介紹COUNTIF的應用。COUNTIF可以用來計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如在成績表中計算每位學生取得優(yōu)秀成績的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、32、32”、appleso1、成績表這里仍以上述

15、成績表的例子說明一些應用方法。我們需要計算的是：每位學生取得優(yōu)秀成績的課程數(shù)。規(guī)則為成績大于90分記做優(yōu)秀。如圖8所示B13*=C0UNTIF(B4:B1090”)A；B,瓦DE森HjarryMikeKerryAntaeJackyAndyStrule數(shù)學955675651006878語文75608575959885英語655490100959080物理100557040907065化學86407850888979政治一78598070889080歷史90558675S59585平均分845481689286?9.綜合評定一合格.不合格合格合格優(yōu)秀合格合格優(yōu)秀門數(shù)112!o01320圖8根據(jù)這一

16、規(guī)則，我們在優(yōu)秀門數(shù)中寫公式(以單元格B13為例):=COUNTIF(B4:B10,90)語法解釋為，計算B4到B10這個范圍，即jarry的各科成績中有多少個數(shù)值大于90的單元格。在優(yōu)秀門數(shù)欄中可以看到jarry的優(yōu)秀門數(shù)為兩門。其他人也可以依次看到。2、銷售業(yè)績表銷售業(yè)績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計算銷售人員的訂單數(shù)，然后匯總每個銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應獲得的獎金。原始數(shù)據(jù)表如圖9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號排列）AB1C.1訂單號訂單金額銷售人員_2,200104015,000.00JARRY3

17、200104:024,500.00HELEN42001040325,000.00MICHAEL5.200104044,200.00JARRY6200104:054,500.00ANNIE720010式62,500.00ANNIE82001040715,000.00JARRY92001040895,000.00MICHAEL10200104091,000.00HELEN112001041050,000,00HELEN12200104112,580.00JARR200.00ANNIE14銷售總額212;480r00圖9原始數(shù)據(jù)表按銷售人員匯總表如圖10所示B17二|=CO

18、UNTIF(JCS2：$C$1Al7)AB口CDJ銷售人員訂單數(shù)1訂單總額1銷售獎金ANNIE13)一10,200,001,020.00-JARRY426,780.002,678.00HELEN355,500.008,325.00MICHAEL2120,000.0018,000.00訂單總數(shù)12212,480.0030,023.00=SUMIF($C$2：SC$13fMT,$B$2:$E$二IF(tn50000,10%,15*C17圖10銷售人員匯總表如圖10所示的表完全是利用函數(shù)計算的方法自動匯總的數(shù)據(jù)。首先建立一個按照銷售人員匯總的表單樣式，如圖所示。然后分別計算訂單數(shù)、訂單總額、銷售獎金。(1)訂單數(shù)用COUNTIF計算銷售人員的訂單數(shù)。以銷售人員ANNIE的訂單數(shù)公式為例。公式：=COUNTIF($C$2:$C$13,A17)語法解釋為計算單元格A17(即銷售人員ANNIE)在“銷售人員“清單$C$2:$C$13的范圍內(nèi)(即圖9所示的原始數(shù)據(jù)表)出現(xiàn)的次數(shù)。這個出現(xiàn)的次數(shù)即可認為是該銷售人員ANNIE的訂單數(shù)。(2)訂單總額-用SUMIF匯總每個銷售人員的銷售額。以銷售人員ANNIE的訂單總額公式為例。公式:=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在“銷售人員清單$C$2:$