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

內(nèi)容一、數(shù)據(jù)錄入和格式化工作表二、公式與函數(shù)的應(yīng)用及案例三、圖表及打印輸出及其它設(shè)置四、數(shù)據(jù)分析及管理一、Excel簡介及基本操作Excel概述

MSExcel是Microsoft公司出品的一個(gè)電子表格軟件，也是目前全球擁有用戶最多的電子表格軟件之一。它具有以下特點(diǎn)：直觀易用性強(qiáng)大的公式處理能力和豐富的函數(shù)運(yùn)算具有解決多種復(fù)雜問題的內(nèi)置模型與微軟其他軟件的無縫協(xié)作性可編程能力與可升級性與Internet配合進(jìn)行協(xié)同工作Excel的主要版本1)Excel6.02）Excel953）Excel974）Excel20005）Excel20036）Excel20077）Excel2010表格標(biāo)題數(shù)據(jù)文字?jǐn)?shù)值自動(dòng)填充公式函數(shù)圖表圖形對象數(shù)據(jù)清單排序篩選分類匯總公式自動(dòng)處理圖表直觀顯示數(shù)據(jù)管理功能Excel的主要功能

Excel的主要基本操作MSExcel主要包含以下基本操作：創(chuàng)建新的工作溥和打開已存在的工作溥選擇工作表及工作表相關(guān)操作在工作表中有效地移動(dòng)在工作表單元中正確地輸入內(nèi)容高效地選擇所需要操作的表格區(qū)域或單元為工作表區(qū)域有單元設(shè)置格式行、列的尺寸調(diào)整,行、列的隱藏與顯示行、列、區(qū)域、單元格的插、刪及移動(dòng)

Excel的基本概念

正確進(jìn)行MSExcel操作必須準(zhǔn)確理解和掌握以下概念：工作溥及工作表工作表的行、列、區(qū)域及單元工作表單元格的內(nèi)容及格式單元格內(nèi)容的表現(xiàn)形式及內(nèi)在實(shí)質(zhì)填充與序列常數(shù)、變量及公式，公式的自動(dòng)運(yùn)算條件及條件處理Excel

的啟動(dòng)和退出啟動(dòng)Excel的方法：

用快捷圖標(biāo)啟動(dòng)：桌面快捷方式圖標(biāo)

雙擊已存在的表格文件（擴(kuò)展名為.xls的文件）啟動(dòng)用“開始”按鈕中的“程序”項(xiàng)啟動(dòng)1234從Excel窗口的“文件”菜單中選擇“退出”命令單擊Excel窗口標(biāo)題欄右側(cè)的關(guān)閉按鈕按Alt+F4鍵退出Excel的方法：

Excel的的對象結(jié)構(gòu)Wordbook(工作薄)Sheet(工作表)Row、Colum(行列)Area(區(qū)域)Cell(表格單元)Excel工作界面工作簿工作簿（Workbook）：是用于存儲(chǔ)并處理數(shù)據(jù)的文件，啟動(dòng)Excel后，系統(tǒng)將自動(dòng)建立建立一個(gè)名為Book1的新工作簿。一個(gè)工作簿是一個(gè)Excel文件，擴(kuò)展名為.XLS或XLSX，工作簿的名字就是文件名.xlsx工作表工作表標(biāo)簽工作表（Worksheet）：由單元格構(gòu)成的表格，是工作簿的重要組成部分，又稱電子（報(bào)）表格。

工作簿包含三個(gè)工作表名稱為：Sheet1，Sheet2，Sheet3

一個(gè)工作簿最多有255張互相獨(dú)立的工作表Sheet。

工作簿是工作表的集合。一個(gè)工作簿可包含多個(gè)工作表。當(dāng)前單元格

單元格：行和列的交點(diǎn)形成的每個(gè)網(wǎng)格稱單元格。是工作表的基本單元。每個(gè)工作表有256列和65536行組成。單元格：是工作表的基本單位。（1）列標(biāo)由A,B,C…字母表示，行標(biāo)由1,2,3…數(shù)字表示（2）每個(gè)工作表只有一個(gè)單元格為當(dāng)前工作單元格,稱為活動(dòng)單元格，當(dāng)選中某一單元格作為活動(dòng)單元格進(jìn)行操作時(shí)，它的四周就會(huì)出現(xiàn)粗黑線框，同時(shí)它的名稱還會(huì)顯示在表格左上方的名稱框中。單元格工作簿工作表工作簿（255）取消按鈕：單擊該按鈕，可取消此次對單元格的輸入或修改操作，相當(dāng)于按Esc鍵。輸入按鈕：單擊該按鈕，可確認(rèn)此次鍵入或修改的內(nèi)容，相當(dāng)于按Enter鍵。插入函數(shù)按鈕：調(diào)出【插入函數(shù)】對話框，以便于在單元格中輸入或修改函數(shù)地址欄/名稱框編輯欄名稱框：顯示活動(dòng)單元格的地址或單元格區(qū)域名稱，也可以給單元格區(qū)域命名。編輯區(qū)：顯示活動(dòng)單元格中的內(nèi)容，向活動(dòng)單元格輸入數(shù)據(jù)、公式或函數(shù)時(shí)，輸入的內(nèi)容會(huì)在單元格和編輯區(qū)中同時(shí)出現(xiàn)。1.Excel文件的建立選擇“文件”|“新建”命令來建立一個(gè)Excel文件。直接點(diǎn)擊常用工具欄中的“新建”按鈕。按Ctrl+N鍵來完成新建操作。2．Excel文件的打開使用“文件”|“打開”命令直接單擊工具欄中的“打開”按鈕。按Ctrl+O鍵3．Excel文件的保存工作薄與工作表的基本操作4.改變默認(rèn)的工作表數(shù)目

“工具”/“選項(xiàng)”命令，彈出“選項(xiàng)”對話框，單擊其中的“常規(guī)”選項(xiàng)卡，如圖所示。微調(diào)框中輸入。5.工作表的選定單擊，選定單個(gè)工作表選擇相鄰多個(gè)工作表：shift（工作組）選擇不相鄰多個(gè)工作表：ctrl選擇全部工作表：右鍵快捷菜單6.工作表的插入、刪除

選定一表，“插入”-“工作表”、“編輯”|“刪除工作表”命令

選定一表，右鍵單擊可見插入、刪除等命令7.工作表復(fù)制、移動(dòng)：同一個(gè)工作簿：拖動(dòng)標(biāo)簽、ctrl不同book之間：如圖,或右鍵“移動(dòng)或復(fù)制工作表”8.工作表的重命名：雙擊要改名的工作表標(biāo)簽，使其反白顯示

，直接輸入新名，然后按<Enter>即可；右鍵快捷菜單“重命名”；菜單“格式”/“工作表”/“重命名”9.切換工作表：出現(xiàn)在工作表中的：直接單擊相應(yīng)的標(biāo)簽未出現(xiàn)在工作表中的：使用滾動(dòng)按鈕切換快捷鍵：Ctrl+PageupCtrl+Pagedown10.工作表的隱藏和恢復(fù)

選定要操作的表，使用“格式”|“工作表”|“隱藏”“取消隱藏”11.改變工作表顯示比例：

“視圖”|“顯示比例”命令（在格式工具欄上有提示操作）當(dāng)一個(gè)工作表數(shù)據(jù)超過一屏幕時(shí)，滾動(dòng)會(huì)使表頭移出當(dāng)前屏幕，而不利于觀看。有時(shí)，我們希望數(shù)據(jù)上下移動(dòng)時(shí)，表頭不動(dòng)，左右移動(dòng)時(shí)，表題列不動(dòng)。拆分屏幕使之成為多個(gè)窗口，目的是使同一工作表中相距較（甚）遠(yuǎn)的數(shù)據(jù)能同時(shí)顯示在同一屏幕上。分割有菜單法和鼠標(biāo)拖動(dòng)法兩種。12.拆分與凍結(jié)工作表(以“銷售統(tǒng)計(jì)表.xls”為例)

拖動(dòng)“水平分割條”可將屏幕分成上下兩個(gè)部分，拖動(dòng)“垂直分割條”可將屏幕分為左右兩個(gè)部分。水平、垂直同時(shí)都分割，最多可以分割成四個(gè)窗口。取消拆分：可通過鼠標(biāo)雙擊拆分線來完成；或?qū)⒉鸱挚蛲献С龉ぷ鲄^(qū)均可取消相應(yīng)的拆分框。保護(hù)和隱藏功能所謂保護(hù)即知道密碼方可修改工作表或工作簿密碼區(qū)分字母的大小寫sheet工作簿保護(hù)工作簿

工作簿的保護(hù)有兩個(gè)方面：第一是保護(hù)工作簿，防止他人非法訪問；第二是禁止他人對工作簿或?qū)ぷ鞑敬翱诘姆欠ú僮?。對工作表的保護(hù)

如果不允許對工作簿中工作表進(jìn)行移動(dòng)、刪除、插入、隱藏、取消隱藏、重命名或禁止對工作簿窗口的移動(dòng)、縮放、隱藏、取消隱藏或關(guān)閉，操作步驟如下：【工具】/保護(hù)/保護(hù)工作表如果選勾復(fù)選框，工作表將不能移動(dòng)、刪除、插入等鍵入密碼，若要取消這種保護(hù)，可以選擇【工具】→保護(hù)→撤消工作簿保護(hù)。保護(hù)單元格①

首先使工作表處于非保護(hù)狀態(tài)（撤消工作表保護(hù)）②

選定需要鎖定或取消鎖定的單元格區(qū)域；③【格式】→單元格/“保護(hù)”標(biāo)簽④

注意：清除“鎖定”復(fù)選框并單擊“確定”按鈕。重新對工作表實(shí)施保護(hù)后，本操作中取消鎖定的單元格就是可以進(jìn)行修改的單元格，其余單元格為保護(hù)單元格。1）隱藏行（列）：法一：“格式”-行-隱藏法二：右鍵快捷菜單-隱藏（4）隱藏行或列①

鼠標(biāo)變?yōu)?形狀時(shí)雙擊②

單擊【格式】→行（或“列”）→取消隱藏。③也可用鼠標(biāo)拖動(dòng)的方式來完成，把鼠標(biāo)指針指向已隱藏行的下一行的行號最上端（或已隱藏列右列的列標(biāo)最左側(cè)），當(dāng)鼠標(biāo)指針出現(xiàn)為=，向下（右）拖動(dòng)鼠標(biāo)即可。2）取消行（列）的隱藏1、Excel2010的數(shù)據(jù)錄入技巧；2、自定義數(shù)字格式；3、Excel2010條件格式設(shè)置；4、數(shù)據(jù)有效性；5、EXCEL多工作表鏈接、凍結(jié)與并排；數(shù)據(jù)錄入和格式化工作表1.1、數(shù)據(jù)錄入的相關(guān)技巧說明性，解釋性的數(shù)據(jù)描述，非數(shù)值類型的。如，員工編號、姓名、出生年月、手機(jī)號碼常常用于各種數(shù)學(xué)計(jì)算的數(shù)據(jù)。如：工資、學(xué)生成績、銷售額等是以“=”號開頭，它可以是簡單的數(shù)學(xué)式，也可以是包含各種Excel函數(shù)的式子數(shù)值文本公式數(shù)據(jù)錄入技巧數(shù)據(jù)錄入技巧錄入分?jǐn)?shù)：零+空格+分?jǐn)?shù)錄入文本格式數(shù)字（郵編、電話等）：單引號+錄入內(nèi)容輸入當(dāng)前日期、時(shí)間：Ctrl+;和Ctrl+Shift+;錄入特殊字符（對號、平方）;填充柄錄入（序列填充）插入批注實(shí)例01數(shù)據(jù)錄入案例分析（1）在數(shù)字后面自動(dòng)添加單位（如：元、千克等）將文本字符括在雙引號("")內(nèi)或在單個(gè)字符前面添加一個(gè)反斜杠(\)（2）輸入身份證的前幾位"51102519"@（3）輸入條形碼前的某些重復(fù)數(shù)字“999999999”@（4）定義小數(shù)點(diǎn)對齊、分?jǐn)?shù)對齊???.???#???/???（5）自定義各種特殊日期格式mmmmd,yyyy(dddd)December30,2008(Tuesday)yyyy“年”m“月”d“日”aaaa2008年12月30日星期四1.2、自動(dòng)限定數(shù)據(jù)格式實(shí)例02自定義數(shù)字格式1.3、Excel2010條件格式設(shè)置什么是條件格式當(dāng)單元格的值滿足預(yù)先設(shè)定的條件時(shí)，自動(dòng)將單元格設(shè)置成預(yù)先設(shè)定的格式多條件格式的規(guī)則按從上到下的順序判斷條件并執(zhí)行最多指定三個(gè)條件根據(jù)公式自動(dòng)設(shè)置格式在條件格式中使用公式和函數(shù)實(shí)現(xiàn)隔行變色等的特殊格式掌握ROW和MOD函數(shù)的用法==mod(row(),2)實(shí)例04條件格式提高數(shù)據(jù)錄入準(zhǔn)確性有效性檢查。什么是數(shù)據(jù)的有效性？數(shù)據(jù)有效性是一個(gè)包含幫助你在工作表中輸入資料提示信息的工具.它有如下功能：(1)給用戶提供一個(gè)選擇列表(2)限定輸入內(nèi)容的類型或大小(3)自定義設(shè)置數(shù)據(jù)有效性的設(shè)置提供下拉式菜單的輸入設(shè)置輸入提示設(shè)置出錯(cuò)警告自定義有效性條件1.4、數(shù)據(jù)的有效性實(shí)例05數(shù)據(jù)有效性1.5、如何處理大型工作表在Excel多個(gè)工作表間快速切換技巧一：用快捷鍵快速導(dǎo)航工作表Ctrl+PageUp 移到上一個(gè)工作表Ctrl+PageDown移到下一個(gè)工作表技巧二：右鍵菜單準(zhǔn)確定位工作表右擊導(dǎo)航狀態(tài)按鈕選擇所需工作表標(biāo)簽名凍結(jié)工作表窗口大型工作表數(shù)據(jù)的分組、分級顯示命名單元格或單元格區(qū)域，以便能夠快速返回到這些工作表區(qū)域組合工作表，以便同時(shí)在多張工作表中輸入共用數(shù)據(jù)實(shí)例06凍結(jié)窗格二、公式與函數(shù)的應(yīng)用及案例公式的基本用法2.1函數(shù)分類與常用函數(shù)應(yīng)用舉例2.2雙擊填充柄復(fù)制公式區(qū)別公式中各種單元格引用方式絕對引用、相對引用、混合引用、三維引用跨工作表或工作簿的數(shù)據(jù)計(jì)算不同工作表間數(shù)據(jù)的計(jì)算、不同工作簿間數(shù)據(jù)的計(jì)算自動(dòng)求和與自動(dòng)計(jì)算功能的使用2.1公式的基本用法實(shí)例08使用相對和絕對引用計(jì)算產(chǎn)品銷售額與利潤實(shí)例9-1跨工作表數(shù)據(jù)計(jì)算實(shí)例9-2跨工作簿數(shù)據(jù)計(jì)算函數(shù)：是預(yù)定義的公式，通過使用一些稱為參數(shù)的特定值來安特定的順序和結(jié)構(gòu)執(zhí)行計(jì)算函數(shù)的使用方式=函數(shù)名稱（參數(shù)1，參數(shù)2，參數(shù)3……)函數(shù)的嵌套特性一個(gè)函數(shù)可以引用其他函數(shù)的結(jié)果作為參數(shù)Excel最多支持七層嵌套函數(shù)2.2函數(shù)實(shí)例10函數(shù)綜合實(shí)例MSExcel中的函數(shù)類型Excel有豐富的內(nèi)部函數(shù)，其類型包括：財(cái)務(wù)函數(shù)日期與時(shí)間函數(shù)數(shù)學(xué)與三角函數(shù)統(tǒng)計(jì)函數(shù)查找與引用函數(shù)數(shù)據(jù)庫函數(shù)文本函數(shù)邏輯函數(shù)信息函數(shù)2.2.1常見函數(shù)簡介IFAND\ORLENMID\LEFT\RIGHT2.2.1邏輯文本類函數(shù)判斷一個(gè)條件是否滿足，如果滿足返回一個(gè)值，如果不滿足則返回另一個(gè)值IF函數(shù)語法：

IF(logical_test,value_if_true,value_if_false)條件為真時(shí)條件為假時(shí)AND\OR函數(shù)AND函數(shù)的作用：是對多個(gè)邏輯值進(jìn)行交集計(jì)算，返回邏輯值

語法：AND(logical1,logical2…)OR函數(shù)的作用：是對多個(gè)邏輯值進(jìn)行并集計(jì)算，返回邏輯值

語法：OR(logical1,logical2,…)MIDB函數(shù)作用自文字指定位置開始向右提取指定長度的字符串，操作對象均為文本，MID以字符為單位，MIDB以字節(jié)為單位截取

語法：MID(text,start_num,num_bytes)LEFT返回文本字符串中的第一個(gè)或前幾個(gè)字符，RIGHT返回文本字符串中的最后一個(gè)或后幾年字符

語法：LEFT(text,num_chars)

語法：RIGHT(text,num_chars)MID(MIDB)

\

LEFT

\

RIGHT函數(shù)DATE：返回代表特定日期的序列號語法：DATE(year,month,day)NOW：返回計(jì)算機(jī)內(nèi)部時(shí)鐘的當(dāng)前日期和時(shí)間語法：NOW()TODAY：返回當(dāng)前日期的序列編號語法：TODAY()注意：序列號是MicrosoftExcel日期和時(shí)間計(jì)算使用的日期-時(shí)間代碼。MicrosoftExcel可將日期存儲(chǔ)為可用于計(jì)算的序列號。默認(rèn)情況下，1900年1月1日的序列號是1，而2008年1月1日的序列號是39448，這是因?yàn)樗?900年1月1日有39448天。2.2.2日期與時(shí)間函數(shù)LOOKUPHLOOKUPVLOOKUP2.2.3查詢和引用類函數(shù)LOOKUP使用LOOKUP函數(shù)是在單行區(qū)域或單列區(qū)域中查找數(shù)值，然后返回第二個(gè)單行區(qū)域或單列區(qū)域中相同位置的數(shù)值

LOOKUP(lookup_value,lookup_vector,result_vector)

說明：lookup_vector為第一個(gè)包含一行或一列的區(qū)域；result_vector為第二個(gè)包含一行或一列的區(qū)域，其大小必須與參數(shù)lookup_vector相同HLOOKUP實(shí)現(xiàn)水平查找使用HLOOKUP函數(shù)可以在表格的首行查找指定的數(shù)值,并由此返回指定行處與該數(shù)值同一列的數(shù)值，當(dāng)比較值位于數(shù)據(jù)表首行時(shí)，可使用此函數(shù)語法：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP_實(shí)現(xiàn)豎直查找在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表首列時(shí),可以使用此函數(shù)語法：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)COUNTCOUNTIFSUMIFINTMOD2.2.4統(tǒng)計(jì)計(jì)數(shù)類函數(shù)

COUNTF函數(shù)COUNT函數(shù)的作用：返回包含數(shù)字以及包含參數(shù)列表中的數(shù)字的單元格的個(gè)數(shù)。利用函數(shù)COUNT可以計(jì)算單元格區(qū)域或數(shù)字?jǐn)?shù)組中數(shù)字字段的輸入項(xiàng)個(gè)數(shù)。語法：COUNT(value1,value2,...)參數(shù)：Value1,value2,...為包含或引用各種類型數(shù)據(jù)的參數(shù)（1到30個(gè)），但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算。說明函數(shù)COUNT在計(jì)數(shù)時(shí)，將把數(shù)字、日期、或以文本代表的數(shù)字計(jì)算在內(nèi)；但是錯(cuò)誤值或其他無法轉(zhuǎn)換成數(shù)字的文字將被忽略。如果參數(shù)是一個(gè)數(shù)組或引用，那么只統(tǒng)計(jì)數(shù)組或引用中的數(shù)字；數(shù)組或引用中的空白單元格、邏輯值、文字或錯(cuò)誤值都將被忽略。COUNTIF函數(shù)可累計(jì)滿足某一條件的對象在單元格區(qū)域中出現(xiàn)的次數(shù)，該條件可以是數(shù)值、文本或表達(dá)式，在人事、工資和成績統(tǒng)計(jì)中有廣泛用途。

COUNTIF函數(shù)語法：

COUNTIF（range,criteria)SUMIF函數(shù)可對滿足某一條件的單元格區(qū)域求和，該條件可以是數(shù)值、文本或表達(dá)式。SUMIF函數(shù)語法：

SUMIF（range,criteria,sum_range)INT

/MODINT函數(shù)的作用：將數(shù)字向下舍入到最接近的整數(shù)。語法：INT(number)參數(shù)：number是需要進(jìn)行向下舍入取整的實(shí)數(shù)。MOD取余函數(shù)

返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號與除數(shù)相同。語法MOD(number,divisor)；Number

為被除數(shù)；Divisor

為除數(shù)。注解如果divisor為零，函數(shù)MOD返回錯(cuò)誤值#DIV/0!。函數(shù)MOD可以借用函數(shù)INT來表示:MOD(n,d)=n-d*INT(n/d)INT取整函數(shù)三、圖表及打印輸出及其它設(shè)置創(chuàng)建Excel圖表3.1編輯Excel圖表3.2柱形圖：用來顯示一段時(shí)間內(nèi)數(shù)據(jù)的變化或者描述各個(gè)項(xiàng)目之間數(shù)據(jù)比較圖表線性圖：主要用于以等時(shí)間間隔顯示數(shù)據(jù)的變化趨勢，強(qiáng)調(diào)的是時(shí)間性和變動(dòng)率，而不是變動(dòng)量餅形圖：顯示數(shù)據(jù)系列中的項(xiàng)目和該項(xiàng)目數(shù)值總和的比例關(guān)系。它只能顯示一個(gè)系列的數(shù)據(jù)比例關(guān)系圖表在數(shù)據(jù)分析中的作用確定分析