常用EXCEL函數(shù)使用詳解.ppt

1、常用EXCEL函數(shù)講解,2018年9月18日,新手、初級用戶、中級用戶、高級用戶和專家五個層次 新手：學(xué)習(xí)者需要大致了解到Excel的基本操作方法和常用功能，諸如輸入數(shù)據(jù)，查找替換，設(shè)置單元格格式，排序、匯總、篩選和保存工作簿。 初級用戶：可以開始在工作中運(yùn)用Excel，比如建立一個簡單的表格，畫一張簡單的圖表，會一些基本簡單的函數(shù)，如SUM、IF 等。 中級用戶三個標(biāo)志： 一是理解并熟練使用各個Excel菜單命令 二是熟練使用數(shù)據(jù)透視表 三是至少掌握20個常用函數(shù)以及函數(shù)的嵌套運(yùn)用，掌握的基本函數(shù)有SUM函數(shù)、IF函數(shù)、VLOOKUP函數(shù)、INDEX函數(shù)、MATCH函數(shù)、OFFSET函數(shù)、T

2、EXT函數(shù)等等,高級用戶兩個標(biāo)志： 一是熟練運(yùn)用數(shù)組公式，也就是那種用花括號包圍起來，必須用組合鍵才能完成錄入的公式。 Excel專家： 從Excel的功能細(xì)分來看，精通全部的人想必寥寥無幾。Excel是應(yīng)用性太強(qiáng)的軟件，意味著一個沒有任何工作經(jīng)驗的普通學(xué)生是很難成為Excel專家的。所以Excel專家也必定是某個或多個行業(yè)的專家，他們都擁有豐富的行業(yè)知識和經(jīng)驗。高超的Excel技術(shù)配合行業(yè)經(jīng)驗來共同應(yīng)用，才有可能把Excel發(fā)揮到極致，所以，如果希望成為Excel專家，就不能只單單學(xué)習(xí)Excel了 。,Excel函數(shù)的種類,財務(wù)函數(shù) 日期函數(shù) 時間函數(shù) 數(shù)學(xué)與三角函數(shù) 統(tǒng)計函數(shù) 數(shù)據(jù)庫管理函

3、數(shù) 文本函數(shù) 信息類函數(shù),函數(shù)的基本語法,函數(shù)的基本語法為： = 函數(shù)名(參數(shù)1,參數(shù)2,參數(shù)n)。 注意問題： 函數(shù)名與其后的括號“（”之間不能有空格。 當(dāng)有多個參數(shù)時，參數(shù)之間要用逗號“，”分隔。 參數(shù)部分總長度不能超過1024個字符。 參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達(dá)式或函數(shù)。 函數(shù)中的逗號“，”、引號“”等都是半角字符，而不是全角字符。,常用函數(shù)介紹,求和函數(shù): SUM/SUMIF/SUMPRODUCT函數(shù) 數(shù)學(xué)函數(shù) AVERGAE、 MAX、MIN、 ABS、 SQRT函數(shù) 計數(shù)函數(shù): COUNT、COUNTA、COUNTIF函數(shù) 條件函數(shù)和邏輯函數(shù): IF

4、函數(shù)、AND函數(shù)、OR函數(shù) 日期和時間函數(shù): DAY、DATE、DAYS360、TODAY函數(shù)等 分析工具庫里的日期函數(shù) 查找和引用函數(shù): VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數(shù) 四舍五入函數(shù): ROUND、FLOOR和CEILING函數(shù) 取整函數(shù): INT函數(shù) 信息函數(shù): ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù) 文本函數(shù)：LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù) 排序函數(shù): RANK、LARGE、SMALL函數(shù) 數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù) 其

5、他函數(shù),求和函數(shù) SUM/SUMIF/SUMPRODUCT函數(shù),SUM函數(shù) :無條件求和 = SUM (參數(shù)1,參數(shù)2,參數(shù)N) SUMIF函數(shù) :條件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函數(shù) :在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和 = SUMPRODUCT(array1,array2,array3, .),求和函數(shù): 應(yīng)用舉例,SUM函數(shù) SUMIF函數(shù) SUMPRODUCT函數(shù),求和函數(shù): 應(yīng)用舉例 隔欄加總計算,某預(yù)算表如圖所示，每個月分為“預(yù)算數(shù)”和“實際數(shù)”，現(xiàn)要求計算年度“預(yù)算數(shù)”和“實際數(shù)”。這實際上就

6、是隔欄加總問題。如果一個一個單元格相加是很繁瑣的。 解決方法：巧妙應(yīng)用第3行的標(biāo)題。,求和函數(shù): 應(yīng)用舉例 往下（或往右）累加匯總,往下（或往右）累加匯總是常見的實務(wù)問題。 解決方法：使用SUM函數(shù)，但單元格引用的第一個地址應(yīng)為絕對引用，而第二個地址為相對引用。,求和函數(shù): 應(yīng)用舉例 動態(tài)匯總當(dāng)天數(shù)據(jù),工作表中存在每天的數(shù)據(jù)，要求動態(tài)匯總計算當(dāng)前的數(shù)據(jù)。 可使用SUMIF函數(shù)。但要注意條件的寫法。,數(shù)學(xué)函數(shù) AVERGAE、 MAX、MIN、 ABS、 SQRT函數(shù),常用的數(shù)學(xué)函數(shù)有： AVERGAE函數(shù)：求平均值 MAX：求最大值 MIN：求最小值 ABS：求絕對值 SQRT：計算平方根,平

7、均值函數(shù): AVERGAE函數(shù),AVERGAE函數(shù) :求N個數(shù)的平均值 = AVERGAE(參數(shù)1,參數(shù)2,參數(shù)N),最大值與最小值函數(shù): MAX、MIN函數(shù),MAX函數(shù) : 求N個數(shù)的最大值 = MAX(參數(shù)1,參數(shù)2,參數(shù)N) MIN函數(shù): 求N個數(shù)的最小值 = MIN(參數(shù)1,參數(shù)2,參數(shù)N),絕對值函數(shù): ABS函數(shù),ABS函數(shù): 求某數(shù)的絕對值 = ABS(參數(shù)) 例如，ABS(-100)=100,開平方函數(shù): SQRT函數(shù),SQRT函數(shù): 求某數(shù)的平方根 = SQRT(參數(shù)) 例如，SQRT(2)=1.4142135623731,計數(shù)函數(shù): COUNT、COUNTA、COUNTIF

8、函數(shù),COUNT函數(shù): 計算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目 = COUNT(參數(shù)1,參數(shù)2,參數(shù)N) COUNTA 函數(shù): 返回參數(shù)列表中非空值的單元格個數(shù) = COUNTA(參數(shù)1，參數(shù)2，參數(shù)N ) COUNTIF函數(shù): 計算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目 = COUNTIF(range,criteria),計數(shù)函數(shù):應(yīng)用舉例,COUNT函數(shù): COUNTA 函數(shù): COUNTIF函數(shù):,計數(shù)函數(shù)應(yīng)用計算銷售業(yè)績,計數(shù)函數(shù):計算高于平均業(yè)績的人數(shù),條件函數(shù)和邏輯函數(shù): IF函數(shù)、AND函數(shù)、OR函數(shù),IF函數(shù): IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。 = IF(條件

9、表達(dá)式，條件值為真時返回的值，條件值為假時返回的值) AND函數(shù)：AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時（即所有參數(shù)的邏輯值都為真時），AND函數(shù)返回TRUE，否則，只要有一個條件不滿足即返回FALSE。 = AND(條件1，條件2，條件N) OR函數(shù)：只要有一個條件滿足時，OR函數(shù)返回TRUE，只有當(dāng)所有條件都不滿足時才返回FALSE。 = OR(條件1,條件2,條件N) 說明：這3個函數(shù)常常聯(lián)合使用。,IF函數(shù)應(yīng)用舉例 之一計算獎金,IF函數(shù)應(yīng)用舉例 之二計算增長率,計算增長率 當(dāng)被比較年份沒有數(shù)據(jù)時，會出現(xiàn)被除數(shù)為0的錯誤。 可以利用IF函數(shù)進(jìn)行處理。 如圖，去年沒有數(shù)據(jù)，而今年有數(shù)據(jù)

10、，就顯示“新增項目”。 去年有數(shù)據(jù)，而今年沒有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。,IF函數(shù)應(yīng)用舉例 之三,自動歸類問題 為了有效管理零用金，希望在輸入現(xiàn)金開支數(shù)據(jù)后，該金額數(shù)據(jù)自動依部門歸類到適當(dāng)?shù)牧校瑥亩有涯康貥?biāo)示出來。 解決辦法：使用IF函數(shù) 如圖，先在單元格F2輸入公式“=IF($D2=F$1,$C2, )”。 選定單元格區(qū)域F2:J2，按【F2】，使處于編輯狀態(tài)。 按【Ctrl+Enter】組合鍵 注意單元格的引用方式。,AND函數(shù)應(yīng)用舉例,OR函數(shù)應(yīng)用舉例,聯(lián)合使用IF、AND和OR函數(shù)計算獎金,日期函數(shù): DAY、DATE、DAYS360、TODAY函數(shù),DAY函數(shù): 返回以序列號表示

11、的某日期的天數(shù)，用整數(shù) 131 表示。 = DAY(日期序列號) 例如 DAY(2006-12-22)=22 DATE函數(shù)：返回代表特定日期的序列號。 = DATE(年,月,日) 例如 DATE(2006,12,23)= 2006-12-23 DAYS360函數(shù)：按照一年 360 天計（每個月以 30 天，一年共計 12 個月），返回兩個日期間相差的天數(shù)。 = DAYS360(開始日期,截止日期,邏輯值) 例如 DAYS360(2000-1-15,2005-12-16)=2131天 TODAY函數(shù)：返回系統(tǒng)當(dāng)前的日期。,日期函數(shù)應(yīng)用(舉例）,設(shè)置動態(tài)標(biāo)題： =今天是 &TEXT(TODAY()

12、,yyyy年m月d日) =今天是 &TEXT(TODAY(),yyyy年m月d日)& &TEXT(WEEKDAY(TODAY(),aaaa) 設(shè)置上月標(biāo)題： =IF(MONTH(TODAY()=1,12,MONTH(TODAY()-1)&月份收支情況“ 設(shè)置本月標(biāo)題 =MONTH(TODAY()&月份收支情況“ 將日期轉(zhuǎn)換為星期 中文星期：=TEXT(WEEKDAY(A1),aaaa) 英文星期：=TEXT(WEEKDAY(A1),“dddd) 計算2年5個月20天后的日期 =DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20) 計算實際歲數(shù)：=INT(YEARFRA

13、C(出生日期,今天,1) =DATEIF(出生日期,今天,”Y”),日期函數(shù)應(yīng)用舉例確定具體日期,日期函數(shù)應(yīng)用舉例確定兩個日期間的天數(shù),日期函數(shù)應(yīng)用舉例設(shè)置日期顯示格式,日期函數(shù)應(yīng)用舉例編制下周計劃表,編制下周計劃表 可以在本周的任何一天制作下周的計劃表 注意：任何一天的日期減去本身的星期數(shù)，就一定等于上一個星期天的日期。,日期函數(shù)綜合應(yīng)用設(shè)計考勤表,時間函數(shù),輸入時間 輸入22:00與輸入10:00 PM是一樣的。 如何計算跨午夜零時的時間間隔？ 輸入：=B2+(B2A2)-A2 或者：=B2+IF(B2A2,1,0)-A2 思路：如果下班時間小于上班時間，就表示已經(jīng)過了1天，因此要加1。否

14、則，如果下班時間大于上班時間，就表示還在當(dāng)前，因此不需要加1天。,分析工具庫里的日期函數(shù),EDATE函數(shù) EOMONTH函數(shù) WEEKNUM函數(shù) WORKDAY函數(shù) NETWORKDAYS函數(shù) 一個特殊的日期函數(shù)：DATEDIF 注意：在使用這些函數(shù)之前，必須加載分析工具庫，即：單擊【工具】【加載宏】命令，打開【加載宏】對話框，選擇“分析工具庫”，如上圖。,EDATE函數(shù),EDATE函數(shù)：返回指定日期往前或往后幾個月的日期。 例： 2007年4月12日之后3個月的日期： =EDATE(“2007-4-12”,3)，為2007-7-12 2007年4月12日之前3個月的日期： =EDATE(“2

15、007-4-12”,-3)，為2007-1-12 計算應(yīng)付賬款的到期日： 如果一筆應(yīng)付款的到期日為自交易日起3個月的那一天，比如交易日為2006年11月30日，滿3個月后為2007-2-28： =EDATE(“2006-11-30”,3)，為2007-2-28,EOMONTH函數(shù),EOMONTH函數(shù)：返回指定日期往前或往后幾個月的特定月份的月底日期。 例： 2007年4月12日之后3個月的月末日期： = EOMONTH(“2007-4-12”,3)，為2007-7-31 2007年4月12日之前5個月的月末日期： =EDATE(“2007-4-12”,-5)，為2006-11-30 計算應(yīng)付賬

16、款的到期日： 如果一筆應(yīng)付款的到期日為自交易日起滿3個月后的下一個月的5號，比如交易日為2006年11月20日，滿3個月后下個月5號就是2007-3-5： =EOMONTH(2006-11-20,3-(DAY(A1)=5)+5,WEEKNUM函數(shù),WEEKNUM函數(shù)：返回指定日期是該年的第幾周。 例： 2007年4月12日是2007年的第15周： =WEEKNUM(“2007-4-12”)，為第15周,WORKDAY函數(shù),WORKDAY函數(shù)：返回某指定日期之前或之后的給定工作日天數(shù)的日期（除去雙休日和國家法定假日） 。 例： 指定日期為2007年4月12日，往后30個工作日的日期為（要出去國家

17、法定的五一3天假日）2007年5月29日： =WORKDAY(2007-4-12,30,2007-5-1,2007-5-2,2007-5-3) 假若一項工程開始日期為2007年4月20日，預(yù)計需要60個工作日，那么預(yù)計完工的時間為哪天？,NETWORKDAYS函數(shù),NETWORKDAYS函數(shù)：返回兩個工作日之間的工作天數(shù)（除去雙休日和國家法定假日）。 例： 2007年4月12日至2007年6月20日之間的工作天數(shù)（除去雙休日和國家法定假日） 為47天。,一個特殊的日期函數(shù)：DATEDIF確定兩個日期間的年數(shù)、月數(shù)和天數(shù),DATEDIF函數(shù)：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)。這個函數(shù)是一個特殊

18、函數(shù)，在函數(shù)清單中找不到，在幫助信息中也找不到。 DATEDIF(開始日期, 結(jié)束日期, 單位) 單位 意義 “Y ” 時間段中的總年數(shù) “M ” 時間段中的總月數(shù) “D ” 時間段中的總天數(shù) “MD ” 兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年和月 “YM ” 兩日期中月數(shù)的差，忽略日期數(shù)據(jù)中的年和日 “YD ” 兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年 例如：某職員進(jìn)公司日期為1985年3月20日，離職時間為2007年8月9日，那么他在公司工作了多少年、多少月和多少天？ 工作年數(shù)：=DATEDIF(“1985-3-20”,“2007-8-9”,“Y”)=22年 工作月數(shù)：=DATEDIF(“198

19、5-3-20”,“2007-8-9”,“YM”)=4個月 工作天數(shù)：=DATEDIF(“1985-3-20”,“2007-8-9”,“MD”)=20天 問題：如果某人2月1日到職，2月28日離職，任職時間是28天還是1個月？ 注意：2月1日到職，開始日期應(yīng)為1月31日；2月28日離職，結(jié)束日期應(yīng)為3月1日。,查找和引用函數(shù): VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函數(shù),VLOOKUP函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。 HLOOKUP函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或

20、數(shù)組當(dāng)前列中指定行處的數(shù)值。 MATCH函數(shù)：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。 INDEX函數(shù)：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。 CHOOSE函數(shù):：根據(jù)指定的索引值返回數(shù)組中的數(shù)據(jù) OFFSET函數(shù)：動態(tài)引用單元格。 提示：我們還可以利用名稱來查找數(shù)據(jù)（詳見文件“通過名稱查找數(shù)據(jù).xls”）,VLOOKUP函數(shù)應(yīng)用舉例 之一,VLOOKUP函數(shù)應(yīng)用舉例 之二制作采購表,根據(jù)兩個表格制作采購表，如圖所示，計算步驟如下： 單元格區(qū)域B10:B13為各種商品的采購數(shù)量，由人工輸入。 在單元格C10中輸入公式“=VLOOKUP(A10,$A$3:$B$6,2,FALSE)

21、”，獲取某商品的單價。 在單元格D10中輸入公式=VLOOKUP(B10,$D$3:$H$6,MATCH(A10,$D$3:$H$3,0),TRUE)”，獲取某商品的折扣。 在單元格E10中輸入公式“=B10*C10*(1-D10)”，計算采購金額。 選取單元格區(qū)域C10:E10，將其向下填充復(fù)制到單元格區(qū)域C13:E13，得到其他商品的單價、折扣和采購金額。,VLOOKUP函數(shù)應(yīng)用舉例 之三根據(jù)產(chǎn)品代碼查詢該產(chǎn)品的詳細(xì)信息,根據(jù)產(chǎn)品代碼查詢該產(chǎn)品的詳細(xì)信息，如圖所示。 說明：由于產(chǎn)品代碼是唯一的標(biāo)識符，沒有重復(fù)，所以可以利用VLOOKUP函數(shù)或者HLOOKUP函數(shù)進(jìn)行查找。,HLOOKUP函

22、數(shù)應(yīng)用舉例 之一,HLOOKUP函數(shù)應(yīng)用舉例 之二,根據(jù)指定的行、列查找數(shù)據(jù),HLOOKUP函數(shù)應(yīng)用舉例 之三計算底薪傭金制的薪金,推銷員的薪金采用底薪傭金制，計算規(guī)則如下： 營業(yè)額 底薪 傭金比率 0-299,999 25,000 0% 300,000-499,999 25,000 1% 500,000-999,999 30,000 2% 1,000,000-1,499,999 35,000 3% 1,500,000以上 40,000 4% 整理上述數(shù)據(jù)到工作表，并輸入相應(yīng)計算公式，即得各個推銷人員的薪金。注意設(shè)計底薪傭金制計算規(guī)則表時要采用各級的底限值。,MATCH函數(shù)應(yīng)用舉例 之一基本應(yīng)

23、用,注意：MATCH返回的位置是相對于指定的單元格區(qū)域而言的，而不是對整個工作表區(qū)域而言的。 例如：MATCH(455,A2:E2,0)=3,MATCH函數(shù)應(yīng)用舉例 之二比較兩張工作表，將僅存在于其中某個工作表的數(shù)據(jù)篩選出來,如圖，在兩個工作表中有重復(fù)的數(shù)據(jù)，現(xiàn)在要在工作表“財務(wù)部上報”中將工作表“銷售部上報”中重復(fù)的數(shù)據(jù)隱藏起來。 篩選步驟： 以工作表“財務(wù)部上報”為準(zhǔn)，設(shè)計一個輔助列，在單元格B2輸入公式： =ISERROR(MATCH(A2,銷售部上報!$A$2:$A$7,0，并向下復(fù)制到數(shù)據(jù)區(qū)域的末尾。 單擊【數(shù)據(jù)】【篩選】【自動篩選】命令。 篩選出需要對數(shù)據(jù)。 最后采用選擇性粘貼的方

24、法將數(shù)據(jù)復(fù)制到別處。,INDEX函數(shù)應(yīng)用舉例,MATCH函數(shù)與INDEX函數(shù)聯(lián)合使用 查找某部門某項費(fèi)用的數(shù)額,根據(jù)指定的部門名稱和費(fèi)用項目，查找相應(yīng)的金額。如下圖。 說明：先用MATCH查找費(fèi)用項目在第幾行，用MATCH查找部門在第幾列，然后用INDEX函數(shù)取出行列交叉處的數(shù)據(jù)。,利用數(shù)組公式處理多維數(shù)據(jù)的查詢,我們也可以利用連字符&連接多個條件進(jìn)行多維數(shù)據(jù)的查詢。 先定義名稱，再利用MATCH函數(shù)和INDEX函數(shù)進(jìn)行查詢。,CHOOSE函數(shù)應(yīng)用舉例,CHOOSE函數(shù)語法：CHOOSE(索引號,值1,值2,) 例如，CHOOSE(1,AA,BB,CC,DD)=AA CHOOSE(3,AA,B

25、B,CC,DD)=“CC 舉例：依在本單位工作年限發(fā)放中秋節(jié)禮品 禮品發(fā)放規(guī)定：本單位工齡未滿3年的送咖啡壺；滿3年未滿6年者送飲水機(jī)；滿6年未滿9年者送電磁爐；9年以上者送電烤箱。 說明： 也可以使用IF函數(shù)。 注意：本單位工齡的計算方法。Excel默認(rèn)的日期系統(tǒng)是1900系統(tǒng)。 也可以利用YEARFRAC函數(shù)計算工齡（或年齡）： =INT(YEARFRAC(C4,$H$1),OFFSET函數(shù)應(yīng)用,OFFSET函數(shù)語法： OFFSET(參照單元格, 列位移量, 行位移量, 高度, 寬度) 例如，公式=OFFSET(C3,2,3,1,1)將返回單元格F5中的值，這里，當(dāng)前指定的引用為單元格C3

26、，以此為參照系，向下偏移2行，為第5行；向右偏移3列，為F列；高度和寬度均為1，表示僅為一個單元格。 計算步驟： 在單元格B13輸入 =OFFSET(INDIRECT(“A”&MATCH($B$10,$A$1:$A$7,0),0,COLUMN(A1)，然后將單元格B13向右復(fù)制。 在上述公式中，首先使用MATCH查找單元格B10中的數(shù)字所在A列的行，然后利用INDIRECT返回A列中該行所在單元格的數(shù)據(jù)，最后利用OFFSET函數(shù)取得該行各列的數(shù)據(jù)。 在單元格B14輸入=SUM(OFFSET(B1,1,$B$10)，計算到指定月份某部門的合計數(shù)，然后將單元格B14向右復(fù)制。 說明：OFFSET函

27、數(shù)在制作動態(tài)查詢和動態(tài)圖表時是非常有用的。,從多個表格區(qū)域中查詢數(shù)據(jù) 之一根據(jù)銷售人員任期確定提成率,有多個表格需要查詢，根據(jù)實際條件決定要查詢那個表格。 利用IF語句判斷使用那個表格。 實例如下，銷售人員任期不同，則提成率也不同。,從多個表格區(qū)域中查詢數(shù)據(jù) 之二,有多個表格區(qū)域，結(jié)構(gòu)相同。 根據(jù)指定的部門、月份、費(fèi)用項目查詢相應(yīng)的費(fèi)用金額。,從多個工作表中查詢數(shù)據(jù),某公司將客戶分為A、B、C三個等級，給于不同的客戶編號（分別以A、B、C開頭）。不同等級客戶的折扣率是不同的，而同一等級客戶里不同產(chǎn)品的折扣率也是不同的。在確定客戶等級和產(chǎn)品之后，還得依據(jù)訂購數(shù)量給于該客戶最終的折扣。怎樣編寫查詢

28、公式？ 太復(fù)雜了！,從多個工作表中查詢數(shù)據(jù),考慮到從不同的工作表進(jìn)行查詢，而工作表名稱即為“客戶”加客戶編號的第一個字母，因此可以利用INDIRECT函數(shù)獲取要查詢工作表的單元格區(qū)域，利用MATCH函數(shù)確定指定商品編號所在的列和采購金額所在的行，再利用INDEX函數(shù)取出相應(yīng)的折扣率。 單元格G2的公式如下： =INDEX(INDIRECT(客戶&LEFT($B2,1)&!$B$4:$E$7), MATCH(F2,INDIRECT(客戶&LEFT($B2,1)&!$A$4:$A$7), MATCH($C2,INDIRECT(客戶&LEFT($B2,1)&!$B$3:$E$3),區(qū)分大小寫的查詢,

29、VLOOKUP函數(shù)和HLOOKUP函數(shù)不區(qū)分大小寫。 如果要區(qū)分大小寫進(jìn)行查詢，可以聯(lián)合使用INDEX函數(shù)、MATCH函數(shù)和EXACT函數(shù)。,四舍五入函數(shù): ROUND、FLOOR和CEILING函數(shù),ROUND函數(shù): 返回某個數(shù)字按指定位數(shù)舍入后的數(shù)字。 FLOOR函數(shù)：根據(jù)指定基數(shù)，將數(shù)字沿絕對值減小的方向向下舍入到最接近的倍數(shù)。 CEILING函數(shù) ：將參數(shù) Number 向上舍入（沿絕對值增大的方向）到最接近的倍數(shù)。 例如：ROUND(300.5485,2)=300.55 FLOOR(2.5, 1)= 2， FLOOR(-2.5, -2)= -2 CEILING(2.5, 1)=3，

30、CEILING(-2.5, -2)= -4,四舍五入函數(shù)應(yīng)用舉例: 產(chǎn)品價格標(biāo)定,應(yīng)用舉例：某貿(mào)易公司經(jīng)常要以匯率換算產(chǎn)品的成本價格，加上必要的管理費(fèi)及預(yù)期利潤后就是產(chǎn)品的價格。因此，產(chǎn)品價格的百位數(shù)以下都會有零頭。公司的政策是：凡是小于30的尾數(shù)去掉，而大于或等于30 的尾數(shù)則進(jìn)位成100。例如，4004被標(biāo)成4000， 4227被標(biāo)成4200，而2145被標(biāo)成2200，1765被標(biāo)成1800。那么，該怎樣設(shè)定計算公式計算價格標(biāo)定？ 計算思路：以100為基數(shù)利用FLOOR函數(shù)取出元時定價的尾部部分，然后利用IF函數(shù)判斷該尾數(shù)是否大于或等于30，然后再決定是利用CEILING函數(shù)還是FLOOR

31、函數(shù)處理原數(shù)據(jù)。,取整函數(shù): INT函數(shù),INT函數(shù): 將數(shù)字向下舍入到最接近的整數(shù) 。 = INT(數(shù)字) 例如： INT(300.5485)=300， INT(-300.5485)=-301 例：某企業(yè)根據(jù)經(jīng)營部門的完成率進(jìn)行評分，標(biāo)準(zhǔn)如下：完成率59.99%以下為1分，超過60%為2分， 超過70%為3分，超過80%為4分，超過90%為5分。 分析：我們可以使用IF函數(shù)進(jìn)行計算。但由于各個標(biāo)準(zhǔn)的間隔正好是10%，因此也可以利用INT函數(shù)進(jìn)行運(yùn)算，并進(jìn)行評分。 說明：公式中(B2-0.49999)*10用于計算超過49.999%的有多少個10%，而(B20.5)表示只有在完成率超過50%

32、時才利用公式(B2-0.49999)*10進(jìn)行計算。,信息函數(shù): ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù),ISBLANK函數(shù)：判斷單元格是否為空白單元格 ISTEXT函數(shù)：判斷單元格數(shù)據(jù)是否為文本 ISNUMBER函數(shù)：判斷單元格數(shù)據(jù)是否為數(shù)字 ISERROR函數(shù)：判斷單元格是否出現(xiàn)錯誤,文本函數(shù)LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù),LEN函數(shù)：獲取字符串的長度 LEFT函數(shù)：獲取字符串左邊指定個數(shù)的文本 RIGHT函數(shù)：獲取字符串右邊指定個數(shù)的文本 TRIM函數(shù)：取消字符串兩側(cè)的空格 FIND函數(shù)：查找某字符在字符串中第一次出現(xiàn)的位置 TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本 其他的文本函數(shù),文本函數(shù)應(yīng)用舉例,例1： LEN(ABCD)=4 LEFT(ABCD,2)= AB RIGHT(ABCD,2)= CD TRIM( ABCD )= ABCD FIND(.,12345.65)=6 TEXT(12345.6687,￥0.00)= ￥12345.67 例2：從身份證號碼獲取出生日期和性別,文本函數(shù)應(yīng)用舉例,例3：將科目編碼與科目名稱分離（假設(shè)科目編碼與科目名稱之間有一個空格） 問題：如果科目