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