# excel 函數 要如何縮小

excel 函數 要如何縮小

=IF(COUNTIF(F2490:F2722,">"&F2723)=1,"天量",IF(COUNTIF(F2490:F2722,">"&F2723)<6,"爆巨量",IF(COUNTIF(F2490:F2722,">"&F2723)<24,"爆大量",IF(COUNTIF(F2490:F2722,">"&F2723)=233,"窒息量",IF(COUNTIF(F2490:F2722,">"&F2723)>218,"凋零量",IF(COUNTIF(F2490:F2722,">"&F2723)>200,"萎縮量",IF(AND(E2723>B2723,F2723>MAX(F2720:F2722)),"買點",IF(F2723>MAX(F2720:F2722),">3天",IF(F2723>F2722,"量大","(量縮)")))))))))

### 3 個解答

• KK
Lv 7
7 年前
最佳解答

=CHOOSE(MATCH(COUNTIF(F2490:F2722,">"&F2723),{1,2,6,24,201,219,233}),"天量","爆巨量","爆大量",

2013-11-16 21:20:02 補充：

接上段

CHOOSE(1+(E2723>B2723)+(F2723>MAX(F2720:F2722))*2,"(量縮)","量大",">3天","買點"),"萎縮量","凋零量","窒息量")

2013-11-17 21:04:00 補充：

我把公式分成4段比較容易看，使用時注意自行接成一列。

一般這類問題用 LOOKUP 即可決解，

但貴題在 COUNTIF(F2490:F2722,">"&F2723) 其值在 201~218 之間時又需要不同判斷(見第3行CHOOSE)，因此使用雙 CHOOSE 來決解。

增加 COUNTIF(F2490:F2722,">"&F2723) = 0 的判斷。

=CHOOSE(MATCH(COUNTIF(F2490:F2722,">"&F2723),{0,1,2,6,24,201,219,233})

,"","天量","爆巨量","爆大量"

,CHOOSE(1+(E2723>B2723)+(F2723>MAX(F2720:F2722))*2,"(量縮)","量大",">3天","買點")

,"萎縮量","凋零量","窒息量")

• Daniel
Lv 7
7 年前

應是受7層IF的限制，改為:

=IF(COUNTIF(F2490:F2722,">"&F2723)*OR(COUNTIF(F2490:F2722,">"&F2723)<24,COUNTIF(F2490:F2722,">"&F2723)>200),LOOKUP(COUNTIF(F2490:F2722,">"&F2723),{1,2,6,201,219,233},

2013-11-16 15:14:09 補充：

接上段

{"天","爆巨","爆大","萎縮","凋零","窒息"})&"量",IF(AND(E2723>B2723,F2723>MAX(F2720:F2722)),"買點",IF(F2723>MAX(F2720:F2722),">3天",IF(F2723>F2722,"量大","(量縮)"))))

2013-11-16 15:19:22 補充：

建議設定名稱，先將游標停在此格，按 [插入] - [名稱] - [定義]，設定名稱假設為 [成交量]，參照到:

=COUNTIF(F2490:F2722,">"&F2723)

2013-11-16 15:22:28 補充：

公式可改為:

=IF(成交量*OR(成交量<24,成交量>200),LOOKUP(成交量,{1,2,6,201,219,233},{"天","爆巨","爆大","萎縮","凋零","窒息"})&"量",

2013-11-16 15:22:58 補充：

接上段

IF(AND(E2723>B2723,F2723>MAX(F2720:F2722)),"買點",IF(F2723>MAX(F2720:F2722),">3天",IF(F2723>F2722,"量大","(量縮)"))))

2013-11-17 14:19:30 補充：

K大，好像沒有判斷到F2723>F2722的部份

2013-11-17 14:58:36 補充：

如果F2723>F2722相當於E2723>B2723的話，則可用006~7的公式，否則可改為:

=CHOOSE(MATCH(COUNTIF(F2490:F2722,">"&F2723),{1,2,6,24,201,219,233}),"天量","爆巨量","爆大量",

2013-11-17 14:59:08 補充：

接上段

IF(F2723>MAX(F2720:F2722),IF(E2723>B2723,"買點",">3天"),IF(F2723>F2722,"量大","(量縮)")),"萎縮量","凋零量","窒息量")

2013-11-17 20:06:03 補充：

不知會不會有 COUNTIF(F2490:F2722,">"&F2723)=0 的情況，若有的話要顯示什麼? 不然會形成#N/A的錯誤值

• 7 年前

看不懂是什麼? 如果不要打那麼多.將工作表作一副本.跟原工作表資料相同.將COUNTIF(F2490:F2722,">"&F2723) 在副本計算出量 如a1.a2.a3...

原工作表就變成=IF(副本!A1=1,"天量",IF(副本!A2<6,"爆巨量",IF(副本!A3<24,"爆大量",IF(副本!A4=233,"窒息量",IF(副本!A5>218,"凋零量",IF(副本!A6>200,"萎縮量",IF(AND(E2723>B2723,F2723>MAX(F2720:F2722)),"買點",IF(F2723>MAX(F2720:F2722),">3天",IF(F2723>F2722,"量大","(量縮)")))))))))