今日目標：
用PQ整理表格
Excel中的函式公式難題，本質上都是因為資料不規範。
我們寫的各種複雜公式，本質上就是在給不規範的資料「擦屁股」！
你同意嗎？
1- 同意，我天天擦屁股
2- 不同意，自己的Excel水平不行，別賴茅坑
昨天群裡有一個HR的同學提問，要計算每個人的績效平均分。
你能看出這個資料的不規範在哪裡嗎？
績效考核結果是ABCD的等級，沒法做平均數計算
，需要根據下面的「績效分數」對應表，查詢之後再做計算。
她目前的做法是這樣的：
1- 新增輔助列，使用VLOOKUP把每個等級轉換成對應的數字
2- 再根據查詢到的數字，計算平均值
這個你能看明白吧？
如果看不明白，在
評論區扣1
，改天詳細講解。
問題是解決了，
但是憑空多了很多的輔助列，給人感覺非常的冗餘。
這個時候，「Excel小問小答」讀者群的大佬們，開始按捺不住了！
1-「Excel小問小答」大佬說
我有一個「Excel小問小答」讀者群，群裡高手如雲啊，咔咔一個公式搞定了這個問題。
PC
PC大佬給出的公式是這樣的：
公式如下：
=AVERAGE（IFERROR（LOOKUP（B2：E2，{“A”；“B”；“C”；“D”；“E”}，{5；4；3；2；1}），“”））
整體思路上，和輔助列的做法是一樣的，只不過輔助列的內容，全部都丟在一個公式裡了。
1- 用LOOKUP函式，把ABCD轉換成對應的分數。
2- 查詢表使用{“A”；“B”；“C”；“D”；“E”}，{5；4；3；2；1}，構建記憶體陣列完成，取代輔助列。
3- IFERROR對查詢不到的結果，進行容錯處理。
4- 使用AVERAGE函式，對查詢結果計算平均值。
這個你能看明白吧？
如果看不明白，
評論區扣2
，人多的話，改天寫文章講解一下。
2- Power Query的做法
前面說了，函式公式其實就是，給不規範的資料擦屁股，這是事後的解決方法。
那麼我們換一個思路，在事前做一些動作，把資料規範起來，不讓資料「拉褲兜子」！
1- 規範資料長這樣
規範的資料長什麼樣呢？長下面這樣：
規範的資料有下面幾個明顯的特徵：
1- 一維資料表。查詢資料的時候，不需要行列標題交叉查詢。
2- 量化資料。所有的衡量指標，不用「ABCD」「一般、良好、優秀」等文字描述，用可以計算的數字代替。
有了上面的這個規範的資料，使用透視表，就可以輕鬆統計每個人的平均績效。
2- 用PQ規範資料
顯然，規範的資料處理起來，要比「擦屁股」好看很多。
為了滿足這兩個規範的特徵，我們需要這樣做：
1- 一維資料表。把二維錶轉成一維表。
2- 量化資料。把ABCD轉成數字分數。
這兩個特徵，可以在Power Query中一次性搞定。下面是具體的操作步驟。
1- 載入資料到PQ
首先，把資料載入到Power Query中去。
使用相同的方法，把績效分數，也載入到Power Query中，最終得到兩張表。
2- 逆透視，二維錶轉一維表
然後，使用Power Query的逆透視功能，把二維錶轉成一維表。
大致操作步驟如下：
1- 按住shift鍵，選擇所有的績效列
2- 在「轉換」選項卡中，點選「逆透視」，把二維錶轉成一維表
3- 績效轉數字
最後，使用Power Query的「合併查詢」功能，把「績效表」中的分數查詢過來。
大致的操作如下：
1- 在「轉換」選項卡中，點選「合併查詢」。
2- 在查詢對話方塊中，上面選擇「績效考評」下面選擇「績效分數」。
3- 上面表格選擇「值」列，下面選擇「等級」列，把資料關聯起來，並單擊「確定」。
1- 在合併後的列中，點選右上角的「展開」按鈕。
2- 勾選「分數」列，單擊「確定」，資料查詢完畢。
4- 統計資料
資料查詢完畢後，單擊「主頁」選項卡中的「關閉並上載至」，選擇「資料透視表」，把資料載入到Excel中。
最後，再用透視表統計資料即可，記得把統計方式改成「平均值」。
3- 總結
俗話說
的
好
- 兵挫挫一個，將熊熊一窩！
- 親自先擒王！
- 捨不得媳婦，套不著狼！
不懂資料規範，公式就越寫越長！
掌握了資料規範的思路，還可以融會貫通，在Power BI中完成相同的資料統計，效果如下：
如果想知道Power BI中的做法，在
評論區扣3
，下節課，我們聊聊，在Power BI中如何實現這個效果。
案例表格下載
單擊「閱讀原文」
，加入拉小登Excel知識星球之後，就可以下載每個文章的練習檔案。
如果這篇文章對你有幫助，請幫忙
「點贊」「在看」「轉發」
。
這對我很重要，能給拉小登更多動力，持續分享優質的內容。
我是拉小登，一個會設計表格的Excel老師