用Sumifs函數查找價格

用Sumifs函數查找價格

嗨，各位經常做表格的表叔、表嬸、表哥、表姐、表弟、表小編們有沒有遇到過下面這樣的多條件帶區間查詢單價的情況呢？圖01

為方便看，我把匹配區（A1:D10）和結果區（F2:K4）放一起了，

需求是查詢某個類別在對應時間段內的價格。

這時間段還真是難到不少人，若固定值可以用輔助列，把它們鏈接起來，然后再用Vlookup函數來查找。

可這是要找它在那一個時間范圍內就有點難了！別怕，知道規則了，方法就比困難多了，看圖中我不就列出了3個公式嗎？

解法一：

常規邏輯，既然是查找返回值，我們能用的函數有Vlookup，Hlookup和Lookup，Hlookup這里不適用，用Vlookup“也不行”，那就用Lookup吧,畢竟Lookup還是很強大的。來看公式1，圖片02

公式如下：

=LOOKUP(,0/(($A$3:$A$10=F3)*($B$3:$B$10<=g3)*($c$3:$c$10>=G3)),$D$3:$D$10)

公式解釋：參數1什么都沒輸，參數二則比較長了，一步一步理，理順了就明白了

A3:A10和F3進行比較，要求是一樣的，所以用等于比較

B3:B10和G3進行比較，B列是起始日期，那它肯定要小于或等于G3了，

C3:C10和G3進行比較，C列是結束日期，那它肯定要大于或等于G3了。

比較結果不是True 就是False

來看一下測試圖03

為了方便對比，我對它們做了換行處理，這樣更方便對比

這結果是怎么弄出來的？選中 $A$3:$A$10=F3 ，按F9就可以了，后面兩個一樣。

這時候你可能會有點暈，不理解，簡單解釋一下吧，上面不是分了三段來比較嗎？

這些都是比較結果，符合條件就是True,不符合條件就是False

有了比較結果以后，要把三段的結果合起來，這里用了 * 來運算，* 是乘法運算，

僅當True * True 的時候才會返回 True（and的效果），

所以接著來看一下運算結果吧！圖04

咦，怎么成了1和0 了？因為在Excel中，True可以用1表示，Fasle可以用0表示，做了數學運算后，就自動轉換成數字了！

再用0來分別除這些內容，目的將0轉換為錯誤值。再來看圖05

為什么要把它轉換成錯誤值呢？

我們的星空女神在很久之前就有發過Lookup函數的詳細解，感興趣的還是去搜索出來學習一下吧，畢竟Lookup也是很強大的。

Lookup的思路就解到這里，用它來解，是因為它是查找函數，用它可以返回任何值，同時能幫我們理清思路！

解法二：

這里我們要返回的值是數字，并且符合條件的結果是唯一的，那我們就可以使用求和函數！

繼續剛才的思路，換個Sum函數繼續測試，來看公式2，圖06

公式如下：

=SUM(($A$3:$A$10=F3)*($B$3:$B$10<=g3)*($c$3:$c$10>=G3)*$D$3:$D$10)

和Lookup的區別是Lookup的D3:D10是作為參數三來使用的，而Sum里依然用的*來運算，原理和Lookup參數2是一樣的！

需要注意的是，這是數組公式，錄入好以后要用 Ctrl + Shift + Enter 三鍵結束，否則結果可能不正確

使用office365（版本12725.200006）的用戶不必三鍵，它能自動識別并運算出正確值。

看到這里，你還會說：我不要求和，我要查找嗎？

解法三：

既然用Sum函數能求出來結果，那我們就能使用Sumifs函數來返回所需要的值了。

使用Sumifs函數的話，那就簡單多了，來看公式圖07

第5行使用了公式來返回公式字符數，可以看出Sumifs字符是最少的（少了行號和絕對引用符號）

=LEN(FORMULATEXT(J3))

這個查找問題為什么可以用求和函數來解？因為它滿足了以下兩個條件，

第一：我們需要返回的結果是數字;

第二：符合條件的結果是唯一的。

若是其中一點不滿足那就不能使用Sumifs函數！

解決問題從特征上來解，可以得到更優的解法，若是想要做到通用（比如返回的結果可能有數字也有字符串），那公式就會復雜一些（使用Lookup）！

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。