office操作技巧手冊系列_EXCEL.doc

基本操作德賽集團內部培訓手冊（一）Excel 2002笑傲紅塵客（王占奎）銀河系出版社廣東*惠州2006*08-2007*03目錄目錄2序言6關于本書6Excel6第1章 基本操作71.1.文件71.1.1.另存為71.1.1.1.設置密碼71.1.1.2.一鍵到位 方便百倍71.1.2.頁面設置81.1.2.1.頁面81.1.2.2.頁邊距81.1.2.3.頁眉頁腳81.1.2.4.工作表:91.1.3.屬性：91.1.4.打印區(qū)域91.2.編輯101.2.1.選擇性粘貼101.2.2.粘貼為超鏈接101.2.3.移動或復制工作表101.2.4.查找（替換）111.2.5.定位111.2.6.鏈接121.3.視圖121.3.1.分頁預覽121.3.2.任務窗格121.3.3.自定義工具欄131.4.插入141.4.1.名城141.4.2.批注151.4.3.對象161.4.4.超鏈接161.5.格式171.5.1.單元格171.5.1.1.數(shù)字171.5.1.2.對齊211.5.1.3.字體211.5.1.4.邊框211.5.1.5.圖案211.5.1.6.保護211.5.2.工作表221.5.3.自動套用格式221.5.4.條件格式221.5.4.1.重復數(shù)據(jù)（實用的技巧）：221.5.4.2.判斷單元格的值設置231.5.4.3.以其他條件，設置當前單元格231.5.4.4.把有公式的單元格標注顏色231.6.工具241.6.1.保護241.6.2.宏241.6.3.加載宏241.6.4.自動更正選項251.6.5.自定義251.6.6.選項251.4.1.1.視圖251.4.1.2.重新計算261.4.1.3.編輯261.4.1.4.常規(guī)261.4.1.5.1-2-3的幫助：261.4.1.6.自定義序列：271.4.1.7.錯誤檢查271.4.1.8.安全性281.6.7.數(shù)據(jù)分析281.7.數(shù)據(jù)281.7.1.排序281.7.1.1.自定義排序281.7.1.2.按照字體顏色排序291.7.2.篩選291.7.2.1.自動篩選291.7.2.2.高級篩選301.7.3.記錄單301.7.4.分類匯總311.7.5.有效性311.7.5.1.創(chuàng)建下拉列表選項321.7.5.2.復雜的下拉表選項321.7.5.3.動態(tài)的下拉表選項331.7.5.4.制作會計科目菜單341.7.5.5.有效性設置的其他情況351.7.5.6.自定義條件示例361.7.5.6.1.防止輸入重復值361.7.5.6.2.限定數(shù)據(jù)輸入數(shù)量371.7.5.6.3.多列唯一值371.7.5.6.4.跨表禁止重復輸入371.7.5.6.5.限定總數(shù)381.7.5.6.6.防止輸入周末日期381.7.5.6.7.日期序時輸入381.7.5.6.8.特定前綴391.7.5.6.9.和窗體結合簡單例子391.7.5.6.10.其他問題401.7.5.7.添加提示信息401.7.5.8.添加出錯信息411.7.5.9.輸入法模式421.7.5.10.源于其它工作簿的列表421.7.5.11.其他實例421.7.6.模擬運算表431.7.7.分列451.7.8.合并計算471.7.9.組及分級顯示471.7.10.數(shù)據(jù)透視表和數(shù)據(jù)透視圖471.8.窗口481.8.1.拆分481.8.2.凍結481.8.3.當前文件481.9.幫助49第2章 函數(shù)(公式)502.1.函數(shù)基礎知識502.2.函數(shù)522.2.1.常用函數(shù)522.2.1.1.SUM522.2.1.2.IF522.2.1.2.1.例子1，IF基礎應用532.2.1.2.2.例子2，比較單元格532.2.1.2.3.例子3，IF嵌套532.2.1.2.4.例子4，542.2.1.3.sumif542.2.1.4.Rand542.2.2.數(shù)據(jù)庫函數(shù)：562.2.2.1.DSUM562.2.3.查找引用函數(shù)582.2.3.1.address582.2.3.2.choose582.2.3.3.column582.2.3.4.hlookup582.2.3.5.hyperlink582.2.3.6.Index612.2.3.7.Indirect612.2.3.8.Lookup642.2.3.9.Match652.2.3.10.Offset652.2.3.11.Row652.2.3.12.vlookup652.2.4.日期時間函數(shù)662.2.4.1.day360662.2.4.2.now662.2.4.3.today662.2.5.數(shù)學函數(shù)662.2.6.財務會計函數(shù)662.2.6.1.amordegrc662.2.7.信息函數(shù)672.2.7.1.cell672.2.7.2.error.ype672.2.7.3.is類672.2.7.4.n672.3.數(shù)組702.4.宏表函數(shù)71第3章 圖表76第4章 窗體和VBA77第5章 綜合應用案例78第6章 經(jīng)典技巧大典79序言關于本書本書給那些經(jīng)常使用EXCEL，又不熟悉它的強大功能的人準備！本書不講類如安裝，新建，保存等非常基礎的東東，如果這些都不會就不要讀此書了！本書可以達到的效果是： Excel第1章 基本操作1.1. 文件1.1.1. 另存為1.1.1.1. 設置密碼為了有效保護文檔安全，經(jīng)常需要設置密碼： （OFFICE密碼只能放菜鳥）A .文件 另存為 工具 常規(guī)選項輸入密碼 確定（要覆蓋原文件才能設置成功密碼，否則另存為新文件，只能是新文件有密碼的）還有一種設置密碼方法：B 工具 選項 安全性 輸入密碼 確定 1.1.1.2. 一鍵到位 方便百倍1.在另存為窗口選中，選中待填入的文件夾2. 工具 添加到“我的位置”3.在另存為的對話框左邊就有了填入的文件4.以后保存文件就部需要找文件夾了，1.1.2. 頁面設置1.1.2.1. 頁面1. 方向：選擇打印的紙張方向2. 縮放：.調整為，意思把多于一頁的表格調整為特定頁數(shù)！此操縱前，最好先把頁邊距調整好 （.文件 打印預覽 頁邊距）。此功能等同于（ 視圖 ） 分頁預覽。1.1.2.2. 頁邊距1.上下左右，頁眉頁腳：設置頁邊距；等同于 .文件 打印預覽 頁邊距2.居中方式：數(shù)據(jù)（圖表）在這個頁面里面的居中（水平，垂直）位置，1.1.2.3. 頁眉頁腳1.自定義頁眉（頁腳）：文件 頁面設置自定義頁眉（頁腳）從左至右順序功能01.: 設置頁眉/頁腳文字的屬性,例如:字體,字型,大小,下劃線,刪除線,上標,下標02.頁碼:當前第幾頁03.:總頁數(shù),所有數(shù)據(jù)共有幾頁04.:日期:系統(tǒng)當前日期,是自動更新的05.:時間系統(tǒng)當前時間, 是自動更新的06.路徑文件:此文件的絕對路徑+文件名,例如07.文件:當前文件名08.標簽名:當前表名09.:圖片:插入圖片,一般是企業(yè)LOGO 或者徽標10.設置:插入圖片后,設置圖片格式的11(12.13):位置: 設置頁眉（頁腳）在在文件的左,中,右位置實際應用例子:1.要求每張文件右下腳都要有”制表人”,？ 答：就在上圖 13”位置直接填入:”制表人:” 說明： 工作表設置（詳見1.1.2.4 工作表）打印標題欄只能打印頂端（左端）標題欄，不能打印尾端標題欄；頁腳設置可以部分解決這個問題。徹底解決就需要設置復雜的公式，詳見CD文件！2.曾經(jīng)有人問：正常編輯時候是自己要求的數(shù)據(jù)和圖表，但是預覽和打印的時候就在頁眉，頁腳位置出現(xiàn)一些多余的文字和數(shù)據(jù)，怎么解決？ 答： 看看頁眉，頁腳的自定義； 把所有多余的數(shù)據(jù)清除就OK了！1.1.2.4. 工作表:1.打印區(qū)域: 不打印全部文章，只打印部分,等同于 .文件 打印區(qū)域 設置打印區(qū)域2.打印標題:每頁都打印同樣的標題!實際應用：如表格很長,要每頁都要有標題行,就點擊頂端標題行設置即可,左端標題列類似.3.打?。壕W(wǎng)格線 把虛線的網(wǎng)格線都打印出來單色打印 彩色的也只是按照黑白打印按草稿方式 不打印邊框，彩色打印成黑白色行號列標 是否打印行號列標批注 是否打印批注錯誤單元格打印為 設置錯誤值打印樣式1.1.3. 屬性：要填寫標題，主題，作者，類別，關鍵詞，備注這些，便于以后檢索.。如果文件繁多，你就喜歡這個功能了。1.1.4. 打印區(qū)域不想打印全部文章，只是打印部分.文件 打印區(qū)域 設置打印區(qū)域實踐經(jīng)驗：如果遇到一個工作表在編輯時候能看見全部數(shù)據(jù)圖表,但是某些區(qū)域，打印預覽看不到，也不能打印出來,那取消打印區(qū)域可以解決1.2. 編輯1.2.1. 選擇性粘貼非常有用的功能，一定要學會！只介紹常用功能，其他有興趣自己動手實踐粘貼1. 全部：等同于“粘貼”（Ctrl+V）2. 公式：只復制公式3. 把公式變成數(shù)值,復制過來（數(shù)值化）4. 格式:只是復制單元格的格式5. 批注：只是復制單元格的批注運算（非常實用的技巧）1.加（減乘除）：用法等同。把大量數(shù)據(jù)一次性加（減乘除）一個特定數(shù)據(jù)，應用于 固定資產(chǎn)折舊的折舊月數(shù)等；把一批數(shù)據(jù)改變正負；把一批數(shù)據(jù)一次性變成千，萬位單位用法如下：空白單元格填入需要的數(shù)值 復制 選擇全部數(shù)據(jù) 鼠標右鍵（編輯）選擇性粘貼 選中 “數(shù)值”，“加（減乘除）”2.跳過空單元不復制空單元格3.“轉置” 粘貼數(shù)據(jù)時選中，把復制的行數(shù)據(jù)變成列數(shù)據(jù)了，反之亦然?。ǚ浅嵱玫募记桑?.粘貼連接 和源數(shù)據(jù)（一般是本文件）產(chǎn)生連接,目標數(shù)據(jù)可以隨著源數(shù)據(jù)改變而更新1.2.2. 粘貼為超鏈接和源數(shù)據(jù)（外部文件，一般是OFFICE文件）產(chǎn)生連接,目標數(shù)據(jù)可以隨源數(shù)據(jù)改變而自動更新。適用于做套表1.2.3. 移動或復制工作表1. 工作簿： 把選定工作表移至（剪切）當前打開的某個excel文件2. 下列選定工作表之前：把選定工作表移至（剪切） 某個表前面3. 建立副本： 選中就不是 移至（剪切），而是復制1.2.4. 查找（替換）1格式：可以按照特定格式查找（替換）2范圍：可在當前工作表，當前整個文件查找例子：1. 想把表內單元格值為0的為改為空格按ctrl+H替換查找內容輸入0替換為空全部替換。這樣做 101 變成 11,1000001 也會變成 11，不是我們要的結果！正確做法:要勾選單元格匹配,只替換0值的單元格2. 查找(替換)特殊字符: * ? (非常重要的知識)如果對類似01011061*A這樣帶有 * 的數(shù)據(jù)處理會出錯的,原因就是在Excel里面 * 表示所有數(shù)據(jù), ? 表示一個數(shù)據(jù)(字符). A.如果處理帶有 * ， ? 的數(shù)據(jù),一定先要把 * （ ? ）換成其他字符，如: +,-,. ; 處理完數(shù)據(jù)再把 * （ ? ）還原 B.怎樣查找（替換） * ( ? ) :編輯查找（替換）輸入*(以*代替*, 在Esc鍵下邊).如圖注意 :1.以上字符都是在拼音狀態(tài)下2.查找范圍:要選擇數(shù)值還是公式視情況定3.如果是查找一定的區(qū)域，要先用鼠標選擇那個區(qū)域，查找前鼠標的位置有1.2.5. 定位.編輯 定位 定位條件 1. 批注 選擇當前表里面所有的批注2. 常量 選擇當前表里面所有的常量3. 公式 選擇當前表里面所有公式計算得出的數(shù)字，文本，邏輯值，錯誤值4. 空值 選擇當前表里面所有的空白值.實際應用：我們經(jīng)常需要更改（刪除）一個有大量數(shù)據(jù)的表里面的特定數(shù)據(jù)，里面的數(shù)據(jù)有公式，文本數(shù)值，邏輯值，數(shù)字，錯誤值，空值 但是數(shù)據(jù)沒有規(guī)律，一個個處理很是耗費時間的，容易出錯，可能遺漏，怎么解決？例如：要把表里面的錯誤值全部去掉（指公式計算出來的錯誤值，如果單元格里面的數(shù)據(jù)就是類似 #DIV/0! 這樣子的錯誤文本，使用查找，替換可以解決；詳見1.2.4查找）， 答：選擇數(shù)據(jù)區(qū)（表格區(qū)域） .編輯 定位 定位條件 公式 選中錯誤 1.2.6. 鏈接當文件有外部文件的鏈接，此功能才能用！1.更新值：更新鏈接的數(shù)據(jù)2.更改源：更改數(shù)據(jù)的鏈接源3.打開源文件：打開引用的源文件4.斷開鏈接：斷開源文件的的鏈接，直接數(shù)值化；如果不斷開，在源文件更改位置（刪除）后，無法更新，將出現(xiàn)錯誤值5.檢查狀態(tài)：檢查源文件是否可以更新6.啟動提示：設置打開excel文件時候的更新屬性注意：工作中，我經(jīng)常遇到xp版本的excel自動更新鏈接的情況，即使在 是否更新對話框 選擇不更新 也不行！所以強烈建議：做好表格后，把有外部文件鏈接的數(shù)據(jù)全部數(shù)字化1.3. 視圖1.3.1. 分頁預覽視圖 分頁預覽 是（頁面 縮放 ）調整為 的圖形效果！此操縱前，最好先把頁邊距調整好，直接用鼠標拉動藍色虛線調整！藍色實線是數(shù)據(jù)區(qū)，藍色虛線是打印線1.3.2. 任務窗格.視圖 任務窗格 點擊后左側將出現(xiàn)任務窗格1新建工作簿：選擇新建一個指定類型工資簿2.剪貼板：處理復制到系統(tǒng)剪貼板的文字圖片等3.搜索：搜索工作簿內容4.插入剪貼畫：插入系統(tǒng)自帶的剪貼畫或者硬盤里面的圖片1.3.3. 自定義工具欄我們每個人的工作不同，習慣不同；所以使用excel方式一定不同，為了更快的提高工作效率，自己打造一個符合自己習慣的工具條；其實還有很多實用的功能，系統(tǒng)工具條沒有體現(xiàn)出來，下面是操縱步驟：打開自定義窗口：視圖 工具欄 自定義：（工具 自定義）還可以在工具條空白區(qū)域點擊鼠標右鍵調出自定義窗口創(chuàng)建自定義工具條第一步：工具欄 新建 彈出 新建工具欄 輸入 名城第二步：點擊命令 在類別選擇需要的分類菜單 選中命令里面自己需要的命令 按住鼠標左鍵 拖到第一步新建的工具條 松開鼠標，完成后見下圖：編輯自定義工具條1.在打開自定義窗口情況下才能編輯。在要編輯的按鈕上點擊鼠標右鍵，如下窗口2.常用命令如下：復位按鈕圖像：回復原始按鈕圖像編輯按鈕圖像：自己用鼠標畫圖更改按鈕圖像：選擇系統(tǒng)提供的圖像更改默認樣式：按鈕外觀有圖無文字總是用文字：文字圖像與文本：圖像文字混用做成自己特有的工具條，如圖：1.4. 插入1.4.1. 名城插入名稱定義 （ ctrl+F3 ）1.名稱:為了更加直觀地標識單元格或單元格區(qū)域,我們可以給它們賦予一個名稱,單元格區(qū)域,鼠標單擊編輯欄頂端的“名稱框”,在其中輸入名稱后回車.也可以選中要命名的單元格或單元格區(qū)域,單擊“插入名稱定義”命令,在打開的“定義名稱”對話框中輸入名稱后確定即可.如果你要刪除已經(jīng)命名的區(qū)域,可以按相同方法打開“定義名稱”對話框,選中你要刪除的名稱點擊刪除即可.a.輸入名稱:中文,英文皆可b.引用位置是單元格,或者公式函數(shù),宏表函數(shù)c.只有點擊確定,或者添加,才有效2.建議使用簡單易記的名稱，不可使用類似A1的名稱，因為它會和單元格的引用混淆。還有很多無效的名稱，系統(tǒng)會自動提示你。引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。在引用工作表單元格或者公式的時候，絕對引用和相對引用是有很大區(qū)別的，注意體會他們的區(qū)別 和在工作表中直接使用公式時的引用道理是一樣的。3. 定義名稱的妙處 減少輸入的工作量如果你在一個文檔中要輸入很多相同的文本，建議使用名稱。例如：定義DATA = I LOVE YOU， EXCEL；因為Excel可以給我?guī)頋L滾財富！ ，你在任何單元格中輸入 =DATA ，都會顯示 I LOVE YOU， EXCEL；因為Excel可以給我?guī)頋L滾財富！ 在一個公式中出現(xiàn)多次相同的字段例如公式 =IF(ISERROR(IF(A1B1,A1/B1,A1),”, IF(A1B1,A1/B1,A1) ，這里你就可以將 IF(A1B1,A1/B1,A1) 定義成名稱A_B ，你的公式便簡化為 =IF(ISERROR(A_B),”,A_B) 超出某些公式的嵌套例如IF函數(shù)的嵌套最多為七重，這時定義為多個名稱就可以解決問題了。也許有人要說，使用輔助單元格也可以。當然可以，不過輔助單元格要防止被無意間被刪除。 字符數(shù)超過一個單元格允許的最大量名稱的引用位置中的字符最大允許量也是有限制的，你可以分割為兩個或多個名稱。同上所述，輔助單元格也可以解決此問題，不過不如名稱方便。 某些EXCEL函數(shù)只能在名稱中使用例如由公式計算結果的函數(shù)，在A1中輸入=1+2+3，然后定義名稱 RESULT = EVALUATE(Sheet1!$A1) ，最后你在B1中寫入 =RESULT ，B1就會顯示6了。還有GET.CELL函數(shù)也只能在名稱中使用，請參考資料第2章 宏表函數(shù)。 圖片的自動更新連接例如你想要在一周內每天有不同的圖片出現(xiàn)在你的文檔中，具體做法是：6.1 找7張圖片分別放在SHEET1 A1至A7單元格中，調整單元格和圖片大小，使之恰好合適6.2 定義名稱MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)6.3 控件工具箱 文字框，在編輯欄中將EMBED(Forms.TextBox.1,)改成MYPIC就大功告成了。這里如果不使用名稱，應該是不行的。此外，名稱和其他，例如數(shù)據(jù)有效性的聯(lián)合使用，會有更多意想不到的結果，具體資料參見數(shù)據(jù)有效性（1.7.5.）1.4.2. 批注一、批注是附加在單元格中，與其他單元格內容分開的注釋。批注是十分有用的提醒方式，例如注釋復雜的公式如何工作，或為其他用戶提供反饋。查看批注內容 Microsoft Excel 提供了幾種查看批注的方法。含有批注的單元格的右上角有三角形的批注標識符。如果指針停在含有標識符的單元格上，就會顯示該單元格的批注。您也可以連續(xù)地顯示批注（單條批注或是工作表上的所有批注）?！皩忛啞惫ぞ邫谀苁鼓错樞蛑饤l查看每項批注。您還可以將批注打印到工作表的相應位置，或在打印輸出結果的底部將批注打印成列表。批注中的用戶姓名 按用戶的姓名來區(qū)分批注，就可以在共享工作簿或傳送給幾個用戶的工作簿中，判斷出鍵入每項批注的操作者。當合并幾個工作簿的更改時，批注也被合并，這樣單元格中所有的批注文字就會按順序顯示。二、為單元格添加批注批注是為單元格鍵入的注釋。單擊要加批注的單元格。 在“插入”菜單中，單擊“批注”。 在彈出的批注框中鍵入批注文本。如果不想在批注中留有您的姓名，請選擇并刪除姓名。完成文本鍵入后，請單擊批注框外部的工作表區(qū)域。三、調整批注大小或移動批注在需要更改批注的單元格上單擊鼠標右鍵，在隨后出現(xiàn)的快捷菜單中單擊“顯示批注”。 單擊批注框的邊框，顯示尺寸控點。請執(zhí)行下列操作之一：更改尺寸 拖動批注框四邊或角點上的尺寸控點。移動批注 拖動批注框的邊框。如果隱藏并重新顯示批注，它將返回到其默認位置。批注中的默認用戶名可以在 工具選項常規(guī) 中修改批注的默認字體、字體大小、顏色可以在“windows外觀”中修改“工具提示”的相應內容1.4.3. 對象插入 對象 office文件都可以插入其他各種類型的文件。新建 是新建的一個空白的其他各種類型文件顯示為圖標：在excel里面是否以文件圖標形式顯示由文件創(chuàng)建 直接把外部做好的文件插到office文件里面鏈接到文件：與文件同步更新1.4.4. 超鏈接插入 超鏈接 要顯示的文字：在excel單元格里面顯示的文字查找范圍：超鏈接文件在本地硬盤的位置地址：互聯(lián)網(wǎng)地址書簽：等同于本文檔中的位置 ，直接鏈接到當前工作表的某個單元格，鏈接到其他表，鏈接到事先定義好的書簽（名稱）1.5. 格式1.5.1. 單元格1.5.1.1. 數(shù)字在Excel中使用自定義格式功能 ：先選中數(shù)據(jù)區(qū) 格式 單元格 數(shù)字 自定義 見圖表1.5. 1，自定義格式的都可刪除）圖表1.5. 1 # 好象是格式在位符，0表示數(shù)位站位符，如設置單元格式為“000”，則輸入數(shù)字1時，顯示為“001”。？一般表示一個字節(jié)位置。 Excel的自定義格式使用下面的通用模型：正數(shù)格式；負數(shù)格式；零格式；文本格式。在這個通用模型中，包含三個數(shù)字段和一個文本段：大于零的數(shù)據(jù)使用正數(shù)格式；小于零的數(shù)據(jù)使用負數(shù)格式；等于零的數(shù)據(jù)使用零格式；輸入單元格的正文使用文本格式。 我們還可以通過使用條件測試，添加描述文本和使用顏色來擴展自定義格式通用模型的應用。 1.使用顏色要在自定義格式的某個段中設置顏色，只需在該段中增加用方括號括住的顏色名或顏色編號。Excel識別的顏色名為：黑色、紅色、白色、藍色、綠色、青色和洋紅。Excel也識別按顏色X指定的顏色，其中X是1至56之間的數(shù)字，代表56種顏色。 2.添加描述文本要在輸入數(shù)字數(shù)據(jù)之后自動添加文本，使用自定義格式為：文本內容；要在輸入數(shù)字數(shù)據(jù)之前自動添加文本，使用自定義格式為：文本內容。符號的位置決定了Excel輸入的數(shù)字數(shù)據(jù)相對于添加文本的位置。 3.創(chuàng)建條件格式可以使用六種邏輯符號來設計一個條件格式：（大于）、=（大于等于）、（小于）、=（小與等于）、=（等于）、（不等于）。 由于自定義格式中最多只有3個數(shù)字段，Excel規(guī)定最多只能在前兩個數(shù)字段中包括2個條件測試，滿足某個測試條件的數(shù)字使用相應段中指定的格式，其余數(shù)字使用第3段格式。如果僅包含一個條件測試，則要根據(jù)不同的情況來具體分析。如下列自定義格式：紅色；綠色；藍色； ：大于0的數(shù)字顯示紅色，負數(shù)顯示綠色，負數(shù)顯示藍色。 紅色0正數(shù)格式；0負數(shù)格式；零格式；文本格式。另外，我們還可以運用自定義格式來達到隱藏輸入數(shù)據(jù)的目的， ；0 只顯示負數(shù)和零，輸入的正數(shù)則不顯示；格式 ； 則隱藏所有的輸入值。自定義格式只改變數(shù)據(jù)的顯示外觀，并不改變數(shù)據(jù)的值，也就是說不影響數(shù)據(jù)的計算。 #,0,? 這三個字符都是數(shù)字占位符，區(qū)別在于：對于#，系統(tǒng)會自動忽略數(shù)字前后的0值，如：格式設置為： #.# ，對于0103.50則只顯示103.5；對于0，系統(tǒng)直接用0來補位，如：0.00，這也是常用的，如輸入103.5則顯示103.50；如格式設置為：00000，輸入50則顯示成00050，這種也是常用的編號設置法；對于?，它除了具有#號功能外(去除前后的無效的0值)，還多了一個空格占位的功能，常用于數(shù)值小數(shù)點對齊，如：格式設置為：?.?，當輸入0103.50或103.5則系統(tǒng)顯示為103.5_(注：為了便于理解用_來表示一個空格占位)。補充說明：在Excel自定義數(shù)字格式使用如下的表達方式：正數(shù)的格式負數(shù)的格式零的格式文本的格式 #,#0.00 Red-#,#0.000.00TEXT 在Excel自定義數(shù)字格式的格式代碼中，用戶最多可以指定四個節(jié)；每個節(jié)之間用分號進行分隔，這四個節(jié)順序定義了格式中的正數(shù)、負數(shù)、零和文本。如果用戶在表達方式中只指定兩個節(jié)，則第一部分用于表示正數(shù)和零，第二部分用于表示負數(shù)。如果用戶在表達方式中只指定了一個節(jié)，那么所有數(shù)字都會使用該格式。如果在表達方式中要跳過某一節(jié)，則對該節(jié)僅使用分號即可。#：數(shù)字占位符(非0值占位符) G/通用格式：用常規(guī)格式顯示數(shù)字：文本占位符 *：重復下一個字符以充滿列寬，：千位分隔符 :用這種格式顯示下一個字符 ?:數(shù)字占位符實例一：要求數(shù)值后面帶單位（個，元，），數(shù)值還可以進行數(shù)學運算自定義 類型 輸入數(shù)值格式，在后面再輸入引號（一定要拼音狀態(tài)下），引號里面添加所需要的文字 實例二：自定義格式大全：自定義類型自定義格式單元格輸入顯示為解釋使用文本#,#0 元人民幣10001,000 元人民幣輸入數(shù)字，自動加入后綴,數(shù)值可運算答案: 通用格式1000答案: 1000只需輸入數(shù)字，自動加入前綴,數(shù)值可運算總額是#,#0 美元1000總額是1,000美元只需輸入數(shù)字，自動加入前綴和后綴,數(shù)值可運算分隔長數(shù)字#,#0,123456789123,457因為 #,# 中有千位分隔符，所以返回的數(shù)據(jù)保留分隔符；格式中最后用千位分隔符，所以數(shù)據(jù)顯示時，千位后自動四舍五入了，實際數(shù)值沒有變，#,#0,1234567891212,345,679#,#0,12341數(shù)據(jù)確認0.00;僅顯示正數(shù)!123123.00如果是負數(shù)，僅顯示后面的描述，實際數(shù)值沒有變。（表示正數(shù)格式的第一個節(jié)正常書寫，表示負數(shù)格式的第二個節(jié)只用了描述）0.00;僅顯示正數(shù)!-123僅顯示正數(shù)!0.00;僅顯示正數(shù)!00.000.00;僅顯示正數(shù)!123.56444123.560用破折號#,#0_);(#,#0);-0-_)0-0-值為0時左右有“”#,#0_);(#,#0);-0-_)12.212值為正時常規(guī)顯示#,#0_);(#,#0);-0-_)-102(102)值為負時用括號括起來電話號碼(#) #-#8523776596295(852377) 659-6295#/#-#8523776596295852377/659-6295日期格式mmmm-yy1995-6-1June-95mmmm d, yyyy1995-6-1June 1, 1995dddd1995-6-1Thursdaymmmm d, yyyy (dddd)1995-6-1June 1, 1995 (Thursday)Its dddd1995-6-1Its Thursday設置不同顏色紅色=1#,#0;G/通用格式11紅色=1#,#0;G/通用格式-1-100.0%紅色=1#,#0;G/通用格式4545G/通用格式;G/通用格式;G/通用格式;紅色G/通用格式德賽電子(惠州)有限公司德賽電子(惠州)有限公司最后一個節(jié)規(guī)定了文本的顏色G/通用格式;G/通用格式;G/通用格式;紅色G/通用格式234234其他符號G/通用格式20042004#-#-#421897322421-89-7322社會福利號 #-#-#421897322社會福利號 421-89-7322=C113-TODAY()2006-12-10已過期100天!描述正數(shù);負數(shù);零;文本32正數(shù)僅以文字的格式表示原來的數(shù)，不影響D53至D56單元格中數(shù)值，可看后面的運算就知道。正數(shù);負數(shù);零;文本-32負數(shù)正數(shù);負數(shù);零;文本0零正數(shù);負數(shù);零;文本德賽電子文本隱藏;1234隱藏單元格內容，但編輯欄里還能看到的。不影響引用，運算。另一種隱藏是把字體設為白色。這兩種方法都是表面的隱藏，都不能打印。;-145條件語句100#,000;99有要求的隱藏，大于100的數(shù)值顯示，否則都不顯示。其余同上。100#,000;102102100#,000;-54100#,000;德賽電子1.5.1.2. 對齊單元格格式的對齊一般功能很簡單，如果做些復雜可參考見CD例子1.5.1.3. 字體功能常見，不再羅嗦1.5.1.4. 邊框常用功能！此功能與其他操做結合，可以發(fā)揮意想不到的效果，可參考CD例子1.5.1.5. 圖案此功能多數(shù)是與其他操做結合使用，可參考CD例子1.5.1.6. 保護非常有用的功能，尤其對于做匯總表的人！我們經(jīng)常遇到這種情況：你辛辛苦苦做成一個表格，發(fā)給別人填寫，別人給你亂改表格！本來很簡單的匯總數(shù)據(jù)的工作將變得很繁瑣，下面的例子將徹底解決你的煩惱！保護的效果：保護自己的數(shù)據(jù)表結構不被修改，公式內容不被看見；需要輸入數(shù)據(jù)的區(qū)域可以自由編輯，否則不可改動！方法1. 保護(隱藏)表格公式不被修改(看見),而數(shù)據(jù)區(qū)卻可以修改.解決如下:方法1.選中表的所有區(qū)域鼠標右鍵”設置單元格格式” ”保護” 取消”鎖定”,”隱藏” 選中公式區(qū)域鼠標右鍵”設置單元格格式” ”保護” 選中”鎖定”,”隱藏” ”工具”保護” ”保護工作表” 輸入”密碼” ”確定”方法2.(適合復雜大表)說明：1.淺色（黃色）是標題欄2.深色（綠色）是公式區(qū)3.中間（白色）是數(shù)據(jù)區(qū)4.要求數(shù)據(jù)區(qū)可以輸入修改數(shù)據(jù)，標題欄只能瀏覽，不可修改；公式區(qū)能看見結果，不能修改公式，不能看見公式。做法：1、全選工作表單元格格式保護取消鎖定。2、編輯 定位定位條件選公式按確定3、選定要保護的單元格，單元格格式保護勾選鎖定、隱藏。4、工具 保護工作表選定鎖定的內容輸入密碼。1.5.2. 工作表1.重命名：更改工作表名字2.隱藏：隱藏工作表3.背景：為空白背景的工作表添加漂亮的圖片4.工作表標簽顏色：更改工作表標簽的顏色，便于工作區(qū)分1.5.3. 自動套用格式不常用，也不實用的！略去?？蓞⒖糃D例子1.5.4. 條件格式1.5.4.1. 重復數(shù)據(jù)（實用的技巧）：方法一.第二個及以上重復數(shù)據(jù)標注特定格式=COUNTIF($D$1:D1,D1)1 （針對D列,數(shù)據(jù)是從D1開始的）=COUNTIF($A$97:A97,A97)1 (針對A列,數(shù)據(jù)是從A97開始的)方法一操作：:選中數(shù)據(jù)區(qū) :格式條件格式 （窗口）.在條件上選擇公式輸入方法一的公式：=COUNTIF($A$97:A97,A97)1) 設置單元格格式注意：1.IF語句是屏蔽了都是0時,條件格式無效；2.注意公式內容和數(shù)據(jù)區(qū)第一行是關聯(lián)的；3.注意鎖定符號位置方法二.把所有重復數(shù)據(jù)設置特定格式=COUNTIF(D:D,D1)1 （針對D列,數(shù)據(jù)是從D1開始的）=COUNTIF(A:A,A97)1 (針對A列,數(shù)據(jù)是從A97開始的)方法二操作：選中數(shù)據(jù)區(qū)格式 條件格式 .選擇公式 .輸入方法二的公式3.設置單元格格式注意公式內容和方法一是不同的說明：1. 有人說可以用排序啊，問題是excel排序只能支持1000行數(shù)據(jù)，超過了排序就無效！2. 如果是剔除重復數(shù)據(jù)，選出不重復唯一值，可參見1.7.2高級篩選3. 如果是選出重復數(shù)據(jù)，參見第二章 函數(shù) 1.5.4.2. 判斷單元格的值設置1.判斷錯誤值=isERROR(A1)紅色：如果是錯誤值，單元格填充紅色2.判斷文本=isTEXT(A1)黃色：如果是文本，單元格填充黃色3.判斷數(shù)值=isNUMBER(A1)藍色 如果是數(shù)值，單元格填充藍色說明：主要利用is類函數(shù)，具體操縱和上面的設置重復值類似，不再累述！1.5.4.3. 以其他條件，設置當前單元格要求：客戶名城單元格格式隨著訂單值變化，怎么做？選中數(shù)據(jù)區(qū)（客戶欄） 格式 條件格式 .選擇公式 .輸入公式（“E1960”） 設置單元格格式1.5.4.4. 把有公式的單元格標注顏色1.在表內任意單元格（這里就選擇A10）定義名稱（s） 公式欄輸入：=GET.CELL(48,Sheet3!A10)這個是宏表函數(shù)，48 意思是 單元格是否含有公式（1，0）1. 選中待處理區(qū)域，使用 條件格式2. 選擇 公式 ，公式欄輸入 S，設定格式3. 效果如圖：1.6. 工具1.6.1. 保護保護工作表：保護當前工作表！實際應用見1.5.1.6的單元格保護允許用戶編輯區(qū)域：設定用戶可編輯區(qū)域，與保護工作表功能關聯(lián)保護工作簿：保護所有工作表，可以在工作表編輯，但不能刪除（移動，添加）工作表1.6.2. 宏如果要運行帶宏的excel文件（例如自定義公式，函數(shù)），要先設置宏的安全性為“中”，一般系統(tǒng)默認是“高”，不能運行宏。 工具宏 安全性安全級中 1.6.3. 加載宏如果把自定義的公式（函數(shù)）變成系統(tǒng)公式（函數(shù)）一樣使用，就需要加載宏。1.首先復制*.xla文件（如果不是*.xla文件，需要把含有自定義公式的excel文件另存為擴展名為.xla的文件）2.工具 加載宏 瀏覽 出現(xiàn)瀏覽窗口，把剛才復制的文件粘貼到窗口里面，或者復制到C:Documents and SettingsXXXApplication DataMicrosoftAddIns里面3.選中自定義的公式（函數(shù)），如圖4.以后需要此公式，就如同用系統(tǒng)自帶的公式一樣1.6.4. 自動更正選項自動更正選項，類似拼音輸入法的詞組記憶功能；例如：輸入公司簡稱“德賽電子”就能自動成為“德賽電子(惠州)有限公司”，操作步驟：工具 自動更正選項 自動更正 出現(xiàn)如圖 “替換”框輸入“德賽電子”，“替換為”框輸入“德賽電子(惠州)有限公司”1.6.5. 自定義如前項 .視圖 工具欄 自定義1.6.6. 選項1.4.1.1. 視圖1.：啟動任務窗格 ：相當于.視圖 任務窗格 選中，每次打開excel都啟動任務窗格2.：編輯欄：是否顯示編輯欄3.：狀態(tài)欄：excel窗口最底下的一欄4.：批注：是否顯示5.：對象：是否顯示窗口選項：自動分頁符：窗口顯示分頁符號（虛線），公式：是顯示公式，還是顯示結果網(wǎng)格線：是否顯示網(wǎng)格線網(wǎng)格線顏色：設置網(wǎng)格線的顏色行號列標：是否顯示行號列標分級顯示符號：零值：是否顯示零值水平滾動條：是否顯示水平滾動條垂直滾動條：是否顯示垂直滾動條工作表標簽：是否顯示工作表標簽1.4.1.2. 重新計算1.計算：是否自動重算工作表公式2迭代計算：一般不用3.工作表選項：更新遠程引用：是否更新工作簿外面的引用；以顯示值為準：按照顯示值確定數(shù)據(jù)值，會更改文件所有表格的格式1904年日期系統(tǒng)：更改日期設置保存外部鏈接數(shù)據(jù)：引用外部數(shù)據(jù)是否保存數(shù)據(jù)1.4.1.3. 編輯1.單元格內部直接編輯：不能在單元格編輯，只能在編輯欄編輯2.單元格拖放功能：是否可以使用單元格拖放功能3.按Enter鍵后移動方向：Enter鍵移動方向，默認向下4.自動設置小數(shù)點：小數(shù)點位數(shù)5.對象隨同單元格剪切復制和排序：插入的圖片圖形等是否隨同單元格變化二變化6.請求自動更新鏈接：是否請求更新鏈接7.記憶式鍵入：是否記憶已經(jīng)輸入的數(shù)據(jù)1.4.1.4. 常規(guī)1.R1C1引用樣式：選中則表的列由字母ABCB變成數(shù)字1232.最近使用的文件列表：（在菜單項文件尾部）保留最新使用的文件數(shù)， 3.新工作簿內的工作表數(shù)：新建文件有多少表4.默認文件位置：保存文件默認位置5.啟動時打開此項的所有文件：啟動excel時候同時啟動的文件6.用戶名：文件的屬性之一1.4.1.5. 1-2-3的幫助：兼容以前軟件的設置1.4.1.6. 自定義序列：自定義序列的使用例子：1.在單元格輸入需要的序列2.點擊添加序列的折疊按鈕， 選中序列3.點擊導入，在自定義序列的列表框最后就出現(xiàn)了4.選中數(shù)據(jù)區(qū)，數(shù)據(jù) 排序 選項選擇自定義排序次序，點擊小，選擇剛才導入的自定義序列5.選擇排序方式，確定1.4.1.7. 錯誤檢查此表詳細列出系統(tǒng)的錯誤檢查的項目，如果不想出現(xiàn)某個錯誤提示，可以設置此處。1.4.1.8. 安全性設置打開，改寫excel文件的密碼，詳見1.1.1設置密碼excel文件的密碼安全性很差，只能防住菜鳥！不推薦使用多級加密。1.6.7. 數(shù)據(jù)分析如果需要一些復雜的特別數(shù)據(jù)分析，需要添加分析工具：工具數(shù)據(jù)分析此項不常用，本書不做介紹1.7. 數(shù)據(jù)1.7.1. 排序1. 數(shù)據(jù) 排序 是常用的排序，本書不再累述！主要講些另類實用的排序技巧1.7.1.1. 自定義排序.自定義序列的使用例子詳見 選項 中的自定義序列，二者結合運用才能發(fā)揮作用1.7.1.2. 按照字體顏色排序EXCE