EXCEL常用函數(shù)培訓(xùn)教程

1、EXCEL 常用函數(shù)培訓(xùn)常用函數(shù)培訓(xùn)學(xué)習(xí)內(nèi)容學(xué)習(xí)內(nèi)容 1 EXCEL快捷鍵操作快捷鍵操作 2 數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù) 3 邏輯函數(shù)邏輯函數(shù) 4 文本函數(shù)文本函數(shù) 5 查找與引用函數(shù)查找與引用函數(shù) 6 數(shù)據(jù)分類與匯總數(shù)據(jù)分類與匯總 7 數(shù)據(jù)透視表數(shù)據(jù)透視表1 EXCEL快捷鍵操作打開工作簿Ctrl+O保存工作簿Ctrl+S增加一個(gè)新的工作薄Ctrl+N關(guān)閉當(dāng)前工作薄Ctrl+W恢復(fù)選定工作簿窗口的大小Ctrl+F5最大化選定工作簿窗口Ctrl+F10最小化選定工作簿窗口Ctrl+F9切換到上一個(gè)工作簿窗口Ctrl+F6切換到下一個(gè)工作簿窗口Ctrl+Shift+F6實(shí)操1 EXCEL快捷鍵

2、操作 相對引用與絕對引用轉(zhuǎn)換F4重新計(jì)算工作簿的所有工作表F9輸入當(dāng)前日期Ctrl+；輸入當(dāng)前時(shí)間Ctrl+Shift+：向下填充輸入Ctrl+D向右填充輸入Ctrl+R打開“單元格格式”對話框Ctrl+1 1 EXCEL快捷鍵操作撤銷上步操作Ctrl+Z恢復(fù)上步操作Ctrl+Y重復(fù)上步操作F4剪切Ctrl+X復(fù)制Ctrl+C粘貼Ctrl+V查找Ctrl+F替換Ctrl+H定位Ctrl+G插入批注Shift+F2打開“插入函數(shù)”對話框Shift+F3定義名稱Ctrl+ F32 數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù)2.1 ROUND函數(shù)函數(shù)將數(shù)字四舍五入將數(shù)字四舍五入用用 途途：按指定位數(shù)四舍五入某個(gè)數(shù)

3、字。語語 法法：ROUND(number，num_digits)參數(shù)意義參數(shù)意義：Number是需要四舍五入的數(shù)字； Num_digits為指定的位數(shù)，Number按此位數(shù)進(jìn)行處理。注注 意意:（1）在單元格格式中可以設(shè)置小數(shù)位數(shù)，但數(shù)字本身并沒有真正的 四舍五入，只是以2位（如設(shè)置為2）的形式顯示數(shù)據(jù)，如果財(cái)務(wù)中采用這種方式進(jìn)行計(jì)算，經(jīng)常會出現(xiàn)幾分錢的誤差的。（2）如果num_digits大于0，則四舍五入到指定的小數(shù)位； 實(shí)例：如果A1=65.25，則公式“=ROUND(A1，1)”返回65.3 如果num_digits等于0，則四舍五入到最接近的整數(shù)； 實(shí)例：如果A1=65.25，則公式

4、“=ROUND(A1，0)”返回65 如果num_digits小于0，則在小數(shù)點(diǎn)左側(cè)按指定位數(shù)四舍五入。 實(shí)例：如果A1=65.25，則公式“=ROUND(A1，-1)”返回70；實(shí)例剖析實(shí)例剖析 2 數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù)2.2 RANK 函數(shù)函數(shù)統(tǒng)計(jì)排名統(tǒng)計(jì)排名用用 途途：返回一個(gè)數(shù)字在數(shù)字列表中的排位。 數(shù)字的排位是其大小與列表中其他值的比值（如果列表已 排過序， 則數(shù)字的排位就是它當(dāng)前的位置）。 語語 法法：RANK(number,ref,order) 參數(shù)意義參數(shù)意義： Number 為需要找到排位的數(shù)字 ； Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用 ； Order 為一數(shù)字，

5、指明排位的方式。（如果 order 為 0或 省略，則按照降序排列。如果 order 不為零則按升序排列 的列表。注注 意意：相同數(shù)值用RANK函數(shù)計(jì)算得到的序數(shù)（名次）相同，但 會導(dǎo)致后續(xù)數(shù)字的序數(shù)空缺。 實(shí)例剖析 2.3 2.3 最大值函數(shù)最大值函數(shù)MAXMAX、最小值函數(shù)、最小值函數(shù)MINMIN 語 法：MAX（number1，number2，） MIN（number1，number2，） 參數(shù)意義：number1，number2是需要找出最大值 （最小值）的1至30個(gè)數(shù)值、數(shù)組或引用。2.4 AVERAGE2.4 AVERAGE平均值函數(shù)平均值函數(shù) 語 法：AVERAGE（number

6、1，number2，） 參數(shù)意義：number1、number2是需要計(jì)算平均值的130個(gè)參數(shù)。 注意：函數(shù)中的參數(shù)可以是數(shù)字、空白單元格、邏輯值或數(shù)字的文本形式，如果參數(shù)是不能轉(zhuǎn)換為數(shù)字的內(nèi)容將導(dǎo)致錯(cuò)誤。如果參數(shù)為數(shù)組或引用，則只有數(shù)組或引用中的數(shù)字參與計(jì)算，空白單元格、邏輯值或文本則被忽略。2 數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù)功功 能能: : 返回?cái)?shù)據(jù)集中第返回?cái)?shù)據(jù)集中第K K個(gè)最大值。個(gè)最大值。語語 法：法： LARGE(array,kLARGE(array,k) ) 參參 數(shù)：數(shù)： array array 為需要找到第為需要找到第 k k 個(gè)最大值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。個(gè)最大值的數(shù)組或數(shù)

7、字型數(shù)據(jù)區(qū)域。 k k 為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置( (從大到小從大到小) )。 實(shí)實(shí) 例：例： 如果如果A1=24A1=24，A2=5A2=5，A3=7A3=7，A4=15A4=15，A5=0A5=0，A6=35A6=35，A7=2A7=2，A8=5A8=5， 則公式則公式=LARGE(A1:A8,3)=LARGE(A1:A8,3)返回返回1515，即數(shù)組中第，即數(shù)組中第3 3個(gè)小的數(shù)字是個(gè)小的數(shù)字是1515（A4A4）。）。 2數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù)2.5 LARGE函數(shù)函數(shù) 2.6 SMALL函數(shù)函數(shù) 用用 途：途： 返回?cái)?shù)據(jù)集中的第K

8、個(gè)最小值。 語語 法：法：SMALLSMALL（ array,k)參 數(shù)：array為需要找到第k個(gè)最小值的數(shù)組或數(shù)字型 數(shù)據(jù)區(qū)域。 k 為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從小到大)。 實(shí)例：實(shí)例： 如果A1=24，A2=5，A3=7，A4=15，A5=0，A6=9，A7=2，A8=5，則公式“=SMALL(A1:A8,5) ”返回7，即數(shù)組中第5個(gè)小的數(shù)字是7。實(shí)例剖析2數(shù)學(xué)與統(tǒng)計(jì)函數(shù)數(shù)學(xué)與統(tǒng)計(jì)函數(shù)3.1 IF函數(shù)- 進(jìn)行條件檢測功 能：執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。 可以使用函數(shù) IF 對數(shù)值和公式進(jìn)行條件檢測。 語 法： IF(logical_test,valu

9、e_if_true,value_if_false) 參 數(shù)：Logical_test 表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。 Value_if_true logical_test 為 TRUE 時(shí)返回的值。 Value_if_false logical_test 為 FALSE 時(shí)返回的值。 實(shí)例剖析 3 3 邏輯函數(shù)邏輯函數(shù)3 邏輯函數(shù)邏輯函數(shù)功功 能：能：所有參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要一個(gè)參數(shù)的 邏輯值為假， 即返回 FLASE。語語 法：法：AND(logical1,logical2, .) 參參 數(shù)數(shù)：Logical1, logical2, . 表示

10、待檢測的 1 到 30 個(gè)條件值，各條件 值可為 TRUE 或 FALSE。 3.2 AND函數(shù)判斷多個(gè)條件同時(shí)為真 3.3 OR函數(shù)判斷任一參數(shù)為真功功 能能：在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE；所 有參數(shù)的邏輯值為 FALSE，才返回 FALSE 。語語 法：法：OR(logical1,logical2,.)OR(logical1,logical2,.) 參參 數(shù)數(shù)：Logical1,logical2.Logical1,logical2.為需要進(jìn)行檢驗(yàn)的1到 30 個(gè)條件表達(dá)式。 實(shí)例剖析 條件求和函數(shù)條件求和函數(shù)SUMIF 語語 法法：SUMIF（range，

11、criteria，sum_range）。參參 數(shù)：數(shù)：range是用于條件判斷的單元格區(qū)域， criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件， 為確定哪些單元格將被相加求和的條件，其形式可 以為數(shù)字、表達(dá)式或文本 sum_range為需要求和的單元格、區(qū)域或引用。實(shí)例操作4.1 MID、 LEFT 、RIGHT 函數(shù)函數(shù)-獲取字符獲取字符4.1.1 MID 4.1.1 MID 函數(shù)函數(shù) 功功 能：從文本字符串中能：從文本字符串中指定的起始位置指定的起始位置起返回起返回指定長度指定長度的字符的字符 語語 法：法：Mid(text,start_num,num_chars) 參參 數(shù)：數(shù)：te

12、xt 準(zhǔn)備從中提取字符串的文本字符串準(zhǔn)備從中提取字符串的文本字符串 start_num準(zhǔn)備提取的第一個(gè)字符的位置準(zhǔn)備提取的第一個(gè)字符的位置 num_chars指定所要提取的字符串的長度指定所要提取的字符串的長度4.1.2 RIGHT 4.1.2 RIGHT 函數(shù)函數(shù)功功 能：從一個(gè)文本字符串的能：從一個(gè)文本字符串的最后一個(gè)字符最后一個(gè)字符開始返回指定個(gè)數(shù)字符開始返回指定個(gè)數(shù)字符語語 法：法：RIGHT(text, num_chars) 參參 數(shù)：數(shù)：text 要提取字符的字符串要提取字符的字符串 num_chars要提取的字符數(shù)，如果忽略，為要提取的字符數(shù)，如果忽略，為14.1.3 LEFT

13、函數(shù)功功 能：從一個(gè)文本字符串的能：從一個(gè)文本字符串的第一個(gè)字符第一個(gè)字符開始返回指定個(gè)數(shù)字符開始返回指定個(gè)數(shù)字符語語 法：法：LEFT(text, num_chars) 參參 數(shù)：數(shù)：text 要提取字符的字符串要提取字符的字符串 num_chars要提取的字符數(shù)，如果忽略，為要提取的字符數(shù)，如果忽略，為1 實(shí)例剖析實(shí)例剖析4 文本函數(shù)文本函數(shù) 4.2 CONCATENATE函數(shù)函數(shù) 4 文本函數(shù)文本函數(shù)功 能：將幾個(gè)文本字符串合并為一個(gè)文本字符串。語 法：CONCATENATE (text1,text2,.)參 數(shù)：Text1, text2, . 為 1 到 30 個(gè)將要合并成單個(gè)文本項(xiàng)的

14、文本項(xiàng)。 這些文本項(xiàng)可以為文本字符串、數(shù)字或?qū)蝹€(gè)單元格的引用。補(bǔ)充說明：也可以用 &（和號）運(yùn)算符代替函數(shù) CONCATENATE 實(shí)現(xiàn)文本 項(xiàng)的合并。實(shí)例剖析4 文本函數(shù)文本函數(shù)4.3 LOWER/UPPER/PROPER大小寫轉(zhuǎn)換 4.3.1 LOWER 功 能： 將一個(gè)文本字符串中的所有大寫字母轉(zhuǎn)換為小寫字母。 語 法： LOWER(text) 參 數(shù)： Text 是要轉(zhuǎn)換為小寫字母的文本。 4.3.2 UPPER 功 能： 將文本轉(zhuǎn)換為大寫形式 語 法： UPPER(text) 參 數(shù)： Text 是要轉(zhuǎn)換為大寫形式的文本 4.3.3 PROPER 功 能：將文本字符串的首字

15、母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫， 將其余的字母轉(zhuǎn)換成小寫。 語 法： PROPER(text) 參 數(shù)：Text 包括在一組雙引號中的文本字符串、返回文本值的公式或是對包含文 本的單元格的引用。 實(shí)例剖析功功 能能：在表格數(shù)組的首列查找指定的值，并由此返回表格數(shù)組當(dāng)前行中其 他列的值 。VLOOKUP 中的 V 參數(shù)表示垂直方向 。語語 法法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 參參 數(shù)數(shù)：Lookup_value 為需要在數(shù)據(jù)表首列進(jìn)行搜索的值 Table_array 為兩列或多列數(shù)據(jù)。使用對區(qū)域

16、或區(qū)域名稱的引用。 table_array 第一列中的值是由 lookup_value 搜索的值。這些值可 以是文本、數(shù)字或邏輯值。文本不區(qū)分大小寫。 Col_index_num 為 table_array 中待返回的匹配值的列序號。 Col_index_num 為 1 時(shí)，返回 table_array 第一列中的數(shù)值； 為 2，返回 table_array 第二列中的數(shù)值，以此類推。 Range_lookup 為邏輯值，指定希望 VLOOKUP 查找精確的匹配值 還是近似匹配值： 1）如果為 FALSE，VLOOKUP 將只尋找精確匹配值。在此情況 下， table_array 第一列的值不

17、需要排序。如果 table_array 第一列 中有兩個(gè)或多個(gè)值與 lookup_value 匹配，則使用第一個(gè)找到的值。 如果找不到精確匹配值，則返回錯(cuò)誤值 #N/A。 2）如果為 TRUE 或省略，則返回精確匹配值或近似匹配值。也就是 說，如果找不到精確匹配值，則返回小于 lookup_value 的最大數(shù) 值。 table_array 第一列中的值必須以升序排序；否則 VLOOKUP 可 能無法返回正確的值。 實(shí)例剖析5 查找與引用函數(shù)查找與引用函數(shù) VLOOKUP 6 數(shù)據(jù)分類與匯總數(shù)據(jù)分類與匯總6.1 概述概述 分類匯總是對工作表中分類匯總是對工作表中指定的行或列中指定的行或列中的數(shù)

18、據(jù)進(jìn)的數(shù)據(jù)進(jìn)行匯總統(tǒng)計(jì)。通過折疊或展開原工作表中的行、列數(shù)行匯總統(tǒng)計(jì)。通過折疊或展開原工作表中的行、列數(shù)據(jù)及匯總結(jié)果，從匯總和明細(xì)兩種角度顯示數(shù)據(jù)，可據(jù)及匯總結(jié)果，從匯總和明細(xì)兩種角度顯示數(shù)據(jù)，可快捷地創(chuàng)建各種匯總報(bào)告。分類匯總的數(shù)據(jù)折疊層次快捷地創(chuàng)建各種匯總報(bào)告。分類匯總的數(shù)據(jù)折疊層次可達(dá)可達(dá)8層。層。 6.2 功能功能 在數(shù)據(jù)表中顯示一組或多組數(shù)據(jù)的分類匯總數(shù)據(jù)，在數(shù)據(jù)表中顯示一組或多組數(shù)據(jù)的分類匯總數(shù)據(jù)，在分組數(shù)據(jù)上完成不同的計(jì)算，如在分組數(shù)據(jù)上完成不同的計(jì)算，如求和、統(tǒng)計(jì)個(gè)數(shù)、求和、統(tǒng)計(jì)個(gè)數(shù)、求平均數(shù)、求最大（最小值）求平均數(shù)、求最大（最小值）等。等。6 數(shù)據(jù)的分類與匯總 6.3 建立

19、分類匯總建立分類匯總 準(zhǔn)備工作： 分類匯總表的每個(gè)數(shù)據(jù)列都有列標(biāo)題；說明:排序的列標(biāo)題稱為分類匯總關(guān)鍵字，在進(jìn)行分類總時(shí)，只能指定排序后的列標(biāo)題為匯總關(guān)鍵字。 操作方法: 選擇“數(shù)據(jù)”“分類匯總”菜單，打開分類匯總對話框,在對話框中完成操作。6 數(shù)據(jù)的分類與匯總匯總結(jié)果保存替換當(dāng)前分類匯總:最后一次的匯總會取代以前的分類匯總。每組數(shù)據(jù)分頁:各種不同的分類數(shù)據(jù)將被分頁保存。匯總結(jié)果顯示在數(shù)據(jù)下方:原數(shù)據(jù)的下方會顯示匯總計(jì)算的結(jié)果。 說明： 以上3種方式可同時(shí)選中； Excel的默認(rèn)選擇是第1和第3項(xiàng)。6 數(shù)據(jù)的分類與匯總1、指定分類字段、指定分類字段2、指定匯總方式、指定匯總方式3、指定匯總字段

20、、指定匯總字段4、指定匯總結(jié)果、指定匯總結(jié)果的保存方式的保存方式實(shí)例剖析6 數(shù)據(jù)的分類與匯總 分類匯總的刪除 對剛建立的分類匯總，若要恢復(fù)為原始數(shù)據(jù)表，則選擇“編輯”中的“撤消”菜單項(xiàng)取消分類匯總，或單擊撤消按鈕。 若建立分類匯總之后又進(jìn)行了其他操作，可按如下方法刪除分類匯總：（1）單擊分類匯總表中的任一單元格；（2）選擇“數(shù)據(jù)”|“分類匯總”菜單項(xiàng)；（3）單擊“分類匯總”對話框中的“全部刪除”按鈕。 實(shí)例剖析7 數(shù)據(jù)透視表7.1 數(shù)據(jù)透視表的作用能夠改變數(shù)據(jù)表的行、列布局;能夠快速匯總大量數(shù)據(jù);能夠基于原數(shù)據(jù)表創(chuàng)建數(shù)據(jù)分組，并對分組進(jìn)行匯總統(tǒng)計(jì)。7 數(shù)據(jù)透視表7.2 數(shù)據(jù)透視表的建立 選擇“數(shù)據(jù)”“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”; 按向?qū)崾咀鐾?，2步; 在第3步中選擇“布局”按鈕，出現(xiàn)如圖所示的對話框。7 數(shù)據(jù)透視表 解釋: 行：拖放到行中的數(shù)據(jù)字段中的每個(gè)數(shù)據(jù)項(xiàng)將占據(jù)透視表的一行。 列：拖放到列中的數(shù)據(jù)字段中的每個(gè)數(shù)據(jù)項(xiàng)將占據(jù)透視表的一列。行和列確定一個(gè)二維表格. 頁：拖放在頁中