Excel基础教程（2）——函数与公式

Excel（2）

认识函数与公式

一、认识Excel公式

1. 运算符

   公式：=开头，但=不表示等于，写公式以等号开始。点击一项单元格，写上运算符，再点击一项单元格，回车。

   算术运算符：+ - * / （加减乘除，也可对文本运算）%（百分号，除以100） &（连字符，用户文本连接、数字连接）^(乘方)

=G12+H12

=G13&H13

​ 文本不可直接求和，加0或者乘以1后，会转为数值，可以求和。

​ 在一个单元格中填写公式后，点击单元格右下角拖拽或在此直接双击可以填充一列。

1. 公式中的比较判断

   比较运算符： = > < >= <= <>（不等于）；得到结果为true或false（false为0，true为1）

   公式中文本要用英文双引号引起来。

=(D6=“本地”)*30+E6

1. 单元格引用

   $代表锁定的意思

   相对引用：无$

=F4+J4

​ 绝对引用：选中要固定的值 eg: J4，按F4，变为绝对引用，有两个$

=F4+$L$4

​ 混合引用：一个$（既要向下又要向右拖动，要考虑两个方向）

九九乘法表：=$B23*C$22

二、认识函数

1. 如何使用函数

   =函数名(参数)

   参数是选择的一个区域。

2. 学习以下函数

   sum, average, max, min, rank（排什么,在哪里排记得F4锁定区域)

=SUM(D5:G5)

=AVERAGE(D5:G5)

=MAX(D5:D11)

=MIN(D5:D11)

=RANK(H5,$H$5:$H$11)

​ 引用为了拖拽而服务，要怎么拖拽就怎么引用。

1. 利用定位工具选择插入公式的位置

   跳跃式求和：选中该列—编辑—查找—转到–定位条件–空值–确定–开始标签右上角自动求和工具（自动选区）

   跳跃式求和：选中整个表—编辑—查找—转到–定位条件–空值–确定—写好公式—control+回车（批量填充）

三、IF函数逻辑判断

if函数和其他函数不同，是用来判断逻辑值的函数，不是数学类的函数。

1. 基本IF用法

   ​如果E2为男，那么称呼为先生，否则为女士。

=IF(E2=“男“,”先生“,”女士”)

1. IF函数的嵌套

   例：针对专业类，填上相应的专业代号。如果C2为理工，专业代号为LG；否则若C2为文科，那么专业代号为WK，否则专业代号为CJ。

=IF(B2=“理工”,“LG”,IF(B2=“文科”,“WK”,“CJ”))

1. 例：考生来源为本地，则加上三十分，考生来源为本省，加上二十分，如果是外省，加上十分。

=IF(G2=“本地”,H2+30,IF(G2=“本省”,H2+20,H2+10))

1. 例：总分为600分及以上，录取情况为第一批；总分400-600为第二批；总分400分以下落榜。

=IF(I2>=600,“第一批”,IF(I2>400,“第二批”,“落榜”))

1. 2003里IF只允许最多七层嵌套。如果超过三层以上的嵌套，那就考虑用别的函数了。

=IF(G6=“A级”,10000,0)+IF(G6=“B级”,9000,0)+IF(G6=“C级”,8000,0)+IF(G6=“D级”,7000,0)

=IF(G22=“A级”,“一级”,“”)&IF(G22=“B级”,“二级”,“”)&IF(G22=“C级”,“三级”,“”)

1. IF+ISERROR函数的运用

   ISERROR代表对值的正确或者错误的判定，返回True或False。

   ​例：如果D35除以C35是个错误值，就返回为0；如果不是错误，就直接运算D35/C35。

=IF(ISERROR(D35/C35),0,D35/C35)

四、AND函数与OR函数

1. AND的用法（且）

=IF(AND(A3=“男”,B3>=60),1000,0)

1. OR的用法（或）

=IF(OR(B12>=60,B12<40),1000,0)

1. IF和AND和OR的组合用法

=IF(OR(AND(B20>60,A20=“男”),AND(B20<40,A20=“女”)),1000,0)

五、COUNTIF函数

1. COUNT函数（数数）

   一个区域中有多少条数据

=COUNT(F:F)

1. COUNTIF函数语法

   ​计算区域中满足给定条件的单元格的个数

   ​=COUNTIF(区域,条件)

=COUNTIF(E:E,H8)

1. COUNTIF函数计算数值区间

   =COUNTIF(B2:G2,“>=60”)

2. 字符串超过15位时COUNTIF函数无法判断

   解决方法： =COUNTIF(A2:A3,A2&“*”)

=COUNTIF($A$8:$A$20,A8&“*”)

1. 在数据区域中寻找重复数据

=IF(COUNTIF(G:G,A2)=0,“未体检”,“已体检”)

1. 在数据有效性中使用COUNTIF函数

   //一组值或一个区域做数据有效性时和条件格式一样，只针对白色的单元格写公式

   将C列设置为禁止输入重复数据：数据–数据验证–允许：自定义–公式：=COUNTIF(C:C,C1)<2

   ​将D2:I20设置为禁止输入重复数据：数据–数据验证–允许：自定义–公式：=COUNTIF($D$2:$I$20,D2)<2

   1. 在条件格式中使用COUNTIF函数

   选中区域—开始选项卡–条件格式–新建规则—样式：经典–使用公式确定要设置格式的单元格–输入公式=COUNTIF(G:G,A2)=0—设置格式–确定

   将银行卡号重复数据标记为红色背景：选中区域—开始选项卡–条件格式–新建规则—样式：经典–使用公式确定要设置格式的单元格–输入公式=COUNTIF($E$2:$E$14,E2&“*”)>1—设置格式–确定

   1. COUNTIFS函数