Excel實(shí)用教程大全

1、Excel高級(jí)使用教程大綱公式和格式(此部分主要用于使報(bào)表做得更規(guī)范、更有效)1、常用函數(shù)介紹求和類：Suma) SUM (numberl ,number2,)計(jì)數(shù)類： Count、countaCOUNT (value1 ,value2,.)COUNTA (value1 ,value2,)日期類：Today、Now (日期可進(jìn)行加減)TODAY ()NOW ()財(cái)務(wù)類：PMT (rate,nper,pv,fv,type)FV(rate,nper,pmt,pv,type)2、序列填充方式：公式填充：拖動(dòng)填充手柄+數(shù)字序列填充：同時(shí)選擇已知2位數(shù)字拖動(dòng)填充手柄+選擇已知1位數(shù)字Ctrl+拖動(dòng)填充手

2、柄+文本序列填充：Tools Option Custom list Newa)日期序列填充：同時(shí)選擇已知日期值拖動(dòng)填充手柄+3、單元格的引用：相對(duì)引用：A1在行中復(fù)制時(shí)，行不動(dòng)，列在動(dòng)，在列中復(fù)制時(shí)，列不動(dòng),行在動(dòng).絕對(duì)引用：$A$1 f4不能動(dòng)白要加$混合引用：$A1 , A$1,適合向兩個(gè)方向上復(fù)制.分別進(jìn)行討論，只討論默認(rèn)在動(dòng)的，不討論默認(rèn)不動(dòng)的。4、輸入數(shù)據(jù)的技巧選擇活動(dòng)單元格周圍的相鄰區(qū)域 ：Ctrl+a選擇活動(dòng)單元格相鄰方向上的區(qū)域：ctrl+shift+方向鍵移動(dòng)列：按shift+拖動(dòng)邊框長(zhǎng)數(shù)字的輸入：3412 formatnumbertext分?jǐn)?shù)的輸入：0空格1/5單元格中的內(nèi)

3、容換行：定位光標(biāo)到要換行處Alt+enter快捷日期輸入 “ Ctrl + ;” 當(dāng)前時(shí)間輸入 “Ctrl + shift + ;5、 條件格式：Format Conditional formatting自身值決定自已的格式。由公式來(lái)決定格式。And(條件1,條件2,條件3)Or(條件1,條件2,條件3)6、輸入數(shù)據(jù)的有效性：數(shù)據(jù)有效性Data Validation list粘帖和選擇性粘帖：Copy Edit Paste special粘貼數(shù)值或公式粘貼鏈接轉(zhuǎn)置：行和列的切換8、格式刷的使用：雙擊多次應(yīng)用格式單擊 單次應(yīng)用格式9、工作表背景 Format Backgroup10、自定義菜單和

4、工具欄Tools Customize 拖放按鈕到工具欄11、模板應(yīng)用模板： File New Templates on my computer spreadsheet solutions自制模板： File Save as Template二、預(yù)覽打印及發(fā)送文檔（此部分可令打印更技巧，節(jié)省紙張）1、打印頁(yè)面設(shè)置：.調(diào)整打印大小文件頁(yè)面設(shè)置：ii.在每頁(yè)上都打印行列標(biāo)題（在每頁(yè)上都打印標(biāo)題，避免要與第一頁(yè)對(duì)照才可找到數(shù) 據(jù)說(shuō)明）File Page setup Sheet：打印批注： File Page setup Sheet comments創(chuàng)建頁(yè)眉頁(yè)腳：打印預(yù)覽 頁(yè)眉頁(yè)腳2、使用E-mail功

5、能：文件發(fā)送工具欄：?jiǎn)螕?e-mail圖表和批注1、批注 Insert Comment圖表Insert ChartType修改圖表類型：各種圖表的作用修改圖表數(shù)據(jù)：刻度、色彩等組合圖表的使用（一類數(shù)據(jù)系列使用一種圖表；另一類數(shù)據(jù)系列使用另種圖表）雙軸線柱圖及餅圖的制作四、 數(shù)據(jù)庫(kù)入門1、排序排行：a）單依據(jù)：定位光標(biāo)到依據(jù)列，單擊按鈕b）多依據(jù)：datasort排列2、篩選：（按條件顯示記錄）自動(dòng)篩選：datafilterauto filtera）指定內(nèi)容篩選b）取極端數(shù)據(jù)top 10：（只對(duì)數(shù)值性字段有效）c）自定義：（最多可以定義兩個(gè)條件，and or）高級(jí)篩選：a）創(chuàng)建條件區(qū)域：b）將條

6、件中用到的列的標(biāo)題復(fù)制到原表格以外的某處c） 在復(fù)制出的標(biāo)題下方寫條件。d）如果條件寫在同一行中為and如果條件寫在同一列中為or*:表示多個(gè)字符？：只表示一個(gè)字符如：Make ModelHondaChevroletfordTypeRate*family*family*family*Price Price2000030000Datafilteradvanced filter去除重復(fù)記錄：3、數(shù)據(jù)庫(kù)函數(shù):=dsum(database,field,criteria)=daverage()=dmax()=dmin()=dcount().=dcounta()4、記錄單(便于定位，錄入數(shù)據(jù))i. Dat

7、a:Forms數(shù)據(jù)庫(kù)管理1、數(shù)據(jù)導(dǎo)入和導(dǎo)出Export: FileSave as type Text （Tab delimited）Import:FileOpen typeText Import wizard2、分類匯總Data subtotal設(shè)置分類自段/匯總方式3、數(shù)據(jù)透視表及數(shù)據(jù)透視圖i.ii.datapivot table and pivot chart report制作透視有的方法按向?qū)Р僮鳎篴）透視表的布局：b）分析出需求中要求計(jì)算的字段（data）c）分析出需求中要求顯示字段（除去計(jì)算的都是要顯示）顯示指定項(xiàng) page顯示多項(xiàng)或所有項(xiàng) rowb）直接拖放字段名a）如果row中有

8、多項(xiàng)字段，可將一項(xiàng)放到column中形成交叉表Page ,row,column中可以為空，但不能放重復(fù)的內(nèi)容.data可以重復(fù)，但不能為空.數(shù)據(jù)透視圖:單擊工具欄上圖標(biāo)按鈕工作表管理1、鏈接工作表超級(jí)鏈接Insert hyperlink Place in this document公式鏈接= select source data粘貝占鏈接 copy paste special paste link矩陣鏈接 insert name define select target cell insert menu name pase2、名稱引用：定位功能，用作公式中的參數(shù)，跨工作表操作。方法：選擇要命名

9、的區(qū)域，單擊名稱框輸入名字：刪除：insertnamedefinedelete3、數(shù)據(jù)合并（如全年報(bào)表合計(jì)）Data Consolidate 選擇要合并的區(qū)域Add Use Lable4、 組合Select theColumns orRows Group and outline Groupalt+shift+取消組合alt+shift+5、創(chuàng)建報(bào)表視圖-自定義視圖： View customer views add-顯示視圖： View customer views show七、共享和保護(hù)J1、保護(hù)指定的區(qū)域：Ctrl+A選擇所有單元格取消 locked Hidden選擇要保護(hù)的單元格區(qū)域選中

10、locked Hidden加密，取消第一個(gè)復(fù)選框。2、工作簿保護(hù)i.加密3、文件的保護(hù)：i. ToolsOptionsSecurityPassword To Open 或者 Password To Modify4、共享工作簿Toolsshare workbook八、 宏1、宏的制作VBA CODE錄制2、宏的執(zhí)行菜單快捷鍵制作宏按鈕View Toolbars Form button調(diào)試宏Toolsmacromacors選擇一個(gè)調(diào)試的macro單擊edit按F8單步執(zhí)行macrocode4、編輯宏修改前：Sub Macro1()Range(C10).SelectRange(A5:J82).Adv

11、ancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range(D1:E2), Unique:=FalseEnd Sub修改后：Sub Macro1()Range(C10).SelectRange(A5:J82).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range(D1).currentregion, Unique:=False End Sub九、IF邏輯塊1、趨勢(shì)分析：TREND(known_ys,known_xs,new_xs,const)CTRL+SHIFT+ENT

12、ER2、變量求解：Tools Goal seek3、規(guī)劃求解：Tools Add Ins Solver4、方案管理器：Tools Scenario5、頻率分布：Tools Add Ins Analysis Toolpak Histogram6、數(shù)據(jù)審核：可以讓你對(duì)公式和結(jié)果看的明明白白? 審核工作表 工具公式審核顯示審核工具欄一 Toolsformula auditingshow formula auditingcircle invalid data? 單元格的追蹤：一 Toolsformula Auditingshow formula Auditingtrace precedents7、數(shù)據(jù)

13、確認(rèn)：Datavalidationsettingscustom數(shù)值的效性：=SUM($H$4:$H$25)=5000數(shù)值的唯一性：=COUNTIF($A$4:$A$18,A4)=1下拉列表的制作：a)內(nèi)容來(lái)源于其他表格：a)選擇要制作下拉列表的內(nèi)容區(qū)域，命名b) 選擇要使用的區(qū)域，=nameb) 內(nèi)容自定義：選擇要使用的區(qū)域，datavalidationallowlistsource,輸入 內(nèi)容，任意兩內(nèi)容之間英文半角的逗號(hào)隔開(kāi)8、使用 IF 函數(shù)：IF(logical_test,Value_if_true,Value_if_false)嵌套IF函數(shù)=IF(條件，真，IF(條件，真，假)合并邏輯函數(shù)IF(AND(條件)，真，假)a) IF嵌套(一個(gè)IF最多可以嵌套7個(gè)if)共8個(gè)IF=SUMIF(range, criteria,data_range)Range:條件所在的區(qū)域Criteria:條件，表達(dá)式用Data_Range:求和的區(qū)域=COUNTIF(range,criteria)十、高級(jí)數(shù)據(jù)管理HLOOKUP/VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value:查找的依據(jù)，它一定在數(shù)據(jù)源的第一列，且值唯一。table_array：數(shù)據(jù)源，命名數(shù)據(jù)源。col_in