Excel使用培訓(xùn)公式篇.pptx

Excel使用培訓(xùn) 公式篇 信息技術(shù)部辦公軟件技能培訓(xùn)系列 1 本課程您將學(xué)到什么 這不是一個(gè)Excel的入門教程 Excel公式知識(shí)的一個(gè)通覽 可能解答一些困惑您的問題 培訓(xùn)內(nèi)容 Excel公式基礎(chǔ)數(shù)組公式調(diào)試公式函數(shù)規(guī)劃求解 3 Excel公式基礎(chǔ) 4 Excel公式基礎(chǔ) 數(shù)據(jù)類型運(yùn)算符使用函數(shù)在公式中引用數(shù)據(jù) 5 數(shù)據(jù)類型 數(shù)值型包括整數(shù) 小數(shù) 有最大有效數(shù)字位數(shù)限制 1 2 1 2 日期時(shí)間型 2011 06 0112 22 32 字符串 文本 型 HelloExcel 6 運(yùn)算符 算術(shù)運(yùn)算符邏輯運(yùn)算符文本運(yùn)算符引用運(yùn)算符運(yùn)算符優(yōu)先級(jí)使用括號(hào) 7 算術(shù)運(yùn)算符 8 邏輯運(yùn)算符 9 文本運(yùn)算符 與號(hào) 聯(lián)接或連接一個(gè)或多個(gè)文本字符串 以生成一段文本 10 當(dāng)把一個(gè)非文本型的數(shù)據(jù)用此連接時(shí) 會(huì)將非文本型數(shù)據(jù)轉(zhuǎn)換為文本型進(jìn)行計(jì)算 但原值包持類型不變 例如 A1 123 A2 A1 元 引用運(yùn)算符 11 運(yùn)算符優(yōu)先級(jí) 12 若是您不確定某兩個(gè)運(yùn)算符誰的優(yōu)先級(jí)更高 或是要更改求值的順序 請(qǐng)將公式中要先計(jì)算的部分用圓括號(hào)括起來 如 5 2 先單目后雙目 先乘除后加減最后才比較 在公式中引用數(shù)據(jù) A1引用樣式R1C1引用樣式 13 A1引用樣式 默認(rèn)引用樣式默認(rèn)情況下 Excel使用A1引用樣式 此樣式引用字母標(biāo)識(shí)列 以及數(shù)字標(biāo)識(shí)行 例如 B2引用列B和行2交叉處的單元格限制 最大XFD共16384列 最多65536行 Excel2007為1048576行 引用其他工作表中的單元格格式 工作簿名稱 工作表名 單元格 區(qū)域 的引用地址例 計(jì)算另一個(gè)文件book2 xls中Sheet1工作表的B1 B10區(qū)域內(nèi)的平均值 AVERAGE book2 xls Sheet1 b1 b10 14 A1引用樣式 相對(duì)引用公式中的相對(duì)單元格引用 如A1 是基于包含公式和單元格引用的單元格的相對(duì)位置 如果把公式移動(dòng)位置 引用位置也隨之改變 如果多行或多列地復(fù)制或填充公式 引用會(huì)自動(dòng)調(diào)整 默認(rèn)情況下 新公式使用相對(duì)引用 絕對(duì)引用公式中的絕對(duì)單元格引用 如 A 1 總是在特定位置引用單元格 如果公式所在單元格的位置改變 絕對(duì)引用將保持不變 如果多行或多列地復(fù)制或填充公式 絕對(duì)引用將不作調(diào)整 15 A1引用樣式 混合引用混合引用具有絕對(duì)列和相對(duì)行或絕對(duì)行和相對(duì)列 絕對(duì)引用列采用 A1 B1等形式 絕對(duì)引用行采用A 1 B 1等形式 如果公式所在單元格的位置改變 則相對(duì)引用將改變 而絕對(duì)引用將不變 三維引用樣式包含單元格或區(qū)域引用 前面加上工作表名稱的范圍 三維引用包含單元格或區(qū)域引用 前面加上工作表名稱的范圍 例如 SUM Sheet2 Sheet13 B5 將計(jì)算B5單元格內(nèi)包含的所有值的和 單元格取值范圍是從工作表2到工作表13 16 R1C1引用樣式 在R1C1樣式中 Excel指出了行號(hào)在R后而列號(hào)在C后的單元格的位置 通過設(shè)置 R1C1引用樣式 復(fù)選框來打開或關(guān)閉R1C1引用樣式 該復(fù)選框位于 Excel設(shè)置 對(duì)話框 例子R 2 C對(duì)同一列中上面兩行的單元格的相對(duì)引用 R 2 C 2 對(duì)在下面兩行 右面兩列的單元格的相對(duì)引用 R2C2對(duì)在工作表的第二行 第二列的單元格的絕對(duì)引用 R 1 對(duì)活動(dòng)單元格整個(gè)上面一行單元格區(qū)域的相對(duì)引用 R對(duì)當(dāng)前行的絕對(duì)引用 17 數(shù)組公式 18 為什么要使用數(shù)組公式 對(duì)一系列的值同時(shí)進(jìn)行計(jì)算 而非對(duì)單個(gè)值計(jì)算 通常是用兩個(gè)單行或單列區(qū)域進(jìn)行對(duì)應(yīng)單元格進(jìn)行計(jì)算 19 選中多個(gè)單元格 輸入數(shù)據(jù)后按Ctrl Enter 可以同時(shí)在多個(gè)單元格輸入相同數(shù)據(jù) 輸入一個(gè)數(shù)組公式 選中 E2 E11 C2 C11 D2 D11按Ctrl shift EnterExcel將用大括號(hào)將數(shù)組公式括起來 20 如果您手動(dòng)鍵入大括號(hào) 公式將被視為文本字符串 并且不起作用 數(shù)組常量 格式 元素 元素 元素 另一行 水平數(shù)組 1 2 3 4 5 垂直數(shù)組 1 2 3 4 5 二維數(shù)組 1 2 3 4 5 6 7 8 9 10 11 12 示例 SUM A1 E1 1 2 3 4 5 SUM 3 4 5 6 7 1 2 3 4 5 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 9 21 調(diào)試公式 22 引用追蹤 追蹤引用單元格 看看本單元格中的公式引用了誰 追蹤從屬單元格 看看本單元格被哪些公式引用 操作 公式審核追蹤單元格 23 公式求值 單步對(duì)公式進(jìn)行求值計(jì)算 以進(jìn)行監(jiān)控求值過程 每次按下 求值 按鈕 公式計(jì)算一步 按下步入按鈕 進(jìn)入嵌套的公式求值 24 函數(shù) 25 函數(shù)概述 函數(shù)是預(yù)定義的公式 通過使用一些稱為參數(shù)的特定數(shù)值來特定的順序或結(jié)構(gòu)執(zhí)行計(jì)算 函數(shù)可用于執(zhí)行簡單或復(fù)雜的計(jì)算 Excel函數(shù)與數(shù)學(xué)上的函數(shù)概念類似 輸入0個(gè)1個(gè)或多個(gè)參數(shù) 按一定的邏輯得到一個(gè)結(jié)果 26 善用Excel的幫助來學(xué)習(xí)函數(shù) 在Excel幫助中搜索需要的函數(shù)名 或是分類查找 函數(shù)參考 可找到函數(shù)的詳細(xì)說明 在鍵入函數(shù)時(shí) Excel也會(huì)提示函數(shù)的說明 關(guān)于函數(shù)的文檔格式說明 枚舉1 枚舉2 尖括號(hào) 要替換為所需要的內(nèi)容 有些文檔中不使用 方括號(hào) 表示可選項(xiàng) 大括號(hào) 表示成組的內(nèi)容 視同為一項(xiàng) 豎線 表示互斥的枚舉項(xiàng) 只用選用其一 27 函數(shù)的結(jié)構(gòu) 函數(shù)的結(jié)構(gòu) 2 函數(shù)名稱 如果要查看可用函數(shù)的列表 可單擊一個(gè)單元格并按Shift F3 3 參數(shù)參數(shù)可以是數(shù)字 文本 TRUE或FALSE等邏輯值 數(shù)組 錯(cuò)誤值 如 N A 或單元格引用 指定的參數(shù)都必須為有效參數(shù)值 參數(shù)也可以是常量 公式或其他函數(shù) 參數(shù)工具提示 在鍵入函數(shù)時(shí) 會(huì)出現(xiàn)一個(gè)帶有語法和參數(shù)的工具提示 例如 鍵入 ROUND 時(shí) 工具提示就會(huì)出現(xiàn) 工具提示只在使用內(nèi)置函數(shù)時(shí)出現(xiàn) 28 常量 不進(jìn)行計(jì)算的值 因此也不會(huì)發(fā)生變化 例如 數(shù)字210以及文本 每季度收入 都是常量 嵌套函數(shù) 在某些情況下 您可能需要將某函數(shù)作為另一函數(shù)的參數(shù)使用 例如 int sum a1 a10 嵌套限制有效的返回值當(dāng)嵌套函數(shù)作為參數(shù)使用時(shí) 它返回的數(shù)值類型必須與參數(shù)使用的數(shù)值類型相同 例如 如果參數(shù)返回一個(gè)TRUE或FALSE值 那么嵌套函數(shù)也必須返回一個(gè)TRUE或FALSE值 否則Excel將顯示 VALUE 錯(cuò)誤值 嵌套級(jí)別限制公式可包含多達(dá)七級(jí)的嵌套函數(shù) 當(dāng)函數(shù)B在函數(shù)A中用作參數(shù)時(shí) 函數(shù)B則為第二級(jí)函數(shù) 以此類推 29 數(shù)學(xué)函數(shù) abs number 絕對(duì)值函數(shù)ceiling number significance 沿絕對(duì)值增大的方向取最接近的significance的倍數(shù) combin number number chosen 給定數(shù)目的對(duì)象集合中提取若干對(duì)象的組合數(shù)even number 沿絕對(duì)值增大方向取最接近的偶數(shù) fact number 返回某數(shù)的階乘 一個(gè)數(shù)的階乘等于1 2 3 該數(shù) floor number significance 將number向下舍入 向零的方向 到最接近的significance的倍數(shù) gcd number1 number2 返回兩個(gè)或多個(gè)整數(shù)的最大公約數(shù)int number 將數(shù)字向下 減小方向 舍入到最接近的整數(shù) lcm number1 number2 返回整數(shù)的最小公倍數(shù) 30 數(shù)學(xué)函數(shù) 2 mod number divisor 返回兩數(shù)相除的余數(shù) 結(jié)果的正負(fù)號(hào)與除數(shù)相同 mround number multiple 返回參數(shù)按指定基數(shù)舍入后的數(shù)值 odd number 沿絕對(duì)值增大方向取最接近的奇數(shù) rand 返回大于等于0及小于1的均勻分布隨機(jī)實(shí)數(shù) 每次計(jì)算工作表時(shí)都將返回一個(gè)新的隨機(jī)實(shí)數(shù) round number num digits 將某個(gè)數(shù)字四舍五入為指定的位數(shù) rounddown number num digits 靠近零值 向下 絕對(duì)值減小的方向 舍入數(shù)字 roundup number num digits 遠(yuǎn)離零值 向上舍入數(shù)字 sign number 返回?cái)?shù)字的符號(hào) 當(dāng)數(shù)字為正數(shù)時(shí)返回1 為零時(shí)返回0 為負(fù)數(shù)時(shí)返回 1 sum number1 number2 返回某一單元格區(qū)域中所有數(shù)字之和 31 數(shù)學(xué)函數(shù) 3 sumif range criteria sum range 可以對(duì)區(qū)域中符合指定條件的值求和 trunc number num digits 將數(shù)字的小數(shù)部分截去 返回整數(shù) average number1 number2 返回參數(shù)的平均值 算術(shù)平均值 count value1 value2 計(jì)算包含數(shù)字的單元格以及參數(shù)列表中數(shù)字的個(gè)數(shù)Convert在不同的單位之間轉(zhuǎn)換數(shù)據(jù) 32 Excel還有大量三角函數(shù) 矩陣運(yùn)算等函數(shù) 統(tǒng)計(jì)函數(shù) Count計(jì)數(shù)Sum求和AVERAGE平均數(shù)AVERAGEA包括非數(shù)字單元格 視為0 的平均數(shù)Max最大值Min求最小值Median一組已知數(shù)字的中值Mode某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值Frequency 數(shù)組 分隔點(diǎn) 數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率 然后返回一個(gè)垂直數(shù)組Rank返回一列數(shù)字的數(shù)字排位PercentRank 數(shù)據(jù)集中值的百分比排位 33 文本函數(shù) asc將字符串中的全角 雙字節(jié) 英文字母或片假名更改為半角 單字節(jié) 字符char返回由代碼數(shù)字指定的字符clean刪除文本中所有非打印字符code返回文本字符串中第一個(gè)字符的unicode數(shù)字代碼concatenate將幾個(gè)文本項(xiàng)合并為一個(gè)文本項(xiàng)exact檢查兩個(gè)文本值是否相同find findb在一個(gè)文本值中查找另一個(gè)文本值 區(qū)分大小寫 想一想 如果希望不分大小寫查詢?cè)撛趺崔k fixed將數(shù)字格式設(shè)置為具有固定小數(shù)位數(shù)的文本jis將字符串中的半角 單字節(jié) 英文字母或片假名更改為全角 雙字節(jié) 字符 34 文本函數(shù) 2 left leftb返回文本值中最左邊的字符len lenb返回文本字符串中的字符個(gè)數(shù)lower將文本轉(zhuǎn)換為小寫mid midb從文本字符串中的指定位置起返回特定個(gè)數(shù)的字符proper將文本值的每個(gè)字的首字母大寫replace replaceb按位置替換文本中的字符rept按給定次數(shù)重復(fù)文本right rightb返回文本值中最右邊的字符search searchb在一個(gè)文本值中查找另一個(gè)文本值與find區(qū)別 不區(qū)分大小寫 可以使用通配符 substitute在文本字符串中用新文本替換舊文本t檢查參數(shù)是否是文本 如是 返回該文本 否則返回空 35 文本函數(shù) 3 text設(shè)置數(shù)字格式并將其轉(zhuǎn)換為文本 Text 1 000000 返回 000001 trim刪除文本中的空格upper將文本轉(zhuǎn)換為大寫形式value將文本參數(shù)轉(zhuǎn)換為數(shù)字n檢查參數(shù)是否可以視為數(shù)字 如是返回轉(zhuǎn)換為數(shù)字的值 否則返回0 type返回表示值的數(shù)據(jù)類型的數(shù)字 36 關(guān)于Unicode和DBCS Unicode 一個(gè)字符就視為一個(gè)字符 與物理上采用什么保存格式無關(guān) DBCS 對(duì)于中文 日文等 一個(gè)字符可能視為二個(gè)字節(jié) 名稱里加 B 后綴的函數(shù)就是DBCS格式的函數(shù) find 人 中華人民共和國 返回3 findB 人 中華人民共和國 返回5 37 日期時(shí)間 date返回特定日期的序列號(hào)datevalue將文本格式的日期轉(zhuǎn)換為序列號(hào)day將序列號(hào)轉(zhuǎn)換為月份內(nèi)日期EDATE返回用于表示開始日期之前或之后月數(shù)的日期的序列號(hào)EOMONTH返回指定月數(shù)之前或之后的月份的最后一天的序列號(hào)hour將序列號(hào)轉(zhuǎn)換為小時(shí)minute將序列號(hào)轉(zhuǎn)換為分鐘month將序列號(hào)轉(zhuǎn)換為月networkdays返回兩個(gè)日期間的全部工作日數(shù)now返回當(dāng)前日期和時(shí)間的序列號(hào)second將序列號(hào)轉(zhuǎn)換為秒time返回特定時(shí)間的序列號(hào) 38 日期時(shí)間 2 timevalue將文本格式的時(shí)間轉(zhuǎn)換為序列號(hào)today返回今天日期的序列號(hào)weekday將序列號(hào)轉(zhuǎn)換為星期日期weeknum將序列號(hào)轉(zhuǎn)換為代表該星期為一年中第幾周的數(shù)字workday返回指定的若干個(gè)工作日之前或之后的日期的序列號(hào)year將序列號(hào)轉(zhuǎn)換為年yearfrac返回兩個(gè)日期之間年數(shù) 小數(shù) 39 關(guān)于日期與時(shí)間的一些話題 日期與時(shí)間的運(yùn)算日期與時(shí)間在excel內(nèi)部是以一個(gè)小數(shù)保存的 整數(shù)部分為從 紀(jì)元 如1900 1 1 以來的天數(shù) 小數(shù)部分為當(dāng)天按24小時(shí)的比例值 如0 6表示14 24日期加1即是第二天的同一時(shí)刻 減1即是前一天同一時(shí)刻 日期常量的輸入 YEARFRAC 2011 5 1 2012 5 2 40 想一想 YEARFRAC 2011 5 1 2012 5 2 是什么結(jié)果 關(guān)于日期與時(shí)間的一些話題 2 Excel有兩個(gè)日期系統(tǒng) 1900日期系統(tǒng) 紀(jì)元從1900 1 1開始的日期系統(tǒng) 默認(rèn)日期系統(tǒng) 1904日期系統(tǒng) 紀(jì)元從1904 1 2開始的日期系統(tǒng) 是為了兼容早期的Macintosh 在不同的的日期系統(tǒng)中 相同的序列值對(duì)應(yīng)的日期不同 相差1462天 約4年零1天 基于兩個(gè)不同日期系統(tǒng)的工作簿之間復(fù)制和粘貼日期或創(chuàng)建外部引用時(shí)會(huì)遇到問題 41 修改使用的日期系統(tǒng) Excel選項(xiàng) 然后單擊 高級(jí) 類別 中或清除 使用1904日期系統(tǒng) 復(fù)選框 邏輯函數(shù) 邏輯值是指對(duì)于 是否 的判斷與運(yùn)算 FALSE邏輯真TRUE邏輯假AND 邏輯值1 如果其所有參數(shù)均為TRUE 則返回TRUEIF 邏輯值 返回值1 返回值2 指定要執(zhí)行的邏輯檢測 如為真 返回第一個(gè)值 否則返回第二個(gè)值 IFERROR 公式 默認(rèn)值 如果公式的計(jì)算結(jié)果錯(cuò)誤 則返回指定的值 否則返回公式的結(jié)果NOT 邏輯值 對(duì)其參數(shù)的邏輯求反OR 邏輯值1 如果任一參數(shù)為TRUE 則返回TRUE 42 查找和引用函數(shù) address以文本形式生成一個(gè)引用值串 address 2 3 1 false excelsheet column返回引用的列號(hào)columns返回引用中包含的列數(shù)hlookup查找數(shù)組的首行 并返回指定單元格的值hyperlink創(chuàng)建快捷方式或跳轉(zhuǎn) 以打開存儲(chǔ)在網(wǎng)絡(luò)服務(wù)器 intranet或internet上的文檔index使用索引從引用或數(shù)組中選擇值lookup在向量或數(shù)組中查找值match在引用或數(shù)組中查找值 得到索引序號(hào) offset從給定引用中返回引用偏移量row返回引用的行號(hào) 43 查找和引用函數(shù) 2 ROWS返回引用中的行數(shù)TRANSPOSE返回?cái)?shù)組的轉(zhuǎn)置VLOOKUP在數(shù)組第一列中查找 然后在行之間移動(dòng)以返回單元格的值CELL返回有關(guān)單元格格式 位置或內(nèi)容的信息Indirect引用指定的單元格內(nèi)容 44 為什么需要這些函數(shù) 因?yàn)槟憧赡軙?huì)需要根據(jù)不同的變量來決定引用哪個(gè)單元格 信息函數(shù) ERROR TYPE返回對(duì)應(yīng)于錯(cuò)誤類型的數(shù)字INFO返回有關(guān)當(dāng)前操作環(huán)境的信息ISBLANK如果值為空 則返回TRUEISERR如果值為除 N A以外的任何錯(cuò)誤值 則返回TRUEISERROR如果值為任何錯(cuò)誤值 則返回TRUEISEVEN如果數(shù)字為偶數(shù) 則返回TRUEISLOGICAL如果值為邏輯值 則返回TRUE 45 信息函數(shù) 2 ISNA如果值為錯(cuò)誤值 N A 則返回TRUEISNONTEXT如果值不是文本 則返回TRUEISNUMBER如果值為數(shù)字 則返回TRUEISODD如果數(shù)字為奇數(shù) 則返回TRUEISREF如果值為引用值 則返回TRUEISTEXT如果值