如何按分類篩選出前幾名?一種可以用我們的無所不能的函數 sumproduct,相關案例我就不一一舉例了,有興趣的後台搜索一下 sumproduct。
除此之外,Power Query 也是這方面的能手。
案例:下圖 1 是公司各銷售人員不同日期的交易數,請篩選出每個人的最大 2 次交易數。
效果如下圖 2 所示。
請忽略我教學數據表的小 bug,A 列的标題忘記改成“姓名”了,不過這不影響咱們今天的教程。
解決方案:
1. 選中數據表的任意單元格 --> 選擇菜單欄的“數據”-->“從表格”
2. 在彈出的對話框中點擊“确定”
數據表已上傳至 Power Query。
3. 選擇菜單欄的“主頁”-->“分組依據”
4. 在彈出的對話框中點擊“确定”
5. 選中代碼中的 Table.RowCount(_),将其替換成以下語句:
Table.FirstN(Table.Sort(_,{"交易數",1}),2)
- Table.Sort(_,{"交易數",1}):将“交易數”列按降序排序
- Table.FirstN(...,2):提取排序後的前 2 名
修改代碼以後,原本的計數列現在就變成了排序表。
随便選中其中的一個單元格,下方就會列出表格的明細,好像已經是我們想要的樣子了。
6. 選中“計數”列或其中的任意單元格 --> 選擇菜單欄的“添加列”-->“重複列”
接下來我們要做的就是把這個重複列的代碼稍微修改一下,從而将篩選表的結果提取出來。
7. 将公式欄中的 Table.DuplicateColumn 替換成 Table.ExpandTableColumn
8. 将 "計數 - 複制" 替換為 {"交易數"}
完整代碼為:= Table.ExpandTableColumn(分組的行, "計數",{"交易數"})
神奇的情況發生了,本來三列的表變成了兩列,且按照我們的要求篩選出來了。
9. 選擇菜單欄的“主頁”-->“關閉并上載”-->“關閉并上載至”
10. 在彈出的對話框中選擇“現有工作表”及需要上傳到的位置 --> 點擊“加載”
綠色區域就是篩選出來的結果。
很多同學會覺得 Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解 Excel 的各個知識點。
現在終于有了,文中專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握 Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、打印技巧等……學完全本,你也能成為 Excel 高手。
,