Excel高級培訓(xùn)課程22

1、Excel高級培訓(xùn)課程22第二講：公 式 與 函 數(shù) 一、基本常識：一、基本常識： 1、什么是公式： 在在Excel中公式是一系列表達式的組合，其中包中公式是一系列表達式的組合，其中包 括函數(shù)，運算符和單元格區(qū)域。用到公式時必須括函數(shù)，運算符和單元格區(qū)域。用到公式時必須 先輸入標志符先輸入標志符=、+，其中最簡單的公式就是復(fù)，其中最簡單的公式就是復(fù) 制單元格（如制單元格（如=A8然后按回車）然后按回車） 2、什么是函數(shù)： 最通俗的理解就是：最通俗的理解就是：Excel軟件內(nèi)部自帶的一些現(xiàn)軟件內(nèi)部自帶的一些現(xiàn) 成的特殊公式，是為了完成某項任務(wù)而編寫的一套成的特殊公式，是為了完成某項任務(wù)而編寫的一

2、套 內(nèi)部程序，需要是可直接拿來使用。他通過一些叫內(nèi)部程序，需要是可直接拿來使用。他通過一些叫 做參數(shù)的設(shè)置可完成一些手工操作無法完成的工作。做參數(shù)的設(shè)置可完成一些手工操作無法完成的工作。3、Excel中的計算： 無論是公式還是函數(shù)，他們都是為了完成某一項任務(wù)而服務(wù)，因此，他們在使用時，有其共性，一個是都帶有=號，二是屬于計算。在Excel中，常用的有以下幾種計算方式，分別是： （1）算術(shù)運算：+、-、*、/（）、%、(方) （2）文本連接運算：&（將多個單元格的內(nèi)容連在一起 放到一個單元格中，文本，數(shù)字，符號都可以。） （3）比較或邏輯運算：、=、=（大于或等于） =（小于或等于）、（

3、不等 于）第二講：公 式 與 函 數(shù) 第二講：公 式 與 函 數(shù)（4）綜合運算：手動計算與函數(shù)綜合，運算方式 綜合，函數(shù)與函數(shù)綜合（嵌套函數(shù)）4、Excel中函數(shù)的限制： （1）參數(shù)個數(shù)：2003版30個，2007版255個。 （2）公式長度：2003版1024個字符，2007版以上 8192個字符。 （3）數(shù)字計算精度：15位第二講：公 式 與 函 數(shù)4、Excel中改變運算優(yōu)先級： 在先要計算的公式中加上（）：如=（2+6）*（5-1）-10 結(jié)果為結(jié)果為2222二、調(diào)用函數(shù)的方法：二、調(diào)用函數(shù)的方法： （1）單擊編輯欄中）單擊編輯欄中Fx（插入函數(shù)）按鈕（插入函數(shù)）按鈕 （2）快捷按鍵：

4、）快捷按鍵：shift+F3 (3) 2007版公式選項卡，版公式選項卡，F(xiàn)x插入函數(shù)按鈕。插入函數(shù)按鈕。第二講：公 式 與 函 數(shù)三、修改公式：三、修改公式： 1、更改計算方式：如將+改為*等。 方法是：選中有內(nèi)容公式的單元格在編輯欄中選中計算符輸入新的計算符即可。 2、更改單元格：如將B5改為F30。 3、更改單元格區(qū)域方式：如將A1：H50改為A1，E3，F(xiàn)48等 其中，英文下的逗號表示和的關(guān)系，幾個單元格相加的和。 冒號表示到的關(guān)系，即從某個單元格到某個單元格。是一個 矩形區(qū)域。 4、更改單元格引用方式： （1）相對引用：計算結(jié)果同行數(shù)值相對應(yīng)。 （2）絕對引用：將某一個單元格或區(qū)域鎖

5、定。計算時希望公式引用 的單元格不被更改。 第二講：公 式 與 函 數(shù)（3 3）混合引用，即相對引用和絕對引用搭配使用。其中，包括行）混合引用，即相對引用和絕對引用搭配使用。其中，包括行 相對，列相對，行絕對，列絕對和完全絕對。相對，列相對，行絕對，列絕對和完全絕對。 轉(zhuǎn)換的方法為，在輸入公式時，將光標定位到編輯欄引用的轉(zhuǎn)換的方法為，在輸入公式時，將光標定位到編輯欄引用的 單元格中，按單元格中，按F4F4鍵（可以在各個引用方式間切換）。鍵（可以在各個引用方式間切換）。5 5、更改函數(shù)：如本來是求和計算，現(xiàn)在根據(jù)需要要變成統(tǒng)計計算。、更改函數(shù)：如本來是求和計算，現(xiàn)在根據(jù)需要要變成統(tǒng)計計算。 方法

6、：選中已計算好的單元格方法：選中已計算好的單元格光標定位在編輯欄中光標定位在編輯欄中選選 中函數(shù)名如中函數(shù)名如SUMSUM輸入新的函數(shù)名如輸入新的函數(shù)名如COUNTIFCOUNTIF。第二講：公 式 與 函 數(shù)四、常用的函數(shù)演示：四、常用的函數(shù)演示： （一）數(shù)學(xué)與三角函數(shù)：（一）數(shù)學(xué)與三角函數(shù)： 1、INT函數(shù)：將數(shù)值取整到整數(shù)（小數(shù)），只有一個參數(shù)。函數(shù)：將數(shù)值取整到整數(shù)（小數(shù)），只有一個參數(shù)。 選取數(shù)字所在單元格選取數(shù)字所在單元格回車使用格式：回車使用格式：INT(number)=int(A1)參數(shù)說明：參數(shù)說明：number表示需要取整的數(shù)值或包含數(shù)值的引用單元格。表示需要取整的數(shù)值或包

7、含數(shù)值的引用單元格。 2、ROUND四舍五入函數(shù)：兩個參數(shù)。參數(shù)四舍五入函數(shù)：兩個參數(shù)。參數(shù)1，選中數(shù)值所在，選中數(shù)值所在單元格；參數(shù)單元格；參數(shù)2，輸入，輸入 舍去的位數(shù)。舍去的位數(shù)。=ROUND(a1,1) 3、TRUNC截取數(shù)值函數(shù)（一般指的是小數(shù)），有截取數(shù)值函數(shù)（一般指的是小數(shù)），有2個參數(shù)。個參數(shù)。參數(shù)參數(shù)1，選中數(shù)值所在單元格；參數(shù)，選中數(shù)值所在單元格；參數(shù)2，輸入，輸入 截取的位數(shù)，如輸入截取的位數(shù)，如輸入0為為整數(shù)。整數(shù)。TRUNC(number,num_digits)參數(shù)說明：參數(shù)說明：number：表示需要截尾取整的數(shù)字。：表示需要截尾取整的數(shù)字。num_digits：表

8、示取整精度的數(shù)：表示取整精度的數(shù)字，默認值為字，默認值為0。 4、MOD返回兩數(shù)的余數(shù)返回兩數(shù)的余數(shù) ：有：有2個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選被除數(shù)數(shù)值所在單元格；參數(shù)，選被除數(shù)數(shù)值所在單元格；參數(shù)2，選除數(shù)所在單元格，選除數(shù)所在單元格。使用格式：。使用格式：MOD(number,divisor)參數(shù)說明：參數(shù)說明：number代表被除數(shù)；代表被除數(shù)；divisor代表除數(shù)。輸入公式：代表除數(shù)。輸入公式：=MOD(13,4)， 第二講：公 式 與 函 數(shù)5、SUMIF單條件求和函數(shù)單條件求和函數(shù) ：有：有3個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選第一條件所在單元格區(qū)域，如計算四川省銷售總額，選第一條

9、件所在單元格區(qū)域，如計算四川省銷售總額 選四川所在單元格區(qū)域；參數(shù)選四川所在單元格區(qū)域；參數(shù)2，選第一條件，如四川；，選第一條件，如四川；參數(shù)參數(shù)3，選求和所在單元格區(qū)域，如銷售額數(shù)值所在區(qū)域。使用格式，選求和所在單元格區(qū)域，如銷售額數(shù)值所在區(qū)域。使用格式：SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：參數(shù)說明：Range代表條件判斷的單元格區(qū)域；代表條件判斷的單元格區(qū)域；Criteria為指定條為指定條件表達式；件表達式；Sum_Range代表需要計算的數(shù)值所在的單元格區(qū)域代表需要計算的數(shù)值所在的單元格區(qū)域。輸入公式：輸入公式：=SUMIF(C2:C63,男男,D2

10、:D63)6、SUMIFS多條件求和函數(shù)：有多條件求和函數(shù)：有255個參數(shù)，但常用的有個參數(shù)，但常用的有5個等。個等。 如本節(jié)案例企業(yè)員工工資本月實發(fā)表格，求市場如本節(jié)案例企業(yè)員工工資本月實發(fā)表格，求市場1部甘部甘 倩琦銷售總額：倩琦銷售總額： 調(diào)用調(diào)用sumifs函數(shù)后，參數(shù)函數(shù)后，參數(shù)1，選求和數(shù)值所在區(qū)域；參數(shù)，選求和數(shù)值所在區(qū)域；參數(shù)2, 第第1條件所在區(qū)域（即所屬部門單元格區(qū)域）；參數(shù)條件所在區(qū)域（即所屬部門單元格區(qū)域）；參數(shù)3，選第，選第1 條件所在單元格（即任意市場部條件所在單元格（即任意市場部1單元格）；第單元格）；第4參數(shù)，第參數(shù)，第2條條 件所在區(qū)域（即銷售員單元格區(qū)域）；

11、第件所在區(qū)域（即銷售員單元格區(qū)域）；第5參數(shù)，選第參數(shù)，選第2條件條件 所在單元格（即甘倩琦）。所在單元格（即甘倩琦）。第二講：公 式 與 函 數(shù) （二）統(tǒng)計函數(shù)：（二）統(tǒng)計函數(shù)： 1、counta：統(tǒng)計選中的區(qū)域中非空單元格數(shù)目，：統(tǒng)計選中的區(qū)域中非空單元格數(shù)目，1個參數(shù)。個參數(shù)。 選中要統(tǒng)計的單元格區(qū)域。輸入公式選中要統(tǒng)計的單元格區(qū)域。輸入公式=COUNTA(A1:A7) 2、countif：單條件統(tǒng)計函數(shù)，有：單條件統(tǒng)計函數(shù)，有2個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選條件所在單元格區(qū)域；參數(shù)，選條件所在單元格區(qū)域；參數(shù)2，選條件所在任意單元格或輸，選條件所在任意單元格或輸入條件。使用格式：入條

12、件。使用格式：COUNTIF(Range,Criteria) 參數(shù)說明：參數(shù)說明：Range代表要統(tǒng)計的單元格區(qū)域；代表要統(tǒng)計的單元格區(qū)域；Criteria表示指定的條件表示指定的條件表達式。輸入公式：表達式。輸入公式：=COUNTIF(B1:B13,=80) 3、countifs：多條件統(tǒng)計函數(shù)，有：多條件統(tǒng)計函數(shù)，有255個參數(shù)，但常用的有個參數(shù)，但常用的有 6個，本小結(jié)案例產(chǎn)品銷售統(tǒng)計表個，本小結(jié)案例產(chǎn)品銷售統(tǒng)計表 ，求市場，求市場1部部 銷售額大于銷售額大于4000的甘倩琦有幾個？的甘倩琦有幾個？ 參數(shù)參數(shù)1，選第，選第1條件單元格所在區(qū)域如條件單元格所在區(qū)域如E3：E56；參數(shù)；參數(shù)

13、2,第第 1條件所在單元格即市場部條件所在單元格即市場部1；參數(shù)；參數(shù)4，第，第2條件單元格所在區(qū)域條件單元格所在區(qū)域 如如F3：F56；參數(shù)；參數(shù)4，選任意條件所在單元格或輸入條，選任意條件所在單元格或輸入條4000 參數(shù)參數(shù)5，第，第3條件單元格所在區(qū)域即條件單元格所在區(qū)域即D3:D56；參數(shù)；參數(shù)6，第，第3條件所在任意單元格條件所在任意單元格即甘倩琦。即甘倩琦。語法語法COUNTIFS(range1, criteria1,range2, criteria2)第二講：公 式 與 函 數(shù)4、rank：統(tǒng)計排名函數(shù)，有：統(tǒng)計排名函數(shù)，有3個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選要排名的函數(shù)區(qū)域開始所

14、在單元格；參數(shù)，選要排名的函數(shù)區(qū)域開始所在單元格；參數(shù)2，選排名數(shù)值所單，選排名數(shù)值所單元格區(qū)域（一般為絕對引用）；參數(shù)元格區(qū)域（一般為絕對引用）；參數(shù)3，升序還是降序，一般為升序，升序還是降序，一般為升序，所以該參數(shù)省略不輸。，所以該參數(shù)省略不輸。使用格式：使用格式：RANK（Number,ref,ord）參數(shù)說明）參數(shù)說明：Number代表需要排序的數(shù)值；代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；代表排序數(shù)值所處的單元格區(qū)域；order代表代表排序方式參數(shù)輸入公式：排序方式參數(shù)輸入公式：=RANK(B2,$B$2:$B$31,0)（三）文本函數(shù)：（三）文本函數(shù)： 1、LEN：

15、統(tǒng)計字符串函數(shù)，有：統(tǒng)計字符串函數(shù)，有1個參數(shù)選中要統(tǒng)計字符的單元格。個參數(shù)選中要統(tǒng)計字符的單元格。主要功能：統(tǒng)計文本字符串中字符數(shù)目。主要功能：統(tǒng)計文本字符串中字符數(shù)目。使用格式：使用格式：LEN(text) 參數(shù)說明參數(shù)說明：text表示要統(tǒng)計的文本字符串。輸入公式：表示要統(tǒng)計的文本字符串。輸入公式：=LEN(A40) 2、LEFT：從左邊開始提取字符串函數(shù)，有：從左邊開始提取字符串函數(shù)，有2個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選中提取字符串的單元格；參數(shù)，選中提取字符串的單元格；參數(shù)2，輸入提取的個數(shù)。，輸入提取的個數(shù)。使用格式：使用格式：LEFT(text,num_chars)參數(shù)說明：參數(shù)

16、說明：text代表要截字符的字符串；代表要截字符的字符串；num_chars代表給定的截取數(shù)目輸入公式：代表給定的截取數(shù)目輸入公式：=LEFT(A38,3) 3、RIGHT：從右側(cè)提取字符串函數(shù)，有：從右側(cè)提取字符串函數(shù)，有2個參數(shù)個參數(shù)參數(shù)參數(shù)1，選中提選中提取字符串的單元格；參數(shù)取字符串的單元格；參數(shù)2，輸入提取，輸入提取 的個數(shù)。的個數(shù)。使用格式：使用格式：RIGHT(text,num_chars) 參數(shù)說明：參數(shù)說明：text代表要截字符的字符串；代表要截字符的字符串；num_chars代表給定的截取數(shù)目。輸入公式：代表給定的截取數(shù)目。輸入公式：=RIGHT(A65,3) 第二講：公

17、式 與 函 數(shù)4、MID：從任意字符開始提取字符，有：從任意字符開始提取字符，有3個參數(shù)。參數(shù)個參數(shù)。參數(shù)1，選中提取選中提取字符串的單元格；參數(shù)字符串的單元格；參數(shù)2，從哪開始提?。ㄝ斎霐?shù)字）；參數(shù)，從哪開始提?。ㄝ斎霐?shù)字）；參數(shù)3，提幾位（輸數(shù)字）。提幾位（輸數(shù)字）。使用格式：使用格式：MID(text,start_num,num_chars)參參數(shù)說明：數(shù)說明：text代表一個文本字符串；代表一個文本字符串；start_num表示指定的起始位置表示指定的起始位置num_chars表示要截取的數(shù)目。輸入公式：表示要截取的數(shù)目。輸入公式：=MID(A47,4,3) 5、TEXT：將數(shù)字轉(zhuǎn)換為

18、文本，有：將數(shù)字轉(zhuǎn)換為文本，有1個參數(shù)。選中要轉(zhuǎn)換數(shù)字單元格個參數(shù)。選中要轉(zhuǎn)換數(shù)字單元格。使用格式：使用格式：TEXT(value,format_text) 參數(shù)說明：參數(shù)說明：value代表需要轉(zhuǎn)換的代表需要轉(zhuǎn)換的數(shù)值或引用的單元格；數(shù)值或引用的單元格；format_text為指定文字形式的數(shù)字格式。輸入公式：為指定文字形式的數(shù)字格式。輸入公式：=TEXT(B68, $0.00)6、VALUE：將文本型數(shù)字轉(zhuǎn)換成數(shù)字格式，有：將文本型數(shù)字轉(zhuǎn)換成數(shù)字格式，有2個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，選要轉(zhuǎn)換的文本所在單元格；參數(shù)，選要轉(zhuǎn)換的文本所在單元格；參數(shù)2，設(shè)置轉(zhuǎn)換，設(shè)置轉(zhuǎn)換 后的格式。一般選擇元

19、單元格，以保證轉(zhuǎn)換后是原來的格式。后的格式。一般選擇元單元格，以保證轉(zhuǎn)換后是原來的格式。使使用格式：用格式：VALUE(text) 參數(shù)說明：參數(shù)說明：text代表需要轉(zhuǎn)換文本型字符串?dāng)?shù)值。公式代表需要轉(zhuǎn)換文本型字符串?dāng)?shù)值。公式=VALUE（A1）7、CONCATENATE：文本連接函數(shù)，將多個單元格的字符連在起：文本連接函數(shù)，將多個單元格的字符連在起，有，有255參數(shù)。參數(shù)參數(shù)。參數(shù)1，選要連接的第，選要連接的第1單元格，第單元格，第2單元格，單元格，使用格式：使用格式：CONCATENATE(Text1，Text) 參數(shù)說明：參數(shù)說明：Text1、Text2為需要連接的字符文本或引用的單元

20、格。為需要連接的字符文本或引用的單元格。 應(yīng)用舉例：在應(yīng)用舉例：在C14單元格中單元格中輸入公式：輸入公式：=CONCATENATE(A14,B14,.com)，確認后，即可將，確認后，即可將A14單元格中字符、單元格中字符、B14單元格中的字符和單元格中的字符和.com連接成一個整體，顯示在連接成一個整體，顯示在C14單元格中。單元格中。 第二講：公 式 與 函 數(shù)（四）邏輯函數(shù)：（四）邏輯函數(shù)： 1、AND：與的含義，判斷：與的含義，判斷2個條件是否同時滿足，都滿足時返回個條件是否同時滿足，都滿足時返回 想要實現(xiàn)的內(nèi)容，有想要實現(xiàn)的內(nèi)容，有1個不滿足時，返回假值。有個不滿足時，返回假值。有

21、255個參數(shù)個參數(shù) 參數(shù)參數(shù)1，輸入條件，輸入條件1，參數(shù)，參數(shù)2，輸入條件，輸入條件2， 注：用這個參注：用這個參數(shù)前，一定要先給定條件。使用格式：數(shù)前，一定要先給定條件。使用格式：AND(logical1,logical2, .) 參數(shù)說明：參數(shù)說明：Logical1,Logical2,Logical3：表示待測試：表示待測試的條件值或表達式，最多這的條件值或表達式，最多這30個。輸入公式：個。輸入公式：=AND(A5=60,B5=60) 2、OR：或的含義，判斷所有條件，有一個滿足為要返回的內(nèi)容：或的含義，判斷所有條件，有一個滿足為要返回的內(nèi)容 都不滿足時為假值。都不滿足時為假值。 本節(jié)

22、案例，打開本節(jié)案例，打開AND函數(shù)表格。函數(shù)表格。 在在C2單元格輸入單元格輸入=AND(A2=男男,B260)*1000 =OR(A2=女女,B260)*1000當(dāng)所有當(dāng)所有參數(shù)值均為邏輯參數(shù)值均為邏輯“假（假（FALSE）”時返回函數(shù)結(jié)果邏輯時返回函數(shù)結(jié)果邏輯“假（假（FALSE）”，否則都返回邏輯，否則都返回邏輯“真（真（TRUE）”。 第三講：剩余函數(shù)與排序，篩選，分類匯總。一、其他常用函數(shù)：一、其他常用函數(shù)： （一）日期函數(shù)：（一）日期函數(shù)：1、TODAY：返回能自動更新的系統(tǒng)當(dāng)前日期，無參數(shù)。：返回能自動更新的系統(tǒng)當(dāng)前日期，無參數(shù)。TODAY() 2、NOW：返回能自動更新的系統(tǒng)當(dāng)

23、前時間，無參數(shù)。：返回能自動更新的系統(tǒng)當(dāng)前時間，無參數(shù)。NOW() 3、DATE：將分散的日期連接在一起，有：將分散的日期連接在一起，有3個參數(shù)。參數(shù)個參數(shù)。參數(shù)1，選年所，選年所在單元格；參數(shù)在單元格；參數(shù)2，選月所在單元格，選月所在單元格參數(shù)參數(shù)3，選日所在單元格。使用格式：，選日所在單元格。使用格式：DATE(year,month,day) 參數(shù)說明：參數(shù)說明：year為指定的年份數(shù)值（小于為指定的年份數(shù)值（小于9999）；）；month為指定為指定的月份數(shù)值（可以大于的月份數(shù)值（可以大于12）；）；day為指定的天數(shù)。為指定的天數(shù)。 輸入公式：輸入公式：=DATE(2003,13,35

24、)，確認后，顯示出，確認后，顯示出2004-2-4 4、YEAR：返回日期中的年，一般與：返回日期中的年，一般與today函數(shù)合用，有函數(shù)合用，有1參數(shù)選參數(shù)選中要截取的日期所在單元格。給出系統(tǒng)日期使用格式中要截取的日期所在單元格。給出系統(tǒng)日期使用格式TODAY()輸入公式：輸入公式：=TODAY()=YEAR(2015-5-6)結(jié)果是結(jié)果是2015 5、MONTH：返回日期中的月，有：返回日期中的月，有1個參數(shù)。個參數(shù)。=MONTH（2015-5-6）結(jié)果是）結(jié)果是5 選中要截取的日期所在單元格。選中要截取的日期所在單元格。第三講：剩余函數(shù)與排序，篩選，分類匯總。6、DAY：返回日期中的日，

25、有：返回日期中的日，有1個參數(shù)。個參數(shù)。 選中要截取的日期所在單元格。選中要截取的日期所在單元格。=DAN（2015-5-6）結(jié)果）結(jié)果是是6（二）查找與引用函數(shù)：（二）查找與引用函數(shù)： 1、LOOKUP：在數(shù)據(jù)區(qū)域的第一行：在數(shù)據(jù)區(qū)域的第一行第一列中查找指定的值，第一列中查找指定的值，然后返回最后一行然后返回最后一行一列中同行一列中同行同列相對應(yīng)的數(shù)值，常用的有同列相對應(yīng)的數(shù)值，常用的有2個參數(shù)。輸入個參數(shù)。輸入=LOOKUP(E2,B2:B8,C2:C8) 參數(shù)參數(shù)1，用誰找，用誰找，選條件所在單元格；參數(shù)，選條件所在單元格；參數(shù)2，到哪找，選數(shù)，到哪找，選數(shù) 據(jù)區(qū)域單元格。據(jù)區(qū)域單元格。

26、 打開本節(jié)案例企業(yè)員工考評管理系統(tǒng)表，利用編號找總成績。打開本節(jié)案例企業(yè)員工考評管理系統(tǒng)表，利用編號找總成績。 選中員工技能考勤表，利用編號選中員工技能考勤表，利用編號HSR1010找它的總成績是：找它的總成績是： 先在其他單元格輸入先在其他單元格輸入HSR1010單擊單擊Fx按鈕按鈕類別為查找類別為查找 與引用與引用選擇選擇LOOCUP確定確定選擇第選擇第2項項確定確定參數(shù)參數(shù)1選選 HSR1010所在單元格所在單元格參數(shù)參數(shù)2選選A3G20（要絕對引用）（要絕對引用）第三講：剩余函數(shù)與排序，篩選，分類匯總。2、HLOOKUP函數(shù)：按行查找函數(shù)，有函數(shù)：按行查找函數(shù)，有4個參數(shù)。個參數(shù)。 參

27、數(shù)參數(shù)1，用誰找；參數(shù)，用誰找；參數(shù)2，到哪找；參數(shù)，到哪找；參數(shù)3，找到后，返回第，找到后，返回第 幾行；參數(shù)幾行；參數(shù)4，精確找，還是模糊找。精確找輸入，精確找，還是模糊找。精確找輸入0,不精確找不精確找，輸入，輸入1，但得需要排序。一般都是精確找，所有可以省略不輸，但得需要排序。一般都是精確找，所有可以省略不輸 打開本小節(jié)案例企業(yè)員工考評管理系統(tǒng)表，選中員工業(yè)績考評表打開本小節(jié)案例企業(yè)員工考評管理系統(tǒng)表，選中員工業(yè)績考評表 選中選中D3單元格單元格輸入公式輸入公式=HLOOKUP(C3,員工業(yè)績獎勵標準員工業(yè)績獎勵標準!$B$2:$F$4,3)*C3，本例省略了最后一個參數(shù)。，本例省略了

28、最后一個參數(shù)。3、VLOOKUP函數(shù)：按列查找函數(shù)，有函數(shù)：按列查找函數(shù)，有4個參數(shù)。個參數(shù)。 參數(shù)參數(shù)1，用誰找；參數(shù)，用誰找；參數(shù)2，到哪找（一般為絕對引用）；參數(shù)，到哪找（一般為絕對引用）；參數(shù)3 找到后，返回第幾列；參數(shù)找到后，返回第幾列；參數(shù)4，精確找，還是模糊找。精確找，精確找，還是模糊找。精確找 輸入輸入0，不精確找輸入，不精確找輸入1。 打開本小節(jié)案例企業(yè)員工考評管理系統(tǒng)表，選中員工年終考評打開本小節(jié)案例企業(yè)員工考評管理系統(tǒng)表，選中員工年終考評 表選中表選中3單元格單元格輸入公式輸入公式=VLOOKUP(A3,員工工作態(tài)度考員工工作態(tài)度考 評評!$A$3:$H$20,8)，本例

29、省略了最后一個參數(shù)。，本例省略了最后一個參數(shù)。 第三講：剩余函數(shù)與排序，篩選，分類匯總。注意：使用注意：使用vlookup函數(shù)，要幾點要求，否則系統(tǒng)將報錯。函數(shù)，要幾點要求，否則系統(tǒng)將報錯。 1、查找的內(nèi)容即用誰找時那一項必須在數(shù)據(jù)區(qū)域的首列、查找的內(nèi)容即用誰找時那一項必須在數(shù)據(jù)區(qū)域的首列 2、參數(shù)一定要輸全。、參數(shù)一定要輸全。 3、沒有特殊要求，一般都用絕對引用。、沒有特殊要求，一般都用絕對引用。 如果想查找時，目標內(nèi)容不是首列，可以把列位置倒換以下。如如果想查找時，目標內(nèi)容不是首列，可以把列位置倒換以下。如用姓名查找編號，可將姓名和編號列倒換位置。用姓名查找編號，可將姓名和編號列倒換位置。

30、（三）函數(shù)綜合使用案例即函數(shù)的嵌套使用。（三）函數(shù)綜合使用案例即函數(shù)的嵌套使用。 1、根據(jù)員工身份證號提取出生年月日：、根據(jù)員工身份證號提取出生年月日： 打開本節(jié)案例企業(yè)員工檔案管理表打開本節(jié)案例企業(yè)員工檔案管理表選中選中D3單元格，輸入公式單元格，輸入公式=IF(LEN(C3)=15,CONCATENATE(19,MID(C3,7,2),年年,MID(C3,9,2),月月,MID(C3,11,2),日日),CONCATENATE(MID(C3,7,4),年年,MID(C3,11,2),月月,MID(C3,13,2),日日) 如果都是如果都是18位的，公式?jīng)]有這么復(fù)雜。位的，公式?jīng)]有這么復(fù)雜。

31、 第三講：剩余函數(shù)與排序，篩選，分類匯總。2、根據(jù)員工身份證號判斷性別為男女：、根據(jù)員工身份證號判斷性別為男女： 打開本節(jié)案例企業(yè)員工檔案管理表打開本節(jié)案例企業(yè)員工檔案管理表選選E3單元格輸入公式單元格輸入公式 =IF(LEN(C3)=15,IF(MOD(MID(C3,15,1),2)=1,“男男, 女女),IF(MOD(MID(C3,17,1),2)=1,男男,女女) 如果身份證都是如果身份證都是18位的，公式?jīng)]這么復(fù)雜。位的，公式?jīng)]這么復(fù)雜。3、計算工齡：、計算工齡： 打開本節(jié)案例企業(yè)員工檔案管理表打開本節(jié)案例企業(yè)員工檔案管理表選中選中G3單元格，輸入公式單元格，輸入公式 =YEAR(TO

32、DAY()-YEAR(F3)第三講：剩余函數(shù)與排序，篩選，分類匯總。二、排序：二、排序： 1、按行排序： 選中要排序的單元格區(qū)域開始選項卡排序和篩選按鈕自定義排序確定選擇選項按行排序確定然后依次設(shè)置即可。 2、2007新增按顏色排序： 打開本節(jié)案例這樣的排序你用過嗎表選中按顏色排 序工資表按要求操作選中B2：B12開始排 序和篩選自定義排序主要關(guān)鍵字為銷售額排序 依據(jù)為單元格顏色次序中先選一個顏色在頂端 確定。第三講：剩余函數(shù)與排序，篩選，分類匯總。2、按自定義序列排序： 打開本節(jié)案例這樣的排序你用過嗎表選中按自定義序列排序工作表先添加已經(jīng)輸好的序列單擊office按鈕Excel選項常用單擊編

33、輯自定義列表單擊紅色折疊按鈕選中B3:B11單元格單擊導(dǎo)入確定 確定開始排序與篩選自定義排序選項次序為自定義序列選中已經(jīng)添加的序列確定確定3、恢復(fù)排序前的順序： 加入一個輔助列，輸入自然數(shù)1,2,3，排序后，在排序時關(guān)鍵是為輔助列，次序為升序確定。第三講：剩余函數(shù)與排序，篩選，分類匯總。4、各行插入空行：制作工資條 加入一個輔助列先在有內(nèi)容的區(qū)域輸入1,3,5等，然后再他下方相等的單元格中輸入2,4,6,8等給輔助列 升序排序刪除輔助列選中有第1行有內(nèi)容區(qū)域的單元格復(fù)制選中有內(nèi)容的區(qū)域，包含空行F5單擊定位條件空值確定粘貼。 第三講：剩余函數(shù)與排序，篩選，分類匯總。三、高級篩選（多條件篩選）三、高級篩選（多條件篩選） 1、高級篩選中的與和或的關(guān)系：、高級篩選中的與和或的關(guān)系： 當(dāng)條件在同行中寫，表示與的關(guān)系，即多個條件當(dāng)條件在同行中寫，表示與的關(guān)系，即多個條件同時滿足；當(dāng)條件錯行寫，表示或的關(guān)系，即滿足其中同時滿足；當(dāng)條件錯行寫，表示或的關(guān)系，即滿足其中一個即可。一個即可。 2、篩選不重復(fù)值：、篩選不重復(fù)值： 選中