EXCEL教程培訓(xùn)課件

你好，Excel!匯報人：Excel的功能序言1.

創(chuàng)造表格

Createansheet2.數(shù)據(jù)共享

Sharethedata3.

娛樂生活

Entertainment內(nèi)容大綱010302日常操作常用函數(shù)推薦Excel服務(wù)器功能介紹日常操作PART1Copy、Paste、Cutclip復(fù)制、粘貼、剪切根據(jù)目的不同，可以對數(shù)據(jù)進(jìn)行剪切、復(fù)制、粘貼、選擇性粘貼。對于選擇性粘貼，可以對目標(biāo)區(qū)域數(shù)據(jù)進(jìn)行加減乘除的轉(zhuǎn)換，還可以實現(xiàn)目標(biāo)區(qū)域數(shù)據(jù)進(jìn)行轉(zhuǎn)置DataValidation數(shù)據(jù)有效性通過設(shè)置實現(xiàn)固定標(biāo)題靈活選擇，演示如下，操作見演示：Conditionalformatting條件格式通過設(shè)置實現(xiàn)固定標(biāo)題靈活選擇，演示如下，操作見演示：Aggregatedata分類匯總實現(xiàn)對同類別數(shù)據(jù)進(jìn)行匯總，根據(jù)數(shù)據(jù)標(biāo)題不同和匯總方式的不同進(jìn)行匯總；分類匯總后點擊表頭左上方1、2、3可以對總會總數(shù)進(jìn)行展開和折疊；注意對數(shù)據(jù)分類匯總時要將同類別數(shù)據(jù)進(jìn)行排序，否則會在不同的行或列出現(xiàn)；具體見截圖和實例演示：AutoFilter自動篩選自動篩選：根據(jù)自定義條件進(jìn)行篩選，或者直接輸入條件篩選；AdvanceFilter高級篩選高級篩選：通過兩個或者以上的條件實現(xiàn)篩選。“列表區(qū)域（L）”為需要高級篩選的數(shù)據(jù)，“條件區(qū)域（C）”為篩選條件，“復(fù)制到（T）”為篩選結(jié)果顯示的區(qū)域Pivottable數(shù)據(jù)透視表/圖數(shù)據(jù)透視表：根據(jù)明細(xì)數(shù)據(jù)字段屬性求匯總值、最大值、最小值、方差、百分比等功能；1、在“插入”菜單欄找到數(shù)據(jù)透視表和數(shù)據(jù)透視圖（P）:Pivottable數(shù)據(jù)透視表/圖2、選擇要數(shù)據(jù)透視的區(qū)域，然后下一步，注意透視表原數(shù)據(jù)表頭不能有空格和合并單元格，如圖：3、此時你可以選擇在當(dāng)前工作表和新建工作表建立數(shù)據(jù)透視表，然后點擊“布局（L）”，將表格屬性拖入列或航中，將需要加工的數(shù)據(jù)拖入數(shù)據(jù)區(qū)域，請看演示：Pivottable數(shù)據(jù)透視表/圖Pivottable數(shù)據(jù)透視表/圖4、雙擊已拖入數(shù)據(jù)區(qū)域的項目，可以選擇要匯總的方式以及值顯示方式，如下圖：Hyperlink超鏈接“插入”菜單欄中找到“超鏈接”，點擊彈出插入超鏈接框其他文件選擇“原有文件或網(wǎng)頁（X）”，本文檔選擇“本文檔中的位置（A）”選擇對應(yīng)文件名，然后確認(rèn)InsertObject插入對象“插入”菜單欄中找到“對象”，點擊彈出插入對象框其他文件選擇“由文件創(chuàng)建”，本文創(chuàng)建選擇“新建”Search、substituted查找、替換打開對應(yīng)工作表，按Ctrl+F為尋找，將依次查找輸入符合條件的結(jié)果，但不能查到保留公式計算結(jié)果的數(shù)值打開對應(yīng)工作表，按Ctrl+F為替換，將查找的內(nèi)容替換為要替換的內(nèi)容，還可以根據(jù)選項進(jìn)行設(shè)置，也可以替換公式。FreezePane凍結(jié)窗口凍結(jié)窗口：當(dāng)需要顯示表頭或者列標(biāo)題時就需要在合適的位置凍結(jié)窗口；常用函數(shù)PART2Generalintroduction總體介紹函數(shù)的定義：Excel中所提的函數(shù)其實是一些預(yù)定義的公式，他們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或者結(jié)果進(jìn)行計算。用戶可以直接用他們對某個區(qū)域內(nèi)的數(shù)值進(jìn)行一系列運(yùn)算。下圖為函數(shù)結(jié)構(gòu)：Generalintroduction總體介紹公式選項表：幫助創(chuàng)建或編輯公式的工具，還可提供有關(guān)函數(shù)及其參數(shù)的信息。單擊編輯欄中“編輯公式”按鈕，就會在編輯欄下面出現(xiàn)公式選項板：Generalintroduction總體介紹函數(shù)的種類：Excel函數(shù)一共有11類，分別是數(shù)據(jù)庫函數(shù)、日期與時間函數(shù)、工程函數(shù)、財務(wù)函數(shù)、信息函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計函數(shù)、文本函數(shù)以及用戶自定義函數(shù)。下面從工作應(yīng)用的角度為大家介紹一些函數(shù)的使用方法：求和函數(shù)SUM函數(shù)是Excel中使用最多的函數(shù)，利用他進(jìn)行求和運(yùn)算可以忽略存在文本、空格等數(shù)據(jù)的單元格，語法簡單、使用方便區(qū)域求和：常用于對一張工作表所有數(shù)據(jù)求總計。此時你可以讓單元格指針停留在存在結(jié)果的單元格，然后在Excel編輯欄輸入公式“=SUM()”用鼠標(biāo)在括號中間單擊，最后拖過需要求和的所有單元格（不連續(xù)單元格可按住Ctrl鍵逐個選中）注意：SUM函數(shù)中的參數(shù)，即被求和的單元格和單元格區(qū)域不能超過30個求和函數(shù)SUMIF函數(shù)：可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。語法：SUMIF(range,criteria,[sum_range])其中，range必須項，表示條件范圍criteria必須項，表示條件sum_range可選項，表示求和范圍四舍五入Round函數(shù)：返回某個數(shù)字按指定位數(shù)舍入后的數(shù)字，格式ROUND(number,num_digits)，number就是將要進(jìn)行四舍五入的數(shù)字；num_digits則是希望的得到數(shù)字的小數(shù)點后的位數(shù)。INT函數(shù)：返回實數(shù)舍入后的整數(shù)值。因此，用INT函數(shù)進(jìn)行四舍五入還是需要一些技巧的，也就是要加上0.5，才能達(dá)到去整的目的。INT(number)，number就是將要進(jìn)行去整的數(shù)字。ROMAN工作表函數(shù)將阿拉伯?dāng)?shù)字轉(zhuǎn)換為文本形式的羅馬數(shù)字；ROUNDDOWN工作表函數(shù)靠近零值，向下（絕對值減小的方向）舍入數(shù)字ROUNDUP工作表函數(shù)遠(yuǎn)離零值，向上（絕對值增大的方向）舍入數(shù)字SQRT工作表函數(shù)返回正平方根邏輯函數(shù)用來判斷真假值，或則進(jìn)行符合檢驗的Excel函數(shù)，我們成為邏輯函數(shù)。在Excel中提供了六種邏輯函數(shù)。即AND、OR、NOT、FALSE、IF、TRUE函數(shù)。AND所有參數(shù)的邏輯值為真時返回TRUE，邏輯值為假時返回FALSE。語法為AND(logical1,logical2,…),其中l(wèi)ogical1,logical2表示待檢測的1到30個條件值，各條件值可能為TRUE,可能是FALSE，參數(shù)必須是邏輯值。邏輯函數(shù)普通條件判斷，如下圖：邏輯函數(shù)多層嵌套，如下圖：條件求解COUNTIF，根據(jù)條件計數(shù)值，可以計算單元格區(qū)域中某個文本串或數(shù)字出現(xiàn)的次數(shù)，如要求計算出大于70分的門數(shù)，或者要求找出是否有重復(fù)記錄，如下圖：文本函數(shù)LOWER：將一個文字串中的所有大寫字母轉(zhuǎn)換為小寫字母；UPPER：將文本轉(zhuǎn)成大寫形式；PROPER：將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫，將其余的字母轉(zhuǎn)換成小寫；這三種函數(shù)的基本語法形式均為函數(shù)名（text），將“pLeaseComEHere”,舉例如下：LOWER(pLeaseComEHere)=pleasecomehereUPPER(pLeaseComEHere)=PLEASECOMEHEREPROPER(pLeaseComEHere)=PleaseComeHere日期與時間函數(shù)取出當(dāng)前系統(tǒng)時間/日期信息的函數(shù)主要有NOW、TODAY;如果需要單獨的年份、月份、日數(shù)或小時的數(shù)據(jù)時，可以使用HOUR/DAY/MONTH/YEAR函數(shù)直接從日期/時間中取出需要的數(shù)據(jù)；DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)，語法形式為：DATEDIF(start_date,end_data,unit)，start_data為結(jié)束日期，可以是日期格式（2016-5-15）或者文本格式（2016/5/15）;end_date為結(jié)束日期，格式同start_date;Unit為所需信息所返回的類型查找函數(shù)LOOKUP用于返回向量（單行區(qū)域或單列區(qū)域）或者數(shù)組中的數(shù)字，函數(shù)LOOKUP有兩種語法形式：向量和數(shù)組（僅介紹向量形式）向量形式：函數(shù)LOOKUP的向量形式是在單行區(qū)域或單列區(qū)域（向量）中查找數(shù)值，然后返回第二個單行（列）區(qū)域中相同位置的數(shù)值。語法：LOOKUP(lookup_value,lookup_vector,result_vector_)Lookup_value為函數(shù)LOOKUP在第一個向量中所要尋找的數(shù)值，可以為數(shù)字、文本、邏輯值或包含數(shù)字的名稱或引用。Lookup_vector為只包含一行（列）的區(qū)域，可以喂文本、數(shù)字或邏輯值需要注意的是Lookup_vector的數(shù)值必須按升序排序，否則沒辦法返回正確結(jié)果。Result_vector只包含一行或則一列的區(qū)域，其大小必須與lookup_vector相同。如果函數(shù)LOOKUP找不到lookup_value，則查找lookup_vector中小于或等于lookup_value的最大數(shù)值。如果lookup_value小于lookup_vector中的最小值，函數(shù)LOOKUP返回錯誤值#N/A查找函數(shù)查找函數(shù)INDEX用于返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用，有兩種形式：數(shù)組和引用。數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組；引用形式通常返回引用。語法表示為INDEX(array,row_num,column_num)返回數(shù)組中指定單元格或單元格數(shù)組的數(shù)值。查找函數(shù)HLOOKUP和VLOOKUPHLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表的首行，并且要查找下面給定行中的數(shù)據(jù)時，請使用函數(shù)HLOOKUP。當(dāng)比較值位于要進(jìn)行數(shù)據(jù)查找的左邊一列時，請使用函數(shù)VLOOKUP。查找函數(shù)語法形式為：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，lookup_value表示要查找的值，它必須位于自定義查找區(qū)域的最左列。lookup_value可以為數(shù)值、引用或文字串。table_array查找的區(qū)域，用于查找數(shù)據(jù)的區(qū)域，上面的查找值必須位于這個區(qū)域的最左列?？梢允褂脤^(qū)域或區(qū)域名稱的引用。Row_index_num為table_array中待返回的匹配值的行序號。Row_index_num為1時，返回table_array第一行的數(shù)值，依此類推；Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推；range_lookup為一邏輯值，指明函數(shù)HLOOKUP查找時是精確匹配還是近視匹配。課堂練習(xí)Excel服務(wù)器介紹PART3以服務(wù)器實際演示為準(zhǔn)你好，Excel!匯報人：EXCEL操作培訓(xùn)Excel技巧與操作培訓(xùn)演講人：ONTENTSC目錄Excel操作與技巧2314基礎(chǔ)常識報表制作圖表制作常用技巧基礎(chǔ)常識1Excel基礎(chǔ)常識（1）工作簿（2）工作表（3）單元格工作簿菜單欄單元格，通過位置（列標(biāo)行標(biāo)）進(jìn)行標(biāo)識。工作表狀態(tài)欄1張工作表（sheet）由65536行*256列，即65536*256=1677萬個單元格組成；1個工作簿（book）最多可含有255張工作表。注：EXCEL2007以下版本。工具欄Excel基礎(chǔ)常識（1）公式（2）函數(shù)公式與函數(shù)公式：在工作表中對數(shù)據(jù)進(jìn)行計算的式子，它可以對工作表數(shù)值進(jìn)行加、減、乘、除等運(yùn)算。函數(shù)：EXCEL預(yù)定義的內(nèi)置程序，可以使用在公式中。公式與函數(shù)數(shù)據(jù)類型（1）數(shù)值型：一般的數(shù)字格式。（2）貨幣型：貨幣格式，對一列數(shù)值進(jìn)行小數(shù)點對齊。（3）日期時間型：可以對兩個時間段的間隔時長進(jìn)行計算。（4）文本型：在文本單元格中，數(shù)字、公式作為文本處理。公式與函數(shù)運(yùn)算符（1）算術(shù)運(yùn)算：+（加）、-（減）、*（乘）、/（除）、%（百分比）、^（乘方)（2）比較運(yùn)算：=（等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、<>（不等于）（3）字符運(yùn)算：&（連接）（4）引用運(yùn)算：（：冒號）、（，逗號）、（空格）要創(chuàng)建一個公式，首先需要選定一個單元格，輸入一個等于號“=”，然后在其后輸入公式的內(nèi)容，按回車鍵就可以按公式計算得出結(jié)果公式與函數(shù)單元格引用引用運(yùn)算符：（：冒號）、（，逗號）、（空格）（1）：（冒號）——區(qū)域運(yùn)算符，如B2:F6表示B2單元格到F6單元格矩形區(qū)域內(nèi)的所有單元格。（2），（逗號）——聯(lián)合運(yùn)算符，將多個引用合并為一個引用，如SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有單元格求和（SUM是求和函數(shù)）（3）（空格）——交叉運(yùn)算符，如SUM(B5:B26A4:D22)表示求B5至B26與A4至D22兩區(qū)域交叉單元格之和，即B5至B22之和。公式與函數(shù)絕對地址與相對地址相對地址：=B3+B4+B5+B6絕對地址：=$B$3+$B$4+$B$5+$B$6混合運(yùn)用，F(xiàn)4切換。兩者的差異是：相對地址：如果在A7單元格內(nèi)輸入公式=B3+B4+B5+B6，那么將A7中的公式復(fù)制至A8單元格時，A8的結(jié)果變成=B4+B5+B6+B7，再將A8中的公式復(fù)制至B8時，B8的結(jié)果變成=C4+C5+C6+C7，也就是相對地址在公式復(fù)制時，會隨單元格的不同而發(fā)生變化。絕對地址：在公式復(fù)制時，不會隨單元格的不同而發(fā)生變化。不管復(fù)制到哪個單元格，結(jié)果都是同一個公式，即=$B$3+$B$4+$B$5+$B$6。輸入公式公式的輸入類似于數(shù)據(jù)的輸入，在單元格中輸入公式時以等號（=）作為開始，然后輸入公式表達(dá)式。（1）啟動Excel2010，在工作簿中創(chuàng)建工作表。在工作表中選擇需要創(chuàng)建公式的單元格，在單元格中輸入公式，如表1所示。（2）完成公式輸入后，按Enter鍵或單擊其他的單元格，輸入公式的單元格中將顯示計算結(jié)果。輸入公式在工作表中可以將公式復(fù)制到其他的單元格中。（1）將鼠標(biāo)指針放置到需要復(fù)制的單元格右下角的控制柄上，鼠標(biāo)指針變?yōu)槭中危醋∈髽?biāo)左鍵拖動鼠標(biāo)至最后一個單元格釋放鼠標(biāo)，公式將被復(fù)制到鼠標(biāo)拖動過的單元格中并顯示計算結(jié)果。（2）在工作表中選擇源單元格，打開“開始”選項卡，在“剪貼板”組中單擊“復(fù)制”按鈕。在工作表中選擇目標(biāo)單元格，單擊“粘貼”按鈕上的下三角按鈕，在下拉列表中“公式”選項，此時選擇的單元格中將能預(yù)覽公式粘貼后的計算結(jié)果。使用函數(shù)向?qū)Р迦牒瘮?shù)使用函數(shù)欄輸入使用公式記憶輸入功能在單元格中輸入函數(shù)的第一個字母時，Excel2010會給出一個以該字母開頭的函數(shù)列表，顯示一個列表，按“↓”鍵在列表中移動光村選擇需要的函數(shù)，按Tab鍵將選擇的函數(shù)輸入到單元格中。函數(shù)功能數(shù)值統(tǒng)計（SUM、SUMIF、COUNT、COUNTIF等）邏輯判斷（AND、IF、ISERROR、NOT、TRUE等）文本處理（MID、MIDB、RIGHT、FIND等）日期時間（NOW、TODAY、TIME、TIMEVALUE等）查找引用（VLOOKUP、ROWS、HYPERLINK等）財務(wù)計算工程分析公式與函數(shù)SUM、SUMPRODUCT、SUMIF、SUMIFS函數(shù)公式與函數(shù)COUNT、COUNTA、COUNTIF、COUNTIFS函數(shù)公式與函數(shù)AND、OR、NOT函數(shù)公式與函數(shù)IF、ISERROR、IFERROR函數(shù)公式與函數(shù)MID、LEFT、RIGHT函數(shù)FIND函數(shù)NOW、TODAY函數(shù)TIME&TIMEVALUE函數(shù)VLOOKUP函數(shù)HYPERLINK函數(shù)注意事項(一)（1）括號（）要對應(yīng)顯示，不可出現(xiàn)單獨的括號，或少括號（2）輸入法的半角全角、大小寫需注意，如：“”不可輸入成“”*不可輸入成×（3）如條件是尋找某個文字：南京，在函數(shù)中應(yīng)輸入為:“南京”如條件是尋找某個單元格或數(shù)字：A5(A列第5行)或5，在函數(shù)中應(yīng)輸入為：A5或5，無需加""符號（4）函數(shù)中設(shè)置的計算范圍需一致（5）需在函數(shù)公式打開狀態(tài)下,同時按下Ctrl+Shift+Enter,當(dāng)公式頭尾出現(xiàn)花括號｛｝時才算計算完成注意事項(二)（1）源數(shù)據(jù)較大時，應(yīng)將“菜單欄/工具”的重新計算調(diào)整為“手動計算”（2）格式：不得出現(xiàn)空格，如：“黑電”和“黑電”（3）某一字段暫不需/無法統(tǒng)計，如是需量化的單元格，則需以“0”填充，不可寫“無”（4）不可將明細(xì)全部刪除后，在原有模板的基礎(chǔ)上重寫貼入明細(xì)再次使用，將會改變計算范圍，應(yīng)重新使用新模板（5）注意統(tǒng)計周期的輸入（6）注意EXCEL函數(shù)設(shè)置中某條件的計算列是否是需要的計算列（7）如需從一個EXCEL表格的數(shù)據(jù)復(fù)制到另一表格中，應(yīng)點擊“菜單欄/編輯”的鏈接中“斷開鏈接”報表制作2數(shù)據(jù)排序一次排序最多可以選擇三個關(guān)鍵字：主關(guān)鍵字、次關(guān)鍵字、第三關(guān)鍵字?；蛘呃霉ぞ邫谏系膬蓚€排序按鈕。數(shù)據(jù)篩選數(shù)據(jù)透視數(shù)據(jù)透視表實際上是一種交互式表格，能夠方便地對大量數(shù)據(jù)進(jìn)行快速匯總，并建立交叉表，還能根據(jù)用戶的需要顯示區(qū)域中的細(xì)節(jié)數(shù)據(jù)。報表排版報表應(yīng)當(dāng)含有：1、報表名稱（在表格抬頭，通常字號略大些）2、表頭（在表格主體的頂端與側(cè)端，通?？捎妙伾貥?biāo)注）3、各字段數(shù)據(jù)值（如有文字內(nèi)容應(yīng)相對簡略，避免視覺疲勞）報表需要：1、合適的列寬和行高（鼠標(biāo)放置需求列或行線的最末端，雙擊即可）2、當(dāng)表格過長或過寬時，應(yīng)使用“凍結(jié)窗口”功能。圖表制作3圖表制作在處理電子表格時，需要對大量繁瑣的數(shù)據(jù)進(jìn)行分析和研究，我們需要利用圖形方式再現(xiàn)數(shù)據(jù)變動和發(fā)展趨勢。EXCEL提供了強(qiáng)有力的圖表處理功能，使得用戶很快就可得到所要的圖表。我們?nèi)粘９ぷ骱蜕钪薪?jīng)常使用的圖表類型有：1、柱形圖：比較分析2、折線圖：趨勢分析3、餅圖：占比分析著重標(biāo)色以柱形圖為例常用技巧4快捷鍵復(fù)制Ctrl+C粘貼Ctrl+V查找Ctrl+F替換Ctrl+H數(shù)據(jù)全選Ctrl+Shift+End撤銷上一次的操作Ctrl+Z重做上一次的操作Ctrl+Y