Excel Power BI商業(yè)智能
DAY1:【數(shù)據(jù)分析概述】&【分析工具Excel概述】 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
1、什么是數(shù)據(jù)分析
2、知識(shí)、智慧、信息和數(shù)據(jù)的不同定義
3、SQL和Excel在數(shù)據(jù)分析中的定位
4、業(yè)務(wù)人員、IT技術(shù)人員及分析人員的不同職責(zé)
5、數(shù)據(jù)化驅(qū)動(dòng)業(yè)務(wù)對(duì)企業(yè)的意義
6、數(shù)據(jù)分析方法分類 - 業(yè)務(wù)數(shù)據(jù)分析、數(shù)據(jù)挖掘分析、大數(shù)據(jù)分析
7、業(yè)務(wù)數(shù)據(jù)分析流程
8、商業(yè)智能(BI)和商業(yè)洞察
9、數(shù)據(jù)可視化分析方法 - 對(duì)比分析、結(jié)構(gòu)分析、透視分析、其他分析方法
10、可視化&交互式是數(shù)據(jù)分析真正移交閱讀者主動(dòng)權(quán)的特點(diǎn)
11、Excel的原理、內(nèi)外部對(duì)象結(jié)構(gòu)、歷史、五大必備功能和Power BI四項(xiàng)功能(Power Query、Power Pivot、Power View和Power Map)
DAY2:【第二章:Excel基礎(chǔ)】&【第三章:Excel數(shù)據(jù)加工處理及基本公式介紹】#CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【“表格”結(jié)構(gòu)數(shù)據(jù)與“表”結(jié)構(gòu)數(shù)據(jù)】
12、表格結(jié)構(gòu)數(shù)據(jù):以單元格為基本數(shù)據(jù)存儲(chǔ)及操作單位 -> 處理批量數(shù)據(jù)效率低
表結(jié)構(gòu)數(shù)據(jù):以字段(列)為基本數(shù)據(jù)存儲(chǔ)及操作單位,比單元格高一個(gè)維度 -> 易于處理批量大量數(shù)據(jù),是數(shù)據(jù)分析的全場(chǎng)景
e.g. Excel中的已建表,方便批量進(jìn)一步操作;還可以導(dǎo)入關(guān)聯(lián)外部數(shù)據(jù)源并與之同步更新,在不同表之間進(jìn)行快速連接,包括通過(guò)Power Query和Power Pivot進(jìn)一步處理。
————————————————————————————————————————————————————————
【Excel數(shù)據(jù)透視圖表與切片器】
13、數(shù)據(jù)透視表:由“篩選器”、“行/列標(biāo)簽”、“值”構(gòu)成的二維值匯總表;易操作性、可計(jì)算性、數(shù)據(jù)處理方法多
@數(shù)據(jù)透視表用來(lái)將一維原始數(shù)據(jù)轉(zhuǎn)換為由行列構(gòu)成的二維值匯總表。
14、數(shù)據(jù)透視圖:基于數(shù)據(jù)透視表生成的圖表,用戶可以通過(guò)鼠標(biāo)拖拽來(lái)快速更改圖標(biāo)顯示結(jié)果;沒(méi)有Excel基本圖表類型中的散點(diǎn)圖和氣泡圖。
@數(shù)據(jù)透視圖既可以通過(guò)數(shù)據(jù)透視表生成也可以直接引用一般表格數(shù)據(jù)生成,此時(shí)會(huì)自動(dòng)生成與其對(duì)應(yīng)的數(shù)據(jù)透視表,一一對(duì)應(yīng)。
!圖和表的升降排序順序相反
15、切片器(2010版本以上):當(dāng)切片器與數(shù)據(jù)透視圖表關(guān)聯(lián)后,對(duì)切片器進(jìn)行選擇時(shí),數(shù)據(jù)透視表會(huì)一起發(fā)生變化。
@切片器與數(shù)據(jù)透視圖表的組合應(yīng)用是生成BI報(bào)表高度交互性可視化界面的基礎(chǔ)。
!只要是同一個(gè)底層數(shù)據(jù)源下,不同數(shù)據(jù)透視圖表都可以通過(guò)同一個(gè)切片器(右鍵,報(bào)表連接)控制
————————————————————————————————————————————————————————
【Excel條件格式與迷你圖】
16、條件格式
1)以單元格值為基礎(chǔ),設(shè)定條件格式:
e.g. “單元格值介于15和25之間的單元格標(biāo)為黃色”,全選單元格,只為包含以下內(nèi)容的單元格設(shè)置格式,單元格值-介于-15到25,格式為填充黃色
2)以公式返回值為基礎(chǔ),設(shè)定條件格式:
e.g. “第二列值大于第一列值時(shí)將第一列中單元格標(biāo)為黃色”,只選第一列,使用公式確定要設(shè)置格式的單元格,=B9>A9(勿鎖定?。袷綖樘畛潼S色
e.g. “將位于偶數(shù)行的單元格標(biāo)為黃色”,全選單元格,使用公式確定要設(shè)置格式的單元格,=MOD(ROW(),2)=0(ROW函數(shù)返回行號(hào)),格式為填充黃色
17、圖標(biāo)集
用圖標(biāo)的不同狀態(tài)來(lái)標(biāo)注當(dāng)前單元格值與閾值間的對(duì)比關(guān)系。注意,1)三種狀態(tài)、四種狀態(tài)和五種狀態(tài)三種類型的圖標(biāo)集;2)數(shù)字、百分比、公式及百分點(diǎn)值(PERCENTILE)四種閾值類型。
@紅黃綠三色“紅綠燈”圖標(biāo)使用最為頻繁,如果選擇困難可以優(yōu)先使用
18、數(shù)據(jù)條、色階
數(shù)據(jù)條:用數(shù)據(jù)條的長(zhǎng)短來(lái)標(biāo)注各單元格值的大小關(guān)系,以一組單元格中的最大值為最長(zhǎng)條形,用數(shù)據(jù)條的長(zhǎng)短變化來(lái)標(biāo)注其他單元格值與次單元格值的對(duì)比關(guān)系。
色階:用不同顏色變化來(lái)標(biāo)注各單元格值的大小關(guān)系,以一組單元格中的最大值為最重顏色,用顏色變化來(lái)標(biāo)注其他單元格值與此單元格值的對(duì)比關(guān)系。
@如果要利用數(shù)據(jù)條或色階來(lái)體現(xiàn)各部分值與匯總值之間的占比關(guān)系,選定單元格區(qū)域應(yīng)包含匯總值,形象觀察部分和整體的關(guān)系
19、迷你圖
是放入單個(gè)單元格中的小型圖,代表所選內(nèi)容中的一行數(shù)據(jù);折線圖、柱形圖、盈虧圖。
@迷你圖可以使用在表格中與單元格結(jié)合使用,令報(bào)表閱讀者可以通過(guò)圖表直觀掌握到具體表格數(shù)據(jù)的對(duì)比趨勢(shì),加深對(duì)抽象數(shù)據(jù)的理解及印象。
————————————————————————————————————————————————————————
【Excel函數(shù)公式】
20、函數(shù)公式
用來(lái)對(duì)單個(gè)單元格或指定單元格區(qū)域進(jìn)行計(jì)算后返回計(jì)算結(jié)果,返回可以是數(shù)值、數(shù)組或單元格地址等。函數(shù)由返回值、等號(hào)、函數(shù)表達(dá)式、參數(shù)、操作符五部分組成。
OFFSET函數(shù):以指定引用為參照系,通過(guò)給定偏移量返回新的引用區(qū)域,OFFSET(reference, rows, cols, height, width)(rows從上到下,cols從左到右,height是向下取m行,width是向右取n列)-> 用于名稱定義,e.g. 名稱指定到對(duì)應(yīng)的圖表的名稱進(jìn)行數(shù)據(jù)源使用,實(shí)現(xiàn)圖表動(dòng)態(tài)展現(xiàn)效果。
21、嵌套函數(shù)
將內(nèi)部函數(shù)的返回值作為外部函數(shù)的參數(shù)使用的方法,使用時(shí)內(nèi)部函數(shù)的返回值數(shù)據(jù)類型一定要與外部函數(shù)的參數(shù)數(shù)據(jù)類型保持一致。
基本數(shù)據(jù)類型包括文本型text、數(shù)值型numerical、布爾型boolean(true或false,不是0或1)、數(shù)組array
e.g. IF(ISERROR(I2),"ERROR","OK")
————————————————————————————————————————————————————————
【Excel基本圖表】
22、基本圖表類型
柱形圖(基于分類):1個(gè)變量;多個(gè)分類項(xiàng)目比較
柱形圖(基于時(shí)間):1個(gè)變量;不同時(shí)點(diǎn)的數(shù)值比較,適用時(shí)間點(diǎn)偏少
條形圖:1個(gè)變量;多個(gè)分類項(xiàng)目比較
折線圖:1個(gè)變量;不同時(shí)點(diǎn)的數(shù)值比較,適用連續(xù)時(shí)間段
餅圖:1個(gè)變量;整體中各部分的占比
散點(diǎn)圖:2個(gè)變量;數(shù)據(jù)間的分布及關(guān)聯(lián)性,可添加回歸公式和趨勢(shì)線
氣泡圖:3個(gè)變量;數(shù)據(jù)間的聯(lián)系比較,用每個(gè)點(diǎn)的不同大小代表第三個(gè)變量
雷達(dá)圖:多個(gè)變量;多重?cái)?shù)據(jù)間的比較,多重維度間平級(jí)關(guān)系
————————————————————————————————————————————————————————
【Excel常用函數(shù)】
23、
1)數(shù)學(xué)函數(shù):SUMIF、SUMPRODUCT、MOD
2)文本函數(shù):LEFT、MID、RIGHT、REPT
3)邏輯和引用函數(shù):IF、ISERROR
4)查找函數(shù):VLOOKUP、HLOOKUP、MATCH、INDEX
5)日期和時(shí)間函數(shù):TODAY、WEEKDAY
6)統(tǒng)計(jì)函數(shù):COUNTIF
ex1. 根據(jù)身份證號(hào)提取性別和出生年月并計(jì)算年齡
left()/mid()/right() mod() year(today()) =E3&"性"
ex2. 利用2015年節(jié)假日統(tǒng)計(jì)表計(jì)算具體日期及對(duì)應(yīng)的星期數(shù)
date(year,mth,day) weekday(date(...))
ex3. 使用INDEX及MATCH組合函數(shù)查找標(biāo)黃部分相關(guān)信息
@定位索引列前方列信息,用index()嵌套match()實(shí)現(xiàn)vlookup()的效果
- MATCH(lookup-value要查找的值, lookup-array查找連續(xù)區(qū)域, match-type查找方式,1升序/-1降序/0任意)
- INDEX(array查找區(qū)域, row-num返回值所在行號(hào), column-num返回值所在列號(hào),唯一時(shí)可不填)
ex4. 用HLOOKUP公式查找出H列標(biāo)黃部分值
!橫向查找;!一般False精確匹配
HLOOKUP(lookup_value要查找的值, table_array查找區(qū)域, row_index_num返回?cái)?shù)據(jù)在區(qū)域的第幾行數(shù), range_lookup模糊匹配/精確匹配的T/F)
ex5. 使用VLOOKUP函數(shù)求I列、K列及M列標(biāo)黃部分值,并用ISERROR與IF函數(shù)的嵌套函數(shù)處理異常值
VLOOKUP(lookup_value要查找的值, table_array查找區(qū)域, col_index_num返回?cái)?shù)據(jù)在區(qū)域的第幾列數(shù), range_lookup模糊匹配/精確匹配的T/F)
ISERROR返回Boolean值,注意存在錯(cuò)誤時(shí)為T,無(wú)異常時(shí)為F
ex6. 用SUMPRODUCT函數(shù)計(jì)算H列標(biāo)黃單元格值
將數(shù)組間的元素相乘后返回乘積之和
=SUMPRODUCT($D$2:$G$2,D4:G4)
=SUM(D2:G2*D4:G4)
按條件收入合計(jì):
SUMIF(range, criteria, [sum_range])
多條件匯總:銷售人員為“蘇術(shù)平”且國(guó)家地區(qū)為“加拿大”的銷售收入總和
=SUMPRODUCT((($D$2:$D$50=$L$8)*($C$2:$C50=$C$3)),$G$2:$G$50)
#用*號(hào)在SUMPRODUCT中表示條件間的AND連接,用+號(hào)表示OR連接
按條件統(tǒng)計(jì)訂單數(shù)量:
COUNTIF(range, criteria)
用REPT在單元格內(nèi)做虛擬條形圖,表現(xiàn)趨勢(shì):
REPT(text返回值, number_times返回遍數(shù))
————————————————————————————————————————————————————————
【Excel基本數(shù)據(jù)處理方法】
24、處理數(shù)據(jù)室數(shù)據(jù)分析流程中的起點(diǎn),也是耗時(shí)最多的環(huán)節(jié)。
數(shù)據(jù)處理方法:
1)處理重復(fù)數(shù)據(jù)
針對(duì)單列數(shù)據(jù)識(shí)別重復(fù)行:
I. 使用公式COUNTIF
II. 排序關(guān)鍵字段后使用公式IF
III. 利用數(shù)據(jù)透視表計(jì)數(shù)匯總
針對(duì)多列重復(fù)的去重:
I. 數(shù)據(jù)-排序和篩選-高級(jí)
(Power Query,刪除行-刪除重復(fù)行)
II. 識(shí)別重復(fù)行后刪除
@需要結(jié)合業(yè)務(wù)需求決定刪除和保留重復(fù)行
2)處理不完整數(shù)據(jù)
查找缺失值:
I. 使用 Ctrl+F
II. 使用定位條件 Ctrl+G,定位格式為“空值”
!眼睛看上去是空白的不一定就沒(méi)有值
III. 利用排序篩選
處理缺失值:
I. 使用0替換數(shù)值類缺失值
II. 使用平均值替換數(shù)值類缺失值
III. 刪除含有缺失值的記錄或不對(duì)此類記錄進(jìn)行操作
IV. 暫時(shí)保留缺失值行,(填充色區(qū)分或添加輔助列標(biāo)注,方便快速定位),在有必要時(shí)再進(jìn)行處理
3)處理異常值
!{編號(hào)}一般是文本型格式,位數(shù)相同,不合系列邏輯很可能是異常值。e.g. 原始數(shù)據(jù)0001轉(zhuǎn)入Excel可能作為數(shù)值型變成1,這時(shí)需要轉(zhuǎn)回文本型并補(bǔ)齊前方的000
!0值在日期格式下是“1900/1/0”,確認(rèn)異常值
!數(shù)值型字段出現(xiàn)“0.0.XXXX”或數(shù)量級(jí)差距巨大可能是誤輸入的異常值 -> 可以設(shè)置平均值的倍數(shù)作為閾值;或散點(diǎn)圖的outlier;或正態(tài)分布3倍標(biāo)準(zhǔn)差外的outlier……
4)數(shù)據(jù)分組
利用先計(jì)數(shù)再設(shè)置不同計(jì)數(shù)對(duì)應(yīng)的分檔,用輔助列+VLOOKUP對(duì)應(yīng)區(qū)間定義文字,最終返回區(qū)間定義文字導(dǎo)目標(biāo)列
5)轉(zhuǎn)換變量類型
處理格式錯(cuò)誤數(shù)據(jù):
I. 設(shè)置格式
II. 使用分列功能,列數(shù)據(jù)格式為“文本”
III. 使用公式補(bǔ)全信息后,轉(zhuǎn)換成日期型
2001-10-02=LEFT(C2,4)&"-"&MID(C2,5,2)&"-"&RIGHT(C2,2)
@常需要相互轉(zhuǎn)換的數(shù)據(jù)類型包括文本型、日期型、數(shù)值型
6)數(shù)據(jù)標(biāo)準(zhǔn)化
I. Min-Max標(biāo)準(zhǔn)化:新數(shù)據(jù)=(原數(shù)據(jù)-極小值)/(極大值-極小值)
@適用于量級(jí)差異大的相同維度間進(jìn)行比較
II. 使用標(biāo)準(zhǔn)分進(jìn)行標(biāo)準(zhǔn)化:標(biāo)準(zhǔn)分=(原始分-平均分)/ 標(biāo)準(zhǔn)差 STDEV
@適用于評(píng)價(jià)標(biāo)準(zhǔn)或許不同的相同維度間進(jìn)行比較
7)設(shè)置變量權(quán)重
III. 加權(quán)平均
利用交叉表設(shè)置權(quán)重:
- 縱向和橫向?qū)Ρ?,橫向重要?jiǎng)t為1,縱向重要?jiǎng)t為0
- 橫向加總,每項(xiàng)都加1,保證數(shù)值最低項(xiàng)合計(jì)值不為零,以免沒(méi)有意義無(wú)法后續(xù)進(jìn)行除法計(jì)算
- 每個(gè)階段合計(jì)值/合計(jì)總值*100%
- 加權(quán)平均值 = 變量1*變量1的權(quán)重 +……+變量n*變量n的權(quán)重
主要數(shù)據(jù)類型:文本型、整數(shù)型、小數(shù)型、布爾型、日期型、其他 類型(會(huì)計(jì)、特殊)
主要變量類型:名義型(彼此間沒(méi)有順序關(guān)系,只表示分類的數(shù)據(jù),例如姓名)、有序型(有順序關(guān)系的數(shù)據(jù),例如優(yōu)良中差)、連續(xù)型(包含若干小數(shù)位,取值密集,例如溫度 )
+思考題:用唯一的數(shù)字區(qū)分開唯一的業(yè)務(wù)類型,形成映射關(guān)系,方便進(jìn)一步數(shù)據(jù)處理和分析
取一個(gè)1-3之間的隨機(jī)小數(shù):=RAND()*(3-1)+1 取一個(gè)1-3之間的隨機(jī)整數(shù):=ROUND(RAND()*(3-1)+1,0)
————————————————————————————————————————————————————————
【Excel基礎(chǔ)描述性統(tǒng)計(jì)類圖表】
頻數(shù):是落在各類別種的數(shù)據(jù)個(gè)數(shù)
頻率:是各類別頻數(shù)與總頻數(shù)之比
@頻數(shù)和頻率分別從絕對(duì)數(shù)和相對(duì)數(shù)上,反映出數(shù)據(jù)在各變量值上的分布狀況。
25、直方圖
用來(lái)展現(xiàn)數(shù)據(jù)在不同組距間分布狀態(tài)的圖表,組距=(最大值-最小值)/ 組數(shù),組數(shù)根據(jù)實(shí)際業(yè)務(wù)情況設(shè)置。
!柱形圖看高度, 直方圖看面積
26、盒須圖(箱型圖)
用來(lái)體現(xiàn)數(shù)據(jù)分散情況。將數(shù)據(jù)由小到大排列并分成四等份,處于三個(gè)分割點(diǎn)位置的數(shù)值就是四分位數(shù)。
上邊緣 = Q3+1.5*(Q3-Q1)
下邊緣 = Q3-1.5*(Q3-Q1)
DAY3:【第4章: Power Query多源大規(guī)模數(shù)據(jù)加工處理方法】 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【Power Query概述及導(dǎo)入多源數(shù)據(jù)】
27、Power Query:多源大規(guī)模數(shù)據(jù)加工處理方法
- 提取整合多數(shù)據(jù)源數(shù)據(jù),包括各種關(guān)系型數(shù)據(jù)庫(kù)、Excel文件、txt格式(制表符Tab分割)及csv格式(逗號(hào)comma分割)的等文本文件(只有數(shù)據(jù)信息&分隔符,沒(méi)有其他信息)、Web頁(yè)面、Hadoop的HDFS等等
- 突破Excel表格的數(shù)據(jù)限制,可快速處理幾百萬(wàn)甚至上千萬(wàn)行(大約2G)的數(shù)據(jù)
- 提供豐富的數(shù)據(jù)處理分析功能
- 可通過(guò)M函數(shù)靈活創(chuàng)建自定義數(shù)據(jù)處理及計(jì)算規(guī)則
- 創(chuàng)建好的數(shù)據(jù)處理流程可以無(wú)限次復(fù)用
@2016以上版本:數(shù)據(jù)-“獲取和轉(zhuǎn)換”功能組
!勿通過(guò)數(shù)據(jù)-獲取外部數(shù)據(jù)進(jìn)行導(dǎo)入
數(shù)據(jù)導(dǎo)入方式一:僅創(chuàng)建連接,只是將外部數(shù)據(jù)源導(dǎo)入到Power Query的查詢引擎中,而在Excel表格界面中不會(huì)留下任何數(shù)據(jù)痕跡——適用于只需要在Power Query中進(jìn)行數(shù)據(jù)處理,而不需要Excel表格界面中對(duì)導(dǎo)入數(shù)據(jù)進(jìn)行預(yù)覽或單元格計(jì)算時(shí),可節(jié)省文件容量,減少計(jì)算壓力
數(shù)據(jù)導(dǎo)入方式二:表,不僅可以將外部數(shù)據(jù)源導(dǎo)入到Power Query的查詢引擎中,同時(shí)在Excel表格界面中生成一份與Power Query相關(guān)聯(lián)的Excel工作表數(shù)據(jù)。在Power Query中發(fā)生的變化,點(diǎn)擊加載后Excel工作表中的數(shù)據(jù)也會(huì)隨之發(fā)生變化?!m用于需要在Excel表格界面中使用數(shù)據(jù)時(shí)
@“加載到…”支持兩種方式間的切換
@Power Query進(jìn)行修改后,記得左上角點(diǎn)“關(guān)閉并上載”或“關(guān)閉并上載至…”(第一次加載時(shí)未設(shè)置加載規(guī)則的情況)
———————————————————————————————————————————————————————
【Power Query合并數(shù)據(jù)】
字段的集合就是數(shù)據(jù)表,數(shù)據(jù)表的集合就是數(shù)據(jù)庫(kù)。每張表字段首行叫標(biāo)題行,標(biāo)題行中內(nèi)容叫字段名,第二到最后一行叫記錄,記載了數(shù)據(jù)內(nèi)容。
存放最終合并結(jié)果的叫主表,為主表提供必要信息的表叫附表。
28、橫向合并數(shù)據(jù):將附表中有而主表中沒(méi)有的字段信息合并至主表中,從而充實(shí)完善主表信息的方法。即合并列,添加新的字段進(jìn)來(lái)。
選中主表,合并-合并查詢
1)確定用于合并兩表的關(guān)鍵字段(主附表中都有且值對(duì)應(yīng)的上的字段,最好沒(méi)有重復(fù)值)
2)進(jìn)行合并操作
3)選擇需要展示的字段信息(連接過(guò)來(lái)的Table附表展開后設(shè)置)
@:兩表若擔(dān)心同名字段存在,可以勾選上“使用原始列名作為前綴”
@1:當(dāng)兩表中用于合并的關(guān)鍵字段值不是一一對(duì)應(yīng)時(shí),不同的聯(lián)接種類會(huì)出現(xiàn)不同的聯(lián)接結(jié)果(默認(rèn)選“內(nèi)部”,另一種是“左外部”)
@2:當(dāng)關(guān)鍵字段中有重復(fù)值時(shí),連接后的總行數(shù)為關(guān)鍵字段值重復(fù)出現(xiàn)次數(shù)的乘積
29、縱向合并數(shù)據(jù):即添加行,在現(xiàn)有數(shù)據(jù)的基礎(chǔ)上添加記錄。
選中主表,合并-追加查詢,“兩個(gè)表”或“三個(gè)或更多表”
1)將有相同字段名的字段縱向合并到一起
2)將不同字段名的字段追加在最后
3)非匹配字段標(biāo)記為“null”值
@Excel是否自動(dòng)添加標(biāo)題行:看導(dǎo)入的原始數(shù)據(jù)的第一行的數(shù)據(jù)類型是否和下方所有記錄數(shù)據(jù)的數(shù)據(jù)類型完全一致,是就會(huì)默認(rèn)首行也是記錄并添加系統(tǒng)標(biāo)題行 -> Power Query,轉(zhuǎn)換-將第一行用作標(biāo)題
!一個(gè)字段只允許有一種數(shù)據(jù)類型,將文本型字段與數(shù)值型字段成功合并后,將會(huì)改變字段類型為文本型(因?yàn)閿?shù)值可以變成文本,而文本無(wú)法變成數(shù)值?。。。?/span>
———————————————————————————————————————————————————————
【Power Query基本功能】
30、
1)“開始”選項(xiàng)卡:對(duì)數(shù)據(jù)進(jìn)行上載查詢、增減行或列、拆分列、分組、改變數(shù)據(jù)類型、更改屬性、導(dǎo)入及合并外部數(shù)據(jù)等基本操作。
2)“轉(zhuǎn)換”選項(xiàng)卡:提供了針對(duì)行與列間的結(jié)構(gòu)性加工處理功能、數(shù)據(jù)類型、格式等設(shè)置功能以及基本的函數(shù)計(jì)算功能。
選中目標(biāo)列,轉(zhuǎn)換-任意列-填充,向上/向下
!空值很危險(xiǎn),null,不知道位數(shù)也不知道下一步該如何操作->轉(zhuǎn)換為已知狀態(tài)的真正控制,比如“-”
選中目標(biāo)列,轉(zhuǎn)換-任意列-透視列,文本型只能計(jì)數(shù)
選中目標(biāo)列,轉(zhuǎn)換-任意列-逆透視列-逆透視其他列,即從二維交叉表轉(zhuǎn)換為一維表
3)“添加列”選項(xiàng)卡:針對(duì)字段信息的加工處理,包括添加新字段、更改字段格式、位字段增加計(jì)算公式等(其中大部分功能與轉(zhuǎn)換選項(xiàng)卡中功能重復(fù),但適用于新添加的字段上的處理)。
選中目標(biāo)列,添加列-常規(guī)-自定義列,進(jìn)行參數(shù)定義即可
4)“視圖”選項(xiàng)卡:用來(lái)顯示或隱藏“查詢編輯器“中的主要功能區(qū)域。
@查詢?cè)赑ower Query 中應(yīng)用的步驟都通過(guò)M函數(shù)形式保存在查詢?cè)O(shè)置中。
@“數(shù)據(jù)透視”:按照某種規(guī)則將數(shù)據(jù)進(jìn)行匯總,匯總行列標(biāo)簽結(jié)果形成二位匯總表
———————————————————————————————————————————————————————
【Power Query M函數(shù)】
31、編輯M函數(shù)(Power Query 80%的能力,提高重復(fù)性工作效率)方法:
600+個(gè),專用于數(shù)據(jù)處理的編程語(yǔ)言,先要會(huì)改,后要會(huì)寫!M函數(shù)先繼承上一步處理的結(jié)果,在此基礎(chǔ)上進(jìn)行目前本身處理,生成本身的處理結(jié)果,依次循環(huán)……
1)通過(guò)“編輯欄”、“添加自定義列”或“高級(jí)編輯器”(批量寫入M函數(shù)的處理邏輯)等功能了解M函數(shù)表達(dá)式寫法
2)根據(jù)需求重新編輯表達(dá)式程序
3)編輯錯(cuò)誤通過(guò)“查詢?cè)O(shè)置”區(qū)域退回重新編輯
4)新建一個(gè)空查詢,再在fx編輯器中輸入“=#shared”可以查看M函數(shù)List列表,轉(zhuǎn)換-到表中 可以轉(zhuǎn)為Table表數(shù)據(jù)方便通過(guò)篩選按鈕查詢定位,例如Text. 或者Date. 等等
- 添加序號(hào)列:添加列-常規(guī)-索引列,Table.AddIndexColumn()
- 重排序的列:鼠標(biāo)選中拖拽,Table.ReorderColumns()
-提取數(shù)據(jù):添加列-從文本-提取,Text.Range()(從前一位往后取一位數(shù)?。?/span>
- 轉(zhuǎn)換數(shù)據(jù)類型:開始-轉(zhuǎn)換-數(shù)據(jù)類型,設(shè)置為“整數(shù)”Int64.Type,Table.TransformColumnTypes()
+文本數(shù)據(jù)類型:type text
+日期數(shù)據(jù)類型:type date
- 添加新字段:Table.AddColumn()
- 判斷奇偶:轉(zhuǎn)換-編號(hào)列-信息,設(shè)置為“偶數(shù)”
- 替換值:開始-轉(zhuǎn)換-替換值,Table.ReplaceValue()
32、Power Query高級(jí)編輯器
let-in結(jié)構(gòu),let中記錄的是每一步操作步驟,in里面記錄的是最終顯示出來(lái)的處理結(jié)果,let等式左邊是當(dāng)前操作的結(jié)果值,右邊是操作內(nèi)容,蛇形相連,各操作間用逗號(hào)隔開,最后一步操作后沒(méi)有逗號(hào)后接in部分!
!借鑒已有M函數(shù)寫新函數(shù)時(shí),注意修改每行結(jié)果值,句末逗號(hào)與否,in顯示內(nèi)容
@未來(lái)有新紀(jì)錄加進(jìn)來(lái),只需要將新紀(jì)錄加進(jìn)Excel原始數(shù)據(jù)sheet中,上方功能欄找到數(shù)據(jù)-全部刷新,就可以按之前的Power Query邏輯得到最新更新后的數(shù)據(jù)
DAY4:【第5章: Power Pivot多維數(shù)據(jù)透視分析方法】 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【Power Pivot 概述及導(dǎo)入數(shù)據(jù)】
透視是在指定維度下對(duì)指標(biāo)值進(jìn)行匯總觀測(cè)的方法。在海量原始數(shù)據(jù)面前,我們是得不到任何有用信息的,只有將其分類匯總后,才能得到這些數(shù)據(jù)背后隱藏的信息。這就是透視分析的意義。
在復(fù)雜的業(yè)務(wù)邏輯面前,非單一數(shù)據(jù)源的簡(jiǎn)單數(shù)據(jù)分析需求,比如想看到不同銷售區(qū)域下每名不同銷售人員的銷售業(yè)績(jī),與所有銷售人員的總平均業(yè)績(jī)的差距是多少,銷售人員業(yè)績(jī)比平均低時(shí)用紅色標(biāo)注,高用綠色標(biāo)注。其中,區(qū)域信息來(lái)自區(qū)域表,銷售業(yè)績(jī)信息來(lái)自于銷售表。
33、Power Pivot是一個(gè)加強(qiáng)版的數(shù)據(jù)透視工具,不僅在數(shù)據(jù)處理量上,更在透視規(guī)則及自定義規(guī)則上得到了大幅改善。不僅擁有比傳統(tǒng)數(shù)據(jù)透視表更加強(qiáng)大靈活的計(jì)算分析能力,還可以導(dǎo)入并關(guān)聯(lián)多種不同數(shù)據(jù)源的大量數(shù)據(jù),并在內(nèi)存中創(chuàng)建自己的多維數(shù)據(jù)模型(多個(gè)表關(guān)聯(lián)在一起的數(shù)據(jù)集合)。
- 搭建多維數(shù)據(jù)分析環(huán)境,形成統(tǒng)一的數(shù)據(jù)環(huán)境
- 突破數(shù)據(jù)行數(shù)限制(勿大于2G?。?/span>
- 簡(jiǎn)潔的操作界面功能
- 強(qiáng)大的自定義功能(200+個(gè)函數(shù))
@“數(shù)據(jù)模型”及Power Pivot中的數(shù)據(jù)
@從一個(gè)外部數(shù)據(jù)源導(dǎo)入一個(gè)表數(shù)據(jù)即在Power Pivot形成一個(gè)工作表
@從Power Query導(dǎo)入:在Power Query首次加載時(shí),勾選“將此數(shù)據(jù)添加到數(shù)據(jù)模型”,再直接進(jìn)入Power Pivot界面可見(jiàn)
@從Excel導(dǎo)入:選中目標(biāo)區(qū)域,在Excel工具欄找到Power Pivot-表格-添加到數(shù)據(jù)模型,再直接進(jìn)入Power Pivot界面可見(jiàn)
————————————————————————————————————————————————————————
【Power Pivot 搭建多維數(shù)據(jù)分析模型】
34、多維數(shù)據(jù)模型:又叫多維數(shù)據(jù)集、立方體(可以從多角度多側(cè)面,用數(shù)據(jù)全面映射某種業(yè)務(wù)的實(shí)際狀況),指的是相互間通過(guò)某種聯(lián)系被關(guān)聯(lián)在一起的不同類別的數(shù)據(jù)集合。涉及該業(yè)務(wù)與之相連的其他業(yè)務(wù)的數(shù)據(jù)全部整合到一起,創(chuàng)建全面的、統(tǒng)一的多維數(shù)據(jù)環(huán)境才能深入全面觀察某個(gè)業(yè)務(wù)問(wèn)題,搭建環(huán)境越充實(shí),分析結(jié)果的可參考性越高。
@Power Pivot中的操作都是最終Excel數(shù)據(jù)透視表的前期準(zhǔn)備工作
多維數(shù)據(jù)集搭建方法:
1)明確表與表之間用于匹配的關(guān)鍵字段
2)在關(guān)系圖視圖模式下,選中某個(gè)表的關(guān)鍵字段拖拽向另一個(gè)表的關(guān)鍵字段
注意:
@1 兩表關(guān)聯(lián)時(shí)必然有一個(gè)主表(*)和一個(gè)附表(1)。用于連接兩表的兩個(gè)關(guān)鍵字段中,至少有一個(gè)關(guān)鍵字段是沒(méi)有重復(fù)值的。(Power Pivot的強(qiáng)制要求,不允許“多對(duì)多”)
@2 如果兩個(gè)關(guān)鍵字段都沒(méi)有重復(fù)值,連接時(shí)先選中的表為主表,形成“一對(duì)一”關(guān)系 。
@3 系統(tǒng)會(huì)將關(guān)鍵字段中有重復(fù)值的表作為主表,沒(méi)有重復(fù)值的表為附表,形成“多對(duì)一”關(guān)系。在數(shù)據(jù)透視表中,只有當(dāng)行/列標(biāo)簽均來(lái)自附表時(shí),附表才能提供值字段。(行/列標(biāo)簽有一個(gè)來(lái)自主表就會(huì)報(bào)錯(cuò)!)
-> 以上連接規(guī)則將用在創(chuàng)建的數(shù)據(jù)透視表中:
主頁(yè)-數(shù)據(jù)透視表
————————————————————————————————————————————————————————
【Power Pivot 創(chuàng)建層次結(jié)構(gòu)】
35、在不同字段間創(chuàng)建層次結(jié)構(gòu)方法:
1)在關(guān)系圖視圖模式下選中父級(jí)字段后,鼠標(biāo)右鍵選中“創(chuàng)建層次結(jié)構(gòu)”
2)為層次結(jié)構(gòu)命名
3)將子級(jí)字段拖拽到創(chuàng)建好的層次結(jié)構(gòu)中父級(jí)字段的下方
@創(chuàng)建層次結(jié)構(gòu)時(shí)一定要符合業(yè)務(wù)邏輯!
————————————————————————————————————————————————————————
【Power Pivot DAX表達(dá)式】
36、DAX數(shù)據(jù)分析表達(dá)式:
Power Pivot的特有函數(shù)集,用于在Power Pivot編輯器內(nèi)為數(shù)據(jù)透視表創(chuàng)建透視規(guī)則(復(fù)雜條件下的篩選或計(jì)算)以及增加新的字段內(nèi)容。
1)DAX表達(dá)式的結(jié)果應(yīng)用在數(shù)據(jù)透視表中
2)DAX表達(dá)式的結(jié)果作用于整列或者表中所有行
3)注意:
@Power Query和Power Pivot中處理的都是表結(jié)構(gòu)數(shù)據(jù),有基于表(字段的合集)和字段的數(shù)據(jù)類型,區(qū)分于Excel中的文本型、數(shù)值型等數(shù)據(jù)類型
- 表名用' '引用,字段名(列名)用[ ]引用,例如'商機(jī)記錄'[贏單率]
- 要注意函數(shù)表達(dá)式中參數(shù)的數(shù)據(jù)類型,尤其是要將“表”與“數(shù)值”正確區(qū)分
- 與Excel公式相同,除了直接在編輯器的公式區(qū)域輸入公式外,還可以單擊公式編輯欄前的fx圖標(biāo)啟動(dòng)插入函數(shù)對(duì)話框,在對(duì)話框中選擇需要的函數(shù)使用
- 表達(dá)式中的函數(shù)名不分大小寫(只有Power Query的M函數(shù)區(qū)分大小寫,每個(gè)單詞首字母大寫)
e.g.
高贏單率:[贏單率] = 0.75
低風(fēng)險(xiǎn):[有無(wú)拖欠還款情況] = “無(wú)”
@右側(cè)DAX編輯新字段:針對(duì)左側(cè)預(yù)覽區(qū)域內(nèi)某一個(gè)字段中的每一行值來(lái)進(jìn)行計(jì)算時(shí)使用
[贏單率文字替換]=switch('商機(jī)記錄'[贏單率],0.15,"低",0.25,"低",0.5,"中",0.75,"高","-")
(!先將兩表創(chuàng)建好連接關(guān)系才能使用related)
[有無(wú)拖欠]=related('商機(jī)相關(guān)企業(yè)信息'[有無(wú)拖欠還款情況])
@下方DAX編輯匯總規(guī)則:針對(duì)上方預(yù)覽區(qū)域的某一個(gè)字段整個(gè)字段創(chuàng)建匯總規(guī)則時(shí)使用(建議字段正下方書寫?。?> 只有在創(chuàng)建數(shù)據(jù)透視表中才能發(fā)揮作用
高贏單率低風(fēng)險(xiǎn)商機(jī)金額加總值:=calculate(sum('商機(jī)記錄'[商機(jī)金額(M)]),filter('商機(jī)記錄','商機(jī)記錄'[贏單率文字替換]="高"),filter('商機(jī)記錄','商機(jī)記錄'[有無(wú)拖欠]="無(wú)"))
總商機(jī)金額:=sum('商機(jī)記錄'[商機(jī)金額(M)])
@注意等號(hào)前的冒號(hào)!注意字段名可用鼠標(biāo)點(diǎn)選整列完成
高贏單率低風(fēng)險(xiǎn)商機(jī)金額百分比:=[高贏單率低風(fēng)險(xiǎn)商機(jī)金額加總值]/[總商機(jī)金額],在主頁(yè)-格式設(shè)置-格式,設(shè)置為“百分比”
————————————————————————————————————————————————————————
【Power Pivot 使用KPI】
37、KPI即在Excel數(shù)據(jù)透視表中創(chuàng)建圖標(biāo)集的方法,圖標(biāo)集方便用戶快速了解數(shù)據(jù)的好壞程度。常見(jiàn)的三色圖標(biāo)有兩個(gè)閾值來(lái)區(qū)分,最小和最大閾值
1)在編輯器下方公式區(qū)域內(nèi)用公式為需要?jiǎng)?chuàng)建KPI的字段指定匯總規(guī)則(至少一個(gè)DAX)
2)點(diǎn)擊“創(chuàng)建KPI”在“關(guān)鍵績(jī)效指標(biāo)(KPI)”對(duì)話框中設(shè)定KPI規(guī)則
3)定義KPI目標(biāo)值的方法有“度量值”及“絕對(duì)值”兩種方法:
度量值 - (需要兩個(gè)匯總規(guī)則)通過(guò)一種匯總規(guī)則和另外一種匯總之間的比對(duì)關(guān)系,以兩種匯總值之間的百分比為判斷依據(jù)的KPI規(guī)則
絕對(duì)值 - 以一個(gè)匯總規(guī)則與某個(gè)絕對(duì)數(shù)值之間的大小關(guān)系為判斷依據(jù)的KPI規(guī)則
e.g.
平均銷售金額:=average([銷售金額])
城市維度總平均金額:=calculate(average([銷售金額]),all('表1'[城市]))
@ALL函數(shù):“所有XX的…”
@目標(biāo)即理想中的狀態(tài)
選中[平均銷售金額],主頁(yè)-創(chuàng)建KPI,度量值:城市維度總平均金額,100%-120% -> 生成紅綠燈圖標(biāo)
銷量:=count('表1'[訂單ID])
選中[銷量],主頁(yè)-創(chuàng)建KPI,絕對(duì)值:10000,1000-4000 -> 生成紅綠燈圖標(biāo)
DAY5:【第6章: Excel高級(jí)數(shù)據(jù)可視化方法精講】 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【數(shù)據(jù)可視化分析方法】
38、對(duì)比分析
1)縱向?qū)Ρ龋簳r(shí)間序列分析,同一指標(biāo)不同時(shí)間下的變化趨勢(shì)(不同間斷分散的時(shí)間段用柱狀圖,連續(xù)連貫密集的時(shí)間點(diǎn)用折線圖)
2)進(jìn)度分析:展現(xiàn)目標(biāo)完成情況的分析方法,儀表盤、圖標(biāo)圖、溫度圖、游標(biāo)圖、方塊圖……
3)橫向?qū)Ρ龋涸谕粫r(shí)間下,部分與總體,部分與部分或是對(duì)象與對(duì)象之間的對(duì)比,常用餅圖、環(huán)形圖(橫向占比對(duì)比,部分與部分)、條形圖(橫向值對(duì)比,部分與總體)、分段折線圖(橫向趨勢(shì)對(duì)比,對(duì)象與對(duì)象)等
@與縱向?qū)Ρ鹊膮^(qū)別:縱向是不同時(shí)間條件下同一指標(biāo)的對(duì)比情況,橫向是同一時(shí)間條件下不同指標(biāo)之間的對(duì)比情況
4)標(biāo)準(zhǔn)值、平均值及計(jì)劃值之間的對(duì)比,圖表類型選擇靈活
@子彈圖,常用于平均對(duì)比分析
@Excel簇狀柱形圖適用于實(shí)際值與計(jì)劃值(非定值)對(duì)比
5)同環(huán)比分析:同比即本期值與同期值之間的對(duì)比;環(huán)比即本期值與上期值之間的對(duì)比,常見(jiàn)簇狀柱形圖或折線圖,[同/環(huán)比增長(zhǎng)率]增加圖標(biāo)集
@一定要考慮業(yè)務(wù)場(chǎng)景去設(shè)計(jì)才有實(shí)際意義,例如餐飲業(yè)的額星期比日期更具有周期性意義
6)預(yù)警分析:用KPI分析、預(yù)警色填充單元格等方式對(duì)關(guān)鍵指標(biāo)進(jìn)行預(yù)警,了解關(guān)鍵值的變化及好壞程度。
7)***透視分析:使用數(shù)據(jù)透視圖表功能進(jìn)行多維度、多層次、多規(guī)則的透視分析,通過(guò)對(duì)觀測(cè)指標(biāo)的洞察,理解及掌握指標(biāo)背后的實(shí)際業(yè)務(wù)情況及風(fēng)險(xiǎn)。
39、結(jié)構(gòu)分析
1)構(gòu)成分析:部分與整體間構(gòu)成關(guān)系的分析方法,常見(jiàn)漏斗圖、瀑布圖、滑珠圖、餅圖、環(huán)形圖等。
2)杜邦分析:不僅適用于財(cái)務(wù)指標(biāo)分析,而且可擴(kuò)展延伸至各種關(guān)鍵指標(biāo)間有明顯結(jié)構(gòu)關(guān)系的業(yè)務(wù)分析中使用。
40、其他分析方法
1)變化分析:反映同一指標(biāo)或多種指標(biāo)狀態(tài)及數(shù)值變化情況的分析方法,常見(jiàn)組合圖表、指標(biāo)構(gòu)成圖等,是一種動(dòng)態(tài)分析方法,要結(jié)合自身實(shí)際業(yè)務(wù)情況,盡可能直觀地選擇合適的圖表對(duì)變化中的指標(biāo)(觀測(cè)值)進(jìn)行展現(xiàn)
2)分組分析:用來(lái)展現(xiàn)關(guān)鍵指標(biāo)在不同區(qū)間(組距)內(nèi)的分布情況,常見(jiàn)直方圖
3)增維分析:將不同類型的圖表嵌套使用的方法,達(dá)到增加信息展現(xiàn)維度,擴(kuò)展分析廣度的目的,例如折線圖與環(huán)形圖的嵌套圖表
————————————————————————————————————————————————————————
【專業(yè)自定義圖表創(chuàng)建及和應(yīng)用方法】
數(shù)據(jù)可視化:通過(guò)對(duì)數(shù)據(jù)的圖形化展現(xiàn)來(lái)直觀快速地傳遞復(fù)雜的數(shù)據(jù)信息,體現(xiàn)數(shù)據(jù)之美的手段。數(shù)據(jù)分析人員通過(guò)使用數(shù)據(jù)可視化技巧來(lái)令數(shù)據(jù)變得好看(一層是容易看容易理解的意思,另一層是美觀賞心悅目的意思)。因?yàn)槿祟悓?duì)視覺(jué)信息更強(qiáng)的認(rèn)知水平,好看的圖表能將信息準(zhǔn)確、直觀、生動(dòng)地傳遞給閱讀者,不僅能幫其迅速理解圖表意思,把握關(guān)鍵信息,還能加深其對(duì)圖表的印象,達(dá)到過(guò)目不忘的效果。在實(shí)際應(yīng)用中,好看的圖表能幫助決策者快速把握業(yè)務(wù)情況,并在重要的商業(yè)決策中做出正確判斷。
41、自定義圖表:為了增強(qiáng)圖表可視化能力而應(yīng)用的一種方法。
1)不是圖表的圖表制作方法:使用Excel基本制圖功能之外的功能創(chuàng)建的數(shù)據(jù)可視化圖表,即迷你圖表。迷你圖表多與報(bào)表中其他單元格數(shù)據(jù)結(jié)合使用,表格+ 迷你圖表的同時(shí)使用的報(bào)表,不僅能直觀傳遞可視化信息,還能完整精確地傳遞數(shù)據(jù)信息,達(dá)到“表圖合一”的效果。
2)嵌套圖表的制作方法:將主圖表與其他圖表或其他圖片、圖形嵌套在一起形成的新圖表,目的一是為了改變?cè)袌D表的展現(xiàn)形式,二是為了增加原有圖表的展示維度。
- 半圓形儀表盤(親切感,會(huì)展現(xiàn)深刻認(rèn)知,突出重要性使用)
- 多維折線圖(擴(kuò)展維度)
3)基本圖表的再創(chuàng)新方法:以改變Excel基本圖表的格式及引用數(shù)據(jù)源數(shù)據(jù)排列規(guī)則等方式來(lái)改變?cè)O(shè)計(jì)意圖的展現(xiàn)形式,需要清晰的設(shè)計(jì)思路和靈活的展現(xiàn)技巧。
- 瀑布圖(堆疊柱形圖+下層占位部分無(wú)填充顏色)
- 分段折線圖(看得更為清晰,數(shù)據(jù)分組分拆成三列錯(cuò)開)
————————————————————————————————————————————————————————
【交互式圖表制作及應(yīng)用方法】
42、交互式圖表:又叫動(dòng)態(tài)圖表,指能夠隨時(shí)響應(yīng)用戶操作指令,改變展現(xiàn)結(jié)果的圖表。圖表從靜態(tài)變?yōu)閯?dòng)態(tài)后,分析的深度和廣度都將得到質(zhì)的改變。
1)切片器與數(shù)據(jù)透視圖的組合應(yīng)用:用切片器直接控制透視圖表(適用于2010版本以上帶切片器的透視圖表)
切片器創(chuàng)建方法:選中需要關(guān)聯(lián)的數(shù)據(jù)透視表或圖,插入-切片器,為切片器選擇字段(字段可多選)
@按住Ctrl鍵點(diǎn)選支持切片器選項(xiàng)多選
@切片器可占用區(qū)域小又希望完整展示所有選項(xiàng)時(shí),可以右鍵格式設(shè)置中找到列數(shù),設(shè)置為2列
更改切片器關(guān)聯(lián)方法:右鍵點(diǎn)擊切片器,選擇“報(bào)表鏈接”,選擇需要關(guān)聯(lián)的數(shù)據(jù)透視表或圖
2)控件與公式的組合應(yīng)用:使用控件、公式,改變靜態(tài)圖標(biāo)數(shù)據(jù)源(限制條件較多,但支持圖表類型廣泛),將控件作為交互平臺(tái),將函數(shù)的返回值作為圖表的數(shù)據(jù)源,通過(guò)選擇控件來(lái)改變函數(shù)返回值,參照基準(zhǔn)點(diǎn)發(fā)生的變化,實(shí)現(xiàn)圖表的動(dòng)態(tài)展現(xiàn)效果的方法。
動(dòng)態(tài)圖表制作步驟:選擇并設(shè)置控件(開發(fā)工具-插入-表單控件-組合框),準(zhǔn)備圖表數(shù)據(jù),創(chuàng)建圖表,測(cè)試
動(dòng)態(tài)圖表制作邏輯:以控件返回值為基礎(chǔ)參考值,用offset等查找定位函數(shù)選擇圖表參考數(shù)據(jù)區(qū)域,將公式寫入創(chuàng)建名稱中作為圖表的參考數(shù)據(jù)使用,將圖表的數(shù)據(jù)源設(shè)定為創(chuàng)建的名稱
e.g.
=OFFSET($B$3,$A$3,1,1,7),B3是表格首行首個(gè)單元格,A3是組合框的返回值結(jié)果
指定名稱定義:因?yàn)樵趫D表里無(wú)法直接寫公式,Excel工具欄中公式-名稱管理器-新建,命名并將引用位置設(shè)置為=OFFSET($B$3,$A$3,1,1,7)
再關(guān)聯(lián)圖表:圖表中右鍵,選擇數(shù)據(jù),編輯,將系列值改為=柱形圖示例!{新建公式名稱}
動(dòng)態(tài)圖表制作注意事項(xiàng):
- 根據(jù)實(shí)際需要選擇適當(dāng)?shù)目丶?/span>
- 根據(jù)業(yè)務(wù)需求決定動(dòng)態(tài)圖表互動(dòng)方式
- 需要注意不要將函數(shù)寫死,業(yè)務(wù)可能后續(xù)發(fā)生變化
- 圖標(biāo)制作好后,一定要先進(jìn)行測(cè)試,保證圖表結(jié)果的準(zhǔn)確性
43、主要控件
1)選項(xiàng)按鈕
2)復(fù)選框
3)列表框
4)滾動(dòng)條、數(shù)值調(diào)節(jié)按鈕
————————————————————————————————————————————————————————
【Power Map數(shù)據(jù)地圖創(chuàng)建及應(yīng)用】
44、數(shù)據(jù)地圖是展現(xiàn)數(shù)據(jù)地理信息最為直觀的方法。Power Map是Power BI系列插件工具中用來(lái)生成數(shù)據(jù)地圖的工具,結(jié)合Bing地圖,支持用戶繪制可視化的地理和時(shí)態(tài)數(shù)據(jù),并用3D方式進(jìn)行分析。需要聯(lián)網(wǎng)與Bing地圖通信環(huán)境下才可使用Power Map。
Excel菜單欄,插入-Power Map-地圖
靜態(tài)地圖:右側(cè)區(qū)域圖層窗格,先選地理和地圖級(jí)別(下一步),再設(shè)置高度,勾選關(guān)注指標(biāo)(默認(rèn)求和,注意按實(shí)際調(diào)整),切換圖形類型
動(dòng)態(tài)地圖(走馬燈):步驟如前,時(shí)間序列場(chǎng)景下添加時(shí)間為月份,有新指標(biāo)要加入地圖,首先添加圖層!
@切換平面地圖并調(diào)整右下角角度會(huì)讓地圖更直觀
————————————————————————————————————————————————————————
【Power View交互式儀表板制作】
45、Power View:可以快速簡(jiǎn)單地制作儀表盤,功能上類似于Excel中的數(shù)據(jù)透視圖表與切片器的組合工具,可以對(duì)數(shù)據(jù)進(jìn)行快速篩選查看,還可以用它制作出功能豐富的動(dòng)態(tài)圖表。
DAY6:【第7章: Excel商業(yè)智能分析報(bào)表初級(jí)案例】及【第8章: Excel商業(yè)智能分析報(bào)表高級(jí)案例】 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【財(cái)務(wù)杜邦分析儀】
46、下層指標(biāo)貼近業(yè)務(wù),從下層指標(biāo)找到影響變化的原因,從原因點(diǎn)做出指導(dǎo)業(yè)務(wù)決策,改善下層指標(biāo)趨勢(shì),從而改善上層指標(biāo)優(yōu)化。
@添加環(huán)比變化及圖標(biāo)集,可知本月趨勢(shì)變好變壞
@連接圖片形式粘貼:只支持單元格(組),圖片里的數(shù)據(jù)可以隨變化同步更新 + 圖表想實(shí)現(xiàn)相同效果,需要復(fù)制圖表下方的所有單元格區(qū)域(利用Shift+方向鍵選?。?/span>
!控件之列表只支持豎向羅列選項(xiàng)
@Offset設(shè)置對(duì)齊原表的行數(shù)數(shù)字輔助列,是對(duì)人為制作的原表的一次核實(shí),保證數(shù)據(jù)分析過(guò)程的準(zhǔn)確性
@=IF(ISERROR(K2-L2)/L2,0,(K2-L2)/L2),嵌套一層Iserror可以保證當(dāng)除法計(jì)算分母為零時(shí),跳過(guò)計(jì)算報(bào)錯(cuò),直接返回0值
@滑珠圖:條形圖結(jié)合散點(diǎn)圖,條形圖是每行103%的數(shù)據(jù)做出來(lái)等長(zhǎng)“細(xì)軸”,散點(diǎn)圖的橫坐標(biāo)是指標(biāo)各行數(shù)值,縱坐標(biāo)分別為0.5/1.5/2.5/3.5/…,就可以讓各值形成滑珠落在細(xì)軸上
————————————————————————————————————————————————————————
【銷售管理分析儀】
47、銷售漏斗分析是關(guān)系型銷售運(yùn)營(yíng)管理(需要長(zhǎng)期維護(hù)銷售關(guān)系)中最為重要的分析方法,科學(xué)反應(yīng)商機(jī)狀態(tài)以及效率的重要的銷售管理模型。
銷售漏斗從發(fā)現(xiàn)商機(jī)開始到最終與客戶成交為止的銷售周期,按不同銷售進(jìn)度分為潛在、接觸、意向、明確、投入、談判、成交的銷售階段(具體依據(jù)企業(yè)經(jīng)營(yíng)模式不同而不同),進(jìn)而對(duì)每一個(gè)銷售階段進(jìn)行精準(zhǔn)管理。銷售漏斗管理的目的是讓每個(gè)階段的銷售商機(jī)都盡可能順利地過(guò)渡到下一個(gè)銷售階段,只有將每一個(gè)銷售階段的商機(jī)管理好,才能實(shí)現(xiàn)盡可能多的銷售機(jī)會(huì),靠近漏斗的最底層,達(dá)成企業(yè)銷售最大化的目標(biāo)。本質(zhì)是企業(yè)風(fēng)險(xiǎn)管理,直接關(guān)系企業(yè)的盈虧狀況,是企業(yè)銷售環(huán)節(jié)的生命線。
環(huán)節(jié)分析的目的是提前發(fā)現(xiàn)各種銷售階段潛在的商機(jī)以及隱藏的風(fēng)險(xiǎn),在通過(guò)分析將風(fēng)險(xiǎn)明確化,以指導(dǎo)銷售人員及時(shí)做出銷售策略調(diào)整(補(bǔ)充新商機(jī)OR現(xiàn)有商機(jī)盡快推進(jìn)),達(dá)到銷售資源最優(yōu)化和商機(jī)資源轉(zhuǎn)換最大化。
銷售管理分析儀可以幫助洞察環(huán)節(jié)風(fēng)險(xiǎn),快速掌握問(wèn)題所在,快速做出商業(yè)決策判斷,得出詳細(xì)結(jié)論,做出明確對(duì)策。
銷售管理分析多維度模型:Power Query連接多部門多數(shù)據(jù)源,Power Pivot搭建統(tǒng)一模型
- 客戶維度 - 產(chǎn)品維度 - 地域維度 - 銷售維度 - 渠道維度 - 商機(jī)維度
!銷售直接關(guān)系企業(yè)營(yíng)收,營(yíng)收又是企業(yè)的生命線
!涉及業(yè)務(wù)活動(dòng)多,還涉及渠道或合作廠商的外部維度
!主要管理維度是商機(jī)維度,由銷售人員手動(dòng)錄入,有主觀因素和分析難度
-> 了解業(yè)務(wù)特點(diǎn),理清業(yè)務(wù)流程
1)設(shè)計(jì)思路 - 故事序章:儀表盤展現(xiàn)總體趨勢(shì)問(wèn)題
- 地域維度達(dá)成率
- 產(chǎn)品維度達(dá)成率
- 行業(yè)/領(lǐng)域維度達(dá)成率
@到達(dá)高階段的商機(jī)是銷售管理者最為關(guān)注的指標(biāo),“階段4以上”是已明確客戶購(gòu)買意向的商機(jī) -> 達(dá)到1.5倍以上的銷售目標(biāo)值才預(yù)計(jì)可以完成本期銷售目標(biāo);“階段5以上”是銷售成本投入階段為與某客戶達(dá)成成交目的(具有分配銷售人員、搞好銷售關(guān)系、投入資金和人員的支持) -> 達(dá)到1.2倍以上的銷售目標(biāo)值才預(yù)計(jì)可以完成本期銷售目標(biāo)
2)設(shè)計(jì)思路 - 故事展開:瀑布圖展現(xiàn)各階段占比情況
了解高低不同階段商機(jī)占比情況以及商機(jī)金額總量的數(shù)值情況。如果低階段商機(jī)過(guò)多,說(shuō)明商機(jī)向高階段轉(zhuǎn)換時(shí)比較乏力 -> 間接標(biāo)明銷售人員需要積極把手頭現(xiàn)有商機(jī)向前推進(jìn);另外若是商機(jī)總量總值過(guò)低,說(shuō)明商機(jī)總量不足 -> 做出補(bǔ)充新商機(jī)進(jìn)來(lái)的銷售決策
3)設(shè)計(jì)思路 - 故事高潮:動(dòng)態(tài)的組合柱狀圖形成周變化圖了解單個(gè)銷售階段商機(jī)構(gòu)成,目的為上一步最終決策提供數(shù)據(jù)依據(jù)
3)設(shè)計(jì)思路 - 故事尾聲:了解細(xì)節(jié)
對(duì)詳細(xì)信息進(jìn)行總結(jié),可供銷售管理者對(duì)某一個(gè)銷售人員做出更為細(xì)致的銷售指導(dǎo),使得大的銷售策略方便更順利地展開
MySQL數(shù)據(jù)庫(kù)入門
DAY7:【第1&2章: Mysql安裝、序章】&【第3章: 數(shù)據(jù)庫(kù)、數(shù)據(jù)表與字段操作】#CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師
【MySQL】
數(shù)據(jù)庫(kù)是存儲(chǔ)、調(diào)用、分析數(shù)據(jù)的倉(cāng)庫(kù),主要分為關(guān)系型數(shù)據(jù)庫(kù)和非關(guān)系型數(shù)據(jù)庫(kù)。關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)稱為RDBMS。
數(shù)據(jù)庫(kù)是表的集合,帶有相關(guān)的數(shù)據(jù);一個(gè)表是多個(gè)字段的集合;一個(gè)字段是一列數(shù)據(jù),由字段名和記錄兩個(gè)部分組成,記錄是實(shí)際數(shù)據(jù)信息。
子商業(yè)數(shù)據(jù)分析中使用的絕大部分?jǐn)?shù)據(jù)都來(lái)自于企業(yè)數(shù)據(jù)庫(kù),企業(yè)數(shù)據(jù)庫(kù)多為關(guān)系型數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)負(fù)責(zé)數(shù)據(jù)收集、數(shù)據(jù)整合、數(shù)據(jù)調(diào)用等工作。
@幾乎所有數(shù)據(jù)分析工具都支持調(diào)用數(shù)據(jù)庫(kù)中數(shù)據(jù),將所需要分析的數(shù)據(jù)在數(shù)據(jù)庫(kù)中整合、處理和調(diào)用是最為理想的,處理后的數(shù)據(jù)也可被其他所有數(shù)據(jù)分析類工具所使用的,推廣至其他工具中去。
關(guān)系型數(shù)據(jù)庫(kù):數(shù)據(jù)庫(kù)應(yīng)用的主流,以行和列的形式存儲(chǔ)數(shù)據(jù),一系列的行和列組成表,一組表組成了數(shù)據(jù)庫(kù),當(dāng)前主流的關(guān)系型數(shù)據(jù)庫(kù)有Oracle、MySQL、IBM DB2、Microsoft SQL Server等。
操作關(guān)系型數(shù)據(jù)庫(kù)時(shí)使用結(jié)構(gòu)化查詢語(yǔ)言,簡(jiǎn)稱SQL,是一種數(shù)據(jù)庫(kù)查詢和程序設(shè)計(jì)語(yǔ)言,用于存取數(shù)據(jù),計(jì)算、查詢、更新和管理數(shù)據(jù)庫(kù)系統(tǒng)。
————————————————————————————————————————————————————————
【MySQL數(shù)據(jù)庫(kù)操作】
1、創(chuàng)建數(shù)據(jù)庫(kù):在數(shù)據(jù)庫(kù)系統(tǒng)中劃分一塊空間,用來(lái)存儲(chǔ)相應(yīng)的數(shù)據(jù)
create database {數(shù)據(jù)庫(kù)名稱};
!每一個(gè)完整語(yǔ)句后加上“;”終止符
2、查看創(chuàng)建好的數(shù)據(jù)庫(kù)
show create database {數(shù)據(jù)庫(kù)名稱};
3、查看所有數(shù)據(jù)庫(kù)列表
show databases;
4、使用數(shù)據(jù)庫(kù)
use {數(shù)據(jù)庫(kù)名稱};
5、刪除數(shù)據(jù)庫(kù)
drop database {數(shù)據(jù)庫(kù)名稱};
@注釋方法:“-- ”或者“#”之后寫注釋,注意第一個(gè)最后有一個(gè)空格
!Excel編碼方式為ANSI,MySQL默認(rèn)編碼方式為utf8,兩者連接時(shí)需要先將編碼方式統(tǒng)一!
————————————————————————————————————————————————————————
6、創(chuàng)建數(shù)據(jù)表
數(shù)據(jù)表是數(shù)據(jù)庫(kù)存儲(chǔ)及操作數(shù)據(jù)的基本單位,承載數(shù)據(jù)的容器。數(shù)據(jù)庫(kù)由多個(gè)數(shù)據(jù)表構(gòu)成,每張數(shù)據(jù)表存儲(chǔ)多個(gè)字段,每個(gè)字段由不同的字段名及記錄構(gòu)成,每個(gè)字段有自己的數(shù)據(jù)結(jié)構(gòu)及約束條件。
@表為了在最大程度上保證數(shù)據(jù)資源的準(zhǔn)確、完整以及高效利用,在表中必須嚴(yán)格規(guī)定每個(gè)字段的相關(guān)屬性,無(wú)論是字段的數(shù)據(jù)類型還是約束條件,都要嚴(yán)謹(jǐn)對(duì)待!創(chuàng)建時(shí)必須按業(yè)務(wù)要求指定設(shè)置好!
@char(#)--固定長(zhǎng)#個(gè)字節(jié)的文本型字段
varchar(n)--可變長(zhǎng)最長(zhǎng)為n個(gè)字節(jié)的文本型字段
#創(chuàng)建并使用test數(shù)據(jù)庫(kù)
create database test;
use test;
#創(chuàng)建員工信息表
create table emp(
depid char(3),
depname varchar(20),
peoplecount int
);
#查看表是否創(chuàng)建成功
show tables;
#刪除數(shù)據(jù)表
drop table emp;
————————————————————————————————————————————————————————
7、數(shù)據(jù)類型
位:bit,是電子計(jì)算機(jī)中最小的數(shù)據(jù)單位,每一位的狀態(tài)只能是0或1
字節(jié):Byte,由8個(gè)二進(jìn)制位構(gòu)成1個(gè)字節(jié),它是存儲(chǔ)空間的基本計(jì)量單位
數(shù)據(jù)類型:不同的數(shù)據(jù)類型具有不同的字節(jié)長(zhǎng)度;字節(jié)長(zhǎng)度越長(zhǎng),能取值的數(shù)值區(qū)間也就越大。
所謂“一把鑰匙開一把鎖”,字段的數(shù)據(jù)類型限定了只有與其相匹配的數(shù)據(jù)信息,才能錄入到字段中來(lái),以保證字段的完整性和準(zhǔn)確性。
1)整數(shù)型:INT()、TINYINT、SMALLINT、MEDIUMINT、BIGINT,括號(hào)里設(shè)置最大顯示寬度,并不限制實(shí)際取值范圍和占用空間
e.g. INT(11),包括正負(fù)號(hào)和十位數(shù)
@均存在有(正負(fù))符號(hào)的和無(wú)符號(hào)的。如果在數(shù)據(jù)類型后加上UNSIGNED屬性可以禁止負(fù)數(shù)
2)小數(shù)型:FLOAT(M,D)(4字節(jié),默認(rèn)為(10,2))、DOUBLE(M,D)(8字節(jié),默認(rèn)為(16,4))、DECIMAL(M,D)——M是顯示寬度,D是小數(shù)位數(shù)
@均只能是有符號(hào)的
3)日期時(shí)間型:DATE、DATETIME(YYYY-MM-DD HH:MM:SS格式)、TIME、TIMESTAMP(時(shí)間戳,例如20241007153600)、YEAR(2位或4位,2位是1970~2069,4位是1901~2155,默認(rèn)四位)
4)字符串型:CHAR(M)、VARCHAR(M)、BLOB或TEXT、TINYBLOB或TINYTEXT、MEDIUMBLOB或MEDIUMTEXT、LONGBLOB或LONGTEXT、ENUM
@CHAR是固定長(zhǎng)度,VARCHAR是可變長(zhǎng)度(L+1,需要占用一位存儲(chǔ)長(zhǎng)度信息),長(zhǎng)度為1-255,注意指定的字符串長(zhǎng)度M一定要大于實(shí)際輸入數(shù)據(jù)的最大長(zhǎng)度,這樣才能完成保存輸入數(shù)據(jù)信息 -> 數(shù)據(jù)長(zhǎng)度不一時(shí),優(yōu)先選用VARCHAR;長(zhǎng)度出入不大時(shí),CHAR比VARCHAR運(yùn)行效果更佳
————————————————————————————————————————————————————————
8、約束條件
約束條件是在表上強(qiáng)制執(zhí)行的數(shù)據(jù)檢驗(yàn)規(guī)則,可用來(lái)保證創(chuàng)建的表的數(shù)據(jù)完整和正確性,類似不同大小篩眼的篩子。
1)PRIMARY KEY 主鍵約束:主鍵又稱主碼,是數(shù)據(jù)表中一列或多列的組合。主鍵約束要求主鍵列的數(shù)據(jù)必須是唯一的并且不允許為空,非空和唯一,保證表中每行記錄都不重復(fù)。使用主鍵,能夠唯一的標(biāo)識(shí)表中的一條記錄,還可以加快數(shù)據(jù)庫(kù)查詢的速度。
字段名 數(shù)據(jù)類型 PRIMARY KEY
- 單字段主鍵
e.g. depid char(3) primary key,
- 多字段聯(lián)合主鍵
e.g. primary key(depname, depid)
2)NOT NULL 非空約束:要求字段的值不能為空,非空。
字段名 數(shù)據(jù)類型 NOT NULL
e.g. depname varchar(20) not null,
3)UNIQUE 唯一約束:要求該列的值必須是唯一的,唯一;允許但只能出現(xiàn)一個(gè)空值;一個(gè)表中可以有多個(gè)字段聲明為唯一的,確保數(shù)據(jù)表的一列或幾列不出現(xiàn)重復(fù)值。
字段名 數(shù)據(jù)類型 UNIQUE
e.g. peoplecount int unique
4)AUTO_INCREMENT 自增字段:一個(gè)表只能有一個(gè)自增字段,必須為主鍵的一部分,默認(rèn)從1開始自增,步長(zhǎng)為1
字段名 數(shù)據(jù)類型 AUTO_INCREMENT
e.g. #創(chuàng)建整數(shù)型自增主鍵
id INT PRIMARY KEY AUTO_INCREMENT
5)DEFAULT 默認(rèn)值:當(dāng)插入記錄時(shí),如果沒(méi)有明確為字段賦值或空值時(shí),系統(tǒng)會(huì)自動(dòng)按默認(rèn)值填充賦值
字段名 數(shù)據(jù)類型 DEFAULT 默認(rèn)值
e.g. depname varcahr(20) default'-',
@標(biāo)準(zhǔn)SQL用''單引號(hào)引用字符串,MySQL拓展至''或""單雙引號(hào)皆可
@desc {表名}——可見(jiàn)按字母排序后各字段的數(shù)據(jù)類型及約束條件
——————————————————————————————————————————————————————
9、向數(shù)據(jù)表中填充數(shù)據(jù)
1)(手動(dòng)錄入)insert into:
insert into 表名(字段1, 字段2, ...)values(),(),(), ...
!字段排列順序要與建表時(shí)建立字段順序一致
e.g. #插入數(shù)據(jù)
insert into fruits(f_id,s_id,f_name,f_price)
values('a1',101,'apple',5.2),
('b1',101,'blackberry',10.2),
……
('b5',107,'xxxx',3.6);
2)(外部數(shù)據(jù)源文件批量導(dǎo)入)
e.g. #為Monthly_Indicator表導(dǎo)入外部txt文件,按絕對(duì)路徑(左斜斜杠分隔),指定Tab制表符為分隔符,忽略數(shù)據(jù)文件中第一行(字段名)
load data local infile '文件路徑.txt'
into table Monthly_Indicator
fields terminated by '\t'
ignore 1 lines;
3)從企業(yè)數(shù)據(jù)庫(kù)直接導(dǎo)入:先聯(lián)系數(shù)據(jù)庫(kù)管理員授權(quán)權(quán)限
-> 檢查表數(shù)據(jù):對(duì)導(dǎo)入表中的數(shù)據(jù)一般從導(dǎo)入內(nèi)容、導(dǎo)入數(shù)據(jù)總行數(shù)以及表結(jié)構(gòu)三方面進(jìn)行檢查
e.g.
#檢查導(dǎo)入內(nèi)容Monthly_Indicator
Select * from Monthly_Indicator;
#檢查導(dǎo)入數(shù)據(jù)總行數(shù)Monthly_Indicator
Select count(*) from Monthly_Indicator;
#檢查表結(jié)構(gòu)
Desc Monthly_Indicator;
@主鍵選擇需要保證聚焦明確分析對(duì)象的描述條件,需要考慮是否多個(gè)主鍵以限定
————————————————————————————————————————————————————————
10、修改數(shù)據(jù)表
修改數(shù)據(jù)庫(kù)中已經(jīng)存在的數(shù)據(jù)表的結(jié)構(gòu),包括修改表名、修改字段數(shù)據(jù)類型或字段名、增加或刪除字段、修改字段的排列位置等。使用alter table語(yǔ)句進(jìn)行修改。
e.g.
#將數(shù)據(jù)表emp改名為empdep
alter table emp rename empdep;
#將數(shù)據(jù)表empdep中depname字段的數(shù)據(jù)類型由varchar(20)修改成varchar(30)
alter table empdep modify depname varcahr(30);
#將數(shù)據(jù)表empdep中depname字段的字段名改為dep
alter table empdep change depname dep varchar(30);
#將數(shù)據(jù)表empdep中dep字段的字段名改回為depname,并將該字段數(shù)據(jù)類型改回為varchar(20);
alter table empdep change dep depname varchar(20);
#為數(shù)據(jù)表empdep添加新字段maname,新字段數(shù)據(jù)類型為varchar(10),約束條件為非空
alter table empdep add maname varchar(10) not null;
@change相對(duì)modify,可以同時(shí)修改數(shù)據(jù)名(重新命名)及數(shù)據(jù)類型,只需要修改數(shù)據(jù)類型就用modify
#將數(shù)據(jù)表empdep中maname字段的排列順序改為第一位
alter table empdep modify maname varchar(10) first;
#將數(shù)據(jù)表empdep中maname字段的排列順序改到depid字段之后
alter table empdep modify maname varchar(10) after depid;
#刪除maname字段
alter table empdep drop maname;
#CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師 #CDA學(xué)習(xí)打卡 #CDA數(shù)據(jù)分析師





