《Excel數(shù)據(jù)處理與可視化》（第二版） 課件 韓春玲 第3部分 數(shù)據(jù)統(tǒng)計分析；第4部分 數(shù)據(jù)可視化

第3部分 數(shù)據(jù)統(tǒng)計分析----3.1數(shù)據(jù)查詢和匹配3.1數(shù)據(jù)查詢和匹配3.1.1查找得票最多的姓名公式“=IFERROR(INDEX(B$2:B$16,SMALL(MODE.MULT(MATCH(B$2:B$16,B$2:B$16,)),ROW(A1))),"")”3.1數(shù)據(jù)查詢和匹配3.1.2查找開獎號碼對應(yīng)的數(shù)字公式“=IF(COUNT(FIND(C$1,$A2)),C$1,"")”3.1數(shù)據(jù)查詢和匹配3.1.3何提取前3名銷量數(shù)據(jù)重復(fù)公式“=LARGE(IF(FREQUENCY($B$2:$B$13,$B$2:$B$13),$B$2:$B$13),ROW(A1))”數(shù)據(jù)不重復(fù)：3.1數(shù)據(jù)查詢和匹配3.1.5去掉最后一個特殊符號及以后內(nèi)容第1種方法：快速填充。第2種方法：公式法公式“=MID(A2,1,LOOKUP(9^9,FIND("-",A2,ROW($1:$30)))-1)”3.1數(shù)據(jù)查詢和匹配3.1.4提取訂貨量對應(yīng)的訂貨型號公式“=INDIRECT(ADDRESS(2,MATCH(LARGE(D4:G4,1),4:4,0)))”3.1數(shù)據(jù)查詢和匹配3.1.6兩列商品型號排序不一，對應(yīng)數(shù)量如何相減公式“=B2-IFERROR(VLOOKUP(A2,$E$2:$F$9,2,0),0)3.1數(shù)據(jù)查詢和匹配3.1.7利用VLOOKUP函數(shù)實現(xiàn)多表數(shù)據(jù)合并查詢公式“=VLOOKUP($B2,INDIRECT($A2&"!a:d"),COLUMN(B1),0)”3.1數(shù)據(jù)查詢和匹配3.1.8VLOOKUP+MATCH，輕松查找數(shù)據(jù)公式“=VLOOKUP(A13,A1:E10,MATCH(B12,A1:E1,0),0)”3.1數(shù)據(jù)查詢和匹配3.1.9滿足條件的數(shù)據(jù)自動“跑”到其它工作表公式“=INDEX(全部!A:A,SMALL(IF(全部!$F:$F="已對",ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&""”3.1數(shù)據(jù)查詢和匹配3.1.10INDEX+MATCH函數(shù)組合應(yīng)用——查找業(yè)績前幾名員工姓名公式“=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))”3.1數(shù)據(jù)查詢和匹配3.1.11INDEX+MATCH函數(shù)組合應(yīng)用——提取行列交叉單元格數(shù)值公式“=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0))”3.1數(shù)據(jù)查詢和匹配3.1.12INDEX+MATCH函數(shù)組合應(yīng)用——提取整行整列數(shù)據(jù)公式“=INDEX(B2:E10,MATCH(A13,A2:A10,0),0)”，按Ctrl+Shift+Enter組合建執(zhí)行計算3.1數(shù)據(jù)查詢和匹配3.1.13利用OFFSET函數(shù)在大量數(shù)據(jù)中查找指定數(shù)據(jù)公式“=IF(COUNTIF(OFFSET(D5,0,0,500,500),A4)<>0,"有","無")”3.1數(shù)據(jù)查詢和匹配3.1.14利用OFFSET函數(shù)在動態(tài)區(qū)域中查找指定數(shù)據(jù)公式“=IF(COUNTIF(OFFSET($D$6,0,0,COUNTA($D:$D),COUNTA($6:$6)),A4)<>0,"有","無")”3.1數(shù)據(jù)查詢和匹配3.1.15跨表查詢指定顧客的購買記錄公式“=INDEX(購買記錄表!B:B,SMALL(IF(購買記錄表!$B$2:$B$12=購買查詢!$B$1,ROW(購買記錄表!$B$2:$B$12),ROWS(購買記錄表!B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter組合建執(zhí)行計算3.1數(shù)據(jù)查詢和匹配3.1.16在大量信息中快速查找哪些員工信息不完整第1種方法：定位法。第2種方法：公式法。3.1數(shù)據(jù)查詢和匹配3.1.17利用OFFSET函數(shù)提取最大銷量整列信息公式“=OFFSET($A$2,ROW(A1)-1,MATCH(MAX($B$5:$K$5),$B$5:$K$5,0))”3.1數(shù)據(jù)查詢和匹配3.1.18利用OFFSET函數(shù)提取前三位銷量整列信息公式“=OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0))”3.1數(shù)據(jù)查詢和匹配3.1.19提取銷售量最大的月份公式“=OFFSET($A$1,,MATCH(MAX(B2:M2),B2:M2,0))”3.1數(shù)據(jù)查詢和匹配3.1.20提取銷售量第一、二、三位的月份公式“=OFFSET($A$1,,MATCH(LARGE($B2:$M2,COLUMN(A$1)),$B2:$M2,0))”3.1數(shù)據(jù)查詢和匹配3.1.21如何給相同姓名添加相同編號1．相同姓名有序排列。第1步，在A2單元格中輸入編號1。第1步，在A3單元格中輸入公式“=IF(B3=B2,A2,A2+1)”，按Enter鍵執(zhí)行計算，再將公式向下填充，即得結(jié)果3.1數(shù)據(jù)查詢和匹配3.1.21如何給相同姓名添加相同編號2．姓名無序排列。公式“=IFERROR(VLOOKUP(G2,IF({1,0},G$1:G1,F$1:F1),2,0),N(F1)+1)”3.1數(shù)據(jù)查詢和匹配3.1.22INDEX+SMALL函數(shù)組合完成一對多查找公式“=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter組合建執(zhí)行計算3.1數(shù)據(jù)查詢和匹配3.1.23VLOOKUP與IFERROR函數(shù)組合屏蔽查找錯誤值公式“=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$12,2,0),"")”3.1數(shù)據(jù)查詢和匹配3.1.24如何查找主單號對應(yīng)的子單號，且子單號同行顯示方法1：輔助列+VLOOKUP函數(shù)。3.1數(shù)據(jù)查詢和匹配3.1.24如何查找主單號對應(yīng)的子單號，且子單號同行顯示方法2：公式“=IFERROR(VLOOKUP($D3&COLUMN(A$1),IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("A2:A"&ROW($2:$13)),$D3),$B$2:$B$13),2,0),"")”3.1數(shù)據(jù)查詢和匹配3.1.25利用公式從地址中取省級行政區(qū)公式“=LEFT(A2,MIN(FIND({"省","市","區(qū)"},A2&"省市區(qū)")))”3.1數(shù)據(jù)查詢和匹配3.1.26Vlookup+IF函數(shù)、Vlookup+CHOOSE函數(shù)實現(xiàn)逆向查詢1．VLOOKUP+IF函數(shù)實現(xiàn)逆向查詢3.1數(shù)據(jù)查詢和匹配3.1.26Vlookup+IF函數(shù)、Vlookup+CHOOSE函數(shù)實現(xiàn)逆向查詢2．VLOOKUP+CHOOSE函數(shù)實現(xiàn)逆向查詢3.1數(shù)據(jù)查詢和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定IF函數(shù)：公式“=IF(B2<60,"不合格",IF(B2<70,"合格",IF(B2<85,"良好","優(yōu)秀")))”3.1數(shù)據(jù)查詢和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定VLOOKUP函數(shù)：公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"優(yōu)秀"},2)”3.1數(shù)據(jù)查詢和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定LOOKUP函數(shù)：公式“=LOOKUP(B2,{0,60,70,85},{"不合格";"合格";"良好";"優(yōu)秀"})”3.1數(shù)據(jù)查詢和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定CHOOSE+MATCH函數(shù)：公式“=CHOOSE(MATCH(B2,{0,60,70,85},1),"不合格","合格","良好","優(yōu)秀")”3.1數(shù)據(jù)查詢和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等級評定INDEX+MATCH函數(shù)：公式“=INDEX({"不合格";"合格";"良好";"優(yōu)秀"},MATCH(B2,{0,60,70,80},1))”3.1數(shù)據(jù)查詢和匹配3.1.28利用VLOOKUP函數(shù)查詢同一部門多個員工信息第1步：建立輔助列。第2步：公式實現(xiàn)。3.1數(shù)據(jù)查詢和匹配3.1.29利用VLOOKUP函數(shù)查詢一種產(chǎn)品多次進貨量公式“=IFERROR(VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:$B$9&COUNTIF(INDIRECT("b2:b"&ROW($2:$9)),$B$12),$C$2:$C$9),2,0),"")”，按Ctrl+Shift+Enter三鍵組合3.1數(shù)據(jù)查詢和匹配3.1.30六個多條件查詢的函數(shù)第3部分 數(shù)據(jù)統(tǒng)計分析----3.2數(shù)據(jù)統(tǒng)計3.2數(shù)據(jù)統(tǒng)計3.2.1根據(jù)等級計算總成績公式“=SUM(SUMIF($I$2:$I$5,B2:F2,$J$2:$J$5))”，按Ctrl+Shift+Enter組合鍵執(zhí)行計算3.2數(shù)據(jù)統(tǒng)計3.2.2依據(jù)評分標準，折算男女同學(xué)體育分數(shù)公式“=IF(G3="男",LOOKUP(H3,$B$4:$B$17,$A$4:$A$17),LOOKUP(H3,$D$4:$D$17,$C$4:$C$17))”3.2數(shù)據(jù)統(tǒng)計3.2.3統(tǒng)計除請假以外參與考核的部門人數(shù)公式“=SUMPRODUCT((B2:B15=E2)*ISNUMBER(C2:C15))”3.2數(shù)據(jù)統(tǒng)計3.2.4根據(jù)規(guī)定好的占比劃分成績等級公式“=LOOKUP(PERCENTRANK.INC(C:C,C2),{0,10,40,80}%,{"D","C","B","A"})”3.2數(shù)據(jù)統(tǒng)計3.2.4根據(jù)規(guī)定好的占比劃分成績等級公式“=LOOKUP(PERCENTRANK.INC(C:C,C2),{0,10,40,80}%,{"D","C","B","A"})”成績由高到低劃分A、B、C、D四個等級，而且規(guī)定好了各等級所占的人數(shù)百分比：等級A占20%，等級B占40%，等級C占30%，等級D占10%3.2數(shù)據(jù)統(tǒng)計3.2.5利用公式填寫金額收據(jù)公式“=LEFT(RIGHT("￥"&ROUND($A2,2)*100,12-COLUMN(A:A)))”3.2數(shù)據(jù)統(tǒng)計3.2.6正值負值分別求和，盈虧情況一目了然負值求和正值求和3.2數(shù)據(jù)統(tǒng)計3.2.7算算每戶有幾口人公式“=IF(A2="戶主",COUNTA(B2:B15)-SUM(C3:C15),"")”3.2數(shù)據(jù)統(tǒng)計3.2.8依據(jù)收費標準，計算不同地區(qū)不同重量快遞費用公式“=SUMPRODUCT(INDEX($B$2:$C$8,MATCH("*"&LEFT(F2,2)&"*",$A$2:$A$8,),)*IF({1,0},1,INT(G2-0.01)))”3.2數(shù)據(jù)統(tǒng)計3.2.9多人分組完成多個項目，統(tǒng)計每個人參與了哪些項目公式“=IFERROR(INDEX($A$1:$A$7,SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1))),"")，按Ctrl+Shift+Enter組合建執(zhí)行計算3.2數(shù)據(jù)統(tǒng)計3.2.10同一單元格中多個姓名，如何統(tǒng)計總?cè)藬?shù)公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1”3.2數(shù)據(jù)統(tǒng)計3.2.11員工姓名和業(yè)績擠在一個單元格中，怎樣統(tǒng)計業(yè)績最大值公式“=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))”,按Ctrl+Shift+Enter組合建執(zhí)行計算3.2數(shù)據(jù)統(tǒng)計3.2.12巧用ROW函數(shù)統(tǒng)計前N名數(shù)據(jù)前三名業(yè)績和：=SUM(LARGE(B2:B37,{1,2,3}))前十名業(yè)績和：=SUM(LARGE(B2:B37,ROW(1:10)))，按Ctrl+Shift+Enter組合建執(zhí)行計算。前十名平均業(yè)績：=AVERAGE(LARGE(B2:B37,ROW(1:10)))，按Ctrl+Shift+Enter組合建執(zhí)行計算。3.2數(shù)據(jù)統(tǒng)計3.2.13全年缺勤表，排除重復(fù)項統(tǒng)計出每月缺勤人數(shù)公式“=SUMPRODUCT((MONTH($A$3:$A$30)=D3)*(MATCH((MONTH($A$3:$A$30)=D3)&$B$3:$B$30,(MONTH($A$3:$A$30)=D3)&$B$3:$B$30,0)=ROW($3:$30)-2))，按Ctrl+Shift+Enter組合建執(zhí)行計算3.2數(shù)據(jù)統(tǒng)計3.2.14巧用動態(tài)區(qū)域統(tǒng)計累計情況1．當(dāng)月完成計劃情況。2．累積計劃。3.2數(shù)據(jù)統(tǒng)計3.2.14巧用動態(tài)區(qū)域統(tǒng)計累計情況3．累積銷量。4．累積完成計劃情況。3.2數(shù)據(jù)統(tǒng)計3.2.15如何根據(jù)銷售額分段提成標準計算累進提成1．添加輔助列。2．公式計算。3.2數(shù)據(jù)統(tǒng)計3.2.16統(tǒng)計代碼含有指定數(shù)字的商品銷量總量1．首位為4的商品銷售總量。2．首位為4尾數(shù)為6的商品銷售總量。3.2數(shù)據(jù)統(tǒng)計3.2.17利用LEN+SUBSTITUTE函數(shù)，計算員工參與項目數(shù)公式“=(LEN(B2)-LEN(SUBSTITUTE(B2,”、“,))+1)*(B2<>”“)”3.2數(shù)據(jù)統(tǒng)計3.2.18按不同字體或是背景顏色統(tǒng)計數(shù)值第2步：提取字體顏色：第1步：建立名稱。第3步：統(tǒng)計。1．不用顏色數(shù)值統(tǒng)計。3.2數(shù)據(jù)統(tǒng)計3.2.18按不同字體或是背景顏色統(tǒng)計數(shù)值第2步：提取背景顏色。第1步：建立名稱。第3步：統(tǒng)計。2．不同背景數(shù)值統(tǒng)計。第3部分 數(shù)據(jù)統(tǒng)計分析----3.3日期、時間范圍統(tǒng)計3.3 日期、時間范圍統(tǒng)計3.3.1制作按年月自動變化的考勤表表頭公式“=IF(MONTH(DATE($B$3,$E$3,COLUMN(A6)))=$E$3,DATE($B$3,$E$3,COLUMN(A6)),"")”3.3 日期、時間范圍統(tǒng)計3.3.2設(shè)置考勤表周六周日列自動變色且自動統(tǒng)計工作日與周末加班時長1．周六周日列自動變色2．自動統(tǒng)計工作日與周末加班時長3.3 日期、時間范圍統(tǒng)計3.3.3制作日期豎排的考勤表表頭第1步：設(shè)置單元格格式。第2步：輸入公式計算日期。第3步：輸入公式計算星期。第4步：條件格式突出周末。3.3 日期、時間范圍統(tǒng)計3.3.4日期與時間分離的三種方法方法1：分列。方法2：INT函數(shù)。方法3：TEXT函數(shù)。3.3 日期、時間范圍統(tǒng)計3.3.5利用SUM+OFFSET函數(shù)查詢產(chǎn)品指定月份期間的銷量合計公式“=SUM(OFFSET(A1,MATCH(A13,A2:A9,0),B13,1,C13-B13+1))”,3.3 日期、時間范圍統(tǒng)計3.3.6利用LOOKUP+DATEDIF函數(shù)計算賬齡公式“=LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,6,12,24},{"6個月內(nèi)","6-12個月","1-2年","超過2年"})”3.3 日期、時間范圍統(tǒng)計3.3.7根據(jù)出生日期制作員工生日提醒公式“=TEXT(7-DATEDIF(B2-7,TODAY(),"YD"),"0天后生日;;今天生日")”3.3 日期、時間范圍統(tǒng)計3.3.82018年的2月有29日嗎公式“=IF(OR(AND(MOD(D2,4)=0,MOD(D2,100)<>0),MOD(D2,400)=0),"閏年","平年")”3.3 日期、時間范圍統(tǒng)計3.3.9判斷會員是否可以升級到VIP公式“=IF(AND(TODAY()-B2>=365,C2>=500),"VIP會員","普通會員")”3.3 日期、時間范圍統(tǒng)計3.3.10白班中班跨日夜班，如何用公式計算工作時長公式“=D2+(D2<C2)-C2”3.3 日期、時間范圍統(tǒng)計3.3.11計算加班時長與補助工資公式“=D2-C2”“自定義”設(shè)置為“h"小時"mm"分鐘"”公式“=HOUR(E2)*100+INT(MINUTE(E2)/15)*25”3.3 日期、時間范圍統(tǒng)計3.3.12SUMIFS函數(shù)按月統(tǒng)計產(chǎn)品銷量第1步：TEXT函數(shù)建立輔助列。第2步：利用SUMIFS函數(shù)完成統(tǒng)計。3.3 日期、時間范圍統(tǒng)計3.3.13計算指定年份與月份的銷售總額公式“=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15))”3.3 日期、時間范圍統(tǒng)計3.3.14根據(jù)入職時間計算帶薪年假天數(shù)公式““=IF(DATEDIF(A2,TODAY(),"y")<1,0,IF(DATEDIF(A2,TODAY(),"y")<10,5,IF(DATEDIF(A2,TODAY(),"y")<20,10,15)))”第1種方法：IF函數(shù)3.3 日期、時間范圍統(tǒng)計3.3.14根據(jù)入職時間計算帶薪年假天數(shù)第2種方法：LOOKUP函數(shù)。公式“=LOOKUP(DATEDIF(A2,TODAY(),"y"),{0,1,10,20},{0,5,10,15})”第3部分 數(shù)據(jù)統(tǒng)計分析----3.4數(shù)據(jù)透視表3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用1．更改匯總方式。3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用2．?dāng)?shù)值排序3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用3．分組統(tǒng)計3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用4．按年季月匯總3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用5．篩選3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用6．篩選前幾項3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用7．添加列字段3.4 數(shù)據(jù)透視表3.4.1數(shù)據(jù)透視表八項基本應(yīng)用8．生成相應(yīng)的數(shù)據(jù)透視圖3.4 數(shù)據(jù)透視表3.4.2巧用數(shù)據(jù)透視表批量生成合并單元格第1步：添加輔助列。第2步：生成并設(shè)置數(shù)據(jù)透視表。第3步：格式刷刷出合并單元格3.4 數(shù)據(jù)透視表3.4.3利用OFFSET函數(shù)定義名稱，實現(xiàn)數(shù)據(jù)透視表動態(tài)更新第1步：定義名稱第2步：插入數(shù)據(jù)透視表3.4 數(shù)據(jù)透視表3.4.4利用數(shù)據(jù)透視表對數(shù)據(jù)進行分段統(tǒng)計第1步：建立數(shù)據(jù)透視表。3.4 數(shù)據(jù)透視表3.4.4利用數(shù)據(jù)透視表對數(shù)據(jù)進行分段統(tǒng)計第2步：分數(shù)值分組。3.4 數(shù)據(jù)透視表3.4.4利用數(shù)據(jù)透視表對數(shù)據(jù)進行分段統(tǒng)計第3步：添加分數(shù)段比例。3.4 數(shù)據(jù)透視表3.4.5巧用數(shù)據(jù)透視表快速拆分工作表第1步：插入數(shù)據(jù)透視表。3.4 數(shù)據(jù)透視表3.4.5巧用數(shù)據(jù)透視表快速拆分工作表第2步：設(shè)計數(shù)據(jù)透視表。第3部分 數(shù)據(jù)統(tǒng)計分析----3.5數(shù)學(xué)專業(yè)分析3.5數(shù)學(xué)專業(yè)分析3.5.1新個稅計算的三個公式第1種方法：IF函數(shù)。公式“=IF((B12-5000)<0,0,IF((B12-5000)<=3000,(B12-5000)*0.03,IF((B12-5000)<=12000,(B12-5000)*0.1-210,IF((B12-5000)<=25000,(B12-5000)*0.2-1420,IF((B12-5000)<=35000,(B12-5000)*0.25-2660,IF((B12-5000)<=55000,(B12-5000)*0.35-4410,IF((B12-5000)<=80000,(B12-5000)*0.35-7160,(B12-5000)*0.45-15160)))))))”3.5數(shù)學(xué)專業(yè)分析3.5.1新個稅計算的三個公式第2種方法：LOOKUP函數(shù)。公式“=IF(B12<5000,0,LOOKUP(B12-5000,$C$2:$C$8,(B12-5000)*$D$2:$D$8-$E$2:$E$8))”3.5數(shù)學(xué)專業(yè)分析3.5.1新個稅計算的三個公式第3種方法：MAX函數(shù)。公式“=MAX((B12-5000)*$D$2:$D$8-$E$2:$E$8,0)”,按Ctrl+Shift+Enter組合鍵執(zhí)行計算3.5數(shù)學(xué)專業(yè)分析3.5.2如何分析共享單車各站點的借車與還車高峰時段第1步：設(shè)計各站點與時間段表格第2步：公式實現(xiàn)1、保留不重復(fù)的站點名稱2、設(shè)置單元格格式只在數(shù)字后加上單位“時”，單元格本身還是只有數(shù)值3.5數(shù)學(xué)專業(yè)分析3.5.3中了五千萬大獎，Excel來幫忙優(yōu)化，夢想可以達成幾件第2步：加載規(guī)劃求解項第3步：實現(xiàn)規(guī)劃求解。3.5數(shù)學(xué)專業(yè)分析3.5.3中了五千萬大獎，Excel來幫忙優(yōu)化，夢想可以達成幾件第2步：加載規(guī)劃求解項3.5數(shù)學(xué)專業(yè)分析3.5.3中了五千萬大獎，Excel來幫忙優(yōu)化，夢想可以達成幾件第3步：實現(xiàn)規(guī)劃求解。3.5數(shù)學(xué)專業(yè)分析3.5.4身份證號碼統(tǒng)計易出現(xiàn)的錯誤在篩選條件的身份證后面加上“*”3.5數(shù)學(xué)專業(yè)分析3.5.5INDEX、RANDBETWEEN、RAND函數(shù)隨機分組第1種方法：INDEX+RANDBETWEEN函數(shù)。公式“=INDEX({"第一組","第二組","第三組"},RANDBETWEEN(1,3))”3.5數(shù)學(xué)專業(yè)分析3.5.5INDEX、RANDBETWEEN、RAND函數(shù)隨機分組第2種方法：RAND函數(shù)。公式“=RAND()”3.5數(shù)學(xué)專業(yè)分析3.5.6TREND函數(shù)預(yù)測交易額公式“=TREND(B2:B6,A2:A6,A7,TRUE)”第4部分 數(shù)據(jù)可視化----4.1條件展現(xiàn)4.1條件展現(xiàn)4.1.1條件格式靈活使用公式，單條件多條件設(shè)置隨你所欲1、單條件設(shè)置4.1條件展現(xiàn)4.1.1條件格式靈活使用公式，單條件多條件設(shè)置隨你所欲2、雙條件設(shè)置4.1條件展現(xiàn)4.1.2自定義奇偶行顏色，數(shù)據(jù)不再眼花繚亂“條件格式”中輸入“=mod(row(),2)=1”4.1條件展現(xiàn)4.1.3數(shù)據(jù)增減，顏色始終追隨匯總行（1）“開始”→“條件格式”→“新建規(guī)則”（2）單擊“新建格式規(guī)則”→“使用公式確定要設(shè)置格式的單元格”，并在“為符合此公式的值設(shè)置格式”文本框中輸入公式“=ISNUMBER(FIND("小計",$A1))”。（3）單擊“格式”按鈕，設(shè)置單元格格式填充一種背景顏色。4.1條件展現(xiàn)4.1.4身份證位數(shù)輸入錯誤，自動變色提示公式“=AND(LEN(A1)<>15,LEN(A1)<>18,A1<>"")”4.1條件展現(xiàn)4.1.5完全相同的行填充相同顏色公式“=SUMPRODUCT(($A$1:$A$15=$A1)*($B$1:$B$15=$B1)*($C$1:$C$15=$C1)*($D$1:$D$15=$D1)*($E$1:$E$15=$E1))>1”4.1條件展現(xiàn)4.1.6突出顯示兩個工作表中完全相同的行Sheet1“條件格式”中輸入公式“=SUMPRODUCT((Sheet2!$A$2:$A$12=$A2)*(Sheet2!$B$2:$B$12=$B2))>=1”Sheet2“條件格式”中輸入公式“=SUMPRODUCT((Sheet1!$A$2:$A$15=$A2)*(Sheet1!$B$2:$B$15=$B2))>=1”4.1條件展現(xiàn)4.1.7繳費的行設(shè)置為綠色“條件格式”中輸入公式“=$D2<>""”4.1條件展現(xiàn)4.1.8條件格式：高亮顯示你要查看的商品“條件格式”中輸入公式“=$B2=$E$1”4.1條件展現(xiàn)4.1.9用不同顏色突出顯示排名前三的數(shù)值“條件格式”中輸入公式“=B2=MAX($B2:$M2)”以突出顯示最大值為例4.1條件展現(xiàn)4.1.10根據(jù)貨號設(shè)置間隔色“條件格式”中輸入公式“=MOD(INT(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2))),2)”4.1條件展現(xiàn)4.1.11所有員工的工資表，設(shè)置僅能查看本人信息第1步：建立公式查詢。輸入公式“=IFERROR(INDEX($A$9:$G$24,MATCH($C$2,$C$9:$C$24,0),COLUMN()),"")”4.1條件展現(xiàn)4.1.11所有員工的工資表，設(shè)置僅能查看本人信息第2步：設(shè)置員工工資表不可見。4.1條件展現(xiàn)4.1.11所有員工的工資表，設(shè)置僅能查看本人信息第3步：設(shè)置工作表保護。4.1條件展現(xiàn)4.1.12商品到期了，文字提醒不醒目，加個圖標1、文字提醒。C2單元格中輸入公式“=IF(B2=TODAY(),"到期",IF(B2<TODAY(),"超期",IF(B2-TODAY()<=3,"即將到期","未到期")))”4.1條件展現(xiàn)4.1.12商品到期了，文字提醒不醒目，加個圖標2、圖標提醒。第1步：計算距離今天的天數(shù)。第2步：設(shè)置圖標。第3步：隱藏天數(shù)。4.1條件展現(xiàn)4.1.13實現(xiàn)智能添加單元格邊框“條件格式”中輸入公式“=A1<>""”4.1條件展現(xiàn)4.1.14突出顯示多次考核業(yè)績不合格的員工姓名“條件格式”中輸入公式“=COUNTIF($B3:$G3,"<6")>=3”4.1條件展現(xiàn)4.1.15標記兩組中不同的數(shù)據(jù)“條件格式”中輸入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”4.1條件展現(xiàn)4.1.16計算合同到期天數(shù)，并設(shè)置“交通三色燈”提醒1、根據(jù)合同簽訂日期與期限計算到期日公式“=EDATE(C2,D2)-1”4.1條件展現(xiàn)4.1.16計算合同到期天數(shù)，并設(shè)置“交通三色燈”提醒2、計算距離到期日天數(shù)公式“=IF(E2>=TODAY(),DATEDIF(TODAY(),E2,"d"),"合同到期")”4.1條件展現(xiàn)4.1.16計算合同到期天數(shù)，并設(shè)置“交通三色燈”提醒3、用“三色交通燈”提醒到期天數(shù)。第4部分 數(shù)據(jù)可視化----4.2典型圖表及應(yīng)用4.2 典型圖表及應(yīng)用4.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第1步：添加輔助列。

第2步：插入堆積柱形圖第3步：切換行/列4.2 典型圖表及應(yīng)用4.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第4步：設(shè)置數(shù)據(jù)區(qū)“無填充”。第5步：美化圖表。4.2 典型圖表及應(yīng)用4.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第4步：設(shè)置數(shù)據(jù)區(qū)“無填充”。第5步：美化圖表。4.2 典型圖表及應(yīng)用4.2.2數(shù)量對比圖，加一條線突顯效果第1步：加一個輔助列第2步：生成圖表。4.2 典型圖表及應(yīng)用4.2.3甘特圖——項目進度清晰可見4.2 典型圖表及應(yīng)用4.2.3甘特圖——項目進度清晰可見第1步：將項目開始時間設(shè)置為“常規(guī)”格式。第2步：插入圖表。4.2 典型圖表及應(yīng)用4.2.3甘特圖——項目進度清晰可見第3步：選擇合適的圖表樣式。第4步：設(shè)置“開始時間”系列為無填充4.2 典型圖表及應(yīng)用4.2.3甘特圖——項目進度清晰可見第5步：修改橫坐標。第6步：修改縱坐標。第7步：美化圖表。4.2 典型圖表及應(yīng)用4.2.4旭日圖——體現(xiàn)數(shù)據(jù)層次及占比的最好圖表第1步：插入“旭日圖”4.2 典型圖表及應(yīng)用4.2.4旭日圖——體現(xiàn)數(shù)據(jù)層次及占比的最好圖表第2步：選擇圖表樣式。第3步：美化圖表。4.2 典型圖表及應(yīng)用4.2.5帶數(shù)值或極值的圖表1、添加數(shù)值4.2 典型圖表及應(yīng)用4.2.5帶數(shù)值或極值的圖表2、添加極值第1步：添加輔助列。第2步：添加圖表。4.2 典型圖表及應(yīng)用4.2.5帶數(shù)值或極值的圖表2、添加極值第3步：更改系列圖表類型第4步：修飾“最大”數(shù)據(jù)系列。4.2 典型圖表及應(yīng)用4.2.6利用模板快速制作N多工作表數(shù)據(jù)分析圖表第1步：插入“簇狀柱形圖”第2步：構(gòu)成圖表的有兩組數(shù)據(jù)4.2 典型圖表及應(yīng)用4.2.6利用模板快速制作N多工作表數(shù)據(jù)分析圖表第3步：另存為模板4.2 典型圖表及應(yīng)用4.2.7如何制作表達數(shù)據(jù)簡潔醒目的迷你圖1．折線迷你圖。4.2 典型圖表及應(yīng)用4.2.7如何制作表達數(shù)據(jù)簡潔醒目的迷你圖2．柱形迷你圖。4.2 典型圖表及應(yīng)用4.2.7如何制作表達數(shù)據(jù)簡潔醒目的迷你圖3．盈虧迷你圖。4.2 典型圖表及應(yīng)用4.2.8如何做出雙坐標軸圖表第1步：插入圖表。4.2 典型圖表及應(yīng)用4.2.8如何做出雙坐標軸圖表第2步：次坐標軸。4.2 典型圖表及應(yīng)用4.2.9圖表更新的三個方法方法1：復(fù)制粘貼。4.2 典型圖表及應(yīng)用4.2.9圖表更新的三個方法方法2：鼠標拖拽。4.2 典型圖表及應(yīng)用4.2.9圖表更新的三個方法方法3：套用表格格式。4.2 典型圖表及應(yīng)用4.2.10圖表，如何翻轉(zhuǎn)打開“設(shè)置坐標軸格式”窗格，“坐標軸選項”中勾選“逆序刻度值”，第4部分 數(shù)據(jù)可視化----4.3動態(tài)圖表4.3 動態(tài)圖表3.2.1產(chǎn)品銷量對比圖：六個月份六行顯示

第2步：“數(shù)據(jù)”→“數(shù)據(jù)驗證”4.3 動態(tài)圖表3.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第3步：創(chuàng)建新名稱，指向G1選擇的車間名稱對應(yīng)的數(shù)值區(qū)域4.3 動態(tài)圖表3.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第4步：創(chuàng)建第一車間各季度數(shù)據(jù)柱形圖，4.3 動態(tài)圖表3.2.1產(chǎn)品銷量對比圖：六個月份六行顯示第5步，完成系列名稱設(shè)置。4.3 動態(tài)圖表4.3.2制作帶控件動態(tài)圖表第1步：制作第一個倉庫的產(chǎn)品圖表。

第2步：插入控件4.3 動態(tài)圖表4.3.2制作帶控件動態(tài)圖表第3步：設(shè)置組合框?qū)傩浴?.3 動態(tài)圖表4.3.2制作帶控件動態(tài)圖表第4步：定義名稱。第5步，編輯數(shù)據(jù)系列4.3 動態(tài)圖表4.3.2制作帶控件動態(tài)圖表第4步：定義名稱。第5步，編輯數(shù)據(jù)系列4.3 動態(tài)圖表4.3.3制作雙列數(shù)據(jù)動態(tài)圖表第1步：制作倉庫一銷量與庫存圖表。第2步：插入控件。4.3 動態(tài)圖表4.3.3制作雙列數(shù)據(jù)動態(tài)圖表第3步：定義“銷量”和“倉庫”兩個名稱。第4步：編輯數(shù)據(jù)系列4.3 動態(tài)圖表