OFFICE XP培訓(xùn)(Excel 進(jìn)階函數(shù)篇)

1、OFFICE XP培訓(xùn)(Excel 進(jìn)階函數(shù)篇)Excel進(jìn)階培訓(xùn)-函數(shù)篇陳君(Doris)微軟認(rèn)證講師陳君函數(shù)? 什么是函數(shù)？? 函數(shù)長(zhǎng)得什么樣，穿著什么衣？? 函數(shù)能幫我們做什么？? 怎樣使用函數(shù)讓它發(fā)揮作用？陳君函數(shù)初露尖尖角陳君函數(shù)的定義? 函數(shù)是一些預(yù)定義的公式，通過(guò)使用一些稱(chēng)為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計(jì)算。輸入?yún)?shù)輸出結(jié)果陳君函數(shù)的結(jié)構(gòu)? “=”符號(hào): 表示是公式函數(shù)? 函數(shù)名稱(chēng): 表示進(jìn)行什么樣的操作? 括號(hào)( ): 包含函數(shù)的參數(shù),及函數(shù)的輸入值? 參數(shù): 要在函數(shù)中使用的值和單元格陳君基本的函數(shù)函數(shù)名稱(chēng) 用途Sum 求和Product 求乘積Average 求算術(shù)

2、平均值Max/Min 最大/最小Count 統(tǒng)計(jì)數(shù)值的個(gè)數(shù)Today/Now 當(dāng)前日期/當(dāng)前日期和時(shí)間Int 向下舍入取整數(shù)ABS 取絕對(duì)值Round 四舍五入陳君基本函數(shù)應(yīng)用舉例請(qǐng)嘗試在單元格中直接輸入=today()或=now()陳君時(shí)間函數(shù)舉例日期 時(shí)間 年 月 日 時(shí) 分 秒 星期Date Time Year Month Day Hour Minute Secondweekday陳君怎樣正確地使用函數(shù)? 運(yùn)用函數(shù)名稱(chēng)框選擇函數(shù)? 運(yùn)用粘貼函數(shù)fx? 在單元格中直接輸入函數(shù)陳君輸入函數(shù)時(shí)容易出現(xiàn)的錯(cuò)誤? 沒(méi)有輸入“=”。Excel認(rèn)為輸入的是文本，不是公式.單元格中完整顯示輸入內(nèi)容。不運(yùn)

3、算。? 函數(shù)名稱(chēng)漏錯(cuò)字母。Excel認(rèn)為給出的是無(wú)效函數(shù)名稱(chēng)，無(wú)法計(jì)算。顯示為#NAME? 括號(hào)輸少了。Excel認(rèn)為你輸入的公式有錯(cuò)誤。顯示為#NAME?或直接給予提醒輸入公式錯(cuò)誤的對(duì)話框? 選錯(cuò)了需要運(yùn)算的參數(shù)區(qū)域。? Excel按照輸入的數(shù)據(jù)計(jì)算，但結(jié)果不是你想要的。? 顯示為#REF。表示引用了無(wú)效的單元格。陳君進(jìn)一步應(yīng)用函數(shù)函數(shù)名稱(chēng) 用途 函數(shù)類(lèi)別Sumif 有條件地求和 數(shù)學(xué)與三角函數(shù)統(tǒng)計(jì)函數(shù)查找與引用函數(shù)邏輯函數(shù)財(cái)務(wù)函數(shù)Dcount 多條件地統(tǒng)計(jì)數(shù)值 數(shù)據(jù)庫(kù)函數(shù)MID 提取字符 文本函數(shù)Find 查找并提取字符 文本函數(shù)Rank 排名次Vlookup 查找記錄IF 判定條件PV/

4、FV 投資現(xiàn)值/投資未來(lái)值陳君Roundup與Rounddown? 王二小出生于1973年10月7日，于1995年7月1日入職公司。年齡計(jì)周歲，而工齡向上取。如工齡為3.24年，則計(jì)為4年。陳君統(tǒng)計(jì)函數(shù)RANK?RANK(number,ref,order)返回一個(gè)數(shù)字在數(shù)字列表中的排位。? Number 為需要找到排位的數(shù)字。? Ref 為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用。Ref 中的非數(shù)值型參數(shù)將被忽略。? Order 為一數(shù)字，指明排位的方式。? 如果 order 為 0（零）或省略，降序? 如果 order 不為零，升序案例應(yīng)用：?jiǎn)T工情況表中，將員工的工資由高到低排名次陳君RANK應(yīng)用案例

5、陳君統(tǒng)計(jì)函數(shù)Large/Small? 思考：我們需要找出排名第N位的某個(gè)數(shù)據(jù)，不做篩選/排序或Rank的情況下，該怎么實(shí)現(xiàn)？? 如在員工表，我們想找到年齡最大或者最小的是多少歲？方法：運(yùn)用large或是small函數(shù)陳君Large/Small? Large? 返回?cái)?shù)據(jù)集中第 k 個(gè)最大值。使用此函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來(lái)選擇數(shù)值。例如，可以使用函數(shù) LARGE 得到第一名、第二名或第三名的得分。? 語(yǔ)法? LARGE(array,k)? Array 為需要從中選擇第 k 個(gè)最大值的數(shù)組或數(shù)據(jù)區(qū)域。? K 為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置（從大到小排）。? 說(shuō)明? 如果數(shù)組為空，函數(shù) LARG

6、E 返回錯(cuò)誤值 #NUM!。? 如果 k 0 或 k 大于數(shù)據(jù)點(diǎn)的個(gè)數(shù)，函數(shù) LARGE 返回錯(cuò)誤值#NUM!。陳君計(jì)數(shù)函數(shù)COUNT思考題：? 怎樣得出指定區(qū)域的文本單元格的個(gè)數(shù)？? 怎樣得出指定區(qū)域的單元格的個(gè)數(shù)？陳君條件計(jì)數(shù)函數(shù)COUNTIF? 如上例，出倉(cāng)表中“兒童口服液”出了多少筆貨？陳君COUNIF函數(shù)的語(yǔ)法結(jié)構(gòu)? 目標(biāo)：計(jì)算區(qū)域中滿足給定條件的單元格的個(gè)數(shù)。? 語(yǔ)法：COUNTIF(range,criteria)? Range 為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。? Criteria 為確定哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表

7、示為 32、;32;、;32; 或 ;apples;。陳君多條件計(jì)數(shù)DCOUNT應(yīng)用陳君DCOUNT語(yǔ)法結(jié)構(gòu)? 目標(biāo)：返回?cái)?shù)據(jù)庫(kù)或數(shù)據(jù)清單的列中滿足指定條件且包含數(shù)字的單元格個(gè)數(shù)? 語(yǔ)法：DCOUNT(database,field,criteria)? Database 構(gòu)成數(shù)據(jù)清單或數(shù)據(jù)庫(kù)的單元格區(qū)域。數(shù)據(jù)庫(kù)是包含一組相關(guān)數(shù)據(jù)的數(shù)據(jù)清單，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。數(shù)據(jù)清單的第一行包含著每一列的標(biāo)志項(xiàng)。? Field 指定函數(shù)所使用的數(shù)據(jù)列。數(shù)據(jù)清單中的數(shù)據(jù)列必須在第一行具有標(biāo)志項(xiàng)。Field 可以是文本，即兩端帶引號(hào)的標(biāo)志項(xiàng)，如“使用年數(shù)” ；此外，F(xiàn)ield 也可以

8、是代表數(shù)據(jù)清單中數(shù)據(jù)列位置的數(shù)字：1 表示第一列，2 表示第二列，等等。參數(shù) field 為可選項(xiàng)，如果省略，函數(shù) DCOUNT 返回?cái)?shù)據(jù)庫(kù)中滿足條件criteria 的所有記錄數(shù)。? Criteria 包含指定條件的單元格區(qū)域。可以為參數(shù) criteria 指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格。陳君Dcount函數(shù)的field參數(shù)省略前后的對(duì)照? 省略掉dcount函數(shù)的field參數(shù)陳君Dcount函數(shù)的field參數(shù)省略前后的對(duì)照? 以“出倉(cāng)單價(jià)”列字段作為dcount函數(shù)的field參數(shù)陳君數(shù)學(xué)與三角函數(shù)（SUMIF）SUMIF(range,crite

9、ria,sum_range)根據(jù)指定條件對(duì)若干單元格求和。? Range 為用于條件判斷的單元格區(qū)域。? Criteria 為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。? Sum_range 是需要求和的實(shí)際單元格。應(yīng)用案例：以“出倉(cāng)單”為數(shù)據(jù)清單，求出某種產(chǎn)品的銷(xiāo)售金額的總和。陳君條件求和SUMIF應(yīng)用案例如需求出“靈芝皇膠囊”的價(jià)值總金額,怎樣實(shí)現(xiàn)?陳君多條件求和：DSUM? DSUM? 返回列表或數(shù)據(jù)庫(kù)的列中滿足指定條件的數(shù)字之和。? 語(yǔ)法? DSUM(database,field,criteria)? Database 構(gòu)成列表或數(shù)據(jù)庫(kù)的單元格區(qū)域。數(shù)據(jù)庫(kù)是包含

10、一組相關(guān)數(shù)據(jù)的列表，其中包含相關(guān)信息的行為記錄，而包含數(shù)據(jù)的列為字段。列表的第一行包含著每一列的標(biāo)志項(xiàng)。? Field 指定函數(shù)所使用的數(shù)據(jù)列。列表中的數(shù)據(jù)列必須在第一行具有標(biāo)志項(xiàng)。Field 可以是文本，即兩端帶引號(hào)的標(biāo)志項(xiàng)，如“使用年數(shù)”或“產(chǎn)量”；此外，F(xiàn)ield 也可以是代表列表中數(shù)據(jù)列位置的數(shù)字：1 表示第一列，2 表示第二列，等等。? Criteria 為一組包含給定條件的單元格區(qū)域。可以為參數(shù) criteria 指定任意區(qū)域，只要它至少包含一個(gè)列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元格。陳君多條件求和：DSUM請(qǐng)問(wèn)dsum函數(shù)的參數(shù)field能否省略陳君邏輯函數(shù)IF? IF(logi

11、cal_test,value_if_true,value_if_false)? 執(zhí)行真假值判斷，根據(jù)邏輯計(jì)算的真假值，返回不同結(jié)果。? 可以使用函數(shù) IF 對(duì)數(shù)值和公式進(jìn)行條件檢測(cè)。? Logical_test 表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。? Value_if_true logical_test 為 TRUE 時(shí)返回的值。如果 logical_test 為T(mén)RUE 而 value_if_true 為空，則本參數(shù)返回 0? Value_if_false logical_test 為 FALSE 時(shí)返回的值陳君IF函數(shù)示例：一次判斷案例數(shù)據(jù)表：IF函數(shù)陳君IF函數(shù)應(yīng)

12、用: 一次判斷后再計(jì)算月銷(xiāo)售額 提成標(biāo)準(zhǔn)10000 0.05銷(xiāo)售代表 月銷(xiāo)售額 銷(xiāo)售提成Kiki 15060Rose 14800John 12350Jane 21010Coco 9500Lily 16030Mimi 18530Jack 8200Grace 13700Ana 25080陳君IF函數(shù)應(yīng)用: 一次判斷后再計(jì)算陳君IF函數(shù)應(yīng)用: 兩次判斷后再計(jì)算月銷(xiāo)售額 提成標(biāo)準(zhǔn)15000 0.08銷(xiāo)售代表 月銷(xiāo)售額 銷(xiāo)售提成Kiki 15060Rose 14800John 12350Jane 21010Coco 9500Lily 16030Mimi 18530Jack 8200Grace 13700

13、Ana 25080陳君IF函數(shù)邏輯推理過(guò)程結(jié)果單元格第一次判斷15000月銷(xiāo)售額0.08月銷(xiāo)售額0.05TrueTrue月銷(xiāo)售額 提成標(biāo)準(zhǔn)15000 0.08falsefalse陳君IF函數(shù)應(yīng)用: 兩次判斷后再計(jì)算陳君IF函數(shù)多重嵌套的輸入方法? 在函數(shù)參數(shù)框中填寫(xiě)前面兩個(gè)參數(shù)后，將光標(biāo)移到Value_if_false欄里，鼠標(biāo)指向函數(shù)名稱(chēng)框，選擇IF函數(shù)陳君IF多重嵌套應(yīng)用案例：計(jì)算應(yīng)納稅金陳君IF應(yīng)用案例：兩個(gè)條件中滿足一個(gè)即可目標(biāo)：選出職務(wù)工資大于2500的高薪人員和小于1000的低薪人員陳君IF應(yīng)用案例：同時(shí)滿足兩個(gè)條件目標(biāo)：判斷出職務(wù)工資在1500至2000之間的員工陳君IF應(yīng)用案例

14、：同時(shí)滿足兩個(gè)條件陳君IF練習(xí)：自動(dòng)判決小學(xué)數(shù)學(xué)試卷陳君時(shí)間函數(shù)與IF結(jié)合舉例:計(jì)電話費(fèi)陳君時(shí)間函數(shù)與IF結(jié)合舉例:計(jì)電話費(fèi)陳君查找與引用函數(shù)LOOKUP? 數(shù)據(jù)源表員工卡號(hào) 姓名 出生 年月 年齡 家庭通信地址 家庭電話區(qū)號(hào) 電話號(hào)碼 學(xué)歷LD001 林海 1980-811-01 23 上海建國(guó)門(mén)外大街22號(hào)103室 200033 021 87654788 大學(xué)本科LD005 王衛(wèi)平 1952-03-06 52 黃岡朝陽(yáng)區(qū)牛王廟 700027 0713 6733826 中專(zhuān)LD006 任水濱 1970-11-01 33 天津朝陽(yáng)區(qū)惠新西里軍退辦公樓300029 022 28667755 碩士

15、研究生LD010 王川 1960-11-01 43 北京車(chē)道溝1號(hào)濱河大廈12層 100086 010 87547288 大專(zhuān)LD015 王小明 1953-10-01 50 武漢市延安路1536號(hào) 700001 027 87612341 大專(zhuān)LD020 劉晉生 1952-11-01 51 上海市友誼賓館60122室 200080 021 65544988 碩士研究生LD003 劉學(xué)燕 1957-04-01 47 廣州安定門(mén)外大街68號(hào)碼層 503311 020 87876655 大學(xué)本科LD004 黃璐京 1961-01-01 43 武漢北京朝陽(yáng)區(qū)亮馬河橋路52號(hào) 700016 027 618

16、87722 中專(zhuān)LD007 張曉寰 1961-12-01 42 廣州朝陽(yáng)區(qū)惠新東街15號(hào) 100028 020 36774421 大專(zhuān)LD011 連威 1981-05-01 23 北京車(chē)道溝1號(hào)濱河大廈12層 100086 010 66343321 大專(zhuān)LD012 劉琳 1946-10-01 57 廣州東山區(qū)學(xué)院南路86號(hào) 500081 020 66578233 中專(zhuān)LD017 莊鳳儀 1961-11-01 42 廣州深南中路統(tǒng)建大樓3棟18層 518031 020 85236788 大學(xué)本科LD018 沈奇峰 1980-01-01 24 廣州市深南中路電子大廈901室 518321 020

17、86537866 中專(zhuān)LD002 陳鵬 1974-11-01 29 珠海建國(guó)門(mén)外大街1號(hào)518室 500004 0756 7654656 大專(zhuān)LD013 沈克 1977-04-01 27 北京東直門(mén)內(nèi)海運(yùn)倉(cāng)2號(hào) 100702 010 23343221 大學(xué)本科LD014 艾芳 1973-12-01 30 東莞王府井大街277號(hào) 500746 0759 6656438 大學(xué)本科LD016 胡海濤 1959-10-01 45 上海市紅旗路1號(hào) 262723 021 28865436 大專(zhuān)LD019 金星 1980-06-01 24 北京市西藏中路237號(hào)100021 010 27789655 大專(zhuān)

18、LD008 楊寶春 1955-05-01 49 東莞菜市口鐵門(mén)胡同17號(hào) 100332 0759 7656577 中專(zhuān)LD009 許東東 1956-07-01 48 上海宣武門(mén)西大街后河沿6號(hào) 200053 021 65668863 大學(xué)本科陳君LOOKUP查詢結(jié)果陳君LOOKUP語(yǔ)法LOOKUP向量形式? 目標(biāo)：從單行或單列中查找一個(gè)值? LOOKUP(lookup_value,lookup_vector,result_vector)? Lookup_value 為函數(shù) LOOKUP 在第一個(gè)向量中所要查找的數(shù)值。Lookup_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱(chēng)或引用。?

19、Lookup_vector 為只包含一行或一列的區(qū)域。Lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值。 Lookup_vector 的數(shù)值必須按升序排序：.、-2、-1、A-Z、FALSE、TRUE；否則，函數(shù) LOOKUP 不能返回正確的結(jié)果。文本不區(qū)分大小寫(xiě)。? Result_vector 只包含一行或一列的區(qū)域，其大小必須與lookup_vector 相同。? 說(shuō)明? 如果函數(shù) LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小于或等于 lookup_value 的最大數(shù)值。? 如果 lookup_value 小于 lookup_vect

20、or 中的最小值，函數(shù) LOOKUP 返回錯(cuò)誤值 #N/A。陳君查找與引用函數(shù)（Vlookup）? VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。在 VLOOKUP 中的 V 代表垂直。? Lookup_value 為需要在數(shù)組第一列中查找的數(shù)值。? Table_array 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。? Col_index_num 為 table_array 中待返回的匹配值的列序號(hào)。? Range_lookup 為一邏輯值，? 為T(mén)R

21、UE 或省略，精確匹配? 為 FALSE，近似匹配。如果找不到，則返回錯(cuò)誤值 #N/A。? 如果 Range_lookup 為 TRUE，則 Table_array 的第一列中的數(shù)值必須按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否則，函數(shù)VLOOKUP 不能返回正確的數(shù)值。如果 Range_lookup 為 FALSE，Table_array 不必進(jìn)行排序。應(yīng)用案例：在“員工表”中，按“員工卡號(hào)”查“基礎(chǔ)工資”。陳君Vlookup函數(shù)應(yīng)用案例A陳君Vlookup函數(shù)應(yīng)用案例B陳君Vlookup函數(shù)跨工作簿查詢陳君文本函數(shù)大小寫(xiě)字母轉(zhuǎn)換目標(biāo)：用于大小寫(xiě)字母使用這些函數(shù)時(shí)，

22、數(shù)和其它非字母字符仍保持不變函數(shù) 語(yǔ)法 實(shí)現(xiàn)目標(biāo)Upper Upper(text) 將全部字母轉(zhuǎn)換成大寫(xiě)Lower lower(text) 將全部字母轉(zhuǎn)換成小寫(xiě)proper proper(text) 將一個(gè)字的第一個(gè)字母轉(zhuǎn)換成大寫(xiě)，而將其余字母轉(zhuǎn)換成小寫(xiě)John D. Smithjohn d. smithJOHN D. SMITHjohn d. smithproper結(jié)果lower結(jié)果upper結(jié)果原文舉例陳君文本函數(shù)計(jì)算和截取字符目標(biāo)：Len、Right、Left、Mid函數(shù)經(jīng)常一起使用，將一行文本分割為單獨(dú)的文字串。函數(shù) 語(yǔ)法 實(shí)現(xiàn)目標(biāo)Len Len (text) 文字串的長(zhǎng)度Right

23、Right(text, num_chars) 一串文字的最右一些字符Left Left(text, num_chars) 一串文字的最左一些字符mid Mid(text,start_num,num_chars)從一串文字串中提取一串文字,提出的起始點(diǎn)由start_num決定，提取的位數(shù)由num_chars決定陳君Len/Left/Right/Mid舉例陳君文本函數(shù)查找文字串的位置? 目標(biāo):找出一個(gè)字符串在另一個(gè)字符串中出現(xiàn)的起始位置.? 語(yǔ)法:? Find(find_text,within_text,start_num)? Search(find_text,within_text,start_

24、num)? 區(qū)別:Seach不區(qū)分大小寫(xiě),可以使用通配符(?)和(*);Find則區(qū)分大小寫(xiě),不允許使用通配符? 結(jié)果是指在within_text中含有find_text起始位置的數(shù),start_num指明開(kāi)始查找的位置.如果省略, start_num的默認(rèn)值為1陳君FIND應(yīng)用舉例陳君MID/FIND/lEN函數(shù)綜合應(yīng)用電話010-512492550757-5050386010-32822668010-465182050759-4632764020-49302015010-42244886010-30156236022-3183766區(qū)號(hào)010075701001007590200100100

25、22=MID(E4,1,FIND(;-;,E4)-1)=MID(E4,FIND(;-;,E4)+1,LEN(E4)-FIND(;-;,E4)電話號(hào)碼512492555050386328226684651820546327644930201542244886301562363183766陳君結(jié)果出錯(cuò)為哪般？錯(cuò)誤表現(xiàn) 錯(cuò)誤原因#NUM! 公式或函數(shù)中某個(gè)數(shù)字有問(wèn)題#DIV/0! 當(dāng)公式被零除時(shí)#NAME? 公式中函數(shù)中使用了不能識(shí)別的文本#VALUE? 使用錯(cuò)誤的參數(shù)或運(yùn)算對(duì)象#REF! 單元格引用無(wú)效#N/A 函數(shù)中沒(méi)有可用數(shù)值# 單元格列寬不夠或日期時(shí)間產(chǎn)生負(fù)值陳君掌握規(guī)律，舉一反三? 函數(shù)衍生的一些規(guī)律前綴加D 基本函數(shù) 后綴加A 后綴加ifDsum Sum SumifDaverage Average Average