模塊07 Excel 2010的數(shù)據(jù)分析功能—制作銷售業(yè)績統(tǒng)計表.ppt

模塊07Excel2010的數(shù)據(jù)分析功能 制作銷售業(yè)績統(tǒng)計表 你知道嗎 Excel2010提供了極強(qiáng)的公式 函數(shù) 數(shù)據(jù)排序 篩選 分類匯總以及圖表等功能 使用這些功能 用戶可以方便的管理 分析數(shù)據(jù) 應(yīng)用場景人們平常會見到分期付款計算 記賬表等電子表格 這些都可以利用Excel2010的數(shù)據(jù)分析功能來制作 如圖所示就是利用Excel2010的數(shù)據(jù)分析功能制作的銷售業(yè)績統(tǒng)計表 請讀者根據(jù)本模塊所介紹的知識和技能 完成這一工作任務(wù) 背景知識通過對銷售數(shù)據(jù)的分析 可以及時反應(yīng)銷售計劃完成的情況 有助于一線人員分析銷售過程中存在的問題 為提高銷售業(yè)績及服務(wù)等技能提供依據(jù)和參考 通過對銷售數(shù)據(jù)的分析 可以及時掌握銷售波動 掌握客戶需求情況的變化 為管理者對企業(yè)的管理提供科學(xué)的依據(jù) 設(shè)計思路在制作銷售業(yè)績統(tǒng)計表的過程中 主要應(yīng)用到使用公式和函數(shù)來計算數(shù)據(jù) 利用排序功能排序數(shù)據(jù) 對數(shù)據(jù)進(jìn)行篩選 創(chuàng)建分類匯總 最后利用圖表分析 制作銷售業(yè)績統(tǒng)計表的基本步驟可分解為 1 使用公式 2 使用函數(shù) 3 排序數(shù)據(jù) 4 篩選數(shù)據(jù) 5 匯總數(shù)據(jù) 6 應(yīng)用圖表 項目任務(wù)7 1使用公式 公式是在工作表中對數(shù)據(jù)進(jìn)行分析和運算的等式 或者是一組連續(xù)的數(shù)據(jù)和運算符組成的序列 公式要以等號 開始 用于表明其后的字符為公式 緊隨等號之后的是需要進(jìn)行計算的元素 各元素之間用運算符隔開 動手做1創(chuàng)建公式在創(chuàng)建公式時可以直接在單元格中輸入 也可以在編輯欄中輸入 在編輯欄中輸入和在單元格中輸入計算結(jié)果是相同的 下面我們?yōu)殇N售業(yè)績統(tǒng)計表運用公式 具體步驟如下 1 打開存放在 案例與素材 模塊07 素材 文件夾中名稱為 銷售業(yè)績統(tǒng)計表 初始 文件 切換到 Sheet1 工作表 2 選定單元格 J3 直接在單元格或編輯欄中輸入公式 D3 E3 F3 G3 H3 I3 3 按回車鍵 或單擊編輯欄中的輸入按鈕即可在單元格中計算出結(jié)果而此時在編輯欄中依然顯示輸入的公式 如圖所示 項目任務(wù)7 1使用公式 動手做2單元格的引用引用的作用在于標(biāo)識工作表上的單元格或單元格區(qū)域 并指明公式中所使用的數(shù)據(jù)的位置 通過引用 可以在公式中使用工作表不同部分的數(shù)據(jù) 或者在多個公式中使用同一個單元格的數(shù)值 還可以引用同一個工作簿中不同工作表上的單元格和其他工作簿中的數(shù)據(jù) 在Excel2010中 系統(tǒng)提供了三種不同的引用類型 相對引用 絕對引用和混合引用 它們之間既有區(qū)別又有聯(lián)系 在引用單元格數(shù)據(jù)時 用戶一定要弄清楚這三種引用類型之間的區(qū)別和聯(lián)系 相對引用 指的是引用單元格的行號和列標(biāo) 所謂相對就是可以變化 它的最大特點就是在單元格中使用公式時如果公式的位置發(fā)生變化 那么所引用的單元格也會發(fā)生變化 絕對引用 顧名思義就是當(dāng)公式的位置發(fā)生變化時 所引用的單元格不會發(fā)生變化 無論移到任何位置 引用都是絕對的 絕對引用使用在單元格名前加一符號 如 A 3表示單元格 A3 是絕對引用 混合引用 就是指只絕對引用行號或者列標(biāo) 如 B6表示絕對引用列標(biāo) B 6則表示絕對引用行號 當(dāng)相對引用的公式發(fā)生位置變化時 絕對引用的行號或列標(biāo)不變 但相對引用的行號或列標(biāo)則發(fā)生變化 如果多行多列地復(fù)制公式 則相對引用自動調(diào)整 而絕對引用不作調(diào)整 例如 如果將一個混合引用 A 1 從A2復(fù)制到B2 它將從 A 1 調(diào)整到 B 1 項目任務(wù)7 2應(yīng)用函數(shù) 函數(shù)是一些預(yù)定義的公式 通過使用一些稱為參數(shù)的特定數(shù)值來按特定的順序或結(jié)構(gòu)執(zhí)行計算 函數(shù)可用于執(zhí)行簡單或復(fù)雜的計算 在公式中合理地使用函數(shù) 可以大大節(jié)省用戶的輸入時間 簡化公式的輸入 應(yīng)用函數(shù)有兩種方法 直接輸入法就是直接在工作表的單元格中輸入函數(shù)的名稱及語法結(jié)構(gòu) 插入函數(shù)法就是當(dāng)用戶在不能確定函數(shù)的拼寫時 則可使用另一種插入函數(shù)的方法來應(yīng)用函數(shù) 直接輸入法的操作非常簡單 只需先選擇要輸入函數(shù)公式的單元格 輸入 號 然后按照函數(shù)的語法直接輸入函數(shù)名稱及各參數(shù)即可 但其要求用戶必須對所使用的函數(shù)較為熟悉 并且十分了解此函數(shù)包括多少個參數(shù)及參數(shù)的類型 然后就可以像輸入公式一樣來輸入函數(shù) 使用起來也較為方便 但由于利用直接輸入法來輸入函數(shù)時 要求用戶必須了解函數(shù)的語法 參數(shù)及使用方法 而且Excel2010提供了200多種函數(shù) 因此用戶不可能全部記住 這時就可以使用插入函數(shù)法 這種方法簡單 快速 它不需要用戶的輸入 而直接插入即可使用 項目任務(wù)7 3排序數(shù)據(jù) 在實際應(yīng)用中 建立數(shù)據(jù)清單輸入數(shù)據(jù)時 人們一般是按照數(shù)據(jù)到來的選后順序輸入的 但是 當(dāng)用戶要直接從數(shù)據(jù)清單中查找所需的信息時 很不直觀 為了提高查找效率 需要重新整理數(shù)據(jù) 對此最有效的方法就是對數(shù)據(jù)進(jìn)行排序 對數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行排序是Excel最常見的應(yīng)用之一 對數(shù)據(jù)記錄進(jìn)行排序時 主要利用 排序 工具按鈕和 排序 對話框來進(jìn)行排序 如果用戶想快速地根據(jù)某一列的數(shù)據(jù)進(jìn)行排序 則可使用 數(shù)據(jù) 選項卡下的 排序和篩選 組中的排序按鈕 升序 按鈕 單擊此按鈕后 系統(tǒng)將按字母表順序 數(shù)據(jù)由小到大 日期由前到后等默認(rèn)的排列順序進(jìn)行排序 降序 按鈕 單擊此按鈕后 系統(tǒng)將反字母表順序 數(shù)據(jù)由大到小 日期由后到前等順序進(jìn)行排序 在按升序排序時 Excel2010將使用如下順序 在按降序排序時 除了空格總是在最后外 其他的排序順序反之 數(shù)字從最小的負(fù)數(shù)到最大的正數(shù)排序 文本以及包含數(shù)字的文本 按下列順序排序 先是數(shù)字0到9 然后是字符 空格 最后是字母A到Z 在邏輯值中 FALSE排在TRUE之前 所有錯誤值的優(yōu)先級等效 空格排在最后 項目任務(wù)7 4數(shù)據(jù)篩選 篩選是查找和處理數(shù)據(jù)清單中數(shù)據(jù)子集的快捷方法 篩選清單僅顯示滿足條件的行 該條件由用戶針對某列指定 篩選與排序不同 它并不重排數(shù)據(jù)清單 而只是將不必顯示的行暫時隱藏 用戶可以使用 自動篩選 或 自定義篩選 功能將那些符合條件的數(shù)據(jù)顯示在工作表中 Excel2010在篩選行時 可以對清單子集進(jìn)行編輯 設(shè)置格式 制作圖表和打印 而不必重新排列或移動 動手做1自動篩選自動篩選是一種快速的篩選方法 用戶可以通過它快速地訪問大量數(shù)據(jù) 從中選出滿足條件的記錄并將其顯示出來 隱藏那些不滿足條件的數(shù)據(jù) 此種方法只適用于條件較簡單的篩選 例如 利用 自動篩選 功能將銷售業(yè)績統(tǒng)計表中 部門 中 銷售 3 部 的記錄顯示出來 具體步驟如下 1 在銷售業(yè)績統(tǒng)計表工作簿中切換到 Sheet2 工作表 2 選中單元格 A3 J46 區(qū)域 或?qū)⑹髽?biāo)定位在數(shù)據(jù)區(qū)域的任意單元格中 3 單擊 數(shù)據(jù) 選項卡下 排序和篩選 組中的 篩選 按鈕 則在選中區(qū)域的標(biāo)題行中文本的右側(cè)出現(xiàn)一個下三角箭頭 4 單擊部門的下三角箭頭打開一個列表 在列表的 數(shù)字篩選 下面的列表中取消 全選 的選中狀態(tài) 然后選擇 銷售 3 部 5 單擊 確定 按鈕 項目任務(wù)7 4數(shù)據(jù)篩選 動手做2自定義篩選在使用 自動篩選 命令篩選數(shù)據(jù)時 還可以利用 自定義 的功能來限定一個或兩個篩選條件 以便于將更接近條件的數(shù)據(jù)顯示出來 例如 在銷售業(yè)績統(tǒng)計表中篩選出 一月 小于 60000 或大于 90000 的數(shù)據(jù) 具體步驟如下 1 單擊 數(shù)據(jù) 選項卡下 排序和篩選 組中的 清除 按鈕 清除原來的篩選結(jié)果 2 單擊 一月 右側(cè)的下三角箭頭打開一個列表 然后指向 數(shù)字篩選 出現(xiàn)一個子菜單 3 在列表中選擇 自定義篩選 選項 打開 自定義自動篩選方式 對話框 4 在左上部的 比較操作符 下拉列表中選擇 大于 在其右邊的文本框中輸入 90000 選中 或 單選按鈕 在左下部的 比較操作符 下列表中選擇 小于 在其右邊的文本框中輸入 60000 5 單擊 確定 按鈕 項目任務(wù)7 4數(shù)據(jù)篩選 動手做3篩選前10個如果用戶要篩選出最大或最小的幾項 用戶可以在篩選列表中使用 前10個 命令來完成 例如 將銷售業(yè)績統(tǒng)計表中一月最大的前5名顯示出來 具體步驟如下 1 單擊 數(shù)據(jù) 選項卡下 排序和篩選 組中的 清除 按鈕 清除原來的篩選結(jié)果 2 單擊 一月 右側(cè)的下三角箭頭打開一個列表 然后指向 數(shù)字篩選 出現(xiàn)一個子菜單 選擇 10個最大的項 選項 打開 自動篩選前10個 對話框 3 在對話框中的最左邊的下拉列表中選擇 最大 項 在中間的文本框中選擇或輸入 5 在最后邊的下拉列表中選擇 項 4 單擊 確定 按鈕 按一月字段自動篩選出排在前5名的效果如圖所示 項目任務(wù)7 5數(shù)據(jù)的分類匯總 分類匯總是對數(shù)據(jù)清單上的數(shù)據(jù)進(jìn)行分析的一種常用方法 Excel2010可以使用函數(shù)實現(xiàn)分類和匯總值計算 匯總函數(shù)有求和 計算 求平均值等多種 使用匯總命令 可以按照用戶選擇的方式對數(shù)據(jù)進(jìn)行匯總 自動建立分級顯示 并在數(shù)據(jù)清單中插入?yún)R總行和分類匯總行 動手做1創(chuàng)建分類匯總分類匯總是將數(shù)據(jù)清單中的某個關(guān)鍵字段進(jìn)行分類 相同值的分為一類 然后對各類進(jìn)行匯總 在進(jìn)行自動分類匯總之前 應(yīng)對數(shù)據(jù)清單進(jìn)行排序?qū)⒁诸愖侄蜗嗤挠涗浖性谝黄?并且數(shù)據(jù)清單的第一行里必須有列標(biāo)記 利用自動分類匯總功能可以對一項或多項指標(biāo)進(jìn)行匯總 例如 對銷售業(yè)績統(tǒng)計表中按 銷售部門 對工作表中的各各月銷售額進(jìn)行求和匯總 具體步驟如下 1 在銷售業(yè)績統(tǒng)計表工作簿中切換到 Sheet3 工作表 2 首先將 部門 字段按升序進(jìn)行排列使相同班級的記錄集中在一起 3 選中單元格 A3 J46 區(qū)域 或?qū)⑹髽?biāo)定位在數(shù)據(jù)區(qū)域的任意單元格中 4 單擊 數(shù)據(jù) 選項卡下 分級顯示 組中的 分類匯總 按鈕 打開 分類匯總 對話框 5 在 分類字段 下拉列表中選擇 部門 在 匯總方式 下拉列表中選擇 求和 在 選定匯總項 列表中選中一月份 二月份 三月份 四月份 五月份 六月份和總銷售額 6 選中 匯總結(jié)果顯示在數(shù)據(jù)下方 復(fù)選框 則將分類匯總的結(jié)果放在本類數(shù)據(jù)的最后一行 7 單擊 確定 按鈕 項目任務(wù)7 5數(shù)據(jù)的分類匯總 動手做2分級顯示數(shù)據(jù)工作表中的數(shù)據(jù)進(jìn)行分類匯總后 將會使原來的工作表顯得有些龐大 如果用戶要想單獨查看匯總數(shù)據(jù)或查看數(shù)據(jù)清單中的明細(xì)數(shù)據(jù) 最簡單的方法就是利用Excel2010提供的分級顯示功能 在對工作表數(shù)據(jù)進(jìn)行分類匯總后 匯總后的工作表在窗口處將出現(xiàn) 1 2 3 的數(shù)字 還有 大括號等 這些符號在Excel2010中稱為分級顯示符號 符號減號是 隱藏明細(xì)數(shù)據(jù) 按鈕 加號是 顯示明細(xì)數(shù)據(jù) 按鈕 單擊減號可以隱藏該級及以下各級的明細(xì)數(shù)據(jù) 單擊加號則可以展開該級明細(xì)數(shù)據(jù) 動手做3消除分級顯示數(shù)據(jù)如果要取消部分分級顯示 可先選定有關(guān)的行或列 然后單擊 數(shù)據(jù) 選項卡下的 分級顯示 組中的 取消組合 按鈕打開一個下拉列表 在下拉列表中選擇 清除分級顯示 按鈕即可 當(dāng)創(chuàng)建了分類匯總后 如果不再需要了 用戶還可以將其刪除掉 首先在分類匯總數(shù)據(jù)清單區(qū)域單擊任一單元格 單擊 數(shù)據(jù) 選項卡下 分級顯示 組中的 分類匯總 按鈕 打開 分類匯總 對話框 在 分類匯總 對話框中單擊 全部刪除 按鈕 最后單擊 確定 按鈕 關(guān)閉對話框 項目任務(wù)7 6應(yīng)用圖表 Excel2010提供的圖表功能 可以將系列數(shù)據(jù)以圖表的方式表達(dá)出來 使數(shù)據(jù)更加清晰易懂 使數(shù)據(jù)表示的含義更形象更直觀 并且用戶可以通過圖表直接了解到數(shù)據(jù)之間的關(guān)系和變化的趨勢 動手做1創(chuàng)建圖表對于一些結(jié)構(gòu)復(fù)雜的表格 用戶往往要花費相當(dāng)長的時間才能對表格中要說明的問題理出個頭緒來 既費時又費力 而如果使用Excel2010的 圖表 功能 則可以將枯燥乏味的數(shù)字轉(zhuǎn)化為圖表 從而使數(shù)據(jù)之間的關(guān)系更一目了然 無論是以何種方式建立的圖表 都與生成它們的工作表上的源數(shù)據(jù)建立了鏈接 這就意味著當(dāng)更新工作表數(shù)據(jù)時 同時也會更新圖表 例如 在銷售統(tǒng)計表中插入銷售 1 部各個銷售人員1 6月份總銷售額圖表 具體步驟如下 1 在銷售業(yè)績統(tǒng)計表工作簿中切換到 Sheet4 工作表 2 在工作表中選擇要繪制圖表的數(shù)據(jù)區(qū)域 這里選擇 銷售 1 部 的 姓名 字段的單元格與 總銷售額 字段單元格 3 單擊 插入 選項卡下 圖表 組中的 柱形圖 按鈕 彈出一個下拉列表 4 在下拉列表中選擇 二維柱形圖 的 簇狀柱形圖 按鈕即可插入圖表 項目任務(wù)7 6應(yīng)用圖表 動手做2調(diào)整圖表的大小和位置通過對圖表的大小進(jìn)行調(diào)整 可以使圖表中的數(shù)據(jù)更清晰 圖表更美觀 在銷售統(tǒng)計表中調(diào)整上面創(chuàng)建的圖表大小的具體操作步驟如下 1 將鼠標(biāo)指向創(chuàng)建的圖表 單擊鼠標(biāo) 選中圖表 2 將鼠標(biāo)移至圖表各邊中間的控制手柄上 當(dāng)鼠標(biāo)變成雙向箭頭狀時 拖動鼠標(biāo)可以改變圖表的寬度和高度 虛線框表示圖表的大小 調(diào)整到合適位置后松開鼠標(biāo) 3 將鼠標(biāo)移至四角的控制手柄上 當(dāng)鼠標(biāo)變成雙向箭頭狀時拖動鼠標(biāo)可以將圖表等比放縮 虛線框表示圖表的大小 調(diào)整到合適大小后松開鼠標(biāo) 動手做3圖表對象的選取在對圖表及圖表中的各個對象進(jìn)行操作時 用戶首先應(yīng)將其選中 然后才能對其進(jìn)行編輯操作 在選定整個圖表時 只需將鼠標(biāo)指向圖表中的空白區(qū)域 當(dāng)出現(xiàn) 圖表區(qū) 的屏幕提示時單擊鼠標(biāo)即可將其選定 選定后整個圖表四周出現(xiàn)八個句柄 此時就表示圖表被選定 被選定之后用戶就可以對整個圖表進(jìn)行移動 縮放等編輯操作了 在選定圖表中的對象時 用戶也可以利用鼠標(biāo)來進(jìn)行選定 用戶只需用鼠標(biāo)直接單擊要選定的圖表對象即可 例如 要選定圖表的標(biāo)題對象 用戶可以將鼠標(biāo)指向圖表標(biāo)題文本 當(dāng)出現(xiàn) 圖表標(biāo)題 的屏幕提示時單擊鼠標(biāo)即可選定圖表標(biāo)題 項目任務(wù)7 6應(yīng)用圖表 動手做4設(shè)置圖表區(qū)的格式可以通過為圖表區(qū)添加邊框 設(shè)置圖表中的字體 填充圖案等來修飾圖表 例如 設(shè)置 總銷售額 圖表區(qū)的格式 具體操作步驟如下 1 將鼠標(biāo)指向圖表的繪圖區(qū) 當(dāng)出現(xiàn)圖表區(qū)的屏幕提示時單擊鼠標(biāo)左鍵即可選定圖表選中圖表 2 切換到 格式 選項卡 在 形狀樣式 組中單擊 形狀填充 按鈕 在 形狀填充 列表的 紋理 列表中選擇 水滴 3 在 形狀樣式 組中單擊 形狀輪廓 按鈕 在 形狀輪廓 列表的 主題顏色 區(qū)域選擇 水綠色 強(qiáng)調(diào)顏色5 淡色40 在 粗細(xì) 列表中選擇 3磅 4 在 形狀樣式 組中單擊 形狀效果 按鈕 在 形狀效果 列表的 發(fā)光 列表中選擇 藍(lán)色 11pt發(fā)光 強(qiáng)調(diào)文字顏色1 項目任務(wù)7 6應(yīng)用圖表 動手做5設(shè)置繪圖區(qū)格式在繪圖區(qū)中 底紋在默認(rèn)情況下為白色 可以根據(jù)需要對其進(jìn)行更改 例如 在總銷售額中設(shè)置 總銷售額 圖表中繪圖區(qū)設(shè)置填充效果 具體操作步驟如下 1 將鼠標(biāo)指向圖表的繪圖區(qū) 當(dāng)出現(xiàn) 繪圖區(qū) 的屏幕提示時單擊鼠標(biāo)左鍵即可選定圖表選中圖表繪圖區(qū) 2 在 布局 選項卡下 當(dāng)前所選內(nèi)容 組中單擊 設(shè)置所選內(nèi)容格式 或在繪圖區(qū)上單擊鼠標(biāo)右鍵 在快捷菜單中選擇 設(shè)置繪圖區(qū)格式 命令 均可打開 設(shè)置繪圖區(qū)格式 對話框 3 在對話框框左側(cè)列表中選擇 填充 在右側(cè)的 填充 區(qū)域選擇 漸變填充 單選按鈕 顯示出漸變填充的一些設(shè)置按鈕 4 單擊 預(yù)設(shè)顏色 按鈕 彈出一個下拉列表 這里選擇 熊熊火焰 5 在 類型 下拉列表中選擇 線性 在 方向 列表中選擇 線性對角 在 角度 列表中設(shè)置角度為 135 6 在 漸變光圈 的顏色列表中選擇 橙色 設(shè)置光圈1的結(jié)束為止為 100 7 單擊 關(guān)閉 按鈕 關(guān)閉 設(shè)置繪圖區(qū)格式 對話框 項目任務(wù)7 6應(yīng)用圖表 動手做6設(shè)置圖表標(biāo)題格式在圖表區(qū)中的字體默認(rèn)為 宋體 10 黑色 標(biāo)題字體默認(rèn)為 宋體 18 黑色 用戶可以根據(jù)需要對其字體格式以及標(biāo)題文本進(jìn)行更改 例如 這里為銷售統(tǒng)計表中的 銷售總額 圖表標(biāo)題設(shè)置字體格式 具體操作步驟如下 1 將鼠標(biāo)指向圖表標(biāo)題 當(dāng)出現(xiàn) 圖表標(biāo)題 的屏幕提示時單擊鼠標(biāo)左鍵即可選中 圖表標(biāo)題 對象 2 將鼠標(biāo)定位在標(biāo)題中 刪除原來的標(biāo)題 總銷售額 然后輸入新的標(biāo)題 雷爍光電科技公司銷售 1 部2015年上半年銷售業(yè)績統(tǒng)計表 3 按住鼠標(biāo)左鍵拖動選中標(biāo)題文本 4 單擊開始選項卡下的 字體 組中的 顏色 下拉列表中選擇 深藍(lán) 動手做7設(shè)置圖例格式如果設(shè)置圖表對象的格式相對簡單 用戶可以在 布局 選項卡下快速進(jìn)行設(shè)置 如要設(shè)置圖例的格式用戶可以在 布局 選項卡的 標(biāo)簽 組中單擊 圖例 按鈕打開一個下拉列表 在列表中用戶可以對圖例的格式進(jìn)行簡單的設(shè)置 如這里選擇 在底部顯示圖例 選中圖例 在 開始 選項卡下 字體 組中設(shè)置圖例的字體為黑體 字號