Excel2016的應用完整課件.pptx

1、Excel2016【Excel2016工作界面】目錄CONTENTS創建班級學生信息表美化學生信息表編輯與修改學生信息表設置學生信息表數據驗證制作學生期末成績表并排名次0102030405篩選學生期末成績表匯總分析學生月考成績表匯總分析學生月考成績表創建圖表分析學生期末成績表06070809Excel 2016的基本操作任務一 創建班級學生信息表【明確任務】學校有新生入學，作為學生會干部的你，要用Excel制作一份學生信息表，方便了解學生基本信息?！局R準備】工作簿、工作表、單元格工作簿是在Excel電子表格中用來存儲并處理數據的文件,其擴展名是.xlsx。工作表是用于存儲和處理數據的表格頁，

2、它構成了工作簿文件，在一個工作簿文件中可以有多個工作表，系統默認一個工作簿中有3個工作表。行與列交叉形成的方格稱為單元格，表中的每個單元格都有一個唯一的名稱，稱為單元格地址?！局R準備】新建工作簿Excel與Word類似，可以通過“開始菜單”單擊目錄下的Excel啟動并新建工作簿。也可通過鼠標右擊桌面空白處新建工作簿。啟動Excel后在“文件”選項卡下選擇“新建”，可新建一空白工作簿【知識準備】保存工作簿與Word類似，在編輯Excel的時候要時常單擊左上角“保存”按鈕，防止數據丟失。對于修改Excel數據同時想保留初始數據 的情況下，可以通過“文件”選項卡下的“另存為”來實現?！局R準備】輸

3、入數據文本鼠標左鍵單擊選擇單元格后直接輸入，默認為左對齊一般數字與輸入文本操作相同，默認為右對齊負數直接在數字前面輸入“-”或者給數字添加圓括號“（）”分數輸入“0”和空格在輸入分數，或者轉換為文本格式可直接輸入長數字最多顯示11位數字，更多位數字以科學計數法的形式顯示以文本格式輸入數字輸入法在英文格式下輸入“”和數字【任務實施】新建學生信息表工作簿鼠標定位到桌面，單擊“右鍵”選擇“新建”，選擇Microsoft Excel工作表，并將其命名為學生信息表?！救蝿諏嵤枯斎胛谋臼髽俗箧I雙擊新建的Excel工作簿將其打開，并在sheet1工作表中A1：J15位置輸入以下文本【檢查評價】評價項目教師

4、評價自我評價 表格內容是否完整 表格格式是否美觀【課堂小結】1234工作簿、工作表、單元格新建工作簿保存工作簿輸入數據表格的基本設置任務二 美化學生信息表【復習回顧】學校有新生入學，作為學生會干部的你，要用Excel制作一份學生信息表，方便了解學生基本信息。工作簿、工作表和單元格有神區別與聯系？如何新建工作表？如何在Excel單元格中輸入分數？【明確任務】創建完學生信息表后，你覺得自己制作的表格不夠美觀，因此嘗試美化學生信息表【知識準備】設置文本格式在開始選項卡下字體選項組中可設置字體格式，包括字體樣式、字號、加粗、傾斜、下劃線、邊框、字體顏色等功能?！局R準備】設置對齊方式對齊方式在Word

5、中屬于一種段落格式，在Excel中則有一個獨立的對齊方式選項組，可以設置豎直和水平方向的對齊方式，此外還有自動換行和合并單元格等功能?！局R準備】表格數據選擇選擇單個單元格選擇相鄰單元格區域選擇不相鄰的單元格區域選擇大量數據中頁面無法顯示全的數據【知識準備】練習打開內容拓展一練習，選擇表格中的A1:J130區域【任務實施】設置字體格式鼠標定位到學生信息表帶數據的表格任意位置，使用“Ctrl+A”快捷鍵（同時按住Ctrl鍵和A鍵）全選文本，在字體選項卡下設置字體效果為“宋體”，字號為“12號”選擇A1單元格，按住鼠標左鍵不放拖動至J1單元格，從而選中A1：J1單元格區域，設置其字號為“14號”并

6、加粗【任務實施】設置字體格式選擇F2：F15單元格，在字體選項卡下將文字變為“傾斜”【任務實施】設置對齊方式鼠標定位到工作表中任意文本，使用“Ctrl+A”全選，在對齊方式選項卡下設置文本對齊方式為“垂直居中”和“居中”選擇“E2：E15”單元格，設置其對齊方式為“左對齊”【任務實施】添加邊框和底紋鼠標定位到工作表中任意文本，使用“Ctrl+A”全選，在字體選項卡下單擊“邊框”，在下拉菜單中選擇“所有框線”【任務實施】添加邊框和底紋選擇“A1：J1”單元格區域，在字體選項卡下選擇“填充顏色”，在打開的下拉列表中選擇“綠色”【檢查評價】評價項目教師評價自我評價 信息表文件內容是否完整 表格格式是

7、否美觀【課堂小結】123設置文本格式設置對齊方式表格數據選擇工作表行、列的添加和刪除任務三 編輯與修改學生信息表【復習回顧】學校有新生入學，作為學生會干部的你，要用Excel制作一份學生信息表，方便了解學生基本信息。如何設置文本格式？如何設置文本對齊方式？如何在表格中選擇不相鄰的單元格區域？【明確任務】開學后班級內出現學生轉專業、調整班級或者統計數據重復等情況，需要對學生信息表的行和列進行調整，這個想時候就需要對工作表的行和列進行添加和刪除，這個任務班主任交給了作為班委的你?！局R準備】插入單元格插入單元格包括活動單元格移動和插入行或列，在開始選項卡下，單元格選項組中點擊插入選項可進行調整。此

8、外選中單元格后執行右鍵-插入也可實現插入單元格?！局R準備】調整單元格的大小 單元格行高默認為14，列寬默認為8.09。大多數情況下我們輸入的數據與單元分的大小是不相匹配的，這個時候就需要我們調整單元格的大小。在開始選項卡單元格選項組中選擇格式選項可調整行高和列寬?！救蝿諏嵤坎迦胄袑⑹髽艘扑椭廖谋尽案哝贰弊髠取?3”位置，點擊鼠標右鍵，選擇“插入”，會在13行上面插入新的一行?！救蝿諏嵤枯斎胛谋驹凇癆13：J13”區域輸入文本內容如下【任務實施】插入列同樣，鼠標右鍵單擊文本“性別”上方的“B”，選擇“插入”，會在B列左側插入新的一列。在新插入的單元格中輸入文本內容如圖【任務實施】拓展練習嘗

9、試在身份證號一列中輸入任意18位身份證號，并將身份證號顯示出來【任務實施】刪除單元格對于個別單元格或單元格區域中的文本，需要刪除的直接鼠標左鍵選中單元格或單元格區域，按“Delete”鍵即可清除文本內容；如若需要刪除一行或一列單元格，可選中行標或列標右鍵單擊，然后選擇“刪除”即可?！緳z查評價】評價項目教師評價自我評價 表格內容是否完整 表格格式是否美觀【課堂小結】12插入單元格調整單元格的大小 數據驗證設置任務四 設置學生信息表數據驗證【復習回顧】學校有新生入學，作為學生會干部的你，要用Excel制作一份學生信息表，方便了解學生基本信息。如何在表格中插入行和列？刪除單元格的方式有哪些？如何調整

10、單元格的大??？【明確任務】你創建的學生信息表中要輸入新生的戶口性質，由于依次輸入非常麻煩，你決定用數據驗證的方式提高工作效率?！局R準備】數據驗證數據驗證是指給單元格中輸入的數據設置一個范圍，當我們輸入數據的時候可以在設置的選項中進行選擇，從而避免重復輸入、類型錯誤等情況。在數據選項卡，數據工具選項組中選擇數據驗證即可設置?！救蝿諏嵤啃陆üぷ鞅碛檬髽俗箧I點擊“Sheet1”右側“+”按鈕，新建一個工作表“Sheet2”【任務實施】輸入數據在A1：A3單元格中輸入數據如圖【任務實施】設置數據驗證在“Sheet1”中選擇F2：F16區域，單擊“數據”菜單下的“數據工具”選項卡中的“數據驗證”，在

11、下拉菜單中選擇“數據驗證”【任務實施】設置數據驗證打開如圖所示數據驗證對話框【任務實施】設置數據驗證驗證條件下的“允許”選擇“序列”【任務實施】設置數據驗證驗證條件下的“允許”選擇“序列”【任務實施】設置數據驗證來源選擇Sheet2工作表A1：A3區域，設置完成后點擊確定按鈕。此時鼠標左鍵選中“Sheet1”工作表“戶口性質”下任意單元格，在右側會出現一下拉菜單按鈕，將“王忠誠”的戶口性質由農村改為縣鎮?！緳z查評價】評價項目教師評價自我評價 表格內容是否完整 表格格式是否美觀使用公式、函數、自動填充、排序功能運用任務五 制作學生期末成績表并排名次【公示與函數】公式公式與函數是Excel處理數據

12、的核心內容之一。與數學公式類似，Excel中的公式也滿足一定的標注規則，主要包含了運算符、常量、函數以及單元格引用等內容。其最主要的特點是以等號開始，以單元格名稱為變量。例如我們要求一個圓的面積，公式展示如圖在B2單元格中輸入面積公式=PI()*A22，其中PI（）為函數，返回值為的大小，A2單元格為自變量半徑?！竟九c函數】函數上面我們提到了PI函數，實際上Excel中的公示與函數是相輔相成的，函數最終可以表示為一個公式，公式中可以包含多個函數。Excel中的函數種類和數量紛繁復雜，除了基本的加減乘除外還包含很多財務函數工程函數等，我們可以通過插入函數對話框下的“有關該函數的幫助”鏈接到微軟

13、官網了解所有公式和函數的功能?！久鞔_任務】期末成績是學生評獎評優的一項重要參考，為此需要對同學們的期末成績進行排名，你作為班長，在搜集到同學們的期末成績后，決定用Excel公式和函數進行排名?！局R準備】公式的概念數學上我們通常稱公式是包含了常量、運算符、變量等元素的方程式，與數學上的概念類似，Excel中的公式也包含了運算符、常量等元素，但它的變量通常以單元格引用的形式出現，同時所有公式都以“=”開始。例如：=1+3【知識準備】公式中的運算符1算數運算符加、減、乘、除等常見的數學運算符。2比較運算符用于比較兩個數值，如“=”、“”、“”等。3文本運算符連接符“&”，可以理解為與、和的意思，表

14、示將數據連接到一起?！局R準備】單元格引用1相對引用相對引用是指引用對象隨公式所在單元格位置變動而變動的一種引用方式，在公式復制或填充時引用單元格隨著移動的位置上相應變化。形式為“A1”、“C2:F3”等2絕對引用絕對引用是指引用對象為固定單元格，就算公式復制填充位置變動，引用單元格位置也不會發生變動。絕對引用是在相對引用的基礎上，在引用單元格的行標和列標前同時添加“$”符號。例如“$F$6”、“$E$4:$G$6”等。3混合引用混合引用是指在公式中同時使用相對引用與絕對引用?！局R準備】編輯公式1復制公式與復制普通文本一樣，使用“Ctrl+C”和“Ctrl+V”即可。2填充公式與填充數據一樣

15、，選中包含公式和需要填充的單元格區域后，選在開始選項卡編輯選項組下的填充，選擇填充方式即可。此外拖動填充柄也可實現公式填充?！局R準備】函數SUM求和AVERAGE求平均數MAX求最大值MIN求最小值IF條件函數，判斷指定條件真假，常以嵌套函數的形式出現COUNT計數RANK排序【知識準備】插入函數對話框界面介紹【知識準備】插入函數對話框界面介紹【任務實施】工作表初步設置在桌面新建Excel工作簿，命名為“學生期末成績表”，并輸入數據如圖【任務實施】工作表初步設置表頭為宋體，14號，加粗；內容為宋體，12號；文本對齊方式全部為“垂直居中”、“居中”；表頭對齊方式再設置“自動換行”，如圖“Ctr

16、l+A”全選表格文本，設置邊框格式為“所有框線”【任務實施】工作表初步設置在A1單元格中添加科目和姓名，樣式設置如圖A1單元格的行高為35，列寬為10【任務實施】使用“SUM”函數求每名學生的總成績SUM函數用于求解單元格區域中所有數值的和，通常用于求解一行或一列的和，也可用于求解不連續區域的和。將鼠標定位到J2單元格，選擇插入函數，如圖【任務實施】使用“SUM”函數求每名學生的總成績在打開的插入函數對話框中選擇“SUM”函數，如圖【任務實施】使用“SUM”函數求每名學生的總成績在Number1中輸入“B2：I2”，點擊確定，就可以求出第一個同學“姜旭”的總分，如圖【任務實施】使用“SUM”函

17、數求每名學生的總成績將鼠標指于J2單元格右下角黑色填充柄位置，如圖【任務實施】使用“SUM”函數求每名學生的總成績按住鼠標左鍵不放向下拖動至J21單元格，即可完成其他學生總成績的計算，我們稱這個操作為自動填充。如圖【任務實施】使用“RANK”函數計算班級學生成績排名RANK函數用于求解某一數字在一列數字中的大小排名，其衍生函數還有RANK.AVG和RANK.EQ。其中RANK.AVG適用于多個數值排名相同，返回平均值排名；RANK.EQ適用于多個數值排名相同，返回改組數值的最佳排名。選中K2單元格，執行插入函數，選擇“RANK”函數，在打開的RANK函數對話框中設置Number為J2，Ref為

18、J2:J21，點擊確定即可求出“姜旭”的成績排名。如圖【任務實施】使用“RANK”函數計算班級學生成績排名選擇K2單元格，將公式中的J2:J21改為$J$2:$J$21，并將K2單元格的公式向下填充，即可得到班級學生成績排名。如圖【知識準備】拓展練習通過網絡檢索，學習單元格相對引用、絕對引用和混合引用相關知識，寫出在上面學習的“RANK”函數中使用了哪種引用方式?！緳z查評價】評價項目教師評價自我評價執行公式公式審核顯示公式操作，檢查公式輸入是否正確表格內容是否完整函數運用是否準確【課堂小結】123公式函數單元格引用自動篩選、高級篩選的運用任務六 篩選學生期末成績表【復習回顧】在Excel中什么

19、是公式、什么是函數？公式與函數的聯系與區別是什么？單元格引用的方式有哪些？【明確任務】任課老師需要利用Excel將成績表中滿足一定條件的數據篩選出來，方便對全班成績進行分析，應當如何操作？【知識準備】自動篩選篩選功能在數據選項卡排序與篩選選項組目錄下，此外在開始選項卡編輯選項組下也可調用?；镜暮Y選包括項目選擇及簡單條件，包括等于、大于、小于等。自動篩選主要包括列表值、格式和條件三種類型，當我們需要定義兩個篩選條件時需要用到“與”與“或”，其中“與”表示同時滿足條件，“或”表示滿足條件之一?！局R準備】高級篩選在原有區域篩選結果指篩選結果顯示在當前位置，原數據被覆蓋將篩選結果復制到其他位置指將

20、篩選結果復制到其他單元格區域，包括其他工作表列表區域指要進行篩選的單元格區域條件區域指包含篩選條件的單元格區域，篩選條件要提前設置復制到指放置篩選結果的單元格區域選擇不重復記錄選中該項可取消篩選結果中的重復值【知識準備】篩選選中B1：K1單元格，點擊數據菜單中排序和篩選選項卡下的篩選按鈕，會發現每個單元格右下角會出現一個下拉菜單按鈕，如圖【知識準備】篩選接下來我們以企業財務會計成績為例，講解篩選的基本知識點擊計算機基礎下拉菜單按鈕，可以選擇單獨顯示任意成績，比如只選擇60.5，點擊確定，會只顯示計算機基礎考60.5的學生的成績【知識準備】篩選也可在下拉菜單中通過數字篩選，選擇符合一定條件的學生

21、成績【知識準備】篩選比如說我們要查找計算機基礎90分以上的學生，可在數字篩選中選擇“大于”輸入值為“90”，點擊確定即可，如圖這樣就可以篩選出計算機成績大于90的學生【知識準備】高級篩選如果想在班級中篩選出各科成績都在70分以上的學生，應當如何進行篩選呢？這就涉及到高級篩選的操作。將表頭復制到A27：K27單元格【知識準備】高級篩選新建Sheet2工作表，輸入文本【知識準備】高級篩選在Sheet1工作表中選擇排序和篩選選項卡下的高級按鈕，如圖【知識準備】高級篩選在高級篩選對話框中選擇列表區域為A1：K21，如圖【知識準備】高級篩選條件區域為Sheet2工作表的A1：K2區域，如圖【知識準備】高

22、級篩選接下來選擇將篩選結果復制到其他位置，復制到設置為A27：K27區域，點擊確定按鈕，即可篩選出各科成績70分以上的同學。如圖【檢查評價】評價項目教師評價自我評價篩選操作步驟是否正確篩選結果是否符合要求【課堂小結】12自動篩選高級篩選分類匯總的運用任務七 匯總分析學生月考成績表【明確任務】期末了，學生各月月考的成績是任課老師給學生平時表現打分的依據，現有某班級9、10、11三個月月考的成績，我們可以通過分類匯總的方式方便老師打分?！局R準備】數據排序排序功能在數據選項卡排序與篩選選項組目錄下，此外在開始選項卡編輯選項組下也可調用。除了簡單的升序降序排列外，對于多條件、多排序依據等需要使用自定

23、義排序。排序依據以實際情況而定，單元格顏色、字體顏色都可排序，此外也可根據需要自定義排序規則?！局R準備】分類匯總分類匯總是對數據進行統計匯總的常用方式，創建分類匯總之前必須對數據進行排序，從而實現數據分類，在此基礎上再對數據進行匯總?！救蝿諏嵤颗判驅⒌?行、第8行和第14行單元格刪除，如圖【任務實施】排序選中A2：I16單元格區域，單擊數據菜單，排序和篩選選項卡下的排序按鈕，如圖【任務實施】排序在打開的排序對話框中，設置主要關鍵字為“姓名”，點擊確定按鈕，如圖【任務實施】排序在打開的排序對話框中，設置主要關鍵字為“姓名”，點擊確定按鈕，如圖【任務實施】分類匯總選擇A1：I16區域，單擊數據

24、菜單，分級顯示選項卡下的分類匯總按鈕，如圖【任務實施】分類匯總在彈出的分類匯總對話框中，設置分類字段為姓名，匯總方式為求和，選定總項除姓名外全部選擇，點擊確定按鈕。如圖【檢查評價】評價項目教師評價自我評價分類匯總操作步驟是否正確分類匯總結果是否符合要求【課堂小結】12數據排序分類匯總合并計算的運用任務八 匯總分析學生月考成績表【明確任務】圖中三個月月考的成績分別分布在三個工作表中，要想把他們匯總到一個工作表中就需要使用合并計算?！局R準備】合并計算合并計算位于數據選項卡數據工具選項組中，指將相互獨立的數據合并到一起計算。主要包括按位置進行合并計算和按類別進行合并計算。按位置合并需要注意數據在源

25、區域中具有相同的順序，并使用相同的標簽。按類別進行合并計算則用于源區域中的數據不以相同的順序排列但使用相同的標簽的情況?！救蝿諏嵤亢喜⒂嬎氵x擇月考總成績表A1單元格，點擊數據菜單欄，數據工具選項卡下的合并計算按鈕。如圖【任務實施】合并計算在打開的合并計算對話框中，函數選擇求和，如圖【任務實施】合并計算單擊引用位置右側藍邊框箭頭，鼠標左鍵點擊3月月考工作表，并按“Ctrl+A”組合鍵選中整個工作表。如圖【任務實施】合并計算點擊“合并計算-引用位置”對話框右側藍色箭頭，選中第一個引用區域，如圖【任務實施】合并計算點擊添加按鈕，將引用位置添加到下方白框中，如圖【任務實施】合并計算重復以上操作，將1

26、月和2月月考也添加到“所有引用位置”當中。如圖【任務實施】合并計算勾選標簽位置下的首行和最左列，點擊確定，如圖【任務實施】拓展練習產品銷售業績統計與分析原文件為某家具城3月份銷售相關情況的統計表，現要求對表格進行美化、使用函數計算銷售額和利潤并使用合并計算求出每人的銷售額和利潤。效果如圖其中標題“家具城銷售額統計”字體為黑體，字號為20號；A2：G2區域字體為宋體，字號為14號，填充顏色為淺綠；內容文本字體為宋體，字號為12號；使用公式或函數求解總銷售額和利潤；分類匯總以“業務員”為分類字段，以求和為匯總方式，以“總銷售額”和“利潤”為選定匯總項?！緳z查評價】評價項目教師評價自我評價合并計算操

27、作步驟是否正確合并計算結果是否準確圖表的創建、編輯任務九 創建圖表分析學生期末成績表【明確任務】當我們想比較每個學生各科成績的時候，會發現單純以表格的形式展示數據并不直觀，這個時候我們可以使用圖表來分析學生成績?！局R準備】推薦的圖表Excel可根據用戶選中的數據智能的推薦圖表，在不知道該用哪種圖表樣式的時候可選擇該選項?！局R準備】組合圖表組合圖表是指多種圖表樣式的疊加，包括簇狀柱形圖-折線圖、簇狀柱形圖-次坐標軸上的折線圖、堆積面積圖-簇狀柱形圖三種基本形式，此外用戶也可根據實際需求選擇兩種基本類型的組合?！局R準備】圖表數據編輯圖表數據編輯主要包括對現有數據編輯和添加數據，當我們插入圖表

28、后，使用鼠標左鍵選中圖表，在圖表工具-設計-數據中可進行數據設置。這里需要注意圖例項和水平軸標簽的設置?！局R準備】設置圖表布局圖表布局在圖表工具-設計-圖表布局中進行設置，除在快速布局中提供的8中預設布局樣式外，通常我們使用添加圖表元素功能來設置圖表布局，坐標軸、圖標標題、數據標簽等元素都可進行設置?！局R準備】設置圖表樣式圖表樣式在圖表工具-設計-圖表樣式中進行設置，Excel提供了多種預制樣式供用戶選擇，此外可通過更改顏色功能改變圖表顏色?！救蝿諏嵤繄D表創建與簡單編輯雙擊打開學生期末成績表源文件選中A1：F7單元格區域，選擇“插入”“圖表”，單擊插入柱形圖或條形圖，如圖【任務實施】圖表

29、創建與簡單編輯在下拉菜單中選擇三維柱形圖三維簇狀柱形圖，如圖【任務實施】圖表創建與簡單編輯將圖表拖動到適當位置，修改圖表標題為“期末成績”，如圖【任務實施】圖表創建與簡單編輯雙擊鼠標左鍵選中安全對應柱子，如圖【任務實施】圖表創建與簡單編輯選擇圖表工具設計添加圖標元素數據標簽數據標注，即可將姜旭的安全成績標注到圖標中。同理，單擊鼠標左鍵可選中一類柱子進行標注，如圖【任務實施】圖表的美化選擇圖表工具設計圖標樣式更改顏色彩色調色板4，如圖【任務實施】圖表的美化選擇圖表樣式下拉菜單中的樣式9，如圖【檢查評價】評價項目教師評價自我評價圖表制作步驟是否正確圖表類型選擇是否合理圖表格式是否美觀【課堂小結】123Excel中常用圖表及其種類設置圖表布局設置圖表樣式