Excel函數(shù)應用實例.ppt

Microsoft Excel 2003 函數(shù)應用實例,培訓講師：,Excel能做什么？,最簡單的一種回答是：Excel是用來做表格的。 更詳細一些的答案版本可能是：Excel是一個電子表格軟件，有強大的數(shù)據(jù)處理與分析功能，能夠把數(shù)據(jù)做成各種商業(yè)圖表，還能編程。 當你對Excel的了解越多，就會發(fā)覺Excel總能在適當?shù)臅r候給你帶來驚喜，做到一些原本你認為不可能的事情。此時你的答案或許應該是： Excel不能做什么？,提綱,函數(shù)應用基礎單元格引用 查找函數(shù)應用數(shù)據(jù)合并 數(shù)據(jù)有效性應用下拉式輸入 統(tǒng)計函數(shù)應用條件匯總 條件格式應用格式提醒 視頻學習 數(shù)據(jù)透視表 郵件合并 宏基礎 常用函數(shù)推薦,關于函數(shù),函數(shù)是一些預定義的公式，通過使用一些稱為參數(shù)的特定數(shù)值來按特定的順序或結(jié)構(gòu)執(zhí)行計算。函數(shù)可用于執(zhí)行簡單或復雜的計算。例如，ROUND 函數(shù)可將單元格 A10 中的數(shù)字四舍五入。 函數(shù)的結(jié)構(gòu)以等號 (=) 開始，后面緊跟函數(shù)名稱和左括號，然后以逗號分隔輸入該函數(shù)的參數(shù)，最后是右括號。,單元格引用,案例：如何實現(xiàn)快速制作九九乘法表？,關于單元格和區(qū)域引用,引用的作用在于標識工作表上的單元格或單元格區(qū)域，它們告訴 Excel 到哪里查找在公式中使用的值。 通過引用，可以在公式中使用工作表不同部分的數(shù)據(jù)，或者在多個公式中使用同一個單元格的數(shù)值。還可以引用同一個工作簿中不同工作表上的單元格和其他工作簿中的數(shù)據(jù)。,單元格引用,單元格引用，可以是單一單元格引用，也可是多個單元格引用，引用在數(shù)據(jù)處理中有三種方式。 絕對引用：例如 $F$6、 $F$6: $K$8，總是在指定位置引用單元格。如果公式所在單元格的位置改變，絕對引用的單元格始終保持不變。 相對引用：例如 A1、A1:B6，是基于包含公式和單元格引用的單元格的相對位置。如果公式所在單元格的位置改變，引用也隨之改變。 混合引用：絕對列和相對行，或是絕對行和相對列，例如$A1、 F$6: K$8，如果公式所在單元的位置改變，引用的行或列隨之改變。,文本連接符,如果希望將多個文本連接生成新的文本，可以使用“&”來實現(xiàn)。 如： A1單元格值為 ：“景鴻物流” B2單元格值為 ：“北京公司” 在C3單元格輸入：=A1&”&B2，則C3單元格顯示： “景鴻物流北京公司”,if函數(shù),if（條件算式, 條件成立返回值,條件不成立返回值），用于執(zhí)行真假值判斷后，根據(jù)邏輯測試的真假值返回不同的結(jié)果，因此If函數(shù)也稱之為條件函數(shù)。 例：=IF(B1160,“合格“,“不合格“) if函數(shù)在實際應用中，可以嵌套使用，但最多不能超過七重。,單元練習,利用單元格引用，快速制作九九乘法表。,Just do it,數(shù)據(jù)合并,案例：如何為出貨清單添加海關稅號？,Match函數(shù),Match (查找值，查找范圍，查找方式) ，查找元素的相應位置。 查找值：可以為數(shù)值（數(shù)字、文本或邏輯值）或?qū)?shù)字、文本或邏輯值的單元格引用。 查找方式：-1、0 或 1； 1，找小于或等于查找值的最大數(shù)值；0，查找等于查找值的第一個數(shù)值； -1，查找大于或等于查找值的最小數(shù)值。如果省略則默認為 1。,函數(shù)返回查找值在查找范圍中的位置，而不是數(shù)值本身。例如，match(“b”,“a”,“b”,“c”,0) 返回 2，即“b”在數(shù)組 “a”,“b”,“c” 中的相應位置。 查找文本值時，不區(qū)分大小寫字母。 如果查找不成功，則返回錯誤值 #N/A。 如果 查找方式為 0 且查找值為文本，則查找值可以包含通配符 (*) 和 (?)。,Index函數(shù),Index(數(shù)據(jù)區(qū)域, 行號, 列號) 數(shù)據(jù)區(qū)域：單元格區(qū)域或數(shù)組常量。 如果數(shù)據(jù)區(qū)域只包含一行或一列，則相對應的參數(shù) 行號 或 列號 為可選參數(shù)。 例：=index(“a”,“b”,“c”,2)，返回b,單元練習,通過零件編號的關聯(lián)，為出貨清單上的貨品添加海關稅號。,Just do it,規(guī)范錄入,案例:如何實現(xiàn)用二級下拉列表輸入部門名稱？,名稱,在工作表中可以創(chuàng)建描述名稱來代表單元格、單元格區(qū)域、公式或常量，名稱更易于理解。 例如：用“產(chǎn)品”來代表區(qū)域“Sales!C20:C30”。 名稱命名規(guī)則 名稱的第一個字符必須是字母或下劃線，名稱中的字符可以是字母、數(shù)字、句號和下劃線， 名稱最多可以包含 255 個字符。,數(shù)據(jù)有效性,Excel可以通過指定有效的單元格輸入項，限制輸入單元格數(shù)據(jù)的規(guī)則和范圍。 指定所需的數(shù)據(jù)有效性類型： 有序列的數(shù)值 數(shù)字有范圍限制 日期或時間有范圍限制 文本為指定長度 計算基于其他單元格內(nèi)容的有效性數(shù)據(jù) 使用公式計算有效性數(shù)據(jù) 在“數(shù)據(jù)”菜單上，單擊“有效性”命令，再單擊“設置”選項卡。,Indirect函數(shù),Indirect(文本字符)，返回由文本字符串指定的單元格引用并立即對引用進行計算，并顯示其內(nèi)容。 當需要更改公式中單元格的引用，而不更改公式本身，請使用函數(shù) 。 如果 文本字符 是對另一個工作簿的引用（外部引用），則那個工作簿必須被打開。如果源工作簿沒有打開，函數(shù) INDIRECT 返回錯誤值 #REF!。,Offset函數(shù),Offset(參照引用，偏移行數(shù)，偏移列數(shù)，高度，寬度) ，以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。 偏移行數(shù)（列數(shù)）：相對于參照引用的左上角單元格，上（下）偏移的行數(shù)（列數(shù)）。如果使用 5 作為偏移行數(shù)（列數(shù)），則說明目標引用區(qū)域的左上角單元格比參考引用低 5 行。行數(shù)（列數(shù)）可為正數(shù)或負數(shù)。 高度（寬度）：即所要返回的引用區(qū)域的行數(shù)（列數(shù)）。高度及寬度必須為正數(shù)。 Offset可用于任何需要將引用作為參數(shù)的函數(shù)。例如：SUM(OFFSET(C2,1,2,3,1) 將計算比單元格 C2 靠下 1 行并靠右 2 列的 3 行 1 列的區(qū)域的總值。,單元練習,以下拉列表的方式實現(xiàn)部門名稱的規(guī)范輸入，二級部門必須與一級部門相對應。,Just do it,條件匯總,案例：如何實現(xiàn)應收賬款核對？,Sumif函數(shù),Sumif（條件范圍，條件值，求和范圍），根據(jù)指定條件對區(qū)域中單元格求和。 條件范圍：用于條件判斷的單元格區(qū)域。 條件值：確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。例如，條件可以表示為 A1、 “32” 或 “apples”。 求和范圍：需要求和的實際單元格區(qū)域，可以用左上角單元格替代。,Countif函數(shù),Countif（條件范圍，條件值），計算區(qū)域中滿足給定條件的單元格的個數(shù)。 條件范圍：需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。 統(tǒng)計條件：確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式、單元格引用或文本。例如，條件可以表示為 A1、“32“ 、“apples“ 。,單元練習,表二中為用友直接導出的數(shù)據(jù)，表一中為按照業(yè)務編號手工每筆每筆錄入的數(shù)據(jù)，讓表一中數(shù)據(jù)按照憑證編號和表二中數(shù)據(jù)一一對上，如果對不上要去查找原因。 表15表30為各作業(yè)點按目的港統(tǒng)計操作噸位日報，要求匯總成半月報，即時查詢各作業(yè)點同一目的港噸位明細及匯總。,Just do it,格式提醒,案例：如何實現(xiàn)標識不同類別的數(shù)據(jù)？,條件格式,條件格式：當指定條件為真時，Excel 自動應用于單元格的格式，快速引起對差異的注意。例如，單元格底紋或字體顏色。 同一單元格，條件格式最多可以設置三重條件； 條件格式，可以通過公式計算來設置； 用定位功能，可以查找有條件格式的單元格。,單元練習,區(qū)分銷售人員銷售業(yè)績。 月銷售額低于1000元的，以紅底白字以示強調(diào) 月銷售額高于10000元的，以綠底白字以示鼓勵。 針對一些限期完成的工作，如何實現(xiàn)提醒功能？,Just do it,視頻學習,數(shù)據(jù)透視表 郵件合并 宏基礎,常用函數(shù)推薦數(shù)學函數(shù),Round（數(shù)值，保留小數(shù)位），將數(shù)字四舍五入到指定小數(shù)位數(shù)。 Int（數(shù)值），刪除數(shù)值小數(shù)，保留整數(shù)部分。 Mod（被除數(shù)，除數(shù)），返回兩數(shù)相除的余數(shù)。 Subtotal（匯總代號，匯總范圍），返回列表或數(shù)據(jù)庫中的統(tǒng)計匯總結(jié)果。 匯總代號： 為 1 到 11（包含隱藏值）或 101 到 111（忽略隱藏值）之間的數(shù)字，指定使用何種函數(shù)在列表中進行分類匯總計算。,常用函數(shù)推薦文本函數(shù),Left（文本，提取字符位數(shù)）：從字符串的最左端位置提取指定數(shù)量的字符。 Mid（文本，提取起始位，提取字符位數(shù)）：從字符串任意指定位置提取指定數(shù)量的字符。 Right（文本，提取字符位數(shù)）：從字符串的最右端位置提取指定數(shù)量的字符。 Len（文本）：返回字符串中字符的個數(shù)，即計算字符串的長度。 Find（查找字符串，源字符串）：查找字符或字符串在指定字符串中首次出現(xiàn)的位置，如不存在，則返錯誤提示。,常用函數(shù)推薦日期函數(shù),Now（）：用于獲取系統(tǒng)當前時間。 Year（日期）、Month（日期）、DAY（日期）:用于獲取指定日期的年份、月份和日期數(shù)值。 Date（年份，月份，日期）：根據(jù)指定年份、月份、日期數(shù)返回日期值。 DateDif（起始日期，終止日期，返回類別），計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。 返回類別為“y”計算整年數(shù)；“m”