商业数据分析从入门到入职（3）Excel进阶应用

淡淡的烟草味﹌ 2022-12-07 11:41 166阅读 0赞

### 文章目录 ###

*  一、数据分类汇总与验证
 *   *  1.分类汇总
     *  2.数据有效性与保护工作表
 *  二、公式与函数
 *   *  1.公式与函数基本使用
     *  2.逻辑判断IF
     *  3.COUNTIF
     *  4.COUNTIF应用
     *   *  重复验证
         *  报名统计
     *  5.SUMIF
 *  三、VLOOKUP的使用
 *   *  1.VLOOKUP的基本用法
     *  2.跨表和跨文件查找
     *  3.模糊查找
     *  4.文本VLOOKUP和HLOOKUP
     *  5.Match和Index
     *  6.返回多列
     *  7.数组与多匹配

# 一、数据分类汇总与验证 #

## 1.分类汇总 ##

Excel中的数据可以通过**组合**实现折叠部分数据，还可以打开数据，如下：  
![excel data group][]

> 如需获取数据进行测试学习，可以直接点击加QQ群 [![Python极客部落][Python]963624318][Python_Python_963624318] ，在群文件夹**商业数据分析从入门到入职**中下载即可。

有很多时候，需要对某一类数据进行汇总，如产品分类为Technology的订单的总销售额为多少，如下：  
![excel subtotal simple][]

可以看到，要进行**分类汇总**，需要先进行**排序**；  
在完成分类汇总后，自动实现组合，分为3个等级，1级为所有行的Sales总计，2级为三个商品种类的Sales汇总，3级为所有订单信息；  
要想将汇总结果复制到其他地方，需要在**定位条件**中选择**可见单元格**。

也可以删除分类汇总，如下：  
![excel subtotal simple delete][]

还可以进行多个汇总，如下：  
![excel subtotal multi column][]

分类汇总可以自动生成一列数据，可以更快速地定位单元格。

这可以应用到给多个多行单元格合并，如下：  
![excel subtotal cell combine][]

显然，实现了预期的效果。

## 2.数据有效性与保护工作表 ##

还可以对数据输入进行限制，如果不合法则不允许输入，此时需要验证**数据的有效性**。  
如下：  
![excel data effectiveness][]

可以看到，可以进行数值范围限制；  
可以提供选项进行选择；  
可以限制位数；  
还可以修改提示弹出框；  
同时，只有在输入、编辑时才会触发输入限制。

这个特性可以用来限制其他用户改写数据，即保护工作表，如下：  
![excel data edit limit][]

显然，此时修改数据都会报错。

# 二、公式与函数 #

## 1.公式与函数基本使用 ##

公式用`=`开始，简单使用如下：  
![excel formula function = simple][excel formula function _ simple]

可以看到，实现了两个数的相加，但是要加的数很多时，如果手动输入，显然会很麻烦。  
此时需要用新的工具进行计算，即函数，如求和就用`sum()`函数，如下：  
![excel formula function = multi][excel formula function _ multi]

可以看到，不仅支持求和计算，而且在数据改变之后，结果也会同步改变，并且可以复制函数到其他位置。

Excel公式的组成部分：  
函数、引用、运算符和常量。  
比如对于公式`=PI()*A@^2`：  
组成部分包括：

*  函数  
    如`PI()`函数返回pi值3.14159…。
 *  引用  
    如A2返回单元格A2中的值。
 *  常量  
    直接输入到公式中的数字或文本值，例如2。
 *  运算符  
    ^运算符表示数字的乘方，而\*运算符表示数字的乘积。

`&`是连字符，可以连接两个文本，如下：  
![excel formula function & text link][excel formula function _ text link]

在Excel中也有真和假，即TRUE和FALSE，TRUE对应1，FALSE对应0。

对一列根据条件进行不同赋值，如下：  
![excel formula function true false quote][]

可以看到，TRUE和FALSE可以参与运算；  
在使用表格中的值代替常量进行计算时，出现了引用的问题，默认一般是相对引用，即在进行填充时一般是使用的相同行对应列的数据，如`=(A3<>"中部地区")*8+K3`、`=(A4<>"中部地区")*8+K4`，而此时`K4`位置的值为空，数值时默默认为0，因此结果为8；  
此时要想达到目标，需要使用绝对引用，即使用`K3`位置的值，此时只需要在行列序号前加`$`符号即可，可以选中`K3`按快捷键`F4`即可实现，变为`=(A3<>"中部地区")*8+$K$3`、`=(A4<>"中部地区")*8+$K$3`等。

还可以进行混合引用，即位置的行和列中只有一个改变，另一个不改变，不改变的用`$`修饰。  
如下：  
![excel formula function quote mixed][]

显然，通过混合引用实现了打印九九乘法表。

函数基本用法如下：  
![excel formula function base operation][]

计算并填充空值如下：  
![excel formula function compute fill][]

显然，要填充的值都是左边第二个数除以左边第一个数，所以能一次性计算出来。

但是很多时候，数据不是这么分布的，就得灵活应变，如下：  
![excel formula function sum auto][]

可以看到，此时用到了自动求和工具。

## 2.逻辑判断IF ##

之前判断是否是中部地区并根据两种情况计算是使用TRUE和FALSE实现的，也可以使用IF来实现。

if判断的简单使用如下：  
![excel logic judge if simple][]

可以看到，如果区域为中部地区，销售单价即为建议单价，否则为建议单价+8.

还可以进行嵌套使用：  
![excel logic judge if nesting][]

还可以进行更复杂的嵌套，如下：  
![excel logic judge if nesting complex][]

显然，if的结果可以进行相加等运算。

除了使用if嵌套，还可以使用连字符`&`，如下：  
![excel logic judge if nesting &][excel logic judge if nesting 1]

显然，连字符也能实现同样的效果，并且用到了逻辑运算**且运算AND**。

有时候出现错误时，会出现错误提示，可以根据需要进行调整，使用`IFERROR`、`IFNA`、`ISERROR`等。  
如下：  
![excel logic judge if iferror][]

现以退休为例进一步说明逻辑判断的优化：  
企业职工退休年龄是男年满60周岁；  
女工人年满50周岁，女干部年满55周岁。

那么可能有以下几种情况：  
满60岁男职工，退休；  
60岁以下男职工，不退休；  
满50岁女职工，退休；  
50岁以下女职工，不退休；  
满55岁女干部，退休；  
55岁以下女干部，不退休；

要使用一般的if判断，可能要嵌套很多if语句，但是其实是可以简化的，因为IF判断的实质就是条件为TRUE执行一种情况、为FALSE时执行另一种情况，因此可以简单将退休与否分为退休和不退休，其中`>60岁且是 男` 者 `>50岁且是女职工`或者`>55岁且是女干部`属于退休，其他均属于不退休，此时可以如下：  
![excel logic judge if two sides][]

因此在最终产生的结果只有两种情况时，可以采用这种方式。

## 3.COUNTIF ##

`COUNTIF`用于根据条件进行计数，符合某个条件则计数，否则不计数，类似的用法还包括`COUNTIFS`、`SUMIF`等。

简单使用如下：  
![excel logic judge countif simple][]

计数时使用较复杂的条件如下：  
![excel logic judge countif condition][]

显然，有多个条件时，可以分别计数再进行算术计算。

有多个条件要满足时，还可以使用`COUNTIFS`，如下：  
![excel logic judge countif countifs][]

此时可以根据多个条件进行计数。

## 4.COUNTIF应用 ##

### 重复验证 ###

很多时候会出现重复数据，这是可以对数据进行计数，如果计数大于1则说明出现了重复。

如下：  
![excel repeat countif][]

可以看到，数据为文本型数据，在进行计数时会根据前15位进行计数，因此在对A3、A11、A12进行计数时会重复，此时可以通过在后面连接通配符解决。

还可以限制输入重复数据，这是结合数据验证实现的，如下：  
![excel repeat countif norepeat][]

### 报名统计 ###

有一个联系人名单，其中有部分已报名，也有对应的名单，根据已报名名单对所有联系人名单进行统计，是否报名，如下：  
![excel application enroll is\_enrolled][excel application enroll is_enrolled]

显然，很快自动统计出是否报名；  
这是通过`IF`和`COUNTIF`结合使用实现的，Excel中结合各种工具可以实现很丰富的功能。

还可以填充颜色，需要使用到**条件格式**：  
![excel application enroll condition style][]

可以看到，对符合条件的数据进行了筛选；  
同时数据发生变化时，结果也会同步变化。

## 5.SUMIF ##

和`COUNTIF`类似，`SUMIF`是根据条件进行求和的，简单使用如下：  
![excel sumif simple][]

待求和数据所在的列和条件所在的列不是同一列时，稍微复杂一点，如下：  
![excel sumif different column][]

显然，此时需要传递3个参数，才能求和。

还可以根据多个条件进行求和，有多种方式，一种方式是**增加辅助列**拼接两个条件，再进行求和，如下：  
![excel sumif multi condition new line][]

可以看到，计算出来的结果是依赖于辅助列的，如果删除或修改辅助列，结果也会发生变化。

还有一种方式是直接使用`SUMIFS`函数，如下：  
![excel sumif multi condition sumifs][]

显然，得到了同样的结果。

还可以进行更加灵活的使用，如**简写参数**，如下：  
![excel sumif shorten params][]

此时也可以求出结果，这范围选择了全部数据，才可以这么简写，一般条件下应该慎重选择这种方式。

对于一定的库存，需要限制出货量不能超过库存，这也可以通过`SUMIF`来实现，如下：  
![excel sumif storage verify][]

显然，实现了通过`SUMIF`来限制输入的效果。

# 三、VLOOKUP的使用 #

## 1.VLOOKUP的基本用法 ##

Excel中的VLOOKUP函数一般用于在表格或区域中**按行查找内容**，如下：

VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])

意义如下：

> VLOOKUP （你想要查找的内容，要查找的位置，包含要返回的值的区域中的列号，返回近似或精确匹配-表示为 1/TRUE或0/FALSE）

通俗一点，参数即为找什么、去哪找、具体结果、精确/模糊；  
会从查找区域的**最左边的列**查找；  
当存在多条满足条件的记录时，只能返回**第1条**满足条件的记录；  
区域**包括所有查找部分以及结果部分**。

简单使用示意如下：  
![excel vlookup simple][]

再如：  
![excel vlookup simple 2][]

显然，可以实现查找。

vlookup还可以实现**嵌套查找**，即联合两张表查找，如下：  
![excel vlookup join tables][]

显然，通过嵌套`VLOOKUP`，实现了两张表的联合查找。

也可以实现多匹配，即查找多个条件，可以通过**添加辅助列**实现，如下：  
![excel vlookup multi match][]

需要注意，辅助列要加在第一列，否则会查找失败。

## 2.跨表和跨文件查找 ##

`VLOOKUP`还可以实现**跨表查找**，即查询涉及两个或多个工作表。  
如下：  
![excel vlookup cross table][]

有时候要查询的数据在选中的范围中不能完全匹配、而只是其中的一部分，此时可以选择**拼接通配符字符串**。  
如下：  
![excel vlookup cross table &][excel vlookup cross table 1]

除了跨表，还可以实现**跨文件**，如下：  
![excel vlookup cross file][]

## 3.模糊查找 ##

之前都是的查找方式都是精确查找，即最后一个参数为0，其实还可以为1，即模糊查找。

模糊查找可以用于**区间查找**，需要区间是有序的，查找的原理是二分查找。  
如下：  
![excel vlookup fuzzy match][]

还可以用于打标签，如下：  
![excel vlookup tag][]

## 4.文本VLOOKUP和HLOOKUP ##

在进行查找时，如果被查找的数据或者选中的区域中有文本形式的数字时，需要进行类型转换，如：  
数字转换成文本时，用连字符连接一个空字符串；  
文本转化成数字时，用`*1`、`+0`、`--`等方式。

如下：  
![excel vlookup text][]

显然，是通过if条件判断来实现的；  
同时需要转换数据类型。

除了VLOOKUP用于**纵向查找**，还有HLOOKUP用于**横向查找**，两者可以结合使用。  
如下：  
![excel vlookup hlookup][]

显然，使用了HLOOKUP后，可以实现同步更新。

## 5.Match和Index ##

`Match`和`VLOOKUP`用法类似，返回的是行和列的序号；  
`Index`用于根据行和列查找数据；  
两者可以结合使用，先用`Match`定位，再用`Index`查找，这比`VLOOKUP`的应用更广，可以进行反向查找，即要查找的数据不在选中区域的第一列。

两者的简单使用如下：  
![excel vlookup match index][]

两者结合使用如下：  
![excel vlookup match index combine][]

此时，可以通过后面查找前面，避开了`VLOOKUP`的限制。

## 6.返回多列 ##

`VLOOKUP`也可以和`Match`结合使用，可以实现返回多列的效果。  
如下：  
![excel vlookup match combine][]

需要注意，这里要使用**混合引用**，否则会出错。

再如：  
![excel vlookup match multi line][]

`VLOOKUP`一般只会返回查询到的第一条数据，但是可以增加辅助列为相同数据进行排序、拼接成唯一的值，从而满足`VLOOKUP`的条件。  
如下：  
![excel vlookup match repeat][]

## 7.数组与多匹配 ##

使用数组的方式实现`VLOOKUP`的多匹配，如下：  
![excel vlookup multi match &][excel vlookup multi match 1]

需要注意，此时因为涉及到数组，不能直接按`Ctrl`，而应该是`Ctrl+Shift+Enter`，才能正常查询到值；  
`=VLOOKUP(E4&F4,IF({1,0},A2:A13&B2:B13,C2:C13),2,0)`中，`IF({1,0},A2:A13&B2:B13,C2:C13)`即是一个数组，包含两列，第一列是A列和B列的连接，第二列是C列，如下：  
![excel vlookup multi match & array][excel vlookup multi match _ array]

可以看到，利用公式和函数隐性地生成了两列的数组。

还可以生成其他形式的数组，如下：  
![excel vlookup array matrix][]

需要注意，都是按`Ctrl+Shift+Enter`得出的。

这可以用于求各行乘积的总和，而不需要先计算各行的乘积，如下：  
![excel vlookup array sum][]

[excel data group]: /images/20221123/23d95e88ef124e2f98eb5b50c4d0783b.png
[Python]: https://pub.idqqimg.com/wpa/images/group.png
[Python_Python_963624318]: /images/20221123/6311f19273984cdc81270fb620964901.png
[excel subtotal simple]: /images/20221123/037d1364cfa34608a5c5e523803b51eb.png
[excel subtotal simple delete]: /images/20221123/e4a7843719e24e47aabe3339c9da032e.png
[excel subtotal multi column]: /images/20221123/1db92eba5a7a4305a41c36e2e1f79565.png
[excel subtotal cell combine]: /images/20221123/b77a77044fc54adea44dcb1560173283.png
[excel data effectiveness]: /images/20221123/7a35862d76564389a68ec02614917542.png
[excel data edit limit]: /images/20221123/b5bd5774fc69417d8a431d26a72f35e9.png
[excel formula function _ simple]: /images/20221123/b1ae0028ac3742d7a36520294d677212.png
[excel formula function _ multi]: /images/20221123/fb458b7518c54edda1cfaaa709a2d991.png
[excel formula function _ text link]: /images/20221123/c3dbbac0519a4488bcb0e9b2d5765350.png
[excel formula function true false quote]: /images/20221123/ca4ff01473b240bda1c931b3e8c48ea1.png
[excel formula function quote mixed]: /images/20221123/15e63b5c49234907816f506d7dcffb0e.png
[excel formula function base operation]: /images/20221123/1f1db5ebd3a14fba94eaab3943887789.png
[excel formula function compute fill]: /images/20221123/2ee4889f62ec40c69187df9a0394d140.png
[excel formula function sum auto]: /images/20221123/3a3f8e99ac804cf3afe9b695bf5916c8.png
[excel logic judge if simple]: /images/20221123/d06b4e26d0b54830956d161cf3dc7fda.png
[excel logic judge if nesting]: /images/20221123/6a7b76f4595944d2ae6e39dbe031f7dd.png
[excel logic judge if nesting complex]: /images/20221123/feef878c79b34a689c703b5aae8f5d6d.png
[excel logic judge if nesting 1]: /images/20221123/5d49322ee3244d9ebb2a35ba4005b086.png
[excel logic judge if iferror]: /images/20221123/0e38c7f0130942778f84ba522ac1b281.png
[excel logic judge if two sides]: /images/20221123/c78c00436a93400ca64c4b0a77c3ff78.png
[excel logic judge countif simple]: /images/20221123/2da21585984e4ef089c83892e3b269d5.png
[excel logic judge countif condition]: /images/20221123/6370ecbd069648edb31ebf434a9563c2.png
[excel logic judge countif countifs]: /images/20221123/5ca8d3b3aebb40188ab49aa123bc9eb8.png
[excel repeat countif]: /images/20221123/7492bf5557fe4b8eb163822cf8df40ff.png
[excel repeat countif norepeat]: /images/20221123/cb4711dff90d4468a09058a978937c53.png
[excel application enroll is_enrolled]: /images/20221123/71e88989550748179d2ffd6fbc519f51.png
[excel application enroll condition style]: /images/20221123/00aa4d112a7b4e958dbd62f1882baf6a.png
[excel sumif simple]: /images/20221123/01c70d42c93547fbb246017ff10a0055.png
[excel sumif different column]: /images/20221123/bb63360766964c5890ac4def7b59dc6c.png
[excel sumif multi condition new line]: /images/20221123/7db73b596a4e465b88e64ff76e9fd663.png
[excel sumif multi condition sumifs]: /images/20221123/248063ea11a547aab6ba397ede1ad31c.png
[excel sumif shorten params]: /images/20221123/290e6de8c9554f9daf03929752e22344.png
[excel sumif storage verify]: /images/20221123/04698d21633d4bca8b24645b0ba3ab24.png
[excel vlookup simple]: /images/20221123/b963746eae1548ce856c2748729e6607.png
[excel vlookup simple 2]: /images/20221123/ab58ce4719bb471e9f184ac812d1d108.png
[excel vlookup join tables]: /images/20221123/14d6b72e62f84595852f179dd70c5ea7.png
[excel vlookup multi match]: /images/20221123/73be6f815dbc4db4a29f86db8aae856a.png
[excel vlookup cross table]: /images/20221123/2b62dd76b83c4e399db3b8b62ac38a6e.png
[excel vlookup cross table 1]: /images/20221123/bcf75b58f8444595b00e6481ec3c1c57.png
[excel vlookup cross file]: /images/20221123/7bec2fb6ca6044b1b1705c0d0c015e36.png
[excel vlookup fuzzy match]: /images/20221123/7d01b2cdf7674e32a0ea13f433e715f6.png
[excel vlookup tag]: /images/20221123/275fc027a91840d2a58288e9f3af6fa2.png
[excel vlookup text]: /images/20221123/478c1672944649829e650c751d6bdb68.png
[excel vlookup hlookup]: /images/20221123/b73fe5e47b5441788d373e456c4151d5.png
[excel vlookup match index]: /images/20221123/2de85fac4aad415c850dc5aacb4611e7.png
[excel vlookup match index combine]: /images/20221123/284549e3b3644ef1a9556dee43be3d1e.png
[excel vlookup match combine]: /images/20221123/ffde1b08b5734c92a2932381f7a90e4f.png
[excel vlookup match multi line]: /images/20221123/0bf2a6141032405f871f0e42072d5059.png
[excel vlookup match repeat]: /images/20221123/ac574bb860bd4d4faa2b3f7470c7899f.png
[excel vlookup multi match 1]: /images/20221123/ea96d0e8949247178329d36caaa184ed.png
[excel vlookup multi match _ array]: /images/20221123/be0aafc038cc41679a7951e79376ffa5.png
[excel vlookup array matrix]: /images/20221123/67077dae079b4ddbae49105c635fecea.png
[excel vlookup array sum]: /images/20221123/ed7b4b1dd65d449ebec894c41390c9d0.png