EXCEL基礎(chǔ)應(yīng)用培訓(xùn)課程(共66張)

1、前言（Preface） Excel 是一個功能強大的電子表格軟件，是 Office 辦公自動化系列軟件的重要組成部分之一。 Excel 具有強大的數(shù)據(jù)計算與分析功能，可以把數(shù)據(jù)用各種統(tǒng)計圖的形式形象地表示出來，被廣泛應(yīng)用于財務(wù)、金融、經(jīng)濟、審計和統(tǒng)計等眾多領(lǐng)域。 熟練使用Excel，掌握Excel的功能，能大大的提高我們的工作效率。2培訓(xùn)內(nèi)容3Excel基礎(chǔ)認識及設(shè)置Excel常用函數(shù)篩選與排序數(shù)據(jù)透視表與圖表鏈接及超級鏈接Excel認識及基礎(chǔ)設(shè)置4Excel主窗口界面Excel基本要素Excel簡化操作Excel常用快捷鍵Excel認識及基礎(chǔ)設(shè)置目錄5Excel主窗口界面Excel基本要素E

2、xcel簡化操作Excel常用快捷鍵6標(biāo)題欄標(biāo)題欄菜單欄菜單欄工具欄工具欄名稱框名稱框編輯框編輯框工作表區(qū)工作表區(qū)工作表標(biāo)簽區(qū)域工作表標(biāo)簽區(qū)域狀態(tài)欄狀態(tài)欄滾滾動動條條區(qū)區(qū)域域滾動條區(qū)域滾動條區(qū)域Excel基本要素7要素解釋工作簿 book存儲并處理數(shù)據(jù)的文件，可包含多個工作表，系統(tǒng)默認為3個工作表sheet由行和列組成的表格，用來存儲和處理數(shù)據(jù)的基礎(chǔ)行row行標(biāo)為數(shù)字，每一工作表最多有65,536行列column列標(biāo)為字母，每一個工作表最多有256列單元格cellexcel基本要素，列標(biāo)行標(biāo)表示，如：A8、CD26，每一個工作表最多有1,677,216個單元格備注：2007版本中最多有1,04

3、8,576行、16,384列Excel簡化操作8提高效率善用右鍵操作自定義工具欄選項設(shè)置良好習(xí)慣Excel使用應(yīng)具備的良好習(xí)慣一、同一對象請使用同一名稱，而不要使用簡寫、別稱，以便使用函數(shù)查找、引用，如果你使用過vlookup等函數(shù)你就可體會到這個習(xí)慣的必要性。同理，不同部門間對同一對象亦應(yīng)統(tǒng)一名稱或編碼，便于數(shù)據(jù)的傳遞、引用。二、排版時不要在字符串內(nèi)、前后插入空格，盡量使用對齊功能來排版，以便使用函數(shù)查找、引用。9Excel使用應(yīng)具備的良好習(xí)慣三、在要使用公式的單元格，盡量不使用合并單元格功能（可用跨列對齊來居中）以便填充公式、排序、篩選。四、排版時使用頂端標(biāo)題行、標(biāo)題列功能、而不要笨拙、野

4、蠻地強制性插入標(biāo)題行、標(biāo)題列。五、數(shù)字及文本的對齊方式請使用Excel默認的格式，即數(shù)字靠右對齊，文本靠左對齊。數(shù)字使用千位分隔符。10頂端標(biāo)題行設(shè)置窗口11Excel使用應(yīng)具備的良好習(xí)慣六、學(xué)會使用拆分窗口功能，免除為看到標(biāo)題行而大量滾屏，以提高工作效率。七、工作簿內(nèi)工作表較多時，使用工作表標(biāo)簽的快捷菜單，快速選取工作表。通過用鼠標(biāo)右鍵單擊標(biāo)簽滾動按鈕，顯示快捷菜單。八、經(jīng)常使用快捷鍵，提高工作效率。九、在同一工作簿的不同工作表的相同單元格輸入相同的數(shù)據(jù)或設(shè)置相同的格式，請使用組合工作表功能，以提高效率。12文本數(shù)字時間文本格式 9,864,882.66 2008-7-12009年8月8日9

5、9.33%2008-7-24 9:29:440112800023477865521:22:08EXCEL1998.22Jul-22 1/2字符顯示格式范例13Excel使用應(yīng)具備的良好習(xí)慣十、 在要進行乘除等運算時，要養(yǎng)成在公式的最外層使用round函數(shù)的習(xí)慣，以免出現(xiàn)四舍五入的誤差。十一、在計算結(jié)果可能出現(xiàn)#DIV/0!、#VALUE!、#N/A等錯誤，而其它單元格又需對此單元格進行運算時，要養(yǎng)成使用if和ISERROR函數(shù)消除錯誤值的習(xí)慣。十二、要對工作表進行臨時性排序時，養(yǎng)成添加序號列的習(xí)慣，以便恢復(fù)原來的順序。十三、養(yǎng)成使用狀態(tài)欄進行臨時性的求和、計數(shù)、求平均值的習(xí)慣。14工作簿選項設(shè)

6、置u 統(tǒng)一工作表的外觀、格式，減少重復(fù)調(diào)整操作15目的操作u【工具】選項自定義工具欄位置： 工具欄操作：【工具】自定義：分為工具欄、命令、選項三個選項卡。在“命令”選項卡可以將你要經(jīng)常用到的一些命令拖到工具欄上，以方便操作，不要時將其拖回去即可。16善用鼠標(biāo)右鍵17單元格行工作表常用快捷鍵18移動光標(biāo)編輯工作表填充工作表其他快捷鍵移動光標(biāo)19Ctrl+光標(biāo)鍵 移動到工作表邊緣； Ctrl+Home 回到A1； Ctrl+End 去到本工作表的最尾端； Ctrl+Page Up (Down) 在同一工作簿中向前、向后切換工作表； Shift+Enter在選定區(qū)域內(nèi)從下往上或從右往左移動，即與 E

7、nter的移動方向相反； Shift+Tab在選定區(qū)域內(nèi)從右至左由下至上移動，即與Tab的移動方向相反。編輯工作表(一)20快捷鍵快捷鍵用法解釋用法解釋Ctrl+A全部選定Ctrl+空格鍵選定整列注：如果與輸入法切換的快捷鍵相沖突時，輸入法切換優(yōu)先Shift+空格鍵選定整行注：此快捷鍵與全角半角切換的快捷鍵相沖突時，輸入法切換優(yōu)先Ctrl+C復(fù)制Ctrl+X剪切Ctrl+V粘貼Ctrl+Alt+V選擇性粘貼Ctrl+Shift+（雙引號）將活動單元格上方單元格中的數(shù)值復(fù)制到當(dāng)前單元格或編輯欄編輯工作表(二)21快捷鍵用法解釋Ctrl+（撇號）：將活動單元格上方單元格中的公式復(fù)制到當(dāng)前單元格或編

8、輯欄Ctrl+B單元格內(nèi)文字加粗Ctrl+3 單元格內(nèi)文字變成斜體Ctrl+4單元格內(nèi)文字加下劃線 Ctrl+Z回到前一步Alt+Tab切換窗口功能鍵F4、 Alt+Enter重復(fù)上一步操作功能鍵F2或Ctrl+在顯示公式和計算結(jié)果之間切換，若單元格內(nèi)為文字，按F2直接在文字的最后端顯示光標(biāo)，可直接進行修改編輯工作表(三)22快捷鍵用法解釋Ctrl+9隱藏選中單元格所在行（取消隱藏用Ctrl+Shift+9）。 Ctrl+0隱藏選中單元格所在列（取消隱藏用Ctrl+Shift+0）。 Ctrl+F9最小化工作簿窗口。 Ctrl+F10最大化或還原選定的工作簿窗口。 PrtScr將屏幕圖片復(fù)制到

9、剪貼板中。 Alt+ PrtScr將當(dāng)前窗口的圖片復(fù)制到剪貼板中。 Alt+ Enter編輯單元格時換行輸入。填充工作表填充工作表23快捷鍵用法解釋Ctrl+Enter在多個單元格輸入同一內(nèi)容，即先選中所需填寫的單元格，輸入數(shù)據(jù)，最后用Ctrl+Enter即可。（不受單元格相鄰與否的限制） Ctrl+DR向下右填充。(相鄰單元格之間)雙擊填充柄復(fù)制公式到整列（至文件尾），即選中有公式的單元格，待右下角出現(xiàn)黑色填充柄時，雙擊即可。 Shift +F11(Alt+Shift+F1)快速插入新工作表。 右鍵單擊工作薄左下角的工作表滾動軸(sheet tabs)在快捷菜單中選擇目標(biāo)工作表。 快速選擇的

10、工作表Alt+ =(等號)用 SUM 函數(shù)插入“自動求和”公式。 Ctrl+;（分號）輸入當(dāng)前日期。 Ctrl+Shift+:（冒號）輸入當(dāng)前時間。其他快捷鍵 Excel的幫助系統(tǒng)中有大量常用的快捷鍵的示例說明，在實際應(yīng)用中，可以點擊【幫助】菜單選項在彈出的對話框中輸入快捷鍵來搜素相關(guān)幫助信息。24常用函數(shù)函數(shù)基本格式及圖示理解常用函數(shù)語法及應(yīng)用實例25函數(shù)的基本格式 必須以=開頭，加上括號和自變量 自變量可以是：范圍、地址、數(shù)值、函數(shù)、范圍名稱、文字、邏輯值 =SUM(c2:h10,k5,100,average(m3:m6)以SUM為例，自變量最多可達30個 有自變量的函數(shù) =SUM(c2:

11、h10) 沒有自變量的函數(shù) =NOW()、=TODAY()26函數(shù)理解 函數(shù)的作用：將指定的數(shù)據(jù)按照一定的規(guī)則轉(zhuǎn)化為需要的結(jié)果27輸入函數(shù)的方法直接在單元格中輸入完整的函數(shù)內(nèi)容 =sum(c2:h10)先輸入指令和左括號再拖曳范圍 =sum(點選插入/函數(shù)，使用插入函數(shù)對話窗口28常用函數(shù)語法及應(yīng)用實例數(shù)學(xué)函數(shù)邏輯判斷函數(shù)取值函數(shù)Vlookup/Hlookup函數(shù)時間函數(shù)轉(zhuǎn)換函數(shù)29數(shù)學(xué)函數(shù) 加總：=SUM(D3:D6) 相乘：PRODUCT(5, 4)=20 取整數(shù)=INT(123.579) =123 指定小數(shù)位數(shù)=ROUND(123.579,2) =123.58 無條件舍位=ROUNDDO

12、WN(123.579,2) =123.57 無條件進位=ROUNDUP(123.572,2) =123.58 距陣相乘：=SUMPRODUCT(B6:E6,A3:D3) 求余數(shù)：=MOD(9,2)=1 求商：=QUOTIENT(8,3)=2 倍數(shù)進位：=CEILING(17.3,0.5)=17.5 =TRUNC(17.3,0.5)=1730SUMIF求和函數(shù)語法格式：SUMIF(比對的范圍,條件,要加總的范圍)示例：=SUMIF(D2:D10,”=1000000”)=SUMIF(資產(chǎn)類別,D5,期初金額)31邏輯判斷函數(shù)IF=IF(條件,條件成立使用此值,不成立使用此值)=IF(D2=90,”

13、優(yōu)”,”)=IF(成績=90,”優(yōu)”,IF(考績=80,”甲”, IF(成績=70,”乙”, IF(成績=60,”丙”, ”丁”)=IF(數(shù)量1000000,金額*0.05,0)uIF語句最多只能嵌套7層32IF組合語句應(yīng)用=IF(AND(B7=90,C7=90,D7=90),“優(yōu)秀,)=IF(OR(B760,C760,D790) ,1,2)返回TRUE 或FALSE33數(shù)值類型判斷函數(shù)ISIS相關(guān)函數(shù)(共有11個)，返回TRUE 或FALSEISNA()ISTEXT()ISBLANK()ISERROR()34取值函數(shù)之字符串 字符串比較=EXACT(“北京”,“北京 ) =FALSE 刪除字

14、符串右邊空格=TRIM(“北京 “) =“北京” 套用指定格式并轉(zhuǎn)換成文字 =TEXT(123456789,“$ #,#0 元整”) =$ 123,456,789 元整 重復(fù)字符串=REPT(,ROUND(D3*100,0) Right、left、mid函數(shù)35取值函數(shù)之小數(shù)取值函數(shù)之小數(shù)取整函數(shù) =INT(123.579) =123 =TRUNC(123.579) =123指定小數(shù)位數(shù)=ROUND(123.579,2) =123.58 =ROUNDDOWN(123.579,2) =123.57 =ROUNDUP(123.572,2) =123.58倍數(shù)進位函數(shù)=CEILING(15.6,0.

15、5) =16 =FLOOR(15.6,0.5) =15.536Vlookup/Hlookup函數(shù)函數(shù)功能： Vlookup用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值 Hlookup用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值 當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用Hlookup 當(dāng)比較值位于數(shù)據(jù)表的最左列，并且要查找右面給定列中的數(shù)據(jù)時，請使用Vlookup37使用方法使用方法 VLOOKUP(lookup_value,table_array,col_index_num,range_lo

16、okup) ) =VLOOKUP(A2,對照表,2,FALSE 或true) 使用true時，對照表中的資料必須由小到大排序 HLOOKUP() 只是資料排列的方向不同 LOOKUP(lookup_value,lookup_vector,result_vector ) =LOOKUP(A2,B2:B10,C2:C10) 對照表中的資料必須由小到大排序38時間函數(shù)時間函數(shù)(一一) Excel的日期范圍：1900/1/1-9999/12/31 NOW() = 2006/1/20 15:18返回當(dāng)前日期和時間 TODAY() = 2006/1/20返回當(dāng)前日期 DATEVALUE(日期)將字符串轉(zhuǎn)換

17、成天數(shù)=DATEVALUE(2005/12/31)=38717=DATEVALUE(“2008/7/1”) -TODAY()=921=“從今天到2008/7/1共經(jīng)過&DATEVALUE(2008/7/1) -TODAY() &天”39時間函數(shù)時間函數(shù)(二二) DAY(TODAY() =27 第幾天 MONTH(TODAY() =12 第幾個月 YEAR(TODAY() =2005 第幾年 WORKDAY(開始日期，經(jīng)過天數(shù),假日) 計算二指定日期之間的工作天(可扣除特別假) NETWORKDAYS(開始日期,結(jié)束日期,假日) 計算二指定日期之間的可工作天數(shù) =NETWORKD

18、AYS(2005/12/25,2006/1/25) =NETWORKDAYS(D1,D2)40時間函數(shù)時間函數(shù)(三三) DATEDIF() 生日、年資計算(計算到今天) =DATEDIF(D3,TODAY(),y) =17 (D3=1988/7/16) =DATEDIF(D3,TODAY(),“ym) =5 =DATEDIF(D3,TODAY(),“md) =11 =DATEDIF(D3,TODAY(),“m”) =209個月 =DATEDIF(D3,TODAY(),“d”) =6373天 半年之后的票期(配合TEXT()取得中文日期格式) =“半年后到期日為：”&TEXT(TODAY

19、()+180,”ee年mm月dd日”) =TEXT(D16,yyyy年mm月dd日“)41轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù) LOWER：所有大寫轉(zhuǎn)換為小寫； 語法：lower（text） UPPER：所有小寫轉(zhuǎn)換為大寫；語法：upper（text） PROPER：首字母及任何非字母后的首字段轉(zhuǎn)換大寫，其他字母轉(zhuǎn)換為小寫 語法：proper（text） VALUE：將一個代表數(shù)值的文本字符串轉(zhuǎn)換成數(shù)值 語法：value（text） TEXT：將任意類型的字符串轉(zhuǎn)換成文本 語法：text（text）42數(shù)據(jù)透視表數(shù)據(jù)透視表數(shù)據(jù)透視表概念數(shù)據(jù)透視表應(yīng)用43數(shù)據(jù)透視表概念 數(shù)據(jù)透視表是“數(shù)據(jù)”菜單中“數(shù)據(jù)透視表與數(shù)據(jù)

20、透視圖”的一部分， “數(shù)據(jù)透視表與數(shù)據(jù)透視圖”有機的綜合了數(shù)據(jù)排序、篩選、分類匯總等數(shù)據(jù)分析的優(yōu)點，可方便地調(diào)整分類匯總的方式，靈活地以多種不同方式展示數(shù)據(jù)的特征。如計算平均數(shù)、標(biāo)準(zhǔn)差，建立列聯(lián)表、計算百分比、建立新的數(shù)據(jù)子集、繪制統(tǒng)計圖形等均可利用這一工具完成。因此，該工具是最常用、功能最全的EXCEL數(shù)據(jù)分析工具之一。44創(chuàng)建數(shù)據(jù)透視表步驟：1、在數(shù)據(jù)工作表選定任意單元格，單擊菜單中的“數(shù)據(jù)數(shù)據(jù)透視表與數(shù)據(jù)透視圖” ，彈出“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之1”對話框；2、單擊“下一步”按紐框，彈出“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之2”對話框；3、確定EXCEL已正確識別數(shù)據(jù)的單元格區(qū)域

21、（一般自動識別），如沒有自動識別，則用鼠標(biāo)進行選取，然后單擊“下一步”按紐，彈出“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之3”對話框4、單擊完成鍵，EXCEL創(chuàng)建了一張新的工作表。45“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之1”對話框46“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之步驟之2”對話框?qū)υ捒?7“數(shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?shù)據(jù)透視表與數(shù)據(jù)透視圖向?qū)?步驟之步驟之3”對話框?qū)υ捒?8數(shù)據(jù)透視表雛形49頁字段頁字段數(shù)據(jù)字段數(shù)據(jù)字段行字段行字段列字段列字段分類變量，分類變量，該變量的該變量的分組類別分組類別排在一行，排在一行，每個變量每個變量占一列。占一列?？煞哦鄠€可放多個變量。

22、變量。分類變量，分類變量，該變量的該變量的分組類別分組類別排在一列，排在一列，每個類別每個類別占一行。占一行?？煞哦鄠€可放多個變量。變量。在頁字段中，放入分類變量，選擇某一分類，產(chǎn)生一在頁字段中，放入分類變量，選擇某一分類，產(chǎn)生一個數(shù)據(jù)透視表，選擇另一分類，產(chǎn)生新的透視表，并個數(shù)據(jù)透視表，選擇另一分類，產(chǎn)生新的透視表，并覆蓋前次表格，相當(dāng)于統(tǒng)計上的分層。選擇覆蓋前次表格，相當(dāng)于統(tǒng)計上的分層。選擇“全部全部”時，時，則忽略該變量的作用。則忽略該變量的作用。50在透視表中添加或刪除字段添加字段步驟 單擊數(shù)據(jù)透視表中任一單元格，從“數(shù)據(jù)”菜單中選中“數(shù)據(jù)透視表” 拖曳“列標(biāo)”鈕至“數(shù)據(jù)”框中 單擊確

23、定按鈕刪除字段步驟 單擊數(shù)據(jù)透視表中任一單元格，從“數(shù)據(jù)”菜單中選中“數(shù)據(jù)透視表” 從“數(shù)據(jù)”框中將某“列標(biāo)”拖曳出來 單擊確定按鈕也可以利用工具欄51數(shù)據(jù)透視表的特點 透視性：可以對各字段進行設(shè)置，從多角度分析數(shù)據(jù)，還可以改變匯總方式； “只讀”性：可以修飾、制作圖表，在源數(shù)據(jù)中更改了某個數(shù)據(jù)，必須通過“更新數(shù)據(jù)”，才能達到修改的目的。52篩選與排序操作篩選操作詳解排序操作詳解53篩選操作詳解篩選操作詳解 利用“數(shù)據(jù)”菜單中的“篩選”命令可對清單中指定的數(shù)據(jù)進行查找和完成其他工作。 在“篩選”命令子菜單中，Excel同時提供了“自動篩選”和“高級篩選”兩種命令來篩選用戶的數(shù)據(jù)?！白詣雍Y選”可滿足大部分需求，但當(dāng)需要更復(fù)雜的條件來篩選清單時，則可用“高級篩選器”。在取消了“自動篩選”命令后，則會顯示所有的數(shù)據(jù)。 54排序操作詳解排序操作詳解 以當(dāng)前數(shù)據(jù)清單中的一個或幾個變量為關(guān)鍵字，對整個數(shù)據(jù)清單進行排序。 排序方式：升序、降序 數(shù)字型變量按數(shù)值大??； 字符型變量按ASCII碼大??； 日期型變量按日期先后； 中文字按拼音或筆畫。55單個關(guān)鍵字排序單個關(guān)鍵字排序1、用鼠標(biāo)單擊一個排序關(guān)鍵字；2、單擊常用工具欄中的升序（A-Z）按紐或降序（Z-A）按紐。那么整個數(shù)據(jù)清單就按指定關(guān)鍵字進行排序5