条件格式与数据验证的公式用法、函数错误类型与IFERROR函数、VLOOKUP和HLOOKUP函数、个人所得税的计算方式、MATCH与INDEX函数

条件格式与数据验证的公式用法：
1、条件格式的公式用法：
在excel里的空白区域输入1，按住Ctrl往下拖，拖到10，选中这10个单元格，开始->条件格式，点击最后一个使用公式确定要设置格式的单元格，点击格式按钮，在点击填充，点击红色，在示例中看到红色的背景色，点击确定按钮，可以看到预览的红色，为符合此公式的值设置格式输入框里输入=G16>5，把值是1的单元格输进去，点击确定按钮，如下图：

可以看到>5的单元格背景色都是红色的，在左侧输入A，高度和右侧的一样，公式是右侧的，同样适用于左侧，也就是后5个值标上红色，对应的最下面5个A也标上红色，结果也的确如此

 

选中姓名这列，点击条件格式，点击最后一个使用公式确定要设置格式的单元格，点击格式按钮，在点击填充，点击黄色，在示例中看到黄色的背景色，点击确定按钮，可以看到预览的黄色，为符合此公式的值设置格式输入框里输入=B1=1，点击确定按钮，如下图：

只要领过补贴的姓名都被标上黄色

前面是B列有值的，如果把B列删掉，选中A列，重复上面的步骤，为符合此公式的值设置格式输入框里输入=COUNTIF($D$1:$D$9,A1)=1，点击确定按钮，同样可以实现

2、数据验证的公式用法：
数据->数据验证，点击数据验证，在设置tab下的允许下拉框选择整数，输入一个最小值(如100)和最大值(如500)，点击确定按钮，该区域输入一个整数，符合条件的不会报错，不符合就报错，输入英文、汉字、特殊字符、小数都会报如下错：

这就是数据验证的原理，把整数切换成任何值，点击确定按钮，就恢复到正常了

防止重复值出现的方法如下：

计算库存量：只有一个产品的库存量要写绝对的($B$2)，不能超过库存量，一般都会减100。公式如下图：

计算库存量：多个产品，勾选品类这列所有空白的单元格，点击数据验证，在设置tab下的允许下拉框选择序列，点击来源，选择A列的牛仔裤到羽绒服这7个单元格，如下图：

点击确定按钮，就会在G列空白单元格后面出现一个下拉框，只能选择那7类产品，不能输入其他的内容，输入会警告，如下图：

H列的数量要小于等于库存量，G列每个产品的求和公式是：=SUMIF(G:G,G2,H:H)，接下来在计算一下每个产品的库存量，公式是=SUMIF(A:A,G2,B:B)，把H列的数据清空，选中所有空白的单元格，点击数据验证，在设置tab下的允许下拉框选择自定义，公式输入=SUMIF(G:G,G2,H:H)<=SUMIF(A:A,G2,B:B)，如下图：

点击确定按钮，在H列里输入每个产品的数量，超过库存量会报警，如下图：

函数错误类型与IFERROR函数：

第一种：空白单元格里输入=1/0，回车，就会报#DIV/0!错误
第二种：，无法识别A和B，所以报#NAME?错误，A和B加上""就可以了
第四种：A8就是上面的#NULL!，数值+文本就报错了，报#VALUE!错误

第五种：=37895434^37854935，两个大数相乘的时候不知道多少位，就会报#NUM!错误
第六种：跨工作表引用就会报#REF!错误
第七种：sum求和时，两个不相交的区域相加就会报#NULL!错误

ISERROR函数：是错误函数吗，是返回TRUE，不是返回FALSE，=ISERROR(value)，如果值正确返回FALSE，如果值错误返回TRUE，如下图：

IFERROR函数：用法：=IFERROR(检查对象，显示值)，如果被检查对象为错误，则显示值，否则显示原对象(就是检查对象)，(E2-D2)/D2，如果D2是0，就会报错，返回100.00%，否则就是显示原对象

公式->其他函数->信息，点击信息，里面有许多函数，有ISBLANK()、ISTEXT()等，将数值类型转成文本，鼠标失去焦点就出现绿色的三角，这是文本类型，选中该单元格，等左侧出现三角时点击忽略错误，绿色的三角就消失了，可以把ISBLANK()、ISTEXT()试一下，出现TRUE，就是文本类型

VLOOKUP和HLOOKUP函数：
VLOOKUP概念：查找某个区域中的某个对象，并引用属于它的某个值(按列查找)，公式->查找与引用，可以找到VLOOKUP函数，查找和引用是不同的，VLOOKUP函数可以实现这两个动作
用法：=VLOOKUP(查找对象，查找区域，对应值在查找区域的列数，模糊/精确匹配)

用VLOOKUP函数查找指定姓名的工资，VLOOKUP函数传4个参数，第1个是要查找的值，这里就是吴奇，I2，第2个是要查找的范围B2:F10，最好从姓名开始往后拖，第3个是要查找的列，工资在哪列就写哪列，最后一个是精确还是模糊查找，0代表精确查找，1代表模糊查找

查到吴奇的工资后，往下拖，李梦的工资报错了，如下图：

#N/A错误是查找函数特有的，说白了就是找不到，点开这个错误，发现第2个参数，李梦不在粉色的范围里，所以报错了

左侧的区域要绝对引用一下，$B$2:$F$10就可以了，如下图：

还有一种方式，选中B-F列，也可以实现，=VLOOKUP(I2,B:F,4,0)

查找张力的货物代码、送货地址和手机号，如下图：

把第三个参数改一下就可以了，分别是3，5，7

近似匹配(1)原理：匹配查找区域内小于查找值的最大值

成绩单表，给同学的总分评级，如下图：

HLOOKUP概念：查找某个区域中的某个对象，并引用属于它的某个值(按行查找） 用的比较少
用法：=HLOOKUP(查找对象，查找区域，对应值在查找区域的行数，模糊/精确匹配）

计算出吴奇的工资后，往下拖就报错了，因为选择的3-7行就不对了，行变化了，所有要横着拖动，吴奇的位置由D11变成E10，工资就是7500，横着拖动就正常了

个人所得税的计算方式：

发现有个错误，原来是税前金额-起征点=负数，因此要修改一下这个vlookup函数，要以税前金额>起征点做个判断，修改如下图：

这下税率都正常了，接下来算速算扣除金额，点击0.45，看到公式，修改格式如下图：

接下来计算扣税金额，根据公式：扣税金额=(税前工资-起征点)*税率-速算扣除金额
在扣税金额单元格里输入=($H6-5000)*IF(H6>5000,VLOOKUP(H6-5000,$A$5:$E$12,4,1),0)-IF($H6>5000,VLOOKUP($H6-5000,$A$5:$E$12,5,1),0)，回车，扣税金额出来了，如下图：

最后计算税后工资，税前工资-扣税金额=税后工资，输入=H6-(($H6-5000)*IF(H6>5000,VLOOKUP(H6-5000,$A$5:$E$12,4,1),0)-IF($H6>5000,VLOOKUP($H6-5000,$A$5:$E$12,5,1),0))，回车，生成税后工资，如下图：

MATCH与INDEX函数：
MATCH概念：查找某个区域中的某个对象的位置，是查找函数
用法：=MATCH(查找对象，查找行/列，模糊/精确匹配），只能查找一行或者一列，基本上都是精确查找
查找张力在表格里的第几行，用MATCH函数，张力的位置是K5，选择C列，精确匹配，输入=MATCH(K5,C:C,0)，回车，找到位置是14行

INDEX概念：显示行列交叉的值，是引用函数
用法：=INDEX(查找区域，行，列)，就是说有个值在第几行、第几列，但是我不知道是什么，用index函数引用一下
查找张力的手机号，MATCH和INDEX函数嵌套使用，输入=INDEX(B:I,MATCH(K5,C:C,0),8)，B:I是查找区域，MATCH(K5,C:C,0)是第14行，上面已实现，区域了的第8列就是手机号码，输入8即可，如下图：

用VLOOKUP也能实现，但是VLOOKUP有短板，VLOOKUP函数中第二个参数，区域选择时，第一列必须有张力，如果不是第一列就会报错了，现在有个表格联系人在最后一列，就没法使用VLOOKUP了，只能INDEX和VLOOKUP嵌套使用来实现，如下图：

INDEX函数传入一个区域，全选，MATCH函数求出行，手机号码在第8列，嵌套使用求出了手机号码，它俩并不能取代VLOOKUP，VLOOKUP更快捷，只是某些场景下INDEX和MATCH嵌套使用