1

## U2儲存格函數如下，I2為動態數字 =IF(I2<=-4000,"1", IF(I2<=-2000,"2", IF(I2<=-1000,"3", IF(I2>=4000,"4", IF(I2>=2000,"5", IF(I2>=1000,"6", IF(AND(I2>=500,I2<=-500),"","000")))))))

### 2 個回答

0
paicheng0111
iT邦研究生 1 級 ‧ 2019-03-24 09:51:49

#### revised on 2019-03-24 11:36

``````function udf_myCompare(myNum as double) as variant
select case myNum
case is <= -4000
udf_myCompare = 1
case is <= -2000
udf_myCompare = 2
case is <= -1000
udf_myCompare = 3
case is >= 4000
udf_myCompare = 4
case is >= 2000
udf_myCompare = 5
case is >= 1000
udf_myCompare = 6
case else
udf_myCompare = "'000"
end select
end function
``````

`U2`寫下`=udf_myCompare(i2)`即可。

(想一想, 好像少了變數名稱)

paicheng0111 iT邦研究生 1 級 ‧ 2019-03-24 11:25:31 檢舉

goodnight iT邦研究生 4 級 ‧ 2019-03-29 22:26:01 檢舉

0

iT邦超人 1 級 ‧ 2019-03-24 10:43:26
``````Sub Macro1()
If Range("I2") <= -4000 Then
Range("U2") = "1"
ElseIf Range("I2") <= -2000 Then
Range("U2") = "2"
ElseIf Range("I2") <= -1000 Then
Range("U2") = "3"
ElseIf Range("I2") >= 4000 Then
Range("U2") = "4"
ElseIf Range("I2") >= 2000 Then
Range("U2") = "5"
ElseIf Range("I2") >= 1000 Then
Range("U2") = "6"
Else
Range("U2") = "'000"
End If
End Sub
``````
erwinho iT邦新手 5 級 ‧ 2019-03-25 14:36:00 檢舉

Excel2016版以後的ifs，用法比if簡潔多了，跟寫select很像。