1、Document number,1,EXCEL常用函數(shù)及數(shù)據(jù)整理技巧,Document number,2,內(nèi)容概要,1、EXCEL函數(shù)應(yīng)用 2、EXCEL數(shù)據(jù)整理方法與技巧 3、快捷鍵應(yīng)用,Document number,3,第一部分 EXCEL常用函數(shù),Document number,4,一、EXCEL函數(shù)類型,文本函數(shù) 日期和時(shí)間函數(shù) 數(shù)學(xué)和三角函數(shù) 統(tǒng)計(jì)函數(shù) 查詢和引用函數(shù) 邏輯函數(shù) 信息函數(shù) 財(cái)務(wù)函數(shù) 工程函數(shù) 數(shù)據(jù)庫函數(shù) 自定議函數(shù),Document number,5,二、文本函數(shù),通過文本函數(shù)，可以在公式中處理字符串。例如可以轉(zhuǎn)換文本格式、截取特定文本字符、查找替代特定文本等。 1

2、、轉(zhuǎn)換類 大小寫轉(zhuǎn)換類 upper(); lower(); proper() 數(shù)字、文本等格式間轉(zhuǎn)換 dollar(); rmb(); text()；value() 2、截取/合并類 截取類 left()/leftb() ； right()/rightb() ； mid()/midb() ; len()/lenb() 合并類 concatenate() 3、文本整理類 trim() /clean() 4、字符查找、比較、替代類 查找類 find(); findb(); search(); searchb() 比較類 exact() 替代類 replace(); substitute(),Doc

3、ument number,6,三、日期與時(shí)間函數(shù),數(shù)據(jù)表處理過程中，日期與時(shí)間函數(shù)是相當(dāng)重要的處理依據(jù)。 1、取當(dāng)前系統(tǒng)時(shí)間、日期信息函數(shù) now() ; today() 2、取得日期、時(shí)間的部分字段值函數(shù) year() ; month() ; day() ; hour() ; minute(); second() 3、日期計(jì)算函數(shù) datedif() ; days360(),Document number,7,四、數(shù)學(xué)和三角函數(shù),1、與求和有關(guān)的函數(shù) SUM(number1,number2, .) 行列求和、區(qū)域求和 （參數(shù)最多30個(gè)） SUMIF(range,criteria,sum_ra

4、nge) 根據(jù)指定條件對若干單元格求和 SUBTOTAL(function_num, ref1, ref2, .) 返回?cái)?shù)據(jù)清單分類匯總,Document number,8,四、數(shù)學(xué)和三角函數(shù),2、數(shù)字取舍類函數(shù) ROUND(number,num_digits) ：返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字 INT(number) ：返回小數(shù)舍入后的整數(shù)，即向下取整 用于四舍五入時(shí)需要技巧即：加0.5后取整 ABS(number)：返回?cái)?shù)字的絕對值。 TRUNC(number,num_digits): 將數(shù)字的小數(shù)部分截去，返回整數(shù)。,Document number,9,五、統(tǒng)計(jì)函數(shù),作為EXCEL函

5、數(shù)中數(shù)量最為龐大的一類，統(tǒng)計(jì)類函數(shù)提供了豐富的概率與統(tǒng)計(jì)函數(shù)功能。： 1、用于求平均值的函數(shù) AVERAGE(number1,number2,.)：返回參數(shù)的平均值（算術(shù)平均值） AVERAGEA(value1,value2,.)：計(jì)算參數(shù)列表中數(shù)值的平均值（算數(shù)平均值） TRIMMEAN(array,percent)：返回?cái)?shù)據(jù)集的內(nèi)部平均值。 2、求單元格個(gè)數(shù)函數(shù) COUNT(value1,value2,.)：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。 COUNTA(value1,value2,.)：返回參數(shù)列表中非空值的單元格個(gè)數(shù)。,Document number,10,五、統(tǒng)計(jì)

6、函數(shù),3、求區(qū)域中數(shù)據(jù)分布頻率函數(shù) FREQUENCY(data_array,bins_array)： 以一列垂直數(shù)組返回某個(gè)區(qū)域中數(shù)據(jù)的頻率分布。 4、線性回歸分析函數(shù) CORREL(array1,array2)：返回單元格區(qū)域 array1 和 array2 之間的相關(guān)系數(shù)。 FORECAST(x,known_ys,known_xs)：根據(jù)已有的數(shù)值計(jì)算或預(yù)測未來值。 5、其他特定需求統(tǒng)計(jì)函數(shù) 最大最小值 MAX(number1,number2,.)/ MIN(number1,number2,.) 第K大與第K小 LARGE(array,k) / SMALL(array,k) 中位數(shù) ME

7、DIAN(number1,number2,.) 排名 RANK(number,ref,order),Document number,11,六、查詢與引用函數(shù),1、絕對引用與相對引用 絕對引用在復(fù)制公式時(shí)不希望EXCEL調(diào)整引用，須使用絕對引用 相對引用在創(chuàng)建公式時(shí)，單元格或單元格區(qū)域的引用通常是包含公式的單元格的相對位置。 2、引用的表示方法 :A1與R1C1兩種類型引用 3、主要函數(shù) LOOKUP(lookup_value,lookup_vector,result_vector) 在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值 HLOOKUP(lookup_

8、value,table_array,row_index_num,range_lookup) 在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值,Document number,12,七、邏輯函數(shù),用于判斷真假值，或者進(jìn)行復(fù)合檢驗(yàn)的EXCEL函數(shù),稱之為邏輯函數(shù)。 1、表達(dá)“且”、“或”、“不”關(guān)系的邏輯函數(shù) AND(logical1,logical2, .) 所有

9、參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要一個(gè)參數(shù)的邏輯值為假，即返回 FALSE。 OR(logical1,logical2,.)：在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為 TRUE，即返回 TRUE；任何一個(gè)參數(shù)的邏輯值為 FALSE，即返回 FALSE。 NOT(logical)：對參數(shù)值求反。當(dāng)要確保不等于某一特定值時(shí)，可以使用此函數(shù)。 2、if()條件函數(shù) IF(logical_test,value_if_true,value_if_false) 執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。最多嵌套7層條件。,Document number,13,八、信息函數(shù),專門用來返回某些指定單

10、元格或區(qū)域等的信息，比如單元格的內(nèi)容、格式、個(gè)數(shù)等，這類函數(shù)統(tǒng)稱為信息類函數(shù)。 主要介紹IS類函數(shù)，常與IF函數(shù)配合使用。 ISBLANK(value) 值為空白單元格時(shí),返回true。 ISERR(value) 值為任意錯(cuò)誤值（除去 #N/A）時(shí),返回true。 ISERROR(value) 值為任意錯(cuò)誤值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）時(shí),返回true。 ISLOGICAL(value) 值為邏輯值時(shí),返回true。 ISNA(value) 值為錯(cuò)誤值 #N/A（值不存在）時(shí),返回true。 ISNONTEXT(value

11、) 值為不是文本的任意項(xiàng)時(shí),返回true。 ISNUMBER(value) 值為數(shù)字時(shí),返回true。 ISREF(value) 值為引用時(shí),返回true。 ISTEXT(value) 值為文本時(shí),返回true。,Document number,14,九、財(cái)務(wù)函數(shù),在此部分結(jié)合常用財(cái)務(wù)管理理論，EXCEL提供了16個(gè)具體財(cái)務(wù)函數(shù)，主要分為投資理財(cái)類函數(shù)與折舊類函數(shù)。 折舊類函數(shù) SLN(cost,salvage,life) 返回某項(xiàng)資產(chǎn)在一個(gè)期間中的線性折舊值。 SYD(cost,salvage,life,per) 返回某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的指定期間的折舊值。 DDB(cost,sal

12、vage,life,period,factor) 使用雙倍余額遞減法或其他指定方法，計(jì)算一筆資產(chǎn)在給定期間內(nèi)的折舊值。,Document number,15,第二部分 EXCEL數(shù)據(jù)整理技巧,Document number,16,一、EXCEL常用數(shù)據(jù)整理方法與技巧,1、排序 2、篩選 3、分列 4、分類匯總 5、合并計(jì)算 6、數(shù)據(jù)透視表,Document number,17,二、數(shù)據(jù)清單的排序,1、排序方式 簡單排序與多重排序 2、排序方法 升序、降序（默認(rèn)以拼音排序）,Document number,18,三、數(shù)據(jù)清單的篩選,1、自動(dòng)篩選 適用于簡單條件，通常是在一個(gè)數(shù)據(jù)清單的一列中查找相

13、同的值。也可以用自定義篩選方式快速查找出符合多重條件的記錄。,Document number,19,三、數(shù)據(jù)清單的篩選,2、高級篩選 如果數(shù)據(jù)清單字段較多，篩選條件也較多，則可以使用高級篩選,注意： A、要首先建立條件區(qū)域 B、條件區(qū)域第一行是作為篩選條件的字段名，必須與數(shù)據(jù)清單中字段名完全相同 C、條件區(qū)域其他行用于輸入篩選條件 D、條件區(qū)域與數(shù)據(jù)清單不能連接，須有一個(gè)空行或空列分離,Document number,20,三、數(shù)據(jù)清單的篩選,2、高級篩選,Document number,21,四、分列,如果某單元格或列包含數(shù)個(gè)可用信息，希望把其拆分在數(shù)個(gè)單元格內(nèi)列示時(shí)，則用到“分列”工具。

14、如下圖中：開票期間項(xiàng),Document number,22,四、分列,Document number,23,四、分列步驟1,分列的兩種類型： 1、按分隔符號分列 2、按固定列寬分列 操作時(shí)應(yīng)具體按被分列項(xiàng)特征，選取不同分列方法。在本例中，兩種方法均可：可以以空格為分列符號分列；也可以用固定列寬人為添加分列線分列,Document number,24,四、分列步驟2,上步中，假設(shè)選分隔符號，則彈出步驟2，選取具體分隔符號（打勾）后，會(huì)在預(yù)覽中顯出分列線，如無誤，點(diǎn)擊“下一步”,Document number,25,四、分列步驟3,選取擬分列后各字段數(shù)據(jù)格式。 可選類型有：常規(guī)、文本、日期，也可以

15、選擇其中一列或幾列不導(dǎo)入,Document number,26,四、分列完成后,原先第 I 列被拆分為I 、J、K三列 為后續(xù)數(shù)據(jù)整理作好準(zhǔn)備。,Document number,27,五、分類匯總,分類匯總功能可以自動(dòng)對所選數(shù)據(jù)進(jìn)行匯總，并插入?yún)R總行。匯總方式靈活多樣：求和、平均數(shù)、最大數(shù)、標(biāo)準(zhǔn)方差等。,具體步驟為： 1、選定工作表，單擊“數(shù)據(jù)” | “分類匯總”命令，將彈出“分類匯總”對話框（如右圖）：選擇分類字段（本例國家）及匯總方式（本例求和），及選定匯總項(xiàng)（本例開票數(shù)量、主營業(yè)務(wù)收入）。 2、單擊“確定”按鈕，結(jié)果為：,Document number,28,五、分類匯總,Documen

16、t number,29,五、分類匯總,對數(shù)據(jù)分類匯總后，還可以恢復(fù)工作表的原始數(shù)據(jù)，方法為：再次選定工作表，單擊“數(shù)據(jù)” | “分類匯總”命令，在彈出“分類匯總”對話框中，單擊“全部刪除”按鈕，即可將工作表恢復(fù)到原始數(shù)據(jù)狀態(tài)。 在對數(shù)據(jù)進(jìn)行分類匯總后，仍可以對已分類字段繼續(xù)按其他方式匯總，稱之為分類匯總嵌套。以下演示為對單價(jià)取平均的嵌套。,Document number,30,五、分類匯總,Document number,31,六、合并計(jì)算,1、合并計(jì)算方式： A、使用三維公式進(jìn)行合并計(jì)算 B、按位置進(jìn)行合并計(jì)算 所有源數(shù)據(jù)具有同樣的位置順序，可以按位置進(jìn)行合并計(jì)算。 C、按分類進(jìn)行合并計(jì)算

17、如果要匯總計(jì)算一組具有相同行、列標(biāo)志但以不同的方式組織數(shù)據(jù)的工作表，可以按分類進(jìn)行合并計(jì)算。 D、通過生成數(shù)據(jù)透視表進(jìn)行合并計(jì)算,Document number,32,六、合并計(jì)算,2、不同合并計(jì)算方式的使用范圍,Document number,33,六、合并計(jì)算,3、使用三維公式合并計(jì)算 即通過輸入合并計(jì)算公式，公式中的引用應(yīng)指向每張工作表中待合并數(shù)據(jù)所在單元格，即可進(jìn)行合并計(jì)算。 如下圖，B4單元格即為一合并公式。,Document number,34,六、合并計(jì)算,4、按位置進(jìn)行合并計(jì)算,Document number,35,六、合并計(jì)算,5、按分類進(jìn)行合并計(jì)算,按分類匯總通常按首行和最

18、左端為數(shù)據(jù)標(biāo)志，創(chuàng)建合并計(jì)算表。,Document number,36,七、數(shù)據(jù)透視表,1、作用： 數(shù)據(jù)透視表可以全面的對數(shù)據(jù)清單進(jìn)行重新組織和統(tǒng)計(jì)數(shù)據(jù)。 2、定義： 數(shù)據(jù)透視表是一種對大量數(shù)據(jù)進(jìn)行快速匯總和建立交叉列表的交互式表格，其不僅可以轉(zhuǎn)換行列以顯示源數(shù)據(jù)的不同匯總結(jié)果，也可以顯示不同頁面以篩選數(shù)據(jù)，還可以根據(jù)用戶的需要顯示區(qū)域中的細(xì)節(jié)數(shù)據(jù)。,Document number,37,七、數(shù)據(jù)透視表,3、特點(diǎn)： EXCEL提供了向?qū)Чδ?，易于建立?shù)據(jù)透視表。 真正的按用戶設(shè)計(jì)的格式來完成數(shù)據(jù)透視表的建立。 當(dāng)原始數(shù)據(jù)更新后,只需單擊“更新數(shù)據(jù)”按鈕，數(shù)據(jù)透視表就會(huì)自動(dòng)更新數(shù)據(jù) 當(dāng)用戶認(rèn)為已有的數(shù)據(jù)透視表不理想時(shí)，可以方便的修改數(shù)據(jù)透視表。,Document number,38,七、數(shù)據(jù)透視表4、用法：步驟一,Document number,39,七、數(shù)據(jù)透視表,4、用法： 步驟二：,步驟三：,Document number,40,七、數(shù)據(jù)透視表,4、用法： 布局 拖動(dòng),Document number,41,七、