Excel Power BI商業(yè)智能

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

1、什么是數(shù)據(jù)分析

2、知識、智慧、信息和數(shù)據(jù)的不同定義

3、SQL和Excel在數(shù)據(jù)分析中的定位

4、業(yè)務(wù)人員、IT技術(shù)人員及分析人員的不同職責

5、數(shù)據(jù)化驅(qū)動業(yè)務(wù)對企業(yè)的意義

6、數(shù)據(jù)分析方法分類 - 業(yè)務(wù)數(shù)據(jù)分析、數(shù)據(jù)挖掘分析、大數(shù)據(jù)分析

7、業(yè)務(wù)數(shù)據(jù)分析流程

8、商業(yè)智能（BI）和商業(yè)洞察

9、數(shù)據(jù)可視化分析方法 - 對比分析、結(jié)構(gòu)分析、透視分析、其他分析方法

10、可視化&交互式是數(shù)據(jù)分析真正移交閱讀者主動權(quán)的特點

11、Excel的原理、內(nèi)外部對象結(jié)構(gòu)、歷史、五大必備功能和Power BI四項功能（Power Query、Power Pivot、Power View和Power Map）

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

【“表格”結(jié)構(gòu)數(shù)據(jù)與“表”結(jié)構(gòu)數(shù)據(jù)】

12、表格結(jié)構(gòu)數(shù)據(jù)：以單元格為基本數(shù)據(jù)存儲及操作單位 -> 處理批量數(shù)據(jù)效率低

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

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

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

【Excel數(shù)據(jù)透視圖表與切片器】

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

@數(shù)據(jù)透視表用來將一維原始數(shù)據(jù)轉(zhuǎn)換為由行列構(gòu)成的二維值匯總表。

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

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

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

15、切片器（2010版本以上）：當切片器與數(shù)據(jù)透視圖表關(guān)聯(lián)后，對切片器進行選擇時，數(shù)據(jù)透視表會一起發(fā)生變化。

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

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

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

【Excel條件格式與迷你圖】

16、條件格式

1）以單元格值為基礎(chǔ)，設(shè)定條件格式：

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

2）以公式返回值為基礎(chǔ)，設(shè)定條件格式：

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

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

17、圖標集

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

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

18、數(shù)據(jù)條、色階

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

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

@如果要利用數(shù)據(jù)條或色階來體現(xiàn)各部分值與匯總值之間的占比關(guān)系，選定單元格區(qū)域應包含匯總值，形象觀察部分和整體的關(guān)系

19、迷你圖

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

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

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

【Excel函數(shù)公式】

20、函數(shù)公式

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

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

21、嵌套函數(shù)

將內(nèi)部函數(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個變量；多個分類項目比較

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

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

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

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

散點圖：2個變量；數(shù)據(jù)間的分布及關(guān)聯(lián)性，可添加回歸公式和趨勢線

氣泡圖：3個變量；數(shù)據(jù)間的聯(lián)系比較，用每個點的不同大小代表第三個變量

雷達圖：多個變量；多重數(shù)據(jù)間的比較，多重維度間平級關(guān)系

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

【Excel常用函數(shù)】

23、

1）數(shù)學函數(shù)：SUMIF、SUMPRODUCT、MOD

2）文本函數(shù)：LEFT、MID、RIGHT、REPT

3）邏輯和引用函數(shù)：IF、ISERROR

4）查找函數(shù)：VLOOKUP、HLOOKUP、MATCH、INDEX

5）日期和時間函數(shù)：TODAY、WEEKDAY

6）統(tǒng)計函數(shù)：COUNTIF

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

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

ex2. 利用2015年節(jié)假日統(tǒng)計表計算具體日期及對應的星期數(shù)

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

ex3. 使用INDEX及MATCH組合函數(shù)查找標黃部分相關(guān)信息

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

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

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

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

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

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

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

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

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

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

將數(shù)組間的元素相乘后返回乘積之和

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

=SUM(D2:G2*D4:G4)

按條件收入合計：

SUMIF(range, criteria, [sum_range])

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

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

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

按條件統(tǒng)計訂單數(shù)量：

COUNTIF(range, criteria)

用REPT在單元格內(nèi)做虛擬條形圖，表現(xiàn)趨勢：

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

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

【Excel基本數(shù)據(jù)處理方法】

24、處理數(shù)據(jù)室數(shù)據(jù)分析流程中的起點，也是耗時最多的環(huán)節(jié)。

數(shù)據(jù)處理方法：

1）處理重復數(shù)據(jù)

針對單列數(shù)據(jù)識別重復行：

I. 使用公式COUNTIF

II. 排序關(guān)鍵字段后使用公式IF

III. 利用數(shù)據(jù)透視表計數(shù)匯總

針對多列重復的去重：

I. 數(shù)據(jù)-排序和篩選-高級

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

II. 識別重復行后刪除

@需要結(jié)合業(yè)務(wù)需求決定刪除和保留重復行

2）處理不完整數(shù)據(jù)

查找缺失值：

I. 使用 Ctrl+F

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

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

III. 利用排序篩選

處理缺失值：

I. 使用0替換數(shù)值類缺失值

II. 使用平均值替換數(shù)值類缺失值

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

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

3）處理異常值

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

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

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

4）數(shù)據(jù)分組

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

5）轉(zhuǎn)換變量類型

處理格式錯誤數(shù)據(jù)：

I. 設(shè)置格式

II. 使用分列功能，列數(shù)據(jù)格式為“文本”

III. 使用公式補全信息后，轉(zhuǎn)換成日期型

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

@常需要相互轉(zhuǎn)換的數(shù)據(jù)類型包括文本型、日期型、數(shù)值型

6）數(shù)據(jù)標準化

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

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

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

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

7）設(shè)置變量權(quán)重

III. 加權(quán)平均

利用交叉表設(shè)置權(quán)重：

- 縱向和橫向?qū)Ρ?，橫向重要則為1，縱向重要則為0

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

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

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

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

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

+思考題：用唯一的數(shù)字區(qū)分開唯一的業(yè)務(wù)類型，形成映射關(guān)系，方便進一步數(shù)據(jù)處理和分析

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

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

【Excel基礎(chǔ)描述性統(tǒng)計類圖表】

頻數(shù)：是落在各類別種的數(shù)據(jù)個數(shù)

頻率：是各類別頻數(shù)與總頻數(shù)之比

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

25、直方圖

用來展現(xiàn)數(shù)據(jù)在不同組距間分布狀態(tài)的圖表，組距=（最大值-最小值）/ 組數(shù)，組數(shù)根據(jù)實際業(yè)務(wù)情況設(shè)置。

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

26、盒須圖（箱型圖）

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

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

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

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

【Power Query概述及導入多源數(shù)據(jù)】

27、Power Query：多源大規(guī)模數(shù)據(jù)加工處理方法

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

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

- 提供豐富的數(shù)據(jù)處理分析功能

- 可通過M函數(shù)靈活創(chuàng)建自定義數(shù)據(jù)處理及計算規(guī)則

- 創(chuàng)建好的數(shù)據(jù)處理流程可以無限次復用

@2016以上版本：數(shù)據(jù)-“獲取和轉(zhuǎn)換”功能組

！勿通過數(shù)據(jù)-獲取外部數(shù)據(jù)進行導入

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

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

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

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

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

【Power Query合并數(shù)據(jù)】

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

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

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

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

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

2）進行合并操作

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

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

@1：當兩表中用于合并的關(guān)鍵字段值不是一一對應時，不同的聯(lián)接種類會出現(xiàn)不同的聯(lián)接結(jié)果（默認選“內(nèi)部”，另一種是“左外部”）

@2：當關(guān)鍵字段中有重復值時，連接后的總行數(shù)為關(guān)鍵字段值重復出現(xiàn)次數(shù)的乘積

29、縱向合并數(shù)據(jù)：即添加行，在現(xiàn)有數(shù)據(jù)的基礎(chǔ)上添加記錄。

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

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

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

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

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

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

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

【Power Query基本功能】

30、

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

2）“轉(zhuǎn)換”選項卡：提供了針對行與列間的結(jié)構(gòu)性加工處理功能、數(shù)據(jù)類型、格式等設(shè)置功能以及基本的函數(shù)計算功能。

選中目標列，轉(zhuǎn)換-任意列-填充，向上/向下

！空值很危險，null，不知道位數(shù)也不知道下一步該如何操作->轉(zhuǎn)換為已知狀態(tài)的真正控制，比如“-”

選中目標列，轉(zhuǎn)換-任意列-透視列，文本型只能計數(shù)

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

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

選中目標列，添加列-常規(guī)-自定義列，進行參數(shù)定義即可

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

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

@“數(shù)據(jù)透視”：按照某種規(guī)則將數(shù)據(jù)進行匯總，匯總行列標簽結(jié)果形成二位匯總表

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

【Power Query M函數(shù)】

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

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

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

2）根據(jù)需求重新編輯表達式程序

3）編輯錯誤通過“查詢設(shè)置”區(qū)域退回重新編輯

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

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

- 重排序的列：鼠標選中拖拽，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)換-編號列-信息，設(shè)置為“偶數(shù)”

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

32、Power Query高級編輯器

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

！借鑒已有M函數(shù)寫新函數(shù)時，注意修改每行結(jié)果值，句末逗號與否，in顯示內(nèi)容

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

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

【Power Pivot 概述及導入數(shù)據(jù)】

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

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

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

- 搭建多維數(shù)據(jù)分析環(huán)境，形成統(tǒng)一的數(shù)據(jù)環(huán)境

- 突破數(shù)據(jù)行數(shù)限制（勿大于2G?。?/span>

- 簡潔的操作界面功能

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

@“數(shù)據(jù)模型”及Power Pivot中的數(shù)據(jù)

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

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

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

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

【Power Pivot 搭建多維數(shù)據(jù)分析模型】

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

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

多維數(shù)據(jù)集搭建方法：

1）明確表與表之間用于匹配的關(guān)鍵字段

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

注意：

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

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

@3 系統(tǒng)會將關(guān)鍵字段中有重復值的表作為主表，沒有重復值的表為附表，形成“多對一”關(guān)系。在數(shù)據(jù)透視表中，只有當行/列標簽均來自附表時，附表才能提供值字段。（行/列標簽有一個來自主表就會報錯！）

-> 以上連接規(guī)則將用在創(chuàng)建的數(shù)據(jù)透視表中：

主頁-數(shù)據(jù)透視表

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

【Power Pivot 創(chuàng)建層次結(jié)構(gòu)】

35、在不同字段間創(chuàng)建層次結(jié)構(gòu)方法：

1）在關(guān)系圖視圖模式下選中父級字段后，鼠標右鍵選中“創(chuàng)建層次結(jié)構(gòu)”

2）為層次結(jié)構(gòu)命名

3）將子級字段拖拽到創(chuàng)建好的層次結(jié)構(gòu)中父級字段的下方

@創(chuàng)建層次結(jié)構(gòu)時一定要符合業(yè)務(wù)邏輯！

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

【Power Pivot DAX表達式】

36、DAX數(shù)據(jù)分析表達式：

Power Pivot的特有函數(shù)集，用于在Power Pivot編輯器內(nèi)為數(shù)據(jù)透視表創(chuàng)建透視規(guī)則（復雜條件下的篩選或計算）以及增加新的字段內(nèi)容。

1）DAX表達式的結(jié)果應用在數(shù)據(jù)透視表中

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

3）注意：

@Power Query和Power Pivot中處理的都是表結(jié)構(gòu)數(shù)據(jù)，有基于表（字段的合集）和字段的數(shù)據(jù)類型，區(qū)分于Excel中的文本型、數(shù)值型等數(shù)據(jù)類型

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

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

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

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

e.g.

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

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

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

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

（！先將兩表創(chuàng)建好連接關(guān)系才能使用related）

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

@下方DAX編輯匯總規(guī)則：針對上方預覽區(qū)域的某一個字段整個字段創(chuàng)建匯總規(guī)則時使用（建議字段正下方書寫?。?> 只有在創(chuàng)建數(shù)據(jù)透視表中才能發(fā)揮作用

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

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

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

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

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

【Power Pivot 使用KPI】

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

1）在編輯器下方公式區(qū)域內(nèi)用公式為需要創(chuàng)建KPI的字段指定匯總規(guī)則（至少一個DAX）

2）點擊“創(chuàng)建KPI”在“關(guān)鍵績效指標（KPI）”對話框中設(shè)定KPI規(guī)則

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

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

絕對值 - 以一個匯總規(guī)則與某個絕對數(shù)值之間的大小關(guān)系為判斷依據(jù)的KPI規(guī)則

e.g.

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

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

@ALL函數(shù)：“所有XX的…”

@目標即理想中的狀態(tài)

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

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

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

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

【數(shù)據(jù)可視化分析方法】

38、對比分析

1）縱向?qū)Ρ龋簳r間序列分析，同一指標不同時間下的變化趨勢（不同間斷分散的時間段用柱狀圖，連續(xù)連貫密集的時間點用折線圖）

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

3）橫向?qū)Ρ龋涸谕粫r間下，部分與總體，部分與部分或是對象與對象之間的對比，常用餅圖、環(huán)形圖（橫向占比對比，部分與部分）、條形圖（橫向值對比，部分與總體）、分段折線圖（橫向趨勢對比，對象與對象）等

@與縱向?qū)Ρ鹊膮^(qū)別：縱向是不同時間條件下同一指標的對比情況，橫向是同一時間條件下不同指標之間的對比情況

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

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

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

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

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

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

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

39、結(jié)構(gòu)分析

1）構(gòu)成分析：部分與整體間構(gòu)成關(guān)系的分析方法，常見漏斗圖、瀑布圖、滑珠圖、餅圖、環(huán)形圖等。

2）杜邦分析：不僅適用于財務(wù)指標分析，而且可擴展延伸至各種關(guān)鍵指標間有明顯結(jié)構(gòu)關(guān)系的業(yè)務(wù)分析中使用。

40、其他分析方法

1）變化分析：反映同一指標或多種指標狀態(tài)及數(shù)值變化情況的分析方法，常見組合圖表、指標構(gòu)成圖等，是一種動態(tài)分析方法，要結(jié)合自身實際業(yè)務(wù)情況，盡可能直觀地選擇合適的圖表對變化中的指標（觀測值）進行展現(xiàn)

2）分組分析：用來展現(xiàn)關(guān)鍵指標在不同區(qū)間（組距）內(nèi)的分布情況，常見直方圖

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

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

【專業(yè)自定義圖表創(chuàng)建及和應用方法】

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

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

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

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

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

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

3）基本圖表的再創(chuàng)新方法：以改變Excel基本圖表的格式及引用數(shù)據(jù)源數(shù)據(jù)排列規(guī)則等方式來改變設(shè)計意圖的展現(xiàn)形式，需要清晰的設(shè)計思路和靈活的展現(xiàn)技巧。

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

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

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

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

42、交互式圖表：又叫動態(tài)圖表，指能夠隨時響應用戶操作指令，改變展現(xiàn)結(jié)果的圖表。圖表從靜態(tài)變?yōu)閯討B(tài)后，分析的深度和廣度都將得到質(zhì)的改變。

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

切片器創(chuàng)建方法：選中需要關(guān)聯(lián)的數(shù)據(jù)透視表或圖，插入-切片器，為切片器選擇字段（字段可多選）

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

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

更改切片器關(guān)聯(lián)方法：右鍵點擊切片器，選擇“報表鏈接”，選擇需要關(guān)聯(lián)的數(shù)據(jù)透視表或圖

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

動態(tài)圖表制作步驟：選擇并設(shè)置控件（開發(fā)工具-插入-表單控件-組合框），準備圖表數(shù)據(jù)，創(chuà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是表格首行首個單元格，A3是組合框的返回值結(jié)果

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

再關(guān)聯(lián)圖表：圖表中右鍵，選擇數(shù)據(jù)，編輯，將系列值改為=柱形圖示例!{新建公式名稱}

動態(tài)圖表制作注意事項：

- 根據(jù)實際需要選擇適當?shù)目丶?/span>

- 根據(jù)業(yè)務(wù)需求決定動態(tài)圖表互動方式

- 需要注意不要將函數(shù)寫死，業(yè)務(wù)可能后續(xù)發(fā)生變化

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

43、主要控件

1）選項按鈕

2）復選框

3）列表框

4）滾動條、數(shù)值調(diào)節(jié)按鈕

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

【Power Map數(shù)據(jù)地圖創(chuàng)建及應用】

44、數(shù)據(jù)地圖是展現(xiàn)數(shù)據(jù)地理信息最為直觀的方法。Power Map是Power BI系列插件工具中用來生成數(shù)據(jù)地圖的工具，結(jié)合Bing地圖，支持用戶繪制可視化的地理和時態(tài)數(shù)據(jù)，并用3D方式進行分析。需要聯(lián)網(wǎng)與Bing地圖通信環(huán)境下才可使用Power Map。

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

靜態(tài)地圖：右側(cè)區(qū)域圖層窗格，先選地理和地圖級別（下一步），再設(shè)置高度，勾選關(guān)注指標（默認求和，注意按實際調(diào)整），切換圖形類型

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

@切換平面地圖并調(diào)整右下角角度會讓地圖更直觀

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

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

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

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

【財務(wù)杜邦分析儀】

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

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

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

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

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

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

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

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

【銷售管理分析儀】

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

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

環(huán)節(jié)分析的目的是提前發(fā)現(xiàn)各種銷售階段潛在的商機以及隱藏的風險，在通過分析將風險明確化，以指導銷售人員及時做出銷售策略調(diào)整（補充新商機OR現(xiàn)有商機盡快推進），達到銷售資源最優(yōu)化和商機資源轉(zhuǎn)換最大化。

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

銷售管理分析多維度模型：Power Query連接多部門多數(shù)據(jù)源，Power Pivot搭建統(tǒng)一模型

- 客戶維度 - 產(chǎn)品維度 - 地域維度 - 銷售維度 - 渠道維度 - 商機維度

！銷售直接關(guān)系企業(yè)營收，營收又是企業(yè)的生命線

！涉及業(yè)務(wù)活動多，還涉及渠道或合作廠商的外部維度

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

-> 了解業(yè)務(wù)特點，理清業(yè)務(wù)流程

1）設(shè)計思路 - 故事序章：儀表盤展現(xiàn)總體趨勢問題

- 地域維度達成率

- 產(chǎn)品維度達成率

- 行業(yè)/領(lǐng)域維度達成率

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

2）設(shè)計思路 - 故事展開：瀑布圖展現(xiàn)各階段占比情況

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

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

3）設(shè)計思路 - 故事尾聲：了解細節(jié)

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

MySQL數(shù)據(jù)庫入門

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

【MySQL】

數(shù)據(jù)庫是存儲、調(diào)用、分析數(shù)據(jù)的倉庫，主要分為關(guān)系型數(shù)據(jù)庫和非關(guān)系型數(shù)據(jù)庫。關(guān)系型數(shù)據(jù)庫管理系統(tǒng)稱為RDBMS。

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

子商業(yè)數(shù)據(jù)分析中使用的絕大部分數(shù)據(jù)都來自于企業(yè)數(shù)據(jù)庫，企業(yè)數(shù)據(jù)庫多為關(guān)系型數(shù)據(jù)庫。數(shù)據(jù)庫負責數(shù)據(jù)收集、數(shù)據(jù)整合、數(shù)據(jù)調(diào)用等工作。

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

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

操作關(guān)系型數(shù)據(jù)庫時使用結(jié)構(gòu)化查詢語言，簡稱SQL，是一種數(shù)據(jù)庫查詢和程序設(shè)計語言，用于存取數(shù)據(jù)，計算、查詢、更新和管理數(shù)據(jù)庫系統(tǒng)。

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

【MySQL數(shù)據(jù)庫操作】

1、創(chuàng)建數(shù)據(jù)庫：在數(shù)據(jù)庫系統(tǒng)中劃分一塊空間，用來存儲相應的數(shù)據(jù)

create database {數(shù)據(jù)庫名稱};

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

2、查看創(chuàng)建好的數(shù)據(jù)庫

show create database {數(shù)據(jù)庫名稱};

3、查看所有數(shù)據(jù)庫列表

show databases;

4、使用數(shù)據(jù)庫

use {數(shù)據(jù)庫名稱};

5、刪除數(shù)據(jù)庫

drop database {數(shù)據(jù)庫名稱};

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

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

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

6、創(chuàng)建數(shù)據(jù)表

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

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

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

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

#創(chuàng)建并使用test數(shù)據(jù)庫

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，是電子計算機中最小的數(shù)據(jù)單位，每一位的狀態(tài)只能是0或1

字節(jié)：Byte，由8個二進制位構(gòu)成1個字節(jié)，它是存儲空間的基本計量單位

數(shù)據(jù)類型：不同的數(shù)據(jù)類型具有不同的字節(jié)長度；字節(jié)長度越長，能取值的數(shù)值區(qū)間也就越大。

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

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

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

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

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

@均只能是有符號的

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一定要大于實際輸入數(shù)據(jù)的最大長度，這樣才能完成保存輸入數(shù)據(jù)信息 -> 數(shù)據(jù)長度不一時，優(yōu)先選用VARCHAR；長度出入不大時，CHAR比VARCHAR運行效果更佳

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

8、約束條件

約束條件是在表上強制執(zhí)行的數(shù)據(jù)檢驗規(guī)則，可用來保證創(chuàng)建的表的數(shù)據(jù)完整和正確性，類似不同大小篩眼的篩子。

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

字段名 數(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)一個空值；一個表中可以有多個字段聲明為唯一的，確保數(shù)據(jù)表的一列或幾列不出現(xiàn)重復值。

字段名 數(shù)據(jù)類型 UNIQUE

e.g. peoplecount int unique

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

字段名 數(shù)據(jù)類型 AUTO_INCREMENT

e.g. #創(chuàng)建整數(shù)型自增主鍵

id INT PRIMARY KEY AUTO_INCREMENT

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

字段名 數(shù)據(jù)類型 DEFAULT 默認值

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

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

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

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

9、向數(shù)據(jù)表中填充數(shù)據(jù)

1）（手動錄入）insert into：

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

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

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ù)源文件批量導入）

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

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

into table Monthly_Indicator

fields terminated by '\t'

ignore 1 lines;

3）從企業(yè)數(shù)據(jù)庫直接導入：先聯(lián)系數(shù)據(jù)庫管理員授權(quán)權(quán)限

-> 檢查表數(shù)據(jù)：對導入表中的數(shù)據(jù)一般從導入內(nèi)容、導入數(shù)據(jù)總行數(shù)以及表結(jié)構(gòu)三方面進行檢查

e.g.

#檢查導入內(nèi)容Monthly_Indicator

Select * from Monthly_Indicator;

#檢查導入數(shù)據(jù)總行數(shù)Monthly_Indicator

Select count(*) from Monthly_Indicator;

#檢查表結(jié)構(gòu)

Desc Monthly_Indicator;

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

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

10、修改數(shù)據(jù)表

修改數(shù)據(jù)庫中已經(jīng)存在的數(shù)據(jù)表的結(jié)構(gòu)，包括修改表名、修改字段數(shù)據(jù)類型或字段名、增加或刪除字段、修改字段的排列位置等。使用alter table語句進行修改。

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相對modify，可以同時修改數(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學習打卡 #CDA數(shù)據(jù)分析師 #CDA學習打卡 #CDA數(shù)據(jù)分析師 #CDA學習打卡 #CDA數(shù)據(jù)分析師