人力資源常用EXCEL函數(shù)公式課件

Excel簡單(jiǎndān)培訓(xùn)2016.09.30第一頁，共22頁。

Excel簡單(jiǎndān)培訓(xùn)2016.09.301內(nèi)容第二頁，共22頁。內(nèi)容第二頁，共22頁。2一、常用(chánɡyònɡ)快捷方式Ctrl+CCtrl+V

Ctrl+XCtrl+ZCtrl+ACtrl+F

Ctrl+SCtrl+WCtrl+PCtrl+G1、大家都知道的一些(yīxiē)常用快捷方式第三頁，共22頁。一、常用(chánɡyònɡ)快捷方式Ctrl+C32、比較(bǐjiào)實用的快捷方式Alt+Enter:強制性換行Ctrl+1：設(shè)置單元格格式Ctrl+*：選定當(dāng)前區(qū)域Ctrl+~：顯示當(dāng)然工作表中所有用到的公式Ctrl+Tab：切換工作簿（當(dāng)前打開(dǎkāi)的所有excel文件）Alt+Tab：切換窗口（當(dāng)前打開(dǎkāi)的WINDOWS窗口）Ctrl+Shift+L:篩選Winkey+d:桌面上的所有窗口瞬間最小化第四頁，共22頁。2、比較(bǐjiào)實用的快捷方式Alt+Enter4二、函數(shù)相關(guān)(xiāngguān)概念1.函數(shù)語法(yǔfǎ):由函數(shù)名+括號+參數(shù)組成例求和函數(shù):SUM(A1,B2,…)參數(shù)與參數(shù)之間用逗號“,”表示第五頁，共22頁。二、函數(shù)相關(guān)(xiāngguān)概念1.函數(shù)語法(yǔfǎ52.運算符:公式運算符:+-*/^比較(bǐjiào)運算符:=><>=<=<>引用運算符:,:第六頁，共22頁。2.運算符:第六頁，共22頁。63.單元格的相對引用與絕對(juéduì)引用:樣式:A1$A1鎖定第A例A$1鎖定第1橫$A$1鎖定第A例與第1橫第七頁，共22頁。3.單元格的相對引用與絕對(juéduì)引用:第七頁，共27三、常用(chánɡyònɡ)函數(shù)1、求和函數(shù)：SumSumif功能：符合指定條件的值求和語法：SUMIF(range，criteria，sum_range)參數(shù)說明：第一個參數(shù):Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù):Criteria是求和條件，由數(shù)字、邏輯(luójí)表達式等組成的判定條件。第三個參數(shù):Sum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。第八頁，共22頁。三、常用(chánɡyònɡ)函數(shù)1、求和函數(shù)：第八頁，共8例：ABC110020100022004020003300603000=SUMIF(A1:A3,“>=200”,B1:B3=?100=SUMIF(A1:A3,“>=200”,C1:C3)=?5000第九頁，共22頁。例：第九頁，共22頁。9Sumproduct功能：給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和語法：SUMPRODUCT（array1，array2，array3，...）參數(shù)說明：Array1，array2，array3，...為2到30個數(shù)組，其相應(yīng)元素需要進行相乘并求和(qiúhé)。特別說明：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值

#VALUE！。函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。第十頁，共22頁。Sumproduct第十頁，共22頁。102、計數(shù)(jìshù)函數(shù)1、Count功能：只統(tǒng)計是數(shù)字的單元格語法：COUNT(value1,value2,...)參數(shù)說明：Value1,value2,...是包含或引用各種類型數(shù)據(jù)(shùjù)的參數(shù)（1～30個）。特別說明：只有數(shù)字類型的數(shù)據(jù)(shùjù)才被計數(shù)。培訓(xùn).xlsx第十一頁，共22頁。2、計數(shù)(jìshù)函數(shù)1、Count第十一頁，共2112、Counta功能：返回參數(shù)列表中非(zhōnɡfēi)空的單元格個數(shù)語法：COUNTA(value1,value2,...)參數(shù)說明：Value1,value2,...為所要計算的值，參數(shù)個數(shù)為1到30個培訓(xùn)(péixùn).xlsx第十二頁，共22頁。2、Counta培訓(xùn)(péixùn).xlsx第十二頁，共2123、Countif功能：對指定區(qū)域中符合指定條件的單元格計數(shù)的一個函數(shù)(hánshù)語法：countif(range，criteria)參數(shù)說明：range：范圍；criteria：條件培訓(xùn).xlsx4、Sumproduct:計算符合(fúhé)2個及以上條件的數(shù)據(jù)個數(shù)第十三頁，共22頁。3、Countif4、Sumproduct:計算符合(fú13Round功能：按指定的位數(shù)進行四舍五入語法：ROUND(number,num_digits）參數(shù)說明：number必需：要四舍五入的數(shù)字；num_digits必需：小數(shù)位數(shù)，按此位數(shù)對number參數(shù)進行四舍五入特別(tèbié)說明：如果

num_digits

大于0（零），則將數(shù)字四舍五入到指定的小數(shù)位。如果

num_digits

等于0，則將數(shù)字四舍五入到最接近的整數(shù)。如果

num_digits

小于0，則在小數(shù)點左側(cè)前幾位進行四舍五入。3、四色五入函數(shù)(hánshù)第十四頁，共22頁。Round3、四色五入函數(shù)(hánshù)第十四頁，共22頁14例：ABC110020100022004020003300603000

=ROUND(A1/A3,2)=0.33=ROUNDUP(A1/A3,2)=0.34=ROUNDDOWN(A1/A3,2)=0.33第十五頁，共22頁。例：第十五頁，共22頁。15Roundup&RounddownRoundup功能：ROUND功能相似，不同之處在于函數(shù)(hánshù)Roundup向上舍入數(shù)字Ronuddown功能(gōngnéng)：向下（絕對值減小的方向）舍入數(shù)字第十六頁，共22頁。Roundup&RounddownRoundupRonudd16公式說明答案=ROUND(2.15,1)將2.15四舍五入到一個小數(shù)位2.2=ROUND(2.149,1)將2.149四舍五入到一個小數(shù)位2.1=ROUND(-1.475,2)將-1.475四舍五入到兩個小數(shù)位-1.48=ROUND(21.5,0)將21.5四舍五入到整數(shù)22=ROUND(21.5,-1)將21.5左側(cè)一位四舍五入20例：公式說明答案=ROUND(2.15,1)=ROUND(2.149,1)=ROUND(-1.475,2)=ROUND(21.5,0)=ROUND(21.5,-1)將2.15四舍五入到一個小數(shù)位2.2將2.149四舍五入到一個小數(shù)位2.1將-1.475四舍五入到兩個小數(shù)位-1.48將21.5四舍五入到整數(shù)22將21.5左側(cè)一位四舍五入20第十七頁，共22頁。公式說明答案=ROUND(2.15,1)將2.15四舍17公式說明答案例：公式說明答案=ROUNDUP(3.2,0)=ROUNDUP(76.9,0)=ROUNDUP(3.14159,3)=ROUNDUP(-3.14159,1)=ROUNDUP(31415.92654,-2)將3.2向上舍入，小數(shù)位為04將76.9向上舍入，小數(shù)位為077將3.14159向上舍入，保留三位小數(shù)3.142將-3.14159向上舍入，保留一位小數(shù)-3.2將31415.92654向上舍入到小數(shù)點左側(cè)兩位31500第十八頁，共22頁。公式說明答案例：公式說明答案=ROUNDUP(3.2,0)=18公式說明答案=ROUNDDOWN(3.2,0)將3.2向下舍入，小數(shù)位為03=ROUNDDOWN(76.9,0)將76.9向下舍入，小數(shù)位為076=ROUNDDOWN(3.14159,3)將3.14159向下舍入，保留三位小數(shù)3.14=ROUNDDOWN(-3.14159,1)將-3.14159向下舍入，保留一位小數(shù)-3.1=ROUNDDOWN(31415.92654,-2)將31415.92654向下舍入到小數(shù)點左側(cè)兩位31400例：公式說明答案=ROUNDDOWN(3.2,0)=ROUNDDOWN(76.9,0)=ROUNDDOWN(3.14159,3)=ROUNDDOWN(-3.14159,1)=ROUNDDOWN(31415.92654,-2)將3.2向下舍入，小數(shù)位為03將76.9向下舍入，小數(shù)位為076將3.14159向下舍入，保留三位小數(shù)3.14將-3.14159向下舍入，保留一位小數(shù)-3.1將31415.92654向下舍入到小數(shù)點左側(cè)兩位31400第十九頁，共22頁。公式說明答案=ROUNDDOWN(3.2,0)將3.2194、查找(cházhǎo)函數(shù)查找表格中的值以列的方式VLOOKUPVlookup(文本,范圍,列,FALSE)文本----條件范圍----條件所在的列列----范圍中對應(yīng)列用數(shù)值表示FALSE----精確(jīngquè)查找培訓(xùn).xlsx第二十頁，共22頁。4、查找(cházhǎo)函數(shù)查找表格中的值以列的方式VL20TheEndThanks第二十一頁，共22頁。TheEndThanks第二十一頁，共22頁。21內(nèi)容(nèiróng)總結(jié)Excel簡單培訓(xùn)。由函數(shù)名+括號+參數(shù)組成。3.單元格的相對引用與絕對引用:。第二個參數(shù):Criteria是求和條件，由數(shù)字、邏輯表達式等組成的判定條件。第三個參數(shù):Sum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。如果

num_digits

大于0（零），則將數(shù)字四舍五入(sìshěwǔrù)到指定的小數(shù)位。=ROUNDDOWN(A1/A3,2)=0.33第二十二頁，共22頁。內(nèi)容(nèiróng)總結(jié)Excel簡單培訓(xùn)。由函數(shù)名+括號22

Excel簡單(jiǎndān)培訓(xùn)2016.09.30第一頁，共22頁。

Excel簡單(jiǎndān)培訓(xùn)2016.09.3023內(nèi)容第二頁，共22頁。內(nèi)容第二頁，共22頁。24一、常用(chánɡyònɡ)快捷方式Ctrl+CCtrl+V

Ctrl+XCtrl+ZCtrl+ACtrl+F

Ctrl+SCtrl+WCtrl+PCtrl+G1、大家都知道的一些(yīxiē)常用快捷方式第三頁，共22頁。一、常用(chánɡyònɡ)快捷方式Ctrl+C252、比較(bǐjiào)實用的快捷方式Alt+Enter:強制性換行Ctrl+1：設(shè)置單元格格式Ctrl+*：選定當(dāng)前區(qū)域Ctrl+~：顯示當(dāng)然工作表中所有用到的公式Ctrl+Tab：切換工作簿（當(dāng)前打開(dǎkāi)的所有excel文件）Alt+Tab：切換窗口（當(dāng)前打開(dǎkāi)的WINDOWS窗口）Ctrl+Shift+L:篩選Winkey+d:桌面上的所有窗口瞬間最小化第四頁，共22頁。2、比較(bǐjiào)實用的快捷方式Alt+Enter26二、函數(shù)相關(guān)(xiāngguān)概念1.函數(shù)語法(yǔfǎ):由函數(shù)名+括號+參數(shù)組成例求和函數(shù):SUM(A1,B2,…)參數(shù)與參數(shù)之間用逗號“,”表示第五頁，共22頁。二、函數(shù)相關(guān)(xiāngguān)概念1.函數(shù)語法(yǔfǎ272.運算符:公式運算符:+-*/^比較(bǐjiào)運算符:=><>=<=<>引用運算符:,:第六頁，共22頁。2.運算符:第六頁，共22頁。283.單元格的相對引用與絕對(juéduì)引用:樣式:A1$A1鎖定第A例A$1鎖定第1橫$A$1鎖定第A例與第1橫第七頁，共22頁。3.單元格的相對引用與絕對(juéduì)引用:第七頁，共229三、常用(chánɡyònɡ)函數(shù)1、求和函數(shù)：SumSumif功能：符合指定條件的值求和語法：SUMIF(range，criteria，sum_range)參數(shù)說明：第一個參數(shù):Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù):Criteria是求和條件，由數(shù)字、邏輯(luójí)表達式等組成的判定條件。第三個參數(shù):Sum_range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當(dāng)省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。第八頁，共22頁。三、常用(chánɡyònɡ)函數(shù)1、求和函數(shù)：第八頁，共30例：ABC110020100022004020003300603000=SUMIF(A1:A3,“>=200”,B1:B3=?100=SUMIF(A1:A3,“>=200”,C1:C3)=?5000第九頁，共22頁。例：第九頁，共22頁。31Sumproduct功能：給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和語法：SUMPRODUCT（array1，array2，array3，...）參數(shù)說明：Array1，array2，array3，...為2到30個數(shù)組，其相應(yīng)元素需要進行相乘并求和(qiúhé)。特別說明：數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值

#VALUE！。函數(shù)SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理。第十頁，共22頁。Sumproduct第十頁，共22頁。322、計數(shù)(jìshù)函數(shù)1、Count功能：只統(tǒng)計是數(shù)字的單元格語法：COUNT(value1,value2,...)參數(shù)說明：Value1,value2,...是包含或引用各種類型數(shù)據(jù)(shùjù)的參數(shù)（1～30個）。特別說明：只有數(shù)字類型的數(shù)據(jù)(shùjù)才被計數(shù)。培訓(xùn).xlsx第十一頁，共22頁。2、計數(shù)(jìshù)函數(shù)1、Count第十一頁，共2332、Counta功能：返回參數(shù)列表中非(zhōnɡfēi)空的單元格個數(shù)語法：COUNTA(value1,value2,...)參數(shù)說明：Value1,value2,...為所要計算的值，參數(shù)個數(shù)為1到30個培訓(xùn)(péixùn).xlsx第十二頁，共22頁。2、Counta培訓(xùn)(péixùn).xlsx第十二頁，共2343、Countif功能：對指定區(qū)域中符合指定條件的單元格計數(shù)的一個函數(shù)(hánshù)語法：countif(range，criteria)參數(shù)說明：range：范圍；criteria：條件培訓(xùn).xlsx4、Sumproduct:計算符合(fúhé)2個及以上條件的數(shù)據(jù)個數(shù)第十三頁，共22頁。3、Countif4、Sumproduct:計算符合(fú35Round功能：按指定的位數(shù)進行四舍五入語法：ROUND(number,num_digits）參數(shù)說明：number必需：要四舍五入的數(shù)字；num_digits必需：小數(shù)位數(shù)，按此位數(shù)對number參數(shù)進行四舍五入特別(tèbié)說明：如果

num_digits

大于0（零），則將數(shù)字四舍五入到指定的小數(shù)位。如果

num_digits

等于0，則將數(shù)字四舍五入到最接近的整數(shù)。如果

num_digits

小于0，則在小數(shù)點左側(cè)前幾位進行四舍五入。3、四色五入函數(shù)(hánshù)第十四頁，共22頁。Round3、四色五入函數(shù)(hánshù)第十四頁，共22頁36例：ABC110020100022004020003300603000

=ROUND(A1/A3,2)=0.33=ROUNDUP(A1/A3,2)=0.34=ROUNDDOWN(A1/A3,2)=0.33第十五頁，共22頁。例：第十五頁，共22頁。37Roundup&RounddownRoundup功能：ROUND功能相似，不同之處在于函數(shù)(hánshù)Roundup向上舍入數(shù)字Ronuddown功能(gōngnéng)：向下（絕對值減小的方向）舍入數(shù)字第十六頁，共22頁。Roundup&RounddownRoundupRonudd38公式說明答案=ROUND(2.15,1)將2.15四舍五入到一個小數(shù)位2.2=ROUND(2.149,1)將2.149四舍五入到一個小數(shù)位2.1=ROUND(-1.475,2)將-1.475四舍五入到兩個小數(shù)位-1.48=ROUND(21.5,0)將21.5四舍五入到整數(shù)22=ROUND(21.5,-1)將21.5左側(cè)一位四舍五入20例：公式說明答案=ROUND(2.15,1)=ROUND(2.149,1)=ROUND(-1.475,2)=ROUND(21.5,0)=ROUND(21.5,-1)將2.15四舍五入到一個小數(shù)位2.2將2.149四舍五入到一個小數(shù)位2.1將-1.475四舍五入到兩個小數(shù)位-1.48將21.5四舍五入到整數(shù)22將21.5左側(cè)一位四舍五入20第十七頁，共22頁。公式說明答案=ROUND(2.15,1)將2.15四舍39公式說明答案例：公式說明答案=ROUNDUP(3.2,0)=ROUNDUP(76.9,0)=ROUNDUP(3.14159,3)=ROUNDUP(-3.14159,1)=ROUNDUP(31415.92654,-2)將3.2向上舍入，小數(shù)位為04將76.9向上舍入，小數(shù)位為077將3.14159向上舍入，保留三位小數(shù)3.142將-3.14159向上舍入，保留一位小數(shù)-3.2將31415.92654向上舍入到小數(shù)點左側(cè)兩位31500第十八頁，共22頁。公式說明答案例：公式說明答案=ROUNDUP(3.2,0)=40公式說明答案=ROUNDDOWN(3.2,0)將3.2向下舍入，小數(shù)位為03=ROUNDDOWN(76.9,0)將76.9向下