第一篇：數(shù)學和計算機中公式和常用函數(shù)教案

第二單元(電子表格)第四課 公式和常用函數(shù)

第四課 公式和常用函數(shù)（第2課時）

苗寨中心校 馬新霞

一、教學目標

知識方面：

1、理解函數(shù)的概念。

2、掌握求SUM、AVERAGE、IF函數(shù)的使用方法。

3、能夠根據所學函數(shù)知識判別計算得到的數(shù)據的正確性。

技能方面：

1、使學生掌握分析數(shù)據、處理數(shù)據的能力。

2、培養(yǎng)學生管理數(shù)據的能力。

3、培養(yǎng)學生綜合運用所學知識，解決實際問題的能力。

情感方面：

1、培養(yǎng)學生主動思考，積極探索的精神。

2、培養(yǎng)學生耐心、細致的工作作風。

二、教學重點：SUM、AVERAGE函數(shù)的使用方法

三、教學難點：IF函數(shù)的使用方法

四、教學方法：講授法、演示法、觀察法、實踐法。

五、教學手段與教學媒體：

1、計算機教室。

2、老師準備的表格素材。

六、學習過程

（一）、復習舊知

公式的應用

（二）、導入課題

由計算“學生成績表”中的總分與平均分的方法與弊端引出課題。

（三）、學習新知

常用函數(shù)的用法

函數(shù)：excel事先定義好的公式，它可以單獨使用，也可以作為一般公式的組成部分來使用。

1、sum函數(shù)

第二單元(電子表格)第四課 公式和常用函數(shù)

功能：計算單元格區(qū)域中所有數(shù)值的和 例：使用SUM函數(shù)求出學生的總分。

教師布置任務：打開桌面上“電子表格”菜單下的“七二班成績表”，利用SUM函數(shù)計算總分

2、AVERAGE函數(shù)

功能：計算指定范圍內數(shù)據的算術平均值 AVERAGE函數(shù)的使用方法與SUM函數(shù)相同。

自主探究：用求平均值函數(shù)計算“七年級二班成績表”中的平均分。（可以討論）提醒：當參與計算的單元格內的數(shù)據發(fā)生變化時，計算結果會自動更新。

3、IF函數(shù)

功能：判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足返回另外一個值 常用的關系運算符：=,>,<,<>,>=,<= 例：計算桌面上第二單元“評委打分情況”工作表中的得分類別，95（含95）以上為“優(yōu)秀”，其他分數(shù)為“一般”。

布置任務：請你動手求出“評委打分情況”工作表中得分類別。

（四）、本節(jié)小結

第二篇：Excel函數(shù)公式

Excel函數(shù)公式

在會計同事電腦中，保保經常看到海量的Excel表格，員工基本信息、提成計算、考勤統(tǒng)計、合同管理....看來再完備的會計系統(tǒng)也取代不了Excel表格的作用。

于是，小呀盡可能多的收集會計工作中的Excel公式，所以就有了這篇本平臺史上最全的Excel公式+數(shù)據分析技巧集。

員工信息表公式

1、計算性別(F列)

=IF(MOD(MID(E3,17,1),2),“男”,“女”)

2、出生年月(G列)

=TEXT(MID(E3,7,8),“0-00-00”)

3、年齡公式(H列)

=DATEDIF(G3,TODAY,“y”)

4、退休日期(I列)

=TEXT(EDATE(G3,12*(5*(F3=“男”)+55)),“yyyy/mm/dd aaaa”)

5、籍貫(M列)

=VLOOKUP(LEFT(E3,6)*1,地址庫!E:F,2,)注：附帶示例中有地址庫代碼表

6、社會工齡(T列)=DATEDIF(S3,NOW,“y”)

7、公司工齡(W列)

=DATEDIF(V3,NOW,“y”)&“年”&DATEDIF(V3,NOW,“ym”)&“月”&DATEDIF(V3,NOW,“md”)&“天”

8、合同續(xù)簽日期(Y列)

=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1

9、合同到期日期(Z列)

=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY,“[<0]過期0天;[<30]即將到期0天;還早”)

10、工齡工資(AA列)

=MIN(700,DATEDIF($V3,NOW,“y”)*50)

11、生肖(AB列)=MID(“猴雞狗豬鼠?；⑼谬埳唏R羊”,MOD(MID(E3,7,4),12)+1,1)

1、本月工作日天數(shù)(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、調休天數(shù)公式(AI列)=COUNTIF(B9:AE9,“調”)

3、扣錢公式(AO列)

婚喪扣10塊，病假扣20元，事假扣30元，礦工扣50元

=SUM((B9:AE9={“事”;“曠”;“病”;“喪”;“婚”})*{30;50;20;10;10})

1、本科學歷人數(shù)

=COUNTIF(D:D,“本科”)

2、辦公室本科學歷人數(shù)

=COUNTIFS(A:A,“辦公室”,D:D,“本科”)3、30~40歲總人數(shù)

=COUNTIFS(F:F,“>=30”,F:F,“<40”)

1、提成比率計算

=VLOOKUP(B3,$C$12:$E$21,3)

2、個人所得稅計算

假如A2中是應稅工資，則計算個稅公式為：

=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)

3、工資條公式

=CHOOSE(MOD(ROW(A3),3)+1,工資數(shù)據源!A$1,OFFSET(工資數(shù)據源!A$1,INT(ROW(A3)/3)，),“")注：

A3:標題行的行數(shù)+2,如果標題行在第3行，則A3改為A5

? 工資數(shù)據源!A$1:工資表的標題行的第一列位置

4、Countif函數(shù)統(tǒng)計身份證號碼出錯的解決方法

由于Excel中數(shù)字只能識別15位內的，在Countif統(tǒng)計時也只會統(tǒng)計前15位，所以很容易出錯。不過只需要用 &”*“轉換為文本型即可正確統(tǒng)計。=Countif(A:A,A2&”*")

1、各部門人數(shù)占比

統(tǒng)計每個部門占總人數(shù)的百分比

2、各個年齡段人數(shù)和占比

公司員工各個年齡段的人數(shù)和占比各是多少呢？

3、各個部門各年齡段占比

分部門統(tǒng)計本部門各個年齡段的占比情況

4、各部門學歷統(tǒng)計

各部門大專、本科、碩士和博士各有多少人呢？

5、按年份統(tǒng)計各部門入職人數(shù) 每年各部門入職人數(shù)情況.今天分享的Excel公式雖然很全，但實際和會計實際要用到的excel公式相比，很多遺漏。歡迎做會計的同學們補充你工作中最常用到的公式。

還會有

第三篇：Excel函數(shù)公式

文本函數(shù)：

UPPER 將一個字符串中所有小寫字母轉換為大寫字母； LOWER 是都變成小寫，PROPER是首字母大寫；

LEFT(text,num_chars)提取一個文本字符串的前num_chars個字母，如圖。

日期和時間函數(shù)：

TODAY 返回當前日期；

NOW 返回當前日期和時間，如圖。

邏輯函數(shù)：

IF(logical_test, value_if_true, value_ if_false)判斷l(xiāng)ogical_test是否是真，如果真則返回值value_if_true,否則返回值value_ if_false，如圖，這里是判斷是否2>1，如果是真則返回1，如果是假則返回0；AND OR NOT 分別是 所有值為真、至少一個為真、求反。

數(shù)學與三角函數(shù)： SUM 求和；

ROUND(number,num_digits)四舍五入，保留num_digits位小數(shù)，如圖，這里如果給-2的話，表示從小數(shù)點向前數(shù)2位； MOD 求余數(shù)； INT 取整函數(shù)；

SIN COS TAN ASIN ACOS ATAN 就是基本的三角函數(shù)了。

統(tǒng)計函數(shù)：

AVERAGE平均值； MAX 最大值； MIN 最小值； COUNT 計數(shù)；

RANK(number,ref,order)排名函數(shù), 將數(shù)值number在一組數(shù)ref中的排名算出，數(shù)值order為0為降序，1為升序，如圖。

第四篇：Excel函數(shù)公式

1、查找重復內容公式：=IF(COUNTIF(A:A,A2)>1,“重復”,“")。

2、用出生年月來計算年齡公式：=TRUNC((DAYS360(H6,”2009/8/30“,FALSE))/360,0)。

3、從輸入的18位身份證號的出生年月計算公式：=CONCATENATE(MID(E2,7,4),”/“,MID(E2,11,2),”/“,MID(E2,13,2))。

4、從輸入的身份證號碼內讓系統(tǒng)自動提取性別，可以輸入以下公式： =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男“,”女

“),IF(MOD(MID(C2,17,1),2)=1,”男“,”女“))公式內的“C2”代表的是輸入身份證號碼的單元格。

1、求和： =SUM(K2:K56)——對K2到K56這一區(qū)域進行求和；

2、平均數(shù)： =AVERAGE(K2:K56)——對K2 K56這一區(qū)域求平均數(shù)；

3、排名： =RANK(K2，K$2:K$56)——對55名學生的成績進行排名；

4、等級： =IF(K2>=85,”優(yōu)“,IF(K2>=74,”良“,IF(K2>=60,”及格“,”不及格“)))

5、學期總評： =K2*0.3+M2*0.3+N2*0.4 ——假設K列、M列和N列分別存放著學生的“平時總評”、“期中”、“期末”三項成績；

6、最高分： =MAX(K2:K56)——求K2到K56區(qū)域（55名學生）的最高分；

7、最低分： =MIN(K2:K56)——求K2到K56區(qū)域（55名學生）的最低分；

8、分數(shù)段人數(shù)統(tǒng)計：

（1）=COUNTIF(K2:K56,”100“)——求K2到K56區(qū)域100分的人數(shù)；假設把結果存放于K57單元格；（2）=COUNTIF(K2:K56,”>=95“)－K57 ——求K2到K56區(qū)域95～99.5分的人數(shù)；假設把結果存放于K58單元格；

（3）=COUNTIF(K2:K56,”>=90“)－SUM(K57:K58)——求K2到K56區(qū)域90～94.5分的人數(shù)；假設把結果存放于K59單元格；

（4）=COUNTIF(K2:K56,”>=85“)－SUM(K57:K59)——求K2到K56區(qū)域85～89.5分的人數(shù)；假設把結果存放于K60單元格；

（5）=COUNTIF(K2:K56,”>=70“)－SUM(K57:K60)——求K2到K56區(qū)域70～84.5分的人數(shù)；假設把結果存放于K61單元格；

（6）=COUNTIF(K2:K56,”>=60“)－SUM(K57:K61)——求K2到K56區(qū)域60～69.5分的人數(shù)；假設把結果存放于K62單元格；

（7）=COUNTIF(K2:K56,”<60“)——求K2到K56區(qū)域60分以下的人數(shù)；假設把結果存放于K63單元格；

說明：COUNTIF函數(shù)也可計算某一區(qū)域男、女生人數(shù)。

如：=COUNTIF(C2:C351,”男“)——求C2到C351區(qū)域（共350人）男性人數(shù)；

9、優(yōu)秀率： =SUM(K57:K60)/55*100

10、及格率： =SUM(K57:K62)/55*100

11、標準差： =STDEV(K2:K56)——求K2到K56區(qū)域(55人)的成績波動情況（數(shù)值越小，說明該班學生間的成績差異較小，反之，說明該班存在兩極分化）；

12、條件求和： =SUMIF(B2:B56,”男“，K2:K56)——假設B列存放學生的性別，K列存放學生的分數(shù)，則此函數(shù)返回的結果表示求該班男生的成績之和；

13、多條件求和：｛=SUM(IF(C3:C322=”男“,IF(G3:G322=1,1,0)))｝ ——假設C列（C3:C322區(qū)域）存放學生的性別，G列（G3:G322區(qū)域）存放學生所在班級代碼（1、2、3、4、5），則此函數(shù)返回的結果表示求一班的男生人數(shù)；這是一個數(shù)組函數(shù)，輸完后要按Ctrl＋Shift＋Enter組合鍵(產生“｛……｝”)?！埃辈荒苁止ぽ斎耄荒苡媒M合鍵產生。

14、根據出生日期自動計算周歲：=TRUNC((DAYS360(D3,NOW()))/360,0)———假設D列存放學生的出生日期，E列輸入該函數(shù)后則產生該生的周歲。

15、在Word中三個小竅門： ①連續(xù)輸入三個“~”可得一條波浪線。②連續(xù)輸入三個“-”可得一條直線。

連續(xù)輸入三個“=”可得一條雙直線。

一、excel中當某一單元格符合特定條件，如何在另一單元格顯示特定的顏色比如： A1〉1時，C1顯示紅色 0

1、單元擊C1單元格，點“格式”>“條件格式”，條件1設為： 公式 =A1=1

2、點“格式”->“字體”->“顏色”，點擊紅色后點“確定”。條件2設為：

公式 =AND(A1>0,A1<1)

3、點“格式”->“字體”->“顏色”，點擊綠色后點“確定”。條件3設為： 公式 =A1<0 點“格式”->“字體”->“顏色”，點擊黃色后點“確定”。

4、三個條件設定好后，點“確定”即出。

二、EXCEL中如何控制每列數(shù)據的長度并避免重復錄入

1、用數(shù)據有效性定義數(shù)據長度。用鼠標選定你要輸入的數(shù)據范圍，點”數(shù)據“->”有效性“->”設置“，”有效性條件“設成”允許“"文本長度”“等于”“5”（具體條件可根據你的需要改變）。

還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好后點“確定”。

2、用條件格式避免重復。

選定A列，點“格式”->“條件格式”，將條件設成“公式=COUNTIF($A:$A,$A1)>1”，點“格式”->“字體”->“顏色”，選定紅色后點兩次“確定”。

這樣設定好后你輸入數(shù)據如果長度不對會有提示，如果數(shù)據重復字體將會變成紅色。

三、在EXCEL中如何把B列與A列不同之處標識出來？

（一）、如果是要求A、B兩列的同一行數(shù)據相比較：

假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，將條件設為: “單元格數(shù)值” “不等于”=B2

點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。用格式刷將A2單元格的條件格式向下復制。B列可參照此方法設置。

（二）、如果是A列與B列整體比較（即相同數(shù)據不在同一行）：

假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，將條件設為: “公式”=COUNTIF($B:$B,$A2)=0 點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。用格式刷將A2單元格的條件格式向下復制。B列可參照此方法設置。

按以上方法設置后，AB列均有的數(shù)據不著色，A列有B列無或者B列有A列無的數(shù)據標記為紅色字體。

四、EXCEL中怎樣批量地處理按行排序

假定有大量的數(shù)據(數(shù)值)，需要將每一行按從大到小排序，如何操作？

由于按行排序與按列排序都是只能有一個主關鍵字,主關鍵字相同時才能按次關鍵字排序。所以，這一問題不能用排序來解決。解決方法如下：

1、假定你的數(shù)據在A至E列，請在F1單元格輸入公式：

=LARGE($A1:$E1,COLUMN(A1))用填充柄將公式向右向下復制到相應范圍。

你原有數(shù)據將按行從大到小排序出現(xiàn)在F至J列。如有需要可用“選擇性粘貼/數(shù)值”復制到其他地方。

注：第1步的公式可根據你的實際情況（數(shù)據范圍）作相應的修改。如果要從小到大排序,公式改為:=SMALL($A1:$E1,COLUMN(A1))

五、巧用函數(shù)組合進行多條件的計數(shù)統(tǒng)計 例：第一行為表頭，A列是“姓名”，B列是“班級”，C列是“語文成績”，D列是“錄取結果”，現(xiàn)在要統(tǒng)計“班級”為“二”，“語文成績”大于等于104，“錄取結果”為“重本”的人數(shù)。統(tǒng)計結果存放在本工作表的其他列。公式如下：

=SUM(IF((B2:B9999=“二”)*(C2:C9999>=104)*(D2:D9999=“重本”),1,0))輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公式符號“{}”。

六、如何判斷單元格里是否包含指定文本？

假定對A1單元格進行判斷有無“指定文本”,以下任一公式均可: =IF(COUNTIF(A1,“*”&“指定文本”&“*”)=1,“有”,“無”)=IF(ISERROR(FIND(“指定文本”,A1,1)),“無”,“有”)求某一區(qū)域內不重復的數(shù)據個數(shù)

例如求A1:A100范圍內不重復數(shù)據的個數(shù)，某個數(shù)重復多次出現(xiàn)只算一個。有兩種計算方法：

一是利用數(shù)組公式：

=SUM(1/COUNTIF(A1:A100,A1:A100))輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公式符號“{}”。二是利用乘積求和函數(shù)：

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))七、一個工作薄中有許多工作表如何快速整理出一個目錄工作表

1、用宏3.0取出各工作表的名稱，方法：

Ctrl+F3出現(xiàn)自定義名稱對話框，取名為X，在“引用位置”框中輸入： =MID(GET.WORKBOOK(1),FIND(“]”,GET.WORKBOOK(1))+1,100)確定

2、用HYPERLINK函數(shù)批量插入連接，方法：

在目錄工作表（一般為第一個sheet）的A2單元格輸入公式： =HYPERLINK(“#'”&INDEX(X,ROW())&“'!A1”,INDEX(X,ROW()))將公式向下填充，直到出錯為止，目錄就生成了。

第五篇：常用的excel函數(shù)公式

一、數(shù)字處理

1、取絕對值 =ABS(數(shù)字)

2、取整 =INT(數(shù)字)

3、四舍五入

=ROUND(數(shù)字,小數(shù)位數(shù))

二、判斷公式

1、把公式產生的錯誤值顯示為空 公式：C2 =IFERROR(A2/B2,“")

說明：如果是錯誤值則顯示為空，否則正常顯示。

2、IF多條件判斷返回值 公式：C2 =IF(AND(A2<500,B2=”未到期“),”補款“,”“)

說明：兩個條件同時成立用AND,任一個成立用OR函數(shù)。

三、統(tǒng)計公式

1、統(tǒng)計兩個表格重復的內容 公式:B2 =COUNTIF(Sheet15!A:A,A2)

說明：如果返回值大于0說明在另一個表中存在，0則不存在。

2、統(tǒng)計不重復的總人數(shù) 公式：C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

說明:用COUNTIF統(tǒng)計出每人的出現(xiàn)次數(shù)，用1除的方式把出現(xiàn)次數(shù)變成分母，然后相加。

四、求和公式

1、隔列求和 公式：H3 =SUMIF($A$2:$G$2,H$2,A3:G3)或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)說明：如果標題行沒有規(guī)則用第2個公式

2、單條件求和 公式：F2 =SUMIF(A:A,E2,C:C)說明：SUMIF函數(shù)的基本用法

3、單條件模糊求和 公式：詳見下圖

說明：如果需要進行模糊求和，就需要掌握通配符的使用，其中星號是表示任意多個字符，如”*A*“就表示a前和后有任意多個字符，即包含A。

4、多條件模糊求和 公式：C11 =SUMIFS(C2:C7,A2:A7,A11&”*“,B2:B7,B11)說明：在sumifs中可以使用通配符*

5、多表相同位置求和 公式：b2 =SUM(Sheet1:Sheet19!B2)

說明：在表中間刪除或添加表后，公式結果會自動更新。

6、按日期和產品求和 公式：F2 =SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)說明：SUMPRODUCT可以完成多條件求和

五、查找與引用公式、單條件查找公式 公式1：C11 =VLOOKUP(B11,B3:F7,4,FALSE)

說明：查找是VLOOKUP最擅長的，基本用法

2、雙向查找公式 公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值

3、查找最后一條符合條件的記錄。公式：詳見下圖

說明：0/(條件)可以把不符合條件的變成錯誤值，而lookup可以忽略錯誤值

4、多條件查找 公式:詳見下圖

說明:公式原理同上一個公式

5、指定區(qū)域最后一個非空值查找 公式;詳見下圖 說明：略

6、按數(shù)字區(qū)域間取對應的值

公式說明：VLOOKUP和LOOKUP函數(shù)都可以按區(qū)間取值，一定要注意，銷售量列的數(shù)字一定要升序排列。

六、字符串處理公式、多單元格字符串合并 公式：c2 =PHONETIC(A2:A7)

說明：Phonetic函數(shù)只能對字符型內容合并，數(shù)字不可以。

2、截取除后3位之外的部分 公式：

=LEFT(D1,LEN(D1)-3)

說明：LEN計算出總長度,LEFT從左邊截總長度-3個

3、截取-前的部分 公式:B2 =Left(A1,FIND(”-“,A1)-1)

說明：用FIND函數(shù)查找位置，用LEFT截取。

4、截取字符串中任一段的公式 公式:B1 =TRIM(MID(SUBSTITUTE($A1,” “,REPT(” “,20)),20,20))

說明:公式是利用強插N個空字符的方式進行截取

5、字符串查找 公式：B2 =IF(COUNT(FIND(”河南“,A2))=0,”否“,”是“)

說明: FIND查找成功，返回字符的位置，否則返回錯誤值，而COUNT可以統(tǒng)計出數(shù)字的個數(shù)，這里可以用來判斷查找是否成功。

6、字符串查找一對多 公式：B2 =IF(COUNT(FIND({”遼寧“,”黑龍江“,”吉林“},A2))=0,”其他“,”東北“)說明：設置FIND第一個參數(shù)為常量數(shù)組，用COUNT函數(shù)統(tǒng)計FIND查找結果

七、日期計算公式

1、兩日期相隔的年、月、天數(shù)計算

A1是開始日期（2011-12-1），B1是結束日期(2013-6-10)。計算： 相隔多少天？=datedif(A1,B1,”d“)結果：557 相隔多少月? =datedif(A1,B1,”m“)結果：18 相隔多少年? =datedif(A1,B1,”Y“)結果：1 不考慮年相隔多少月？=datedif(A1,B1,”Ym“)結果：6 不考慮年相隔多少天？=datedif(A1,B1,”YD“)結果：192 不考慮年月相隔多少天？=datedif(A1,B1,”MD“)結果：9 datedif函數(shù)第3個參數(shù)說明： ”Y“ 時間段中的整年數(shù)?！盡“ 時間段中的整月數(shù)。”D“ 時間段中的天數(shù)。

”MD“ 天數(shù)的差。忽略日期中的月和年?！盰M“ 月數(shù)的差。忽略日期中的日和年?！盰D" 天數(shù)的差。忽略日期中的年。

2、扣除周末天數(shù)的工作日天數(shù) 公式：C2 =NETWORKDAYS.INTL(IF(B2

說明：返回兩個日期之間的所有工作日數(shù)，使用參數(shù)指示哪些天是周末，以及有多少天是周末。周末和任何指定為假期的日期不被視為工作日