電子表格應(yīng)用

第4章Excel電子表格應(yīng)用中國人民大學(xué)信息學(xué)院尤曉東摘要4.0Excel學(xué)習(xí)建議4.1Excel基本知識(shí)4.2Excel工作表的建立與編輯4.3Excel公式與函數(shù)4.4Excel數(shù)據(jù)處理4.5Excel圖表4.6Excel學(xué)習(xí)綜合案例：成績單4.7小結(jié)中國人民大學(xué)信息學(xué)院尤曉東24.0Excel學(xué)習(xí)建議建議先快速瀏覽4.6節(jié)綜合案例的系列教學(xué)視頻，了解Excel的功能及其應(yīng)用：040601成績單的構(gòu)建040602常用統(tǒng)計(jì)函數(shù)(1)040603排序040604分類匯總040605篩選040606統(tǒng)計(jì)(2)與圖表應(yīng)用040607數(shù)據(jù)透視然后進(jìn)行4.1至4.5節(jié)的學(xué)習(xí)，掌握具體功能使用。最后再次詳細(xì)瀏覽4.6節(jié)綜合案例的教學(xué)視頻，掌握Excel各項(xiàng)功能的綜合應(yīng)用。中國人民大學(xué)信息學(xué)院尤曉東34.1Excel基本知識(shí)4.1.1Excel的啟動(dòng)與退出4.1.2Excel的窗口結(jié)構(gòu)4.1.3Excel中的數(shù)據(jù)類型4.1.4工作簿文件中的數(shù)據(jù)結(jié)構(gòu)參閱教學(xué)視頻0401。中國人民大學(xué)信息學(xué)院尤曉東44.1.1Excel的啟動(dòng)與退出常用啟動(dòng)方式：“開始”|“所有程序”|MicrosoftOffice|MicrosoftOfficeExcel2010從桌面快捷方式啟動(dòng)從“開始”菜單快捷方式啟動(dòng)還有多種啟動(dòng)方式，不一不列出。中國人民大學(xué)信息學(xué)院尤曉東5中國人民大學(xué)信息學(xué)院尤曉東64.1.2Excel的窗口結(jié)構(gòu)中國人民大學(xué)信息學(xué)院尤曉東標(biāo)簽滾動(dòng)按鈕工作表標(biāo)簽狀態(tài)欄視圖切換顯示比例滑塊名稱框行號(hào)快速訪問工具欄功能區(qū)標(biāo)題欄功能區(qū)最小化按鈕活動(dòng)單元格插入工作表按鈕水平滾動(dòng)條填充柄編輯欄垂直滾動(dòng)條展開編輯欄按鈕列標(biāo)插入函數(shù)按鈕74.1.3Excel中的數(shù)據(jù)類型本質(zhì)上，Excel的數(shù)據(jù)類型分文本、邏輯、數(shù)值等。根據(jù)定義的顯示格式的不同，數(shù)值類型可以顯示為數(shù)字、貨幣、會(huì)計(jì)、日期、時(shí)間、百分比、分?jǐn)?shù)、科學(xué)計(jì)數(shù)、特殊、自定義等子類型。單元格內(nèi)值的數(shù)據(jù)類型可通過“開始”選項(xiàng)卡的“格式”工具按鈕或“設(shè)置單元格格式”對話框?qū)崿F(xiàn)。錯(cuò)誤提示：當(dāng)單元格數(shù)據(jù)有誤或顯示不正常時(shí)，會(huì)顯示錯(cuò)誤提示。參見教學(xué)視頻040103。中國人民大學(xué)信息學(xué)院尤曉東8類型說明常規(guī)根據(jù)單元格的內(nèi)容自動(dòng)識(shí)別和設(shè)置。文本按輸入的字面進(jìn)行顯示和處理。文本格式的數(shù)字不作為數(shù)值進(jìn)行計(jì)算和處理。邏輯TRUE（真）或FALSE（假）值之一。數(shù)值（數(shù)字）用于一般數(shù)字的表示。對于非計(jì)算性數(shù)字（如手機(jī)號(hào)、身份證號(hào)等），一般建議使用文本格式。貨幣用于表示一般貨幣數(shù)值。會(huì)計(jì)會(huì)計(jì)格式可按貨幣符號(hào)和小數(shù)點(diǎn)對齊數(shù)值。日期顯示為日期值（本質(zhì)上，日期也是數(shù)值，可以和數(shù)值格式轉(zhuǎn)換顯示）。時(shí)間顯示為時(shí)間值（本質(zhì)上，時(shí)間也是數(shù)值，可以和數(shù)值格式轉(zhuǎn)換顯示）。百分比以百分?jǐn)?shù)形式顯示數(shù)值。分?jǐn)?shù)以分?jǐn)?shù)形式顯示數(shù)值?？茖W(xué)計(jì)數(shù)以科學(xué)計(jì)數(shù)法形式顯示數(shù)值。特殊特殊類型的內(nèi)容。自定義以指定格式顯示數(shù)值。中國人民大學(xué)信息學(xué)院尤曉東9錯(cuò)誤提示說明#VALUE!公式中所含單元格數(shù)據(jù)類型不匹配。例如，數(shù)值與文本相加。#NAME?公式中出現(xiàn)沒有命名的文本。#REF!單元格引用無效。#N/A數(shù)據(jù)不可用。#DIV/0!數(shù)值除以0或除以非數(shù)值單元。#NULL!指定了兩個(gè)不相交區(qū)域的交集。#NUM!公式或函數(shù)中含有無效的數(shù)值。#####列寬不足以顯示所有內(nèi)容，或在單元格中使用了負(fù)日期或時(shí)間。Excel常見錯(cuò)誤提示中國人民大學(xué)信息學(xué)院尤曉東104.1.4工作簿文件中的數(shù)據(jù)結(jié)構(gòu)工作簿：Excel環(huán)境中用來儲(chǔ)存并處理工作數(shù)據(jù)的文件。也就是說Excel文檔就是工作簿。每一本工作簿可以擁有多張不同的工作表，其擴(kuò)展名為XLSX或XLS。工作表：在Excel中用于存儲(chǔ)和處理數(shù)據(jù)的主要文檔。也稱為電子表格。工作表由排列成行或列的單元格組成。工作表總是存儲(chǔ)在工作簿中。單元格是Excel工作表中行與列的交叉部分，它是組成工作表的最小單位，單個(gè)數(shù)據(jù)的輸入和修改都是在單元格中進(jìn)行的。單元格按所在位置的“列標(biāo)+行號(hào)”來命名，例如“C8單元格”指的是“C”列與第8行交叉位置上的單元格。區(qū)域是指工作表上的兩個(gè)或多個(gè)單元格。區(qū)域中的單元格可以相鄰或不相鄰。工作簿、工作表、單元格、單元區(qū)域的命名。中國人民大學(xué)信息學(xué)院尤曉東114.2Excel工作表的建立與編輯4.2.1建立工作表實(shí)例4.2.2數(shù)據(jù)輸入方法4.2.3數(shù)據(jù)編輯方法4.2.4單元格式設(shè)置4.2.5編輯工作表實(shí)例4.2.6工作表的整體操作4.2.7工作表的打印輸出4.2.8在工作表中增加迷你圖中國人民大學(xué)信息學(xué)院尤曉東124.2.1建立工作表實(shí)例參見教學(xué)視頻“040601成績單的構(gòu)建”。中國人民大學(xué)信息學(xué)院尤曉東134.2.2數(shù)據(jù)輸入方法數(shù)據(jù)輸入方式鍵盤輸入下拉列表選擇根據(jù)系統(tǒng)記憶輸入使用填充功能輸入填充柄是位于選定區(qū)域右下角的小黑方塊。將用鼠標(biāo)指向填充柄時(shí)，鼠標(biāo)的指針更改為黑十字。使用填充柄可以快速地進(jìn)行區(qū)域填充操作（包括數(shù)據(jù)輸入和公式復(fù)制等）。參見教學(xué)視頻040202.中國人民大學(xué)信息學(xué)院尤曉東144.2.3數(shù)據(jù)編輯方法選擇、剪切、復(fù)制和粘貼數(shù)據(jù)重寫和修改數(shù)據(jù)插入和刪除數(shù)據(jù)清除數(shù)據(jù)撤消和恢復(fù)操作參見教學(xué)視頻040203。中國人民大學(xué)信息學(xué)院尤曉東154.2.4單元格式設(shè)置“設(shè)置單元格格式”對話框“數(shù)字”選項(xiàng)卡“對齊”選項(xiàng)卡“字體”選項(xiàng)卡“邊框”選項(xiàng)卡“填充”選項(xiàng)卡“保護(hù)”選項(xiàng)卡“格式”工具按鈕行列尺寸設(shè)置條件格式設(shè)置參見教學(xué)視頻040204。中國人民大學(xué)信息學(xué)院尤曉東16條件格式條件格式基于條件更改單元格區(qū)域的外觀。如果條件為True，則基于該條件設(shè)置單元格區(qū)域的格式；如果條件為False，則不基于該條件設(shè)置單元格區(qū)域的格式。條件格式可達(dá)到以下效果：突出顯示所關(guān)注的單元格或單元格區(qū)域；強(qiáng)調(diào)異常值；使用數(shù)據(jù)條、顏色刻度和圖標(biāo)集來直觀地顯示數(shù)據(jù)。無論是手動(dòng)還是按條件設(shè)置的單元格格式，都可以按格式進(jìn)行排序和篩選，其中包括單元格顏色和字體顏色?？蓪卧駞^(qū)域、Excel表格或數(shù)據(jù)透視表應(yīng)用條件格式。使用條件格式可以直觀地查看和分析數(shù)據(jù)、發(fā)現(xiàn)關(guān)鍵問題以及識(shí)別模式和趨勢。中國人民大學(xué)信息學(xué)院尤曉東174.2.5編輯工作表實(shí)例參見“4.6.1成績單的構(gòu)建”參見教學(xué)視頻040601。中國人民大學(xué)信息學(xué)院尤曉東184.2.6工作表的整體操作工作表的整體操作包括選擇、插入、刪除、移動(dòng)、復(fù)制、重命名一個(gè)工作表。選擇工作表插入工作表刪除工作表移動(dòng)和復(fù)制工作表重命名工作表改變工作表標(biāo)簽顏色參見教學(xué)視頻040206.中國人民大學(xué)信息學(xué)院尤曉東194.2.7工作表的打印輸出從“文件”菜單選擇“打印”命令進(jìn)行打印設(shè)置和打印。按快捷鍵Ctrl+P可以快速調(diào)出“打印”命令對話框。參見教學(xué)視頻040207。中國人民大學(xué)信息學(xué)院尤曉東204.2.8在工作表中增加迷你圖迷你圖是MicrosoftExcel2010中的一個(gè)新功能，它是工作表單元格中的一個(gè)微型圖表，可提供數(shù)據(jù)的直觀表示。參見教學(xué)視頻040208。中國人民大學(xué)信息學(xué)院尤曉東214.3Excel公式與函數(shù)4.3.1單元格引用4.3.2Excel公式4.3.3Excel函數(shù)4.3.4在單元格中輸入公式4.3.5填充操作輸入公式中國人民大學(xué)信息學(xué)院尤曉東224.3.1單元格引用單元格單元區(qū)域相對引用絕對引用混合引用跨工作表引用跨工作簿引用參見教學(xué)視頻040301。中國人民大學(xué)信息學(xué)院尤曉東23相對引用、絕對引用與混合引用相對引用：公式中的相對單元格引用（如A1）是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格位置改變，引用也隨之改變。如果多行或多列地復(fù)制或填充公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下新公式使用相對引用。絕對引用：公式中的絕對單元格引用（如$A$1）總是在特定位置引用單元格。如果公式所在單元格的位置改變，絕對引用將保持不變。如果多行或多列地復(fù)制或填充公式，絕對引用將不作調(diào)整?；旌弦茫夯旌弦镁哂薪^對列和相對行或絕對行和相對列。絕對引用列采用$A1、$B1等形式。絕對引用行采用A$1、B$1等形式。如果公式所在單元格的位置改變，則相對引用將改變，而絕對引用將不變。如果多行或多列地復(fù)制或填充公式，相對引用將自動(dòng)調(diào)整，而絕對引用將不作調(diào)整。中國人民大學(xué)信息學(xué)院尤曉東244.3.2Excel公式公式是一個(gè)運(yùn)算表達(dá)式，由運(yùn)算對象和運(yùn)算符按照一定規(guī)則和需要連接而成。運(yùn)算對象可以是常量、單元格引用、公式或函數(shù)運(yùn)算符包括算術(shù)、比較、文本連接和引用四種類型。算術(shù)運(yùn)算符：加+、減－、乘*、除/、百分號(hào)%、乘冪^比較運(yùn)算符：等于=、大于>、小于<、大于等于>=、小于等于<=，不等于<>。結(jié)果為邏輯值。文本連接運(yùn)算符：&。引用運(yùn)算符符：冒號(hào)：、逗號(hào)，、空格。公式始終以等號(hào)“=”開頭。關(guān)于運(yùn)算符的使用，參見教學(xué)視頻040302。中國人民大學(xué)信息學(xué)院尤曉東25

以下為一些公式例子：中國人民大學(xué)信息學(xué)院尤曉東27=5+2*3將5加到2與3的乘積中。=A1+A2+A3將單元格A1、A2和A3中的值相加。=SQRT(A1)使用SQRT函數(shù)返回單元格A1中值的平方根。=TODAY()返回當(dāng)前日期。=UPPER("hello")使用UPPER工作表函數(shù)將文本“hello”轉(zhuǎn)換為“HELLO”。=A1>0測試單元格A1，確定它是否包含大于0值。4.3.3Excel函數(shù)函數(shù)是預(yù)定義的公式，通過使用一些稱為參數(shù)的特定數(shù)值來按特定的順序或結(jié)構(gòu)執(zhí)行計(jì)算。函數(shù)可用于執(zhí)行簡單或復(fù)雜的計(jì)算。函數(shù)由函數(shù)名和參數(shù)兩部分構(gòu)成。函數(shù)名由英文字母構(gòu)成，參數(shù)由圓括號(hào)括住。根據(jù)函數(shù)的不同，參數(shù)的個(gè)數(shù)可能不同。如果參數(shù)個(gè)數(shù)為0，則仍需要保留括住函數(shù)的括號(hào)。Excel函數(shù)可以作為公式中一個(gè)運(yùn)算對象，也可以作為整個(gè)公式來使用。函數(shù)類別有數(shù)學(xué)與三角函數(shù)、統(tǒng)計(jì)函數(shù)、邏輯函數(shù)、日期與時(shí)間函數(shù)、財(cái)務(wù)函數(shù)、文本函數(shù)、查找與引用函數(shù)等。參見教學(xué)視頻040303。中國人民大學(xué)信息學(xué)院尤曉東28

Excel基礎(chǔ)應(yīng)用應(yīng)掌握以下函數(shù)的使用：統(tǒng)計(jì)函數(shù)：AVERAGE，COUNT，COUNTA，COUNTIF，COUNTIFS，COUNTBLANK，MAX，MIN數(shù)學(xué)與三角函數(shù)：SUM，SUMIF，RAND，ROUND，TRUNC邏輯函數(shù)：IF，AND，OR文本函數(shù)：LEN，LEFT，RIGHT，MID查找與引用函數(shù)：LOOKUP，VLOOKUP日期與時(shí)間函數(shù)：TODAY，YEAR，MONTH，DAY，NOW參見教學(xué)視頻040303。中國人民大學(xué)信息學(xué)院尤曉東294.3.4在單元格中輸入公式在單元格中，可以直接輸入和使用數(shù)值、文本、邏輯值，也可以輸入和使用公式，由系統(tǒng)根據(jù)公式計(jì)算出相應(yīng)的值并顯示出來。公式輸入時(shí)須以等號(hào)=為前導(dǎo)符號(hào)。如果需要在某單元格中輸入以等號(hào)開頭的文本，則可以單引號(hào)開頭。如果需要輸入以單引號(hào)開頭的文本，則需要再多輸入一個(gè)單引號(hào)。在向單元格中輸入公式時(shí)，輸入的公式會(huì)同時(shí)在單元格和編輯欄的編輯框中顯示，當(dāng)結(jié)束輸入后，單元格中顯示出公式計(jì)算結(jié)果值，而編輯欄中可顯示公式本身。在光標(biāo)選定單元格時(shí)，按F2功能鍵可使該單元格進(jìn)入編輯狀態(tài)。中國人民大學(xué)信息學(xué)院尤曉東304.3.5填充操作輸入公式參見教學(xué)視頻040202。中國人民大學(xué)信息學(xué)院尤曉東314.4Excel數(shù)據(jù)處理4.4.1數(shù)據(jù)排序4.4.2數(shù)據(jù)篩選4.4.3數(shù)據(jù)的分類匯總具體操作參見4.6節(jié)綜合案例。中國人民大學(xué)信息學(xué)院尤曉東324.5Excel圖表4.5.1圖表的概念4.5.2圖表的類型4.5.3圖表的創(chuàng)建過程4.5.4圖表編輯過程4.5.5圖表創(chuàng)建實(shí)例參見教學(xué)視頻0405。中國人民大學(xué)信息學(xué)院尤曉東334.6Excel學(xué)習(xí)綜合案例：成績單我們將以成績單的應(yīng)用為例，通過一系列綜合性的例子，來向大家介紹