日期沒有排序,用Excel快速查詢產(chǎn)品最新報價的7種函數(shù)公式法
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2023-03-16 14:30:19點擊:7674
編按:
如果報價清單中的日期沒有排序,怎么才能快速查到各種產(chǎn)品的最新報價呢?直接套用網(wǎng)上Lookup函數(shù)1、0結(jié)構(gòu)或者2、1結(jié)構(gòu)套路公式是無法查到的,必須再搭配一個函數(shù)解決排序。今天就給大家分享7種不排序就能查最新價格的方法。
如圖所示,每種產(chǎn)品都有多個日期的不同報價,并且這些日期并沒有從小到大升序排列。現(xiàn)在需要查找出對應(yīng)產(chǎn)品最新的報價。
首先分析一下這個問題。
如果日期是按升序排序了的,則就是一個簡單的單條件查找,查找最后一個(日期最大)符合條件(產(chǎn)品名稱)的價格,用LOOKUP的標準1、0結(jié)構(gòu)套路公式即可查到。但是現(xiàn)在日期沒有排序,本質(zhì)上就變成多條件查找了。條件1是產(chǎn)品名稱,條件2是產(chǎn)品對應(yīng)的日期最大值。
理清楚這個邏輯,解決思路也就有了,以下為大家介紹7個不同的解法。
不排序查最新價格方法1 LOOKUP +MAXIFS函數(shù)
公式為=LOOKUP(1,0/((MAXIFS(B:B,A:A,E2)=B:B)*(A:A=E2)),C:C)
這個公式用的LOOKUP多條件匹配的標準套路:
=LOOKUP(1,0/((條件1所在列=條件1)*(條件2所在列=條件2)),結(jié)果所在列)
需要注意的是公式中用到了MAXIFS(B:B,A:A,E2),這個函數(shù)的作用是按條件返回最大值,用法與SUMIFS類似,在2016及以下的Excel中可能沒這個函數(shù),因此公式需要做對應(yīng)的調(diào)整。
不排序查最新價格方法2 LOOKUP +MAX+IF函數(shù)
公式為=LOOKUP(1,0/(E2&MAX(IF(A:A=E2,B:B))=A:A&B:B),C:C)
與公式1有兩個區(qū)別,區(qū)別1是用MAX(IF(A:A=E2,B:B))取代了MAXIFS函數(shù),區(qū)別2是用&對兩組條件進行合并,將多條件變成了單條件。
不排序查最新價格方法3 INDEX+MATCH+MAXIFS函數(shù)
公式為=INDEX(C:C,MATCH(E2&MAXIFS(B:B,A:A,E2),A:A&B:B,0))
這個公式用的是INDEX+MATCH這對經(jīng)典組合,用MAXIFS得到產(chǎn)品對應(yīng)的最近日期,再用&將多條件變成單條件,也算是一個常規(guī)思路了。
不排序查最新價格方法4 FILTER+ MAXIFS函數(shù)
公式為=FILTER(C:C,(A:A=E2)*(B:B=MAXIFS(B:B,A:A,E2)))
這個公式用的兩個都是新函數(shù),FILTER函數(shù)的作用是按指定的一組條件或多組條件篩選數(shù)據(jù),用法為FILTER(結(jié)果所在列, (條件1所在列=條件1)*(條件2所在列=條件2)),關(guān)于這個函數(shù)的詳細用法可以參考之前的教程。強大的篩選函數(shù)FILTER用法集
不排序查最新價格方法5 VLOOKUP+SORT函數(shù)
公式為=VLOOKUP(E2,SORT(A$2:C11,2,-1),3,)
這個問題還有個特點就是數(shù)據(jù)源并不是按日期排序的,完全是亂序,所以要使用SORT函數(shù)先對數(shù)據(jù)源排序,SORT(A$2:C11,2,-1)的意思是對數(shù)據(jù)源按照第二列降序排序,這樣得到結(jié)果最近的日期就拍到前面了,再用VLOOKUP匹配得到的就是最新的報價。
不排序查最新價格方法6 SUMIFS+ MAX+IF函數(shù)
公式為=SUMIFS(C:C,A:A,E2,B:B,MAX(IF(A:A=E2,B:B)))
當滿足多個條件的結(jié)果是數(shù)字且只有一條時,多條件匹配和多條件求和的結(jié)果是一致的,所以這個問題也可以用SUMIFS來解決,關(guān)于SUMIFS函數(shù)大家都很熟悉了,這就不啰嗦了。比較特殊的就是有一組條件要用MAX+IF或者MAXIFS得到。
不排序查最新價格方法7 SUMPRODUCT+MAXIFS函數(shù)
公式為=SUMPRODUCT((MAXIFS(B:B,A:A,E2)=B:B)*(E2=A:A),C:C)
既然SUMIFS都可以解決,SUMPRODUCT更加可以了,如果之前的公式都懂了,這個公式也就沒任何難度了。
以上雖然列舉了7個方法,實際上搞明白原理的話,還可以組合出更多的公式來,有興趣的同學(xué)可以自己試試,把你組合的公式分享出來。
最后,除開上面的函數(shù)公式法,也可以用數(shù)據(jù)透視表的方法獲得產(chǎn)品的最新報價,有需要了解的伙伴可以留言聯(lián)系我們。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!