PowerPivot數據建模

Urin的自习室
19粉絲
大家好在之前的章節當中
我們講過百萬行的數據處理
那在這麼大型的數據處理當中呢
我們一定得用到我們的power PIVOT
數據建模
那這個數據建模
具體是一個什麼樣的含義呢
我今天跟大家來解釋一下
我們所說的數據建模呢
其實也就是關係的建立
通常情況下面我們都會使用到數據庫
例如circle
my circle access
在Excel當中呢
我們想要實現數據的建模
我們可以使用到power PIVOT
在office系列當中呢
如果你是13版的
你需要去下載插件
如果你是2016專業版以後的版本呢
你會自帶這個工具
很多同學可能是學生版
所以呢你可能沒有這樣一個工具
如果你是蘋果的系統
或許呢你使用不了powerpivot
或者呢是powerquery
但是你可以使用啊circle或者是mycircle
去實現一些建模的工作
其理論呢
大致呢也都是差不多的
如果你滿足上述我所說過的版本要求
或者呢
自己已經下載了power PIVOT插件的話呢
我們可以來看一個小小的例子
看到這張簡單的表格
中間呢會有日期
省份產品ID
銷售量以及客戶評分
是否你會覺得很單調呢
好我們再加入三張表格
這個時候
我們可以根據省份來確認負責人
根據產品ID來確認產品名稱
以及根據我們的客戶評分
來確認他的滿意程度
需要特別注意的是
輔助表格當中我們都是唯一值
或許你會使用VLOOKUP函數去查找
這樣一些內容
並且放置在銷售表格當中
使得表格的信息更加完整
針對數據量比較小的表格呢
當然沒有問題
也符合常規的操作方式
但是如果你的表格數據有20萬行以上
那麼也就意味着說
這三列將會有超過60萬個單元格
使用到v look up函數
這也無疑增加了表格的負擔
基於這樣的情況
我們可以通過關係搭建的方式
變相的實現v look up函數屬性
這也就是power PIVOT的建模
無論銷售表格當中的數據多麼龐大
用來支撐它的信息表格
都不會發生變化
情況越是複雜
數據量越是龐大
power PIVOT也會越來越強大
看到我們的實例
這是一份從02年到04年
3萬多行的數據
在這個中間呢
可能會有一些缺失
譬如客戶地址
我們所對應到的負責人呢
在這一塊
然後關於我們的這個客戶評分
我們的這個滿意程度的字眼呢
在這塊
然後還會有一個產品的名稱
所對應到的零售價格
這個就是我們所有的信息
可能你的實際情況會更加的複雜
比如說這邊會有很多表頭
然後每一個表頭呢
可能所對應出來的
這樣一個延伸部分呢
都像我們的這個信息表格這樣
所以呃在這種情況下面
我們啊
第一考慮就得使用到我們的power PIVOT
還會有一種可能
也就是
我們使用power query導入了TXT文件
但是在TXT文件當中呢
可能信息不是那麼全面
所以我們需要在Excel當
中來製作一些類似於信息表格
去輔助我們的TXT文件
讓整個的信息更加完整
好那第一步呢
我們需要把所有的表格都變成超級表
選擇我們的第一張表格
Ctrl加t確定
然後呢選擇到第二個表格
Ctrl加t包含標題
確定啊大家注意啊
我們一定得包含我們的標題
變成超級表之後呢
我們需要對錶格來進行命名
不然的話可能到時候你會區別不了
第一張表格是我們的銷售數據
第二張表格是我們的省份地址
客客戶
客戶負責人
然後呢可以選擇到第三張表格是評分
第四張表格呢是我們的零售價
好這邊做完了以後呢
我們就需要把它給導入進
我們的power PIVOT
中間大家可以選擇到數據
選擇到管理數據模型
或者呢大家可以在我的表格上方呢
看到這邊會有個叫做power PIVOT
怎麼來的呢
如果你符合版本要求
我們可以選擇到文件
然後再選擇到我們的選項
選擇到加載項
選擇到管理這塊呢
選擇到com加載項轉到
然後呢
我們可以把這個power pivot for Excel呢
把它給打開了
點擊到確定啊
然後呢我們再選擇到文件
然後我們再選擇到我們的選項
在自定義功能區呢
我們把power PIVOT找到了以後呢
把它給添加在我們的這個選項卡上方
啊然後點擊到確定
這樣的話呢
你就能夠找到我們的power PIVOT
接下來我們選擇到第一張表格
隨便選擇一個單元
格然後再選擇到添加到數據模型
OK那現在呢
你就可以看到
這邊就是我們的第一張表格嘛
然後依次的
我們可以把第二張表格添加進來
第3張表格添加
第4張表格添加
好
當所有的表格都添加進我們的power PIVOT
以後呢大家可以看到這個中間呢
可以進行拉伸
在我們的下方啊是一個計算區域
就是比如說你要呃你要寫一些Dex
或者呢是需要寫一些度量值呢
我們都可以在下方來進行輸入
上方呢就是我們的表格
我們可以到上面來進行一個預覽了
好吧
接下來我們需要來做一個關係的建立
於是呢我們可以選擇到在上方啊
這邊會有個叫做關係視圖
我們選擇到它
然後所有的表格呢
它都會啊
進入到我們的這樣一個關係視圖當中
我們可以把銷售表格呢放在中間
然後把其他的表格呢把它給放在周邊
我們選擇到客戶地址
和銷售表格當中的客戶地址
來進行個連接
那這樣的話呢
其實也就等於說
我們實現了一個v look up函數
隱形的呢
也就是在我們的下方呢
就會把責任人把他給放過來
只是說他沒有切切實實的做到而已
沒有顯示出來
但是呢他在數據模型當中已經啊
會有這樣一個形狀所在了
然後大家可以看到在我們的負責人啊
這樣一張表單中間呢
這邊會寫個一
然後在銷售數據表單這塊呢
會有一個呃通配符啊
通配符所代表的意思也就是
多個那這邊呢
是一個那這邊是多個
所以這個是一個一對多的關係
在呃任何的數據建模當中呢
都會存在一個一對多的關係
如果是多對多的關係呢
我們就需要中間來建立一個橋樑
那這個呢
可能會在我的呃power系列當中哈
來具體講到
好同樣的道理
我們可以把評分
拉扯到客戶評分這一塊
那其實呢
我們可以通過客戶評分
就可以鎖定到客戶的滿意程度
他是滿意還是不滿意
具體的一個文字字眼
也是一個v look up函數的邏輯
那同樣的道理
我們可以把零售價格中間的產品名稱
和這塊的產品名稱來進行個連接
於是呢在這張表格中間
我們也知道了
哎呦哪一個產品
它的零售價格是多少
好這個呢
也就是最基本的關係建立
我們再稍微回到解析這一塊
注意一個問題
我們始終都強調
在建立關係的時候
主體表格可以出現重複值
而在輔助的信息表格當中
必須是唯一值
那麼關於日期也是同樣的道理
如果我們想要建立一個數據模型
最後呢用這個數據模型
去做一個數據透視表的歸納
例如2011年的1月1號
這一天我們的總銷量
甭管它是什麼產品
甭管它是什麼省份
那它的銷量就是28
如果日期呢
是2011年的1月2號
產品名稱爲西瓜
那它的銷量呢
就應該是44
所以在這種情況下面
我們需要一個日曆表格
而這個日曆表格呢
它都是單獨存在的
也就是說2011年1月1號1月2號1月3號
它都是唯一值我們通
過這個唯一值
去歸納在我們原始表格當中
重複出現的日期
這樣的話呢
才符合我們最後可能做成數據透視表
需要的一個結果
所以依照之前的理論
我們需要來加入一個日曆
那當然了
在power BI和在power PIVOT中間呢
它會有一些不一樣
我們可以選擇到數據視圖啊
然後到這邊選擇到設計
選擇到日期表
在power BI當中呢
我們需要用Dax去寫
而在Excel當中呢
就很方便
直接呢新建
那這樣的話呢
我們這個日曆呢
也就直接一步就到位了
大家可以看到1月1號 1月2號
1月3號 1月4號等等啊
它都是在我們原始表格當中呢
進行了一個歸納
好吧
好我們再回到我們的power PIVOT的中間
現在我們有日曆
這樣一張表格的中間呢
可能會很複雜
在這塊呢我不會講啊
在呃我的power PIVOT系列當中呢
這塊呢講的都比較明白
然後在關係視圖當中
我們可以選擇到我們的日曆
然後呢可以選擇到中間的date
date呢它就是中間的唯一值啊
和日期一樣
當然它是呃
微軟的所以說自動調取出來的
就英文呢
我們可以選擇到date
和我們的日期進行一個相連
這樣的話呢
所有的數據關係我們都建立完畢了
選擇到數據視圖
然後呢再選擇到我們的銷售數據
這樣一張表格
好
大家可以想一下
這邊會有數量
然後大家拿到課件了以後呢
大家先可以看一下中間的邏輯啊
我們會有數量
然後呢在零售價
表單當中呢
我們就會有這個零售價格
我們怎麼樣把零售價格
把它給放進我們的銷售數據當中呢
那這樣的話呢
我們需要用到Max函數
這邊我們可以新建一列
然後名稱呢可以叫做零售價
然後呢我們可以到這邊再來寫到
等於什麼呢
等於我們的related函數
relationship呢就是關係的意思
related就是和什麼有關係啊
那之前我們已經建立好關係了
那related呢
就是說那我們這張表格
和其他有關聯的一些表格
我們可以用到related函數
然後把中間的內容
把它給調取到某一張相關的表格上方
啊我們可以選擇到related函數
然後再選擇到呃零售價
這樣張表格中間的零售價格確定好
這零售價格呢
它也會根據我們的這個產品
比如說產品一啊就是150
產品2就是231等等
接下來我們再添加一列
然後這塊呢可以給它命名叫做銷售額
然後它等於什麼呢
我們可以選擇到下方
好添加進來了以後
我們等於啊
等於我們的數量乘以我們的零售價格
對吧
他就等於我們的這樣一個銷售額
確定好
大家可以看到啊
最後呢我們算出結果就在這塊
因爲呢
等一會我們會要把這個內容呢
把它給拉扯進我們的數據透視表當中
所以我們可以到這邊來做一個度量值
度量值它的特點是
我們能夠把度量值
放放在各種環境當中
當我們在數據透視表當中篩選行
篩選列我們的度量值都可以發
生一個精確的變化
所以我們可以用這樣一個值
去代替所有的內容
那這塊呢
我們直接寫到雙擊啊銷售總額
冒號等於什麼呢
等於SUM函數
然後再選擇到我們的銷售額系列當中
確定
我們可以把它給拉開來
看一下我們最後的結果
那這個值呢
是銷售總額
我們可以把這樣一個值
放在各種環境當中
比如說哎
在數據透視表當中啊
比如說這是有商調貨對吧
那他所統計出來的呢
也都是對應到的
我們的這樣一個表頭內容
好在剛纔的製作過程中間
我們加入了額外的兩列
比如說這邊會有零售價格
從呃零售價格表單調取過來
以及銷售額
我們用零售價格和數量去進行相乘
然後呢最後呢
我們再用這樣一個度量值
把銷售額進行一個彙總
其實一步呢
我們也可以把它給做到
這邊我們可以雙擊這個單元格
然後呢可以寫到銷售
總額a啊
和上方的這樣一個名詞呢
進行一個區分
冒號等於
SUM x函數
然後是在哪一張表格中間呢
是在我們的銷售數據表格當中
然後我們的表達式呢
我們可以用到related函數
調取出零售價格表單中間的零售價格
括弧回來
然後再去乘以什麼呢
乘以在我們這張表格中間的數量
括弧回來
確定好大家可以看到啊
這個結果呢
也都是一樣的
所以
我們可以用這樣一種text的方式呢
去節省一些空間
好
在這樣一個比較簡單的情況下面呢
我們就只製作一個度量值
好吧那接下來我們可以把power PIVOT
剛纔這一塊呢
把它給整個保存一下
現在呢
我們可以選擇到這邊數據透視表
或者呢你直接把它給關閉
然後呢新創建一個工作表
然後呢到這邊選擇Alt and v啊
來插入數據透視表
並且呢使用到此工作的數據模型
也就是剛纔在power PIVOT中間
所建立到的
這樣一個模型
現有工作表的呃這樣一個單元格
點擊到確定好
然後呢
我們啊現在就可以把日曆把它給打開
然後呢把日曆的層次結構把它給打開
我們雙擊它把它給放在右側
然後我們可以到中間呢
來選擇到我們的年份
放在行的位置
然後呢我們可以把銷售數據啊
這樣一張表單
中間的銷售額把它給放過來
那這個呢就是整個我們的彙總情況
或者呢
你把這個銷售額把它給放過來
也可以了
都是一樣的結果
都是一樣
大家可以對比一下
好吧
然後呢我們還可以依照客戶
他的這樣一個名字啊
比如說這邊呢會有很多人負責嘛
我們可以選擇到
客戶負責人啊
然後把這個負責人呢
把它給放在我們的列的位置
這樣的話呢我們就可以看到
那當然了
這邊我們插入了兩個銷售總額
我們只需要選擇到一個就行
那這個呢
就是02年到04年
每一個負責人他們的情況
同時呢我們還可以插入數據透視表
Alt and v
然後選擇到現有工作表某個單元格
這一次呢我
們可以把這個隨便啊
這邊你可以依照我們的產品名稱啊
然後看一下產品銷售的情況
然後呢
把這個度量值把它給拉扯在值的位置
好吧並且呢
這個中間還可以加入各種各樣的元素
啊包括這一邊
就比如說產品一產品2
產品三產品4
所對應到的
這樣一個省份當中的銷售情況
好吧我們可以把客戶地址呢
把它給放在行的位置了
然後把產品1234呢
把它給放在列的位置了
那這個呢
就是整個我們統計出來的情況
那今天呢
整個我們的這個系列也就講完了
那其實呢
也就告訴大家
最簡單的這個建模應該怎麼樣去考慮
那當然了
我們這個情況比較簡單
如果你的情況特別特別複雜的話呢
可能你只有用到這種方式
我們才能夠實現
就是你想要的這樣一個目的
好吧那如果大家對於power系列
感興趣的話呢
大家可以去購買我的power頻道啊
