用公式折騰到凌晨，透視表1分鐘解決不爽嗎？

2020-10-20 Excel不加班

來自凌晨的求助，希望能用公式解決。

要根據期間、科目兩個條件，篩選出前5大的數據。

這種用公式當然麻煩啦，先看看參考公式。

=INDEX(C:C,MOD(LARGE(($F$4=$A$2:$A$24)*($G$4=$B$2:$B$24)*$D$2:$D$24*100+ROW($2:$24),ROW(A1)),100))

先來看看透視表的效果，根據期間或者科目的條件進行篩選，都可以自動獲取前5大的數據。

有的時候，透視表1分鐘就能解決的問題，確實不知道要折騰公式幹什麼？跟著盧子一起看看是怎麼操作的。

Step 01 創建透視表，將期間、科目拉到篩選，公司拉到行，金額拉到值。

Step 02 對金額進行降序排序，對區間、科目進行篩選。

Step 03 值篩選，前10項，最大10項改成5，確定。

就這樣，簡簡單單，核心部分全搞定。

推薦：

上篇：

聊一聊，為什麼你總喜歡用公式解決問題？

作者：盧子，清華暢銷書作者，《Excel效率手冊 早做完，不加班》系列叢書創始人，個人公眾號：Excel不加班（ID：Excelbujiaban）

相關焦點

  • 1分鐘就解決了！接到學員問題，透視表拖拉幾下就解決問題！
    這種問題如果用公式，是非常麻煩的，用透視表卻分分鐘搞定。我們是來解決問題的，什麼方法簡單快捷，優選選擇這種方法，不要為難自己，也不要折騰別人。Step 01 插入透視表，將型號拉到篩選區域，日期拉到行區域，產量拉到值區域。
  • 一對多查詢的幾個公式,可以直接套用,閒公式複雜,用數據透視表
    但這些函數普通用法，只會查找到第1個符合條件的結果，如果碰到下圖中的情況，有多個結果時，普通用法難以完成。今天分享一對多查詢的幾個公式，公式都比較複雜，用時直接複製粘貼，再修改單元格地址即可，不明白可以留言或私信我。另外，最後一種方法用數據透視表功能來完成，相信小夥伴們都能學會！
  • 火眼金睛——數據透視表(1)
    這個大家一定要學會，剛開始在工作中，我還沒學會數據透視表時，那真是一個頭兩個大，有一次加班到凌晨，就在提交表格的前一刻，我都不敢保證結果是正確的，從那時開始，我就痛下決心學習EXCEL，學習過程比較慢，前前後後有將近半年的時間，在學函數更是痛苦，還好我遇到了數據透視表，那真是遇到真愛也不為過，感覺整個世界都是明亮的！所以，我一定要給大家分享數據透視表，讓它的魅力遍灑全網！
  • 除了用命令排序外,還可以用函數公式、透視表排序
    排序，在Excel中是非常常見的操作，除了用命令排序外，還可以用函數公式去實現。一、Excel排序：命令排序。目的：對「銷售額」按「降序」排序。解讀：1、用Rank函數排序時，排序的順序由第三個參數「排序方式」決定，當省略或為「0」時，按「降序」排序，為「1」時按升序排序。2、對每個銷售額而言，排序的範圍都是固定的，所以數據範圍（F3:F9）要用絕對引用或混合引用。
  • Excel小白如何用一個晚上就搞定數據透視表?
    在知乎上提問《如何快速學習數據透視表？》的回答下，@指北針的回答獲得了接近4000個贊：裡邊列出了數據透視表的很多很多實用乾貨。然而，這些乾貨對一個完全不知道數據透視表有何奧妙的小白來說，真的是屠龍之術，就算一時好奇照著教程操作一遍，我敢打賭，絕大多數小白看了這些乾貨的第二天，就會忘得乾乾淨淨。為什麼？
  • 你會用數據透視表嗎?
    數據透視表，大部分Excel用戶都已很熟悉的功能。今天為大家介紹一個數據透視表的另類用法，99%的人都想不到。根據明細表批量生成固定格式的表格，一直是Word郵件合併的專利。但，如果要求生成多個Excel工作表表格，郵件合併也無能為力了。
  • Excel數據透視表高級篩選用欄位和公式組合多個條件並篩選到新表
    在 Excel數據透視表中也可以進行高級篩選，並能把篩選結果得到到一個新工作表中。像在 Excel 普通表格一樣，高級篩選條件區域既可以用單個條件也可以用多個條件；多個條件的組合方式分為用欄位組合與用公式組合；如果條件比較複雜，用欄位不好組合，可以用公式組合。在數據透視表中，除可篩選普通的欄位外，還可以把分類匯總結果一起篩選。
  • 求條件最大值,Excel函數公式,還是用數據透視表!
    今天是2019年5月1日，祝各位小夥伴們勞動節快樂！輸入公式：=MAX(B:B)2、最高價格對應的商品是什麼最高的價格是30，那麼對應的商品是什麼，方法很多，這裡採用一個經典的INDEX+MATCH組合的方法，使用公式：=INDEX(A:A,MATCH(MAX(B:B),B:B,0))3、某些產品的最高價格是多少
  • 有問題衝透視表來，別為難公式了
    比如8月5日出現2次張三，同一天的只能算1次。這個案例是無意間看到某同行這樣寫的公式，真夠複雜，你看懂了嗎？=COUNT(0/(MATCH(A$1:A10&E2,A$1:A10&B$1:B10,)=ROW(A$1:A10)))真的太為難公式了，這種問題透視表出馬，輕輕鬆鬆3步搞定！
  • Excel表格中一對多查詢的幾個公式，可以直接套用，閒公式複雜，用數據透視表
    日常工作中，從表格中按條件查詢數據一般都要想到LOOKUP和VLOOKUP函數，還有一個INDEX和MATCH函數組合也是超好用的。但這些函數普通用法，只會查找到第1個符合條件的結果，如果碰到下圖中的情況，有多個結果時，普通用法難以完成。
  • Excel財務帳齡分析,數據透視表1分鐘輕鬆搞定!
    如下所示，左邊A:D列是公司的應收明細，現在需要匯總，各家公司30天以內，30-59天，60-89天，90-120天，120天以上超期的金額分別是多少用數據透視表簡單幾步就可以完成了。使用數據透視表快速匯總首先在原始數據中新增一列，計算出每一條明細超期的天數，我們使用公式：=TODAY()-C2然後我們插入數據透視表，放在現有工作表位置G1單元格將客戶放在行標籤，將超期天數放在列標籤，將金額放入值，會得到一個很長的數據透視表
  • 還在用複雜的公式嗎?就是它分分鐘統計出數據個數
    還在用複雜的公式嗎？就是它分分鐘統計出數據個數大家好，很高興又和您如期相遇在學習的道路上，一起攜手同行，共同進步。二、解決方案Excel老鳥肯定第一時間想到的利用函數公式，沒錯，函數公式的確很好用，但是需要使用者有一定的操作基礎，這裡也算拋磚引玉，看下如何更好地利用公式來完成需要完成的任務。通過上慄觀察分析發現，需要統計的部門和商品有的出現的次數是一次，有的可能超過了三次。
  • excel數據透視表:善用這些功能,提高工作效率!下篇
    用excel做張老闆最愛的自動化表格，讓你的工資翻一番！》。七、總表拆分為分表看到這個標題，小夥伴們有沒有覺得很熟悉呢？沒錯在前不久的文章《別再為拆分、合併工作表鬧心啦！最實用的7種方法，分分鐘搞定它！
  • Excel – 才知道,數據透視表也能用條件格式自動凸顯數據
    數據透視表用得不少，但是很少人想到：數據透視表也可以配合條件格式自動凸顯數據。話不多說，來看案例。案例：下圖 1 是一季度各銷售部門的指標和實際業績表，請用該數據表製作出數據透視表，要求：所有比指標低的實際值自動變為紅色實際值之和大於 500 的所有姓名單元格自動填充綠色
  • Excel教程:用透視表一樣做好帳齡分析表!(上)
    《一張應收款帳齡統計表逼哭了多少會計人》，涉及到一個公式：=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"})雖說方便有效，但對於暈函數、暈公式的朋友來說顯然不是一個好的辦法：「不能總是炫公式呀——很難記的」「不要歧視我們這些函數小白」
  • excel數據透視表：善用這些功能，提高工作效率！下篇
    在上篇文章中，我們為大家分享了透視表的前5條妙用，分別是合併同類項、按條件匯總數據、統計非重複數據、排名、批量創建表格，不知道大家都還記得嗎？那麼今天我們書接上回，繼續為大家分享關於透視表的後5條妙用，趕緊來看看吧！（由於篇幅原因，文章分為上下兩篇，本篇為下篇。）
  • 不懂公式、透視表、PQ的同事,卻輕鬆搞定跨表匯總的Excel難題
    公司來了一個新同事，Excel水平好像很差：不會寫複雜的公式，數據透視表也只會簡單應用，更別提Power query這種高級功能了。很多同事都有點看不起他。如下面的產品A，江蘇表在E列，安微表在C列，上海表卻在D列，其他表也不固定。匯總後效果：該怎麼匯總？所有人都發愁了：一個一個加？如果表很多列很多，會累S的。
  • 怎樣用一張數據透視表打動老闆
    因為數據透視表基本上就能夠滿足初級和簡單的數據分析問題，學習起來也並不是非常複雜，在跟領導進行匯報的時候，善於巧用數據透視表能讓你的表達更加深入和準確。那麼，具體怎麼用數據透視表來進行分析呢？二、創建數據透視表操作步驟是：插入-數據透視表-選擇數據區域-勾選「新工作表」-確定。
  • excel圖表技巧：切片器加透視表製作動態圖表
    數據驗證配公式；切片器加透視表；定義名稱與控制項；兩兩搭配動圖現。上次武林盟主使用的是「數據驗證配公式」大法，今日老衲傳授的系「切片器加透視表」大法。廢話少說，請施主先看看GIF操作吧。第二式：設置欄位列表首先在「數據透視表欄位列表」中選擇欄位，將不同欄位拖入拖動到不同區域。
  • 戰鬥機透視表居然搞不定，還得靠這個函數解決
    ，不過這個不影響，公式是沒問題的。統計數據的話，盧子一般採用透視表，不過這個案例用透視表效果並不好，退而求其次，選擇用公式解決。多條件求和可以用SUMPRODUCT或者SUMIFS函數，考慮到數據會隨時更新，用SUMIFS函數效果會更好。1.