2014年10月14日 星期二

Excel-找出消費金額大於某一數值者(名稱重覆者僅列第一個)(SUMIF,COUNTIF)

網友想要在一個客戶消費清單的 Excel 資料表中,找出消費金額大於某個數值者,因為客戶有重覆消費,所以金額要累計。如何能快速找出合於上述規則的客戶呢?

假設金額定為 2000 元,以下圖為例:其中第 4, 13, 14, 19 列為一次消費金額大於2000元者,第 3, 5, 10, 21 列,則為多次消費金額大於 2000 元者。

【問題解決】

儲存格C2:

=IF((COUNTIF($A$2:A2,A2)=1)*(SUMIF($A$2:$A$23,A2,$B$2:$B$23)>=2000),"V","")

(COUNTIF($A$2:A2,A2)=1):條件一,找出由第一個儲存格A2開始,且目前為止,只出現一次的名字。

SUMIF($A$2:$A$23,A2,$B$2:$B$23)>=2000:條件二,找出和儲存格A2相同內容而金額總計超過 2000 者。

IF((條件一)*(條件二),"V",""):公式中的「*」運算子,乃為執行邏輯 AND 運算,如果條件成立,則輸出「V」,否則輸出空白。

複製存格C2,往下各列貼上。

接著使用「自動篩選」功能,挑出C欄中為「V」,複製之後即可再利用。

沒有留言:

張貼留言

好康東東