1_2_01.透視列(行轉列實現一維轉二維)

Urin的自习室
19粉絲
2024年3月6日 #Excel #PowerQuery
各位朋友大家好
從這節課開始
我們進入到第二章的學習
第二章主要講解的是精細數據整理
讓我們的數據更規範
那在上一章
我們講了
以不同的方式
來導入數據到power query當中
那麼這節課的話
我們把導入的數據
做一些更精細化的處理工作
好 具體的這節課我們講的是透視列啊
將行方向的數據轉換成列方向轉換到
那麼實現一維轉二維
我們來試一下啊
來試一下
嗯
好 我們打開這個數據
先來看一下這裏的話
其實就是一個一維表
何爲一維表呢
就是看每一條數據
我只需要把每一行的數據
給它讀取出來
哎二維表的話
你一個值
你既要看什麼呀
橫行方向
也要看列方向
交叉起來
你可能才能讀取到這個數據
那麼比方說
這邊這個就是二維是吧
這個數據 500253
它是指的動畫片裏面的2015年啊
2015年 對
而這個呢
它可以把每一條數據哎
這樣來整理出來
那麼接下來我們來試一下
我如果根據現在的這個數據
來生成右邊的這個二維表
其實就相當於彙總是吧
行方向放置影片的類別
列方向放置它的年份放在這裏
那麼這個的話實現的方法其實挺多的
呃如果我們拋開
不用power query來完成的話
可以用數據透視表
但用power query的話
其實也可以完成這個效果
但是前面我們講的分組依據裏邊
好像是不行是吧
好像不行
好我們來試一下
先來試一下
我把數據啊弄到一個新的表
裏面來然後呢
然後呢我們選擇數據
從表格把數據導入進去
好導入進去
導入進去之後的話
你看他對數據做了一些轉換啊
比方說這個我覺得我不想轉換
你可以把它取消
取消它自動的這個更改也可以
而現在的話
他其實如果你不看方向的話
就是原來這個
原來這個
如果你不看方向的話
它其實是
呃
這個以類別以年份來彙總數據是吧
有兩個字段
只不過呢
它在這裏是擺放的方向
一個在行方向
一個在列方向
如果我們用之前的那個
呃
分組依據來做的話
點擊那麼啊
先用分組依據的點擊
然後呢首先我們是以年份
然後再新建再這個影片的類別
來對我們的銷售數量進行一個彙總
所以你求和
然後選擇出售數量
然後比方說叫做總數量是吧
總數量OK
這就是標準的完成的這個效果
來 大家看一下對不對對不對
然後呢
現在問題就是我不想這樣來呈現是吧
我能不能把這個年份或者是類別
放到列的方向
我的意思就是像這個樣子
比方說他的年份放到這個方向了
那麼就是放到列的方向了
然後類別放到行的方向
其實是可以的
接下來我們就實現來轉換一下
我想轉換的是影片的類別啊
哦轉換的是年份
那我就選擇年份
在轉換這個地方
有一個叫做透視列啊
點擊透視列
透視列的話
他說你以年份來透視的值列
哎就是要進行計算的列
那麼肯定是什麼呀
總數量就是這
邊然後下邊還有一個選擇
就是你要幹什麼啊
就是你把相當於的意思
你要對於你選擇的年份
要放到列的方向
透視列
你就想就就理解成放到列的方向
然後呢我想放到這個方向之後
我要透視哪一類
就是要改變哪哪哪裏的數
數據那麼是總數量
對總數量幹什麼
有很多種方式
大家看
是不是我們分組依據裏面的那些
它默認是求和
我們來求和一下
好點擊確定
這個時候你看它是不是擺放過來了
擺放過來
那麼看到的效果就是一樣的
那你說能啊
你可以雙擊
再次雙擊這個步驟
那你看能不能修改一下呢
比方說我修改成平均
哎你看沒有變化
然後呢
只有一個
那麼你甚至也不要聚合也可以是吧
就是不要聚合
就是不要進行任何的彙總處理
那你最終可以看一下
如果以技術的方式來看的話
就可以看得出來哦
原來是隻有一個值
當然如果有多個值的話
你就不能用
不能用什麼呀
不要聚合
因爲如果有多
只有當那個值是唯一的時候
唯一值出現的時候
你纔可以使用這個
否則的話
你使用它會出錯
等一下我們可以來試一下
這就是這個效果啊
這個效果
當然我把它取消
你不但可以以這個年份
放在列的方向
也可以把這個類別也放在那個方向
然後呢選擇總數量進行求
啊 選擇年份來聚合
總數量默認是求和
其實剛纔大家看到它是這個裏面的
這個裏面的每個值都是唯一值是
吧
就是比方說14年愛奇藝這樣這個數量
14年動畫片這個數量
然後15年又是愛奇藝
是不是他們都是唯一值
所以呢你用哪種彙總方式都可以
然後接下來的話
你可以把這個數據上載
並傳到啊外外層的表格裏面來
是不是就完成了
除了這種方式
其實
我們也可以有另外一種方式來操作
啊我複製一份出來啊複製
複製一個表出來
然後我把它取消學校
然後我其實可以直接就是不用
剛纔的什麼分組啊
分了組再透視透視列啊透視列
透視列的話
我還是就選擇我還是就選擇什麼呀
嗯出售的數量然後呢進行求和
我們來看一下哎是不是不行
不是不行是
呃
哎是爲什麼
是因爲我的字段太多了
因爲我是以年份
然後呢來透視初始的數量來進行求和
然後呢把年份放在列字段啊列的方向
而這兩列呢自然在行的方向
然後這個時候他的數據就特別多
是吧因爲你有什麼影碟名稱這些
所以你爲了能夠成功的話
其實你可以把這邊刪除啊
刪除之後的話
等下我選擇以年份來透視數量啊
出售數量刪除它就可以了
對不對
好我們再來看一次
你直接進行透視它是不行的
選擇
然後出售數量求和是不是不行啊
其實不是不行是什麼呀
他以這兩個爲準是吧
以這兩個爲準啊
他不是以一個字段爲準
所以呢看到的擴展數據就很多很多
那其實他還是進行了這個求和
所以呢我們有必要把這邊給他什
麼呀你說我
我做好之後再刪除可不可以呢
啊這樣的話就不行是吧
一定要在透視之前把它刪除啊
啊這個時候你看到的透視列纔可以
好
我們就重新做一次選擇啊
數據源導入進來之後刪除
然後選擇年份
裏面順便有更多的瞭解
哎如果我計數
你來看一下
以出生數量來計數
是不是就會出現了
這個意思就是
相當於2014年劇情片有3個
2015年有3個
2016年有一個
然後甚至有6個的這個恐怖片 2016年
那如果說這個時候
你要把它設置成其他的都行啊
比方說最小值啊
這些可以找一個最小的
3個裏面找一個最小的
是吧
恐怖片裏面6個找一個最小的都行
你就是不能夠把它改成誰
不能把它改成不要聚合
看到沒不要聚合之後
你會發現全部都是錯的啊
不是全部都是錯的
有的就是錯的
你看選擇呃
這個error
這下面有
不進行激活的話
它怎麼擺放你的數據呢
有多有少的啊
那你說爲什麼有的可以呢
啊有的爲什麼可以呢
我們來看一下
可以的原因是因爲他的數據只有一條
看到沒這隻有一條的
他就可以正常的顯示出來
有多條的就全部會出錯啊
全部會出錯啊
這是它的這個特特性
特點
那麼我們還是把它轉化爲什麼呀
求和啊
OK這樣就完成了
是不是兩種不同的方式來完成
第一種的話
可以先對我們的數據源進行一個分組
之後分組之後
然後再進行透視
因爲分組之後的話
每一行數據
都是什麼呀
唯一的每一條數據都是唯一
在進行透視的時候
無論我們使用哪一
不需要的是現在不需要的是影片名稱
這一列刪除
刪除之後直接來做透視啊
就就來做透視列
因爲直接透視列的話
它這裏是有進行彙總的選擇
也就是說有進行聚合函數選擇
而這是它的基礎
接下來呢
我們再來試一個啊
我把它放棄
然後這個呢
放棄接下來呢
我們再來說一個啊
實例啊再講一個實例
就是多單元格記錄合併及彙總
哎這個實例的話
它的實用價值可能更高一些
更高一些
那麼看一下
比方說現在我想我想找出每個部門啊
假如這這個是一個記錄的話
顯示的是每個部門某某人
他的捐助金額
然後總金額一共捐了多少錢
財務部有哪些人捐錢啊
人數是吧
人數名單都列在這裏
總共多少錢對吧
哎這個效果
這個效果的話
它的這個
嗯
實用性就更高了
他是怎麼做的呢
哎首先的話
我們把數據導入進來啊
然後再複製一個出來
複製一個出來
把複製出來的這個表做成
進行進一步的數據處理彙總
然後呢再進行什麼呀啊
再和他進行一個呃
合併的查詢是吧
合併查詢好
我們來看一下究竟是怎樣的完成的
這個效果好
我們把這個數據給它複製的一個新表
啊
以免他受到干擾是吧
我們做後面的時候
然後就進入到編輯查詢編輯器裏面
給他改一個名字
這個的話我們就叫做捐款表是吧
啊捐
款表的那個名單
然後呢再複製它啊
是下面這個複製
上面是複製它數數據
具體的數據好
然後到這個表的話
要給它改一個
叫做彙總表
是不是
那麼接下來我們就最重要的是
先把彙總表給處理起來
怎麼處理
把每個部門的名單給他
怎麼樣收攏起來
然後我們是這樣來做的
大大家注意看
先選擇這個GN專注的金額
這一列暫時不要
然後呢我們插入一個新的列啊
叫做添加索引
那麼其實就是添加一個索引值是吧
使每一行
使每一行的都是列
透視列透視列
好選擇好之後的話
我們對什麼呀
對
對哪一個
對姓名選擇進行什麼樣的設計
不要進行任何的設計
只要做一個擺放就行了
是吧你不能做計數啊
啊你看技術是這個樣子
他就哎會計算他有多少個
但是呢這種方式不行
那麼我們想把他這個名字給顯示出來
就說不要聚合
你看把它顯示出來了
這樣的話
每一個部門的
大家沒有發現
每一個部門的人他就全部出現了
比方說管理部有這三個人
他出現在這裏
財務部有這兩個人
銷售部有這四個人出現在這裏
然後接下來最重要的是
我們是將這些人民把它連接在一起
啊怎麼連接
前面合併列
合併列的話
合併之後我們可以做一個名稱
叫做名單啊
新新列的名字嘛
然後呢我們可以空格來分隔啊
來來合併作爲分隔符號
哎這個時候你看是不是
然後這個時候你看肯定哎
你像這些的前面後面啊
可能中間這些都有
都有空格
那怎麼辦
我們可以先做一次去除
前面已經講過了
在轉換的格式裏面做一次修整
哎你看
把兩側的空格全部去掉
中間的只有
只有一個空格的話
留一個
然後接下來我們再來進行一個替換值
查找的是空格
替換值什麼呀
比如說斜槓啊
左斜槓右斜槓都行啊
比如右斜槓吧
啊
是併到這裏的
也就是這是
這是我彙總的表
然後這是我的捐款表
我想把每個部門的表的數據
什麼樣把它列到後面來
來加到上面來
是不是好
我們選擇彙總
哎剛纔這是可以任意拖動啊
拖動下面的一些
然後呢
我們選擇什麼方式來給它加入進來呢
好我們前面講的叫做
叫做這個合併查詢就可以派上用場
我可以將這兩個表進行合併起來是吧
然後選擇合併查詢
然後選擇第一個
然後第二個捐款表
那麼以部門爲這個連接的字段
然後呢以他爲基準是吧
就說左外
哎第一個成功了
管理部對應的有哪些記錄
他以表的形式來呈現
大家可以看一下
管理部這三個人
是不是在下面可以看到
財務部呢
這兩個銷售部呢
這四個
然後現在我希望把它擴展開來
可不可以啊
可以你可以擴展開來看一下
啊
看到沒哎
擴展開了
但是呢擴展開了這個數據的話哎
擴展開之後
你就可以以什麼呀
以部門和名單來彙總他的捐助
但是其實如果你就想彙總的話
沒有必要展開
可以直接點擊這個啊
他除了擴展
我們是不是還有個聚合
聚合的話我們就選擇以金額來進行
什麼呀記號啊
點擊確定
看到沒這下就OK了
這下OK了
是不是直接就把它是吧總金額
好完成
那麼這個數據就OK了
然後把它上載出來
然後比方說我選擇一個鏈接
或者說做一個表也行是吧
好直接做成一個表格也行
那麼這個表他也拿出來了
我們不需要
好這是個數據源
這呢是處理後的結果
你可以看一下
管理部是不是1,000塊
然後呢管理部是不是1,000塊
這三個選擇起來
1,000 這些順序的話
其實可以不用在一起都行是吧
這些順序可以不用排在一起都行
然後還有剛纔那個
還有剛纔的那個計數啊
忘了做是吧
計數的話
如果你要做的話再加上去就行了
好那麼我們關閉並上載
然後它是不是就出來了啊
就出來了
但是這個的話它有一個不智能的地方
就是當我的記錄增加的時候
它自動處理不了啊
自動處理不了
就比方說我現在又增加一個人
我的名字在某某部門
然後這邊的話它處理不了啊
原因是在於什麼呢
原因在於在合併的時候
我在這邊
合併的時候就是就是進行轉制啊
在這個地方進行合併列的時候
我是手工選擇的10到8
如果再增加一個的話
它能夠自動增加這個索引9
但是由於我是手工選擇8的話啊1-8
所以9沒有被選一
它沒有被合併
那麼有沒有方法啊目
前來說我們這種方法來解決的話
是比較完美的
大家也看到了關於這個透視列
它的一個用法
是吧其實用的非常的多
所以爲什麼我要在這裏
第二章一剛開始就要講這個功能呢
下節課我們來講
和它相反方向的另外一個功能
叫做逆透視力
好了
那這節課這個內容我們就講解到這裏
同學們再見
