可成 發問時間: 電腦與網際網路軟體 · 7 年前

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,"量大","(量縮)")))))))))

已更新項目:

感謝kk大師

請將意見轉到回答

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,"量大","(量縮)")))))))))

還有問題?馬上發問,尋求解答。