excel常用函數(shù)及實用技巧.ppt

《excel常用函數(shù)及實用技巧.ppt》由會員分享，可在線閱讀，更多相關《excel常用函數(shù)及實用技巧.ppt（35頁珍藏版）》請在裝配圖網(wǎng)上搜索。

EXCEL常用函數(shù)及實用技巧 主要內(nèi)容 Excel函數(shù)用法 Excel實用小技巧 Excel基礎常識 1 常用術語 工作簿 單元格 工作表 單元格區(qū)域 xls sheet A1 列號行號 A1 D13 對角上兩個單元格 常用的數(shù)據(jù)類型 文本 數(shù)值 日期 貨幣等 注 各種操作均需區(qū)別對待每類對象 2 相對地址和絕對地址 單元格的列標和行號又稱為地址 在列標和行號前加入 符號便構成了絕對地址A1 R1C1 A1 1 A A 1在地址輸入時 相對地址和絕對地址F4鍵進行循環(huán)轉換Excel中公式的相對地址 在該公式自身的位置發(fā)生變化 或在其引用對象的位置發(fā)生變化時 會自動進行相應的調(diào)整 3 單元格的選取 1 連續(xù)單元格的選取 選取相鄰的行 列 shift 不相鄰的單元格選取 選取不相鄰的行 列 ctrl 4 工作表的重命名 復制 移動和隱藏 工作表的重命名 復制 移動和隱藏1 重命名工作表2 工作表在本工作薄復制或移動到新工作薄或其他工作薄3 工作表可以隱藏和顯現(xiàn) 格式 工作表 隱藏or取消隱藏 位置 在選定工作表標簽鼠標右擊 5 保護工作表 薄 區(qū)域 位置 工具 保護 保護工作簿 1 不能移動刪除工作頁2 不能增加新工作頁3 現(xiàn)有工作頁可以修改 若此頁沒設保護 保護工作表 1 當前工作表只讀 2 默認保護全部區(qū)域 3 每個工作表可設不同密碼 密碼可為空 二 Excel常用函數(shù) 函數(shù)及公式概念介紹 常用函數(shù)表達式 函數(shù)使用的關鍵點 事半功倍的好工具 函數(shù) 何為函數(shù) Excel函數(shù)即是預先定義 執(zhí)行計算 分析等處理數(shù)據(jù)任務的特殊公式 簡單說 用機器幫我們邏輯 數(shù)值等方面的計算統(tǒng)計 如果您遇到了麻煩趕緊找它幫忙 函數(shù)格式說明 舉例 SUM A1 B 公式開始 函數(shù)名 聞其名 見其意 參數(shù) 區(qū)域 常用函數(shù)介紹 1 求和函數(shù) SUM SUMIF 2 查找函數(shù) VLOOKUP HLOOKUP 3 其他函數(shù) Average Max Min Countif Subtotal 1 常用的求和函數(shù)1 Sum 進行基本數(shù)值求和 語法 sum number1 number2 2 Sumif 根據(jù)條件求和 語法 sumif range criteria sum range SUM用途 返回某一單元格區(qū)域中所有數(shù)字之和 語法 SUM number1 number2 參數(shù) Number1 number2 為1到N個需要求和的數(shù)值 只顯示公式 無計算結果 SUMIF 條件求和 用途 根據(jù)指定條件對若干單元格 區(qū)域或引用求和 語法 SUMIF range criteria sum range 參數(shù) Range為用于條件判斷的單元格區(qū)域 Criteria是由數(shù)字 邏輯表達式等組成的判定條件 Sum range為需要求和的單元格 區(qū)域或引用 2 常用的查找函數(shù) Vlookup Hlookup函數(shù) VLOOKUP 用于在數(shù)據(jù)表的首列查找指定的數(shù)值 并返回數(shù)據(jù)表當前行中指定列處的值 Hlookup用于在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值 并由此返回表格或數(shù)組當前行中指定列處的數(shù)值當比較值位于數(shù)據(jù)表的首行 并且要查找下面給定行中的數(shù)據(jù)時 請使用Hlookup當比較值位于數(shù)據(jù)表的最左列 并且要查找右面給定列中的數(shù)據(jù)時 請使用Vlookup 8 Vlookup Hlookup函數(shù) 使用方法 HLOOKUP lookup value table array row index num rang lookup VLOOKUP lookup value table array col index num rang lookup 所要查找的值 用于查找數(shù)據(jù)的區(qū)域 所要查找的數(shù)據(jù)必須與該區(qū)域首行 或最左列 的數(shù)據(jù)進行比較 返回對應該行序號的單元格 返回對應該列序號的單元格 FALSE 精確匹配 或TRUE 近似匹配 3 其他函數(shù)1 Average 求平均數(shù) 注意不包括空白和字符AVG AVERAGE Number1 Number2 Number3 2 Max 查找一組數(shù)的最大值MAX MAX Number1 Number2 Number3 3 Min 查找一組數(shù)的最小值MIN MIN Number1 Number2 Number3 常用函數(shù)快捷方式 在excel表格的右下角有一個常用函數(shù)快捷方式的區(qū)域 如同所示 可以右鍵點擊該區(qū)域 使用六種常用函數(shù)這六種常用函數(shù) 分別是 平均值 AVERAGE 計數(shù) COUNTA 計數(shù)值 COUNT 最大值 MAX 最小值 MIN 求和 SUM COUNTIF COUNTIF函數(shù)功能 統(tǒng)計符合條件要求的單元格的數(shù)量例 在B5單元格中輸入 countif B2 B4 20 可以求出在B2到B4這三個單元格中大于20的數(shù)字有幾個 SUBTOTAL函數(shù) SUBTOTAL函數(shù) 為了分別統(tǒng)計各個專業(yè)的人數(shù) 可以在D1單元格中輸入 SUBTOTAL 3 D3 D30 然后利用 數(shù)據(jù) 篩選 自動篩選 功能即可 SUBTOTAL函數(shù) 用途 對篩選列表中的可見單元格進行運算語法及功能SUBTOTAL function num ref1 ref2 參數(shù) Function num為1到11之間的自然數(shù) 用來指定分類匯總計算使用的函數(shù) 所引用區(qū)域 如何取出單元格中的部分字符 1 Left 從左邊開始截取指定數(shù)目的字符 語法 left text num chars 參數(shù) Text是包含要提取字符的文本字符串 Num chars指定要由LEFT所提取的字符數(shù) 2 Right 從右邊開始截取指定數(shù)目的字符 語法 right text num chars 用法舉例 left ad246g 3 ad2 RIGHT ad246g 3 46g 3 Mid 從文本指定位置開始截取指定數(shù)目的字符語法 mid text start num num chars 參數(shù) Text是包含要提取字符的文本字符串 Start num是文本中要提取的第一個字符的位置 文本中第一個字符的start num為1 以此類推 Num chars指定希望MID從文本中返回字符的個數(shù) 用法舉例 mid 351429198205090303 7 8 19820509 函數(shù)使用關鍵點 明確達到的目標 明確各表之間的關聯(lián)性使得表格自動化 將復雜的邏輯關系拆分為若干簡單步驟 明確文本和數(shù)字的區(qū)別 公式中級聯(lián)符號要求半角狀態(tài)錄入 自己編錄公式 切記 開頭 函數(shù)運用靈活性強 同時也使用一出現(xiàn)問題 必須核查結果是否正確 學會查看別人的使用方法 編輯欄 告訴你公式的使用細節(jié) 千萬別忘了 函數(shù)的常見錯誤 EXCEL實用技巧 1 頁面顯示比例的放大或縮小 按住ctrl 鼠標滾動 在word powerpoint中也可使用 位置 格式 條件格式 2 條件格式 條件格式1 不是宏程序 卻是可以智能判斷條件的靈活格式 具有醒目提示的作用 方便快捷 2 不同條件執(zhí)行不同單元格格式 最多3個條件 執(zhí)行順序為條件一 二 三 3 條件可以是本單元格數(shù)值大小條件 還可以是設置的公式函數(shù) 可將功能大大加強 3 數(shù)據(jù)有效性 位置 數(shù)據(jù) 有效性 數(shù)據(jù)有效性1 對輸入格式進行限制 限制為日期 數(shù)值 序列等格式 2 序列下拉框提示選項 實現(xiàn)快捷輸入 3 非限定值為無效數(shù)據(jù) 警告提醒 4 序列來源可手工輸入或引用單元區(qū)域 4 如何在EXCEL中快速輸入身份證號碼 直接輸入則顯示科學計數(shù)法 應該設置成文本格式 或者先輸入 即英文狀態(tài)半角的小單引號 再輸入身份證號也可以 5 輸入m的立方 例如 m3 選中 3 右擊 單元格格式 選中上標 6 如何在excel輸入分數(shù) 例 如需在表中輸入 1 3在單元格中輸入 0空格1 3 7 禁止復制隱藏行或列中的數(shù)據(jù) 如果你復制了包含隱藏列 或行 的一個數(shù)據(jù)區(qū)域 然后把它粘貼到一個新的工作表 那么Excel把隱藏列也粘貼過來了 要想避免這種情況 選定要復制的數(shù)據(jù)區(qū)域 編輯 定位 定位條件 定位條件 可見單元格 或按F5 復制經(jīng)篩選后的單元格的操作方法同上 8 表頭和格式刷 表頭斜線步驟1 劃直線2 添加文本框3 去掉文本的邊框 調(diào)整直線 格式刷1 格式刷就是將源單元格的格式屬性應用到所選單元格或范圍2 格式刷不改變數(shù)值 公式 批注等3 格式包括行寬 填充 邊框 字體大小顏色等 9 在一個單元格內(nèi)快速換行 Alt 回車鍵 即可輸入第二行內(nèi)容 再按 Alt 回車鍵 輸入第三行以此類推 10 取消超級鏈接 1 如果正在輸入E mail地址 在輸入完畢后敲回車鍵 剛才輸入的內(nèi)容會變成藍色 此時單擊智能標記選擇 撤消超鏈接 命令即可 2 如果在工作表中已存在超級鏈接 右鍵單擊單元格 在快捷菜單中選擇 取消超鏈接 命令即可 大家分享在平時工作中用到的經(jīng)驗 謝謝大家