Excel与数据处理课件

Excel基礎1.1啟動與退出Excel1．啟動Excel的方法通過Windows任務欄選擇“開始”按鈕通過Excel快捷方式雙擊Ｅxcel檔的名字或圖示２、退出Ｅxcel的方法單擊Excel窗口右上角的關閉按鈕選擇“檔”菜單中的“退出”菜單項。按快捷鍵Alt+F4。1.1.1

Excel介面1.1.2

Excel基本概念1、工作簿在Excel中創建的檔叫做工作簿，其擴展名是.xls工作薄由工作表組成，新建工作薄只有默認的３個工作表。但可以根據需要增加，其個數原則上僅受限於記憶體。新建第一個工作薄的默認名：book1.xls。2、工作表由一些橫向和縱向的網格組成的表格。一個工作表最多有65536行、256列。工作表由工作表標符區別，如sheet1、sheet21.1.2

Excel基本概念工作薄與工作表的關係1.1.2

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

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

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

1.7工作表列印2、列印預覽（1）列印預覽的意義列印預覽是列印稿的螢幕預現，其效果與最終的列印效果沒有什麼區別。在進行工作表的正式列印之前，有必要進行列印預覽。以便及早發現問題，進行頁面和紙張的重新設置，避免列印出無用的表格。（2）方法單擊常用工具條中的列印預覽工具按鈕

1.7工作表列印3、縮放列印比例以適應打印紙大小比較糟糕的列印情況：只有一兩行（列）的內容被列印到了另一頁上！列印內容不足一頁，整張打印紙內容較少，頁面難看。這些情況可通過列印縮放解決！1.7工作表列印4、列印標題或頁碼選擇“視圖”|“頁眉和頁腳”菜單項單擊“自定義頁眉”可輸入頁眉或頁腳的文字。也可以選擇頁碼或日期等1.7工作表列印5、標題在工作表的首行如果工作表的首行是標題，而且工作表內容較多，可能列印多頁。可以通過頁面設置，將首行指定為標題。1.7工作表列印6、列印整個工作簿或指定工作表區域如果單擊常用工具欄中的列印圖示，Excel只會將當前工作表的內容列印出來。如果需要列印工作簿中的全部工作表，或列印一個工作表的指定區域，選擇“檔”|“列印”菜單數據輸入2.1Excel的數據類型1、Excel的常見數據類型數字型、日期型、文本型、邏輯型數據數字型表現形式多樣：貨幣、小數、百分數、科學計數法、各種編號、郵遞區號、電話號碼等多種形式2.1Excel的數據類型2、Excel數值型數據的取值範圍功能最大限制數字精度15位單元格中可鍵入的最大數值9.99999999999999E307最大正數1.79769313486231E308最小負數-2.2250738585072E-308最小正數2.229E-308最大負數-2.2250738585073E-3082.2基本數據的輸入1、數據輸入的一般過程（1）選定要輸入數據的單元格。（2）從鍵盤上輸入數據。（3）按Enter鍵後2、輸入數值（1）正數的輸入：+234，13,333,333，12E3（2）負數的輸入：-234，(234)，-12e2（3）分數的輸入:22/3,03/4（4）貨幣數據的輸入:￥123，$212.2基本數據的輸入3、輸入文本字元文本應逐字輸入數字文本以‘開頭輸入，或用=“數字”方式輸入。輸入文本32，可輸入：=“32”或輸入：'32文本形式的公式，要通過“插入”|“對象”菜單命令如2.2基本數據的輸入4、輸入日期以yy-mm-dd形式，或mm-dd形式輸入通過格式化得到其他形式的日期，這樣可減少輸入格式方法是選擇“格式”|“單元格”|“數字”|“日期”2.2基本數據的輸入5、輸入公式公式是對工作表中的數值進行計算的等式。公式要以等號（＝）開始。例如在A3輸入：=5+2*3，結果等於2乘3再加5。A3中將顯示112.3相同數據的輸入1、複製相同數據建立具有相同數據的不同工作表，可用複製方法。某班主任建立相同的成績表頭，可用此法。複製一班表頭2.3相同數據的輸入2、填充複製相同數據設要建立下圖所示“學生檔案”表。其中入學時間、班級、系都是相同數據。（1）在G2中輸入“電腦通信”，然後Enter。（2）單擊G2，G2右下角會出現黑色小方塊，它就是填充柄。（3）向下拖動填充柄，拖過的單元格都被填入了“電腦通信”。2.3相同數據的輸入3、用Enter輸入相同數據設要建立下圖所示“學生檔案”表。其中入學時間、班級、系都是相同數據。（1）選中F2：F11（2）輸入：JK001（3）按Ctrl+Enter可用同樣方法輸入C、G列數據2.4編號的輸入1、複製輸入連續的編號連續編號（或等差、等比性質的數據）應該採用複製或序列填充的方式進行輸入。比如公職或單位的職工編號、電話號碼、手機號碼、零件編號等。輸入右圖A列的學號（1）在A2輸入1003020101

在A3輸入1003020101（2）選中A2：A3（3）向下拖動A3單元格的填充柄。2.4編號的輸入2、填充產生連續編號填充序列方式適用於輸入較多的連續編號數據。比如要輸入電話號碼：663321240~663328000，如右圖所示，方法如下。在第一個單元格中輸入起始號碼選擇“編輯”|“填充”|“序列”菜單命令。在Excel彈出的“序列”對話框中指定“列”在步長中輸入1在終止值中輸入最後一個號碼。2.4編號的輸入3、利用自定義格式產生特殊編號人們常會遇到各種具有一定規則的特殊格式的編號。如右圖的保險號。1、選中要輸入保險號的區域2、選擇“格式”|“單元格”菜單命令。3、選中“自定義”4、輸入自定格式：p00-000-00002.4編號的輸入用自定義格式輸入大數字編號當在單元格中輸入一個位數較多的數值時（比如超過15位的整數），Excel會自動將輸入的數據顯示為科學記數法，這可能並不符合我們的需要。產生這種問題的原因是數值精度問題，Excel的默認精度是15位，如果一個數值的長度超過15位，則15位之後的數字都被視為0。這種位數較多的大數字編號，可以用自定義格式進行輸入2.4編號的輸入用自定義格式輸入大數字編號建立右圖所示的電話用戶檔案，其中身份證號為18位，且前15位全部相同。若直接輸入，不僅數據多，且要出錯，比如在G4輸入Excel會顯示為5.1021E17解決方法是定義G列保存身份證號的單元格的自定義格式為："51021419601230"0000設置自定義格式後，只需要輸入身份證的最後4位編號2.4編號的輸入選中D2單元格，選擇

“格式”|“單元格”菜單項“類型”文本框中輸入："51021419601230"00002.5組合多個單元格數據1、用&運算符組合數據“&”為字元連接運算，其作用是把前後的兩個字串（也可以是數值）連接為一個字串。如“ADKDKD”&“DKA”的結果為ADKDKDDKA。123&45&678的結果為“'12345678”設下圖E列數據由C、D列數據組合而成。在E2中輸入公式=C2&D2，然後向下複製！2.5組合多個單元格數據2、用&和文本函數的結合&常和文本函數left、right、mid等合用，這幾個函數都很簡單，其調用語法如下：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組合多個單元格數據3、用&和文本函數的結合案例某電話公司的繳費帳號由身份證號的後15位（全長18位），後接電話號碼構成。如下圖所示。用&和文本函數可以很快建立繳費帳號。在I3輸入公式“=Right(G3,17)&H3”，然後向下填充複製該公式到I列的其餘單元格2.6採用下拉列表進行數據選擇1、下拉列表的適用範圍專案個數少而規範的數據，比如職稱、工種、單位及產品類型等，這類數據適宜採用Excel的“數據有效性”檢驗方式，以下拉列表的方式輸入。例如：某校教師檔案如右圖所示，其中的職稱列數據只能從“講師、教授、副教授、助教”中選擇。這類數據用下拉列表輸入，方便而準確。2.6採用下拉列表進行數據選擇2、下拉列表建立方法（1）選中要建立下拉列表的單元格區域。（2）選擇

“數據”|“有效性”菜單項。（3）選擇“設置”標籤，然後從“允許”下拉列表中選擇“序列”選項。（4）在“來源”文本框中輸入職稱名字“助教,講師,副教授,教授”

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

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

：可以產生0~100之間的數據RAND()*50+50

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

的結果是8INT(-8.9)的結果是-9

2.10用RAND和INT函數產生實驗數據案例建立如圖所示的學生成績表，用INT函數和RAND函數產生C2:J11區域的成績，設成績在20~100之間。(1)選中C2:G11區域(2)輸入公式：=20+int(rand()*80(3)按Ctrl+Enter說明：用rand產生的數據會隨時發生變化，可將它貼粘為數據，它就不會發生變化了。其方法是：（1）選中C3:G11區域，然後選擇“複製”（2）選擇“編輯”|“選擇性貼粘”（3）從彈出的對話框中選擇“數值”。格式化工作表3.1設置單元格數據的對齊方式數據對齊方式在默認情況下，文本靠單元格的左邊對齊，數值靠單元格的右邊對齊。有時需要特殊的對齊方式，如斜線表頭、旋轉字體、垂直居中等例如：3.1設置單元格數據的對齊方式通過跨列居中按扭合併單元格選中要合併的單元格，單擊跨列居中按鈕取消單元格的合併選中要合併的單元格，單擊跨列居中按鈕3、通過菜單命令也可以選擇“格式”|“單元格”菜單，然後在對話框中進行設置。3.1設置單元格數據的對齊方式垂直對齊方式文本、數字的旋轉同一單元格文本換行：Alter+Enter單元格文本自動換行3.2文本的格式設置文本格式設置的內容文字的設置大致包括字體、字型、修飾、對齊方式、字體顏色等文本格式設置的方法選擇“格式”|“單元格”菜單命令選擇彈出對話框中的“字體”，然後就可進行各項設置。3.2文本的格式設置文本格式化案例3.3格式化數據1、Excel的自動數據格式數字、日期、時間等類型的數據在Excel中都是以數字形式存儲的，同樣的數字在具有不同格式的單元格中會表現出不同的形式。例如在下圖的第3行中，輸入的都是10.3，但結果卻有很大的不同。3.3格式化數據2、使用系統提供的數據格式Excel提供了許多數據格式，包括數據的精確度、顯示方式等內容（貨幣：美元、歐元、人民幣，百分比等）。數據格式設置的常見內容有以下幾種。數據的精確度以百分比方式顯示數據數據的分節顯示

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

3.3格式化數據【例3-4】定義電話、保險號碼的簡化輸入3.4格式化日期1、概述在Excel中，日期和時間實際上是以序列數字的形式存儲，以日期形式顯示的。所以當一個單元格的格式被設置為日期格式後，即使在其中輸入純粹的數字，該數字也會被顯示為一個日期。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條件格式條件格式的意義對於不同的數據，可按不同的條件設置其顯示格式。比如在一個學生成績表中，把所有不及格的成績顯示為紅色，就能夠很容易找到有不及格科目的學生。下麵是條件格式運用的一個例子：當單元格的數值大於200時，用黑色顯示；當小於100時用紅色顯示；100~200之間的數據用藍色顯示。將該單元格的格式設置為如下的自定義數字格式：[黑色][>=200]#,###;[紅色][<=100]#,###;[藍色]#,###

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

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

絕對引用的形式是在引用單元格的列號與行號前面加“$”符號。比如，$A$1就是對A1單元格的絕對引用。4.2引用絕對引用的案例假設LG空調在重慶各商場的銷售價格相同，重慶各商場的銷售數據如下圖所示。計算各商場的銷售總額。在E8輸入公式：=$c$5*C8然後向下複製此公式！4.2引用5、混合引用混合引用具有絕對列和相對行，或是絕對行和相對列。比如，$A1、$B1

案例4.2引用6、內部引用與外部引用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、公式的使用中的錯誤資訊錯誤值錯誤原因########單元格所含的數字、日期或時間比單元格寬，或者單元格的日期、時間公式產生了一個負值，就會產生#####錯誤。#VALUE!1.在需要數字或邏輯值時輸入了文本，Excel不能將文本轉換為正確的數據類型。2.輸入或編輯數組公式時，按了Enter鍵。3.把單元格引用、公式或函數作為數組常量輸入。#DIV/O!1.輸入的公式中包含明顯的除數為零（0）#NAME?1.在公式中輸入文本時沒有使用雙引號。但這些文本又不是名字。2.函數名的拼寫錯誤。3.刪除了公式中使用的名稱。4.名字拼寫有錯#N/A1.在調用函數時參數。2.在數組的參數的行數或列數與包含數組公式的區域的行數或列數不一致。3.在沒有排序的數據表中使用了VLOOKUP、HLOOKUP或MATCH工作表函數查找數值#REF!刪除了公式中所引用的單元或單元格區域#NUM!1.由公式產生的數字太大或太小，Excel不能表示。2.在需要數字參數的函數中使用了非數字參數#NULL!在公式的兩個區域中加入了空格從而求交叉區域，但實際上這兩個區域無重迭區域4.3數組公式及其應用1、數組公式的含義數組公式能夠對兩組或兩組以上的數據（兩個或兩個以上的單元格區域）同時進行計算。在數組公式中使用的數據稱為數組參數，數組參數可以是一個數據區域，也可以是數組常量。2、數組公式的建立方法（1）選中需要保存數組公式結果的單元格或單元格區域。（2）輸入公式的內容。（3）按Ctrl+Shift+Enter鍵。4.3數組公式及其應用3、用數組公式計算兩個數據區域的乘積【例4-1】某超市3月份進購的產品圖4-5所示，已知各種產品的數量、單價和折扣，要計算各種產品的應付金額。4.3數組公式及其應用4、數組擴展在公式中用數組作為參數時，所有的數組必須是同維的。如果數組參數或數組區域的維數不匹配，Excel會自動擴展該參數。比如，某商場29寸電視機售價為3128元，有一個10天的銷售記錄，如下圖的B、C兩列所示。現要計算每天的銷售額。4.3數組公式及其應用1、選中F8:F14；2、輸入公式：=C8:C14*C53、按Ctrl+Shift+Enter4.3數組公式及其應用5、二維數組涉及許多行(或列)數據處理的數組公式，就是所謂的二維數組。合理地運用二維數組的運算功能，會提高數據處理的能力，有時在不同工作表之間進行數據匯總時會特別有效。4.3數組公式及其應用案例某食品商店將每個月的食品銷售資料統計在一個工作表中，1～6月的銷售數據分別記錄在Book1工作簿的Sheet1～Sheet6中，現在要統計每種商品前6個月的銷售總量和銷售金額。見下圖：4.4名字及其應用1、名字概述

可以給一個單元格或單元格區域取一個名字，這個名字可以出現在公式中，用來指代引用的單元格或單元格區域，這會使公式的意義更加明確。名字其實是一個識別字，由字母或下劃線開頭的一個或多個字元組成。在Excel中，還可以用漢字字元作為名字。名字不區分字母的大小寫，比如ABC、abc、Abc都是同一個名字。可以為同一單元格或單元格區域定義多個不同的名字。名字一旦定義就可以在同一工作薄的不同工作表之間共用。4.4名字及其應用2、名字的意義在Excel中，同一個工作簿中的名字是共用的，在一個工作表中定義的名字，可以被另一個工作表的公式引用，這使公式具有更強大的功能，它能夠利用單元格的名字在不同的工作表中查找到正確的單元格或單元格區域。名字給單元格的引用帶來了許多方便，因為它使人們不必記住單元格在工作表中的引用位置，用名字就能找到它。4.4名字及其應用名字應用案例在下圖中，B、C列是某商場所有產品的當日單價，E、F列是產品當天的銷售記錄。銷售的產品多達幾百種，而每天則可能只賣掉其中的幾十種產品，現在要計算當日產品的銷售金額。若不用名字，單價要從C列的幾百個數據中去查找！

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

通過名字查找蔬菜單價指定A2:B12最左邊為名字4.4名字及其應用2）名字與Indirect函數結合應用Indirect函數與名字相結合，可使數據查找更方便。Indirect函數的用法是：Indirect(x)其中的X可以是單元格引用或名字。若X為單元格引用，它必須用“”引起來，如：=INDIRECT(“A1”)，將返回A1單元格中的內容若X為單元格名字，它將返回該名字所對應的單元格的內容。4.4名字及其應用某蔬菜商在一個工作表中保存蔬菜的單價，在另一工作表中保存銷售記錄，現要查找銷售記錄表中各蔬菜的單價。指定A2:B11區域的“最左列”為名字在D2中輸入公式：=INDIRECT(B2)向下複製該公式！4.4名字及其應用3．應用行列交叉點查找數據所謂行列交叉點指的是由[行的名字列的名字]確定的單元格。把一個工作表的首行、首列指定為名字之後，可以使用名字引用行、列交叉點對應單元格中的數據。案例某電信公司的電話用戶檔案，其中有成千上萬個電話用戶的電話號碼。如果知道某個用戶的名字，那麼要在這樣的表中查找該用戶的電話號碼、電話類型，或申請日期等數據就顯得很困難，因為表中的資料太多了。通過行、列交叉點的名字引用能很輕鬆地找到所需的數據。4.4名字及其應用1指定A2:E17的“首行，最左列”為名字。2、在任空白單元格中輸入“客戶名字庫第二行任一單元格標題”，就能找到該客戶的對應數據。函數5.1函數簡介1、函數的概念函數是能夠完成特定功能的程式。在Excel中，它是系統預定義的一些公式，它們使用一些稱為參數的特定數值按特定的順序或結構進行計算,然後把計算的結果存放在某個單元格中。在大多數情況下，函數的計算結果是數值。當然，它也可以返回文本、引用、邏輯值、數組或工作表的資訊5.1函數簡介2、Excel函數分類分類功能簡介資料庫函數對數據清單中的數據進行分析、查找、計算等日期與時間對日期和時間進行計算、設置及格式化處理工程函數用於工程數據分析與處理資訊函數對單元格或公式中數據類型進行判定財務函數進行財務分析及財務數據的計算邏輯函數進行邏輯判定、條件檢查統計函數對工作表數據進行統計、分析查找函數查找特定的數據或引用公式中的特定資訊文本函數對公式、單格中的字元、文本進行格式化或運算數學函數進行數學計算等外部函數進行外部函數調用及資料庫的鏈接查詢等功能自定義函數用戶用vba編寫，用於完成特定功能的函數5.1函數簡介3、函數調用函數的語法函數名(參數1,參數2,參數3,…)在公式中調用函數

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

SUM(x1,x2,…,x30)x1，x2，…，x30是需要求和的參數，可以是數據或單元格區域功能計算各參數的數值之和說明：①參數表中的數字、邏輯值及數字的文本運算式將被計算。例如，SUM(3,2)=5，SUM("9",20,true)=30。因為文本值被轉換成數字，而邏輯值“true”被轉換成數字1。5.2常用工作表函數②如果參數為數組或引用，那麼只有其中的數字被計算。數組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。例如，設A1的值為“9”，A2為true，則公式SUM(A1,A2,20)的計算結果為20，而不是30。因為本公式中包括兩個引用A1、A2，而A1的值為文本，A2的值為邏輯值，它們在計算時被忽略，最終就只有一個數值20參與運算。③SUM函數的參數最多可達30個，不同類型的參數可以同時出現。例如，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常用工作表函數5、條件求和函數SUMIF用法SUMIF(range,criteria,sum_range)其中，range是用於條件判斷的單元格區域，criteria條件，其形式可以為數字、運算式或文本；sum_range是需求和的實際單元格。只有當range中的相應單元格滿足條件時，才對sum_range中的單元格求和。若省略sum_range，則直接對range中的單元格求和。功能對range單元格區域中的數據進行Criteria條件檢查，然後對滿足條件行的sum_range同行進行求和。5.2常用工作表函數案例某商場的銷售記錄如圖所示。如果統計各類商品的總銷售額。彩電的銷售總額：=SUMIF(C$3:C$13,"彩電",F$3:F$13)5.2常用工作表函數6、平均值函數AVERAGE，AVERAGEA用法AVERAGE(n1，n2，…，n30)AVERAGEA(n1，n2，…，n30)其中，n1，n2，……，n30是要計算平均值的參數，該函數最多允許有30個參數。參數可以是數字，或者是涉及數字的名稱、數組或引用功能兩函數都是求參數的平均值。AverAge不對文本和邏輯值類數據進行計算；AverageA要對文本和邏輯函數進行平均值計算：文本被視為0，邏輯值true被視為1，false被視為0，空文本（""）也作為0計算5.2常用工作表函數案例下圖說明AVERAGE函數與AVERAGEA函數的區別5.2常用工作表函數7、計數函數COUNT、COUNTA、COUNTBLANK、COUNTIF用法COUNT(v1,v2,…)COUNTA(v1,v2,…)COUNTBLANK(range)COUNTIF(range,criteria)功能Count統計數字的個數CountA統計數字和文本的個數CountBlank統計空白單元格的個數Countif統計滿足條件Criteria的單元格個數5.2常用工作表函數8、求最大值、最小值函數MAX，MIN用法MAX(number1,number2,...)MIN(number1,number2,...)其中number1,number2,...

是要從中找出最大值或最小值的1到30個數字參數，也可以是單元格或單元格區域的引用。功能MAX函數返回一組值中的最大值，MIN函數返回一組值中的最小值5.2常用工作表函數本節案例問題的解決5.3邏輯函數1．比較運算比較運算就是人們常說的比較式，又稱關係運算。比較運算只有兩種不同的結果，要麼“正確”，要麼“錯誤”，不可能有第三種結果。比較運算符含義示例=等於a1=3>大於a1>b1<小於a1<1>=大於等於a1>=60<=小於等於a1<=”dd”<>不等於a1<>05.3邏輯函數2．邏輯運算AND(l1,l2,…)OR(l1,l2,…)NOT(logical)TRUE()FALSE()其中，l1,l2是關係式或邏輯值。5.3邏輯函數案例某單位有3000名職工，要按其工資繳納個人收入所得稅，稅率與工資的關係如表所示。該單位的職工表如下所示，用IF函數求出各職工的稅率！工資稅率3000以上稅率=30%2500~3000稅率=25%2000~2500稅率=20%1500~2000稅率=15%1200~1500稅率=10%1000~1200稅率=8%800~1000稅率=5%800以下稅率=05.3邏輯函數在E4中輸入編輯欄中的公式！5.4數學和三角函數Excel提供了許多數學和三角函數，它們能夠完成大多數數學和三角運算，這些函數可以在公式中直接引用，然後將公式的計算結果返回到輸入公式的單元格中。常見的數學函數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日期及時間函數

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

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

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

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

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

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

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

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

是單元格區域或數組常量。row_num

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

功能index函數返回array中row_num和column_num交叉處的單元格引用。5.6查找及引用函數小結數據查找是Excel辦公應用中最常見、最重要的問題，方法不當，本來幾分種就能解決的問題，可能要弄幾小時，甚至幾天。總的說來，在工作中應用最廣、作用最大的查找函數是VLOOKUP。在查找數據的案例集合.xls工作薄中包括了我幾年以來從實際辦公應用中收集到的數據查找案例。5.7財務函數Excel提供了許多有關財務、投資、償還、利息及折舊方面的函數，在工作表中運用這些函數可以較松地完成相關的財務運算，或者對其他財務管理軟體的運算輸出數據進行驗證。5.7財務函數1、折舊函數

Excel折舊函數有DB、DDB、SLN、YDB及VDB。運用這5個折舊函數可以確定指定時期內資產的折舊值。這5個折舊函數有4個參數是共有的，如表所示。cost為資產原值salvage為資產在折舊期末的價值（也稱為資產殘值）lIFe為折舊期限（有時也可稱作資產的生命週期）period必須使用與lIFe相同的單位5.7財務函數DB函數用法DB(cost,salvage,lIFe,period,month)其中：lIFe和period須使用相同的時間單位；month為第一年的月份數，如果省略，則默認為12。功能DB函數使用固定餘額遞減法，計算一筆資產在給定期限的折舊值例如某學校購買了一批電腦，價值為500000元使用期限為3年，報廢價值為100000元。每年的折舊公式及結果值如下所示：DB(500000,100000,3,1,6)=￥103750.00DB(500000,100000,3,2,6)=￥164443.75DB(500000,100000,3,3,6)=￥96199.595.7財務函數DDB函數用法：DDB(cost,salvage,lIFe,period,factor)其中，factor為餘額遞減速率。如factor省略，則默認為2（雙倍餘額遞減法）。5個參數都必須為正數功能DDB函數使用雙倍餘額遞減法或其他指定方法，計算一筆資產在給定期限內的折舊值例如：學校花10萬元購買了一臺新設備，使用期限為10年，報廢價值為1萬。下麵的例子給出幾個期限內的折舊值（結果保留兩位小數）。DDB(100000,10000,3650,1)=￥54.79，第一天的折舊值。factor被Excel默認設置設為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財務函數SYD、SLN、VDB函數

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

投資函數的參數Excel投資分析方面的函數，這些函數使用的參數大致相同，意義相近，如下表所示。參數說明rate為各期利率，為固定值per用於計算其本金數額的期次，必須在1~nper之間nper為總投資（或貸款）期次，即該項投資（或貸款）的付款期總數pv為現值，即從該項投資（或貸款）開始計算時已經入賬的款項，或一系列未來付款當前值的累積和，也稱為本金fv為未來值，或在最後一次付款後希望得到的現金餘額，如果省略fv，則假設其值為零（如一筆貸款的未來值即為零）type數字0或1，用以指定各期的付款時間是在期初還是期末，0：期末；1：期初value1,value2value1,…,value29代表1~29個償還金額不相等時的分期償還額5.7財務函數PMT函數

用法PMT(rate,nper,pv,fv,type)說明，PMT返回的支付款項包括本金和利息，但不包括稅款。rate和nper單位要一致。功能PMT基於固定利率及等額分期付款方式，計算投資或貸款的每期付款額。例如：某人買房了，貸款200000元，利率為7%，分10個月付清，則他的月支付額為：PMT(7%/12,10,200000)=￥-20647.26

5.7財務函數PV函數用法PV(rate,nper,PMT,fv,type)功能PV函數可以計算投資的現值。現值是一系列未來付款當前值的累積和，如借入方的借入款即為貸出方貸款的現值。例如：某人想買一筆養老保險，該保險可以在今後25年內於每月末回報500元。該保險的購買成本為75000，假定投資回報率為6.7%。該保險是否合算？現在可以通過函數PV計算一下這筆投資是否值得。該項投資的年金現值為：PV(0.067/12,12*25,500,0)=-72700.055.7財務函數FV函數

用法FV(rate,nper,PMT,pv,type)功能FV函數計算投資在將來某個日期的價值，它可以計算出投資的一次性償還金額，也可以計算