Excel進階課程(函數(shù)).ppt

1、Excel函數(shù)在商業(yè)中的應(yīng)用,公式函數(shù)必備知識,案例：計算訂單銷售額及利潤,建立公式,相對引用 公式走哪我跟哪，跟公式的相對位置不變 如： 絕對引用 給錢我就不走了，位置絕對不移動 如：,案例：一個公式完成折扣計算表,混合引用與數(shù)組,混合引用：=C$4*$B5 行列都不鎖定:A1 行列都鎖定:$A$1 列活動行鎖定:A$1 列鎖定行活動:$A1 數(shù)組的使用 =C4:E4*B5:B7 Shift+ctrl+enter,公式中常見錯誤判斷,公式中常見錯誤判斷,商業(yè)函數(shù)的使用技能,函數(shù)語法規(guī)則,= 函數(shù)名（參數(shù)） 某些函數(shù)的參數(shù)可以省略 參數(shù)與參數(shù)之間用逗號分開“，”,常用的基本函數(shù),sum (范圍

2、)求和 average (范圍)平均數(shù) max (范圍)最大值 min (范圍)最小值 count (范圍)計數(shù) 計數(shù)字 counta (范圍)計數(shù) 非空單元格,案例：sum另類用法,一鍵填充求和區(qū)域（藍色區(qū)域）,案例：銷售訂單表,計算訂單個數(shù),邏輯函數(shù),if （邏輯判斷式，真值，假值）,案例：成績表,判斷考試等級： =210 A 級 210 B級 是否補考：三門成績必須全部大于分才不需補考,的嵌套,=20 A 級 210220 B級 210 C級 If的嵌套不能超過7層,查找函數(shù),vlookup （查詢對象，數(shù)據(jù)源，顯示序列，匹配參數(shù)） 匹配參數(shù)：false精確查找=0(推薦) true模糊

3、查找=1 顯示序列：需要顯示的數(shù)據(jù)位于數(shù)據(jù)源中第幾列,必須是數(shù)字,Vlookup數(shù)據(jù)源要求,數(shù)據(jù)源： 標準的二維數(shù)據(jù) 查詢對象必須位于數(shù)據(jù)源第一列 顯示對象必須位于數(shù)據(jù)源當中 必須是一個連續(xù)區(qū)域,案例：同一張工作表中的查詢,自動顯示所選人員的總成績,案例：不同工作表之間的查詢,案例：數(shù)據(jù)對比,查找兩張表中ID號相同的數(shù)據(jù),日期函數(shù),year (日期) month (日期) day (日期) date （年,月,日） Today() 當前日期 now()當前時間 Hour、minute、second、time datedif (起始日期，結(jié)束日期，返回類型),案例：將產(chǎn)品成交日期進行處理,成交日

4、期的年月日分開 計算成交年數(shù)，并可每年自動更新,文本函數(shù),left (文本對象，截取位數(shù)) right (文本對象，截取位數(shù)) mid(文本對象，開始位數(shù)，截取位數(shù)),案例：整理產(chǎn)品型號,將品牌和型號分開兩列顯示 使用分列功能,Excel進階課程之圖表,常見圖表的制作技巧,認識圖表商務(wù)模型,創(chuàng)建圖表的方法 一鍵生成法 按部就班法 用圖表傳遞信息的類型 常用圖表類型及選擇技巧,圖表類型的選擇,柱形圖：強調(diào)的是數(shù)據(jù)區(qū)中所有的數(shù)據(jù) 條形圖：與柱形圖強調(diào)要點相同，建議使用在橫軸為時間線時 折線圖：強調(diào)的是數(shù)據(jù)趨勢 餅圖：強調(diào)的是部分對整體的貢獻 XY散點圖：強調(diào)X軸與Y軸之間的相對關(guān)系,圖表的基本修改

5、方式,移動和調(diào)整圖表 更改圖表類型 移動圖表元素 刪除、格式化圖表元素,修改原則： 看得見的部分雙擊 看不見的單擊右鍵,圖表工具欄的使用,圖表對象：用于選擇需要在圖表中選定修改的圖表對象 圖表區(qū)格式：調(diào)出在圖表中選定的圖表對象的格式屬性 圖表類型：用于修改整個圖表或者圖表中某一數(shù)據(jù)系列的圖表類型,圖表工具欄的使用,圖例：是否顯示圖例 數(shù)據(jù)表：屬否顯示原始數(shù)據(jù) 按行/按列：圖表的數(shù)據(jù)顯示以行或列標題為分類 順時針/逆時針排列：文字的排列方式,制作柱形圖和折線圖的混合圖表,例題：根據(jù)下列數(shù)據(jù)，制作“產(chǎn)品銷售展示圖”,案例：產(chǎn)品銷售展示圖,更改圖表類型,設(shè)置次坐標軸（雙坐標軸）,雙擊整條折線，打開“

6、數(shù)據(jù)系列格式”對話框,修改坐標軸單位,雙擊次坐標軸數(shù)據(jù)，打開“坐標軸格式”對話框,在餅圖中顯示類別名稱和百分比,練習(xí)：展示三月份的三種產(chǎn)品的市場份額，并明確標示份額百分比。,圖表的美化技巧,根據(jù)下表數(shù)據(jù)，制作“奧運金牌對比圖”,案例：奧運金牌對比圖,選擇圖表類型,更改繪圖區(qū)格式,雙擊繪圖區(qū)，將邊框和填充色都設(shè)置為“無”,更改圖表區(qū)格式,雙擊圖表區(qū)，邊框設(shè)為“無”，區(qū)域進行“填充效果”設(shè)置,填充效果,刪除坐標軸、網(wǎng)格線,單擊網(wǎng)格線、橫坐標軸，刪除Delete 雙擊縱坐標軸，如圖所示，選擇“無”,修改數(shù)據(jù)系列格式,雙擊數(shù)據(jù)系列，在“圖案”中選擇橙色，邊框選“無”,增加數(shù)據(jù)系列的寬度,雙擊數(shù)據(jù)系列，

7、在“選項”中減少“分類間距”,加寬坐標軸文字與圖形間距,雙擊坐標軸，在“對齊”選項卡中增加“偏移量”數(shù)值,后期處理,相應(yīng)位置添加圖片和文本框，修改格式,Excel商業(yè)數(shù)據(jù)處理與分析,數(shù)據(jù)清單的應(yīng)用與篩選,預(yù)備知識,條件格式 在設(shè)定單元格的格式時，可以使用條件判斷的方式，使單元格自動進行格式更改。 菜單：格式條件格式 有效性 用于限定可以在單元格中錄入的對象的范圍 菜單：數(shù)據(jù)有效性,案例：支出證明單的條件格式,金額出填寫不同范圍的數(shù)據(jù)，字體顏色不同,案例：支出證明單有效性限定,金額必須填寫01000元之間的數(shù)字 鼠標放在填寫金額的單元格內(nèi)，自動出現(xiàn)提示信息 填寫錯誤將彈出“出錯警告”,案例：企業(yè)

8、員工檔案數(shù)據(jù)清單,為簡化錄入，請在“部門”列中制作下拉菜單 Alt+ 數(shù)據(jù)有效性,案例：突出顯示高工資人員,工資金額在5500元以上的人員名稱自動變紅色,案例：限制銷售日期的有效性,銷售業(yè)績表銷售日期，只能填寫2008-4-1到2008-6-1之間的數(shù)據(jù),數(shù)據(jù)的排序,案例：拼音排序,員工檔案表按姓名的漢語拼音順序排序,案例：筆劃排序,員工檔案表按姓名的筆劃排序（中文版）,案例：自定義排序（一）,企業(yè)員工檔案數(shù)據(jù)清單表按照學(xué)位高低排序,案例：自定義排序（二）,工具選項自定義序列 數(shù)據(jù)排序選項自定義序列,利用篩選快速查找數(shù)據(jù),自動篩選,自定義:等于、不等于、包含 “與”“或”的關(guān)系 前10個 全部

9、顯示,Subtotal函數(shù)介紹,subtotal(運算類型，范圍),數(shù)據(jù)透視分析,數(shù)據(jù)源的要求,不能合并單元格 第一行必須是字段名，并且不能為空。 數(shù)據(jù)源數(shù)據(jù)可以為空，但不能空整行整列。 同一列的數(shù)據(jù)必須是同種類型的數(shù)據(jù)。 刪除所有匯總數(shù)據(jù)，刪除分類匯總。,創(chuàng)建員工檔案數(shù)據(jù)透視,建立數(shù)據(jù)透視表 設(shè)置數(shù)據(jù)透視表格式 多角度查看數(shù)據(jù)透視表 報表分成兩大類： 行字段、列字段、頁字段：放置分類項 數(shù)據(jù)項：放置匯總數(shù)據(jù),企業(yè)銷售業(yè)績的多指標分析,設(shè)置排序 分類匯總數(shù)據(jù) 添加新字段 隱藏部分字段 更新數(shù)據(jù)區(qū)域 組合分析數(shù)據(jù),分類匯總數(shù)據(jù),每個城市，各產(chǎn)品的銷售總額，二維表查看,隱藏部分字段,刷新數(shù)據(jù),更新數(shù)據(jù)區(qū)域,當數(shù)據(jù)源范圍發(fā)生變化后，可進入向?qū)У诙街匦逻x擇數(shù)據(jù)源,數(shù)據(jù)透視圖的編輯,查看每種材料，各型號的采購數(shù)量匯總圖 （1）先制作透視表 （2）單擊工具欄中的,部門采購情況的透視圖分析,數(shù)據(jù)透視圖的編輯 透視圖類型的切換 餅圖塊的拆分、修改記錄 透視圖的靜態(tài)轉(zhuǎn)化,數(shù)據(jù)透視圖的編輯,查看每種材料，各型號的采購數(shù)量匯總圖 （1）再制作透視圖：,透視圖類型的切換,右鍵-圖表類型-餅圖,餅圖塊的拆分、修改記錄,雙擊-添加數(shù)據(jù)標志-類別名稱和百分比,透視圖的靜態(tài)轉(zhuǎn)化,刪除數(shù)據(jù)透視表 隱藏字段按鈕,多人合作文檔,專業(yè)報告制作美化技