麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] IF的困擾 [打印本頁]

作者: PJChen 時間: 2013-5-7 23:00 標題: IF的困擾

IF是一個我個人很常使用的函數,但往往用IF時,感覺公式都寫得很長,不知是否有替代函數?或者有省略的寫法?
例如以下公式,意思是當答案大於5時,顯示為5,否則就用原算式,
Q1249=IF(IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249)>5,5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))

作者: p212 時間: 2013-5-8 08:40

本帖最後由 p212 於 2013-5-8 08:43 編輯

回復 1# PJChen

Q1249=~~IF(~~IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249)~~>5,5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))~~

去除刪除線部份修改為 Q1249=IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249) 即可，請參考！

作者: p212 時間: 2013-5-8 09:11

回復 1# PJChen

Sorry! 未看清您的需求，上列(2#)的意見請忽略。

作者: ML089 時間: 2013-5-8 09:57

=--TEXT(5/IF(AND(N1249<N1250,N1250>0),N1248,N1248)*N1249,"[>5]5")

作者: PJChen 時間: 2013-5-8 11:57

回復 4# ML089

請教TEXT不是用在文字方面的函數,為何會使用它? "[>5]5")的意思又何解?
=--TEXT(5/IF(AND(N1249<N1250,N1250>0),N1248,N1248)*N1249,"[>5]5")

作者: ML089 時間: 2013-5-8 13:36

回復 5# PJChen

"[>5]5")的意思又何解?
大於5的數全部為5，其他不變

作者: p212 時間: 2013-5-8 16:13

本帖最後由 p212 於 2013-5-8 16:15 編輯

回復 6# ML089
Mr. ML089：您好！
"[>5]5"：表示大於5的數全部為5，其他不變。
上述概念之應用可從Excel的說明檔獲得詳細解說？
可否煩請指點迷津，提供非資訊背景出身之同好學習，謝謝！

作者: ML089 時間: 2013-5-8 18:39

回復 7# p212
回復 5# PJChen
"[>5]5"：表示大於5的數全部為5，其他不變。
上述概念之應用可從Excel的說明檔獲得詳細解說？

回覆:
Excel的說明檔沒有此說明，但可以參考儲存格格式設定中的 - 特殊 - 電話類有類似做法可以參考。
晚上回家我再找一些資料給大家參考

先參考儲存格格式設定中的 - 特殊
[attach]14924[/attach]

作者: ML089 時間: 2013-5-8 19:37

本帖最後由 ML089 於 2013-5-8 23:54 編輯

[條件值] 設置條件格式

顯示值自訂格式代碼原始值
2875 8965 [>99999999](0###) #### ####;#### #### 28758965
(021) 2345 9821 [>99999999](0###) #### ####;#### #### 2123459821
(0755) 2345 9821 [>99999999](0###) #### ####;#### #### 75523459821
99 [>100][紅色]0;[藍色]0 99
105 [>100][紅色]0;[藍色]0 105
123.40 [>100][綠色]#,##0.00;[<100][紅色]#,##0.00 123.4
87.00 [>100][綠色]#,##0.00;[<100][紅色]#,##0.00 87

EXCEL 表格貼上來有點亂，再補充圖形於下
使用TEXT函數格式文本時，其第二個參數的格式代碼，除了各個顏色代碼外，其他的與上表基本相同
[attach]14930[/attach]

作者: PJChen 時間: 2013-5-8 21:29

回復 9# ML089

感謝!!學到了以前所未接觸的,真太妙了!!

作者: ML089 時間: 2013-5-10 12:38

回復 1# PJChen
回復 2# p212

隨著每天心情不同，看法也會不同

上次用 TEXT ，現在看起來覺的用得不好，這是純數學比較問題卻用過濾方法來處理，數字轉文字再轉數字多少有精度問題，速度也影響很大

題目是計算值大於5就取5，用 MIN(5,計算值) 簡單的數學比較就能處理。

原式
Q1249
=IF(IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249)>5,5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))

用MIN()就能縮減
=MIN(5,IF(AND(N1249<N1250,N1250>0),5/N1250*N1249,5/N1248*N1249))

計算式結構修改也能簡短
=MIN(5,5/IF(AND(N1249<N1250,N1250>0),N1248,N1248)*N1249)

作者: Bodhidharma 時間: 2013-5-10 13:16

回復 12# ML089

嗯，用min是個好方法！

是說我最近也遇到類似的問題：
if(一長串算式=0,"",一長串算式)
或是
if((一長串算式=0)+(一長串算式>另一長串算式),"",一長串算式)
這個除了用定義名稱外，有省略的寫法嗎？

note:
一長串算式：INDEX('1020514全名冊'!A:A,MATCH(ROUNDUP((ROW()-2)/31,0)*3-2,'1020514全名冊'!A:A,0)+MOD(ROW()-3,31))
另一長串算式：ROUNDUP((ROW()-2)/31,0)*3

作者: Bodhidharma 時間: 2013-5-10 13:32

回復 13# Bodhidharma

if(一長串算式=0,"",一長串算式)
或是
if((一長串算式=0)+(一長串算式>另一長串算式),"",一長串算式)

稍微研究了一下，似乎可以用
text(一長串算式,"[=0] ")
以及
text(一長串算式,"[=0] ;[>另一長串算式] "
的方式處理，不過這理的空格是" "，與""有一點差距
請問text要如何回傳""呢？

作者: Bodhidharma 時間: 2013-5-10 13:49

回復 14# Bodhidharma

text的format_text語法似乎很好用，不過找不到教學文件…
text(value,[條件一]格式一;[條件二]格式二...)
似乎是用select case的邏輯在進行的，當符合第n個條件時，就用該格式，然後就跳出此函數
想請教如果要同時符合兩個條件，要如何設定？(用[條件一][條件二]似乎不行…)

作者: ML089 時間: 2013-5-10 16:10

回復 14# Bodhidharma
>if(一長串算式=0,"",一長串算式)
>或是
>if((一長串算式=0)+(一長串算式>另一長串算式),"",一長串算式)
>
>稍微研究了一下，似乎可以用
>text(一長串算式,"[=0] ")
>以及
>text(一長串算式,"[=0] ;[>另一長串算式] "
>的方式處理，不過這理的空格是" "，與""有一點差距
>請問text要如何回傳""呢？

可以用 TEXT(一長串算式,"[<>];;;")
0 回傳為 ""，其他依原值回傳，數字仍為文字格式，使用上仍需注意

作者: ML089 時間: 2013-5-10 16:20

回復 15# Bodhidharma
>text的format_text語法似乎很好用，不過找不到教學文件…
>text(value,[條件一]格式一;[條件二]格式二...)
>似乎是用select case的邏輯在進行的，當符合第n個條件時，就用該格式，然後就跳出此函數
>想請教如果要同時符合兩個條件，要如何設定？(用[條件一][條件二]似乎不行…)

TEXT 條件只能設定3個，2個可以指定，其他就是第3個
例如
=TEXT(A1,"[>10]!A;[>5]!B;!C")
大於10顯示為 "A"
5 - 10顯示為 "B"
其他顯示 "C"

作者: PJChen 時間: 2013-5-10 23:40

回復 17# ML089

我原想將新學到的式子套進Q2當中,但公式中太多if,就套不出來了,不知以下公式出現很多if時,是否可用其他函數?

Q2=IF($G2="","",ROUND(IF(IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))>5,5,IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))),3))

[attach]14947[/attach]

作者: ML089 時間: 2013-5-11 00:09

回復 18# PJChen

=IF($G2="","",ROUND(MIN(5,IF(E2=0,0,IF(F2<=$AA$2,5/$Z$2*N2,IF(AND(F2>$AA$2,N1=0,N2>$X$2,N2<N3,N3>0),5/N3*N2,IF(AND(F2>$AA$2,N1>0,N2>$X$2,N2>N3,N3=0),5/N1*N2,5))))),3))

複製代碼

作者: PJChen 時間: 2013-5-11 00:35

回復 19# ML089

新的式子確實比原式簡短,函數說明中只標示了:MIN(number1, [number2], ...),
不知在新的式子中,min的用法解釋為何?
有無比較簡易的參考例子可幫忙我了解?
Tks.

作者: PJChen 時間: 2013-5-11 01:07

回復 19# ML089

min + if 的用法,以下是我自己的理解,錯了請指正.. TKS.

...MIN(5,IF(E2=0,0,IF(..,IF(..,IF(..))))),3))
解: 最小為5,否則 IF(E2=0,0,IF(..,IF(..,IF(..))))

作者: Bodhidharma 時間: 2013-5-11 01:16

回復 21# PJChen

呃…min(number1,number2,...numberN)：回傳number1至numberN之中最小的值
所以min(5,一長串公式)就會回傳5和"一長串公式"之中最小的值，也就是一長串公式比5小的話就回傳一長串公式，比5大的話就回傳5

作者: PJChen 時間: 2013-5-11 01:22

回復 22# Bodhidharma

了解!! min + if 的配合,真的比單獨用if 簡潔又好用!! 謝謝.

作者: Bodhidharma 時間: 2013-5-11 03:06

回復 7# p212

在excelhome找到一篇相當經典的text函數介紹文章
http://club.excelhome.net/forum.php?mod=viewthread&tid=331382
不過該篇有點深，可能得靜下心來好好研究
(話說我自己也在研究中…)

作者: ML089 時間: 2013-5-11 08:08

回復 21# PJChen

一般可以用比較法來加深 MIN(5, 公式)
公式的值為 1時，MIN(5, 1) = 1
公式的值為 1時，MIN(5, 2) = 2
公式的值為 1時，MIN(5, 3) = 3
公式的值為 1時，MIN(5, 4) = 4
公式的值為 1時，MIN(5, 5) = 5
公式的值為 1時，MIN(5, 6) = 5
公式的值為 1時，MIN(5, 7) = 5
...
這跟公式有多少 IF(...,IF(... 無關，

作者: Scott090 時間: 2013-5-11 09:36

利用儲存格式，真太妙了；學習,感謝

作者: PJChen 時間: 2013-5-11 13:33

回復 25# ML089

Noted, thanks.

作者: pigchen 時間: 2013-5-24 09:41

又學到一招,謝謝大大分享

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)