如何設(shè)置自定義數(shù)字格式代碼.docx

如何設(shè)置自定義數(shù)字格式代碼Excel提供了大量樣式的內(nèi)部數(shù)字格式，但仍然不能滿(mǎn)足部分人的需要，于是我們就需要使用自定義數(shù)字格式來(lái)處理問(wèn)題。1.創(chuàng)建自定義格式例如：?jiǎn)卧裰袃?nèi)容顯示為“張三”，要求將“張三”顯示為“李四”。操作步驟：選取”張三”的單元格，打開(kāi)【單元格格式】對(duì)話(huà)框，選取【數(shù)字】選項(xiàng)卡，在【分類(lèi)】列表框中選取【自定義】選項(xiàng)，在右側(cè)的【類(lèi)別】框中輸入“;“李四”。2.自定義格式代碼基本結(jié)構(gòu)人們可以指定一系列代碼作為數(shù)字格式來(lái)創(chuàng)建自定義格式，如上面的例題，“;“李四”就是一組代碼。代碼分為四部分，中間用“;”號(hào)分隔，具體如下：正數(shù)格式；負(fù)數(shù)格式；零格式；文本格式3.自定義格式常用的代碼代碼是設(shè)置自定義格式必不可少的部分，主要有數(shù)字和文本代碼，以及日期和時(shí)間代碼兩種。1）數(shù)字和文本代碼“G/通用格式”：以常規(guī)的數(shù)字顯示，相當(dāng)于【分類(lèi)】列表中的【常規(guī)】選項(xiàng)。如輸入10.001就顯示10.001.“0”：數(shù)字占位符。如果單元格的內(nèi)容大于占位符，則顯示實(shí)際數(shù)字，如果小于占位符，則用0補(bǔ)位。如代碼“000”時(shí)，12則顯示為012.“#”：數(shù)字占位符。只顯示有意義的零而不顯示無(wú)意義的零。如代碼為“#”顯示12，而不是顯示012，此處12前的0即為無(wú)意義的零。小數(shù)點(diǎn)后數(shù)字大于“#”的數(shù)量，則按“#”的位數(shù)四舍五入。“？”：數(shù)字占位符。在小數(shù)點(diǎn)兩邊為無(wú)意義的零添加空格，以便當(dāng)按固定寬度是小數(shù)點(diǎn)可對(duì)齊?！埃保呵环指罘?hào)。數(shù)字使用千位分割符號(hào)。如果在代碼中“，”后，則把原來(lái)的數(shù)字縮小1000倍。如設(shè)置代碼：“0，”，1000則顯示為1.“文本”：顯示雙引號(hào)中的文本。顏色：用指定的顏色顯示字符。有八種顏色可選，紅色、黑色、黃色、綠色、白色、藍(lán)色、青色和洋紅。條件：可以先判斷單元格內(nèi)容，然后再設(shè)置格式。條件格式化只限于使用三個(gè)條件，其中兩個(gè)條件是明確的，另一個(gè)是除了這兩個(gè)條件外的其他情況，條件要放到方括號(hào)中。如代碼設(shè)置：“100”“超額完成”；=100“基本完成”；“未完成”,意義為大于100顯示“超額完成”，等于100顯示為“基本完成”，其他的顯示為“未完成”。2）日期和時(shí)間代碼下面是常用的日期和時(shí)間代碼?！癥YYY”或“YY”：按四位（19009999）或兩位（0099）顯示年?！癕M”或“M”：以?xún)晌唬?112）或（112）顯示月“DD”或“D”：以?xún)晌唬?131）或一位（131）來(lái)顯示天“H”或“HH”：以一位（023）或兩位（0123）來(lái)顯示小時(shí)“M”或“MM”：以一位（059）或（0159）顯示分鐘“S”或“SS”： 以一位（059）或（0159）顯示秒。4.數(shù)字自定義格式綜合應(yīng)用實(shí)例1）設(shè)置萬(wàn)元顯示要求：把“12345”顯示為“1.2”代碼：“0”.“#，”代碼說(shuō)明：由于是萬(wàn)元顯示，所以小數(shù)點(diǎn)要向前移動(dòng)四位，這里用“，”向前移三位，用“#”占一位，總共是四位。把“.”插入到一個(gè)小數(shù)的倒數(shù)第五位，然后用“0”確?！?”插入的是數(shù)字中間，完成了萬(wàn)元顯示。注意：用“#”.“#”也可以完成萬(wàn)元顯示，但當(dāng)數(shù)字小于1萬(wàn)時(shí)，小數(shù)點(diǎn)前沒(méi)有顯示零，如5000，顯示為“.5”。用途：在上報(bào)的財(cái)務(wù)報(bào)表中，有報(bào)送的對(duì)象只須知道大概總數(shù)，而不必知道某個(gè)數(shù)字是幾元幾角時(shí)，這時(shí)把報(bào)表做成萬(wàn)元顯示是一個(gè)理想選擇。2）小數(shù)位對(duì)齊要求；將一組數(shù)字對(duì)齊。代碼：“？.？”代碼說(shuō)明：“.？”可以在小數(shù)點(diǎn)后對(duì)無(wú)意義的零添加空格，在某一列使用自定義格式，小數(shù)點(diǎn)后無(wú)論幾位，都會(huì)用空格補(bǔ)充，以達(dá)到小數(shù)點(diǎn)對(duì)齊的效果。用途：在工作表中對(duì)齊某列無(wú)規(guī)則小數(shù)位數(shù)。3）設(shè)置文本型代碼要求：“1”顯示為“000001”。代碼：“000000”代碼說(shuō)明：“0”是數(shù)字占位符，當(dāng)數(shù)字的實(shí)際位數(shù)小于“0”的位數(shù)時(shí)，會(huì)顯示這些無(wú)意義的零。用途：在商品管理中，一般都要設(shè)置物品代碼，而這些物品代碼可以通過(guò)設(shè)置文本格式后再來(lái)輸入代碼來(lái)實(shí)現(xiàn)，但無(wú)意義的零還是要手工輸入。用零作為占位符的自定義數(shù)字代碼設(shè)置數(shù)字格式，則可以讓單元格自動(dòng)添零。3）數(shù)字串顯示為日期格式要求：把“20090811”設(shè)置為“2008-08-11”。代碼：“0-00-00”代碼說(shuō)明：0是數(shù)字占位符，“0-00-00”中的最后兩個(gè)零是為11占位，中間的兩個(gè)零為08占位，第一個(gè)0是為前面的2009占位。用途：在很多專(zhuān)業(yè)軟件導(dǎo)出的日期為YYYYMMDD格式，而在Excel中則不是標(biāo)準(zhǔn)的日期格式，這時(shí)用插入“-”的方法可顯示為標(biāo)準(zhǔn)的日期格式。4）帶單位符號(hào)顯示的數(shù)字計(jì)算要求：在“數(shù)量”列顯示的數(shù)量后加單位“條”；在“進(jìn)價(jià)”列的進(jìn)價(jià)后顯示單位“元/條”；在“金額”列能對(duì)“數(shù)量”列和“進(jìn)價(jià)”列進(jìn)行運(yùn)算，并在該列金額后單位“元”，即金額=數(shù)量*進(jìn)價(jià)。代碼：“數(shù)量”列：#“條” “進(jìn)價(jià)”列：#“元/條” “金額”列：#“元”代碼說(shuō)明：代碼中的文本可以在單元格中顯示，即帶引號(hào)的文本作為單元格添加的顯示內(nèi)容，“#”來(lái)代表單元格中實(shí)際數(shù)字，其實(shí)運(yùn)算還是在數(shù)字之間進(jìn)行的。用途：當(dāng)遇到單元格中的內(nèi)容為運(yùn)算的數(shù)字，但又需要顯示數(shù)字加文本時(shí)就可以用到，在數(shù)值單元格中添加單位。絕對(duì)引用和相對(duì)引用的區(qū)別絕對(duì)引用 單元格中的絕對(duì)單元格引用（例如 $F$6）總是在指定位置引用單元格F6。如果公式所在單元格的位置改變，絕對(duì)引用的單元格始終保持不變。如果多行或多列地復(fù)制公式，絕對(duì)引用將不作調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用，需要將它們轉(zhuǎn)換為絕對(duì)引用。例如，如果將單元格 B2 中的絕對(duì)引用復(fù)制到單元格 B3，則在兩個(gè)單元格中一樣，都是 $F$6。 相對(duì)引用 公式中的相對(duì)單元格引用（例如 A1）是基于包含公式和單元格引用的單元格的相對(duì)位置。如果公式所在單元格的位置改變，引用也隨之改變。如果多行或多列地復(fù)制公式，引用會(huì)自動(dòng)調(diào)整。默認(rèn)情況下，新公式使用相對(duì)引用。例如，如果將單元格 B2 中的相對(duì)引用復(fù)制到單元格 B3，將自動(dòng)從 =A1 調(diào)整到 =A2。 將公式從一個(gè)單元格復(fù)制到另一時(shí)，相對(duì)和絕對(duì)之間的差異就顯得重要。默認(rèn)使用相對(duì)引用.絕對(duì)引用是你選的單元格，不會(huì)變的。相對(duì)引用則是單元格的相對(duì)位置。比如是左邊第二格，就一直是左邊二格。輸入公式和函數(shù)后可能出現(xiàn)的錯(cuò)誤有哪幾種？分別是由于什么原因造成的？應(yīng)如何解決？2.2.1 #!原因：導(dǎo)致這種錯(cuò)誤的情況一有兩種情況：1 如果單元格所含的數(shù)字、日期或時(shí)間比單元格寬，就會(huì)產(chǎn)生#!錯(cuò)誤。2 單元格的日期時(shí)間公式產(chǎn)生了一個(gè)負(fù)值，就會(huì)產(chǎn)生#!錯(cuò)誤。解決方法：如果單元格所含的數(shù)字、日期或時(shí)間比單元格寬，可以拖動(dòng)單元格列表的寬度來(lái)實(shí)現(xiàn)。如果使用的是1900年的日期系統(tǒng)，那么Excel中的日期和時(shí)間必須為正值，用較早的日期或者時(shí)間值減去較晚的日期或者時(shí)間值就會(huì)導(dǎo)致#!錯(cuò)誤。如果公式正確，也可以將單元格的格式改為非日期和時(shí)間型來(lái)顯示該值。2.2.2#VALUE!當(dāng)使用錯(cuò)誤的參數(shù)或運(yùn)算對(duì)象類(lèi)型時(shí)，或者當(dāng)公式自動(dòng)更正功能不能更正公式時(shí)，將產(chǎn)生錯(cuò)誤值#VALUE!。原因一：在需要數(shù)字或邏輯值時(shí)輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類(lèi)型。解決方法：確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如：如果單元格A1包含一個(gè)數(shù)字，單元格A2包含文本學(xué)籍，則公式=A1+A2將返回錯(cuò)誤值#VALUE!?？梢杂肧UM工作表函數(shù)將這兩個(gè)值相加(SUM函數(shù)忽略文本)：=SUM(A1:A2)。原因二：將單元格引用、公式或函數(shù)作為數(shù)組常量輸入。解決方法：確認(rèn)數(shù)組常量不是單元格引用、公式或函數(shù)。原因三：賦予需要單一數(shù)值的運(yùn)算符或函數(shù)一個(gè)數(shù)值區(qū)域。解決方法：將數(shù)值區(qū)域改為單一數(shù)值。修改數(shù)值區(qū)域，使其包含公式所在的數(shù)據(jù)行或列。2.2.3 #DIV/O!當(dāng)公式被零除時(shí)，將會(huì)產(chǎn)生錯(cuò)誤值#DIV/O!。原因一：在公式中，除數(shù)使用了指向空單元格或包含零值單元格的單元格引用(在Excel中如果運(yùn)算對(duì)象是空白單元格，Excel將此空值當(dāng)作零值)。解決方法：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。原因二：輸入的公式中包含明顯的除數(shù)零，例如：=5/0。解決方法：將零改為非零值。2.2.4 #NAME?在公式中使用了Excel不能識(shí)別的文本時(shí)將產(chǎn)生錯(cuò)誤值#NAME?。原因一：刪除了公式中使用的名稱(chēng)，或者使用了不存在的名稱(chēng)。解決方法：確認(rèn)使用的名稱(chēng)確實(shí)存在。選擇菜單插入&line;名稱(chēng)&line;定義命令，如果所需名稱(chēng)沒(méi)有被列出，請(qǐng)使用定義命令添加相應(yīng)的名稱(chēng)。原因二：名稱(chēng)的拼寫(xiě)錯(cuò)誤。解決方法：修改拼寫(xiě)錯(cuò)誤的名稱(chēng)。原因三：在公式中使用標(biāo)志。解決方法：選擇菜單中工具&line;選項(xiàng)命令，打開(kāi)選項(xiàng)對(duì)話(huà)框，然后單擊重新計(jì)算標(biāo)簽，在工作薄選項(xiàng)下，選中接受公式標(biāo)志復(fù)選框。原因四：在公式中輸入文本時(shí)沒(méi)有使用雙引號(hào)。解決方法：Excel將其解釋為名稱(chēng)，而不理會(huì)用戶(hù)準(zhǔn)備將其用作文本的想法，將公式中的文本括在雙引號(hào)中。例如：下面的公式將一段文本總計(jì)：和單元格B50中的數(shù)值合并在一起：=總計(jì)：&B50原因五：在區(qū)域的引用中缺少冒號(hào)。解決方法：確認(rèn)公式中，使用的所有區(qū)域引用都使用冒號(hào)。例如：SUM(A2:B34)。2.2.5 #N/A原因：當(dāng)在函數(shù)或公式中沒(méi)有可用數(shù)值時(shí)，將產(chǎn)生錯(cuò)誤值#N/A。解決方法：如果工作表中某些單元格暫時(shí)沒(méi)有數(shù)值，請(qǐng)?jiān)谶@些單元格中輸入#N/A，公式在引用這些單元格時(shí)，將不進(jìn)行數(shù)值計(jì)算，而是返回#N/A。2.2.6 #REF!當(dāng)單元格引用無(wú)效時(shí)將產(chǎn)生錯(cuò)誤值#REF!。原因：刪除了由其他公式引用的單元格，或?qū)⒁苿?dòng)單元格粘貼到由其他公式引用的單元格中。解決方法：更改公式或者在刪除或粘貼單元格之后，立即單擊撤消按鈕，以恢復(fù)工作表中的單元格。2.2.7 #NUM!當(dāng)公式或函數(shù)中某個(gè)數(shù)字有問(wèn)題時(shí)將產(chǎn)生錯(cuò)誤值#NUM!。原因一：在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)。解決方法：確認(rèn)函數(shù)中使用的參數(shù)類(lèi)型正確無(wú)誤。原因二：使用了迭代計(jì)算的工作表函數(shù)，例如：IRR或RATE，并且函數(shù)不能產(chǎn)生有效的結(jié)果。解決方法：為工作表函數(shù)使用不同的初始值。原因三：由公式產(chǎn)生的數(shù)字太大或太小，Excel不能表示。解決方法：修改公式，使其結(jié)果在有效數(shù)字范圍之間。2.2.8 #NULL!當(dāng)試圖為兩個(gè)并不相交的區(qū)域指定交叉點(diǎn)時(shí)將產(chǎn)生錯(cuò)誤值#NULL!。原因：使用了不正確的區(qū)域運(yùn)算符或不正確的單元格引用。解決方法：如果要引用兩個(gè)不相交的區(qū)域，請(qǐng)使用聯(lián)合運(yùn)算符逗號(hào)(，)。公式要對(duì)兩個(gè)區(qū)域求和，請(qǐng)確認(rèn)在引用這兩個(gè)區(qū)域時(shí)，使用逗號(hào)。如：SUM(A1:A13，D12:D23)。如果沒(méi)有使用逗號(hào)，Excel將試圖對(duì)同時(shí)屬于兩個(gè)區(qū)域的單元格求和，但是由于A1:A13和D12:D23并不相交，所以他們沒(méi)有共同的單元格。提高輸入數(shù)據(jù)的準(zhǔn)確性為了提高輸入數(shù)據(jù)的準(zhǔn)確性，其基本的功能要求是：輸入操作簡(jiǎn)單、輕松；輸入效率高，即具有重復(fù)內(nèi)容自動(dòng)復(fù)制和簡(jiǎn)易代碼輸入替代功能；輸入格式美觀(guān)大方；具有醒目的提示等。然而，僅有這些功能要求是不夠的，它們不能從根本上提高輸入數(shù)據(jù)的準(zhǔn)確性。因?yàn)椋罅康脑紨?shù)據(jù)的輸入是件繁瑣而又單調(diào)的工作，難免出錯(cuò)。所以，必須要有更嚴(yán)格、更有效的科學(xué)方法和手段來(lái)提高輸入數(shù)據(jù)的準(zhǔn)確性。本文給出了提高輸入數(shù)據(jù)準(zhǔn)確性的方法，供數(shù)據(jù)庫(kù)管理系統(tǒng)的開(kāi)發(fā)者參考與借鑒。一、輸入數(shù)據(jù)的校驗(yàn)與查錯(cuò)方法通常情況下輸入數(shù)據(jù)的校驗(yàn)與查錯(cuò)方法有兩種，其中第一種是邊輸入邊校驗(yàn)法。在這種方法中，假若輸入數(shù)據(jù)有錯(cuò)，則要求數(shù)據(jù)錄入者立即更正錯(cuò)誤。這種方法常常用于所輸入的數(shù)據(jù)具有某種規(guī)律和特征，若數(shù)據(jù)錄入者鍵入的數(shù)據(jù)違背了這個(gè)規(guī)律和特征，即立即給出輸入出錯(cuò)警告，并強(qiáng)制性要求數(shù)據(jù)錄入者對(duì)當(dāng)前輸入的數(shù)據(jù)給予修正。例如，在財(cái)務(wù)管理系統(tǒng)中，一張“記帳憑證”一般有借方金額和貸方金額兩欄數(shù)字。會(huì)計(jì)制度要求同一張憑證中借方金額合計(jì)和貸方金額合計(jì)必須相等。根據(jù)這一特征，所以在開(kāi)發(fā)“記帳憑證”數(shù)據(jù)輸入程序時(shí)，程序應(yīng)能自動(dòng)判斷，在一張“記帳憑證”的數(shù)據(jù)輸入結(jié)束后，借方金額合計(jì)與貸方金額合計(jì)是否相等，若不相等，應(yīng)強(qiáng)制要求數(shù)據(jù)輸入者立即重新輸入。又例如，在每年的高考中，考生的成績(jī)數(shù)據(jù)有一部分要通過(guò)人工閱卷評(píng)分后，然后由專(zhuān)人輸入計(jì)算機(jī)。對(duì)于考生成績(jī)數(shù)據(jù)，它所具有的特征是：每題的最高分和最低分(零分)是確定的，并且均為數(shù)字字符。根據(jù)這個(gè)特征，所以在開(kāi)發(fā)的考生成績(jī)數(shù)據(jù)管理系統(tǒng)的輸入模塊中，應(yīng)具有如下功能，即在每題數(shù)據(jù)輸入結(jié)束后，自動(dòng)判斷輸入的分?jǐn)?shù)值是否符合上述規(guī)律，若不符合，則應(yīng)立即發(fā)出警告，并強(qiáng)制要求錄入者重新輸入。除此以外，第二種數(shù)據(jù)校驗(yàn)與查錯(cuò)的方法是雙工輸入比較法。所謂雙工輸入比較法，就是將同一批數(shù)據(jù)由兩個(gè)輸入人員在不同的時(shí)間和不同的終端上分別錄入，并且形成兩個(gè)臨時(shí)數(shù)據(jù)庫(kù)文件，然后由第三個(gè)人在程序的作用下對(duì)兩個(gè)庫(kù)文件中的數(shù)據(jù)進(jìn)行逐項(xiàng)比較并進(jìn)行確認(rèn)或修改。在這種方法中，盡管同一批數(shù)據(jù)被錄入了兩次從而造成了數(shù)據(jù)冗余和影響了錄入進(jìn)度，但對(duì)于被錄入的數(shù)據(jù)不存在明顯的規(guī)律和上述第一種方法不能查出輸入出錯(cuò)的場(chǎng)合，以及對(duì)輸入數(shù)據(jù)的準(zhǔn)確性要求很高的場(chǎng)合，是一種不可缺少的和行之有效的方法，因?yàn)?，兩個(gè)數(shù)據(jù)錄入者都同時(shí)在某處出錯(cuò)的機(jī)會(huì)極少，故這種方法可以極大地減少出錯(cuò)率。根據(jù)概率論原理，如果兩個(gè)數(shù)據(jù)錄入者各自的出錯(cuò)率為百分之一，則雙工輸入法的出錯(cuò)率僅為萬(wàn)分之一。雙工輸入比較法在FoxPro環(huán)境下的基本算法是：將同一批數(shù)據(jù)由兩個(gè)錄入者在不同時(shí)間和不同的終端上錄入，并存入兩個(gè)不同名的庫(kù)文件中。輸入“”中產(chǎn)生的兩個(gè)庫(kù)文件名。在兩個(gè)工作區(qū)中分別打開(kāi)一個(gè)庫(kù)文件，并按記錄號(hào)使兩個(gè)庫(kù)文件建立關(guān)聯(lián)。對(duì)兩個(gè)庫(kù)文件中的對(duì)應(yīng)記錄進(jìn)行逐項(xiàng)比較，若不相等，則進(jìn)行登記，包括位置的登記。若記錄未結(jié)束，則記錄指針加1，轉(zhuǎn)。打印輸出所有不相等的數(shù)據(jù)項(xiàng)及其位置。確認(rèn)和修改。在上述算法“”中