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

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