excel函數(shù)公開(kāi)課一等獎(jiǎng)省優(yōu)質(zhì)課大賽獲獎(jiǎng)?wù)n件

EXCEL函數(shù)培訓(xùn)課件

呂治軍-11-16

潑水節(jié)，大家相互潑水表示祝福，突然有個(gè)人罵道：哪個(gè)混蛋潑我了？大家勸道：潑水表示祝福。那人吼到：少扯淡，哪個(gè)混蛋用開(kāi)水潑我了…

早晨特冷和朋友去ATM取錢(qián)，巧遇運(yùn)鈔車加鈔，只好站一旁等。朋友問(wèn)我凍手不？我冷冷說(shuō)：凍手！瞬間四桿槍指向我倆。被送入派出所。路上一直緘默我問(wèn)朋友你怎么不開(kāi)腔呢？瞬間八桿槍指向我倆。警察：叫什么名字？我：蔣英羽。警察：什么名字？我：蔣英羽！警察：what

is

your

name？

歷史考試選擇題：黃花崗起義第一槍誰(shuí)開(kāi)？A宋教仁B孫中山C黃興D徐錫麟，考生選C。又看第二題：黃花崗起義第二槍誰(shuí)開(kāi)？考生傻了，就選了個(gè)B。接著看第三題：黃花崗起義中，第三槍誰(shuí)開(kāi)？考生瘋了，胡亂選了A?？荚嚦鰜?lái)就去找出卷老師，老師拿出書(shū)本說(shuō)：黃興連開(kāi)三槍，揭開(kāi)了黃花崗起義序幕……

Excel函數(shù)培訓(xùn)1、什么是函數(shù)？函數(shù)是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)特殊公式-------實(shí)際上就是已經(jīng)定義好公式。2、什么是公式？ 公式就是用戶自行設(shè)計(jì)對(duì)工作表數(shù)據(jù)計(jì)算和處理表示式。公式中能夠包含函數(shù)。一、函數(shù)基礎(chǔ)1、函數(shù)結(jié)構(gòu) 函數(shù)是由函數(shù)名稱和一個(gè)或多個(gè)參數(shù)組成，其形式為：函數(shù)名（參數(shù)1,參數(shù)2,…），參數(shù)可是常量（數(shù)字與文本），代數(shù)表示式，單元格及單元格區(qū)域，邏輯值，數(shù)組和其它函數(shù)。 函數(shù)語(yǔ)法:由函數(shù)名+括號(hào)+參數(shù)組成例求和函數(shù):SUM(A1,B2,…)參數(shù)與參數(shù)之間用逗號(hào)“

,

”表示2、函數(shù)種類

(1)邏輯函數(shù)(2)時(shí)間與日期函數(shù)

(3)數(shù)學(xué)與三角函數(shù)(4)文本函數(shù)

(5)財(cái)務(wù)函數(shù)(6)統(tǒng)計(jì)函數(shù)

(7)查找與引用函數(shù)(8)其它函數(shù)3、相關(guān)概念

A、常規(guī)運(yùn)算符：“+”“-”“*”“/”B、比較運(yùn)算符：“=”“>”“<”“>=”“<=”“<>”

C、引用運(yùn)算符:,:(用于區(qū)域)D、邏輯值：True(真-1,1)False(假0)E.單元格相對(duì)引用與絕對(duì)引用 樣式:A1

$A1鎖定第A例

A$1鎖定第1橫

$A$1鎖定第A例與第1橫

F4鍵是快速鎖定功效鍵二、函數(shù)介紹與應(yīng)用 (1)AND函數(shù) 格式：AND(logical1,logical2,...)

全部參數(shù)邏輯值為真時(shí)返回TRUE；只要一個(gè)參數(shù)邏輯值為假即返回FALSE。簡(jiǎn)言之，就是當(dāng)AND參數(shù)全部滿足某一條件時(shí)，返回結(jié)果為T(mén)RUE，不然為FALSE。

(2)OR函數(shù) 格式：OR(logical1,logical2,...) OR函數(shù)指在其參數(shù)組中，任何一個(gè)參數(shù)邏輯值為T(mén)RUE，即返回TRUE,它與AND函數(shù)區(qū)分在于，AND函數(shù)要求全部函數(shù)邏輯值均為真，結(jié)果方為真。而OR函數(shù)僅需其中任何一個(gè)為真即可為真

(3)NOT函數(shù)格式：NOT(logical) NOT函數(shù)用于對(duì)參數(shù)值求反。當(dāng)要確保一個(gè)值不等于某一特定值時(shí)，能夠使用NOT函數(shù)。簡(jiǎn)言之，就是當(dāng)參數(shù)值為T(mén)RUE時(shí)，NOT函數(shù)返回結(jié)果恰與之相反，結(jié)果為FALSEAND(條件)

括號(hào)中條件值均為T(mén)RUE，則為T(mén)RUE；假如任一個(gè)為FALSE，則為FALSE=AND（5>3,2>1）=TRUE=AND（1>3,2>1）=FALSEOR(條件)

括號(hào)中條件值均為FALSE，則為FALSE；假如任一個(gè)為T(mén)RUE，則為T(mén)RUE=OR（5>3,2>1）=TRUE=OR（1>3,2>1）=TRUE=OR（1>3,2>5）=FALSENOT(條件)

括號(hào)中條件值為FALSE，則為T(mén)RUE；假如為T(mén)RUE，則為FALSE

=NOT（5>3）=FALSE=NOT（1>3）=TRUE表示式

IF(logical_test,value_if_true,value_if_false)

IF(邏輯條件,真值,假值)

說(shuō)明

邏輯條件:

運(yùn)算結(jié)果為T(mén)rue或者False表示式或值

真值

:Logical_test為T(mén)rue時(shí)返回值

假值:Logical_test為False時(shí)返回值

簡(jiǎn)言之，假如第一個(gè)參數(shù)logical_test返回結(jié)果為真話，則執(zhí)行第二個(gè)參數(shù)Value_if_true結(jié)果，不然執(zhí)行第三個(gè)參數(shù)Value_if_false結(jié)果IF函數(shù)用產(chǎn)品型號(hào)判斷公式F1=IF(SUM(A1:A5)<>0,SUM(A1:A5),“”)

解析：假如SUM(A1:E1)不等于零，則在F1單元格中顯示SUM(A1:E1)結(jié)果，不然顯示字符串。

幾點(diǎn)說(shuō)明：

（1）SUM(C5:F5)不等于零正規(guī)寫(xiě)法是SUM(C5:F5)<>0，在EXCEL中能夠省略<>0；

（2）“”表示字符串內(nèi)容為空，所以執(zhí)行結(jié)果是在單元格中不顯示任何字符。

例：假如單元格A1、B1、C1中是各科成績(jī)，假如有一科成績(jī)不到60分，則不錄用。公式為？=IF（OR（A1<60，B1<60，C1<60)”不錄用”,”錄用“）ABCDEF11223344245例解一嵌套使用：

If函數(shù)能夠嵌套使用七層,利用

True、False結(jié)構(gòu)復(fù)雜關(guān)系！IF函數(shù)方法一：G26=IF(F26>=180,IF(F26>=240,“優(yōu)異”，“合格”）,“不合格”）例解二方法二：G26=IF(F26>=240,“優(yōu)異”，IF(F26>=180,“合格”，“不合格”))思索：各工序目標(biāo)是否達(dá)標(biāo)。sum函數(shù)語(yǔ)法:

SUM(number1,number2,...)參數(shù):

number1，number2，...為1到30個(gè)需要求和參數(shù)。定義：返回某一單元格區(qū)域中數(shù)字、邏輯值及數(shù)字文本表示式之和算術(shù)平均數(shù):AVERAGE

格式：AVERAGE(數(shù)值1,數(shù)值2,…)ABC11002010002200403300603000

=AVERAGE（A1：A3）=200=AVERAGE（A1：A3，B1）=105四舍五入函數(shù):ROUNDROUND(數(shù)值,小數(shù)位)ABC11002010002200403300603000

=ROUND(A1/A3,2)=0.33=ROUNDUP(A1/A3,2)=0.34=ROUNDDOWN(A1/A3,2)=0.33Int取整函數(shù)（取向較小方向數(shù),向下取整）Int(數(shù)值)ABC110.820.3-10.32-10.840 int(A1)=10int(A2)=-11 int(B1)=20 int(C1)=-11定義：依據(jù)指定條件對(duì)若干單元格求和

sumif函數(shù)語(yǔ)法

SUMIF(range,criteria,sum_range)

1）range

為用于條件判斷單元格區(qū)域。

2）criteria

為確定哪些單元格將被相加求和條件，其形式可認(rèn)為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、“32”、“>32”或“李先春"。

3）sum_range

是需要求和實(shí)際單元格。求出上圖中李先春銷量總和？例解一=sumif(B2:E7,”李先春“，C2:E7)

結(jié)果=488求出上圖中大于10數(shù)值總和？例解二sumif(C3:C7,">=10")

結(jié)果=33Count表示式：

Count(value1,value2,……)

說(shuō)明:

計(jì)算連續(xù)或不連續(xù)區(qū)域數(shù)字個(gè)數(shù)。參數(shù)value代表1-30個(gè)各種不一樣類型數(shù)據(jù)，或單元格引用，但只對(duì)數(shù)字型數(shù)據(jù)進(jìn)行計(jì)算。COUNT

已知：A1=0A2=2A3=“”A4=“#REEF”

A5=Count(A1,A2,A3,A4,2)

結(jié)果

A6=3CountA表示式：

CountA(value1,value2,……)說(shuō)明:

計(jì)算參數(shù)表中任意類型數(shù)據(jù)個(gè)數(shù)?；騿卧駞^(qū)域中非空單元格個(gè)數(shù)。參數(shù)value代表1-30個(gè)各種不一樣類型數(shù)據(jù)，或單元格引用。COUNTA

已知：

A1=0A2=2A3=“”A4=“#REEF”

A5=“總量”

A6=Count(A1,A2,A3,A4,A5,2)

結(jié)果

A6=6COUNT與COUNTA區(qū)分

count只統(tǒng)計(jì)數(shù)字型單元格個(gè)數(shù),對(duì)于文字,錯(cuò)誤值,邏輯值<True>不能統(tǒng)計(jì),而counta能夠,但對(duì)于空白單元格都不能統(tǒng)計(jì).

countif函數(shù)語(yǔ)法：

COUNTIF(range,criteria)

說(shuō)明：

Range為需要計(jì)算其中滿足條件單元格數(shù)目標(biāo)單元格區(qū)域，

Criteria為確定哪些單元格將被計(jì)算在內(nèi)條件，其形式可認(rèn)為數(shù)字、表達(dá)式、單元格引用或文本（條件可以表示為79、“79”、“>79”、“李先春”或B4）上圖中求出數(shù)據(jù)1中A開(kāi)頭個(gè)數(shù)？例解一=countif(B2:B6,”A*”)

結(jié)果=3Left函數(shù)

表示式：Left(Text,num_chars)

說(shuō)明

Text:文本字符串

num_chars:指定要提取字符數(shù)

注意:num_chars必須大于等于0

假如n_cs大于文本長(zhǎng)度,返回全部文本假如省略num_chars,默認(rèn)為1

功效:從字符串左端取指定個(gè)數(shù)字符

已知:A1=012141002A2=Left(A1,2)

結(jié)果:A2=01

例解一Left函數(shù)

已知:A1=012141002A2=right(A1,2)

結(jié)果:A2=02

例解一right函數(shù)

已知:A1=012141002A2=right(A1,2)

結(jié)果:A2=02

例解一right函數(shù)mid函數(shù)

表示式：

Mid(Text，start_num，num_chars)Text:文本字符串

start_num:提取第一個(gè)字符位置

num_chars:返回字符個(gè)數(shù),不能是負(fù)數(shù)

（注：start_num大于文本總長(zhǎng)度,返回空，start_num+num_chars大于文本總長(zhǎng)度,返回到文本末字符串，start_num小于1,返回錯(cuò)誤值#value!）

已知：

A1=002141001A2=mid(A1,3,3)結(jié)果

A2=214

mid函數(shù)功效：

從一個(gè)文本字符串指定位置開(kāi)始，截取指定數(shù)目標(biāo)字符。

字符截取計(jì)算字符長(zhǎng)度LEN

LEN(文本)

計(jì)算字符串長(zhǎng)度

=LEN（“abcdef”）=6SDWL1005C3N6JSTFR01SDWL1005C3N6JSTF=Left(,12)&Right(,len()-13)將數(shù)值轉(zhuǎn)化為文本TEXTTEXT(數(shù)值,參數(shù))參數(shù)為轉(zhuǎn)換后文本格式.

=TEXT（1234,0）=1234將文本符轉(zhuǎn)化為數(shù)值VALUEVALUE(數(shù)值文本),分列=VALUE（“1234”）=1234合并字符函數(shù):CONCATENATECONCATENATE(文本1，…)

合并字符串&=CONCATENATE（“a”,“ef”）=aef比較兩個(gè)字符是否完全相符EXACTEXACT(文本1,文本2)比較兩個(gè)字符是否完全相符,是則為T(mén)RUE,不然為FALSE.=EXACT（“a”,“a”）=TRUE=EXACT（“a”,“ab”）=FALSE=EXACT（“a”,“A”）=FALSE大寫(xiě)函數(shù)：UPPER(text)把文本中小寫(xiě)字母改變成大寫(xiě)字母，是大寫(xiě)不變。

UPPER(“acbDE”)=ACBDE小寫(xiě)函數(shù)：LOWER(text)把文本中大寫(xiě)字母改變成小寫(xiě)字母，是小寫(xiě)不變。

LOWER(“acbDE”)=acbde去除文本中全部空格：TRIM(text)

除了單詞之間單個(gè)空格外，去除文本中全部空格。TRIM(“SWPA5040S100MT”)=SWPA5040S100MTTRIM(“Howdoyoudo?”)=Howdoyoudo?Max-Min函數(shù)表示式：

Max(number1,number2,......)MAX

求出一組數(shù)中最大值。

數(shù)據(jù)計(jì)算MIN

求出一組數(shù)中最小值。

數(shù)據(jù)計(jì)算已知：

A1=6,A2=4,A3=8假如：A4=Max(A1,A2,A3,2)結(jié)果：A4=8Max-Min函數(shù)

假如：A4=MIN(A1,A2,A3,2)結(jié)果：A4=2Rank函數(shù)表示式：rank(Number,ref,order)使用功效：求指定數(shù)字在一列數(shù)字中排位（排名次）說(shuō)明：Number代表需要求排位數(shù)據(jù)（通常使用單元格相對(duì)引用），ref代表Number所在一組數(shù)據(jù)（通常使用單元格區(qū)域絕對(duì)引用）Order為一數(shù)字，指明排位方式（0或省略為降序，大于0則為升序）。求A2在$A$2:$A$5單元格區(qū)域降序排列名次？

=rank(A2,$A$2:$A$5)

=2示例：EXCELABCD1分?jǐn)?shù)排名2502326430552例：1-升序,0-降序

ABC11002010002200403300603000=RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3vlookup表示式:VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）

說(shuō)明:1:要查找值;2:查找內(nèi)容數(shù)組;3:數(shù)據(jù)后開(kāi)始列數(shù);4:TrueFalse

VLOOKUP函數(shù)功效:在表格或數(shù)值數(shù)組首列查找指定數(shù)值，并由此返回表格或數(shù)組當(dāng)前行中指定列處數(shù)值。

Vlookup示例:

在B50中得出王勇總分?jǐn)?shù)?B50=Vlookup(A50,A1:E5,5,0)

=223excelAB50王勇51郭俊在字符串中查找特定字符FIND查找一個(gè)字符在另一個(gè)字符串中位置FIND(文本,范圍,數(shù)值)數(shù)值表示查找第幾個(gè).=FIND（“a”,“abcaef”,1）=1=FIND（“a”,“abcaef”,2）=4能夠用來(lái)取小批號(hào)1.返回日期序列年份YEAR

YEAR(日期)

A1=-07-29星期二