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

千里之行，始于足下讓知識帶有溫度。第第2頁/共2頁精品文檔推薦考勤工資計算常用Excel函數(shù)Excel函數(shù)應用之規(guī)律函數(shù)

編者語：Excel是辦公室自動化中十分重要的一款軟件，無數(shù)巨型國際企業(yè)都是依賴Excel舉行數(shù)據(jù)管理。它不僅僅能夠便利的處理表格和舉行圖形分析，其更強大的功能體現(xiàn)在對數(shù)據(jù)的自動處理和計算，然而無數(shù)缺少理工科背景或是對Excel強大數(shù)據(jù)處理功能不了解的人卻難以進一步深化。編者以為，對Excel函數(shù)應用的不了解正是阻止一般用戶徹低把握Excel的攔路虎，然而目前這一部份容的教學文章卻又很少見，所以特殊組織了這一個《Excel函數(shù)應用》系列，希翼能夠對Excel進階者有所協(xié)助?！禘xcel函數(shù)應用》系列，將每周更新，逐步系統(tǒng)的介紹Excel各類函數(shù)及其應用，敬請關注！

用來推斷真假值，或者舉行復合檢驗的Excel函數(shù)，我們稱為規(guī)律函數(shù)。在Excel中提供了六種規(guī)律函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)。

一、AND、OR、NOT函數(shù)

這三個函數(shù)都用來返回參數(shù)規(guī)律值。具體介紹見下：

（一）AND函數(shù)

全部參數(shù)的規(guī)律值為真時返回TRUE；只要一個參數(shù)的規(guī)律值為假即返回FALSE。簡言之，就是當AND的參數(shù)所有滿足某一條件時，返回結果為TRUE，否則為FALSE。

語法為AND(logical1,logical2,...)，其中Logical1,logical2,...表示待檢測的1到30個條件值，各條件值可能為TRUE，可能為FALSE。參數(shù)必需是規(guī)律值，或者包含規(guī)律值的數(shù)組或引用。舉例說明：

1、在B2單元格中輸入數(shù)字50，在C2中寫公式=AND(B2>30,B20，在EXCEL中可以省略0；

（2）""表示字符串的容為空，因此執(zhí)行的結果是在單元格中不顯示任何字符。

圖4

2、不同的條件返回不同的結果

假如對上述例子有了很好的理解后，我們就很簡單將IF函數(shù)應用到更廣泛的領域。比如，在成果表中按照不同的成果區(qū)別合格與不合格?，F(xiàn)在我們就以某班級的英語成果為例詳細說

明用法。

圖6

某班級的成果如圖6所示，為了做出終于的綜合評定，我們設定根據(jù)平均分推斷該同學成果是否合格的規(guī)章。假如各科平均分超過60分則認為是合格的，否則記作不合格。

按照這一規(guī)章，我們在綜合評定中寫公式（以單元格B12為例）：

=IF(B11>60,"合格","不合格")

語法解釋為，假如單元格B11的值大于60，則執(zhí)行其次個參數(shù)即在單元格B12中顯示合格字樣，否則執(zhí)行第三個參數(shù)即在單元格B12中顯示不合格字樣。

在綜合評定欄中可以看到因為C列的學生各科平均分為54分，綜合評定為不合格。其余均為合格。

3、多層嵌套函數(shù)的應用

在上述的例子中，我們只是將成果容易區(qū)別為合格與不合格，在實際應用中，成果通常是有多個等級的，比如優(yōu)、良、中、及格、不及格等。有方法一次性區(qū)別嗎？可以使用多層嵌套的方法來實現(xiàn)。仍以上例為例，我們設定綜合評定的規(guī)章為當各科平均分超過90時，評定為優(yōu)秀。如圖7所示。

圖7

說明：為了解釋起來比較便利，我們在這里僅做兩重嵌套的示例，您可以根據(jù)實際狀況舉行更多重的嵌套，但請注重Excel的IF函數(shù)最多允許七重嵌套。

按照這一規(guī)章，我們在綜合評定中寫公式（以單元格F12為例）：

=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格"),"不合格")

語法解釋為，假如單元格F11的值大于60，則執(zhí)行其次個參數(shù)，在這里為嵌套函數(shù)，繼續(xù)推斷單元格F11的值是否大于90（為了讓大家體味一下AND函數(shù)的應用，寫成AND(F11>90)，實際上可以僅寫F11>90），假如滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，假如F11的值以上條件都不滿足，則執(zhí)行第三個參數(shù)即在單元格F12中顯示不合格字樣。

在綜合評定欄中可以看到因為F列的學生各科平均分為92分，綜合評定為優(yōu)秀。

（三）按照條件計算值

在了解了IF函數(shù)的使用辦法后，我們再來看看與之類似的Excel提供的可按照某一條件來分析數(shù)據(jù)的其他函數(shù)。例如，假如要計算單元格區(qū)域中某個文本串或數(shù)字浮現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。假如要按照單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。關于SUMIF函數(shù)在數(shù)學與三角函數(shù)中以做了較為具體的介紹。這里重點介紹COUNTIF的應用。

COUNTIF可以用來計算給定區(qū)域滿足特定條件的單元格的數(shù)目。比如在成果表中計算每位同學取得優(yōu)秀成果的課程數(shù)。在工資表中求出全部基本工資在2000元以上的員工數(shù)。

語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數(shù)

目的單元格區(qū)域。Criteria確定哪些單元格將被計算在的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為32、"32"、">32"、"apples"。

1、成果表

這里仍以上述成果表的例子說明一些應用辦法。我們需要計算的是：每位同學取得優(yōu)秀成果的課程數(shù)。規(guī)章為成果大于90分記做優(yōu)秀。如圖8所示

圖8

按照這一規(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ù)，然后匯總每個銷售人員的銷售額，并且按照總發(fā)貨量打算每次銷售應獲得的獎金。

原始數(shù)據(jù)表如圖9所示（原始數(shù)據(jù)是以流水單形式列出的，即按訂單號羅列）

圖9原始數(shù)據(jù)表

按銷售人員匯總表如圖10所示

圖10銷售人員匯總表

如圖10所示的表徹低是利用函數(shù)計算的辦法自動匯總的數(shù)據(jù)。首先建立一個根據(jù)銷售人員匯總的表單樣式，如圖所示。然后分離計算訂單數(shù)、訂單總額、銷售獎金。

（1）訂單數(shù)--用COUNTIF計算銷售人員的訂單數(shù)。

以銷售人員ANNIE的訂單數(shù)公式為例。公式：

=COUNTIF($C$2:$C$13,A17)

語法解釋為計算單元格A17(即銷售人員ANNIE)在"銷售人員"清單$C$2:$C$13的圍（即圖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:$C$13中檢查單元格A17中的文本（即銷售人員ANNIE），然后計算"訂單金額"列（$B$2:$B$13）中相應量的和。

這個相應量的和就是銷售人員ANNIE的訂單總額。

（3）銷售獎金--用IF按照訂單總額打算每次銷售應獲得的獎金。

假定公司的銷售獎金規(guī)章為當訂單總額超過5萬元時，嘉獎幅度為百分之十五，否則為百分之十。按照這一規(guī)章仍以銷售人員ANNIE為例說明。公式為：