信息技術(shù)基礎(chǔ) 課件 項目3 Excel 2016 電子表格軟件應(yīng)用

信息技術(shù)基礎(chǔ)項目3Excel2016電子表格軟件應(yīng)用目錄CONTENTSExcel2016工作界面及基礎(chǔ)知識制作員工工資表制作銷售趨勢分析圖分析庫存商品明細表01.02.03.04.Excel2016工作界面及基礎(chǔ)知識3.1工作完成情況1.Excel2016的工作界面快速訪問工具欄編輯欄列標(biāo)窗口控制按鈕組工作表區(qū)域垂直滾動條視圖按鈕水平滾動條活動工作表標(biāo)簽行號控制句柄活動單元格“全部選定”按鈕名稱框功能區(qū)工作完成情況工作完成情況（1）新建和保存工作簿（2）打開與關(guān)閉工作簿（3）插入工作表（4）刪除工作表（5）重命名工作表（6）移動或復(fù)制工作表（7）隱藏或顯示工作表（8）凍結(jié)工作表

選擇“凍結(jié)拆分窗格”選項選定工作表區(qū)域的任意單元格，然后選擇“視圖”選項卡,在“窗口”選項組中單擊“凍結(jié)窗格”按鈕，從彈出的下拉列表中選擇“凍結(jié)拆分窗格”選項，可以凍結(jié)此單元格上邊和左邊的區(qū)域，以使其位置固定不變，從而方便數(shù)據(jù)的瀏覽。如果要取消凍結(jié)，則可以選擇“視圖”選項卡，在“窗口”選項組中單擊“凍結(jié)窗格”按鈕，從彈出的下拉列表中選擇“取消凍結(jié)窗格”選項。2.工作表和工作簿的常見操作工作完成情況工作完成情況（1）選定單元格及單元格區(qū)域（2）輸入數(shù)據(jù)（3）快速輸入工作表數(shù)據(jù)（4）查找和替換3.在工作表中輸入數(shù)據(jù)4.單元格、行和列的相關(guān)操作（1）插入與刪除單元格（2）合并與拆分單元格（3）插入與刪除行和列（4）隱藏與顯示行和列（5）改變行高和列寬（1）修改與刪除單元格內(nèi)容（2）移動和復(fù)制表格數(shù)據(jù)（3）設(shè)置表格邊框和填充效果①設(shè)置表格邊框。選定要設(shè)置邊框的單元格區(qū)域,選擇“開始”選項卡,在“字體”選項組中單擊“邊框”的下三角按鈕,然后從彈出的下拉列表中選擇適當(dāng)?shù)倪吙驑邮?。②設(shè)置表格填充效果。選定要設(shè)置的單元格區(qū)域,在“設(shè)置單元格格式”對話框的“填充”選項卡中設(shè)置單元格區(qū)域的背景色、填充效果、圖案顏色、圖案樣式等。（4）套用表格格式（5）設(shè)置條件格式（6）格式的復(fù)制與清除（7）保護工作表用鼠標(biāo)右鍵單擊工作表標(biāo)簽,從彈出的快捷菜單中選擇“保護工作表”選項,打開“保護工作表”對話框,勾選“保護工作表及鎖定的單元格內(nèi)容”復(fù)選框。5.編輯與設(shè)置表格數(shù)據(jù)制作員工工資表3.2工作完成情況工作完成情況（1）新建表格（2）輸入數(shù)據(jù)1.編輯表格內(nèi)容2.計算加班費（1）計算加班時長選定工作表“加班統(tǒng)計表”的C3:C23單元格區(qū)域,選擇“數(shù)據(jù)”選項卡,在“數(shù)據(jù)工具”選項組中單擊“合并計算”按鈕,打開“合并計算”對話框。在“函數(shù)”下拉列表中選擇“求和”選項,然后將光標(biāo)移至“引用位置”文本框,單擊“第1周”工作表,選定該工作表的C3:C23單元格區(qū)域,單擊“添加”按鈕,將“第1周!$C$3:$C$23”添加到“所有引用位置”列表框中。將其他的加班時長按照上述方法也添加到“所有引用位置”列表框中,如圖所示,單擊“確定”按鈕完成數(shù)據(jù)合并。工作完成情況工作完成情況2.計算加班費（2）計算加班費①插入函數(shù)。選定要插入函數(shù)的D3單元格,單擊編輯欄中的“插入函數(shù)”按鈕,打開“插入函數(shù)”對話框,在“或選擇類別”下拉列表中選擇“常用函數(shù)”選項,從“選擇函數(shù)”列表框中選擇“IF”選項,單擊“確定”按鈕。②在打開的“函數(shù)參數(shù)”對話框中設(shè)置函數(shù)參數(shù)。③選定D3單元格,按住鼠標(biāo)左鍵向下拖動控制句柄至D23單元格處。選定D2單元格,單擊“審閱”選項卡中“批注”選項組的“新建批注”按鈕,如圖3-42所示,在其中添加加班費的計算方法。工作完成情況工作完成情況3.填充并計算工資（1）引用單元格①單元格地址的引用格式。單元格地址的引用格式是[工作簿名]工作表名!單元格地址。當(dāng)引用當(dāng)前工作簿或工作表中的數(shù)據(jù)時，相應(yīng)項可省略。②引用的類型。Excel2016提供了三種不同類型的引用：相對引用、絕對引用和混合引用（可以使用F4鍵切換三種類型）。在實際應(yīng)用中，要根據(jù)數(shù)據(jù)的關(guān)系決定采用哪種類型的引用。（2）計算應(yīng)發(fā)工資金額計算應(yīng)發(fā)工資總額時可以在H4單元格中輸入計算公式“=E4+F4+G4”后按Enter鍵,如圖所示；也可以選定H4單元格,然后單擊編輯欄中的“插入函數(shù)”按鈕,在打開的“插入函數(shù)”對話框的“或選擇類別”下拉列表中選擇“常用函數(shù)”選項,從“選擇函數(shù)”列表框中選擇“SUM”選項,單擊“確定”按鈕,接著在打開的“函數(shù)參數(shù)”對話框中輸入單元格的位置,最后單擊“確定”按鈕。向下填充計算公式或函數(shù)即可計算每個人的應(yīng)發(fā)工資總額。工作完成情況工作完成情況3.填充并計算工資（3）計算個人所得稅計算個人所得稅需要使用函數(shù)IF中嵌套函數(shù)

IF

來實現(xiàn)多個條件的設(shè)定。在I4單元格中應(yīng)輸入“=IF(H4<=5000,0,IF(H4<=8000,(H4-5000)?0.03,IF(H4<=17000,(H4-5000)?0.1-210,(H4-5000)?0.2-1410)))”，然后按Enter鍵插入函數(shù)，最后向下填充公式即可。（4）扣款總額和實發(fā)工資?？劭羁傤~的計算公式為：扣款總額=個人所得稅+其他；實發(fā)工資的計算公式為：實發(fā)工資=應(yīng)發(fā)工資總額-扣款總額，這兩項的計算方法與計算應(yīng)發(fā)工資總額的計算方法一致，此處不再重復(fù)。4.美化表格（1）設(shè)置格式（2）設(shè)置表格邊框（3）凍結(jié)窗格5.Excel2016的函數(shù)Excel2016中所提到的函數(shù)其實是一些預(yù)定義的公式,它們使用一些被稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算。Excel2016的函數(shù)一共有12類,在“公式”選項卡的“函數(shù)庫”選項組中可以找到,單擊“函數(shù)庫”選項組的“其他函數(shù)”的下三角按鈕,在彈出的下拉列表中顯示的是其他類型的函數(shù)。制作銷售趨勢分析圖3.3工作完成情況工作完成情況1.獲取數(shù)據(jù)（1）創(chuàng)建一個新的工作簿,在Sheet1表格中單擊“數(shù)據(jù)”選項卡中“獲取外部數(shù)據(jù)”選項組的“自文本”按鈕,打開“導(dǎo)入文本文件”對話框,如圖所示。選擇要導(dǎo)入的文本文件,然后單擊“導(dǎo)入”按鈕。（2）打開“文本導(dǎo)入向?qū)?第1步,共3步”對話框,如圖所示。在“原始數(shù)據(jù)類型”選區(qū)中,選中“分隔符號”單選按鈕,然后單擊“下一步”按鈕。（3）打開“文中導(dǎo)入向?qū)?第2步,共3步”對話框,如圖所示。在“分隔符號”選區(qū)中勾選“Tab鍵”復(fù)選框,表示此處分隔符號為制表符Tab鍵,在“數(shù)據(jù)預(yù)覽”選區(qū)中可以看到導(dǎo)入數(shù)據(jù)的效果,然后單擊“下一步”按鈕。（4）打開“文本導(dǎo)入向?qū)?第3步,共3步”對話框,如圖所示。觀察“數(shù)據(jù)預(yù)覽”選區(qū),發(fā)現(xiàn)不需要更改數(shù)據(jù)格式,單擊“完成”按鈕。2.創(chuàng)建圖表“圖表”選項組工作完成情況工作完成情況3.設(shè)置圖表（1）調(diào)整圖表的大小和位置（2）更改圖表數(shù)據(jù)源圖表創(chuàng)建完成后,可以在后續(xù)操作中根據(jù)需要向其中添加新數(shù)據(jù),或者刪除已有的數(shù)據(jù)。（3）交換圖表的行與列“選擇數(shù)據(jù)源”對話框工作完成情況工作完成情況4.格式化圖表（1）設(shè)置圖表標(biāo)題（2）設(shè)置圖表坐標(biāo)軸（3）添加圖例和數(shù)據(jù)標(biāo)簽（4）更改圖表類型（5）設(shè)置圖標(biāo)樣式

（6）添加趨勢線“設(shè)置圖表標(biāo)題格式”任務(wù)窗格“設(shè)置坐標(biāo)軸格式”任務(wù)窗格工作完成情況工作完成情況5.打印活動工作表（1）頁面設(shè)置①設(shè)置紙張大小。選擇“頁面布局”選項卡,在“頁面設(shè)置”選項組中單擊“紙張大小”按鈕,從彈出的下拉列表中選擇所需的紙張。②設(shè)置紙張方向。選擇“頁面布局”選項卡,然后在“頁面設(shè)置”選項組中單擊“紙張方向”按鈕,從彈出的下拉列表中選擇一種紙張方向。③設(shè)置頁邊距。選擇“頁面布局”選項卡,在“頁面設(shè)置”選項組中單擊“頁邊距”按鈕,從彈出的下拉列表中選擇一種頁邊距方案。④設(shè)置打印區(qū)域。⑤設(shè)置打印標(biāo)題。⑥設(shè)置頁眉和頁腳。（2）打印工作表①打印預(yù)覽選擇“文件”→“打印”選項,可以在右側(cè)預(yù)覽打印效果。如果看不清楚,那么可以單擊預(yù)覽頁面下方的“縮放到頁面”按鈕。②打印工作表選擇“文件”→“打印”選項,顯示“打印”選項面板。在“份數(shù)”微調(diào)框中輸入要打印的份數(shù)。分析庫存商品明細表3.4工作完成情況工作完成情況1.整理原始數(shù)據(jù)打開工作簿“庫存商品明細表”,然后選定待分析的A1:F41單元格區(qū)域,選擇“數(shù)據(jù)”選項卡,單擊“數(shù)據(jù)工具”選項組中的“刪除重復(fù)項”按鈕,打開“刪除重復(fù)項”對話框,如圖所示。勾選“數(shù)據(jù)包含標(biāo)題”復(fù)選框,并勾選列表框中的所有復(fù)選框,表示只有這些列都相同時才認為是重復(fù)項。單擊“確定”按鈕,Excel2016將搜索并刪除重復(fù)值,刪除完成后,打開顯示處理結(jié)果的對話框,單擊“確定”按鈕,完成操作?！皠h除重復(fù)項”對話框工作完成情況工作完成情況2.數(shù)據(jù)的排序（1）簡單排序選定包含數(shù)據(jù)的任意單元格,然后選擇“數(shù)據(jù)”選項卡,在“排序和篩選”選項組中單擊“升序”或“降序”按鈕,默認按列排序。（2）復(fù)雜排序復(fù)雜排序是指對選定的數(shù)據(jù)區(qū)域,按照兩個以上的排序關(guān)鍵字按行或按列進行排序的方法。以下是先按照工作簿“庫存商品明細表”的“在庫數(shù)量”降序排列,當(dāng)庫存數(shù)量相同時,再按照“單價”降序排列的操作方法。（3）自定義排序自定義排序是指對選定的數(shù)據(jù)區(qū)域按照用戶定義的順序進行排序。選定數(shù)據(jù)區(qū)域的任意單元格,選擇“數(shù)據(jù)”選項卡,在“排序和篩選”選項組中單擊“排序”按鈕,打開“排序”對話框。在“主要關(guān)鍵字”下拉列表中選擇“名稱”選項,在“次序”下拉列表中選擇“自定義序列”選項,打開“自定義序列”對話框。在“自定義序列”選項卡的“輸入序列”列表框中依次輸入指定的序列,每輸入一項,按一次Enter鍵,輸入完成后,單擊“添加”按鈕,序列就被添加到“自定義序列”列表框中。單擊“確定”按鈕,返回“排序”對話框,然后單擊“確定”按鈕,數(shù)據(jù)區(qū)域?qū)凑丈鲜鲋付ǖ男蛄信判?。工作完成情況工作完成情況3.數(shù)據(jù)的篩選（1）自動篩選自動篩選是指按照單一條件進行數(shù)據(jù)篩選。選定數(shù)據(jù)區(qū)域的任意單元格,選擇“數(shù)據(jù)”選項卡,在“排序和篩選”選項組中單擊“篩選”按鈕,表格中的每個表頭右側(cè)將顯示自動篩選下三角按鈕。（2）自定義篩選單擊“單價”列的自動篩選下三角按鈕,從彈出的下拉列表中選擇“數(shù)字篩選”→“介于”選項,打開“自定義自動篩選方式”對話框。在“大于或等于”右側(cè)下拉列表框中輸入“20”,并選中“與”單選按鈕,然后在“小于或等于”右側(cè)的下拉列表框中輸入“70”。單擊“確定”按鈕,即可顯示符合條件的記錄。（3）高級篩選單擊數(shù)據(jù)區(qū)域中的任意單元格,然后選擇“數(shù)據(jù)”選項卡,在“排序和篩選”選項組中單擊“高級”按鈕,打開“高級篩選”對話框。在“方式”選區(qū)中選中“將篩選結(jié)果復(fù)制到其他位置”單選按鈕(若選中“在原有區(qū)域顯示篩選結(jié)果”單選按鈕,則不用指定“復(fù)制到”區(qū)域)?！傲斜韰^(qū)域”文本框?qū)⒈蛔詣釉O(shè)定為數(shù)據(jù)區(qū)域$A$1:$F$37。將光標(biāo)移至“條件區(qū)域”文本框中,然后選擇包括表頭在內(nèi)的條件區(qū)域$I$1:$N$2。工作完成情況工作完成情況4.數(shù)據(jù)的分類匯總（1）創(chuàng)建分類匯總單擊數(shù)據(jù)區(qū)域中“名稱”列的任意單元格,選擇“數(shù)據(jù)”選項卡,在“排序和篩選”選項組中單擊“升序”按鈕,對該字段進行排序。在“分級顯示”選項組中單擊“分類匯總”按鈕,打開“分類匯總”對話框。在“分類字段”下拉列表中選擇“名稱”選項,在“匯總方式”下拉列表中選擇“求和”選項,在“選定匯總項”列表框中勾選“在庫數(shù)量”復(fù)選框。單擊“確定”按鈕。（2）嵌套分類匯總首先,對數(shù)據(jù)區(qū)域中要實施分類匯總的多個字段進行排序。然后,將光標(biāo)移至數(shù)據(jù)區(qū)域中,使用上面介紹的方法,按第一關(guān)鍵字對數(shù)據(jù)區(qū)域進行分類匯總。最后,再次打開“分類匯總”對話框,在“分類字段”下拉列表中選擇次要關(guān)鍵字,將“匯總方式”下拉列表框和“選定匯總項”列表框保持與第一關(guān)鍵字相同的設(shè)置,并取消勾選“替換當(dāng)前分類匯總”復(fù)選框。單擊“確定”按鈕,操作完成。（3）復(fù)制分類匯總的結(jié)果通過分級顯示按鈕僅顯示需要復(fù)制的結(jié)果,按Alt+;組合鍵選定當(dāng)前顯示的內(nèi)容,然后按Ctrl+C組合鍵將其復(fù)制到剪貼板中。在目標(biāo)單元格區(qū)域中按Ctrl+V組合鍵完成粘貼操作。如有需要,可以使用“分類匯總”對話框?qū)⒛繕?biāo)位置的分類匯總?cè)縿h除。工作完成情況工作完成情況5.建立數(shù)據(jù)透視表（1）創(chuàng)建數(shù)據(jù)透視表（2）添加、刪除、更