2010Excel培訓教程【完整版】.ppt

1 Excel培訓教程 上 2 開始語 Excel是MicrosoftOffice軟件包的組成部分 按啟動Windows應(yīng)用的方法運行它后 屏幕上就會顯示其操作窗口 并且打開一張名為Book1 工作薄1 的電子報表 如圖所示 標題欄 下拉菜單 工具欄 狀態(tài)欄 工作表區(qū) 列標 行號 滾動條 工作表標簽 單元格 3 建立第一份電子報表 工作簿 在MicrosoftExcel中是處理和存儲數(shù)據(jù)的文件 每一個工作簿都可以包含多張工作表 因此可在一份文件中管理多種類型的相關(guān)信息 工作表 就是顯示在屏幕上的 由表格組成的一個區(qū)域 此區(qū)域稱為 工作表區(qū) 各種數(shù)據(jù)將通過它來輸入顯示 下面將以建立一張員工工資表的操作來說明Excel的一些基本操作 步驟一 參見右圖 單擊A1單元格 工作表提供了一系列的單元格 各單元格也各有一個名稱 將光標移至單元格內(nèi)后 其狀態(tài)將變成一個十字形 4 步驟二 輸入 姓名 參見下圖 按一下鍵盤上的右方向鍵 然后輸入 年齡 接著在位于右旁的兩列中分別輸入 職務(wù) 工資額 使用鍵盤上的左 右 上 下方向鍵可以將光標移至各單元格上 這就選定了當前單元格 從而讓您在各單元格中輸入文字信息 5 步驟四 移動鼠標單擊A2單元格 然后參照上面的內(nèi)容 輸入其它單元格中的內(nèi)容 結(jié)果參見下圖 完成上述操作后 員工工資表 就編制好了 如圖所示 在這一份電子表格中 所包含的員工數(shù)目有限 但說明了基本的操作方法 中文Excel關(guān)閉當前操作窗口時不會自動保存操作結(jié)果 因此必須先執(zhí)行 文件 下拉菜單中的 保存 命令 否則會出現(xiàn) 是否保存 的提示 6 設(shè)置單元格的格式與MicrosoftWord一樣 在Excel中也能設(shè)置文字的格式 而且此格式將表現(xiàn)在最終的電子表格中 此外 還可以設(shè)置其它與數(shù)據(jù)信息相關(guān)的屬性 下面的操作將結(jié)合 員工工資表 的特點來進行說明 請先單擊左下方的Sheet1標簽 返回 員工工資表 步驟一 單擊行號1 選定此行中的文字內(nèi)容 然后右擊鼠標器 進入圖12所示的快捷菜單 并從中選擇 設(shè)置單元格格式 命令 7 步驟二 進入 單元格格式 對話框后 單擊 字體 選項卡 如圖所示 步驟三 從 字體 窗口中選擇一種新的字體后 從 字形 窗口中選擇一種字形 接著可在 字號 窗口中設(shè)置文字的大小尺寸 此后 您還可以在 字體 選項卡中設(shè)置文字的顏色 以及下劃線 刪除線等屬性 還能預覽設(shè)置結(jié)果 單擊 確定 按鈕后 選定文字就將按新設(shè)置的字體格式顯示在電子報表中 8 設(shè)置貨幣符號步驟一 單擊D列號 選定此列中的所有內(nèi)容 接著右擊鼠標右鍵 然后從快捷菜單中選擇 設(shè)置單元格格式 命令 進入 單元格格式 對話框后選擇 數(shù)字 選項卡 參見下圖 步驟二 在 分類 列表窗中選擇 貨幣 項 步驟三 設(shè)置好 小數(shù)位數(shù) 與 貨幣符號 然后單擊 確定 按鈕 9 加入標題為了加入標題 首先需要在報表將插入一行 以便于書寫它 其次還要合并此行中的各列 步驟一 單擊行號1 然后從 插入 下拉菜單中選擇 行 命令 步驟二 單擊行號1 確保選定新行 然后右擊鼠標進入快捷菜單 接著從中選擇 設(shè)置單元格格式 命令 然后在 單元格格式 對話框中選擇 對齊 選項卡 參見右圖 打開 合并單元格 檢測框 最后單擊 確定 按鈕 步驟三 在 行1 書寫好標題 員工工資表 并參照前面的內(nèi)容 設(shè)置好標題的文字字體與大小尺寸 10 添加 總計 單元格Excel提供有各種用于計算的函數(shù) 其中SUM將用于 總計 計算指定列中所有數(shù)值的和 請按下列步驟進行操作 步驟一 單擊在 工資 列最后的第一個空白單元格 接著從 插入 下拉菜單中選擇 函數(shù) 命令 參見左圖 在 函數(shù)名 列表窗中選定某個函數(shù)后 它的功能描述字將顯示在 函數(shù)分類 列表窗的下方 您可以據(jù)此了解到這個所提供的各函數(shù)功能 操作時 可先從 函數(shù)分類 列表窗中選定某類函數(shù) 然后從 函數(shù)名 列表窗中選擇此類函數(shù)的某一個 11 步驟二 在 粘貼函數(shù) 對話框的 函數(shù)名 列表窗中選定SUM函數(shù) 然后單擊 確定 按鈕 參見下圖 步驟三 在選定求和范圍后 單擊SUM函數(shù)計算的結(jié)果信息框中的 確定 按鈕 注 Excel提供有自動計算功能 如選定 工資額 列中的各單元格后 狀態(tài)欄中就將顯示各項數(shù)據(jù)記錄的總值 若選定單元格后右擊狀態(tài)欄 然后從一份快捷菜單中還能選擇顯示這些選定單元格內(nèi)各數(shù)字的平均值 最大值或最小值等 12 制作員工工資圖表利用Excel提供的圖表功能 可以基于工作表中的數(shù)據(jù)建立圖形表格 這是一種使用圖形來描述數(shù)據(jù)的方法 用于直觀地表達各統(tǒng)計值大小差異 為了制作員工工資圖表 請按下列步驟進行操作 步驟一 將光標移至A3單元格上 然后單擊它并向下拖動 選定各員工的姓名 并按住鍵盤上的Ctrl鍵 將光標移至D3單元格上 向下拖動 選定各員工的工資額后 結(jié)束拖動并釋放Ctrl鍵 結(jié)果應(yīng)如右圖所示 13 步驟二 單擊工具箱中的 圖表向?qū)?按鈕 進入 圖表向?qū)?的第一步操作對話框 注 若按住 按下不放可查看示例 按鈕 子圖形類型 將變成預覽窗口 并且顯示您所選定的圖表類型與電子報表中數(shù)據(jù)的圖表外觀 14 步驟三 在 圖表向?qū)?的第二步操作對話框中 打開 行 單選按鈕 15 步驟四單擊 下一步 按鈕后 在 圖表選項 對話框的 圖表標題 欄中輸入 員工工資圖表 16 步驟五 單擊 下一步 按鈕后 在 圖表位置 對話框中打開 作為新工作表插入 單選按鈕 17 最后 單擊 完成 按鈕 一張工作圖表就產(chǎn)生了 18 單元格和區(qū)域引用引用的作用在于標識工作表上的單元格或單元格區(qū)域 并指明公式中所使用的數(shù)據(jù)的位置 通過引用 可以在公式中使用工作表不同部分的數(shù)據(jù) 或者在多個公式中使用同一單元格的數(shù)值 還可以引用同一工作簿不同工作表的單元格 不同工作簿的單元格 甚至其它應(yīng)用程序中的數(shù)據(jù) 引用不同工作簿中的單元格稱為外部引用 引用其它程序中的數(shù)據(jù)稱為遠程引用 在EXCEL中 存在兩種引用類型 1 A1 引用類型 用字母標志列 從A到IV 共256列 用數(shù)字標志行 從1到65536 2 R1C1 引用類型 用 R 加行數(shù)字和 C 加列數(shù)字來指示單元格的位置 19 在默認狀態(tài)下 MicrosoftExcel使用A1引用類型 如果要引用單元格 請順序輸入列字母和行數(shù)字 例如 D50引用了列D和行50交叉處的單元格 如果要引用單元格區(qū)域 請輸入?yún)^(qū)域左上角單元格的引用 冒號 和區(qū)域右下角單元格的引用 示例如右圖所示 20 引用工作表外部數(shù)據(jù)隨著大量的數(shù)據(jù)積累 還需要通過鏈接或者外部引用來共享其它工作簿或工作表中的數(shù)據(jù) 如下例中 AVERAGE工作表函數(shù)將計算同一工作簿中Marketing工作表B1 B10區(qū)域內(nèi)的平均值 21 一 什么是函數(shù)Excel中所提的函數(shù)其實是一些預定義的公式 它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進行計算 用戶可以直接用它們對某個區(qū)域內(nèi)的數(shù)值進行一系列運算 如分析和處理日期值和時間值 確定貸款的支付額 確定單元格中的數(shù)據(jù)類型 計算平均值 排序顯示和運算文本數(shù)據(jù)等等 例如 SUM函數(shù)對單元格或單元格區(qū)域進行加法運算 參數(shù)參數(shù)可以是數(shù)字 文本 形如TRUE或FALSE的邏輯值 數(shù)組 形如 N A的錯誤值或單元格引用 給定的參數(shù)必須能產(chǎn)生有效的值 參數(shù)也可以是常量 公式或其它函數(shù) 參數(shù)不僅僅是常量 公式或函數(shù) 還可以是數(shù)組 單元格引用 常量常量是直接鍵入到單元格或公式中的數(shù)字或文本值 或由名稱所代表的數(shù)字或文本值 例如 日期10 9 96 數(shù)字210和文本 QuarterlyEarnings 都是常量 公式或由公式得出的數(shù)值都不是常量 22 函數(shù)的結(jié)構(gòu)如下圖所示 函數(shù)的結(jié)構(gòu)以函數(shù)名稱開始 后面是左圓括號 以逗號分隔的參數(shù)和右圓括號 如果函數(shù)以公式的形式出現(xiàn) 請在函數(shù)名稱前面鍵入等號 23 嵌套函數(shù)所謂嵌套函數(shù) 就是指在某些情況下 您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用 也就是說一個函數(shù)可以是另一個函數(shù)的參數(shù) 例如下圖中所示的公式使用了嵌套的AVERAGE函數(shù) 并將結(jié)果與50相比較 這個公式的含義是 如果單元格F2到F5的平均值大于50 則求G2到G5的和 否則顯示數(shù)值0 24 Excel的計算公式 在Excel中 公式 是在單元格中執(zhí)行計算的方程式 如一個執(zhí)行數(shù)學計算的加 減就是一種簡單的公式 在前面的操作中 對工資額的總計就使用了這樣的公式 此時若單擊顯示工資額總計的單元格 D9 當它處于選定狀態(tài) 編輯欄 中就會顯示所使用的公式 如圖所示 25 只要您記住公式的應(yīng)用法則 無論在單元格中 還是在 編輯欄 中都能建立并使用公式的 以下是一個公式范例 如果正確的創(chuàng)建了計算公式 那么在中文Excel2000的默認狀態(tài)下 其計算值就會顯示在單元格中 公式則顯示在 編輯欄 中 如果要使工作表中所有的公式在顯示公式內(nèi)容與顯示結(jié)果之間切換 可按下CTRL 組合鍵 位于鍵盤左上側(cè) 26 Excel的運算符 1 算術(shù)運算符用于完成基本的數(shù)學運算 如加法 減法和乘法 連接數(shù)字和產(chǎn)生數(shù)字結(jié)果等 各算術(shù)運算與用途如下表所列 27 2 比較運算符用于比較兩個值 結(jié)果將是一個邏輯值 即不是TRUE 真 就是FALSE 假 與其它的計算機程序語言查類似 這類運算符還用于按條件做下一步運算 各算術(shù)運算符名稱與用途如下表所列 28 3 文本運算符這實際上是一個文字串聯(lián)符 用于加入或連接一個或更多字符串來產(chǎn)生一大段文本 如 North wind 結(jié)果將是Northwind 4 引用運算符引用下表的運算符可以將單元格區(qū)域合并起來進行計算 29 如果公式中使用了多個運算符 Excel將按下表所列的順序進行運算 如果公式中包含了相同優(yōu)先級的運算符 例如 同時包含了乘法和除法運算符 則將從左到右進行計算 如果要修改計算的順序 可把需要首先計算的部分放在一對圓括號內(nèi) 30 應(yīng)用計算公式的范例 1 求和應(yīng)用 SUM F6 D7 E7 2 合并 姓 和 名 D5 E53 合并日期與文本 時間 TEXT F5 d mmm yy 4 按百分比增加 F 5 1 5 5 基于單個條件求和 SUMIF B3 B8 20 D3 D8 31 認識TEXT工作函數(shù)上面 合并日期與文本 公式中的TEXT是一個常用的工作表函數(shù) 能將一數(shù)值轉(zhuǎn)換為按指定數(shù)字格式來表示的文本 語法 TEXT value format text Value為數(shù)值 計算結(jié)果為數(shù)值的公式 或?qū)?shù)值單元格的引用 Format text所要選用的文本型數(shù)字格式 即 單元格格式 對話框 數(shù)字 選項卡的 分類 列表框中顯示的格式 Format text不能包含星號 也不能是 常規(guī) 型 TEXT工作函數(shù)還能圓整數(shù)值 或者轉(zhuǎn)換日期格式示例 TEXT 2 715 0 00 等于 2 72 TEXT 4 15 91 mmmmdd yyyy 等于 April15 1991 說明 通過 格式 菜單調(diào)用 單元格格式 對話框 然后在 數(shù)字 選項卡上設(shè)置單元格的格式 只會改變單元格的格式而不會影響其中的數(shù)值 使用函數(shù)TEXT可以將數(shù)值轉(zhuǎn)換為帶格式的文本 而其結(jié)果將不再作為數(shù)字參與計算 32 認識SUMIF工作函數(shù)這是上面 使用單個條件求和 公式中出現(xiàn)的工作函數(shù) 用于根據(jù)指定條件對若干單元格求和 語法 SUMIF range criteria sum range 其中 Range為用于條件判斷的單元格區(qū)域 Criteria為確定哪些單元格將被相加求和的條件 其形式可以為數(shù)字 表達式或文本 例如 條件可以表示為32 32 32 apples Sum range為需要求和的實際單元格 只有當Range中的相應(yīng)單元格滿足條件時 才對sum range中的單元格求和 如果省略sum range 則直接對Range中的單元格求和 33 相對引用與絕對引用根據(jù)所要完成的任務(wù) 既可以使用相對單元格引用 也可以使用絕對單元格引用 前者引用的是相對于公式單元格位于某一位置處的單元格 后者引用的是特定位置處的單元格 當使用相對引用生成公式時 對單元格或區(qū)域的引用通?；谒鼈兣c公式單元格的相對位置 當復制使用相對引用的公式時 被粘貼公式中的引用將被更新 并指向與當前公式位置相對應(yīng)的其它單元格 如果不希望在復制公式時 引用發(fā)生改變 請使用絕對引用 例如 如果公式將單元格A5乘以單元格C1 A5 C1 將公式復制到另一單元格中 則公式中的兩個引用都將改變 如果在不希望改變的引用前加一個美元符號 就可以對單元格C1進行絕對引用 如果要對單元格C1進行絕對引用 請如下在公式中加入美元符號 A5 C 1相對引用與絕對引用也可混合使用 如 C1或 C 1 34 數(shù)據(jù)清單創(chuàng)建數(shù)據(jù)清單 數(shù)據(jù)清單 是工作表中包含相關(guān)數(shù)據(jù)的一系列數(shù)據(jù)行 如前面所建立的 員工工資表 這張電子報表就包含有這樣的數(shù)據(jù)行 它可以像數(shù)據(jù)庫一樣接受瀏覽與編輯等操作 數(shù)據(jù)清單中的列是數(shù)據(jù)庫中的字段 數(shù)據(jù)清單中的列標志是數(shù)據(jù)庫中的字段名稱 數(shù)據(jù)清單中的每一行對應(yīng)數(shù)據(jù)庫中的一個記錄使用鼠標器選定單元格區(qū)域 Excel會在需要的時候自動建立一份數(shù)據(jù)清單 注意 在每張工作表上只能建立并使用一份數(shù)據(jù)清單 也應(yīng)避免在一張工作表上建立多份數(shù)據(jù)清單 因為某些數(shù)據(jù)清單管理功能 如篩選 等一次也只能在一份數(shù)據(jù)清單中使用 35 使用數(shù)據(jù)清單 應(yīng)當注意遵循下列準則 1 將類型相同的數(shù)據(jù)項置于同一列中2 使數(shù)據(jù)清單獨立于其它數(shù)據(jù)在工作表中 數(shù)據(jù)清單與其他數(shù)據(jù)間至少要留出一個空列和一個空行 以便在執(zhí)行排序 篩選或插入自動匯總等操作時 有利于Excel檢測和選定數(shù)據(jù)清單 3 將關(guān)鍵數(shù)據(jù)置于清單的頂部或底部這樣可避免將關(guān)鍵數(shù)據(jù)放到數(shù)據(jù)清單的左右兩側(cè) 因為這些數(shù)據(jù)在Excel篩選數(shù)據(jù)清單時可能會被隱藏 4 注意顯示行和列在修改數(shù)據(jù)清單之前 應(yīng)確保隱藏的行或列也被顯示 因為 如果清單中的行和列沒有被顯示 那么數(shù)據(jù)有可能會被刪除 5 避免空行和空列避免在數(shù)據(jù)清單中隨便放置空行和空列 將有利于Excel檢測和選定數(shù)據(jù)清單 因為單元格開頭和末尾的多余空格會影響排序與搜索 所以不要在單元格內(nèi)文本前面或后面鍵入空格 可采用縮進單元格內(nèi)文本的辦法來代替鍵入空格 36 編輯數(shù)據(jù)記錄對于數(shù)據(jù)清單中的數(shù)據(jù)記錄 Excel允許新建 刪除數(shù)據(jù)記錄 按某些條件查詢數(shù)據(jù)記錄 而且操作簡單 只需要從 數(shù)據(jù) 下拉菜單中選擇 記錄單 命令 進入下圖所示的數(shù)據(jù)記錄單對話框就能完成這些操作 37 查詢數(shù)據(jù)記錄要制定一個查詢條件 只需單擊 條件 按鈕 進入如圖所示的數(shù)據(jù)記錄單對話框 然后在各字段框中輸入查詢內(nèi)容即可 注意 在數(shù)據(jù)記錄單中一次最多只能顯示32個字段 38 排序數(shù)據(jù)記錄在Excel中也可以根據(jù)現(xiàn)有的數(shù)據(jù)資料對數(shù)據(jù)值進行排序 按遞增方式排序的數(shù)據(jù)類型及其數(shù)據(jù)的順序為 1 數(shù)字 順序是從小數(shù)到大數(shù) 從負數(shù)到正數(shù) 2 文字和包含數(shù)字的文字 其順序是 0123456789 空格 ABCDEFGHIJKLMNOPQRSTUVWXYZ 3 邏輯值 False在True之前 4 錯誤值 所有的錯誤值都是相等的 5 空白 不是空格 單元格總是排在最后 遞減排序的順序與遞增順序恰好相反 但空白單元格將排在最后 日期 時間和漢字也當文字處理 是根據(jù)它們內(nèi)部表示的基礎(chǔ)值排序 39 最簡單的排序操作是使用 常用 工具欄中的按鈕 在這個工具欄上有兩個用于排序的按鈕 圖標上有AZ與向下的箭頭的按鈕 用于按降序方式重排數(shù)據(jù) 按鈕上標有ZA與向下的箭頭的按鈕用于遞減排序 注意 若選定了某一列后來使用以上所示的操作 排序?qū)⒅话l(fā)生在這一列中 其它列的數(shù)據(jù)排列將保持不變 其結(jié)果可能會破壞原始記錄結(jié)構(gòu) 造成數(shù)據(jù)錯誤 可通過 數(shù)據(jù) 菜單中的 排序 命令 打開右圖所示對話框 對排序條件進行相應(yīng)設(shè)置 排序 對話框 可以使用的各選項功能所述如下 40 1 主要關(guān)鍵字通過一份下拉菜單選擇排序字段 打開位于右旁的單選按鈕 可控制按遞增或遞減的方式進行排序 2 次要關(guān)鍵字前面設(shè)置的 主要關(guān)鍵字 列中出現(xiàn)了重復項 就將按次要關(guān)鍵字來排序重復的部分 3 第三關(guān)鍵字4 有標題行在數(shù)據(jù)排序時 包含清單的第一行 5 無標題行在數(shù)據(jù)排序時 不包含清單的第一行 注意 如果排序結(jié)果與所預期的不同 說明排序數(shù)據(jù)的類型有出入 若想得到最好的結(jié)果 就要確保列中所有單元格屬于同一數(shù)據(jù)類型 應(yīng)避免在連續(xù)的單元格中交替輸入數(shù)字或文字 若要將數(shù)字以文字方式輸入 如郵政編碼或產(chǎn)品編號 請在數(shù)字之前加上一個省略符號 如 610000 或在輸入之前把該列格式化為文字 或輸成中文符號 如 610000 41 篩選數(shù)據(jù)Excel提供有兩條用于篩選的命令 自動篩選 和 高級篩選 為了使用 自動篩選 命令 可以按下列步驟進行操作 步驟一 從 數(shù)據(jù) 下拉菜單中選擇 篩選 命令 然后從 篩選 子菜單中選擇 自動篩選 命令 此后 數(shù)據(jù)清單中第一行的各列中將分別顯示出一個下拉按鈕 如下圖所示 自動篩選就將通過它們來進行 42 高級篩選數(shù)據(jù)使用高級篩選功能可以對某個列或者多個列應(yīng)用多個篩選條件 為了使用此功能 在工作表的數(shù)據(jù)清單上方 至少應(yīng)有三個能用作條件區(qū)域的空行 而且數(shù)據(jù)清單必須有列標 條件區(qū)域 包含一組搜索條件的單元格區(qū)域 可以用它在高級篩選篩選數(shù)據(jù)清單的數(shù)據(jù) 它包含一個條件標志行 同時至少有一行用來定義搜索條件 有了條件區(qū)域 就可以按下列操作步驟來進行高級篩選 步驟一 選擇數(shù)據(jù)清單中含有要篩選值的列標 將其復制到條件區(qū)域中的第一空行里的某個單元格 步驟二 在條件區(qū)域中輸入篩選條件 如圖所示 43 步驟三 從 數(shù)據(jù) 下拉菜單中選擇 篩選 命令 然后從 篩選 子菜單中選擇 高級篩選 命令 進入圖所示的 高級篩選 對話框 步驟四 單擊 高級篩選 對話框中 條件區(qū)域 設(shè)置按鈕后 單擊選定條件區(qū)域中的條件 然后再單擊此按鈕返回 高級篩選 對話框 最后單擊 確定 按鈕 結(jié)束操作 在 高級篩選 對話框中進行操作時 若篩選后要隱藏不符合條件的數(shù)據(jù)行 并讓篩選的結(jié)果顯示在數(shù)據(jù)清單中 可打開 在原有區(qū)域顯示篩選結(jié)果 單選按扭 若要將符合條件的數(shù)據(jù)行復制到工作表的其他位置 則需要打開 將篩選結(jié)果復制到其他位置 單選按鈕 并通過 復制到 編輯框指定粘貼區(qū)域的左上角 從而設(shè)置復制位置 44 Excel培訓教程 下 45 Excel函數(shù)函數(shù)的種類 1 數(shù)學和三角函數(shù) 2 邏輯函數(shù) 3 文本 日期與時間函數(shù) 4 查詢和引用函數(shù) 5 統(tǒng)計函數(shù) 6 工程函數(shù) 7 財務(wù)函數(shù) 8 信息函數(shù) 9 數(shù)據(jù)庫函數(shù) 10 用戶自定義函數(shù) 46 1 數(shù)學和三角函數(shù) 47 48 49 2 邏輯函數(shù) 邏輯函數(shù)是用來判斷真假值 或者進行復合檢驗的Excel函數(shù) 在Excel中提供了六種邏輯函數(shù) 即AND OR NOT FALSE IF TRUE函數(shù) 一 AND函數(shù)所有參數(shù)的邏輯值為真時返回TRUE 只要一個參數(shù)的邏輯值為假即返回FALSE 語法 AND logical1 logical2 其中Logical1 logical2 表示待檢測的1到30個條件值 參數(shù)必須是邏輯值 或者包含邏輯值的數(shù)組或引用 例 AND B2 30 B2 60 AND B1 B3 二 OR函數(shù)OR函數(shù)指在其參數(shù)組中 任何一個參數(shù)邏輯值為TRUE 即返回TRUE 它與AND函數(shù)的區(qū)別在于 AND函數(shù)要求所有函數(shù)邏輯值均為真 結(jié)果方為真 而OR函數(shù)僅需其中任何一個為真即可為真 50 三 NOT函數(shù)NOT函數(shù)用于對參數(shù)值求反 當要確保一個值不等于某一特定值時 可以使用NOT函數(shù) 簡言之 就是當參數(shù)值為TRUE時 NOT函數(shù)返回的結(jié)果恰與之相反 結(jié)果為FALSE 四 TRUE FALSE函數(shù)TRUE FALSE函數(shù)用來返回參數(shù)的邏輯值 由于可以直接在單元格或公式中鍵入值TRUE或者FALSE 因此這兩個函數(shù)通?？梢圆皇褂?五 IF函數(shù)IF函數(shù)用于執(zhí)行真假值判斷后 根據(jù)邏輯測試的真假值返回不同的結(jié)果 因此If函數(shù)也稱之為條件函數(shù) 語法 IF logical test value if true value if false 例 IF SUM C5 F5 SUM C5 F5 IF B11 60 合格 不合格 51 3 文本 日期與時間函數(shù) 一 文本函數(shù) 一 大小寫轉(zhuǎn)換LOWER 將一個文字串中的所有大寫字母轉(zhuǎn)換為小寫字母 UPPER 將文本轉(zhuǎn)換成大寫形式 PROPER 將文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫 將其余的字母轉(zhuǎn)換成小寫 示例 Lower pLeaseComEHere pleasecomehere upper pLeaseComEHere PLEASECOMEHERE proper pLeaseComEHere PleaseComeHere 52 二 取出字符串中的部分字符可以使用Mid Left Right等函數(shù)從長字符串內(nèi)獲取一部分字符 具體語法為 LEFT函數(shù) LEFT text num chars 其中Text是包含要提取字符的文本串 Num chars指定要由LEFT所提取的字符數(shù) MID函數(shù) MID text start num num chars 其中Text是包含要提取字符的文本串 Start num是文本中要提取的第一個字符的位置 RIGHT函數(shù) RIGHT text num chars 其中Text是包含要提取字符的文本串 Num chars指定希望RIGHT提取的字符數(shù) 比如 LEFT Thisisanapple 4 ThisRIGHT Thisisanapple 5 appleMID Thisisanapple 6 2 is 53 三 去除字符串的空白在字符串形態(tài)中 空白也是一個有效的字符 但是如果字符串中出現(xiàn)空白字符時 容易在判斷或?qū)Ρ葦?shù)據(jù)是發(fā)生錯誤 在Excel中 可以使用Trim函數(shù)清除字符串中的空白 語法 TRIM text 其中Text為需要清除其中空格的文本 比如 TRIM MynameisMary MynameisMary注意 Trim函數(shù)不會清除單詞之間的單個空格 如果連這部分空格都需清除的話 建議使用替換功能 54 四 字符串的比較EXACT函數(shù)測試兩個字符串是否完全相同 如果它們完全相同 則返回TRUE 否則 返回FALSE 函數(shù)EXACT能區(qū)分大小寫 但忽略格式上的差異 語法 EXACT text1 text2 Text1為待比較的第一個字符串 Text2為待比較的第二個字符串 例如 EXACT China china False 55 文本函數(shù)集 56 57 58 二 日期與時間函數(shù) 一 取出當前系統(tǒng)時間 日期信息用于取出當前系統(tǒng)時間 日期信息的函數(shù)主要有NOW TODAY 語法形式均為函數(shù)名 二 取得日期 時間的部分字段值如果需要單獨的年份 月份 日數(shù)或小時的數(shù)據(jù)時 可以使用HOUR DAY MONTH YEAR函數(shù)直接從日期 時間中取出需要的數(shù)據(jù) 示例 設(shè)E5 2003 5 3012 30PM 如果要取E5的年份 月份 日數(shù)及小時數(shù) 可以分別采用相應(yīng)函數(shù)實現(xiàn) YEAR E5 2003MONTH E5 5DAY E5 30HOUR E5 12 59 日期與時間函數(shù)集 60 61 4 查詢和引用函數(shù) 1 引用的作用在Excel中引用的作用在于標識工作表上的單元格或單元格區(qū)域 并指明公式中所使用的數(shù)據(jù)的位置 通過引用 可以在公式中使用工作表不同部分的數(shù)據(jù) 或者在多個公式中使用同一單元格的數(shù)值 還可以引用同一工作簿不同工作表的單元格 不同工作簿的單元格 甚至其它應(yīng)用程序中的數(shù)據(jù) 2 引用的含義關(guān)于引用需要了解如下幾種情況的含義 外部引用 不同工作簿中的單元格的引用稱為外部引用 遠程引用 引用其它程序中的數(shù)據(jù)稱為遠程引用 相對引用 在創(chuàng)建公式時 單元格或單元格區(qū)域的引用通常是相對于包含公式的單元格的相對位置 絕對引用 如果在復制公式時不希望Excel調(diào)整引用 那么請使用絕對引用 即加入美元符號 如 C 1 62 3 引用的表示方法關(guān)于引用有兩種表示的方法 即A1和R1C1引用樣式 1 引用樣式一 默認 A1A1的引用樣式是Excel的默認引用類型 這種類型引用字母標志列 從A到IV 共256列 和數(shù)字標志行 從1到65536 這些字母和數(shù)字被稱為行和列標題 如果要引用單元格 請順序輸入列字母和行數(shù)字 例如 C25引用了列C和行25交叉處的單元格 如果要引用單元格區(qū)域 請輸入?yún)^(qū)域左上角單元格的引用 冒號 和區(qū)域右下角單元格的引用 如A20 C35 2 引用樣式二 R1C1在R1C1引用樣式中 Excel使用 R 加行數(shù)字和 C 加列數(shù)字來指示單元格的位置 例如 單元格絕對引用R1C1與A1引用樣式中的絕對引用 A 1等價 63 一 ADDRESS COLUMN ROW1 ADDRESS用于按照給定的行號和列標 建立文本類型的單元格地址 語法 ADDRESS Row num Column num Abs num A1 Sheet text Row num指在單元格引用中使用的行號 Column num指在單元格引用中使用的列標 Abs num指明返回的引用類型 1代表絕對引用 2代表絕對行號 相對列標 3代表相對行號 絕對列標 4為相對引用 A1用以指明A1或R1C1引用樣式的邏輯值 如果A1為TRUE或省略 函數(shù)ADDRESS返回A1樣式的引用 如果A1為FALSE 函數(shù)ADDRESS返回R1C1樣式的引用 Sheet text為一文本 指明作為外部引用的工作表的名稱 如果省略sheet text 則不使用任何工作表名 即 ADDRESS 行號 列標 引用類型 引用樣式 工作表名稱 比如 ADDRESS 4 5 1 FALSE Book1 Sheet1 等于 Book1 Sheet1 R4C5 64 2 COLUMN用于返回給定引用的列標 語法形式為 COLUMN reference Reference為需要得到其列標的單元格或單元格區(qū)域 如果省略reference 則假定為是對函數(shù)COLUMN所在單元格的引用 如果reference為一個單元格區(qū)域 并且函數(shù)COLUMN作為水平數(shù)組輸入 則函數(shù)COLUMN將reference中的列標以水平數(shù)組的形式返回 但是Reference不能引用多個區(qū)域 3 ROW用于返回給定引用的行號 語法形式為 ROW reference Reference為需要得到其行號的單元格或單元格區(qū)域 如果省略reference 則假定是對函數(shù)ROW所在單元格的引用 如果reference為一個單元格區(qū)域 并且函數(shù)ROW作為垂直數(shù)組輸入 則函數(shù)ROW將reference的行號以垂直數(shù)組的形式返回 但是Reference不能對多個區(qū)域進行引用 65 二 AREAS COLUMNS INDEX ROWS1 AREAS用于返回引用中包含的區(qū)域個數(shù) 其中區(qū)域表示連續(xù)的單元格組或某個單元格 語法 AREAS reference Reference為對某一單元格或單元格區(qū)域的引用 也可以引用多個區(qū)域 如果需要將幾個引用指定為一個參數(shù) 則必須用括號括起來 例如 AREAS B2 D4 等于1 AREAS B2 D4 E5 F6 I9 等于32 COLUMNS用于返回數(shù)組或引用的列數(shù) 語法 COLUMNS array Array為需要得到其列數(shù)的數(shù)組 數(shù)組公式或?qū)卧駞^(qū)域的引用 例如 COLUMNS A1 C4 等于3 COLUMNS 1 2 3 4 5 6 等于3 66 3 ROWS用于返回引用或數(shù)組的行數(shù) 語法 ROWS array Array為需要得到其行數(shù)的數(shù)組 數(shù)組公式或?qū)卧駞^(qū)域的引用 例如 ROWS A1 C4 等于4 ROWS 1 2 3 4 5 6 等于24 INDEX用于返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用 函數(shù)INDEX 有兩種形式 數(shù)組和引用 數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組 引用形式通常返回引用 語法 INDEX array或reference row num column num area num 返回數(shù)組中指定單元格或單元格數(shù)組的數(shù)值 Array為單元格區(qū)域或數(shù)組常數(shù) Row num為數(shù)組中某行的行序號 函數(shù)從該行返回數(shù)值 Column num為數(shù)組中某列的列序號 函數(shù)從該列返回數(shù)值 需注意的是Row num和column num必須指向array中的某一單元格 否則 函數(shù)INDEX返回錯誤值 REF area num指定的區(qū)域 假如存在多個區(qū)域引用的話 示例 INDEX 1 2 3 4 2 2 等于4 67 三 HLOOKUP LOOKUP MATCH VLOOKUP1 LOOKUP函數(shù)與MATCH函數(shù)LOOKUP函數(shù)可以返回向量 單行區(qū)域或單列區(qū)域 或數(shù)組中的數(shù)值 此系列函數(shù)用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值 并由此返回表格或數(shù)組當前列中指定行處的數(shù)值 當比較值位于數(shù)據(jù)表的首行 并且要查找下面給定行中的數(shù)據(jù)時 使用函數(shù)HLOOKUP 當比較值位于要進行數(shù)據(jù)查找的左邊一列時 使用函數(shù)VLOOKUP 如果需要找出匹配元素的位置而不是匹配元素本身 則應(yīng)該使用函數(shù)MATCH而不是函數(shù)LOOKUP MATCH函數(shù)用來返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置 從以上分析可知 查找函數(shù)的功能 一是按搜索條件 返回被搜索區(qū)域內(nèi)數(shù)據(jù)的一個數(shù)據(jù)值 二是按搜索條件 返回被搜索區(qū)域內(nèi)某一數(shù)據(jù)所在的位置值 68 2 LOOKUP用于返回向量 單行區(qū)域或單列區(qū)域 或數(shù)組中的數(shù)值 函數(shù)LOOKUP有兩種語法形式 向量和數(shù)組 1 向量形式函數(shù)LOOKUP的向量形式是在單行區(qū)域或單列區(qū)域 向量 中查找數(shù)值 然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值 語法 LOOKUP lookup value lookup vector result vector Lookup value為函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值 可以為數(shù)字 文本 邏輯值或包含數(shù)值的名稱或引用 Lookup vector為只包含一行或一列的區(qū)域 Lookup vector的數(shù)值可以為文本 數(shù)字或邏輯值 注意 Lookup vector的數(shù)值必須按升序排序 2 1 0 1 2 A Z FALSE TRUE 否則 函數(shù)LOOKUP不能返回正確的結(jié)果 文本不區(qū)分大小寫 Result vector只包含一行或一列的區(qū)域 其大小必須與lookup vector相同 如果函數(shù)LOOKUP找不到lookup value 則查找lookup vector中小于或等于lookup value的最大數(shù)值 如果lookup value小于lookup vector中的最小值 函數(shù)LOOKUP返回錯誤值 N A 示例 LOOKUP ddd A2 A8 B2 B8 等于18 LOOKUP bbb A2 A8 B2 B8 等于21 69 2 數(shù)組形式函數(shù)LOOKUP的數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值 然后返回數(shù)組的最后一行或最后一列中相同位置的數(shù)值 語法 LOOKUP lookup value array 示例 LOOKUP C a b c d 1 2 3 4 等于3 LOOKUP bump a 1 b 2 c 3 等于2要點 這些數(shù)值必須按升序排列 2 1 0 1 2 A Z FALSE TRUE 否則 函數(shù)LOOKUP不能返回正確的結(jié)果 通常情況下 最好使用函數(shù)HLOOKUP或函數(shù)VLOOKUP來替代函數(shù)LOOKUP的數(shù)組形式 函數(shù)LOOKUP的這種形式主要用于與其他電子表格兼容 70 3 HLOOKUP與VLOOKUPHLOOKUP用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值 并由此返回表格或數(shù)組當前列中指定行處的數(shù)值 VLOOKUP用于在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值 并由此返回表格或數(shù)組當前行中指定列處的數(shù)值 語法 HLOOKUP lookup value table array row index num range lookup VLOOKUP lookup value table array col index num range lookup 其中 Lookup value表示要查找的值 它必須位于自定義查找區(qū)域的最左列 Lookup value可以為數(shù)值 引用或文字串 Table array查找的區(qū)域 用于查找數(shù)據(jù)的區(qū)域 上面的查找值必須位于這個區(qū)域的最左列 可以使用對區(qū)域或區(qū)域名稱的引用 Row index num為table array中待返回的匹配值的行序號 Row index num為1時 返回table array第一行的數(shù)值 row index num為2時 返回table array第二行的數(shù)值 以此類推 Col index num為相對列號 最左列為1 其右邊一列為2 依此類推 Range lookup為一邏輯值 指明函數(shù)HLOOKUP查找時是精確匹配 還是近似匹配 示例 HLOOKUP 年齡 A1 D8 5 TRUE 等于18 VLOOKUP ddd A1 D8 4 TRUE 等于900 71 4 MATCH函數(shù)MATCH函數(shù)有兩方面的功能 兩種操作都返回一個位置值 一是確定區(qū)域中的一個值在一列中的準確位置 這種精確的查詢與列表是否排序無關(guān) 二是確定一個給定值位于已排序列表中的位置 這不需要準確的匹配 語法結(jié)構(gòu)為 MATCH lookup value lookup array match type lookup value為要搜索的值 lookup array 要查找的區(qū)域 必須是一行或一列 match type 匹配形式 有0 1和 1三種選擇 0 表示一個準確的搜索 1 表示搜索小于或等于查換值的最大值 查找區(qū)域必須為升序排列 1 表示搜索大于或等于查找值的最小值 查找區(qū)域必須降序排開 以上的搜索 如果沒有匹配值 則返回 N A 示例 MATCH 1000 D2 D8 0 等于4 MATCH 1000 D2 D8 1 等于4 MATCH 1000 D2 D8 1 等于 N A因為沒有按降序排列 72 四 HYPERLINK所謂HYPERLINK 也就是創(chuàng)建快捷方式 以打開文檔或網(wǎng)絡(luò)驅(qū)動器 甚至INTERNET地址 在某個Excel文檔中 也許您需要引用別的Excel文檔或Word文檔等等 其步驟和方法是這樣的 1 選中您要輸入此函數(shù)的單元格 比如B6 2 單擊常用工具欄中的 粘貼函數(shù) 圖標 將出現(xiàn) 粘貼函數(shù) 對話框 在 函數(shù)分類 框中選擇 常用 在 函數(shù)名 框中選擇HYPERLINK 3 單擊 確定 后將彈出HYPERLINK函數(shù)參數(shù)設(shè)置對話框 4 在 Link location 中鍵入要鏈接的文件或INTERNET地址 比如 c mydocuments Excel函數(shù) doc 在 Friendly name 中鍵入 Excel函數(shù) 這里是假設(shè)我們要打開的文檔位于c mydocuments下的文件 Excel函數(shù) doc 5 單擊 確定 回到您正編輯的Excel文檔 此時再單擊B6單元格就可立即打開用Word編輯的會議紀要文檔 73 六 CHOOSE TRANSPOSE1 CHOOSE函數(shù)函數(shù)CHOOSE可以使用index num返回數(shù)值參數(shù)清單中的數(shù)值 使用函數(shù)CHOOSE可以基于索引號返回多達29個待選數(shù)值中的任一數(shù)值 語法 CHOOSE index num value1 value2 Index num用以指明待選參數(shù)序號的參數(shù)值 Index num必須為1到29之間的數(shù)字 或者是包含數(shù)字1到29的公式或單元格引用 Value1 value2 為1到29個數(shù)值參數(shù) 函數(shù)CHOOSE基于index num 從中選擇一個數(shù)值或執(zhí)行相應(yīng)的操作 參數(shù)可以為數(shù)字 單元格引用 已定義的名稱 公式 函數(shù)或文本 示例 CHOOSE 2 1st 2nd 3rd Finished 等于 2nd SUM A1 CHOOSE 3 A10 A20 A30 等于SUM A1 A30 74 2 TRANSPOSE函數(shù)TRANSPOSE用于返回區(qū)域的轉(zhuǎn)置 函數(shù)TRANSPOSE必須在某個區(qū)域中以數(shù)組公式的形式輸入 該區(qū)域的行數(shù)和列數(shù)分別與array的列數(shù)和行數(shù)相同 使用函數(shù)TRANSPOSE可以改變工作表或宏表中數(shù)組的垂直或水平走向 語法 TRANSPOSE array Array為需要進行轉(zhuǎn)置的數(shù)組或工作表中的單元格區(qū)域 所謂數(shù)組的轉(zhuǎn)置就是 將數(shù)組的第一行作為新數(shù)組的第一列 數(shù)組的第二行作為新數(shù)組的第二列 以此類推 示例 第一步 由于需要轉(zhuǎn)置的多個單元格形式 因此需要以數(shù)組公式的方法輸入公式 故首先選定需轉(zhuǎn)置的范圍 此處我們設(shè)定轉(zhuǎn)置后存放的范圍為A18 G19 第二步 單擊常用工具欄中的 粘貼函數(shù) 圖標 將出現(xiàn) 粘貼函數(shù) 對話框 在 函數(shù)分類 框中選擇 查找與引用函數(shù) 框中選擇TRANSPOSE 單擊 確定 后將彈出TRANSPOSE函數(shù)參數(shù)設(shè)置對話框 第三步 選擇數(shù)組的范圍即A2 B8第四步 由于此處是以數(shù)組公式輸入 因此需要按CRTL SHIFT ENTER組合鍵來確定為數(shù)組公式 此時會在公式中顯示 隨即轉(zhuǎn)置成功 75 5 統(tǒng)計函數(shù) Excel的統(tǒng)計工作表函數(shù)用于對數(shù)據(jù)區(qū)域進行統(tǒng)計分析 例如 統(tǒng)計工作表函數(shù)可以用來統(tǒng)計樣本的方差 數(shù)據(jù)區(qū)間的頻率分布等 統(tǒng)計工作表函數(shù)中提供了很多屬于統(tǒng)計學范疇的函數(shù) 但也有些函數(shù)其實在你我的日常生活中是很常用的 比如求班級平均成績 排名等 以下主要介紹一些常見的統(tǒng)計函數(shù) 一 用于求平均值的統(tǒng)計函數(shù)AVERAGE TRIMMEAN1 求參數(shù)的算術(shù)平均值函數(shù)AVERAGE語法 AVERAGE number1 number2 其中Number1 number2 為要計算平均值的1 30個參數(shù) 這些參數(shù)可以是數(shù)字 或者是涉及數(shù)字的名稱 數(shù)組或引用 如果數(shù)組或單元格引用參數(shù)中有文字 邏輯值或空單元格 則忽略其值 但是 如果單元格包含零值則計算在內(nèi) 76 2 求數(shù)據(jù)集的內(nèi)部平均值TRIMMEAN函數(shù)TRIMMEAN先從數(shù)據(jù)集的頭部和尾部除去一定百分比的數(shù)據(jù)點 然后再求平均值 當希望在分析中剔除一部分數(shù)據(jù)的計算時 可以使用此函數(shù) 語法 TRIMMEAN array percent 其中Array為需要進行篩選并求平均值的數(shù)組或數(shù)據(jù)區(qū)域 Percent為計算時所要除去的數(shù)據(jù)點的比例 例如 如果