Excel在數(shù)據(jù)管理與分析中的應(yīng)用

第1章Excel基礎(chǔ)本章學(xué)習(xí)目標(biāo)１、認(rèn)識Excel的工作界面、窗口的組成元素２、掌握工作薄、工作表、單元格等基本概念３、掌握工作表、單元格的常見操作如插入、刪除等５、掌握菜單、工具條的操作和自定義方法６、了解掌握多種觀察工作表數(shù)據(jù)的方法７、掌握工作表打印的幾個問題：打印預(yù)覽、打印紙、頁碼及打印標(biāo)題等1.1啟動與退出Excel1．啟動Excel的方法通過Windows任務(wù)欄選擇“開始”按鈕通過Excel快捷方式雙擊Ｅxcel文件的名字或圖標(biāo)２、退出Ｅxcel的方法單擊Excel窗口右上角的關(guān)閉按鈕選擇“文件”菜單中的“退出”菜單項(xiàng)。按快捷鍵Alt+F4。1.1.1

Excel界面1.1.2

Excel基本概念1、工作簿在Excel中創(chuàng)建的文件叫做工作簿，其擴(kuò)展名是.xls工作薄由工作表組成，新建工作薄只有默認(rèn)的３個工作表。但可以根據(jù)需要增加，其個數(shù)原則上僅受限于內(nèi)存。新建第一個工作薄的默認(rèn)名：book1.xls。2、工作表由一些橫向和縱向的網(wǎng)格組成的表格。一個工作表最多有65536行、256列。工作表由工作表標(biāo)符區(qū)別，如sheet1、sheet21.1.2

Excel基本概念工作薄與工作表的關(guān)系1.1.2

Excel基本概念3、行號工作表由65536行組成，每行的編號稱為行號．4、列標(biāo)工作表有256列，用于區(qū)別各列的英文字母稱列標(biāo)5、單元格、活動單元格工作表中的網(wǎng)格，它用列標(biāo)和行號定位。如A1就表示Ａ列第１行對應(yīng)的單元格6、編輯欄用于顯示、修改活動單元格的內(nèi)容7、全選按鈕用于選定整個工作表所有的單元格。1.2建立工作薄1、直接啟動Ｅxcel將建立book1.xls2、建立近似于以前工作薄打開原來的工作薄，立即將其另存為新工作薄3、根據(jù)模板建立工作薄可選擇“新建工作簿”任務(wù)窗格中的“本機(jī)上的模板…”選項(xiàng)，Excel將會彈出一個列有許多模板的對話框1.3工作表操作1.3.1工作表常見操作1．工作表的切換2．插入工作表3．刪除工作表4．移動工作表5．修改工作表標(biāo)簽的名字6．復(fù)制工作表7．修改新工作簿的默認(rèn)工作表個數(shù)1.3工作表操作1.3.2工作表行、列操作選擇行、列選擇單行：用鼠標(biāo)單擊要選擇的行號連接多行：按住Shift，然后單擊第1和最后兩個行號不連接多行：按住Ctrl，依次單擊要選擇的行號列的選擇方法同行的選擇法。

按住Shift單擊行號2、3就選中了！1.3工作表操作刪除行、列插入行、列

調(diào)整行高調(diào)整列寬把鼠標(biāo)移到行號3的下邊線上，當(dāng)指針變成一個黑色“十字架”時，按下并拖動鼠標(biāo)左鍵，就可增、減該行的行高把鼠標(biāo)移到G列的右邊線上，當(dāng)指針變成一個黑色“十字架”時，雙擊鼠標(biāo)左鍵，G列的寬度就會自動進(jìn)行調(diào)整以顯示出那些被遮住的字1.4單元格操作1、選擇單元格單個、連續(xù)與不連續(xù)的多個單元格的選擇2、清除單元格內(nèi)容3、刪除單元格注意單元格清除與刪除的區(qū)別4、插入單元格5、移動單元格6、復(fù)制單元格7、合并單元格1.5配置工作環(huán)境1、定制菜單刪除菜單欄或菜單項(xiàng)增加菜單中的菜單項(xiàng)2、工具欄操作工具欄顯示、隱藏、修改、移動3、Excel文件、工作表操作設(shè)置文件保存的目錄最近打開的文件數(shù)、工作表數(shù)目4、更改文件自動保存的間隔時間1.6查看工作表數(shù)據(jù)1、全屏幕查看工作表數(shù)據(jù)選擇“視圖”|“全屏顯示”菜單命令2、按比例查看工作表數(shù)據(jù)選擇“視圖”|“顯示比例…”菜單項(xiàng)從彈出的對話框中選擇一個顯示比例1.6查看工作表數(shù)據(jù)3、按打印頁面分頁查看工作表數(shù)據(jù)選擇“視圖”|“分頁預(yù)覽”菜單項(xiàng)打印之前，用這種查看方式能發(fā)現(xiàn)一些打印問題。1.6查看工作表數(shù)據(jù)4、在多窗口查看數(shù)據(jù)1、單擊要進(jìn)行窗口分隔的位置。2、選擇“窗口”|“拆分”菜單項(xiàng)。3、拖動分隔線，重新定位各個窗格的大小1.6查看工作表數(shù)據(jù)5、凍結(jié)行列標(biāo)題查看數(shù)據(jù)單擊要凍結(jié)的行、列交叉點(diǎn)下邊的單元格。選擇“窗口”|“凍結(jié)窗口”菜單項(xiàng)。1.7工作表打印1、打印紙及頁面設(shè)置進(jìn)行正式打印之前要確定的幾件事情：①打印紙的大小；②打印的份數(shù)；③是否需要頁眉④是打印指定工作表，還是整個工作簿，或某個工作表的單元格區(qū)域。這些問題可以通過頁面設(shè)置解決。1.7工作表打印頁面設(shè)置的方法選擇“文件”|“頁面設(shè)置”菜單項(xiàng)，在如下對話框中設(shè)置。1.7工作表打印2、打印預(yù)覽（1）打印預(yù)覽的意義打印預(yù)覽是打印稿的屏幕預(yù)現(xiàn)，其效果與最終的打印效果沒有什么區(qū)別。在進(jìn)行工作表的正式打印之前，有必要進(jìn)行打印預(yù)覽。以便及早發(fā)現(xiàn)問題，進(jìn)行頁面和紙張的重新設(shè)置，避免打印出無用的表格。（2）方法單擊常用工具條中的打印預(yù)覽工具按鈕

1.7工作表打印2、打印預(yù)覽（1）打印預(yù)覽的意義打印預(yù)覽是打印稿的屏幕預(yù)現(xiàn)，其效果與最終的打印效果沒有什么區(qū)別。在進(jìn)行工作表的正式打印之前，有必要進(jìn)行打印預(yù)覽。以便及早發(fā)現(xiàn)問題，進(jìn)行頁面和紙張的重新設(shè)置，避免打印出無用的表格。（2）方法單擊常用工具條中的打印預(yù)覽工具按鈕

1.7工作表打印3、縮放打印比例以適應(yīng)打印紙大小比較糟糕的打印情況：只有一兩行（列）的內(nèi)容被打印到了另一頁上！打印內(nèi)容不足一頁，整張打印紙內(nèi)容較少，頁面難看。這些情況可通過打印縮放解決！1.7工作表打印4、打印標(biāo)題或頁碼選擇“視圖”|“頁眉和頁腳”菜單項(xiàng)單擊“自定義頁眉”可輸入頁眉或頁腳的文字。也可以選擇頁碼或日期等1.7工作表打印5、標(biāo)題在工作表的首行如果工作表的首行是標(biāo)題，而且工作表內(nèi)容較多，可能打印多頁?？梢酝ㄟ^頁面設(shè)置，將首行指定為標(biāo)題。1.7工作表打印6、打印整個工作簿或指定工作表區(qū)域如果單擊常用工具欄中的打印圖標(biāo)，Excel只會將當(dāng)前工作表的內(nèi)容打印出來。如果需要打印工作簿中的全部工作表，或打印一個工作表的指定區(qū)域，選擇“文件”|“打印”菜單TheEnd第2章數(shù)據(jù)輸入本章學(xué)習(xí)目標(biāo)1、了解Excel的數(shù)據(jù)類型2、掌握不同類型數(shù)據(jù)的輸入方法3、掌握復(fù)制公式輸入數(shù)據(jù)的方法4、掌握大批量相同數(shù)據(jù)的輸入方法5、掌握通過自定義格式簡少數(shù)據(jù)輸入的方法6、掌握用IF、Vlookup等函數(shù)查找輸入數(shù)據(jù)的方法7、掌握用Rand和int函數(shù)產(chǎn)生大量模擬數(shù)據(jù)的方法。

2.1Excel的數(shù)據(jù)類型1、Excel的常見數(shù)據(jù)類型數(shù)字型、日期型、文本型、邏輯型數(shù)據(jù)數(shù)字型表現(xiàn)形式多樣：貨幣、小數(shù)、百分?jǐn)?shù)、科學(xué)計(jì)數(shù)法、各種編號、郵政編碼、電話號碼等多種形式2.1Excel的數(shù)據(jù)類型2、Excel數(shù)值型數(shù)據(jù)的取值范圍功能最大限制數(shù)字精度15位單元格中可鍵入的最大數(shù)值9.99999999999999E307最大正數(shù)1.79769313486231E308最小負(fù)數(shù)-2.2250738585072E-308最小正數(shù)2.229E-308最大負(fù)數(shù)-2.2250738585073E-3082.2基本數(shù)據(jù)的輸入1、數(shù)據(jù)輸入的一般過程（1）選定要輸入數(shù)據(jù)的單元格。（2）從鍵盤上輸入數(shù)據(jù)。（3）按Enter鍵后2、輸入數(shù)值（1）正數(shù)的輸入：+234，13,333,333，12E3（2）負(fù)數(shù)的輸入：-234，(234)，-12e2（3）分?jǐn)?shù)的輸入:22/3,03/4（4）貨幣數(shù)據(jù)的輸入:￥123，$212.2基本數(shù)據(jù)的輸入3、輸入文本字符文本應(yīng)逐字輸入數(shù)字文本以‘開頭輸入，或用=“數(shù)字”方式輸入。輸入文本32，可輸入：=“32”或輸入：'32文本形式的公式，要通過“插入”|“對象”菜單命令如2.2基本數(shù)據(jù)的輸入4、輸入日期以yy-mm-dd形式，或mm-dd形式輸入通過格式化得到其它形式的日期，這樣可減少輸入格式方法是選擇“格式”|“單元格”|“數(shù)字”|“日期”2.2基本數(shù)據(jù)的輸入5、輸入公式公式是對工作表中的數(shù)值進(jìn)行計(jì)算的等式。公式要以等號（＝）開始。例如在A3輸入：=5+2*3，結(jié)果等于2乘3再加5。A3中將顯示112.3相同數(shù)據(jù)的輸入1、復(fù)制相同數(shù)據(jù)建立具有相同數(shù)據(jù)的不同工作表，可用復(fù)制方法。某班主任建立相同的成績表頭，可用此法。復(fù)制一班表頭2.3相同數(shù)據(jù)的輸入2、填充復(fù)制相同數(shù)據(jù)設(shè)要建立下圖所示“學(xué)生檔案”表。其中入學(xué)時間、班級、系都是相同數(shù)據(jù)。（1）在G2中輸入“計(jì)算機(jī)通信”，然后Enter。（2）單擊G2，G2右下角會出現(xiàn)黑色小方塊，它就是填充柄。（3）向下拖動填充柄，拖過的單元格都被填入了“計(jì)算機(jī)通信”。2.3相同數(shù)據(jù)的輸入3、用Enter輸入相同數(shù)據(jù)設(shè)要建立下圖所示“學(xué)生檔案”表。其中入學(xué)時間、班級、系都是相同數(shù)據(jù)。（1）選中F2：F11（2）輸入：JK001（3）按Ctrl+Enter可用同樣方法輸入C、G列數(shù)據(jù)2.4編號的輸入1、復(fù)制輸入連續(xù)的編號連續(xù)編號（或等差、等比性質(zhì)的數(shù)據(jù)）應(yīng)該采用復(fù)制或序列填充的方式進(jìn)行輸入。比如公職或單位的職工編號、電話號碼、手機(jī)號碼、零件編號等。輸入右圖A列的學(xué)號（1）在A2輸入1003020101

在A3輸入1003020101（2）選中A2：A3（3）向下拖動A3單元格的填充柄。2.4編號的輸入2、填充產(chǎn)生連續(xù)編號填充序列方式適用于輸入較多的連續(xù)編號數(shù)據(jù)。比如要輸入電話號碼：663321240~663328000，如右圖所示，方法如下。在第一個單元格中輸入起始號碼選擇“編輯”|“填充”|“序列”菜單命令。在Excel彈出的“序列”對話框中指定“列”在步長中輸入1在終止值中輸入最后一個號碼。2.4編號的輸入3、利用自定義格式產(chǎn)生特殊編號人們常會遇到各種具有一定規(guī)則的特殊格式的編號。如右圖的保險號。1、選中要輸入保險號的區(qū)域2、選擇“格式”|“單元格”菜單命令。3、選中“自定義”4、輸入自定格式：p00-000-00002.4編號的輸入用自定義格式輸入大數(shù)字編號當(dāng)在單元格中輸入一個位數(shù)較多的數(shù)值時（比如超過15位的整數(shù)），Excel會自動將輸入的數(shù)據(jù)顯示為科學(xué)記數(shù)法，這可能并不符合我們的需要。產(chǎn)生這種問題的原因是數(shù)值精度問題，Excel的默認(rèn)精度是15位，如果一個數(shù)值的長度超過15位，則15位之后的數(shù)字都被視為0。這種位數(shù)較多的大數(shù)字編號，可以用自定義格式進(jìn)行輸入2.4編號的輸入用自定義格式輸入大數(shù)字編號建立右圖所示的電話用戶檔案，其中身份證號為18位，且前15位全部相同。若直接輸入，不僅數(shù)據(jù)多，且要出錯，比如在G4輸入Excel會顯示為5.1021E17解決方法是定義G列保存身份證號的單元格的自定義格式為："51021419601230"0000設(shè)置自定義格式后，只需要輸入身份證的最后4位編號2.4編號的輸入選中D2單元格，選擇“格式”|“單元格”菜單項(xiàng)“類型”文本框中輸入："51021419601230"00002.5組合多個單元格數(shù)據(jù)1、用&運(yùn)算符組合數(shù)據(jù)“&”為字符連接運(yùn)算，其作用是把前后的兩個字符串（也可以是數(shù)值）連接為一個字符串。如“ADKDKD”&“DKA”的結(jié)果為ADKDKDDKA。123&45&678的結(jié)果為“'12345678”設(shè)下圖E列數(shù)據(jù)由C、D列數(shù)據(jù)組合而成。在E2中輸入公式=C2&D2，然后向下復(fù)制！2.5組合多個單元格數(shù)據(jù)2、用&和文本函數(shù)的結(jié)合&常和文本函數(shù)left、right、mid等合用，這幾個函數(shù)都很簡單，其調(diào)用語法如下：left(text,n)right(text,n)mid(textn1,n2)其中：left截取text文本左邊的n個字符；比如left("1234",2)="12"right截取text文本右邊的n個字符；比如right("1234",2)="34"mid從text的第n1字符開始，取n2個字符；比如MID("1234",2,2)="23"2.5組合多個單元格數(shù)據(jù)3、用&和文本函數(shù)的結(jié)合案例某電話公司的繳費(fèi)帳號由身份證號的后15位（全長18位），后接電話號碼構(gòu)成。如下圖所示。用&和文本函數(shù)可以很快建立繳費(fèi)帳號。在I3輸入公式“=Right(G3,17)&H3”，然后向下填充復(fù)制該公式到I列的其余單元格2.6采用下拉列表進(jìn)行數(shù)據(jù)選擇1、下拉列表的適用范圍項(xiàng)目個數(shù)少而規(guī)范的數(shù)據(jù)，比如職稱、工種、單位及產(chǎn)品類型等，這類數(shù)據(jù)適宜采用Excel的“數(shù)據(jù)有效性”檢驗(yàn)方式，以下拉列表的方式輸入。例如：某校教師檔案如右圖所示，其中的職稱列數(shù)據(jù)只能從“講師、教授、副教授、助教”中選擇。這類數(shù)據(jù)用下拉列表輸入，方便而準(zhǔn)確。2.6采用下拉列表進(jìn)行數(shù)據(jù)選擇2、下拉列表建立方法（1）選中要建立下拉列表的單元格區(qū)域。（2）選擇“數(shù)據(jù)”|“有效性”菜單項(xiàng)。（3）選擇“設(shè)置”標(biāo)簽，然后從“允許”下拉列表中選擇“序列”選項(xiàng)。（4）在“來源”文本框中輸入職稱名字“助教,講師,副教授,教授”

2.7利用公式與函數(shù)進(jìn)行查找輸入1、查找輸入的意義如果表格中的不同數(shù)據(jù)列之間存在一定的依賴關(guān)系，那么用IF公式進(jìn)行數(shù)據(jù)的轉(zhuǎn)換輸入是一種效率較高的輸入方法。如果某些數(shù)據(jù)已經(jīng)在其它數(shù)據(jù)表中建立好了，查詢輸入可以提高效率，且能減少輸入錯誤。2.7利用公式與函數(shù)進(jìn)行查找輸入2、IF函數(shù)的應(yīng)用IF函數(shù)又稱為條件函數(shù)，它能夠?qū)o出的條件進(jìn)行判斷，并根據(jù)判斷結(jié)果的正誤執(zhí)行不同的運(yùn)算。IF函數(shù)的調(diào)用形式如下：IF(條件式,條件正確時函數(shù)的取值,條件錯誤時函數(shù)的取值)比如，IF(3>2,“right”,“error”)”的結(jié)果是“right”，而IF(3<2,"right","error")的結(jié)果應(yīng)該是“error”因?yàn)?>2是正確的，函數(shù)的結(jié)果就是“條件正確時的取值”。與之相對應(yīng)，而3<2是錯誤的，所以……。2.7利用公式與函數(shù)進(jìn)行查找輸入IF函數(shù)可以嵌套使用，即在IF函數(shù)中還可以使用IF函數(shù)，最多可以嵌套7層。比如：IF(A2>89,“A”,IF(A2>79,“B”))，如果A2等于93，則該函數(shù)的結(jié)果是“A”；如果A2等于80，則函數(shù)的結(jié)果是內(nèi)層IF(A2>79,“B”)的結(jié)果，顯然80>79，所以結(jié)果為“B”；如果A2等于65呢？函數(shù)的結(jié)果就是IF(A2>79,"B")不成立時的取值，這時該函數(shù)的結(jié)果就是一個空值，因?yàn)镮F(A2>79,"B")沒有給出A2>79不成立時的取值，在這種情況下，Excel將返回一個空值（即什么結(jié)果都沒有！）給該函數(shù)。2.7利用公式與函數(shù)進(jìn)行查找輸入3、IF函數(shù)查找轉(zhuǎn)換案例某學(xué)校年度獎金如下圖所示，獎金根據(jù)職稱確定。教授：2000元，副教授：1500，講師：1000，助教：是500元。在F3輸入公式：=IF(E3="教授",2000,IF(E3="副教授",1500,IF(E3="講師",1000,IF(E3="助教",500))))2.7利用公式與函數(shù)進(jìn)行查找輸入公式含義分析2.7利用公式與函數(shù)進(jìn)行查找輸入4、Vlookup函數(shù)查找Vlookup函數(shù)能夠從其它工作表（或本工作表）將某數(shù)據(jù)列中的數(shù)據(jù)查找出需要的數(shù)據(jù)。VLOOKUP函數(shù)的用法如下：VLOOKUP（x,table,n,r）其中，x是要查找的值，table是一個單元格區(qū)域（即由工作表的1列或多列組成）。Excel將在table的第一列查找有無x這樣的值（只在table的第1列查找有無x）

n是table中待返回的匹配值的列序號。。r可以取0或1，當(dāng)r為0時，該函數(shù)將進(jìn)行精確匹配查找；當(dāng)r=1時，table的第一列應(yīng)按升序排列，否則找到的結(jié)果可能不正確，r=1表示Excel實(shí)行的是近似值查找。2.7利用公式與函數(shù)進(jìn)行查找輸入5、Vlookup函數(shù)查找案例（1）問題描述：某移動公司更換了新系統(tǒng)，使原有的部分電話號碼不能使用，同時又新增加了許多電話號碼。系統(tǒng)變化后，大部分用戶仍然使用原來的用戶檔案（電話號碼和繳費(fèi)合同號都沒有改變），而那些不能使用的電話號碼和新增加的電話號碼則需要重新建立用戶檔案2.7利用公式與函數(shù)進(jìn)行查找輸入（2）帳號查找過程在需要提取帳號的新號碼工作表的B3輸入查找公式，并向下復(fù)制此公式。=VLOOKUP(A3,舊帳號!A$2:$B$6884,2,0)舊帳號是原來的工作表B列是從舊帳號的B列找到的數(shù)據(jù)2.8快速輸入數(shù)據(jù)填充序列1、數(shù)據(jù)序列的含義序列就是一組數(shù)據(jù)，這組數(shù)據(jù)有先后順序，內(nèi)容固定，在應(yīng)用中常常以一組數(shù)據(jù)的整體形式出現(xiàn)。2、Excel內(nèi)置序列對于經(jīng)常使用的一些數(shù)據(jù)序列，比如月份、星期、季度等，Excel已經(jīng)將它們內(nèi)置在系統(tǒng)中，在輸入這些數(shù)據(jù)時，只需要輸入第一個數(shù)據(jù)，其余的可以采用填充復(fù)制的方法由Excel自動產(chǎn)生。2.8快速輸入數(shù)據(jù)填充序列3、自定義序列用戶可以將經(jīng)常使用的數(shù)據(jù)序列添加到Excel系統(tǒng)中，該序列就會擁有與Excel內(nèi)置序列相同功能。比如，輸入該序列的第一項(xiàng)內(nèi)容后，其后各項(xiàng)都可以通過填充復(fù)制產(chǎn)生。案例某大學(xué)中有“計(jì)算機(jī)學(xué)院，通信學(xué)院，經(jīng)濟(jì)管理學(xué)院，法律學(xué)院，中藥學(xué)院，電子技術(shù)學(xué)完”等多所學(xué)院，學(xué)校辦公室經(jīng)常要用到這些名字。將這組數(shù)據(jù)定義為自義序列。2.8快速輸入數(shù)據(jù)填充序列建立方法（1）選擇“工具”|“選項(xiàng)”|“自定義序列”。（2）在“選項(xiàng)”對話框的“輸入序列”中依次輸入序列內(nèi)容。（3）建立輸入序列后，輸入第一項(xiàng)，即“計(jì)算機(jī)學(xué)院”后，通過填充復(fù)制就能產(chǎn)生其它各項(xiàng)內(nèi)容。2.9限定輸入數(shù)據(jù)的長度，顯示提示和出錯信息對輸入到單元格中的數(shù)據(jù)進(jìn)行一定程度的限制，比如限制小數(shù)位數(shù)、日期和時間的范圍、字符的個數(shù)等。當(dāng)輸入的數(shù)據(jù)不符合限定規(guī)則時，Excel就顯示一些警告信息，并且不接受輸入的數(shù)據(jù)。如果工作表的數(shù)據(jù)是由其他人輸入的，則有必要在別人輸入數(shù)據(jù)時給出一些簡要的提示信息，或?qū)?shù)據(jù)輸入的規(guī)則作些說明2.9限定輸入數(shù)據(jù)的長度，顯示提示和出錯信息案例是某俱樂部的會員檔案表，現(xiàn)以此為例說明限定單元格輸入位數(shù)，顯示提示和告警信息的方法。2.10用RAND和INT函數(shù)產(chǎn)生實(shí)驗(yàn)數(shù)據(jù)1、Rand函數(shù)又稱為隨機(jī)函數(shù)，它產(chǎn)生一個介于0~1之間小數(shù)。對Rand函數(shù)的結(jié)果進(jìn)行放大，能產(chǎn)生任意范圍內(nèi)的數(shù)據(jù)。如：RAND()*100

：可以產(chǎn)生0~100之間的數(shù)據(jù)RAND()*50+50

：可以產(chǎn)生50~100之間的數(shù)據(jù)。2、Int函數(shù)又稱取整函數(shù)，將數(shù)字向下舍入到最接近的整數(shù)。比如：INT(8.9)

的結(jié)果是8INT(-8.9)

的結(jié)果是-9

2.10用RAND和INT函數(shù)產(chǎn)生實(shí)驗(yàn)數(shù)據(jù)案例建立如圖所示的學(xué)生成績表，用INT函數(shù)和RAND函數(shù)產(chǎn)生C2:J11區(qū)域的成績，設(shè)成績在20~100之間。(1)選中C2:G11區(qū)域(2)輸入公式：=20+int(rand()*80(3)按Ctrl+Enter說明：用rand產(chǎn)生的數(shù)據(jù)會隨時發(fā)生變化，可將它貼粘為數(shù)據(jù)，它就不會發(fā)生變化了。其方法是：（1）選中C3:G11區(qū)域，然后選擇“復(fù)制”（2）選擇“編輯”|“選擇性貼粘”（3）從彈出的對話框中選擇“數(shù)值”。TheEnd第3章格式化工作表本章學(xué)習(xí)目標(biāo)1、了解Excel格式化的意義2、掌握單元格格式化的不同方法3、掌握文本格式化的方法4、掌握數(shù)據(jù)格式化的方法5、了解自定義格式化數(shù)據(jù)的方法和意義6、掌握日期有格式化方法7、掌握工作表套用格式和自定義格式化方法

3.1設(shè)置單元格數(shù)據(jù)的對齊方式數(shù)據(jù)對齊方式在默認(rèn)情況下，文本靠單元格的左邊對齊，數(shù)值靠單元格的右邊對齊。有時需要特殊的對齊方式，如斜線表頭、旋轉(zhuǎn)字體、垂直居中等例如：3.1設(shè)置單元格數(shù)據(jù)的對齊方式通過跨列居中按扭合并單元格選中要合并的單元格，單擊跨列居中按鈕取消單元格的合并選中要合并的單元格，單擊跨列居中按鈕3、通過菜單命令也可以選擇“格式”|“單元格”菜單，然后在對話框中進(jìn)行設(shè)置。3.1設(shè)置單元格數(shù)據(jù)的對齊方式垂直對齊方式文本、數(shù)字的旋轉(zhuǎn)同一單元格文本換行：Alter+Enter單元格文本自動換行3.2文本的格式設(shè)置文本格式設(shè)置的內(nèi)容文字的設(shè)置大致包括字體、字型、修飾、對齊方式、字體顏色等文本格式設(shè)置的方法選擇“格式”|“單元格”菜單命令選擇彈出對話框中的“字體”，然后就可進(jìn)行各項(xiàng)設(shè)置。3.2文本的格式設(shè)置文本格式化案例3.3格式化數(shù)據(jù)1、Excel的自動數(shù)據(jù)格式數(shù)字、日期、時間等類型的數(shù)據(jù)在Excel中都是以數(shù)字形式存儲的，同樣的數(shù)字在具有不同格式的單元格中會表現(xiàn)出不同的形式。例如在下圖的第3行中，輸入的都是10.3，但結(jié)果卻有很大的不同。3.3格式化數(shù)據(jù)2、使用系統(tǒng)提供的數(shù)據(jù)格式Excel提供了許多數(shù)據(jù)格式，包括數(shù)據(jù)的精確度、顯示方式等內(nèi)容（貨幣：美元、歐元、人民幣，百分比等）。數(shù)據(jù)格式設(shè)置的常見內(nèi)容有以下幾種。數(shù)據(jù)的精確度以百分比方式顯示數(shù)據(jù)數(shù)據(jù)的分節(jié)顯示

數(shù)據(jù)格式設(shè)置工具欄可快速設(shè)置數(shù)據(jù)格式3.3格式化數(shù)據(jù)3、數(shù)據(jù)格式設(shè)置案例——貨幣形式的數(shù)據(jù)3.3格式化數(shù)據(jù)4、自定義格式意義自定義格式允許用戶修改單元格的格式，對單元格中數(shù)據(jù)的顯示形式、字體、字型以及單元格的邊框、背景等內(nèi)容進(jìn)行設(shè)置。通過自定義格式不但可以將單元格數(shù)據(jù)顯示為需要的樣式，而且能夠在輸入數(shù)據(jù)的時候顯示一些重要的提示信息，以減少輸入數(shù)據(jù)的出錯率。此外，通過自定義格式的設(shè)置，還能減少單元格內(nèi)重復(fù)數(shù)據(jù)的輸入。3.3格式化數(shù)據(jù)5、自定義格式的形式格式組成正數(shù)的格式碼；負(fù)數(shù)的格式碼；0的格式碼；文本的格式碼說明①格式代碼中最多可以指定四個節(jié)。各節(jié)以分號分隔，它們順序定義了單元格顯示正數(shù)、負(fù)數(shù)、零和文本時的格式。②如果只指定了兩個節(jié)，則第一部分用于表示正數(shù)和零，第二部分用于表示負(fù)數(shù)。如果只指定一個節(jié)，則所有數(shù)字都會使用該格式。如果要跳過某一節(jié)，則對該節(jié)僅使用分號即可。比如，若將單元格的格式設(shè)置為：#,###.00;;“零”;則當(dāng)在此單元格中輸入負(fù)數(shù)時，輸入的負(fù)數(shù)就不會被顯示出來（被隱藏了）。3.3格式化數(shù)據(jù)③在格式碼中指定的字符被用來作為占位符或格式指示符。0作為一個占位符，它在沒有任何數(shù)字被顯示的位置上顯示一個0。④符號“_)”跟在一個正數(shù)的格式后面，以保證這個正數(shù)右邊會留下一個空格，空格的寬度與圓括號“）”的寬度一樣寬。⑤正數(shù)、負(fù)數(shù)和0靠單元格的右邊線對齊。3.3格式化數(shù)據(jù)自定義格式碼的例子3.3格式化數(shù)據(jù)格式符功能及用法通用格式對未格式化的單元格使用默認(rèn)格式，在列寬許可的情況下，盡可能地顯示較高的精度，在列寬不許可的情況下，使用科學(xué)記數(shù)法顯示數(shù)值#數(shù)字的占位符，不顯示無效的0。如單元格的格式碼為：#,###.##，則1233456.6789將被顯示為1,233,456.68。0234.30將被顯示為234.30作數(shù)字的占位符，如果數(shù)字的位數(shù)少于格式中的零，則顯示無效的零。如單元格的格式碼為：0,000.00，則1233456.6789將被顯示為1,233,456.68。0234.30將被顯示為0,234.30%把輸入的數(shù)字乘以100，然后以百分?jǐn)?shù)的形式顯示出來"text"顯示雙引號內(nèi)的文本。如設(shè)置自定義格式為："33981"0#，輸入1將被顯示為：3398101，輸入22，將被顯示為：3398122[顏色]用指定的顏色格式化單元格內(nèi)容，如在定義了[紅色]的格式的單元格中輸入的內(nèi)容將以紅色顯示[條件]對單元格進(jìn)行條件格式化，允許的條件有：<，>，<=，>=，<>，=6、Excel常用格式碼的含義3.3格式化數(shù)據(jù)7、自定義格式案例3.3格式化數(shù)據(jù)8．自定義格式的方法（1）選中要格式化的單元格。（2）選擇“格式”|“單元格”菜單（3）選擇彈出對話框的“數(shù)字”標(biāo)簽，然后選擇“分類”列表框中的“自定義”選項(xiàng)。（4）在“類型”文本框中，輸入自定義格式。（5）在單元格中輸入數(shù)據(jù)，數(shù)據(jù)將以自定義格式顯示。3.3格式化數(shù)據(jù)【例3-1】用自定義數(shù)字格式輸入相同的編號。定義A列學(xué)號的自定義格式為："329901"003.3格式化數(shù)據(jù)【例3-2】設(shè)置自定義格式，控制用戶輸入設(shè)置姓名列的自定義格式：[紅色]"姓名不能輸入數(shù)字";[紅色]"人名不能是數(shù)字";[紅色]"姓名不能是0"設(shè)置工作年齡列的自定義格式：G/通用格式;[紅色]"工作年齡不能是負(fù)數(shù)";[紅色]0;[紅色]"工作年齡不能是文本"3.3格式化數(shù)據(jù)【例3-3】隱藏不需要的0值。在輸入學(xué)生成績之前，預(yù)先在該列中輸入了一個成績計(jì)算公式“=SUM(C5:E5*0.7)+F5*0.3”。所以G列出現(xiàn)了許多0設(shè)置G列總分區(qū)域的自定義格式為：G/通用格式;"綜合成績不能為負(fù)";

3.3格式化數(shù)據(jù)【例3-4】定義電話、保險號碼的簡化輸入3.4格式化日期1、概述在Excel中，日期和時間實(shí)際上是以序列數(shù)字的形式存儲，以日期形式顯示的。所以當(dāng)一個單元格的格式被設(shè)置為日期格式后，即使在其中輸入純粹的數(shù)字，該數(shù)字也會被顯示為一個日期。3.4格式化日期2、日期的格式碼顯示使用此代碼月份（1~12）M月份（01~12）Mm月份（Jan~Dec）Mmm月份（January~December）Mmmm月份（英語中各月單詞的第一個字母）Mmmmm日（1~31）D日（01~31）Dd日（Sun~Sat）Ddd日（Sunday~Saturday）Dddd年（00~99）Yy年（1900~9999）Yyyy3.4格式化日期3、日期格式化案例3.6條件格式條件格式的意義對于不同的數(shù)據(jù)，可按不同的條件設(shè)置其顯示格式。比如在一個學(xué)生成績表中，把所有不及格的成績顯示為紅色，就能夠很容易找到有不及格科目的學(xué)生。下面是條件格式運(yùn)用的一個例子：當(dāng)單元格的數(shù)值大于200時，用黑色顯示；當(dāng)小于100時用紅色顯示；100~200之間的數(shù)據(jù)用藍(lán)色顯示。將該單元格的格式設(shè)置為如下的自定義數(shù)字格式：[黑色][>=200]#,###;[紅色][<=100]#,###;[藍(lán)色]#,###

3.6條件格式2、條件設(shè)置案例某公司的工資表，為了查看電話費(fèi)的補(bǔ)貼情況，用條件格式將電話費(fèi)在2000元以上的單元格設(shè)置成了紅字和淺綠色的背景；將1000元~2000元之間的單元格設(shè)置成了黑字和淺藍(lán)色的背景；將1000元以下的單元格設(shè)置成了深藍(lán)色的字體和淺青色的背景。3.6條件格式3、案例結(jié)果：E列數(shù)據(jù)3.7工作表的格式化1、自動套用格式Excel提供了10多種已設(shè)置好數(shù)據(jù)顯示樣式的工作表，用戶可在工作表中直接套用這些樣式，美化工作表，并突出顯示這些工作表中的數(shù)據(jù)。一個普通的工作表3.7工作表的格式化上述普通工作表的缺點(diǎn)表名不醒目。表的標(biāo)題不突出（第3行）。當(dāng)職工較多時，看數(shù)據(jù)較困難。上、下行數(shù)據(jù)易混淆，如在看F7單元格的數(shù)據(jù)時，易看成F6或F8的數(shù)據(jù)。整個表不太美觀。自動套用Excel內(nèi)置某種格式后的工作表3.7工作表的格式化2、設(shè)置工作表的邊框和底紋Excel的默認(rèn)邊框是虛線、無底紋，所有的表格都表示為同一種樣式。如果直接在Excel下進(jìn)行表格的打印則沒有網(wǎng)格線（當(dāng)然，可通過打印設(shè)置讓它顯示表格線），不便于特殊信息的突出顯示。通過邊框和底紋的設(shè)置，一方面可使表格美觀，同時也可改變信息的顯示方式，讓人們盡快掌握表中的信息。3.7工作表的格式化設(shè)置邊框和底紋后的工作表3.7工作表的格式化3、取消工作表的網(wǎng)格線（1）選擇“工具”|“選項(xiàng)”菜單命令。（2）選中“視圖”去掉“網(wǎng)格線”復(fù)選框中的“√”3.7工作表的格式化4、設(shè)置三維表格通過對單元格的格式設(shè)置（自定義格式），可以創(chuàng)建上凸和下凹的立體效果，數(shù)據(jù)顯示在這樣的表格中，看起來更輕松。

三維表格示例3.7工作表的格式化三維表格的設(shè)置方法。（1）取消工作表的網(wǎng)絡(luò)線。（2）把要設(shè)置三維效果的表格區(qū)域的底紋設(shè)置為淺灰色（一般情況下是把整個工作表的底紋都設(shè)置為淺灰色。當(dāng)然，其他色彩的淺色也可以，但淺灰色的立體視覺效果更佳）。3.8運(yùn)用模板1、模板概述模板即模型、樣板，它是一種特殊的工作簿（工作簿模板）或工作表（工作表模板），在模板中已經(jīng)設(shè)置好許多格式，如一個工作簿包含多少個工作表，工作表中單元格的格式，各種標(biāo)題的字號和大小等內(nèi)容。在需要的時候，可以根據(jù)模板來創(chuàng)建新的工作表，新工作表具有與模板相同的表格樣式。在Excel中模板文件的擴(kuò)展名是“.xlt”3.8運(yùn)用模板2、使用系統(tǒng)的默認(rèn)模板Excel提供的內(nèi)置模板3.8運(yùn)用模板3、內(nèi)置模板應(yīng)用案例建立下圖所示的職工考勤表。3.8運(yùn)用模板職工考勤表的建立方法選擇“新建工作簿”任務(wù)窗格”中的新建命令。如果任務(wù)窗格沒有顯示出來，可以選擇“視圖”菜單項(xiàng)中的“任務(wù)窗格”選擇任務(wù)窗格中的“本機(jī)上的模板”選項(xiàng)，Excel將會彈出“模板”對話框，選擇該對話框中的“電子表格方案”標(biāo)簽，然后從下面的列表框中選擇“考勤記錄”。3.8運(yùn)用模板4、建立自定義模板對于經(jīng)常重復(fù)使用的工作表，可以將它們設(shè)置為模板，在需要的時候就可根據(jù)它來創(chuàng)建工作簿。模板的建立過程與一個普通工作簿的建立過程基本相同。建立自定義模板案例某單位辦公管理人員（財(cái)務(wù)管理人員）經(jīng)常與差旅報(bào)銷單打交道，要用到一張如下圖所示的差旅報(bào)銷單。3.8運(yùn)用模板差旅報(bào)銷單3.8運(yùn)用模板差旅報(bào)銷單模板的建立（1）建立一個新的Excel工作簿，并在其中的一個工作表中輸入表格內(nèi)容。注意，在“合計(jì)”后面的單元格即E20中輸入公式“==SUM(I16:I19)*100”。因?yàn)镮16：I19單元格區(qū)域中的金額單位是元，所以該公式中對匯總金額乘以100，將它轉(zhuǎn)換為分，便于將它設(shè)置為大寫金額的元角分。（2）設(shè)置單元格的格式，包括邊框、底紋、字型、字號、必要的單元格合并等。（3）設(shè)置E20單元格的自定義格式為：[DBNum2]#"萬"#"仟"#"佰"#"拾"#"元"0"角"0"分"3.8運(yùn)用模板（4）選擇“另存為”按鈕，會見到“另存為”對話框。（5）從“保存類型”的列表中選擇“模板（*.xlt）”TheEnd第4章公式本章學(xué)習(xí)目標(biāo)1、理會Excel公式的意義2、理解引用的形式和用法3、掌握數(shù)組公式的用法4、掌握名字及其應(yīng)用4.1公式、運(yùn)算符１、公式Excel的公式由運(yùn)算符、數(shù)值、字符串、變量和函數(shù)組成。公式必須以等號“=”開頭，即在Excel的單元格中，凡是以等號開頭的輸入數(shù)據(jù)都被認(rèn)為是公式。在等號的后面可以跟數(shù)值、運(yùn)算符、變量或函數(shù)，在公式中還可以使用括號。例如：=10+4*6/2+(2+1)*50就是一個公式，可以在任何單元格中輸入此公式．Excel會把公式的計(jì)算結(jié)果顯示在相應(yīng)的單元格中．4.1公式、運(yùn)算符2、運(yùn)算符在公式中可以使用運(yùn)算符。Excel中的運(yùn)算符運(yùn)算符運(yùn)算功能優(yōu)先級()括號1-負(fù)號2%百分號3^乘方4*與/乘、除法5+與-加、減法6&文本連接7=、<、>、<=、>=、<>等于、小于、大于、小于等于、大于等于、不等于84.1公式、運(yùn)算符說明：括號的運(yùn)算級別最高，在Excel的公式中只能使用小括號，無中括號和大括號。小括號可以嵌套使用，當(dāng)有多重小括號時，最內(nèi)層的表達(dá)式優(yōu)先運(yùn)算。同等級別的運(yùn)算符從左到右依次進(jìn)行?！?”為字符連接運(yùn)算，其作用是把前后的兩個字符串連接為一串。例如，"ABC"&"DEF"的結(jié)果為“ABCDEF”，"財(cái)務(wù)"&"經(jīng)濟(jì)"的結(jié)果為“財(cái)務(wù)經(jīng)濟(jì)”。4.2引用1、引用的概念在公式中用到了其他單元格在表格中的位置。引用的作用在于標(biāo)識工作表中的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的單元格位置。引用不同工作簿中的單元格稱為鏈接。2、引用的類型A1引用RC引用4.2引用3、相對引用相對引用也稱為相對地址引用，是指在一個公式中直接用單元格的列標(biāo)與行號來取用某個單元格中的內(nèi)容。4.2引用相對引用的例子某超市3月份的進(jìn)貨單如下圖所示，計(jì)算“金額”。在G3計(jì)算公式=C3*D3*F3然后向下復(fù)制該公式！4.2引用4、絕對引用絕對引用總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對引用保持不變。

絕對引用的形式是在引用單元格的列號與行號前面加“$”符號。比如，$A$1就是對A1單元格的絕對引用。4.2引用絕對引用的案例假設(shè)LG空調(diào)在重慶各商場的銷售價格相同，重慶各商場的銷售數(shù)據(jù)如下圖所示。計(jì)算各商場的銷售總額。在E8輸入公式：=$c$5*C8然后向下復(fù)制此公式！4.2引用5、混合引用混合引用具有絕對列和相對行，或是絕對行和相對列。比如，$A1、$B1案例4.2引用6、內(nèi)部引用與外部引用1）引用相同工作表中的單元格，例：

=G3+G5+G10*102）引用同一工作簿的不同工作表中的單元格，例

=Sheet1!G3+Sheet1!G5+Sheet1!E273）引用不同工作簿中的單元格，例：

=[Book1]Sheet1!$IL$4+[Book1]Sheet2!$E$74）同一公式中存在幾中不同的引用，例：

=[Book1]Sheet1!$A$4+Sheet1!G7+F94.2引用7、公式的使用中的錯誤信息錯誤值錯誤原因########單元格所含的數(shù)字、日期或時間比單元格寬，或者單元格的日期、時間公式產(chǎn)生了一個負(fù)值，就會產(chǎn)生#####錯誤。#VALUE!1.在需要數(shù)字或邏輯值時輸入了文本，Excel不能將文本轉(zhuǎn)換為正確的數(shù)據(jù)類型。2.輸入或編輯數(shù)組公式時，按了Enter鍵。3.把單元格引用、公式或函數(shù)作為數(shù)組常量輸入。#DIV/O!1.輸入的公式中包含明顯的除數(shù)為零（0）#NAME?1.在公式中輸入文本時沒有使用雙引號。但這些文本又不是名字。2.函數(shù)名的拼寫錯誤。3.刪除了公式中使用的名稱。4.名字拼寫有錯#N/A1.在調(diào)用函數(shù)時參數(shù)。2.在數(shù)組的參數(shù)的行數(shù)或列數(shù)與包含數(shù)組公式的區(qū)域的行數(shù)或列數(shù)不一致。3.在沒有排序的數(shù)據(jù)表中使用了VLOOKUP、HLOOKUP或MATCH工作表函數(shù)查找數(shù)值#REF!刪除了公式中所引用的單元或單元格區(qū)域#NUM!1.由公式產(chǎn)生的數(shù)字太大或太小，Excel不能表示。2.在需要數(shù)字參數(shù)的函數(shù)中使用了非數(shù)字參數(shù)#NULL!

在公式的兩個區(qū)域中加入了空格從而求交叉區(qū)域，但實(shí)際上這兩個區(qū)域無重迭區(qū)域4.3數(shù)組公式及其應(yīng)用1、數(shù)組公式的含義數(shù)組公式能夠?qū)山M或兩組以上的數(shù)據(jù)（兩個或兩個以上的單元格區(qū)域）同時進(jìn)行計(jì)算。在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個數(shù)據(jù)區(qū)域，也可以是數(shù)組常量。2、數(shù)組公式的建立方法（1）選中需要保存數(shù)組公式結(jié)果的單元格或單元格區(qū)域。（2）輸入公式的內(nèi)容。（3）按Ctrl+Shift+Enter鍵。4.3數(shù)組公式及其應(yīng)用3、用數(shù)組公式計(jì)算兩個數(shù)據(jù)區(qū)域的乘積【例4-1】某超市3月份進(jìn)購的產(chǎn)品圖4-5所示，已知各種產(chǎn)品的數(shù)量、單價和折扣，要計(jì)算各種產(chǎn)品的應(yīng)付金額。4.3數(shù)組公式及其應(yīng)用4、數(shù)組擴(kuò)展在公式中用數(shù)組作為參數(shù)時，所有的數(shù)組必須是同維的。如果數(shù)組參數(shù)或數(shù)組區(qū)域的維數(shù)不匹配，Excel會自動擴(kuò)展該參數(shù)。比如，某商場29寸電視機(jī)售價為3128元，有一個10天的銷售記錄，如下圖的B、C兩列所示?，F(xiàn)要計(jì)算每天的銷售額。4.3數(shù)組公式及其應(yīng)用1、選中F8:F14；2、輸入公式：=C8:C14*C53、按Ctrl+Shift+Enter4.3數(shù)組公式及其應(yīng)用5、二維數(shù)組涉及許多行(或列)數(shù)據(jù)處理的數(shù)組公式，就是所謂的二維數(shù)組。合理地運(yùn)用二維數(shù)組的運(yùn)算功能，會提高數(shù)據(jù)處理的能力，有時在不同工作表之間進(jìn)行數(shù)據(jù)匯總時會特別有效。4.3數(shù)組公式及其應(yīng)用案例某食品商店將每個月的食品銷售資料統(tǒng)計(jì)在一個工作表中，1～6月的銷售數(shù)據(jù)分別記錄在Book1工作簿的Sheet1～Sheet6中，現(xiàn)在要統(tǒng)計(jì)每種商品前6個月的銷售總量和銷售金額。見下圖：4.4名字及其應(yīng)用1、名字概述

可以給一個單元格或單元格區(qū)域取一個名字，這個名字可以出現(xiàn)在公式中，用來指代引用的單元格或單元格區(qū)域，這會使公式的意義更加明確。名字其實(shí)是一個標(biāo)識符，由字母或下劃線開頭的一個或多個字符組成。在Excel中，還可以用漢字字符作為名字。名字不區(qū)分字母的大小寫，比如ABC、abc、Abc都是同一個名字?？梢詾橥粏卧窕騿卧駞^(qū)域定義多個不同的名字。名字一旦定義就可以在同一工作薄的不同工作表之間共用。4.4名字及其應(yīng)用2、名字的意義在Excel中，同一個工作簿中的名字是共享的，在一個工作表中定義的名字，可以被另一個工作表的公式引用，這使公式具有更強(qiáng)大的功能，它能夠利用單元格的名字在不同的工作表中查找到正確的單元格或單元格區(qū)域。名字給單元格的引用帶來了許多方便，因?yàn)樗谷藗儾槐赜涀卧裨诠ぷ鞅碇械囊梦恢茫妹志湍苷业剿?.4名字及其應(yīng)用名字應(yīng)用案例在下圖中，B、C列是某商場所有產(chǎn)品的當(dāng)日單價，E、F列是產(chǎn)品當(dāng)天的銷售記錄。銷售的產(chǎn)品多達(dá)幾百種，而每天則可能只賣掉其中的幾十種產(chǎn)品，現(xiàn)在要計(jì)算當(dāng)日產(chǎn)品的銷售金額。若不用名字，單價要從C列的幾百個數(shù)據(jù)中去查找！

4.4名字及其應(yīng)用3、名字的定義在Excel中，可以為一個獨(dú)立的單元格、連續(xù)的單元格區(qū)域或許多不連續(xù)的單元格構(gòu)成的單元格組合定義一個名字（或多個名字）定義名字有多種方法，如定義、粘貼、指定或標(biāo)志等4.4名字及其應(yīng)用4、定義單元格或單元格區(qū)域的名字D3單元格的名字定義方法如下：4.4名字及其應(yīng)用（1）選擇“插入”|“名稱”|“定義”菜單命令.（2）在彈出的“定義名字”對話框的“引用位置”輸入要定義名字的單元格引用位置。（3）在“在當(dāng)前工作簿中的名稱”中輸入名字4.4名字及其應(yīng)用5、將工作表的首行（或首列）定義為名字“指定”的方式一次可定義多個名字：可以將表格的首行或首列指定為相應(yīng)的列或行的名字。方法（1）選中要指定名字的單元格區(qū)域。（2）選擇“插入”|“名稱”|“指定”菜單項(xiàng)。（3）在“指定名稱”對話框中，選中“首行”、“最左列”的復(fù)選框。這樣就把該表的首行、首列指定成了相應(yīng)的列、行的名字4.4名字及其應(yīng)用指定首行、首列為名字的案例4.4名字及其應(yīng)用6、名字的應(yīng)用1）通過名字在不同的工作表之間傳遞數(shù)據(jù)

通過名字查找蔬菜單價指定A2:B12最左邊為名字4.4名字及其應(yīng)用2）名字與Indirect函數(shù)結(jié)合應(yīng)用Indirect函數(shù)與名字相結(jié)合，可使數(shù)據(jù)查找更方便。Indirect函數(shù)的用法是：Indirect(x)其中的X可以是單元格引用或名字。若X為單元格引用，它必須用“”引起來，如：=INDIRECT(“A1”)，將返回A1單元格中的內(nèi)容若X為單元格名字，它將返回該名字所對應(yīng)的單元格的內(nèi)容。4.4名字及其應(yīng)用某蔬菜商在一個工作表中保存蔬菜的單價，在另一工作表中保存銷售記錄，現(xiàn)要查找銷售記錄表中各蔬菜的單價。指定A2:B11區(qū)域的“最左列”為名字在D2中輸入公式：=INDIRECT(B2)向下復(fù)制該公式！4.4名字及其應(yīng)用3．應(yīng)用行列交叉點(diǎn)查找數(shù)據(jù)所謂行列交叉點(diǎn)指的是由[行的名字列的名字]確定的單元格。把一個工作表的首行、首列指定為名字之后，可以使用名字引用行、列交叉點(diǎn)對應(yīng)單元格中的數(shù)據(jù)。案例某電信公司的電話用戶檔案，其中有成千上萬個電話用戶的電話號碼。如果知道某個用戶的名字，那么要在這樣的表中查找該用戶的電話號碼、電話類型，或申請日期等數(shù)據(jù)就顯得很困難，因?yàn)楸碇械馁Y料太多了。通過行、列交叉點(diǎn)的名字引用能很輕松地找到所需的數(shù)據(jù)。4.4名字及其應(yīng)用1指定A2:E17的“首行，最左列”為名字。2、在任空白單元格中輸入“客戶名字庫第二行任一單元格標(biāo)題”，就能找到該客戶的對應(yīng)數(shù)據(jù)。TheEnd第5章函數(shù)本章學(xué)習(xí)目標(biāo)1、了解函數(shù)極其調(diào)用方法2、掌握常用工作表函數(shù)的用法3、掌握常見統(tǒng)計(jì)函數(shù)的用法4、掌握條件函數(shù)的用法，特別是：If\sumif\countif\等5、掌握解查找引用類函數(shù)的用法6、掌握常用財(cái)務(wù)函數(shù)的用法

5.1函數(shù)簡介1、函數(shù)的概念函數(shù)是能夠完成特定功能的程序。在Excel中，它是系統(tǒng)預(yù)定義的一些公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算,然后把計(jì)算的結(jié)果存放在某個單元格中。在大多數(shù)情況下，函數(shù)的計(jì)算結(jié)果是數(shù)值。當(dāng)然，它也可以返回文本、引用、邏輯值、數(shù)組或工作表的信息5.1函數(shù)簡介2、Excel函數(shù)分類分類功能簡介數(shù)據(jù)庫函數(shù)對數(shù)據(jù)清單中的數(shù)據(jù)進(jìn)行分析、查找、計(jì)算等日期與時間對日期和時間進(jìn)行計(jì)算、設(shè)置及格式化處理工程函數(shù)用于工程數(shù)據(jù)分析與處理信息函數(shù)對單元格或公式中數(shù)據(jù)類型進(jìn)行判定財(cái)務(wù)函數(shù)進(jìn)行財(cái)務(wù)分析及財(cái)務(wù)數(shù)據(jù)的計(jì)算邏輯函數(shù)進(jìn)行邏輯判定、條件檢查統(tǒng)計(jì)函數(shù)對工作表數(shù)據(jù)進(jìn)行統(tǒng)計(jì)、分析查找函數(shù)查找特定的數(shù)據(jù)或引用公式中的特定信息文本函數(shù)對公式、單格中的字符、文本進(jìn)行格式化或運(yùn)算數(shù)學(xué)函數(shù)進(jìn)行數(shù)學(xué)計(jì)算等外部函數(shù)進(jìn)行外部函數(shù)調(diào)用及數(shù)據(jù)庫的鏈接查詢等功能自定義函數(shù)用戶用vba編寫，用于完成特定功能的函數(shù)5.1函數(shù)簡介3、函數(shù)調(diào)用函數(shù)的語法函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)3,…)在公式中調(diào)用函數(shù)

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0)嵌套函數(shù)5.1函數(shù)簡介4、函數(shù)輸入在單元格的公式中直接輸入函數(shù)調(diào)用函數(shù)調(diào)用向?qū)?、Excel的幫助系統(tǒng)5.2常用工作表函數(shù)1、案例某班學(xué)生成績表如圖所示，統(tǒng)計(jì)出其中的平均分、總分、參考人數(shù)、等級及缺考人數(shù)等數(shù)據(jù)。該工作表要用到：if,average,sum,count,countif，max,min等函數(shù)5.2常用工作表函數(shù)2、條件函數(shù)IF用法IF(條件,表達(dá)式1,表達(dá)式2)。功能當(dāng)條件成立時，計(jì)算出表達(dá)式1的值；當(dāng)條件不成立時，計(jì)算出表達(dá)式2的值事例If(A1>60,“及格了”,“不及格”)，若A1單元格的值是76，則該函數(shù)的結(jié)果是：及格了；若A1單元格的值是50，則該函數(shù)的結(jié)果是:不及格。IF函數(shù)的嵌套調(diào)用5.2常用工作表函數(shù)3、匯總求和函數(shù)自動求和按鈕圖示工作表中的所有匯總數(shù)據(jù)可用自動和按鈕計(jì)算出來！5.2常用工作表函數(shù)4、SUM函數(shù)用法

SUM(x1,x2,…,x30)x1，x2，…，x30是需要求和的參數(shù)，可以是數(shù)據(jù)或單元格區(qū)域功能計(jì)算各參數(shù)的數(shù)值之和說明：①參數(shù)表中的數(shù)字、邏輯值及數(shù)字的文本表達(dá)式將被計(jì)算。例如，SUM(3,2)=5，SUM("9",20,true)=30。因?yàn)槲谋局当晦D(zhuǎn)換成數(shù)字，而邏輯值“true”被轉(zhuǎn)換成數(shù)字1。5.2常用工作表函數(shù)②如果參數(shù)為數(shù)組或引用，那么只有其中的數(shù)字被計(jì)算。數(shù)組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。例如，設(shè)A1的值為“9”，A2為true，則公式SUM(A1,A2,20)的計(jì)算結(jié)果為20，而不是30。因?yàn)楸竟街邪▋蓚€引用A1、A2，而A1的值為文本，A2的值為邏輯值，它們在計(jì)算時被忽略，最終就只有一個數(shù)值20參與運(yùn)算。③SUM函數(shù)的參數(shù)最多可達(dá)30個，不同類型的參數(shù)可以同時出現(xiàn)。例如，A2:E2包含5、15、30、40、50、a3的值為10，則SUM(A2:C2,A3)=60，SUM(B2:E2,15)=150，SUM(A2:D2,{1,2,3,4},A3,10)=110。5.2常用工作表函數(shù)5、條件求和函數(shù)SUMIF用法SUMIF(range,criteria,sum_range)其中，range是用于條件判斷的單元格區(qū)域，criteria條件，其形式可以為數(shù)字、表達(dá)式或文本；sum_range是需求和的實(shí)際單元格。只有當(dāng)range中的相應(yīng)單元格滿足條件時，才對sum_range中的單元格求和。若省略sum_range，則直接對range中的單元格求和。功能對range單元格區(qū)域中的數(shù)據(jù)進(jìn)行Criteria條件檢查，然后對滿足條件行的sum_range同行進(jìn)行求和。5.2常用工作表函數(shù)案例某商場的銷售記錄如圖所示。如果統(tǒng)計(jì)各類商品的總銷售額。彩電的銷售總額：=SUMIF(C$3:C$13,"彩電",F$3:F$13)5.2常用工作表函數(shù)6、平均值函數(shù)AVERAGE，AVERAGEA用法AVERAGE(n1，n2，…，n30)AVERAGEA(n1，n2，…，n30)其中，n1，n2，……，n30是要計(jì)算平均值的參數(shù)，該函數(shù)最多允許有30個參數(shù)。參數(shù)可以是數(shù)字，或者是涉及數(shù)字的名稱、數(shù)組或引用功能兩函數(shù)都是求參數(shù)的平均值。AverAge不對文本和邏輯值類數(shù)據(jù)進(jìn)行計(jì)算；AverageA要對文本和邏輯函數(shù)進(jìn)行平均值計(jì)算：文本被視為0，邏輯值true被視為1，false被視為0，空文本（""）也作為0計(jì)算5.2常用工作表函數(shù)案例下圖說明AVERAGE函數(shù)與AVERAGEA函數(shù)的區(qū)別5.2常用工作表函數(shù)7、計(jì)數(shù)函數(shù)COUNT、COUNTA、COUNTBLANK、COUNTIF用法COUNT(v1,v2,…)COUNTA(v1,v2,…)COUNTBLANK(range)COUNTIF(range,criteria)功能Count統(tǒng)計(jì)數(shù)字的個數(shù)CountA統(tǒng)計(jì)數(shù)字和文本的個數(shù)CountBlank統(tǒng)計(jì)空白單元格的個數(shù)Countif統(tǒng)計(jì)滿足條件Criteria的單元格個數(shù)5.2常用工作表函數(shù)8、求最大值、最小值函數(shù)MAX，MIN用法MAX(number1,number2,...)MIN(number1,number2,...)其中number1,number2,...

是要從中找出最大值或最小值的1到30個數(shù)字參數(shù)，也可以是單元格或單元格區(qū)域的引用。功能MAX函數(shù)返回一組值中的最大值，MIN函數(shù)返回一組值中的最小值5.2常用工作表函數(shù)本節(jié)案例問題的解決5.3邏輯函數(shù)1．比較運(yùn)算比較運(yùn)算就是人們常說的比較式，又稱關(guān)系運(yùn)算。比較運(yùn)算只有兩種不同的結(jié)果，要么“正確”，要么“錯誤”，不可能有第三種結(jié)果。比較運(yùn)算符含義示例=等于a1=3>大于a1>b1<小于a1<1>=大于等于a1>=60<=小于等于a1<=”dd”<>不等于a1<>05.3邏輯函數(shù)2．邏輯運(yùn)算AND(l1,l2,…)OR(l1,l2,…)NOT(logical)TRUE()FALSE()其中，l1,l2是關(guān)系式或邏輯值。5.3邏輯函數(shù)案例某單位有3000名職工，要按其工資繳納個人收入所得稅，稅率與工資的關(guān)系如表所示。該單位的職工表如下所示，用IF函數(shù)求出各職工的稅率！工資稅率3000以上稅率=30%2500~3000稅率=25%2000~2500稅率=20%1500~2000稅率=15%1200~1500稅率=10%1000~1200稅率=8%800~1000稅率=5%800以下稅率=05.3邏輯函數(shù)在E4中輸入編輯欄中的公式！5.4數(shù)學(xué)和三角函數(shù)Excel提供了許多數(shù)學(xué)和三角函數(shù)，它們能夠完成大多數(shù)數(shù)學(xué)和三角運(yùn)算，這些函數(shù)可以在公式中直接引用，然后將公式的計(jì)算結(jié)果返回到輸入公式的單元格中。常見的數(shù)學(xué)函數(shù)MOD、TRUNC、ABS、SQRT、SIN、ASIN(n1)，ACOS(n1)，ATAN(n2)、EXP(n)、POWER(x,n)、LN(n)、FACT(n)、LOG(n,base)、MINVERSE(array)、MMULT(array1,array2)……5.5日期及時間函數(shù)

1、Excel處理日期的方式Excel將日期存儲為一系列連續(xù)的序列數(shù)，將時間存儲為小數(shù)。例如，37260.73表示2002-1-45:36PM，在這個數(shù)中小數(shù)點(diǎn)前面的數(shù)字37260表示日期2002-1-4，小數(shù)點(diǎn)后的數(shù)字73表示時間，即下午5點(diǎn)36分。日期和時間都是數(shù)值，因此它們也可以進(jìn)行各種運(yùn)算。如果要計(jì)算兩個日期之間的差值，可以用一個日期減去另一個日期。5.5日期及時間函數(shù)1．DATE函數(shù)用法DATE(year,month,day)功能DATE函數(shù)利用所給的參數(shù)，構(gòu)造一個日期序列數(shù)例如DATE(2005,3,21)的結(jié)果是2005-3-215.5日期及時間函數(shù)2．YEAR，MONTH，DAY函數(shù)用法YEAR(serial_number)MONTH(serial_number)DAY(serial_number)其中的serial_number是一個日期或數(shù)字。功能YEAR函數(shù)返回某日期的年份。MONTH函數(shù)返回以系列數(shù)表示的日期中的月份。DAY函數(shù)返回以系列數(shù)表示的某日期的天數(shù)，用整數(shù)1~31表示。5.5日期及時間函數(shù)3．TODAY、NOW函數(shù)用法TODAY()NOW()功能TODAY函數(shù)返回系統(tǒng)的當(dāng)前日期。NOW函數(shù)計(jì)算當(dāng)前日期和時間。5.5日期及時間函數(shù)5．WEEKDAY用法WEEKDAY(serial_number,return_type)其中：serial_number代表要查找的日期，或日期的系列數(shù)，以了解該日期為星期幾；return_type確定返回值類型的數(shù)字，。功能WEEKDAY計(jì)算給定的日期是星期幾5.5日期及時間函數(shù)NETWORKDAYS函數(shù)用法NETWORKDAYS(start_date,end_date,holidays)其中start_date表示代表開始日期，end_date為終止日期，holidays表示不在工作日歷中的一個或多個日期所構(gòu)成的可選區(qū)域，如元旦節(jié)、五一節(jié)、春節(jié)。5.5日期及時間函數(shù)案例某建筑公司按天數(shù)計(jì)算每個民工的報(bào)酬，民工每個星期工作5天，工作時間和請假時間如圖的A~H列所示，I列是每天的報(bào)酬，用日期函數(shù)計(jì)算每個民工的工作天數(shù)和總工資。5.6查找及引用函數(shù)概述查找引用函數(shù)能通過地址、行、列對工作表的單元格進(jìn)行訪問，也可通過這些函數(shù)從單元格的地址中求出其所在的行或列，進(jìn)而查獲更多的信息。當(dāng)需要從一個工作表查詢特定的值、單元格內(nèi)容、格式或選擇單元格區(qū)域時，這類函數(shù)特別有用。5.6查找及引用函數(shù)1．ADDRESS函數(shù)用法ADDRESS(row,col,abs_num,a1,sheet_text)其中row_num是引用中使用的行號；column_num是引用中使用的列標(biāo)；abs_num指明返回的單元格引用類型sheet_text為一文本，指明作為外部引用的工作表的名稱，如果省略sheet_text，則不使用任何工作表名。功能用指定的行號和列標(biāo)，建立文本類型的單元格地址

例如：ADDRESS(6,3)=“$C$6”ADDRESS(6,1,2)=A$6DDRESS(2,3,1,false,"[book1]sheet1")=[book1]sheet1!r2c3

5.6查找及引用函數(shù)2．choose函數(shù)用法CHOOSE(N,V1,V2,...)功能利用索引N從參數(shù)清單V1,V2,...中選擇需要的數(shù)值。例如：CHOOSE(5,“一月”,“二月”,“三月”,“四月”,“五月”,“六月”)=“五月”；SUM(A1:CHOOSE(3,A10,A20,A30))=SUM(A1:A30)

CHOOSE(B5,"nails","screws","nuts","bolts")="nuts"

5.6查找及引用函數(shù)4．LOOKUP函數(shù)用法LOOKUP(value,r1,r2)其中value是要查找的數(shù)值，可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。r1為只包含一行或一列的區(qū)域，其值可以為文本、數(shù)字或邏輯值。r2為只包含一行或一列的區(qū)域，其大小必須與r1相同

功能該函數(shù)在r1所在的行或列中查找值為value的單元格，然后返回r2中與r1同行或同列的單元格中的值。5.6查找及引用函數(shù)5、HLOOKUP函數(shù)用法HLOOKUP(value,table,n,range_lookup)說明value為需要在數(shù)據(jù)表第一行中進(jìn)行的查找的數(shù)值，它可以是數(shù)值、引用或文字串。table是需要在其中查找數(shù)據(jù)的數(shù)據(jù)表、可以為對區(qū)域或區(qū)域名稱的引用。它的第一行的數(shù)值可以為文本、數(shù)字或邏輯值。如果range_lookup為1，則進(jìn)行近似值查找，且table第一行的數(shù)值必須按升序排列；如果range_lookup為0，則進(jìn)行精確查找，且table不必進(jìn)行排序。功能在數(shù)據(jù)清單（或區(qū)域）table的第一行中查找值為value的單元格，如果找到，則返回該列第n行單元格的值。

5.6查找及引用函數(shù)案例5.6查找及引用函數(shù)6．MATCH函數(shù)用法MATCH(value,array,type)其中value是在array中查找的數(shù)值。array是包含要查找數(shù)據(jù)的區(qū)域，數(shù)組type為數(shù)字-1、0或1

功能在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置取值函數(shù)功能-1array必須按降序排列，查找大于或等于value的最小數(shù)值0array不必排序，查找等于value的第一個數(shù)值，精確查找1array必須按升序排列，查找小于或等于value的最大數(shù)值5.6查找及引用函數(shù)6、INDEX函數(shù)用法INDEX(array,row_num,column_num)其中array

是單元格區(qū)域或數(shù)組常量。row_num

是array中某行的行號，column_num是array中某列的列號。

功能index函數(shù)返回array中row_num和column_num交叉處的單元格引用。5.6查找及引用函數(shù)小結(jié)數(shù)據(jù)查找是Excel辦公應(yīng)用中最常見、最重要的問題，方法不當(dāng)，本來幾分種就能解決的問題，可能要弄幾小時，甚至幾天?？偟恼f來，在工作中應(yīng)用最廣、作用最大的查找函數(shù)是VLOOKUP。在查找數(shù)據(jù)的案例集合.xls工作薄中包括了我?guī)啄暌詠韽膶?shí)際辦公應(yīng)用中收集到的數(shù)據(jù)查找案例。5.7財(cái)務(wù)函數(shù)Excel提供了許多有關(guān)財(cái)務(wù)、投資、償還、利息及折舊方面的函數(shù)，在工作表中運(yùn)用這些函數(shù)可以較松地完成相關(guān)的財(cái)務(wù)運(yùn)算，或者對其他財(cái)務(wù)管理軟件的運(yùn)算輸出數(shù)據(jù)進(jìn)行驗(yàn)證。5.7財(cái)務(wù)函數(shù)1、折舊函數(shù)

Excel折舊函數(shù)有DB、DDB、SLN、YDB及VDB。運(yùn)用這5個折舊函數(shù)可以確定指定時期內(nèi)資產(chǎn)的折舊值。這5個折舊函數(shù)有4個參數(shù)是共有的，如表所示。cost為資產(chǎn)原值salvage為資產(chǎn)在折舊期末的價值（也稱為資產(chǎn)殘值）lIFe為折舊期限（有時也可稱作資產(chǎn)的生命周期）period必須使用與lIFe相同的單位5.7財(cái)務(wù)函數(shù)DB函數(shù)用法DB(cost,salvage,lIFe,period,month)其中：lIFe和period須使用相同的時間單位；month為第一年的月份數(shù)，如果省略，則默認(rèn)為12。功能DB函數(shù)使用固定余額遞減法，計(jì)算一筆資產(chǎn)在給定期限的折舊值例如某學(xué)校購買了一批計(jì)算機(jī)，價值為500000元使用期限為3年，報(bào)廢價值為100000元。每年的折舊公式及結(jié)果值如下所示：DB(500000,100000,3,1,6)=￥103750.00DB(500000,100000,3,2,6)=￥164443.75DB(500000,100000,3,3,6)=￥96199.595.7財(cái)務(wù)函數(shù)DDB函數(shù)用法：DDB(cost,salvage,lIFe,period,factor)其中，factor為余額遞減速率。如factor省略，則默認(rèn)為2（雙倍余額遞減法）。5個參數(shù)都必須為正數(shù)功能DDB函數(shù)使用雙倍余額遞減法或其他指定方法，計(jì)算一筆資產(chǎn)在給定期限內(nèi)的折舊值例如：學(xué)?；?0萬元購買了一臺新設(shè)備，使用期限為10年，報(bào)廢價值為1萬。下面的例子給出幾個期限內(nèi)的折舊值（結(jié)果保留兩位小數(shù)）。DDB(100000,10000,3650,1)=￥54.79，第一天的折舊值。factor被Excel默認(rèn)設(shè)置設(shè)為2。DDB(100000,10000,120,1)=￥1666.67，第一個月折舊值。DDB(100000,10000,10,1)=￥20000.00，第一年的折舊值。DDB(100000,10000,10,3,1.5)=￥10837.50，第三年的折舊。這里沒有使用雙倍余額遞減法，factor=1.5。

5.7財(cái)務(wù)函數(shù)SYD、SLN、VDB函數(shù)

用法SYD(cost,salvage,life,per)SLN(cost,salvage,life)VDB(cost,salvage,lIFe,start_period,end_period,factor,no_switch)功能SYD函數(shù)計(jì)算某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的某期限內(nèi)的折舊值SLN函數(shù)計(jì)算一項(xiàng)資產(chǎn)每期的直線折舊值VDB函數(shù)代表可變余額遞減法，可使用雙倍遞減余額法或其他指定的方法，計(jì)算指定期間內(nèi)或某一時間段內(nèi)的資產(chǎn)折舊額。5.7財(cái)務(wù)函數(shù)2、投資函數(shù)

投資函數(shù)的參數(shù)Excel投資分析方面的函數(shù)，這些函數(shù)使用的參數(shù)大致相同，意義相近，如下表所示。參數(shù)說明rate為各期利率，為固定值per用于計(jì)算其本金數(shù)額的期次，必須在1~nper之間nper為總投資（或貸款）期次，即該項(xiàng)投資（或貸款）的付款期總數(shù)pv為現(xiàn)值，即從該項(xiàng)投資（或貸款）開始計(jì)算時已經(jīng)入賬的款項(xiàng)，或一系列未來付款當(dāng)前值的累積和，也稱為本金fv為未來