EXCEL公式及函數的高級應用

1、EXCEL公式及函數的高級應用公式和函數是 Excel最基本、最重要的應用工具，是 Excel的核心，因此，應對公式和 函數熟練掌握，才能在實際應用中得心應手。2.1.1數組公式及其應用數組公式就是可以同時進行多重計算并返回一種或多種結果的公式。 在數組公 式中使用兩組或多組數據稱為數組參數， 數組參數可以是一個數據區(qū)域，也可 以是數組常量。數組公式中的每個數組參數必須有相同數量的行和列。2.1.1.1數組公式的輸入、編輯及刪除1 數組公式的輸入數組公式的輸入步驟如下：(1) 選定單元格或單元格區(qū)域。如果數組公式將返回一個結果，單擊需要輸入數組公式的單元格；如果數組公式將返回多個結果，則要選定

2、需要輸入數組 公式的單元格區(qū)域。(2) 輸入數組公式。(3) 同時按“ Crtl+Shift+Enter ”組合鍵，貝U Excel自動在公式的兩邊加上大括號 0特別要注意的是，第(3)步相當重要，只有輸入公式后同時按“ Crtl+Shift+E nter ”組合鍵，系統(tǒng)才會把公式視為一個數組公式。否則，如 果只按Enter鍵，則輸入的只是一個簡單的公式，也只在選中的單元格區(qū)域的 第1個單元格顯示出一個計算結果。在數組公式中，通常都使用單元格區(qū)域引用，但也可以直接鍵入數值數組，這 樣鍵入的數值數組被稱為數組常量。當不想在工作表中按單元格逐個輸入數值 時，可以使用這種方法。如果要生成數組常量，必

3、須按如下操作：(1) 直接在公式中輸入數值，并用大括號“ ”括起來。不同列的數值用逗號“，”分開。(3)不同行的數值用分號分開(1)選取單元格區(qū)域A1:D1，如圖2-1所示圖2-1 選取單元格區(qū)域A1:D1圖2-2在編輯欄中輸入數組公式圖2-3同時按Ctrl+Shift+Enter組合鍵，得到數組常量則可采用所示。、D1中分20、30、輸入數組常量的方法：例如，要在單元格A1:D1中分別輸入10, 20, 30和40這4個數值, 下述的步驟：(2)在公式編輯欄中輸入數組公式“ =10,20,30,40 ”，如圖2-2(2) 同時按Ctrl+Shift+Enter組合鍵，即可在單元格 A1、B1

4、、別輸入了 10、20、30、40,如圖2-3所示。假若要在單元格 A1、B1、C1、D1、A2、B2、C2 D2中分別輸入10、40、50、60、70、80,則可以采用下述的方法：(3)不同行的數值用分號分開(3)不同行的數值用分號分開選取單元格區(qū)域A1:D2，如圖2-4所示A1'-A 1>1 C E A-L1圖2-4選取單元格區(qū)域A1:D2(1) 在編輯欄中輸入公式“ =10,20,30,40;50,60,70,80”，如圖2-5所示山訃 二| x “ “上:亙西范 石兩戴丁靜】| *I i ? I I 圖2-5在編輯欄中輸入數組公式(3)按 Ctrl+Shift+Enter

5、組合鍵，就在單元格 A1、B1、C1、D1、A2、B2、C2 D2中分別輸入了 10、20、30、40和 50、60、70、80,如圖 2-6 所示。AL二J =110. -0.30. 40.50. 60.70, BOJJ丄A 1 B . C ：9I匚ajw鉤SCTO3圖2-6同時按Ctrl+Shift+Enter組合鍵，得到數組常量輸入公式數組的方法例如，在單元格A3:D3中均有相同的計算公式，它們分別為單元格A1:D1與單 元格A2:D2中數據的和，即單元格 A3中的公式為“ =A1+A2，單元格B3中的 公式為“ =B1+B2，,，則可以采用數組公式的方法輸入公式，方法如下：(1) 選取

6、單元格區(qū)域A3:D3,如圖2-7所示。(2) 在公式編輯欄中輸入數組公式“ =A1:D1+A2:D2',如圖2-8所示。圖2-7選取單元格區(qū)域A3:D3圖2-8在編輯欄中輸入數組公式(3) 同時按Ctrl+Shift+Enter組合鍵，即可在單元格A3:D3中得到數組公式“=A1:D1+A2:D2，如圖 2-9 所示。圖2-9 同時按Ctrl+Shift+Enter組合鍵，得到數組公式用Excel做個工程倒計時牌我們常?？吹竭@樣的工程倒計時牌 ：這多么鼓舞人心！她如擂起陣陣戰(zhàn)鼓，激勵人們奮進能否在EXCEL中制成這樣的廣告呢？ 能!而且還很很簡單。首先制表如下：BCD1工程管理倒計時3

7、制計吋1vX.'i com在單元格B3中，輸入系統(tǒng)時間:B3: =TODAY()在單元格D3中，輸入計劃結束日期；在單元格C3中，有兩種方案：一是從開始就進行倒計時，二是距結束日期為若干天進行倒計時(設25天)。當從開始就進行倒計時:C3: =D3-B3;當距結束日期為若干天進行倒計時:C3: =IF(D3-B3<26,D3- B3,”。)為了在B3中出現今天是2006年8月15日工程還有”，可在B3中進行單元格格式 在類型(T)中如下圖輸入 今天是2006年8月15日工程還有”。邊框i圖案保護|示例今天是西頁年&月15日工程還有對齊|字體類型2夭"是牽十片它日

8、訂w- ff # 1J W 劃行天訊天 訐密紳 一總已蓋今Ld用 數者 比記規(guī)值幣計期間分數學本殊卿常數貨會B時百分科文特h刪除_|以現有格式為基昭，生威自定義的數字格式.下一步利用圖表進行作圖 選中A2:D3圖表向導條形圖堆積條形圖圖表向導-4歩彈乏I -圖表類奎區(qū)I標唯類型自定義類型圖表類型©:圖圖圖團圖 積環(huán)達面泡 面圓雷曲氣子團表類型:鷲鬻輛翹類別軸上的毎按下不放可查看示例® '下一步y(tǒng)esKvewr1下一步數據區(qū)域系列產生在列下一步輸入標題選擇網格線勾銷圖例數據標志顯 示V值下一步直至完成 如圖：國慶獻禮項目工程進入倒計時全市人民在關注看你們I今天是羽葩年

9、&倒計時月15日工程還有今天是今天是今天是今天是今夭是今天是今天是今天是今天是 2血£年2DDG年勿葩年劉訊年2006 2006 2006 2006 YDE年 丁月22 8月1日&月M &月21 &月31 9月10 9月卻9月加 加0 日工程工程還日工程日工程日工稈日工程日工稈日工程iyesky, c cw對座標軸進行格式：刻度/數字/對齊如下：坐標軸搭式用 數專P比記義 規(guī)值幣計腳分數事味忌 簾數貸會儲在分科立特自字體示例10-1對齊|類型: 雷4日星期二197-3-4199T-3-4 1：30 FM1WT-M 13:30廠錘接到源日期格式把日期時間

10、系列數顯示為日期值 使用時間格式將只顯示時間蘸I 並* yes/cy.wc(Mi?坐標軸搭式度=1!說織文本度同時清除分類軸。其余格式略。EXCEL數據分析處理 來源：未知（19611） 2005-11-9 【字體：大 中 小】 切換為繁體中文Excel提供了強大的數據分析處理功能，利用它們可以實現對數據的排序、分 類匯總、篩選及數據透視等操作。在進行數據分析處理之前，首先必須注意以下幾個問題：（1）避免在數據清單中存在有空行和空列。（2）避免在單元格的開頭和末尾鍵入空格。（3）避免在一張工作表中建立多個數據清單，每張工作表應僅使用一個數據 清單。（4）在工作表的數據清單應與其他數據之間至少留

11、出一個空列和一個空行， 以便于檢測和選定數據清單。（5）關鍵數據應置于數據清單的頂部或底部。2.3.1數據排序2.3.1.1數據排序的規(guī)則Excel允許對字符、數字等數據按大小順序進行升序或降序排列，要進行排序 的數據稱之為關鍵字。不同類型的關鍵字的排序規(guī)則如下：數值：按數值的大小。字母：按字母先后順序。日期：按日期的先后。漢字：按漢語拼音的順序或按筆畫順序。邏輯值：升序時FALSE排在TRUE前面，降序時相反?？崭瘢嚎偸桥旁谧詈?。231.2數據排序步驟（1）單擊數據區(qū)中要進行排序的任意單元格。（2）單擊【數據】菜單，選擇【排序】項，系統(tǒng)將彈出【排序】對話框，如 圖2-35所示。IT1 擰C

12、i*JCCD墓三芒It卞I:沿金(1' r *.tfMH©- i 0 I 9M 圖2-35【排序】對話框（3）在【排序】對話框中用下拉列表框選擇要排序的關鍵字，關鍵字有“主 要關鍵字”、“次要關鍵字”和“第三關鍵字”，根據需要分別選擇不同的關 鍵字；（4）單擊【確定】按鈕，數據就按要求進行了排序。當只有一個關鍵字時，可以單擊工具欄上的升序按鈕 岡或降序按鈕昱，進行 自動排序。2.3.1.3自定義排序在有些情況下，對數據的排序順序可能非常特殊，既不是按數值大小次序、也 不是按漢字的拼音順序或筆畫順序， 而是按照指定的特殊次序，如對總公司的各個分公司按照要求的順序進行排序， 按產

13、品的種類或規(guī)格排序等等，這時就 需要自定義排序。利用自定義排序方法進行排序，首先應建立自定義序列，其方法可參閱第1章的有關內容。建立好自定義序列后，即可對數據進行排序，方法是：單擊數 據區(qū)中要進行排序的任意單元格，單擊【數據】菜單，選擇【排序】項，在彈 出的【排序】對話框中單擊【選項】按鈕，系統(tǒng)彈出【排序選項】對話框，如 圖2-36所示，在【自定義排序次序】的下拉列表中，選擇前面建立的自定義 序列，然后單擊【確定】按鈕，即可對數據進行自定義排序。圖2-36【排序選項】對話框2.3.2數據的查找與篩選企業(yè)的管理人員經常需要在數據庫或數據清單眾多的數據中找出需要的數據， Excel提供了功能強大的

14、數據查找與篩選工具。數據查找是指從原始數據中提取滿足條件的數據記錄，源數據不會改變，也不會被隱藏；數據篩選是指把數 據庫或數據清單中所有不滿足條件的數據記錄隱藏起來，只顯示滿足條件的數據記錄。常用的數據查找與篩選方法有：記錄單查找、自動篩選和高級篩選。下面結合實例說明各種查找方法的具體應用?！纠? 11】圖2-37為某公司的部分商品銷售記錄清單。貞.IBc ft) e*>mu.-ao>ir-ii iliaLU 2囂圧誰n囂券ISW12Q0a-7-< 2W3-7-4 3JM0-T"*ZOT1-1I MJ-7-6aa®-i4 ran" &ra

15、-7-1 3M3H-T-S snx-12»聞»禍omwwuoMp*40妙誹I *1- 卯崔?1££辭Kyi話ys圖 2-37某公司的商品銷售明細清單根據圖2-37中的有關資料，可以分別采用記錄單查找、自動篩選或高級篩選 的方式查找或選擇所需要的信息，如下所述：用EXCEL制作新年萬年歷來源：佚名（28364）2008-1-16 【字體：大中小】 切換為叵1繁體中文丄JExcel標志設計筆記本廣告設計新年伊始，制作一個新年萬年歷送給別人新年的祝福，這份心意是非常重要的。制作新 年萬年歷有很多辦法，但用Office的Excel表格制作你又試過沒？下面給大家介

16、紹用Excel制作萬年歷。今天我們介紹用Excel制作萬年歷的方法。這個新年萬年歷可以顯示當月的月歷, 還可以隨意查閱任何日期所屬的月歷，非常方便。如果你愿意，還可以讓它在特殊的日 子里顯示不同的提醒文字，一起來試試吧！本文所涉及到的函數有:1、AND (Iogical1,logical2,)2、DATE (year,month,day)3、DAY (serial number)4、IF (Logical,Value_if_true,Value_if_false)5、INT (number)6、MONTH (serial_number)7、NOW ()8、OR (Iogical1,logica

17、l2, .)1、啟動EXCEL2003，新建一個工作表，取名保存（如萬年歷 .xls），并在相應的單 元格中，輸入如圖1所示的文本。ABCDEFGH1星期北京時間2371234545星期日星期一星期二星期三星期四星期五星期六6789101112.扁查詢年月年i2、同時選中B1、C1、D1單元格，按 格式”工具欄上的 合并及居中”按鈕，將其合 并成一個單元格，并輸入公式：=TODAY （）。選中B1 （合并后的）單元格，執(zhí)行 格式t單元格”命令，打開 單元格格式”寸話框（如 圖2），在 數字"標簽中的 分類"下面選中 日期"選項，再在右側 類型"下面選中二

18、0（一 年三月十四日”選項，確定”退出，將日期設置成中文形式。二OO年三f:三月十四日3001年3月14日2001年3月炯規(guī)值幣卄冊針 射常數貨會靜百分科文特自空邃置(國家陛國型中殳沖國)單元搞格式對齊字擁邊旌圖案保護示例二OQ三年十二月二十七日 類型Q):*2001-3-14薩C01年3月14日-i鷲竊體黒麗黠鬆轅黯鎰普星劭)的項I 離刖消注意：TODAY ()函數用于提取當前系統(tǒng)日期，請將系統(tǒng)日期一定要調整準確喲。3、選中 F1 單元格，輸入公式：=IF(WEEKDAY(B1,2)=7," 日 ”,WEEKDAY(B1,2);選中H1單元格，輸入公式：=NOW ()。選中F1單元

19、格，打開 單元格格式”對話框，在 數字”標簽中的 分類'下面選中 特殊 選項，再在右側 類型”下面選中 中文小寫數字”選項，確定”退出，將 星期數”設置成中文 小寫形式；選中H1單元格，打開 單元格格式”對話框，在 數字”標簽中的 分類”下面選中 時間”選項，再在右側 類型”下面選中一款時間格式， 確定”退出。注意：上述前面一個公式的含義是：如果(IF)當前日期(B1 )是星期“7”(WEEKDAY(B1,2)=7 )，則在F1單元格中顯示 日”，否則，直接顯示出星期的數值 (WEEKDAY(B1,2)。上述第二個函數(NOW ()用于提取當前系統(tǒng)日期和時間，也請將系統(tǒng)日期和 時間調整準確。4、 在11、12單元格分別輸入1900、1901，然后同時選中11、12單元格，用 填充 柄”向下拖拉至1151單元格，輸入1900 2050年份序列。同樣的方法，在J1至J12單元格中輸入1 12月份序列