2014年3月10日 星期一

Excel-將銷售額依區間計算總額

有網友問到:根據一個銷售額清單的 Excel 資料表,如何根據金額區間來計算總額?參考下圖,以每 500 為區間來計算總額。

【準備工作】

選取儲存格B2:B26,按一下 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:銷售額。

【輸入公式】

儲存格E2:

=SUMPRODUCT((銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500)*銷售額)

(銷售額>(ROW(1:1)-1)*500):當往下複製儲存格時,ROW(1:1)=1 會變為 ROW(2:2)=2、ROW(3:3)=3、…。計算結果會依序產生 0、500、1000、1500、2000、…。

本例運算結果產生陣列 {True,True,True,True,True,True,True,True,True,True,True,…}。

(銷售額<=ROW(1:1)*500):當往下複製儲存格時,計算結果會依序產生 500、1000、1500、2000、2500、…。

本例運算結果產生陣列 {False,False,False,False,False,True,False,False,False,False,True,…}。

(銷售額>(ROW(1:1)-1)*500)*(銷售額<=ROW(1:1)*500):其中的「*」相當於執行 AND 運算,因此只有第 6、11、… 的結果為 True

複製儲存格E2,貼至儲存格 E2:E6。

沒有留言:

張貼留言

好康東東