Excel常用函數(shù)基礎(chǔ)知識.ppt

貴在積累 潛移默化 Excel2003基礎(chǔ)知識 網(wǎng)絡(luò)采集 小麥 761935952011 07 12 前言 本文僅是excel的認(rèn)知基礎(chǔ) 滿足日常辦公需要 如需更深理解應(yīng)用 請查閱相關(guān)專業(yè)書籍 本文內(nèi)容均來自網(wǎng)絡(luò) 作者僅對原內(nèi)容進(jìn)行了摘取 整理 由于有些地方無法注明具體出處 故一律均未注明出處 并在此向原作者致敬 本文僅作學(xué)習(xí)交流之用 勿作商業(yè)用途 本文結(jié)構(gòu) excel基礎(chǔ)知識 第壹部分 excel函數(shù)及公式 一 函數(shù)和公式簡述 二 函數(shù)和公式基礎(chǔ) 三 數(shù)組基礎(chǔ)知識 四 宏表函數(shù)基礎(chǔ) 第貳部分 excel高級篩選 第叁部分 excel條件格式 第肆部分 excel數(shù)據(jù)透視 第伍部分 excelVBA了解 1 什么是函數(shù) 2 什么是公式 3 函數(shù)的參數(shù) 1 公式基礎(chǔ)認(rèn)知 2 常用函數(shù)了解 第壹部分 excel函數(shù)及公式一 函數(shù)和公式簡述 1 什么是函數(shù)Excel函數(shù)即是預(yù)先定義 執(zhí)行計(jì)算 分析等處理數(shù)據(jù)任務(wù)的特殊公式 以常用的求和函數(shù)SUM為例 它的語法是 SUM number1 number2 其中 SUM 稱為函數(shù)名稱 一個函數(shù)只有唯一的一個名稱 它決定了函數(shù)的功能和用途 函數(shù)名稱后緊跟左括號 接著是用逗號分隔的稱為參數(shù)的內(nèi)容 最后用一個右括號表示函數(shù)結(jié)束 參數(shù)是函數(shù)中最復(fù)雜的組成部分 它規(guī)定了函數(shù)的運(yùn)算對象 順序或結(jié)構(gòu)等 使得用戶可以對某個單元格或區(qū)域進(jìn)行處理 如分析存款利息 確定成績名次 計(jì)算三角函數(shù)值等 按照函數(shù)的來源 Excel函數(shù)可以分為內(nèi)置函數(shù)和擴(kuò)展函數(shù)兩大類 前者只要啟動了Excel 用戶就可以使用它們 而后者必須通過單擊 工具 加載宏 菜單命令加載 然后才能像內(nèi)置函數(shù)那樣使用 2 什么是公式函數(shù)與公式既有區(qū)別又互相聯(lián)系 如果說前者是Excel預(yù)先定義好的特殊公式 后者就是由用戶自行設(shè)計(jì)對工作表進(jìn)行計(jì)算和處理的公式 以公式 SUM E1 H1 A1 26 為例 它要以等號 開始 其內(nèi)部可以包括函數(shù) 引用 運(yùn)算符和常量 上式中的 SUM E1 H1 是函數(shù) A1 則是對單元格A1的引用 使用其中存儲的數(shù)據(jù) 26 則是常量 和 則是算術(shù)運(yùn)算符 另外還有比較運(yùn)算符 文本運(yùn)算符和引用運(yùn)算符 如果函數(shù)要以公式的形式出現(xiàn) 它必須有兩個組成部分 一個是函數(shù)名稱前面的等號 另一個則是函數(shù)本身 3 函數(shù)的參數(shù)函數(shù)右邊括號中的部分稱為參數(shù) 假如一個函數(shù)可以使用多個參數(shù) 那么參數(shù)與參數(shù)之間使用半角逗號進(jìn)行分隔 參數(shù)可以是常量 數(shù)字和文本 邏輯值 例如TRUE或FALSE 數(shù)組 錯誤值 例如 N A 或單元格引用 例如E1 H1 甚至可以是另一個或幾個函數(shù)等 參數(shù)的類型和位置必須滿足函數(shù)語法的要求 否則將返回錯誤信息 1 常量常量是直接輸入到單元格或公式中的數(shù)字或文本 或由名稱所代表的數(shù)字或文本值 例如數(shù)字 2890 56 日期 2003 8 19 和文本 黎明 都是常量 但是公式或由公式計(jì)算出的結(jié)果都不是常量 因?yàn)橹灰降膮?shù)發(fā)生了變化 它自身或計(jì)算出來的結(jié)果就會發(fā)生變化 2 邏輯值邏輯值是比較特殊的一類參數(shù) 它只有TRUE 真 或FALSE 假 兩種類型 例如在公式 IF A3 0 A2 A3 中 A3 0 就是一個可以返回TRUE 真 或FALSE 假 兩種結(jié)果的參數(shù) 當(dāng) A3 0 為TRUE 真 時在公式所在單元格中填入 0 否則在單元格中填入 A2 A3 的計(jì)算結(jié)果 3 數(shù)組數(shù)組用于可產(chǎn)生多個結(jié)果 或可以對存放在行和列中的一組參數(shù)進(jìn)行計(jì)算的公式 Excel中有常量和區(qū)域兩類數(shù)組 前者放在 按下Ctrl Shift Enter組合鍵自動生成 內(nèi)部 而且內(nèi)部各列的數(shù)值要用逗號 隔開 各行的數(shù)值要用分號 隔開 假如你要表示第1行中的56 78 89和第2行中的90 76 80 就應(yīng)該建立一個2行3列的常量數(shù)組 56 78 89 90 76 80 區(qū)域數(shù)組是一個矩形的單元格區(qū)域 該區(qū)域中的單元格共用一個公式 例如公式 TREND B1 B3 A1 A3 作為數(shù)組公式使用時 它所引用的矩形單元格區(qū)域 B1 B3 A1 A3 就是一個區(qū)域數(shù)組 4 錯誤值使用錯誤值作為參數(shù)的主要是信息函數(shù) 例如 ERROR TYPE 函數(shù)就是以錯誤值作為參數(shù) 它的語法為 ERROR TYPE error val 如果其中的參數(shù)是 NUM 則返回?cái)?shù)值 6 5 單元格引用單元格引用是函數(shù)中最常見的參數(shù) 引用的目的在于標(biāo)識工作表單元格或單元格區(qū)域 并指明公式或函數(shù)所使用的數(shù)據(jù)的位置 便于它們使用工作表各處的數(shù)據(jù) 或者在多個函數(shù)中使用同一個單元格的數(shù)據(jù) 還可以引用同一工作簿不同工作表的單元格 甚至引用其他工作簿中的數(shù)據(jù) 根據(jù)公式所在單元格的位置發(fā)生變化時 單元格引用的變化情況 我們可以引用分為相對引用 絕對引用和混合引用三種類型 以存放在F2單元格中的公式 SUM A2 E2 為例 當(dāng)公式由F2單元格復(fù)制到F3單元格以后 公式中的引用也會變化為 SUM A3 E3 若公式自F列向下繼續(xù)復(fù)制 行標(biāo) 每增加1行 公式中的行標(biāo)也自動加1 如果上述公式改為 SUM A 3 E 3 則無論公式復(fù)制到何處 其引用的位置始終是 A3 E3 區(qū)域 混合引用有 絕對列和相對行 或是 絕對行和相對列 兩種形式 前者如 SUM A3 E3 后者如 SUM A 3 E 3 上面的幾個實(shí)例引用的都是同一工作表中的數(shù)據(jù) 如果要分析同一工作簿中多張工作表上的數(shù)據(jù) 就要使用三維引用 假如公式放在工作表Sheet1的C6單元格 要引用工作表Sheet2的 A1 A6 和Sheet3的 B2 B9 區(qū)域進(jìn)行求和運(yùn)算 則公式中的引用形式為 SUM Sheet2 A1 A6 Sheet3 B2 B9 也就是說三維引用中不僅包含單元格或區(qū)域引用 還要在前面加上帶 的工作表名稱 假如你要引用的數(shù)據(jù)來自另一個工作簿 如工作簿Book1中的SUM函數(shù)要絕對引用工作簿Book2中的數(shù)據(jù) 其公式為 SUM Book2 Sheet1 SAS1 SAS8 Book2 Sheet2 SBS1 SBS9 也就是在原來單元格引用的前面加上 Book2 Sheet1 放在中括號里面的是工作簿名稱 帶 的則是其中的工作表名稱 即是跨工作簿引用單元格或區(qū)域時 引用對象的前面必須用 作為工作表分隔符 再用中括號作為工作簿分隔符 不過三維引用的要受到較多的限制 例如不能使用數(shù)組公式等 提示 上面介紹的是Excel默認(rèn)的引用方式 稱為 A1引用樣式 如果你要計(jì)算處在 宏 內(nèi)的行和列 必須使用 R1C1引用樣式 在這種引用樣式中 Excel使用 R 加 行標(biāo) 和 C 加 列標(biāo) 的方法指示單元格位置 啟用或關(guān)閉R1C1引用樣式必須單擊 工具 選項(xiàng) 菜單命令 打開對話框的 常規(guī) 選項(xiàng)卡 選中或清除 設(shè)置 下的 R1C1引用樣式 選項(xiàng) 由于這種引用樣式很少使用 限于篇幅本文不做進(jìn)一步介紹 6 嵌套函數(shù)除了上面介紹的情況外 函數(shù)也可以是嵌套的 即一個函數(shù)是另一個函數(shù)的參數(shù) 例如 IF OR RIGHTB E2 1 1 RIGHTB E2 1 3 RIGHTB E2 1 5 RIGHTB E2 1 7 RIGHTB E2 1 9 男 女 其中公式中的IF函數(shù)使用了嵌套的RIGHTB函數(shù) 并將后者返回的結(jié)果作為IF的邏輯判斷依據(jù) 7 名稱和標(biāo)志為了更加直觀地標(biāo)識單元格或單元格區(qū)域 我們可以給它們賦予一個名稱 從而在公式或函數(shù)中直接引用 例如 B2 B46 區(qū)域存放著學(xué)生的物理成績 求解平均分的公式一般是 AVERAGE B2 B46 在給B2 B46區(qū)域命名為 物理分?jǐn)?shù) 以后 該公式就可以變?yōu)?AVERAGE 物理分?jǐn)?shù) 從而使公式變得更加直觀 給一個單元格或區(qū)域命名的方法是 選中要命名的單元格或單元格區(qū)域 鼠標(biāo)單擊編輯欄頂端的 名稱框 在其中輸入名稱后回車 也可以選中要命名的單元格或單元格區(qū)域 單擊 插入 名稱 定義 菜單命令 在打開的 定義名稱 對話框中輸入名稱后確定即可 如果你要刪除已經(jīng)命名的區(qū)域 可以按相同方法打開 定義名稱 對話框 選中你要刪除的名稱刪除即可 由于Excel工作表多數(shù)帶有 列標(biāo)志 例如一張成績統(tǒng)計(jì)表的首行通常帶有 序號 姓名 數(shù)學(xué) 物理 等 列標(biāo)志 也可以稱為字段 如果單擊 工具 選項(xiàng) 菜單命令 在打開的對話框中單擊 重新計(jì)算 選項(xiàng)卡 選中 工作簿選項(xiàng) 選項(xiàng)組中的 接受公式標(biāo)志 選項(xiàng) 公式就可以直接引用 列標(biāo)志 了 例如 B2 B46 區(qū)域存放著學(xué)生的物理成績 而B1單元格已經(jīng)輸入了 物理 字樣 則求物理平均分的公式可以寫成 AVERAGE 物理 需要特別說明的是 創(chuàng)建好的名稱可以被所有工作表引用 而且引用時不需要在名稱前面添加工作表名 這就是使用名稱的主要優(yōu)點(diǎn) 因此名稱引用實(shí)際上是一種絕對引用 但是公式引用 列標(biāo)志 時的限制較多 它只能在當(dāng)前數(shù)據(jù)列的下方引用 不能跨越工作表引用 但是引用 列標(biāo)志 的公式在一定條件下可以復(fù)制 從本質(zhì)上講 名稱和標(biāo)志都是單元格引用的一種方式 因?yàn)樗鼈儾皇俏谋?使用時名稱和標(biāo)志都不能添加引號 1 1公式在一個單元格輸入等號時 EXCEL就認(rèn)為你輸入了一個公式 EXCEL單元格接受5種元素的輸入 運(yùn)算符例如 單元格引用例如 A7 值或字符串例如7 5或 金額 函數(shù)和參數(shù)例如SUM或IF括號可以控制公式的運(yùn)算順序 一個公式最多可容納1024個字符 如果要創(chuàng)建超出范圍限制的公式 必須把公式分為多個公式 或創(chuàng)建一個自定義公式 創(chuàng)建公式時 盡量不要使用硬編碼值 例如計(jì)算7 5 的銷售稅 可以把銷售稅放到一個單元格中 使用單元格引用代替文字引用 一旦稅率發(fā)生改變 只要維護(hù)稅率單元格 而不需修改每個單元格 二 函數(shù)和公式基礎(chǔ) 1 2關(guān)于運(yùn)算符運(yùn)算符對公式中的元素進(jìn)行特定類型的運(yùn)算 MicrosoftExcel包含四種類型的運(yùn)算符 算術(shù)運(yùn)算符 比較運(yùn)算符 文本運(yùn)算符和引用運(yùn)算符 算術(shù)運(yùn)算符 加號3 3 減號3 1 乘號2 5 除號4 3 百分比20 求冪3 2 比較運(yùn)算符 等號A2 A1 大與B3 B2 大于等于A1 B1不等于A1B1 文本運(yùn)算符 NORTH EAST 引用運(yùn)算符 區(qū)域引用 A1 B15 聯(lián)合引用 A1 B15 C1 D15 空格交叉引用 A1 D15C1 C15 1 3引用大多數(shù)公式都會使用單元格或范圍引用一個或多個單元格 單元格的引用有4種類型 分別使用美元符號加以區(qū)別 相對完全相對 公式被復(fù)制時 單元格引用掉整到新的位置 如A1 A2絕對完全絕對 公式被復(fù)制時 單元格引用不會改變 如 A 1行絕對部分絕對引用 公式被復(fù)制時 列部分調(diào)整 行部分不會改變 如A 1列絕對部分絕對引用 公式被復(fù)制時 行部分調(diào)整 列部分不會改變 如 A1 可以使用F4鍵在引用模式中進(jìn)行循環(huán) 工作表之間的引用 SHEET2 A1 1工作薄之間的引用 供應(yīng)商表 XLS SHEET2 A1 1工作薄之間的引用 銷售表6月 XLS SHEE2 A1 1 1 4公式中的錯誤信息 EXCEL2003在錯誤公式邊可以顯示一個智能圖標(biāo) 可以單擊這個圖標(biāo)了解這個錯誤的進(jìn)一步信息 1 5定義名稱工作表中引用單元格范圍計(jì)算并不直觀 EXCEL可以把單元格 范圍 圖表 圖形命名為有意義的名稱 提供給公式引用 如下圖即是對單元格范圍G2 G13命名為單價(jià) 名稱不能包含任何空格 可以使用下劃線或點(diǎn)號代替空格可以使用字符和數(shù)字組合 但首字符必須是字母或下劃線 不能以數(shù)字開頭名稱長度限制在255個字符內(nèi)不能使用符號名稱不區(qū)分大小寫 2 1工作表函數(shù)工作表函數(shù)就是在公式中使用的一種內(nèi)部工具可以簡化公式保證公式運(yùn)行 否作無法計(jì)算提高編輯任務(wù)的速度允許 有條件地 運(yùn)行公式 使之具備基本判斷能力 函數(shù) 是預(yù)先錄制好的公式 函數(shù)集是EXCEL中的精華 函數(shù)都需要使用括號 括號中的內(nèi)容就是函數(shù)的參數(shù) 函數(shù)的結(jié)果決定于參數(shù)的使用方法 一個函數(shù)可以 不帶參數(shù)只有一個參數(shù)固定數(shù)量的參數(shù)不確定數(shù)量的參數(shù)可選參數(shù) 文本函數(shù) 連接連個文本字符串TRIM 刪除數(shù)據(jù)前后所有多余的空格 用一個空格代替多個空格 如 TRIM MyHome MyhomeLEN 返回單元格中字符的數(shù)量 如 LEN GREENHEAKVIP 13LEFT 從左起返回確定數(shù)量的字符 如 LEFT Beijing 3 BeiRIGTH 從右起返回確定數(shù)量的字符 如 RIGHT Beijing 4 jingMID 在字符串中任意位置返回確定數(shù)量的字符 如 MID7 8 19760811UPPER 將文本全部轉(zhuǎn)化為大寫 如 UPPER join JOINLOWER 將文本全部轉(zhuǎn)化為小寫 如 LOWER JOIN joinPROPER 將文本首字母大寫 如 PROPER joinme JoinMe 查找函數(shù) VLOOKUP 查找表格中的第一列值 返回相應(yīng)的值到指定的表格列種 查找表是垂直放置的 語法如下 VLOOKUP lookup value table array col index num range lookup Lookup value 查找值在查找表的第一列Table array 包含查找表的范圍Col index num 匹配值在表中的列編號Range lookup 可選 為1或空 可以找到一個近似值 為0 找匹配值 HLOOKUP 查找表格中的第一行值 返回相應(yīng)的值到指定的表格行中 語法如下 HLOOKUP lookup value table array row index num range lookup Lookup value 查找值在查找表的第一行Table array 包含查找表的范圍Col index num 匹配值在表中的行編號Range lookup 可選 為1或空 可以找到一個近似值 為0 找匹配值 查找函數(shù) MATCH MATCH函數(shù)可以返回范圍中的一個單元格相對位置 指出它可以匹配某個指定值 語法如下 MATCH lookup value lookup array match type INDEX 返回范圍內(nèi)的單元格 語法如下 INDEX array row num column num 信息函數(shù) ISERROR 值為任何錯誤值 N A VALUE REF DIV 0 NUM NAME 或 NULL 語法為 ISERROR VALUE 統(tǒng)計(jì)函數(shù) MAX 返回參數(shù)列表中的最大值 忽略邏輯值和文本MAX NUMBER1 NUMBER2 MIN 返回參數(shù)列表中的最小值 忽略邏輯值和文本MIN NUMBER1 NUMBER2 邏輯函數(shù) IF IF函數(shù)根據(jù)給定的條件 根據(jù)條件的不同返回不同的結(jié)果 語法為 IF Logical test value if true value if false IF A1 90 優(yōu)秀 if A1 80 良好 IF A1 60 及格 不及格 AND 所有參數(shù)的邏輯值為真時返回真OR 當(dāng)有一個參數(shù)為真時即返回真COUNTIF 計(jì)算區(qū)域中滿足給定條件的單元格的個數(shù) 語法如下 COUNTIF range criteria COUNTIF B1 B10 apples SUMIF 根據(jù)指定的條件對若干單元格求和 語法如下SUMIF range criteria sum range SUMIF A2 A20 10000 B2 B20 SUMIF A2 A20 0 信息函數(shù) SUMPRODUCT 在給定的幾組數(shù)組中 將數(shù)組間元素相乘 并返回乘積之和SUMPRODUCT array1 array OFFSET 以指定的引用為參照系 通過給定的偏移量來得到新的引用 OFFSET reference rows cols height width 三 數(shù)組基礎(chǔ)知識 1 數(shù)組公式 是用于建立可以產(chǎn)生多個結(jié)果或?qū)梢源娣旁谛泻土兄械囊唤M參數(shù)進(jìn)行運(yùn)算的單個公式 數(shù)組公式的特點(diǎn)就是可以執(zhí)行多重計(jì)算 它返回的是一組數(shù)據(jù)結(jié)果 由于一個單元格內(nèi)只能儲存一個數(shù)值 所以當(dāng)結(jié)果是一組數(shù)據(jù)時 單元格只返回第一個值 如果你需要用到所有的運(yùn)算結(jié)果時 要么用多個單元格去分別返回 可以用index 來返回 要么用某些函數(shù)來取其共性 如SUM MAX MIN 等 2 參數(shù) 數(shù)組公式最大的特征就是所引用的參數(shù)是數(shù)組參數(shù) 包括區(qū)域數(shù)組和常量數(shù)組 區(qū)域數(shù)組 是一個矩形的單元格區(qū)域 如 A 1 D 5常量數(shù)組 是一組給定的常量 如 1 2 3 或 1 2 3 或 1 2 3 1 2 3 數(shù)組公式中的參數(shù)必須為 矩形 如 1 2 3 1 2 就無法引用了3 輸入 同時按下CTRL SHIFT ENTER數(shù)組公式的外面會自動加上大括號 予以區(qū)分有的時候 看上去是一般應(yīng)用的公式也應(yīng)該是屬于數(shù)組公式 只是它所引用的是數(shù)組常量對于參數(shù)為常量數(shù)組的公式 則在參數(shù)外有大括號 公式外則沒有 輸入時也不必按CTRL SHIFT ENTER 4 數(shù)組簡單運(yùn)算過程 四 宏表函數(shù)基礎(chǔ) 常用宏表函數(shù) GET CELL type num reference 返回關(guān)于格式化 位置或單元格內(nèi)容的信息 在由特定單元格狀態(tài)決定行為的宏中 使用GET CELL GET DOCUMENT type num nametext GET WORKBOOK type num name text EVALUATE formula text 對以文字表示的一個公式或表達(dá)式求值 并返回結(jié)果 若要運(yùn)行宏或子例程 請使用RUN函數(shù) FILES directory text 返回指定目錄的所有文件名的水平文字?jǐn)?shù)組 使用FILES可以建立一個供宏操作的文件名清單 DOCUMENTS type num match text 以文字形式的水平數(shù)組返回指定的已打開工作簿中按字母順序排列的名字 使用DOCUMENTS可以檢索已打開工作簿的名字 供處理該打開工作簿的其他函數(shù)使用 第貳部分 excel高級篩選 何為高級篩選 在EXCEL中高級篩選是自動篩選的升級功能 可以將自動篩選的定制格式改為自定義設(shè)置 它的功能更加優(yōu)于自動篩選 1 高級篩選的主要功能 1 設(shè)置多個篩選條件 篩選條件之間可以是與的關(guān)系 或的關(guān)系 與或結(jié)合的關(guān)系 可以設(shè)置一個也可以設(shè)置多個 允許使用通配符 2 篩選結(jié)果的存放位置不同 可在數(shù)據(jù)區(qū)原址進(jìn)行篩選 把不需要的記錄隱藏 此特點(diǎn)類似于自動篩選 也可以把篩選結(jié)果復(fù)制到本表的其他位置或其他表中 在復(fù)制時可以選擇篩選后的數(shù)據(jù)列 3 可篩選不重復(fù)記錄 2 高級篩選的使用方法 高級篩選需要在數(shù)據(jù)區(qū)外設(shè)置一個條件區(qū)域 由標(biāo)題行和條件行組成 篩選條件行允許使用帶運(yùn)算符的表達(dá)式 還可以同時設(shè)置多列條件 或多行條件的表達(dá)式 條件種類涵蓋自動篩選中所有定制格式的條件 包括等于 大于 小于 大于等于 小于等于 包含等 3 篩選條件的種類 1 不包含單元格引用的篩選條件 a 不帶通配符的篩選條件 500 表示篩選出大于500的記錄 2002 4 7 表示大于等于2002年4月7日的記錄b 帶通配符的條件設(shè)置 代表多個字符 代表單個字符 代表篩選 代表篩選 c 文本型條件的設(shè)置 王 表示以王開始的任何字符串 王 表示篩選只有一個字符王的記錄 M 表示所有打頭字母在M到Z提示 此類表達(dá)式的特點(diǎn)不