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

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