EXCEL在會計日常工作中的應(yīng)用

1、EXCEL在會計日常工作中的應(yīng)用一、數(shù)據(jù)有效性及函數(shù)的結(jié)合數(shù)據(jù)有效性下拉列表選擇實例：有效性二、單元格編輯選取使用定位選?。旖萱I：CTRL+G）選擇性粘貼粘貼數(shù)值（單擊鼠標右鍵-選擇性粘貼-數(shù)值）粘貼運算（單擊鼠標右鍵-選擇性粘貼-公式）查找（快捷鍵：CTRL+F）三、條件格式條件格式的設(shè)立、添加條件格式的設(shè)立、添加添加條件添加條件三、條件格式公式條件公式條件實例：實例：條件格式條件格式四、 分列1、啟動Excel2003（其他版本請仿照操作），打開相應(yīng)的工作表，在B列右側(cè)插入一個空白列，用于保存分列的結(jié)果。 2、選中B列，執(zhí)行“數(shù)據(jù)分列”命令，打開“文本分列向?qū)?3步驟之1”對話框（如圖2

2、），選中其中的“分隔符號-”選項（通常是默認選項）。四、 分列3、按“下一步”按鈕，進入“文本分列向?qū)?3步驟之2”對話框（如圖3）4、實例：分列四、 公式與函數(shù)5.13 IS類信息函數(shù)類信息函數(shù)IS類信息函數(shù)是一個具有相似特征函數(shù)的集合類信息函數(shù)是一個具有相似特征函數(shù)的集合,它們根據(jù)判它們根據(jù)判斷返回斷返回TRUE或或FALSE,常見的常見的IS函數(shù)有以下幾個函數(shù)有以下幾個:ISBLANK:判斷是否為空格判斷是否為空格,是則返回是則返回TRUE,否則返回否則返回FalseISERR:判斷值是否任意錯誤值判斷值是否任意錯誤值(除去除去#N/A)ISERROR:判斷值是否任意錯誤值判斷值是否任意

3、錯誤值(#N/A、#VALUE！、！、#REF！、！、#DIV/0！、！、#NUM！、！、#NAME？)ISLOGICAL：判斷值是否為邏輯值：判斷值是否為邏輯值ISNA：判斷值是否為錯誤值：判斷值是否為錯誤值#N/A （值不存在）（值不存在）ISNOTEXT：判斷值是否為任意不是文本的項：判斷值是否為任意不是文本的項ISNUMBER：判斷值是否為數(shù)字：判斷值是否為數(shù)字ISREF：判斷值是否為引用：判斷值是否為引用ISTEXT：判斷值是否文本：判斷值是否文本四、函數(shù)公式公式公式:=ISERROR(D2)判斷值是否為任意錯誤值（判斷值是否為任意錯誤值（#N/A，VALUE?。┧摹?公式與函數(shù)5.

4、11 文本函數(shù)文本函數(shù)5.11.1 字符串的查找和截取字符串的查找和截取功能功能:LEFT:取左邊的取左邊的N個字符個字符RIGHT:取右邊的取右邊的N個字符個字符MID:根據(jù)指定位置取指定位數(shù)字符根據(jù)指定位置取指定位數(shù)字符LEN:字符串的字符數(shù)字符串的字符數(shù)FIND:在指定字符串中查找指定字符的位置在指定字符串中查找指定字符的位置SEARCH:查找特定字符或文本串的位置查找特定字符或文本串的位置四、 公式與函數(shù)5.11 文本函數(shù)文本函數(shù)語法語法: LEFT(字符串或引用單元格字符串或引用單元格,N) RIGHT (字符串或引用單元格字符串或引用單元格,N) MID (字符串或引用單元格字符串

5、或引用單元格,開始截取位數(shù)開始截取位數(shù),長度長度) LEN (字符串或引用單元格字符串或引用單元格) FIND (查找的字符查找的字符,字符串或引用單元格字符串或引用單元格,開始查找位數(shù)開始查找位數(shù)) SEARCH (查找的字符查找的字符,字符串或引用單元格字符串或引用單元格,開始查找位數(shù)開始查找位數(shù))說明說明: FIND區(qū)分大小寫并且不允許使用通配符區(qū)分大小寫并且不允許使用通配符,而而SEARCH可以可以 例例:= LEFT(“ABCDEFG”,2) ？例例:= RIGHT(“ABCDEFG”,2) ？例例:= MID (“ABCDEFG”,2,3) ？例例:= LEN (“ABCDEFG”

6、,) ？例例:= FIND (“F”,“ABCDEFG”,1) ？例例:= SEARCH (“?f”,“ABCDEFG”,1) ？ABFGBCD765四、 公式與函數(shù)5.1 IF函數(shù)IF函數(shù)是工作中最常用函數(shù)之一，它可以根據(jù)設(shè)置的條件進行運算或返回值。語法：=IF（邏輯表達式，TRUE，F(xiàn)ALSE）邏輯表達式不成邏輯表達式不成立返回的值立返回的值邏輯表達式成邏輯表達式成立返回的值立返回的值返回值為返回值為TRUE或或FALSE的邏輯表的邏輯表達式達式例例:=IF(53,對對,不對不對) 例例:=IF(5B2,節(jié)約節(jié)約，超支超支）四、 公式與函數(shù)5.1.1 單條件和多條件判斷2.單條件判斷并運算

7、運算邏輯表達式不邏輯表達式不成立進行運算成立進行運算的表達式的表達式邏輯表達式成邏輯表達式成立時進行運算立時進行運算的表達式的表達式邏輯表達條件：邏輯表達條件：銷售額是否超過銷售額是否超過3萬元萬元C2=IF（B230000,B2*0.015,B2*0.01）四、 公式與函數(shù)5.1.1 單條件和多條件判斷3.單條件判斷返回引用區(qū)域引用區(qū)域邏輯表達式不邏輯表達式不成立返回的區(qū)成立返回的區(qū)域域邏輯表達式成邏輯表達式成立時返回的區(qū)立時返回的區(qū)域域邏輯表達條件：邏輯表達條件：A2是否等于銷售是否等于銷售一部一部=SUM(IF(A2=銷售一部銷售一部,B5:B9,E5:E9)四、 公式與函數(shù)5.1.1

8、單條件和多條件判斷4.多多條件判斷IF函數(shù)實例（P113）邏輯表達式不邏輯表達式不成立返回的表成立返回的表達式達式邏輯表達式成邏輯表達式成立時返回的值立時返回的值邏輯表達條件：邏輯表達條件：B2或或C2任一為任一為0是是否成立否成立=IF(OR(B2=0,C2=0), ,(C2-B2)/C2)四、 公式與函數(shù)5.1.1 單條件和多條件判斷4.多條件判斷IF函數(shù)實例（P113）=IF(B220000,B2*1%,IF(B225000,B2*2%,IF(B235000,B2*3%,B2*4%)=IF(B220000,B2*1%,IF(20000=B225000,B2*2%,IF(25000=B23

9、5000,B2*3%,B2*4%)常見的常見的錯誤錯誤常見的常見的錯誤錯誤四、 公式與函數(shù)5.1.1 單條件和多條件判斷4.多條件判斷=IF(B210000,B2*1%,0)+IF(AND(B2=10000),B2*2%,0)+IF(AND(B2=20000),B2*3%,0)+IF(AND(B2=30000),B2*4%,0)+IF(AND(B2=40000),B2*5%,0)+IF(AND(B2=50000),B2*6%,0)+IF(AND(B2=60000),B2*7%,0)+IF(AND(B2=70000),B2*8%,0)+IF(B280000,B2*9%,0)四、 公式與函數(shù)5.2

10、 SUM函數(shù)函數(shù)SUM函數(shù)是工作中最常用函數(shù)之一，幾乎所有的函數(shù)是工作中最常用函數(shù)之一，幾乎所有的表格中都有合并的運算。表格中都有合并的運算。語法：語法：=SUM（參數(shù)（參數(shù)1，參數(shù)，參數(shù)2，參數(shù)參數(shù)30）參數(shù)最多為參數(shù)最多為30個個參數(shù)可以為引用，數(shù)值，參數(shù)可以為引用，數(shù)值，文本，表達式和數(shù)組文本，表達式和數(shù)組例例:=SUM(5,3,2,1)例例:=(a1:b1)四、 公式與函數(shù)5.2.1 連續(xù)、不連續(xù)及交叉區(qū)域求和連續(xù)、不連續(xù)及交叉區(qū)域求和例例1：連續(xù)區(qū)域的求和：連續(xù)區(qū)域的求和 =SUM(A1:C5)例例1：不連續(xù)區(qū)域的求和：不連續(xù)區(qū)域的求和 =SUM(A1，B3，D22)例例1：交叉區(qū)域

11、的求和：交叉區(qū)域的求和 =SUM(1:3 C:C)注意：這注意：這兒有空格兒有空格四、 公式與函數(shù)5.2.2 多工作表自動匯總多工作表自動匯總是是SUM函數(shù)的三維應(yīng)用函數(shù)的三維應(yīng)用例：例： =SUM(1日日:空白空白!C5)單引號的作用是單引號的作用是去掉工作表名的去掉工作表名的空格空格工作表的名稱必工作表的名稱必須加感嘆號！須加感嘆號！四、 公式與函數(shù)5.1 SUMIF函數(shù)函數(shù)SUMIF函數(shù)是根據(jù)指定條件對若干單元格求和。函數(shù)是根據(jù)指定條件對若干單元格求和。語法：語法：=SUMIF（條件范圍，條件，求和范圍）（條件范圍，條件，求和范圍）需要求和的實際范需要求和的實際范圍，省略則對條件圍，省略

12、則對條件范圍求和范圍求和只能用單條件而不只能用單條件而不能用復(fù)合條件，可能用復(fù)合條件，可以使用通配符以使用通配符用于條件判斷的單用于條件判斷的單元格區(qū)域元格區(qū)域例例:=SUMIF(B2:B9,“副副教授教授”,D2:D9)例例:=SUMIF(D2:D9,“2000)四、 公式與函數(shù)5.1 SUMIF函數(shù)函數(shù)求和范圍求和范圍條件條件條件范圍條件范圍打開打開EXCEL四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題1:根據(jù)根據(jù)B列銷售金額求和列銷售金額求和,要求對銷售金額大要求對銷售金額大于于2000的數(shù)值求和的數(shù)值求和省略求和范圍省略求和范圍,對條件范圍進對條件范圍進行求和行求和條件條件

13、條件范圍條件范圍=SUMIF（B2:B9,“2000）四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題2:根據(jù)商品名稱求和根據(jù)商品名稱求和,要求對商品名稱為要求對商品名稱為A1的的銷售金額求和銷售金額求和求和范圍求和范圍條件條件條件范圍條件范圍=SUMIF（A2:A9,“A1“,B2:B9）四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題3:根據(jù)根據(jù)B列銷售金額求和列銷售金額求和,要求對銷售金額大要求對銷售金額大于于D2的數(shù)值求和的數(shù)值求和省略求和范圍省略求和范圍,對條件范圍進對條件范圍進行求和行求和條件條件條件范圍條件范圍=SUMIF（B2:B9,“&D2）四、 公式

14、與函數(shù)5.3.1 單條件求和單條件求和省略求和范圍省略求和范圍,對條件范圍進對條件范圍進行求和行求和條件條件條件范圍條件范圍=SUMIF(B2:B9,“&AVERAGE (B2:B9)四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題5:求商品名稱包含求商品名稱包含”A”的銷售金額之和的銷售金額之和求和范圍求和范圍條件條件條件范圍條件范圍=SUMIF（A2:A9,“A*“,B2:B9）四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題2:根據(jù)商品名稱求和根據(jù)商品名稱求和,要求對商品名稱為要求對商品名稱為A1的的銷售金額求和銷售金額求和求和范圍求和范圍條件條件條件范圍條件范圍

15、=SUMIF（A2:A9,“A1“,B2:B9）四、 公式與函數(shù)5.3.1 單條件求和單條件求和問題問題6:根據(jù)商品名稱求第四五個字符為根據(jù)商品名稱求第四五個字符為”A2”,且字且字符總長度為符總長度為6個字符的銷售金額求和個字符的銷售金額求和求和范圍求和范圍條件條件條件范圍條件范圍=SUMIF（A2:A9,“?A2?“,B2:B9）四、 公式與函數(shù)5.3.2 多條件及區(qū)間求和多條件及區(qū)間求和問題問題1:符合入庫數(shù)量大于符合入庫數(shù)量大于4小于小于10的商品的商品,對其入對其入庫數(shù)量求和庫數(shù)量求和=SUMIF (C2:C9,“4“)-SUMIF (C2:C9,“=10“)410四、 公式與函數(shù)5

16、.3.2 多條件及區(qū)間求和多條件及區(qū)間求和問題問題2:B列品名分別為列品名分別為”AA”,”BB”,”CC”的銷售數(shù)的銷售數(shù)量之和量之和=SUM(SUMIF (B2:B9 ,”AA”,”BB”,”CC”,C2:C9)四、 公式與函數(shù)5.3.2 多條件及區(qū)間求和多條件及區(qū)間求和問題問題3:對品名分別為對品名分別為”AA”的手機入庫數(shù)量進行求的手機入庫數(shù)量進行求和和=SUMIF (A2:A9 ,”AA手機手機”,D2:D9)注意要先添加一輔助列注意要先添加一輔助列四、 公式與函數(shù)5.3.3 不相鄰區(qū)域的求和不相鄰區(qū)域的求和SUMIF函數(shù)實例函數(shù)實例（P123）=SUMIF (A3:D11 ,”1”

17、,B3:E11)注意兩個區(qū)域的大小要一致注意兩個區(qū)域的大小要一致四、 公式與函數(shù)5.4 COUNTIF函數(shù)函數(shù)COUNTIF函數(shù)是根據(jù)指計算給定區(qū)域內(nèi)滿足特函數(shù)是根據(jù)指計算給定區(qū)域內(nèi)滿足特定條件單元格數(shù)目。定條件單元格數(shù)目。語法：語法：=COUNTIF（條件范圍，條件）（條件范圍，條件）可以為數(shù)字可以為數(shù)字,表達式表達式或文本或文本用于條件判斷的單用于條件判斷的單元格區(qū)域元格區(qū)域例例:=COUNTIF(B2:B9,“副教授副教授”)例例:=COUNTIF(D2:D9,“2000)四、 公式與函數(shù)5.4 COUNTIF函數(shù)函數(shù)條件條件條件范圍條件范圍打開打開EXCEL四、 公式與函數(shù)5.4.1

18、按條件計數(shù)按條件計數(shù)問題問題1:統(tǒng)計實發(fā)工資大于統(tǒng)計實發(fā)工資大于2500的人數(shù)的人數(shù)條件條件條件范圍條件范圍=COUNTIF（E2:E7,“2500）四、 公式與函數(shù)5.4.1 按條件計數(shù)按條件計數(shù)問題問題2:統(tǒng)計財務(wù)部的人數(shù)統(tǒng)計財務(wù)部的人數(shù)條件條件條件范圍條件范圍=COUNTIF（A2:A7,”財務(wù)部財務(wù)部”）四、 公式與函數(shù)5.4.2 COUNTIF計數(shù)常見的錯誤計數(shù)常見的錯誤1.區(qū)域選取的影響區(qū)域選取的影響=COUNTIF（B3:B8,C3:C8,”6”）=COUNTIF（B3:C8,”6”）四、 公式與函數(shù)5.4.2 COUNTIF計數(shù)常見的錯誤計數(shù)常見的錯誤2.數(shù)字格式的影響數(shù)字格式

19、的影響解決辦法解決辦法:把文本數(shù)字轉(zhuǎn)換成數(shù)值型把文本數(shù)字轉(zhuǎn)換成數(shù)值型四、 公式與函數(shù)5.4.2 COUNTIF計數(shù)常見的錯誤計數(shù)常見的錯誤3.長數(shù)字的影響長數(shù)字的影響解決辦法解決辦法:在長數(shù)字中添加在長數(shù)字中添加*號號四、 公式與函數(shù)5.5 SUMPRODUCT函數(shù)函數(shù)SUMPRODUCT函數(shù)是在給定的幾組數(shù)組中函數(shù)是在給定的幾組數(shù)組中,將將數(shù)組間對應(yīng)的元素相乘數(shù)組間對應(yīng)的元素相乘,并返回乘積之和。并返回乘積之和。語法：語法：= SUMPRODUCT （數(shù)組（數(shù)組1，數(shù)組，數(shù)組2,數(shù)組數(shù)組3，）數(shù)組參數(shù)必須具有相同的維數(shù)，否則函數(shù)數(shù)組參數(shù)必須具有相同的維數(shù)，否則函數(shù)SUMPRODUCT將返回錯

20、誤值：將返回錯誤值：“#VALUE！”例例:= SUMPRODUCT(1,2,3,4,5)=?=1*2*3*4*5=120四、 公式與函數(shù)5.5 SUMPRODUCT函數(shù)函數(shù)數(shù)組數(shù)組2數(shù)組數(shù)組1打開打開EXCEL數(shù)組數(shù)組3四、 公式與函數(shù)5.5.1庫存金額的簡便運算庫存金額的簡便運算不用設(shè)置金額列不用設(shè)置金額列,直接計算出總?cè)霂旖痤~直接計算出總?cè)霂旖痤~數(shù)組數(shù)組2數(shù)組數(shù)組1= SUMPRODUCT (B2:B9,C2:C9)數(shù)組參數(shù)必須具有相同的維數(shù)數(shù)組參數(shù)必須具有相同的維數(shù)四、 公式與函數(shù)5.5.2 多條件計數(shù)和求和多條件計數(shù)和求和1.多條件同時成立多條件同時成立計數(shù)計數(shù): SUMPRODUC

21、T(條件條件1)*(條件條件2)*(條件條件3)*(條件條件n)求和求和: SUMPRODUCT(條件條件1)*(條件條件2)*(條件條件3)*(條件條件n)*(要統(tǒng)計的數(shù)據(jù)區(qū)域要統(tǒng)計的數(shù)據(jù)區(qū)域)2.任一條件成立任一條件成立計數(shù)計數(shù): SUMPRODUCT(條件條件1)+(條件條件2)+(條件條件3)+(條件條件n)求和求和: SUMPRODUCT(條件條件1)+(條件條件2)*(條件條件3)+(條件條件n)*(要統(tǒng)計的數(shù)據(jù)區(qū)域要統(tǒng)計的數(shù)據(jù)區(qū)域)四、 公式與函數(shù)多條件計數(shù)和求和多條件計數(shù)和求和例例5-17 在入庫明細匯總表中在入庫明細匯總表中,根據(jù)要求計算根據(jù)要求計算問題問題1:計算供應(yīng)商計算

22、供應(yīng)商A1的冰箱入庫類型的品種數(shù)的冰箱入庫類型的品種數(shù).條件條件2:類別為冰箱類別為冰箱條件條件1:供應(yīng)商的名字為供應(yīng)商的名字為A1= SUMPRODUCT (B3:B11=“A1”)*(C3:C11=“冰箱冰箱”)四、 公式與函數(shù)多條件計數(shù)和求和多條件計數(shù)和求和例例5-17 在入庫明細匯總表中在入庫明細匯總表中,根據(jù)要求計算根據(jù)要求計算問題問題2:計算供應(yīng)商計算供應(yīng)商A3的洗衣機入庫數(shù)量的洗衣機入庫數(shù)量.條件條件3:類別為洗衣機類別為洗衣機條件條件1:供應(yīng)商供應(yīng)商的名字為的名字為A3= SUMPRODUCT (B3:B11=“A3”),(C3:C11=“洗衣機洗衣機”)*E3:E11)統(tǒng)計數(shù)

23、據(jù)統(tǒng)計數(shù)據(jù):入庫數(shù)量入庫數(shù)量四、 公式與函數(shù)5.6 VLOOKUP函數(shù)函數(shù)VLOOKUP函數(shù)是在表格或數(shù)值組的首列查找指函數(shù)是在表格或數(shù)值組的首列查找指定的數(shù)值定的數(shù)值,并由此返回表格或數(shù)組當前行中指定列并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。它是最常用的函數(shù)之一處的數(shù)值。它是最常用的函數(shù)之一功能：功能：1.指定位置查找和引用數(shù)據(jù)指定位置查找和引用數(shù)據(jù)2.表與表的核對表與表的核對3.利用模糊運算進行區(qū)間查詢利用模糊運算進行區(qū)間查詢四、 公式與函數(shù)5.6 VLOOKUP函數(shù)函數(shù)語法語法:=VLOOKUP(查找目標查找目標,查找區(qū)域查找區(qū)域,相對列數(shù)相對列數(shù),TRUE或或FALSE)要找的內(nèi)要

24、找的內(nèi)容在查找容在查找區(qū)域中的區(qū)域中的哪一列哪一列?在哪兒查找在哪兒查找?注注意意:查找內(nèi)容必須查找內(nèi)容必須在查找區(qū)域的第在查找區(qū)域的第一列一列!要查找的要查找的內(nèi)容內(nèi)容例例:=VLOOKUP(B2,$D$2:$H$9,3,0)TRUE:模糊查模糊查找找,FALSE:精確精確查找查找,可以用其可以用其1和和0代替代替四、 公式與函數(shù)5.6 VLOOKUP函數(shù)函數(shù)相對列數(shù)相對列數(shù)查找區(qū)域查找區(qū)域查找目標查找目標打開打開EXCEL精確查找或精確查找或模糊查找模糊查找四、 公式與函數(shù)5.6.1 單個區(qū)域查找單個區(qū)域查找問題問題1:要求在要求在C列列,從員工信息表中根據(jù)姓名查找從員工信息表中根據(jù)姓名查

25、找其級別其級別.=VLOOKUP(B2,$G$9:$H$14,2,0)要找的內(nèi)要找的內(nèi)容在查找容在查找區(qū)域中的區(qū)域中的第第2列列在哪兒查找在哪兒查找?員工信息表員工信息表,注意注意絕對引用的使用絕對引用的使用!要查找的要查找的內(nèi)容內(nèi)容:張張三三精確查找精確查找,可以可以用用0代替代替四、 公式與函數(shù)5.6.1 單個區(qū)域查找單個區(qū)域查找問題問題2:要求在要求在D,E列列,分別根據(jù)工資級別和姓名分別根據(jù)工資級別和姓名,從從基本工資表和提成表查找相應(yīng)的數(shù)值基本工資表和提成表查找相應(yīng)的數(shù)值.=VLOOKUP(C2,$G$2:$H$7,2,0)要找的內(nèi)要找的內(nèi)容在查找容在查找區(qū)域中的區(qū)域中的第第2列列在

26、哪兒查找在哪兒查找?基本工資表基本工資表,注意注意絕對引用的使用絕對引用的使用!要查找的要查找的內(nèi)容內(nèi)容:工工資級別資級別精確查找精確查找,可以可以用用0代替代替四、 公式與函數(shù)5.6.2 多個區(qū)域查找多個區(qū)域查找利用以前所學的函數(shù)利用以前所學的函數(shù)=VLOOKUP(A2,IF(C2=公司公司1,$F$3:$G$6,$F$10:$G$13),2,0)要找的內(nèi)要找的內(nèi)容在查找容在查找區(qū)域中的區(qū)域中的第第2列列在哪兒查找在哪兒查找?現(xiàn)在有兩個表現(xiàn)在有兩個表,需要判斷需要判斷的時候就要想到的時候就要想到IF函數(shù)函數(shù)要查找的要查找的內(nèi)容內(nèi)容:姓姓名名精確查找精確查找,可以用可以用0代替代替四、 公式與

27、函數(shù)5.6.3 模糊查找計算個人所得稅模糊查找計算個人所得稅以前學過以前學過IF函數(shù)條件判斷后再求值函數(shù)條件判斷后再求值,但嵌套太多但嵌套太多,容容易出錯易出錯,這里運用這里運用VLOOKUP函數(shù)函數(shù)來解決這個問題來解決這個問題=C2*要找的內(nèi)要找的內(nèi)容在查找容在查找區(qū)域中的區(qū)域中的第第3列列在哪在哪兒查兒查找找?要查找要查找的內(nèi)容的內(nèi)容:應(yīng)稅所應(yīng)稅所得得模糊查找模糊查找,可以省略可以省略應(yīng)稅應(yīng)稅所得所得VLOOKUP(C2,$G$2:$I$10,3)-VLOOKUP(C2,$G$2:$J$10,4)四、 公式與函數(shù)5.6.4 處理查找出現(xiàn)的錯誤處理查找出現(xiàn)的錯誤在利用在利用VLOOKUP函數(shù)

28、查找時函數(shù)查找時,常遇到下列幾種常遇到下列幾種查詢錯誤查詢錯誤:參數(shù)設(shè)置錯誤參數(shù)設(shè)置錯誤空格及不可見字符引起的錯誤空格及不可見字符引起的錯誤格式不一致引起的錯誤格式不一致引起的錯誤四、 公式與函數(shù)1.參數(shù)設(shè)置錯誤參數(shù)設(shè)置錯誤錯誤原因錯誤原因:選取選取查詢區(qū)域錯誤查詢區(qū)域錯誤錯誤原因錯誤原因:省略參數(shù)是省略參數(shù)是模糊查找模糊查找公式公式1:=VLOOKUP(B10,A1:E5,3,0)公式公式2:=VLOOKUP(B11,B2:C5,3,0)錯誤原因錯誤原因:選選取查詢區(qū)域取查詢區(qū)域錯誤錯誤公式公式3:=VLOOKUP(B12,B2:E5,3)四、 公式與函數(shù)2.空格及不可見字符引起的錯誤空格及

29、不可見字符引起的錯誤解決方法解決方法:替換替換不可見字符不可見字符解決方法解決方法:轉(zhuǎn)換格式轉(zhuǎn)換格式(1)空格引起的錯誤空格引起的錯誤解決方法解決方法:替替換空格換空格(2)不可見字符引起的錯誤不可見字符引起的錯誤3.數(shù)字格式不一致引起的錯誤數(shù)字格式不一致引起的錯誤四、 公式與函數(shù)5.8 其他數(shù)學函數(shù)其他數(shù)學函數(shù)本節(jié)介紹的本節(jié)介紹的ROUND和和MOD函數(shù)用法簡單函數(shù)用法簡單,但用途卻但用途卻極為廣泛極為廣泛.5.8.1 用用ROUND函數(shù)處理工資表的計算誤差函數(shù)處理工資表的計算誤差語法語法:=ROUND(數(shù)字數(shù)字,指定的位數(shù)指定的位數(shù))如果指定的位數(shù)如果指定的位數(shù)大于大于0,則舍入到指定的小

30、數(shù)位則舍入到指定的小數(shù)位;如果指定的位數(shù)如果指定的位數(shù)等于等于0,則舍入到最接近的整數(shù)則舍入到最接近的整數(shù);如果指定的位數(shù)如果指定的位數(shù)小于小于0,則在小數(shù)舍入則在小數(shù)舍入;例例:=ROUND(25.265,2)=25.27例例:=ROUND(25.265,0)=25例例:=ROUND(25.265,-1)=30四、 公式與函數(shù)5.8.1 ROUND函數(shù)函數(shù)指定的位數(shù)指定的位數(shù)數(shù)字數(shù)字打開打開EXCEL四、 公式與函數(shù)5.8.1用用ROUND函數(shù)處理工資表的計算誤差函數(shù)處理工資表的計算誤差=ROUND(G4,2)通過設(shè)置小數(shù)點位數(shù)通過設(shè)置小數(shù)點位數(shù),只是顯示上保留只是顯示上保留兩位小數(shù)兩位小數(shù)

31、,實質(zhì)上單元內(nèi)部的小數(shù)位數(shù)實質(zhì)上單元內(nèi)部的小數(shù)位數(shù)并沒有改變并沒有改變,而用而用ROUND函數(shù)是實質(zhì)上函數(shù)是實質(zhì)上把多余的位數(shù)舍掉了把多余的位數(shù)舍掉了,而不僅僅是在顯而不僅僅是在顯示上。示上。四、 公式與函數(shù)5.9 其他統(tǒng)計函數(shù)其他統(tǒng)計函數(shù)統(tǒng)計函數(shù)是工作中常用的函數(shù)統(tǒng)計函數(shù)是工作中常用的函數(shù),以前我們學過以前我們學過SUM,COUNT等函數(shù)。本節(jié)將介紹其他幾個統(tǒng)計函等函數(shù)。本節(jié)將介紹其他幾個統(tǒng)計函數(shù)。數(shù)。5.9.1 用用COUNTA函數(shù)自動統(tǒng)計工資表人數(shù)函數(shù)自動統(tǒng)計工資表人數(shù)功能功能:返回參數(shù)組中非空值的數(shù)目。返回參數(shù)組中非空值的數(shù)目。語法語法:=COUNTA(參數(shù)參數(shù)1,參數(shù)參數(shù)2,參數(shù)參數(shù)

32、3參數(shù)參數(shù)N)N最大值為最大值為30；可以進行多工作表的三維引用；可以進行多工作表的三維引用注意和注意和COUNT函數(shù)的對比！函數(shù)的對比！COUNT函數(shù)只統(tǒng)計數(shù)值型數(shù)據(jù)函數(shù)只統(tǒng)計數(shù)值型數(shù)據(jù)例例:=COUNTA(A1:B67)四、 公式與函數(shù)5.9.1 COUNTA函數(shù)函數(shù)參數(shù)參數(shù)2參數(shù)參數(shù)1打開打開EXCEL四、 公式與函數(shù)5.9.1用用COUNTA函數(shù)自動統(tǒng)計工資表人數(shù)函數(shù)自動統(tǒng)計工資表人數(shù)=COUNTA(B2:B5)不便于插入行不便于插入行=COUNTA(INDIRECT(B2:B&ROW()-1)利用利用INDIRECT函數(shù)把字符串轉(zhuǎn)換為引用，函數(shù)把字符串轉(zhuǎn)換為引用，利用利用RO

33、W函數(shù)把當前行數(shù)減去函數(shù)把當前行數(shù)減去1，得出上一，得出上一行的行數(shù)。行的行數(shù)。四、 公式與函數(shù)5.9.2用用MAX函數(shù)設(shè)置變動序號函數(shù)設(shè)置變動序號MAX和和MIN函數(shù)是分別求最大值和最小值的函數(shù)，函數(shù)是分別求最大值和最小值的函數(shù)，它們常在復(fù)雜的數(shù)組公式中出現(xiàn)。它們常在復(fù)雜的數(shù)組公式中出現(xiàn)。功能：功能：MAX求一組數(shù)中的最大值；求一組數(shù)中的最大值；MIN求一組數(shù)中求一組數(shù)中的最小值的最小值語法：語法：=MAX(數(shù)值數(shù)值1,數(shù)值數(shù)值2,) =MIN(數(shù)值數(shù)值1,數(shù)值數(shù)值2,)MAX和和MIN函數(shù)實例函數(shù)實例（P141）四、 公式與函數(shù)MAX和和MIN函數(shù)實例函數(shù)實例（P141）設(shè)置序號，要求：設(shè)

34、置序號，要求：序號隨行的刪除可自動調(diào)整為新的連續(xù)序號序號隨行的刪除可自動調(diào)整為新的連續(xù)序號在小計行、合計行和空行前不加序號在小計行、合計行和空行前不加序號=IF(OR(B6=,B6=小計小計,B6=合計合計),MAX($A$1:A5)+1)注意絕對引注意絕對引用和相對引用和相對引用的使用用的使用四、 公式與函數(shù)5.10 其他查找引用函數(shù)其他查找引用函數(shù)如果公式在如果公式在A10單元格單元格例例:=ROW()=？例例:=ROW(C25)=？例例:=COLUMN()=？例例:= COLUMN(C25)=？102513四、 公式與函數(shù)5.10 其他查找引用函數(shù)其他查找引用函數(shù)C14=VLOOKUP(

35、$B14,$B$2:$F$9,2,0)C15=VLOOKUP($B15,$B$2:$F$9,COLUMN(B1),0)這兩個公式的區(qū)別在于相對列數(shù)的不同。第一個公式直接這兩個公式的區(qū)別在于相對列數(shù)的不同。第一個公式直接用數(shù)字用數(shù)字2，后面的公式必須要手工來改相對列數(shù)，這樣不，后面的公式必須要手工來改相對列數(shù)，這樣不便于公式的復(fù)制；第二個公式用的是便于公式的復(fù)制；第二個公式用的是COLUMN(B1),這樣這樣隨著公式隨著公式向右向右的拖動，的拖動，列列發(fā)生改變，函數(shù)返回的值也隨之發(fā)生改變，函數(shù)返回的值也隨之改變，就可以實現(xiàn)公式的輕松復(fù)制。改變，就可以實現(xiàn)公式的輕松復(fù)制。四、 公式與函數(shù)5.10

36、其他查找引用函數(shù)其他查找引用函數(shù)5.10.2 MATCH和和INDEX實現(xiàn)雙向查找實現(xiàn)雙向查找MATCH是查詢函數(shù)是查詢函數(shù),INDEX是引用函數(shù)，在實際查找是引用函數(shù)，在實際查找并返回值過程中，并返回值過程中，MATCH和和INDEX總是結(jié)對出現(xiàn)在總是結(jié)對出現(xiàn)在公式中。公式中。功能：功能：MATCH返回在指定方式下與指定數(shù)值匹配的數(shù)組中返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置元素的相應(yīng)位置INDEX返回表格、區(qū)域中的數(shù)值或數(shù)值的引用。返回表格、區(qū)域中的數(shù)值或數(shù)值的引用。四、 公式與函數(shù)5.10 其他查找引用函數(shù)其他查找引用函數(shù)5.10.2 MATCH和和INDEX實現(xiàn)雙向查找實現(xiàn)

37、雙向查找語法：語法：=MATCH(查找的值查找的值,查找區(qū)域查找區(qū)域,查找類型查找類型)查找的類型為三種：查找的類型為三種：-1，0，1如果為如果為1,查找小于或等于查找值的最大數(shù)值；查找小于或等于查找值的最大數(shù)值；如果為如果為0,查找等于查找值的第一個數(shù)值；查找等于查找值的第一個數(shù)值；如果為如果為-1,查找大于或等于查找值的最小數(shù)值；查找大于或等于查找值的最小數(shù)值；如果省略，則默認為如果省略，則默認為1在哪兒查找在哪兒查找?要查找的內(nèi)容要查找的內(nèi)容四、 公式與函數(shù)5.10 其他查找引用函數(shù)其他查找引用函數(shù)5.10.2 MATCH和和INDEX實現(xiàn)雙向查找實現(xiàn)雙向查找語法：語法：=INDEX(

38、區(qū)域區(qū)域,行數(shù)行數(shù),列數(shù)列數(shù))行數(shù)和列數(shù)：是指相對于該區(qū)域的行行數(shù)和列數(shù)：是指相對于該區(qū)域的行數(shù)和列數(shù)，而并非相對整個工作表的數(shù)和列數(shù)，而并非相對整個工作表的行數(shù)和列數(shù)。行數(shù)和列數(shù)。為單元格為單元格 區(qū)域區(qū)域或數(shù)組常數(shù)或數(shù)組常數(shù)=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0)四、 公式與函數(shù)行數(shù)行數(shù)為單元格為單元格 區(qū)域區(qū)域或數(shù)組常數(shù)或數(shù)組常數(shù)=INDEX($A$1:$H$6,MATCH(A11,$A$1:$A$6,0),MATCH(B11,$A$1:$H$1,0)列數(shù)列數(shù)查找的類型為查找的類型為0,查找等于查找值的第

39、一個數(shù)值，查找等于查找值的第一個數(shù)值，第一個結(jié)果是第一個結(jié)果是2，表示行數(shù)為，表示行數(shù)為2；第二個結(jié)果為第二個結(jié)果為4，表示列數(shù)為，表示列數(shù)為4在哪兒查找在哪兒查找?要查找的內(nèi)容：要查找的內(nèi)容：部門、部門、3月費用計月費用計劃劃例例:= SUBSTITUTE(A2,”國稅局國稅局”,”國家稅務(wù)國家稅務(wù)局局”)例例:= REPLACE(A2,4,3,”國家稅務(wù)局國家稅務(wù)局”)四、 公式與函數(shù)5.11 文本函數(shù)文本函數(shù)5.11.2 SUBSTITUTE和和REPLACE實現(xiàn)指定內(nèi)容替換實現(xiàn)指定內(nèi)容替換語法：語法：= SUBSTITUTE (字符串或引用單元格字符串或引用單元格,要替換的文本要替換的

40、文本,要替換舊文本的新文本要替換舊文本的新文本,替換掉第替換掉第N個舊的文本個舊的文本)語法：語法：= REPLACE (字符串或引用單元格字符串或引用單元格,要替換起始位置要替換起始位置,要要替換原文本的字符個數(shù)替換原文本的字符個數(shù),新的文本新的文本)公式中的公式中的N如果省略如果省略,默認替換全部的舊文本字符默認替換全部的舊文本字符四、 公式與函數(shù)5.12 時間函數(shù)時間函數(shù)5.12.1 用用TODAY和和NOW計算和更新庫齡計算和更新庫齡本小節(jié)介紹的是能分別顯示當前日期函數(shù)本小節(jié)介紹的是能分別顯示當前日期函數(shù)TODAY和和當前時間函數(shù)當前時間函數(shù)NOW,它們的特點可以隨著日期和時它們的特點可以隨著日期和時間的變化而變化間的變化而變化,但這種更新是在工作簿打開或工作但這種更新是在工作簿打開或工作表重新計算的前提下表重新計算的前提下.在下面幾中常見情況下在下面幾中常見情況下,工作工作表會重新計算表會重新計算.(1)雙擊工作表中的某個單元格雙擊工作表中的某個單元格(2)編輯任一個單元格內(nèi)容編輯任一個單元格內(nèi)容(3)按按F9鍵鍵四、 公式與函數(shù)5.12 時間函數(shù)時間函數(shù)功能功能:TODAY:顯示當前日期顯示當前日期NOW:顯示當前時間顯示當前時間語法語法:TODAY()NOW(