_Excel公式教程 (2016-01-28 21:41:37)

轉載

▼

标簽： excel公式教程 單元格格式代碼分類： Excel公式教程-原理篇

在自定義格式代碼中最多可以指定四個部分，依次定義正數、負數、零和文本的格式；各部分之間用分号隔開。

當隻指定了一個部分，如果用符号@指定為文本格式，則單元格的内容将全部轉換為文本值；如果沒有用符号@指定為文本格式，則該部分同時定義正數、負數和零的格式，文本使用常規格式。

當隻指定了兩個部分，如果第二部分用符号@指定為文本格式，則第一部分同時定義正數、負數和零的格式；如果第二部分沒有用符号@指定為文本格式，則第一部分定義正數和零的格式，第二部分定義負數的格式，文本使用常規格式。

當隻指定了三個部分，如果第三部分用符号@指定為文本格式，則第一部分同時定義正數和零的格式，第二部分定義負數的格式；如果第三部分沒有用符号@指定為文本格式，則三個部分依次定義正數、負數和零的格式，文本使用常規格式。

文本格式必須是格式代碼的最後一部分。

如果格式代碼某部分為空格式，則單元格不顯示該部分對應的值。例如：

格式“;;;”，三個分号使四個部分都是空格式，該格式隐藏單元格的值，但不能隐藏錯誤值。

格式“G/通用格式;-G/通用格式;G/通用格式;”，最後一部分為空格式，隐藏文本值不顯示。

格式“G/通用格式;;;”，後面三個部分都是空格式，僅顯示正的數字值。

格式“;-G/通用格式;;”，僅顯示負的數字值。

格式字符不區分字母大小寫（但科學記數形式裡的E必須用大寫）。詳述如下：

【1】G/通用格式 就是常規格式。

【2】. 小數點符。在數字部分中，如果沒有小數點符，數字将四舍五入為整數顯示；如果有多個小數點符，則以第一個為小數點定位符，其餘的視作在小數部分插入的字符。例如：

格式“0”為整數格式，輸入56.7顯示“57”。

格式“0.000.000”的第一個小數點符為小數點定位符，輸入123.45678顯示“123.456.780”。

【3】#、0、? 數字占位符。

（1）如果數字小數點右側的位數大于所設定格式中占位符的位數，則該數字将按占位符位數進行四舍五入；如果數字小數點左側的位數大于占位符的位數，那麼超出的位數也會顯示出來。

（2）如果數字小數點兩側的位數小于占位符的位數，占位符#不顯示無意義的零，而占位符0顯示無意義的零，占位符?則以空格代替無意義的零。

（3）如果所設定的格式中小數點左側全部是#，那麼小于1的數字将以小數點開始。如果格式中小數點右側是#，則小數點可能不對齊。

如果單元格的值為0，設置格式“0”顯示“0”；設置格式“#”或“?”顯示空白，相當于在“選項”對話框“視圖”選項卡中設置了零值不顯示。

格式“0.00”為兩位小數格式，輸入45.678顯示“45.68”，輸入345.6顯示“345.60”。

格式“0-00-00”把8位數字以日期形式顯示，輸入20031225顯示“2003-12-25”。

【4】, 千位分隔符或以一千為單位表示數字的數量級。例如：

格式“#,##0”為帶千位分隔符的整數格式，輸入1234.5顯示“1,235”。

格式“#,##0”為帶千位分隔符的整數格式，輸入1234.5顯示“1,235”，輸入1234567顯示“1,234,567”。

格式“#,##0.00”為帶千位分隔符的兩位小數格式，輸入1234.5顯示“1,234.50”。

格式“0,,百萬”以百萬為單位保留整數，輸入5678900顯示“6百萬”。

【5】% 百分号，相當于把數字除以100，并在後面添加百分号。例如：

格式“0.00%”為兩位小數的百分比格式，輸入70.2顯示“70.20%”。

【6】分數形式。例如：

格式“# ?/?”，輸入1.5顯示“1 1/2”。

格式“# ?/4”，輸入.5顯示“2/4”。可以任意指定分母數字。

也可在單元格直接以分數形式輸入，例如輸入“0 1/2”，即相當于輸入0.5并自動設置成分數格式。

【7】E 、E- 科學記數形式。E必須是大寫。例如：

格式“0.00E 00”，輸入.5顯示“5.00E-01”，輸入123456789顯示“1.23E 08”。

格式“0.00E-00”，輸入.5顯示“5.00E-01”，輸入123456789顯示“1.23E08”。

【8】貨币格式。例如：

格式“￥#,##0.00;￥-#,##0.00”為人民币格式，輸入12345.67顯示“￥12,345.67”。

格式“$#,##0.00;$-#,##0.00”，輸入12345.67顯示“$12,345.67”。

格式“"US$"#,##0.00;-"US$"#,##0.00”為美元格式，輸入12345.67顯示“US$12,345.67”。

格式“[$HKD] #,##0.00;[$HKD] -#,##0.00”為港币格式，輸入12345.67顯示“HKD 12,345.67”。

格式“[$HK$-C04]#,##0.00;-[$HK$-C04]#,##0.00”為港币格式，輸入12345.67顯示“HK$12,345.67”。

貨币格式有很多種形式，詳見單元格格式對話框、貨币格式中“貨币符号（國家/地區）”下拉菜單。

【9】_ 留下一個和下一個字符同等寬度的空格。例如設置格式“0.00_);(0.00)”可使正數和括号内的負數對齊。

【10】@ 顯示輸入的文本。隻能在格式代碼的最後一部分使用@。如果格式代碼隻有一個部分且使用了@，則輸入的值都将保存為文本。使用多個@可重複顯示輸入的内容。例如：

格式“@”，就是文本格式，單元格設置該格式後，輸入的所有内容（包括公式）将存儲為文本。而設置格式前所輸入的内容并不改變。設置文本格式後，單元格自動默認左對齊。

格式“@-@”，輸入“溫州”，顯示“溫州-溫州”。

【11】"" 顯示雙引号内的字符。

在格式中可以直接輸入格式代碼以外的字符而無需使用引号，例如：

格式“0萬”，輸入“12”，顯示“12萬”。

格式“0!.0,萬”以萬為單位保留1位小數，輸入125670顯示“12.6萬”。

格式“0!.0000萬”以萬為單位保留4位小數，輸入12567顯示“1.2567萬”。

要把單元格設置為以萬為單位保留兩位小數，可設置格式“0.00,,萬%”，在%前按住Alt，用小鍵盤輸入10（即相當于輸入了一個換行符），并把單元格設置為自動換行。

格式“0!.00,,億”以億為單位保留兩位小數，輸入18653471415顯示“186.53億”。

如果想單元格顯示格式符号，可以用括号括住。例如：

格式“0"mm"”，輸入“23”，顯示“23mm”。

【12】\ 顯示下一個字符及其後的字符，直到遇到格式符号為止。例如：

格式“0\ml”，輸入“23”，顯示“23ml”。

【13】! 顯示下一字符。一般用于顯示1個格式符号。例如：

格式“!"0!ml!"”，輸入“123”，顯示“"123ml"”。

【14】* 重複下一字符，使單元格内容充滿列寬。

格式“**;**;**;**”模仿密碼保護，輸入任何值都顯示“********”，可惜在輸入過程中還是顯示的。

格式“*- 0”模仿目錄中頁碼之前、與标題之間的分隔符，輸入“2”，顯示“------ 2”。

格式“@ *-”模仿目錄中标題之後、與頁碼之間的分隔符，輸入“緒論”，顯示“緒論 ---”。

【15】日期格式。

（1）y 年份。

y或yy 顯示兩位年份。

yyy或yyyy 顯示四位年份。

（2）e 顯示四位年份。E也是科學計數形式的代碼符。

（3）b 佛曆BE（Buddist Era）年份，即以公元前543年為紀年元年，對1900年以後的日期有效。

b或bb 顯示兩位佛曆年份。

bbb或bbbb 顯示四位佛曆年份。

（4）b1、b2 以公曆、回曆解析其後日期格式的y、m、d。

（5）m 月份。M在時間格式中代表分鐘。

m 顯示月份的阿拉伯數字形式：1～12。

mm 顯示月份的兩位阿拉伯數字形式：01～12。

mmm 顯示月份的英文簡寫形式：Jan～Dec。

mmmm 顯示月份的英文形式：January～December。

mmmmm 顯示月份的第一個英文字母：J～D。

（6）d、a 日期或星期。

d 顯示日期的阿拉伯數字形式：1～31。

dd 顯示日期的兩位阿拉伯數字形式：01～31。

ddd 顯示星期的英文簡寫形式：Mon～Sun。

dddd 顯示星期的英文形式：Monday～Sunday。

aaa 顯示星期的中文簡寫形式：一～日。

aaaa 顯示星期的中文形式：星期一～星期日。

（7）-、/ 日期分隔符。可在控制面闆的“區域和語言選項”裡設置默認的日期分隔符。

（8）g 日本年号。明治年間為1868～1911年；大正年間為1912～1925年；昭和年間為1926～1988年；1989年之後是平成。

g 顯示大寫字母的日本年号：M、T、S、H。

gg 顯示漢字簡稱的日本年号：明、大、昭、平。

ggg 顯示漢字的日本年号：明治、大正、昭和、平成。

例如，單元格輸入“2015-8-20”，

設置格式“e年mm月dd日（aaa）”，顯示“2015年08月20日(四)”。

設置格式“e年”“yyy年”或“yyyy年”，顯示“2015年”。

設置格式“y年”或“yy年”，顯示“15年”。

設置格式“佛曆bbb年”或“佛曆bbbb年”，顯示“佛曆2558年”。BE=AD 543。

設置格式“b2回曆yyy年m月d日”，顯示“回曆1436年11月6日”。

設置格式“m月”，顯示“8月”。

設置格式“d”，顯示“20”。

設置格式“aaaa”，顯示“星期四”。

設置格式“e"年1月-"e年m月”，顯示“2015年1月-2015年8月”。

設置格式“[$-411]ggg e年m月d日”，顯示“平成 27年8月20日”。

【16】時間格式。

（1）h 小時。

h 顯示0～23。

hh 顯示00～23。

（2）m 分鐘。

m 顯示0～59。

mm 顯示00～59。

（3）s 秒。

s 顯示0～59。

ss 顯示00～59。

（4）: 時間分隔符。可在控制面闆的“區域和語言選項”裡設置默認的時間分隔符。

（5）[h][m][s] 小時、分鐘、秒的發生數。

例如，單元格輸入“16:3:36”，

設置格式“h:mm:ss”，顯示“16:03:36”。

設置格式“h時m分s秒”，顯示“16時3分36秒”。

設置格式“[$-409]h:mm am/pm”或“[$-409]h:mm a/p”，顯示“4:03 PM”。

設置格式“[$-804]h:mm 上午/下午”，顯示“4:03 下午”。

設置格式“上午/下午h時m分s秒”，顯示“下午4時3分36秒”。

又如，單元格輸入：

="16:50:36"-"12:10:16"

設置格式“[h]小時”，顯示“4小時”，注意是截尾舍入。

設置格式“[h]小時m分”，顯示“4小時40分”，注意m不能再加“[]”。

設置格式“[m]分種”，顯示“280分種”。

另外，單元格可以輸入并顯示百分之一秒，而編輯欄隻能四舍五入顯示到秒。例如：

設置格式“h:mm:ss.00”，輸入16:03:36.75，單元格顯示“16:03:36.75”，編輯欄隻顯示“16:03:37”。

【17】[顔色] 可用顔色名稱[黑色][白色][紅色][藍色][黃色][綠色][洋紅][藍綠色]設置八種顔色，或用[顔色1]-[顔色56]調用調色闆上的顔色。

設置格式“[紅色]0;[綠色]0”，輸入正數顯示為紅色字體，輸入負數顯示為綠色字體。

【18】[條件] 可以在格式代碼中設置簡單的條件判斷。

1、條件由比較運算符、數字常量構成，例如：[>=60]。

2、條件隻比較數字，不比較文本。如果比較運算符後面是文本、引用，或不指定比較對象，一律視作數字0。例如：[<>A]、[<>A1]、[<>]都相當于[<>0]。

3、格式代碼中最多可以設置兩個條件，但格式代碼可包含四個部分：

[條件1]數字格式1;[條件2]數字格式2;數字格式3;文本格式

設置格式“[>=60]合格”，輸入大于等于60的數字顯示“合格”；輸入小于60的數字或文本值，則使用常規格式顯示。

設置格式“[>=60]合格;;不合格;缺考”，輸入大于等于60的數字顯示“合格”；輸入負數則不顯示（使用第二部分的空格式）；輸入小于60的數字顯示“不合格”；輸入文本“沒考試”則顯示“缺考”。

設置格式“[紅色][>=85]A;[洋紅][>=60]!B;[藍色]C;[綠色]!D”，輸入大于等于85的數字，則顯示紅色的A；輸入小于85且大于等于60的數字，則顯示洋紅色的B；輸入小于60的數字，則顯示藍色的C；輸入文本則顯示綠色的D。

【19】[DBNum] 中文數字或全角數字。

例如，單元格輸入“105000.8”，

設置格式“[DBNum1]”顯示中文小寫數字“一十萬五千.八”；

設置格式“[DBNum1]0.00”顯示中文小寫數字“一○五○○○.八○”；

設置格式“[DBNum2]”顯示中文大寫數字“壹拾萬伍仟.捌”；

設置格式“[DBNum2]0.00”顯示中文大寫數字“壹零伍零零零.捌零”；

設置格式“[DBNum3]”顯示全角數字“１十萬５千.８”；

設置格式“[DBNum3]0.00”顯示全角數字“１０５０００.８０”。

又如，單元格輸入日期“2003-02-15”，

設置格式“[DBNum1]e年m月d日”顯示中文小寫日期“二○○三年二月十五日”。

設置格式“[DBNum2]e年mm月dd日”顯示中文大寫日期“貳零零叁年零貳月壹拾伍日”。

【20】[$] 設置數字、貨币、日期和時間格式所屬的國家/地區。比如：[$-804]代表中國，[$-409]代表美國，[$-809]代表英國，等等。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!