信息技術(shù)基礎(chǔ)課件：電子表格軟件Excel 2016

5.1概述5.2數(shù)據(jù)錄入5.3數(shù)據(jù)挖掘5.4數(shù)據(jù)展示5.1概述5.1.1Excel2016的啟動Excel2016的啟動有3種方式：(1)在開始菜單程序里面運(yùn)行Excel2016。(2)雙擊桌面上已有的“MicrosoftOfficeExcel2016”快捷方式，啟動Excel2016。(3)雙擊已有的Excel工作簿文件(擴(kuò)展名為.xlsx)，啟動Excel2016。啟動Excel2016后，Excel2016的用戶界面如圖5.1所示。Excel2016用戶界面中包含如下幾個部分。(1)快速訪問工具欄，存放方便快速訪問和頻繁使用的工具，用戶可以根據(jù)需求自行添加。(2)標(biāo)題欄。Excel2016進(jìn)行操作的工作簿的名稱。(3)功能區(qū)。Excel的基本功能都可以在功能區(qū)中通過不同類型的選項(xiàng)卡來實(shí)現(xiàn)，這些選項(xiàng)卡包括“開始”“插入”“頁面布局”“公式”“數(shù)據(jù)”“審閱”等。(4)名稱框。名稱框位于數(shù)據(jù)區(qū)的左上方，顯示當(dāng)前活動單元格的名稱。(5)編輯欄。編輯欄位于數(shù)據(jù)區(qū)的右上方，用來輸入、編輯單元格的數(shù)據(jù)或者公式，顯示當(dāng)前活動單元格內(nèi)的數(shù)值。(6)活動單元格。數(shù)據(jù)區(qū)中每一行和每一列的邊界交叉圍成的長方形區(qū)域稱為單元格，它們是Excel中最基本的操作對象。(7)列號。數(shù)據(jù)區(qū)中每一列上方的大寫英文字母表示列號，即每一列的列名，對應(yīng)稱為A列、B列、C列……(8)行號。數(shù)據(jù)區(qū)每一行左側(cè)的阿拉伯?dāng)?shù)字表示行號，即每一行的編號，對應(yīng)稱為第1行、第2行、第3行……(9)數(shù)據(jù)區(qū)。由若干個單元格、行號、列號組成，用于存放數(shù)據(jù)、處理數(shù)據(jù)、制作表格，及以表格的形式存放各類數(shù)據(jù)信息。(10)工作表標(biāo)簽。Excel2016以前的版本中，一個工作簿默認(rèn)包含3個空白的工作表，并分別命名為Sheet1、Sheet2、sheet3。Excel2016則默認(rèn)為一個空白的工作表Sheet1。工作表的名稱顯示在數(shù)據(jù)區(qū)下方的工作表標(biāo)簽中。當(dāng)前顯示的工作表稱為活動工作表，單擊工作標(biāo)簽右方的“＋”，可以插入新的空白工作表，使用鼠標(biāo)單擊不同的標(biāo)簽項(xiàng)，可以在不同的工作表之間進(jìn)行切換。5.1.2Excel2016的退出Excel2016的退出的方式也有3種：(1)單擊“文件”按鈕，選擇“關(guān)閉”。(2)使用快捷鍵“Alt＋F4”，關(guān)閉窗口。(3)在標(biāo)題欄最右側(cè)單擊“×”按鈕，關(guān)閉Excel2016窗口。5.2數(shù)據(jù)錄入5.2.1新建文件1.新建工作簿新建工作簿的方法有以下3種。(1)啟動Excel2016時，系統(tǒng)自動生成一個新的工作簿，名為“工作簿1”。(2)單擊“文件”按鈕，在打開的選項(xiàng)列表中選擇“新建”，選擇新建一個“空白工作簿”或已有的模板創(chuàng)建新的工作簿，如圖5.2所示。(3)在特定文件夾中新建工作表，在文件夾中單擊鼠標(biāo)右鍵，選擇“新建XLSX工作表”，如圖5.3所示。2.保存工作簿保存工作簿的方法有以下3種。(1)單擊“文件”按鈕，在選項(xiàng)列表中選擇“保存”按鈕如圖5.4所示。(2)使用快捷鍵“Ctrl＋S”快捷保存工作簿。(3)以另一個工作簿的方式保存，單擊“文件”按鈕，在選項(xiàng)列表中選擇“另存為”按鈕，進(jìn)一步選擇存儲位置、存儲名稱另存為新的工作簿，如圖5.5所示。3.工作簿的保護(hù)在Excel中，對工作表進(jìn)行數(shù)據(jù)操作、分析、處理后，可以對工作表或者工作簿進(jìn)行保護(hù)。保護(hù)工作簿的方法有兩種：(1)通過“審閱”選項(xiàng)卡，在“更改”組中進(jìn)行工作簿保護(hù)選項(xiàng)的選擇，如圖5.6所示。(2)通過“文件”選項(xiàng)卡，在“信息”選項(xiàng)中的右側(cè)選擇“保護(hù)工作簿”按鈕，進(jìn)行保護(hù)方式的選擇，如圖5.7所示。4.工作表的插入、刪除和移動復(fù)制在工作表標(biāo)簽右方單擊“＋”，可以插入新的空白工作表，如圖5.8所示。在需要刪除的工作表名稱上單擊鼠標(biāo)右鍵，可刪除工作表。5.工作表重命名如需修改工作表的名稱，可以通過如下兩種方法：(1)雙擊工作表標(biāo)簽名，標(biāo)簽名由白色變?yōu)榛疑x中狀態(tài)后，直接輸入需要更改的名稱即可。(2)在工作表標(biāo)簽處單擊鼠標(biāo)右鍵，選擇“重命名”進(jìn)行更改。5.2.2輸入數(shù)據(jù)1.數(shù)據(jù)錄入在Excel2016工作表中有多種輸入數(shù)據(jù)的方式，最簡單的是選定單元格輸入后，單擊“Enter”鍵完成。Excel2016可以輸入的數(shù)據(jù)類型有多種，包括數(shù)字型、日期型、文本型、時間型等。如果要輸入的數(shù)據(jù)是序列數(shù)據(jù)，還可以通過序列數(shù)據(jù)自動填充的方式完成數(shù)據(jù)的輸入。2.數(shù)據(jù)填充Excel2016通過選定相應(yīng)的單元格并拖動填充柄，可以快速填充多種類型的數(shù)據(jù)序列。另外，基于在第一個單元格所建立的格式，Excel可以自動延續(xù)一系列數(shù)字、數(shù)字/文本組合、日期或時間段。(1)簡單數(shù)據(jù)填充。簡單數(shù)據(jù)填充是指通過選擇數(shù)據(jù)然后拖動填充柄填充，但選中一個數(shù)據(jù)和選擇兩個數(shù)據(jù)填充得到的效果是不同的，如圖5.11所示。(2)系統(tǒng)序列填充。以等比序列為例，如圖5.12所示。在起始單元格E1輸入1，然后選定區(qū)域E1∶E15，在“開始”菜單下的“編輯”組中選擇“填充”按鈕，在列表中選擇“序列”選項(xiàng)打開“序列”對話框，如圖5.13所示，產(chǎn)生步長值為3的等比序列。(3)自定義序列填充。Excel除了系統(tǒng)本身的序列外，還可以根據(jù)用戶的需求，把經(jīng)常要用到的序列定義為自己的數(shù)據(jù)序列。單擊“文件”按鈕選擇“選項(xiàng)”，在選項(xiàng)窗口中選擇“高級”，之后在“常規(guī)”選項(xiàng)中選擇“編輯自定義列表”按鈕打開“自定義序列”對話框，如圖5.14所示。(4)自定義數(shù)字格式。當(dāng)需要處理一些有特定格式的數(shù)字或符號，如準(zhǔn)考證號、學(xué)號、身份證號、銀行卡號等時，可使用自定義數(shù)字格式。3.數(shù)據(jù)編輯(1)復(fù)制數(shù)據(jù)。選中需要復(fù)制數(shù)據(jù)的單元格，第一種方法是在“開始”功能選項(xiàng)卡“剪貼板”組中選擇“復(fù)制”；第二種方法是在選擇的單元格上單擊鼠標(biāo)右鍵復(fù)制。(2)粘貼數(shù)據(jù)。第一種方法是在“開始”功能選項(xiàng)卡“剪貼板”組中選擇“粘貼”。第二種方法是選擇性粘貼。將C1∶C6數(shù)據(jù)復(fù)制到G1∶G6中，選中C1∶C6復(fù)制，再選中G1∶G6，單擊鼠標(biāo)右鍵，出現(xiàn)“選擇性粘貼”面板，選中“加”法運(yùn)算，如圖5.16所示。(3)清除數(shù)據(jù)。單擊“開始”功能選項(xiàng)卡，在“編輯”組的“清除”下拉單中選擇需要清除的項(xiàng)目。5.2.3格式設(shè)計(jì)1.單元格格式設(shè)計(jì)1)選取單元格選取單個單元格的方式有如下3種。(1)單個單元格選取，鼠標(biāo)直接單擊要選擇的單元格。(2)在名稱框中輸入要選擇的單元格地址。(3)在“開始”功能選項(xiàng)卡的“編輯”組中找到“查找和選擇”下拉單中的“轉(zhuǎn)到”，打開“定位”對話框，在“引用位置”中輸入要選擇的單元格地址，單擊“確定”按鈕，如圖5.17所示。2)選取行、列和工作表(1)選定單行(列)，在工作表上直接單擊該行(列)的行(列)號即可。(2)選定連續(xù)行(列)區(qū)域，在選取的第一行(列)的行(列)號處單擊，然后按住“Shift”鍵單擊選取的最后一行(列)的行號(列號)。(3)選定不連續(xù)的行、列區(qū)域，按住“Ctrl”鍵，隨后單擊需要的行(列)號。(4)選定整個工作表有兩種方式：一種是單擊工作表左上角行號與列號相交處的按鈕；另一種是使用快捷鍵“Ctrl＋A”。3)單元格、行和列的插入選擇操作對象后，可以使用不同的方式在選擇對象的位置插入新的單元格、行或列，如在工作表中選擇單元格A1或者第一行，單擊鼠標(biāo)右鍵，在彈出的右鍵菜單中選擇“插入”命令，打開“插入”對話框，在其中選擇插入對象的類型(單元格、行或列)。這里單擊選中“整行”，單擊“確定”按鈕即可在A1單元格的上方插入一空白行，原先工作表中的所有內(nèi)容將自動下移一行，如圖5.18所示。4)單元格、行和列的刪除選擇操作對象后，可以使用不同的方式在選擇對象的位置刪除單元格、行或列，如在工作表中選擇單元格H3，單擊鼠標(biāo)右鍵，在彈出的右鍵菜單中選擇“刪除”命令，打開“刪除”對話框，在其中選擇插入對象的類型(單元格、行或列)，這里單擊選中“整行”，單擊“確定”按鈕即可刪除H3單元格所在的行，其所在的行下方的所有內(nèi)容將自動上移一行，如圖5.19所示。5)單元格、行和列的隱藏為了方便數(shù)據(jù)的閱讀，有時需要將工作表中的某一行或某一列隱藏起來不顯示，這里以隱藏C列為例介紹隱藏操作。在列標(biāo)簽C單元格單擊鼠標(biāo)右鍵，選擇“隱藏”。如需要取消隱藏并重新顯示C列的內(nèi)容，則可選擇整個工作表區(qū)域(組合鍵Ctrl＋A)，然后在C列處單擊鼠標(biāo)右鍵，在下拉列表中選擇“取消隱藏”命令。6)單元格的合并居中為了突出一些重要的內(nèi)容(如標(biāo)題)，必要時可以將多個單元格合并到一起進(jìn)行顯示。如圖5.22所示，選擇A1∶H1，在“開始”菜單欄“對齊方式”選項(xiàng)卡中選擇“合并居中”選項(xiàng)，單擊“合并后居中”進(jìn)行合并和居中。7)設(shè)置單元格格式為了使表格美觀、明了，必要時可以對單元格進(jìn)行格式設(shè)置。為使表格達(dá)到如圖5.23所示的效果，操作步驟如下。(1)標(biāo)題“六(五)班成績表”的字體設(shè)為“華文新魏”，字號為“24”，顏色為“深藍(lán)”。(2)行名的字體設(shè)為“華文琥珀”，字號為“12”，顏色為“紅色”。(3)其他區(qū)域的字體設(shè)為“黑體”，字號為“14”，顏色為“橙色”。(4)對整個表格進(jìn)行對齊操作，選擇需要調(diào)整的整個表格，單擊“開始”菜單下的“對齊方式”選項(xiàng)組右下角的單元格格式按鈕，選擇居中對齊，如圖5.24所示。2.頁面格式設(shè)置1)頁面設(shè)置單擊“頁面布局”選項(xiàng)卡中“頁面設(shè)置”組中的右下方按鈕，打開“頁面設(shè)置”對話框，如圖5.25所示。在對話框中可以對頁面的頁邊距、紙張方向、大小等參數(shù)進(jìn)行設(shè)置，方法與Word文檔的頁面設(shè)置類似。這里設(shè)置紙張方向?yàn)椤翱v向”，紙張大小為“A3”，設(shè)置4個方向的頁邊距均為“1.9厘米”，居中方式為“水平”。2)頁眉頁腳在“頁眉/頁腳”選項(xiàng)卡中為頁面添加頁眉，單擊“自定義頁眉”按鈕打開“頁眉”對話框，如圖5.26所示。在“中”框中輸入頁眉內(nèi)容“平時成績”，單擊“確定”按鈕完成頁眉的添加。在“工作表”選項(xiàng)卡“打印區(qū)域”框中設(shè)置打印的范圍，這里選擇工作表的數(shù)據(jù)范圍A1∶H15，這樣該區(qū)域以外的空白單元格將不會打印，如圖5.27所示。3)打印預(yù)覽使用Excel2016打印表格的時候，默認(rèn)只有第一頁有表頭。如果文檔分為多頁打印，而其他頁面沒有表頭，這樣對我們?yōu)g覽數(shù)據(jù)很不方便。此時，打開頁面布局中的“頁面設(shè)置”→“打印標(biāo)題”按鈕，在“頁面設(shè)置”對話框中的“工作表”標(biāo)簽中設(shè)置打印標(biāo)題的區(qū)域，把它選到你需要打印標(biāo)題的區(qū)域，這樣打印時每張都會有表頭，如圖5.28所示。頁面設(shè)置完成后，即可單擊“文件”按鈕進(jìn)入“打印”窗口，如圖5.29所示。在窗口右側(cè)顯示了要打印頁面的預(yù)覽效果，如果對預(yù)覽效果表示滿意，則可以在左側(cè)設(shè)置打印的份數(shù)、打印機(jī)等參數(shù)，單擊上方的“打印”按鈕進(jìn)行打印。如果還需要進(jìn)一步調(diào)整，單擊其他選項(xiàng)卡標(biāo)簽即可回到工作表編輯窗口。5.3數(shù)據(jù)挖掘5.3.1使用公式公式本質(zhì)是用運(yùn)算符將常量、單元格引用和函數(shù)、各運(yùn)算數(shù)等連接在一起的式子，以“＝”開頭。公式運(yùn)算符主要有3種：算術(shù)運(yùn)算符、關(guān)系運(yùn)算符和文本運(yùn)算符。5.3.2使用函數(shù)Excel中有300多個內(nèi)部函數(shù)，涉及許多應(yīng)用領(lǐng)域，例如財(cái)務(wù)、工程、統(tǒng)計(jì)、數(shù)據(jù)庫、時間處理、數(shù)學(xué)運(yùn)算等，可分為數(shù)學(xué)與三角函數(shù)、日期與時間函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)、統(tǒng)計(jì)函數(shù)、財(cái)務(wù)函數(shù)、數(shù)據(jù)庫函數(shù)、查找與引用函數(shù)和工程函數(shù)等10大類，常見的函數(shù)示例見表5.2。1.條件計(jì)數(shù)函數(shù)COUNTIF函數(shù)的表達(dá)式為COUNTIF(range，criteria)，包含兩個必需參數(shù)：一是range，用來確定要計(jì)數(shù)的單元格區(qū)域；二是criteria，表示計(jì)數(shù)的條件，可以為數(shù)字、表達(dá)式、單元格引用等。2.文本函數(shù)LEN函數(shù)的功能是返回文本字符串中的字符數(shù)。其語法格式為LEN(text)，其中參數(shù)text為要查找其長度的文本，空格也將作為字符進(jìn)行計(jì)數(shù)。LEFT函數(shù)的功能是根據(jù)所指定的字符數(shù)從文本字符串左邊取出第一個或前幾個字符。即LEFT函數(shù)對字符串進(jìn)行“左截取”，其語法格式為LEFT(text，num_chars)，其中，參數(shù)text是要提取字符的文本字符串；num_chars是指定提取的字符個數(shù)，它必須大于或等于0。如果省略參數(shù)num_chars，默認(rèn)值為1。如果參數(shù)num_chars大于文本長度，則LEFT函數(shù)返回所有文本。運(yùn)用LEN和LEFT函數(shù)，將通信地址中的郵編和地址分開，具體運(yùn)用如圖5.37所示。3.邏輯函數(shù)Excel中的邏輯函數(shù)共有6個，分別是IF、AND、OR、NOT、TRUE和FALSE，另外，IS類函數(shù)作為信息判斷的函數(shù)，也有邏輯判斷的功能。IF函數(shù)也稱條件函數(shù)，其功能是對指定的條件判斷真假，根據(jù)參數(shù)的真假值，返回不同結(jié)果。數(shù)據(jù)處理中，經(jīng)常利用IF函數(shù)對公式和數(shù)值進(jìn)行條件檢測。AND、OR、NOT等3個函數(shù)的語法格式分別為AND(logical1，logical2，…)，OR(logical1，logical2，…)，NOT(logical)。其中，AND和OR函數(shù)中的參數(shù)logical1、logical2表示待測試的條件值或表達(dá)式，其結(jié)果可為TRUE或FALSE，最多不超過30個。TRUE函數(shù)和FALSE函數(shù)都是無參函數(shù)，其結(jié)果分別是返回邏輯值“TRUE”和“FALSE”。IS類函數(shù)屬于信息判斷函數(shù)，其功能是檢驗(yàn)數(shù)值的類型，并且根據(jù)參數(shù)的值返回TRUE或FALSE，從而也可以起到一定的邏輯判斷和條件檢測功能。IS類函數(shù)總共包括9個用來檢驗(yàn)數(shù)值或引用類型的工作表函數(shù)，分別為ISBLANK函數(shù)、ISERE函數(shù)、ISERROR函數(shù)、ISLOGICAL函數(shù)、ISNA函數(shù)、ISNONTEXT函數(shù)、ISNUMBER函數(shù)、ISREF函數(shù)和ISTEXT函數(shù)，它們具有相同的語法格式：IS函數(shù)名(value)。在Excel中使用公式和函數(shù)不當(dāng)會導(dǎo)致錯誤，常見的錯誤形式見表5.3。5.3.3排序?qū)?shù)據(jù)進(jìn)行排序有助于快速直觀地顯示數(shù)據(jù)并更好地理解數(shù)據(jù)內(nèi)容，有助于分析和決策。利用Excel提供的排序功能，可以使用不同的方式對數(shù)據(jù)清單的內(nèi)容進(jìn)行排序。1.簡單的排序設(shè)定排序的關(guān)鍵字，使用單列簡單排序工具可以快速地將數(shù)據(jù)清單中的內(nèi)容按照某一列的信息進(jìn)行排序。2.復(fù)雜的排序簡單的排序只能有一個關(guān)鍵字，為了滿足需要，在復(fù)雜排序中，可以進(jìn)行多個關(guān)鍵字的排序。5.3.4分類匯總在Excel中進(jìn)行數(shù)據(jù)處理時，分類匯總是最頻繁的操作之一。分類匯總首先對數(shù)據(jù)進(jìn)行分類，之后按照分類分別進(jìn)行匯總。匯總之前必選先按類別字段進(jìn)行排序。5.3.5篩選篩選是數(shù)據(jù)處理最常用的方式之一，一般分為自動篩選和高級篩選兩種。1.自動篩選自動篩選是指采用簡單的條件快速篩選數(shù)據(jù)，將不能滿足條件的數(shù)據(jù)隱藏起來，只顯示滿足條件的數(shù)據(jù)。2.高級篩選使用自動篩選功能可以滿足數(shù)據(jù)清單中大部分的篩選需要，但部分條件較為復(fù)雜的篩選需求，則需要通過“高級篩選”功能來完成。高級篩選在進(jìn)行篩選時，需要建立條件區(qū)域。條件區(qū)域的規(guī)則如表5.5所示。5.3.6引用單元格使用公式運(yùn)算時，常常需要借助引用單元格地址來引用其中的數(shù)據(jù)，因此，單元格引用是Excel中最常用的運(yùn)算對象。引用分為相對引用、絕對引用、混合引用等3種。1.相對引用相對引用也稱相對單元格引用，相對引用中的單元格地址引用地址伴隨目標(biāo)位置的變化而變化。2.絕對引用與相對引用不同，絕對引用與包含公式的單元格的位置無關(guān)。單元格的絕對引用是指在單元格標(biāo)識符的行號和列號前都加上“$”符號，表示將行列凍結(jié)，不再發(fā)生位置的變化。3.混合引用混合引用是指引用行或列中的某一項(xiàng)被凍結(jié)的情況，即行號固定、列號可變，或列號固定、行號可變。5.4數(shù)據(jù)展示5.4.1圖表在Excel中，圖表是將工作表中的數(shù)據(jù)用圖形表示出來。圖表可以使數(shù)據(jù)更加有趣、吸引人、易于閱讀和評價，也可以幫助我們分析和比較數(shù)據(jù)。建立圖表后，可以通過增加圖表項(xiàng)，如數(shù)據(jù)標(biāo)記、圖例、標(biāo)題、文字、趨勢線、誤差線及網(wǎng)格線來美化圖表及強(qiáng)調(diào)某些信息。將引例一成績單中的語文成績，以柱形圖的形式展示，展示內(nèi)容如圖5.47所示，操作步驟如下。(1)選中姓名和語文兩列數(shù)據(jù)，選擇“插入”選項(xiàng)卡中“圖表”中的“柱形圖”圖標(biāo)，如圖5.48所示。(2)單擊“確定”按鈕后，進(jìn)入“設(shè)計(jì)”選項(xiàng)卡，在“設(shè)計(jì)”的最左端是“添加圖表元素”組，可根據(jù)需要選擇，如圖5.49所示。(3)如想快速得到多個元素的圖形，可在“設(shè)計(jì)”選項(xiàng)卡中的“快速布局”中選擇，如圖5.50所示。(4)輸入圖表標(biāo)題和縱坐標(biāo)軸的名稱，可在圖表右側(cè)對名稱文字進(jìn)行修改、填充或增加邊框等，如圖5.51所示。(5)為圖表選擇“切換行/列”，然后選擇“更改圖表類型”，之后可在“更改圖表類型”面板中更換成其他需要的圖表類型，如圖5.52所示。5.4.2透視表1.透視表與分類匯總的區(qū)別分類匯總是指對一定分類標(biāo)準(zhǔn)排序后的數(shù)值按分類標(biāo)準(zhǔn)進(jìn)行合計(jì)匯總，可以多級匯總