 donghan donghan 當前離線
- 在線時間
- 582 小時
- 經驗
- 879
- 威望
- 0
- 性別
- 保密
- 最後登錄
- 2012-1-30
- 註冊時間
- 2009-4-17
- 閱讀許可權
- 70
- UID
- 773370
- 積分
- 2270
- 帖子
- 886
- 精華
- 1
- 評選資格
- False
4093財富
5鮮花
22技術
等級5EH鐵杆 EH鐵杆, 積分 2270, 距離下一級還需 930 積分
- 積分排行
- 403
- 帖子
- 886
- 精華
- 1
- 分享
- 0
串個門
加好友
打招呼
發消息
|
5樓 發表於 2010-3-9 09:36:43 |只看該作者 |(樓主)Excel伺服器2010軟體和教程下載
| 高級應用部分：一、在TEXT函數的條件格式中使用變數： 例題請按住F9 TEXT函數的條件格式公式樣式：text(數據源，"[條件1]顯示格式；[條件2]顯示格式；否則顯示格式；文本顯示格式") 條件格式可以全部或者部分使用變數，當部分使用變數時，條件和顯示格式的任何部分都可以使用變數，包括條件格式中的運算符號。 當使用變數時，不使用變數的部分用引號（"")引起來，然後用&符號與使用變數的部分連接起來，運算後形成條件格式的樣式。 變數可以是單元格引用，公式(包括數組公式）、函數結果。 根據需要，條件格式的每個部分或者全部可以省略不使用。 1、條件格式全部使用變數：見格式舉例部分，條件格式完全使用的是單元格引用。 2、在條件代碼中使用變數 公式樣式： =text(數據源,"[運算符號"&變數&"]格式代碼;[運算符號"&變數&"]格式代碼;格式代碼") 運算符號應是比較運算符：>、=、<、>=、<=、<>。 格式代碼是自定義單元格的代碼（顏色代碼除外）。 例子： 0.1561 隨機數<0.3 判定隨機數大小 0.4085 隨機數>0.3 > 運算符號用單元格引用 3、在格式代碼中使用變數 公式樣式： =text(數據源,"條件代碼"&變數&";條件代碼"&變數&";"&變數) 例子： 0.09322163 0.0932 0.00 0.0000 0.0 B13>0.5顯示兩位小數，B13<0.3顯示一位小數，其它則顯示四位小數 15 非月份數據 B20=10時出現錯誤 在格式代碼中使用變數時，要注意引用的變數中是否有不能顯示為原義字元的字元,如0。 可用公式： 非月份數據 當然可以在條件部分和格式部分同時使用變數，此處不在舉例，有興趣的可以根據上面的公式自己組合。 4、條件格式中使用函數公式 如：text()與REPT()聯合使用 例1： 123456789 10￥ 20￥ 30￥ 40￥ 50￥ 60￥ 70￥ 80￥ 90￥ A26=123456789,如何在C26顯示10￥ 20￥ 30￥ 40￥ 50￥ 60￥ 70￥ 80￥ 90￥ ，公式C26=SUBSTITUTE(TEXT(A26,REPT("0] ",9)),"]","0￥") 例2：將"*"號前後的數字統一補足4位數,不足4位的前面補"0" 原格式 公式 1*12*234*254 0001*0012*0234*0254 10*234*56*211 0010*0234*0056*0211 1*1*25*36 0001*0001*0025*0036 以上公式只是演示在條件格式中使用變數的方法，大家可以根據該方法靈活運用，解決實際問題。 例3：怎樣才能去掉每組數前面的0呢 原格式 公式 121 1,21 30523 3,5,23 4502 45,2 123 1,23 567030612 5,67,3,6,12 二、與IF函數比較 例題請按住F9 text的一般格式"正數;負數;零;文本"對於處理數據來說，最多可以有四部分，但第四部分是數據源是非數據時的顯示值,如： 正 10加[]判斷時的條件格式也最多只能加四個判斷條件格式，與IF函數對比如下： 1、當有一個的時候，如：text(A1，"[>條件1]顯示1")，相當於IF(A1>條件1,顯示1,A1)； 你好 你好 23.75102578 2、當有兩個時，如：text(A1，"[>條件1]顯示1；顯示2")相當於IF(A1>條件1,顯示1,顯示2) 你好 你好 text(A1，"[>條件1]顯示1;")相當於IF(A1>條件1,顯示1,"")； 你好 你好 3、當有三個時，如：text(A1，"[>條件1]顯示1；[>條件2]顯示2;顯示3)相當於IF(A1>條件1,顯示1,IF(A1>條件2,顯示2,顯示3)) text(A1，"[>條件1]顯示1；[>條件2]顯示2;顯示3)相當於IF(A1>條件1,顯示1,IF(A1>條件2,顯示2,顯示3)) 你好 你好 你好 你好 4、當有四個時，如：text(A1，"[>條件1]顯示1；[>條件2]顯示2;顯示3;顯示4)相當於IF(ISTEXT(A1),"顯示4",IF(A1>條件1,顯示1,IF(A1>條件2,顯示2,顯示3))) 優 優 text(A1，"[>條件1]顯示1；[>條件2]顯示2;顯示3;)相當於IF(ISTEXT(A1),"",IF(A1>條件1,顯示1,IF(A1>條件2,顯示2,顯示3))) 三、TEXT函數的第一個參數研究 1、第一個參數為一個單元格引用 這種情況是比較常見的，具體例子可以看格式舉例部分。 2、第一個參數為多單元格引用 日期 求出B57:B62區域中最小的年份、月份 1985-06-17 05:05:05 數組公式如下： 1906-07-19 06:06:06 1906年 1907-08-20 07:07:07 2月 1999-02-14 00:00:00 19060719 2010-03-09 14:23:38 8日 2008-08-08 00:00:00 3、第一個參數為函數、公式 !A:A !B:B !C:C !D:D 4、第一個參數為2緯數組 姓名 性別 籍貫 出生年月 工號 張三丰 男 北京 1970年8月 KT001 李四光 女 天津 1980年9月 KT002 王麻子 男 河北 1975年3月 KT003 工號 姓名 籍貫 用VLOOKUP聯合TEXT從右往左查詢 KT001 張三丰 北京 TEXT({1,-1},）類似if({1,-1},,)的功能 5、第一個參數為多緯數組 優 不及格 優 不及格 優 及格 不及格 及格 及格 四、錯誤處理： 例題請按住F9 TEXT函數只能簡單處理錯誤值，對於出錯的引用單元格本身不能在進行引用計算。 公式樣式： =TEXT(--ISERROR(A1),"[=1]顯示值1;顯示值2")或者=TEXT(--ISERROR(A1),"[=0]顯示值1;顯示值2") 顯示值里不能包含A1，否則A1為錯誤值時仍然出現錯誤值。 1、錯誤時顯示為錯誤值,否則顯示歡迎你，=TEXT(--ISERROR(K44),"[=1]錯誤值;歡迎你") 歡迎你 2、錯誤時顯示為空值,否則顯示另外單元格的值，=TEXT(--ISERROR(K44),"[=1]"""";[=0]"&E91+1) 46 3、錯誤時顯示為0,否則顯示另外單元格的值，==--TEXT(--ISERROR(K44),"[=1]!0;"&E91+1) 46 4、錯誤時顯示另外單元格的值，否則也顯示另外單元格的值，=TEXT(--ISERROR(K44),"[=1]"&D91+1&";"&E91+1) 46 5、錯誤時顯示為錯誤值，否則顯示原值,此時會出錯，=TEXT(--ISERROR(K44),"[=1]錯誤值;"&K44) 10 五、TEXT函數帶"0"、"."和"!"的解釋： 1）=TEXT(2,"[=2]我0;[=1]你;-1;")，此處0是佔位符，結果是：我2 我2 2）=TEXT(2,"[=2]我!0;[=1]你;-1;") ，此處0前有！，不是佔位符，被強制顯示，結果是：我0 我0 3）=TEXT(-222,"[=-222]3!00!00.00;[=1]"),此處第一和第三的0時被強制顯示，其它為佔位符， 302202.00 所以小數點前只有兩位佔位符，所以第二個0處顯示22，第四個0處顯示2，結果是：302202.00 4）TEXT(-2,"[=-2]我3!.0;[=1]"),此處小數點是被強制顯示的字元，即在小數點前各有一個佔位符0，結果是：我32.2 我32.2 5）=TEXT(-2,"[=-2]我3!0.0;[=1]")，此處第一個0被強制顯示，只有「.0"才是佔位符號，結果是：我302.0 我302.0 6)=TEXT(-22.22,"[=-22.22]3!0!.0;[=1]"),此處只有3後面的「0.」都是強制顯示，只有最後一個才是佔位符號， 30.22 實際上沒有小數點及小數部分的佔位符號，結果是：30.22 7）=TEXT(-22.666,"[=-22.666]3!0!.0.00;[=1]")，此處的後面的「0.0」才是佔位符號，結果是：30.22.67 30.22.67 7)=TEXT(0.69,"[>0.2]0.5")，此處0.是佔位符，0.69四捨五入為1.，結果是：1.5 1.5 解釋：在TEXT函數中，如果在"0"和"."前面沒有!(或者)，就是佔位符，否則就是被強制顯示的字元。只有有了小數點佔位符號（不是!.）， 小數點後面的小數部分才能被顯示，否則只顯示整數部分，並且沒有被顯示的部分會自動四捨五入。 關於"0"、"."和"!"的詳細解釋請參考補充說明部分。 六、TEXT函數的參數中帶「-」的解釋: 1、TEXT函數的參數帶一個"-"號的解釋，見以下語句： 1）=TEXT(-1,"-2;[=-1]-3")顯示-3 -3 -3 2）=TEXT(-1,"[=-1]-2;-3")顯示-2 -2 -2 3）=TEXT(-1,"[=1]-2;-3")顯示--3 --3 #VALUE! -3 4）=TEXT(-1,"-2;[=-1]-3")顯示-3 -3 -3 5)=TEXT(-1,"[=1]-2;[=2]-3;-4")顯示--4 --4 #VALUE! -4 6）TEXT(-1,"[=1]-2;[=-1]"&-3)顯示-3 -3 -3 7)=TEXT(-1,"[=1]-2;[=2]-3;"&-45)顯示--45 --45 #VALUE! -45 8)=TEXT(-1,"[=1]-2;[=-1]"&-3&";"&-4)顯示-3 -3 -3 9)=TEXT(-1,"[=1]-2;[=2]-3;-你好;歡迎")顯示--你好 --你好 #VALUE! 10)=TEXT("-1","-2;[=-1]-3")顯示-3 -3 -3 個人解釋：第一個參數的「-」作為運算符負號使用；如果第二個參數中[=第一參數]的條件出現，則第二個參數的「-」作為運算符負號使用； 如果第二個參數中[=第一參數]的條件沒有出現，則作為字元符號使用並且如果且TEXT的條件只有一部分時，顯示原值，有兩部分以上時，則多顯示一個運算符負號「-」。 2、TEXT函數的參數帶兩個"-"號的解釋，見以下語句： 1)=TEXT(--1,"[=1]-2;[=-1]-3;-4")顯示-2 -2 -2 2 2)=TEXT(--1,"[=1]--2;[=-1]--3;-4")顯示--2 --2 #VALUE! -2 3)=TEXT("--1","[=1]--2;[=-1]--3;-4")顯示--1 --1 #VALUE! -1 4)=TEXT(--1,"[=1]--2;[=-1]--3;-4")顯示--2 --2 #VALUE! -2 5)=TEXT(--1,"[=-1]-2;[=1]"&--3)顯示3 3 3 個人解釋：兩個「-」連用，如果「-」沒有在引號內，都作為運算符負號使用；如果在引號內，緊挨著數字的一個作為運算符負號使用，另一個「-」作為字元符號使用。 3、TEXT函數的兩個參數帶兩個以上"-"號連用，見以下語句： 1)=TEXT(---1,"[=1]--2;--3")顯示---3 ---3 #VALUE! #VALUE! -3 2)=TEXT(----1,"[=1]--2;--3")顯示--2 --2 #VALUE! -2 2 3)=TEXT(----1,"[=1]--2;--3")顯示--3 --3 #VALUE! -3 3 4)=TEXT(---6,"[=1]-2;--3")顯示-2 -2 -2 2 #VALUE! 5）TEXT(---6,"[=1]-2;--3;--4")顯示-6 -6 -6 6 #VALUE! 6）=TEXT(----1,"[=-1]--2;--3")顯示--3 --3 #VALUE! -3 3 7)=TEXT(---1,"[=1]--2;"&-3)顯示--3 --3 #VALUE! -3 8)=TEXT(----1,"[=-1]--2;"&---3)顯示-3 -3 -3 3 9)=TEXT("---1","[=1]-2;[=-1]--3;-4")顯示---1 ---1 #VALUE! #VALUE! -1 10)=TEXT("----1","[=1]--2;[=-1]--3;-4")顯示----1 ----1 #VALUE! #VALUE! #VALUE! -1 概括： 1）第一參數的「-」號如果多於1個且在引號內，則第一參數為字元。 2）第一參數的「-」號如果只有1個或者多於1個但沒在引號內，則第一參數為數值。 3）如果第一參數的計算結果為正數，第二個參數的「-「不在引號內,"-"全部為運算符符號使用。第二個參數的「-「在引號內時,緊挨著數字的一個「-」作為運算符負號使用。 其它「-」作為字元符號使用。 4）如果第一參數的計算結果為負數， 4-1)第二個參數的「-「不在引號內時:當「-」為單數時，則有1個作為字元使用，其它作為運算符負號使用，雙數時全部「-」作為運算符符號使用。 4-2)第二個參數的「-「在引號內，則緊挨著數字的一個「-」作為運算符負號使用，其它「-」作為字元符號使用；並且如果第二參數中[=第一參數]的條件沒有出現， 此時還要看TEXT的條件格式有幾部分：有兩部分且第二部分沒有條件時或者有三部分但第二部分有條件（=非第一參數）時，此時結果值前面還要多顯示一個「-」字元。 4、條件格式是區間，第一參數為負數 1）=TEXT(-3,"[<-4]-4;-5")顯示-5 -5 -5 5 2）=TEXT(-3,"[<3]4;-5")顯示-4 -4 -4 4 3）=TEXT(-3,"[>-4]-4;-5;-6")顯示--4 --4 #VALUE! -4 4）=TEXT(-3,"[<3]-4;-5")顯示--4 --4 #VALUE! -4 5)=TEXT(-3,"[<-4]-4;"&---5&";-6")顯示-5 -5 -5 5 6）=TEXT(-3,"[<-4]-4;[>4]--5;-6")顯示--6 --6 #VALUE! -6 對第二參數的個人解釋： 4-1）如果條件區間的範圍跨度包含正負數（第一部分[]不滿足條件且第二部分省略[]除外），就會多顯示一個負號；如果條件區間僅在負數範圍內，就不會多顯示負號。 4-3）如果第一部分[]不滿足條件且第二部分省略[]，此時，顯示第二部分的原數值。 4-2）數字前的「-」都作為字元使用（第一部分[]不滿足條件且第二部分省略[]除外）。 七、關於INDIRECT+TEXT(數字，"r0C00")此類格式的應用 1、解釋：TEXT(102,"r0c00") 函數的計算結果為"r1c02"，前面再加個indirect函數，相當於INDIRECT("r1c02",)，計算結果就是等於1行2列即B1的值。 故展開該函數： =INDIRECT(TEXT(1002,"r00c00"),)--->B10的值 =INDIRECT(TEXT(102,"r00c0"),)---->B10的值,注意前後的變化 =INDIRECT(TEXT(103,"r00c0"),)--->C100的值 =INDIRECT(TEXT(1003,"r00c0"),)--->C100的值 =INDIRECT(TEXT(901,"r00c0"),)--->A90的值 INDIRECT(TEXT({1681;1682;1683;1691;1692;1693;1701;1702;1703},"!r00c0"),)-------> 得到一個數組INDIRECT({"r168c1";"r168c2";"r168c3";"r169c1";"r169c2";"r169c3";"r170c1";"r170c2";"r170c3"},) 1 4 7 45 452 5 8 3 6 9 2、怎麼解決繁體版TEXT函數的R0C000用法問題？ 問題：為什麼在繁體版的Excel中使用INDIRECT+TEXT(數字，"R0C000")的解法不管用呢？怎樣解決？ 回答：繁體版的格式代碼與R、C有衝突，需要加強制佔位符！或，用TEXT(數字，"!R0!C000")或者TEXT(數字，"R0C000")可以解決。 indirect與TEXT聯合應用例子，將數字按S型排列： indirect函數的使用，請參考論壇chenjun版主的文章：函數的深入理解。 本文參考論壇文章：1、你未曾見過的TEXT函數用法2、TEXT 函數應用技巧 |
|
