計算機應(yīng)用技能基礎(chǔ)第5章Excel基礎(chǔ)及高級應(yīng)用

第5章Excel2010基礎(chǔ)及高級應(yīng)用

SchoolofComputerScience知識要點：基本概念：工作簿、工作表和單元格的概念。工作簿的操作：工作簿的新建、保存、打開等。工作表的操作：工作表的選擇、復制、移動、插入、刪除、重命名等。單元格的操作：單元格的選擇、合并、拆分、插入、刪除、單元格的引用、行列的插入與刪除、行高列寬的調(diào)整、數(shù)據(jù)的輸入與修改、自動填充與序列填充等。公式和常用函數(shù)的使用：公式的輸入和編輯、常用函數(shù)（Sum、Average、Count、Countif、Sumif、If、Max、Min、Rank、Round、Int、Mod、Mid）等的使用。數(shù)據(jù)分析及管理：數(shù)據(jù)的排序、篩選、分類匯總、數(shù)據(jù)透視表等。圖表操作：建立圖表、編輯圖表、復合圖表制作、雙層餅圖制作等。SchoolofComputerScience目錄5.1Excel基本操作5.2Excel公式和函數(shù)5.3Excel數(shù)據(jù)管理及分析5.4圖表5.5Excel高級應(yīng)用5.6Excel文檔的打印SchoolofComputerScience5.1Excel基本操作5.1.1Excel2010的啟動和退出1．啟動啟動Excel2010有多種方法，常用如下4種方法：①

單擊【開始】|【程序】|【MicrosoftOffice】|【MicrosoftExcel2010】命令。②

雙擊桌面上的Excel快捷方式圖標

。③

直接在【資源管理器】或【計算機】中雙擊指定的Excel文件名。④

單擊【開始】|【運行】，輸入“excel.exe”可執(zhí)行文件。SchoolofComputerScience5.1Excel基本操作2．退出退出Excel2010常用如下幾種方法：單擊Excel窗口右上角的【關(guān)閉】按鈕。單擊【文件】|【退出】命令。雙擊Excel窗口左上角的【系統(tǒng)控制】菜單按鈕。直接按組合鍵ALT+F4。SchoolofComputerScience5.1Excel基本操作5.1.2Excel2010的主窗口SchoolofComputerScience5.1Excel基本操作5.1.3Excel基本概念1．工作簿

工作簿是計算和儲存數(shù)據(jù)的文件。一個工作簿就是一個Excel文件，其擴展名為“.xlsx”。Excel啟動后，自動打開一個被命名為“工作簿1”的工作簿。一個工作簿由若干個工作表組成（默認為3個）。2．工作表

工作表用于組織和分析數(shù)據(jù)，Excel的工作表由1048576行、16384列組成。一個工作簿可以包含多個工作表（其個數(shù)原則上受限于內(nèi)存），這樣可使一個文件中包含多種類型的相關(guān)信息，操作時不必打開多個文件，而直接在同一個文件的不同工作表中方便地切換。默認情況下，Excel的一個工作簿中有3個工作表，名稱分別為Sheet1、Sheet2、Sheet3，當前工作表為Sheet1，用戶根據(jù)實際情況可以增減工作表和選擇工作表。SchoolofComputerScience5.1Excel基本操作3．單元格

單元格是組成工作表的最小單位。一張工作表由1048576×16384個單元格組成，每一行列交叉處即為一單元格。每個單元格用它所在的列名和行名來引用，列名用字母及字母組合A~Z，AA~AZ，BA~BZ，……，ZA~ZZ，AAA~AAZ，……，XEA~XEZ，XFA~XFD表示，行名用自然數(shù)1~1048576表示。如：A6、D20、IV500、ABC12345等。

如果在不同工作表中引用單元格，為了加以區(qū)分，通常在單元格名稱前加工作表名稱，例如Sheet2!D3表示Sheet2工作表的D3單元格。如果在不同的工作簿之間引用單元格，則在單元格名稱前加相應(yīng)的工作簿和工作表名稱，例如[工作簿1]Sheet1!B5表示工作簿1Sheet1工作表中的B5單元格。SchoolofComputerScience5.1Excel基本操作工作簿、工作表、單元格的關(guān)系，可用下圖表示。SchoolofComputerScience5.1Excel基本操作5.1.4Excel文件創(chuàng)建與保存1．創(chuàng)建工作簿

啟動Excel之后，系統(tǒng)自動創(chuàng)建一個空白的工作簿，默認的文件名是工作簿1。如果要重新建立一個文件，可以單擊【文件】|【新建】命令，在右側(cè)選擇“空白工作簿”后點擊界面右下角的“創(chuàng)建”圖標就可以新建一個空白的工作簿。如果選擇右側(cè)的表格模板，點擊“下載”圖標后就可以根據(jù)模板創(chuàng)建表格。SchoolofComputerScience5.1Excel基本操作2．保存工作簿

在Excel工作表中將數(shù)據(jù)編輯完畢后就可以保存了。單擊【文件】|【保存】命令，如果是第一次保存文件，將打開【另存為】對話框，用戶可以在對話框中選擇保存路徑、輸入文件名、選擇文件保存類型。Excel默認的保存類型是“Excel工作簿（*.xlsx）”。如果保存類型選擇“Excel97-2003工作簿（*.xls）”，可以保存為早期版本的工作簿。SchoolofComputerScience5.1Excel基本操作5.1.5工作表中輸入數(shù)據(jù)在Excel單元格中輸入數(shù)據(jù)通常有3種方法。①單擊選中的單元格，鼠標形狀變?yōu)?/p>

時，在該單元格中輸入數(shù)據(jù)，然后按Enter鍵、Tab鍵或選擇“↑”“↓”“←”“→”方向鍵定位到其他單元格繼續(xù)輸入數(shù)據(jù)。②雙擊選中的單元格，鼠標形狀變?yōu)镮型時，就可以進行數(shù)據(jù)的輸入了。③單擊選中的單元格，在編輯欄內(nèi)輸入數(shù)據(jù)，最后用鼠標單擊控制按鈕

或

，“取消”或“確定”輸入的內(nèi)容。如果輸入有錯，按Backspace鍵刪除，再重新輸入。SchoolofComputerScience5.1Excel基本操作1．單元格的選定在Excel中，要輸入數(shù)據(jù)或?qū)?shù)據(jù)進行操作，必須先選定該單元格或單元格區(qū)域。（1）選定單個單元格單擊某個單元格就可選定它，并且對應(yīng)的單元格名稱會出現(xiàn)在名稱框中。（2）選定單元格區(qū)域區(qū)域是一組單元格，若想選定工作表中的單元格區(qū)域，除了使用鼠標拖動以外，還可以用鼠標單擊所選區(qū)域的左上角單元格，然后按住Shift鍵，再用鼠標單擊該區(qū)域?qū)蔷€右下角的單元格后松開鼠標。

（3）選定離散的單元格選定第一個單元格后，按住Ctrl鍵，再用鼠標單擊所要選定的單元格，即可選定多個離散的單元格。（4）選定整行或整列單擊某行或某列所在的行號或列號即可完成。（5）選定工作表單擊工作表左上角的【全選】按鈕，可以選中整個工作表。SchoolofComputerScience5.1Excel基本操作2．輸入常量所謂常量是指數(shù)值保持不變的量。常量有3種基本類型：文本、數(shù)值和日期時間。（1）輸入文本常量文本常量可以包含漢字、大小寫英文字母、數(shù)字、特殊字符等。文本常量默認的對齊方式是左對齊。有些數(shù)字如學號、電話號碼、郵編、身份證號碼等常作文本處理，此時只需在輸入數(shù)字前加上一個英文狀態(tài)下的單引號（'），即當作文本左對齊。例如輸入電話號碼“'1580833****”。當輸入的文字長度超出單元格寬度時，如右邊單元格無內(nèi)容，則擴展到右邊列；否則將截斷顯示。SchoolofComputerScience5.1Excel基本操作（2）輸入數(shù)值常量數(shù)值常量包含0~9、＋、－、E、e、$、/（分號）、%以及小數(shù)點和千分位符號（,）等特殊字符。如：$50,000。數(shù)值常量默認的對齊方式是右對齊。當輸入數(shù)據(jù)太長時，Excel自動以科學計數(shù)法表示，如：3.45E+12；當單元格容納不下一個格式化的數(shù)字時，就用若干個“#”代替?？梢酝ㄟ^調(diào)整單元格的列寬使其正常顯示。Excel的數(shù)字精度為15位，當長度超過15位時，Excel會將多余的數(shù)字轉(zhuǎn)換為0，如輸入：1234512345123456時，在計算中以1234512345123450參加計算。如果要輸入正數(shù)，則直接輸入數(shù)字即可。如果要輸入負數(shù)，必須在數(shù)字前加一個負號“-”或者給數(shù)字加一個圓括號。例如，輸入“-1”或者“(1)”都會得到-1。如果要輸入百分數(shù)，可直接在數(shù)字后面加上百分號％。例如，要輸入50％，則在單元格中先輸入50，再輸入％。如果要輸入小數(shù)，直接輸入小數(shù)點即可。如：3.5。如果要輸入分數(shù)，如“2/3”，必須在單元格內(nèi)輸入“02/3”，即前面需要加“0”和空格，否則，Excel會將用戶輸入的“2/3”自動轉(zhuǎn)換成日期“2月3日”。SchoolofComputerScience5.1Excel基本操作（3）輸入日期時間Excel內(nèi)置了一些日期時間的格式，當輸入數(shù)據(jù)與這些相匹配時，Excel將自動識別它們。其常見日期時間格式為“yy/mm/dd”、“yy-mm-dd”、“hh:mm(AM/PM)”。日期時間默認的對齊方式是右對齊。輸入日期時，可用斜杠（/）或減號（-）分割日期的年、月、日。輸入時間時，按××時:××分:××秒格式。若按12小時制輸入時間，則應(yīng)在時間數(shù)字的末尾空一格，隨后鍵入字母am或pm，比如輸入7:20am，顯示為07:20AM，缺少空格將被當作文本數(shù)據(jù)處理。同時輸入日期和時間，在中間用空格分隔。如輸入2013年4月20日下午4:30，則可輸入：2013-4-2016:30或2013-4-204:30pm（但時間均顯示為24小時制）。若要輸入當天的日期，可按快捷鍵Ctrl+；（分號）。若要輸入當前時間，可按快捷鍵Ctrl+Shift+：（冒號）或Ctrl+Shift+；（分號）。SchoolofComputerScience5.1Excel基本操作下圖顯示了不同類型的常量輸入格式。SchoolofComputerScience5.1Excel基本操作3．一些輸入技巧（1）換行輸入若要在一個單元格中換行輸入數(shù)據(jù)，可以通過以下兩種方法實現(xiàn)。單擊【開始】選項卡功能區(qū)“對齊方式”分組中的“自動換行”按鈕。當鼠標變成I型時，按住Alt＋Enter組合鍵。（2）輸入相同數(shù)據(jù)要在不同單元格中輸入相同數(shù)據(jù)，可以先選中要輸入相同數(shù)據(jù)的單元格，然后在編輯欄中鍵入數(shù)據(jù)內(nèi)容，最后按Ctrl＋Enter鍵。（3）填充輸入使用填充柄自定義序列SchoolofComputerScience5.1Excel基本操作5.1.6工作表的修改與編輯1.插入和刪除單元格2.復制和移動單元格3.選擇性粘貼4.插入/刪除行和列SchoolofComputerScience5.1Excel基本操作5.1.7工作表的格式化1.調(diào)整列寬和行高2.格式化單元格（1）合并居中（2）數(shù)字格式（3）對齊方式（4）字體與顏色（5）邊框和填充3．條件格式化4．套用表格格式

舉例：以“學生成績表”為例，格式化工作表SchoolofComputerScience5.1Excel基本操作5.1.8工作表的管理1．選定工作表2．插入或刪除工作表3．移動或復制工作表4．重命名工作表5．設(shè)置工作表標簽顏色6．拆分工作表7．凍結(jié)和取消凍結(jié)窗口SchoolofComputerScience5.2Excel公式和函數(shù)在Excel中，可以利用公式和函數(shù)對數(shù)據(jù)進行分析和計算。函數(shù)是預定義的公式，Excel提供了財務(wù)、邏輯、文本、日期和時間、查找與引用、數(shù)學和三角函數(shù)、統(tǒng)計等多種函數(shù)供用戶使用。1．公式的組成Excel的公式以“=”開頭，“=”后面可以包括多種元素：運算符、單元格引用、數(shù)值、文本和函數(shù)等。Excel的運算符包括算術(shù)運算符、關(guān)系運算符、連接運算符和引用運算符。SchoolofComputerScience運算符

內(nèi)容算術(shù)運算符“%”(百分比)、“+”(加)、“－”(減)、“*”(乘)、“/”(除)、“^”(乘方)關(guān)系運算符“＝”(等于)、“<”(小于)、“>”(大于)、“<=”(小于等于)、“>=”(大于等于)、“<>”(不等于)連接運算符

“&”(文本連接)引用運算符

“,”(逗號)、“:”(冒號)5.2Excel公式和函數(shù)2．公式的輸入Excel的公式以“=”開頭，公式中所有的符號都是英文半角的符號。通過以下步驟創(chuàng)建公式：①選中輸入公式的單元格；②輸入等號“=”；③在單元格或者編輯欄中輸入公式具體內(nèi)容；④按Enter鍵，完成公式的創(chuàng)建。SchoolofComputerScience5.2Excel公式和函數(shù)3．公式的復制和自動填充（1）相對引用地址（2）絕對引用地址（3）混合引用地址三種引用輸入時可互相轉(zhuǎn)換：在公式中用鼠標或鍵盤選定引用單元格的部分，反復按F4鍵可進行引用間的轉(zhuǎn)換。轉(zhuǎn)換的規(guī)律如下：A1－$A$1－A$1－$A1－A1。SchoolofComputerScience5.2Excel公式和函數(shù)5.2.2Excel函數(shù)1.函數(shù)的格式函數(shù)的基本格式為：函數(shù)名（參數(shù)1，參數(shù)2，……）。2.函數(shù)的輸入有三種方法：插入函數(shù)；直接輸入法；使用“自動求和”。3.常用函數(shù)（見書P77頁表1-5-2）4.常用函數(shù)應(yīng)用實例（見書P178頁例9）SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析Excel具有強大的數(shù)據(jù)庫管理功能，可以方便地組織、管理和分析大量的數(shù)據(jù)信息。在Excel中，工作表內(nèi)一塊連續(xù)不間斷的數(shù)據(jù)就是一個數(shù)據(jù)庫，可以對數(shù)據(jù)庫的數(shù)據(jù)進行排序、篩選、分類匯總、合并計算等操作。5.3.1數(shù)據(jù)清單5.3.2排序1．快速排序

如果僅僅需要對數(shù)據(jù)清單中的某列數(shù)據(jù)進行排序時，只需要單擊此列中的任一單元格，再單擊【開始】選項卡“編輯”分組中的

按鈕下的升序

或降序

按鈕即可（也可從【數(shù)據(jù)】選項卡“排序和篩選”分組中找到相應(yīng)升序和降序按鈕進行排序）。SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析2．多重排序

在排序時，可以指定多個排序條件，即多個排序的關(guān)鍵字。首先按照“主要關(guān)鍵字”排序；對主要關(guān)鍵字相同的記錄，再按照“次要關(guān)鍵字”排序；對主要關(guān)鍵字和次要關(guān)鍵字相同的記錄，還可以按照第三關(guān)鍵字排序。舉例說明：P180頁例10SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析5.3.3篩選

數(shù)據(jù)篩選可以實現(xiàn)從數(shù)據(jù)清單中提煉出滿足某種條件的數(shù)據(jù)，不滿足條件的數(shù)據(jù)只是被暫時隱藏起來，并未真正被刪除；一旦篩選條件被取消，這些數(shù)據(jù)又重新出現(xiàn)。Excel提供了兩種條件篩選命令。1.自動篩選

按照選定內(nèi)容自定義篩選，它適合簡單條件的篩選。2.高級篩選

自動篩選一次只能對一個字段進行篩選，如果對多個字段進行篩選，則可用高級篩選來實現(xiàn)。高級篩選適合復雜條件篩選。

舉例說明：P182頁例11SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析5.3.4分類匯總

所謂分類匯總就是首先將數(shù)據(jù)分類，然后將數(shù)據(jù)按照類進行匯總分析處理。分類的方法是對該分類字段進行排序，匯總時使用“分類匯總”對話框。分類匯總可以使數(shù)據(jù)清單中大量數(shù)據(jù)更明確化和條理化。1.簡單分類匯總

簡單分類匯總，是只對一個字段分類、且只采用一種匯總方式的分類匯總。2.多重分類匯總

多重分類匯總是指有多個分類字段的情況，或有多個匯總方式或多個匯總項，有時是多種情況的組合。遇到對多個字段排序時，要使用“排序”對話框。遇到統(tǒng)計多種匯總方式時，因為一次只能統(tǒng)計一個匯總方式，所以要分多次匯總操作才能完成。

舉例說明：P185頁例13SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析5.3.5合并計算

在日常工作中，經(jīng)常要將一些分散的數(shù)據(jù)整理成一份完整的表格，這是一個將多個數(shù)據(jù)庫合并成一個數(shù)據(jù)庫、同時對其中的數(shù)據(jù)進行統(tǒng)計的操作。

舉例說明：P86頁例14SchoolofComputerScience5.3Excel數(shù)據(jù)管理及分析5.3.6數(shù)據(jù)透視表

數(shù)據(jù)透視表是包含數(shù)據(jù)匯總的交互式表格。使用數(shù)據(jù)透視表功能可以按用戶的要求匯總數(shù)據(jù)、重新組建數(shù)據(jù)，還可以創(chuàng)建數(shù)據(jù)透視圖等。SchoolofComputerScience5.4圖表5.4.1圖表概述

Excel提供的圖表功能是用圖形的方式來表現(xiàn)工作表中數(shù)據(jù)與數(shù)據(jù)之間的關(guān)系，從而使數(shù)據(jù)分析更加直觀、形象，讓用戶更好的了解數(shù)據(jù)的關(guān)系以及發(fā)展趨勢等。Excel中的圖表有柱形圖、折線圖、餅圖、條形圖、面積圖、散點圖等等。SchoolofComputerScience5.4圖表5.4.2建立圖表Excel中圖表的建立，可以選擇【插入】選項卡“圖表”分組中相應(yīng)圖表按鈕進行創(chuàng)建。SchoolofComputerScience5.4圖表5.4.3編輯與格式化圖表

當圖表創(chuàng)建完成后，Excel會自動加載選項卡“圖表工具”，其中包含“設(shè)計”、“布局”、“格式”三個選項?？梢岳谩皥D表工具”對圖表中各個內(nèi)容進行修改編輯與格式化處理。SchoolofComputerScience5.4圖表SchoolofComputerScience圖表編輯修改效果（一）5.4圖表SchoolofComputerScience圖表編輯修改效果（二）5.4圖表SchoolofComputerScience圖表編輯格式化效果（三）5.5Excel高級應(yīng)用5.5.1Excel函數(shù)綜合應(yīng)用Excel函數(shù)非常豐富，可以各類函數(shù)結(jié)合應(yīng)用，解決一些較復雜的問題。

【例20】如何利用身份證號碼計算出生日期、年齡、性別？SchoolofComputerScience5.5Excel高級應(yīng)用5.5.2Excel單元格下拉列表設(shè)置方法

利用Excel數(shù)據(jù)有效性，可以方便設(shè)置單元格下拉列表，這里介紹三種設(shè)置方法。1.直接輸入方法①選擇要設(shè)置的單元格，比如A1單元格；②單擊【數(shù)據(jù)】選項卡“數(shù)據(jù)工具”分組中的數(shù)據(jù)有效性按鈕，彈出“數(shù)據(jù)有效性”對話框；③在“設(shè)置”選項中→“有效性條件”→“允許”中選擇“序列”→右邊的“忽略空值”和“提供下拉箭頭”全部打勾→在“來源”下面輸入數(shù)據(jù)，比如“1,2,3,4,5,6,7,8,9”(不包括雙引號，分隔符號“,”必須為半角模式)→按“確定”就完成了，再次選擇該A1單元格，就出現(xiàn)了下拉列表。SchoolofComputerScience5.5Excel高級應(yīng)用5.5.2Excel單元格下拉列表設(shè)置方法

2.引用同一工作表內(nèi)的數(shù)據(jù)

如果同一工作表的某列就是下拉列表想要的數(shù)據(jù)，比如引用工作表Sheet1的B2:B5，B2：B5分別有數(shù)據(jù)1、2、3、4，操作方法與第1種方法相似，不同之處僅在第③步輸入數(shù)據(jù)不相同，這里需要輸入數(shù)據(jù)“=$B$2:$B$5”，也可以按右邊