常用的20個(gè)EXCEL函數(shù)的使用詳解課件

常用的20個(gè)EXCEL函數(shù)常用的20個(gè)EXCEL函數(shù)第6章使用函數(shù)Excel函數(shù)的種類函數(shù)的基本語法函數(shù)的調(diào)用嵌套函數(shù)的使用使用Excel的幫助來理解函數(shù)常用函數(shù)介紹使用自定義函數(shù)第6章使用函數(shù)Excel函數(shù)的種類Excel函數(shù)的種類財(cái)務(wù)函數(shù)日期函數(shù)時(shí)間函數(shù)數(shù)學(xué)與三角函數(shù)統(tǒng)計(jì)函數(shù)數(shù)據(jù)庫管理函數(shù)文本函數(shù)信息類函數(shù)Excel函數(shù)的種類財(cái)務(wù)函數(shù)函數(shù)的基本語法函數(shù)的基本語法為：

=函數(shù)名(參數(shù)1,參數(shù)2,…,參數(shù)n)。注意問題：函數(shù)名與其后的括號(hào)“（”之間不能有空格。當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)之間要用逗號(hào)“，”分隔。參數(shù)部分總長(zhǎng)度不能超過1024個(gè)字符。參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達(dá)式或函數(shù)。函數(shù)中的逗號(hào)“，”、引號(hào)“"”等都是半角字符，而不是全角字符。函數(shù)的基本語法函數(shù)的基本語法為：函數(shù)的調(diào)用直接在單元格中輸入函數(shù)利用插入函數(shù)按鈕或命令插入函數(shù)函數(shù)的調(diào)用直接在單元格中輸入函數(shù)嵌套函數(shù)的使用當(dāng)一個(gè)函數(shù)中的參數(shù)為另外一個(gè)函數(shù)時(shí)，就是在使用嵌套函數(shù)。利用插入函數(shù)按鈕或命令插入各個(gè)函數(shù)。嵌套函數(shù)的使用當(dāng)一個(gè)函數(shù)中的參數(shù)為另外一個(gè)函數(shù)時(shí)，就是在使用使用Excel的幫助來理解函數(shù)直接在Excel右上角輸入函數(shù)名利用Excel函數(shù)幫助任務(wù)窗格。使用Excel的幫助來理解函數(shù)直接在Excel右上角輸入函數(shù)常用函數(shù)介紹求和函數(shù):SUM/SUMIF/SUMPRODUCT函數(shù)數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQRT函數(shù)計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)條件函數(shù)和邏輯函數(shù):IF函數(shù)、AND函數(shù)、OR函數(shù)日期和時(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ù)其他函數(shù)常用函數(shù)介紹求和函數(shù):SUM/SUMIF/SUMPRODU求和函數(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ù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和

=SUMPRODUCT(array1,array2,array3,...)求和函數(shù):SUM/SUMIF/SUMPRODUCT函數(shù)SU求和函數(shù):應(yīng)用舉例SUM函數(shù)

SUMIF函數(shù)SUMPRODUCT函數(shù)求和函數(shù):應(yīng)用舉例SUM函數(shù)求和函數(shù):應(yīng)用舉例

——隔欄加總計(jì)算某預(yù)算表如圖所示，每個(gè)月分為“預(yù)算數(shù)”和“實(shí)際數(shù)”，現(xiàn)要求計(jì)算年度“預(yù)算數(shù)”和“實(shí)際數(shù)”。這實(shí)際上就是隔欄加總問題。如果一個(gè)一個(gè)單元格相加是很繁瑣的。解決方法：巧妙應(yīng)用第3行的標(biāo)題。求和函數(shù):應(yīng)用舉例

——隔欄加總計(jì)算某預(yù)算表如圖所示，每求和函數(shù):應(yīng)用舉例

——往下（或往右）累加匯總往下（或往右）累加匯總是常見的實(shí)務(wù)問題。解決方法：使用SUM函數(shù)，但單元格引用的第一個(gè)地址應(yīng)為絕對(duì)引用，而第二個(gè)地址為相對(duì)引用。求和函數(shù):應(yīng)用舉例

——往下（或往右）累加匯總往下（或往求和函數(shù):應(yīng)用舉例

——?jiǎng)討B(tài)匯總當(dāng)天數(shù)據(jù)工作表中存在每天的數(shù)據(jù)，要求動(dòng)態(tài)匯總計(jì)算當(dāng)前的數(shù)據(jù)?？墒褂肧UMIF函數(shù)。但要注意條件的寫法。求和函數(shù):應(yīng)用舉例

——?jiǎng)討B(tài)匯總當(dāng)天數(shù)據(jù)工作表中存在每天數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQRT函數(shù)常用的數(shù)學(xué)函數(shù)有：AVERGAE函數(shù)：求平均值MAX：求最大值MIN：求最小值A(chǔ)BS：求絕對(duì)值SQRT：計(jì)算平方根數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQ平均值函數(shù):AVERGAE函數(shù)AVERGAE函數(shù):求N個(gè)數(shù)的平均值

=AVERGAE(參數(shù)1,參數(shù)2,…,參數(shù)N)平均值函數(shù):AVERGAE函數(shù)AVERGAE函數(shù):求N個(gè)最大值與最小值函數(shù):MAX、MIN函數(shù)MAX函數(shù):求N個(gè)數(shù)的最大值

=MAX(參數(shù)1,參數(shù)2,…,參數(shù)N)MIN函數(shù):求N個(gè)數(shù)的最小值

=MIN(參數(shù)1,參數(shù)2,…,參數(shù)N)最大值與最小值函數(shù):MAX、MIN函數(shù)MAX函數(shù):求絕對(duì)值函數(shù):ABS函數(shù)ABS函數(shù):求某數(shù)的絕對(duì)值

=ABS(參數(shù))例如，ABS(-100)=100絕對(duì)值函數(shù):ABS函數(shù)ABS函數(shù):求某數(shù)的絕對(duì)值開平方函數(shù):SQRT函數(shù)SQRT函數(shù):求某數(shù)的平方根

=SQRT(參數(shù))例如，SQRT(2)=1.4142135623731開平方函數(shù):SQRT函數(shù)SQRT函數(shù):求某數(shù)的平方根計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)COUNT函數(shù):計(jì)算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目

=COUNT(參數(shù)1,參數(shù)2,…,參數(shù)N)COUNTA函數(shù):返回參數(shù)列表中非空值的單元格個(gè)數(shù)

=COUNTA(參數(shù)1，參數(shù)2，…，參數(shù)N)COUNTIF函數(shù):計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目

=COUNTIF(range,criteria)計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)CO計(jì)數(shù)函數(shù):應(yīng)用舉例COUNT函數(shù):COUNTA函數(shù):COUNTIF函數(shù):計(jì)數(shù)函數(shù):應(yīng)用舉例COUNT函數(shù):計(jì)數(shù)函數(shù)應(yīng)用

——計(jì)算銷售業(yè)績(jī)計(jì)數(shù)函數(shù)應(yīng)用

——計(jì)算銷售業(yè)績(jī)計(jì)數(shù)函數(shù):

——計(jì)算高于平均業(yè)績(jī)的人數(shù)計(jì)數(shù)函數(shù):

——計(jì)算高于平均業(yè)績(jī)的人數(shù)條件函數(shù)和邏輯函數(shù):

IF函數(shù)、AND函數(shù)、OR函數(shù)IF函數(shù):IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。

=IF(條件表達(dá)式，條件值為真時(shí)返回的值，條件值為假時(shí)返回的值)AND函數(shù)：AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時(shí)（即所有參數(shù)的邏輯值都為真時(shí)），AND函數(shù)返回TRUE，否則，只要有一個(gè)條件不滿足即返回FALSE。

=AND(條件1，條件2，…條件N)OR函數(shù)：只要有一個(gè)條件滿足時(shí)，OR函數(shù)返回TRUE，只有當(dāng)所有條件都不滿足時(shí)才返回FALSE。

=OR(條件1,條件2,…條件N)說明：這3個(gè)函數(shù)常常聯(lián)合使用。條件函數(shù)和邏輯函數(shù):

IF函數(shù)、AND函數(shù)、OR函數(shù)IF函IF函數(shù)應(yīng)用舉例之一

——計(jì)算獎(jiǎng)金IF函數(shù)應(yīng)用舉例之一

——計(jì)算獎(jiǎng)金IF函數(shù)應(yīng)用舉例之二

——計(jì)算增長(zhǎng)率計(jì)算增長(zhǎng)率當(dāng)被比較年份沒有數(shù)據(jù)時(shí)，會(huì)出現(xiàn)被除數(shù)為0的錯(cuò)誤?？梢岳肐F函數(shù)進(jìn)行處理。如圖，去年沒有數(shù)據(jù)，而今年有數(shù)據(jù)，就顯示“新增項(xiàng)目”。去年有數(shù)據(jù)，而今年沒有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。IF函數(shù)應(yīng)用舉例之二

——計(jì)算增長(zhǎng)率計(jì)算增長(zhǎng)率IF函數(shù)應(yīng)用舉例之三自動(dòng)歸類問題為了有效管理零用金，希望在輸入現(xiàn)金開支數(shù)據(jù)后，該金額數(shù)據(jù)自動(dòng)依部門歸類到適當(dāng)?shù)牧?，從而更加醒目地?biāo)示出來。解決辦法：使用IF函數(shù)如圖，先在單元格F2輸入公式“=IF($D2=F$1,$C2,"")”。選定單元格區(qū)域F2:J2，按【F2】，使處于編輯狀態(tài)。按【Ctrl+Enter】組合鍵注意單元格的引用方式。IF函數(shù)應(yīng)用舉例之三自動(dòng)歸類問題AND函數(shù)應(yīng)用舉例AND函數(shù)應(yīng)用舉例OR函數(shù)應(yīng)用舉例OR函數(shù)應(yīng)用舉例聯(lián)合使用IF、AND和OR函數(shù)

計(jì)算獎(jiǎng)金聯(lián)合使用IF、AND和OR函數(shù)

計(jì)算獎(jiǎng)金日期函數(shù):

DAY、DATE、DAYS360、TODAY函數(shù)DAY函數(shù):返回以序列號(hào)表示的某日期的天數(shù)，用整數(shù)1～31表示。

=DAY(日期序列號(hào))例如DAY("2006-12-22")=22DATE函數(shù)：返回代表特定日期的序列號(hào)。

=DATE(年,月,日)例如DATE(2006,12,23)="2006-12-23"DAYS360函數(shù)：按照一年360天計(jì)（每個(gè)月以30天，一年共計(jì)12個(gè)月），返回兩個(gè)日期間相差的天數(shù)。

=DAYS360(開始日期,截止日期,邏輯值)

例如DAYS360("2000-1-15","2005-12-16")=2131天TODAY函數(shù)：返回系統(tǒng)當(dāng)前的日期。日期函數(shù):

DAY、DATE、DAYS360、TODAY函日期函數(shù)應(yīng)用(舉例）

設(shè)置動(dòng)態(tài)標(biāo)題：="今天是"&TEXT(TODAY(),"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")計(jì)算2年5個(gè)月20天后的日期=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20)計(jì)算實(shí)際歲數(shù)：=INT(YEARFRAC(出生日期,今天,1)=DATEIF(出生日期,今天,”Y”)日期函數(shù)應(yīng)用(舉例）

設(shè)置動(dòng)態(tài)標(biāo)題：日期函數(shù)應(yīng)用舉例——確定具體日期

日期函數(shù)應(yīng)用舉例——確定具體日期

日期函數(shù)應(yīng)用舉例

——確定兩個(gè)日期間的天數(shù)

日期函數(shù)應(yīng)用舉例

——確定兩個(gè)日期間的天數(shù)

日期函數(shù)應(yīng)用舉例

——設(shè)置日期顯示格式

日期函數(shù)應(yīng)用舉例

——設(shè)置日期顯示格式

日期函數(shù)應(yīng)用舉例——編制下周計(jì)劃表

編制下周計(jì)劃表可以在本周的任何一天制作下周的計(jì)劃表注意：任何一天的日期減去本身的星期數(shù)，就一定等于上一個(gè)星期天的日期。日期函數(shù)應(yīng)用舉例——編制下周計(jì)劃表

編制下周計(jì)劃表日期函數(shù)綜合應(yīng)用——設(shè)計(jì)考勤表日期函數(shù)綜合應(yīng)用——設(shè)計(jì)考勤表時(shí)間函數(shù)

輸入時(shí)間輸入22:00與輸入10:00PM是一樣的。如何計(jì)算跨午夜零時(shí)的時(shí)間間隔？輸入：=B2+(B2<A2)-A2

或者：=B2+IF(B2<A2,1,0)-A2思路：如果下班時(shí)間小于上班時(shí)間，就表示已經(jīng)過了1天，因此要加1。否則，如果下班時(shí)間大于上班時(shí)間，就表示還在當(dāng)前，因此不需要加1天。時(shí)間函數(shù)

輸入時(shí)間分析工具庫里的日期函數(shù)EDATE函數(shù)EOMONTH函數(shù)WEEKNUM函數(shù)WORKDAY函數(shù)NETWORKDAYS函數(shù)一個(gè)特殊的日期函數(shù)：DATEDIF注意：在使用這些函數(shù)之前，必須加載分析工具庫，即：?jiǎn)螕簟竟ぞ摺俊炯虞d宏】命令，打開【加載宏】對(duì)話框，選擇“分析工具庫”，如上圖。分析工具庫里的日期函數(shù)EDATE函數(shù)EDATE函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個(gè)月的日期。例：2007年4月12日之后3個(gè)月的日期：=EDATE(“2007-4-12”,3)，為2007-7-122007年4月12日之前3個(gè)月的日期：=EDATE(“2007-4-12”,-3)，為2007-1-12計(jì)算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起3個(gè)月的那一天，比如交易日為2006年11月30日，滿3個(gè)月后為2007-2-28：=EDATE(“2006-11-30”,3)，為2007-2-28EDATE函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個(gè)月的EOMONTH函數(shù)EOMONTH函數(shù)：返回指定日期往前或往后幾個(gè)月的特定月份的月底日期。例：2007年4月12日之后3個(gè)月的月末日期：=EOMONTH(“2007-4-12”,3)，為2007-7-312007年4月12日之前5個(gè)月的月末日期：=EDATE(“2007-4-12”,-5)，為2006-11-30計(jì)算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起滿3個(gè)月后的下一個(gè)月的5號(hào)，比如交易日為2006年11月20日，滿3個(gè)月后下個(gè)月5號(hào)就是2007-3-5：=EOMONTH("2006-11-20",3-(DAY(A1)<=5))+5EOMONTH函數(shù)EOMONTH函數(shù)：返回指定日期往前或往后WEEKNUM函數(shù)WEEKNUM函數(shù)：返回指定日期是該年的第幾周。例：2007年4月12日是2007年的第15周：=WEEKNUM(“2007-4-12”)，為第15周WEEKNUM函數(shù)WEEKNUM函數(shù)：返回指定日期是該年的第WORKDAY函數(shù)WORKDAY函數(shù)：返回某指定日期之前或之后的給定工作日天數(shù)的日期（除去雙休日和國(guó)家法定假日）。例：指定日期為2007年4月12日，往后30個(gè)工作日的日期為（要出去國(guó)家法定的五一3天假日）2007年5月29日：=WORKDAY("2007-4-12",30,{"2007-5-1","2007-5-2","2007-5-3"})假若一項(xiàng)工程開始日期為2007年4月20日，預(yù)計(jì)需要60個(gè)工作日，那么預(yù)計(jì)完工的時(shí)間為哪天？WORKDAY函數(shù)WORKDAY函數(shù)：返回某指定日期之前或之NETWORKDAYS函數(shù)NETWORKDAYS函數(shù)：返回兩個(gè)工作日之間的工作天數(shù)（除去雙休日和國(guó)家法定假日）。例：2007年4月12日至2007年6月20日之間的工作天數(shù)（除去雙休日和國(guó)家法定假日）為47天。NETWORKDAYS函數(shù)NETWORKDAYS函數(shù)：返回兩一個(gè)特殊的日期函數(shù)：DATEDIF

——確定兩個(gè)日期間的年數(shù)、月數(shù)和天數(shù)DATEDIF函數(shù)：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。這個(gè)函數(shù)是一個(gè)特殊函數(shù)，在函數(shù)清單中找不到，在幫助信息中也找不到。

DATEDIF(開始日期,結(jié)束日期,單位)

單位意義

“Y”時(shí)間段中的總年數(shù)

“M”時(shí)間段中的總月數(shù)

“D”時(shí)間段中的總天數(shù)

“MD”兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年和月

“YM”兩日期中月數(shù)的差，忽略日期數(shù)據(jù)中的年和日

“YD”兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年例如：某職員進(jìn)公司日期為1985年3月20日，離職時(shí)間為2007年8月9日，那么他在公司工作了多少年、多少月和多少天？工作年數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“Y”)=22年工作月數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“YM”)=4個(gè)月工作天數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“MD”)=20天問題：如果某人2月1日到職，2月28日離職，任職時(shí)間是28天還是1個(gè)月？注意：2月1日到職，開始日期應(yīng)為1月31日；2月28日離職，結(jié)束日期應(yīng)為3月1日。一個(gè)特殊的日期函數(shù)：DATEDIF

——確定兩個(gè)日期間的年數(shù)查找和引用函數(shù):VLOOKUP、HLOOKUP、MATCH、INDEX、CHOOSE、OFFSET函數(shù)VLOOKUP函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。HLOOKUP函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。MATCH函數(shù)：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。INDEX函數(shù)：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。CHOOSE函數(shù):：根據(jù)指定的索引值返回?cái)?shù)組中的數(shù)據(jù)OFFSET函數(shù)：動(dòng)態(tài)引用單元格。提示：我們還可以利用名稱來查找數(shù)據(jù)（詳見文件“通過名稱查找數(shù)據(jù).xls”）查找和引用函數(shù):VLOOKUP、HLOOKUP、MAVLOOKUP函數(shù)應(yīng)用舉例之一VLOOKUP函數(shù)應(yīng)用舉例之一VLOOKUP函數(shù)應(yīng)用舉例之二

制作采購表根據(jù)兩個(gè)表格制作采購表，如圖所示，計(jì)算步驟如下：?jiǎn)卧駞^(qū)域B10:B13為各種商品的采購數(shù)量，由人工輸入。在單元格C10中輸入公式“=VLOOKUP(A10,$A$3:$B$6,2,FALSE)”，獲取某商品的單價(jià)。在單元格D10中輸入公式=VLOOKUP(B10,$D$3:$H$6,MATCH(A10,$D$3:$H$3,0),TRUE)”，獲取某商品的折扣。在單元格E10中輸入公式“=B10*C10*(1-D10)”，計(jì)算采購金額。選取單元格區(qū)域C10:E10，將其向下填充復(fù)制到單元格區(qū)域C13:E13，得到其他商品的單價(jià)、折扣和采購金額。VLOOKUP函數(shù)應(yīng)用舉例之二

制作采購表根據(jù)兩個(gè)表格制VLOOKUP函數(shù)應(yīng)用舉例之三

根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的詳細(xì)信息根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的詳細(xì)信息，如圖所示。說明：由于產(chǎn)品代碼是唯一的標(biāo)識(shí)符，沒有重復(fù)，所以可以利用VLOOKUP函數(shù)或者HLOOKUP函數(shù)進(jìn)行查找。VLOOKUP函數(shù)應(yīng)用舉例之三

根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的HLOOKUP函數(shù)應(yīng)用舉例之一HLOOKUP函數(shù)應(yīng)用舉例之一HLOOKUP函數(shù)應(yīng)用舉例之二根據(jù)指定的行、列查找數(shù)據(jù)HLOOKUP函數(shù)應(yīng)用舉例之二根據(jù)指定的行、列查找數(shù)據(jù)HLOOKUP函數(shù)應(yīng)用舉例之三

計(jì)算底薪傭金制的薪金推銷員的薪金采用底薪傭金制，計(jì)算規(guī)則如下：

營(yíng)業(yè)額底薪傭金比率

0-299,99925,0000%300,000-499,99925,0001%500,000-999,99930,0002%1,000,000-1,499,99935,0003%1,500,000以上40,0004%整理上述數(shù)據(jù)到工作表，并輸入相應(yīng)計(jì)算公式，即得各個(gè)推銷人員的薪金。注意設(shè)計(jì)底薪傭金制計(jì)算規(guī)則表時(shí)要采用各級(jí)的底限值。HLOOKUP函數(shù)應(yīng)用舉例之三

計(jì)算底薪傭金制的薪金推銷MATCH函數(shù)應(yīng)用舉例之一

基本應(yīng)用注意：MATCH返回的位置是相對(duì)于指定的單元格區(qū)域而言的，而不是對(duì)整個(gè)工作表區(qū)域而言的。例如：MATCH(455,A2:E2,0)=3MATCH函數(shù)應(yīng)用舉例之一

基本應(yīng)用注意：MATCH返回MATCH函數(shù)應(yīng)用舉例之二

比較兩張工作表，將僅存在于其中某個(gè)工作表的數(shù)據(jù)篩選出來如圖，在兩個(gè)工作表中有重復(fù)的數(shù)據(jù)，現(xiàn)在要在工作表“財(cái)務(wù)部上報(bào)”中將工作表“銷售部上報(bào)”中重復(fù)的數(shù)據(jù)隱藏起來。篩選步驟：以工作表“財(cái)務(wù)部上報(bào)”為準(zhǔn)，設(shè)計(jì)一個(gè)輔助列，在單元格B2輸入公式：

=ISERROR(MATCH(A2,銷售部上報(bào)!$A$2:$A$7,0，并向下復(fù)制到數(shù)據(jù)區(qū)域的末尾。單擊【數(shù)據(jù)】→【篩選】→【自動(dòng)篩選】命令。篩選出需要對(duì)數(shù)據(jù)。最后采用選擇性粘貼的方法將數(shù)據(jù)復(fù)制到別處。MATCH函數(shù)應(yīng)用舉例之二

比較兩張工作表，將僅存在于INDEX函數(shù)應(yīng)用舉例INDEX函數(shù)應(yīng)用舉例MATCH函數(shù)與INDEX函數(shù)聯(lián)合使用

——查找某部門某項(xiàng)費(fèi)用的數(shù)額根據(jù)指定的部門名稱和費(fèi)用項(xiàng)目，查找相應(yīng)的金額。如下圖。說明：先用MATCH查找費(fèi)用項(xiàng)目在第幾行，用MATCH查找部門在第幾列，然后用INDEX函數(shù)取出行列交叉處的數(shù)據(jù)。MATCH函數(shù)與INDEX函數(shù)聯(lián)合使用

——查找某部門某項(xiàng)利用數(shù)組公式處理多維數(shù)據(jù)的查詢我們也可以利用連字符&連接多個(gè)條件進(jìn)行多維數(shù)據(jù)的查詢。先定義名稱，再利用MATCH函數(shù)和INDEX函數(shù)進(jìn)行查詢。利用數(shù)組公式處理多維數(shù)據(jù)的查詢我們也可以利用連字符&連接多個(gè)CHOOSE函數(shù)應(yīng)用舉例CHOOSE函數(shù)語法：CHOOSE(索引號(hào),值1,值2,…)例如，CHOOSE(1,{"AA","BB","CC","DD"})="AA"CHOOSE(3,{"AA","BB","CC","DD"})=“CC"舉例：依在本單位工作年限發(fā)放中秋節(jié)禮品禮品發(fā)放規(guī)定：本單位工齡未滿3年的送咖啡壺；滿3年未滿6年者送飲水機(jī)；滿6年未滿9年者送電磁爐；9年以上者送電烤箱。說明：也可以使用IF函數(shù)。注意：本單位工齡的計(jì)算方法。Excel默認(rèn)的日期系統(tǒng)是1900系統(tǒng)。也可以利用YEARFRAC函數(shù)計(jì)算工齡（或年齡）：

=INT(YEARFRAC(C4,$H$1))CHOOSE函數(shù)應(yīng)用舉例CHOOSE函數(shù)語法：CHOOSE(OFFSET函數(shù)應(yīng)用OFFSET函數(shù)語法：OFFSET(參照單元格,列位移量,行位移量,[高度],[寬度])

例如，公式=OFFSET(C3,2,3,1,1)將返回單元格F5中的值，這里，當(dāng)前指定的引用為單元格C3，以此為參照系，向下偏移2行，為第5行；向右偏移3列，為F列；高度和寬度均為1，表示僅為一個(gè)單元格。計(jì)算步驟：在單元格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))，計(jì)算到指定月份某部門的合計(jì)數(shù)，然后將單元格B14向右復(fù)制。說明：OFFSET函數(shù)在制作動(dòng)態(tài)查詢和動(dòng)態(tài)圖表時(shí)是非常有用的。OFFSET函數(shù)應(yīng)用OFFSET函數(shù)語法：從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之一

——根據(jù)銷售人員任期確定提成率有多個(gè)表格需要查詢，根據(jù)實(shí)際條件決定要查詢那個(gè)表格。利用IF語句判斷使用那個(gè)表格。實(shí)例如下，銷售人員任期不同，則提成率也不同。從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之一

——根據(jù)銷售人員任期確定從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之二有多個(gè)表格區(qū)域，結(jié)構(gòu)相同。根據(jù)指定的部門、月份、費(fèi)用項(xiàng)目查詢相應(yīng)的費(fèi)用金額。從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之二有多個(gè)表格區(qū)域，結(jié)構(gòu)相同。從多個(gè)工作表中查詢數(shù)據(jù)某公司將客戶分為A、B、C三個(gè)等級(jí)，給于不同的客戶編號(hào)（分別以A、B、C開頭）。不同等級(jí)客戶的折扣率是不同的，而同一等級(jí)客戶里不同產(chǎn)品的折扣率也是不同的。在確定客戶等級(jí)和產(chǎn)品之后，還得依據(jù)訂購數(shù)量給于該客戶最終的折扣。怎樣編寫查詢公式？太復(fù)雜了?。。?！從多個(gè)工作表中查詢數(shù)據(jù)某公司將客戶分為A、B、C三個(gè)等級(jí)，給從多個(gè)工作表中查詢數(shù)據(jù)考慮到從不同的工作表進(jìn)行查詢，而工作表名稱即為“客戶”加客戶編號(hào)的第一個(gè)字母，因此可以利用INDIRECT函數(shù)獲取要查詢工作表的單元格區(qū)域，利用MATCH函數(shù)確定指定商品編號(hào)所在的列和采購金額所在的行，再利用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")))從多個(gè)工作表中查詢數(shù)據(jù)考慮到從不同的工作表進(jìn)行查詢，而工作表區(qū)分大小寫的查詢VLOOKUP函數(shù)和HLOOKUP函數(shù)不區(qū)分大小寫。如果要區(qū)分大小寫進(jìn)行查詢，可以聯(lián)合使用INDEX函數(shù)、MATCH函數(shù)和EXACT函數(shù)。區(qū)分大小寫的查詢VLOOKUP函數(shù)和HLOOKUP函數(shù)不區(qū)分四舍五入函數(shù):

ROUND、FLOOR和CEILING函數(shù)

ROUND函數(shù):返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字。FLOOR函數(shù)：根據(jù)指定基數(shù)，將數(shù)字沿絕對(duì)值減小的方向向下舍入到最接近的倍數(shù)。CEILING函數(shù)：將參數(shù)Number向上舍入（沿絕對(duì)值增大的方向）到最接近的倍數(shù)。例如：ROUND(300.5485,2)=300.55FLOOR(2.5,1)=2，F(xiàn)LOOR(-2.5,-2)=-2CEILING(2.5,1)=3，CEILING(-2.5,-2)=-4四舍五入函數(shù):

ROUND、FLOOR和CEILING函數(shù)四舍五入函數(shù)應(yīng)用舉例:

——產(chǎn)品價(jià)格標(biāo)定

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

——產(chǎn)品價(jià)格標(biāo)定應(yīng)用舉例：某貿(mào)易取整函數(shù):INT函數(shù)INT函數(shù):將數(shù)字向下舍入到最接近的整數(shù)。

=INT(數(shù)字)

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

ISBLANK、ISTEXT、ISNUMBER、ISERROR函數(shù)

ISBLANK函數(shù)：判斷單元格是否為空白單元格ISTEXT函數(shù)：判斷單元格數(shù)據(jù)是否為文本ISNUMBER函數(shù)：判斷單元格數(shù)據(jù)是否為數(shù)字ISERROR函數(shù)：判斷單元格是否出現(xiàn)錯(cuò)誤信息函數(shù):

ISBLANK、ISTEXT、ISNUMBER文本函數(shù)

LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函數(shù)LEN函數(shù)：獲取字符串的長(zhǎng)度LEFT函數(shù)：獲取字符串左邊指定個(gè)數(shù)的文本RIGHT函數(shù)：獲取字符串右邊指定個(gè)數(shù)的文本TRIM函數(shù)：取消字符串兩側(cè)的空格FIND函數(shù)：查找某字符在字符串中第一次出現(xiàn)的位置TEXT函數(shù)：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本其他的文本函數(shù)文本函數(shù)

LEN、LEFT、RIGHT、TRIM、FIND、文本函數(shù)應(yīng)用舉例例1：LEN("ABCD")=4LEFT("ABCD",2)="AB"RIGHT("ABCD",2)="CD"TRIM("ABCD")="ABCD"FIND(".","12345.65")=6TEXT(12345.6687,"￥0.00")=￥12345.67例2：從身份證號(hào)碼獲取出生日期和性別文本函數(shù)應(yīng)用舉例例1：文本函數(shù)應(yīng)用舉例例3：將科目編碼與科目名稱分離（假設(shè)科目編碼與科目名稱之間有一個(gè)空格）問題：如果科目編碼與科目名稱之間沒有空格，該怎么辦呢？可使用LEN函數(shù)和LENB函數(shù)：文本函數(shù)應(yīng)用舉例例3：將科目編碼與科目名稱分離（假設(shè)科目編碼文本函數(shù)應(yīng)用舉例例4：編制簡(jiǎn)單的統(tǒng)計(jì)表選取單元格C2。輸入“=REPT("”，（不要按回車鍵，還沒有完成輸入）單擊【插入】→【特殊符號(hào)】命令，插入一個(gè)黑正方形。接著輸入“",B2*50)”將單元格C2向下填充復(fù)制。最后將C列的各個(gè)單元格的字體顏色分別設(shè)置為不同的顏色文本函數(shù)應(yīng)用舉例例4：編制簡(jiǎn)單的統(tǒng)計(jì)表排序函數(shù):RANK、LARGE、SMALL函數(shù)RANK函數(shù)：返回一個(gè)數(shù)字在數(shù)字列表中的排位。LARGE函數(shù)：返回?cái)?shù)據(jù)集中第k個(gè)最大值。例如，可以使用函數(shù)LARGE得到第一名、第二名或第三名的得分。SMALL函數(shù)：返回?cái)?shù)據(jù)集中第k個(gè)最小值。排序函數(shù):RANK、LARGE、SMALL函數(shù)RANK函數(shù)排序函數(shù)應(yīng)用舉例排序函數(shù)應(yīng)用舉例數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVGEAGE函數(shù)MicrosoftExcel提供了一些用于對(duì)存儲(chǔ)在列表或數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行分析的函數(shù)，這些函數(shù)統(tǒng)稱為數(shù)據(jù)庫函數(shù)（Dfunctions）。這些函數(shù)的語法結(jié)構(gòu)基本相同，即：Dfunctions(database,field,criteria)函數(shù)的三個(gè)參數(shù)database、field和criteria的含義如下：database：構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。數(shù)據(jù)庫是包含一組相關(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è)定條件的單元格。在財(cái)務(wù)管理中，常用的數(shù)據(jù)庫函數(shù)有DSUM函數(shù)、DMAX函數(shù)、DMIN函數(shù)、DGET函數(shù)等。數(shù)據(jù)庫函數(shù)：DGET、DMAX、DMIN、DSUM、DAVG復(fù)合條件的語法同一行的構(gòu)成了“與”關(guān)系條件。查找薪金在5000至8000元之間的紀(jì)錄。同一列構(gòu)成了“或”關(guān)系條件。查找部門為銷售部和辦公室的記錄。不同列、同行構(gòu)成了不同字段的“與”關(guān)系。查找男性且為經(jīng)理的紀(jì)錄。不同列、不同行構(gòu)成了不同字段的“或”關(guān)系條件。查找職稱為高級(jí)工程師或職務(wù)為經(jīng)理的紀(jì)錄。不同列、不同行的“與”關(guān)系和“或”關(guān)系的復(fù)雜條件：查找辦公室的女性經(jīng)理、或者銷售部的男性高級(jí)工程師的紀(jì)錄。復(fù)合條件的語法同一行的構(gòu)成了“與”關(guān)系條件。DSUM函數(shù)應(yīng)用舉例DSUM函數(shù)應(yīng)用舉例DGET函數(shù)應(yīng)用舉例DGET函數(shù)應(yīng)用舉例DMAX函數(shù)和DMIN函數(shù)應(yīng)用舉例DMAX函數(shù)和DMIN函數(shù)應(yīng)用舉例其他函數(shù)其他函數(shù)有很多，可根據(jù)需要使用。下面利用矩陣運(yùn)算函數(shù)MMULT來計(jì)算材料的采購量。想一想，如果用SUMPRODUCT函數(shù)該怎么做？要聯(lián)合使用SUMPRODUCT函數(shù)和TRANSPOSE函數(shù)，并且以數(shù)組公式輸入。其他函數(shù)其他函數(shù)有很多，可根據(jù)需要使用。使用自定義函數(shù)自定義函數(shù)以Function開頭，以EndFunction結(jié)束。在Function與EndFunction之間的是程序代碼。自定義函數(shù)的返回值只有一個(gè)，并且只能通過表達(dá)式來調(diào)用。自定義函數(shù)可以有參數(shù)，也可以沒有參數(shù)。自定義函數(shù)的程序結(jié)構(gòu)如下：不帶參數(shù)的自定義函數(shù)結(jié)構(gòu)如下：Function函數(shù)名()……（程序代碼）函數(shù)名=表達(dá)式EndFunction帶參數(shù)的自定義函數(shù)如下：Function函數(shù)名(參數(shù)1,參數(shù)2,…)……（程序代碼）函數(shù)名=表達(dá)式EndFunction使用自定義函數(shù)自定義函數(shù)以Function開頭，以EndF使用自定義函數(shù)創(chuàng)建自定義函數(shù)打開VBE界面插入模塊。編寫自定義函數(shù)程序代碼。使用自定義函數(shù)創(chuàng)建自定義函數(shù)使用自定義函數(shù)在工作表中使用自定義函數(shù)單擊工作表上的任一單元格。單擊編輯欄的【插入函數(shù)】按鈕，或執(zhí)行【插入】→【函數(shù)】命令，打開【插入函數(shù)】對(duì)話框。在【或選擇類別】中選“用戶定義”，在【選擇函數(shù)】中選自定義函數(shù)。單擊【確定】按鈕，打開自定義函數(shù)的對(duì)話框。輸入有關(guān)參數(shù)。使用自定義函數(shù)在工作表中使用自定義函數(shù)常用的20個(gè)EXCEL函數(shù)常用的20個(gè)EXCEL函數(shù)第6章使用函數(shù)Excel函數(shù)的種類函數(shù)的基本語法函數(shù)的調(diào)用嵌套函數(shù)的使用使用Excel的幫助來理解函數(shù)常用函數(shù)介紹使用自定義函數(shù)第6章使用函數(shù)Excel函數(shù)的種類Excel函數(shù)的種類財(cái)務(wù)函數(shù)日期函數(shù)時(shí)間函數(shù)數(shù)學(xué)與三角函數(shù)統(tǒng)計(jì)函數(shù)數(shù)據(jù)庫管理函數(shù)文本函數(shù)信息類函數(shù)Excel函數(shù)的種類財(cái)務(wù)函數(shù)函數(shù)的基本語法函數(shù)的基本語法為：

=函數(shù)名(參數(shù)1,參數(shù)2,…,參數(shù)n)。注意問題：函數(shù)名與其后的括號(hào)“（”之間不能有空格。當(dāng)有多個(gè)參數(shù)時(shí)，參數(shù)之間要用逗號(hào)“，”分隔。參數(shù)部分總長(zhǎng)度不能超過1024個(gè)字符。參數(shù)可以是數(shù)值、文本、邏輯值、單元格引用，也可以是各種表達(dá)式或函數(shù)。函數(shù)中的逗號(hào)“，”、引號(hào)“"”等都是半角字符，而不是全角字符。函數(shù)的基本語法函數(shù)的基本語法為：函數(shù)的調(diào)用直接在單元格中輸入函數(shù)利用插入函數(shù)按鈕或命令插入函數(shù)函數(shù)的調(diào)用直接在單元格中輸入函數(shù)嵌套函數(shù)的使用當(dāng)一個(gè)函數(shù)中的參數(shù)為另外一個(gè)函數(shù)時(shí)，就是在使用嵌套函數(shù)。利用插入函數(shù)按鈕或命令插入各個(gè)函數(shù)。嵌套函數(shù)的使用當(dāng)一個(gè)函數(shù)中的參數(shù)為另外一個(gè)函數(shù)時(shí)，就是在使用使用Excel的幫助來理解函數(shù)直接在Excel右上角輸入函數(shù)名利用Excel函數(shù)幫助任務(wù)窗格。使用Excel的幫助來理解函數(shù)直接在Excel右上角輸入函數(shù)常用函數(shù)介紹求和函數(shù):SUM/SUMIF/SUMPRODUCT函數(shù)數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQRT函數(shù)計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)條件函數(shù)和邏輯函數(shù):IF函數(shù)、AND函數(shù)、OR函數(shù)日期和時(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ù)其他函數(shù)常用函數(shù)介紹求和函數(shù):SUM/SUMIF/SUMPRODU求和函數(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ù)組間對(duì)應(yīng)的元素相乘，并返回乘積之和

=SUMPRODUCT(array1,array2,array3,...)求和函數(shù):SUM/SUMIF/SUMPRODUCT函數(shù)SU求和函數(shù):應(yīng)用舉例SUM函數(shù)

SUMIF函數(shù)SUMPRODUCT函數(shù)求和函數(shù):應(yīng)用舉例SUM函數(shù)求和函數(shù):應(yīng)用舉例

——隔欄加總計(jì)算某預(yù)算表如圖所示，每個(gè)月分為“預(yù)算數(shù)”和“實(shí)際數(shù)”，現(xiàn)要求計(jì)算年度“預(yù)算數(shù)”和“實(shí)際數(shù)”。這實(shí)際上就是隔欄加總問題。如果一個(gè)一個(gè)單元格相加是很繁瑣的。解決方法：巧妙應(yīng)用第3行的標(biāo)題。求和函數(shù):應(yīng)用舉例

——隔欄加總計(jì)算某預(yù)算表如圖所示，每求和函數(shù):應(yīng)用舉例

——往下（或往右）累加匯總往下（或往右）累加匯總是常見的實(shí)務(wù)問題。解決方法：使用SUM函數(shù)，但單元格引用的第一個(gè)地址應(yīng)為絕對(duì)引用，而第二個(gè)地址為相對(duì)引用。求和函數(shù):應(yīng)用舉例

——往下（或往右）累加匯總往下（或往求和函數(shù):應(yīng)用舉例

——?jiǎng)討B(tài)匯總當(dāng)天數(shù)據(jù)工作表中存在每天的數(shù)據(jù)，要求動(dòng)態(tài)匯總計(jì)算當(dāng)前的數(shù)據(jù)?？墒褂肧UMIF函數(shù)。但要注意條件的寫法。求和函數(shù):應(yīng)用舉例

——?jiǎng)討B(tài)匯總當(dāng)天數(shù)據(jù)工作表中存在每天數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQRT函數(shù)常用的數(shù)學(xué)函數(shù)有：AVERGAE函數(shù)：求平均值MAX：求最大值MIN：求最小值A(chǔ)BS：求絕對(duì)值SQRT：計(jì)算平方根數(shù)學(xué)函數(shù)AVERGAE、MAX、MIN、ABS、SQ平均值函數(shù):AVERGAE函數(shù)AVERGAE函數(shù):求N個(gè)數(shù)的平均值

=AVERGAE(參數(shù)1,參數(shù)2,…,參數(shù)N)平均值函數(shù):AVERGAE函數(shù)AVERGAE函數(shù):求N個(gè)最大值與最小值函數(shù):MAX、MIN函數(shù)MAX函數(shù):求N個(gè)數(shù)的最大值

=MAX(參數(shù)1,參數(shù)2,…,參數(shù)N)MIN函數(shù):求N個(gè)數(shù)的最小值

=MIN(參數(shù)1,參數(shù)2,…,參數(shù)N)最大值與最小值函數(shù):MAX、MIN函數(shù)MAX函數(shù):求絕對(duì)值函數(shù):ABS函數(shù)ABS函數(shù):求某數(shù)的絕對(duì)值

=ABS(參數(shù))例如，ABS(-100)=100絕對(duì)值函數(shù):ABS函數(shù)ABS函數(shù):求某數(shù)的絕對(duì)值開平方函數(shù):SQRT函數(shù)SQRT函數(shù):求某數(shù)的平方根

=SQRT(參數(shù))例如，SQRT(2)=1.4142135623731開平方函數(shù):SQRT函數(shù)SQRT函數(shù):求某數(shù)的平方根計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)COUNT函數(shù):計(jì)算給定區(qū)域內(nèi)數(shù)值型參數(shù)的數(shù)目

=COUNT(參數(shù)1,參數(shù)2,…,參數(shù)N)COUNTA函數(shù):返回參數(shù)列表中非空值的單元格個(gè)數(shù)

=COUNTA(參數(shù)1，參數(shù)2，…，參數(shù)N)COUNTIF函數(shù):計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目

=COUNTIF(range,criteria)計(jì)數(shù)函數(shù):COUNT、COUNTA、COUNTIF函數(shù)CO計(jì)數(shù)函數(shù):應(yīng)用舉例COUNT函數(shù):COUNTA函數(shù):COUNTIF函數(shù):計(jì)數(shù)函數(shù):應(yīng)用舉例COUNT函數(shù):計(jì)數(shù)函數(shù)應(yīng)用

——計(jì)算銷售業(yè)績(jī)計(jì)數(shù)函數(shù)應(yīng)用

——計(jì)算銷售業(yè)績(jī)計(jì)數(shù)函數(shù):

——計(jì)算高于平均業(yè)績(jī)的人數(shù)計(jì)數(shù)函數(shù):

——計(jì)算高于平均業(yè)績(jī)的人數(shù)條件函數(shù)和邏輯函數(shù):

IF函數(shù)、AND函數(shù)、OR函數(shù)IF函數(shù):IF函數(shù)也稱條件函數(shù)，它根據(jù)參數(shù)條件的真假，返回不同的結(jié)果。

=IF(條件表達(dá)式，條件值為真時(shí)返回的值，條件值為假時(shí)返回的值)AND函數(shù)：AND函數(shù)表示邏輯與，當(dāng)所有條件都滿足時(shí)（即所有參數(shù)的邏輯值都為真時(shí)），AND函數(shù)返回TRUE，否則，只要有一個(gè)條件不滿足即返回FALSE。

=AND(條件1，條件2，…條件N)OR函數(shù)：只要有一個(gè)條件滿足時(shí)，OR函數(shù)返回TRUE，只有當(dāng)所有條件都不滿足時(shí)才返回FALSE。

=OR(條件1,條件2,…條件N)說明：這3個(gè)函數(shù)常常聯(lián)合使用。條件函數(shù)和邏輯函數(shù):

IF函數(shù)、AND函數(shù)、OR函數(shù)IF函IF函數(shù)應(yīng)用舉例之一

——計(jì)算獎(jiǎng)金IF函數(shù)應(yīng)用舉例之一

——計(jì)算獎(jiǎng)金IF函數(shù)應(yīng)用舉例之二

——計(jì)算增長(zhǎng)率計(jì)算增長(zhǎng)率當(dāng)被比較年份沒有數(shù)據(jù)時(shí)，會(huì)出現(xiàn)被除數(shù)為0的錯(cuò)誤?？梢岳肐F函數(shù)進(jìn)行處理。如圖，去年沒有數(shù)據(jù)，而今年有數(shù)據(jù)，就顯示“新增項(xiàng)目”。去年有數(shù)據(jù)，而今年沒有數(shù)據(jù)，就顯示“已經(jīng)停產(chǎn)”。IF函數(shù)應(yīng)用舉例之二

——計(jì)算增長(zhǎng)率計(jì)算增長(zhǎng)率IF函數(shù)應(yīng)用舉例之三自動(dòng)歸類問題為了有效管理零用金，希望在輸入現(xiàn)金開支數(shù)據(jù)后，該金額數(shù)據(jù)自動(dòng)依部門歸類到適當(dāng)?shù)牧?，從而更加醒目地?biāo)示出來。解決辦法：使用IF函數(shù)如圖，先在單元格F2輸入公式“=IF($D2=F$1,$C2,"")”。選定單元格區(qū)域F2:J2，按【F2】，使處于編輯狀態(tài)。按【Ctrl+Enter】組合鍵注意單元格的引用方式。IF函數(shù)應(yīng)用舉例之三自動(dòng)歸類問題AND函數(shù)應(yīng)用舉例AND函數(shù)應(yīng)用舉例OR函數(shù)應(yīng)用舉例OR函數(shù)應(yīng)用舉例聯(lián)合使用IF、AND和OR函數(shù)

計(jì)算獎(jiǎng)金聯(lián)合使用IF、AND和OR函數(shù)

計(jì)算獎(jiǎng)金日期函數(shù):

DAY、DATE、DAYS360、TODAY函數(shù)DAY函數(shù):返回以序列號(hào)表示的某日期的天數(shù)，用整數(shù)1～31表示。

=DAY(日期序列號(hào))例如DAY("2006-12-22")=22DATE函數(shù)：返回代表特定日期的序列號(hào)。

=DATE(年,月,日)例如DATE(2006,12,23)="2006-12-23"DAYS360函數(shù)：按照一年360天計(jì)（每個(gè)月以30天，一年共計(jì)12個(gè)月），返回兩個(gè)日期間相差的天數(shù)。

=DAYS360(開始日期,截止日期,邏輯值)

例如DAYS360("2000-1-15","2005-12-16")=2131天TODAY函數(shù)：返回系統(tǒng)當(dāng)前的日期。日期函數(shù):

DAY、DATE、DAYS360、TODAY函日期函數(shù)應(yīng)用(舉例）

設(shè)置動(dòng)態(tài)標(biāo)題：="今天是"&TEXT(TODAY(),"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")計(jì)算2年5個(gè)月20天后的日期=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20)計(jì)算實(shí)際歲數(shù)：=INT(YEARFRAC(出生日期,今天,1)=DATEIF(出生日期,今天,”Y”)日期函數(shù)應(yīng)用(舉例）

設(shè)置動(dòng)態(tài)標(biāo)題：日期函數(shù)應(yīng)用舉例——確定具體日期

日期函數(shù)應(yīng)用舉例——確定具體日期

日期函數(shù)應(yīng)用舉例

——確定兩個(gè)日期間的天數(shù)

日期函數(shù)應(yīng)用舉例

——確定兩個(gè)日期間的天數(shù)

日期函數(shù)應(yīng)用舉例

——設(shè)置日期顯示格式

日期函數(shù)應(yīng)用舉例

——設(shè)置日期顯示格式

日期函數(shù)應(yīng)用舉例——編制下周計(jì)劃表

編制下周計(jì)劃表可以在本周的任何一天制作下周的計(jì)劃表注意：任何一天的日期減去本身的星期數(shù)，就一定等于上一個(gè)星期天的日期。日期函數(shù)應(yīng)用舉例——編制下周計(jì)劃表

編制下周計(jì)劃表日期函數(shù)綜合應(yīng)用——設(shè)計(jì)考勤表日期函數(shù)綜合應(yīng)用——設(shè)計(jì)考勤表時(shí)間函數(shù)

輸入時(shí)間輸入22:00與輸入10:00PM是一樣的。如何計(jì)算跨午夜零時(shí)的時(shí)間間隔？輸入：=B2+(B2<A2)-A2

或者：=B2+IF(B2<A2,1,0)-A2思路：如果下班時(shí)間小于上班時(shí)間，就表示已經(jīng)過了1天，因此要加1。否則，如果下班時(shí)間大于上班時(shí)間，就表示還在當(dāng)前，因此不需要加1天。時(shí)間函數(shù)

輸入時(shí)間分析工具庫里的日期函數(shù)EDATE函數(shù)EOMONTH函數(shù)WEEKNUM函數(shù)WORKDAY函數(shù)NETWORKDAYS函數(shù)一個(gè)特殊的日期函數(shù)：DATEDIF注意：在使用這些函數(shù)之前，必須加載分析工具庫，即：?jiǎn)螕簟竟ぞ摺俊炯虞d宏】命令，打開【加載宏】對(duì)話框，選擇“分析工具庫”，如上圖。分析工具庫里的日期函數(shù)EDATE函數(shù)EDATE函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個(gè)月的日期。例：2007年4月12日之后3個(gè)月的日期：=EDATE(“2007-4-12”,3)，為2007-7-122007年4月12日之前3個(gè)月的日期：=EDATE(“2007-4-12”,-3)，為2007-1-12計(jì)算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起3個(gè)月的那一天，比如交易日為2006年11月30日，滿3個(gè)月后為2007-2-28：=EDATE(“2006-11-30”,3)，為2007-2-28EDATE函數(shù)EDATE函數(shù)：返回指定日期往前或往后幾個(gè)月的EOMONTH函數(shù)EOMONTH函數(shù)：返回指定日期往前或往后幾個(gè)月的特定月份的月底日期。例：2007年4月12日之后3個(gè)月的月末日期：=EOMONTH(“2007-4-12”,3)，為2007-7-312007年4月12日之前5個(gè)月的月末日期：=EDATE(“2007-4-12”,-5)，為2006-11-30計(jì)算應(yīng)付賬款的到期日：如果一筆應(yīng)付款的到期日為自交易日起滿3個(gè)月后的下一個(gè)月的5號(hào)，比如交易日為2006年11月20日，滿3個(gè)月后下個(gè)月5號(hào)就是2007-3-5：=EOMONTH("2006-11-20",3-(DAY(A1)<=5))+5EOMONTH函數(shù)EOMONTH函數(shù)：返回指定日期往前或往后WEEKNUM函數(shù)WEEKNUM函數(shù)：返回指定日期是該年的第幾周。例：2007年4月12日是2007年的第15周：=WEEKNUM(“2007-4-12”)，為第15周WEEKNUM函數(shù)WEEKNUM函數(shù)：返回指定日期是該年的第WORKDAY函數(shù)WORKDAY函數(shù)：返回某指定日期之前或之后的給定工作日天數(shù)的日期（除去雙休日和國(guó)家法定假日）。例：指定日期為2007年4月12日，往后30個(gè)工作日的日期為（要出去國(guó)家法定的五一3天假日）2007年5月29日：=WORKDAY("2007-4-12",30,{"2007-5-1","2007-5-2","2007-5-3"})假若一項(xiàng)工程開始日期為2007年4月20日，預(yù)計(jì)需要60個(gè)工作日，那么預(yù)計(jì)完工的時(shí)間為哪天？WORKDAY函數(shù)WORKDAY函數(shù)：返回某指定日期之前或之NETWORKDAYS函數(shù)NETWORKDAYS函數(shù)：返回兩個(gè)工作日之間的工作天數(shù)（除去雙休日和國(guó)家法定假日）。例：2007年4月12日至2007年6月20日之間的工作天數(shù)（除去雙休日和國(guó)家法定假日）為47天。NETWORKDAYS函數(shù)NETWORKDAYS函數(shù)：返回兩一個(gè)特殊的日期函數(shù)：DATEDIF

——確定兩個(gè)日期間的年數(shù)、月數(shù)和天數(shù)DATEDIF函數(shù)：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)。這個(gè)函數(shù)是一個(gè)特殊函數(shù)，在函數(shù)清單中找不到，在幫助信息中也找不到。

DATEDIF(開始日期,結(jié)束日期,單位)

單位意義

“Y”時(shí)間段中的總年數(shù)

“M”時(shí)間段中的總月數(shù)

“D”時(shí)間段中的總天數(shù)

“MD”兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年和月

“YM”兩日期中月數(shù)的差，忽略日期數(shù)據(jù)中的年和日

“YD”兩日期中天數(shù)的差，忽略日期數(shù)據(jù)中的年例如：某職員進(jìn)公司日期為1985年3月20日，離職時(shí)間為2007年8月9日，那么他在公司工作了多少年、多少月和多少天？工作年數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“Y”)=22年工作月數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“YM”)=4個(gè)月工作天數(shù)： =DATEDIF(“1985-3-20”,“2007-8-9”,“MD”)=20天問題：如果某人2月1日到職，2月28日離職，任職時(shí)間是28天還是1個(gè)月？注意：2月1日到職，開始日期應(yīng)為1月31日；2月28日離職，結(jié)束日期應(yīng)為3月1日。一個(gè)特殊的日期函數(shù)：DATEDIF

——確定兩個(gè)日期間的年數(shù)查找和引用函數(shù):VLOOKUP、HLOOKUP、MATCH、INDEX、CHOOSE、OFFSET函數(shù)VLOOKUP函數(shù)：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。HLOOKUP函數(shù)：從表格或數(shù)值數(shù)組的首行查找指定的數(shù)值，并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。MATCH函數(shù)：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。INDEX函數(shù)：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。CHOOSE函數(shù):：根據(jù)指定的索引值返回?cái)?shù)組中的數(shù)據(jù)OFFSET函數(shù)：動(dòng)態(tài)引用單元格。提示：我們還可以利用名稱來查找數(shù)據(jù)（詳見文件“通過名稱查找數(shù)據(jù).xls”）查找和引用函數(shù):VLOOKUP、HLOOKUP、MAVLOOKUP函數(shù)應(yīng)用舉例之一VLOOKUP函數(shù)應(yīng)用舉例之一VLOOKUP函數(shù)應(yīng)用舉例之二

制作采購表根據(jù)兩個(gè)表格制作采購表，如圖所示，計(jì)算步驟如下：?jiǎn)卧駞^(qū)域B10:B13為各種商品的采購數(shù)量，由人工輸入。在單元格C10中輸入公式“=VLOOKUP(A10,$A$3:$B$6,2,FALSE)”，獲取某商品的單價(jià)。在單元格D10中輸入公式=VLOOKUP(B10,$D$3:$H$6,MATCH(A10,$D$3:$H$3,0),TRUE)”，獲取某商品的折扣。在單元格E10中輸入公式“=B10*C10*(1-D10)”，計(jì)算采購金額。選取單元格區(qū)域C10:E10，將其向下填充復(fù)制到單元格區(qū)域C13:E13，得到其他商品的單價(jià)、折扣和采購金額。VLOOKUP函數(shù)應(yīng)用舉例之二

制作采購表根據(jù)兩個(gè)表格制VLOOKUP函數(shù)應(yīng)用舉例之三

根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的詳細(xì)信息根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的詳細(xì)信息，如圖所示。說明：由于產(chǎn)品代碼是唯一的標(biāo)識(shí)符，沒有重復(fù)，所以可以利用VLOOKUP函數(shù)或者HLOOKUP函數(shù)進(jìn)行查找。VLOOKUP函數(shù)應(yīng)用舉例之三

根據(jù)產(chǎn)品代碼查詢?cè)摦a(chǎn)品的HLOOKUP函數(shù)應(yīng)用舉例之一HLOOKUP函數(shù)應(yīng)用舉例之一HLOOKUP函數(shù)應(yīng)用舉例之二根據(jù)指定的行、列查找數(shù)據(jù)HLOOKUP函數(shù)應(yīng)用舉例之二根據(jù)指定的行、列查找數(shù)據(jù)HLOOKUP函數(shù)應(yīng)用舉例之三

計(jì)算底薪傭金制的薪金推銷員的薪金采用底薪傭金制，計(jì)算規(guī)則如下：

營(yíng)業(yè)額底薪傭金比率

0-299,99925,0000%300,000-499,99925,0001%500,000-999,99930,0002%1,000,000-1,499,99935,0003%1,500,000以上40,0004%整理上述數(shù)據(jù)到工作表，并輸入相應(yīng)計(jì)算公式，即得各個(gè)推銷人員的薪金。注意設(shè)計(jì)底薪傭金制計(jì)算規(guī)則表時(shí)要采用各級(jí)的底限值。HLOOKUP函數(shù)應(yīng)用舉例之三

計(jì)算底薪傭金制的薪金推銷MATCH函數(shù)應(yīng)用舉例之一

基本應(yīng)用注意：MATCH返回的位置是相對(duì)于指定的單元格區(qū)域而言的，而不是對(duì)整個(gè)工作表區(qū)域而言的。例如：MATCH(455,A2:E2,0)=3MATCH函數(shù)應(yīng)用舉例之一

基本應(yīng)用注意：MATCH返回MATCH函數(shù)應(yīng)用舉例之二

比較兩張工作表，將僅存在于其中某個(gè)工作表的數(shù)據(jù)篩選出來如圖，在兩個(gè)工作表中有重復(fù)的數(shù)據(jù)，現(xiàn)在要在工作表“財(cái)務(wù)部上報(bào)”中將工作表“銷售部上報(bào)”中重復(fù)的數(shù)據(jù)隱藏起來。篩選步驟：以工作表“財(cái)務(wù)部上報(bào)”為準(zhǔn)，設(shè)計(jì)一個(gè)輔助列，在單元格B2輸入公式：

=ISERROR(MATCH(A2,銷售部上報(bào)!$A$2:$A$7,0，并向下復(fù)制到數(shù)據(jù)區(qū)域的末尾。單擊【數(shù)據(jù)】→【篩選】→【自動(dòng)篩選】命令。篩選出需要對(duì)數(shù)據(jù)。最后采用選擇性粘貼的方法將數(shù)據(jù)復(fù)制到別處。MATCH函數(shù)應(yīng)用舉例之二

比較兩張工作表，將僅存在于INDEX函數(shù)應(yīng)用舉例INDEX函數(shù)應(yīng)用舉例MATCH函數(shù)與INDEX函數(shù)聯(lián)合使用

——查找某部門某項(xiàng)費(fèi)用的數(shù)額根據(jù)指定的部門名稱和費(fèi)用項(xiàng)目，查找相應(yīng)的金額。如下圖。說明：先用MATCH查找費(fèi)用項(xiàng)目在第幾行，用MATCH查找部門在第幾列，然后用INDEX函數(shù)取出行列交叉處的數(shù)據(jù)。MATCH函數(shù)與INDEX函數(shù)聯(lián)合使用

——查找某部門某項(xiàng)利用數(shù)組公式處理多維數(shù)據(jù)的查詢我們也可以利用連字符&連接多個(gè)條件進(jìn)行多維數(shù)據(jù)的查詢。先定義名稱，再利用MATCH函數(shù)和INDEX函數(shù)進(jìn)行查詢。利用數(shù)組公式處理多維數(shù)據(jù)的查詢我們也可以利用連字符&連接多個(gè)CHOOSE函數(shù)應(yīng)用舉例CHOOSE函數(shù)語法：CHOOSE(索引號(hào),值1,值2,…)例如，CHOOSE(1,{"AA","BB","CC","DD"})="AA"CHOOSE(3,{"AA","BB","CC","DD"})=“CC"舉例：依在本單位工作年限發(fā)放中秋節(jié)禮品禮品發(fā)放規(guī)定：本單位工齡未滿3年的送咖啡壺；滿3年未滿6年者送飲水機(jī)；滿6年未滿9年者送電磁爐；9年以上者送電烤箱。說明：也可以使用IF函數(shù)。注意：本單位工齡的計(jì)算方法。Excel默認(rèn)的日期系統(tǒng)是1900系統(tǒng)。也可以利用YEARFRAC函數(shù)計(jì)算工齡（或年齡）：

=INT(YEARFRAC(C4,$H$1))CHOOSE函數(shù)應(yīng)用舉例CHOOSE函數(shù)語法：CHOOSE(OFFSET函數(shù)應(yīng)用OFFSET函數(shù)語法：OFFSET(參照單元格,列位移量,行位移量,[高度],[寬度])

例如，公式=OFFSET(C3,2,3,1,1)將返回單元格F5中的值，這里，當(dāng)前指定的引用為單元格C3，以此為參照系，向下偏移2行，為第5行；向右偏移3列，為F列；高度和寬度均為1，表示僅為一個(gè)單元格。計(jì)算步驟：在單元格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))，計(jì)算到指定月份某部門的合計(jì)數(shù)，然后將單元格B14向右復(fù)制。說明：OFFSET函數(shù)在制作動(dòng)態(tài)查詢和動(dòng)態(tài)圖表時(shí)是非常有用的。OFFSET函數(shù)應(yīng)用OFFSET函數(shù)語法：從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之一

——根據(jù)銷售人員任期確定提成率有多個(gè)表格需要查詢，根據(jù)實(shí)際條件決定要查詢那個(gè)表格。利用IF語句判斷使用那個(gè)表格。實(shí)例如下，銷售人員任期不同，則提成率也不同。從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之一

——根據(jù)銷售人員任期確定從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之二有多個(gè)表格區(qū)域，結(jié)構(gòu)相同。根據(jù)指定的部門、月份、費(fèi)用項(xiàng)目查詢相應(yīng)的費(fèi)用金額。從多個(gè)表格區(qū)域中查詢數(shù)據(jù)之二有多個(gè)表格區(qū)域，結(jié)構(gòu)相同。從多個(gè)工作表中查詢數(shù)據(jù)某公司將客戶分為A、B、C三個(gè)等級(jí)，給于不同的客戶編號(hào)（分別以A、B、C開頭）。不同等級(jí)客戶的折扣率是不同的，而同一等級(jí)客戶里不同產(chǎn)品的折扣率也是不同的。在確定客戶等級(jí)和產(chǎn)品之后，還得依據(jù)訂購數(shù)量給于該客戶最終的折扣。怎樣編寫查詢公式？太復(fù)雜了?。。?！從多個(gè)工作表中查詢數(shù)據(jù)某公司將客戶分為A、B、C三個(gè)等級(jí)，給從多個(gè)工作表中查詢數(shù)據(jù)考慮到從不同的工作表進(jìn)行查詢，而工作表名稱即為“客戶”加客戶編號(hào)的第一個(gè)字母，因此可以利用INDIRECT函數(shù)獲取要查詢工作表的單元格區(qū)域，利用MATCH函數(shù)確定指定商品編號(hào)所在的列和采購金額所在的行，再利用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")))從多個(gè)工作表中查詢數(shù)據(jù)考慮到從不同的工作表進(jìn)行查詢，而工作表區(qū)分大小寫的查詢VLOOKUP函數(shù)和HLOOKUP函數(shù)不區(qū)分大小寫。如果要區(qū)分大小寫進(jìn)行查詢，可以聯(lián)合使用INDEX函數(shù)、MATCH函數(shù)和EXACT函數(shù)。區(qū)分大小寫的查詢VLOOKUP函數(shù)和HLOOKUP函數(shù)不區(qū)分四舍五入函數(shù):

ROUND、FLOOR和CEILING函數(shù)

ROUND函數(shù):返回某個(gè)數(shù)字按指定位數(shù)舍入后的數(shù)字。FLOOR函數(shù)：根據(jù)指定基數(shù)，將數(shù)字沿絕對(duì)值減小的方向向下舍入到最接近的倍數(shù)。CEILING函數(shù)：將參數(shù)Number