tft每日頭條

 > 生活

 > excel 中怎樣設置條件求和

excel 中怎樣設置條件求和

生活 更新时间:2024-12-23 19:10:17

你現在的狀态,是過去的你用努力換來的,而你未來的狀态,是現在的你用努力決定的。——繼續學習的一天

在excel中,有一些數據是需要進行動态展示和複雜計算。

動态展示最常見的就是動态圖表和動态列表框,以及切片器等,尤其在數據整理中,列表框是大家應用比較多的基礎功能。

今天我們就通過某外貿公司的2021年銷售數據來分享一下,關于設置動态條件,并根據設置的多個條件,進行銷量的彙總,以作為會議報告的銷售分析指标。

在下圖中,作者設置了三個條件:産品、開始時間和結束時間。

excel 中怎樣設置條件求和(Excel如何設置動态條件)1

這次計算的目的,是為了得到産品在一段時間内銷售情況。

那麼,我們一步步來,先設置動态條件。

正如上文所說,我們通過創建一個動态列表框來設置條件。

首先我們點擊A2單元格,即要設置列表的單元格地址,然後在數據工具欄中找到”數據驗證“功能。

點擊數據驗證的下拉圖标中的”數據驗證“,進入設置界面。

excel 中怎樣設置條件求和(Excel如何設置動态條件)2

并在界面設置中在“允許”下方的列表框中選擇“序列”,如圖中箭頭所示,并在下方“來源”框内,輸入我們要顯示的産品名稱區域,即A4:A18單元格區域,也就是各産品大類名稱。

設置完後,點擊确定。

我們在下圖可以看到,A2單元格設置了一個列表框,可以點擊列表中的任意内容,單元格便會以這個内容來顯示。

excel 中怎樣設置條件求和(Excel如何設置動态條件)3

随後再選擇開始月份和結束月份下方的單元格,再次進行列表框的設置,方法步驟與上面相同,隻不過在輸入“來源”時,将單元格地址更換為B3:L3的月份标題行區域。

excel 中怎樣設置條件求和(Excel如何設置動态條件)4

設置好動态列表框後,可以任意選擇需要查詢的條件。

接下來,就要根據設定的條件,開始銷售數據的計算。

在下圖所示,作者設置的條件是,産品為“Household products”,時間段是6月至7月的銷量。所以我們的思路就是先選中符合條件的單元格區域,再進行求和計算。

excel 中怎樣設置條件求和(Excel如何設置動态條件)5

而在excel中,最為強大的引用函數,必然有offset函數的一席之地。

如上圖中紅框内的公式:=OFFSET(A3,MATCH(A2,A4:A18,0),MATCH(B2,B3:L3,0),1,MATCH(C2,B3:L3,0)-MATCH(B2,B3:L3,0) 1)

這個公式比較長,所以在圖中,根據各參數進行了換行顯示。

offset函數有五個參數,其表達式為:=offset(參照單元格,向下移動幾行,向右移動幾列,引用區域的行數,引用區域的列數)

第一參數——參照單元格:也就是說以這個單元格為參照;

第二參數——向下移動幾行:從參照單元格開始,向下偏移的行數,如果為1,則向下移動1行;

第三參數——向右移動幾行:從參照單元格開始,向右偏移的列數,如果為2,則向右移動2行;

第四參數——引用區域的行數:當從參照單元格開始偏移了指定的行和列數以後,再向下選定指定的行數來作為引用的區域,如果為3,則代表從參照單元格偏移了行和列之後的位置,再向下拉取3行,這3行内容便作為offset函數将要引用的單元格區域;

第五參數——引用區域的列數:與第四參數相似,隻不過它是選定列數作為引用區域,如果是2,就表示再向右選擇兩列。

我們回到公式中,通過圖片中标注的第一到第五參數,可以比較快地理清各個參數的含義。

而其中又使用了一個查找引用函數——match函數。

match函數的作用是返回指定值在一列中的位置,如第二參數的MATCH(A2,A4:A18,0),即表示查找返回A2單元格的值在A4:A18單元格區域的位置,返回的結果以數值顯示,結果是10,這樣便得到offset函數第二參數,即向下移動10行。

而向下移動10行,正好是“Household products”在品類名稱列的位置。

第三參數的MATCH(B2,B3:L3,0),同理,是返回B2“6月銷量”在月份行中的位置,結果等于6.

第四參數的值是1,表示隻選定1行區域,最後的第五參數MATCH(C2,B3:L3,0)-MATCH(B2,B3:L3,0) 1),我們弄懂了match函數的含義,就可以清楚這個表達式的結果是C2返回的位置減去B2返回的位置,再加上1.

簡單來講,就是結束時間的月份位置減去開始時間的月份位置,也就是7月到6月,那麼7減去6等于1,我們再加上1,結果為2,也就說最終選定的區域是1行2列,正是我們指定條件下要求和的銷量數據。

但最後我們要注意,offset函數引用的是一個區域數據,而區域數據就是一個數組,即數據組合,數組公式要三鍵結束,CTRL SHIFT ENTER,否則公式結果會出錯!在公式中,就是以大括号的形式顯示。

我們可以按下F9來運行公式,看看它的計算結果,是兩個值,而非單獨一個值。

excel 中怎樣設置條件求和(Excel如何設置動态條件)6

得到了需要計算的單元格區域,接下來進行求和,就簡單多了。

數組的求和,百分之九十九都是嵌套SUM函數。

excel 中怎樣設置條件求和(Excel如何設置動态條件)7

而且使用方法很簡單,直接在前方嵌套一個sum函數,輸入完公式後,同樣以三鍵結束。它會對offset函數公式求得的兩個值進行求和,便得到了我們需要的總銷量。

我們也可以再試試其他産品和其他時間段的條件下,銷量的求和,如下圖所示:

excel 中怎樣設置條件求和(Excel如何設置動态條件)8

綜上所述,通過嵌套的數組函數,可以比較方便地計算出指定多條件下的銷量總和。而動态列表框的設置,則可以更靈活地展示出不同條件下的銷量統計。

學會這樣的求和技巧,可以在數據統計分析中,交出一份不錯的答卷。

當然,如果還會跨表多條件銷量求和,或者通過動态圖表來顯示不同條件下的銷量信息,會更為加分。

這些技巧,在之後的課程中,也會逐步與童鞋們一起分享學習。

以上就是今天的全部内容,歡迎關注作者,在視頻中觀看視頻教程。

往期回顧:


Excel小技巧:一分鐘批量給中文上方添加拼音或快速轉換為拼音

Excel表格求和時怎麼把後面新插入的單元格數據自動計算進去

Excel表格怎麼通過公式提取單元格文本中的指定字符串

,

更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!

查看全部

相关生活资讯推荐

热门生活资讯推荐

网友关注

Copyright 2023-2024 - www.tftnews.com All Rights Reserved