Excel Power BI商業智能

DAY1：【數據分析概述】&【分析工具Excel概述】 #CDA學習打卡 #CDA數據分析師

1、什么是數據分析

2、知識、智慧、信息和數據的不同定義

3、SQL和Excel在數據分析中的定位

4、業務人員、IT技術人員及分析人員的不同職責

5、數據化驅動業務對企業的意義

6、數據分析方法分類 - 業務數據分析、數據挖掘分析、大數據分析

7、業務數據分析流程

8、商業智能（BI）和商業洞察

9、數據可視化分析方法 - 對比分析、結構分析、透視分析、其他分析方法

10、可視化&交互式是數據分析真正移交閱讀者主動權的特點

11、Excel的原理、內外部對象結構、歷史、五大必備功能和Power BI四項功能（Power Query、Power Pivot、Power View和Power Map）

DAY2：【第二章：Excel基礎】&【第三章：Excel數據加工處理及基本公式介紹】#CDA學習打卡 #CDA數據分析師

【“表格”結構數據與“表”結構數據】

12、表格結構數據：以單元格為基本數據存儲及操作單位 -> 處理批量數據效率低

表結構數據：以字段（列）為基本數據存儲及操作單位，比單元格高一個維度 -> 易于處理批量大量數據，是數據分析的全場景

e.g. Excel中的已建表，方便批量進一步操作；還可以導入關聯外部數據源并與之同步更新，在不同表之間進行快速連接，包括通過Power Query和Power Pivot進一步處理。

————————————————————————————————————————————————————————

【Excel數據透視圖表與切片器】

13、數據透視表：由“篩選器”、“行/列標簽”、“值”構成的二維值匯總表；易操作性、可計算性、數據處理方法多

@數據透視表用來將一維原始數據轉換為由行列構成的二維值匯總表。

14、數據透視圖：基于數據透視表生成的圖表，用戶可以通過鼠標拖拽來快速更改圖標顯示結果；沒有Excel基本圖表類型中的散點圖和氣泡圖。

@數據透視圖既可以通過數據透視表生成也可以直接引用一般表格數據生成，此時會自動生成與其對應的數據透視表，一一對應。

！圖和表的升降排序順序相反

15、切片器（2010版本以上）：當切片器與數據透視圖表關聯后，對切片器進行選擇時，數據透視表會一起發生變化。

@切片器與數據透視圖表的組合應用是生成BI報表高度交互性可視化界面的基礎。

！只要是同一個底層數據源下，不同數據透視圖表都可以通過同一個切片器（右鍵，報表連接）控制

————————————————————————————————————————————————————————

【Excel條件格式與迷你圖】

16、條件格式

1）以單元格值為基礎，設定條件格式：

e.g. “單元格值介于15和25之間的單元格標為黃色”，全選單元格，只為包含以下內容的單元格設置格式，單元格值-介于-15到25，格式為填充黃色

2）以公式返回值為基礎，設定條件格式：

e.g. “第二列值大于第一列值時將第一列中單元格標為黃色”，只選第一列，使用公式確定要設置格式的單元格，=B9>A9（勿鎖定?。?，格式為填充黃色

e.g. “將位于偶數行的單元格標為黃色”，全選單元格，使用公式確定要設置格式的單元格，=MOD(ROW(),2)=0(ROW函數返回行號），格式為填充黃色

17、圖標集

用圖標的不同狀態來標注當前單元格值與閾值間的對比關系。注意，1）三種狀態、四種狀態和五種狀態三種類型的圖標集；2）數字、百分比、公式及百分點值（PERCENTILE）四種閾值類型。

@紅黃綠三色“紅綠燈”圖標使用最為頻繁，如果選擇困難可以優先使用

18、數據條、色階

數據條：用數據條的長短來標注各單元格值的大小關系，以一組單元格中的最大值為最長條形，用數據條的長短變化來標注其他單元格值與次單元格值的對比關系。

色階：用不同顏色變化來標注各單元格值的大小關系，以一組單元格中的最大值為最重顏色，用顏色變化來標注其他單元格值與此單元格值的對比關系。

@如果要利用數據條或色階來體現各部分值與匯總值之間的占比關系，選定單元格區域應包含匯總值，形象觀察部分和整體的關系

19、迷你圖

是放入單個單元格中的小型圖，代表所選內容中的一行數據；折線圖、柱形圖、盈虧圖。

@迷你圖可以使用在表格中與單元格結合使用，令報表閱讀者可以通過圖表直觀掌握到具體表格數據的對比趨勢，加深對抽象數據的理解及印象。

————————————————————————————————————————————————————————

【Excel函數公式】

20、函數公式

用來對單個單元格或指定單元格區域進行計算后返回計算結果，返回可以是數值、數組或單元格地址等。函數由返回值、等號、函數表達式、參數、操作符五部分組成。

OFFSET函數：以指定引用為參照系，通過給定偏移量返回新的引用區域，OFFSET(reference, rows, cols, height, width)（rows從上到下，cols從左到右，height是向下取m行，width是向右取n列）-> 用于名稱定義，e.g. 名稱指定到對應的圖表的名稱進行數據源使用，實現圖表動態展現效果。

21、嵌套函數

將內部函數的返回值作為外部函數的參數使用的方法，使用時內部函數的返回值數據類型一定要與外部函數的參數數據類型保持一致。

基本數據類型包括文本型text、數值型numerical、布爾型boolean（true或false，不是0或1）、數組array

e.g. IF(ISERROR(I2),"ERROR","OK")

————————————————————————————————————————————————————————

【Excel基本圖表】

22、基本圖表類型

柱形圖（基于分類）：1個變量；多個分類項目比較

柱形圖（基于時間）：1個變量；不同時點的數值比較，適用時間點偏少

條形圖：1個變量；多個分類項目比較

折線圖：1個變量；不同時點的數值比較，適用連續時間段

餅圖：1個變量；整體中各部分的占比

散點圖：2個變量；數據間的分布及關聯性，可添加回歸公式和趨勢線

氣泡圖：3個變量；數據間的聯系比較，用每個點的不同大小代表第三個變量

雷達圖：多個變量；多重數據間的比較，多重維度間平級關系

————————————————————————————————————————————————————————

【Excel常用函數】

23、

1）數學函數：SUMIF、SUMPRODUCT、MOD

2）文本函數：LEFT、MID、RIGHT、REPT

3）邏輯和引用函數：IF、ISERROR

4）查找函數：VLOOKUP、HLOOKUP、MATCH、INDEX

5）日期和時間函數：TODAY、WEEKDAY

6）統計函數：COUNTIF

ex1. 根據身份證號提取性別和出生年月并計算年齡

left()/mid()/right() mod() year(today()) =E3&"性"

ex2. 利用2015年節假日統計表計算具體日期及對應的星期數

date(year,mth,day) weekday(date(...))

ex3. 使用INDEX及MATCH組合函數查找標黃部分相關信息

@定位索引列前方列信息，用index()嵌套match()實現vlookup()的效果

- MATCH(lookup-value要查找的值, lookup-array查找連續區域, match-type查找方式，1升序/-1降序/0任意)

- INDEX(array查找區域, row-num返回值所在行號, column-num返回值所在列號，唯一時可不填)

ex4. 用HLOOKUP公式查找出H列標黃部分值

！橫向查找；！一般False精確匹配

HLOOKUP(lookup_value要查找的值, table_array查找區域, row_index_num返回數據在區域的第幾行數, range_lookup模糊匹配/精確匹配的T/F)

ex5. 使用VLOOKUP函數求I列、K列及M列標黃部分值，并用ISERROR與IF函數的嵌套函數處理異常值

VLOOKUP(lookup_value要查找的值, table_array查找區域, col_index_num返回數據在區域的第幾列數, range_lookup模糊匹配/精確匹配的T/F)

ISERROR返回Boolean值，注意存在錯誤時為T，無異常時為F

ex6. 用SUMPRODUCT函數計算H列標黃單元格值

將數組間的元素相乘后返回乘積之和

=SUMPRODUCT($D$2:$G$2,D4:G4)

=SUM(D2:G2*D4:G4)

按條件收入合計：

SUMIF(range, criteria, [sum_range])

多條件匯總：銷售人員為“蘇術平”且國家地區為“加拿大”的銷售收入總和

=SUMPRODUCT((($D$2:$D$50=$L$8)*($C$2:$C50=$C$3)),$G$2:$G$50)

#用*號在SUMPRODUCT中表示條件間的AND連接，用+號表示OR連接

按條件統計訂單數量：

COUNTIF(range, criteria)

用REPT在單元格內做虛擬條形圖，表現趨勢：

REPT(text返回值, number_times返回遍數)

————————————————————————————————————————————————————————

【Excel基本數據處理方法】

24、處理數據室數據分析流程中的起點，也是耗時最多的環節。

數據處理方法：

1）處理重復數據

針對單列數據識別重復行：

I. 使用公式COUNTIF

II. 排序關鍵字段后使用公式IF

III. 利用數據透視表計數匯總

針對多列重復的去重：

I. 數據-排序和篩選-高級

（Power Query，刪除行-刪除重復行）

II. 識別重復行后刪除

@需要結合業務需求決定刪除和保留重復行

2）處理不完整數據

查找缺失值：

I. 使用 Ctrl+F

II. 使用定位條件 Ctrl+G，定位格式為“空值”

！眼睛看上去是空白的不一定就沒有值

III. 利用排序篩選

處理缺失值：

I. 使用0替換數值類缺失值

II. 使用平均值替換數值類缺失值

III. 刪除含有缺失值的記錄或不對此類記錄進行操作

IV. 暫時保留缺失值行，（填充色區分或添加輔助列標注，方便快速定位），在有必要時再進行處理

3）處理異常值

！{編號}一般是文本型格式，位數相同，不合系列邏輯很可能是異常值。e.g. 原始數據0001轉入Excel可能作為數值型變成1，這時需要轉回文本型并補齊前方的000

！0值在日期格式下是“1900/1/0”，確認異常值

！數值型字段出現“0.0.XXXX”或數量級差距巨大可能是誤輸入的異常值 -> 可以設置平均值的倍數作為閾值；或散點圖的outlier；或正態分布3倍標準差外的outlier……

4）數據分組

利用先計數再設置不同計數對應的分檔，用輔助列+VLOOKUP對應區間定義文字，最終返回區間定義文字導目標列

5）轉換變量類型

處理格式錯誤數據：

I. 設置格式

II. 使用分列功能，列數據格式為“文本”

III. 使用公式補全信息后，轉換成日期型

2001-10-02=LEFT(C2,4)&"-"&MID(C2,5,2)&"-"&RIGHT(C2,2)

@常需要相互轉換的數據類型包括文本型、日期型、數值型

6）數據標準化

I. Min-Max標準化：新數據=（原數據-極小值）/（極大值-極小值）

@適用于量級差異大的相同維度間進行比較

II. 使用標準分進行標準化：標準分=（原始分-平均分）/ 標準差 STDEV

@適用于評價標準或許不同的相同維度間進行比較

7）設置變量權重

III. 加權平均

利用交叉表設置權重：

- 縱向和橫向對比，橫向重要則為1，縱向重要則為0

- 橫向加總，每項都加1，保證數值最低項合計值不為零，以免沒有意義無法后續進行除法計算

- 每個階段合計值/合計總值*100%

- 加權平均值 = 變量1*變量1的權重 +……+變量n*變量n的權重

主要數據類型：文本型、整數型、小數型、布爾型、日期型、其他 類型（會計、特殊）

主要變量類型：名義型（彼此間沒有順序關系，只表示分類的數據，例如姓名）、有序型（有順序關系的數據，例如優良中差）、連續型（包含若干小數位，取值密集，例如溫度 ）

+思考題：用唯一的數字區分開唯一的業務類型，形成映射關系，方便進一步數據處理和分析

取一個1-3之間的隨機小數：=RAND()*(3-1)+1 取一個1-3之間的隨機整數：=ROUND(RAND()*(3-1)+1,0)

————————————————————————————————————————————————————————

【Excel基礎描述性統計類圖表】

頻數：是落在各類別種的數據個數

頻率：是各類別頻數與總頻數之比

@頻數和頻率分別從絕對數和相對數上，反映出數據在各變量值上的分布狀況。

25、直方圖

用來展現數據在不同組距間分布狀態的圖表，組距=（最大值-最小值）/ 組數，組數根據實際業務情況設置。

！柱形圖看高度， 直方圖看面積

26、盒須圖（箱型圖）

用來體現數據分散情況。將數據由小到大排列并分成四等份，處于三個分割點位置的數值就是四分位數。

上邊緣 = Q3+1.5*（Q3-Q1）

下邊緣 = Q3-1.5*（Q3-Q1）

DAY3：【第4章: Power Query多源大規模數據加工處理方法】 #CDA學習打卡 #CDA數據分析師

【Power Query概述及導入多源數據】

27、Power Query：多源大規模數據加工處理方法

- 提取整合多數據源數據，包括各種關系型數據庫、Excel文件、txt格式（制表符Tab分割）及csv格式（逗號comma分割）的等文本文件（只有數據信息&分隔符，沒有其他信息）、Web頁面、Hadoop的HDFS等等

- 突破Excel表格的數據限制，可快速處理幾百萬甚至上千萬行（大約2G）的數據

- 提供豐富的數據處理分析功能

- 可通過M函數靈活創建自定義數據處理及計算規則

- 創建好的數據處理流程可以無限次復用

@2016以上版本：數據-“獲取和轉換”功能組

！勿通過數據-獲取外部數據進行導入

數據導入方式一：僅創建連接，只是將外部數據源導入到Power Query的查詢引擎中，而在Excel表格界面中不會留下任何數據痕跡——適用于只需要在Power Query中進行數據處理，而不需要Excel表格界面中對導入數據進行預覽或單元格計算時，可節省文件容量，減少計算壓力

數據導入方式二：表，不僅可以將外部數據源導入到Power Query的查詢引擎中，同時在Excel表格界面中生成一份與Power Query相關聯的Excel工作表數據。在Power Query中發生的變化，點擊加載后Excel工作表中的數據也會隨之發生變化?！m用于需要在Excel表格界面中使用數據時

@“加載到…”支持兩種方式間的切換

@Power Query進行修改后，記得左上角點“關閉并上載”或“關閉并上載至…”（第一次加載時未設置加載規則的情況）

———————————————————————————————————————————————————————

【Power Query合并數據】

字段的集合就是數據表，數據表的集合就是數據庫。每張表字段首行叫標題行，標題行中內容叫字段名，第二到最后一行叫記錄，記載了數據內容。

存放最終合并結果的叫主表，為主表提供必要信息的表叫附表。

28、橫向合并數據：將附表中有而主表中沒有的字段信息合并至主表中，從而充實完善主表信息的方法。即合并列，添加新的字段進來。

選中主表，合并-合并查詢

1）確定用于合并兩表的關鍵字段（主附表中都有且值對應的上的字段，最好沒有重復值）

2）進行合并操作

3）選擇需要展示的字段信息（連接過來的Table附表展開后設置）

@：兩表若擔心同名字段存在，可以勾選上“使用原始列名作為前綴”

@1：當兩表中用于合并的關鍵字段值不是一一對應時，不同的聯接種類會出現不同的聯接結果（默認選“內部”，另一種是“左外部”）

@2：當關鍵字段中有重復值時，連接后的總行數為關鍵字段值重復出現次數的乘積

29、縱向合并數據：即添加行，在現有數據的基礎上添加記錄。

選中主表，合并-追加查詢，“兩個表”或“三個或更多表”

1）將有相同字段名的字段縱向合并到一起

2）將不同字段名的字段追加在最后

3）非匹配字段標記為“null”值

@Excel是否自動添加標題行：看導入的原始數據的第一行的數據類型是否和下方所有記錄數據的數據類型完全一致，是就會默認首行也是記錄并添加系統標題行 -> Power Query，轉換-將第一行用作標題

！一個字段只允許有一種數據類型，將文本型字段與數值型字段成功合并后，將會改變字段類型為文本型（因為數值可以變成文本，而文本無法變成數值?。。。?/span>

———————————————————————————————————————————————————————

【Power Query基本功能】

30、

1）“開始”選項卡：對數據進行上載查詢、增減行或列、拆分列、分組、改變數據類型、更改屬性、導入及合并外部數據等基本操作。

2）“轉換”選項卡：提供了針對行與列間的結構性加工處理功能、數據類型、格式等設置功能以及基本的函數計算功能。

選中目標列，轉換-任意列-填充，向上/向下

！空值很危險，null，不知道位數也不知道下一步該如何操作->轉換為已知狀態的真正控制，比如“-”

選中目標列，轉換-任意列-透視列，文本型只能計數

選中目標列，轉換-任意列-逆透視列-逆透視其他列，即從二維交叉表轉換為一維表

3）“添加列”選項卡：針對字段信息的加工處理，包括添加新字段、更改字段格式、位字段增加計算公式等（其中大部分功能與轉換選項卡中功能重復，但適用于新添加的字段上的處理）。

選中目標列，添加列-常規-自定義列，進行參數定義即可

4）“視圖”選項卡：用來顯示或隱藏“查詢編輯器“中的主要功能區域。

@查詢在Power Query 中應用的步驟都通過M函數形式保存在查詢設置中。

@“數據透視”：按照某種規則將數據進行匯總，匯總行列標簽結果形成二位匯總表

———————————————————————————————————————————————————————

【Power Query M函數】

31、編輯M函數（Power Query 80%的能力，提高重復性工作效率）方法：

600+個，專用于數據處理的編程語言，先要會改，后要會寫！M函數先繼承上一步處理的結果，在此基礎上進行目前本身處理，生成本身的處理結果，依次循環……

1）通過“編輯欄”、“添加自定義列”或“高級編輯器”(批量寫入M函數的處理邏輯）等功能了解M函數表達式寫法

2）根據需求重新編輯表達式程序

3）編輯錯誤通過“查詢設置”區域退回重新編輯

4）新建一個空查詢，再在fx編輯器中輸入“=#shared”可以查看M函數List列表，轉換-到表中 可以轉為Table表數據方便通過篩選按鈕查詢定位，例如Text. 或者Date. 等等

- 添加序號列：添加列-常規-索引列，Table.AddIndexColumn()

- 重排序的列：鼠標選中拖拽，Table.ReorderColumns()

-提取數據：添加列-從文本-提取，Text.Range()（從前一位往后取一位數?。?/span>

- 轉換數據類型：開始-轉換-數據類型，設置為“整數”Int64.Type,Table.TransformColumnTypes()

+文本數據類型：type text

+日期數據類型：type date

- 添加新字段：Table.AddColumn()

- 判斷奇偶：轉換-編號列-信息，設置為“偶數”

- 替換值：開始-轉換-替換值，Table.ReplaceValue()

32、Power Query高級編輯器

let-in結構，let中記錄的是每一步操作步驟，in里面記錄的是最終顯示出來的處理結果，let等式左邊是當前操作的結果值，右邊是操作內容，蛇形相連，各操作間用逗號隔開，最后一步操作后沒有逗號后接in部分！

！借鑒已有M函數寫新函數時，注意修改每行結果值，句末逗號與否，in顯示內容

@未來有新紀錄加進來，只需要將新紀錄加進Excel原始數據sheet中，上方功能欄找到數據-全部刷新，就可以按之前的Power Query邏輯得到最新更新后的數據

DAY4：【第5章: Power Pivot多維數據透視分析方法】 #CDA學習打卡 #CDA數據分析師

【Power Pivot 概述及導入數據】

透視是在指定維度下對指標值進行匯總觀測的方法。在海量原始數據面前，我們是得不到任何有用信息的，只有將其分類匯總后，才能得到這些數據背后隱藏的信息。這就是透視分析的意義。

在復雜的業務邏輯面前，非單一數據源的簡單數據分析需求，比如想看到不同銷售區域下每名不同銷售人員的銷售業績，與所有銷售人員的總平均業績的差距是多少，銷售人員業績比平均低時用紅色標注，高用綠色標注。其中，區域信息來自區域表，銷售業績信息來自于銷售表。

33、Power Pivot是一個加強版的數據透視工具，不僅在數據處理量上，更在透視規則及自定義規則上得到了大幅改善。不僅擁有比傳統數據透視表更加強大靈活的計算分析能力，還可以導入并關聯多種不同數據源的大量數據，并在內存中創建自己的多維數據模型（多個表關聯在一起的數據集合）。

- 搭建多維數據分析環境，形成統一的數據環境

- 突破數據行數限制（勿大于2G?。?/span>

- 簡潔的操作界面功能

- 強大的自定義功能（200+個函數）

@“數據模型”及Power Pivot中的數據

@從一個外部數據源導入一個表數據即在Power Pivot形成一個工作表

@從Power Query導入：在Power Query首次加載時，勾選“將此數據添加到數據模型”，再直接進入Power Pivot界面可見

@從Excel導入：選中目標區域，在Excel工具欄找到Power Pivot-表格-添加到數據模型，再直接進入Power Pivot界面可見

————————————————————————————————————————————————————————

【Power Pivot 搭建多維數據分析模型】

34、多維數據模型：又叫多維數據集、立方體（可以從多角度多側面，用數據全面映射某種業務的實際狀況），指的是相互間通過某種聯系被關聯在一起的不同類別的數據集合。涉及該業務與之相連的其他業務的數據全部整合到一起，創建全面的、統一的多維數據環境才能深入全面觀察某個業務問題，搭建環境越充實，分析結果的可參考性越高。

@Power Pivot中的操作都是最終Excel數據透視表的前期準備工作

多維數據集搭建方法：

1）明確表與表之間用于匹配的關鍵字段

2）在關系圖視圖模式下，選中某個表的關鍵字段拖拽向另一個表的關鍵字段

注意：

@1 兩表關聯時必然有一個主表（*）和一個附表（1）。用于連接兩表的兩個關鍵字段中，至少有一個關鍵字段是沒有重復值的。（Power Pivot的強制要求，不允許“多對多”）

@2 如果兩個關鍵字段都沒有重復值，連接時先選中的表為主表，形成“一對一”關系 。

@3 系統會將關鍵字段中有重復值的表作為主表，沒有重復值的表為附表，形成“多對一”關系。在數據透視表中，只有當行/列標簽均來自附表時，附表才能提供值字段。（行/列標簽有一個來自主表就會報錯?。?/strong>

-> 以上連接規則將用在創建的數據透視表中：

主頁-數據透視表

————————————————————————————————————————————————————————

【Power Pivot 創建層次結構】

35、在不同字段間創建層次結構方法：

1）在關系圖視圖模式下選中父級字段后，鼠標右鍵選中“創建層次結構”

2）為層次結構命名

3）將子級字段拖拽到創建好的層次結構中父級字段的下方

@創建層次結構時一定要符合業務邏輯！

————————————————————————————————————————————————————————

【Power Pivot DAX表達式】

36、DAX數據分析表達式：

Power Pivot的特有函數集，用于在Power Pivot編輯器內為數據透視表創建透視規則（復雜條件下的篩選或計算）以及增加新的字段內容。

1）DAX表達式的結果應用在數據透視表中

2）DAX表達式的結果作用于整列或者表中所有行

3）注意：

@Power Query和Power Pivot中處理的都是表結構數據，有基于表（字段的合集）和字段的數據類型，區分于Excel中的文本型、數值型等數據類型

- 表名用' '引用，字段名（列名）用[ ]引用，例如'商機記錄'[贏單率]

- 要注意函數表達式中參數的數據類型，尤其是要將“表”與“數值”正確區分

- 與Excel公式相同，除了直接在編輯器的公式區域輸入公式外，還可以單擊公式編輯欄前的fx圖標啟動插入函數對話框，在對話框中選擇需要的函數使用

- 表達式中的函數名不分大小寫（只有Power Query的M函數區分大小寫，每個單詞首字母大寫）

e.g.

高贏單率：[贏單率] = 0.75

低風險：[有無拖欠還款情況] = “無”

@右側DAX編輯新字段：針對左側預覽區域內某一個字段中的每一行值來進行計算時使用

[贏單率文字替換]=switch('商機記錄'[贏單率],0.15,"低",0.25,"低",0.5,"中",0.75,"高","-")

（！先將兩表創建好連接關系才能使用related）

[有無拖欠]=related('商機相關企業信息'[有無拖欠還款情況])

@下方DAX編輯匯總規則：針對上方預覽區域的某一個字段整個字段創建匯總規則時使用（建議字段正下方書寫?。?> 只有在創建數據透視表中才能發揮作用

高贏單率低風險商機金額加總值:=calculate(sum('商機記錄'[商機金額(M)]),filter('商機記錄','商機記錄'[贏單率文字替換]="高"),filter('商機記錄','商機記錄'[有無拖欠]="無"))

總商機金額:=sum('商機記錄'[商機金額(M)])

@注意等號前的冒號！注意字段名可用鼠標點選整列完成

高贏單率低風險商機金額百分比:=[高贏單率低風險商機金額加總值]/[總商機金額]，在主頁-格式設置-格式，設置為“百分比”

————————————————————————————————————————————————————————

【Power Pivot 使用KPI】

37、KPI即在Excel數據透視表中創建圖標集的方法，圖標集方便用戶快速了解數據的好壞程度。常見的三色圖標有兩個閾值來區分，最小和最大閾值

1）在編輯器下方公式區域內用公式為需要創建KPI的字段指定匯總規則（至少一個DAX）

2）點擊“創建KPI”在“關鍵績效指標（KPI）”對話框中設定KPI規則

3）定義KPI目標值的方法有“度量值”及“絕對值”兩種方法：

度量值 - （需要兩個匯總規則）通過一種匯總規則和另外一種匯總之間的比對關系，以兩種匯總值之間的百分比為判斷依據的KPI規則

絕對值 - 以一個匯總規則與某個絕對數值之間的大小關系為判斷依據的KPI規則

e.g.

平均銷售金額:=average([銷售金額])

城市維度總平均金額:=calculate(average([銷售金額]),all('表1'[城市]))

@ALL函數：“所有XX的…”

@目標即理想中的狀態

選中[平均銷售金額]，主頁-創建KPI，度量值：城市維度總平均金額，100%-120% -> 生成紅綠燈圖標

銷量:=count('表1'[訂單ID])

選中[銷量]，主頁-創建KPI，絕對值：10000，1000-4000 -> 生成紅綠燈圖標

DAY5：【第6章: Excel高級數據可視化方法精講】 #CDA學習打卡 #CDA數據分析師

【數據可視化分析方法】

38、對比分析

1）縱向對比：時間序列分析，同一指標不同時間下的變化趨勢（不同間斷分散的時間段用柱狀圖，連續連貫密集的時間點用折線圖）

2）進度分析：展現目標完成情況的分析方法，儀表盤、圖標圖、溫度圖、游標圖、方塊圖……

3）橫向對比：在同一時間下，部分與總體，部分與部分或是對象與對象之間的對比，常用餅圖、環形圖（橫向占比對比，部分與部分）、條形圖（橫向值對比，部分與總體）、分段折線圖（橫向趨勢對比，對象與對象）等

@與縱向對比的區別：縱向是不同時間條件下同一指標的對比情況，橫向是同一時間條件下不同指標之間的對比情況

4）標準值、平均值及計劃值之間的對比，圖表類型選擇靈活

@子彈圖，常用于平均對比分析

@Excel簇狀柱形圖適用于實際值與計劃值（非定值）對比

5）同環比分析：同比即本期值與同期值之間的對比；環比即本期值與上期值之間的對比，常見簇狀柱形圖或折線圖，[同/環比增長率]增加圖標集

@一定要考慮業務場景去設計才有實際意義，例如餐飲業的額星期比日期更具有周期性意義

6）預警分析：用KPI分析、預警色填充單元格等方式對關鍵指標進行預警，了解關鍵值的變化及好壞程度。

7）***透視分析：使用數據透視圖表功能進行多維度、多層次、多規則的透視分析，通過對觀測指標的洞察，理解及掌握指標背后的實際業務情況及風險。

39、結構分析

1）構成分析：部分與整體間構成關系的分析方法，常見漏斗圖、瀑布圖、滑珠圖、餅圖、環形圖等。

2）杜邦分析：不僅適用于財務指標分析，而且可擴展延伸至各種關鍵指標間有明顯結構關系的業務分析中使用。

40、其他分析方法

1）變化分析：反映同一指標或多種指標狀態及數值變化情況的分析方法，常見組合圖表、指標構成圖等，是一種動態分析方法，要結合自身實際業務情況，盡可能直觀地選擇合適的圖表對變化中的指標（觀測值）進行展現

2）分組分析：用來展現關鍵指標在不同區間（組距）內的分布情況，常見直方圖

3）增維分析：將不同類型的圖表嵌套使用的方法，達到增加信息展現維度，擴展分析廣度的目的，例如折線圖與環形圖的嵌套圖表

————————————————————————————————————————————————————————

【專業自定義圖表創建及和應用方法】

數據可視化：通過對數據的圖形化展現來直觀快速地傳遞復雜的數據信息，體現數據之美的手段。數據分析人員通過使用數據可視化技巧來令數據變得好看（一層是容易看容易理解的意思，另一層是美觀賞心悅目的意思）。因為人類對視覺信息更強的認知水平，好看的圖表能將信息準確、直觀、生動地傳遞給閱讀者，不僅能幫其迅速理解圖表意思，把握關鍵信息，還能加深其對圖表的印象，達到過目不忘的效果。在實際應用中，好看的圖表能幫助決策者快速把握業務情況，并在重要的商業決策中做出正確判斷。

41、自定義圖表：為了增強圖表可視化能力而應用的一種方法。

1）不是圖表的圖表制作方法：使用Excel基本制圖功能之外的功能創建的數據可視化圖表，即迷你圖表。迷你圖表多與報表中其他單元格數據結合使用，表格+ 迷你圖表的同時使用的報表，不僅能直觀傳遞可視化信息，還能完整精確地傳遞數據信息，達到“表圖合一”的效果。

2）嵌套圖表的制作方法：將主圖表與其他圖表或其他圖片、圖形嵌套在一起形成的新圖表，目的一是為了改變原有圖表的展現形式，二是為了增加原有圖表的展示維度。

- 半圓形儀表盤（親切感，會展現深刻認知，突出重要性使用）

- 多維折線圖（擴展維度）

3）基本圖表的再創新方法：以改變Excel基本圖表的格式及引用數據源數據排列規則等方式來改變設計意圖的展現形式，需要清晰的設計思路和靈活的展現技巧。

- 瀑布圖（堆疊柱形圖+下層占位部分無填充顏色）

- 分段折線圖（看得更為清晰，數據分組分拆成三列錯開）

————————————————————————————————————————————————————————

【交互式圖表制作及應用方法】

42、交互式圖表：又叫動態圖表，指能夠隨時響應用戶操作指令，改變展現結果的圖表。圖表從靜態變為動態后，分析的深度和廣度都將得到質的改變。

1）切片器與數據透視圖的組合應用：用切片器直接控制透視圖表（適用于2010版本以上帶切片器的透視圖表）

切片器創建方法：選中需要關聯的數據透視表或圖，插入-切片器，為切片器選擇字段（字段可多選）

@按住Ctrl鍵點選支持切片器選項多選

@切片器可占用區域小又希望完整展示所有選項時，可以右鍵格式設置中找到列數，設置為2列

更改切片器關聯方法：右鍵點擊切片器，選擇“報表鏈接”，選擇需要關聯的數據透視表或圖

2）控件與公式的組合應用：使用控件、公式，改變靜態圖標數據源（限制條件較多，但支持圖表類型廣泛），將控件作為交互平臺，將函數的返回值作為圖表的數據源，通過選擇控件來改變函數返回值，參照基準點發生的變化，實現圖表的動態展現效果的方法。

動態圖表制作步驟：選擇并設置控件（開發工具-插入-表單控件-組合框），準備圖表數據，創建圖表，測試

動態圖表制作邏輯：以控件返回值為基礎參考值，用offset等查找定位函數選擇圖表參考數據區域，將公式寫入創建名稱中作為圖表的參考數據使用，將圖表的數據源設定為創建的名稱

e.g.

=OFFSET($B$3,$A$3,1,1,7)，B3是表格首行首個單元格，A3是組合框的返回值結果

指定名稱定義：因為在圖表里無法直接寫公式，Excel工具欄中公式-名稱管理器-新建，命名并將引用位置設置為=OFFSET($B$3,$A$3,1,1,7)

再關聯圖表：圖表中右鍵，選擇數據，編輯，將系列值改為=柱形圖示例!{新建公式名稱}

動態圖表制作注意事項：

- 根據實際需要選擇適當的控件

- 根據業務需求決定動態圖表互動方式

- 需要注意不要將函數寫死，業務可能后續發生變化

- 圖標制作好后，一定要先進行測試，保證圖表結果的準確性

43、主要控件

1）選項按鈕

2）復選框

3）列表框

4）滾動條、數值調節按鈕

————————————————————————————————————————————————————————

【Power Map數據地圖創建及應用】

44、數據地圖是展現數據地理信息最為直觀的方法。Power Map是Power BI系列插件工具中用來生成數據地圖的工具，結合Bing地圖，支持用戶繪制可視化的地理和時態數據，并用3D方式進行分析。需要聯網與Bing地圖通信環境下才可使用Power Map。

Excel菜單欄，插入-Power Map-地圖

靜態地圖：右側區域圖層窗格，先選地理和地圖級別（下一步），再設置高度，勾選關注指標（默認求和，注意按實際調整），切換圖形類型

動態地圖（走馬燈）：步驟如前，時間序列場景下添加時間為月份，有新指標要加入地圖，首先添加圖層！

@切換平面地圖并調整右下角角度會讓地圖更直觀

————————————————————————————————————————————————————————

【Power View交互式儀表板制作】

45、Power View：可以快速簡單地制作儀表盤，功能上類似于Excel中的數據透視圖表與切片器的組合工具，可以對數據進行快速篩選查看，還可以用它制作出功能豐富的動態圖表。

DAY6：【第7章: Excel商業智能分析報表初級案例】及【第8章: Excel商業智能分析報表高級案例】 #CDA學習打卡 #CDA數據分析師

【財務杜邦分析儀】

46、下層指標貼近業務，從下層指標找到影響變化的原因，從原因點做出指導業務決策，改善下層指標趨勢，從而改善上層指標優化。

@添加環比變化及圖標集，可知本月趨勢變好變壞

@連接圖片形式粘貼：只支持單元格（組），圖片里的數據可以隨變化同步更新 + 圖表想實現相同效果，需要復制圖表下方的所有單元格區域（利用Shift+方向鍵選?。?/span>

！控件之列表只支持豎向羅列選項

@Offset設置對齊原表的行數數字輔助列，是對人為制作的原表的一次核實，保證數據分析過程的準確性

@=IF(ISERROR(K2-L2)/L2,0,(K2-L2)/L2)，嵌套一層Iserror可以保證當除法計算分母為零時，跳過計算報錯，直接返回0值

@滑珠圖：條形圖結合散點圖，條形圖是每行103%的數據做出來等長“細軸”，散點圖的橫坐標是指標各行數值，縱坐標分別為0.5/1.5/2.5/3.5/…，就可以讓各值形成滑珠落在細軸上

————————————————————————————————————————————————————————

【銷售管理分析儀】

47、銷售漏斗分析是關系型銷售運營管理（需要長期維護銷售關系）中最為重要的分析方法，科學反應商機狀態以及效率的重要的銷售管理模型。

銷售漏斗從發現商機開始到最終與客戶成交為止的銷售周期，按不同銷售進度分為潛在、接觸、意向、明確、投入、談判、成交的銷售階段（具體依據企業經營模式不同而不同），進而對每一個銷售階段進行精準管理。銷售漏斗管理的目的是讓每個階段的銷售商機都盡可能順利地過渡到下一個銷售階段，只有將每一個銷售階段的商機管理好，才能實現盡可能多的銷售機會，靠近漏斗的最底層，達成企業銷售最大化的目標。本質是企業風險管理，直接關系企業的盈虧狀況，是企業銷售環節的生命線。

環節分析的目的是提前發現各種銷售階段潛在的商機以及隱藏的風險，在通過分析將風險明確化，以指導銷售人員及時做出銷售策略調整（補充新商機OR現有商機盡快推進），達到銷售資源最優化和商機資源轉換最大化。

銷售管理分析儀可以幫助洞察環節風險，快速掌握問題所在，快速做出商業決策判斷，得出詳細結論，做出明確對策。

銷售管理分析多維度模型：Power Query連接多部門多數據源，Power Pivot搭建統一模型

- 客戶維度 - 產品維度 - 地域維度 - 銷售維度 - 渠道維度 - 商機維度

！銷售直接關系企業營收，營收又是企業的生命線

！涉及業務活動多，還涉及渠道或合作廠商的外部維度

！主要管理維度是商機維度，由銷售人員手動錄入，有主觀因素和分析難度

-> 了解業務特點，理清業務流程

1）設計思路 - 故事序章：儀表盤展現總體趨勢問題

- 地域維度達成率

- 產品維度達成率

- 行業/領域維度達成率

@到達高階段的商機是銷售管理者最為關注的指標，“階段4以上”是已明確客戶購買意向的商機 -> 達到1.5倍以上的銷售目標值才預計可以完成本期銷售目標；“階段5以上”是銷售成本投入階段為與某客戶達成成交目的（具有分配銷售人員、搞好銷售關系、投入資金和人員的支持） -> 達到1.2倍以上的銷售目標值才預計可以完成本期銷售目標

2）設計思路 - 故事展開：瀑布圖展現各階段占比情況

了解高低不同階段商機占比情況以及商機金額總量的數值情況。如果低階段商機過多，說明商機向高階段轉換時比較乏力 -> 間接標明銷售人員需要積極把手頭現有商機向前推進；另外若是商機總量總值過低，說明商機總量不足 -> 做出補充新商機進來的銷售決策

3）設計思路 - 故事高潮：動態的組合柱狀圖形成周變化圖了解單個銷售階段商機構成，目的為上一步最終決策提供數據依據

3）設計思路 - 故事尾聲：了解細節

對詳細信息進行總結，可供銷售管理者對某一個銷售人員做出更為細致的銷售指導，使得大的銷售策略方便更順利地展開

MySQL數據庫入門

DAY7：【第1&2章: Mysql安裝、序章】&【第3章: 數據庫、數據表與字段操作】#CDA學習打卡 #CDA數據分析師

【MySQL】

數據庫是存儲、調用、分析數據的倉庫，主要分為關系型數據庫和非關系型數據庫。關系型數據庫管理系統稱為RDBMS。

數據庫是表的集合，帶有相關的數據；一個表是多個字段的集合；一個字段是一列數據，由字段名和記錄兩個部分組成，記錄是實際數據信息。

子商業數據分析中使用的絕大部分數據都來自于企業數據庫，企業數據庫多為關系型數據庫。數據庫負責數據收集、數據整合、數據調用等工作。

@幾乎所有數據分析工具都支持調用數據庫中數據，將所需要分析的數據在數據庫中整合、處理和調用是最為理想的，處理后的數據也可被其他所有數據分析類工具所使用的，推廣至其他工具中去。

關系型數據庫：數據庫應用的主流，以行和列的形式存儲數據，一系列的行和列組成表，一組表組成了數據庫，當前主流的關系型數據庫有Oracle、MySQL、IBM DB2、Microsoft SQL Server等。

操作關系型數據庫時使用結構化查詢語言，簡稱SQL，是一種數據庫查詢和程序設計語言，用于存取數據，計算、查詢、更新和管理數據庫系統。

————————————————————————————————————————————————————————

【MySQL數據庫操作】

1、創建數據庫：在數據庫系統中劃分一塊空間，用來存儲相應的數據

create database {數據庫名稱};

！每一個完整語句后加上“;”終止符

2、查看創建好的數據庫

show create database {數據庫名稱};

3、查看所有數據庫列表

show databases;

4、使用數據庫

use {數據庫名稱};

5、刪除數據庫

drop database {數據庫名稱};

@注釋方法：“-- ”或者“#”之后寫注釋，注意第一個最后有一個空格

！Excel編碼方式為ANSI，MySQL默認編碼方式為utf8，兩者連接時需要先將編碼方式統一！

————————————————————————————————————————————————————————

6、創建數據表

數據表是數據庫存儲及操作數據的基本單位，承載數據的容器。數據庫由多個數據表構成，每張數據表存儲多個字段，每個字段由不同的字段名及記錄構成，每個字段有自己的數據結構及約束條件。

@表為了在最大程度上保證數據資源的準確、完整以及高效利用，在表中必須嚴格規定每個字段的相關屬性，無論是字段的數據類型還是約束條件，都要嚴謹對待！創建時必須按業務要求指定設置好！

@char(#)--固定長#個字節的文本型字段

varchar(n)--可變長最長為n個字節的文本型字段

#創建并使用test數據庫

create database test;

use test;

#創建員工信息表

create table emp(

depid char(3),

depname varchar(20),

peoplecount int

);

#查看表是否創建成功

show tables;

#刪除數據表

drop table emp;

————————————————————————————————————————————————————————

7、數據類型

位：bit，是電子計算機中最小的數據單位，每一位的狀態只能是0或1

字節：Byte，由8個二進制位構成1個字節，它是存儲空間的基本計量單位

數據類型：不同的數據類型具有不同的字節長度；字節長度越長，能取值的數值區間也就越大。

所謂“一把鑰匙開一把鎖”，字段的數據類型限定了只有與其相匹配的數據信息，才能錄入到字段中來，以保證字段的完整性和準確性。

1）整數型：INT()、TINYINT、SMALLINT、MEDIUMINT、BIGINT，括號里設置最大顯示寬度，并不限制實際取值范圍和占用空間

e.g. INT(11)，包括正負號和十位數

@均存在有（正負）符號的和無符號的。如果在數據類型后加上UNSIGNED屬性可以禁止負數

2）小數型：FLOAT(M,D)（4字節，默認為(10,2)）、DOUBLE(M,D)（8字節，默認為(16,4)）、DECIMAL(M,D)——M是顯示寬度，D是小數位數

@均只能是有符號的

3）日期時間型：DATE、DATETIME（YYYY-MM-DD HH:MM:SS格式）、TIME、TIMESTAMP（時間戳，例如20241007153600）、YEAR（2位或4位，2位是1970~2069，4位是1901~2155，默認四位）

4）字符串型：CHAR(M)、VARCHAR(M)、BLOB或TEXT、TINYBLOB或TINYTEXT、MEDIUMBLOB或MEDIUMTEXT、LONGBLOB或LONGTEXT、ENUM

@CHAR是固定長度，VARCHAR是可變長度（L+1，需要占用一位存儲長度信息），長度為1-255，注意指定的字符串長度M一定要大于實際輸入數據的最大長度，這樣才能完成保存輸入數據信息 -> 數據長度不一時，優先選用VARCHAR；長度出入不大時，CHAR比VARCHAR運行效果更佳

————————————————————————————————————————————————————————

8、約束條件

約束條件是在表上強制執行的數據檢驗規則，可用來保證創建的表的數據完整和正確性，類似不同大小篩眼的篩子。

1）PRIMARY KEY 主鍵約束：主鍵又稱主碼，是數據表中一列或多列的組合。主鍵約束要求主鍵列的數據必須是唯一的并且不允許為空，非空和唯一，保證表中每行記錄都不重復。使用主鍵，能夠唯一的標識表中的一條記錄，還可以加快數據庫查詢的速度。

字段名 數據類型 PRIMARY KEY

- 單字段主鍵

e.g. depid char(3) primary key,

- 多字段聯合主鍵

e.g. primary key(depname, depid)

2）NOT NULL 非空約束：要求字段的值不能為空，非空。

字段名 數據類型 NOT NULL

e.g. depname varchar(20) not null,

3）UNIQUE 唯一約束：要求該列的值必須是唯一的，唯一；允許但只能出現一個空值；一個表中可以有多個字段聲明為唯一的，確保數據表的一列或幾列不出現重復值。

字段名 數據類型 UNIQUE

e.g. peoplecount int unique

4）AUTO_INCREMENT 自增字段：一個表只能有一個自增字段，必須為主鍵的一部分，默認從1開始自增，步長為1

字段名 數據類型 AUTO_INCREMENT

e.g. #創建整數型自增主鍵

id INT PRIMARY KEY AUTO_INCREMENT

5）DEFAULT 默認值：當插入記錄時，如果沒有明確為字段賦值或空值時，系統會自動按默認值填充賦值

字段名 數據類型 DEFAULT 默認值

e.g. depname varcahr(20) default'-',

@標準SQL用''單引號引用字符串，MySQL拓展至''或""單雙引號皆可

@desc {表名}——可見按字母排序后各字段的數據類型及約束條件

——————————————————————————————————————————————————————

9、向數據表中填充數據

1）（手動錄入）insert into：

insert into 表名(字段1, 字段2, ...)values(),(),(), ...

！字段排列順序要與建表時建立字段順序一致

e.g. #插入數據

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）（外部數據源文件批量導入）

e.g. #為Monthly_Indicator表導入外部txt文件，按絕對路徑（左斜斜杠分隔），指定Tab制表符為分隔符，忽略數據文件中第一行（字段名）

load data local infile '文件路徑.txt'

into table Monthly_Indicator

fields terminated by '\t'

ignore 1 lines;

3）從企業數據庫直接導入：先聯系數據庫管理員授權權限

-> 檢查表數據：對導入表中的數據一般從導入內容、導入數據總行數以及表結構三方面進行檢查

e.g.

#檢查導入內容Monthly_Indicator

Select * from Monthly_Indicator;

#檢查導入數據總行數Monthly_Indicator

Select count(*) from Monthly_Indicator;

#檢查表結構

Desc Monthly_Indicator;

@主鍵選擇需要保證聚焦明確分析對象的描述條件，需要考慮是否多個主鍵以限定

————————————————————————————————————————————————————————

10、修改數據表

修改數據庫中已經存在的數據表的結構，包括修改表名、修改字段數據類型或字段名、增加或刪除字段、修改字段的排列位置等。使用alter table語句進行修改。

e.g.

#將數據表emp改名為empdep

alter table emp rename empdep;

#將數據表empdep中depname字段的數據類型由varchar(20)修改成varchar(30)

alter table empdep modify depname varcahr(30);

#將數據表empdep中depname字段的字段名改為dep

alter table empdep change depname dep varchar(30);

#將數據表empdep中dep字段的字段名改回為depname，并將該字段數據類型改回為varchar(20);

alter table empdep change dep depname varchar(20);

#為數據表empdep添加新字段maname，新字段數據類型為varchar(10)，約束條件為非空

alter table empdep add maname varchar(10) not null;

@change相對modify，可以同時修改數據名（重新命名）及數據類型，只需要修改數據類型就用modify

#將數據表empdep中maname字段的排列順序改為第一位

alter table empdep modify maname varchar(10) first;

#將數據表empdep中maname字段的排列順序改到depid字段之后

alter table empdep modify maname varchar(10) after depid;

#刪除maname字段

alter table empdep drop maname;

#CDA學習打卡 #CDA數據分析師 #CDA學習打卡 #CDA數據分析師 #CDA學習打卡 #CDA數據分析師