Excel基础教程（3）

Excel（3）

一、条件格式与公式

1. 条件格式

   条件格式： 如果数据怎么样，就给单元格设置什么格式，动态改变单元格格式，标注出特殊数据。

   数据范围：选中区域–开始选项卡–条件格式–突出显示单元格规则—规则–设置格式

   模糊匹配：选中区域–开始选项卡–条件格式–突出显示单元格规则–文本包含–设置格式

   清除条件格式：开始选项卡–条件格式–清除规则–清除所选单元格的规则

   查找重复值：选中区域–开始选项卡–条件格式–突出显示单元格规则—重复值 （空列中也可做相应设置，使得若输入重复值会有格式变化）

   项目规则选取：选中区域–开始选项卡–条件格式–项目选取规则

   色阶和数据条：色阶通过颜色过渡显示数据大小（温度），数据条通过条的长短显示数据大小。

   图标集：通过不同形状描述不同大小的数值。

   新建规则：选中区域–开始选项卡—条件格式—新建规则—样式：经典

   为错误值设置格式：选中区域–开始选项卡—条件格式—新建规则—样式：经典–只为包含以下内容的单元格设置格式–错误

2. 切片器（其实就是筛选器）

   为数据透视表中的数据制作数据条：

   a. 点中表中任一数据单元格–插入选项卡–数据透视表—确定—右键–数据透视表选项—显示–经典的数据透视表布局–字段按行、列放到行字段、列字段和值字段–随便选择行字段中的一个日期—右键—组合–步长月份

   b. 选中要统计的数据区域—开始选项卡–条件格式–数据条

   c. 点中数据透视表中任一单元格—插入选项卡–切片器（筛选分类按钮）【右上角清除筛选器或者选中切片器按delete键删掉】

   注意：切片器必须工作于.xlsx格式的EXCEL文件！

3. 定义多重条件的条件格式

   选中区域–开始选项卡—条件格式，每个条件做一次，条件覆盖区域不相互重合时。

   条件相互覆盖时，先做区域大的条件，后做的会覆盖先做的。

4. 使用公式定义条件格式

   条件格式不针对单元格本身内容而是根据其他单元格内容做判断时，需要用公式。

   将数量大于100的项目日期标记为红色背景：选中日期区域–开始选项卡—条件格式–新建规则–样式：经典–使用公式确定要设置格式的单元—公式为 =D2>100

   将数量大于100的项目整行标记为红色背景：选中整个数据区域–开始选项卡—条件格式–新建规则–样式：经典–使用公式确定要设置格式的单元—公式为 =$D2>100

   注意：写公式时注意单元格引用！

5. 修改条件格式

   选中已做的条件格式–开始选项卡—条件格式–管理规则–编辑规则

二、文本函数

文本函数：关心一个单元格中的字符串怎么样。

1. 截取文本

   =LEFT(截取哪个单元格,截取几位)

提取前三位： =LEFT(A3,3)

​ =RIGHT(截取哪个单元格,截取几位)

提取后四位： =RIGHT(E3,4)

​ =MID(截取哪个单元格,从第几位开始,截取几位)

提取中间三位： =MID(A3,4,3)

提取身份证性别位数字（身份证有15位也有18位）： =RIGHT(LEFT(B13,17),1)

1. 获取文本中的信息

   FIND函数：查找一个字符在字符串中是第几位 。当该字符有多个时，会返回第一个的位数。

   =FIND(找谁,在哪找,(从哪一位开始找))

提取邮箱@前的姓名： =LEFT(F2,FIND(“@”,F2)-1)

要想找到第二个@的位置： =FIND(”@”,H11,find(“@”,H11)+1) 从第一个@后面一位开始找

提取邮箱@后的域名： =MID(F2,FIND(“@”,F2)+1,FIND(“.”,F2)-FIND(“@”,F2)-1)

1. LEN和LENB

   求字符串的长度（即有几个字符）： =LEN(text)

=LEN(“张三”) 结果为2

​ 求字符串的字节长度（即有几个字节）： =LENB(text)

=LENB(“张三”) 结果为4

​ 汉字一个字符等于两个字节，汉字是双字节字符。字母数字特殊符号等都是单字节字符。

提取283元或458千克的单位（利用一个汉字字节长度比字符长度多1）： =RIGHT(A2,LENB(A2)-LEN(A2))

1. 关于身份证

   身份证地区判断：（提取身份证号前六位，在对应地区码表中找到所属地区）

=VLOOKUP(LEFT(B2,6)*1,地区码!$A:$B,2,0)

​ 【注意：文本和数字格式！通过文本（如身份证号）处理得到的结果肯定是文本，而地区码是数值（判断是不是数值可以求和验证一下，能相加就是数值），直接找会出错，要文本*1或—文本】

​ 身份证提取生日：

=DATE(MID(B2,7,4),MID(B2,11,2),MID(B2,13,2))

=TEXT(MID(B2,7,8),"0000-00-00")          TEXT得到的是文本格式，不是日期格式，所以要*1转换

​ 身份证判断性别：

=IF(MOD(RIGHT(LEFT(B2,17),1),2)=1,“男”,“女”)

​ 身份证验证真伪：

=IF(LOOKUP((LEFT(A1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2)-ROUNDDOWN((LEFT(A1,1)*7+MID(A1,2,1)*9+MID(A1,3,1)*10+MID(A1,4,1)*5+MID(A1,5,1)*8+MID(A1,6,1)*4+MID(A1,7,1)*2+MID(A1,8,1)+MID(A1,9,1)*6+MID(A1,10,1)*3+MID(A1,11,1)*7+MID(A1,12,1)*9+MID(A1,13,1)*10+MID(A1,14,1)*5+MID(A1,15,1)*8+MID(A1,16,1)*4+MID(A1,17,1)*2)/11,0)*11,{0,1,2,3,4,5,6,7,8,9,10},{"1","0","x","9","8","7","6","5","4","3","2"})=RIGHT(A1,1),"OK","Wrong")

三、数学函数

一、认识函数

1. ROUND函数

   ROUND函数：四舍五入。

   =ROUND(待处理数字,要四舍五入的位数)

=ROUND(E2,2)

​ ROUNDUP函数：直接向上进位（计算工作量需要1.1个人工，直接进位算为2个）

​ =ROUNDUP(待处理数字,要保留的位数)

=ROUNDUP(H3,0)

​ ROUNDDOWN函数：直接舍掉（计算员工假期能休3.7天，直接舍掉算为3天）

​ =ROUNDDOWN(待处理数字,要保留的位数)

=ROUNDDOWN(H3,0)

​ INT函数：直接取整（处理负值时与ROUNDDOWN有区别：-2.5用INT为-3，用ROUNDDOWN为-2）

​ =INT(待处理数字)

=INT(H3)

1. MOD函数

   MOD函数：求余数。

   =MOD(除数,被除数)

   判断奇偶： =MOD(除数,2)

   得到小数部分： =MOD(除数,1)

   特殊的舍入：员工假期计算，舍为整数或几.5（ 节假日最小单位为半天）

=IF(MOD(C2,1)>=0.5,INT(C2)+0.5,INT(C2)) 或 =INT(C2*2)/2

1. ROW函数与COLUMN函数

   求单元格的行数： =ROW(单元格)

=ROW(A8)

​ 求单元格的列数： =COLUMN(单元格)

=COLUMN(C13)

​ 返回公式所在单元格自己的行数： =ROW()

​ 返回公式所在单元格自己的列数： =COLUMN()

​ ROW()和COLUMN()常用于判断当前单元格的位置。

1. 基于位置规律的引用

   INDEX函数：给其一个区域，找出一个东西。（INDEX类似于一个机械手抓东西，但是需要规则指导）

   列转为行：复制一列—选择性粘贴—转置。

列转为行（引用位置与行列有关）： =INDEX($A:$A,COLUMN()-2)

跳跃取值（间隔5放大5倍）： =INDEX($E:$E,ROW()*5-17)

一列分多列： =INDEX($A:$A,ROW()*3+COLUMN()-10)

​ 分析思路：先写需求，再找规律，最后调试。

四、LOOKUP函数和数组

1. 回顾统计函数SUMIF和SUMIFS

   =SUMIF(在哪里找,找什么,把什么求和)

=SUMIF($B:$B,I8,$E:$E)

​ =SUMIFS(把什么求和,第一个条件区域,第一个条件,第二个条件区域,第二个