Excel函數(shù)培訓(xùn)教材,機(jī) 能 別:電腦 編訂日期:2011年12月20日 版本:第001版,培訓(xùn)大綱,一、相對(duì)引用和絕對(duì)引用 二、常用函數(shù)解析 （1）日期與時(shí)間函數(shù) （2）統(tǒng)計(jì)函數(shù) （3）查找和引用函數(shù) （4）文本函數(shù) （5）邏輯函數(shù) 三、結(jié)合公式自定義數(shù)據(jù)有效性和條件格式,如圖：C1=A1+B1，如果將C1單元格復(fù)制粘貼到D1，得到的結(jié)果是7而不是原來(lái)是單元格的值5， 這是因?yàn)閺?fù)制過(guò)程中我們一并也把公式復(fù)制到了D1，當(dāng)計(jì)算結(jié)果欄位由原來(lái)的C1偏移到D1時(shí)，則參與計(jì)算的其他欄位也會(huì)同時(shí)發(fā)生偏移，即D1=B1+C1。,倘若將A1、B1絕對(duì)引用，那么參與公式計(jì)算是相應(yīng)欄位就被鎖定，若此時(shí)將C1復(fù)制粘貼到其他欄位，得到的結(jié)果就是5,如果只是想粘貼數(shù)值，則可在粘貼的時(shí)候用“選擇性粘貼”只針對(duì)數(shù)值進(jìn)行粘貼，如下圖在將C1欄位粘貼到D1欄位時(shí)，右擊選擇性粘貼數(shù)值，則的得到的結(jié)果將是原來(lái)C1欄位的數(shù)值5（無(wú)論是絕對(duì)引用還是相對(duì)引用）。,一、相對(duì)引用和絕對(duì)引用,混合引用是有絕對(duì)的行或相對(duì)的列。絕對(duì)引用列是采用$A1、$B1等形式。絕對(duì)引用行是采用A$1、B$1等形式。如果公式所在單元格的位置改變，則相對(duì)引用的改變而絕對(duì)引用的不變。如果多行或多列的復(fù)制公式，相對(duì)引用自動(dòng)調(diào)整而絕對(duì)引用將不做調(diào)整。例如，下圖公式中絕對(duì)引用了A列和23行，公式復(fù)制過(guò)程中只對(duì)相對(duì)引用的部分進(jìn)行調(diào)整。,注：相對(duì)引用與絕對(duì)引用可以通過(guò)快捷鍵“F4”進(jìn)行相互切換。,一、相對(duì)引用和絕對(duì)引用,TODAY():返回日期格式的當(dāng)前日期,（1）日期與時(shí)間函數(shù),NOW():返回日期時(shí)間格式的當(dāng)前日期和時(shí)間,WEEKDAY():返回某日期的星期數(shù)。在默認(rèn)情況下，它的值為1(星期天)到7(星期六)之間的一個(gè)整數(shù)。 語(yǔ)法：WEEKDAY(serial_number，return_type) 參數(shù)：Serial_number 是要返回日期數(shù)的日期，通常用帶引號(hào)的文本串(如“2001/02/26”)。Return_type為確定返回值類(lèi)型的數(shù)字，數(shù)字1 或省略，則1 至7 代表星期天到星期六；數(shù)字2 ，則1 至7 代表星期一到星期天；數(shù)字3，則0至6代表星期一到星期天。,NETWORKDAYS (）函數(shù)專(zhuān)門(mén)用于計(jì)算兩個(gè)日期值之間完整的工作日數(shù)值。這個(gè)工作日數(shù)值將不包括雙休日和專(zhuān)門(mén)指定的其他各種假期。 語(yǔ)法：NETWORKDAYS (Start_date,End_date,Holidays) Start_date 表示開(kāi)始日期；End_date為終止日期，Holidays 表示作為特定假日的一個(gè)或多個(gè)日期。這些參數(shù)值既可以手工輸入，也可以對(duì)單元格的值進(jìn)行引用。 例如，下圖中要計(jì)算2012-5-1到2012-5-31的工作日，A2為起始日期，B2為終止日期C2:C4為特定的假日。計(jì)算公式如下圖：,（1）日期與時(shí)間函數(shù),注：手工輸入的日期需加英文狀態(tài)的引號(hào)。可Ctrl+.進(jìn)行切換。,WORKDAY ：返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和專(zhuān)門(mén)指定的假日)的某一日期的值，并扣除周末或假日。 語(yǔ)法：WORKDAY(start_date，days，holidays) 參數(shù)：Start_date 為開(kāi)始日期；Days 為Start_date 之前或之后不含周末及節(jié)假日的天數(shù)；Days是正值將產(chǎn)生未來(lái)日期、負(fù)值產(chǎn)生過(guò)去日期；Holidays為可選的數(shù)據(jù)清單，表示需要從工作日中排除的日期值(如法定假日或非法定假日)。此清單可以是包含日期的單元格區(qū)域，也可以是由代表日期的序列號(hào)所構(gòu)成的數(shù)組常量。 例如，下圖中要計(jì)算2012-12-25前排除特定節(jié)假日往前、后推10個(gè)工作日的日期，計(jì)算公式如下：,（1）日期與時(shí)間函數(shù),DATEDIF（）返回兩個(gè)日期之間的年月日間隔數(shù)。 語(yǔ)法：DATEDIF(start_date,end_date,unit) ，Start_date 代表時(shí)間段內(nèi)的第一個(gè)日期或起始日期。 End_date 它代表時(shí)間段內(nèi)的最后一個(gè)日期或結(jié)束日期。 Unit 為所需信息的返回類(lèi)型。 Unit 返回 Y 時(shí)間段中的整年數(shù)。 M 時(shí)間段中的整月數(shù)。 D 時(shí)間段中的天數(shù)。 YD start_date 與 end_date 日期中天數(shù)的差。忽略日期中的年。 MD start_date 與 end_date 日期中天數(shù)的差。忽略日期中的月和年。 YM start_date 與 end_date 日期中月數(shù)的差。忽略日期中的日和年。 注：結(jié)束日期必須大于起始日期。,（1）日期與時(shí)間函數(shù),COUNTIF （）用于計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。 語(yǔ)法：COUNTIF(range，criteria) 參數(shù)：Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria 為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。,（2）統(tǒng)計(jì)函數(shù),（2）統(tǒng)計(jì)函數(shù),COUNTIFS（）用于計(jì)算區(qū)域中滿足給定多條件的單元格的個(gè)數(shù)。 語(yǔ)法： COUNTIFS(range1，criteria1，range2，criteria2， ) Rang1為需要計(jì)算其中滿足條件1的單元格數(shù)目的單元格區(qū)域。Criteria1 為確定哪些單元格將被計(jì)算在內(nèi)的第一個(gè)條件, Rang2為需要計(jì)算其中滿足條件2的單元格數(shù)目的單元格區(qū)域。Criteria2為確定哪些單元格將被計(jì)算在內(nèi)的第二個(gè)條件，以此類(lèi)推，每一對(duì)range和criteria 之間用逗號(hào)隔開(kāi)。,（2）統(tǒng)計(jì)函數(shù),SUMIF():根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和。 語(yǔ)法：SUMIF(range，criteria，sum_range) 參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件，Sum_range 為需要求和的單元格、區(qū)域或引用。 例如，下圖中要計(jì)算銷(xiāo)售部的工資總和，A列為用于條件判斷的單元格區(qū)域，$E$4為判斷條件，C列需要求和的區(qū)域。計(jì)算公式如下：,（2）統(tǒng)計(jì)函數(shù),SUMIFS():根據(jù)指定的多條件對(duì)若干單元格、區(qū)域或引用求和。 語(yǔ)法： SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .) 參數(shù)： sum_range 是需要求和的實(shí)際單元格區(qū)域 criteria_range1為用于條件1判斷的單元格區(qū)域。 criteria1為條件1。 criteria_range2為用于條件2判斷的單元格區(qū)域。 criteria2為條件2。 例如，下圖中要計(jì)算求8月1日以前的筆記本銷(xiāo)量總額以及求8月1日到8月7日期間服務(wù)器的銷(xiāo)量總額，屬于多條件引用求和。計(jì)算公式如下：,（3）查找和引用函數(shù),VLOOKUP（） ：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 語(yǔ)法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup) 參數(shù)：Lookup_value為需要在數(shù)據(jù)表第一列中查找的數(shù)值，Table_array 為需要在其中查找數(shù)據(jù)區(qū)域。Col_index_num 為table_array 中待返回的匹配值的列序號(hào)。Range_lookup為一邏輯值，指明函數(shù)VLOOKUP 返回時(shí)是精確匹配還是近似匹配。如果Range_lookup為 1或省略，則返回近似匹配值（模糊匹配），如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值；如果range_value 為0， 函數(shù)VLOOKUP 將返回精確匹配值。 注：1.查找值一定要在第一列。 2.模糊匹配時(shí)第一列一定要升序排列。,（3）查找和引用函數(shù),精確查找案例，下圖中要查找AB-S05的銷(xiāo)售額，G3為要查找的數(shù)值，A:D為要查找的區(qū)域，4表示要查找值在區(qū)域的第4列，0表示精確查找，公式如下圖所示：,模糊查找案例，下圖中要查找收入$21,566對(duì)應(yīng)的稅率，B3為要查找的數(shù)值，D3:F9為要查找的區(qū)域，3表示要查找值在區(qū)域的第3列，1表示模糊查找，公式如下圖所示：,OFFSET（），以指定的引用為參照系，通過(guò)給定偏移量得到新的引用。返回的引用可以是一個(gè)單元格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。 如下圖使用OFFSET函數(shù)實(shí)現(xiàn)動(dòng)態(tài)圖表，OFFSET最少包含了3個(gè)參數(shù)，其中第一個(gè)參數(shù)表示從該欄位進(jìn)行偏移（起點(diǎn)），第二個(gè)參數(shù)表示行偏移數(shù)，第三個(gè)參數(shù)表示列偏移數(shù)，所以當(dāng)?shù)诙?、第三參?shù)值變化時(shí)，對(duì)結(jié)果區(qū)域引用并插入圖表，則該圖表就是會(huì)隨參數(shù)值的變化而變化。,（3）查找和引用函數(shù),（4）文本函數(shù),CONCATENATE（）：將若干文字串合并到一個(gè)文字串中，其功能與&運(yùn)算符相同。 語(yǔ)法：CONCATENATE(text1，text2，.) 參數(shù)：Text1，text2，. 為將要合并成單個(gè)文本的文本項(xiàng)，這些文本項(xiàng)可以是文字串、數(shù)字或?qū)蝹€(gè)單元格的引用。,LEN ()函數(shù)返回文本字符串中的字符數(shù)。 語(yǔ)法：LEN(text) Text 是要查找其長(zhǎng)度的文本。空格將作為字符進(jìn)行計(jì)數(shù)。,（4）文本函數(shù),right（）：根據(jù)所指定的字符數(shù)返回文本串中最后一個(gè)或多個(gè)字符。 語(yǔ)法：RIGHT(text，num_chars)參數(shù)：Text 是包含要提取字符的文本串；Num_chars 指定希望RIGHT 提取的字符數(shù)，它必須大于或等于0。,LEFT（）：根據(jù)所指定的字符數(shù)返回文本串中第一個(gè)或多個(gè)字符。 語(yǔ)法：RIGHT(text，num_chars)參數(shù)：Text 是包含要提取字符的文本串；Num_chars 指定希望left提取的字符數(shù)，它必須大于或等于0。,MOD ():函數(shù)返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。 語(yǔ)法：MOD(number,divisor) Number 為被除數(shù)；Divisor為除數(shù)。,（4）文本函數(shù),MID()： 返回文本串中從指定位置開(kāi)始的特定數(shù)目的字符。 語(yǔ)法：MID(text，start_num， num_chars) 參數(shù)：Text 是包含要提取字符的文本串。Start_num 是文本中要提取的第一個(gè)字符的位置，Num_chars指定希望MID 從文本中返回字符的個(gè)數(shù)。,數(shù)據(jù)分列：可以對(duì)單元格的內(nèi)容進(jìn)行拆分，采用數(shù)據(jù)分列固定寬度或分隔符進(jìn)行拆分，拆分過(guò)程中注意相應(yīng)的格式。,（5）邏輯函數(shù),IF():執(zhí)行邏輯判斷，它可以根據(jù)邏輯表達(dá)式的真假，返回不同的結(jié)果，從而執(zhí)行數(shù)值或公式的條件檢測(cè)。 語(yǔ)法：IF(logical_test，value_if_true，value_if_false)。 參數(shù)：Logical_test 計(jì)算結(jié)果為T(mén)RUE 或FALSE 的任何數(shù)值或表達(dá)式；Value_if_true 是Logical_test 為T(mén)RUE 時(shí)函數(shù)的返回值。而且Value_if_true 可以是一個(gè)表達(dá)式；Value_if_false 是Logical_test 為FALSE 時(shí)函數(shù)的返回值。Value_if_false 也可以是一個(gè)表達(dá)式。,（5）邏輯函數(shù),IF函數(shù)可以多層嵌套使用，但最多只能嵌套7層 例如，下圖中要將人員的總分按照大小進(jìn)行分級(jí)，公司如下：,三、結(jié)合公式自定義數(shù)據(jù)有效性和條件格式,限定欄位中的文本長(zhǎng)度： 例如，身份證號(hào)通常是18位，當(dāng)輸入的位數(shù)不等于18位是需要能夠進(jìn)行提示，我們就可以通過(guò)“條件格式”來(lái)實(shí)現(xiàn)提示，當(dāng)號(hào)碼不等于18位是顯示紅色。設(shè)置過(guò)程如下圖：條件格式-新建規(guī)則-設(shè)置公示-編輯相應(yīng)的格式,還可以通過(guò)查看“條件格式-管理規(guī)則”查看公式、格式、應(yīng)用范圍是否正確,三、結(jié)合公式自定義數(shù)據(jù)有效性和條件格式,通過(guò)公式自定義設(shè)置單元格格式，例如，下圖中要將不同大小值的單元格設(shè)置不同的格式。右擊設(shè)置單元格格式-自定義-用公式對(duì)通用格式進(jìn)行編輯。例如下圖中要將A列數(shù)值根據(jù)大小設(shè)置相應(yīng)的顏色，,謝 謝！,