Excel笔记2

1. =IF函数：最简单应用：=IF(B27>=60,"及格","不及格")

多条件的话需要嵌套;

=IF(E25>=500,"专属",IF(AND(E25>=1000,E25<500),"进卡","普通"))

1. IF函数应用--数据类型判断：ISBLANK---是否空格

ISNUMBER----是否数字

ISTEXT----是否文本

ISNOTTEXT----是否非文本

ISNA---是否是#NA

ISERR---是否是除#NA以外的任何错误值

实例：

=FIND("快乐",H25)---没判断之前

 

=ISNUMBER(FIND("快乐",H25))---判断之后

 

为了提高易读性，进一步判断：

=IF(ISNUMBER(FIND("快乐",H25)),"包含","不包含")

 

1. 数组计算：在进行数组的运算之前都需要先选中所需空白单元格。

==Ctrl+/删除数组。数组与单值运算：=数值+表格所有值，然后ctrl+shift+回车。

==同方向的一维数组计算：=SUM(第一组数组*第二组数组)----新的数组。然后ctrl+shift+回车。

==不同方向的数组运算：需要行内的每一行*整一列，依次计算。

==数据公式中带有条件判断：判断要计算正数相加结果=SUM(全选中>0)*再次全选中),然后ctrl+shift+回车。

==取前几列数组：纵向：=INDEX(G1:G5,ROW(1:3))

横向：=INDEX(G1:G5,COLUMN(1:3))

案例：

数列求和：1~100：=SUM(ROW(1:100))

奇数列求和：=SUM(IF(ISODD(ROW(1:100)),ROW(1:100),0))---ISODD

偶数列求和：=SUM(IF(ISEVEN(ROW(1:100)),ROW(1:100),0))--ISEVEN

计算前3名的奖金金额：=LARGE(F1:F3*G1:G3,ROW(1:3))

计算前3名的奖金占:

=SUM(LARGE(F1:F3*G1:G3,ROW(1:3)))/SUM(F1:F3*G1:G3)

==非法值求和：=SUM(IF(ISNA(E1:E3),0,E1:E3))---跳过非法值求和

==条件统计：求年龄大于30岁以上的职称称为高级的男员工人数：

=SUM(C9:C15>30)*(D9:D15="高级")*(E9:E15="男")----中间都是*号

==逆向查询：=VLOOKUP(H12,IF({1,0},D10:D15,C10:C15),2,)

 

1. SUMPRODUCT(先乘积再求和)

条件计数：=SUMPRODUCT((条件1)*(条件2)*....)

条件求和：=SUMPRODUCT((条件1)*(条件2)*....，求和列)

--对于逻辑“或”（并集）的多条件之间可以用加号“+”

--对于逻辑“与”（交集）的多条件之间可以用乘号“*”

VALUE是一个转换符号：将日期转为数值.

计算1,5月份的总增人数：

=SUMPRODUCT((MONTH(K9:K12)=5)+(MONTH(K9:K12)=1),L9:L12)

1. 汇总之王-Subtotal：=SUBTOTAL(109,E10:E15)

第一个参数就是可选函数。

1. 数据库函数：DAVERAGE(database, field, criteria)

Database    构成列表或数据库的单元格区域。

Field    指定函数所使用的列

Criteria    为包含指定条件的单元格区域。

1. Indirect函数：利用间接引用实现跨表计算及二级菜单制作：

 ==直接引用：=A1--------在跨表计算会有局限性---数据不能批量处理。

==间接引用：=INDIRECT("A1")

跨表求和：=SUM(INDIRECT(B1&"A1:A4"))

不同的表格对字段进行汇总：要对行进行锁定。A$4

(字段一致的情况)=INDIRECT(A$4&!C*ROW(B2))

(字段不一致的情况)=VLOOKUP($C6,INDIRECT(D$&"!B1:C8"),2)

分析清楚行和列谁需要固定来f4.

1. 插入表格的时候要把它改成“普通区域”。
2. 快速添加名称管理器的数据：选中所需要的，在第二步把最左列的√去掉。确定即可。
3. =INDIRECT(---------=INDIRECT($G2)两次f4
4. 

最后一节课：附上成功完成的二级菜单：