Excel基础函数操作

1.公示的概念

公式：以“=”开始，对单元格进行运算处理。最终返回一个值。

公式的构成：各种运算符，函数，字符，数字，单元格引用等等。

2.算术运算符

解释符号加+减-乘*除/乘幂^3.比较运算符是excel公式中运用频率很高的符号。

公式中有比较运算符，就会产生逻辑值：TRUE或是FALSE

名称符号等于=大于>大于等于>=小于<小于等于<=不等于<>

例如标出大于等于90分的记录，则需要先判断逻辑值，运算后转换成数值

4.将逻辑值转换为数值的方法

逻辑值负负逻辑值逻辑值0逻辑值-0逻辑值*1逻辑值/1FALSE00000TRUE11111FALSE00000TRUE11111FALSE00000FALSE00000FALSE00000FALSE00000TRUE11111TRUE11111备注：                                           逻辑值前后均可                        逻辑值前后均可

注意：逻辑值只要产生了数字运算，就会自动转换

5.函数：内置好的一些数据计算，处理方法。

①函数引入：函数虽说在公式中不是必不可少的，但基本上是为函数而生的。没有函数的公式是走不远的。

②函数的格式：函数名（参数1，参数2…）

③函数的使用方法利用录入提示输入函数利用参数定位利用函数对话框

6.引用

相对引用：就像人的影子（你走，我也走）

绝对引用：以不变应万变

混合引用：不同于相对与绝对引用，混合引用只引用单元格中的行或列

引用说明表

引用名称引用结果引用实质相对引用=A1行列不固定绝对引用=$A$1行列固定混合引用=A$1固定行混合引用=$A1固定列应用场景：当公式要向行，列方向都要填充时
注意：各引用之间的转换快捷键F4

7.连连看

①连接符&：连接单元格（注意：单元格与字符，数字的连接，字符，数字需要加双引号）

姓名年龄连接1连接2连接3张三19张三19张三-19岁张三-19-100李四34李四34李四-34岁李四-34-100王五29王五29王五-29岁王五-29-100                                                          =A5&B5                 =A5&"-"&B5&"岁"       =A5&"-"&B5&"-"&100

 ②连接函数CONCATENATE                                           =CONCATENATE(A15,"-",B15,"岁")

姓名年龄连接1                    连接2连接3张三19 张三19张三-19岁张三-19-100李四34李四34李四-34岁李四-34-100王五29王五29王五-29岁王五-29-100                                                          =CONCATENATE(A15,B15)     =CONCATENATE(A15,"-",B15,"-",100)

③连接函数PHONETIC（注意：phonetic函数忽略数字）

我们是中国人大家好excel公式溜溜溜verygood888结果

我们是中国人大家好excel公式溜溜溜verygood

=PHONETIC(A24:D24)

8.数组（就是一组数）

①数组元素间的分隔

符事情样式逗号{1,2,3}分号｛1;2;3｝内存数组并不存在于单元格之中，而在内存中，查看内存数组的值（抹黑公式，按F9）

无论是何种数组，只要再次发生计算，即产生了内存数组（数学，比较，连接运算等）
注意：产生了内存数组，则需要按ctrl+shift+enter

②二维数组：由不同方向的一维数组构成（注意分号和逗号的区别）

9.SUM函数引导

①计算总和

费用金额出差费450伙食费690管理费230水电费56网费120总费用1546用ALT+=，或者sum函数

②利用sum函数忽略错误值

费用金额报销50%出差费450225伙食费690345管理费无#VALUE!水电费5628网费无#VALUE!③单条件计数（不及格人数）
姓名分数A68B60C67D72E69F80G51H66人数1=SUM((B5:B12<60)-0)

 ④多条件计数（两科同时不及格的人数）          
姓名语文数学A6987B4649C5382D8360E4085F9040G5350H6689I7889J4747K4248人数4=SUM((C26:C36<60)*(B26:B36<60))

⑤单条件求和（求出A产品的总金额）

产品数量金额B54972A481104B581102A44748A621426C882024B421092A851360C601320C41902A781092总金额5730=SUM((A50:A60="A")*C50:C60)

10.计数

①count（计算数字的个数，在除法中，0不能为除数，count忽略错误值，不计算）

每个学生90分以上的科目数是多少

学生语文数学英语化学历史政治90分科数张三6082599069971李四91951008064543王五6270796289720陈六5963659460851田七9894666251612                                                                                                                            =COUNT(1/(B14:G14>90))

②counta（计算非空单元格）

计算考试科目的进度

学员科目1科目2科目3科目4张三√√李四√王五√√√陈六√√√√田七结果

已考科数50%25%75%100%0%

=COUNTA(B25:E25)/COUNTA($B$24:$E$24)

11.min函数

024爱学习结果：最小值0计算上班小时数（上，下午标准上班时间是4小时，如果超过4小时，则以四小时为准）

                                     上班时间统计表

日期上午下午2013/6/64.53.92013/6/74.762013/6/8532013/6/95.162013/6/1054.12013/6/11342013/6/123.832013/6/13352013/6/142013/6/15结果

小时数7.9878876.8700

 =MIN(--B35,4)+MIN(--C35,4)

12.average函数

求每个人的最高平均分

姓名语文数学英语平均分A189887182.7A198748686.0A294639283.0A289798885.3A387795874.7A193855377.0A468819982.7A486517169.3                                                           =AVERAGE(B32:D32)

姓名最高平均分A186.0A285.3A374.7A482.7                                                 =MAX((G35=A$32:A$39)*E$32:E$39)

先求平均分，然后关联姓名，然后数组合并

如果是全勤则加100，否则加50，求每个人的工资?                                                            

姓名基本工资全勤A12300A14500A16500是A23800A22700A35400是A42800A45100是结果：工资23504550660038502750550028505200  =AVERAGE(C48="是",1)*100+B48

逻辑值与1求平均值，为真是1，为假是0.5

13.TRIMMEAN（返回数据集的内部平均值）

=TRIMMEAN（求平均区域，最大+最小的个数/总个数）

去掉一个最高分，最低分的最后得分

去掉两个最大值与两个最小值                                                                                                                                                                    =TRIMMEAN(B6:H6,4/7)

参赛者评委1评委2评委3评委4评委5评委6评委7最后得分最后得分张三8.089.538.949.479.848.179.79.1629.313333李四8.799.488.549.259.269.998.419.0649.1王五9.479.919.669.318.049.199.029.339.323333陈办8.578.688.168.268.498.538.178.4048.426667周作建8.898.289.878.198.28.618.778.558.553333                                                                                                                                                                                                =(SUM(B6:H6)-MAX(B6:H6)-MIN(B6:H6))/5 
14.IF函数导入

IF函数是EXCEL非常重要的一个函数

除数字0=FALSE，其他一切数字均可判断为TRUE

标准级别>=101级>=52级>=23级<24级

姓名工龄级别张三141级李四23级宁波04级宋江23级宇文化及101级杨志141级朱明杰121级黄天14级植有权43级

                                                =IF(B4>=10,1,IF(B4>=5,2,IF(B4>=2,3,4)))&"级"

SUMIF 函数

=SUMIF("条件区域","条件","求和区域")

姓名部门基础工资A1销售1部4500A2销售2部2500A3销售3部3000A4销售1部1500A5销售2部4500A6销售1部3500A7销售3部1500A8销售1部3000A9销售3部3000A10销售3部1500

基础总工资15000=SUMIF(C37:C46,">=2000")-SUMIF(C37:C46,">4000")

SUMIF 通配符的用法（？--一个字符   *---任意个字符）

条件写法开始上海*非开始<>上海*结束*上海非结束<>*上海包含*上海*非包含<>*上海*客户订货数量大上海酒店552天津总部226北京分公司979上海分公司471成都分公司950上海办事一处488北京一处343美丽天津232北京和上海的平均总订货数量？

平均订货量1416.5=AVERAGE(SUMIF(A22:A29,{"*上海*","*北京*"},B22:B29))

COUNTIF条件计数

=COUNTIF("条件区域","条件")

补贴人员补助金额张三丰500李铁100王下300张龙400赵虎500李消400明明300张屯林100

张姓和李姓的人数各多少？

姓氏人数张3李2                        =COUNTIF(A41:A48,{"张*";"李*"})

1.COUNTIF如果进行的是，把数组作为条件的计数，结果是一个数组

2.注意分隔符","代表行    ";"代表列