office操作技巧手冊(cè)系列_EXCEL.doc

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